| 1 |
<!DOCTYPE html> |
| 2 |
<html><head> |
| 3 |
<meta name="viewport" content="width=device-width, initial-scale=1.0"> |
| 4 |
<meta http-equiv="content-type" content="text/html; charset=UTF-8"> |
| 5 |
<link href="sqlite.css" rel="stylesheet"> |
| 6 |
<title>Pragma statements supported by SQLite</title> |
| 7 |
<!-- path= --> |
| 8 |
</head> |
| 9 |
<body> |
| 10 |
<div class=nosearch> |
| 11 |
<a href="index.html"> |
| 12 |
<img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0"> |
| 13 |
</a> |
| 14 |
<div><!-- IE hack to prevent disappearing logo --></div> |
| 15 |
<div class="tagline desktoponly"> |
| 16 |
Small. Fast. Reliable.<br>Choose any three. |
| 17 |
</div> |
| 18 |
<div class="menu mainmenu"> |
| 19 |
<ul> |
| 20 |
<li><a href="index.html">Home</a> |
| 21 |
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a> |
| 22 |
<li class='wideonly'><a href='about.html'>About</a> |
| 23 |
<li class='desktoponly'><a href="docs.html">Documentation</a> |
| 24 |
<li class='desktoponly'><a href="download.html">Download</a> |
| 25 |
<li class='wideonly'><a href='copyright.html'>License</a> |
| 26 |
<li class='desktoponly'><a href="support.html">Support</a> |
| 27 |
<li class='desktoponly'><a href="prosupport.html">Purchase</a> |
| 28 |
<li class='search' id='search_menubutton'> |
| 29 |
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a> |
| 30 |
</ul> |
| 31 |
</div> |
| 32 |
<div class="menu submenu" id="submenu"> |
| 33 |
<ul> |
| 34 |
<li><a href='about.html'>About</a> |
| 35 |
<li><a href='docs.html'>Documentation</a> |
| 36 |
<li><a href='download.html'>Download</a> |
| 37 |
<li><a href='support.html'>Support</a> |
| 38 |
<li><a href='prosupport.html'>Purchase</a> |
| 39 |
</ul> |
| 40 |
</div> |
| 41 |
<div class="searchmenu" id="searchmenu"> |
| 42 |
<form method="GET" action="search"> |
| 43 |
<select name="s" id="searchtype"> |
| 44 |
<option value="d">Search Documentation</option> |
| 45 |
<option value="c">Search Changelog</option> |
| 46 |
</select> |
| 47 |
<input type="text" name="q" id="searchbox" value=""> |
| 48 |
<input type="submit" value="Go"> |
| 49 |
</form> |
| 50 |
</div> |
| 51 |
</div> |
| 52 |
<script> |
| 53 |
function toggle_div(nm) { |
| 54 |
var w = document.getElementById(nm); |
| 55 |
if( w.style.display=="block" ){ |
| 56 |
w.style.display = "none"; |
| 57 |
}else{ |
| 58 |
w.style.display = "block"; |
| 59 |
} |
| 60 |
} |
| 61 |
function toggle_search() { |
| 62 |
var w = document.getElementById("searchmenu"); |
| 63 |
if( w.style.display=="block" ){ |
| 64 |
w.style.display = "none"; |
| 65 |
} else { |
| 66 |
w.style.display = "block"; |
| 67 |
setTimeout(function(){ |
| 68 |
document.getElementById("searchbox").focus() |
| 69 |
}, 30); |
| 70 |
} |
| 71 |
} |
| 72 |
function div_off(nm){document.getElementById(nm).style.display="none";} |
| 73 |
window.onbeforeunload = function(e){div_off("submenu");} |
| 74 |
/* Disable the Search feature if we are not operating from CGI, since */ |
| 75 |
/* Search is accomplished using CGI and will not work without it. */ |
| 76 |
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){ |
| 77 |
document.getElementById("search_menubutton").style.display = "none"; |
| 78 |
} |
| 79 |
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */ |
| 80 |
function hideorshow(btn,obj){ |
| 81 |
var x = document.getElementById(obj); |
| 82 |
var b = document.getElementById(btn); |
| 83 |
if( x.style.display!='none' ){ |
| 84 |
x.style.display = 'none'; |
| 85 |
b.innerHTML='show'; |
| 86 |
}else{ |
| 87 |
x.style.display = ''; |
| 88 |
b.innerHTML='hide'; |
| 89 |
} |
| 90 |
return false; |
| 91 |
} |
| 92 |
var antiRobot = 0; |
| 93 |
function antiRobotGo(){ |
| 94 |
if( antiRobot!=3 ) return; |
| 95 |
antiRobot = 7; |
| 96 |
var j = document.getElementById("mtimelink"); |
| 97 |
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href"); |
| 98 |
} |
| 99 |
function antiRobotDefense(){ |
| 100 |
document.body.onmousedown=function(){ |
| 101 |
antiRobot |= 2; |
| 102 |
antiRobotGo(); |
| 103 |
document.body.onmousedown=null; |
| 104 |
} |
| 105 |
document.body.onmousemove=function(){ |
| 106 |
antiRobot |= 2; |
| 107 |
antiRobotGo(); |
| 108 |
document.body.onmousemove=null; |
| 109 |
} |
| 110 |
setTimeout(function(){ |
| 111 |
antiRobot |= 1; |
| 112 |
antiRobotGo(); |
| 113 |
}, 100) |
| 114 |
antiRobotGo(); |
| 115 |
} |
| 116 |
antiRobotDefense(); |
| 117 |
</script> |
| 118 |
|
| 119 |
<h1 align="center">PRAGMA Statements</h1> |
| 120 |
|
| 121 |
|
| 122 |
|
| 123 |
<p>The PRAGMA statement is an SQL extension specific to SQLite and used to |
| 124 |
modify the operation of the SQLite library or to query the SQLite library for |
| 125 |
internal (non-table) data. The PRAGMA statement is issued using the same |
| 126 |
interface as other SQLite commands (e.g. <a href="lang_select.html">SELECT</a>, <a href="lang_insert.html">INSERT</a>) but is |
| 127 |
different in the following important respects: |
| 128 |
</p> |
| 129 |
<ul> |
| 130 |
<li>The pragma command is specific to SQLite and is |
| 131 |
not compatible with any other SQL database engine. |
| 132 |
<li>Specific pragma statements may be removed and others added in future |
| 133 |
releases of SQLite. There is no guarantee of backwards compatibility. |
| 134 |
<li>No error messages are generated if an unknown pragma is issued. |
| 135 |
Unknown pragmas are simply ignored. This means if there is a typo in |
| 136 |
a pragma statement the library does not inform the user of the fact. |
| 137 |
<li>Some pragmas take effect during the SQL compilation stage, not the |
| 138 |
execution stage. This means if using the C-language <a href="c3ref/prepare.html">sqlite3_prepare()</a>, |
| 139 |
<a href="c3ref/step.html">sqlite3_step()</a>, <a href="c3ref/finalize.html">sqlite3_finalize()</a> API (or similar in a wrapper |
| 140 |
interface), the pragma may run during the <a href="c3ref/prepare.html">sqlite3_prepare()</a> call, |
| 141 |
not during the <a href="c3ref/step.html">sqlite3_step()</a> call as normal SQL statements do. |
| 142 |
Or the pragma might run during sqlite3_step() just like normal |
| 143 |
SQL statements. Whether or not the pragma runs during sqlite3_prepare() |
| 144 |
or sqlite3_step() depends on the pragma and on the specific release |
| 145 |
of SQLite. |
| 146 |
<li>The <a href="lang_explain.html">EXPLAIN</a> and <a href="eqp.html">EXPLAIN QUERY PLAN</a> prefixes to SQL statements |
| 147 |
only affect the behavior of the statement during <a href="c3ref/step.html">sqlite3_step()</a>. |
| 148 |
That means that PRAGMA statements that take effect during |
| 149 |
<a href="c3ref/prepare.html">sqlite3_prepare()</a> will behave the same way regardless of whether or |
| 150 |
not they are prefaced by "EXPLAIN". |
| 151 |
</ul> |
| 152 |
|
| 153 |
<p>The C-language API for SQLite provides the <a href="c3ref/c_fcntl_begin_atomic_write.html#sqlitefcntlpragma">SQLITE_FCNTL_PRAGMA</a> |
| 154 |
<a href="c3ref/file_control.html">file control</a> which gives <a href="vfs.html">VFS</a> implementations the |
| 155 |
opportunity to add new PRAGMA statements or to override the meaning of |
| 156 |
built-in PRAGMA statements.</p> |
| 157 |
|
| 158 |
|
| 159 |
<hr /><a name="syntax"></a> |
| 160 |
<h2>PRAGMA command syntax</h2> |
| 161 |
<p><b><a href="syntax/pragma-stmt.html">pragma-stmt:</a></b> |
| 162 |
<button id='xb4fb8aa3' onclick='hideorshow("xb4fb8aa3","x97df0995")'>hide</button></p> |
| 163 |
<div id='x97df0995' class='imgcontainer'> |
| 164 |
<div style="max-width:824px"><svg xmlns='http://www.w3.org/2000/svg' style='font-size:initial;' class="pikchr" viewBox="0 0 824.352 99.576" data-pikchr-date="20250319161943"> |
| 165 |
<circle cx="5.76" cy="17.28" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 166 |
<polygon points="32.4,17.28 20.88,21.6 20.88,12.96" style="fill:rgb(0,0,0)"/> |
| 167 |
<path d="M9.36,17.28L26.64,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 168 |
<path d="M47.52,32.4L104.371,32.4A15.12 15.12 0 0 0 119.491 17.28A15.12 15.12 0 0 0 104.371 2.16L47.52,2.16A15.12 15.12 0 0 0 32.4 17.28A15.12 15.12 0 0 0 47.52 32.4Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 169 |
<text x="75.9456" y="17.28" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">PRAGMA</text> |
| 170 |
<polygon points="155.491,17.28 143.971,21.6 143.971,12.96" style="fill:rgb(0,0,0)"/> |
| 171 |
<path d="M119.491,17.28L149.731,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 172 |
<path d="M170.611,32.4L266.976,32.4A15.12 15.12 0 0 0 282.096 17.28A15.12 15.12 0 0 0 266.976 2.16L170.611,2.16A15.12 15.12 0 0 0 155.491 17.28A15.12 15.12 0 0 0 170.611 32.4Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 173 |
<text x="218.794" y="17.28" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">schema-name</text> |
| 174 |
<polygon points="305.136,17.28 293.616,21.6 293.616,12.96" style="fill:rgb(0,0,0)"/> |
| 175 |
<path d="M282.096,17.28L299.376,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 176 |
<path d="M320.256,32.4A15.12 15.12 0 0 0 335.376 17.28A15.12 15.12 0 0 0 320.256 2.16A15.12 15.12 0 0 0 305.136 17.28A15.12 15.12 0 0 0 320.256 32.4Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 177 |
<text x="320.256" y="17.28" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">.</text> |
| 178 |
<polygon points="371.376,17.28 359.856,21.6 359.856,12.96" style="fill:rgb(0,0,0)"/> |
| 179 |
<path d="M335.376,17.28L365.616,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 180 |
<path d="M386.496,32.4L483.667,32.4A15.12 15.12 0 0 0 498.787 17.28A15.12 15.12 0 0 0 483.667 2.16L386.496,2.16A15.12 15.12 0 0 0 371.376 17.28A15.12 15.12 0 0 0 386.496 32.4Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 181 |
<text x="435.082" y="17.28" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">pragma-name</text> |
| 182 |
<polygon points="540.307,82.296 528.787,86.616 528.787,77.976" style="fill:rgb(0,0,0)"/> |
| 183 |
<path d="M498.787,17.28 L 506.287,17.28 Q 513.787,17.28 513.787,32.28 L 513.787,67.296 Q 513.787,82.296 524.167,82.296 L 534.547,82.296" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 184 |
<path d="M555.427,97.416A15.12 15.12 0 0 0 570.547 82.296A15.12 15.12 0 0 0 555.427 67.176A15.12 15.12 0 0 0 540.307 82.296A15.12 15.12 0 0 0 555.427 97.416Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 185 |
<text x="555.427" y="82.296" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">(</text> |
| 186 |
<polygon points="593.587,82.296 582.067,86.616 582.067,77.976" style="fill:rgb(0,0,0)"/> |
| 187 |
<path d="M570.547,82.296L587.827,82.296" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 188 |
<path d="M593.587,97.416L720.192,97.416L720.192,67.176L593.587,67.176Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 189 |
<text x="656.89" y="82.296" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">pragma-value</text> |
| 190 |
<polygon points="743.232,82.296 731.712,86.616 731.712,77.976" style="fill:rgb(0,0,0)"/> |
| 191 |
<path d="M720.192,82.296L737.472,82.296" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 192 |
<path d="M758.352,97.416A15.12 15.12 0 0 0 773.472 82.296A15.12 15.12 0 0 0 758.352 67.176A15.12 15.12 0 0 0 743.232 82.296A15.12 15.12 0 0 0 758.352 97.416Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 193 |
<text x="758.352" y="82.296" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">)</text> |
| 194 |
<polygon points="814.992,17.28 803.472,21.6 803.472,12.96" style="fill:rgb(0,0,0)"/> |
| 195 |
<path d="M773.472,82.296 L 780.972,82.296 Q 788.472,82.296 788.472,67.296 L 788.472,32.28 Q 788.472,17.28 798.852,17.28 L 809.232,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 196 |
<circle cx="818.592" cy="17.28" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 197 |
<polygon points="540.307,44.496 528.787,48.816 528.787,40.176" style="fill:rgb(0,0,0)"/> |
| 198 |
<path d="M498.787,17.28 L 506.287,17.28 Q 513.787,17.28 513.787,30.888 Q 513.787,44.496 524.167,44.496 L 534.547,44.496" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 199 |
<path d="M555.427,59.616A15.12 15.12 0 0 0 570.547 44.496L570.547,44.496A15.12 15.12 0 0 0 555.427 29.376A15.12 15.12 0 0 0 540.307 44.496L540.307,44.496A15.12 15.12 0 0 0 555.427 59.616Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 200 |
<text x="555.427" y="44.496" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">=</text> |
| 201 |
<polygon points="593.587,44.496 582.067,48.816 582.067,40.176" style="fill:rgb(0,0,0)"/> |
| 202 |
<path d="M570.547,44.496L587.827,44.496" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 203 |
<path d="M593.587,59.616L720.192,59.616L720.192,29.376L593.587,29.376Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 204 |
<text x="656.89" y="44.496" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">pragma-value</text> |
| 205 |
<polygon points="773.472,44.496 761.952,48.816 761.952,40.176" style="fill:rgb(0,0,0)"/> |
| 206 |
<path d="M720.192,44.496L767.712,44.496" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 207 |
<path d="M773.472,44.496 L 780.972,44.496 Q 788.472,44.496 788.472,36.996 L 788.472,29.496" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 208 |
<polygon points="656.89,17.28 645.37,21.6 645.37,12.96" style="fill:rgb(0,0,0)"/> |
| 209 |
<path d="M498.787,17.28L651.13,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 210 |
<path d="M656.89,17.28L803.472,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 211 |
<polygon points="218.794,44.496 207.274,48.816 207.274,40.176" style="fill:rgb(0,0,0)"/> |
| 212 |
<path d="M119.491,17.28 L 126.991,17.28 Q 134.491,17.28 134.491,30.888 Q 134.491,44.496 149.491,44.496 L 198.034,44.496 L 213.034,44.496" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 213 |
<path d="M218.794,44.496 L 335.376,44.496 Q 350.376,44.496 350.376,30.888 Q 350.376,17.28 357.876,17.28 L 365.376,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 214 |
</svg> |
| 215 |
</div> |
| 216 |
<p><b><a href="syntax/pragma-value.html">pragma-value:</a></b> |
| 217 |
<button id='xbe24227a' onclick='hideorshow("xbe24227a","x93501224")'>hide</button></p> |
| 218 |
<div id='x93501224' class='imgcontainer'> |
| 219 |
<div style="max-width:264px"><svg xmlns='http://www.w3.org/2000/svg' style='font-size:initial;' class="pikchr" viewBox="0 0 264.499 110.16" data-pikchr-date="20250319161943"> |
| 220 |
<circle cx="5.76" cy="17.28" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 221 |
<polygon points="63.36,17.28 51.84,21.6 51.84,12.96" style="fill:rgb(0,0,0)"/> |
| 222 |
<path d="M9.36,17.28L57.6,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 223 |
<path d="M63.36,32.4L201.139,32.4L201.139,2.16L63.36,2.16Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 224 |
<text x="132.25" y="17.28" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">signed-number</text> |
| 225 |
<polygon points="255.139,17.28 243.619,21.6 243.619,12.96" style="fill:rgb(0,0,0)"/> |
| 226 |
<path d="M201.139,17.28L249.379,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 227 |
<circle cx="258.739" cy="17.28" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 228 |
<path d="M78.48,70.2L105.61,70.2A15.12 15.12 0 0 0 120.73 55.08L120.73,55.08A15.12 15.12 0 0 0 105.61 39.96L78.48,39.96A15.12 15.12 0 0 0 63.36 55.08L63.36,55.08A15.12 15.12 0 0 0 78.48 70.2Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 229 |
<text x="92.0448" y="55.08" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">name</text> |
| 230 |
<path d="M78.48,108L171.734,108A15.12 15.12 0 0 0 186.854 92.88A15.12 15.12 0 0 0 171.734 77.76L78.48,77.76A15.12 15.12 0 0 0 63.36 92.88A15.12 15.12 0 0 0 78.48 108Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 231 |
<text x="125.107" y="92.88" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">signed-literal</text> |
| 232 |
<polygon points="63.36,92.88 51.84,97.2 51.84,88.56" style="fill:rgb(0,0,0)"/> |
| 233 |
<path d="M21.84,17.28 L 29.34,17.28 Q 36.84,17.28 36.84,32.28 L 36.84,77.88 Q 36.84,92.88 47.22,92.88 L 57.6,92.88" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 234 |
<polygon points="201.139,92.88 189.619,97.2 189.619,88.56" style="fill:rgb(0,0,0)"/> |
| 235 |
<path d="M186.854,92.88L195.379,92.88" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 236 |
<path d="M201.139,92.88 L 208.639,92.88 Q 216.139,92.88 216.139,77.88 L 216.139,32.28 Q 216.139,17.28 223.639,17.28 L 231.139,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 237 |
<polygon points="201.139,55.08 189.619,59.4 189.619,50.76" style="fill:rgb(0,0,0)"/> |
| 238 |
<path d="M120.73,55.08L195.379,55.08" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 239 |
<path d="M201.139,55.08 L 208.639,55.08 Q 216.139,55.08 216.139,47.58 L 216.139,40.08" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 240 |
<polygon points="63.36,55.08 51.84,59.4 51.84,50.76" style="fill:rgb(0,0,0)"/> |
| 241 |
<path d="M36.84,39.96 L 36.84,47.52 Q 36.84,55.08 47.22,55.08 L 57.6,55.08" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 242 |
</svg> |
| 243 |
</div> |
| 244 |
<p><b><a href="syntax/signed-number.html">signed-number:</a></b> |
| 245 |
<button id='x93d34385' onclick='hideorshow("x93d34385","x3b58d01a")'>show</button></p> |
| 246 |
<div id='x3b58d01a' style='display:none;' class='imgcontainer'> |
| 247 |
<div style="max-width:292px"><svg xmlns='http://www.w3.org/2000/svg' style='font-size:initial;' class="pikchr" viewBox="0 0 292.013 99.576" data-pikchr-date="20250319161943"> |
| 248 |
<circle cx="5.76" cy="17.28" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 249 |
<polygon points="50.88,44.496 39.36,48.816 39.36,40.176" style="fill:rgb(0,0,0)"/> |
| 250 |
<path d="M9.36,17.28 L 16.86,17.28 Q 24.36,17.28 24.36,30.888 Q 24.36,44.496 34.74,44.496 L 45.12,44.496" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 251 |
<path d="M66,59.616A15.12 15.12 0 0 0 81.12 44.496L81.12,44.496A15.12 15.12 0 0 0 66 29.376A15.12 15.12 0 0 0 50.88 44.496L50.88,44.496A15.12 15.12 0 0 0 66 59.616Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 252 |
<text x="66" y="44.496" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">+</text> |
| 253 |
<polygon points="122.64,17.28 111.12,21.6 111.12,12.96" style="fill:rgb(0,0,0)"/> |
| 254 |
<path d="M81.12,44.496 L 88.62,44.496 Q 96.12,44.496 96.12,30.888 Q 96.12,17.28 106.5,17.28 L 116.88,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 255 |
<path d="M137.76,32.4L244.493,32.4A15.12 15.12 0 0 0 259.613 17.28A15.12 15.12 0 0 0 244.493 2.16L137.76,2.16A15.12 15.12 0 0 0 122.64 17.28A15.12 15.12 0 0 0 137.76 32.4Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 256 |
<text x="191.126" y="17.28" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">numeric-literal</text> |
| 257 |
<polygon points="282.653,17.28 271.133,21.6 271.133,12.96" style="fill:rgb(0,0,0)"/> |
| 258 |
<path d="M259.613,17.28L276.893,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 259 |
<circle cx="286.253" cy="17.28" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 260 |
<polygon points="50.88,82.296 39.36,86.616 39.36,77.976" style="fill:rgb(0,0,0)"/> |
| 261 |
<path d="M9.36,17.28 L 16.86,17.28 Q 24.36,17.28 24.36,32.28 L 24.36,67.296 Q 24.36,82.296 34.74,82.296 L 45.12,82.296" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 262 |
<path d="M66,97.416A15.12 15.12 0 0 0 81.12 82.296L81.12,82.296A15.12 15.12 0 0 0 66 67.176A15.12 15.12 0 0 0 50.88 82.296L50.88,82.296A15.12 15.12 0 0 0 66 97.416Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 263 |
<text x="66" y="82.296" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">-</text> |
| 264 |
<path d="M81.12,82.296 L 88.62,82.296 Q 96.12,82.296 96.12,67.296 L 96.12,44.376 L 96.12,29.376" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 265 |
<polygon points="66,17.28 54.48,21.6 54.48,12.96" style="fill:rgb(0,0,0)"/> |
| 266 |
<path d="M9.36,17.28L60.24,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 267 |
<path d="M66,17.28L111.12,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> |
| 268 |
</svg> |
| 269 |
</div> |
| 270 |
</div> |
| 271 |
</div> |
| 272 |
</div> |
| 273 |
|
| 274 |
|
| 275 |
<p> |
| 276 |
A pragma can take either zero or one argument. The argument may be either |
| 277 |
in parentheses or it may be separated from the pragma name by an equal sign. |
| 278 |
The two syntaxes yield identical results. |
| 279 |
In many pragmas, the argument is a boolean. The boolean can be one of: |
| 280 |
</p> |
| 281 |
|
| 282 |
<center> |
| 283 |
<b>1 yes true on<br>0 no false off</b> |
| 284 |
</center> |
| 285 |
|
| 286 |
<p>Keyword arguments can optionally appear in quotes. |
| 287 |
(Example: <tt>'yes' [FALSE]</tt>.) Some pragmas |
| 288 |
take a string literal as their argument. When a pragma takes a keyword |
| 289 |
argument, it will usually also take a numeric equivalent as well. |
| 290 |
For example, "0" and "no" mean the same thing, as does "1" and "yes". |
| 291 |
When querying the value of a setting, many pragmas return the number |
| 292 |
rather than the keyword.</p> |
| 293 |
|
| 294 |
<p>A pragma may have an optional <span class='yyterm'>schema-name</span> |
| 295 |
before the pragma name. |
| 296 |
The <span class='yyterm'>schema-name</span> is the name of an <a href="lang_attach.html">ATTACH</a>-ed database |
| 297 |
or "main" or "temp" for the main and the TEMP databases. If the optional |
| 298 |
schema name is omitted, "main" is assumed. In some pragmas, the schema |
| 299 |
name is meaningless and is simply ignored. In the documentation below, |
| 300 |
pragmas for which the schema name is meaningful are shown with a |
| 301 |
"<i>schema.</i>" prefix.</p> |
| 302 |
|
| 303 |
|
| 304 |
<hr /><a name="pragfunc"></a> |
| 305 |
<h2>PRAGMA functions</h2> |
| 306 |
|
| 307 |
|
| 308 |
<p> |
| 309 |
PRAGMAs that return results and that have no side-effects can be |
| 310 |
accessed from ordinary <a href="lang_select.html">SELECT</a> statements as <a href="vtab.html#tabfunc2">table-valued functions</a>. |
| 311 |
For each participating PRAGMA, the corresponding table-valued function |
| 312 |
has the same name as the PRAGMA with a 7-character "pragma_" prefix. |
| 313 |
The PRAGMA argument and schema, if any, are passed as arguments to the |
| 314 |
table-valued function, with the schema as an optional, last argument. |
| 315 |
|
| 316 |
<p>For example, information about the columns in an index can be |
| 317 |
read using the <a href="pragma.html#pragma_index_info">index_info pragma</a> as follows: |
| 318 |
|
| 319 |
<blockquote><pre> |
| 320 |
PRAGMA index_info('idx52'); |
| 321 |
</pre></blockquote> |
| 322 |
|
| 323 |
<p>Or, the same content can be read using: |
| 324 |
|
| 325 |
<blockquote><pre> |
| 326 |
SELECT * FROM pragma_index_info('idx52'); |
| 327 |
</pre></blockquote> |
| 328 |
|
| 329 |
<p>The advantage of the table-valued function format is that the query |
| 330 |
can return just a subset of the PRAGMA columns, can include a WHERE clause, |
| 331 |
can use aggregate functions, and the table-valued function can be just |
| 332 |
one of several data sources in a join. |
| 333 |
For example, to get a list of all indexed columns in a schema, one |
| 334 |
could query: |
| 335 |
|
| 336 |
<blockquote><pre> |
| 337 |
SELECT DISTINCT m.name || '.' || ii.name AS 'indexed-columns' |
| 338 |
FROM sqlite_schema AS m, |
| 339 |
pragma_index_list(m.name) AS il, |
| 340 |
pragma_index_info(il.name) AS ii |
| 341 |
WHERE m.type='table' |
| 342 |
ORDER BY 1; |
| 343 |
</pre></blockquote> |
| 344 |
|
| 345 |
<p> |
| 346 |
Additional notes: |
| 347 |
<ul> |
| 348 |
<li><p> |
| 349 |
Table-valued functions exist only for built-in PRAGMAs, not for PRAGMAs |
| 350 |
defined using the <a href="c3ref/c_fcntl_begin_atomic_write.html#sqlitefcntlpragma">SQLITE_FCNTL_PRAGMA</a> file control. |
| 351 |
<li><p> |
| 352 |
Table-valued functions exist only for PRAGMAs that return results and |
| 353 |
that have no side-effects. |
| 354 |
<li><p> |
| 355 |
This feature could be used to implement |
| 356 |
<a href="https://en.wikipedia.org/wiki/Information_schema">information schema</a> |
| 357 |
by first creating a separate schema using |
| 358 |
<blockquote><pre> |
| 359 |
<a href="lang_attach.html">ATTACH</a> ':memory:' AS 'information_schema'; |
| 360 |
</pre></blockquote> |
| 361 |
Then creating |
| 362 |
<a href="lang_createview.html">VIEWs</a> in that schema that implement the official information schema |
| 363 |
tables using table-valued PRAGMA functions. |
| 364 |
<li><p> |
| 365 |
The table-valued functions for PRAGMA feature was added |
| 366 |
in SQLite version 3.16.0 (2017-01-02). Prior versions of SQLite |
| 367 |
cannot use this feature. |
| 368 |
</ul> |
| 369 |
|
| 370 |
<hr /><a name="toc"></a> |
| 371 |
<h2>List Of PRAGMAs</h2> |
| 372 |
<div class='columns' style='columns: 12em auto;'> |
| 373 |
<ul style='padding-top:0;'> |
| 374 |
<li><a href='pragma.html#pragma_analysis_limit'>analysis_limit</a></li> |
| 375 |
<li><a href='pragma.html#pragma_application_id'>application_id</a></li> |
| 376 |
<li><a href='pragma.html#pragma_auto_vacuum'>auto_vacuum</a></li> |
| 377 |
<li><a href='pragma.html#pragma_automatic_index'>automatic_index</a></li> |
| 378 |
<li><a href='pragma.html#pragma_busy_timeout'>busy_timeout</a></li> |
| 379 |
<li><a href='pragma.html#pragma_cache_size'>cache_size</a></li> |
| 380 |
<li><a href='pragma.html#pragma_cache_spill'>cache_spill</a></li> |
| 381 |
<li><a href='pragma.html#pragma_case_sensitive_like'><s>case_sensitive_like¹</s></a></li> |
| 382 |
<li><a href='pragma.html#pragma_cell_size_check'>cell_size_check</a></li> |
| 383 |
<li><a href='pragma.html#pragma_checkpoint_fullfsync'>checkpoint_fullfsync</a></li> |
| 384 |
<li><a href='pragma.html#pragma_collation_list'>collation_list</a></li> |
| 385 |
<li><a href='pragma.html#pragma_compile_options'>compile_options</a></li> |
| 386 |
<li><a href='pragma.html#pragma_count_changes'><s>count_changes¹</s></a></li> |
| 387 |
<li><a href='pragma.html#pragma_data_store_directory'><s>data_store_directory¹</s></a></li> |
| 388 |
<li><a href='pragma.html#pragma_data_version'>data_version</a></li> |
| 389 |
<li><a href='pragma.html#pragma_database_list'>database_list</a></li> |
| 390 |
<li><a href='pragma.html#pragma_default_cache_size'><s>default_cache_size¹</s></a></li> |
| 391 |
<li><a href='pragma.html#pragma_defer_foreign_keys'>defer_foreign_keys</a></li> |
| 392 |
<li><a href='pragma.html#pragma_empty_result_callbacks'><s>empty_result_callbacks¹</s></a></li> |
| 393 |
<li><a href='pragma.html#pragma_encoding'>encoding</a></li> |
| 394 |
<li><a href='pragma.html#pragma_foreign_key_check'>foreign_key_check</a></li> |
| 395 |
<li><a href='pragma.html#pragma_foreign_key_list'>foreign_key_list</a></li> |
| 396 |
<li><a href='pragma.html#pragma_foreign_keys'>foreign_keys</a></li> |
| 397 |
<li><a href='pragma.html#pragma_freelist_count'>freelist_count</a></li> |
| 398 |
<li><a href='pragma.html#pragma_full_column_names'><s>full_column_names¹</s></a></li> |
| 399 |
<li><a href='pragma.html#pragma_fullfsync'>fullfsync</a></li> |
| 400 |
<li><a href='pragma.html#pragma_function_list'>function_list</a></li> |
| 401 |
<li><a href='pragma.html#pragma_hard_heap_limit'>hard_heap_limit</a></li> |
| 402 |
<li><a href='pragma.html#pragma_ignore_check_constraints'>ignore_check_constraints</a></li> |
| 403 |
<li><a href='pragma.html#pragma_incremental_vacuum'>incremental_vacuum</a></li> |
| 404 |
<li><a href='pragma.html#pragma_index_info'>index_info</a></li> |
| 405 |
<li><a href='pragma.html#pragma_index_list'>index_list</a></li> |
| 406 |
<li><a href='pragma.html#pragma_index_xinfo'>index_xinfo</a></li> |
| 407 |
<li><a href='pragma.html#pragma_integrity_check'>integrity_check</a></li> |
| 408 |
<li><a href='pragma.html#pragma_journal_mode'>journal_mode</a></li> |
| 409 |
<li><a href='pragma.html#pragma_journal_size_limit'>journal_size_limit</a></li> |
| 410 |
<li><a href='pragma.html#pragma_legacy_alter_table'>legacy_alter_table</a></li> |
| 411 |
<li><a href='pragma.html#pragma_legacy_file_format'>legacy_file_format</a></li> |
| 412 |
<li><a href='pragma.html#pragma_locking_mode'>locking_mode</a></li> |
| 413 |
<li><a href='pragma.html#pragma_max_page_count'>max_page_count</a></li> |
| 414 |
<li><a href='pragma.html#pragma_mmap_size'>mmap_size</a></li> |
| 415 |
<li><a href='pragma.html#pragma_module_list'>module_list</a></li> |
| 416 |
<li><a href='pragma.html#pragma_optimize'>optimize</a></li> |
| 417 |
<li><a href='pragma.html#pragma_page_count'>page_count</a></li> |
| 418 |
<li><a href='pragma.html#pragma_page_size'>page_size</a></li> |
| 419 |
<li><a href='pragma.html#pragma_parser_trace'>parser_trace²</a></li> |
| 420 |
<li><a href='pragma.html#pragma_pragma_list'>pragma_list</a></li> |
| 421 |
<li><a href='pragma.html#pragma_query_only'>query_only</a></li> |
| 422 |
<li><a href='pragma.html#pragma_quick_check'>quick_check</a></li> |
| 423 |
<li><a href='pragma.html#pragma_read_uncommitted'>read_uncommitted</a></li> |
| 424 |
<li><a href='pragma.html#pragma_recursive_triggers'>recursive_triggers</a></li> |
| 425 |
<li><a href='pragma.html#pragma_reverse_unordered_selects'>reverse_unordered_selects</a></li> |
| 426 |
<li><a href='pragma.html#pragma_schema_version'>schema_version³</a></li> |
| 427 |
<li><a href='pragma.html#pragma_secure_delete'>secure_delete</a></li> |
| 428 |
<li><a href='pragma.html#pragma_short_column_names'><s>short_column_names¹</s></a></li> |
| 429 |
<li><a href='pragma.html#pragma_shrink_memory'>shrink_memory</a></li> |
| 430 |
<li><a href='pragma.html#pragma_soft_heap_limit'>soft_heap_limit</a></li> |
| 431 |
<li><a href='pragma.html#pragma_stats'>stats³</a></li> |
| 432 |
<li><a href='pragma.html#pragma_synchronous'>synchronous</a></li> |
| 433 |
<li><a href='pragma.html#pragma_table_info'>table_info</a></li> |
| 434 |
<li><a href='pragma.html#pragma_table_list'>table_list</a></li> |
| 435 |
<li><a href='pragma.html#pragma_table_xinfo'>table_xinfo</a></li> |
| 436 |
<li><a href='pragma.html#pragma_temp_store'>temp_store</a></li> |
| 437 |
<li><a href='pragma.html#pragma_temp_store_directory'><s>temp_store_directory¹</s></a></li> |
| 438 |
<li><a href='pragma.html#pragma_threads'>threads</a></li> |
| 439 |
<li><a href='pragma.html#pragma_trusted_schema'>trusted_schema</a></li> |
| 440 |
<li><a href='pragma.html#pragma_user_version'>user_version</a></li> |
| 441 |
<li><a href='pragma.html#pragma_vdbe_addoptrace'>vdbe_addoptrace²</a></li> |
| 442 |
<li><a href='pragma.html#pragma_vdbe_debug'>vdbe_debug²</a></li> |
| 443 |
<li><a href='pragma.html#pragma_vdbe_listing'>vdbe_listing²</a></li> |
| 444 |
<li><a href='pragma.html#pragma_vdbe_trace'>vdbe_trace²</a></li> |
| 445 |
<li><a href='pragma.html#pragma_wal_autocheckpoint'>wal_autocheckpoint</a></li> |
| 446 |
<li><a href='pragma.html#pragma_wal_checkpoint'>wal_checkpoint</a></li> |
| 447 |
<li><a href='pragma.html#pragma_writable_schema'>writable_schema³</a></li> |
| 448 |
</ul> |
| 449 |
</div> |
| 450 |
|
| 451 |
<p>Notes: |
| 452 |
<ol> |
| 453 |
<li>Pragmas whose names are <s>struck through</s> |
| 454 |
are deprecated. Do not use them. They exist |
| 455 |
for historical compatibility. |
| 456 |
<li>These pragmas are only available in builds using non-standard |
| 457 |
compile-time options. |
| 458 |
<li>These pragmas are used for testing SQLite and are not recommended |
| 459 |
for use in application programs.</ol></p> |
| 460 |
<a name="pragma_analysis_limit"></a> |
| 461 |
<h _id=pragma_analysis_limit style="display:none"> PRAGMA analysis_limit</h><hr> |
| 462 |
<p><b>PRAGMA analysis_limit; |
| 463 |
<br>PRAGMA analysis_limit = </b><i>N</i><b>;</b></p> |
| 464 |
<p>Query or change a limit on the <a href="lang_analyze.html#approx">approximate ANALYZE</a> setting. |
| 465 |
This is the approximate number of |
| 466 |
rows examined in each index by the <a href="lang_analyze.html">ANALYZE</a> command. |
| 467 |
If the argument <i>N</i> is omitted, then the analysis limit |
| 468 |
is unchanged. |
| 469 |
If the limit is zero, then the analysis limit is disabled and |
| 470 |
the ANALYZE command will examine all rows of each index. |
| 471 |
If N is greater than zero, then the analysis limit is set to N |
| 472 |
and subsequent ANALYZE commands will stop analyzing |
| 473 |
each index after it has examined approximately N rows. |
| 474 |
If N is a negative number or something other than an integer value, |
| 475 |
then the pragma behaves as if the N argument was omitted. |
| 476 |
In all cases, the value returned is the new analysis limit used |
| 477 |
for subsequent ANALYZE commands. |
| 478 |
<p>This pragma can be used to help the ANALYZE command run faster |
| 479 |
on large databases. The results of analysis are not as good |
| 480 |
when only part of each index is examined, but the results are |
| 481 |
usually good enough. Setting N to 100 or 1000 allows the |
| 482 |
ANALYZE command to run quickly, even on enormous |
| 483 |
database files. |
| 484 |
<p>This pragma was added in SQLite version 3.32.0 (2020-05-22). |
| 485 |
The current implementation only uses the lower 31 bits of the |
| 486 |
N value - higher order bits are silently ignored. Future versions |
| 487 |
of SQLite might begin using higher order bits. |
| 488 |
<p>Beginning with SQLite version 3.46.0 (2024-05-23), |
| 489 |
the recommended way of running <a href="lang_analyze.html">ANALYZE</a> is with the |
| 490 |
<a href="pragma.html#pragma_optimize">PRAGMA optimize</a> command. The <a href="pragma.html#pragma_optimize">PRAGMA optimize</a> will automatically |
| 491 |
set a reasonable, temporary analysis limit that ensures that the |
| 492 |
<a href="pragma.html#pragma_optimize">PRAGMA optimize</a> command will finish quickly even on enormous |
| 493 |
databases. Applications that use the <a href="pragma.html#pragma_optimize">PRAGMA optimize</a> instead of |
| 494 |
running <a href="lang_analyze.html">ANALYZE</a> directly do not need to set an analysis limit. |
| 495 |
<a name="pragma_application_id"></a> |
| 496 |
<h _id=pragma_application_id style="display:none"> PRAGMA application_id</h><hr> |
| 497 |
<p><b>PRAGMA </b><i>schema.</i><b>application_id; |
| 498 |
<br>PRAGMA </b><i>schema.</i><b>application_id = </b><i>integer </i><b>;</b> |
| 499 |
|
| 500 |
<p>The application_id PRAGMA is used to query or set the 32-bit |
| 501 |
signed big-endian "Application ID" integer located at offset |
| 502 |
68 into the <a href="fileformat2.html#database_header">database header</a>. Applications that use SQLite as their |
| 503 |
<a href="appfileformat.html">application file-format</a> should set the Application ID integer to |
| 504 |
a unique integer so that utilities such as |
| 505 |
<a href="http://www.darwinsys.com/file/">file(1)</a> can determine the specific |
| 506 |
file type rather than just reporting "SQLite3 Database". A list of |
| 507 |
assigned application IDs can be seen by consulting the |
| 508 |
<a href="https://sqlite.org/src/artifact?ci=trunk&filename=magic.txt">magic.txt</a> file in the SQLite source repository. |
| 509 |
|
| 510 |
<p> See also the <a href="pragma.html#pragma_user_version">user_version pragma</a>. |
| 511 |
<a name="pragma_auto_vacuum"></a> |
| 512 |
<h _id=pragma_auto_vacuum style="display:none"> PRAGMA auto_vacuum</h><hr> |
| 513 |
<p><b>PRAGMA </b><i>schema.</i><b>auto_vacuum;<br> |
| 514 |
PRAGMA </b><i>schema.</i><b>auto_vacuum = </b> |
| 515 |
<i>0 | NONE | 1 | FULL | 2 | INCREMENTAL</i><b>;</b></p> |
| 516 |
|
| 517 |
<p>Query or set the auto-vacuum status in the database.</p> |
| 518 |
|
| 519 |
<p>The default setting for auto-vacuum is 0 or "none", |
| 520 |
unless the <a href="compile.html#default_autovacuum">SQLITE_DEFAULT_AUTOVACUUM</a> compile-time option is used. |
| 521 |
The "none" setting means that auto-vacuum is disabled. |
| 522 |
When auto-vacuum is disabled and data is deleted from a database, |
| 523 |
the database file remains the same size. Unused database file |
| 524 |
pages are added to a "<a href="fileformat2.html#freelist">freelist</a>" and reused for subsequent inserts. So |
| 525 |
no database file space is lost. However, the database file does not |
| 526 |
shrink. In this mode the <a href="lang_vacuum.html">VACUUM</a> |
| 527 |
command can be used to rebuild the entire database file and |
| 528 |
thus reclaim unused disk space.</p> |
| 529 |
|
| 530 |
<p>When the auto-vacuum mode is 1 or "full", the freelist pages are |
| 531 |
moved to the end of the database file and the database file is truncated |
| 532 |
to remove the freelist pages at every transaction commit. |
| 533 |
Note, however, that auto-vacuum only truncates the freelist pages |
| 534 |
from the file. Auto-vacuum does not defragment the database nor |
| 535 |
repack individual database pages the way that the |
| 536 |
<a href="lang_vacuum.html">VACUUM</a> command does. In fact, because |
| 537 |
it moves pages around within the file, auto-vacuum can actually |
| 538 |
make fragmentation worse.</p> |
| 539 |
|
| 540 |
<p>Auto-vacuuming is only possible if the database stores some |
| 541 |
additional information that allows each database page to be |
| 542 |
traced backwards to its referrer. Therefore, auto-vacuuming must |
| 543 |
be turned on before any tables are created. It is not possible |
| 544 |
to enable or disable auto-vacuum after a table has been created.</p> |
| 545 |
|
| 546 |
<p>When the value of auto-vacuum is 2 or "incremental" then the additional |
| 547 |
information needed to do auto-vacuuming is stored in the database file |
| 548 |
but auto-vacuuming does not occur automatically at each commit as it |
| 549 |
does with auto_vacuum=full. In incremental mode, the separate |
| 550 |
<a href="pragma.html#pragma_incremental_vacuum">incremental_vacuum</a> pragma must |
| 551 |
be invoked to cause the auto-vacuum to occur.</p> |
| 552 |
|
| 553 |
<p>The database connection can be changed between full and incremental |
| 554 |
autovacuum mode at any time. However, changing from |
| 555 |
"none" to "full" or "incremental" can only occur when the database |
| 556 |
is new (no tables |
| 557 |
have yet been created) or by running the <a href="lang_vacuum.html">VACUUM</a> command. To |
| 558 |
change auto-vacuum modes, first use the auto_vacuum pragma to set |
| 559 |
the new desired mode, then invoke the <a href="lang_vacuum.html">VACUUM</a> command to |
| 560 |
reorganize the entire database file. To change from "full" or |
| 561 |
"incremental" back to "none" always requires running <a href="lang_vacuum.html">VACUUM</a> even |
| 562 |
on an empty database. |
| 563 |
</p> |
| 564 |
|
| 565 |
<p>When the auto_vacuum pragma is invoked with no arguments, it |
| 566 |
returns the current auto_vacuum mode.</p> |
| 567 |
<a name="pragma_automatic_index"></a> |
| 568 |
<h _id=pragma_automatic_index style="display:none"> PRAGMA automatic_index</h><hr> |
| 569 |
<p><b>PRAGMA automatic_index; |
| 570 |
<br>PRAGMA automatic_index = </b><i>boolean</i><b>;</b></p> |
| 571 |
|
| 572 |
<p>Query, set, or clear the <a href="optoverview.html#autoindex">automatic indexing</a> capability. |
| 573 |
<p><a href="optoverview.html#autoindex">Automatic indexing</a> is enabled by default as of |
| 574 |
<a href="releaselog/3_7_17.html">version 3.7.17</a> (2013-05-20), |
| 575 |
but this might change in future releases of SQLite. |
| 576 |
<a name="pragma_busy_timeout"></a> |
| 577 |
<h _id=pragma_busy_timeout style="display:none"> PRAGMA busy_timeout</h><hr> |
| 578 |
<p><b>PRAGMA busy_timeout; |
| 579 |
<br>PRAGMA busy_timeout = </b><i>milliseconds</i><b>;</b></p> |
| 580 |
<p>Query or change the setting of the |
| 581 |
<a href="c3ref/busy_timeout.html">busy timeout</a>. |
| 582 |
This pragma is an alternative to the <a href="c3ref/busy_timeout.html">sqlite3_busy_timeout()</a> C-language |
| 583 |
interface which is made available as a pragma for use with language |
| 584 |
bindings that do not provide direct access to <a href="c3ref/busy_timeout.html">sqlite3_busy_timeout()</a>. |
| 585 |
|
| 586 |
<p>Each database connection can only have a single |
| 587 |
<a href="c3ref/busy_handler.html">busy handler</a>. This PRAGMA sets the busy handler |
| 588 |
for the process, possibly overwriting any previously set busy handler. |
| 589 |
<a name="pragma_cache_size"></a> |
| 590 |
<h _id=pragma_cache_size style="display:none"> PRAGMA cache_size</h><hr> |
| 591 |
<p><b>PRAGMA </b><i>schema.</i><b>cache_size; |
| 592 |
<br>PRAGMA </b><i>schema.</i><b>cache_size = </b><i>pages</i><b>; |
| 593 |
<br>PRAGMA </b><i>schema.</i><b>cache_size = -</b><i>kibibytes</i><b>;</b></p> |
| 594 |
<p>Query or change the suggested maximum number of database disk pages |
| 595 |
that SQLite will hold in memory at once per open database file. Whether |
| 596 |
or not this suggestion is honored is at the discretion of the |
| 597 |
<a href="c3ref/pcache_methods2.html">Application Defined Page Cache</a>. |
| 598 |
The default page cache that is built into SQLite honors the request, |
| 599 |
however alternative application-defined page cache implementations |
| 600 |
may choose to interpret the suggested cache size in different ways |
| 601 |
or to ignore it altogether. |
| 602 |
The default suggested cache size is -2000, which means the cache size |
| 603 |
is limited to 2048000 bytes of memory. |
| 604 |
The default suggested cache size can be altered using the |
| 605 |
<a href="compile.html#default_cache_size">SQLITE_DEFAULT_CACHE_SIZE</a> compile-time options. |
| 606 |
The TEMP database has a default suggested cache size of 0 pages.</p> |
| 607 |
|
| 608 |
<p>If the argument N is positive then the suggested cache size is set |
| 609 |
to N. If the argument N is negative, then the |
| 610 |
number of cache pages is adjusted to be a number of pages that would |
| 611 |
use approximately abs(N*1024) bytes of memory based on the current |
| 612 |
page size. SQLite remembers the number of pages in the page cache, |
| 613 |
not the amount of memory used. So if you set the cache size using |
| 614 |
a negative number and subsequently change the page size (using the |
| 615 |
<a href="pragma.html#pragma_page_size">PRAGMA page_size</a> command) then the maximum amount of cache |
| 616 |
memory will go up or down in proportion to the change in page size. |
| 617 |
|
| 618 |
<p><i>Backwards compatibility note:</i> |
| 619 |
The behavior of cache_size with a negative N |
| 620 |
was different prior to <a href="releaselog/3_7_10.html">version 3.7.10</a> (2012-01-16). In |
| 621 |
earlier versions, the number of pages in the cache was set |
| 622 |
to the absolute value of N.</p> |
| 623 |
|
| 624 |
<p>When you change the cache size using the cache_size pragma, the |
| 625 |
change only endures for the current session. The cache size reverts |
| 626 |
to the default value when the database is closed and reopened.</p> |
| 627 |
|
| 628 |
<p>The default page cache implemention does not allocate |
| 629 |
the full amount of cache memory all at once. Cache memory |
| 630 |
is allocated in smaller chunks on an as-needed basis. The page_cache |
| 631 |
setting is a (suggested) upper bound on the amount of memory that the |
| 632 |
cache can use, not the amount of memory it will use all of the time. |
| 633 |
This is the behavior of the default page cache implementation, but an |
| 634 |
<a href="c3ref/pcache_methods2.html">application defined page cache</a> is free |
| 635 |
to behave differently if it wants. |
| 636 |
<a name="pragma_cache_spill"></a> |
| 637 |
<h _id=pragma_cache_spill style="display:none"> PRAGMA cache_spill</h><hr> |
| 638 |
<p><b>PRAGMA cache_spill; |
| 639 |
<br>PRAGMA cache_spill=</b><i>boolean</i><b>; |
| 640 |
<br>PRAGMA </b><i>schema.</i><b>cache_spill=<i>N</i>;</b></p> |
| 641 |
|
| 642 |
<p>The cache_spill pragma enables or disables the ability of the pager |
| 643 |
to spill dirty cache pages to the database file in the middle of a |
| 644 |
transaction. Cache_spill is enabled by default and most applications |
| 645 |
should leave it that way as cache spilling is usually advantageous. |
| 646 |
However, a cache spill has the side-effect of acquiring an |
| 647 |
<a href="lockingv3.html#excl_lock">EXCLUSIVE lock</a> on the database file. Hence, some applications that |
| 648 |
have large long-running transactions may want to disable cache spilling |
| 649 |
in order to prevent the application from acquiring an exclusive lock |
| 650 |
on the database until the moment that the transaction <a href="lang_transaction.html">COMMIT</a>s. |
| 651 |
<p>The "PRAGMA cache_spill=<i>N</i>" form of this pragma sets a minimum |
| 652 |
cache size threshold required for spilling to occur. The number of pages |
| 653 |
in cache must exceed both the cache_spill threshold and the maximum cache |
| 654 |
size set by the <a href="pragma.html#pragma_cache_size">PRAGMA cache_size</a> statement in order for spilling to |
| 655 |
occur. |
| 656 |
<p>The "PRAGMA cache_spill=<i>boolean</i>" form of this pragma applies |
| 657 |
across all databases attached to the database connection. But the |
| 658 |
"PRAGMA cache_spill=<i>N</i>" form of this statement only applies to |
| 659 |
the "main" schema or whatever other schema is specified as part of the |
| 660 |
statement. |
| 661 |
<a name="pragma_case_sensitive_like"></a> |
| 662 |
<h _id=pragma_case_sensitive_like style="display:none"> PRAGMA case_sensitive_like</h><hr> |
| 663 |
<p><b>PRAGMA case_sensitive_like = </b><i>boolean</i><b>;</b></p> |
| 664 |
<p>The default behavior of the <a href="lang_expr.html#like">LIKE</a> operator is to ignore case |
| 665 |
for ASCII characters. Hence, by default <b>'a' LIKE 'A'</b> is |
| 666 |
true. The case_sensitive_like pragma installs a new application-defined |
| 667 |
LIKE function that is either case sensitive or insensitive depending |
| 668 |
on the value of the case_sensitive_like pragma. |
| 669 |
When case_sensitive_like is disabled, the default LIKE behavior is |
| 670 |
expressed. When case_sensitive_like is enabled, case becomes |
| 671 |
significant. So, for example, |
| 672 |
<b>'a' LIKE 'A'</b> is false but <b>'a' LIKE 'a'</b> is still true.</p> |
| 673 |
|
| 674 |
<p>This pragma uses <a href="c3ref/create_function.html">sqlite3_create_function()</a> to overload the |
| 675 |
LIKE and GLOB functions, which may override previous implementations |
| 676 |
of LIKE and GLOB registered by the application. This pragma |
| 677 |
only changes the behavior of the SQL <a href="lang_expr.html#like">LIKE</a> operator. It does not |
| 678 |
change the behavior of the <a href="c3ref/strlike.html">sqlite3_strlike()</a> C-language interface, |
| 679 |
which is always case insensitive.</p> |
| 680 |
|
| 681 |
<p><b>WARNING:</b> If a database uses the LIKE operator anywhere in |
| 682 |
the schema, such as in a <a href="lang_createtable.html#ckconst">CHECK constraint</a> or in an |
| 683 |
<a href="expridx.html">expression index</a> or in the WHERE clause of a <a href="partialindex.html">partial index</a>, then |
| 684 |
changing the definition of the LIKE operator using this PRAGMA can |
| 685 |
cause the database to appear to be corrupt. <a href="pragma.html#pragma_integrity_check">PRAGMA integrity_check</a> |
| 686 |
will report errors. The database is not really corrupt in that |
| 687 |
changing the behavior of LIKE back to the way |
| 688 |
it was when the schema was defined and the database was populated |
| 689 |
will clear the problem. If the use of LIKE occurs only in indexes, |
| 690 |
then the problem can be cleared by running <a href="lang_reindex.html">REINDEX</a>. Nevertheless, |
| 691 |
the use of the case_sensitive_like pragma is discouraged.</p> |
| 692 |
|
| 693 |
|
| 694 |
<p style='background-color: #ffd0d0;'> |
| 695 |
<b>This pragma is deprecated</b> and exists |
| 696 |
for backwards compatibility only. New applications |
| 697 |
should avoid using this pragma. Older applications should discontinue |
| 698 |
use of this pragma at the earliest opportunity. This pragma may be omitted |
| 699 |
from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>. |
| 700 |
</p> |
| 701 |
|
| 702 |
<a name="pragma_cell_size_check"></a> |
| 703 |
<h _id=pragma_cell_size_check style="display:none"> PRAGMA cell_size_check</h><hr> |
| 704 |
<p><b>PRAGMA cell_size_check |
| 705 |
<br>PRAGMA cell_size_check = </b><i>boolean</i><b>;</b></p> |
| 706 |
<p>The cell_size_check pragma enables or disables additional sanity |
| 707 |
checking on database b-tree pages as they are initially read from disk. |
| 708 |
With cell size checking enabled, database corruption is detected earlier |
| 709 |
and is less likely to "spread". However, there is a small performance |
| 710 |
hit for doing the extra checks and so cell size checking is turned off |
| 711 |
by default. |
| 712 |
<a name="pragma_checkpoint_fullfsync"></a> |
| 713 |
<h _id=pragma_checkpoint_fullfsync style="display:none"> PRAGMA checkpoint_fullfsync</h><hr> |
| 714 |
<p><b>PRAGMA checkpoint_fullfsync |
| 715 |
<br>PRAGMA checkpoint_fullfsync = </b><i>boolean</i><b>;</b></p> |
| 716 |
<p>Query or change the fullfsync flag for <a href="wal.html#ckpt">checkpoint</a> operations. |
| 717 |
If this flag is set, then the F_FULLFSYNC syncing method is used |
| 718 |
during checkpoint operations on systems that support F_FULLFSYNC. |
| 719 |
The default value of the checkpoint_fullfsync flag |
| 720 |
is off. Only Mac OS-X supports F_FULLFSYNC.</p> |
| 721 |
|
| 722 |
<p>If the <a href="pragma.html#pragma_fullfsync">fullfsync</a> flag is set, then the F_FULLFSYNC syncing |
| 723 |
method is used for all sync operations and the checkpoint_fullfsync |
| 724 |
setting is irrelevant.</p> |
| 725 |
<a name="pragma_collation_list"></a> |
| 726 |
<h _id=pragma_collation_list style="display:none"> PRAGMA collation_list</h><hr> |
| 727 |
<p><b>PRAGMA collation_list;</b></p> |
| 728 |
<p>Return a list of the collating sequences defined for the current |
| 729 |
database connection.</p> |
| 730 |
<a name="pragma_compile_options"></a> |
| 731 |
<h _id=pragma_compile_options style="display:none"> PRAGMA compile_options</h><hr> |
| 732 |
<p><b>PRAGMA compile_options;</b></p> |
| 733 |
<p>This pragma returns the names of <a href="compile.html">compile-time options</a> used when |
| 734 |
building SQLite, one option per row. The "SQLITE_" prefix is omitted |
| 735 |
from the returned option names. See also the |
| 736 |
<a href="c3ref/compileoption_get.html">sqlite3_compileoption_get()</a> C/C++ interface and the |
| 737 |
<a href="lang_corefunc.html#sqlite_compileoption_get">sqlite_compileoption_get()</a> SQL functions.</p> |
| 738 |
<a name="pragma_count_changes"></a> |
| 739 |
<h _id=pragma_count_changes style="display:none"> PRAGMA count_changes</h><hr> |
| 740 |
<p><b>PRAGMA count_changes; |
| 741 |
<br>PRAGMA count_changes = </b>boolean</i><b>;</b></p> |
| 742 |
|
| 743 |
<p>Query or change the count-changes flag. Normally, when the |
| 744 |
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 |
| 745 |
return no data. When count-changes is set, each of these commands |
| 746 |
returns a single row of data consisting of one integer value - the |
| 747 |
number of rows inserted, modified or deleted by the command. The |
| 748 |
returned change count does not include any insertions, modifications |
| 749 |
or deletions performed by triggers, any changes made automatically |
| 750 |
by <a href="foreignkeys.html#fk_actions">foreign key actions</a>, or updates caused by an <a href="lang_upsert.html">upsert</a>.</p> |
| 751 |
|
| 752 |
<p>Another way to get the row change counts is to use the |
| 753 |
<a href="c3ref/changes.html">sqlite3_changes()</a> or <a href="c3ref/total_changes.html">sqlite3_total_changes()</a> interfaces. |
| 754 |
There is a subtle difference, though. When an INSERT, UPDATE, or |
| 755 |
DELETE is run against a view using an <a href="lang_createtrigger.html#instead_of_trigger">INSTEAD OF trigger</a>, |
| 756 |
the count_changes pragma reports the number of rows in the view |
| 757 |
that fired the trigger, whereas <a href="c3ref/changes.html">sqlite3_changes()</a> and |
| 758 |
<a href="c3ref/total_changes.html">sqlite3_total_changes()</a> do not. |
| 759 |
|
| 760 |
|
| 761 |
<p style='background-color: #ffd0d0;'> |
| 762 |
<b>This pragma is deprecated</b> and exists |
| 763 |
for backwards compatibility only. New applications |
| 764 |
should avoid using this pragma. Older applications should discontinue |
| 765 |
use of this pragma at the earliest opportunity. This pragma may be omitted |
| 766 |
from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>. |
| 767 |
</p> |
| 768 |
|
| 769 |
<a name="pragma_data_store_directory"></a> |
| 770 |
<h _id=pragma_data_store_directory style="display:none"> PRAGMA data_store_directory</h><hr> |
| 771 |
<p><b>PRAGMA data_store_directory; |
| 772 |
<br>PRAGMA data_store_directory = '</b><i>directory-name</i><b>';</b></p> |
| 773 |
<p>Query or change the value of the <a href="c3ref/data_directory.html">sqlite3_data_directory</a> global |
| 774 |
variable, which windows operating-system interface backends use to |
| 775 |
determine where to store database files specified using a relative |
| 776 |
pathname.</p> |
| 777 |
|
| 778 |
<p>Changing the data_store_directory setting is <u>not</u> threadsafe. |
| 779 |
Never change the data_store_directory setting if another thread |
| 780 |
within the application is running any SQLite interface at the same time. |
| 781 |
Doing so results in undefined behavior. Changing the data_store_directory |
| 782 |
setting writes to the <a href="c3ref/data_directory.html">sqlite3_data_directory</a> global |
| 783 |
variable and that global variable is not protected by a mutex.</p> |
| 784 |
|
| 785 |
<p>This facility is provided for WinRT which does not have an OS |
| 786 |
mechanism for reading or changing the current working directory. |
| 787 |
The use of this pragma in any other context is discouraged and may |
| 788 |
be disallowed in future releases.</p> |
| 789 |
|
| 790 |
|
| 791 |
<p style='background-color: #ffd0d0;'> |
| 792 |
<b>This pragma is deprecated</b> and exists |
| 793 |
for backwards compatibility only. New applications |
| 794 |
should avoid using this pragma. Older applications should discontinue |
| 795 |
use of this pragma at the earliest opportunity. This pragma may be omitted |
| 796 |
from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>. |
| 797 |
</p> |
| 798 |
|
| 799 |
<a name="pragma_data_version"></a> |
| 800 |
<h _id=pragma_data_version style="display:none"> PRAGMA data_version</h><hr> |
| 801 |
<p><b>PRAGMA </b><i>schema.</i><b>data_version;</b></p> |
| 802 |
<p>The "PRAGMA data_version" command provides an indication that the |
| 803 |
database file has been modified. |
| 804 |
Interactive programs that hold database content in memory or that |
| 805 |
display database content on-screen can use the PRAGMA data_version |
| 806 |
command to determine if they need to flush and reload their memory |
| 807 |
or update the screen display.</p> |
| 808 |
|
| 809 |
<p>The integer values returned by two |
| 810 |
invocations of "PRAGMA data_version" from the same connection |
| 811 |
will be different if changes were committed to the database |
| 812 |
by any other connection in the interim. |
| 813 |
The "PRAGMA data_version" value is unchanged for commits made |
| 814 |
on the same database connection. |
| 815 |
The behavior of "PRAGMA data_version" is the same for all database |
| 816 |
connections, including database connections in separate processes |
| 817 |
and <a href="sharedcache.html">shared cache</a> database connections. |
| 818 |
|
| 819 |
<p>The "PRAGMA data_version" value is a local property of each |
| 820 |
database connection and so values returned by two concurrent invocations |
| 821 |
of "PRAGMA data_version" on separate database connections are |
| 822 |
often different even though the underlying database is identical. |
| 823 |
It is only meaningful to compare the "PRAGMA data_version" values |
| 824 |
returned by the same database connection at two different points in |
| 825 |
time. |
| 826 |
<a name="pragma_database_list"></a> |
| 827 |
<h _id=pragma_database_list style="display:none"> PRAGMA database_list</h><hr> |
| 828 |
<p><b>PRAGMA database_list;</b></p> |
| 829 |
<p>This pragma works like a query to return one row for each database |
| 830 |
attached to the current database connection. |
| 831 |
The second column is "main" for the main database file, "temp" |
| 832 |
for the database file used to store TEMP objects, or the name of the |
| 833 |
ATTACHed database for other database files. |
| 834 |
The third column is the name of the database file itself, or an empty |
| 835 |
string if the database is not associated with a file.</p> |
| 836 |
<a name="pragma_default_cache_size"></a> |
| 837 |
<h _id=pragma_default_cache_size style="display:none"> PRAGMA default_cache_size</h><hr> |
| 838 |
<b>PRAGMA </b><i>schema.</i><b>default_cache_size; |
| 839 |
<br>PRAGMA </b><i>schema.</i><b>default_cache_size |
| 840 |
= </b><i>Number-of-pages</i><b>;</b></p> |
| 841 |
|
| 842 |
<p>This pragma queries or sets the suggested maximum number of pages |
| 843 |
of disk cache that will be allocated per open database file. |
| 844 |
The difference between this pragma and <a href="pragma.html#pragma_cache_size">cache_size</a> is that the |
| 845 |
value set here persists across database connections. |
| 846 |
The value of the default cache size is stored in the 4-byte |
| 847 |
big-endian integer located at offset 48 in the header of the |
| 848 |
database file. |
| 849 |
</p> |
| 850 |
|
| 851 |
|
| 852 |
<p style='background-color: #ffd0d0;'> |
| 853 |
<b>This pragma is deprecated</b> and exists |
| 854 |
for backwards compatibility only. New applications |
| 855 |
should avoid using this pragma. Older applications should discontinue |
| 856 |
use of this pragma at the earliest opportunity. This pragma may be omitted |
| 857 |
from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>. |
| 858 |
</p> |
| 859 |
|
| 860 |
<a name="pragma_defer_foreign_keys"></a> |
| 861 |
<h _id=pragma_defer_foreign_keys style="display:none"> PRAGMA defer_foreign_keys</h><hr> |
| 862 |
<p><b>PRAGMA defer_foreign_keys |
| 863 |
<br>PRAGMA defer_foreign_keys = </b><i>boolean</i><b>;</b></p> |
| 864 |
<p>When the defer_foreign_keys <a href="pragma.html#syntax">PRAGMA</a> is on, |
| 865 |
enforcement of all <a href="foreignkeys.html">foreign key constraints</a> is delayed until the |
| 866 |
outermost transaction is committed. The defer_foreign_keys pragma |
| 867 |
defaults to OFF so that foreign key constraints are only deferred if |
| 868 |
they are created as "DEFERRABLE INITIALLY DEFERRED". The |
| 869 |
defer_foreign_keys pragma is automatically switched off at each |
| 870 |
COMMIT or ROLLBACK. Hence, the defer_foreign_keys pragma must be |
| 871 |
separately enabled for each transaction. This pragma is |
| 872 |
only meaningful if foreign key constraints are enabled, of course.</p> |
| 873 |
|
| 874 |
<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>,...) |
| 875 |
C-language interface can be used during a transaction to determine |
| 876 |
if there are deferred and unresolved foreign key constraints.</p> |
| 877 |
|
| 878 |
<p>Caution: |
| 879 |
Setting "defer_foreign_keys=0" resets the internal foreign-key |
| 880 |
tracking state of the database connection, so that it becomes |
| 881 |
possible to commit the current transaction even if there are |
| 882 |
foreign key violations.</p> |
| 883 |
<a name="pragma_empty_result_callbacks"></a> |
| 884 |
<h _id=pragma_empty_result_callbacks style="display:none"> PRAGMA empty_result_callbacks</h><hr> |
| 885 |
<p><b>PRAGMA empty_result_callbacks; |
| 886 |
<br>PRAGMA empty_result_callbacks = </b><i>boolean</i><b>;</b></p> |
| 887 |
|
| 888 |
<p>Query or change the empty-result-callbacks flag.</p> |
| 889 |
|
| 890 |
<p>The empty-result-callbacks flag affects the <a href="c3ref/exec.html">sqlite3_exec()</a> API only. |
| 891 |
Normally, when the empty-result-callbacks flag is cleared, the |
| 892 |
callback function supplied to the <a href="c3ref/exec.html">sqlite3_exec()</a> is not invoked |
| 893 |
for commands that return zero rows of data. When empty-result-callbacks |
| 894 |
is set in this situation, the callback function is invoked exactly once, |
| 895 |
with the third parameter set to 0 (NULL). This is to enable programs |
| 896 |
that use the <a href="c3ref/exec.html">sqlite3_exec()</a> API to retrieve column-names even when |
| 897 |
a query returns no data.</p> |
| 898 |
|
| 899 |
|
| 900 |
<p style='background-color: #ffd0d0;'> |
| 901 |
<b>This pragma is deprecated</b> and exists |
| 902 |
for backwards compatibility only. New applications |
| 903 |
should avoid using this pragma. Older applications should discontinue |
| 904 |
use of this pragma at the earliest opportunity. This pragma may be omitted |
| 905 |
from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>. |
| 906 |
</p> |
| 907 |
|
| 908 |
<a name="pragma_encoding"></a> |
| 909 |
<h _id=pragma_encoding style="display:none"> PRAGMA encoding</h><hr> |
| 910 |
<p><b>PRAGMA encoding; |
| 911 |
<br>PRAGMA encoding = 'UTF-8'; |
| 912 |
<br>PRAGMA encoding = 'UTF-16'; |
| 913 |
<br>PRAGMA encoding = 'UTF-16le'; |
| 914 |
<br>PRAGMA encoding = 'UTF-16be';</b></p> |
| 915 |
<p>In first form, if the main database has already been |
| 916 |
created, then this pragma returns the text encoding used by the |
| 917 |
main database, one of 'UTF-8', 'UTF-16le' (little-endian UTF-16 |
| 918 |
encoding) or 'UTF-16be' (big-endian UTF-16 encoding). If the main |
| 919 |
database has not already been created, then the value returned is the |
| 920 |
text encoding that will be used to create the main database, if |
| 921 |
it is created by this session.</p> |
| 922 |
|
| 923 |
<p>The second through fifth forms of this pragma |
| 924 |
set the encoding that the main database will be created with if |
| 925 |
it is created by this session. The string 'UTF-16' is interpreted |
| 926 |
as "UTF-16 encoding using native machine byte-ordering". It is not |
| 927 |
possible to change the text encoding of a database after it has been |
| 928 |
created and any attempt to do so will be silently ignored.</p> |
| 929 |
|
| 930 |
<p>If no encoding is first set with this pragma, |
| 931 |
then the encoding with which the main database will be created |
| 932 |
defaults to one determined by the |
| 933 |
<a href="c3ref/open.html">API used to open the connection</a>.</p> |
| 934 |
|
| 935 |
<p>Once an encoding has been set for a database, it cannot be changed.</p> |
| 936 |
|
| 937 |
<p>Databases created by the <a href="lang_attach.html">ATTACH</a> command always use the same encoding |
| 938 |
as the main database. An attempt to <a href="lang_attach.html">ATTACH</a> a database with a different |
| 939 |
text encoding from the "main" database will fail.</p> |
| 940 |
<a name="pragma_foreign_key_check"></a> |
| 941 |
<h _id=pragma_foreign_key_check style="display:none"> PRAGMA foreign_key_check</h><hr> |
| 942 |
<p><b>PRAGMA </b><i>schema.</i><b>foreign_key_check; |
| 943 |
<br>PRAGMA </b><i>schema.</i><b>foreign_key_check(</b><i>table-name</i><b>);</b></b></p> |
| 944 |
|
| 945 |
<p>The foreign_key_check pragma checks the database, or the table |
| 946 |
called "<i>table-name</i>", for |
| 947 |
<a href="foreignkeys.html">foreign key constraints</a> that are violated. The foreign_key_check |
| 948 |
pragma returns one row output for each foreign key violation. |
| 949 |
There are four columns in each result row. |
| 950 |
The first column is the name of the table that contains the REFERENCES |
| 951 |
clause. The second column is the <a href="lang_createtable.html#rowid">rowid</a> of the row that |
| 952 |
contains the invalid REFERENCES clause, or NULL if the child table is a |
| 953 |
<a href="withoutrowid.html">WITHOUT ROWID</a> table. The third column is the name |
| 954 |
of the table that is referred to. The fourth column is the index of |
| 955 |
the specific foreign key constraint that failed. The fourth column |
| 956 |
in the output of the foreign_key_check pragma is the same integer as |
| 957 |
the first column in the output of the <a href="pragma.html#pragma_foreign_key_list">foreign_key_list pragma</a>. |
| 958 |
When a "<i>table-name</i>" is specified, the only foreign key constraints |
| 959 |
checked are those created by REFERENCES clauses in the |
| 960 |
CREATE TABLE statement for <i>table-name</i>.</p> |
| 961 |
<a name="pragma_foreign_key_list"></a> |
| 962 |
<h _id=pragma_foreign_key_list style="display:none"> PRAGMA foreign_key_list</h><hr> |
| 963 |
<p><b>PRAGMA foreign_key_list(</b><i>table-name</i><b>);</b></p> |
| 964 |
|
| 965 |
<p>This pragma returns one row for each <a href="foreignkeys.html">foreign key constraint</a> |
| 966 |
created by a REFERENCES clause in the CREATE TABLE statement of |
| 967 |
table "<i>table-name</i>". |
| 968 |
<a name="pragma_foreign_keys"></a> |
| 969 |
<h _id=pragma_foreign_keys style="display:none"> PRAGMA foreign_keys</h><hr> |
| 970 |
<p><b>PRAGMA foreign_keys; |
| 971 |
<br>PRAGMA foreign_keys = </b><i>boolean</i><b>;</b></p> |
| 972 |
<p>Query, set, or clear the enforcement of <a href="foreignkeys.html">foreign key constraints</a>. |
| 973 |
|
| 974 |
<p>This pragma is a no-op within a transaction; foreign key constraint |
| 975 |
enforcement may only be enabled or disabled when there is no pending |
| 976 |
<a href="lang_transaction.html">BEGIN</a> or <a href="lang_savepoint.html">SAVEPOINT</a>. |
| 977 |
|
| 978 |
<p>Changing the foreign_keys setting affects the execution of |
| 979 |
all statements prepared |
| 980 |
using the database connection, including those prepared before the |
| 981 |
setting was changed. Any existing statements prepared using the legacy |
| 982 |
<a href="c3ref/prepare.html">sqlite3_prepare()</a> interface may fail with an <a href="rescode.html#schema">SQLITE_SCHEMA</a> error |
| 983 |
after the foreign_keys setting is changed. |
| 984 |
|
| 985 |
<p>As of SQLite <a href="releaselog/3_6_19.html">version 3.6.19</a>, the default setting for foreign |
| 986 |
key enforcement is OFF. However, that might change in a future |
| 987 |
release of SQLite. The default setting for foreign key enforcement |
| 988 |
can be specified at compile-time using the <a href="compile.html#default_foreign_keys">SQLITE_DEFAULT_FOREIGN_KEYS</a> |
| 989 |
preprocessor macro. To minimize future problems, applications should |
| 990 |
set the foreign key enforcement flag as required by the application |
| 991 |
and not depend on the default setting. |
| 992 |
<a name="pragma_freelist_count"></a> |
| 993 |
<h _id=pragma_freelist_count style="display:none"> PRAGMA freelist_count</h><hr> |
| 994 |
<p><b>PRAGMA </b><i>schema.</i><b>freelist_count;</b></p> |
| 995 |
<p>Return the number of unused pages in the database file.</p> |
| 996 |
<a name="pragma_full_column_names"></a> |
| 997 |
<h _id=pragma_full_column_names style="display:none"> PRAGMA full_column_names</h><hr> |
| 998 |
<p><b>PRAGMA full_column_names; |
| 999 |
<br>PRAGMA full_column_names = </b><i>boolean</i><b>;</b></p> |
| 1000 |
|
| 1001 |
<p>Query or change the full_column_names flag. This flag together |
| 1002 |
with the <a href="pragma.html#pragma_short_column_names">short_column_names</a> flag determine |
| 1003 |
the way SQLite assigns names to result columns of <a href="lang_select.html">SELECT</a> statements. |
| 1004 |
Result columns are named by applying the following rules in order: |
| 1005 |
<ol> |
| 1006 |
<li><p>If there is an AS clause on the result, then the name of |
| 1007 |
the column is the right-hand side of the AS clause.</p></li> |
| 1008 |
<li><p>If the result is a general expression, not just the name of |
| 1009 |
a source table column, |
| 1010 |
then the name of the result is a copy of the expression text.</p></li> |
| 1011 |
<li><p>If the <a href="pragma.html#pragma_short_column_names">short_column_names</a> pragma is ON, then the name of the |
| 1012 |
result is the name of the source table column without the |
| 1013 |
source table name prefix: COLUMN.</p></li> |
| 1014 |
<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> |
| 1015 |
are OFF then case (2) applies. |
| 1016 |
</p></li> |
| 1017 |
<li><p>The name of the result column is a combination of the source table |
| 1018 |
and source column name: TABLE.COLUMN</p></li> |
| 1019 |
</ol> |
| 1020 |
|
| 1021 |
|
| 1022 |
<p style='background-color: #ffd0d0;'> |
| 1023 |
<b>This pragma is deprecated</b> and exists |
| 1024 |
for backwards compatibility only. New applications |
| 1025 |
should avoid using this pragma. Older applications should discontinue |
| 1026 |
use of this pragma at the earliest opportunity. This pragma may be omitted |
| 1027 |
from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>. |
| 1028 |
</p> |
| 1029 |
|
| 1030 |
<a name="pragma_fullfsync"></a> |
| 1031 |
<h _id=pragma_fullfsync style="display:none"> PRAGMA fullfsync</h><hr> |
| 1032 |
<p><b>PRAGMA fullfsync |
| 1033 |
<br>PRAGMA fullfsync = </b><i>boolean</i><b>;</b></p> |
| 1034 |
<p>Query or change the fullfsync flag. This flag |
| 1035 |
determines whether or not the F_FULLFSYNC syncing method is used |
| 1036 |
on systems that support it. The default value of the fullfsync flag |
| 1037 |
is off. Only Mac OS X supports F_FULLFSYNC.</p> |
| 1038 |
|
| 1039 |
<p>See also <a href="pragma.html#pragma_checkpoint_fullfsync">checkpoint_fullfsync</a>.</p> |
| 1040 |
<a name="pragma_function_list"></a> |
| 1041 |
<h _id=pragma_function_list style="display:none"> PRAGMA function_list</h><hr> |
| 1042 |
<p><b>PRAGMA function_list;</b> |
| 1043 |
<p>This pragma returns a list of SQL functions |
| 1044 |
known to the database connection. Each row of the result |
| 1045 |
describes a single calling signature for a single SQL function. |
| 1046 |
Some SQL functions will have multiple rows in the result set |
| 1047 |
if they can (for example) be invoked with a varying number of |
| 1048 |
arguments or can accept text in various encodings. |
| 1049 |
<a name="pragma_hard_heap_limit"></a> |
| 1050 |
<h _id=pragma_hard_heap_limit style="display:none"> PRAGMA hard_heap_limit</h><hr> |
| 1051 |
<p><b>PRAGMA hard_heap_limit<br> |
| 1052 |
PRAGMA hard_heap_limit=</b><i>N</i></p> |
| 1053 |
|
| 1054 |
<p>This pragma invokes the <a href="c3ref/hard_heap_limit64.html">sqlite3_hard_heap_limit64()</a> interface with |
| 1055 |
the argument N, if N is specified and N is a positive integer that |
| 1056 |
is less than the current hard heap limit. |
| 1057 |
The hard_heap_limit pragma always returns the same integer |
| 1058 |
that would be returned by the <a href="c3ref/hard_heap_limit64.html">sqlite3_hard_heap_limit64</a>(-1) C-language |
| 1059 |
function. That is to say, it always returns the value of the hard |
| 1060 |
heap limit that is set after any changes imposed by this PRAGMA. |
| 1061 |
</p> |
| 1062 |
|
| 1063 |
<p>This pragma can only lower the heap limit, never raise it. |
| 1064 |
The C-language interface <a href="c3ref/hard_heap_limit64.html">sqlite3_hard_heap_limit64()</a> must be used |
| 1065 |
to raise the heap limit.</p> |
| 1066 |
|
| 1067 |
<p>See also the <a href="pragma.html#pragma_soft_heap_limit">soft_heap_limit pragma</a>. |
| 1068 |
<a name="pragma_ignore_check_constraints"></a> |
| 1069 |
<h _id=pragma_ignore_check_constraints style="display:none"> PRAGMA ignore_check_constraints</h><hr> |
| 1070 |
<p><b>PRAGMA ignore_check_constraints = </b><i>boolean</i><b>;</b></p> |
| 1071 |
|
| 1072 |
<p>This pragma enables or disables the enforcement of CHECK constraints. |
| 1073 |
The default setting is off, meaning that CHECK constraints are |
| 1074 |
enforced by default.</p> |
| 1075 |
<a name="pragma_incremental_vacuum"></a> |
| 1076 |
<h _id=pragma_incremental_vacuum style="display:none"> PRAGMA incremental_vacuum</h><hr> |
| 1077 |
<p><b>PRAGMA </b><i>schema.</i><b>incremental_vacuum</b><i>(N)</i><b>;<br> |
| 1078 |
PRAGMA </b><i>schema.</i><b>incremental_vacuum;</b></p> |
| 1079 |
<p>The incremental_vacuum pragma causes up to <i>N</i> pages to |
| 1080 |
be removed from the <a href="fileformat2.html#freelist">freelist</a>. The database file is truncated by |
| 1081 |
the same amount. The incremental_vacuum pragma has no effect if |
| 1082 |
the database is not in |
| 1083 |
<a href="#pragma_auto_vacuum">auto_vacuum=incremental</a> mode |
| 1084 |
or if there are no pages on the freelist. If there are fewer than |
| 1085 |
<i>N</i> pages on the freelist, or if <i>N</i> is less than 1, or |
| 1086 |
if the "(<i>N</i>)" argument is omitted, then the entire |
| 1087 |
freelist is cleared.</p> |
| 1088 |
<a name="pragma_index_info"></a> |
| 1089 |
<h _id=pragma_index_info style="display:none"> PRAGMA index_info</h><hr> |
| 1090 |
<p><b>PRAGMA </b><i>schema.</i><b>index_info(</b><i>index-name</i><b>);</b></p> |
| 1091 |
<p>This pragma returns one row for each key column in the named index. |
| 1092 |
A key column is a column that is actually named in the <a href="lang_createindex.html">CREATE INDEX</a> |
| 1093 |
index statement or <a href="lang_createtable.html#uniqueconst">UNIQUE constraint</a> or <a href="lang_createtable.html#primkeyconst">PRIMARY KEY constraint</a> that |
| 1094 |
created the index. Index entries also usually contain auxiliary |
| 1095 |
columns that point back to the table row being indexed. The auxiliary |
| 1096 |
index-columns are not shown by the index_info pragma, but they are |
| 1097 |
listed by the <a href="pragma.html#pragma_index_xinfo">index_xinfo pragma</a>.</p> |
| 1098 |
|
| 1099 |
<p>Output columns from the index_info pragma are as follows: |
| 1100 |
<ol> |
| 1101 |
<li>The rank of the column within the index. (0 means left-most.) |
| 1102 |
<li>The rank of the column within the table being indexed. |
| 1103 |
A value of -1 means <a href="lang_createtable.html#rowid">rowid</a> and a value of -2 means that an |
| 1104 |
<a href="expridx.html">expression</a> is being used. |
| 1105 |
<li>The name of the column being indexed. This columns is NULL |
| 1106 |
if the column is the <a href="lang_createtable.html#rowid">rowid</a> or an <a href="expridx.html">expression</a>. |
| 1107 |
</ol> |
| 1108 |
|
| 1109 |
<p>If there is no index named <i>index-name</i> but there is a |
| 1110 |
<a href="withoutrowid.html">WITHOUT ROWID</a> table with that name, then (as of |
| 1111 |
SQLite <a href="releaselog/3_30_0.html">version 3.30.0</a> on 2019-10-04) this pragma returns the |
| 1112 |
PRIMARY KEY columns of the WITHOUT ROWID table as they are used |
| 1113 |
in the records of the underlying b-tree, which is to say with |
| 1114 |
duplicate columns removed. |
| 1115 |
<a name="pragma_index_list"></a> |
| 1116 |
<h _id=pragma_index_list style="display:none"> PRAGMA index_list</h><hr> |
| 1117 |
<p><b>PRAGMA </b><i>schema.</i><b>index_list(</b><i>table-name</i><b>);</b></p> |
| 1118 |
<p>This pragma returns one row for each index associated with the |
| 1119 |
given table. |
| 1120 |
<p>Output columns from the index_list pragma are as follows: |
| 1121 |
<ol> |
| 1122 |
<li>A sequence number assigned to each index for internal tracking |
| 1123 |
purposes. |
| 1124 |
<li>The name of the index. |
| 1125 |
<li>"1" if the index is UNIQUE and "0" if not. |
| 1126 |
<li>"c" if the index was created by a <a href="lang_createindex.html">CREATE INDEX</a> statement, |
| 1127 |
"u" if the index was created by a <a href="lang_createtable.html#uniqueconst">UNIQUE constraint</a>, or |
| 1128 |
"pk" if the index was created by a <a href="lang_createtable.html#primkeyconst">PRIMARY KEY constraint</a>. |
| 1129 |
<li>"1" if the index is a <a href="partialindex.html">partial index</a> and "0" if not. |
| 1130 |
</ol> |
| 1131 |
</p> |
| 1132 |
<a name="pragma_index_xinfo"></a> |
| 1133 |
<h _id=pragma_index_xinfo style="display:none"> PRAGMA index_xinfo</h><hr> |
| 1134 |
<p><b>PRAGMA </b><i>schema.</i><b>index_xinfo(</b><i>index-name</i><b>);</b></p> |
| 1135 |
<p>This pragma returns information about every column in an index. |
| 1136 |
Unlike this <a href="pragma.html#pragma_index_info">index_info pragma</a>, this pragma returns information about |
| 1137 |
every column in the index, not just the key columns. |
| 1138 |
(A key column is a column that is actually named in the <a href="lang_createindex.html">CREATE INDEX</a> |
| 1139 |
index statement or <a href="lang_createtable.html#uniqueconst">UNIQUE constraint</a> or <a href="lang_createtable.html#primkeyconst">PRIMARY KEY constraint</a> that |
| 1140 |
created the index. Auxiliary columns are additional columns needed to |
| 1141 |
locate the table entry that corresponds to each index entry.) |
| 1142 |
|
| 1143 |
<p>Output columns from the index_xinfo pragma are as follows: |
| 1144 |
<ol> |
| 1145 |
<li>The rank of the column within the index. (0 means left-most. |
| 1146 |
Key columns come before auxiliary columns.) |
| 1147 |
<li>The rank of the column within the table being indexed, or -1 if |
| 1148 |
the index-column is the <a href="lang_createtable.html#rowid">rowid</a> of the table being indexed and -2 |
| 1149 |
if the <a href="expridx.html">index is on an expression</a>. |
| 1150 |
<li>The name of the column being indexed, or NULL if the index-column |
| 1151 |
is the <a href="lang_createtable.html#rowid">rowid</a> of the table being indexed or an |
| 1152 |
<a href="expridx.html">expression</a>. |
| 1153 |
<li>1 if the index-column is sorted in reverse (DESC) order by the |
| 1154 |
index and 0 otherwise. |
| 1155 |
<li>The name for the <a href="datatype3.html#collation">collating sequence</a> |
| 1156 |
used to compare values in the index-column. |
| 1157 |
<li>1 if the index-column is a key column and 0 if the index-column |
| 1158 |
is an auxiliary column. |
| 1159 |
</ol> |
| 1160 |
|
| 1161 |
<p>If there is no index named <i>index-name</i> but there is a |
| 1162 |
<a href="withoutrowid.html">WITHOUT ROWID</a> table with that name, then (as of |
| 1163 |
SQLite <a href="releaselog/3_30_0.html">version 3.30.0</a> on 2019-10-04) this pragma returns the |
| 1164 |
columns of the WITHOUT ROWID table as they are used |
| 1165 |
in the records of the underlying b-tree, which is to say with |
| 1166 |
de-duplicated PRIMARY KEY columns first followed by data columns. |
| 1167 |
<a name="pragma_integrity_check"></a> |
| 1168 |
<h _id=pragma_integrity_check style="display:none"> PRAGMA integrity_check</h><hr> |
| 1169 |
<p><b>PRAGMA </b><i>schema.</i><b>integrity_check; |
| 1170 |
<br>PRAGMA </b><i>schema.</i><b>integrity_check(</b><i>N</i><b>) |
| 1171 |
<br>PRAGMA </b><i>schema.</i><b>integrity_check(</b><i>TABLENAME</i><b>)</b></p> |
| 1172 |
<p>This pragma does a low-level formatting and consistency check |
| 1173 |
of the database. The integrity_check pragma look for: |
| 1174 |
<ul> |
| 1175 |
<li> Table or index entries that are out of sequence |
| 1176 |
<li> Misformatted records |
| 1177 |
<li> Missing pages |
| 1178 |
<li> Missing or surplus index entries |
| 1179 |
<li> UNIQUE, CHECK, and NOT NULL constraint errors |
| 1180 |
<li> Integrity of the freelist |
| 1181 |
<li> Sections of the database that are used more than once, or not at all |
| 1182 |
</ul> |
| 1183 |
<p>If the integrity_check pragma finds problems, strings are returned |
| 1184 |
(as multiple rows with a single column per row) which describe |
| 1185 |
the problems. Pragma integrity_check will return at most <i>N</i> |
| 1186 |
errors before the analysis quits, with N defaulting |
| 1187 |
to 100. If pragma integrity_check finds no errors, a |
| 1188 |
single row with the value 'ok' is returned.</p> |
| 1189 |
|
| 1190 |
<p>The usual case is that the entire database file is checked. However, |
| 1191 |
if the argument is <i>TABLENAME</i>, then checking is only performed for the |
| 1192 |
the table named and its associated indexes. |
| 1193 |
This is called a "partial integrity check". Because only a subset of the |
| 1194 |
database is checked, errors such as unused sections of the file or duplication |
| 1195 |
use of the same section of the file by two or more tables cannot be detected. |
| 1196 |
The freelist is only verified on a |
| 1197 |
partial integrity check if <i>TABLENAME</i> is <a href="schematab.html">sqlite_schema</a> or one of its |
| 1198 |
aliases. Support for partial integrity checks was added with |
| 1199 |
version 3.33.0 (2020-08-14). |
| 1200 |
|
| 1201 |
<p>PRAGMA integrity_check does not find |
| 1202 |
<a href="foreignkeys.html">FOREIGN KEY</a> errors. |
| 1203 |
Use the <a href="pragma.html#pragma_foreign_key_check">PRAGMA foreign_key_check</a> command to find errors in |
| 1204 |
FOREIGN KEY constraints.</p> |
| 1205 |
|
| 1206 |
<p>See also the <a href="pragma.html#pragma_quick_check">PRAGMA quick_check</a> command which does most of the |
| 1207 |
checking of PRAGMA integrity_check but runs much faster.</p> |
| 1208 |
<a name="pragma_journal_mode"></a> |
| 1209 |
<h _id=pragma_journal_mode style="display:none"> PRAGMA journal_mode</h><hr> |
| 1210 |
<p><b>PRAGMA </b><i>schema.</i><b>journal_mode; |
| 1211 |
<br>PRAGMA </b><i>schema.</i><b>journal_mode |
| 1212 |
= <i>DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF</i></b></p> |
| 1213 |
|
| 1214 |
<p>This pragma queries or sets the journal mode for databases |
| 1215 |
associated with the current <a href="c3ref/sqlite3.html">database connection</a>.</p> |
| 1216 |
|
| 1217 |
<p>The first form of this pragma queries the current journaling |
| 1218 |
mode for <i>database</i>. When <i>database</i> is omitted, the |
| 1219 |
"main" database is queried.</p> |
| 1220 |
|
| 1221 |
<p>The second form changes the journaling mode for "<i>database</i>" |
| 1222 |
or for all attached databases if "<i>database</i>" is omitted. |
| 1223 |
The new journal mode is returned. If the journal mode |
| 1224 |
could not be changed, the original journal mode is returned.</p> |
| 1225 |
|
| 1226 |
<p>The DELETE journaling mode is the default. In the DELETE |
| 1227 |
mode, the rollback journal is deleted at the conclusion of each |
| 1228 |
transaction. Indeed, the delete operation is the action that causes |
| 1229 |
the transaction to commit. |
| 1230 |
(See the document titled <a href="atomiccommit.html"> |
| 1231 |
Atomic Commit In SQLite</a> for additional detail.)</p> |
| 1232 |
|
| 1233 |
<p>The TRUNCATE journaling mode commits transactions by truncating |
| 1234 |
the rollback journal to zero-length instead of deleting it. On many |
| 1235 |
systems, truncating a file is much faster than deleting the file since |
| 1236 |
the containing directory does not need to be changed.</p> |
| 1237 |
|
| 1238 |
<p>The PERSIST journaling mode prevents the rollback journal from |
| 1239 |
being deleted at the end of each transaction. Instead, the header |
| 1240 |
of the journal is overwritten with zeros. This will prevent other |
| 1241 |
database connections from rolling the journal back. The PERSIST |
| 1242 |
journaling mode is useful as an optimization on platforms where |
| 1243 |
deleting or truncating a file is much more expensive than overwriting |
| 1244 |
the first block of a file with zeros. See also: |
| 1245 |
<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> |
| 1246 |
|
| 1247 |
<p>The MEMORY journaling mode stores the rollback journal in |
| 1248 |
volatile RAM. This saves disk I/O but at the expense of database |
| 1249 |
safety and integrity. If the application using SQLite crashes in |
| 1250 |
the middle of a transaction when the MEMORY journaling mode is set, |
| 1251 |
then the database file will very likely |
| 1252 |
<a href="howtocorrupt.html#cfgerr">go corrupt</a>.</p> |
| 1253 |
|
| 1254 |
<p>The WAL journaling mode uses a <a href="wal.html">write-ahead log</a> instead of a |
| 1255 |
rollback journal to implement transactions. The WAL journaling mode |
| 1256 |
is persistent; after being set it stays in effect |
| 1257 |
across multiple database connections and after closing and |
| 1258 |
reopening the database. A database in WAL journaling mode |
| 1259 |
can only be accessed by SQLite <a href="releaselog/3_7_0.html">version 3.7.0</a> (2010-07-21) |
| 1260 |
or later.</p> |
| 1261 |
|
| 1262 |
<p>The OFF journaling mode disables the rollback journal completely. |
| 1263 |
No rollback journal is ever created and hence there is never a rollback |
| 1264 |
journal to delete. The OFF journaling mode disables the atomic |
| 1265 |
commit and rollback capabilities of SQLite. The <a href="lang_transaction.html">ROLLBACK</a> command |
| 1266 |
no longer works; it behaves in an undefined way. Applications must |
| 1267 |
avoid using the <a href="lang_transaction.html">ROLLBACK</a> command when the journal mode is OFF. |
| 1268 |
If the application crashes |
| 1269 |
in the middle of a transaction when the OFF journaling mode is |
| 1270 |
set, then the database file will very likely |
| 1271 |
<a href="howtocorrupt.html#cfgerr">go corrupt</a>. Without a journal, there is no way for |
| 1272 |
a statement to unwind partially completed operations following |
| 1273 |
a constraint error. This might also leave the database in a corrupted |
| 1274 |
state. For example, if a duplicate entry causes a |
| 1275 |
<a href="lang_createindex.html">CREATE UNIQUE INDEX</a> statement to fail half-way through, |
| 1276 |
it will leave behind a partially created, and hence corrupt, index. |
| 1277 |
Because OFF journaling |
| 1278 |
mode allows the database file to be corrupted using ordinary SQL, |
| 1279 |
it is disabled when <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigdefensive">SQLITE_DBCONFIG_DEFENSIVE</a> is enabled.</p> |
| 1280 |
|
| 1281 |
<p>Note that the journal_mode for an <a href="inmemorydb.html">in-memory database</a> |
| 1282 |
is either MEMORY or OFF and can not be changed to a different value. |
| 1283 |
An attempt to change the journal_mode of an <a href="inmemorydb.html">in-memory database</a> to |
| 1284 |
any setting other than MEMORY or OFF is ignored. Note also that |
| 1285 |
the journal_mode cannot be changed while a transaction is active.</p> |
| 1286 |
<a name="pragma_journal_size_limit"></a> |
| 1287 |
<h _id=pragma_journal_size_limit style="display:none"> PRAGMA journal_size_limit</h><hr> |
| 1288 |
<p><b> |
| 1289 |
PRAGMA </b><i>schema.</i><b>journal_size_limit<br> |
| 1290 |
PRAGMA </b><i>schema.</i><b>journal_size_limit = </b><i>N</i> <b>;</b> |
| 1291 |
|
| 1292 |
<p>If a database connection is operating in |
| 1293 |
<a href="pragma.html#pragma_locking_mode">exclusive locking mode</a> or in |
| 1294 |
<a href="pragma.html#pragma_journal_mode">persistent journal mode</a> |
| 1295 |
(PRAGMA journal_mode=persist) then |
| 1296 |
after committing a transaction the <a href="lockingv3.html#rollback">rollback journal</a> file may remain in |
| 1297 |
the file-system. This increases performance for subsequent transactions |
| 1298 |
since overwriting an existing file is faster than append to a file, |
| 1299 |
but it also consumes |
| 1300 |
file-system space. After a large transaction (e.g. a <a href="lang_vacuum.html">VACUUM</a>), |
| 1301 |
the rollback journal file may consume a very large amount of space. |
| 1302 |
|
| 1303 |
<p>Similarly, in <a href="wal.html">WAL mode</a>, the write-ahead log file is not truncated |
| 1304 |
following a <a href="wal.html#ckpt">checkpoint</a>. Instead, SQLite reuses the existing file |
| 1305 |
for subsequent WAL entries since overwriting is faster than appending. |
| 1306 |
|
| 1307 |
<p>The journal_size_limit pragma may be used to limit the size of |
| 1308 |
rollback-journal and WAL files left |
| 1309 |
in the file-system after transactions or checkpoints. |
| 1310 |
Each time a transaction is committed or a WAL file resets, SQLite |
| 1311 |
compares the size of the rollback journal file or WAL file left in |
| 1312 |
the file-system to the size limit |
| 1313 |
set by this pragma and if the journal or WAL file is larger |
| 1314 |
it is truncated to the limit. |
| 1315 |
|
| 1316 |
<p>The second form of the pragma listed above is used to set a new limit |
| 1317 |
in bytes for the specified database. A negative number implies no limit. |
| 1318 |
To always truncate rollback journals and WAL files to their minimum size, |
| 1319 |
set the journal_size_limit to zero. |
| 1320 |
Both the first and second forms of the pragma listed above return a single |
| 1321 |
result row containing a single integer column - the value of the journal |
| 1322 |
size limit in bytes. The default journal size limit is -1 (no limit). The |
| 1323 |
<a href="compile.html#default_journal_size_limit">SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT</a> preprocessor macro can be used to change |
| 1324 |
the default journal size limit at compile-time.</p> |
| 1325 |
|
| 1326 |
<p>This pragma only operates on the single database specified prior |
| 1327 |
to the pragma name (or on the "main" database if no database is specified.) |
| 1328 |
There is no way to change the journal size limit on all attached databases |
| 1329 |
using a single PRAGMA statement. The size limit must be set separately for |
| 1330 |
each attached database. |
| 1331 |
<a name="pragma_legacy_alter_table"></a> |
| 1332 |
<h _id=pragma_legacy_alter_table style="display:none"> PRAGMA legacy_alter_table</h><hr> |
| 1333 |
<p><b>PRAGMA legacy_alter_table; |
| 1334 |
<br>PRAGMA legacy_alter_table = <i>boolean</i></b></p> |
| 1335 |
<p>This pragma sets or queries the value of the legacy_alter_table |
| 1336 |
flag. When this flag is on, the <a href="lang_altertable.html#altertabrename">ALTER TABLE RENAME</a> |
| 1337 |
command (for changing the name of a table) works as it did |
| 1338 |
in SQLite 3.24.0 (2018-06-04) and earlier. More specifically, |
| 1339 |
when this flag is on |
| 1340 |
the <a href="lang_altertable.html#altertabrename">ALTER TABLE RENAME</a> command only rewrites the initial occurrence |
| 1341 |
of the table name in its <a href="lang_createtable.html">CREATE TABLE</a> statement and in any associated |
| 1342 |
<a href="lang_createindex.html">CREATE INDEX</a> and <a href="lang_createtrigger.html">CREATE TRIGGER</a> statements. Other references to the |
| 1343 |
table are unmodified, including: |
| 1344 |
<ul> |
| 1345 |
<li> References to the table within the bodies of triggers and views. |
| 1346 |
<li> References to the table within CHECK constraints in the original |
| 1347 |
CREATE TABLE statement. |
| 1348 |
<li> References to the table within the WHERE clauses of <a href="partialindex.html">partial indexes</a>. |
| 1349 |
</ul> |
| 1350 |
The default setting for this pragma is OFF, which means that all |
| 1351 |
references to the table anywhere in the schema are converted to the new name. |
| 1352 |
<p>This pragma is provided as a work-around for older programs that |
| 1353 |
contain code that expect the incomplete behavior |
| 1354 |
of <a href="lang_altertable.html#altertabrename">ALTER TABLE RENAME</a> found in older versions of SQLite. |
| 1355 |
New applications should leave this flag turned off. |
| 1356 |
<p>For compatibility with older <a href="vtab.html">virtual table</a> implementations, |
| 1357 |
this flag is turned on temporarily while the <a href="vtab.html#xrename">sqlite3_module.xRename</a> |
| 1358 |
method is being run. The value of this flag is restored after the |
| 1359 |
<a href="vtab.html#xrename">sqlite3_module.xRename</a> method finishes. |
| 1360 |
<p>The legacy alter table behavior can also be toggled on and off |
| 1361 |
using the <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfiglegacyaltertable">SQLITE_DBCONFIG_LEGACY_ALTER_TABLE</a> option to the |
| 1362 |
<a href="c3ref/db_config.html">sqlite3_db_config()</a> interface. |
| 1363 |
<p>The legacy alter table behavior is a per-connection setting. Turning |
| 1364 |
this features on or off affects all attached database files within the |
| 1365 |
<a href="c3ref/sqlite3.html">database connection</a>. |
| 1366 |
The setting does not persist. Changing this setting in one connection |
| 1367 |
does not affect any other connections. |
| 1368 |
<a name="pragma_legacy_file_format"></a> |
| 1369 |
<h _id=pragma_legacy_file_format style="display:none"> PRAGMA legacy_file_format</h><hr> |
| 1370 |
<p><b>PRAGMA legacy_file_format;</b> |
| 1371 |
<p>This pragma no longer functions. It has become a no-op. |
| 1372 |
The capabilities formerly provided by PRAGMA legacy_file_format |
| 1373 |
are now available using the <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfiglegacyfileformat">SQLITE_DBCONFIG_LEGACY_FILE_FORMAT</a> |
| 1374 |
option to the <a href="c3ref/db_config.html">sqlite3_db_config()</a> C-language interface. |
| 1375 |
<p> |
| 1376 |
<a name="pragma_locking_mode"></a> |
| 1377 |
<h _id=pragma_locking_mode style="display:none"> PRAGMA locking_mode</h><hr> |
| 1378 |
<p><b>PRAGMA </b><i>schema.</i><b>locking_mode; |
| 1379 |
<br>PRAGMA </b><i>schema.</i><b>locking_mode |
| 1380 |
= <i>NORMAL | EXCLUSIVE</i></b></p> |
| 1381 |
<p>This pragma sets or queries the database connection locking-mode. |
| 1382 |
The locking-mode is either NORMAL or EXCLUSIVE. |
| 1383 |
|
| 1384 |
<p>In NORMAL locking-mode (the default unless overridden at compile-time |
| 1385 |
using <a href="compile.html#default_locking_mode">SQLITE_DEFAULT_LOCKING_MODE</a>), a database connection |
| 1386 |
unlocks the database file at the conclusion of each read or |
| 1387 |
write transaction. When the locking-mode is set to EXCLUSIVE, the |
| 1388 |
database connection never releases file-locks. The first time the |
| 1389 |
database is read in EXCLUSIVE mode, a shared lock is obtained and |
| 1390 |
held. The first time the database is written, an exclusive lock is |
| 1391 |
obtained and held.</p> |
| 1392 |
|
| 1393 |
<p>Database locks obtained by a connection in EXCLUSIVE mode may be |
| 1394 |
released either by closing the database connection, or by setting the |
| 1395 |
locking-mode back to NORMAL using this pragma and then accessing the |
| 1396 |
database file (for read or write). Simply setting the locking-mode to |
| 1397 |
NORMAL is not enough - locks are not released until the next time |
| 1398 |
the database file is accessed.</p> |
| 1399 |
|
| 1400 |
<p>There are three reasons to set the locking-mode to EXCLUSIVE. |
| 1401 |
<ol> |
| 1402 |
<li>The application wants to prevent other processes from |
| 1403 |
accessing the database file. |
| 1404 |
<li>The number of system calls for filesystem operations is reduced, |
| 1405 |
possibly resulting in a small performance increase. |
| 1406 |
<li><a href="wal.html">WAL</a> databases can be accessed in EXCLUSIVE mode without the |
| 1407 |
use of shared memory. |
| 1408 |
(<a href="wal.html#noshm">Additional information</a>) |
| 1409 |
</ol> |
| 1410 |
</p> |
| 1411 |
|
| 1412 |
<p>When the locking_mode pragma specifies a particular database, |
| 1413 |
for example:</p> |
| 1414 |
|
| 1415 |
<blockquote> |
| 1416 |
PRAGMA <b>main.</b>locking_mode=EXCLUSIVE; |
| 1417 |
</blockquote> |
| 1418 |
|
| 1419 |
<p>then the locking mode applies only to the named database. If no |
| 1420 |
database name qualifier precedes the "locking_mode" keyword then |
| 1421 |
the locking mode is applied to all databases, including any new |
| 1422 |
databases added by subsequent <a href="lang_attach.html">ATTACH</a> commands.</p> |
| 1423 |
|
| 1424 |
<p>The "temp" database (in which TEMP tables and indices are stored) |
| 1425 |
and <a href="inmemorydb.html">in-memory databases</a> |
| 1426 |
always uses exclusive locking mode. The locking mode of temp and |
| 1427 |
<a href="inmemorydb.html">in-memory databases</a> cannot |
| 1428 |
be changed. All other databases use the normal locking mode by default |
| 1429 |
and are affected by this pragma.</p> |
| 1430 |
|
| 1431 |
<p>If the locking mode is EXCLUSIVE when first entering |
| 1432 |
<a href="wal.html">WAL journal mode</a>, then the locking mode cannot be changed to |
| 1433 |
NORMAL until after exiting WAL journal mode. |
| 1434 |
If the locking mode is NORMAL when first entering WAL |
| 1435 |
journal mode, then the locking mode can be changed between NORMAL and |
| 1436 |
EXCLUSIVE and back again at any time and without needing to exit |
| 1437 |
WAL journal mode.</p> |
| 1438 |
<a name="pragma_max_page_count"></a> |
| 1439 |
<h _id=pragma_max_page_count style="display:none"> PRAGMA max_page_count</h><hr> |
| 1440 |
<p><b>PRAGMA </b><i>schema.</i><b>max_page_count; |
| 1441 |
<br>PRAGMA </b><i>schema.</i><b>max_page_count = </b><i>N</i><b>;</b></p> |
| 1442 |
<p>Query or set the maximum number of pages in the database file. |
| 1443 |
Both forms of the pragma return the maximum page count. The second |
| 1444 |
form attempts to modify the maximum page count. The maximum page |
| 1445 |
count cannot be reduced below the current database size. |
| 1446 |
</p> |
| 1447 |
<a name="pragma_mmap_size"></a> |
| 1448 |
<h _id=pragma_mmap_size style="display:none"> PRAGMA mmap_size</h><hr> |
| 1449 |
<p><br><b>PRAGMA </b><i>schema.</i><b>mmap_size; |
| 1450 |
<br>PRAGMA </b><i>schema.</i><b>mmap_size=</b><i>N</i></p> |
| 1451 |
|
| 1452 |
<p>Query or change the maximum number of bytes that are set |
| 1453 |
aside for memory-mapped I/O on a single database. The first form |
| 1454 |
(without an argument) queries the current limit. The second |
| 1455 |
form (with a numeric argument) sets the limit for the specified |
| 1456 |
database, or for all databases if the optional database name is |
| 1457 |
omitted. In the second form, if the database name is omitted, the |
| 1458 |
limit that is set becomes the default limit for all databases that |
| 1459 |
are added to the <a href="c3ref/sqlite3.html">database connection</a> by subsequent <a href="lang_attach.html">ATTACH</a> |
| 1460 |
statements.</p> |
| 1461 |
|
| 1462 |
<p>The argument N is the maximum number of bytes of the database file |
| 1463 |
that will be accessed using memory-mapped I/O. If N is zero then |
| 1464 |
memory mapped I/O is disabled. If N is negative, then the limit |
| 1465 |
reverts to the default value determined by the most recent |
| 1466 |
<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 |
| 1467 |
time default determined by <a href="compile.html#default_mmap_size">SQLITE_DEFAULT_MMAP_SIZE</a> if no |
| 1468 |
start-time limit has been set.</p> |
| 1469 |
|
| 1470 |
<p>The <a href="pragma.html#pragma_mmap_size">PRAGMA mmap_size</a> statement will never increase the amount |
| 1471 |
of address space used for memory-mapped I/O above the |
| 1472 |
hard limit set by the <a href="compile.html#max_mmap_size">SQLITE_MAX_MMAP_SIZE</a> compile-time option, |
| 1473 |
nor the hard limit set at startup-time by the second argument to |
| 1474 |
sqlite3_config(<a href="c3ref/c_config_covering_index_scan.html#sqliteconfigmmapsize">SQLITE_CONFIG_MMAP_SIZE</a>)</p> |
| 1475 |
|
| 1476 |
<p>The size of the memory-mapped I/O region cannot be changed while |
| 1477 |
the memory-mapped I/O region is in active use, to avoid unmapping |
| 1478 |
memory out from under running SQL statements. For this reason, |
| 1479 |
the mmap_size pragma may be a no-op if the prior mmap_size is non-zero |
| 1480 |
and there are other SQL statements running concurrently on the same |
| 1481 |
<a href="c3ref/sqlite3.html">database connection</a>.</p> |
| 1482 |
<a name="pragma_module_list"></a> |
| 1483 |
<h _id=pragma_module_list style="display:none"> PRAGMA module_list</h><hr> |
| 1484 |
<p><b>PRAGMA module_list;</b> |
| 1485 |
<p>This pragma returns a list of |
| 1486 |
<a href="vtab.html">virtual table</a> modules registered with the database connection. |
| 1487 |
<a name="pragma_optimize"></a> |
| 1488 |
<h _id=pragma_optimize style="display:none"> PRAGMA optimize</h><hr> |
| 1489 |
<p><b>PRAGMA optimize; |
| 1490 |
<br>PRAGMA optimize(</b><i>MASK</i><b>); |
| 1491 |
<br>PRAGMA </b><i>schema</i><b>.optimize; |
| 1492 |
<br>PRAGMA </b><i>schema</i><b>.optimize(</b><i>MASK</i><b>);</b></p> |
| 1493 |
<p>Attempt to optimize the database. All schemas are optimized in the |
| 1494 |
first two forms, and only the specified schema is optimized in the latter |
| 1495 |
two.</p> |
| 1496 |
<p>In most applications, using PRAGMA optimize as follows will help |
| 1497 |
SQLite to achieve the best possible query performance: |
| 1498 |
<ol> |
| 1499 |
<li><p> |
| 1500 |
Applications with short-lived database connections should run |
| 1501 |
"PRAGMA optimize;" once, just prior to closing each database connection. |
| 1502 |
|
| 1503 |
<li><p> |
| 1504 |
Applications that use long-lived database connections should run |
| 1505 |
"PRAGMA optimize=0x10002;" when the connection is first opened, and then |
| 1506 |
also run "PRAGMA optimize;" periodically, perhaps once per day or once |
| 1507 |
per hour. |
| 1508 |
|
| 1509 |
<li><p>All applications should run "PRAGMA optimize;" after a schema change, |
| 1510 |
especially after one or more <a href="lang_createindex.html">CREATE INDEX</a> statements. |
| 1511 |
</ol> |
| 1512 |
<p>This pragma is usually a no-op or nearly so and is very fast. On the |
| 1513 |
occasions where it does need to run ANALYZE on one or more tables, it |
| 1514 |
sets a temporary <a href="pragma.html#pragma_analysis_limit">analysis limit</a>, valid for the duration |
| 1515 |
of this pragma only, that prevents the ANALYZE invocations from running for |
| 1516 |
too long.</p> |
| 1517 |
<p>Recommended practice is that applications with short-lived database |
| 1518 |
connections should run "PRAGMA optimize" once when the database connection |
| 1519 |
closes. Applications with long-lived database connections should run |
| 1520 |
"PRAGMA optimize=0x10002" when the database connection first opens, then |
| 1521 |
run "PRAGMA optimize" again at periodic intervals - perhaps once per day. |
| 1522 |
All applications should run "PRAGMA optimize" after schema changes, especially |
| 1523 |
<a href="lang_createindex.html">CREATE INDEX</a>. </p> |
| 1524 |
<p>The details of optimizations performed by this pragma are expected |
| 1525 |
to change and improve over time. Applications should anticipate that |
| 1526 |
this pragma will perform new optimizations in future releases.</p> |
| 1527 |
|
| 1528 |
<p>The optional MASK argument is a bitmask of optimizations to perform: |
| 1529 |
|
| 1530 |
<table border=0 cellspacing=14 cellpadding=0> |
| 1531 |
<tr><td valign="top">0x00001 |
| 1532 |
<td>Debugging mode. Do not actually perform any optimizations |
| 1533 |
but instead return one line of text for each optimization |
| 1534 |
that would have been done. Off by default. |
| 1535 |
<tr><td valign="top">0x00002 |
| 1536 |
<td>Run <a href="lang_analyze.html">ANALYZE</a> on tables that might benefit. On by default.</dd> |
| 1537 |
<tr><td valign="top">0x00010 |
| 1538 |
<td>When running <a href="lang_analyze.html">ANALYZE</a>, set a temporary <a href="pragma.html#pragma_analysis_limit">PRAGMA analysis_limit</a> to |
| 1539 |
prevent excess run-time. On by default. |
| 1540 |
<tr><td valign="top">0x10000 |
| 1541 |
<td>Check the size of all tables, not just tables that have been |
| 1542 |
recently used, to see if any have grown and shrunk significantly |
| 1543 |
and hence might |
| 1544 |
benefit from being re-analyzed. Off by default. |
| 1545 |
</table> |
| 1546 |
<p>The default MASK is 0xfffe.</p> |
| 1547 |
|
| 1548 |
<p>To see all optimizations that would have been done without actually |
| 1549 |
doing them, run "PRAGMA optimize(-1)".</p> |
| 1550 |
|
| 1551 |
<p><b>Determination Of When To Run Analyze</b></p> |
| 1552 |
<p> In the current implementation, a table is analyzed if and only if |
| 1553 |
all of the following are true: |
| 1554 |
<ol> |
| 1555 |
<li> |
| 1556 |
MASK bit 0x02 is set. |
| 1557 |
<li> |
| 1558 |
The table is an ordinary table, not a view or virtual table. |
| 1559 |
<li> |
| 1560 |
The table name does not begin with "sqlite_". |
| 1561 |
<li> |
| 1562 |
One or more of the following are true: |
| 1563 |
<ol type="a"> |
| 1564 |
<li> The 0x10000 bit of MASK is set |
| 1565 |
<li> One or more indexes on the table lack entries in the sqlite_stat1 table. |
| 1566 |
<li> The query planner used sqlite_stat1 statistics for one or more indexes |
| 1567 |
of this table at some point during the lifetime of the current database |
| 1568 |
connection. |
| 1569 |
</ol> |
| 1570 |
<li> |
| 1571 |
One or more of the following are true: |
| 1572 |
<ol type="a"> |
| 1573 |
<li> One or more indexes on the table lack entries in the sqlite_stat1 table. |
| 1574 |
<li> The number of rows in the table has increased or decreased by 10-fold |
| 1575 |
since the last time ANALYZE was run on the table. |
| 1576 |
</ol> |
| 1577 |
</ol> |
| 1578 |
<p> The rules for when tables are analyzed are likely to change in |
| 1579 |
future releases. New MASK values may be added in the future. Future |
| 1580 |
versions of this pragma might accept a string literal argument instead |
| 1581 |
of a bit mask, though the bit mask argument will continue to be supported |
| 1582 |
for backwards compatibility. |
| 1583 |
<a name="pragma_page_count"></a> |
| 1584 |
<h _id=pragma_page_count style="display:none"> PRAGMA page_count</h><hr> |
| 1585 |
<p><b>PRAGMA </b><i>schema.</i><b>page_count;</b></p> |
| 1586 |
<p>Return the total number of pages in the database file.</p> |
| 1587 |
<a name="pragma_page_size"></a> |
| 1588 |
<h _id=pragma_page_size style="display:none"> PRAGMA page_size</h><hr> |
| 1589 |
<p><b>PRAGMA </b><i>schema.</i><b>page_size; |
| 1590 |
<br>PRAGMA </b><i>schema.</i><b>page_size = </b><i>bytes</i><b>;</b></p> |
| 1591 |
<p>Query or set the page size of the database. The page |
| 1592 |
size must be a power of two between 512 and 65536 inclusive. |
| 1593 |
</p> |
| 1594 |
|
| 1595 |
<p>When a new database is created, SQLite assigns a page size to |
| 1596 |
the database based on platform and filesystem. For many years, |
| 1597 |
the default page size was almost always 1024 bytes, but beginning |
| 1598 |
with SQLite <a href="releaselog/3_12_0.html">version 3.12.0</a> (2016-03-29), |
| 1599 |
the default page size increased to 4096. |
| 1600 |
The default page size is recommended for most applications. |
| 1601 |
|
| 1602 |
<p>Specifying a new page size does not change the page size |
| 1603 |
immediately. Instead, the new page size is remembered and is used |
| 1604 |
to set the page size when the database is first created, if it does |
| 1605 |
not already exist when the page_size pragma is issued, or at the |
| 1606 |
next <a href="lang_vacuum.html">VACUUM</a> command that is run on the same database connection |
| 1607 |
while not in <a href="wal.html">WAL mode</a>.</p> |
| 1608 |
|
| 1609 |
<p>The <a href="compile.html#default_page_size">SQLITE_DEFAULT_PAGE_SIZE</a> compile-time option can be used |
| 1610 |
to change the default page size assigned to new databases. |
| 1611 |
<a name="pragma_parser_trace"></a> |
| 1612 |
<h _id=pragma_parser_trace style="display:none"> PRAGMA parser_trace</h><hr> |
| 1613 |
<p><b>PRAGMA parser_trace = </b><i>boolean</i><b>; </b></p> |
| 1614 |
|
| 1615 |
<p>If SQLite has been compiled with the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time |
| 1616 |
option, then the parser_trace pragma can be used to turn on tracing |
| 1617 |
for the SQL parser used internally by SQLite. |
| 1618 |
This feature is used for debugging SQLite itself.</p> |
| 1619 |
|
| 1620 |
|
| 1621 |
<p style='background-color: #f0e0ff;'> |
| 1622 |
This pragma is intended for use when debugging SQLite itself. It |
| 1623 |
is only available when the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time option |
| 1624 |
is used.</p> |
| 1625 |
|
| 1626 |
<a name="pragma_pragma_list"></a> |
| 1627 |
<h _id=pragma_pragma_list style="display:none"> PRAGMA pragma_list</h><hr> |
| 1628 |
<p><b>PRAGMA pragma_list;</b> |
| 1629 |
<p>This pragma returns a list of PRAGMA commands |
| 1630 |
known to the database connection. |
| 1631 |
<a name="pragma_query_only"></a> |
| 1632 |
<h _id=pragma_query_only style="display:none"> PRAGMA query_only</h><hr> |
| 1633 |
<p><b>PRAGMA query_only; |
| 1634 |
<br>PRAGMA query_only = </b><i>boolean</i><b>;</b></p> |
| 1635 |
|
| 1636 |
<p>The query_only pragma prevents data changes on database files when |
| 1637 |
enabled. When this pragma is enabled, any attempt to CREATE, DELETE, |
| 1638 |
DROP, INSERT, or UPDATE will result in an <a href="rescode.html#readonly">SQLITE_READONLY</a> error. |
| 1639 |
However, the database is not truly read-only. You can still run |
| 1640 |
a <a href="wal.html#ckpt">checkpoint</a> or a <a href="lang_transaction.html">COMMIT</a> and the return value of the |
| 1641 |
<a href="c3ref/db_readonly.html">sqlite3_db_readonly()</a> routine is not affected. |
| 1642 |
</p> |
| 1643 |
<a name="pragma_quick_check"></a> |
| 1644 |
<h _id=pragma_quick_check style="display:none"> PRAGMA quick_check</h><hr> |
| 1645 |
<p><b>PRAGMA </b><i>schema.</i><b>quick_check; |
| 1646 |
<br>PRAGMA </b><i>schema.</i><b>quick_check(</b><i>N</i><b>)</b> |
| 1647 |
<br>PRAGMA </b><i>schema.</i><b>quick_check(</b><i>TABLENAME</i><b>)</b></p> |
| 1648 |
<p>The pragma is like <a href="pragma.html#pragma_integrity_check">integrity_check</a> except that it does not verify |
| 1649 |
UNIQUE constraints and does not verify |
| 1650 |
that index content matches table content. By skipping UNIQUE |
| 1651 |
and index consistency checks, quick_check is able to run faster. |
| 1652 |
PRAGMA quick_check runs in O(N) time whereas <a href="pragma.html#pragma_integrity_check">PRAGMA integrity_check</a> |
| 1653 |
requires O(NlogN) time where N is the total number of rows in the |
| 1654 |
database. Otherwise the two pragmas are the same. |
| 1655 |
</p> |
| 1656 |
<a name="pragma_read_uncommitted"></a> |
| 1657 |
<h _id=pragma_read_uncommitted style="display:none"> PRAGMA read_uncommitted</h><hr> |
| 1658 |
<p><b>PRAGMA read_uncommitted; |
| 1659 |
<br>PRAGMA read_uncommitted = </b><i>boolean</i><b>;</b></p> |
| 1660 |
<p>Query, set, or clear READ UNCOMMITTED isolation. The default isolation |
| 1661 |
level for SQLite is SERIALIZABLE. Any process or thread can select |
| 1662 |
READ UNCOMMITTED isolation, but SERIALIZABLE will still be used except |
| 1663 |
between connections that share a common page and schema cache. |
| 1664 |
Cache sharing is enabled using the <a href="c3ref/enable_shared_cache.html">sqlite3_enable_shared_cache()</a> API. |
| 1665 |
Cache sharing is disabled by default. |
| 1666 |
</p> |
| 1667 |
|
| 1668 |
<p>See <a href="sharedcache.html">SQLite Shared-Cache Mode</a> for additional information.</p> |
| 1669 |
<a name="pragma_recursive_triggers"></a> |
| 1670 |
<h _id=pragma_recursive_triggers style="display:none"> PRAGMA recursive_triggers</h><hr> |
| 1671 |
<p><b>PRAGMA recursive_triggers; |
| 1672 |
<br>PRAGMA recursive_triggers = </b><i>boolean</i><b>;</b></p> |
| 1673 |
<p>Query, set, or clear the recursive trigger capability. |
| 1674 |
|
| 1675 |
<p>Changing the recursive_triggers setting affects the execution of |
| 1676 |
all statements prepared |
| 1677 |
using the database connection, including those prepared before the |
| 1678 |
setting was changed. Any existing statements prepared using the legacy |
| 1679 |
<a href="c3ref/prepare.html">sqlite3_prepare()</a> interface may fail with an <a href="rescode.html#schema">SQLITE_SCHEMA</a> error |
| 1680 |
after the recursive_triggers setting is changed. |
| 1681 |
|
| 1682 |
<p>Prior to SQLite <a href="releaselog/3_6_18.html">version 3.6.18</a> (2009-09-11), |
| 1683 |
recursive triggers were not supported. |
| 1684 |
The behavior of SQLite was always as if this pragma was |
| 1685 |
set to OFF. Support for recursive triggers was added in version 3.6.18 |
| 1686 |
but was initially turned OFF by default, for compatibility. Recursive |
| 1687 |
triggers may be turned on by default in future versions of SQLite. |
| 1688 |
</p> |
| 1689 |
|
| 1690 |
<p>The depth of recursion for triggers has a hard upper limit set by |
| 1691 |
the <a href="limits.html#max_trigger_depth">SQLITE_MAX_TRIGGER_DEPTH</a> compile-time option and a run-time |
| 1692 |
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> |
| 1693 |
<a name="pragma_reverse_unordered_selects"></a> |
| 1694 |
<h _id=pragma_reverse_unordered_selects style="display:none"> PRAGMA reverse_unordered_selects</h><hr> |
| 1695 |
<p><b>PRAGMA reverse_unordered_selects; |
| 1696 |
<br>PRAGMA reverse_unordered_selects = </b><i>boolean</i><b>;</b></p> |
| 1697 |
<p>When enabled, this PRAGMA causes many <a href="lang_select.html">SELECT</a> statements without |
| 1698 |
an ORDER BY clause to emit their results in the reverse order from what |
| 1699 |
they normally would. This can help debug applications that are |
| 1700 |
making invalid assumptions about the result order. |
| 1701 |
The reverse_unordered_selects pragma works for most SELECT statements, |
| 1702 |
however the query planner may sometimes choose an algorithm that is |
| 1703 |
not easily reversed, in which case the output will appear in the same |
| 1704 |
order regardless of the reverse_unordered_selects setting. |
| 1705 |
<p>SQLite makes no |
| 1706 |
guarantees about the order of results if a SELECT omits the ORDER BY |
| 1707 |
clause. Even so, the order of results does not change from one |
| 1708 |
run to the next, and so many applications mistakenly come to depend |
| 1709 |
on the arbitrary output order whatever that order happens to be. However, |
| 1710 |
sometimes new versions of SQLite will contain optimizer enhancements |
| 1711 |
that will cause the output order of queries without ORDER BY clauses |
| 1712 |
to shift. When that happens, applications that depend on a certain |
| 1713 |
output order might malfunction. By running the application multiple |
| 1714 |
times with this pragma both disabled and enabled, cases where the |
| 1715 |
application makes faulty assumptions about output order can be |
| 1716 |
identified and fixed early, reducing problems |
| 1717 |
that might be caused by linking against a different version of SQLite. |
| 1718 |
</p> |
| 1719 |
<a name="pragma_schema_version"></a> |
| 1720 |
<h _id=pragma_schema_version style="display:none"> PRAGMA schema_version</h><hr> |
| 1721 |
<p><b>PRAGMA </b><i>schema.</i><b>schema_version; |
| 1722 |
<br>PRAGMA </b><i>schema.</i><b>schema_version = </b><i>integer </i>; |
| 1723 |
|
| 1724 |
<p> The schema_version pragma will get or set |
| 1725 |
the value of the schema-version integer at offset 40 in the |
| 1726 |
<a href="fileformat2.html#database_header">database header</a>. |
| 1727 |
|
| 1728 |
<p> SQLite automatically increments the schema-version whenever the |
| 1729 |
schema changes. As each SQL statement runs, the schema version is |
| 1730 |
checked to ensure that the schema has not changed since the SQL |
| 1731 |
statement was <a href="c3ref/prepare.html">prepared</a>. |
| 1732 |
Subverting this mechanism by using "PRAGMA schema_version=N" |
| 1733 |
to change the value of the schema_version |
| 1734 |
may cause SQL statement to run using an obsolete schema, |
| 1735 |
which can lead to incorrect answers and/or |
| 1736 |
<a href="howtocorrupt.html#cfgerr">database corruption</a>. |
| 1737 |
It is always safe to read the schema_version, but changing the |
| 1738 |
schema_version can cause problems. For this reason, attempts |
| 1739 |
to change the value of schema_version are a silent no-op when |
| 1740 |
<a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigdefensive">defensive mode</a> is enabled for a |
| 1741 |
database connection. |
| 1742 |
|
| 1743 |
|
| 1744 |
<p><span style='background-color: #ffff60;'> |
| 1745 |
<b>Warning:</b> |
| 1746 |
Misuse of this pragma can result in <a href="howtocorrupt.html#cfgerr">database corruption</a>. |
| 1747 |
</span></p> |
| 1748 |
|
| 1749 |
|
| 1750 |
<p> For the purposes of this pragma, the <a href="lang_vacuum.html">VACUUM</a> command is considered |
| 1751 |
a schema change, since <a href="lang_vacuum.html">VACUUM</a> will usually alter the "rootpage" |
| 1752 |
values for entries in the <a href="schematab.html">sqlite_schema table</a>. |
| 1753 |
|
| 1754 |
|
| 1755 |
<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>. |
| 1756 |
<a name="pragma_secure_delete"></a> |
| 1757 |
<h _id=pragma_secure_delete style="display:none"> PRAGMA secure_delete</h><hr> |
| 1758 |
<p><b>PRAGMA </b><i>schema.</i><b>secure_delete; |
| 1759 |
<br>PRAGMA </b><i>schema.</i><b>secure_delete = </b><i>boolean</i>|<b>FAST</b></p> |
| 1760 |
<p>Query or change the secure-delete setting. When secure_delete is |
| 1761 |
on, SQLite overwrites deleted content with zeros. The default |
| 1762 |
setting for secure_delete is determined by the <a href="compile.html#secure_delete">SQLITE_SECURE_DELETE</a> |
| 1763 |
compile-time option and is normally off. The off setting for |
| 1764 |
secure_delete improves performance by reducing the number of CPU cycles |
| 1765 |
and the amount of disk I/O. Applications that wish to avoid leaving |
| 1766 |
forensic traces after content is deleted or updated should enable the |
| 1767 |
secure_delete pragma prior to performing the delete or update, or else |
| 1768 |
run <a href="lang_vacuum.html">VACUUM</a> after the delete or update. |
| 1769 |
|
| 1770 |
<p>The "fast" setting for secure_delete (added circa 2017-08-01) |
| 1771 |
is an intermediate setting in between "on" and "off". |
| 1772 |
When secure_delete is set to "fast", |
| 1773 |
SQLite will overwrite deleted content with zeros only if doing so |
| 1774 |
does not increase the amount of I/O. In other words, the "fast" |
| 1775 |
setting uses more CPU cycles but does not use more I/O. |
| 1776 |
This has the effect of purging all old content from <a href="fileformat2.html#btree">b-tree pages</a>, |
| 1777 |
but leaving forensic traces on <a href="fileformat2.html#freelist">freelist pages</a>. |
| 1778 |
|
| 1779 |
<p> |
| 1780 |
When there are <a href="lang_attach.html">attached databases</a> and no database |
| 1781 |
is specified in the pragma, all databases have their secure-delete |
| 1782 |
setting altered. |
| 1783 |
The secure-delete setting for newly attached databases is the setting |
| 1784 |
of the main database at the time the ATTACH command is evaluated. |
| 1785 |
|
| 1786 |
<p> |
| 1787 |
When multiple database connections share the same cache, changing |
| 1788 |
the secure-delete flag on one database connection changes it for them |
| 1789 |
all. |
| 1790 |
</p> |
| 1791 |
|
| 1792 |
<p><b>Limitation:</b> |
| 1793 |
The secure_delete pragma only causes deleted content to be scrubbed |
| 1794 |
from ordinary tables. If <a href="vtab.html">virtual tables</a> store content in |
| 1795 |
<a href="vtab.html#xshadowname">shadow tables</a>, then deleting content from the virtual table does |
| 1796 |
not necessarily remove forensic traces from the shadow tables. |
| 1797 |
In particular, the <a href="fts3.html">FTS3</a> and <a href="fts5.html">FTS5</a> virtual tables that come |
| 1798 |
bundled with SQLite might leave forensic traces in their shadow tables |
| 1799 |
even if the secure_delete pragma is enabled. |
| 1800 |
</p> |
| 1801 |
<a name="pragma_short_column_names"></a> |
| 1802 |
<h _id=pragma_short_column_names style="display:none"> PRAGMA short_column_names</h><hr> |
| 1803 |
<p><b>PRAGMA short_column_names; |
| 1804 |
<br>PRAGMA short_column_names = </b><i>boolean</i><b>;</b></p> |
| 1805 |
|
| 1806 |
<p>Query or change the short-column-names flag. This flag affects |
| 1807 |
the way SQLite names columns of data returned by <a href="lang_select.html">SELECT</a> statements. |
| 1808 |
See the <a href="pragma.html#pragma_full_column_names">full_column_names</a> pragma for full details. |
| 1809 |
</p> |
| 1810 |
|
| 1811 |
|
| 1812 |
<p style='background-color: #ffd0d0;'> |
| 1813 |
<b>This pragma is deprecated</b> and exists |
| 1814 |
for backwards compatibility only. New applications |
| 1815 |
should avoid using this pragma. Older applications should discontinue |
| 1816 |
use of this pragma at the earliest opportunity. This pragma may be omitted |
| 1817 |
from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>. |
| 1818 |
</p> |
| 1819 |
|
| 1820 |
<a name="pragma_shrink_memory"></a> |
| 1821 |
<h _id=pragma_shrink_memory style="display:none"> PRAGMA shrink_memory</h><hr> |
| 1822 |
<p><b>PRAGMA shrink_memory</b></p> |
| 1823 |
|
| 1824 |
<p>This pragma causes the database connection on which it is invoked |
| 1825 |
to free up as much memory as it can, by calling |
| 1826 |
<a href="c3ref/db_release_memory.html">sqlite3_db_release_memory()</a>. |
| 1827 |
</p> |
| 1828 |
<a name="pragma_soft_heap_limit"></a> |
| 1829 |
<h _id=pragma_soft_heap_limit style="display:none"> PRAGMA soft_heap_limit</h><hr> |
| 1830 |
<p><b>PRAGMA soft_heap_limit<br> |
| 1831 |
PRAGMA soft_heap_limit=</b><i>N</i></p> |
| 1832 |
|
| 1833 |
<p>This pragma invokes the <a href="c3ref/hard_heap_limit64.html">sqlite3_soft_heap_limit64()</a> interface with |
| 1834 |
the argument N, if N is specified and is a non-negative integer. |
| 1835 |
The soft_heap_limit pragma always returns the same integer |
| 1836 |
that would be returned by the <a href="c3ref/hard_heap_limit64.html">sqlite3_soft_heap_limit64</a>(-1) C-language |
| 1837 |
function. |
| 1838 |
</p> |
| 1839 |
<p>See also the <a href="pragma.html#pragma_hard_heap_limit">hard_heap_limit pragma</a>. |
| 1840 |
<a name="pragma_stats"></a> |
| 1841 |
<h _id=pragma_stats style="display:none"> PRAGMA stats</h><hr> |
| 1842 |
<p><b>PRAGMA stats;</b> </p> |
| 1843 |
<p>This pragma returns auxiliary information about tables and |
| 1844 |
indices. The returned information is used during testing to help |
| 1845 |
verify that the query planner is operating correctly. The format |
| 1846 |
and meaning of this pragma will likely change from one release |
| 1847 |
to the next. Because of its volatility, the behavior and output |
| 1848 |
format of this pragma are deliberately undocumented.</p> |
| 1849 |
|
| 1850 |
|
| 1851 |
<p style='background-color: #f0e0ff;'> |
| 1852 |
The intended use of this pragma is only for testing and validation of |
| 1853 |
SQLite. This pragma is subject to change without notice and is not |
| 1854 |
recommended for use by application programs.</p> |
| 1855 |
|
| 1856 |
<a name="pragma_synchronous"></a> |
| 1857 |
<h _id=pragma_synchronous style="display:none"> PRAGMA synchronous</h><hr> |
| 1858 |
<p><b>PRAGMA </b><i>schema.</i><b>synchronous; |
| 1859 |
<br>PRAGMA </b><i>schema.</i><b>synchronous = </b> |
| 1860 |
<i>0 | OFF | 1 | NORMAL | 2 | FULL | 3 | EXTRA</i><b>;</b></p> |
| 1861 |
|
| 1862 |
<p>Query or change the setting of the "synchronous" flag. |
| 1863 |
The first (query) form will return the synchronous setting as an |
| 1864 |
integer. The second form changes the synchronous setting. |
| 1865 |
The meanings of the various synchronous settings are as follows:</p> |
| 1866 |
<dl> |
| 1867 |
<dt><b>EXTRA</b> (3)</dt> |
| 1868 |
<dd><p> |
| 1869 |
EXTRA synchronous is like FULL with the addition that the directory |
| 1870 |
containing a <a href="lockingv3.html#rollback">rollback journal</a> is synced after that journal is unlinked |
| 1871 |
to commit a transaction in DELETE mode. EXTRA provides additional |
| 1872 |
durability if the commit is followed closely by a power loss. Without |
| 1873 |
EXTRA, depending on the underlying filesystem, it is possible that a |
| 1874 |
single transaction that commits right before a power loss might get |
| 1875 |
rolled back upon reboot. The database will not go corrupt. But the |
| 1876 |
last transaction might go missing, thus violating durability, if |
| 1877 |
EXTRA is not set. |
| 1878 |
<p> |
| 1879 |
EXTRA is no different from FULL in <a href="wal.html">WAL mode</a>. |
| 1880 |
</dd> |
| 1881 |
<dt><b>FULL</b> (2)</dt> |
| 1882 |
<dd><p> |
| 1883 |
When synchronous is FULL (2), the SQLite database engine will |
| 1884 |
use the xSync method of the <a href="vfs.html">VFS</a> to ensure that all content is safely |
| 1885 |
written to the disk surface prior to continuing. |
| 1886 |
This ensures that an operating system crash or power failure will |
| 1887 |
not corrupt the database. |
| 1888 |
<p> |
| 1889 |
FULL is the default synchronous mode for a <a href="lockingv3.html#rollback">rollback journal</a>. |
| 1890 |
FULL is atomic, consistent, isolated, and durable (ACID) in <a href="wal.html">WAL mode</a> and |
| 1891 |
is atomic, consistent, and isolated with a <a href="lockingv3.html#rollback">rollback journal</a>. FULL |
| 1892 |
might also be durable using a rollback journal, depending on the |
| 1893 |
underlying filesystem. FULL is not necessarily durable across a power |
| 1894 |
loss in rollback mode, so if durability is desired, it is best to |
| 1895 |
set the synchronous mode to EXTRA. |
| 1896 |
<dt><b>NORMAL</b> (1)</dt> |
| 1897 |
<dd><p> |
| 1898 |
When synchronous is NORMAL (1), the SQLite database |
| 1899 |
engine will still sync at the most critical moments, but less often |
| 1900 |
than in FULL mode. There is a very small (though non-zero) chance that |
| 1901 |
a power failure at just the wrong time could corrupt the database in |
| 1902 |
<a href="pragma.html#pragma_journal_mode">journal_mode</a>=DELETE on an older filesystem. |
| 1903 |
<a href="wal.html">WAL mode</a> is safe from corruption with synchronous=NORMAL, and probably |
| 1904 |
DELETE mode is safe too on modern filesystems. WAL mode is always consistent |
| 1905 |
with synchronous=NORMAL, but WAL mode does lose durability. A transaction |
| 1906 |
committed in WAL mode with synchronous=NORMAL might roll back following |
| 1907 |
a power loss or system crash. Transactions are durable across application |
| 1908 |
crashes regardless of the synchronous setting or journal mode. |
| 1909 |
<p> |
| 1910 |
The synchronous=NORMAL setting provides the best balance between performance |
| 1911 |
and safety for most applications running in <a href="wal.html">WAL mode</a>. You lose durability |
| 1912 |
across power lose with synchronous NORMAL in WAL mode, but that is not |
| 1913 |
important for most applications. Transactions are still atomic, consistent, |
| 1914 |
and isolated, which are the most important characteristics in most use |
| 1915 |
cases.</dd> |
| 1916 |
<dt><b>OFF</b> (0)</dt> |
| 1917 |
<dd><p> |
| 1918 |
With synchronous OFF (0), SQLite continues without syncing |
| 1919 |
as soon as it has handed data off to the operating system. |
| 1920 |
If the application running SQLite crashes, the data will be safe, but |
| 1921 |
the database <a href="howtocorrupt.html#cfgerr">might become corrupted</a> if the operating system |
| 1922 |
crashes or the computer loses power before that data has been written |
| 1923 |
to non-volatile storage. |
| 1924 |
On the other hand, commits can be much faster with synchronous OFF. |
| 1925 |
<p> |
| 1926 |
Setting synchronous to OFF is a good option when creating a new database |
| 1927 |
from scratch, in a scenario where the process of creating the database |
| 1928 |
can be repeated if a power loss occurs in the middle, and when |
| 1929 |
performance is critical. |
| 1930 |
</dd></dl> |
| 1931 |
</p> |
| 1932 |
|
| 1933 |
<p>Here is the matrix: |
| 1934 |
|
| 1935 |
<p><table border="1" cellpadding="0" cellspacing="0"> |
| 1936 |
<tr> |
| 1937 |
<th> |
| 1938 |
<th> Rollback Mode |
| 1939 |
<th> WAL Mode |
| 1940 |
</tr> |
| 1941 |
<tr> |
| 1942 |
<th> EXTRA |
| 1943 |
<td align="center"> ACID |
| 1944 |
<td align="center"> ACID |
| 1945 |
</tr> |
| 1946 |
<tr> |
| 1947 |
<th> FULL |
| 1948 |
<td align="center"> Maybe not durable |
| 1949 |
<td align="center"> ACID |
| 1950 |
</tr> |
| 1951 |
<tr> |
| 1952 |
<th> NORMAL |
| 1953 |
<td align="center"> Maybe not consistent |
| 1954 |
<td align="center"> Maybe not durable |
| 1955 |
</tr> |
| 1956 |
<tr> |
| 1957 |
<th> OFF |
| 1958 |
<td align="center"> Not consistent |
| 1959 |
<td align="center"> Not consistent |
| 1960 |
</tr> |
| 1961 |
</table> |
| 1962 |
|
| 1963 |
|
| 1964 |
<p>In <a href="wal.html">WAL</a> mode when synchronous is NORMAL (1), the WAL file is |
| 1965 |
synchronized before each <a href="wal.html#ckpt">checkpoint</a> and the database file is |
| 1966 |
synchronized after each completed <a href="wal.html#ckpt">checkpoint</a> and the WAL file |
| 1967 |
header is synchronized when a WAL file begins to be reused after |
| 1968 |
a checkpoint, but no sync operations occur during most transactions. |
| 1969 |
With synchronous=FULL in WAL mode, an additional |
| 1970 |
sync operation of the WAL file happens after each transaction commit. |
| 1971 |
The extra WAL sync following each transaction helps ensure that |
| 1972 |
transactions are durable across a power loss. Transactions are |
| 1973 |
consistent with or without the extra syncs provided by |
| 1974 |
synchronous=FULL. |
| 1975 |
If durability is not a concern, then synchronous=NORMAL is normally |
| 1976 |
all one needs in WAL mode.</p> |
| 1977 |
|
| 1978 |
<p>The TEMP schema always has synchronous=OFF since the content of |
| 1979 |
of TEMP is ephemeral and is not expected to survive a power outage. |
| 1980 |
Attempts to change the synchronous setting for TEMP are |
| 1981 |
silently ignored. |
| 1982 |
|
| 1983 |
<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> |
| 1984 |
<a name="pragma_table_info"></a> |
| 1985 |
<h _id=pragma_table_info style="display:none"> PRAGMA table_info</h><hr> |
| 1986 |
<p><b>PRAGMA </b><i>schema.</i><b>table_info(</b><i>table-name</i><b>);</b></p> |
| 1987 |
<p>This pragma returns one row for each normal column |
| 1988 |
in the named table. |
| 1989 |
Columns in the result set include: "name" (its name); "type" |
| 1990 |
(data type if given, else ''); "notnull" (whether or not the column |
| 1991 |
can be NULL); "dflt_value" (the default value for the column); |
| 1992 |
and "pk" (either zero for columns that are not part of the primary key, |
| 1993 |
or the 1-based index of the column within the primary key).</p> |
| 1994 |
<p>The "cid" column should not be taken to mean more than |
| 1995 |
"rank within the current result set".</p> |
| 1996 |
<p>The table named in the table_info pragma can also be a view.</p> |
| 1997 |
<p>This pragma does not show information about <a href="gencol.html">generated columns</a> or |
| 1998 |
<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 |
| 1999 |
of columns that includes generated and hidden columns. |
| 2000 |
<a name="pragma_table_list"></a> |
| 2001 |
<h _id=pragma_table_list style="display:none"> PRAGMA table_list</h><hr> |
| 2002 |
<p> <b>PRAGMA table_list; |
| 2003 |
<br>PRAGMA </b><i>schema.</i><b>table_list; |
| 2004 |
<br>PRAGMA table_list(</b><i>table-name</i><b>);</b></p> |
| 2005 |
<p>This pragma returns information about the tables and views in the schema, |
| 2006 |
one table per row of output. The table_list pragma first appeared |
| 2007 |
in SQLite version 3.37.0 (2021-11-27). As of its initial release |
| 2008 |
the columns returned by the table_list pragma include those listed below. |
| 2009 |
Future versions of SQLite will probably add additional columns of |
| 2010 |
output. |
| 2011 |
<p> |
| 2012 |
<ol> |
| 2013 |
<li> <b>schema</b>: the schema in which the table or view appears |
| 2014 |
(for example "main" or "temp"). |
| 2015 |
<li> <b>name</b>: the name of the table or view. |
| 2016 |
<li> <b>type</b>: the type of object - one of "table", "view", |
| 2017 |
"shadow" (for <a href="vtab.html#xshadowname">shadow tables</a>), or "virtual" for |
| 2018 |
<a href="vtab.html">virtual tables</a>. |
| 2019 |
<li> <b>ncol</b>: the number of columns in the table, including |
| 2020 |
<a href="gencol.html">generated columns</a> and <a href="vtab.html#hiddencol">hidden columns</a>. |
| 2021 |
<li> <b>wr</b>: 1 if the table is a <a href="withoutrowid.html">WITHOUT ROWID</a> table or 0 if is not. |
| 2022 |
<li> <b>strict</b>: 1 if the table is a <a href="stricttables.html">STRICT table</a> or 0 if it is not. |
| 2023 |
<li> <i>Additional columns will likely be added in future releases.</i> |
| 2024 |
</ol> |
| 2025 |
<p> |
| 2026 |
The default behavior is to show all tables in all schemas. If the |
| 2027 |
<i>schema.</i> name appears before the pragma, then only tables in that |
| 2028 |
one schema are shown. If a <i>table-name</i> argument is supplied, then |
| 2029 |
only information about that one table is returned. |
| 2030 |
<a name="pragma_table_xinfo"></a> |
| 2031 |
<h _id=pragma_table_xinfo style="display:none"> PRAGMA table_xinfo</h><hr> |
| 2032 |
<p><b>PRAGMA </b><i>schema.</i><b>table_xinfo(</b><i>table-name</i><b>);</b></p> |
| 2033 |
<p>This pragma returns one row for each column in the named table, |
| 2034 |
including <a href="gencol.html">generated columns</a> and <a href="vtab.html#hiddencol">hidden columns</a>. |
| 2035 |
The output has the same columns as for <a href="pragma.html#pragma_table_info">PRAGMA table_info</a> plus |
| 2036 |
a column, "hidden", whose value signifies a normal column (0), |
| 2037 |
a dynamic or stored generated column (2 or 3), |
| 2038 |
or a hidden column in a virtual table (1). The rows for which |
| 2039 |
this field is non-zero are those omitted for <a href="pragma.html#pragma_table_info">PRAGMA table_info</a>. |
| 2040 |
<a name="pragma_temp_store"></a> |
| 2041 |
<h _id=pragma_temp_store style="display:none"> PRAGMA temp_store</h><hr> |
| 2042 |
<p><b>PRAGMA temp_store; |
| 2043 |
<br>PRAGMA temp_store = </b> |
| 2044 |
<i>0 | DEFAULT | 1 | FILE | 2 | MEMORY</i><b>;</b></p> |
| 2045 |
|
| 2046 |
<p>Query or change the setting of the "<b>temp_store</b>" parameter. |
| 2047 |
When temp_store is DEFAULT (0), the compile-time C preprocessor macro |
| 2048 |
<a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> is used to determine where temporary tables and indices |
| 2049 |
are stored. When |
| 2050 |
temp_store is MEMORY (2) <a href="inmemorydb.html#temp_db">temporary tables</a> and indices are kept |
| 2051 |
as if they were in pure <a href="inmemorydb.html">in-memory databases</a>. |
| 2052 |
When temp_store is FILE (1) <a href="inmemorydb.html#temp_db">temporary tables</a> and indices are stored |
| 2053 |
in a file. The <a href="pragma.html#pragma_temp_store_directory">temp_store_directory</a> pragma can be used to specify |
| 2054 |
the directory containing temporary files when |
| 2055 |
<b>FILE</b> is specified. When the temp_store setting is changed, |
| 2056 |
all existing temporary tables, indices, triggers, and views are |
| 2057 |
immediately deleted.</p> |
| 2058 |
|
| 2059 |
<p>It is possible for the library compile-time C preprocessor symbol |
| 2060 |
<a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> to override this pragma setting. |
| 2061 |
The following table summarizes |
| 2062 |
the interaction of the <a href="compile.html#temp_store">SQLITE_TEMP_STORE</a> preprocessor macro and the |
| 2063 |
temp_store pragma:</p> |
| 2064 |
|
| 2065 |
<blockquote> |
| 2066 |
<table cellpadding="2" border="1"> |
| 2067 |
<tr><th valign="bottom"><a href="compile.html#temp_store">SQLITE_TEMP_STORE</a></th> |
| 2068 |
<th valign="bottom">PRAGMA<br>temp_store</th> |
| 2069 |
<th>Storage used for<br>TEMP tables and indices</th></tr> |
| 2070 |
<tr><td align="center">0</td> |
| 2071 |
<td align="center"><em>any</em></td> |
| 2072 |
<td align="center">file</td></tr> |
| 2073 |
<tr><td align="center">1</td> |
| 2074 |
<td align="center">0</td> |
| 2075 |
<td align="center">file</td></tr> |
| 2076 |
<tr><td align="center">1</td> |
| 2077 |
<td align="center">1</td> |
| 2078 |
<td align="center">file</td></tr> |
| 2079 |
<tr><td align="center">1</td> |
| 2080 |
<td align="center">2</td> |
| 2081 |
<td align="center">memory</td></tr> |
| 2082 |
<tr><td align="center">2</td> |
| 2083 |
<td align="center">0</td> |
| 2084 |
<td align="center">memory</td></tr> |
| 2085 |
<tr><td align="center">2</td> |
| 2086 |
<td align="center">1</td> |
| 2087 |
<td align="center">file</td></tr> |
| 2088 |
<tr><td align="center">2</td> |
| 2089 |
<td align="center">2</td> |
| 2090 |
<td align="center">memory</td></tr> |
| 2091 |
<tr><td align="center">3</td> |
| 2092 |
<td align="center"><em>any</em></td> |
| 2093 |
<td align="center">memory</td></tr> |
| 2094 |
</table> |
| 2095 |
</blockquote> |
| 2096 |
<a name="pragma_temp_store_directory"></a> |
| 2097 |
<h _id=pragma_temp_store_directory style="display:none"> PRAGMA temp_store_directory</h><hr> |
| 2098 |
<p><b>PRAGMA temp_store_directory; |
| 2099 |
<br>PRAGMA temp_store_directory = '</b><i>directory-name</i><b>';</b></p> |
| 2100 |
<p>Query or change the value of the <a href="c3ref/temp_directory.html">sqlite3_temp_directory</a> global |
| 2101 |
variable, which many operating-system interface backends use to |
| 2102 |
determine where to store <a href="inmemorydb.html#temp_db">temporary tables</a> and indices.</p> |
| 2103 |
|
| 2104 |
<p>When the temp_store_directory setting is changed, all existing temporary |
| 2105 |
tables, indices, triggers, and viewers in the database connection that |
| 2106 |
issued the pragma are immediately deleted. In |
| 2107 |
practice, temp_store_directory should be set immediately after the first |
| 2108 |
database connection for a process is opened. If the temp_store_directory |
| 2109 |
is changed for one database connection while other database connections |
| 2110 |
are open in the same process, then the behavior is undefined and |
| 2111 |
probably undesirable.</p> |
| 2112 |
|
| 2113 |
<p>Changing the temp_store_directory setting is <u>not</u> threadsafe. |
| 2114 |
Never change the temp_store_directory setting if another thread |
| 2115 |
within the application is running any SQLite interface at the same time. |
| 2116 |
Doing so results in undefined behavior. Changing the temp_store_directory |
| 2117 |
setting writes to the <a href="c3ref/temp_directory.html">sqlite3_temp_directory</a> global |
| 2118 |
variable and that global variable is not protected by a mutex.</p> |
| 2119 |
|
| 2120 |
<p>The value <i>directory-name</i> should be enclosed in single quotes. |
| 2121 |
To revert the directory to the default, set the <i>directory-name</i> to |
| 2122 |
an empty string, e.g., <i>PRAGMA temp_store_directory = ''</i>. An |
| 2123 |
error is raised if <i>directory-name</i> is not found or is not |
| 2124 |
writable. </p> |
| 2125 |
|
| 2126 |
<p>The default directory for temporary files depends on the OS. Some |
| 2127 |
OS interfaces may choose to ignore this variable and place temporary |
| 2128 |
files in some other directory different from the directory specified |
| 2129 |
here. In that sense, this pragma is only advisory.</p> |
| 2130 |
|
| 2131 |
|
| 2132 |
<p style='background-color: #ffd0d0;'> |
| 2133 |
<b>This pragma is deprecated</b> and exists |
| 2134 |
for backwards compatibility only. New applications |
| 2135 |
should avoid using this pragma. Older applications should discontinue |
| 2136 |
use of this pragma at the earliest opportunity. This pragma may be omitted |
| 2137 |
from the build when SQLite is compiled using <a href="compile.html#omit_deprecated">SQLITE_OMIT_DEPRECATED</a>. |
| 2138 |
</p> |
| 2139 |
|
| 2140 |
<a name="pragma_threads"></a> |
| 2141 |
<h _id=pragma_threads style="display:none"> PRAGMA threads</h><hr> |
| 2142 |
<p><b>PRAGMA threads; |
| 2143 |
<br>PRAGMA threads = </b><i>N</i><b>;</b></p> |
| 2144 |
<p>Query or change the value of the |
| 2145 |
<a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitworkerthreads">SQLITE_LIMIT_WORKER_THREADS</a>,...) limit for |
| 2146 |
the current database connection. This limit sets an upper bound |
| 2147 |
on the number of auxiliary threads that a <a href="c3ref/stmt.html">prepared statement</a> is |
| 2148 |
allowed to launch to assist with a query. The default limit is 0 |
| 2149 |
unless it is changed using the <a href="compile.html#default_worker_threads">SQLITE_DEFAULT_WORKER_THREADS</a> |
| 2150 |
compile-time option. When the limit is zero, that means no |
| 2151 |
auxiliary threads will be launched.</p> |
| 2152 |
|
| 2153 |
<p>This pragma is a thin wrapper around the |
| 2154 |
<a href="c3ref/limit.html">sqlite3_limit</a>(db,<a href="c3ref/c_limit_attached.html#sqlitelimitworkerthreads">SQLITE_LIMIT_WORKER_THREADS</a>,...) interface. |
| 2155 |
</p> |
| 2156 |
<a name="pragma_trusted_schema"></a> |
| 2157 |
<h _id=pragma_trusted_schema style="display:none"> PRAGMA trusted_schema</h><hr> |
| 2158 |
<p><b>PRAGMA trusted_schema; |
| 2159 |
<br>PRAGMA trusted_schema = </b><i>boolean</i><b>;</b></p> |
| 2160 |
<p>The trusted_schema setting is a per-connection boolean that |
| 2161 |
determines whether or not SQL functions and virtual tables that |
| 2162 |
have not been security audited are allowed to be run by views, |
| 2163 |
triggers, or in expressions of the schema such as <a href="lang_createtable.html#ckconst">CHECK constraints</a>, |
| 2164 |
<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 |
| 2165 |
<a href="partialindex.html">partial indexes</a>. This setting can also be controlled using |
| 2166 |
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>,...) |
| 2167 |
C-language interface. |
| 2168 |
<p>In order to maintain backwards compatibility, this setting is |
| 2169 |
ON by default. There are advantages to turning it off, and most |
| 2170 |
applications will be unaffected if it is turned off. For that reason, |
| 2171 |
all applications are encouraged to switch this setting off on every |
| 2172 |
database connection as soon as that connection is opened. |
| 2173 |
<p>The <a href="compile.html#trusted_schema">-DSQLITE_TRUSTED_SCHEMA=0</a> compile-time option will cause |
| 2174 |
this setting to default to OFF. |
| 2175 |
<a name="pragma_user_version"></a> |
| 2176 |
<h _id=pragma_user_version style="display:none"> PRAGMA user_version</h><hr> |
| 2177 |
<p><b>PRAGMA </b><i>schema.</i><b>user_version; |
| 2178 |
<br>PRAGMA </b><i>schema.</i><b>user_version = </b><i>integer </i><b>;</b> |
| 2179 |
|
| 2180 |
|
| 2181 |
<p> The user_version pragma will get or set |
| 2182 |
the value of the user-version integer at offset 60 in the |
| 2183 |
<a href="fileformat2.html#database_header">database header</a>. The user-version is an integer that is |
| 2184 |
available to applications to use however they want. SQLite |
| 2185 |
makes no use of the user-version itself. |
| 2186 |
|
| 2187 |
<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>. |
| 2188 |
<a name="pragma_vdbe_addoptrace"></a> |
| 2189 |
<h _id=pragma_vdbe_addoptrace style="display:none"> PRAGMA vdbe_addoptrace</h><hr> |
| 2190 |
<p><b>PRAGMA vdbe_addoptrace = </b><i>boolean</i><b>;</b></p> |
| 2191 |
|
| 2192 |
<p>If SQLite has been compiled with the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time |
| 2193 |
option, then the vdbe_addoptrace pragma can be used to cause complete |
| 2194 |
VDBE opcodes to be displayed as they are created during code generation. |
| 2195 |
This feature is used for debugging SQLite itself. See the |
| 2196 |
<a href="vdbe.html#trace">VDBE documentation</a> for more |
| 2197 |
information.</p> |
| 2198 |
|
| 2199 |
|
| 2200 |
<p style='background-color: #f0e0ff;'> |
| 2201 |
This pragma is intended for use when debugging SQLite itself. It |
| 2202 |
is only available when the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time option |
| 2203 |
is used.</p> |
| 2204 |
|
| 2205 |
<a name="pragma_vdbe_debug"></a> |
| 2206 |
<h _id=pragma_vdbe_debug style="display:none"> PRAGMA vdbe_debug</h><hr> |
| 2207 |
<p><b>PRAGMA vdbe_debug = </b><i>boolean</i><b>;</b></p> |
| 2208 |
|
| 2209 |
<p>If SQLite has been compiled with the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time |
| 2210 |
option, then the vdbe_debug pragma is a shorthand for three other |
| 2211 |
debug-only pragmas: vdbe_addoptrace, vdbe_listing, and vdbe_trace. |
| 2212 |
This feature is used for debugging SQLite itself. See the |
| 2213 |
<a href="vdbe.html#trace">VDBE documentation</a> for more |
| 2214 |
information.</p> |
| 2215 |
|
| 2216 |
|
| 2217 |
<p style='background-color: #f0e0ff;'> |
| 2218 |
This pragma is intended for use when debugging SQLite itself. It |
| 2219 |
is only available when the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time option |
| 2220 |
is used.</p> |
| 2221 |
|
| 2222 |
<a name="pragma_vdbe_listing"></a> |
| 2223 |
<h _id=pragma_vdbe_listing style="display:none"> PRAGMA vdbe_listing</h><hr> |
| 2224 |
<p><b>PRAGMA vdbe_listing = </b><i>boolean</i><b>;</b></p> |
| 2225 |
|
| 2226 |
<p>If SQLite has been compiled with the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time |
| 2227 |
option, then the vdbe_listing pragma can be used to cause a complete |
| 2228 |
listing of the virtual machine opcodes to appear on standard output |
| 2229 |
as each statement is evaluated. |
| 2230 |
When vdbe_listing is on, the entire content of a program is printed |
| 2231 |
just prior to beginning execution. The statement |
| 2232 |
executes normally after the listing is printed. |
| 2233 |
This feature is used for debugging SQLite itself. See the |
| 2234 |
<a href="vdbe.html#trace">VDBE documentation</a> for more |
| 2235 |
information.</p> |
| 2236 |
|
| 2237 |
|
| 2238 |
<p style='background-color: #f0e0ff;'> |
| 2239 |
This pragma is intended for use when debugging SQLite itself. It |
| 2240 |
is only available when the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time option |
| 2241 |
is used.</p> |
| 2242 |
|
| 2243 |
<a name="pragma_vdbe_trace"></a> |
| 2244 |
<h _id=pragma_vdbe_trace style="display:none"> PRAGMA vdbe_trace</h><hr> |
| 2245 |
<p><b>PRAGMA vdbe_trace = </b><i>boolean</i><b>;</b></p> |
| 2246 |
|
| 2247 |
<p>If SQLite has been compiled with the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time |
| 2248 |
option, then the vdbe_trace pragma can be used to cause virtual machine |
| 2249 |
opcodes to be printed on standard output as they are evaluated. |
| 2250 |
This feature is used for debugging SQLite. See the |
| 2251 |
<a href="vdbe.html#trace">VDBE documentation</a> for more |
| 2252 |
information.</p> |
| 2253 |
|
| 2254 |
|
| 2255 |
<p style='background-color: #f0e0ff;'> |
| 2256 |
This pragma is intended for use when debugging SQLite itself. It |
| 2257 |
is only available when the <a href="compile.html#debug">SQLITE_DEBUG</a> compile-time option |
| 2258 |
is used.</p> |
| 2259 |
|
| 2260 |
<a name="pragma_wal_autocheckpoint"></a> |
| 2261 |
<h _id=pragma_wal_autocheckpoint style="display:none"> PRAGMA wal_autocheckpoint</h><hr> |
| 2262 |
<p><b>PRAGMA wal_autocheckpoint;<br> |
| 2263 |
PRAGMA wal_autocheckpoint=</b><i>N</i><b>;</b></p> |
| 2264 |
|
| 2265 |
<p>This pragma queries or sets the <a href="wal.html">write-ahead log</a> |
| 2266 |
<a href="wal.html#ckpt">auto-checkpoint</a> interval. |
| 2267 |
When the <a href="wal.html">write-ahead log</a> is enabled (via the |
| 2268 |
<a href="pragma.html#pragma_journal_mode">journal_mode pragma</a>) a checkpoint will be run automatically whenever |
| 2269 |
the write-ahead log equals or exceeds <i>N</i> pages in length. |
| 2270 |
Setting the auto-checkpoint size to zero or a negative value |
| 2271 |
turns auto-checkpointing off.</p> |
| 2272 |
|
| 2273 |
<p>This pragma is a wrapper around the |
| 2274 |
<a href="c3ref/wal_autocheckpoint.html">sqlite3_wal_autocheckpoint()</a> C interface. |
| 2275 |
All automatic checkpoints are <a href="c3ref/wal_checkpoint_v2.html">PASSIVE</a>.</p> |
| 2276 |
|
| 2277 |
<p>Autocheckpointing is enabled by default with an interval |
| 2278 |
of 1000 or <a href="compile.html#default_wal_autocheckpoint">SQLITE_DEFAULT_WAL_AUTOCHECKPOINT</a>.</p> |
| 2279 |
|
| 2280 |
<a name="pragma_wal_checkpoint"></a> |
| 2281 |
<h _id=pragma_wal_checkpoint style="display:none"> PRAGMA wal_checkpoint</h><hr> |
| 2282 |
<p><b>PRAGMA </b><i>schema.</i><b>wal_checkpoint;</b><br> |
| 2283 |
<b>PRAGMA </b><i>schema.</i><b>wal_checkpoint(PASSIVE);</b><br> |
| 2284 |
<b>PRAGMA </b><i>schema.</i><b>wal_checkpoint(FULL);</b><br> |
| 2285 |
<b>PRAGMA </b><i>schema.</i><b>wal_checkpoint(RESTART);</b><br> |
| 2286 |
<b>PRAGMA </b><i>schema.</i><b>wal_checkpoint(TRUNCATE);</b><br> |
| 2287 |
<b>PRAGMA </b><i>schema.</i><b>wal_checkpoint(NOOP);</b> |
| 2288 |
</p> |
| 2289 |
|
| 2290 |
<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>), |
| 2291 |
this pragma causes a <a href="wal.html#ckpt">checkpoint</a> operation to run on database |
| 2292 |
<i>database</i>, or on all attached databases if <i>database</i> |
| 2293 |
is omitted. If <a href="wal.html">write-ahead log</a> mode is disabled, this pragma is a |
| 2294 |
harmless no-op.</p> |
| 2295 |
|
| 2296 |
<p>Invoking this |
| 2297 |
pragma without an argument is equivalent to calling the |
| 2298 |
<a href="c3ref/wal_checkpoint.html">sqlite3_wal_checkpoint()</a> C interface.</p> |
| 2299 |
Invoking this pragma with an argument is equivalent to calling the |
| 2300 |
<a href="c3ref/wal_checkpoint_v2.html">sqlite3_wal_checkpoint_v2()</a> C interface with a |
| 2301 |
<a href="c3ref/c_checkpoint_full.html">3rd parameter</a> |
| 2302 |
corresponding to the argument: |
| 2303 |
|
| 2304 |
<dl> |
| 2305 |
<dt>PASSIVE<dd> |
| 2306 |
Checkpoint as many frames as possible without waiting for any database |
| 2307 |
readers or writers to finish. Sync the db file if all frames in the log |
| 2308 |
are checkpointed. This mode is the same as calling the |
| 2309 |
<a href="c3ref/wal_checkpoint.html">sqlite3_wal_checkpoint()</a> C interface. The |
| 2310 |
<a href="c3ref/busy_handler.html">busy-handler callback</a> is never invoked in |
| 2311 |
this mode. |
| 2312 |
|
| 2313 |
<dt>FULL<dd> |
| 2314 |
This mode blocks |
| 2315 |
(invokes the <a href="c3ref/busy_handler.html">busy-handler callback</a>) |
| 2316 |
until there is no |
| 2317 |
database writer and all readers are reading from the most recent database |
| 2318 |
snapshot. It then checkpoints all frames in the log file and syncs the |
| 2319 |
database file. FULL blocks concurrent writers while it is |
| 2320 |
running, but readers can proceed. |
| 2321 |
|
| 2322 |
<dt>RESTART<dd> |
| 2323 |
This mode works the same way as FULL with the addition that after |
| 2324 |
checkpointing the log file it blocks (calls the |
| 2325 |
<a href="c3ref/busy_handler.html">busy-handler callback</a>) |
| 2326 |
until all readers are finished with the log file. This ensures |
| 2327 |
that the next client to write to the database file restarts the log file |
| 2328 |
from the beginning. RESTART blocks concurrent writers while it is |
| 2329 |
running, but allows readers to proceed. |
| 2330 |
|
| 2331 |
<dt>TRUNCATE<dd> |
| 2332 |
This mode works the same way as RESTART with the |
| 2333 |
addition that the WAL file is truncated to zero bytes upon successful |
| 2334 |
completion. |
| 2335 |
|
| 2336 |
<dt>NOOP<dd> |
| 2337 |
This mode does not checkpoint any frames. It is used to obtain the |
| 2338 |
returned values only. |
| 2339 |
</dl> |
| 2340 |
|
| 2341 |
|
| 2342 |
<p>The wal_checkpoint pragma returns a single row with three |
| 2343 |
integer columns. The first column is usually 0 but will be |
| 2344 |
1 if a RESTART or FULL or TRUNCATE checkpoint was blocked from completing, |
| 2345 |
for example because another thread or process was actively |
| 2346 |
using the database. In other words, the first column is 0 if the |
| 2347 |
equivalent call to <a href="c3ref/wal_checkpoint_v2.html">sqlite3_wal_checkpoint_v2()</a> would have returned |
| 2348 |
<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>. |
| 2349 |
The second column is the number of modified pages that have been |
| 2350 |
written to the write-ahead log file. |
| 2351 |
The third column is the number of pages in the write-ahead log file |
| 2352 |
that have been successfully moved back into the database file at |
| 2353 |
the conclusion of the checkpoint. |
| 2354 |
The second and third column are -1 if there is no |
| 2355 |
write-ahead log, for example if this pragma is invoked on a database |
| 2356 |
connection that is not in <a href="wal.html">WAL mode</a>.</p> |
| 2357 |
<a name="pragma_writable_schema"></a> |
| 2358 |
<h _id=pragma_writable_schema style="display:none"> PRAGMA writable_schema</h><hr> |
| 2359 |
<p><b>PRAGMA writable_schema = </b><i>boolean</i><b>;</b><br> |
| 2360 |
<b>PRAGMA writable_schema = RESET</b></p> |
| 2361 |
|
| 2362 |
<p>When this pragma is on, and the <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigdefensive">SQLITE_DBCONFIG_DEFENSIVE</a> flag |
| 2363 |
is off, then the <a href="schematab.html">sqlite_schema</a> table |
| 2364 |
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> |
| 2365 |
statements. If the argument is "RESET" then schema writing is |
| 2366 |
disabled (as with "PRAGMA writable_schema=OFF") and, in addition, the |
| 2367 |
schema is reloaded. <span style='background-color: #ffff60;'><b>Warning:</b> |
| 2368 |
misuse of this pragma can easily result in |
| 2369 |
a <a href="howtocorrupt.html#cfgerr">corrupt database file</a>.</span> |
| 2370 |
|
| 2371 |
<hr> |
| 2372 |
<p align="center"><small><i>This page was last updated on 2026-05-21 16:39:22Z </small></i></p> |
| 2373 |
|