Search notes:

SQLite: pragmas

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.
The pragma statement has two primary purposes:

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

foreign_keys controls if foreign keys are enforced.
table_info('tab_name') displays column information of the table named tab_name.

cache_size

This pragma controls the memory usage of SQLite.
pragma cache_size = 1000000 allocates 1 MB for the DB cache.

case_sensitive_like

case_sensitive_like controls whether or not a like expression compares two strings case sensitively or case insensitively: see collate and case sensitiveness.

compile_options

pragma compile_options shows the values of the compile time options used when SQLite was compiled.

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
--
Github repository about-sqlite, path: /pragma/foreign_key_check.sql

journal_mode

The 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.
See also Perl - DBD::SQLite: write while reading.

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
--
Github repository about-sqlite, path: /pragma/schema_version.sql

synchronous

pragma synchronous = OFF prevents SQLite from doing fsync's when writing.
This helps improve the performance, however, the database is not transactionally safe anymore.

Index