SQLite Code Factory online Help

Return to chapter overview

sql-statement ::=

PRAGMA name = value |

PRAGMA function(arg)

The PRAGMA command is used to modify the operation of the SQLite library. The pragma command is experimental and specific pragma statements may be removed or added in future releases of SQLite. Use this command with caution.

The current implementation supports the following pragmas:

•	PRAGMA cache_size;

PRAGMA cache_size = Number-of-pages;

Query or change the maximum number of database disk pages that SQLite will hold in memory at once. Each page uses about 1.5K of memory. The default cache size is 2000. If you are doing UPDATEs or DELETEs that change many rows of a database and you do not mind if SQLite 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 only endures 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 check the cache size permanently.

•	PRAGMA count_changes = ON;

PRAGMA count_changes = OFF;

When on, the COUNT_CHANGES pragma causes the callback function to be invoked once for each DELETE, INSERT, or UPDATE operation. The argument is the number of rows that were changed.

This pragma may be removed from future versions of SQLite. Consider using the sqlite_changes() API function instead.

•	PRAGMA default_cache_size;

PRAGMA default_cache_size = Number-of-pages;

Query or change the maximum number of database disk pages that SQLite will hold in memory at once. Each page uses about 1.5K of memory. This pragma works like the cache_size pragma with the addition 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.

•	PRAGMA default_synchronous;

PRAGMA default_synchronous = ON;

PRAGMA default_synchronous = OFF;

Query or change the setting of the "synchronous" flag in the database. When synchronous is on (the default), the SQLite database engine will pause at critical moments to make sure that data has actually be written to the disk surface. (In other words, it invokes the equivalent of the fsync() system call.) In synchronous mode, a SQLite database should be fully recoverable even if the operating system crashes or power is interrupted unexpectedly. The penalty for this assurance is that some database operations take longer because the engine has to wait on the (relatively slow) disk drive. The alternative is to turn synchronous off. With synchronous off, SQLite continues processing as soon as it has handed data off to the operating system. If the application running SQLite crashes, the data will be safe, but the database could (in theory) become corrupted if the operating system crashes or the computer suddenly loses power. On the other hand, some operations are as much as 50 or more times faster with synchronous off.

This pragma changes the synchronous mode persistently. Once changed, the mode stays as set even if the database is closed and reopened. The synchronous pragma does the same thing but only applies the setting to the current session.

•	PRAGMA empty_result_callbacks = ON;

PRAGMA empty_result_callbacks = OFF;

When on, the EMPTY_RESULT_CALLBACKS pragma causes the callback function to be invoked once for each query that has an empty result set. The third "argv" parameter to the callback is set to NULL because there is no data to report. But the second "argc" and fourth "columnNames" parameters are valid and can be used to determine the number and names of the columns that would have been in the result set had the set not been empty.

•	PRAGMA full_column_names = ON;

PRAGMA full_column_names = OFF;

The column names reported in a SQLite callback are normally just the name of the column itself, except for joins when "TABLE.COLUMN" is used. But when full_column_names is turned on, column names are always reported as "TABLE.COLUMN" even for simple queries.

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

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

•	PRAGMA parser_trace = ON;

PRAGMA parser_trace = OFF;

Turn tracing of the SQL parser inside of the SQLite library on and off. This is used for debugging. This only works if the library is compiled without the NDEBUG macro.

•	PRAGMA integrity_check;

The command does an integrity check of the entire database. It looks for out-of-order records, missing pages, and malformed records. If any problems are found, then a single string is returned which is a description of all problems. If everything is in order, "ok" is returned.

•	PRAGMA synchronous;

PRAGMA synchronous = ON;

PRAGMA synchronous = OFF;

Query or change the setting of the "synchronous" flag in the database for the duration of the current database connect. The synchronous flag reverts to its default value when the database is closed and reopened. For additional information on the synchronous flag, see the description of the default_synchronous pragma.

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

•	PRAGMA vdbe_trace = ON;

PRAGMA vdbe_trace = OFF;

Turn tracing of the virtual database engine inside of the SQLite library on and off. This is used for debugging.

No error message is generated if an unknown pragma is issued.

Unknown pragmas are ignored.

Return to chapter overview

SQLite Code Factory online Help

PRAGMA

Thanksgiving Sale