The PRAGMA Command

The PRAGMA command provides an interface to modify the operation of the SQLite library and perform low-level operations to retrieve information about the connected database.

Fetching Database Information

This section describes the set of PRAGMA directives that allows you to find information about the currently attached databases.

 PRAGMA database_list; 

One row is returned for each open database containing the path to the database file and the name that the database was attached with. The first two rows returned will be the main database and the location in which temporary tables are stored.

A PRAGMA command can be executed from the sqlite program as well as through any of the language APIsafter all, the sqlite program is just a simple front end that passes typed commands to the C/C++ interface and uses a choice of callback function, selected using .mode, to output the rows to screen.

The following example shows the result of executing PRAGMA database_list through the sqlite program after a second database has been attached to the session:

 sqlite> ATTACH DATABASE db2 AS db2; sqlite> PRAGMA database_list; seq   name  file 0     main  /home/chris/sqlite/admin/db1 1     temp  /var/tmp/sqlite_6x5rZ2drAEtVpzj 2     db2   /home/chris/sqlite/admin/db2 

If executed from a language interface, the pseudo-table returned by this pragma contains columns named seq, name, and file.

To find information about a database table, use the table_info pragma with the following syntax:

 PRAGMA table_info(table-name); 

The table-name argument is required and can refer only to a table in the main database, not one attached using ATTACH DATABASE. One row is returned for each column in that table, containing the columns shown in Table 10.1.

Table 10.1. Columns in the Pseudo-Table Returned by PRAGMA table_info

cid	An integer column ID, beginning at zero, that shows the order in which columns appear in the table.
name	The name of the column. The capitalization used in the CREATE TABLE statement is retained.
type	The data type of the column, taken verbatim from the CREATE TABLE statement.
notnull	If the column was declared as NOT NULL, this column will contain a non-zero value; otherwise, it will be zero.
dflt_value	Contains the DEFAULT value for the column if one has been specified, otherwise NULL.
pk	Will be 1 for the column(s) making up the PRIMARY KEY on the table and 0 for all other columns.

To find information about the indexes on a table, use the index_list and index_info pragmas as follows:

 PRAGMA index_list(table-name); PRAGMA index_info(index-name); 

PRAGMA index_list causes one row to be returned for each index on the given table-name with the columns shown in Table 10.2.

Table 10.2. Columns in the Pseudo-Table Returned by PRAGMA index_list

seq	An integer beginning at zero that indicates the order in which the indexes were added to the table.
name	The name of the index. This is the identifier given in a CREATE INDEX statement, or the assigned name if the index was created with a PRIMARY KEY or UNIQUE constraint in the CREATE TABLE statement.
unique	Will be 1 if the index was declared as UNIQUE; otherwise, it will be 0.

When you know the name of an index, PRAGMA index_info can be used to find out which column or columns make up that index. A single column index will return a single row, whereas a clustered index will return one row for each indexed column, in the relevant order. The columns returned are shown in Table 10.3.

Table 10.3. Columns in the Pseudo-Table Returned by PRAGMA index_info

seqno	An integer beginning at zero that indicates the sequence of the columns in the index
cid	The cid number of the indexed column, from table_info
name	The name of the indexed column

When an index is created implicitly by the use of a UNIQUE or PRIMARY KEY constraint in the CREATE TABLE statement, the index will take an automatically assigned name of the form (table-name autoindex num). The following example shows these pragmas being run for a table with both explicit and implicit index names:

 sqlite> CREATE TABLE mytable (    ...>   col1 INTEGER NOT NULL,    ...>   col2 CHAR NOT NULL,    ...>   col3 INTEGER UNIQUE,    ...>   col4 CHAR,    ...>   PRIMARY KEY (col1, col2)    ...> ); sqlite> CREATE INDEX col4_idx ON mytable(col4); sqlite> PRAGMA index_list(mytable); seq   name                            unique ----  ------------------------------  ------ 0     col4_idx                        0 1     (mytable autoindex 2)           1 2     (mytable autoindex 1)           1 sqlite> PRAGMA index_info('(mytable autoindex 2)'); seqno  cid   name -----  ----  ---------- 0      0     col1 1      1     col2 

Note

If the table-name or index-name argument is not found in the current database, no error is returned. The result of the PRAGMA is simply silent.

Altering Database Parameters

This section describes the set of PRAGMA directives that enables you to adjust certain database parameters for the purposes of performance tuning at the overall database level. The first parameter that can be adjusted is cache_size.

 PRAGMA cache_size; 

This instruction will return the maximum number of database disk pages that will be held in memory at once. The default value of 2000 pages equates to around 3000KB of memoryeach page taking up approximately 1.5KB.

The same PRAGMA can be used to set a new cache size by assigning a value as follows:

 PRAGMA cache_size = num-pages; 

Increasing the cache size can improve performance if the SQL operations you are executing would use a larger amount of memory than the size of the cache. For instance, an UPDATE operation on a large number of rows would benefit from a larger cache size. Keeping a limit on the cache size ensures that SQLite's memory usage remains under your control.

The cache_size can be altered dynamically, so if one query in your application in particular can benefit from a larger number of pages being cached, you can increase the size for that query and drop it back down afterwards.

The temp_store pragma allows the location used for the temporary database to be queried or changed.

 PRAGMA temp_store; PRAGMA temp_store = value; 

The value of temp_store can be one of the constants DEFAULT, MEMORY, or FILE, which have the values 0, 1, and 2 respectively. The values indicate whether SQLite should store temporary database objects in memory or to disk. The DEFAULT value is set at compile time and unless you have changed it, it will be FILE.

Using in-memory databases for temporary tables can produce significant performance savings if your application can afford the additional memory usage.

The synchronous pragma allows the synchronous flag for the current database session to be queried or changed.

 PRAGMA synchronous; PRAGMA synchronous = value; 

The value can be OFF, NORMAL, or FULL, which have the values 0, 1, and 2 respectively.

In synchronous mode FULL or NORMAL, SQLite will pause periodically to make sure data has been written to the disk before carrying on with the next operation. FULL mode is very safe and ensures that after a system crash for any reason, even unrelated to SQLite, the database will not be corrupt.

Because FULL synchronous is very slow, NORMAL mode is used more often. SQLite still pauses to check at critical moments, but there is a small chance that an operating-system crash or power failure at the wrong time could cause corruption of the database file.

When synchronous is OFF, SQLite does not pause to check that data has been written to disk. This mode is very fastsome operations have been clocked at 50 times faster than NORMAL modehowever, the chance of data loss is greater because it relies on the operating system to ensure that data has been written. Setting synchronous=OFF can give some major performance benefits, but you should give careful consideration to the potential consequences.

A good time to turn synchronous OFF is when populating a large database for the first time. In this situation, the risk of corruption is not critical because the population process could be restarted from a known point in the event of a failure.

Note

NORMAL mode is used by default, even though there is a small chance of data loss, for performance reasons. In reality, the kind of system crash that would cause your database to become corrupt is likely to carry with it other, more serious problems such as hardware failure, so NORMAL mode is considered acceptable for everyday use.

Altering Database Parameters Permanently

Changes made in the way shown in the preceding section will persist only until the current database session is closed. To change a database parameter permanently, the same PRAGMA directives can be called with a default_ prefix to query and set the value that is stored in the database file.

To query the current default cache size of the attached database, use the following instruction:

 PRAGMA default_cache_size; 

To increase the cache size for that database to 4000 pages permanently, the command would be

 PRAGMA default_cache_size = 4000; 

The same prefix can be applied to give default_temp_store and default_synchronous pragmas to query or set the permanent values of temp_store and synchronous respectively.

Altering Query Parameters

Though it can now be done using the sqlite_changes() function call, the traditional method of counting changes made to the database as a result of an INSERT, UPDATE, or DELETE operation was to use the count_changes PRAGMA. This PRAGMA is likely to be removed from future versions of SQLite in favor of sqlite_changes(), so it is included here only for historical value.

 PRAGMA count_changes = ON; PRAGMA count_changes = OFF; 

When count_changes is set to ON, a single row is returned whenever an operation is performed that modifies the database containing the number of affected rows. The following example shows this in action using the sqlite program:

 sqlite> PRAGMA count_changes = ON; sqlite> UPDATE contacts SET first_name = upper(first_name); 4 sqlite> DELETE FROM contacts WHERE first_name = 'CHRIS'; 1 

Unless you are working with legacy code that uses count_changes, there is a small performance gain to be had by leaving this pragma turned off.

Note

The examples in this chapter use ON or OFF as the Boolean argument where one is required. ON, trUE, and YES are equivalent to a value of 1, whereas OFF, FALSE, and NO are equivalent to zero.

The column names passed to the callback function in the C/C++ interface as columnNamesalso used in other language APIs to identify the name of the column returnedare usually just the names of the columns, with a table prefix used only where a join has taken place. Use the full_column_names pragma to force SQLite to report a column name as table.column, even when only one table has been queried.

 PRAGMA full_column_names = ON; 

The following output shows an example of this in the sqlite monitor programthe column headings now include the table name:

 sqlite> PRAGMA full_column_names = ON; sqlite> SELECT first_name, last_name FROM contacts; contacts.first_name  contacts.last_name -------------------  ------------------ CHRIS                NEWMAN PADDY                O'BRIEN TOM                  THOMAS BILL                 WILLIAMS JO                   JONES 

The columnNames parameter to the callback function will always contain the names of the columns selected in elements numbered from zero to argc1, but this array can be extended to also include the data types specified in the CREATE TABLE statement.

 PRAGMA show_datatypes = ON; 

Where no data type was specified in the CREATE TABLE statement, it is reported as NUMERIC. Expressions evaluated in a query are reported as either NUMERIC or TEXT depending on the expression itself.

The example in Listing 10.1 shows how the show_datatypes pragma can be used to view the data types of both database columns and evaluated expressions.

Listing 10.1. Using PRAGMA show_datatypes to Find the Data Types of Columns and Expressions

 #include <stdio.h> #include <sqlite.h> int callback(void *pArg, int argc, char **argv, char **columnNames) {   int i;   for (i=0; i<argc; i++) {     printf("%-20s %-8s %s\n",                 columnNames[i], columnNames[i+argc], argv[i]);   }   return(0); } int main() {   char *errmsg;   int ret;   sqlite *db = sqlite_open("db1", 0777, &errmsg);   if (db == 0)   {     fprintf(stderr, "Could not open database: %s\n", errmsg);     sqlite_freemem(errmsg);     return(1);   }   ret = sqlite_exec(db,           "SELECT id, first_name, id+2, upper(first_name) "           "FROM contacts LIMIT 1", callback, NULL, &errmsg);   if (ret != SQLITE_OK)   {     fprintf(stderr, "SQL error: %s\n", errmsg);   }   sqlite_close(db);   return(0); } 

The resulting output shows that the column types of the database columns are as they were declared in the CREATE TABLE statement, but the expressions revert to one of the internal data types, NUMERIC or TEXT.

 $ ./listing10.1 id                   INTEGER  1 first_name           CHAR     Chris id+2                 NUMERIC  3 upper(first_name)    TEXT     CHRIS 

Analyzing the Database

An integrity check can be performed on the entire database with the integrity_check pragma. It is simply issued as follows:

 PRAGMA integrity_check; 

The check searches for corrupt indexes, malformed records, and other such problems. Any issues are returned as a single string or if there are no problems, a single string ok is returned. The response you would hope to see is

 sqlite> PRAGMA integrity_check; ok 

The parser_trace pragma turns ON or OFF internal tracing of the SQLite parser stack and, when ON, returns one row for each step of the parser trace. To use this feature, SQLite must have been compiled without the NDEBUG compile time option.

The following example shows the first few lines of output from the parser trace for a simple SELECT statement. As you can see, it is not desirable to have this output turned on unless you are looking for a particular problem with the parser codethere were 108 lines of output in total, just for this simple query!

 sqlite> PRAGMA parser_trace=ON; sqlite> SELECT first_name, last_name FROM contacts; parser: Input SELECT parser: Reduce [explain ::=]. parser: Shift 3 parser: Stack: explain parser: Shift 73 parser: Stack: explain SELECT parser: Input ID parser: Reduce [distinct ::=]. parser: Shift 74 parser: Stack: explain SELECT distinct parser: Reduce [sclp ::=]. parser: Shift 289 parser: Stack: explain SELECT distinct sclp parser: Shift 64 parser: Stack: explain SELECT distinct sclp ID parser: Input COMMA parser: Reduce [expr ::= ID]. parser: Shift 290 [...] 

Similarly, tracing can be enabled at the Virtual Database Engine level using the vdbe_trace pragma. One row is returned for each VDBE opcode in the trace, which gives a result very similar to that obtained by the EXPLAIN command.

 sqlite> PRAGMA vdbe_trace=ON; sqlite> DELETE FROM contacts;    0 Transaction     0    0    1 VerifyCookie    0    3    2 Transaction     1    0    3 Clear           3    0    4 SetCounts       0    0    5 Commit          0    0    6 Halt            0    0 

We will examine the VDBE opcodes in more detail later in this chapter.