summaryrefslogtreecommitdiffstats
path: root/www/pragma.html
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-13 14:07:11 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-13 14:07:11 +0000
commit63847496f14c813a5d80efd5b7de0f1294ffe1e3 (patch)
tree01c7571c7c762ceee70638549a99834fdd7c411b /www/pragma.html
parentInitial commit. (diff)
downloadsqlite3-63847496f14c813a5d80efd5b7de0f1294ffe1e3.tar.xz
sqlite3-63847496f14c813a5d80efd5b7de0f1294ffe1e3.zip
Adding upstream version 3.45.1.upstream/3.45.1
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'www/pragma.html')
-rw-r--r--www/pragma.html2282
1 files changed, 2282 insertions, 0 deletions
diff --git a/www/pragma.html b/www/pragma.html
new file mode 100644
index 0000000..8f4545c
--- /dev/null
+++ b/www/pragma.html
@@ -0,0 +1,2282 @@
+<!DOCTYPE html>
+<html><head>
+<meta name="viewport" content="width=device-width, initial-scale=1.0">
+<meta http-equiv="content-type" content="text/html; charset=UTF-8">
+<link href="sqlite.css" rel="stylesheet">
+<title>Pragma statements supported by SQLite</title>
+<!-- path= -->
+</head>
+<body>
+<div class=nosearch>
+<a href="index.html">
+<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
+</a>
+<div><!-- IE hack to prevent disappearing logo --></div>
+<div class="tagline desktoponly">
+Small. Fast. Reliable.<br>Choose any three.
+</div>
+<div class="menu mainmenu">
+<ul>
+<li><a href="index.html">Home</a>
+<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
+<li class='wideonly'><a href='about.html'>About</a>
+<li class='desktoponly'><a href="docs.html">Documentation</a>
+<li class='desktoponly'><a href="download.html">Download</a>
+<li class='wideonly'><a href='copyright.html'>License</a>
+<li class='desktoponly'><a href="support.html">Support</a>
+<li class='desktoponly'><a href="prosupport.html">Purchase</a>
+<li class='search' id='search_menubutton'>
+<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
+</ul>
+</div>
+<div class="menu submenu" id="submenu">
+<ul>
+<li><a href='about.html'>About</a>
+<li><a href='docs.html'>Documentation</a>
+<li><a href='download.html'>Download</a>
+<li><a href='support.html'>Support</a>
+<li><a href='prosupport.html'>Purchase</a>
+</ul>
+</div>
+<div class="searchmenu" id="searchmenu">
+<form method="GET" action="search">
+<select name="s" id="searchtype">
+<option value="d">Search Documentation</option>
+<option value="c">Search Changelog</option>
+</select>
+<input type="text" name="q" id="searchbox" value="">
+<input type="submit" value="Go">
+</form>
+</div>
+</div>
+<script>
+function toggle_div(nm) {
+var w = document.getElementById(nm);
+if( w.style.display=="block" ){
+w.style.display = "none";
+}else{
+w.style.display = "block";
+}
+}
+function toggle_search() {
+var w = document.getElementById("searchmenu");
+if( w.style.display=="block" ){
+w.style.display = "none";
+} else {
+w.style.display = "block";
+setTimeout(function(){
+document.getElementById("searchbox").focus()
+}, 30);
+}
+}
+function div_off(nm){document.getElementById(nm).style.display="none";}
+window.onbeforeunload = function(e){div_off("submenu");}
+/* Disable the Search feature if we are not operating from CGI, since */
+/* Search is accomplished using CGI and will not work without it. */
+if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
+document.getElementById("search_menubutton").style.display = "none";
+}
+/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
+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;
+}
+var antiRobot = 0;
+function antiRobotGo(){
+if( antiRobot!=3 ) return;
+antiRobot = 7;
+var j = document.getElementById("mtimelink");
+if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
+}
+function antiRobotDefense(){
+document.body.onmousedown=function(){
+antiRobot |= 2;
+antiRobotGo();
+document.body.onmousedown=null;
+}
+document.body.onmousemove=function(){
+antiRobot |= 2;
+antiRobotGo();
+document.body.onmousemove=null;
+}
+setTimeout(function(){
+antiRobot |= 1;
+antiRobotGo();
+}, 100)
+antiRobotGo();
+}
+antiRobotDefense();
+</script>
+
+<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>The pragma command is specific to SQLite and is
+ not compatible with any other SQL database engine.
+<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 <a href="lang_explain.html">EXPLAIN</a> and <a href="eqp.html">EXPLAIN QUERY PLAN</a> prefixes to SQL statements
+ only affect the behavior of the statement during <a href="c3ref/step.html">sqlite3_step()</a>.
+ That means that PRAGMA statements that take effect during
+ <a href="c3ref/prepare.html">sqlite3_prepare()</a> will behave the same way regardless of whether or
+ not they are prefaced by "EXPLAIN".
+</ul>
+
+<p>The C-language API for SQLite provides the <a href="c3ref/c_fcntl_begin_atomic_write.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='xd117fae3' onclick='hideorshow("xd117fae3","xa21fab4a")'>hide</button></p>
+ <div id='xa21fab4a' class='imgcontainer'>
+ <div style="max-width:824px"><svg xmlns='http://www.w3.org/2000/svg' class="pikchr" viewBox="0 0 824.352 99.576">
+<circle cx="5" cy="17" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<polygon points="32,17 20,21 20,12" style="fill:rgb(0,0,0)"/>
+<path d="M9,17L26,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<path d="M47,32L104,32A15 15 0 0 0 119 17A15 15 0 0 0 104 2L47,2A15 15 0 0 0 32 17A15 15 0 0 0 47 32Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<text x="75" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">PRAGMA</text>
+<polygon points="155,17 143,21 143,12" style="fill:rgb(0,0,0)"/>
+<path d="M119,17L149,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<path d="M170,32L266,32A15 15 0 0 0 282 17A15 15 0 0 0 266 2L170,2A15 15 0 0 0 155 17A15 15 0 0 0 170 32Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<text x="218" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">schema-name</text>
+<polygon points="305,17 293,21 293,12" style="fill:rgb(0,0,0)"/>
+<path d="M282,17L299,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<path d="M320,32A15 15 0 0 0 335 17A15 15 0 0 0 320 2A15 15 0 0 0 305 17A15 15 0 0 0 320 32Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<text x="320" y="17" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">.</text>
+<polygon points="371,17 359,21 359,12" style="fill:rgb(0,0,0)"/>
+<path d="M335,17L365,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<path d="M386,32L483,32A15 15 0 0 0 498 17A15 15 0 0 0 483 2L386,2A15 15 0 0 0 371 17A15 15 0 0 0 386 32Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<text x="435" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">pragma-name</text>
+<polygon points="540,82 528,86 528,77" style="fill:rgb(0,0,0)"/>
+<path d="M498,17 L 506,17 Q 513,17 513,32 L 513,67 Q 513,82 524,82 L 534,82" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<path d="M555,97A15 15 0 0 0 570 82A15 15 0 0 0 555 67A15 15 0 0 0 540 82A15 15 0 0 0 555 97Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<text x="555" y="82" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">(</text>
+<polygon points="593,82 582,86 582,77" style="fill:rgb(0,0,0)"/>
+<path d="M570,82L587,82" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<path d="M593,97L720,97L720,67L593,67Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<text x="656" y="82" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">pragma-value</text>
+<polygon points="743,82 731,86 731,77" style="fill:rgb(0,0,0)"/>
+<path d="M720,82L737,82" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<path d="M758,97A15 15 0 0 0 773 82A15 15 0 0 0 758 67A15 15 0 0 0 743 82A15 15 0 0 0 758 97Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<text x="758" y="82" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">)</text>
+<polygon points="814,17 803,21 803,12" style="fill:rgb(0,0,0)"/>
+<path d="M773,82 L 780,82 Q 788,82 788,67 L 788,32 Q 788,17 798,17 L 809,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<circle cx="818" cy="17" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<polygon points="540,44 528,48 528,40" style="fill:rgb(0,0,0)"/>
+<path d="M498,17 L 506,17 Q 513,17 513,30 Q 513,44 524,44 L 534,44" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<path d="M555,59A15 15 0 0 0 570 44L570,44A15 15 0 0 0 555 29A15 15 0 0 0 540 44L540,44A15 15 0 0 0 555 59Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<text x="555" y="44" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">=</text>
+<polygon points="593,44 582,48 582,40" style="fill:rgb(0,0,0)"/>
+<path d="M570,44L587,44" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<path d="M593,59L720,59L720,29L593,29Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<text x="656" y="44" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">pragma-value</text>
+<polygon points="773,44 761,48 761,40" style="fill:rgb(0,0,0)"/>
+<path d="M720,44L767,44" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<path d="M773,44 L 780,44 Q 788,44 788,36 L 788,29" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<polygon points="656,17 645,21 645,12" style="fill:rgb(0,0,0)"/>
+<path d="M498,17L651,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<path d="M656,17L803,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<polygon points="218,44 207,48 207,40" style="fill:rgb(0,0,0)"/>
+<path d="M119,17 L 126,17 Q 134,17 134,30 Q 134,44 149,44 L 198,44 L 213,44" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<path d="M218,44 L 335,44 Q 350,44 350,30 Q 350,17 357,17 L 365,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+</svg>
+</div>
+<p><b><a href="syntax/pragma-value.html">pragma-value:</a></b>
+<button id='xc2c54c29' onclick='hideorshow("xc2c54c29","xdbeedbf8")'>hide</button></p>
+ <div id='xdbeedbf8' class='imgcontainer'>
+ <div style="max-width:264px"><svg xmlns='http://www.w3.org/2000/svg' class="pikchr" viewBox="0 0 264.499 110.16">
+<circle cx="5" cy="17" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<polygon points="63,17 51,21 51,12" style="fill:rgb(0,0,0)"/>
+<path d="M9,17L57,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<path d="M63,32L201,32L201,2L63,2Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<text x="132" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">signed-number</text>
+<polygon points="255,17 243,21 243,12" style="fill:rgb(0,0,0)"/>
+<path d="M201,17L249,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<circle cx="258" cy="17" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<path d="M78,70L105,70A15 15 0 0 0 120 55L120,55A15 15 0 0 0 105 39L78,39A15 15 0 0 0 63 55L63,55A15 15 0 0 0 78 70Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<text x="92" y="55" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">name</text>
+<path d="M78,108L171,108A15 15 0 0 0 186 92A15 15 0 0 0 171 77L78,77A15 15 0 0 0 63 92A15 15 0 0 0 78 108Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<text x="125" y="92" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">signed-literal</text>
+<polygon points="63,92 51,97 51,88" style="fill:rgb(0,0,0)"/>
+<path d="M21,17 L 29,17 Q 36,17 36,32 L 36,77 Q 36,92 47,92 L 57,92" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<polygon points="201,92 189,97 189,88" style="fill:rgb(0,0,0)"/>
+<path d="M186,92L195,92" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<path d="M201,92 L 208,92 Q 216,92 216,77 L 216,32 Q 216,17 223,17 L 231,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<polygon points="201,55 189,59 189,50" style="fill:rgb(0,0,0)"/>
+<path d="M120,55L195,55" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<path d="M201,55 L 208,55 Q 216,55 216,47 L 216,40" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<polygon points="63,55 51,59 51,50" style="fill:rgb(0,0,0)"/>
+<path d="M36,39 L 36,47 Q 36,55 47,55 L 57,55" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+</svg>
+</div>
+<p><b><a href="syntax/signed-number.html">signed-number:</a></b>
+<button id='x8e288662' onclick='hideorshow("x8e288662","x58b8c58c")'>show</button></p>
+ <div id='x58b8c58c' style='display:none;' class='imgcontainer'>
+ <div style="max-width:292px"><svg xmlns='http://www.w3.org/2000/svg' class="pikchr" viewBox="0 0 292.013 99.576">
+<circle cx="5" cy="17" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<polygon points="50,44 39,48 39,40" style="fill:rgb(0,0,0)"/>
+<path d="M9,17 L 16,17 Q 24,17 24,30 Q 24,44 34,44 L 45,44" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<path d="M66,59A15 15 0 0 0 81 44L81,44A15 15 0 0 0 66 29A15 15 0 0 0 50 44L50,44A15 15 0 0 0 66 59Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<text x="66" y="44" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">+</text>
+<polygon points="122,17 111,21 111,12" style="fill:rgb(0,0,0)"/>
+<path d="M81,44 L 88,44 Q 96,44 96,30 Q 96,17 106,17 L 116,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<path d="M137,32L244,32A15 15 0 0 0 259 17A15 15 0 0 0 244 2L137,2A15 15 0 0 0 122 17A15 15 0 0 0 137 32Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<text x="191" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">numeric-literal</text>
+<polygon points="282,17 271,21 271,12" style="fill:rgb(0,0,0)"/>
+<path d="M259,17L276,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<circle cx="286" cy="17" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<polygon points="50,82 39,86 39,77" style="fill:rgb(0,0,0)"/>
+<path d="M9,17 L 16,17 Q 24,17 24,32 L 24,67 Q 24,82 34,82 L 45,82" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<path d="M66,97A15 15 0 0 0 81 82L81,82A15 15 0 0 0 66 67A15 15 0 0 0 50 82L50,82A15 15 0 0 0 66 97Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<text x="66" y="82" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">-</text>
+<path d="M81,82 L 88,82 Q 96,82 96,67 L 96,44 L 96,29" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<polygon points="66,17 54,21 54,12" style="fill:rgb(0,0,0)"/>
+<path d="M9,17L60,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+<path d="M66,17L111,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
+</svg>
+</div>
+</div>
+</div>
+</div>
+
+
+<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' &#91;FALSE&#93;</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 <span class='yyterm'>schema-name</span>
+before the pragma name.
+The <span class='yyterm'>schema-name</span> is the name of an <a href="lang_attach.html">ATTACH</a>-ed database
+or "main" or "temp" for the main and the TEMP databases. If the optional
+schema name is omitted, "main" is assumed. In some pragmas, the schema
+name is meaningless and is simply ignored. In the documentation below,
+pragmas for which the schema name is meaningful are shown with a
+"<i>schema.</i>" prefix.</p>
+
+
+<hr /><a name="pragfunc"></a>
+<h2>PRAGMA functions</h2>
+
+
+<p>
+PRAGMAs that return results and that have no side-effects can be
+accessed from ordinary <a href="lang_select.html">SELECT</a> statements as <a href="vtab.html#tabfunc2">table-valued functions</a>.
+For each participating PRAGMA, the corresponding table-valued function
+has the same name as the PRAGMA with a 7-character "pragma_" prefix.
+The PRAGMA argument and schema, if any, are passed as arguments to the
+table-valued function, with the schema as an optional, last argument.
+
+<p>For example, information about the columns in an index can be
+read using the <a href="pragma.html#pragma_index_info">index_info pragma</a> as follows:
+
+<blockquote><pre>
+PRAGMA index_info('idx52');
+</pre></blockquote>
+
+<p>Or, the same content can be read using:
+
+<blockquote><pre>
+SELECT * FROM pragma_index_info('idx52');
+</pre></blockquote>
+
+<p>The advantage of the table-valued function format is that the query
+can return just a subset of the PRAGMA columns, can include a WHERE clause,
+can use aggregate functions, and the table-valued function can be just
+one of several data sources in a join.
+For example, to get a list of all indexed columns in a schema, one
+could query:
+
+<blockquote><pre>
+SELECT DISTINCT m.name || '.' || ii.name AS 'indexed-columns'
+ FROM sqlite_schema AS m,
+ pragma_index_list(m.name) AS il,
+ pragma_index_info(il.name) AS ii
+ WHERE m.type='table'
+ ORDER BY 1;
+</pre></blockquote>
+
+<p>
+Additional notes:
+<ul>
+<li><p>
+Table-valued functions exist only for built-in PRAGMAs, not for PRAGMAs
+defined using the <a href="c3ref/c_fcntl_begin_atomic_write.html#sqlitefcntlpragma">SQLITE_FCNTL_PRAGMA</a> file control.
+<li><p>
+Table-valued functions exist only for PRAGMAs that return results and
+that have no side-effects.
+<li><p>
+This feature could be used to implement
+<a href="https://en.wikipedia.org/wiki/Information_schema">information schema</a>
+by first creating a separate schema using
+<blockquote><pre>
+<a href="lang_attach.html">ATTACH</a> ':memory:' AS 'information_schema';
+</pre></blockquote>
+Then creating
+<a href="lang_createview.html">VIEWs</a> in that schema that implement the official information schema
+tables using table-valued PRAGMA functions.
+<li><p>
+The table-valued functions for PRAGMA feature was added
+in SQLite version 3.16.0 (2017-01-02). Prior versions of SQLite
+cannot use this feature.
+</ul>
+
+<hr /><a name="toc"></a>
+<h2>List Of PRAGMAs</h2>
+<div class='columns' style='columns: 12em auto;'>
+<ul style='padding-top:0;'>
+<li><a href='pragma.html#pragma_analysis_limit'>analysis_limit</a></li>
+<li><a href='pragma.html#pragma_application_id'>application_id</a></li>
+<li><a href='pragma.html#pragma_auto_vacuum'>auto_vacuum</a></li>
+<li><a href='pragma.html#pragma_automatic_index'>automatic_index</a></li>
+<li><a href='pragma.html#pragma_busy_timeout'>busy_timeout</a></li>
+<li><a href='pragma.html#pragma_cache_size'>cache_size</a></li>
+<li><a href='pragma.html#pragma_cache_spill'>cache_spill</a></li>
+<li><a href='pragma.html#pragma_case_sensitive_like'><s>case_sensitive_like&sup1;</s></a></li>
+<li><a href='pragma.html#pragma_cell_size_check'>cell_size_check</a></li>
+<li><a href='pragma.html#pragma_checkpoint_fullfsync'>checkpoint_fullfsync</a></li>
+<li><a href='pragma.html#pragma_collation_list'>collation_list</a></li>
+<li><a href='pragma.html#pragma_compile_options'>compile_options</a></li>
+<li><a href='pragma.html#pragma_count_changes'><s>count_changes&sup1;</s></a></li>
+<li><a href='pragma.html#pragma_data_store_directory'><s>data_store_directory&sup1;</s></a></li>
+<li><a href='pragma.html#pragma_data_version'>data_version</a></li>
+<li><a href='pragma.html#pragma_database_list'>database_list</a></li>
+<li><a href='pragma.html#pragma_default_cache_size'><s>default_cache_size&sup1;</s></a></li>
+<li><a href='pragma.html#pragma_defer_foreign_keys'>defer_foreign_keys</a></li>
+<li><a href='pragma.html#pragma_empty_result_callbacks'><s>empty_result_callbacks&sup1;</s></a></li>
+<li><a href='pragma.html#pragma_encoding'>encoding</a></li>
+<li><a href='pragma.html#pragma_foreign_key_check'>foreign_key_check</a></li>
+<li><a href='pragma.html#pragma_foreign_key_list'>foreign_key_list</a></li>
+<li><a href='pragma.html#pragma_foreign_keys'>foreign_keys</a></li>
+<li><a href='pragma.html#pragma_freelist_count'>freelist_count</a></li>
+<li><a href='pragma.html#pragma_full_column_names'><s>full_column_names&sup1;</s></a></li>
+<li><a href='pragma.html#pragma_fullfsync'>fullfsync</a></li>
+<li><a href='pragma.html#pragma_function_list'>function_list</a></li>
+<li><a href='pragma.html#pragma_hard_heap_limit'>hard_heap_limit</a></li>
+<li><a href='pragma.html#pragma_ignore_check_constraints'>ignore_check_constraints</a></li>
+<li><a href='pragma.html#pragma_incremental_vacuum'>incremental_vacuum</a></li>
+<li><a href='pragma.html#pragma_index_info'>index_info</a></li>
+<li><a href='pragma.html#pragma_index_list'>index_list</a></li>
+<li><a href='pragma.html#pragma_index_xinfo'>index_xinfo</a></li>
+<li><a href='pragma.html#pragma_integrity_check'>integrity_check</a></li>
+<li><a href='pragma.html#pragma_journal_mode'>journal_mode</a></li>
+<li><a href='pragma.html#pragma_journal_size_limit'>journal_size_limit</a></li>
+<li><a href='pragma.html#pragma_legacy_alter_table'>legacy_alter_table</a></li>
+<li><a href='pragma.html#pragma_legacy_file_format'>legacy_file_format</a></li>
+<li><a href='pragma.html#pragma_locking_mode'>locking_mode</a></li>
+<li><a href='pragma.html#pragma_max_page_count'>max_page_count</a></li>
+<li><a href='pragma.html#pragma_mmap_size'>mmap_size</a></li>
+<li><a href='pragma.html#pragma_module_list'>module_list</a></li>
+<li><a href='pragma.html#pragma_optimize'>optimize</a></li>
+<li><a href='pragma.html#pragma_page_count'>page_count</a></li>
+<li><a href='pragma.html#pragma_page_size'>page_size</a></li>
+<li><a href='pragma.html#pragma_parser_trace'>parser_trace&sup2;</a></li>
+<li><a href='pragma.html#pragma_pragma_list'>pragma_list</a></li>
+<li><a href='pragma.html#pragma_query_only'>query_only</a></li>
+<li><a href='pragma.html#pragma_quick_check'>quick_check</a></li>
+<li><a href='pragma.html#pragma_read_uncommitted'>read_uncommitted</a></li>
+<li><a href='pragma.html#pragma_recursive_triggers'>recursive_triggers</a></li>
+<li><a href='pragma.html#pragma_reverse_unordered_selects'>reverse_unordered_selects</a></li>
+<li><a href='pragma.html#pragma_schema_version'>schema_version&sup3;</a></li>
+<li><a href='pragma.html#pragma_secure_delete'>secure_delete</a></li>
+<li><a href='pragma.html#pragma_short_column_names'><s>short_column_names&sup1;</s></a></li>
+<li><a href='pragma.html#pragma_shrink_memory'>shrink_memory</a></li>
+<li><a href='pragma.html#pragma_soft_heap_limit'>soft_heap_limit</a></li>
+<li><a href='pragma.html#pragma_stats'>stats&sup3;</a></li>
+<li><a href='pragma.html#pragma_synchronous'>synchronous</a></li>
+<li><a href='pragma.html#pragma_table_info'>table_info</a></li>
+<li><a href='pragma.html#pragma_table_list'>table_list</a></li>
+<li><a href='pragma.html#pragma_table_xinfo'>table_xinfo</a></li>
+<li><a href='pragma.html#pragma_temp_store'>temp_store</a></li>
+<li><a href='pragma.html#pragma_temp_store_directory'><s>temp_store_directory&sup1;</s></a></li>
+<li><a href='pragma.html#pragma_threads'>threads</a></li>
+<li><a href='pragma.html#pragma_trusted_schema'>trusted_schema</a></li>
+<li><a href='pragma.html#pragma_user_version'>user_version</a></li>
+<li><a href='pragma.html#pragma_vdbe_addoptrace'>vdbe_addoptrace&sup2;</a></li>
+<li><a href='pragma.html#pragma_vdbe_debug'>vdbe_debug&sup2;</a></li>
+<li><a href='pragma.html#pragma_vdbe_listing'>vdbe_listing&sup2;</a></li>
+<li><a href='pragma.html#pragma_vdbe_trace'>vdbe_trace&sup2;</a></li>
+<li><a href='pragma.html#pragma_wal_autocheckpoint'>wal_autocheckpoint</a></li>
+<li><a href='pragma.html#pragma_wal_checkpoint'>wal_checkpoint</a></li>
+<li><a href='pragma.html#pragma_writable_schema'>writable_schema&sup3;</a></li>
+</ul>
+</div>
+
+<p>Notes:
+<ol>
+<li>Pragmas whose names are <s>struck through</s>
+are deprecated. Do not use them. They exist
+for historical compatibility.
+<li>These pragmas are only available in builds using non-standard
+compile-time options.
+<li>These pragmas are used for testing SQLite and are not recommended
+for use in application programs.</ol></p>
+<a name="pragma_analysis_limit"></a>
+<h _id=pragma_analysis_limit style="display:none"> PRAGMA analysis_limit</h><hr>
+ <p><b>PRAGMA analysis_limit;
+ <br>PRAGMA analysis_limit = </b><i>N</i><b>;</b></p>
+ <p>Query or change a limit on the <a href="lang_analyze.html#approx">approximate ANALYZE</a> setting.
+ This is the approximate number of
+ rows examined in each index by the <a href="lang_analyze.html">ANALYZE</a> command.
+ If the argument <i>N</i> is omitted, then the analysis limit
+ is unchanged.
+ If the limit is zero, then the analysis limit is disabled and
+ the ANALYZE command will examine all rows of each index.
+ If N is greater than zero, then the analysis limit is set to N
+ and subsequent ANALYZE commands will stop analyzing
+ each index after it has examined approximately N rows.
+ If N is a negative number or something other than an integer value,
+ then the pragma behaves as if the N argument was omitted.
+ In all cases, the value returned is the new analysis limit used
+ for subsequent ANALYZE commands.
+ <p>This pragma can be used to help the ANALYZE command run faster
+ on large databases. The results of analysis are not as good
+ when only part of each index is examined, but the results are
+ usually good enough. Setting N to 100 or 1000 allows the
+ ANALYZE command to run very quickly, even on multi-gigabyte
+ database files. This pragma is particularly useful in combination
+ with <a href="pragma.html#pragma_optimize">PRAGMA optimize</a>.
+ <p>This pragma was added in SQLite version 3.32.0 (2020-05-22).
+ The current implementation only uses the lower 31 bits of the
+ N value - higher order bits are silently ignored. Future versions
+ of SQLite might begin using higher order bits.
+<a name="pragma_application_id"></a>
+<h _id=pragma_application_id style="display:none"> PRAGMA application_id</h><hr>
+ <p><b>PRAGMA </b><i>schema.</i><b>application_id;
+ <br>PRAGMA </b><i>schema.</i><b>application_id = </b><i>integer </i><b>;</b>
+
+ <p>The application_id PRAGMA is used to query or set the 32-bit
+ signed 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="https://www.sqlite.org/src/artifact?ci=trunk&filename=magic.txt">magic.txt</a> file in the SQLite source repository.
+
+<p> See also the <a href="pragma.html#pragma_user_version">user_version pragma</a>.
+<a name="pragma_auto_vacuum"></a>
+<h _id=pragma_auto_vacuum style="display:none"> PRAGMA auto_vacuum</h><hr>
+ <p><b>PRAGMA </b><i>schema.</i><b>auto_vacuum;<br>
+ PRAGMA </b><i>schema.</i><b>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>
+<h _id=pragma_automatic_index style="display:none"> PRAGMA automatic_index</h><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
+ <a href="releaselog/3_7_17.html">version 3.7.17</a> (2013-05-20),
+ but this might change in future releases of SQLite.
+<a name="pragma_busy_timeout"></a>
+<h _id=pragma_busy_timeout style="display:none"> PRAGMA busy_timeout</h><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>
+<h _id=pragma_cache_size style="display:none"> PRAGMA cache_size</h><hr>
+ <p><b>PRAGMA </b><i>schema.</i><b>cache_size;
+ <br>PRAGMA </b><i>schema.</i><b>cache_size = </b><i>pages</i><b>;
+ <br>PRAGMA </b><i>schema.</i><b>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, which means the cache size
+ is limited to 2048000 bytes of memory.
+ The default suggested cache size can be altered using the
+ <a href="compile.html#default_cache_size">SQLITE_DEFAULT_CACHE_SIZE</a> compile-time options.
+ The TEMP database has a default suggested cache size of 0 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 be a number of pages that would
+ use approximately abs(N*1024) bytes of memory based on the current
+ page size. SQLite remembers the number of pages in the page cache,
+ not the amount of memory used. So if you set the cache size using
+ a negative number and subsequently change the page size (using the
+ <a href="pragma.html#pragma_page_size">PRAGMA page_size</a> command) then the maximum amount of cache
+ memory will go up or down in proportion to the change in page size.
+
+ <p><i>Backwards compatibility note:</i>
+ The behavior of cache_size with a negative N
+ was different prior to <a href="releaselog/3_7_10.html">version 3.7.10</a> (2012-01-16). In
+ earlier versions, 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>
+
+ <p>The default page cache implemention does not allocate
+ the full amount of cache memory all at once. Cache memory
+ is allocated in smaller chunks on an as-needed basis. The page_cache
+ setting is a (suggested) upper bound on the amount of memory that the
+ cache can use, not the amount of memory it will use all of the time.
+ This is the behavior of the default page cache implementation, but an
+ <a href="c3ref/pcache_methods2.html">application defined page cache</a> is free
+ to behave differently if it wants.
+<a name="pragma_cache_spill"></a>
+<h _id=pragma_cache_spill style="display:none"> PRAGMA cache_spill</h><hr>
+ <p><b>PRAGMA cache_spill;
+ <br>PRAGMA cache_spill=</b><i>boolean</i><b>;
+ <br>PRAGMA </b><i>schema.</i><b>cache_spill=<i>N</i>;</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.
+ <p>The "PRAGMA cache_spill=<i>N</i>" form of this pragma sets a minimum
+ cache size threshold required for spilling to occur. The number of pages
+ in cache must exceed both the cache_spill threshold and the maximum cache
+ size set by the <a href="pragma.html#pragma_cache_size">PRAGMA cache_size</a> statement in order for spilling to
+ occur.
+ <p>The "PRAGMA cache_spill=<i>boolean</i>" form of this pragma applies
+ across all databases attached to the database connection. But the
+ "PRAGMA cache_spill=<i>N</i>" form of this statement only applies to
+ the "main" schema or whatever other schema is specified as part of the
+ statement.
+<a name="pragma_case_sensitive_like"></a>
+<h _id=pragma_case_sensitive_like style="display:none"> PRAGMA case_sensitive_like</h><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. This pragma
+ only changes the behavior of the SQL <a href="lang_expr.html#like">LIKE</a> operator. It does not
+ change the behavior of the <a href="c3ref/strlike.html">sqlite3_strlike()</a> C-language interface,
+ which is always case insensitive.</p>
+
+ <p><b>WARNING:</b> If a database uses the LIKE operator anywhere in
+ the schema, such as in a <a href="lang_createtable.html#ckconst">CHECK constraint</a> or in an
+ <a href="expridx.html">expression index</a> or in the WHERE clause of a <a href="partialindex.html">partial index</a>, then
+ changing the definition of the LIKE operator using this PRAGMA can
+ cause the database to appear to be corrupt. <a href="pragma.html#pragma_integrity_check">PRAGMA integrity_check</a>
+ will report errors. The database is not really corrupt in that
+ changing the behavior of LIKE back to the way
+ it was when the schema was defined and the database was populated
+ will clear the problem. If the use of LIKE occurs only in indexes,
+ then the problem can be cleared by running <a href="lang_reindex.html">REINDEX</a>. Nevertheless,
+ the use of the case_sensitive_like pragma is discouraged.</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_cell_size_check"></a>
+<h _id=pragma_cell_size_check style="display:none"> PRAGMA cell_size_check</h><hr>
+ <p><b>PRAGMA cell_size_check
+ <br>PRAGMA cell_size_check = </b><i>boolean</i><b>;</b></p>
+ <p>The cell_size_check pragma enables or disables additional sanity
+ checking on database b-tree pages as they are initially read from disk.
+ With cell size checking enabled, database corruption is detected earlier
+ and is less likely to "spread". However, there is a small performance
+ hit for doing the extra checks and so cell size checking is turned off
+ by default.
+<a name="pragma_checkpoint_fullfsync"></a>
+<h _id=pragma_checkpoint_fullfsync style="display:none"> PRAGMA checkpoint_fullfsync</h><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>
+<h _id=pragma_collation_list style="display:none"> PRAGMA collation_list</h><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>
+<h _id=pragma_compile_options style="display:none"> PRAGMA compile_options</h><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>
+<h _id=pragma_count_changes style="display:none"> PRAGMA count_changes</h><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, any changes made automatically
+ by <a href="foreignkeys.html#fk_actions">foreign key actions</a>, or updates caused by an <a href="lang_upsert.html">upsert</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>
+<h _id=pragma_data_store_directory style="display:none"> PRAGMA data_store_directory</h><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_data_version"></a>
+<h _id=pragma_data_version style="display:none"> PRAGMA data_version</h><hr>
+ <p><b>PRAGMA </b><i>schema.</i><b>data_version;</b></p>
+ <p>The "PRAGMA data_version" command provides an indication that the
+ database file has been modified.
+ Interactive programs that hold database content in memory or that
+ display database content on-screen can use the PRAGMA data_version
+ command to determine if they need to flush and reload their memory
+ or update the screen display.</p>
+
+ <p>The integer values returned by two
+ invocations of "PRAGMA data_version" from the same connection
+ will be different if changes were committed to the database
+ by any other connection in the interim.
+ The "PRAGMA data_version" value is unchanged for commits made
+ on the same database connection.
+ The behavior of "PRAGMA data_version" is the same for all database
+ connections, including database connections in separate processes
+ and <a href="sharedcache.html">shared cache</a> database connections.
+
+ <p>The "PRAGMA data_version" value is a local property of each
+ database connection and so values returned by two concurrent invocations
+ of "PRAGMA data_version" on separate database connections are
+ often different even though the underlying database is identical.
+ It is only meaningful to compare the "PRAGMA data_version" values
+ returned by the same database connection at two different points in
+ time.
+<a name="pragma_database_list"></a>
+<h _id=pragma_database_list style="display:none"> PRAGMA database_list</h><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 "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>
+<h _id=pragma_default_cache_size style="display:none"> PRAGMA default_cache_size</h><hr>
+ <b>PRAGMA </b><i>schema.</i><b>default_cache_size;
+ <br>PRAGMA </b><i>schema.</i><b>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>
+<h _id=pragma_defer_foreign_keys style="display:none"> PRAGMA defer_foreign_keys</h><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>
+<h _id=pragma_empty_result_callbacks style="display:none"> PRAGMA empty_result_callbacks</h><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>
+<h _id=pragma_encoding style="display:none"> PRAGMA encoding</h><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>If no encoding is first set with this pragma,
+ then the encoding with which the main database will be created
+ defaults to one determined by the
+ <a href="c3ref/open.html">API used to open the connection</a>.</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>
+<h _id=pragma_foreign_key_check style="display:none"> PRAGMA foreign_key_check</h><hr>
+ <p><b>PRAGMA </b><i>schema.</i><b>foreign_key_check;
+ <br>PRAGMA </b><i>schema.</i><b>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. The foreign_key_check
+ pragma returns one row output for each foreign key 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, or NULL if the child table is a
+ <a href="withoutrowid.html">WITHOUT ROWID</a> table. 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>
+<h _id=pragma_foreign_key_list style="display:none"> PRAGMA foreign_key_list</h><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>
+<h _id=pragma_foreign_keys style="display:none"> PRAGMA foreign_keys</h><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>
+<h _id=pragma_freelist_count style="display:none"> PRAGMA freelist_count</h><hr>
+ <p><b>PRAGMA </b><i>schema.</i><b>freelist_count;</b></p>
+ <p>Return the number of unused pages in the database file.</p>
+<a name="pragma_full_column_names"></a>
+<h _id=pragma_full_column_names style="display:none"> PRAGMA full_column_names</h><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>
+<h _id=pragma_fullfsync style="display:none"> PRAGMA fullfsync</h><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_function_list"></a>
+<h _id=pragma_function_list style="display:none"> PRAGMA function_list</h><hr>
+ <p><b>PRAGMA function_list;</b>
+ <p>This pragma returns a list of SQL functions
+ known to the database connection. Each row of the result
+ describes a single calling signature for a single SQL function.
+ Some SQL functions will have multiple rows in the result set
+ if they can (for example) be invoked with a varying number of
+ arguments or can accept text in various encodings.
+<a name="pragma_hard_heap_limit"></a>
+<h _id=pragma_hard_heap_limit style="display:none"> PRAGMA hard_heap_limit</h><hr>
+ <p><b>PRAGMA hard_heap_limit<br>
+ PRAGMA hard_heap_limit=</b><i>N</i></p>
+
+ <p>This pragma invokes the <a href="c3ref/hard_heap_limit64.html">sqlite3_hard_heap_limit64()</a> interface with
+ the argument N, if N is specified and N is a positive integer that
+ is less than the current hard heap limit.
+ The hard_heap_limit pragma always returns the same integer
+ that would be returned by the <a href="c3ref/hard_heap_limit64.html">sqlite3_hard_heap_limit64</a>(-1) C-language
+ function. That is to say, it always returns the value of the hard
+ heap limit that is set after any changes imposed by this PRAGMA.
+ </p>
+
+ <p>This pragma can only lower the heap limit, never raise it.
+ The C-language interface <a href="c3ref/hard_heap_limit64.html">sqlite3_hard_heap_limit64()</a> must be used
+ to raise the heap limit.</p>
+
+ <p>See also the <a href="pragma.html#pragma_soft_heap_limit">soft_heap_limit pragma</a>.
+<a name="pragma_ignore_check_constraints"></a>
+<h _id=pragma_ignore_check_constraints style="display:none"> PRAGMA ignore_check_constraints</h><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>
+<h _id=pragma_incremental_vacuum style="display:none"> PRAGMA incremental_vacuum</h><hr>
+ <p><b>PRAGMA </b><i>schema.</i><b>incremental_vacuum</b><i>(N)</i><b>;<br>
+ PRAGMA </b><i>schema.</i><b>incremental_vacuum;</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 the "(<i>N</i>)" argument is omitted, then the entire
+ freelist is cleared.</p>
+<a name="pragma_index_info"></a>
+<h _id=pragma_index_info style="display:none"> PRAGMA index_info</h><hr>
+ <p><b>PRAGMA </b><i>schema.</i><b>index_info(</b><i>index-name</i><b>);</b></p>
+ <p>This pragma returns one row for each key column in the named index.
+ A key column is a column that is actually named in the <a href="lang_createindex.html">CREATE INDEX</a>
+ index statement or <a href="lang_createtable.html#uniqueconst">UNIQUE constraint</a> or <a href="lang_createtable.html#primkeyconst">PRIMARY KEY constraint</a> that
+ created the index. Index entries also usually contain auxiliary
+ columns that point back to the table row being indexed. The auxiliary
+ index-columns are not shown by the index_info pragma, but they are
+ listed by the <a href="pragma.html#pragma_index_xinfo">index_xinfo pragma</a>.</p>
+
+ <p>Output columns from the index_info pragma are as follows:
+ <ol>
+ <li>The rank of the column within the index. (0 means left-most.)
+ <li>The rank of the column within the table being indexed.
+ A value of -1 means <a href="lang_createtable.html#rowid">rowid</a> and a value of -2 means that an
+ <a href="expridx.html">expression</a> is being used.
+ <li>The name of the column being indexed. This columns is NULL
+ if the column is the <a href="lang_createtable.html#rowid">rowid</a> or an <a href="expridx.html">expression</a>.
+ </ol>
+
+ <p>If there is no index named <i>index-name</i> but there is a
+ <a href="withoutrowid.html">WITHOUT ROWID</a> table with that name, then (as of
+ SQLite <a href="releaselog/3_30_0.html">version 3.30.0</a> on 2019-10-04) this pragma returns the
+ PRIMARY KEY columns of the WITHOUT ROWID table as they are used
+ in the records of the underlying b-tree, which is to say with
+ duplicate columns removed.
+<a name="pragma_index_list"></a>
+<h _id=pragma_index_list style="display:none"> PRAGMA index_list</h><hr>
+ <p><b>PRAGMA </b><i>schema.</i><b>index_list(</b><i>table-name</i><b>);</b></p>
+ <p>This pragma returns one row for each index associated with the
+ given table.
+ <p>Output columns from the index_list pragma are as follows:
+ <ol>
+ <li>A sequence number assigned to each index for internal tracking
+ purposes.
+ <li>The name of the index.
+ <li>"1" if the index is UNIQUE and "0" if not.
+ <li>"c" if the index was created by a <a href="lang_createindex.html">CREATE INDEX</a> statement,
+ "u" if the index was created by a <a href="lang_createtable.html#uniqueconst">UNIQUE constraint</a>, or
+ "pk" if the index was created by a <a href="lang_createtable.html#primkeyconst">PRIMARY KEY constraint</a>.
+ <li>"1" if the index is a <a href="partialindex.html">partial index</a> and "0" if not.
+ </ol>
+ </p>
+<a name="pragma_index_xinfo"></a>
+<h _id=pragma_index_xinfo style="display:none"> PRAGMA index_xinfo</h><hr>
+ <p><b>PRAGMA </b><i>schema.</i><b>index_xinfo(</b><i>index-name</i><b>);</b></p>
+ <p>This pragma returns information about every column in an index.
+ Unlike this <a href="pragma.html#pragma_index_info">index_info pragma</a>, this pragma returns information about
+ every column in the index, not just the key columns.
+ (A key column is a column that is actually named in the <a href="lang_createindex.html">CREATE INDEX</a>
+ index statement or <a href="lang_createtable.html#uniqueconst">UNIQUE constraint</a> or <a href="lang_createtable.html#primkeyconst">PRIMARY KEY constraint</a> that
+ created the index. Auxiliary columns are additional columns needed to
+ locate the table entry that corresponds to each index entry.)
+
+ <p>Output columns from the index_xinfo pragma are as follows:
+ <ol>
+ <li>The rank of the column within the index. (0 means left-most.
+ Key columns come before auxiliary columns.)
+ <li>The rank of the column within the table being indexed, or -1 if
+ the index-column is the <a href="lang_createtable.html#rowid">rowid</a> of the table being indexed and -2
+ if the <a href="expridx.html">index is on an expression</a>.
+ <li>The name of the column being indexed, or NULL if the index-column
+ is the <a href="lang_createtable.html#rowid">rowid</a> of the table being indexed or an
+ <a href="expridx.html">expression</a>.
+ <li>1 if the index-column is sorted in reverse (DESC) order by the
+ index and 0 otherwise.
+ <li>The name for the <a href="datatype3.html#collation">collating sequence</a>
+ used to compare values in the index-column.
+ <li>1 if the index-column is a key column and 0 if the index-column
+ is an auxiliary column.
+ </ol>
+
+ <p>If there is no index named <i>index-name</i> but there is a
+ <a href="withoutrowid.html">WITHOUT ROWID</a> table with that name, then (as of
+ SQLite <a href="releaselog/3_30_0.html">version 3.30.0</a> on 2019-10-04) this pragma returns the
+ columns of the WITHOUT ROWID table as they are used
+ in the records of the underlying b-tree, which is to say with
+ de-duplicated PRIMARY KEY columns first followed by data columns.
+<a name="pragma_integrity_check"></a>
+<h _id=pragma_integrity_check style="display:none"> PRAGMA integrity_check</h><hr>
+ <p><b>PRAGMA </b><i>schema.</i><b>integrity_check;
+ <br>PRAGMA </b><i>schema.</i><b>integrity_check(</b><i>N</i><b>)
+ <br>PRAGMA </b><i>schema.</i><b>integrity_check(</b><i>TABLENAME</i><b>)</b></p>
+ <p>This pragma does a low-level formatting and consistency check
+ of the database. The integrity_check pragma look for:
+ <ul>
+ <li> Table or index entries that are out of sequence
+ <li> Misformatted records
+ <li> Missing pages
+ <li> Missing or surplus index entries
+ <li> UNIQUE, CHECK, and NOT NULL constraint errors
+ <li> Integrity of the freelist
+ <li> Sections of the database that are used more than once, or not at all
+ </ul>
+ <p>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>The usual case is that the entire database file is checked. However,
+ if the argument is <i>TABLENAME</i>, then checking is only performed for the
+ the table named and its associated indexes.
+ This is called a "partial integrity check". Because only a subset of the
+ database is checked, errors such as unused sections of the file or duplication
+ use of the same section of the file by two or more tables cannot be detected.
+ The freelist is only verified on a
+ partial integrity check if <i>TABLENAME</i> is <a href="schematab.html">sqlite_schema</a> or one of its
+ aliases. Support for partial integrity checks was added with
+ version 3.33.0 (2020-08-14).
+
+ <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 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>
+<h _id=pragma_journal_mode style="display:none"> PRAGMA journal_mode</h><hr>
+ <p><b>PRAGMA </b><i>schema.</i><b>journal_mode;
+ <br>PRAGMA </b><i>schema.</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 form of this pragma queries the current journaling
+ mode for <i>database</i>. When <i>database</i> is omitted, the
+ "main" database is queried.</p>
+
+ <p>The second form changes the journaling mode for "<i>database</i>"
+ or for all attached databases if "<i>database</i>" is omitted.
+ 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
+ <a href="howtocorrupt.html#cfgerr">go corrupt</a>.</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 <a href="releaselog/3_7_0.html">version 3.7.0</a> (2010-07-21)
+ 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
+ <a href="howtocorrupt.html#cfgerr">go corrupt</a>. Without a journal, there is no way for
+ a statement to unwind partially completed operations following
+ a constraint error. This might also leave the database in a corrupted
+ state. For example, if a duplicate entry causes a
+ <a href="lang_createindex.html">CREATE UNIQUE INDEX</a> statement to fail half-way through,
+ it will leave behind a partially created, and hence corrupt, index.
+ Because OFF journaling
+ mode allows the database file to be corrupted using ordinary SQL,
+ it is disabled when <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigdefensive">SQLITE_DBCONFIG_DEFENSIVE</a> is enabled.</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>
+<h _id=pragma_journal_size_limit style="display:none"> PRAGMA journal_size_limit</h><hr>
+ <p><b>
+ PRAGMA </b><i>schema.</i><b>journal_size_limit<br>
+ PRAGMA </b><i>schema.</i><b>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_alter_table"></a>
+<h _id=pragma_legacy_alter_table style="display:none"> PRAGMA legacy_alter_table</h><hr>
+ <p><b>PRAGMA legacy_alter_table;
+ <br>PRAGMA legacy_alter_table = <i>boolean</i></b></p>
+ <p>This pragma sets or queries the value of the legacy_alter_table
+ flag. When this flag is on, the <a href="lang_altertable.html#altertabrename">ALTER TABLE RENAME</a>
+ command (for changing the name of a table) works as it did
+ in SQLite 3.24.0 (2018-06-04) and earlier. More specifically,
+ when this flag is on
+ the <a href="lang_altertable.html#altertabrename">ALTER TABLE RENAME</a> command only rewrites the initial occurrence
+ of the table name in its <a href="lang_createtable.html">CREATE TABLE</a> statement and in any associated
+ <a href="lang_createindex.html">CREATE INDEX</a> and <a href="lang_createtrigger.html">CREATE TRIGGER</a> statements. Other references to the
+ table are unmodified, including:
+ <ul>
+ <li> References to the table within the bodies of triggers and views.
+ <li> References to the table within CHECK constraints in the original
+ CREATE TABLE statement.
+ <li> References to the table within the WHERE clauses of <a href="partialindex.html">partial indexes</a>.
+ </ul>
+ The default setting for this pragma is OFF, which means that all
+ references to the table anywhere in the schema are converted to the new name.
+ <p>This pragma is provided as a work-around for older programs that
+ contain code that expect the incomplete behavior
+ of <a href="lang_altertable.html#altertabrename">ALTER TABLE RENAME</a> found in older versions of SQLite.
+ New applications should leave this flag turned off.
+ <p>For compatibility with older <a href="vtab.html">virtual table</a> implementations,
+ this flag is turned on temporarily while the <a href="vtab.html#xrename">sqlite3_module.xRename</a>
+ method is being run. The value of this flag is restored after the
+ <a href="vtab.html#xrename">sqlite3_module.xRename</a> method finishes.
+ <p>The legacy alter table behavior can also be toggled on and off
+ using the <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfiglegacyaltertable">SQLITE_DBCONFIG_LEGACY_ALTER_TABLE</a> option to the
+ <a href="c3ref/db_config.html">sqlite3_db_config()</a> interface.
+ <p>The legacy alter table behavior is a per-connection setting. Turning
+ this features on or off affects all attached database files within the
+ <a href="c3ref/sqlite3.html">database connection</a>.
+ The setting does not persist. Changing this setting in one connection
+ does not affect any other connections.
+<a name="pragma_legacy_file_format"></a>
+<h _id=pragma_legacy_file_format style="display:none"> PRAGMA legacy_file_format</h><hr>
+ <p><b>PRAGMA legacy_file_format;</b>
+ <p>This pragma no longer functions. It has become a no-op.
+ The capabilities formerly provided by PRAGMA legacy_file_format
+ are now available using the <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfiglegacyfileformat">SQLITE_DBCONFIG_LEGACY_FILE_FORMAT</a>
+ option to the <a href="c3ref/db_config.html">sqlite3_db_config()</a> C-language interface.
+ <p>
+<a name="pragma_locking_mode"></a>
+<h _id=pragma_locking_mode style="display:none"> PRAGMA locking_mode</h><hr>
+ <p><b>PRAGMA </b><i>schema.</i><b>locking_mode;
+ <br>PRAGMA </b><i>schema.</i><b>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>
+<h _id=pragma_max_page_count style="display:none"> PRAGMA max_page_count</h><hr>
+ <p><b>PRAGMA </b><i>schema.</i><b>max_page_count;
+ <br>PRAGMA </b><i>schema.</i><b>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>
+<h _id=pragma_mmap_size style="display:none"> PRAGMA mmap_size</h><hr>
+ <p><br><b>PRAGMA </b><i>schema.</i><b>mmap_size;
+ <br>PRAGMA </b><i>schema.</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 at startup-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_module_list"></a>
+<h _id=pragma_module_list style="display:none"> PRAGMA module_list</h><hr>
+ <p><b>PRAGMA module_list;</b>
+ <p>This pragma returns a list of
+ <a href="vtab.html">virtual table</a> modules registered with the database connection.
+<a name="pragma_optimize"></a>
+<h _id=pragma_optimize style="display:none"> PRAGMA optimize</h><hr>
+ <p><b>PRAGMA optimize;
+ <br>PRAGMA optimize(</b><i>MASK</i><b>);
+ <br>PRAGMA </b><i>schema</i><b>.optimize;
+ <br>PRAGMA </b><i>schema</i><b>.optimize(</b><i>MASK</i><b>);</b></p>
+ <p>Attempt to optimize the database. All schemas are optimized in the
+ first two forms, and only the specified schema is optimized in the latter
+ two.</p>
+ <p>To achieve the best long-term query performance without the need to
+ do a detailed engineering analysis of the application schema and SQL,
+ it is recommended that applications run "PRAGMA optimize" (with no arguments)
+ just before closing each <a href="c3ref/sqlite3.html">database connection</a>. Long-running applications
+ might also benefit from setting a timer to run "PRAGMA optimize" every
+ few hours.
+ </p>
+ <p>This pragma is usually a no-op or nearly so and is very fast.
+ However if SQLite feels
+ that performing database optimizations (such as running <a href="lang_analyze.html">ANALYZE</a>
+ or creating new indexes) will improve the performance of future queries, then
+ some database I/O may be done. Applications that want to limit the amount
+ of work performed can set a timer that will invoke
+ <a href="c3ref/interrupt.html">sqlite3_interrupt()</a> if the pragma goes on for too long.
+ Or, since SQLite 3.32.0, the application can use
+ <a href="pragma.html#pragma_analysis_limit">PRAGMA analysis_limit=<i>N</i></a> for some small
+ value of <i>N</i> (a few hundred or a few thousand) to limit the depth
+ of analyze.
+ </p>
+ <p>The details of optimizations performed by this pragma are expected
+ to change and improve over time. Applications should anticipate that
+ this pragma will perform new optimizations in future releases.</p>
+
+ <p>The optional MASK argument is a bitmask of optimizations to perform:
+ <ol>
+ <li value='1'><p>
+ Debugging mode. Do not actually perform any optimizations
+ but instead return one line of text for each optimization
+ that would have been done. Off by default.
+ <li value='2'><p>
+ Run <a href="lang_analyze.html">ANALYZE</a> on tables that might benefit. On by default.
+ See below for additional information.
+ <li value='4'><p>
+ <em>(Not yet implemented)</em>
+ Record usage and performance
+ information from the current session in the
+ database file so that it will be available to "optimize"
+ pragmas run by future database connections.
+ <li value='8'><p>
+ <em>(Not yet implemented)</em>
+ Create indexes that might have been helpful to recent queries.
+ </ol>
+ <p>The default MASK is and always shall be 0xfffe. The 0xfffe mask means
+ perform all of the optimizations listed above except Debug Mode. If new
+ optimizations are added in the future that should be off by default, those
+ new optimizations will be given a mask of 0x10000 or larger.</p>
+
+ <p>To see all optimizations that would have been done without actually
+ doing them, run "PRAGMA optimize(-1)". To use only the ANALYZE
+ optimization, run "PRAGMA optimize(0x02)".</p>
+
+ <p><b>Determination Of When To Run Analyze</b></p>
+ <p> In the current implementation, a table is analyzed if and only if
+ all of the following are true:
+ <ul>
+ <li><p>
+ MASK bit 0x02 is set.
+ <li><p>
+ The query planner used <a href="fileformat2.html#stat1tab">sqlite_stat1</a>-style statistics for one or
+ more indexes of the table at some point during the lifetime of
+ the current connection.
+ <li><p>
+ One or more indexes of the table are currently unanalyzed <em>or</em>
+ the number of rows in the table has increased by 25 times or more
+ since the last time ANALYZE was run.
+ </ul>
+ <p> The rules for when tables are analyzed are likely to change in
+ future releases.
+<a name="pragma_page_count"></a>
+<h _id=pragma_page_count style="display:none"> PRAGMA page_count</h><hr>
+ <p><b>PRAGMA </b><i>schema.</i><b>page_count;</b></p>
+ <p>Return the total number of pages in the database file.</p>
+<a name="pragma_page_size"></a>
+<h _id=pragma_page_size style="display:none"> PRAGMA page_size</h><hr>
+ <p><b>PRAGMA </b><i>schema.</i><b>page_size;
+ <br>PRAGMA </b><i>schema.</i><b>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 assigns a page size to
+ the database based on platform and filesystem. For many years,
+ the default page size was almost always 1024 bytes, but beginning
+ with SQLite <a href="releaselog/3_12_0.html">version 3.12.0</a> (2016-03-29),
+ the default page size increased to 4096.
+ The default page size is recommended for most applications.
+
+ <p>Specifying a new page size does not change the page size
+ immediately. Instead, the new page size is remembered and is used
+ to set the page size when the database is first created, if it does
+ not already exist when the page_size pragma is issued, or at the
+ next <a href="lang_vacuum.html">VACUUM</a> command that is run on the same database connection
+ while not in <a href="wal.html">WAL mode</a>.</p>
+
+ <p>The <a href="compile.html#default_page_size">SQLITE_DEFAULT_PAGE_SIZE</a> compile-time option can be used
+ to change the default page size assigned to new databases.
+<a name="pragma_parser_trace"></a>
+<h _id=pragma_parser_trace style="display:none"> PRAGMA parser_trace</h><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_pragma_list"></a>
+<h _id=pragma_pragma_list style="display:none"> PRAGMA pragma_list</h><hr>
+ <p><b>PRAGMA pragma_list;</b>
+ <p>This pragma returns a list of PRAGMA commands
+ known to the database connection.
+<a name="pragma_query_only"></a>
+<h _id=pragma_query_only style="display:none"> PRAGMA query_only</h><hr>
+ <p><b>PRAGMA query_only;
+ <br>PRAGMA query_only = </b><i>boolean</i><b>;</b></p>
+
+ <p>The query_only pragma prevents data changes on database files when
+ enabled. When this pragma is enabled, any attempt to CREATE, DELETE,
+ DROP, INSERT, or UPDATE will result in an <a href="rescode.html#readonly">SQLITE_READONLY</a> error.
+ However, the database is not truly read-only. You can still run
+ a <a href="wal.html#ckpt">checkpoint</a> or a <a href="lang_transaction.html">COMMIT</a> and the return value of the
+ <a href="c3ref/db_readonly.html">sqlite3_db_readonly()</a> routine is not affected.
+ </p>
+<a name="pragma_quick_check"></a>
+<h _id=pragma_quick_check style="display:none"> PRAGMA quick_check</h><hr>
+ <p><b>PRAGMA </b><i>schema.</i><b>quick_check;
+ <br>PRAGMA </b><i>schema.</i><b>quick_check(</b><i>N</i><b>)</b>
+ <br>PRAGMA </b><i>schema.</i><b>quick_check(</b><i>TABLENAME</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 constraints and does not verify
+ that index content matches table content. By skipping UNIQUE
+ and index consistency checks, quick_check is able to run faster.
+ PRAGMA quick_check runs in O(N) time whereas <a href="pragma.html#pragma_integrity_check">PRAGMA integrity_check</a>
+ requires O(NlogN) time where N is the total number of rows in the
+ database. Otherwise the two pragmas are the same.
+ </p>
+<a name="pragma_read_uncommitted"></a>
+<h _id=pragma_read_uncommitted style="display:none"> PRAGMA read_uncommitted</h><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>
+<h _id=pragma_recursive_triggers style="display:none"> PRAGMA recursive_triggers</h><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 <a href="releaselog/3_6_18.html">version 3.6.18</a> (2009-09-11),
+ 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>
+<h _id=pragma_reverse_unordered_selects style="display:none"> PRAGMA reverse_unordered_selects</h><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 many <a href="lang_select.html">SELECT</a> statements without
+ an ORDER BY clause to emit their results in the reverse order from what
+ they normally would. This can help debug applications that are
+ making invalid assumptions about the result order.
+ The reverse_unordered_selects pragma works for most SELECT statements,
+ however the query planner may sometimes choose an algorithm that is
+ not easily reversed, in which case the output will appear in the same
+ order regardless of the reverse_unordered_selects setting.
+ <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>
+<h _id=pragma_schema_version style="display:none"> PRAGMA schema_version</h><hr>
+ <p><b>PRAGMA </b><i>schema.</i><b>schema_version;
+ <br>PRAGMA </b><i>schema.</i><b>schema_version = </b><i>integer </i>;
+
+<p> The schema_version pragma will get or set
+ the value of the schema-version integer at offset 40 in the
+ <a href="fileformat2.html#database_header">database header</a>.
+
+<p> SQLite automatically increments the schema-version whenever the
+ schema changes. As each SQL statement runs, the schema version is
+ checked to ensure that the schema has not changed since the SQL
+ statement was <a href="c3ref/prepare.html">prepared</a>.
+ Subverting this mechanism by using "PRAGMA schema_version=N"
+ to change the value of the schema_version
+ may cause SQL statement to run using an obsolete schema,
+ which can lead to incorrect answers and/or
+ <a href="howtocorrupt.html#cfgerr">database corruption</a>.
+ It is always safe to read the schema_version, but changing the
+ schema_version can cause problems. For this reason, attempts
+ to change the value of schema_version are a silent no-op when
+ <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigdefensive">defensive mode</a> is enabled for a
+ database connection.
+
+
+ <p><span style='background-color: #ffff60;'>
+ <b>Warning:</b>
+ Misuse of this pragma can result in <a href="howtocorrupt.html#cfgerr">database corruption</a>.
+ </span></p>
+
+
+<p> For the purposes of this pragma, the <a href="lang_vacuum.html">VACUUM</a> command is considered
+ a schema change, since <a href="lang_vacuum.html">VACUUM</a> will usually alter the "rootpage"
+ values for entries in the <a href="schematab.html">sqlite_schema table</a>.
+
+
+<p> See also the <a href="pragma.html#pragma_application_id">application_id pragma</a> and <a href="pragma.html#pragma_user_version">user_version pragma</a>.
+<a name="pragma_secure_delete"></a>
+<h _id=pragma_secure_delete style="display:none"> PRAGMA secure_delete</h><hr>
+ <p><b>PRAGMA </b><i>schema.</i><b>secure_delete;
+ <br>PRAGMA </b><i>schema.</i><b>secure_delete = </b><i>boolean</i>|<b>FAST</b></p>
+ <p>Query or change the secure-delete setting. When secure_delete is
+ on, SQLite overwrites deleted content with zeros. The default
+ setting for secure_delete is determined by the <a href="compile.html#secure_delete">SQLITE_SECURE_DELETE</a>
+ compile-time option and is normally off. The off setting for
+ secure_delete improves performance by reducing the number of CPU cycles
+ and the amount of disk I/O. Applications that wish to avoid leaving
+ forensic traces after content is deleted or updated should enable the
+ secure_delete pragma prior to performing the delete or update, or else
+ run <a href="lang_vacuum.html">VACUUM</a> after the delete or update.
+
+ <p>The "fast" setting for secure_delete (added circa 2017-08-01)
+ is an intermediate setting in between "on" and "off".
+ When secure_delete is set to "fast",
+ SQLite will overwrite deleted content with zeros only if doing so
+ does not increase the amount of I/O. In other words, the "fast"
+ setting uses more CPU cycles but does not use more I/O.
+ This has the effect of purging all old content from <a href="fileformat2.html#btree">b-tree pages</a>,
+ but leaving forensic traces on <a href="fileformat2.html#freelist">freelist pages</a>.
+
+ <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>
+
+ <p><b>Limitation:</b>
+ The secure_delete pragma only causes deleted content to be scrubbed
+ from ordinary tables. If <a href="vtab.html">virtual tables</a> store content in
+ <a href="vtab.html#xshadowname">shadow tables</a>, then deleting content from the virtual table does
+ not necessarily remove forensic traces from the shadow tables.
+ In particular, the <a href="fts3.html">FTS3</a> and <a href="fts5.html">FTS5</a> virtual tables that come
+ bundled with SQLite might leave forensic traces in their shadow tables
+ even if the secure_delete pragma is enabled.
+ </p>
+<a name="pragma_short_column_names"></a>
+<h _id=pragma_short_column_names style="display:none"> PRAGMA short_column_names</h><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>
+<h _id=pragma_shrink_memory style="display:none"> PRAGMA shrink_memory</h><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>
+<h _id=pragma_soft_heap_limit style="display:none"> PRAGMA soft_heap_limit</h><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/hard_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/hard_heap_limit64.html">sqlite3_soft_heap_limit64</a>(-1) C-language
+ function.
+ </p>
+ <p>See also the <a href="pragma.html#pragma_hard_heap_limit">hard_heap_limit pragma</a>.
+<a name="pragma_stats"></a>
+<h _id=pragma_stats style="display:none"> PRAGMA stats</h><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 one 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>
+<h _id=pragma_synchronous style="display:none"> PRAGMA synchronous</h><hr>
+ <p><b>PRAGMA </b><i>schema.</i><b>synchronous;
+ <br>PRAGMA </b><i>schema.</i><b>synchronous = </b>
+ <i>0 | OFF | 1 | NORMAL | 2 | FULL | 3 | EXTRA</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. The second form changes the synchronous setting.
+ The meanings of the various synchronous settings are as follows:</p>
+ <dl>
+ <dt><b>EXTRA</b> (3)</dt>
+ <dd>
+ EXTRA synchronous is like FULL with the addition that the directory
+ containing a <a href="lockingv3.html#rollback">rollback journal</a> is synced after that journal is unlinked
+ to commit a transaction in DELETE mode. EXTRA provides additional
+ durability if the commit is followed closely by a power loss.</dd>
+ <dt><b>FULL</b> (2)</dt>
+ <dd>
+ 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. FULL is the
+ most commonly used synchronous setting when not in <a href="wal.html">WAL mode</a>.</dd>
+ <dt><b>NORMAL</b> (1)</dt>
+ <dd>
+ 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
+ <a href="pragma.html#pragma_journal_mode">journal_mode</a>=DELETE on an older filesystem.
+ <a href="wal.html">WAL mode</a> is safe from corruption with synchronous=NORMAL, and probably
+ DELETE mode is safe too on modern filesystems. WAL mode is always consistent
+ with synchronous=NORMAL, but WAL mode does lose durability. A transaction
+ committed in WAL mode with synchronous=NORMAL might roll back following
+ a power loss or system crash. Transactions are durable across application
+ crashes regardless of the synchronous setting or journal mode.
+ The synchronous=NORMAL setting is a good choice for most applications
+ running in <a href="wal.html">WAL mode</a>.</dd>
+ <dt><b>OFF</b> (0)</dt>
+ <dd>
+ 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 <a href="howtocorrupt.html#cfgerr">might become corrupted</a> if the operating system
+ crashes or the computer loses power before that data has been written
+ to the disk surface. On the other hand, commits can be orders of
+ magnitude faster with synchronous OFF.
+ </dd></dl>
+ </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 helps ensure that
+ transactions are durable across a power loss. Transactions are
+ consistent with or without the extra syncs provided by
+ synchronous=FULL.
+ If durability is not a concern, then synchronous=NORMAL is normally
+ all one needs in WAL mode.</p>
+
+ <p>The TEMP schema always has synchronous=OFF since the content of
+ of TEMP is ephemeral and is not expected to survive a power outage.
+ Attempts to change the synchronous setting for TEMP are
+ silently ignored.
+
+ <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>
+<h _id=pragma_table_info style="display:none"> PRAGMA table_info</h><hr>
+ <p><b>PRAGMA </b><i>schema.</i><b>table_info(</b><i>table-name</i><b>);</b></p>
+ <p>This pragma returns one row for each normal column
+ in the named table.
+ Columns in the result set include: "name" (its name); "type"
+ (data type if given, else ''); "notnull" (whether or not the column
+ can be NULL); "dflt_value" (the default value for the column);
+ and "pk" (either zero for columns that are not part of the primary key,
+ or the 1-based index of the column within the primary key).</p>
+ <p>The "cid" column should not be taken to mean more than
+ "rank within the current result set".</p>
+ <p>The table named in the table_info pragma can also be a view.</p>
+ <p>This pragma does not show information about <a href="gencol.html">generated columns</a> or
+ <a href="vtab.html#hiddencol">hidden columns</a>. Use <a href="pragma.html#pragma_table_xinfo">PRAGMA table_xinfo</a> to get a more complete list
+ of columns that includes generated and hidden columns.
+<a name="pragma_table_list"></a>
+<h _id=pragma_table_list style="display:none"> PRAGMA table_list</h><hr>
+ <p> <b>PRAGMA table_list;
+ <br>PRAGMA </b><i>schema.</i><b>table_list;
+ <br>PRAGMA table_list(</b><i>table-name</i><b>);</b></p>
+ <p>This pragma returns information about the tables and views in the schema,
+ one table per row of output. The table_list pragma first appeared
+ in SQLite version 3.37.0 (2021-11-27). As of its initial release
+ the columns returned by the table_list pragma include those listed below.
+ Future versions of SQLite will probably add additional columns of
+ output.
+ <p>
+ <ol>
+ <li> <b>schema</b>: the schema in which the table or view appears
+ (for example "main" or "temp").
+ <li> <b>name</b>: the name of the table or view.
+ <li> <b>type</b>: the type of object - one of "table", "view",
+ "shadow" (for <a href="vtab.html#xshadowname">shadow tables</a>), or "virtual" for
+ <a href="vtab.html">virtual tables</a>.
+ <li> <b>ncol</b>: the number of columns in the table, including
+ <a href="gencol.html">generated columns</a> and <a href="vtab.html#hiddencol">hidden columns</a>.
+ <li> <b>wr</b>: 1 if the table is a <a href="withoutrowid.html">WITHOUT ROWID</a> table or 0 if is not.
+ <li> <b>strict</b>: 1 if the table is a <a href="stricttables.html">STRICT table</a> or 0 if it is not.
+ <li> <i>Additional columns will likely be added in future releases.</i>
+ </ol>
+ <p>
+ The default behavior is to show all tables in all schemas. If the
+ <i>schema.</i> name appears before the pragma, then only tables in that
+ one schema are shown. If a <i>table-name</i> argument is supplied, then
+ only information about that one table is returned.
+<a name="pragma_table_xinfo"></a>
+<h _id=pragma_table_xinfo style="display:none"> PRAGMA table_xinfo</h><hr>
+ <p><b>PRAGMA </b><i>schema.</i><b>table_xinfo(</b><i>table-name</i><b>);</b></p>
+ <p>This pragma returns one row for each column in the named table,
+ including <a href="gencol.html">generated columns</a> and <a href="vtab.html#hiddencol">hidden columns</a>.
+ The output has the same columns as for <a href="pragma.html#pragma_table_info">PRAGMA table_info</a> plus
+ a column, "hidden", whose value signifies a normal column (0),
+ a dynamic or stored generated column (2 or 3),
+ or a hidden column in a virtual table (1). The rows for which
+ this field is non-zero are those omitted for <a href="pragma.html#pragma_table_info">PRAGMA table_info</a>.
+<a name="pragma_temp_store"></a>
+<h _id=pragma_temp_store style="display:none"> PRAGMA temp_store</h><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
+ as if they were in pure <a href="inmemorydb.html">in-memory databases</a>.
+ 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>
+<h _id=pragma_temp_store_directory style="display:none"> PRAGMA temp_store_directory</h><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>
+<h _id=pragma_threads style="display:none"> PRAGMA threads</h><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_trusted_schema"></a>
+<h _id=pragma_trusted_schema style="display:none"> PRAGMA trusted_schema</h><hr>
+ <p><b>PRAGMA trusted_schema;
+ <br>PRAGMA trusted_schema = </b><i>boolean</i><b>;</b></p>
+ <p>The trusted_schema setting is a per-connection boolean that
+ determines whether or not SQL functions and virtual tables that
+ have not been security audited are allowed to be run by views,
+ triggers, or in expressions of the schema such as <a href="lang_createtable.html#ckconst">CHECK constraints</a>,
+ <a href="lang_createtable.html#dfltval">DEFAULT clauses</a>, <a href="gencol.html">generated columns</a>, <a href="expridx.html">expression indexes</a>, and/or
+ <a href="partialindex.html">partial indexes</a>. This setting can also be controlled using
+ the <a href="c3ref/db_config.html">sqlite3_db_config</a>(db,<a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigtrustedschema">SQLITE_DBCONFIG_TRUSTED_SCHEMA</a>,...)
+ C-language interface.
+ <p>In order to maintain backwards compatibility, this setting is
+ ON by default. There are advantages to turning it off, and most
+ applications will be unaffected if it is turned off. For that reason,
+ all applications are encouraged to switch this setting off on every
+ database connection as soon as that connection is opened.
+ <p>The <a href="compile.html#trusted_schema">-DSQLITE_TRUSTED_SCHEMA=0</a> compile-time option will cause
+ this setting to default to OFF.
+<a name="pragma_user_version"></a>
+<h _id=pragma_user_version style="display:none"> PRAGMA user_version</h><hr>
+ <p><b>PRAGMA </b><i>schema.</i><b>user_version;
+ <br>PRAGMA </b><i>schema.</i><b>user_version = </b><i>integer </i><b>;</b>
+
+
+<p> The user_version pragma will get or set
+ the value of the user-version integer at offset 60 in the
+ <a href="fileformat2.html#database_header">database header</a>. The user-version is an integer that is
+ available to applications to use however they want. SQLite
+ makes no use of the user-version itself.
+
+<p> See also the <a href="pragma.html#pragma_application_id">application_id pragma</a> and <a href="pragma.html#pragma_schema_version">schema_version pragma</a>.
+<a name="pragma_vdbe_addoptrace"></a>
+<h _id=pragma_vdbe_addoptrace style="display:none"> PRAGMA vdbe_addoptrace</h><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>
+<h _id=pragma_vdbe_debug style="display:none"> PRAGMA vdbe_debug</h><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>
+<h _id=pragma_vdbe_listing style="display:none"> PRAGMA vdbe_listing</h><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>
+<h _id=pragma_vdbe_trace style="display:none"> PRAGMA vdbe_trace</h><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>
+<h _id=pragma_wal_autocheckpoint style="display:none"> PRAGMA wal_autocheckpoint</h><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>
+<h _id=pragma_wal_checkpoint style="display:none"> PRAGMA wal_checkpoint</h><hr>
+ <p><b>PRAGMA </b><i>schema.</i><b>wal_checkpoint;</b><br>
+ <b>PRAGMA </b><i>schema.</i><b>wal_checkpoint(PASSIVE);</b><br>
+ <b>PRAGMA </b><i>schema.</i><b>wal_checkpoint(FULL);</b><br>
+ <b>PRAGMA </b><i>schema.</i><b>wal_checkpoint(RESTART);</b><br>
+ <b>PRAGMA </b><i>schema.</i><b>wal_checkpoint(TRUNCATE);</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 the
+ <a href="c3ref/wal_checkpoint.html">sqlite3_wal_checkpoint()</a> C interface. The
+ <a href="c3ref/busy_handler.html">busy-handler callback</a> is never invoked in
+ this mode.
+
+ <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 concurrent writers while it is
+ running, but readers can proceed.
+
+ <dt>RESTART<dd>
+ This mode works the same way as FULL with the addition that after
+ checkpointing the log file it blocks (calls the
+ <a href="c3ref/busy_handler.html">busy-handler callback</a>)
+ until all readers are finished with the log file. This ensures
+ that the next client to write to the database file restarts the log file
+ from the beginning. RESTART blocks concurrent writers while it is
+ running, but allowed readers to proceed.
+
+ <dt>TRUNCATE<dd>
+ This mode works the same way as RESTART with the
+ addition that the WAL file is truncated to zero bytes upon successful
+ completion.
+ </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 or TRUNCATE 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>
+<h _id=pragma_writable_schema style="display:none"> PRAGMA writable_schema</h><hr>
+ <p><b>PRAGMA writable_schema = </b><i>boolean</i><b>;</b><br>
+ <b>PRAGMA writable_schema = RESET</b></p>
+
+ <p>When this pragma is on, and the <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigdefensive">SQLITE_DBCONFIG_DEFENSIVE</a> flag
+ is off, then the <a href="schematab.html">sqlite_schema</a> table
+ 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. If the argument is "RESET" then schema writing is
+ disabled (as with "PRAGMA writable_schema=OFF") and, in addition, the
+ schema is reloaded. <span style='background-color: #ffff60;'><b>Warning:</b>
+ misuse of this pragma can easily result in
+ a <a href="howtocorrupt.html#cfgerr">corrupt database file</a>.</span>
+
+<hr>
+<p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/pragma.in?m=402bc96491">2023-12-05 14:43:20</a> UTC </small></i></p>
+
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-27 17:09:40 +0000