SQLite Guide (5)-Pragma command usage (complete)

The Pragma statement is an SQL extension of SQLite data and is unique to it. It is mainly used to modify the operations of SQLite database or internal data query. It uses the same form as select and insert statements, but there are several important differences:
1. Specific Pragma statements may be removed, and new Pragma statements may be added to new versions. Therefore, backward compatibility cannot be guaranteed.
2. Unknown Pragma commands do not contain error messages, which are ignored simply.
3. Some Pragma only works in the SQL compilation phase, rather than the execution phase. This means that if the C language, sqlite3_prepare (), sqlite3_step (), sqlite3_finalize () APIs are used, The Pragma command may only run in the call of prepare, instead of executing in the last two APIs. Alternatively, Pragma may run when sqlite3_step () is executed. The stage of execution depends on The Pragma and the SQLite release version.
4. The Pragma command is unique to SQLite and basically cannot be compatible with other databases.
The syntax format of The Pragma command is as follows:

It can contain no parameter or only one parameter. This parameter can be an equal sign value or enclosed in parentheses. The two have the same effect. In many cases, the parameter value is Boolean, and the value is (1, yes, true or on) or (0, no, false, off)
Keyword parameter, which can be enclosed in quotation marks, e.g. 'Yes' [false]. Some Pragma commands use strings as parameters. "0" and "no" indicate the same meaning. When querying a set value, in many cases, the returned value is a value rather than a keyword.
You can select the database name before The Pragma name. The database name is the name of the database associated with "Attach", or "Main", "Temp" to indicate the master database and the temporary database. If the optional database name is omitted, the default value is "Main. In some Pragma commands, the database name is meaningless and is ignored simply.
Let's take a look at some useful Pragma commands in SQLite:
Auto_vacuum
Automatic_index
Cache_size
Case_sensitive_like
Checkpoint_fullfsync
Collation_list
Compile_options
Count_changes instances
Database_list
Default_cache_size bytes
Empty_result_callbacks expired
Encoding
Foreign_key_list
Foreign_keys
Freelist_count
Full_column_names succeeded
Fullfsync
Ignore_check_constraints
Incremental_vacuum
Index_info
Index_list
Integrity_check
Journal_mode
Journal_size_limit
Legacy_file_format
Locking_mode
Max_page_count
Page_count
Page_size
Parser_trace 2
Quick_check
Read_uncommitted
Recursive_triggers
Reverse_unordered_selects
Schema_version
Secure_delete
Short_column_names tables
Synchronous
Table_info
Temp_store
Temp_store_directory metadata
User_version
Vdbe_listing 2
Vdbe_trace 2
Wal_autocheckpoint
Wal_checkpoint
Writable_schema
Here there are a few marked with 1 on the right, it seems that it has been obsoleted. 2 is used only for Debug. It is only useful when SQLite is built under the precompiled macro sqlite_debug.
Let's take a look at the specific usage of these commands:
1. Pragma auto_vacuum;
Pragma auto_vacuum = 0 or none | 1 or full | 2 or incremental;
Here, 0 and none indicate the same meaning.
The default value is 0, indicating that Auto Vacuum is disabled unless the sqlite_default_autovacuum macro is defined during compilation. When data is deleted, the database size does not change. Useless database file pages will be added to freelist for future reuse. In this case, you can use the VACUUM command to recreate the entire database to reclaim useless disk space.
When the value is 1, all freelist pages will be moved to the end of the file, and the files will be truncated each time the transaction is committed. Note that automatic vacuum only truncates the freelist page from the file, and does not perform operations such as fragment reorganization. That is to say, it is not complete without the VACUUM command. In fact, automatic vacuum will make more fragments.
Only when the database stores some additional information, it allows each database page to track its reference pages, and automatic vacuum can be used. It must be enabled without creating any tables. After a table is created, auto-vacuum cannot be enabled or disabled.
If the value is 2, it indicates that the incremental vacuum does not automatically vacuum every time a transaction is committed. An independent incremental_vacuum statement must be called to trigger auto-vacuum.
The database can be switched in vacuum mode. However, it cannot be switched from none to full or incremental. To switch, either the database is a brand new database (without any tables) or run the VACUUM command separately. To change the automatic vacuum mode, first execute the auto_vacuum statement to set the new mode, and then call vacuum to reorganize the database.
The auto_vacuum statement without parameters returns the current auto_vacuum mode value.
2. Pragma automatic_index;
Pragma automatic_index = Boolean;
Query, set, or clear automatic indexes. The default value is true (1 ).
3. Pragma cache_size;
Pragma cache_size = <Number of pages>;
Query or modify the maximum number of database pages in the opened database memory. The default value is 2000. This setting only changes the cache size in the current session. When the database is re-opened, the default value is restored. You can use default_cache_size to set the cache size for all sessions.
4. Pragma case_sensitive_like = Boolean;
The default behavior is to ignore the case sensitivity of ASCII characters. 'A' like 'A' will be true. When case_sensitive_like is disabled, the default like behavior will be used. When it is enabled, it is case sensitive.
5. Pragma checkpoint_fullfsync
Pragma checkpoint_fullfsync = Boolean;
Query or set the flag value of fullfsync. If this value is set, the f_fullfsync synchronization method is called during the checkpoint operation. The default value is off. Only Mac OS-X OS supports f_fullfsync. In addition, if the fullfsync value is set, the f_fullfsync synchronization method is used in all the sync operations, and the checkpoint_fullfsync mark is completely irrelevant.
6. Pragma collation_list;
Returns all sorting orders defined by the current database connection.
7. Pragma compile_options;
If you like this, return all the pre-compiled macros used for compiling SQLite. Of course, the prefix with "SQLite _" is ignored. In fact, it is returned by calling the sqlite3_compileoption_get () method.
8. Pragma count_changes;
Pragma count_changes = Boolean;
This command has been disabled. It is only used to maintain backward compatibility. If this value is not set, the insert, update, and delete statements do not return many rows of changed data.
In fact, sqlite3_changes () can obtain the number of changed rows.
9. Pragma database_list;
Returns the list of databases associated with the current database connection.
10. Pragma default_cache_size;
Pragma default_cache_size = number-of-pages;
Set the default cache sie, in the unit of pages. Unfortunately, this command will also be discarded.
11. Pragma empty_result_callbacks;
Pragma empty_result_callbacks = Boolean;
Only for backward compatibility. If this flag is cleared, the callback function provided by sqlite3_exec () (returns 0 or multiple rows of data) will not be triggered.
12. Pragma encoding;
Pragma encoding = "UTF-8 ";
Pragma encoding = "UTF-16 ";
Pragma encoding = "UTF-16le ";
Pragma encoding = "UTF-16be ";
The default value is UTF-8. If you use the attach command, you must use the same character set encoding as the main database. If the new database encoding is different from the main database encoding, it will fail.
13. Pragma foreign_key_list (Table-name );
Back to foreign key list
14. Pragma foreign_keys;
Pragma foreign_keys = Boolean;
Query settings or clear restrictions on foreign keys. The foreign key limit is valid only when begin or savepoint is not in pending state.
Changing this setting will affect the execution of all prepared SQL statements.
From 3.6.19, the default FK force limit is off. That is to say, the foreign key dependency is not forced.
15. Pragma freelist_count;
Returns the number of unused pages in the database file.
16. Pragma full_column_names;
Pragma full_column_names = Boolean;
Deprecated.
1. If there is an as clause, the column name will use the alias after.
2. if the result is only a common expression, rather than a column name of the source table, the text of the expression is used.
3. If the short_column_names switch is on, the source table column name is used without the table name prefix.
4. If both switches are set to off, 2nd rules are used.
5. The result column is a combination of source columns of the learning source table: Table. Column
17. Pragma fullfsync;
Pragma fullfsync = Boolean;
The default value is off, and only Mac OS supports f_fullfsync.
18. Pragma ignore_check_constraints = Boolean;
Whether to force the check constraint. The default value is off.
19. Pragma incremental_vacuum (N );
Page N is removed from freelist. Set this parameter. The same page number is truncated each time. This command must be in auto_vacuum = Incremental mode. If the number of pages in freelist is less than N, N is less than 1, or N is completely ignored, the whole freelist is cleared.
20. Pragma index_info (index-name );
Obtain the specified index.
21. Pragma index_list (Table-name );
Obtain the index information associated with the target table.
22. Pragma integrity_check;
Pragma integrity_check (integer );
When you perform the full check of the entire database, you can view records in wrong order, lost pages, and destroyed indexes.
23.
Pragma journal_mode;
Pragma database. journal_mode;
Pragma journal_mode = Delete | truncate | persist | memory | Wal | off
Pragma database. journal_mode = Delete | truncate | persist | memory | Wal | off
Used to set the journal_mode. delete of the database is the default action. In this mode, the journal file is deleted at the end of each transaction, which causes the transaction to be committed.
In truncate mode, the rollback journal is truncated to 0 instead of deleting it. In most cases, it is faster than the delete mode (because you do not need to delete files)
In persist mode, the rollback journal is not deleted at the end of each transaction, but is filled with 0 in the journal header, which will prevent other database connections from rollback. this mode is optimized on some platforms, especially when deleting or truncate a file is more expensive than overwriting the first file.
In memory mode, only rollback logs are stored in Ram, saving disk I/O, but the cost is the loss of stability and integrity. If the crash in the middle is lost, the database may be damaged.
Wal mode, that is, write-ahead log replaces rollback journal. This mode is persistent, and data is connected across multiple databases. It is still valid after the database is re-opened. This mode is only valid after 3.7.0.
(After experiment, it is found that it will generate two files:. SHM and. Wal)
Off mode, so there is no transaction support.
Note that there are only two modes for memory databases: memory or off. In addition, if an active transaction exists, the transaction mode cannot be changed.
24. Pragma journal_size_limit
Pragma journal_size_limit = N;
If "exclusive mode (Pragma locking_mode = exclusive) or (Pragma journal_mode = persist) is used during the connection, after the transaction is committed, the journal file will still be in the file system. This may increase the efficiency, but it also consumes space. A large transaction (such as vacuum) consumes a lot of disk space.
This setting limits the size of the Journal File. The default value is-1.
25. Pragma legacy_file_format;
Pragma legacy_file_format = Boolean;
If the value is on, the file format 3.0.0 is used. If the value is off, the latest file format is used. This may cause the old version of SQLite to fail to open the file.
When the sqlite3 database in the new file format is opened for the first time, the value is off, but the default value is on.
26. Pragma locking_mode;
Pragma locking_mode = normal | exclusive
The default value is normal. The database connection removes the filelock at the end of each read or write transaction. In exclusive mode, the connection will never release the filelock. In this mode, the shared lock is obtained and held when the first read operation is performed. The first write operation acquires and holds the exclusive lock.
Release the exclusive lock. Only when the database connection is closed or the lock mode is changed back to normal will the database files (read or write) be accessed again. It is not enough to simply set it to normal. The exclusive lock will be released only when it is accessed again next time.
Set the lock mode to exclusive for the following reasons:
1. The application needs to prevent other processes from accessing database files.
2. The number of system calls to the file system is reduced, leading to a slight performance reduction.
3. The wal log mode can be used in exclusive mode without the need for shared memory.
When a database name is specified, only the target database can take effect. For example:
Pragma main. locking_mode = exclusive; when the database name is not specified, it takes effect for all opened databases. The temp or memory database always uses the exclusive lock mode.
When you enter the wal log mode for the first time, the lock mode uses exclusive. After that, the lock mode cannot be changed until you exit the wal log mode. If the lock mode starts with normal, the first time you enter Wal, the lock mode can be changed and you do not need to exit the wal mode.
27. Pragma max_page_count;
Pragma max_page_count = N;
Query or set the maximum number of pages of database files
28. Pragma page_count;
Number of pages returned for database files
29. Pragma page_size;
Pragma page_size = bytes;
Query or set the page size of the database file, which must be a multiplication factor of 2 and between 512 and 65536.
A default size is given when you create a database. The page_size command immediately changes the page size (if the database is empty, it means that no table is created ). If the new size is specified, it is between running vacuum commands, and the database is not in Wal log mode, then the VACUUM command will adjust the page size to the new size (there should be no restrictions on table creation)
Sqlite_default_page_size the default value is 1024, and the maximum default page size is 8192. In Windows, sometimes the default page size may be greater than 1024, depending on getdiskfreespace () to obtain the actual set slice size.
30. Pragma parser_trace = Boolean;
Used in debug.
31. Pragma quick_check;
Pragma quick_check (integer)
It is similar to integrity_check, But it skips the validation of matching between the index content and the table content.
32. Pragma read_uncommitted;
Pragma read_uncommitted = Boolean;
Read uncommitted switch. The default transaction isolation level is serializable. Any process or thread can set the read-not-committed isolation level. However, serializable is still used, except for the connections that share the cache of a page and table mode.
33. Pragma recursive_triggers;
Pragma recursive_triggers = Boolean;
All statements are affected. Before 3.6.18, this switch was not supported. The default value is off.
34. Pragma reverse_unordered_selects;
Pragma reverse_unordered_selects = Boolean;
When this switch is enabled, select statements without order by will output results in reverse order.
35. Pragma schema_version;
Pragma schema_version = integer;
Pragma user_version;
Pragma user_version = integer;
The schema and user version are 32-bit integers (expressed in big endian mode) at 40 or 60 bytes in the database file header ).
The schema version is maintained internally by SQLite. This value is added when the schema changes. It is dangerous to explicitly change this value.
The user version can be used by applications.
36. Pragma secure_delete;
Pragma database. secure_delete;
Pragma secure_delete = Boolean
Pragma database. secure_delete = Boolean
When set to on, the deleted content will be overwritten with 0. The default value is determined by the macro sqlite_secure_delete. It is off.
37. Pragma short_column_names;
Pragma short_column_names = Boolean;
Deprecated.
38. Pragma synchronous;
Pragma synchronous = 0 | off | 1 | normal | 2 | full;
Query the sync flag value. The default value is full.
39. Pragma table_info (Table-name );
Returns basic table information.
40. Pragma temp_store;
Pragma temp_store = 0 | default | 1 | file | 2 | memory;
Query or set the parameter value of temp_store.
Sqlite_temp_store Pragma temp_store storage used fortemp tables
0 any file
1 0 file
1 1 file
1 2 memory
2 0 memory
2 1 file
2 2 memory
3 Any memory
40. Pragma temp_store_directory;
Pragma temp_store_directory = 'Directory-name ';
Set or change the directory location of temp_store. deprecated.
41. Pragma vdbe_listing = Boolean;
For debug
42. Pragma vdbe_trace = Boolean;
For debug
43. Pragma wal_autocheckpoint;
Pragma wal_autocheckpoint = N;
Set the interval of Wal automatic checkpoints (in page). The default value is 1000.
44. Pragma database. wal_checkpoint;
Pragma database. wal_checkpoint (passive );
Pragma database. wal_checkpoint (full );
Pragma database. wal_checkpoint (restart );
45. Pragma writable_schema = Boolean;
When it is set to on, the sqlite_master table can perform the cud operation. This is dangerous !!