Using SQLite

Name

legacy_file_format — Determine the default format for new database files

Common Usage

PRAGMA legacy_file_format;
PRAGMA legacy_file_format = switch;

Description

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 ...