SQLite PRAGMA

The SQLite PRAGMA command is a special command used to control various environment variables and status flags in the SQLite environment. PRAGMA values can be read and can also be set as needed.

Syntax

To query the current PRAGMA value, simply provide the name of the compilation directive.

PRAGMA pragma_name;

To set a new value for PRAGMA, use the following syntax.

PRAGMA pragma_name = value;

The mode can be a name or an equivalent integer, but the returned value is always an integer.

auto_vacuum PRAGMA

auto_vacuum PRAGMA get or set the automatic vacuum mode. The following is a simple syntax.

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

Below wheremodeCan-

cache_size Pragma

cache_sizeThe maximum size of the page cache that can be obtained or temporarily set in memory. The following is a simple syntax.

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

pagesThe value represents the number of pages in the cache. The default size of the built-in page cache is2, with a minimum size of10pages.

case_sensitive_like Pragma

case_sensitive_like Pragma controls the case sensitivity of built-in LIKE expressions. By default, this pragma is false, meaning that the built-in LIKE operator ignores letter case. The following is a simple syntax.

PRAGMA case_sensitive_like = [true|false];

The current state of this compilation directive cannot be queried.

count_changes Pragma

count_changes Pragma gets or sets the return value of data manipulation statements (such as INSERT, UPDATE, and DELETE). The following is a simple syntax.

PRAGMA count_changes;
PRAGMA count_changes = [true|false];

By default, this compilation directive is set to false, and these statements return no content. If set to true, each mentioned statement will return a single-column, single-row table consisting of a single integer value indicating the number of rows affected by the operation.

database_list Pragma

database_listThis utility is used to list all attached databases. The following is a simple syntax.

PRAGMA database_list;

This utility returns a three-column table with one row for each open or connected database, giving the database sequence number, its name, and the associated file.

encoding Pragma

encoding Pragma controls how strings are encoded and stored in the database files. The following is a simple syntax.

PRAGMA encoding;
PRAGMA encoding = format;

The format value can beUTF-8, UTF-16ororUTF-16beone of them.

freelist_count Pragma

freelist_count Pragma returns an integer indicating how many database pages are currently marked as free and available. The following is a simple syntax.

PRAGMA [database.]freelist_count;

The format value can beUTF-8, UTF-16orUTF-16beone of them.

index_info Pragma

index_info Pragma returns information about the database indexes. The following is a simple syntax.

PRAGMA [database.]index_info(index_name);

The result set will include one row for each column contained in the index, giving the column order, the index of the column in the table, and the column name.

index_list Pragma

index_list Pragma lists all indexes associated with the table. The following is a simple syntax.

PRAGMA [database.]index_list(table_name);

The result set will include one row for each index, thus providing the index sequence, index name, and a flag indicating whether the index is unique.

journal_mode Pragma

journal_mode Pragma gets or sets the log mode that controls how log files are stored and processed. The following is a simple syntax.

PRAGMA journal_mode;
PRAGMA journal_mode = mode;
PRAGMA database.journal_mode;
PRAGMA database.journal_mode = mode;

The following table lists the five supported journal modes.

max_page_count PRAGMA

max_page_count PRAGMA gets or sets the maximum number of pages allowed for the database. The following is a simple syntax.

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

The default value is1,073,741,823That is, a gigabyte page, which means, if the default is1 KB page size, then the database can grow to1 TB.

page_count PRAGMA

page_count PRAGMA returns the current page number of the database. The following is a simple syntax-

PRAGMA [database.]page_count;

The size of the database file should be page_count * page_size.

page_size PRAGMA

page_size PRAGMA gets or sets the size of the database pages. The following is a simple syntax.

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

By default, the allowed size is512、1024、2048、4096、8192、16384and32768Bytes. The only way to change the page size of an existing database is to set the page size and then immediately perform a VACUUM on the database.

parser_trace PRAGMA

parser_trace PRAGMA indicates control over printing debugging status as it parses SQL commands. The following is a simple syntax.

PRAGMA parser_trace = [true|false];

By default, it is set to false, but when enabled by setting it to true, the SQL parser will print its status while parsing SQL commands.

recursive_triggers PRAGMA

recursive_triggers PRAGMA gets or sets the recursive trigger feature. If recursive triggers are not enabled, trigger operations will not trigger another trigger. The following is a simple syntax.

PRAGMA recursive_triggers;
PRAGMA recursive_triggers = [true|false];

schema_version PRAGMA

schema_version PRAGMA gets or sets the schema version value stored in the database header. The following is a simple syntax.

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

This is a32A signed integer value used to track schema changes. This value is incremented every time a command that changes the mode is executed (such as CREATE ... or DROP ...).

secure_delete PRAGMA

secure_delete PRAGMA is used to control how content is deleted from the database. The following is a simple syntax.

PRAGMA secure_delete;
PRAGMA secure_delete = [true|false];
PRAGMA database.secure_delete;
PRAGMA database.secure_delete = [true|false];

The default value of the secure delete flag is usually off, but it can be changed by using the SQLITE_SECURE_DELETE build option.

sql_trace PRAGMA

sql_trace PRAGMA is used to dump SQL trace results to the screen. The following is a simple syntax.

PRAGMA sql_trace;
PRAGMA sql_trace = [true|false];

You must compile SQLite with the SQLITE_DEBUG directive to include this compile-time directive.

synchronous PRAGMA

synchronous PRAGMA get or set the current disk synchronization mode, controlling how actively SQLite writes data to physical storage. The following is a simple syntax.

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

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

temp_store PRAGMA

temp_store PRAGMA get or set the storage mode used for temporary database files. The following is a simple syntax.

PRAGMA temp_store;
PRAGMA temp_store = mode;

SQLite supports the following storage modes.

temp_store_directory PRAGMA

temp_store_directory PRAGMA get or set the location used for temporary database files. The following is a simple syntax.

PRAGMA temp_store_directory;
PRAGMA temp_store_directory = 'directory_path';

user_version PRAGMA

user_versionCompile-time directives to get or set the user-defined version value stored in the database header. The following is a simple syntax.

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

This is a32This is a signed integer value that developers can set for version tracking purposes.

writable_schema PRAGMA

writable_schemaCompile to get or set the ability to modify system tables. The following is a simple syntax.

PRAGMA writable_schema;
PRAGMA writable_schema = [true|false];

If this compilation directive is set, tables starting with sqlite_ can be created and modified, including the sqlite_master table. Be careful when using compilation directives, as they can cause the database to be completely corrupted.