SQLite PRAGMA

grammar

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

PRAGMA pragma_name;

To set a new value for the PRAGMA, the following syntax:

PRAGMA pragma_name = value;

Setting mode, it can be a name or an integer equivalent, but the return 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;

Wherein, mode can be any of the following:

cache_size Pragma

cache_size Pragma can get or set the maximum size of the temporary pages in memory cache.The syntax is as follows:

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

value represents the number ofpages in the page cache.Built default page cache size is 2,000 pages, the minimum size is 10.

case_sensitive_like Pragma

sensitivecase_sensitive_like Pragma built LIKE expression control sensitivity.By default, the Pragma is false, which means that the built-LIKE operator to ignore the case of letters. The syntax is as follows:

PRAGMA case_sensitive_like = [true | false];

There is no way to check the current status of the 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, the Pragma is false, the statement does not return anything. If set to true, each statement mentioned will return a single separate table consists of a single integer value, which represents the integer operation affected row.

database_list Pragma

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

PRAGMA database_list;

The Pragma will return a single row table with three columns, when turned on or attach the database, the database will give the serial number, its name and related files.

encoding Pragma

How to control theencoding Pragma string encoded and stored in the database file.The syntax is as follows:

PRAGMA encoding;
PRAGMA encoding = format;

Format value can be UTF-8, UTF-16le one or UTF-16be.

freelist_count Pragma

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

PRAGMA [database.] Freelist_count;

Format value can be UTF-8, UTF-16le one or UTF-16be.

index_info Pragma

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

PRAGMA [database.] Index_info (index_name);

The result set for each column contains a sequence in a given index, column index within a table, column name column displays the row.

index_list Pragma

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

PRAGMA [database.] Index_list (table_name);

The result set for each index column is given sequence index name, the index indicates whether or not a unique identifier display line.

journal_mode Pragma

journal_mode Pragma Gets or sets the log files are stored and how to handle the log mode.The syntax is as follows ::

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

Here log supports five modes:

max_page_count Pragma

max_page_count Pragma database Gets or sets the maximum number of pages allowed.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 page, that is, if the default page size of 1 KB, then the database grew up in a megabyte.

page_count Pragma

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

PRAGMA [database.] Page_count;

Database file size 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 allowable size 512,1024,2048,4096,8192,16384,32768 bytes. The only way to change the existing database page size is to set the page size, then immediately VACUUM the database.

parser_trace Pragma

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

PRAGMA parser_trace = [true | false];

By default, it is set to false, but is set to true to enable, then the SQL parser can parse SQL commands as it prints out its status.

recursive_triggers Pragma

recursive_triggers Pragma Gets or sets the recursive trigger function.If recursive trigger is not enabled, a 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 schema version value is 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 changes to the architecture. Whenever a schema change command (such as CREATE ... or DROP ...), this value is incremented.

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];

Default security delete flag is normally closed, but it can be constructed by SQLITE_SECURE_DELETE option to change.

sql_trace Pragma

sql_trace Pragma for the SQL dump to track results on the screen.The syntax is as follows:

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

SQLite must be compiled by SQLITE_DEBUG instruction to reference the Pragma.

synchronous Pragma

synchronous Pragma Gets or sets the current disk synchronous mode, the mode control positive SQLite how data is written to physical storage.The syntax is as follows:

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

SQLite supports the following synchronization modes:

temp_store Pragma

temp_store Pragma 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:

temp_store_directory Pragma

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

PRAGMA temp_store_directory;
PRAGMA temp_store_directory = 'directory_path';

user_version Pragma

versionuser_version Pragma Gets or sets the value stored in the user database header custom.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

Arewritable_schema Pragma Gets or sets the ability to modify system tables.The syntax is as follows:

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

If the Pragma, the table began to sqlite_, you can create and modify, including sqlite_master table. When we use the Pragma pay attention, because it may result in damage to the entire database.