YouTip

The SQLite **PRAGMA** command is a special command used within the SQLite environment to control various environmental variables and state flags. A PRAGMA value can be read and also set as needed. ## Syntax To query the current PRAGMA value, simply provide the pragma name: PRAGMA pragma_name; To set a new value for a PRAGMA, the syntax is: PRAGMA pragma_name = value; The setting mode can be a name or an equivalent integer, but the returned value will always be an integer. ## auto_vacuum Pragma The **auto_vacuum** Pragma gets or sets the auto-vacuum mode. The syntax is as follows: PRAGMA [database.]auto_vacuum; PRAGMA [database.]auto_vacuum = mode; Here, **mode** can be any of the following: | Pragma Value | Description | | --- | --- | | 0 or NONE | Disables Auto-vacuum. This is the default mode, meaning the database file size will not shrink unless the VACUUM command is manually used. | | 1 or FULL | Enables 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 | Enables Auto-vacuum, but it must be activated manually. In this mode, references to data are maintained, and free pages are only placed on the free list. These pages can be overwritten at any time using the **incremental_vacuum pragma**. | ## cache_size Pragma The **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; 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 The **case_sensitive_like** Pragma controls the case sensitivity of the built-in LIKE expression. By default, this Pragma is false, meaning the built-in LIKE operator ignores letter case. The syntax is as follows: PRAGMA case_sensitive_like = [true|false]; Currently, there is no way to query 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 is as follows: PRAGMA count_changes; PRAGMA count_changes = [true|false]; By default, this Pragma is false, and these statements do not return anything. If set to true, each mentioned statement will return a single-row, single-column table consisting of a single integer value representing the number of rows affected by the operation. ## database_list Pragma The **database_list** Pragma is used to list all database connections. The syntax is as follows: PRAGMA database_list; This Pragma will return a single-row, three-column table, providing the sequence number, name, and associated file of each database whenever it is opened or attached. ## encoding Pragma The **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 The **freelist_count** Pragma returns an integer representing the current number of database pages marked as free and available. The syntax is as follows: PRAGMA [database.]freelist_count; ## index_info Pragma The **index_info** Pragma returns information about a database index. The syntax is as follows: PRAGMA [database.]index_info( index_name ); The result set will display one row for each column contained in the given index, showing the column sequence within the table and the column name. ## index_list Pragma The **index_list** Pragma lists all indexes associated with a table. The syntax is as follows: PRAGMA .index_list( table_name ); The result set will display one row for each index, showing the index sequence, index name, and a flag indicating whether the index is unique. ## journal_mode Pragma The **journal_mode** Pragma gets or sets the journal mode that controls how the journal file is stored and handled. The syntax is as follows: PRAGMA journal_mode; PRAGMA journal_mode = mode; PRAGMA database.journal_mode; PRAGMA database.journal_mode = mode; Five journal modes are supported here: | Pragma Value | Description | | --- | --- | | DELETE | The default mode. In this mode, the journal file is deleted at the end of a transaction. | | TRUNCATE | The journal file is truncated to zero length. | | PERSIST | The journal file is left in place, but its header is overwritten to indicate that the journal is no longer valid. | | MEMORY | Journal records are kept in memory rather than on disk. | | OFF | No journal records are kept. | ## max_page_count Pragma The **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 gigabyte of pages. That is, if the default page size is 1 KB, then the database can grow to one megabyte. ## page_count Pragma The **page_count** Pragma returns the current number of pages in the 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 The **page_size** Pragma gets or sets the size of a database page. The syntax is as follows: PRAGMA [database.]page_size; PRAGMA [database.]page_size = bytes; By default, allowed sizes are 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 The **parser_trace** Pragma controls the printing of debug status 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 enables the SQL parser to print its status as it parses SQL commands. ## recursive_triggers Pragma The **recursive_triggers** Pragma gets or sets the recursive triggers feature. If recursive triggers are not enabled, a trigger action will not fire another trigger. The syntax is as follows: PRAGMA recursive_triggers; PRAGMA recursive_triggers = [true|false]; ## schema_version Pragma The **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 schema changes. This value is incremented whenever a schema-changing command is executed (such as CREATE... or DROP...). ## secure_delete Pragma The **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 secure delete flag is usually off, but this can be changed via the SQLITE_SECURE_DELETE build option. ## sql_trace Pragma The **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 be compiled with the SQLITE_DEBUG directive to reference this Pragma. ## synchronous Pragma The **synchronous** Pragma gets or sets the current disk synchronization mode, which controls how aggressively 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 | 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 The **temp_store** Pragma gets or sets the storage mode used for temporary database files. The syntax is as follows: PRAGMA temp_store; PRAGMA temp_store = mode; SQLite supports the following storage modes: | Pragma Value | Description | | --- | --- | | 0 or DEFAULT | Use the compile-time default mode. Usually FILE. | | 1 or FILE | Use file-based storage. | | 2 or MEMORY | Use memory-based storage. | ## temp_store_directory Pragma The **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 The **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 that can be set by developers for version tracking purposes. ## writable_schema Pragma The **writable_schema** Pragma gets or sets whether system tables can be modified. The syntax is as follows: PRAGMA writable_schema; PRAGMA writable_schema = [true|false]; If this Pragma is set, tables starting with sqlite_ can be created and modified, including the sqlite_master table. Use this Pragma with caution, as it may corrupt the entire database.