1.27. SQLite PRAGMA

1.27.1. Grammar ¶

To query the current PRAGMA Value, you only need to provide the pragma Name:

PRAGMA pragma_name; 

To work for PRAGMA Set a new value with the following syntax:

PRAGMA pragma_name = value; 

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

1.27.2. Auto_vacuum Pragma ¶

auto_vacuum Pragma gets or sets auto-vacuum Mode. The syntax is as follows:

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

Among them mode It can be any of the following:

1.27.3. Cache_size Pragma ¶

cache_size Pragma 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; 

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

1.27.4. Case_sensitive_like Pragma ¶

case_sensitive_like Pragma controls the built-in LIKE Case sensitivity of the expression. By default, the Pragma For false Which means that the built-in LIKE Operator ignores the case of letters. The syntax is as follows:

PRAGMA case_sensitive_like = [true|false]; 

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

1.27.5. Count_changes Pragma ¶

count_changes Pragma gets or sets the return values 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, the Pragma is false, and these statements return nothing. If set to true, each mentioned statement returns a single-row, single-column table consisting of a single integer value that represents the row affected by the operation.

1.27.6. Database_list Pragma ¶

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

PRAGMA database_list; 

The Pragma will return a single-row, three-column table, and each time you open or attach a database, it will give the serial number in the database, its name, and related files.

1.27.7. Encoding Pragma ¶

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

PRAGMA encoding; PRAGMA encoding = format; 

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

1.27.8. Freelist_count Pragma ¶

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

PRAGMA [database.]freelist_count; 

1.27.9. 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 displays a row for each column contained in the given column sequence, the column index in the table, and the column name.

1.27.10. Index_list Pragma ¶

index_list Pragma 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 given column sequence index, index name, and identification indicating whether the index is unique.

1.27.11. 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 logging modes are supported here:

1.27.12. 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 gigabyte, which is a gigabyte page, that is, if the default page size is 1 KB, then one megabyte grows in the database.

1.27.13. Page_count Pragma ¶

page_count Pragma returns the number of 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.

1.27.14. 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 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 VACUUM the database immediately.

1.27.15. Parser_trace Pragma ¶

parser_trace Pragma parses the SQL command with it to control the debugging state of the print. 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 parses the SQL command with it to print out its state.

1.27.16. Recursive_triggers Pragma ¶

recursive_triggers Pragma gets or sets recursive trigger functionality. 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]; 

1.27.17. 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 that is used to track architectural changes. Whenever a schema change command is executed (such as CREATE… Or DROP…), this value is incremented.

1.27.18. 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 of the secure delete flag is usually turned off, but this can be done through the SQLITE_SECURE_DELETE Build options to change.

1.27.19. Sql_trace Pragma ¶

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

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

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

1.27.20. Synchronous Pragma ¶

synchronous Pragma gets or sets the synchronization mode of the current disk, 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:

1.27.21. Temp_store Pragma ¶

temp_store Pragma gets or sets the storage mode used by the tempdb file. The syntax is as follows:

PRAGMA temp_store; PRAGMA temp_store = mode; 

SQLite supports the following storage modes:

1.27.22. Temp_store_directory Pragma ¶

temp_store_directory Pragma gets or sets the location used for tempdb files. The syntax is as follows:

PRAGMA temp_store_directory; PRAGMA temp_store_directory = 'directory_path'; 

1.27.23. 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 that can be set by the developer for version tracking purposes.

1.27.24. 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 the Pragma , then the table is sqlite\_ To begin, you can create and modify, including sqlite_master Watch. Use the Pragma Be careful, as it can lead to corruption of the entire database.