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:
To set a new value for PRAGMA, the syntax is as follows:
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 = 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 = 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:
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 = [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:
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 = 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:
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:
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:
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 database.journal_mode;
PRAGMA database.journal_mode = mode;
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 = 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:
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 = 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:
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:
schema_version Pragma gets or sets the schema version value stored in the database header. The syntax is as follows:
secure_delete Pragma is used to control how content is deleted from the database. The syntax is as follows:
##PRAGMA 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 = [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 = 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:
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 = '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 = 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 = [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.
