SQLite - PRAGMA

SQLite PRAGMA command is a special command to be used to control various environmental variables and state flags within the SQLite environment. A PRAGMA value can be read and it can also be set based on the requirements.

Syntax

To query the current PRAGMA value, the syntax is given below:

PRAGMA pragma_name;

To set a new value for PRAGMA, the following syntax can be used:

PRAGMA pragma_name = value;

In many PRAGMAs, the value is a boolean. The boolean can be one of:

• 1 yes true on
• 0 no false off

Lets see most commonly used PRAGMAs.

auto_vacuum Pragma

The auto_vacuum pragma gets or sets the auto-vacuum mode. The syntax used for this Pragma is as follows:

PRAGMA [database.]auto_vacuum;
PRAGMA [database.]auto_vacuum = mode;

Where mode can be any of the following:

cache_size Pragma

The cache_size pragma can get or temporarily set the suggested maximum number of database disk pages that SQLite will hold in memory at once per open database file. The syntax used for this Pragma is as follows:

PRAGMA [database.]cache_size;
PRAGMA [database.]cache_size = pages;
PRAGMA [database.]cache_size = -kibibytes;

The default suggested cache size is -2000, which means the cache size is limited to 2048000 bytes of memory. The default suggested cache size can be altered using the SQLITE_DEFAULT_CACHE_SIZE compile-time options. The TEMP database has a default suggested cache size of 0 pages.

case_sensitive_like Pragma

The case_sensitive_like pragma controls the case-sensitivity of the built-in LIKE expression. By default, this pragma is false which means that the built-in LIKE operator ignores the letter case. The syntax used for this Pragma is as follows:

PRAGMA case_sensitive_like = boolean;

There is no way to query for the current state of this pragma.

count_changes Pragma

The count_changes pragma gets or sets the return value of data manipulation statements such as INSERT, UPDATE and DELETE. The syntax used for this Pragma is as follows:

PRAGMA count_changes;
PRAGMA count_changes = boolean;

By default, this pragma is false and these statements do not return anything. If set to true, each of the mentioned statement will return a one-column, one-row table consisting of a single integer value indicating impacted rows by the operation.

database_list Pragma

The database_list pragma will be used to list down all the databases attached. The syntax used for this Pragma is as follows:

PRAGMA database_list;

This pragma will return a three-column table with one row per open or attached database giving database sequence number, its name and the file associated.

encoding Pragma

The encoding pragma controls how strings are encoded and stored in a database file. The syntax used for this Pragma is as follows:

PRAGMA encoding;
PRAGMA encoding = format;

The format value can be one of the following: UTF-8, UTF-16, UTF-16le, or UTF-16be.

freelist_count Pragma

The freelist_count pragma returns the number of unused pages in the database file. The syntax used for this Pragma is as follows:

PRAGMA [database.]freelist_count;

foreign_keys Pragma

The foreign_keys pragma queries, sets, or clears the enforcement of foreign key constraints.

This pragma is a no-op within a transaction; foreign key constraint enforcement may only be enabled or disabled when there is no pending BEGIN or SAVEPOINT.

Changing the foreign_keys setting affects the execution of all statements prepared using the database connection, including those prepared before the setting was changed. The syntax used for this Pragma is as follows:

PRAGMA foreign_keys;
PRAGMA foreign_keys = boolean;

function_list Pragma

The function_list pragma returns a list of SQL functions known to the database connection. Each row of the result describes a single calling signature for a single SQL function. Some SQL functions will have multiple rows in the result set if they can (for example) be invoked with a varying number of arguments or can accept text in various encodings. The syntax used for this Pragma is as follows:

PRAGMA function_list;

index_info Pragma

The index_info pragma returns information about a database index. The syntax used for this Pragma is as follows:

PRAGMA [database.]index_info(index_name);

The result set will contain one row for each column contained in the index giving column sequence, column index with-in table and column name. Output columns from the index_info pragma are as follows:

• The rank of the column within the index. (0 means left-most.)
• The rank of the column within the table being indexed. A value of -1 means rowid and a value of -2 means that an expression is being used.
• The name of the column being indexed. This columns is NULL if the column is the rowid or an expression.

index_list Pragma

The index_list pragma returns one row for each index associated with the given table. The syntax used for this Pragma is as follows:

PRAGMA [database.]index_list(table_name);

Output columns from the index_list pragma are as follows:

• A sequence number assigned to each index for internal tracking purposes.
• The name of the index.
• "1" if the index is UNIQUE and "0" if not.
• "c" if the index was created by a CREATE INDEX statement, "u" if the index was created by a UNIQUE constraint, or "pk" if the index was created by a PRIMARY KEY constraint.
• "1" if the index is a partial index and "0" if not.

journal_mode Pragma

The journal_mode pragma gets or sets the journal mode which controls how the journal file is stored and processed. The syntax used for this Pragma is as follows:

PRAGMA [database.]journal_mode;
PRAGMA [database.]journal_mode = mode;

There are six supported journal modes as listed below:

max_page_count Pragma

The max_page_count pragma gets or sets the maximum allowed page count for a database. The syntax used for this Pragma is as follows:

PRAGMA [database.]max_page_count;
PRAGMA [database.]max_page_count = N;

Both forms of the pragma return the maximum page count. The second form attempts to modify the maximum page count. The maximum page count cannot be reduced below the current database size.

page_count Pragma

The page_count pragma returns the total number of pages in the database file. The syntax used for this Pragma is as follows:

PRAGMA [database.]page_count;

page_size Pragma

The page_size pragma gets or sets the size of the database pages. The syntax used for this Pragma is as follows:

PRAGMA [database.]page_size;
PRAGMA [database.]page_size = bytes;

The page size must be a power of two between 512 and 65536 inclusive. The only way to alter the page size on an existing database is to set the page size and then immediately VACUUM the database.

parser_trace Pragma

The parser_trace pragma controls printing the debugging state as it parses SQL commands. The syntax used for this Pragma is as follows:

PRAGMA parser_trace = boolean;

By default, it is set to false but when enabled by setting it to true, the SQL parser will print its state as it parses SQL commands.

pragma_list Pragma

The pragma_list pragma returns a list of PRAGMA commands known to the database connection. The syntax used for this Pragma is as follows:

PRAGMA pragma_list;

query_only Pragma

The query_only pragma prevents data changes on database files when enabled. When this pragma is enabled, any attempt to CREATE, DELETE, DROP, INSERT, or UPDATE will result in an SQLITE_READONLY error. However, the database is not truly read-only. You can still run a checkpoint or a COMMIT and the return value of the sqlite3_db_readonly() routine is not affected. The syntax used for this Pragma is as follows:

PRAGMA query_only;
PRAGMA query_only = boolean;

recursive_triggers Pragma

The recursive_triggers pragma gets or sets the recursive trigger functionality. If recursive triggers are not enabled, a trigger action will not fire another trigger. The syntax used for this Pragma is as follows:

PRAGMA recursive_triggers;
PRAGMA recursive_triggers = boolean;

schema_version Pragma

The schema_version pragma gets or sets the value of the schema-version integer at offset 40 in the database header. The syntax used for this Pragma is as follows:

PRAGMA [database.]schema_version;
PRAGMA [database.]schema_version = integer;

SQLite automatically increments the schema-version whenever the schema changes.

secure_delete Pragma

The secure_delete pragma is used to control how the content is deleted from the database. The syntax used for this Pragma is as follows:

PRAGMA [database.]secure_delete;
PRAGMA [database.]secure_delete = boolean|FAST;

The "fast" setting for secure_delete is an intermediate setting in between "on" and "off".

The default value for the secure delete flag is normally off, but this can be changed with the SQLITE_SECURE_DELETE build option.

shrink_memory Pragma

The shrink_memory Pragma causes the database connection on which it is invoked to free up as much memory as it can. The syntax used for this Pragma is as follows:

PRAGMA shrink_memory

synchronous Pragma

The synchronous pragma gets or sets the current disk synchronization mode, which controls how aggressively SQLite will write data all the way out to physical storage. The syntax used for this Pragma is as follows:

PRAGMA [database.]synchronous;
PRAGMA [database.]synchronous = mode;

SQLite supports the following synchronization modes as listed in the table.

table_info Pragma

The table_info pragma returns one row for each column in the named table. Columns in the result set include the column name, data type, whether or not the column can be NULL, and the default value for the column. The "pk" column in the result set is zero for columns that are not part of the primary key, and is the index of the column in the primary key for columns that are part of the primary key. The syntax used for this Pragma is as follows:

PRAGMA [database.]table_info(table-name);

The table named in the table_info pragma can also be a view.

table_list Pragma

The table_list pragma returns information about the tables and views in the schema, one table per row of output. The syntax used for this Pragma is as follows:

PRAGMA table_list;
PRAGMA [database.]table_list;
PRAGMA table_list(table-name);

The columns returned by the table_list pragma include the following:

• schema: the schema in which the table or view appears (for example "main" or "temp").
• name: the name of the table or view.
• type: the type of object - one of "table", "view", "shadow" (for shadow tables), or "virtual" for virtual tables.
• ncol: the number of columns in the table, including generated columns and hidden columns.
• wr: 1 if the table is a WITHOUT ROWID table or 0 if is not.
• strict: 1 if the table is a STRICT table or 0 if it is not.

The default behavior is to show all tables in all databases. If the database. name appears before the pragma, then only tables in that one schema are shown. If a table-name argument is supplied, then only information about that one table is returned.

temp_store Pragma

The temp_store pragma gets or sets the storage mode used by temporary database files. The syntax used for this Pragma is as follows:

PRAGMA temp_store;
PRAGMA temp_store = mode;

SQLite supports the following storage modes.

temp_store_directory Pragma

PRAGMA temp_store_directory;
PRAGMA temp_store_directory = 'directory_path';

user_version Pragma

The user_version pragma gets or sets the value of the user-version integer at offset 60 in the database header. The syntax used for this Pragma is as follows:

PRAGMA [database.]user_version;
PRAGMA [database.]user_version = integer;

The user-version is an integer that is available to applications to use however they want. SQLite makes no use of the user-version itself.

writable_schema Pragma

The writable_schema pragma gets or sets the ability to modify system tables. The syntax used for this Pragma is as follows:

PRAGMA writable_schema;
PRAGMA writable_schema = boolean;
PRAGMA writable_schema = RESET;

If this pragma is set, tables that start with sqlite_ can be created and modified, including the sqlite_master table. If the argument is "RESET" then schema writing is disabled (as with "PRAGMA writable_schema=OFF") and, in addition, the schema is reloaded. Be careful while using pragma because it can lead to complete database corruption.