SQLite PRAGMA

SQLite's PRAGMA command is a special command that can be used to control various environment variables and status flags within the SQLite environment. A PRAGMA value can be read and set as required.

Syntax

To query the current PRAGMA value, you only need to provide the name of the pragma:

PRAGMA pragma_name;

To set a new value for PRAGMA, the syntax is as follows:

PRAGMA pragma_name = value;

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

auto_vacuum Pragma

auto_vacuum Pragma Gets or sets the auto-vacuum mode. The syntax is as follows:

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

Among them, mode can Is any of the following:

Pragma Value	Description
0 or NONE	Disable Auto-vacuum. This is the default mode, which means that the database file size will not be reduced unless the VACUUM command is used manually.
1 or FULL	Enable Auto-vacuum, which is fully automatic. In this mode, the database file is allowed to shrink as data is removed from the database.
2 or INCREMENTAL	Enable Auto-vacuum, but must be activated manually. In this mode, citation data is maintained and free pages are only placed in the free list. These pages can be overwritten at any time using the incremental_vacuum pragma.

cache_size Pragma

cache_size Pragma gets or temporarily sets the maximum size of the page cache in memory. The syntax is as follows:

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

pages The value is expressed in The number of pages in the cache. The built-in page cache has a default size of 2,000 pages and a minimum size of 10 pages.

case_sensitive_like Pragma

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 letter case. The syntax is as follows:

PRAGMA case_sensitive_like = [true|false];

There is currently no way to query the current status of this Pragma.

count_changes Pragma

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

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

By default, this Pragma is false, and these statements do not return anything thing. If set to true, each mentioned statement will return a single-row, single-column table consisting of a single integer value representing the row affected by the operation.

database_list Pragma

database_list Pragma will be used to list all database connections. The syntax is as follows:

PRAGMA database_list;

This Pragma will return a table with a single row and three columns. Whenever the database is opened or attached, the sequence in the database will be given. number, its name and associated files.

encoding Pragma

encoding Pragma controls how strings are encoded and stored in the database file. The syntax is as follows:

PRAGMA encoding;
PRAGMA encoding = format;

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

freelist_count Pragma

freelist_count Pragma returns an integer representing the number of database pages currently marked as free and available. The syntax is as follows:

PRAGMA [database.]freelist_count;

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

index_info Pragma

index_info Pragma returns information about the database index. The syntax is as follows:

PRAGMA [database.]index_info(index_name);

The result set will be every column contained in the index and table of the given column sequence. The columns of index and column names show a row.

index_list Pragma

index_list Pragma lists all indexes associated with the table. The syntax is as follows:

PRAGMA [database.]index_list( table_name );

The result set will display one row for each index for a given column sequence, the index name, and an identifier 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 syntax is as follows::

##PRAGMA journal_mode;

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

Five log modes are supported here:

Pragma valueDescription##DELETETRUNCATEPERSISTMEMORYOFF

max_page_count Pragma

max_page_count Pragma Gets or sets the maximum number of pages allowed for the database. The syntax is as follows:

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

The default value is 1,073,741,823, which is a gigabit pages, which is one megabyte that grows in the database if the default page size is 1 KB.

page_count Pragma

page_count Pragma returns the number of web pages in the current database. The syntax is as follows:

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 page. The syntax is as follows:

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

By default, the allowed size is 512, 1024, 2048, 4096, 8192, 16384, 32768 bytes. The only way to change the page size of an existing database is to set the page size and then immediately VACUUM the database.

parser_trace Pragma

parser_trace Pragma controls the debugging status of printing as it parses SQL commands. The syntax is as follows:

PRAGMA parser_trace = [true|false];

By default, it is set to false, but when set to true, it is enabled, and the SQL parser will print out the SQL command as it parses it. state.

recursive_triggers Pragma

recursive_triggers Pragma Gets or sets the recursive trigger function. If recursive triggers are not enabled, one trigger action will not trigger another trigger. The syntax is as follows:

##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 syntax is as follows:

PRAGMA [database.]schema_version;

PRAGMA [database.]schema_version = number;

This is a 32-bit signed integer value, Used to track architectural changes. This value is incremented each time a schema change command 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 syntax is as follows:

##PRAGMA secure_delete;

PRAGMA secure_delete = [true|false];

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

The default value for the safe delete flag is usually off, but this can be changed via the SQLITE_SECURE_DELETE build option.

sql_trace Pragma

sql_trace Pragma is used to dump SQL trace results to the screen. The syntax is as follows:

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

SQLite must use the SQLITE_DEBUG directive to compile the Pragma to be referenced.

synchronous Pragma

synchronous Pragma Gets or sets the current disk's synchronization mode, which controls how aggressive SQLite writes data to physical storage. The syntax is as follows:

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

SQLite supports the following synchronization modes:


Default mode. In this mode, at the end of the transaction, the log file is deleted.
The log file is staged to zero byte length.
The log file is left in place, but the header is overwritten, indicating that the log is no longer valid.
Log records are kept in memory, not on disk.
No logging is maintained.

Pragma Value	Description
0 or OFF	No synchronization.
1 or NORMAL	Synchronize after each sequence of critical disk operations.
2 or FULL	Synchronize after each critical disk operation.

temp_store Pragma

temp_store Pragma Gets or sets the storage mode used by temporary database files. The syntax is as follows:

##PRAGMA temp_store;

PRAGMA temp_store = mode;

SQLite supports the following storage modes:

Pragma value Description0 or DEFAULTDefault to use the compile-time mode. Usually FILE. 1 or FILE uses file-based storage. 2 or MEMORYUse memory-based storage.

temp_store_directory Pragma

temp_store_directory Pragma Gets or sets the location used for temporary database files. The syntax is as follows:

PRAGMA temp_store_directory;
PRAGMA temp_store_directory = 'directory_path';

user_version Pragma

user_version Pragma Gets or sets the user-defined version value stored in the database header. The syntax is as follows:

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

This is a 32-bit signed integer value , can be set by the developer for version tracking purposes.

writable_schema Pragma

writable_schema Pragma Gets or sets whether the system table can be modified. The syntax is as follows:

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

If this Pragma is set, the table starts with sqlite_ and can be created and modifications, including the sqlite_master table. Be careful when using this Pragma as it can lead to corruption of the entire database.

SQLite constraints →

submit

php.cn