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:
PRAGMApragma_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:
PRAGMAdatabase
.pragma_name
; PRAGMAdatabase
.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:
PRAGMAdatabase
.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()
.
18.191.157.186