Re: [sqlite] the sqlite3 documentation would be pretty good if it wasn't tragic...

For your second point: it is a reference, not a tutorial. For gentler introduction, you might want to go to http://www.sqlitetutorial.net/ First and foremost, though, itis one of the best documentations I've worked with. Yes, it's wordy, but it's not verbose. It's precise and aims for formal corr

[sqlite] behavior with inconsistent pragma journal_mode

process: - create a new db and set pragma journal_mode=PERSIST - create a table - open the same db again, without setting journal mode - create another table Result: second create table statement returns SQLITE_IOERR (code 10) after notable delay Honestly, I'm not very proud of this use ca

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

Suggestion: Warning banner, and a .saveas command that copies the db to a file. (I haven't read the entire thread, sorry if this already came up.) -- View this message in context: http://sqlite.1065341.n5.nabble.com/Proposed-enhancement-to-the-sqlite3-exe-command-line-shell-tp73827p74071.html

Re: [sqlite] auto_vacuum default setting

auto_vacuum at full is slower: When you delete some data, SQLite would shrink the file size to the minimum. If, in the next transaction, you add new data, SQLite would again have to extend the file size by at least a page. This takes time. Without auto-vacuum=FULL, the Insert statement would reu

Re: [sqlite] Collation advice

> In Hungarian, yes, that's what happens. I shouldn't be surprised :) I liike up digraphs (http://en.wikipedia.org/wiki/Digraph_(orthography)#Digraphs_in_Unicode) and found at least some of them have unicode code points. But if you can't cover all, yeah, normalization won't help. (btw. Muen

Re: [sqlite] BLOB & Other Data Type

You can see that e.g. with a select statement: create table test ( Name TEXT, Age Int); INSERT INTO Test VALUES ('Klaus', 22); INSERT INTO Test VALUES ('Meier', '022'); SELECT * FROM Test WHERE Age=22; In this case, the comparison is made on integers, '022' converted to integer wi

Re: [sqlite] Collation advice

What Simon says extends to another case: if you change the visible behavior of that function, even if it's a necessary bug fix, you end up with broken indices. That's true for every collation sequence, though. If I interpret your code correctly, however, the user can switch the preferred langua

Re: [sqlite] How to "upgrade" a DB without write access to its file, or to the direction it's in?

Another option: Copy the meta-data to an in-memory-DB. As you "load all meta data into memory" anyway this seems a viable option. ATTACH ':memory:' AS mem INSERT INTO mem.Table SELECT * FROM Table (repeat for each table, you may want to create/recreate constraint and indices in the attach

Re: [sqlite] SQLITE_DEFAULT_AUTOVACUUM not preserved for in-memory-db

[edit] this is of course 3.*7*.12 and 3.*7*.17 -- View this message in context: http://sqlite.1065341.n5.nabble.com/SQLITE-DEFAULT-AUTOVACUUM-not-preserved-for-in-memory-db-tp70571p70572.html Sent from the SQLite mailing list archive at Nabble.com. __

[sqlite] SQLITE_DEFAULT_AUTOVACUUM not preserved for in-memory-db

When compiling the amalgamation with #define SQLITE_DEFAULT_AUTOVACUUM 2 and creating an in-memory-DB, pragma auto_vacuum will return 0 (confirmed on 3.1.12, 3.1.17) Doing this in a disk DB, or after changing it at runtime (pragma auto_vacuum=N; VACUUM;), auto_vacuum will return the

[sqlite] incremental_vacuum within or outside transaction?

Is there any rationale for (not) running incremental_vacuum in an transaction? Unlike a full VACUUM, it seems to work with or without. Purpose: We are running with auto_vacuum = INCREMENTAL, and when closing the file in our software, we do an on-demand cleanup like so: if (Query('pragma freelis

Re: [sqlite] Strategies for checking the version of a database?

> http://semver.org/ Yikes! A good example for how many words it takes to formalize somethign that "feels" obvious. -- View this message in context: http://sqlite.1065341.n5.nabble.com/Strategies-for-checking-the-version-of-a-database-tp64330p64419.html Sent from the SQLite mailing list arc

Re: [sqlite] Strategies for checking the version of a database?

I'm using SQLite as application data file format for our software product line, and being compatible in both directions is important for us (and honored by our users). I've only recently moved to SQLite, so the thoughts listed here haven't stood the test of time yet, but I'd assume they are helpful

Re: [sqlite] index has higher cache priority than data?

There is also the compile-time option SQLITE_DIRECT_OVERFLOW_READ (see here: http://www.sqlite.org/compile.html) that makes content in overflow pages bypass the SQlite page cache. In my understanding, that should help if the majority data consists of large BLOB / String cells. (It would be nice

Re: [sqlite] index has higher cache priority than data?

I have a little experience with a somewhat similar setup: a typical "real" file is 200MB...2GB. I am making a "snapshot" of the data structure (few hundred K) into an attached in-memory-database. I've seen that the timre required to create that snapshot depends largely on the size of the entire t

Re: [sqlite] Fw: create table question

> our Visual Studio project will not breakpoint in certain places For SQLite, this is usually caused by Visual Studio only supporting breakpoints in the first 64K lines. (VS doesn't even tell you the breakpoints don't work, they just don't get hit). -- View this message in context: http://sql

Re: [sqlite] When to call VACUUM - revisited

Hi Simon, I already read your previous replies, but to revisit my scenaro: - My OS is "sensitive to fragmentation" - We are running with auto-vacuum enabled, so the freelist_count is usually small (not a good indicator) but fragmentation supposedly gets worse -We use sqlite as application