PRAGMA

Modify or query the library

Synopsis:


PRAGMA name [= value] | function( arg)

Description:

The PRAGMA command is a special command used to modify the operation of the QDB process or to query the library for internal (non-table) data. The PRAGMA command is issued using the same interface as other QDB commands (e.g., SELECT or INSERT), but is different in the following important respects:

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. These strings are case-insensitive, and do not require quotes. An unrecognized string will be treated as 1, and will not generate an error. When the value is returned, it is as an integer.

The available pragmas fall into the following basic categories:

  1. Pragmas used to modify the operation of the QDB process in some manner, or to query for the current mode of operation:
  2. Pragmas used to query the schema of the current database:
  3. Pragmas used to query or modify the databases' two version values, the schema-version and the user-version:
  4. Pragmas used to debug the library and verify that database files are not corrupted:

Auto-vacuum

PRAGMA auto_vacuum;
PRAGMA auto_vacuum = 0 | 1;

Query or set the auto-vacuum flag in the database.

Normally, when a transaction that deletes data from a database is committed, the database file remains the same size. Unused database file pages are marked as such and reused later on, when data is inserted into the database. In this mode the VACUUM command or qdb_vacuum() is used 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 is not useful in a database with the auto-vacuum flag set). To support this functionality, the database stores extra information internally, resulting in slightly larger database files than would otherwise be possible.

It is possible to modify the value of the auto-vacuum flag only before any tables have been created in the database. No error message is returned if an attempt to modify the auto-vacuum flag is made after one or more 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 (such as NOR flash memory); when databases are stored on such media, you should consider using qdb_vacuum (or the VACUUM SQL statement) rather than turning on auto-vacuum mode.

Cache size

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 doing UPDATEs or DELETEs 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 the default value when the database is closed and reopened. Use the default_cache_size pragma to permanently change the cache size.

Case sensitivity

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 case_sensitive_like is enabled, 'a' LIKE 'A' is false, but 'a' LIKE 'a' is still true.

Count changes

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 count-changes is set, each of these commands returns a single row of data consisting of one integer value: the number of rows inserted, modified, or deleted by the command. The returned change count doesn't include any insertions, modifications, or deletions performed by triggers.

Default cache size

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 with the additional feature that it changes the cache size persistently. With this pragma, you can set the cache size once and that setting is retained and reused every time you reopen the database.

Full column names

PRAGMA full_column_names;
PRAGMA full_column_names = 0 | 1;

Query or change the full-column-names flag. This flag affects the way QDB names columns of data returned by SELECT statements when the expression for the column is a table-column name or the wildcard *. Normally, such result columns are named table-name|alias column-name if the SELECT statement joins two or more tables together, or simply column-name if the SELECT statement queries a single table. When the full-column-names flag is set, such columns are always named table-name|alias column-name regardless of whether or not a join is performed.

If both the short-column-names and full-column-names are set, then the behavior associated with the full-column-names flag is exhibited.

Legacy file format

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 on, 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 off, new databases are created using 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 databases that already exist.

Page size

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 page size must be a power of two greater than or equal to 512 and less than or equal to 8192.

Short column names

PRAGMA short_column_names;
PRAGMA short_column_names = 0 | 1;

Query or change the short-column-names flag. This flag affects the way QDB names columns of data returned by SELECT statements when the expression for the column is a table-column name or the wildcard *. Normally, such result columns are named table-name|alias column-name if the SELECT statement joins two or more tables together, or simply column-name if the SELECT statement queries a single table. When the short-column-names flag is set, such columns are always named column-name regardless of whether or not a join is performed.

If both the short-column-names and full-column-names are set, then the behavior associated with the full-column-names flag is exhibited.

Synchronous

PRAGMA synchronous;
PRAGMA synchronous = FULL; (2)
PRAGMA synchronous = NORMAL; (1)
PRAGMA synchronous = OFF; (0)

Query or change the setting of the synchronous flag. The first query form will return the setting as an integer.

When synchronous is FULL (2), the QDB database engine will pause at critical moments to make sure that data has actually been written to the disk surface before continuing. This ensures that if the operating system crashes or if there is a power failure, the database will be uncorrupted after rebooting. FULL synchronous is very safe, but it is also slow.

When synchronous is NORMAL, the QDB database engine will still pause 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.

With synchronous 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 surface. However, some operations are as much as 50 or more times faster with synchronous OFF.

The default seting is FULL.

Temp store

PRAGMA temp_store;
PRAGMA temp_store = DEFAULT; (0)
PRAGMA temp_store = FILE; (1)
PRAGMA temp_store = MEMORY; (2)

Query or change the setting of the temp_store parameter. When temp_store is DEFAULT (0), the compile-time C preprocessor macro TEMP_STORE is used to determine where temporary tables and indexes are stored.

When temp_store is MEMORY (2), temporary tables and indexes are kept in memory.

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.

It is possible for the library compile-time C preprocessor symbol TEMP_STORE to override this pragma setting. The following table summarizes the interaction of the TEMP_STORE preprocessor macro and the temp_store pragma. It shows the storage used for TEMP tables and indexes:

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

Foreign key list

PRAGMA foreign_key_list(table-name);

For each foreign key that references a column in the argument table, invoke the callback function with information about that foreign key. The callback function will be invoked once for each column in each foreign key.

Index info

PRAGMA index_info(index-name);

For each column that the named index references, invoke the callback function once with information about that column, including the column name and the column number.

Index list

PRAGMA index_list(table-name);

For each index on the named table, invoke the callback function once with information about that index. Arguments include the index name and a flag to indicate whether or not the index must be unique.

Table info

PRAGMA table_info(table-name);

For each column in the named table, invoke the callback function once with information about that column, including the column name, data type, whether or not the column can be NULL, and the default value for the column.

Schema and user version

PRAGMA [database.]schema_version;
PRAGMA [database.]schema_version = integer ;
PRAGMA [database.]user_version;
PRAGMA [database.]user_version = integer ;

The pragmas schema_version and user_version are used to set or get the value of the schema-version and user-version, respectively. Both the schema-version and the user-version are 32-bit signed integers stored in the database header.

The schema-version is usually manipulated only internally by QDB. It is incremented by QDB whenever the database schema is modified (by creating or dropping a table or index). The schema version is used by QDB each time a query is executed to ensure that the internal cache of the schema used when compiling the SQL query matches the schema of the database against which the compiled query is actually executed.

CAUTION:
Subverting this version check mechanism by using PRAGMA schema_version to modify the schema version is potentially dangerous and may lead to program crashes or database corruption. Use with caution!

The user-version is not used internally by QDB. It may be used by applications for any purpose.

Integrity check

PRAGMA integrity_check;
PRAGMA integrity_check(integer)

The 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.