diff options
Diffstat (limited to '')
| -rw-r--r-- | www/pragma.html | 2254 |
1 files changed, 2254 insertions, 0 deletions
diff --git a/www/pragma.html b/www/pragma.html new file mode 100644 index 0000000..f500baf --- /dev/null +++ b/www/pragma.html @@ -0,0 +1,2254 @@ +<!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='x2347' onclick='hideorshow("x2347","x2348")'>hide</button></p> + <div id='x2348' 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='x2349' onclick='hideorshow("x2349","x2350")'>hide</button></p> + <div id='x2350' 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='x2351' onclick='hideorshow("x2351","x2352")'>show</button></p> + <div id='x2352' 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' [FALSE]</tt>.) Some pragmas +takes a string literal as their argument. When pragma takes a keyword +argument, it will usually also take a numeric equivalent as well. +For example, "0" and "no" mean the same thing, as does "1" and "yes". +When querying the value of a setting, many pragmas return the number +rather than the keyword.</p> + +<p>A pragma may have an optional <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> +This feature is experimental and is subject to change. Further documentation +will become available if and when the table-valued functions for PRAGMAs +feature becomes officially supported. +<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'>case_sensitive_like</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¹</s></a></li> +<li><a href='pragma.html#pragma_data_store_directory'><s>data_store_directory¹</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¹</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¹</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¹</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²</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³</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¹</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³</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¹</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²</a></li> +<li><a href='pragma.html#pragma_vdbe_debug'>vdbe_debug²</a></li> +<li><a href='pragma.html#pragma_vdbe_listing'>vdbe_listing²</a></li> +<li><a href='pragma.html#pragma_vdbe_trace'>vdbe_trace²</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³</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="http://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> +<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> +<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=eb1e52298c36d5a07">2022-11-21 19:17:39</a> UTC </small></i></p> + |