I recommend taking a look at http://www.sqlite.org/fileformat2.html and look at 1.2 The Database Header. Since everything's stored in the file, the permanent pragmas are going to be ones which change one of the values in there.
<Not complete lists> Permanent: page_size (change requires vacuum) auto_vacuum (change to or from incremental requires vacuum) journal_mode (only WAL vs non-WAL) legacy_file_format (think can only be changed at database creation) encoding (think can only be changed at database creation) user_version Connection lifespan only: (Also note this means any of these will not affect any other connections) foreign_keys (biggest one to remember I think) journal_mode (only if between the non-WAL types) synchronous automatic_index busy_timeout cache_size case_sensitive_like defer_foreign_keys ignore_check_constraints locking_mode max_page_count mmap_size recursive_triggers reverse_unordered_selects etc, etc... Please correct me if I messed up. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jens Alfke Sent: Wednesday, April 12, 2017 3:29 PM To: SQLite mailing list Subject: [sqlite] Which pragmas are persistent? Many of SQLite's pragma commands change database settings. It would be helpful if their documentation[1] stated which of these persist across closing/reopening the database, and which are scoped only to the open connection. For example, the docs say that that 'pragma journal_mode=WAL' is persistent. Presumably 'mmap_size' is not because it doesn't have any effect on file storage. But there are some I'm unsure about: auto_vacuum, page_size — Persistent? journal_size_limit, synchronous — Ephemeral? I'm asking because I'd like to know which pragmas I need to reissue every time the database connection is opened, and which I only need to issue when initializing a new database. —Jens [1]: http://www.sqlite.org/pragma.html _______________________________________________ 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
