Table of Contents for

Using SQLite

This appendix covers all of the PRAGMA commands recognized by SQLite. Pragmas are SQLite-specific statements used to control various environmental variables and state flags within the SQLite environment. They are typically used to activate or configure many of the optional or advanced features of the SQLite library.

Although some pragmas are read-only, most pragma commands can be used to query the current value or set a new value. To query the current value, just provide the name of the pragma:

PRAGMA pragma_name;

In most cases this will return a one-column, one-row result set with the current value. To set a new value, use a syntax like this:

PRAGMA pragma_name = value;

Although a few pragmas will return the updated value, in most cases the set syntax will not return anything. Any unrecognized or malformed pragma will silently return without error. Be very careful about spelling your pragma commands correctly, or SQLite will consider the pragma unrecognized—an application bug that can be extremely difficult to find and fix.

Nearly all pragmas fall into one of two categories. The first category includes pragmas that are associated with the database connection. These pragma values can modify and control how the SQLite engine, as a whole, interacts with all of the databases attached to the database connection.

The second category of pragmas operate on specific databases. These pragmas will typically allow different values for each database that has been opened or attached to a database connection. Most database-specific identifiers will only last the lifetime of the database connection. If the database is detached and reattached (or closed and reopened), the pragma will assume the default value. There are a few pragmas, however, that will cause a change that is recorded into the database file itself. These values will typically persist across database connections. In both cases, the default values can typically be altered with compile-time directives.

The syntax for database-specific pragmas is somewhat similar to database-specific identifiers used elsewhere in SQL, and uses the logical database name, followed by a period, followed by the pragma command:

PRAGMA database.pragma_name;
PRAGMA database.pragma_name = value;

Like identifiers, if a logical database name is not given, most pragmas will assume it to be main. This is the database that was used to open the initial database connection. There are a few exceptions to this syntax, so read the documentation carefully.

A number of pragma values are simple true/false Boolean values or on/off state values. In this appendix, these values are referred to as “switches.” To set a switch, several different values are recognized:

A switch pragma will almost always return a simple integer value of 0 or 1.

Generally, any value that consists of a numeric value or constant (such as TRUE) can be given without quotes. Known identifiers, such as an attached database name or a table name, can also be given without quotes—unless they contain reserved characters. In that case, they should be given in double quotes, similar to how you would use them in any other SQL command. Other text values, such as filesystem path names, should be placed in single quotes.

There are also a handful of read-only pragmas that return a full multicolumn, multirow result set. These results can be processed in code the same way a SELECT result is processed, using repeated calls to sqlite3_step() and sqlite3_column_xxx(). A few of these pragmas require additional parameters, which are put in parentheses, similar to a function call. For example, here is the syntax for the table_info pragma:

PRAGMA database.table_info( table_name );

For more specifics, see the individual pragma descriptions.

A number of the pragmas control database values that cannot be changed once the database has been initialized. For example, the page_size pragma must be set before the database structure is written to disk. This may seem like a bit of a chicken-and-egg problem, since you need to open or attach a database in order to configure it, but opening it creates the database.

Thankfully, the database file header is usually not initialized and actually written to disk until it is absolutely required. This delay in the initialization allows a blank database file to be created by opening or attaching a new file. As long as the relevant pragmas are all set before the database is used, everything works as expected. Generally, it is the first CREATE TABLE statement that triggers an initialization. The other case is attaching an additional database. The main database (the one opened to create the database connection) must be initialized before any other database is attached to the database connection. If the main database is uninitialized, executing an ATTACH command will first force an initialization of the main (and only the main) database.

One final caution when using pragma statements from the C API. Exactly how and when a pragma takes effect is very dependent on the specific pragma and how it is being used. In some cases, the pragma will cause a change when the pragma statement is prepared. In other cases, you must step through the statement. Exactly how and when the pragmas do their thing is dependent on the particular pragma, and has been known to change from one release of SQLite to another. As a result, it is suggested that you do not pre-prepare pragma statements, like you might do with other SQL statements. Rather, any pragma statement should be prepared, stepped, and finalized in a single pass, when you want the pragma to take effect. When using a static pragma statement, it would also be perfectly safe to use sqlite3_exec().