Debian Codegraph @

www/pragma.html

raw
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' &#91;FALSE&#93;</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&sup1;</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&sup1;</s></a></li>
387 <li><a href='pragma.html#pragma_data_store_directory'><s>data_store_directory&sup1;</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&sup1;</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&sup1;</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&sup1;</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&sup2;</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&sup3;</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&sup1;</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&sup3;</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&sup1;</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&sup2;</a></li>
442 <li><a href='pragma.html#pragma_vdbe_debug'>vdbe_debug&sup2;</a></li>
443 <li><a href='pragma.html#pragma_vdbe_listing'>vdbe_listing&sup2;</a></li>
444 <li><a href='pragma.html#pragma_vdbe_trace'>vdbe_trace&sup2;</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&sup3;</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>&nbsp;
1938 <th>&nbsp;Rollback Mode&nbsp;
1939 <th>&nbsp;WAL Mode&nbsp;
1940 </tr>
1941 <tr>
1942 <th>&nbsp;EXTRA&nbsp;
1943 <td align="center">&nbsp;ACID&nbsp;
1944 <td align="center">&nbsp;ACID&nbsp;
1945 </tr>
1946 <tr>
1947 <th>&nbsp;FULL&nbsp;
1948 <td align="center">&nbsp;Maybe not durable&nbsp;
1949 <td align="center">&nbsp;ACID&nbsp;
1950 </tr>
1951 <tr>
1952 <th>&nbsp;NORMAL&nbsp;
1953 <td align="center">&nbsp;Maybe not consistent&nbsp;
1954 <td align="center">&nbsp;Maybe not durable&nbsp;
1955 </tr>
1956 <tr>
1957 <th>&nbsp;OFF&nbsp;
1958 <td align="center">&nbsp;Not consistent&nbsp;
1959 <td align="center">&nbsp;Not consistent&nbsp;
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