SQLite PRAGMA

SQLite PRAGMA

Grammar

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 pattern, which can be an integer with a name or equivalent, but the returned value will always be an integer.

auto_vacuum Pragma

auto_vacuum P ragma gets or sets auto-vacuum mode. The syntax is as follows:

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

Where mode can be any of the following:

PRAGMA value	describe
0 or None	Disable auto-vacuum.This is the default mode, meaning that the size of the database file is not reduced unless you manually use the Vacuum command.
1 or FULL	Enable Auto-Vacuum is fully automated.In this mode, the database file is allowed to be reduced from the data from the database.
2 or Incremental	Auto-Vacuum is enabled, but must be activated manually.In this mode, the reference data is maintained, and the free page is only in the free list.These pages can be used at any time incremental_vacuum pragma Overlay.

cache_size Pragma

cache_size P ragma can get or temporarily set the maximum size of the page cache in memory. The syntax is as follows:

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

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

case_sensitive_like Pragma

case_sensitive_like P ragma controls the case sensitivity of the built-in LIKE expression. B y default, the Pragma is false, which means that the built-in LIKE operator ignores the case of the letters. The syntax is as follows:

PRAGMA case_sensitive_like = [true|false];

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

count_changes Pragma

count_changes P ragma gets or sets the return values of data action statements, such as INSERT, UPDATE, and DELETE. The syntax is as follows:

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

By default, the Pragma is false, and these statements do not return anything. If set to true, each of the statements mentioned returns a single-row table consisting of a single integer value that represents the rows affected by the operation.

database_list Pragma

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

PRAGMA database_list;

The Pragma returns a single-row, three-column table that gives the serial number of the database, its name, and the associated files whenever the database is opened or attached.

encoding Pragma

encoding P ragma controls how strings are encoded and stored in database files. The syntax is as follows:

PRAGMA encoding;
PRAGMA encoding = format;

The format values can be UTF-8, UTF-16le, or UTF-16be.

freelist_count Pragma

freelist_count P ragma returns an integer that indicates the number of database pages that are currently marked as free and available. The syntax is as follows:

PRAGMA [database.]freelist_count;

The format values can be UTF-8, UTF-16le, or UTF-16be.

index_info Pragma

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

PRAGMA [database.]index_info( index_name );

The result set displays a row for each column contained in the column series given, the column index within the table, and the column name.

index_list Pragma

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

PRAGMA [database.]index_list( table_name );

The result set displays a row for each index that gives a column sequence, the index name, and the identity that indicates whether the index is unique.

journal_mode Pragma

journal_mode P ragma 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;

There are five log modes supported here:

PRAGMA value	describe
DELETE	Default mode.In this mode, the log file will be deleted at the end of the transaction.
TRUNCATE	The log file is staged as zero-byte length.
PERSIST	The log file is stayed in place, but the head is rewritten, indicating that the log is no longer effective.
MEMORY	The logging is kept in memory instead of disk.
OFF	Do not retain any logging.

max_page_count Pragma

max_page_count P ragma 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 is 1,073,741,823, which is a gigabit page, which is a megabyte that grows in the database if the default is 1 KB of page size.

page_count Pragma

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

PRAGMA [database.]page_count;

The size of the database file page_count the page_size.

page_size Pragma

page_size P ragma 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 dimensions are 512, 1024, 2048, 4096, 8192, 16384, and 32768 bytes. The only way to change the page size of an existing database is to set the page size and immediately vacuum the database.

parser_trace Pragma

parser_trace Pragma as it parses SQL commands to control the debug state of printing, the syntax is as follows:

PRAGMA parser_trace = [true|false];

By default, it is set to false, but is enabled when set to true, at which point the SQL Parser prints out its state as it resolves the SQL command.

recursive_triggers Pragma

recursive_triggers P ragma gets or sets the recursive trigger feature. I f recursive triggers are not enabled, one trigger action will not trigger another. The syntax is as follows:

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

schema_version Pragma

schema_version P ragma gets or sets the schema version values 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 that tracks changes in the schema. W henever a schema changes command execution (such as CREATE...) O r DROP... , the value increments.

secure_delete Pragma

secure_delete P ragma controls how content is removed 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 a security removal flag is usually turned off, but this can be SQLITE_SECURE_DELETE by using the build option.

sql_trace Pragma

sql_trace P ragma is used to dump SQL tracking results to the screen. The syntax is as follows:

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

SQLite must compile SQLITE_DEBUG Pragma to reference by using the directive.

synchronous Pragma

synchronous P ragma acquires or sets the current disk's synchronization mode, which controls how active SQLite writes data to physical storage. The syntax is as follows:

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

SQLite supports the following synchronization modes:

PRAGMA value	describe
0 or OFF	Do not synchronize.
1 or Normal	Synchronize after each sequence of the critical disk operation.
2 or full	Synchronize after each key disk operation.

temp_store Pragma

temp_store P ragma gets or sets the storage mode used by the temporary database file. The syntax is as follows:

PRAGMA temp_store;
PRAGMA temp_store = mode;

SQLite supports the following storage modes:

PRAGMA value	describe
0 or Default	The compile time is used by default.Usually File.
1 or File	Use file-based storage.
2 or Memory	Use memory-based storage.

temp_store_directory Pragma

temp_store_directory P ragma gets or sets the location for temporary database files. The syntax is as follows:

PRAGMA temp_store_directory;
PRAGMA temp_store_directory = 'directory_path';

user_version Pragma

user_version P ragma gets or sets user-defined version values stored at the header of the database. The syntax is as follows:

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

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

writable_schema Pragma

writable_schema P ragma gets or sets whether the system table can be modified. The syntax is as follows:

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

If the Pragma is set, the table starts with sqlite_ and can be created and modified, including sqlite_master table. Be careful when using Pragma because it can cause corruption of the entire database.