There are three pragma's which affect the "organization" of a newly created database. When a database is to be created these commands must be given BEFORE any command which opens or creates the database:
pragma auto_vacuum pragma encoding pragma page_size pragma data_store_directory The issuance (or preparation) of ANY OTHER COMMAND will cause a new blank database to be created using the values of auto_vacuum, encoding, and page_size in effect at the time that command is issued (prepared). The default for auto_vacuum is 0 or none. auto_vacuum can be changed freely between full (1) and incremental (2) if the database was created with a non-zero value of auto_vacuum (auto_vacuum was not none at the time of database creation). page_size can be changed for an existing non-wal mode database by changing the page_size and doing a vacuum. encoding cannot be changed and defaults to utf8. The reference to "create tables" in the database in the documentation is a euphamism for "database file created" since a database will always have at least one table in it -- the sqlite_master table -- once the database is created, even if no user tables are created. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On >Behalf Of Jens Alfke >Sent: Friday, 21 February, 2020 16:24 >To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >Subject: [sqlite] Setting auto_vacuum=2 doesn't work after setting >journal_mode=WAL > >I just discovered that none of our databases are auto-vacuuming, even >though we set auto_vacuum to 'incremental' when a database is created, >and periodically call 'pragma incremental_vacuum'. If I go into the CLI, >open a database and run "pragma auto_vacuum", it returns 0. > >After some experimentation I've discovered that (at least in 3.28) >`PRAGMA auto_vacuum=incremental` has to be done as the very first thing >after creating a database, even before `PRAGMA journal_mode=WAL`. I know >it's documented that auto_vacuum has to be enabled before creating any >tables, but the docs say nothing about ordering wrt other pragmas! > >To be precise, this is what we currently do after creating a new >database, which does not work: > >sqlite3_exec(db, "PRAGMA journal_mode=WAL; " > "PRAGMA auto_vacuum=incremental; " // ⟵ will >have no effect > "BEGIN; " > "CREATE TABLE …….. ; " > "PRAGMA user_version=302; " > "END;"); > >If I swap the first two statements, it does work: > >sqlite3_exec(db, "PRAGMA auto_vacuum=incremental; " // ⟵ will take >effect > "PRAGMA journal_mode=WAL; " > "BEGIN; " > "CREATE TABLE …….. ; " > "PRAGMA user_version=302; " > "END;"); > >Is this expected? If so, the docs for auto_vacuum should be updated. > >—Jens >_______________________________________________ >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