<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html><head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title>Pragma statements supported by SQLite</title>
<style type="text/css">
body {
margin: auto;
font-family: Verdana, sans-serif;
padding: 8px 1%;
}
a { color: #044a64 }
a:visited { color: #734559 }
.logo { position:absolute; margin:3px; }
.tagline {
float:right;
text-align:right;
font-style:italic;
width:300px;
margin:12px;
margin-top:58px;
}
.menubar {
clear: both;
border-radius: 8px;
background: #044a64;
padding: 0px;
margin: 0px;
cell-spacing: 0px;
}
.toolbar {
text-align: center;
line-height: 1.6em;
margin: 0;
padding: 0px 8px;
}
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
.toolbar a:visited { color: white; }
.toolbar a:hover { color: #044a64; background: white; }
.content { margin: 5%; }
.content dt { font-weight:bold; }
.content dd { margin-bottom: 25px; margin-left:20%; }
.content ul { padding:0px; padding-left: 15px; margin:0px; }
/* Things for "fancyformat" documents start here. */
.fancy img+p {font-style:italic}
.fancy .codeblock i { color: darkblue; }
.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
.fancy h2 { margin-left: 10px }
.fancy h3 { margin-left: 20px }
.fancy h4 { margin-left: 30px }
.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
.fancy #toc a { color: darkblue ; text-decoration: none }
.fancy .todo { color: #AA3333 ; font-style : italic }
.fancy .todo:before { content: 'TODO:' }
.fancy p.todo { border: solid #AA3333 1px; padding: 1ex }
.fancy img { display:block; }
.fancy :link:hover, .fancy :visited:hover { background: wheat }
.fancy p,.fancy ul,.fancy ol { margin: 1em 5ex }
.fancy li p { margin: 1em 0 }
/* End of "fancyformat" specific rules. */
</style>
</head>
<body>
<div><!-- container div to satisfy validator -->
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite Logo"
border="0"></a>
<div><!-- IE hack to prevent disappearing logo--></div>
<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>
<table width=100% class="menubar"><tr>
<td width=100%>
<div class="toolbar">
<a href="about.html">About</a>
<a href="sitemap.html">Sitemap</a>
<a href="docs.html">Documentation</a>
<a href="download.html">Download</a>
<a href="copyright.html">License</a>
<a href="news.html">News</a>
<a href="support.html">Support</a>
</div>
<script>
gMsg = "Search SQLite Docs..."
function entersearch() {
var q = document.getElementById("q");
if( q.value == gMsg ) { q.value = "" }
q.style.color = "black"
q.style.fontStyle = "normal"
}
function leavesearch() {
var q = document.getElementById("q");
if( q.value == "" ) {
q.value = gMsg
q.style.color = "#044a64"
q.style.fontStyle = "italic"
}
}
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
</script>
<td>
<div style="padding:0 1em 0px 0;white-space:nowrap">
<form name=f method="GET" action="https://www.sqlite.org/search">
<input id=q name=q type=text
onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">
<input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
</form>
</div>
</table>
<div class=startsearch></div>
<h1 align="center">PRAGMA Statements</h1>
<p>The PRAGMA statement is an SQL extension specific to SQLite and used to
modify the operation of the SQLite library or to query the SQLite library for
internal (non-table) data. The PRAGMA statement is issued using the same
interface as other SQLite commands (e.g. <a href="lang_select.html">SELECT</a>, <a href="lang_insert.html">INSERT</a>) but is
different in the following important respects:
</p>
<ul>
<li>Specific pragma statements may be removed and others added in future
releases of SQLite. There is no guarantee of backwards compatibility.
<li>No error messages are generated if an unknown pragma is issued.
Unknown pragmas are simply ignored. This means if there is a typo in
a pragma statement the library does not inform the user of the fact.
<li>Some pragmas take effect during the SQL compilation stage, not the
execution stage. This means if using the C-language <a href="c3ref/prepare.html">sqlite3_prepare()</a>,
<a href="c3ref/step.html">sqlite3_step()</a>, <a href="c3ref/finalize.html">sqlite3_finalize()</a> API (or similar in a wrapper
interface), the pragma may run during the <a href="c3ref/prepare.html">sqlite3_prepare()</a> call,
not during the <a href="c3ref/step.html">sqlite3_step()</a> call as normal SQL statements do.
Or the pragma might run during sqlite3_step() just like normal
SQL statements. Whether or not the pragma runs during sqlite3_prepare()
or sqlite3_step() depends on the pragma and on the specific release
of SQLite.
<li>The pragma command is specific to SQLite and is very unlikely
to be compatible with any other SQL database engine.
</ul>
<p>The C-language API for SQLite provides the <a href="c3ref/c_fcntl_busyhandler.html#sqlitefcntlpragma">SQLITE_FCNTL_PRAGMA</a>
<a href="c3ref/file_control.html">file control</a> which gives <a href="vfs.html">VFS</a> implementations the
opportunity to add new PRAGMA statements or to override the meaning of
built-in PRAGMA statements.</p>
<hr /><a name="syntax"></a>
<h2>PRAGMA command syntax</h2>
<p><b><a href="syntax/pragma-stmt.html">pragma-stmt:</a></b>
<button id='x1467' onclick='hideorshow("x1467","x1468")'>hide</button></p>
<blockquote id='x1468'>
<img alt="syntax diagram pragma-stmt" src="images/syntax/pragma-stmt.gif" />
<p><b><a href="syntax/pragma-value.html">pragma-value:</a></b>
<button id='x1469' onclick='hideorshow("x1469","x1470")'>hide</button></p>
<blockquote id='x1470'>
<img alt="syntax diagram pragma-value" src="images/syntax/pragma-value.gif" />
<p><b><a href="syntax/signed-number.html">signed-number:</a></b>
<button id='x1471' onclick='hideorshow("x1471","x1472")'>show</button></p>
<blockquote id='x1472' style='display:none;'>
<img alt="syntax diagram signed-number" src="images/syntax/signed-number.gif" />
</blockquote>
</blockquote>
</blockquote>
<p>
A pragma can take either zero or one argument. The argument is may be either
in parentheses or it may be separated from the pragma name by an equal sign.
The two syntaxes yield identical results.
In many pragmas, the argument is a boolean. The boolean can be one of:
</p>
<center>
<b>1 yes true on<br>0 no false off</b>
</center>
<p>Keyword arguments can optionally appear in quotes.
(Example: <tt>'yes' [FALSE]</tt>.) Some pragmas
takes a string literal as their argument. When pragma takes a keyword
argument, it will usually also take a numeric equivalent as well.
For example, "0" and "no" mean the same thing, as does "1" and "yes".
When querying the value of a setting, many pragmas return the number
rather than the keyword.</p>
<p>A pragma may have an optional database name before the pragma name.
The database name is the name of an <a href="lang_attach.html">ATTACH</a>-ed database or it can be
"main" or "temp" for the main and the TEMP databases. If the optional
database name is omitted, "main" is assumed. In some pragmas, the database
name is meaningless and is simply ignored.</p>
<hr /><a name="toc"></a>
<h2>List Of PRAGMAs</h2>
<table border=0 width="100%" cellpadding=10>
<tr><td valign="top" align="left"><ul>
<li><a href="#pragma_application_id">application_id</a>
<li><a href="#pragma_auto_vacuum">auto_vacuum</a>
<li><a href="#pragma_automatic_index">automatic_index</a>
<li><a href="#pragma_busy_timeout">busy_timeout</a>
<li><a href="#pragma_cache_size">cache_size</a>
<li><a href="#pragma_cache_spill">cache_spill</a>
<li><a href="#pragma_case_sensitive_like">case_sensitive_like</a>
<li><a href="#pragma_checkpoint_fullfsync">checkpoint_fullfsync</a>
<li><a href="#pragma_collation_list">collation_list</a>
<li><a href="#pragma_compile_options">compile_options</a>
<li><a href="#pragma_count_changes"><s>count_changes</s></a>¹
<li><a href="#pragma_data_store_directory"><s>data_store_directory</s></a>¹
<li><a href="#pragma_database_list">database_list</a>
<li><a href="#pragma_default_cache_size"><s>default_cache_size</s></a>¹
<li><a href="#pragma_defer_foreign_keys">defer_foreign_keys</a>
<li><a href="#pragma_empty_result_callbacks"><s>empty_result_callbacks</s></a>¹
<li><a href="#pragma_encoding">encoding</a>
<li><a href="#pragma_foreign_key_check">foreign_key_check</a>
<li><a href="#pragma_foreign_key_list">foreign_key_list</a>
<li><a href="#pragma_foreign_keys">foreign_keys</a>
<li><a href="#pragma_freelist_count">freelist_count</a>
</ul></td><td valign="top" align="left"><ul>
<li><a href="#pragma_full_column_names"><s>full_column_names</s></a>¹
<li><a href="#pragma_fullfsync">fullfsync</a>
<li><a href="#pragma_ignore_check_constraints">ignore_check_constraints</a>
<li><a href="#pragma_incremental_vacuum">incremental_vacuum</a>
<li><a href="#pragma_index_info">index_info</a>
<li><a href="#pragma_index_list">index_list</a>
<li><a href="#pragma_integrity_check">integrity_check</a>
<li><a href="#pragma_journal_mode">journal_mode</a>
<li><a href="#pragma_journal_size_limit">journal_size_limit</a>
<li><a href="#pragma_legacy_file_format">legacy_file_format</a>
<li><a href="#pragma_locking_mode">locking_mode</a>
<li><a href="#pragma_max_page_count">max_page_count</a>
<li><a href="#pragma_mmap_size">mmap_size</a>
<li><a href="#pragma_page_count">page_count</a>
<li><a href="#pragma_page_size">page_size</a>
<li><a href="#pragma_parser_trace"><i>parser_trace</i></a>²
<li><a href="#pragma_query_only">query_only</a>
<li><a href="#pragma_quick_check">quick_check</a>
<li><a href="#pragma_read_uncommitted">read_uncommitted</a>
<li><a href="#pragma_recursive_triggers">recursive_triggers</a>
<li><a href="#pragma_reverse_unordered_selects">reverse_unordered_selects</a>
</ul></td><td valign="top" align="left"><ul>
<li><a href="#pragma_schema_version">schema_version</a>
<li><a href="#pragma_secure_delete">secure_delete</a>
<li><a href="#pragma_short_column_names"><s>short_column_names</s></a>¹
<li><a href="#pragma_shrink_memory">shrink_memory</a>
<li><a href="#pragma_soft_heap_limit">soft_heap_limit</a>
<li><a href="#pragma_stats"><i>stats</i></a>³
<li><a href="#pragma_synchronous">synchronous</a>
<li><a href="#pragma_table_info">table_info</a>
<li><a href="#pragma_temp_store">temp_store</a>
<li><a href="#pragma_temp_store_directory"><s>temp_store_directory</s></a>¹
<li><a href="#pragma_threads">threads</a>
<li><a href="#pragma_schema_version">user_version</a>
<li><a href="#pragma_vdbe_addoptrace"><i>vdbe_addoptrace</i></a>²
<li><a href="#pragma_vdbe_debug"><i>vdbe_debug</i></a>²
<li><a href="#pragma_vdbe_listing"><i>vdbe_listing</i></a>²
<li><a href="#pragma_vdbe_trace"><i>vdbe_trace</i></a>²
<li><a href="#pragma_wal_autocheckpoint">wal_autocheckpoint</a>
<li><a href="#pragma_wal_checkpoint">wal_checkpoint</a>
<li><a href="#pragma_writable_schema">writable_schema</a>
</ul></td></tr></table>
<p>Notes:
<ol>
<li>Pragmas whose names are marked through in the list above
are deprecated that are maintained for historical compatibility only.
Do not use the deprecated pragmas in new applications.
Remove deprecated pragmas
from existing applications at your earliest opportunity.</blockquote>
<li>These pragmas are used for debugging SQLite and
are only available when SQLite is compiled using <a href="compile.html#debug">SQLITE_DEBUG</a>.
<li>These pragmas are used for testing SQLite and are not recommended
for use in application programs.</ol></p>
<a name="pragma_application_id"></a>
<hr>
<p><b>PRAGMA application_id;
<br>PRAGMA application_id = </b><i>integer </i><b>;</b>
<p> The application_id PRAGMA is used to query or set the 32-bit
unsigned big-endian "Application ID" integer located at offset
68 into the <a href="fileformat2.html#database_header">database header</a>. Applications that use SQLite as their
<a href="appfileformat.html">application file-format</a> should set the Application ID integer to
a unique integer so that utilities such as
<a href="http://www.darwinsys.com/file/">file(1)</a> can determine the specific
file type rather than just reporting "SQLite3 Database". A list of
assigned application IDs can be seen by consulting the
<a href="http://www.sqlite.org/src/artifact?ci=trunk&filename=magic.txt">magic.txt</a> file in the SQLite source repository.
<a name="pragma_auto_vacuum"></a>
<hr>
<p><b>PRAGMA auto_vacuum;<br>
PRAGMA auto_vacuum = </b>
<i>0 | NONE | 1 | FULL | 2 | INCREMENTAL</i><b>;</b></p>
<p>Query or set the auto-vacuum status in the database.</p>
<p>The default setting for auto-vacuum is 0 or "none",
unless the <a href="compile.html#default_autovacuum">SQLITE_DEFAULT_AUTOVACUUM</a> compile-time option is used.
The "none" setting means that auto-vacuum is disabled.
When auto-vacuum is disabled and data is deleted data from a database,
the database file remains the same size. Unused database file
pages are added to a "<a href="fileformat2.html#freelist">freelist</a>" and reused for subsequent inserts. So
no database file space is lost. However, the database file does not
shrink. In this mode the <a href="lang_vacuum.html">VACUUM</a>
command can be used to rebuild the entire database file and
thus reclaim unused disk space.</p>
<p>When the auto-vacuum mode is 1 or "full", the freelist pages are
moved to the end of the database file and the database file is truncated
to remove the freelist pages at every transaction commit.
Note, however, that auto-vacuum only truncates the freelist pages
from the file. Auto-vacuum does not defragment the database nor
repack individual database pages the way that the
<a href="lang_vacuum.html">VACUUM</a> command does. In fact, because
it moves pages around within the file, auto-vacuum can actually
make fragmentation worse.</p>
<p>Auto-vacuuming is only possible if the database stores some
additional information that allows each database page to be
traced backwards to its referrer. Therefore, auto-vacuuming must
be turned on before any tables are created. It is not possible
to enable or disable auto-vacuum after a table has been created.</p>
<p>When the value of auto-vacuum is 2 or "incremental" then the additional
information needed to do auto-vacuuming is stored in the database file
but auto-vacuuming does not occur automatically at each commit as it
does with auto_vacuum=full. In incremental mode, the separate
<a href="pragma.html#pragma_incremental_vacuum">incremental_vacuum</a> pragma must
be invoked to cause the auto-vacuum to occur.</p>
<p>The database connection can be changed between full and incremental
autovacuum mode at any time. However, changing from
"none" to "full" or "incremental" can only occur when the database
is new (no tables
have yet been created) or by running the <a href="lang_vacuum.html">VACUUM</a> command. To
change auto-vacuum modes, first use the auto_vacuum pragma to set
the new desired mode, then invoke the <a href="lang_vacuum.html">VACUUM</a> command to
reorganize the entire database file. To change from "full" or
"incremental" back to "none" always requires running <a href="lang_vacuum.html">VACUUM</a> even
on an empty database.
</p>
<p>When the auto_vacuum pragma is invoked with no arguments, it
returns the current auto_vacuum mode.</p>
<a name="pragma_automatic_index"></a>
<hr>
<p><b>PRAGMA automatic_index;
<br>PRAGMA automatic_index = </b><i>boolean</i><b>;</b></p>
<p>Query, set, or clear the <a href="optoverview.html#autoindex">automatic indexing</a> capability.
<p><a href="optoverview.html#autoindex">Automatic indexing</a> is enabled by default as of version 3.7.17,
but this might change in future releases of SQLite.
<a name="pragma_busy_timeout"></a>
<hr>
<p><b>PRAGMA busy_timeout;
<br>PRAGMA busy_timeout = </b><i>milliseconds</i><b>;</b></p>
<p>Query or change the setting of the
<a href="c3ref/busy_timeout.html">busy timeout</a>.
This pragma is an alternative to the <a href="c3ref/busy_timeout.html">sqlite3_busy_timeout()</a> C-language
interface which is made available as a pragma for use with language
bindings that do not provide direct access to <a href="c3ref/busy_timeout.html">sqlite3_busy_timeout()</a>.
<p>Each database connection can only have a single
<a href="c3ref/busy_handler.html">busy handler</a>. This PRAGMA sets the busy handler
for the process, possibly overwriting any previously set busy handler.
<a name="pragma_cache_size"></a>
<hr>
<p><b>PRAGMA cache_size;
<br>PRAGMA cache_size = </b><i>pages</i><b>;
<br>PRAGMA cache_size = -</b><i>kibibytes</i><b>;</b></p>
<p>Query or change the suggested maximum number of database disk pages
that SQLite will hold in memory at once per open database file. Whether
or not this suggestion is honored is at the discretion of the
<a href="c3ref/pcache_methods2.html">Application Defined Page Cache</a>.
The default page cache that is built into SQLite honors the request,
however alternative application-defined page cache implementations
may choose to interpret the suggested cache size in different ways
or to ignore it all together.
The default suggested cache size is 2000 pages.</p>
<p>If the argument N is positive then the suggested cache size is set
to N. If the argument N is negative, then the
number of cache pages is adjusted to use approximately N*1024 bytes
of memory.
<i>Backwards compatibility note:</i>
The behavior of cache_size with a negative N
was different in SQLite versions prior to 3.7.10. In
version 3.7.9 and earlier, the number of pages in the cache was set
to the absolute value of N.</p>
<p>When you change the cache size using the cache_size pragma, the
change only endures for the current session. The cache size reverts
to the default value when the database is closed and reopened.</p>
<a name="pragma_cache_spill"></a>
<hr>
<p><b>PRAGMA cache_spill;
<br>PRAGMA cache_spill=</b><i>boolean</i><b>;</b></p>
<p>The cache_spill pragma enables or disables the ability of the pager
to spill dirty cache pages to the database file in the middle of a
transaction. Cache_spill is enabled by default and most applications
should leave it that way as cache spilling is usually advantageous.
However, a cache spill has the side-effect of acquiring an
<a href="lockingv3.html#excl_lock">EXCLUSIVE lock</a> on the database file. Hence, some applications that
have large long-running transactions may want to disable cache spilling
in order to prevent the application from acquiring an exclusive lock
on the database until the moment that the transaction <a href="lang_transaction.html">COMMIT</a>s.
<a name="pragma_case_sensitive_like"></a>
<hr>
<p><b>PRAGMA case_sensitive_like = </b><i>boolean</i><b>;</b></p>
<p>The default behavior of the <a href="lang_expr.html#like">LIKE</a> operator is to ignore case
for ASCII characters. Hence, by default <b>'a' LIKE 'A'</b> is
true. The case_sensitive_like pragma installs a new application-defined
LIKE function that is either case sensitive or insensitive depending
on the value of the case_sensitive_like pragma.
When case_sensitive_like is disabled, the default LIKE behavior is
expressed. When case_sensitive_like is enabled, case becomes
significant. So, for example,
<b>'a' LIKE 'A'</b> is false but <b>'a' LIKE 'a'</b> is still true.</p>
<p>This pragma uses <a href="c3ref/create_function.html">sqlite3_create_function()</a> to overload the
LIKE and GLOB functions, which may override previous implementations
of LIKE and GLOB registered by the application.</p>
<a name="pragma_checkpoint_fullfsync"></a>
<hr>
<p><b>PRAGMA checkpoint_fullfsync
<br>PRAGMA checkpoint_fullfsync = </b><i>boolean</i><b>;</b></p>
<p>Query or change the fullfsync flag for <a href="wal.html#ckpt">checkpoint</a> operations.
If this flag is set, then the F_FULLFSYNC syncing method is used
during checkpoint operations on systems that support F_FULLFSYNC.
The default value of the checkpoint_fullfsync flag
is off. Only Mac OS-X supports F_FULLFSYNC.</p>
<p>If the <a href="pragma.html#pragma_fullfsync">fullfsync</a> flag is set, then the F_FULLFSYNC syncing
method is used for all sync operations and the checkpoint_fullfsync
setting is irrelevant.</p>
<a name="pragma_collation_list"></a>
<hr>
<p><b>PRAGMA collation_list;</b></p>
<p>Return a list of the collating sequences defined for the current
database connection.</p>
<a name="pragma_compile_options"></a>
<hr>
<p><b>PRAGMA compile_options;</b></p>
<p>This pragma returns the names of <a href="compile.html">compile-time options</a> used when
building SQLite, one option per row. The "SQLITE_" prefix is omitted
from the returned option names. See also the
<a href="c3ref/compileoption_get.html">sqlite3_compileoption_get()</a> C/C++ interface and the
<a href="lang_corefunc.html#sqlite_compileoption_get">sqlite_compileoption_get()</a> SQL functions.</p>
<a name="pragma_count_changes"></a>
<hr>
<p><b>PRAGMA count_changes;
<br>PRAGMA count_changes = </b>boolean</i><b>;</b></p>
<p>Query or change the count-changes flag. Normally, when the
count-changes flag is not set, <a href="lang_insert.html">INSERT</a>, <a href="lang_update.html">UPDATE</a> and <a href="lang_delete.html">DELETE</a> statements
return no data. When count-changes is set, each of these commands
returns a single row of data consisting of one integer value - the
number of rows inserted, modified or deleted by the command. The
returned change count does not include any insertions, modifications
or deletions performed by triggers, or any changes made automatically
by <a href="foreignkeys.html#fk_actions">foreign key actions</a>.</p>
<p>Another way to get the row change counts is to use the
<a href="c3ref/changes.html">sqlite3_changes()</a> or <a href="c3ref/total_changes.html">sqlite3_total_changes()</a> interfaces.
There is a subtle different, though. When an INSERT, UPDATE, or
DELETE is run against a view using an <a href="lang_createtrigger.html#instead_of_trigger">INSTEAD OF trigger</a>,
the count_changes pragma reports the number of rows in the view
that fired the trigger, whereas <a href="c3ref/changes.html">sqlite3_changes()</a> and
<a href="c3ref/total_changes.html">sqlite3_total_changes()</a> do not.
<p style='background-color: #ffd0d0;'>
<b>This pragma is deprecated</b> and exists
for backwards compatibility only. New applications
should avoid using this pragma. Older applications should discontinue
use of this pragma at the earliest opportunity. This pragma may be omitted
from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>.
</p>
<a name="pragma_data_store_directory"></a>
<hr>
<p><b>PRAGMA data_store_directory;
<br>PRAGMA data_store_directory = '</b><i>directory-name</i><b>';</b></p>
<p>Query or change the value of the <a href="c3ref/data_directory.html">sqlite3_data_directory</a> global
variable, which windows operating-system interface backends use to
determine where to store database files specified using a relative
pathname.</p>
<p>Changing the data_store_directory setting is <u>not</u> threadsafe.
Never change the data_store_directory setting if another thread
within the application is running any SQLite interface at the same time.
Doing so results in undefined behavior. Changing the data_store_directory
setting writes to the <a href="c3ref/data_directory.html">sqlite3_data_directory</a> global
variable and that global variable is not protected by a mutex.</p>
<p>This facility is provided for WinRT which does not have an OS
mechanism for reading or changing the current working directory.
The use of this pragma in any other context is discouraged and may
be disallowed in future releases.</p>
<p style='background-color: #ffd0d0;'>
<b>This pragma is deprecated</b> and exists
for backwards compatibility only. New applications
should avoid using this pragma. Older applications should discontinue
use of this pragma at the earliest opportunity. This pragma may be omitted
from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>.
</p>
<a name="pragma_database_list"></a>
<hr>
<p><b>PRAGMA database_list;</b></p>
<p>This pragma works like a query to return one row for each database
attached to the current database connection.
The second column is the "main" for the main database file, "temp"
for the database file used to store TEMP objects, or the name of the
ATTACHed database for other database files.
The third column is the name of the database file itself, or an empty
string if the database is not associated with a file.</p>
<a name="pragma_default_cache_size"></a>
<hr>
<b>PRAGMA default_cache_size;
<br>PRAGMA default_cache_size = </b><i>Number-of-pages</i><b>;</b></p>
<p>This pragma queries or sets the suggested maximum number of pages
of disk cache that will be allocated per open database file.
The difference between this pragma and <a href="pragma.html#pragma_cache_size">cache_size</a> is that the
value set here persists across database connections.
The value of the default cache size is stored in the 4-byte
big-endian integer located at offset 48 in the header of the
database file.
</p>
<p style='background-color: #ffd0d0;'>
<b>This pragma is deprecated</b> and exists
for backwards compatibility only. New applications
should avoid using this pragma. Older applications should discontinue
use of this pragma at the earliest opportunity. This pragma may be omitted
from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>.
</p>
<a name="pragma_defer_foreign_keys"></a>
<hr>
<p><b>PRAGMA defer_foreign_keys
<br>PRAGMA defer_foreign_keys = </b><i>boolean</i><b>;</b></p>
<p>When the defer_foreign_keys <a href="pragma.html#syntax">PRAGMA</a> is on,
enforcement of all <a href="foreignkeys.html">foreign key constraints</a> is delayed until the
outermost transaction is committed. The defer_foreign_keys pragma
defaults to OFF so that foreign key constraints are only deferred if
they are created as "DEFERRABLE INITIALLY DEFERRED". The
defer_foreign_keys pragma is automatically switched off at each
COMMIT or ROLLBACK. Hence, the defer_foreign_keys pragma must be
separately enabled for each transaction. This pragma is
only meaningful if foreign key constraints are enabled, of course.</p>
<p>The <a href="c3ref/db_status.html">sqlite3_db_status</a>(db,<a href="c3ref/c_dbstatus_options.html#sqlitedbstatusdeferredfks">SQLITE_DBSTATUS_DEFERRED_FKS</a>,...)
C-language interface can be used during a transaction to determine
if there are deferred and unresolved foreign key constraints.</p>
<a name="pragma_empty_result_callbacks"></a>
<hr>
<p><b>PRAGMA empty_result_callbacks;
<br>PRAGMA empty_result_callbacks = </b><i>boolean</i><b>;</b></p>
<p>Query or change the empty-result-callbacks flag.</p>
<p>The empty-result-callbacks flag affects the <a href="c3ref/exec.html">sqlite3_exec()</a> API only.
Normally, when the empty-result-callbacks flag is cleared, the
callback function supplied to the <a href="c3ref/exec.html">sqlite3_exec()</a> is not invoked
for commands that return zero rows of data. When empty-result-callbacks
is set in this situation, the callback function is invoked exactly once,
with the third parameter set to 0 (NULL). This is to enable programs
that use the <a href="c3ref/exec.html">sqlite3_exec()</a> API to retrieve column-names even when
a query returns no data.</p>
<p style='background-color: #ffd0d0;'>
<b>This pragma is deprecated</b> and exists
for backwards compatibility only. New applications
should avoid using this pragma. Older applications should discontinue
use of this pragma at the earliest opportunity. This pragma may be omitted
from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>.
</p>
<a name="pragma_encoding"></a>
<hr>
<p><b>PRAGMA encoding;
<br>PRAGMA encoding = "UTF-8";
<br>PRAGMA encoding = "UTF-16";
<br>PRAGMA encoding = "UTF-16le";
<br>PRAGMA encoding = "UTF-16be";</b></p>
<p>In first form, if the main database has already been
created, then this pragma returns the text encoding used by the
main database, one of "UTF-8", "UTF-16le" (little-endian UTF-16
encoding) or "UTF-16be" (big-endian UTF-16 encoding). If the main
database has not already been created, then the value returned is the
text encoding that will be used to create the main database, if
it is created by this session.</p>
<p>The second through fifth forms of this pragma
set the encoding that the main database will be created with if
it is created by this session. The string "UTF-16" is interpreted
as "UTF-16 encoding using native machine byte-ordering". It is not
possible to change the text encoding of a database after it has been
created and any attempt to do so will be silently ignored.</p>
<p>Once an encoding has been set for a database, it cannot be changed.</p>
<p>Databases created by the <a href="lang_attach.html">ATTACH</a> command always use the same encoding
as the main database. An attempt to <a href="lang_attach.html">ATTACH</a> a database with a different
text encoding from the "main" database will fail.</p>
<a name="pragma_foreign_key_check"></a>
<hr>
<p><b>PRAGMA foreign_key_check;
<br>PRAGMA foreign_key_check(</b><i>table-name</i><b>);</b></b></p>
<p>The foreign_key_check pragma checks the database, or the table
called "<i>table-name</i>", for
<a href="foreignkeys.html">foreign key constraints</a> that are violated and returns one row of
output for each violation. There are four columns in each result row.
The first column is the name of the table that contains the REFERENCES
clause. The second column is the <a href="lang_createtable.html#rowid">rowid</a> of the row that
contains the invalid REFERENCES clause. The third column is the name
of the table that is referred to. The fourth column is the index of
the specific foreign key constraint that failed. The fourth column
in the output of the foreign_key_check pragma is the same integer as
the first column in the output of the <a href="pragma.html#pragma_foreign_key_list">foreign_key_list pragma</a>.
When a "<i>table-name</i>" is specified, the only foreign key constraints
checked are those created by REFERENCES clauses in the
CREATE TABLE statement for <i>table-name</i>.</p>
<a name="pragma_foreign_key_list"></a>
<hr>
<p><b>PRAGMA foreign_key_list(</b><i>table-name</i><b>);</b></p>
<p>This pragma returns one row for each <a href="foreignkeys.html">foreign key constraint</a>
created by a REFERENCES clause in the CREATE TABLE statement of
table "<i>table-name</i>".
<a name="pragma_foreign_keys"></a>
<hr>
<p><b>PRAGMA foreign_keys;
<br>PRAGMA foreign_keys = </b><i>boolean</i><b>;</b></p>
<p>Query, set, or clear the enforcement of <a href="foreignkeys.html">foreign key constraints</a>.
<p>This pragma is a no-op within a transaction; foreign key constraint
enforcement may only be enabled or disabled when there is no pending
<a href="lang_transaction.html">BEGIN</a> or <a href="lang_savepoint.html">SAVEPOINT</a>.
<p>Changing the foreign_keys setting affects the execution of
all statements prepared
using the database connection, including those prepared before the
setting was changed. Any existing statements prepared using the legacy
<a href="c3ref/prepare.html">sqlite3_prepare()</a> interface may fail with an <a href="rescode.html#schema">SQLITE_SCHEMA</a> error
after the foreign_keys setting is changed.
<p>As of SQLite <a href="releaselog/3_6_19.html">version 3.6.19</a>, the default setting for foreign
key enforcement is OFF. However, that might change in a future
release of SQLite. The default setting for foreign key enforcement
can be specified at compile-time using the <a href="compile.html#default_foreign_keys">SQLITE_DEFAULT_FOREIGN_KEYS</a>
preprocessor macro. To minimize future problems, applications should
set the foreign key enforcement flag as required by the application
and not depend on the default setting.
<a name="pragma_freelist_count"></a>
<hr>
<p><b>PRAGMA freelist_count;</b></p>
<p>Return the number of unused pages in the database file.</p>
<a name="pragma_full_column_names"></a>
<hr>
<p><b>PRAGMA full_column_names;
<br>PRAGMA full_column_names = </b><i>boolean</i><b>;</b></p>
<p>Query or change the full_column_names flag. This flag together
with the <a href="pragma.html#pragma_short_column_names">short_column_names</a> flag determine
the way SQLite assigns names to result columns of <a href="lang_select.html">SELECT</a> statements.
Result columns are named by applying the following rules in order:
<ol>
<li><p>If there is an AS clause on the result, then the name of
the column is the right-hand side of the AS clause.</p></li>
<li><p>If the result is a general expression, not a just the name of
a source table column,
then the name of the result is a copy of the expression text.</p></li>
<li><p>If the <a href="pragma.html#pragma_short_column_names">short_column_names</a> pragma is ON, then the name of the
result is the name of the source table column without the
source table name prefix: COLUMN.</p></li>
<li><p>If both pragmas <a href="pragma.html#pragma_short_column_names">short_column_names</a> and <a href="pragma.html#pragma_full_column_names">full_column_names</a>
are OFF then case (2) applies.
</p></li>
<li><p>The name of the result column is a combination of the source table
and source column name: TABLE.COLUMN</p></li>
</ol>
<p style='background-color: #ffd0d0;'>
<b>This pragma is deprecated</b> and exists
for backwards compatibility only. New applications
should avoid using this pragma. Older applications should discontinue
use of this pragma at the earliest opportunity. This pragma may be omitted
from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>.
</p>
<a name="pragma_fullfsync"></a>
<hr>
<p><b>PRAGMA fullfsync
<br>PRAGMA fullfsync = </b><i>boolean</i><b>;</b></p>
<p>Query or change the fullfsync flag. This flag
determines whether or not the F_FULLFSYNC syncing method is used
on systems that support it. The default value of the fullfsync flag
is off. Only Mac OS X supports F_FULLFSYNC.</p>
<p>See also <a href="pragma.html#pragma_checkpoint_fullfsync">checkpoint_fullfsync</a>.</p>
<a name="pragma_ignore_check_constraints"></a>
<hr>
<p><b>PRAGMA ignore_check_constraints = </b><i>boolean</i><b>;</b></p>
<p>This pragma enables or disables the enforcement of CHECK constraints.
The default setting is off, meaning that CHECK constraints are
enforced by default.</p>
<a name="pragma_incremental_vacuum"></a>
<hr>
<p><b>PRAGMA incremental_vacuum</b><i>(N)</i><b>;</b></p>
<p>The incremental_vacuum pragma causes up to <i>N</i> pages to
be removed from the <a href="fileformat2.html#freelist">freelist</a>. The database file is truncated by
the same amount. The incremental_vacuum pragma has no effect if
the database is not in
<a href="#pragma_auto_vacuum">auto_vacuum=incremental</a> mode
or if there are no pages on the freelist. If there are fewer than
<i>N</i> pages on the freelist, or if <i>N</i> is less than 1, or
if <i>N</i> is omitted entirely, then the entire freelist is cleared.</p>
<a name="pragma_index_info"></a>
<hr>
<p><b>PRAGMA index_info(</b><i>index-name</i><b>);</b></p>
<p>This pragma returns one row each column in the named index.
The first column of the result is the rank of the column within the index.
The second column of the result is the rank of the column within the
table. The third column of output is the name of the column being indexed.
</p>
<a name="pragma_index_list"></a>
<hr>
<p><b>PRAGMA index_list(</b><i>table-name</i><b>);</b></p>
<p>This pragma returns one row for each index associated with the
given table.
Columns of the result set include the
index name and a flag to indicate whether or not the index is UNIQUE.
</p>
<a name="pragma_integrity_check"></a>
<hr>
<p><b>PRAGMA integrity_check;
<br>PRAGMA integrity_check(</b><i>N</i><b>)</b></p>
<p>This pragma does an integrity check of the entire database. The
integrity_check pragma
looks for out-of-order records, missing pages, malformed records, missing
index entries, and UNIQUE and NOT NULL constraint errors.
If the integrity_check pragma finds problems, strings are returned
(as multiple rows with a single column per row) which describe
the problems. Pragma integrity_check will return at most <i>N</i>
errors before the analysis quits, with N defaulting
to 100. If pragma integrity_check finds no errors, a
single row with the value 'ok' is returned.</p>
<p>PRAGMA integrity_check does not find
<a href="foreignkeys.html">FOREIGN KEY</a> errors.
Use the <a href="pragma.html#pragma_foreign_key_check">PRAGMA foreign_key_check</a> command for to find errors in
FOREIGN KEY constraints.</p>
<p>See also the <a href="pragma.html#pragma_quick_check">PRAGMA quick_check</a> command which does most of the
checking of PRAGMA integrity_check but runs much faster.</p>
<a name="pragma_journal_mode"></a>
<hr>
<p><b>PRAGMA journal_mode;
<br>PRAGMA </b><i>database</i><b>.journal_mode;
<br>PRAGMA journal_mode
= <i>DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF</i>
<br>PRAGMA </b><i>database</i><b>.journal_mode
= <i>DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF</i></b></p>
<p>This pragma queries or sets the journal mode for databases
associated with the current <a href="c3ref/sqlite3.html">database connection</a>.</p>
<p>The first two forms of this pragma query the current journaling
mode for <i>database</i>. When <i>database</i> is omitted, the
"main" database is queried.</p>
<p>The last two forms change the journaling mode. The 4th form
changes the journaling mode for a specific database connection named.
Use "main" for the main database (the database that was opened by
the original <a href="c3ref/open.html">sqlite3_open()</a>, <a href="c3ref/open.html">sqlite3_open16()</a>, or
<a href="c3ref/open.html">sqlite3_open_v2()</a> interface call) and use "temp" for database
that holds TEMP tables. The 3rd form changes the journaling mode
on all databases attached to the connection.
The new journal mode is returned. If the journal mode
could not be changed, the original journal mode is returned.</p>
<p>The DELETE journaling mode is the normal behavior. In the DELETE
mode, the rollback journal is deleted at the conclusion of each
transaction. Indeed, the delete operation is the action that causes
the transaction to commit.
(See the document titled <a href="atomiccommit.html">
Atomic Commit In SQLite</a> for additional detail.)</p>
<p>The TRUNCATE journaling mode commits transactions by truncating
the rollback journal to zero-length instead of deleting it. On many
systems, truncating a file is much faster than deleting the file since
the containing directory does not need to be changed.</p>
<p>The PERSIST journaling mode prevents the rollback journal from
being deleted at the end of each transaction. Instead, the header
of the journal is overwritten with zeros. This will prevent other
database connections from rolling the journal back. The PERSIST
journaling mode is useful as an optimization on platforms where
deleting or truncating a file is much more expensive than overwriting
the first block of a file with zeros. See also:
<a href="pragma.html#pragma_journal_size_limit">PRAGMA journal_size_limit</a> and <a href="compile.html#default_journal_size_limit">SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT</a>.</p>
<p>The MEMORY journaling mode stores the rollback journal in
volatile RAM. This saves disk I/O but at the expense of database
safety and integrity. If the application using SQLite crashes in
the middle of a transaction when the MEMORY journaling mode is set,
then the database file will very likely go corrupt.</p>
<p>The WAL journaling mode uses a <a href="wal.html">write-ahead log</a> instead of a
rollback journal to implement transactions. The WAL journaling mode
is persistent; after being set it stays in effect
across multiple database connections and after closing and
reopening the database. A database in WAL journaling mode
can only be accessed by SQLite version 3.7.0 or later.</p>
<p>The OFF journaling mode disables the rollback journal completely.
No rollback journal is ever created and hence there is never a rollback
journal to delete. The OFF journaling mode disables the atomic
commit and rollback capabilities of SQLite. The <a href="lang_transaction.html">ROLLBACK</a> command
no longer works; it behaves in an undefined way. Applications must
avoid using the <a href="lang_transaction.html">ROLLBACK</a> command when the journal mode is OFF.
If the application crashes
in the middle of a transaction when the OFF journaling mode is
set, then the database file will very likely go corrupt.</p>
<p>Note that the journal_mode for an <a href="inmemorydb.html">in-memory database</a>
is either MEMORY or OFF and can not be changed to a different value.
An attempt to change the journal_mode of an <a href="inmemorydb.html">in-memory database</a> to
any setting other than MEMORY or OFF is ignored. Note also that
the journal_mode cannot be changed while a transaction is active.</p>
<a name="pragma_journal_size_limit"></a>
<hr>
<p><b>
PRAGMA journal_size_limit<br>
PRAGMA journal_size_limit = </b><i>N</i> <b>;</b>
<p>If a database connection is operating in
<a href="pragma.html#pragma_locking_mode">exclusive locking mode</a> or in
<a href="pragma.html#pragma_journal_mode">persistent journal mode</a>
(PRAGMA journal_mode=persist) then
after committing a transaction the <a href="lockingv3.html#rollback">rollback journal</a> file may remain in
the file-system. This increases performance for subsequent transactions
since overwriting an existing file is faster than append to a file,
but it also consumes
file-system space. After a large transaction (e.g. a <a href="lang_vacuum.html">VACUUM</a>),
the rollback journal file may consume a very large amount of space.
<p>Similarly, in <a href="wal.html">WAL mode</a>, the write-ahead log file is not truncated
following a <a href="wal.html#ckpt">checkpoint</a>. Instead, SQLite reuses the existing file
for subsequent WAL entries since overwriting is faster than appending.
<p>The journal_size_limit pragma may be used to limit the size of
rollback-journal and WAL files left
in the file-system after transactions or checkpoints.
Each time a transaction is committed or a WAL file resets, SQLite
compares the size of the rollback journal file or WAL file left in
the file-system to the size limit
set by this pragma and if the journal or WAL file is larger
it is truncated to the limit.
<p>The second form of the pragma listed above is used to set a new limit
in bytes for the specified database. A negative number implies no limit.
To always truncate rollback journals and WAL files to their minimum size,
set the journal_size_limit to zero.
Both the first and second forms of the pragma listed above return a single
result row containing a single integer column - the value of the journal
size limit in bytes. The default journal size limit is -1 (no limit). The
<a href="compile.html#default_journal_size_limit">SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT</a> preprocessor macro can be used to change
the default journal size limit at compile-time.</p>
<p>This pragma only operates on the single database specified prior
to the pragma name (or on the "main" database if no database is specified.)
There is no way to change the journal size limit on all attached databases
using a single PRAGMA statement. The size limit must be set separately for
each attached database.
<a name="pragma_legacy_file_format"></a>
<hr>
<p><b>PRAGMA legacy_file_format;
<br>PRAGMA legacy_file_format = <i>boolean</i></b></p>
<p>This pragma sets or queries the value of the legacy_file_format
flag. When this flag is on, new SQLite databases are created in
a file format that is readable and writable by all versions of
SQLite going back to 3.0.0. When the flag is off, new databases
are created using the latest file format which might not be
readable or writable by versions of SQLite prior to 3.3.0.</p>
<p>When the legacy_file_format pragma is issued with no argument,
it returns the setting of the flag. This pragma does <u>not</u> tell
which file format the current database is using; it tells what format
will be used by any newly created databases.</p>
<p>The legacy_file_format pragma is initialized to OFF when an existing
database in the newer file format is first opened.</p>
<p>The default file format is set by the
<a href="compile.html#default_file_format">SQLITE_DEFAULT_FILE_FORMAT</a> compile-time option.</p>
<a name="pragma_locking_mode"></a>
<hr>
<p><b>PRAGMA locking_mode;
<br>PRAGMA locking_mode = <i>NORMAL | EXCLUSIVE</i></b></p>
<p>This pragma sets or queries the database connection locking-mode.
The locking-mode is either NORMAL or EXCLUSIVE.
<p>In NORMAL locking-mode (the default unless overridden at compile-time
using <a href="compile.html#default_locking_mode">SQLITE_DEFAULT_LOCKING_MODE</a>), a database connection
unlocks the database file at the conclusion of each read or
write transaction. When the locking-mode is set to EXCLUSIVE, the
database connection never releases file-locks. The first time the
database is read in EXCLUSIVE mode, a shared lock is obtained and
held. The first time the database is written, an exclusive lock is
obtained and held.</p>
<p>Database locks obtained by a connection in EXCLUSIVE mode may be
released either by closing the database connection, or by setting the
locking-mode back to NORMAL using this pragma and then accessing the
database file (for read or write). Simply setting the locking-mode to
NORMAL is not enough - locks are not released until the next time
the database file is accessed.</p>
<p>There are three reasons to set the locking-mode to EXCLUSIVE.
<ol>
<li>The application wants to prevent other processes from
accessing the database file.
<li>The number of system calls for filesystem operations is reduced,
possibly resulting in a small performance increase.
<li><a href="wal.html">WAL</a> databases can be accessed in EXCLUSIVE mode without the
use of shared memory.
(<a href="wal.html#noshm">Additional information</a>)
</ol>
</p>
<p>When the locking_mode pragma specifies a particular database,
for example:</p>
<blockquote>
PRAGMA <b>main.</b>locking_mode=EXCLUSIVE;
</blockquote>
<p>Then the locking mode applies only to the named database. If no
database name qualifier precedes the "locking_mode" keyword then
the locking mode is applied to all databases, including any new
databases added by subsequent <a href="lang_attach.html">ATTACH</a> commands.</p>
<p>The "temp" database (in which TEMP tables and indices are stored)
and <a href="inmemorydb.html">in-memory databases</a>
always uses exclusive locking mode. The locking mode of temp and
<a href="inmemorydb.html">in-memory databases</a> cannot
be changed. All other databases use the normal locking mode by default
and are affected by this pragma.</p>
<p>If the locking mode is EXCLUSIVE when first entering
<a href="wal.html">WAL journal mode</a>, then the locking mode cannot be changed to
NORMAL until after exiting WAL journal mode.
If the locking mode is NORMAL when first entering WAL
journal mode, then the locking mode can be changed between NORMAL and
EXCLUSIVE and back again at any time and without needing to exit
WAL journal mode.</p>
<a name="pragma_max_page_count"></a>
<hr>
<p><b>PRAGMA max_page_count;
<br>PRAGMA max_page_count = </b><i>N</i><b>;</b></p>
<p>Query or set the maximum number of pages in the database file.
Both forms of the pragma return the maximum page count. The second
form attempts to modify the maximum page count. The maximum page
count cannot be reduced below the current database size.
</p>
<a name="pragma_mmap_size"></a>
<hr>
<p><br><b>PRAGMA </b><i>database</i><b>.mmap_size;
<br>PRAGMA </b><i>database</i><b>.mmap_size=</b><i>N</i></p>
<p>Query or change the maximum number of bytes that are set
aside for memory-mapped I/O on a single database. The first form
(without an argument) queries the current limit. The second
form (with a numeric argument) sets the limit for the specified
database, or for all databases if the optional database name is
omitted. In the second form, if the database name is omitted, the
limit that is set becomes the default limit for all databases that
are added to the <a href="c3ref/sqlite3.html">database connection</a> by subsequent <a href="lang_attach.html">ATTACH</a>
statements.</p>
<p>The argument N is the maximum number of bytes of the database file
that will be accessed using memory-mapped I/O. If N is zero then
memory mapped I/O is disabled. If N is negative, then the limit
reverts to the default value determined by the most recent
<a href="c3ref/config.html">sqlite3_config</a>(<a href="c3ref/c_config_covering_index_scan.html#sqliteconfigmmapsize">SQLITE_CONFIG_MMAP_SIZE</a>), or to the compile
time default determined by <a href="compile.html#default_mmap_size">SQLITE_DEFAULT_MMAP_SIZE</a> if not
start-time limit has been set.</p>
<p>The <a href="pragma.html#pragma_mmap_size">PRAGMA mmap_size</a> statement will never increase the amount
of address space used for memory-mapped I/O above the
hard limit set by the <a href="compile.html#max_mmap_size">SQLITE_MAX_MMAP_SIZE</a> compile-time option,
nor the hard limit set start-time by the second argument to
sqlite3_config(<a href="c3ref/c_config_covering_index_scan.html#sqliteconfigmmapsize">SQLITE_CONFIG_MMAP_SIZE</a>)</p>
<p>The size of the memory-mapped I/O region cannot be changed while
the memory-mapped I/O region is in active use, to avoid unmapping
memory out from under running SQL statements. For this reason,
the mmap_size pragma may be a no-op if the prior mmap_size is non-zero
and there are other SQL statements running concurrently on the same
<a href="c3ref/sqlite3.html">database connection</a>.</p>
<a name="pragma_page_count"></a>
<hr>
<p><b>PRAGMA page_count;</b></p>
<p>Return the total number of pages in the database file.</p>
<a name="pragma_page_size"></a>
<hr>
<p><b>PRAGMA page_size;
<br>PRAGMA page_size = </b><i>bytes</i><b>;</b></p>
<p>Query or set the page size of the database. The page
size must be a power of two between 512 and 65536 inclusive.
</p>
<p>When a new database is created, SQLite assigned a default page size
based on information received from the xSectorSize and
xDeviceCharacteristics methods of the <a href="c3ref/io_methods.html">sqlite3_io_methods</a> object
of the newly created database file. The page_size pragma will only
cause an immediate change in the
page size if it is issued while the database is still empty, prior
to the first CREATE TABLE statement. If the page_size pragma is
used to specify a new page size just prior to
running the <a href="lang_vacuum.html">VACUUM</a> command and if the database is not in
<a href="wal.html">WAL journal mode</a> then <a href="lang_vacuum.html">VACUUM</a> will change the page
size to the new value.</p>
<p>If SQLite is compiled with the SQLITE_ENABLE_ATOMIC_WRITE option,
then the default page size is chosen to be the largest page size
less than or equal to SQLITE_MAX_DEFAULT_PAGE_SIZE for which atomic
write is enabled according to the
xDeviceCharacteristics method of the <a href="c3ref/io_methods.html">sqlite3_io_methods</a> object for
the database file. If the SQLITE_ENABLE_ATOMIC_WRITE option is
disabled or if xDeviceCharacteristics reports no suitable atomic
write page sizes, then the default page size is the larger of
SQLITE_DEFAULT_PAGE_SIZE
and the sector size as reported by the xSectorSize method of the
<a href="c3ref/io_methods.html">sqlite3_io_methods</a> object, but not more than
SQLITE_MAX_DEFAULT_PAGE_SIZE. The normal configuration for SQLite
running on workstations is for atomic write to be
disabled, for the maximum page size to be set to 65536, for
SQLITE_DEFAULT_PAGE_SIZE to be 1024, and for the
maximum default page size to be set to 8192. The default xSectorSize
method on unix workstation implementations always reports a sector size
of 512 bytes. Hence,
the default page size chosen by SQLite on unix is usually 1024 bytes.
On windows, the GetDiskFreeSpace() interface is used to obtain the
actual device sector size and hence the default page size on windows
will sometimes be greater than 1024.</p>
<a name="pragma_parser_trace"></a>
<hr>
<p><b>PRAGMA parser_trace = </b><i>boolean</i><b>; </b></p>
<p>If SQLite has been compiled with the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time
option, then the parser_trace pragma can be used to turn on tracing
for the SQL parser used internally by SQLite.
This feature is used for debugging SQLite itself.</p>
<p style='background-color: #f0e0ff;'>
This pragma is intended for use when debugging SQLite itself. It
is only available when the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time option
is used.</p>
<a name="pragma_query_only"></a>
<hr>
<p><b>PRAGMA query_only;
<br>PRAGMA query_only = </b><i>boolean</i><b>;</b></p>
<p>The query_only pragma prevents all changes to database files when
enabled.</p>
<a name="pragma_quick_check"></a>
<hr>
<p><b>PRAGMA quick_check;
<br>PRAGMA quick_check(</b><i>N</i><b>)</b></p>
<p>The pragma is like <a href="pragma.html#pragma_integrity_check">integrity_check</a> except that it does not verify
UNIQUE and NOT NULL constraints and does not verify
that index content matches table content. By skipping UNIQUE and NOT NULL
and index consistency checks, quick_check is able to run much faster than
integrity_check. Otherwise the two pragmas are the same.
</p>
<a name="pragma_read_uncommitted"></a>
<hr>
<p><b>PRAGMA read_uncommitted;
<br>PRAGMA read_uncommitted = </b><i>boolean</i><b>;</b></p>
<p>Query, set, or clear READ UNCOMMITTED isolation. The default isolation
level for SQLite is SERIALIZABLE. Any process or thread can select
READ UNCOMMITTED isolation, but SERIALIZABLE will still be used except
between connections that share a common page and schema cache.
Cache sharing is enabled using the <a href="c3ref/enable_shared_cache.html">sqlite3_enable_shared_cache()</a> API.
Cache sharing is disabled by default.
</p>
<p>See <a href="sharedcache.html">SQLite Shared-Cache Mode</a> for additional information.</p>
<a name="pragma_recursive_triggers"></a>
<hr>
<p><b>PRAGMA recursive_triggers;
<br>PRAGMA recursive_triggers = </b><i>boolean</i><b>;</b></p>
<p>Query, set, or clear the recursive trigger capability.
<p>Changing the recursive_triggers setting affects the execution of
all statements prepared
using the database connection, including those prepared before the
setting was changed. Any existing statements prepared using the legacy
<a href="c3ref/prepare.html">sqlite3_prepare()</a> interface may fail with an <a href="rescode.html#schema">SQLITE_SCHEMA</a> error
after the recursive_triggers setting is changed.
<p>Prior to SQLite version 3.6.18, recursive triggers were not
supported. The behavior of SQLite was always as if this pragma was
set to OFF. Support for recursive triggers was added in version 3.6.18
but was initially turned OFF by default, for compatibility. Recursive
triggers may be turned on by default in future versions of SQLite.
</p>
<p>The depth of recursion for triggers has a hard upper limit set by
the <a href="limits.html#max_trigger_depth">SQLITE_MAX_TRIGGER_DEPTH</a> compile-time option and a run-time
limit set by <a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimittriggerdepth">SQLITE_LIMIT_TRIGGER_DEPTH</a>,...).</p>
<a name="pragma_reverse_unordered_selects"></a>
<hr>
<p><b>PRAGMA reverse_unordered_selects;
<br>PRAGMA reverse_unordered_selects = </b><i>boolean</i><b>;</b></p>
<p>When enabled, this PRAGMA causes <a href="lang_select.html">SELECT</a> statements without
an ORDER BY clause to emit their results in the reverse order of what
they normally would. This can help debug applications that are
making invalid assumptions about the result order.<p>SQLite makes no
guarantees about the order of results if a SELECT omits the ORDER BY
clause. Even so, the order of results does not change from one
run to the next, and so many applications mistakenly come to depend
on the arbitrary output order whatever that order happens to be. However,
sometimes new versions of SQLite will contain optimizer enhancements
that will cause the output order of queries without ORDER BY clauses
to shift. When that happens, applications that depend on a certain
output order might malfunction. By running the application multiple
times with this pragma both disabled and enabled, cases where the
application makes faulty assumptions about output order can be
identified and fixed early, reducing problems
that might be caused by linking against a different version of SQLite.
</p>
<a name="pragma_schema_version"></a>
<hr>
<p><b>PRAGMA schema_version;
<br>PRAGMA schema_version = </b><i>integer </i><b>;
<br>PRAGMA user_version;
<br>PRAGMA user_version = </b><i>integer </i><b>;</b>
<p> The pragmas schema_version and user_version are used to set or get
the value of the schema-version and user-version, respectively. The
schema-version and the user-version are big-endian 32-bit signed
integers stored in the database header at offsets 40 and 60,
respectively.</p>
<p> The schema-version is usually only manipulated internally by SQLite.
It is incremented by SQLite whenever the database schema is modified
(by creating or dropping a table or index). The schema version is
used by SQLite each time a query is executed to ensure that the
internal cache of the schema used when compiling the SQL query matches
the schema of the database against which the compiled query is actually
executed. Subverting this mechanism by using "PRAGMA schema_version"
to modify the schema-version is potentially dangerous and may lead
to program crashes or database corruption. Use with caution!</p>
<p> The user-version is not used internally by SQLite. It may be used by
applications for any purpose.</p>
<a name="pragma_secure_delete"></a>
<hr>
<p><b>PRAGMA secure_delete;
<br>PRAGMA </b><i>database</i><b>.secure_delete;
<br>PRAGMA secure_delete = </b><i>boolean</i><b>
<br>PRAGMA </b><i>database</i><b>.secure_delete =
</b><i>boolean</i></p>
<p>Query or change the secure-delete setting. When secure-delete
on, SQLite overwrites deleted content with zeros. The default
setting is determined by the <a href="compile.html#secure_delete">SQLITE_SECURE_DELETE</a>
compile-time option.
<p>
When there are <a href="lang_attach.html">attached databases</a> and no database
is specified in the pragma, all databases have their secure-delete
setting altered.
The secure-delete setting for newly attached databases is the setting
of the main database at the time the ATTACH command is evaluated.
<p>
When multiple database connections share the same cache, changing
the secure-delete flag on one database connection changes it for them
all.
</p>
<a name="pragma_short_column_names"></a>
<hr>
<p><b>PRAGMA short_column_names;
<br>PRAGMA short_column_names = </b><i>boolean</i><b>;</b></p>
<p>Query or change the short-column-names flag. This flag affects
the way SQLite names columns of data returned by <a href="lang_select.html">SELECT</a> statements.
See the <a href="pragma.html#pragma_full_column_names">full_column_names</a> pragma for full details.
</p>
<p style='background-color: #ffd0d0;'>
<b>This pragma is deprecated</b> and exists
for backwards compatibility only. New applications
should avoid using this pragma. Older applications should discontinue
use of this pragma at the earliest opportunity. This pragma may be omitted
from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>.
</p>
<a name="pragma_shrink_memory"></a>
<hr>
<p><b>PRAGMA shrink_memory</b></p>
<p>This pragma causes the database connection on which it is invoked
to free up as much memory as it can, by calling
<a href="c3ref/db_release_memory.html">sqlite3_db_release_memory()</a>.
</p>
<a name="pragma_soft_heap_limit"></a>
<hr>
<p><b>PRAGMA soft_heap_limit<br>
PRAGMA soft_heap_limit=</b><i>N</i></p>
<p>This pragma invokes the <a href="c3ref/soft_heap_limit64.html">sqlite3_soft_heap_limit64()</a> interface with
the argument N, if N is specified and is a non-negative integer.
The soft_heap_limit pragma always returns the same integer
that would be returned by the <a href="c3ref/soft_heap_limit64.html">sqlite3_soft_heap_limit64</a>(-1) C-language
function.
</p>
<a name="pragma_stats"></a>
<hr>
<p><b>PRAGMA stats;</b> </p>
<p>This pragma returns auxiliary information about tables and
indices. The returned information is used during testing to help
verify that the query planner is operating correctly. The format
and meaning of this pragma will likely change from release
to the next. Because of its volatility, the behavior and output
format of this pragma are deliberately undocumented.</p>
<p style='background-color: #f0e0ff;'>
The intended use of this pragma is only for testing and validation of
SQLite. This pragma is subject to change without notice and is not
recommended for use by application programs.</p>
<a name="pragma_synchronous"></a>
<hr>
<p><b>PRAGMA synchronous;
<br>PRAGMA synchronous = </b>
<i>0 | OFF | 1 | NORMAL | 2 | FULL</i><b>;</b></p>
<p>Query or change the setting of the "synchronous" flag.
The first (query) form will return the synchronous setting as an
integer. When synchronous is FULL (2), the SQLite database engine will
use the xSync method of the <a href="vfs.html">VFS</a> to ensure that all content is safely
written to the disk surface prior to continuing.
This ensures that an operating system crash or power failure will
not corrupt the database.
FULL synchronous is very safe, but it is also slower.
When synchronous is NORMAL (1), the SQLite database
engine will still sync at the most critical moments, but less often
than in FULL mode. There is a very small (though non-zero) chance that
a power failure at just the wrong time could corrupt the database in
NORMAL mode. But in practice, you are more likely to suffer
a catastrophic disk failure or some other unrecoverable hardware
fault.
With synchronous OFF (0), SQLite continues without syncing
as soon as it has handed data off to the operating system.
If the application running SQLite crashes, the data will be safe, but
the database might become corrupted if the operating system
crashes or the computer loses power before that data has been written
to the disk surface. On the other hand, some
operations are as much as 50 or more times faster with synchronous OFF.
</p>
<p>In <a href="wal.html">WAL</a> mode when synchronous is NORMAL (1), the WAL file is
synchronized before each <a href="wal.html#ckpt">checkpoint</a> and the database file is
synchronized after each completed <a href="wal.html#ckpt">checkpoint</a> and the WAL file
header is synchronized when a WAL file begins to be reused after
a checkpoint, but no sync operations occur during most transactions.
With synchronous=FULL in WAL mode, an additional
sync operation of the WAL file happens after each transaction commit.
The extra WAL sync following each transaction help ensure that
transactions are durable across a power loss, but they do not aid
in preserving consistency.
If durability is not a concern, then synchronous=NORMAL is normally
all one needs in WAL mode.</p>
<p>The default setting is synchronous=FULL.</p>
<p>See also the <a href="pragma.html#pragma_fullfsync">fullfsync</a> and <a href="pragma.html#pragma_checkpoint_fullfsync">checkpoint_fullfsync</a> pragmas.</p>
<a name="pragma_table_info"></a>
<hr>
<p><b>PRAGMA table_info(</b><i>table-name</i><b>);</b></p>
<p>This pragma returns one row for each column in the named table.
Columns in the result set include the column name,
data type, whether or not the column can be NULL, and the default
value for the column. The "pk" column in the result set is zero
for columns that are not part of the primary key, and is the index of
the column in the primary key for columns that are part of the primary
key.</p>
<p>The table named in the table_info pragma can also be a view.</p>
<a name="pragma_temp_store"></a>
<hr>
<p><b>PRAGMA temp_store;
<br>PRAGMA temp_store = </b>
<i>0 | DEFAULT | 1 | FILE | 2 | MEMORY</i><b>;</b></p>
<p>Query or change the setting of the "<b>temp_store</b>" parameter.
When temp_store is DEFAULT (0), the compile-time C preprocessor macro
<a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> is used to determine where temporary tables and indices
are stored. When
temp_store is MEMORY (2) <a href="inmemorydb.html#temp_db">temporary tables</a> and indices are kept in
as if they were pure <a href="inmemorydb.html">in-memory databases</a> memory.
When temp_store is FILE (1) <a href="inmemorydb.html#temp_db">temporary tables</a> and indices are stored
in a file. The <a href="pragma.html#pragma_temp_store_directory">temp_store_directory</a> pragma can be used to specify
the directory containing temporary files when
<b>FILE</b> is specified. When the temp_store setting is changed,
all existing temporary tables, indices, triggers, and views are
immediately deleted.</p>
<p>It is possible for the library compile-time C preprocessor symbol
<a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> to override this pragma setting.
The following table summarizes
the interaction of the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> preprocessor macro and the
temp_store pragma:</p>
<blockquote>
<table cellpadding="2" border="1">
<tr><th valign="bottom"><a href="compile.html#temp_store">SQLITE_TEMP_STORE</a></th>
<th valign="bottom">PRAGMA<br>temp_store</th>
<th>Storage used for<br>TEMP tables and indices</th></tr>
<tr><td align="center">0</td>
<td align="center"><em>any</em></td>
<td align="center">file</td></tr>
<tr><td align="center">1</td>
<td align="center">0</td>
<td align="center">file</td></tr>
<tr><td align="center">1</td>
<td align="center">1</td>
<td align="center">file</td></tr>
<tr><td align="center">1</td>
<td align="center">2</td>
<td align="center">memory</td></tr>
<tr><td align="center">2</td>
<td align="center">0</td>
<td align="center">memory</td></tr>
<tr><td align="center">2</td>
<td align="center">1</td>
<td align="center">file</td></tr>
<tr><td align="center">2</td>
<td align="center">2</td>
<td align="center">memory</td></tr>
<tr><td align="center">3</td>
<td align="center"><em>any</em></td>
<td align="center">memory</td></tr>
</table>
</blockquote>
<a name="pragma_temp_store_directory"></a>
<hr>
<p><b>PRAGMA temp_store_directory;
<br>PRAGMA temp_store_directory = '</b><i>directory-name</i><b>';</b></p>
<p>Query or change the value of the <a href="c3ref/temp_directory.html">sqlite3_temp_directory</a> global
variable, which many operating-system interface backends use to
determine where to store <a href="inmemorydb.html#temp_db">temporary tables</a> and indices.</p>
<p>When the temp_store_directory setting is changed, all existing temporary
tables, indices, triggers, and viewers in the database connection that
issued the pragma are immediately deleted. In
practice, temp_store_directory should be set immediately after the first
database connection for a process is opened. If the temp_store_directory
is changed for one database connection while other database connections
are open in the same process, then the behavior is undefined and
probably undesirable.</p>
<p>Changing the temp_store_directory setting is <u>not</u> threadsafe.
Never change the temp_store_directory setting if another thread
within the application is running any SQLite interface at the same time.
Doing so results in undefined behavior. Changing the temp_store_directory
setting writes to the <a href="c3ref/temp_directory.html">sqlite3_temp_directory</a> global
variable and that global variable is not protected by a mutex.</p>
<p>The value <i>directory-name</i> should be enclosed in single quotes.
To revert the directory to the default, set the <i>directory-name</i> to
an empty string, e.g., <i>PRAGMA temp_store_directory = ''</i>. An
error is raised if <i>directory-name</i> is not found or is not
writable. </p>
<p>The default directory for temporary files depends on the OS. Some
OS interfaces may choose to ignore this variable and place temporary
files in some other directory different from the directory specified
here. In that sense, this pragma is only advisory.</p>
<p style='background-color: #ffd0d0;'>
<b>This pragma is deprecated</b> and exists
for backwards compatibility only. New applications
should avoid using this pragma. Older applications should discontinue
use of this pragma at the earliest opportunity. This pragma may be omitted
from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>.
</p>
<a name="pragma_threads"></a>
<hr>
<p><b>PRAGMA threads;
<br>PRAGMA threads = </b><i>N</i><b>;</b></p>
<p>Query or change the value of the
<a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitworkerthreads">SQLITE_LIMIT_WORKER_THREADS</a>,...) limit for
the current database connection. This limit sets an upper bound
on the number of auxiliary threads that a <a href="c3ref/stmt.html">prepared statement</a> is
allowed to launch to assist with a query. The default limit is 0
unless it is changed using the <a href="compile.html#default_worker_threads">SQLITE_DEFAULT_WORKER_THREADS</a>
compile-time option. When the limit is zero, that means no
auxiliary threads will be launched.</p>
<p>This pragma is a thin wrapper around the
<a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitworkerthreads">SQLITE_LIMIT_WORKER_THREADS</a>,...) interface.
</p>
<a name="pragma_vdbe_addoptrace"></a>
<hr>
<p><b>PRAGMA vdbe_addoptrace = </b><i>boolean</i><b>;</b></p>
<p>If SQLite has been compiled with the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time
option, then the vdbe_addoptrace pragma can be used to cause a complete
VDBE opcodes to be displayed as they are created during code generation.
This feature is used for debugging SQLite itself. See the
<a href="vdbe.html#trace">VDBE documentation</a> for more
information.</p>
<p style='background-color: #f0e0ff;'>
This pragma is intended for use when debugging SQLite itself. It
is only available when the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time option
is used.</p>
<a name="pragma_vdbe_debug"></a>
<hr>
<p><b>PRAGMA vdbe_debug = </b><i>boolean</i><b>;</b></p>
<p>If SQLite has been compiled with the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time
option, then the vdbe_debug pragma is a shorthand for three other
debug-only pragmas: vdbe_addoptrace, vdbe_listing, and vdbe_trace.
This feature is used for debugging SQLite itself. See the
<a href="vdbe.html#trace">VDBE documentation</a> for more
information.</p>
<p style='background-color: #f0e0ff;'>
This pragma is intended for use when debugging SQLite itself. It
is only available when the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time option
is used.</p>
<a name="pragma_vdbe_listing"></a>
<hr>
<p><b>PRAGMA vdbe_listing = </b><i>boolean</i><b>;</b></p>
<p>If SQLite has been compiled with the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time
option, then the vdbe_listing pragma can be used to cause a complete
listing of the virtual machine opcodes to appear on standard output
as each statement is evaluated.
With listing is on, the entire content of a program is printed
just prior to beginning execution. The statement
executes normally after the listing is printed.
This feature is used for debugging SQLite itself. See the
<a href="vdbe.html#trace">VDBE documentation</a> for more
information.</p>
<p style='background-color: #f0e0ff;'>
This pragma is intended for use when debugging SQLite itself. It
is only available when the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time option
is used.</p>
<a name="pragma_vdbe_trace"></a>
<hr>
<p><b>PRAGMA vdbe_trace = </b><i>boolean</i><b>;</b></p>
<p>If SQLite has been compiled with the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time
option, then the vdbe_trace pragma can be used to cause virtual machine
opcodes to be printed on standard output as they are evaluated.
This feature is used for debugging SQLite. See the
<a href="vdbe.html#trace">VDBE documentation</a> for more
information.</p>
<p style='background-color: #f0e0ff;'>
This pragma is intended for use when debugging SQLite itself. It
is only available when the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time option
is used.</p>
<a name="pragma_wal_autocheckpoint"></a>
<hr>
<p><b>PRAGMA wal_autocheckpoint;<br>
PRAGMA wal_autocheckpoint=</b><i>N</i><b>;</b></p>
<p>This pragma queries or sets the <a href="wal.html">write-ahead log</a>
<a href="wal.html#ckpt">auto-checkpoint</a> interval.
When the <a href="wal.html">write-ahead log</a> is enabled (via the
<a href="pragma.html#pragma_journal_mode">journal_mode pragma</a>) a checkpoint will be run automatically whenever
the write-ahead log equals or exceeds <i>N</i> pages in length.
Setting the auto-checkpoint size to zero or a negative value
turns auto-checkpointing off.</p>
<p>This pragma is a wrapper around the
<a href="c3ref/wal_autocheckpoint.html">sqlite3_wal_autocheckpoint()</a> C interface.
All automatic checkpoints are <a href="c3ref/wal_checkpoint_v2.html">PASSIVE</a>.</p>
<p>Autocheckpointing is enabled by default with an interval
of 1000 or <a href="compile.html#default_wal_autocheckpoint">SQLITE_DEFAULT_WAL_AUTOCHECKPOINT</a>.</p>
<a name="pragma_wal_checkpoint"></a>
<hr>
<p><b>PRAGMA </b><i>database</i><b>.wal_checkpoint;</b><br>
<b>PRAGMA </b><i>database</i><b>.wal_checkpoint(PASSIVE);</b><br>
<b>PRAGMA </b><i>database</i><b>.wal_checkpoint(FULL);</b><br>
<b>PRAGMA </b><i>database</i><b>.wal_checkpoint(RESTART);</b>
</p>
<p>If the <a href="wal.html">write-ahead log</a> is enabled (via the <a href="pragma.html#pragma_journal_mode">journal_mode pragma</a>),
this pragma causes a <a href="wal.html#ckpt">checkpoint</a> operation to run on database
<i>database</i>, or on all attached databases if <i>database</i>
is omitted. If <a href="wal.html">write-ahead log</a> mode is disabled, this pragma is a
harmless no-op.</p>
<p>Invoking this
pragma without an argument is equivalent to calling the
<a href="c3ref/wal_checkpoint.html">sqlite3_wal_checkpoint()</a> C interface.</p>
Invoking this pragma with an argument is equivalent to calling the
<a href="c3ref/wal_checkpoint_v2.html">sqlite3_wal_checkpoint_v2()</a> C interface with a
<a href="c3ref/c_checkpoint_full.html">3rd parameter</a>
corresponding to the argument:
<dl>
<dt>PASSIVE<dd>
Checkpoint as many frames as possible without waiting for any database
readers or writers to finish. Sync the db file if all frames in the log
are checkpointed. This mode is the same as calling
sqlite3_wal_checkpoint(). The
<a href="c3ref/busy_handler.html">busy-handler callback</a> is never invoked.
<dt>FULL<dd>
This mode blocks
(invokes the <a href="c3ref/busy_handler.html">busy-handler callback</a>)
until there is no
database writer and all readers are reading from the most recent database
snapshot. It then checkpoints all frames in the log file and syncs the
database file. FULL blocks database writers while it is
running, but not database readers.
<dt>RESTART<dd>
This mode works the same way as SQLITE_CHECKPOINT_FULL, except after
checkpointing the log file it blocks (calls the
<a href="c3ref/busy_handler.html">busy-handler callback</a>)
until all readers are reading from the database file only. This ensures
that the next client to write to the database file restarts the log file
from the beginning. RESTART blocks database writers while it is
running, but not database readers.
</dl>
<p>The wal_checkpoint pragma returns a single row with three
integer columns. The first column is usually 0 but will be
1 if a RESTART or FULL checkpoint was blocked from completing,
for example because another thread or process was actively
using the database. In other words, the first column is 0 if the
equivalent call to <a href="c3ref/wal_checkpoint_v2.html">sqlite3_wal_checkpoint_v2()</a> would have returned
<a href="rescode.html#ok">SQLITE_OK</a> or 1 if the equivalent call would have returned <a href="rescode.html#busy">SQLITE_BUSY</a>.
The second column is the number of modified pages that have been
written to the write-ahead log file.
The third column is the number of pages in the write-ahead log file
that have been successfully moved back into the database file at
the conclusion of the checkpoint.
The second and third column are -1 if there is no
write-ahead log, for example if this pragma is invoked on a database
connection that is not in <a href="wal.html">WAL mode</a>.</p>
<a name="pragma_writable_schema"></a>
<hr>
<p><b>PRAGMA writable_schema = </b><i>boolean</i><b>;</b></p>
<p>When this pragma is on, the SQLITE_MASTER tables in which database
can be changed using ordinary <a href="lang_update.html">UPDATE</a>, <a href="lang_insert.html">INSERT</a>, and <a href="lang_delete.html">DELETE</a>
statements. Warning: misuse of this pragma can easily result in
a corrupt database file.</p>
<hr>