I think there're two questions going on here. (Though I'm probably adding on to the original question)
First is similar to a recent thread and is: When I open a connection, what's the best way to ensure I've connected to an existing file, and am not about to create a brand new one by trying to find out? (In SQLite and apart from external programming language file system commands anyway) The other one is: What do I need to explicitly set, and in what order when I'm making a new database? For me, for example, I use SQLite in Python, so while I know what's on my own computer, when I hand off the scripts to the world at large who knows what version or compilation options their Python installations' sqlite3.dll has in it. So of page_size, auto_vacuum, legacy_file_format, encoding, journal_mode, etc. what pragmas do I need to set and in what order to make sure it creates what I want? For example as stated, if you do journal_mode = wal before you set page_size, then you may not notice that page_size didn't get set because the file had been created at the journal_mode change. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Wednesday, April 12, 2017 3:53 PM To: SQLite mailing list Subject: Re: [sqlite] Ambiguity in 'pragma page_size' docs On 4/12/17, Jens Alfke <j...@mooseyard.com> wrote: > The docs[1] say that: > >> The page_size pragma will only set in the page size if it is issued before >> any other SQL statements that cause I/O against the database file. > > It's not stated explicitly, but I believe the pragma has to be issued before > any other statement _ever_ causes I/O, i.e. it has to be the very first > persistent statement ever used on a new database file. (Except for the > situation regarding VACUUM described later on.) Is this true? I think the best thing to do is: "PRAGMA page_size=4096; VACUUM;" -- all in a single call to sqlite3_exec(). But only do this when you really need to change the page size as it is expensive. To a first approximation, you never need to change the page size. So why is this important to you? > >> SQL statements that cause I/O against the database file include "CREATE", >> "SELECT", "BEGIN IMMEDIATE", and "PRAGMA journal_mode=WAL". > > I'm guessing that "PRAGMA user_version" also causes I/O. > Yes > > The implication is > that, if you want to set the page_size, you have to do it every time the > database is opened, not just on first-time initialization; because checking > to see if you've initialized the database already would trigger I/O which > will make setting the page_size a no-op. (Unless you use the filesystem to > check that the db file is missing or empty before opening it, I suppose.) > The implication *should* be that you *never* change the page size. SQLite will pick a good page size for you automatically, which works in 99.99% of all cases. In the exceptional case where you do need to adjust the page_size manually, do it exactly once, as if you are running CREATE INDEX. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
