| Updated: April 19, 2023 |
Modify or query the QDB library
PRAGMA name [= value] | function( arg)
The pragmas that take an integer value also accept symbolic names. The strings on, true, and yes are equivalent to 1; the strings off, false, and no are equivalent to 0. They are all case-insensitive and do not require quotes. An unrecognized string is treated as 1 and does not generate an error. When the value returned, it is an integer.
PRAGMA auto_vacuum; PRAGMA auto_vacuum = 0 | 1;
Query or set the auto_vacuum pragma flag for the database.
Normally, when a transaction that deletes data is committed, the database file remains the same size. Unused file pages are marked as such and reused later, when data is inserted into the database. In this mode, you must use the VACUUM command or qdb_vacuum() to reclaim unused space.
When the auto-vacuum flag is set, the database file shrinks when a transaction that deletes data is committed (the VACUUM command or qdb_vacuum() call aren't needed in this mode). To support this functionality, the database stores extra information internally, resulting in slightly larger database files than would otherwise be generated.
You can set or unset the auto-vacuum flag only before any tables have been created in the database. No error message is returned if you attempt to modify the flag after tables have been created.
Auto-vacuum mode is off by default. Frequent vacuum operations can be costly on storage media with slow write-access times (e.g., NOR flash memory). When databases are stored on such media, you should consider using qdb_vacuum() or the VACUUM SQL command rather than auto-vacuum mode.
PRAGMA cache_size; PRAGMA cache_size = Number-of-pages;
Query or change the maximum number of database disk pages that QDB will hold in memory at once. Each page uses about 1.5 KB of memory. The default cache size is 2000 pages. If you are running UPDATE or DELETE operations that change many rows of a database and you do not mind if QDB uses more memory, you can increase the cache size for a possible speed improvement.
When you change the cache size using the cache_size pragma, the change endures only for the current session. The cache size reverts to its default value when the database is closed and reopened. To permanently change the cache size, use the default_cache_size pragma.
PRAGMA case_sensitive_like; PRAGMA case_sensitive_like = 0 | 1;
The default behavior of the LIKE operator is to ignore case for Latin1 characters. Hence, by default, 'a' LIKE 'A' is true. The case_sensitive_like pragma can be turned on to change this behavior. When this pragma is enabled, 'a' LIKE 'A' is false, but 'a' LIKE 'a' is still true.
PRAGMA count_changes; PRAGMA count_changes = 0 | 1;
Query or change the count_changes flag. Normally, when the count_changes flag is not set, INSERT, UPDATE, and DELETE statements return no data. When the flag is set, each of these commands returns a single row of data consisting of one integer value: the number of rows inserted, updated, or deleted by the command. The returned count doesn't include any insertions, updates, or deletions performed by triggers.
PRAGMA default_cache_size; PRAGMA default_cache_size = Number-of-pages;
Query or change the maximum number of database disk pages that QDB will hold in memory at once. Each page uses 1 KB on disk and about 1.5 KB in memory. This pragma works like the cache_size pragma except that it changes the cache size permanently. With the default_cache_size pragma, you can set the cache size once and that setting is then reused every time you reopen the database.
PRAGMA full_column_names; PRAGMA full_column_names = 0 | 1;
When the full_column_names flag is set, the columns are always given names in the first form, regardless of whether a join is performed. If both the short_column_names and full_column_names flags are set, then the behavior associated with the full_column_names flag is exhibited.
PRAGMA legacy_file_format; PRAGMA legacy_file_format = ON | OFF
This pragma sets or queries the value of the legacy_file_format flag. When this flag is set, new SQLite databases are created in a file format that is readable and writable by all versions of SQLite going back to 3.0.0. When the flag is unset, new databases are created in the latest file format, which might not be readable or writable by older versions of SQLite.
This flag affects only newly created databases. It has no effect on existing databases.
PRAGMA page_size; PRAGMA page_size = bytes;
Query or set the page size of the database. The page size may be set only if the database has not yet been created. The specified size must be a power of two greater than or equal to 512 and less than or equal to 8192.
PRAGMA short_column_names; PRAGMA short_column_names = 0 | 1;
When the short_column_names flag is set, such columns are always given names in the first form, regardless of whether a join is performed. If both the short_column_names and full_column_names flags are set, then the behavior associated with the full_column_names flag is exhibited.
PRAGMA synchronous; PRAGMA synchronous = FULL; (2) PRAGMA synchronous = NORMAL; (1) PRAGMA synchronous = OFF; (0)
Query or change the synchronous pragma setting. The first query form returns the setting as an integer.
When synchronous is FULL (2), the QDB database engine pauses at critical moments to make sure that data has actually been written to the disk before continuing. This ensures that if the operating system crashes or there is a power failure, the database will be uncorrupted after rebooting. The FULL setting is very safe but slow.
When synchronous is NORMAL (1), the database engine still pauses at the most critical moments, but less often than in FULL mode. There is a very small (though non-zero) chance that a power failure at just the wrong time could corrupt the database in NORMAL mode. But in practice, you're more likely to suffer a catastrophic disk failure or some other unrecoverable hardware fault.
When synchronous is OFF (0), QDB continues without pausing as soon as it has handed data off to the operating system. If the application running QDB crashes, the data will be safe but the database might become corrupted if the operating system crashes or the computer loses power before that data has been written to the disk. However, some operations are as much as 50 or more times faster with the OFF setting.
The default setting is FULL.
PRAGMA temp_store; PRAGMA temp_store = DEFAULT; (0) PRAGMA temp_store = FILE; (1) PRAGMA temp_store = MEMORY; (2)
Query or change the temp_store pragma setting. When temp_store is DEFAULT (0), the library's C preprocessor macro TEMP_STORE determines where temporary tables and indexes are stored.
When temp_store is FILE (1), temporary tables and indexes are stored in a file. The temp_store_directory pragma can be used to specify the directory containing this file. When the temp_store setting is changed, all existing temporary tables, indexes, triggers, and views are immediately deleted.
When temp_store is MEMORY (2), temporary tables and indexes are kept in memory.
| TEMP_STORE | PRAGMA temp_store | Storage |
|---|---|---|
| 0 | Any | File |
| 1 | 0 | File |
| 1 | 1 | File |
| 1 | 2 | Memory |
| 2 | 0 | Memory |
| 2 | 1 | File |
| 2 | 2 | Memory |
| 3 | Any | Memory |
PRAGMA foreign_key_list(table-name);
For each foreign key that references a column in table-name, invoke the callback function with information about that key. This function will be invoked once for each column in each foreign key.
PRAGMA index_info(index-name);
For each column referenced by index-name, invoke the callback function once with information about that column, including the column name and number.
PRAGMA index_list(table-name);
For each index in table-name, invoke the callback function once with information about that index. Arguments include the index name and a flag to indicate whether the index must be unique.
PRAGMA table_info(table-name);
For each column in table-name, invoke the callback function once with information about that column, including the column name, data type, whether it can be NULL, and its default value.
PRAGMA [database.]schema_version; PRAGMA [database.]schema_version = integer ; PRAGMA [database.]user_version; PRAGMA [database.]user_version = integer ;
The schema_version and user_version pragmas set or get the values of the schema version and user version of the database. Both pragma settings are 32-bit signed integers and are stored in the database header.
The schema-version setting is usually modified only internally by QDB, which increments it whenever the database schema is modified (by creating or dropping a table or index). The schema version is used each time an SQL query is executed, to ensure that the internal cache of the schema used when compiling the query matches the schema of the database against which the query is executed.
The user-version is not used internally by QDB—it may be used by applications for any purpose.
PRAGMA integrity_check; PRAGMA integrity_check(integer)
This command does an integrity check of the entire database. It looks for out-of-order records, missing pages, malformed records, and corrupt indexes. If any problems are found, then strings are returned (as multiple rows with a single column per row) that describe the problems. At most integer errors will be reported before the analysis quits. The default value for integer is 100. If no errors are found, a single row with the value 'ok' is returned.