pragma is an
SQLite statement that is specific to the SQLite SQL dialect, that is, it is not
standard SQL, but rather an extension of SQLite.
Select interface for pragmas
Pragmas that are used to query internal data return the data as a table. These pragma have corresponding interface that allows to query them with a
select statement.
The name of these interfaces is the pragma name prefixed with pragma_.
For example, the following two statements are roughly equivalent:
pragma table_info('tab_foo');
select * from pragma_table_info('tab_foo');
Misc pragmas
cache_size
This pragma controls the memory usage of
SQLite.
pragma cache_size = 1000000 allocates 1 MB for the DB cache.
foreign_key_check
create table tab_p (
id integer primary key
);
create table tab_c (
ref integer references tab_p
);
insert into tab_p values (42);
insert into tab_c values (10);
insert into tab_c values (42);
insert into tab_c values (99);
-- Check the entire database
pragma foreign_key_check;
--
-- tab_c|1|tab_p|0
-- tab_c|3|tab_p|0
--
-- Check one table only:
pragma foreign_key_check('tab_c');
--
-- tab_c|1|tab_p|0
-- tab_c|3|tab_p|0
--
index_list
The sqlite shell command .schema does not explicitely display the indexes created for unique columns. These indexes can be shown however with index_list.
create table t1 (id integer primary key, txt text not null , val double);
create table t2 (id integer primary key, txt text not null unique, val double);
The following statement returns an empty set:
pragma index_list('t1');
For t2, the index for the unique index is shown:
pragma index_list('t2');
--
-- 0|sqlite_autoindex_t2_1|1|u|0
See also the index_info pragma, which shows the columns of an index:
pragma index_info('sqlite_autoindex_t2_1');
--
-- 0|1|txt
journal_mode
journal_mode can be set to
WAL (Write-Ahead Log). In most cases, WAL provides more concurrency as readers to not block writers and a writer does not block readers.
More details can be found
here.
page_size, page_count
pragma page_size returns the size of a page in bytes (for example 4096) and page_count the number of pages in the database.
Thus, page_size multiplied by page_count is the total size of the database in bytes.
Compare with
select pageno, pgoffset, pgsize from dbstat.
schema_version
--
-- schema_version is increased when the schema changes.
-- The version number is used to invalidate prepared statements
--
create table t_01 (
a number
);
pragma schema_version;
--
-- 1
--
create table t_02 (
b number
);
pragma schema_version;
--
-- 2
--
insert into t_02 values (5);
pragma schema_version;
--
-- 2
--
synchronous
pragma synchronous = OFF prevents
SQLite from doing
fsync's when writing which helps improve the
performance, however, the database is not transactionally safe anymore.
This pragma cannot be changed within a transaction:
sqlite> begin transaction;
sqlite> pragma synchronous = off;
Parse error: Safety level may not be changed inside a transaction
** Note that (for historical reasons) the PAGER_SYNCHRONOUS_* macros differ
** from the SQLITE_DEFAULT_SYNCHRONOUS value by 1.
**
** PAGER_SYNCHRONOUS DEFAULT_SYNCHRONOUS
** OFF 1 0
** NORMAL 2 1
** FULL 3 2
** EXTRA 4 3
**
** The "PRAGMA synchronous" statement also uses the zero-based numbers.
** In other words, the zero-based numbers are used for all external interfaces
** and the one-based values are used internally.
*/
#ifndef SQLITE_DEFAULT_SYNCHRONOUS
# define SQLITE_DEFAULT_SYNCHRONOUS 2
#endif
#ifndef SQLITE_DEFAULT_WAL_SYNCHRONOUS
# define SQLITE_DEFAULT_WAL_SYNCHRONOUS SQLITE_DEFAULT_SYNCHRONOUS
#endif