Re: [sqlite] ensuring uniqueness of tuples spanning across multipletables?
> Jim Morris writes: > The recent thread may relate: "[sqlite] Is there an efficient way to > insert unique rows (UNIQUE(a, b, c)) into an fts3 virtual table?" > INSERT INTO fts3_table (a,b,c) > SELECT 'an A','a B','a C' > WHERE NOT EXISTS > (SELECT DISTINCT a,b,c > FROM fts3_table > WHERE a='an A' AND b='a B' AND c='a C'); > The above SQL could be adapted to your schema. As mentioned, the > performance will be slower than a straight insert. Thanks. It's a solution not quite for the problem I'm having, but I'll probably stick to it (and to the denormalized schema it imples.) However, I wonder, would the following (slightly more concise) query imply any performance loss in comparison to the one above? INSERT INTO fts3_table (a, b, c) SELECT 'an A', 'a B', 'a C' EXCEPT SELECT DISTINCT a, b, c FROM fts3_table; Also, I'm curious if DISTINCT may cause any performance loss in the case that the columns in question are constrained by an UNIQUE index? Like: CREATE UNIQUE INDEX "foo-unique" ON "foo" (a, b, c); -- FSF associate member #7257 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] System.Data.SQLite Exception with wrong password
Hi, I'm using a password protected database. When I type the wrong password in my app the resulting Exception is a SQLiteException with this message: "File opened is not a database file". The ErrorCode is set to "NotADatabase". Wouldn't it more correct if this code is set to something like SQLiteErrorCode.Auth or the Exception is more specialized? Thank you Marco ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DateTimeOffset in SQLite
Steffen Mangold wrote: > how to use DateTimeOffset with Sqlite, if it is possible? What's DateTimeOffset? Offset from what to what? What exactly are you trying to achieve? See if this helps: http://www.sqlite.org/lang_datefunc.html -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] DateTimeOffset in SQLite
Hi all, how to use DateTimeOffset with Sqlite, if it is possible? Regards Steffen Mangold ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Meaning of sqlite3_wal_checkpoint_v2 parameters
Hi! Can you help me understand more exactly what the output parameters are from sqlite3_wal_checkpoint_v2()? Specifically: 1) What is the relationship between pnLog and pnCkpt: is pnLog>=pnCkpt always true? 2) Under what circumstances would pnLog>pnCkpt be true? Also, can you confirm I understand the following correctly: 3) When using SQLITE_CHECKPOINT_PASSIVE, the WAL file will grow as needed, indefinitely, without ever shrinking. 4) When using a separate checkpointing thread, pnLog only goes down if the read/write thread has no active queries when the checkpoint runs. (Eg, if the checkpoint thread runs while the read/write thread has an active query, it'll still checkpoint, but it just won't reset the pnLog to 0.) Finally, and I'm not sure if this is related, but I've read in several threads that sometimes you can get the SQLITE_CORRUPT error even if the database isn't corrupt, and the proper response is to just retry. 5) Is this considered to be a bug, or is it the result of some temporary hardware issue? 6) Is this aggravated by the use of WAL, or totally unrelated? Some data about our setup is at the end of this email; I welcome you advice. Thanks! -david -- We have a 3.7.7.1 database named "main.db", which is in WAL mode, with a corresponding "main.db-wal" file. The main.db file is 13519440896 bytes (about 13GB) while the WAL file is 25439184 bytes (about 25MB). Both threads open the database with "SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_NOMUTEX" and SQLITE_CONFIG_MULTITHREAD, sqlite3_enable_shared_cache(0), and set "PRAGMA journal_mode = WAL;" We checkpoint the main.db database every 15 seconds using SQLITE_CHECKPOINT_PASSIVE, outputting pnLog and pnChkpt each time, with the results looking something like: 298 298 263 263 483 483 943 943 11711171 16531653 19471947 212 212 436 436 10111011 12771277 16111611 20352035 24082408 27032703 31283128 548 548 858 858 13381338 18391839 386 386 493 493 679 679 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite WPF application (designer view) toolbox greyed out
I successfully built a .NET Forms application in VS2010 using the toolbox to add a SqlLiteConnection etc. by dragging the connection symbol from the toolbox into designer view of the corresponding source file. This gave me all required class variables and what was needed to program the connection and do SQL queries. Now I would like to do a WPF application. The toolbox also appears but I don't see any SqliteConnection under WPF and the one I see in the SQlite treeview in the toolbox is greyed out. Any clues why this doesn't work for WPF apps? -- Christoph ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
