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

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.
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.
TODO: These comments and defines in the source code: 
** 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

Index