The legacy_file_format pragma gets or sets the
legacy compatibility flag. This flag is used to determine what
file format will be used when creating new database files. If
the flag is set to true, SQLite will create new database files
in a “legacy” file format that is backwards compatible all the
way to SQLite v3.0.0. If the flag is not set, SQLite will create
new files in the most up-to-date format supported by that
library.
By default, this pragma is on, meaning databases will be created using the legacy format. Because the default is on, the vast majority of SQLite files out there are in the legacy file format.
This pragma cannot be used to determine the format of an existing file, nor can it be used to change an existing file. It only effects new files created with this database connection, and must be set before the database file is initialized. An existing legacy file can be promoted to the latest format by setting this pragma to off and manually vacuuming the file, but down-grades back to the legacy format are not supported.
The main difference between the original file format and the most current file format is support for descending indexes. Descending indexes cannot be used in conjunction with the legacy file format. The newer format also uses a more efficient on-disk representation for the integer values 0 and 1 (used for Boolean values) that saves one byte per value.
Please note that the use of this
pragma does not guarantee backwards
compatibility. If a database is created using the legacy file
format, but the schema (CREATE
TABLE, etc.) uses more modern SQL language
features, you won’t be able to open the database with an older
version of SQLite.
When the latest file format (format v4) was first introduced, this flag had a default value of false. This caused a great number of problems in environments that did incremental or sporadic upgrades. To avoid such problems, the default value was changed to true. It is conceivable that the default value for this pragma may change in the future. The v4 file format is backward compatible to SQLite version 3.3.0.