Debian Codegraph @

www/vtab.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>The Virtual Table Mechanism Of 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 <div class=fancy>
119 <div class=nosearch>
120 <div class="fancy_title">
121 The Virtual Table Mechanism Of SQLite
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#introduction">1. Introduction</a></div>
126 <div class="fancy-toc2"><a href="#usage">1.1. Usage</a></div>
127 <div class="fancy-toc3"><a href="#temporary_virtual_tables">1.1.1. Temporary virtual tables</a></div>
128 <div class="fancy-toc3"><a href="#eponymous_virtual_tables">1.1.2. Eponymous virtual tables</a></div>
129 <div class="fancy-toc3"><a href="#eponymous_only_virtual_tables">1.1.3. Eponymous-only virtual tables</a></div>
130 <div class="fancy-toc2"><a href="#implementation">1.2. Implementation</a></div>
131 <div class="fancy-toc2"><a href="#virtual_tables_and_shared_cache">1.3. Virtual Tables And Shared Cache</a></div>
132 <div class="fancy-toc2"><a href="#creating_new_virtual_table_implementations">1.4. Creating New Virtual Table Implementations</a></div>
133 <div class="fancy-toc2"><a href="#security_considerations">1.5. Security Considerations</a></div>
134 <div class="fancy-toc1"><a href="#virtual_table_methods">2. Virtual Table Methods</a></div>
135 <div class="fancy-toc2"><a href="#the_xcreate_method">2.1. The xCreate Method</a></div>
136 <div class="fancy-toc3"><a href="#hidden_columns_in_virtual_tables">2.1.1. Hidden columns in virtual tables</a></div>
137 <div class="fancy-toc3"><a href="#table_valued_functions">2.1.2. Table-valued functions</a></div>
138 <div class="fancy-toc3"><a href="#_without_rowid_virtual_tables_">2.1.3. WITHOUT ROWID Virtual Tables </a></div>
139 <div class="fancy-toc2"><a href="#the_xconnect_method">2.2. The xConnect Method</a></div>
140 <div class="fancy-toc2"><a href="#the_xbestindex_method">2.3. The xBestIndex Method</a></div>
141 <div class="fancy-toc3"><a href="#inputs">2.3.1. Inputs</a></div>
142 <div class="fancy-toc4"><a href="#like_glob_regexp_and_match_functions">2.3.1.1. LIKE, GLOB, REGEXP, and MATCH functions</a></div>
143 <div class="fancy-toc4"><a href="#limit_and_offset">2.3.1.2. LIMIT and OFFSET</a></div>
144 <div class="fancy-toc4"><a href="#right_hand_side_values_of_constraints">2.3.1.3. Right-hand side values of constraints</a></div>
145 <div class="fancy-toc3"><a href="#outputs">2.3.2. Outputs</a></div>
146 <div class="fancy-toc4"><a href="#omit_constraint_checking_in_bytecode">2.3.2.1. Omit constraint checking in bytecode</a></div>
147 <div class="fancy-toc4"><a href="#order_by_and_orderbyconsumed">2.3.2.2. ORDER BY and orderByConsumed</a></div>
148 <div class="fancy-toc3"><a href="#return_value">2.3.3. Return Value</a></div>
149 <div class="fancy-toc3"><a href="#enforcing_required_parameters_on_table_valued_functions">2.3.4. Enforcing Required Parameters On Table-Valued Functions</a></div>
150 <div class="fancy-toc2"><a href="#the_xdisconnect_method">2.4. The xDisconnect Method</a></div>
151 <div class="fancy-toc2"><a href="#the_xdestroy_method">2.5. The xDestroy Method</a></div>
152 <div class="fancy-toc2"><a href="#the_xopen_method">2.6. The xOpen Method</a></div>
153 <div class="fancy-toc2"><a href="#the_xclose_method">2.7. The xClose Method</a></div>
154 <div class="fancy-toc2"><a href="#the_xeof_method">2.8. The xEof Method</a></div>
155 <div class="fancy-toc2"><a href="#the_xfilter_method">2.9. The xFilter Method</a></div>
156 <div class="fancy-toc2"><a href="#the_xnext_method">2.10. The xNext Method</a></div>
157 <div class="fancy-toc2"><a href="#the_xcolumn_method">2.11. The xColumn Method</a></div>
158 <div class="fancy-toc2"><a href="#the_xrowid_method">2.12. The xRowid Method</a></div>
159 <div class="fancy-toc2"><a href="#the_xupdate_method">2.13. The xUpdate Method</a></div>
160 <div class="fancy-toc2"><a href="#the_xfindfunction_method">2.14. The xFindFunction Method</a></div>
161 <div class="fancy-toc2"><a href="#the_xbegin_method">2.15. The xBegin Method</a></div>
162 <div class="fancy-toc2"><a href="#the_xsync_method">2.16. The xSync Method</a></div>
163 <div class="fancy-toc2"><a href="#the_xcommit_method">2.17. The xCommit Method</a></div>
164 <div class="fancy-toc2"><a href="#the_xrollback_method">2.18. The xRollback Method</a></div>
165 <div class="fancy-toc2"><a href="#the_xrename_method">2.19. The xRename Method</a></div>
166 <div class="fancy-toc2"><a href="#the_xsavepoint_xrelease_and_xrollbackto_methods">2.20. The xSavepoint, xRelease, and xRollbackTo Methods</a></div>
167 <div class="fancy-toc2"><a href="#the_xshadowname_method">2.21. The xShadowName Method</a></div>
168 <div class="fancy-toc2"><a href="#the_xintegrity_method">2.22. The xIntegrity Method</a></div>
169 </div>
170 </details>
171 </div>
172
173
174
175
176
177
178 <h1 id="introduction"><span>1. </span>Introduction</h1>
179
180 <p>A virtual table is an object that is registered with an open SQLite
181 <a href="c3ref/sqlite3.html">database connection</a>. From the perspective of an SQL statement,
182 the virtual table object looks like any other table or view.
183 But behind the scenes, queries and updates on a virtual table
184 invoke callback methods of the virtual table object instead of
185 reading and writing on the database file.
186
187 </p><p>The virtual table mechanism allows an application to publish
188 interfaces that are accessible from SQL statements as if they were
189 tables. SQL statements can do almost anything to a
190 virtual table that they can do to a real table, with the following
191 exceptions:
192
193 </p><p>
194 </p><ul>
195 <li> One cannot create a trigger on a virtual table.
196 </li><li> One cannot create additional indices on a virtual table.
197 (Virtual tables can have indices but that must be built into
198 the virtual table implementation. Indices cannot be added
199 separately using <a href="lang_createindex.html">CREATE INDEX</a> statements.)
200 </li><li> One cannot run <a href="lang_altertable.html">ALTER TABLE ... ADD COLUMN</a>
201 commands against a virtual table.
202 </li></ul>
203
204 <p>Individual virtual table implementations might impose additional
205 constraints. For example, some virtual implementations might provide
206 read-only tables. Or some virtual table implementations might allow
207 <a href="lang_insert.html">INSERT</a> or <a href="lang_delete.html">DELETE</a> but not <a href="lang_update.html">UPDATE</a>. Or some virtual table implementations
208 might limit the kinds of UPDATEs that can be made.
209
210 </p><p>A virtual table might represent an in-memory data structure.
211 Or it might represent a view of data on disk that is not in the
212 SQLite format. Or the application might compute the content of the
213 virtual table on demand.
214
215 </p><p>Here are some existing and postulated uses for virtual tables:
216
217 </p><ul>
218 <li> A <a href="fts3.html">full-text search</a> interface
219 </li><li> Spatial indices using <a href="rtree.html">R-Trees</a>
220 </li><li> Introspect the disk content of an SQLite database file
221 (the <a href="dbstat.html">dbstat virtual table</a>)
222 </li><li> Read and/or write the content of a comma-separated value (CSV)
223 file
224 </li><li> Access the filesystem of the host computer as if it were a database table
225 </li><li> Enabling SQL manipulation of data in statistics packages like R
226 </li></ul>
227
228 <p>See the <a href="vtablist.html">list of virtual tables</a> page for a longer list of actual
229 virtual table implementations.
230
231
232 </p><h2 id="usage"><span>1.1. </span>Usage</h2>
233
234 <p>A virtual table is created using a <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement.
235
236 <p><b><a href="syntax/create-virtual-table-stmt.html">create-virtual-table-stmt:</a></b>
237 <button id='xd23c61b6' onclick='hideorshow("xd23c61b6","x63ad5754")'>hide</button></p>
238 <div id='x63ad5754' class='imgcontainer'>
239 <div style="max-width:624px"><svg xmlns='http://www.w3.org/2000/svg' style='font-size:initial;' class="pikchr" viewBox="0 0 624.096 259.848" data-pikchr-date="20250319161943">
240 <circle cx="5.76" cy="17.28" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
241 <polygon points="32.4,17.28 20.88,21.6 20.88,12.96" style="fill:rgb(0,0,0)"/>
242 <path d="M9.36,17.28L26.64,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
243 <path d="M47.52,32.4L100.915,32.4A15.12 15.12 0 0 0 116.035 17.28A15.12 15.12 0 0 0 100.915 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);" />
244 <text x="74.2176" y="17.28" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">CREATE</text>
245 <polygon points="139.075,17.28 127.555,21.6 127.555,12.96" style="fill:rgb(0,0,0)"/>
246 <path d="M116.035,17.28L133.315,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
247 <path d="M154.195,32.4L213.696,32.4A15.12 15.12 0 0 0 228.816 17.28A15.12 15.12 0 0 0 213.696 2.16L154.195,2.16A15.12 15.12 0 0 0 139.075 17.28A15.12 15.12 0 0 0 154.195 32.4Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
248 <text x="183.946" y="17.28" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">VIRTUAL</text>
249 <polygon points="251.856,17.28 240.336,21.6 240.336,12.96" style="fill:rgb(0,0,0)"/>
250 <path d="M228.816,17.28L246.096,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
251 <path d="M266.976,32.4L306.317,32.4A15.12 15.12 0 0 0 321.437 17.28A15.12 15.12 0 0 0 306.317 2.16L266.976,2.16A15.12 15.12 0 0 0 251.856 17.28A15.12 15.12 0 0 0 266.976 32.4Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
252 <text x="286.646" y="17.28" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">TABLE</text>
253 <polygon points="357.437,17.28 345.917,21.6 345.917,12.96" style="fill:rgb(0,0,0)"/>
254 <path d="M321.437,17.28L351.677,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
255 <path d="M372.557,32.4A15.12 15.12 0 0 0 387.677 17.28A15.12 15.12 0 0 0 372.557 2.16A15.12 15.12 0 0 0 357.437 17.28A15.12 15.12 0 0 0 372.557 32.4Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
256 <text x="372.557" y="17.28" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">IF</text>
257 <polygon points="410.717,17.28 399.197,21.6 399.197,12.96" style="fill:rgb(0,0,0)"/>
258 <path d="M387.677,17.28L404.957,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
259 <path d="M425.837,32.4L445.939,32.4A15.12 15.12 0 0 0 461.059 17.28A15.12 15.12 0 0 0 445.939 2.16L425.837,2.16A15.12 15.12 0 0 0 410.717 17.28A15.12 15.12 0 0 0 425.837 32.4Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
260 <text x="435.888" y="17.28" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">NOT</text>
261 <polygon points="484.099,17.28 472.579,21.6 472.579,12.96" style="fill:rgb(0,0,0)"/>
262 <path d="M461.059,17.28L478.339,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
263 <path d="M499.219,32.4L543.859,32.4A15.12 15.12 0 0 0 558.979 17.28A15.12 15.12 0 0 0 543.859 2.16L499.219,2.16A15.12 15.12 0 0 0 484.099 17.28A15.12 15.12 0 0 0 499.219 32.4Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
264 <text x="521.539" y="17.28" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">EXISTS</text>
265 <path d="M47.52,108L143.885,108A15.12 15.12 0 0 0 159.005 92.88A15.12 15.12 0 0 0 143.885 77.76L47.52,77.76A15.12 15.12 0 0 0 32.4 92.88A15.12 15.12 0 0 0 47.52 108Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
266 <text x="95.7024" y="92.88" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">schema-name</text>
267 <polygon points="182.045,92.88 170.525,97.2 170.525,88.56" style="fill:rgb(0,0,0)"/>
268 <path d="M159.005,92.88L176.285,92.88" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
269 <path d="M197.165,108A15.12 15.12 0 0 0 212.285 92.88A15.12 15.12 0 0 0 197.165 77.76A15.12 15.12 0 0 0 182.045 92.88A15.12 15.12 0 0 0 197.165 108Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
270 <text x="197.165" y="92.88" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">.</text>
271 <polygon points="248.285,92.88 236.765,97.2 236.765,88.56" style="fill:rgb(0,0,0)"/>
272 <path d="M212.285,92.88L242.525,92.88" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
273 <path d="M263.405,108L338.573,108A15.12 15.12 0 0 0 353.693 92.88A15.12 15.12 0 0 0 338.573 77.76L263.405,77.76A15.12 15.12 0 0 0 248.285 92.88A15.12 15.12 0 0 0 263.405 108Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
274 <text x="300.989" y="92.88" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">table-name</text>
275 <path d="M47.52,219.888L86.5152,219.888A15.12 15.12 0 0 0 101.635 204.768A15.12 15.12 0 0 0 86.5152 189.648L47.52,189.648A15.12 15.12 0 0 0 32.4 204.768A15.12 15.12 0 0 0 47.52 219.888Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
276 <text x="67.0176" y="204.768" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">USING</text>
277 <polygon points="124.675,204.768 113.155,209.088 113.155,200.448" style="fill:rgb(0,0,0)"/>
278 <path d="M101.635,204.768L118.915,204.768" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
279 <path d="M139.795,219.888L234.432,219.888A15.12 15.12 0 0 0 249.552 204.768A15.12 15.12 0 0 0 234.432 189.648L139.795,189.648A15.12 15.12 0 0 0 124.675 204.768A15.12 15.12 0 0 0 139.795 219.888Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
280 <text x="187.114" y="204.768" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">module-name</text>
281 <polygon points="285.552,204.768 274.032,209.088 274.032,200.448" style="fill:rgb(0,0,0)"/>
282 <path d="M249.552,204.768L279.792,204.768" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
283 <path d="M300.672,219.888A15.12 15.12 0 0 0 315.792 204.768A15.12 15.12 0 0 0 300.672 189.648A15.12 15.12 0 0 0 285.552 204.768A15.12 15.12 0 0 0 300.672 219.888Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
284 <text x="300.672" y="204.768" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">(</text>
285 <polygon points="351.792,204.768 340.272,209.088 340.272,200.448" style="fill:rgb(0,0,0)"/>
286 <path d="M315.792,204.768L346.032,204.768" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
287 <path d="M366.912,219.888L497.376,219.888A15.12 15.12 0 0 0 512.496 204.768A15.12 15.12 0 0 0 497.376 189.648L366.912,189.648A15.12 15.12 0 0 0 351.792 204.768A15.12 15.12 0 0 0 366.912 219.888Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
288 <text x="432.144" y="204.768" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">module-argument</text>
289 <polygon points="548.496,204.768 536.976,209.088 536.976,200.448" style="fill:rgb(0,0,0)"/>
290 <path d="M512.496,204.768L542.736,204.768" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
291 <path d="M563.616,219.888A15.12 15.12 0 0 0 578.736 204.768A15.12 15.12 0 0 0 563.616 189.648A15.12 15.12 0 0 0 548.496 204.768A15.12 15.12 0 0 0 563.616 219.888Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
292 <text x="563.616" y="204.768" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">)</text>
293 <polygon points="614.736,204.768 603.216,209.088 603.216,200.448" style="fill:rgb(0,0,0)"/>
294 <path d="M578.736,204.768L608.976,204.768" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
295 <circle cx="618.336" cy="204.768" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
296 <path d="M432.144,257.688A15.12 15.12 0 0 0 447.264 242.568A15.12 15.12 0 0 0 432.144 227.448A15.12 15.12 0 0 0 417.024 242.568A15.12 15.12 0 0 0 432.144 257.688Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
297 <text x="432.144" y="242.568" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">,</text>
298 <polygon points="447.264,242.568 458.784,238.248 458.784,246.888" style="fill:rgb(0,0,0)"/>
299 <path d="M512.496,204.768 L 519.996,204.768 Q 527.496,204.768 527.496,219.768 L 527.496,227.568 Q 527.496,242.568 512.496,242.568 L 468.024,242.568 L 453.024,242.568" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
300 <path d="M417.024,242.568 L 343.752,242.568 Q 328.752,242.568 328.752,227.568 L 328.752,219.768 Q 328.752,204.768 336.252,204.768 L 343.752,204.768" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
301 <polygon points="432.144,180.576 420.624,184.896 420.624,176.256" style="fill:rgb(0,0,0)"/>
302 <path d="M249.552,204.768 L 257.052,204.768 Q 264.552,204.768 264.552,192.672 Q 264.552,180.576 279.552,180.576 L 411.384,180.576 L 426.384,180.576" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
303 <path d="M432.144,180.576 L 576.696,180.576 Q 591.696,180.576 591.696,192.672 Q 591.696,204.768 599.196,204.768 L 606.696,204.768" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
304 <polygon points="187.114,153.36 198.634,149.04 198.634,157.68" style="fill:rgb(0,0,0)"/>
305 <path d="M353.693,92.88 L 361.193,92.88 Q 368.693,92.88 368.693,107.88 L 368.693,138.36 Q 368.693,153.36 353.693,153.36 L 207.874,153.36 L 192.874,153.36" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
306 <polygon points="32.4,204.768 20.88,209.088 20.88,200.448" style="fill:rgb(0,0,0)"/>
307 <path d="M187.114,153.36 L 20.76,153.36 Q 5.76,153.36 5.76,168.36 L 5.76,189.768 Q 5.76,204.768 16.2,204.768 L 26.64,204.768" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
308 <polygon points="429.979,62.64 441.499,58.32 441.499,66.96" style="fill:rgb(0,0,0)"/>
309 <path d="M558.979,17.28 L 566.479,17.28 Q 573.979,17.28 573.979,32.28 L 573.979,47.64 Q 573.979,62.64 558.979,62.64 L 450.739,62.64 L 435.739,62.64" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
310 <polygon points="32.4,92.88 20.88,97.2 20.88,88.56" style="fill:rgb(0,0,0)"/>
311 <path d="M429.979,62.64 L 20.76,62.64 Q 5.76,62.64 5.76,77.64 L 5.76,77.88 Q 5.76,92.88 16.2,92.88 L 26.64,92.88" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
312 <polygon points="336.437,47.28 332.117,35.76 340.757,35.76" style="fill:rgb(0,0,0)"/>
313 <path d="M321.437,17.28 L 328.937,17.28 Q 336.437,17.28 336.437,29.4 L 336.437,41.52" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
314 <path d="M336.437,47.28 L 336.437,54.96 Q 336.437,62.64 328.937,62.64 L 321.437,62.64" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
315 <polygon points="95.7024,123.12 84.1824,127.44 84.1824,118.8" style="fill:rgb(0,0,0)"/>
316 <path d="M5.76,77.76 L 5.76,108.12 Q 5.76,123.12 20.76,123.12 L 74.9424,123.12 L 89.9424,123.12" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
317 <path d="M95.7024,123.12 L 210.245,123.12 Q 225.245,123.12 225.245,108.12 L 225.245,107.88 Q 225.245,92.88 231.005,92.88 L 236.765,92.88" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
318 </svg>
319 </div>
320 </div>
321
322
323 </p><p>The CREATE VIRTUAL TABLE statement creates a new table
324 called <span class='yyterm'>table-name</span> derived from the class
325 <span class='yyterm'>module-name</span>. The <span class='yyterm'>module-name</span>
326 is the name that is registered for the virtual table by
327 the <a href="c3ref/create_module.html">sqlite3_create_module()</a> interface.
328
329 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE tablename USING modulename;
330 </pre></div>
331
332 <p>One can also provide comma-separated arguments to the module following
333 the module name:
334
335 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE tablename USING modulename(arg1, arg2, ...);
336 </pre></div>
337
338 <p>The format of the arguments to the module is very general. Each
339 <span class='yyterm'>module-argument</span>
340 may contain keywords, string literals, identifiers, numbers, and
341 punctuation. Each <span class='yyterm'>module-argument</span> is passed as
342 written (as text) into the
343 <a href="vtab.html#xcreate">constructor method</a> of the virtual table implementation
344 when the virtual
345 table is created and that constructor is responsible for parsing and
346 interpreting the arguments. The argument syntax is sufficiently general
347 that a virtual table implementation can, if it wants to, interpret its
348 arguments as <a href="lang_createtable.html#tablecoldef">column definitions</a> in an ordinary <a href="lang_createtable.html">CREATE TABLE</a> statement.
349 The implementation could also impose some other interpretation on the
350 arguments.
351
352 </p><p>Once a virtual table has been created, it can be used like any other
353 table with the exceptions noted above and imposed by specific virtual
354 table implementations. A virtual table is destroyed using the ordinary
355 <a href="lang_droptable.html">DROP TABLE</a> syntax.
356
357 </p><h3 id="temporary_virtual_tables"><span>1.1.1. </span>Temporary virtual tables</h3>
358
359 <p>There is no "CREATE TEMP VIRTUAL TABLE" statement. To create a
360 temporary virtual table, add the "temp" schema
361 before the virtual table name.
362
363 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE <b>temp.</b>tablename USING module(arg1, ...);
364 </pre></div>
365
366 <a name="epovtab"></a>
367
368 <h3 id="eponymous_virtual_tables"><span>1.1.2. </span>Eponymous virtual tables</h3>
369
370 <p>Some virtual tables exist automatically in the "main" schema of
371 every database connection in which their
372 module is registered, even without a <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement.
373 Such virtual tables are called "eponymous virtual tables".
374 To use an eponymous virtual table, simply use the
375 module name as if it were a table.
376 Eponymous virtual tables exist in the "main" schema only, so they will
377 not work if prefixed with a different schema name.
378
379 </p><p>An example of an eponymous virtual table is the <a href="dbstat.html">dbstat virtual table</a>.
380 To use the dbstat virtual table as an eponymous virtual table,
381 simply query against the "dbstat"
382 module name, as if it were an ordinary table. (Note that SQLite
383 must be compiled with the <a href="compile.html#enable_dbstat_vtab">SQLITE_ENABLE_DBSTAT_VTAB</a> option to include
384 the dbstat virtual table in the build.)
385
386 </p><div class="codeblock"><pre>SELECT * FROM dbstat;
387 </pre></div>
388
389 <p>A virtual table is eponymous if its <a href="vtab.html#xcreate">xCreate</a> method is the exact same
390 function as the <a href="vtab.html#xconnect">xConnect</a> method, or if the <a href="vtab.html#xcreate">xCreate</a> method is NULL.
391 The <a href="vtab.html#xcreate">xCreate</a> method is called when a virtual table is first created
392 using the <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement. The <a href="vtab.html#xconnect">xConnect</a> method
393 is invoked whenever
394 a database connection attaches to or reparses a schema. When these two methods
395 are the same, that indicates that the virtual table has no persistent
396 state that needs to be created and destroyed.
397
398 <a name="epoonlyvtab"></a>
399
400 </p><h3 id="eponymous_only_virtual_tables"><span>1.1.3. </span>Eponymous-only virtual tables</h3>
401 <p>If the <a href="vtab.html#xcreate">xCreate</a> method is NULL, then
402 <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statements are prohibited for that virtual table,
403 and the virtual table is an "eponymous-only virtual table".
404 Eponymous-only virtual tables are useful as
405 <a href="vtab.html#tabfunc2">table-valued functions</a>.
406
407 </p><p>
408 Note that prior to <a href="releaselog/3_9_0.html">version 3.9.0</a> (2015-10-14),
409 SQLite did not check the xCreate method
410 for NULL before invoking it. So if an eponymous-only virtual table is
411 registered with SQLite <a href="releaselog/3_8_11_1.html">version 3.8.11.1</a> (2015-07-29)
412 or earlier and a <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a>
413 command is attempted against that virtual table module, a jump to a NULL
414 pointer will occur, resulting in a crash.
415
416 </p><h2 id="implementation"><span>1.2. </span>Implementation</h2>
417
418 <p>Several new C-level objects are used by the virtual table implementation:
419
420 </p><div class="codeblock"><pre>typedef struct sqlite3_vtab sqlite3_vtab;
421 typedef struct sqlite3_index_info sqlite3_index_info;
422 typedef struct sqlite3_vtab_cursor sqlite3_vtab_cursor;
423 typedef struct sqlite3_module sqlite3_module;
424 </pre></div>
425
426 <p>The <a href="c3ref/module.html">sqlite3_module</a> structure defines a module object used to implement
427 a virtual table. Think of a module as a class from which one can
428 construct multiple virtual tables having similar properties. For example,
429 one might have a module that provides read-only access to
430 comma-separated-value (CSV) files on disk. That one module can then be
431 used to create several virtual tables where each virtual table refers
432 to a different CSV file.
433
434 </p><p>The module structure contains methods that are invoked by SQLite to
435 perform various actions on the virtual table such as creating new
436 instances of a virtual table or destroying old ones, reading and
437 writing data, searching for and deleting, updating, or inserting rows.
438 The module structure is explained in more detail below.
439
440 </p><p>Each virtual table instance is represented by an <a href="c3ref/vtab.html">sqlite3_vtab</a> structure.
441 The sqlite3_vtab structure looks like this:
442
443 </p><div class="codeblock"><pre>struct sqlite3_vtab {
444 const sqlite3_module *pModule;
445 int nRef;
446 char *zErrMsg;
447 };
448 </pre></div>
449
450 <p>Virtual table implementations will normally subclass this structure
451 to add additional private and implementation-specific fields.
452 The nRef field is used internally by the SQLite core and should not
453 be altered by the virtual table implementation. The virtual table
454 implementation may pass error message text to the core by putting
455 an error message string in zErrMsg.
456 Space to hold this error message string must be obtained from an
457 SQLite memory allocation function such as <a href="c3ref/mprintf.html">sqlite3_mprintf()</a> or
458 <a href="c3ref/free.html">sqlite3_malloc()</a>.
459 Prior to assigning a new value to zErrMsg, the virtual table
460 implementation must free any preexisting content of zErrMsg using
461 <a href="c3ref/free.html">sqlite3_free()</a>. Failure to do this will result in a memory leak.
462 The SQLite core will free and zero the content of zErrMsg when it
463 delivers the error message text to the client application or when
464 it destroys the virtual table. The virtual table implementation only
465 needs to worry about freeing the zErrMsg content when it overwrites
466 the content with a new, different error message.
467
468 </p><p>The <a href="c3ref/vtab_cursor.html">sqlite3_vtab_cursor</a> structure represents a pointer to a specific
469 row of a virtual table. This is what an sqlite3_vtab_cursor looks like:
470
471 </p><div class="codeblock"><pre>struct sqlite3_vtab_cursor {
472 sqlite3_vtab *pVtab;
473 };
474 </pre></div>
475
476 <p>Once again, practical implementations will likely subclass this
477 structure to add additional private fields.
478
479 </p><p>The <a href="c3ref/index_info.html">sqlite3_index_info</a> structure is used to pass information into
480 and out of the xBestIndex method of the module that implements a
481 virtual table.
482
483 </p><p>Before a <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement can be run, the module
484 specified in that statement must be registered with the database
485 connection. This is accomplished using either of the <a href="c3ref/create_module.html">sqlite3_create_module()</a>
486 or <a href="c3ref/create_module.html">sqlite3_create_module_v2()</a> interfaces:
487
488 </p><div class="codeblock"><pre>int sqlite3_create_module(
489 sqlite3 *db, /* SQLite connection to register module with */
490 const char *zName, /* Name of the module */
491 const sqlite3_module *, /* Methods for the module */
492 void * /* Client data for xCreate/xConnect */
493 );
494 int sqlite3_create_module_v2(
495 sqlite3 *db, /* SQLite connection to register module with */
496 const char *zName, /* Name of the module */
497 const sqlite3_module *, /* Methods for the module */
498 void *, /* Client data for xCreate/xConnect */
499 void(*xDestroy)(void*) /* Client data destructor function */
500 );
501 </pre></div>
502
503 <p>The <a href="c3ref/create_module.html">sqlite3_create_module()</a> and <a href="c3ref/create_module.html">sqlite3_create_module_v2()</a>
504 routines associate a module name with an <a href="c3ref/module.html">sqlite3_module</a>
505 structure and a separate client data pointer that is specific
506 to each module. The only difference between the two create_module methods
507 is that the _v2 method includes an extra parameter that specifies a
508 destructor for client data pointer. The module structure is what defines
509 the behavior of a virtual table. The module structure looks like this:
510
511 </p><div class="codeblock"><pre>
512 struct sqlite3_module {
513 int iVersion;
514 int (*xCreate)(sqlite3*, void *pAux,
515 int argc, char *const*argv,
516 sqlite3_vtab **ppVTab,
517 char **pzErr);
518 int (*xConnect)(sqlite3*, void *pAux,
519 int argc, char *const*argv,
520 sqlite3_vtab **ppVTab,
521 char **pzErr);
522 int (*xBestIndex)(sqlite3_vtab *pVTab, sqlite3_index_info*);
523 int (*xDisconnect)(sqlite3_vtab *pVTab);
524 int (*xDestroy)(sqlite3_vtab *pVTab);
525 int (*xOpen)(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor);
526 int (*xClose)(sqlite3_vtab_cursor*);
527 int (*xFilter)(sqlite3_vtab_cursor*, int idxNum, const char *idxStr,
528 int argc, sqlite3_value **argv);
529 int (*xNext)(sqlite3_vtab_cursor*);
530 int (*xEof)(sqlite3_vtab_cursor*);
531 int (*xColumn)(sqlite3_vtab_cursor*, sqlite3_context*, int);
532 int (*xRowid)(sqlite3_vtab_cursor*, sqlite_int64 *pRowid);
533 int (*xUpdate)(sqlite3_vtab *, int, sqlite3_value **, sqlite_int64 *);
534 int (*xBegin)(sqlite3_vtab *pVTab);
535 int (*xSync)(sqlite3_vtab *pVTab);
536 int (*xCommit)(sqlite3_vtab *pVTab);
537 int (*xRollback)(sqlite3_vtab *pVTab);
538 int (*xFindFunction)(sqlite3_vtab *pVtab, int nArg, const char *zName,
539 void (**pxFunc)(sqlite3_context*,int,sqlite3_value**),
540 void **ppArg);
541 int (*xRename)(sqlite3_vtab *pVtab, const char *zNew);
542 /* The methods above are in version 1 of the sqlite_module object. Those
543 ** below are for version 2 and greater. */
544 int (*xSavepoint)(sqlite3_vtab *pVTab, int);
545 int (*xRelease)(sqlite3_vtab *pVTab, int);
546 int (*xRollbackTo)(sqlite3_vtab *pVTab, int);
547 /* The methods above are in versions 1 and 2 of the sqlite_module object.
548 ** Those below are for version 3 and greater. */
549 int (*xShadowName)(const char*);
550 /* The methods above are in versions 1 through 3 of the sqlite_module object.
551 ** Those below are for version 4 and greater. */
552 int (*xIntegrity)(sqlite3_vtab *pVTab, const char *zSchema,
553 const char *zTabName, int mFlags, char **pzErr);
554 };
555 </pre></div>
556
557 <p>The module structure defines all of the methods for each virtual
558 table object. The module structure also contains the iVersion field which
559 defines the particular edition of the module table structure. Currently,
560 iVersion is always 4 or less, but in future releases of SQLite the module
561 structure definition might be extended with additional methods and in
562 that case the maximum iVersion value will be increased.
563
564 </p><p>The rest of the module structure consists of methods used to implement
565 various features of the virtual table. Details on what each of these
566 methods do are provided in the sequel.
567
568 </p><h2 id="virtual_tables_and_shared_cache"><span>1.3. </span>Virtual Tables And Shared Cache</h2>
569
570 <p>Prior to SQLite <a href="releaselog/3_6_17.html">version 3.6.17</a> (2009-08-10),
571 the virtual table mechanism assumes
572 that each <a href="c3ref/sqlite3.html">database connection</a> kept
573 its own copy of the database schema. Hence, the virtual table mechanism
574 could not be used in a database that has <a href="sharedcache.html">shared cache mode</a> enabled.
575 The <a href="c3ref/create_module.html">sqlite3_create_module()</a> interface would return an error if
576 <a href="sharedcache.html">shared cache mode</a> is enabled. That restriction was relaxed
577 beginning with SQLite <a href="releaselog/3_6_17.html">version 3.6.17</a>.
578
579 <a name="customvtab"></a>
580
581 </p><h2 id="creating_new_virtual_table_implementations"><span>1.4. </span>Creating New Virtual Table Implementations</h2>
582
583 <p>Follow these steps to create your own virtual table:
584
585 </p><p>
586 </p><ol>
587 <li> Write all necessary methods.
588 </li><li> Create an instance of the <a href="c3ref/module.html">sqlite3_module</a> structure containing pointers
589 to all the methods from step 1.
590 </li><li> Register your <a href="c3ref/module.html">sqlite3_module</a> structure using one of the
591 <a href="c3ref/create_module.html">sqlite3_create_module()</a> or <a href="c3ref/create_module.html">sqlite3_create_module_v2()</a> interfaces.
592 </li><li> Run a <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> command that specifies the new module in
593 the USING clause.
594 </li></ol>
595
596 <p>The only really hard part is step 1. You might want to start with an
597 existing virtual table implementation and modify it to suit your needs.
598 The <a href="https://sqlite.org/src/dir?ci=trunk&type=tree">SQLite source tree</a>
599 contains many virtual table implementations that are suitable for copying,
600 including:
601
602 </p><p>
603 </p><ul>
604 <li> <b><a href="https://sqlite.org/src/file/ext/misc/templatevtab.c">templatevtab.c</a></b>
605 &rarr; A virtual table created specifically to serve as a template for
606 other custom virtual tables.
607 </li><li> <b><a href="https://sqlite.org/src/file/ext/misc/series.c">series.c</a></b>
608 &rarr; Implementation of the generate_series() table-valued function.
609 </li><li> <b><a href="https://sqlite.org/src/file/src/json.c">json.c</a></b> &rarr;
610 Contains the sources for the <a href="json1.html#jeach">json_each()</a> and <a href="json1.html#jtree">json_tree()</a> table-valued
611 functions.
612 </li><li> <b><a href="https://sqlite.org/src/file/ext/misc/csv.c">csv.c</a></b> &rarr;
613 A virtual table that reads CSV files.
614 </li></ul>
615
616
617 <p>There are <a href="vtablist.html">many other virtual table implementations</a>
618 in the SQLite source tree that can be used as examples. Locate
619 these other virtual table implementations by searching
620 for "sqlite3_create_module".
621
622 </p><p>You might also want to implement your new virtual table as a
623 <a href="loadext.html">loadable extension</a>.
624
625 </p><h2 id="security_considerations"><span>1.5. </span>Security Considerations</h2>
626
627 <p>Custom virtual tables can become security vulnerabilities if not
628 carefully implemented. To help prevent problems, SQLite provides the
629 following additional security features for virtual tables:
630
631 </p><ol>
632 <li><p>
633 The <a href="vtab.html#xconnect">xConnect</a> and/or <a href="vtab.html#xcreate">xCreate</a> method can invoke <a href="c3ref/vtab_config.html">sqlite3_vtab_config()</a>
634 with the <a href="c3ref/c_vtab_constraint_support.html#sqlitevtabdirectonly">SQLITE_VTAB_DIRECTONLY</a> option to prevent the virtual table from
635 being used from within triggers or views. This is recommended if the capability
636 of using the virtual table from within triggers or views is not needed.
637
638 </p></li><li><p>
639 Avoid using <a href="c3ref/vtab_config.html">sqlite3_vtab_config()</a> with the <a href="c3ref/c_vtab_constraint_support.html#sqlitevtabinnocuous">SQLITE_VTAB_INNOCUOUS</a> option
640 unless absolutely necessary. Only set SQLITE_VTAB_INNOCUOUS if you are
641 certain that your virtual table has no side effects and its purpose cannot
642 be achieved without that setting.
643
644 </p></li><li><p>
645 When the virtual table itself runs SQL statements and those statements depend
646 on arguments to the CREATE VIRTUAL TABLE statement that created the virtual
647 table, use the <a href="c3ref/prepare.html">sqlite3_prepare_v3()</a> interface with the
648 <a href="c3ref/c_prepare_dont_log.html#sqlitepreparefromddl">SQLITE_PREPARE_FROM_DDL</a> option to prevent a bypass of the
649 <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigtrustedschema">SQLITE_DBCONFIG_TRUSTED_SCHEMA</a> setting. If you are unsure whether or not
650 an SQL statement depends on the CREATE VIRTUAL TABLE statement, it is usually
651 harmless (depending on the semantics of the virtual table) to include
652 SQLITE_PREPARE_FROM_DDL even if it is not strictly needed, and so the default
653 action should probably be to use SQLITE_PREPARE_FROM_DDL unless you have a
654 specific reason not to.
655 </p></li></ol>
656
657 <p>See also <a href="./appfunc.html#sec">Security Implications For Custom
658 SQL Functions</a> and
659 <a href="./security.html">Defense Against The Dark Arts</a>.
660
661 </p><h1 id="virtual_table_methods"><span>2. </span>Virtual Table Methods</h1>
662
663 <a name="xcreate"></a>
664
665 <h2 id="the_xcreate_method"><span>2.1. </span>The xCreate Method</h2>
666
667 <div class="codeblock"><pre>int (*xCreate)(sqlite3 *db, void *pAux,
668 int argc, char *const*argv,
669 sqlite3_vtab **ppVTab,
670 char **pzErr);
671 </pre></div>
672
673 <p>The xCreate method is called to create a new instance of a virtual table
674 in response to a <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement.
675 If the xCreate method is the same pointer as the <a href="vtab.html#xconnect">xConnect</a> method, then the
676 virtual table is an <a href="vtab.html#epovtab">eponymous virtual table</a>.
677 If the xCreate method is omitted (if it is a NULL pointer) then the virtual
678 table is an <a href="vtab.html#epoonlyvtab">eponymous-only virtual table</a>.
679
680
681 </p><p>The db parameter is a pointer to the SQLite <a href="c3ref/sqlite3.html">database connection</a> that
682 is executing the <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement.
683 The pAux argument is the copy of the client data pointer that was the
684 fourth argument to the <a href="c3ref/create_module.html">sqlite3_create_module()</a> or
685 <a href="c3ref/create_module.html">sqlite3_create_module_v2()</a> call that registered the
686 <a href="c3ref/module.html">virtual table module</a>.
687 The argv parameter is an array of argc pointers to null terminated strings.
688 The first string, argv[0], is the name of the module being invoked. The
689 module name is the name provided as the second argument to
690 <a href="c3ref/create_module.html">sqlite3_create_module()</a> and as the argument to the USING clause of the
691 <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement that is running.
692 The second, argv[1], is the name of the database in which the new virtual
693 table is being created. The database name is "main" for the primary database, or
694 "temp" for TEMP database, or the name given at the end of the <a href="lang_attach.html">ATTACH</a>
695 statement for attached databases. The third element of the array, argv[2],
696 is the name of the new virtual table, as specified following the TABLE
697 keyword in the <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement.
698 If present, the fourth and subsequent strings in the argv[] array report
699 the arguments to the module name in the <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement.
700
701 </p><p>The job of this method is to construct the new virtual table object
702 (an <a href="c3ref/vtab.html">sqlite3_vtab</a> object) and return a pointer to it in *ppVTab.
703
704 </p><p>As part of the task of creating a new <a href="c3ref/vtab.html">sqlite3_vtab</a> structure, this
705 method <u>must</u> invoke <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a> to tell the SQLite
706 core about the columns and datatypes in the virtual table.
707 The <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a> API has the following prototype:
708
709 </p><div class="codeblock"><pre>int sqlite3_declare_vtab(sqlite3 *db, const char *zCreateTable)
710 </pre></div>
711
712 <p>The first argument to <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a> must be the same
713 <a href="c3ref/sqlite3.html">database connection</a> pointer as the first parameter to this method.
714 The second argument to <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a> must a zero-terminated
715 UTF-8 string that contains a well-formed <a href="lang_createtable.html">CREATE TABLE</a> statement that
716 defines the columns in the virtual table and their data types.
717 The name of the table in this CREATE TABLE statement is ignored,
718 as are all constraints. Only the column names and datatypes matter.
719 The CREATE TABLE statement string need not be
720 held in persistent memory. The string can be
721 deallocated and/or reused as soon as the <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a>
722 routine returns.
723
724 </p><p>The xConnect method can also optionally request special features
725 for the virtual table by making one or more calls to
726 the <a href="c3ref/vtab_config.html">sqlite3_vtab_config()</a> interface:
727
728 </p><div class="codeblock"><pre>int sqlite3_vtab_config(sqlite3 *db, int op, ...);
729 </pre></div>
730
731 <p>Calls to sqlite3_vtab_config() are optional. But for maximum
732 security, it is recommended that virtual table implementations
733 invoke "<a href="c3ref/vtab_config.html">sqlite3_vtab_config</a>(db, <a href="c3ref/c_vtab_constraint_support.html#sqlitevtabdirectonly">SQLITE_VTAB_DIRECTONLY</a>)" if the
734 virtual table will not be used from inside of triggers or views.
735
736 </p><p>The xCreate method need not initialize the pModule, nRef, and zErrMsg
737 fields of the <a href="c3ref/vtab.html">sqlite3_vtab</a> object. The SQLite core will take care of
738 that chore.
739
740 </p><p>The xCreate should return <a href="rescode.html#ok">SQLITE_OK</a> if it is successful in
741 creating the new virtual table, or <a href="rescode.html#error">SQLITE_ERROR</a> if it is not successful.
742 If not successful, the <a href="c3ref/vtab.html">sqlite3_vtab</a> structure must not be allocated.
743 An error message may optionally be returned in *pzErr if unsuccessful.
744 Space to hold the error message string must be allocated using
745 an SQLite memory allocation function like
746 <a href="c3ref/free.html">sqlite3_malloc()</a> or <a href="c3ref/mprintf.html">sqlite3_mprintf()</a> as the SQLite core will
747 attempt to free the space using <a href="c3ref/free.html">sqlite3_free()</a> after the error has
748 been reported up to the application.
749
750 </p><p>
751 If the xCreate method is omitted (left as a NULL pointer) then the
752 virtual table is an <a href="vtab.html#epoonlyvtab">eponymous-only virtual table</a>. New instances of
753 the virtual table cannot be created using <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> and the
754 virtual table can only be used via its module name.
755 Note that SQLite versions prior to 3.9.0 (2015-10-14) do not understand
756 eponymous-only virtual tables and will segfault if an attempt is made
757 to <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> on an eponymous-only virtual table because
758 the xCreate method was not checked for null.
759
760 </p><p>
761 If the xCreate method is the exact same pointer as the <a href="vtab.html#xconnect">xConnect</a> method,
762 that indicates that the virtual table does not need to initialize backing
763 store. Such a virtual table can be used as an <a href="vtab.html#epovtab">eponymous virtual table</a>
764 or as a named virtual table using <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> or both.
765
766 <a name="hiddencol"></a>
767
768 </p><h3 id="hidden_columns_in_virtual_tables"><span>2.1.1. </span>Hidden columns in virtual tables</h3>
769 <p>If a column datatype contains the special keyword "HIDDEN"
770 (in any combination of upper and lower case letters) then that keyword
771 is omitted from the column datatype name and the column is marked
772 as a hidden column internally.
773 A hidden column differs from a normal column in three respects:
774
775 </p><p>
776 </p><ul>
777 <li> Hidden columns are not listed in the dataset returned by
778 "<a href="pragma.html#pragma_table_info">PRAGMA table_info</a>",
779 </li><li> Hidden columns are not included in the expansion of a "*"
780 expression in the result set of a <a href="lang_select.html">SELECT</a>, and
781 </li><li> Hidden columns are not included in the implicit column-list
782 used by an <a href="lang_insert.html">INSERT</a> statement that lacks an explicit column-list.
783 </li></ul>
784
785 <p>For example, if the following SQL is passed to <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a>:
786
787 </p><div class="codeblock"><pre>CREATE TABLE x(a HIDDEN VARCHAR(12), b INTEGER, c INTEGER Hidden);
788 </pre></div>
789
790 <p>Then the virtual table would be created with two hidden columns,
791 and with datatypes of "VARCHAR(12)" and "INTEGER".
792
793 </p><p>An example use of hidden columns can be seen in the <a href="fts3.html">FTS3</a> virtual
794 table implementation, where every FTS virtual table
795 contains an <a href="fts3.html#hiddencol">FTS hidden column</a> that is used to pass information from the
796 virtual table into <a href="fts3.html#snippet">FTS auxiliary functions</a> and to the <a href="fts3.html#full_text_index_queries">FTS MATCH</a> operator.
797
798 <a name="tabfunc2"></a>
799
800 </p><h3 id="table_valued_functions"><span>2.1.2. </span>Table-valued functions</h3>
801
802 <p>A <a href="vtab.html">virtual table</a> that contains <a href="vtab.html#hiddencol">hidden columns</a> can be used like
803 a table-valued function in the FROM clause of a <a href="lang_select.html">SELECT</a> statement.
804 The arguments to the table-valued function become constraints on
805 the HIDDEN columns of the virtual table.
806
807 </p><p>For example, the "generate_series" extension (located in the
808 <a href="https://sqlite.org/src/artifact?ci=trunk&filename=ext/misc/series.c">ext/misc/series.c</a>
809 file in the <a href="https://sqlite.org/src/tree?ci=trunk">source tree</a>)
810 implements an <a href="vtab.html#epovtab">eponymous virtual table</a> with the following schema:
811
812 </p><div class="codeblock"><pre>CREATE TABLE generate_series(
813 value,
814 start HIDDEN,
815 stop HIDDEN,
816 step HIDDEN
817 );
818 </pre></div>
819
820 <p>The <a href="vtab.html#xbestindex">sqlite3_module.xBestIndex</a> method in the implementation of this
821 table checks for equality constraints against the HIDDEN columns, and uses
822 those as input parameters to determine the range of integer "value" outputs
823 to generate. Reasonable defaults are used for any unconstrained columns.
824 For example, to list all integers between 5 and 50:
825
826 </p><div class="codeblock"><pre>SELECT value FROM generate_series(5,50);
827 </pre></div>
828
829 <p>The previous query is equivalent to the following:
830
831 </p><div class="codeblock"><pre>SELECT value FROM generate_series WHERE start=5 AND stop=50;
832 </pre></div>
833
834 <p>Arguments on the virtual table name are matched to <a href="vtab.html#hiddencol">hidden columns</a>
835 in order. The number of arguments can be less than the
836 number of hidden columns, in which case the latter hidden columns are
837 unconstrained. However, an error results if there are more arguments
838 than there are hidden columns in the virtual table.
839
840 <a name="worid"></a>
841
842 </p><h3 id="_without_rowid_virtual_tables_"><span>2.1.3. </span> WITHOUT ROWID Virtual Tables </h3>
843
844 <p>Beginning with SQLite <a href="releaselog/3_14.html">version 3.14.0</a> (2016-08-08),
845 the CREATE TABLE statement that
846 is passed into <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a> may contain a <a href="withoutrowid.html">WITHOUT ROWID</a> clause.
847 This is useful for cases where the virtual table rows
848 cannot easily be mapped into unique integers. A CREATE TABLE
849 statement that includes WITHOUT ROWID must define one or more columns as
850 the PRIMARY KEY. Every column of the PRIMARY KEY must individually be
851 NOT NULL and all columns for each row must be collectively unique.
852
853 </p><p>Note that SQLite does not enforce the PRIMARY KEY for a WITHOUT ROWID
854 virtual table. Enforcement is the responsibility of the underlying
855 virtual table implementation. But SQLite does assume that the PRIMARY KEY
856 constraint is valid - that the identified columns really are UNIQUE and
857 NOT NULL - and it uses that assumption to optimize queries against the
858 virtual table.
859
860 </p><p>The rowid column is not accessible on a
861 WITHOUT ROWID virtual table (of course).
862
863 </p><p>The <a href="vtab.html#xupdate">xUpdate</a> method was originally designed around having a
864 <a href="lang_createtable.html#rowid">ROWID</a> as a single value. The <a href="vtab.html#xupdate">xUpdate</a> method has been expanded to
865 accommodate an arbitrary PRIMARY KEY in place of the ROWID, but the
866 PRIMARY KEY must still be only one column. For this reason, SQLite
867 will reject any WITHOUT ROWID virtual table that has more than one
868 PRIMARY KEY column and a non-NULL xUpdate method.
869
870 <a name="xconnect"></a>
871
872 </p><h2 id="the_xconnect_method"><span>2.2. </span>The xConnect Method</h2>
873
874 <div class="codeblock"><pre>int (*xConnect)(sqlite3*, void *pAux,
875 int argc, char *const*argv,
876 sqlite3_vtab **ppVTab,
877 char **pzErr);
878 </pre></div>
879
880 <p>The xConnect method is very similar to <a href="vtab.html#xcreate">xCreate</a>.
881 It has the same parameters and constructs a new <a href="c3ref/vtab.html">sqlite3_vtab</a> structure
882 just like xCreate.
883 And it must also call <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a> like xCreate. It
884 should also make all of the same <a href="c3ref/vtab_config.html">sqlite3_vtab_config()</a> calls as
885 xCreate.
886
887 </p><p>The difference is that xConnect is called to establish a new
888 connection to an existing virtual table whereas xCreate is called
889 to create a new virtual table from scratch.
890
891 </p><p>The xCreate and xConnect methods are only different when the
892 virtual table has some kind of backing store that must be initialized
893 the first time the virtual table is created. The xCreate method creates
894 and initializes the backing store. The xConnect method just connects
895 to an existing backing store. When xCreate and xConnect are the same,
896 the table is an <a href="vtab.html#epovtab">eponymous virtual table</a>.
897
898 </p><p>As an example, consider a virtual table implementation that
899 provides read-only access to existing comma-separated-value (CSV)
900 files on disk. There is no backing store that needs to be created
901 or initialized for such a virtual table (since the CSV files already
902 exist on disk) so the xCreate and xConnect methods will be identical
903 for that module.
904
905 </p><p>Another example is a virtual table that implements a full-text index.
906 The xCreate method must create and initialize data structures to hold
907 the dictionary and posting lists for that index. The xConnect method,
908 on the other hand, only has to locate and use an existing dictionary
909 and posting lists that were created by a prior xCreate call.
910
911 </p><p>The xConnect method must return <a href="rescode.html#ok">SQLITE_OK</a> if it is successful
912 in creating the new virtual table, or <a href="rescode.html#error">SQLITE_ERROR</a> if it is not
913 successful. If not successful, the <a href="c3ref/vtab.html">sqlite3_vtab</a> structure must not be
914 allocated. An error message may optionally be returned in *pzErr if
915 unsuccessful.
916 Space to hold the error message string must be allocated using
917 an SQLite memory allocation function like
918 <a href="c3ref/free.html">sqlite3_malloc()</a> or <a href="c3ref/mprintf.html">sqlite3_mprintf()</a> as the SQLite core will
919 attempt to free the space using <a href="c3ref/free.html">sqlite3_free()</a> after the error has
920 been reported up to the application.
921
922 </p><p>The xConnect method is required for every virtual table implementation,
923 though the <a href="vtab.html#xcreate">xCreate</a> and xConnect pointers of the <a href="c3ref/module.html">sqlite3_module</a> object
924 may point to the same function if the virtual table does not need to
925 initialize backing store.
926
927 <a name="xbestindex"></a>
928
929 </p><h2 id="the_xbestindex_method"><span>2.3. </span>The xBestIndex Method</h2>
930
931 <p>SQLite uses the xBestIndex method of a virtual table module to determine
932 the best way to access the virtual table.
933 The xBestIndex method has a prototype like this:
934
935 </p><div class="codeblock"><pre>int (*xBestIndex)(sqlite3_vtab *pVTab, sqlite3_index_info*);
936 </pre></div>
937
938 <p>The SQLite core communicates with the xBestIndex method by filling
939 in certain fields of the <a href="c3ref/index_info.html">sqlite3_index_info</a> structure and passing a
940 pointer to that structure into xBestIndex as the second parameter.
941 The xBestIndex method fills out other fields of this structure which
942 forms the reply. The <a href="c3ref/index_info.html">sqlite3_index_info</a> structure looks like this:
943
944 </p><div class="codeblock"><pre>struct sqlite3_index_info {
945 /* Inputs */
946 const int nConstraint; /* Number of entries in aConstraint */
947 const struct sqlite3_index_constraint {
948 int iColumn; /* Column constrained. -1 for ROWID */
949 unsigned char op; /* Constraint operator */
950 unsigned char usable; /* True if this constraint is usable */
951 int iTermOffset; /* Used internally - xBestIndex should ignore */
952 } *const aConstraint; /* Table of WHERE clause constraints */
953 const int nOrderBy; /* Number of terms in the ORDER BY clause */
954 const struct sqlite3_index_orderby {
955 int iColumn; /* Column number */
956 unsigned char desc; /* True for DESC. False for ASC. */
957 } *const aOrderBy; /* The ORDER BY clause */
958
959 /* Outputs */
960 struct sqlite3_index_constraint_usage {
961 int argvIndex; /* if >0, constraint is part of argv to xFilter */
962 unsigned char omit; /* Do not code a test for this constraint */
963 } *const aConstraintUsage;
964 int idxNum; /* Number used to identify the index */
965 char *idxStr; /* String, possibly obtained from sqlite3_malloc */
966 int needToFreeIdxStr; /* Free idxStr using sqlite3_free() if true */
967 int orderByConsumed; /* True if output is already ordered */
968 double estimatedCost; /* Estimated cost of using this index */
969 <b>/* Fields below are only available in SQLite 3.8.2 and later */</b>
970 sqlite3_int64 estimatedRows; /* Estimated number of rows returned */
971 <b>/* Fields below are only available in SQLite 3.9.0 and later */</b>
972 int idxFlags; /* Mask of SQLITE_INDEX_SCAN_* flags */
973 <b>/* Fields below are only available in SQLite 3.10.0 and later */</b>
974 sqlite3_uint64 colUsed; /* Input: Mask of columns used by statement */
975 };
976 </pre></div>
977
978 <p>Note the warnings on the "estimatedRows", "idxFlags", and "colUsed" fields.
979 These fields were added with SQLite versions 3.8.2, 3.9.0, and 3.10.0, respectively.
980 Any extension that reads or writes these fields must first check that the
981 version of the SQLite library in use is greater than or equal to the appropriate
982 version - perhaps comparing the value returned from <a href="c3ref/libversion.html">sqlite3_libversion_number()</a>
983 against constants 3008002, 3009000, and/or 3010000. The result of attempting
984 to access these fields in an sqlite3_index_info structure created by an
985 older version of SQLite are undefined.
986
987 </p><p>In addition, there are some defined constants:
988
989 </p><div class="codeblock"><pre>#define SQLITE_INDEX_CONSTRAINT_EQ 2
990 #define SQLITE_INDEX_CONSTRAINT_GT 4
991 #define SQLITE_INDEX_CONSTRAINT_LE 8
992 #define SQLITE_INDEX_CONSTRAINT_LT 16
993 #define SQLITE_INDEX_CONSTRAINT_GE 32
994 #define SQLITE_INDEX_CONSTRAINT_MATCH 64
995 #define SQLITE_INDEX_CONSTRAINT_LIKE 65 /* 3.10.0 and later */
996 #define SQLITE_INDEX_CONSTRAINT_GLOB 66 /* 3.10.0 and later */
997 #define SQLITE_INDEX_CONSTRAINT_REGEXP 67 /* 3.10.0 and later */
998 #define SQLITE_INDEX_CONSTRAINT_NE 68 /* 3.21.0 and later */
999 #define SQLITE_INDEX_CONSTRAINT_ISNOT 69 /* 3.21.0 and later */
1000 #define SQLITE_INDEX_CONSTRAINT_ISNOTNULL 70 /* 3.21.0 and later */
1001 #define SQLITE_INDEX_CONSTRAINT_ISNULL 71 /* 3.21.0 and later */
1002 #define SQLITE_INDEX_CONSTRAINT_IS 72 /* 3.21.0 and later */
1003 #define SQLITE_INDEX_CONSTRAINT_LIMIT 73 /* 3.38.0 and later */
1004 #define SQLITE_INDEX_CONSTRAINT_OFFSET 74 /* 3.38.0 and later */
1005 #define SQLITE_INDEX_CONSTRAINT_FUNCTION 150 /* 3.25.0 and later */
1006 #define SQLITE_INDEX_SCAN_UNIQUE 1 /* Scan visits at most 1 row */
1007 </pre></div>
1008
1009 <p>Use the <a href="c3ref/vtab_collation.html">sqlite3_vtab_collation()</a> interface to find the name of
1010 the <a href="datatype3.html#collation">collating sequence</a> that should be used when evaluating the i-th
1011 constraint:
1012
1013 </p><div class="codeblock"><pre>const char *sqlite3_vtab_collation(sqlite3_index_info*, int i);
1014 </pre></div>
1015
1016 <p>The SQLite core calls the xBestIndex method when it is compiling a query
1017 that involves a virtual table. In other words, SQLite calls this method
1018 when it is running <a href="c3ref/prepare.html">sqlite3_prepare()</a> or the equivalent.
1019 By calling this method, the
1020 SQLite core is saying to the virtual table that it needs to access
1021 some subset of the rows in the virtual table and it wants to know the
1022 most efficient way to do that access. The xBestIndex method replies
1023 with information that the SQLite core can then use to conduct an
1024 efficient search of the virtual table.
1025
1026 </p><p>While compiling a single SQL query, the SQLite core might call
1027 xBestIndex multiple times with different settings in <a href="c3ref/index_info.html">sqlite3_index_info</a>.
1028 The SQLite core will then select the combination that appears to
1029 give the best performance.
1030
1031 </p><p>Before calling this method, the SQLite core initializes an instance
1032 of the <a href="c3ref/index_info.html">sqlite3_index_info</a> structure with information about the
1033 query that it is currently trying to process. This information
1034 derives mainly from the WHERE clause and ORDER BY or GROUP BY clauses
1035 of the query, but also from any ON or USING clauses if the query is a
1036 join. The information that the SQLite core provides to the xBestIndex
1037 method is held in the part of the structure that is marked as "Inputs".
1038 The "Outputs" section is initialized to zero.
1039
1040 </p><p>The information in the <a href="c3ref/index_info.html">sqlite3_index_info</a> structure is ephemeral
1041 and may be overwritten or deallocated as soon as the xBestIndex method
1042 returns. If the xBestIndex method needs to remember any part of the
1043 <a href="c3ref/index_info.html">sqlite3_index_info</a> structure, it should make a copy. Care must be
1044 take to store the copy in a place where it will be deallocated, such
1045 as in the idxStr field with needToFreeIdxStr set to 1.
1046
1047 </p><p>Note that xBestIndex will always be called before <a href="vtab.html#xfilter">xFilter</a>, since
1048 the idxNum and idxStr outputs from xBestIndex are required inputs to
1049 xFilter. However, there is no guarantee that xFilter will be called
1050 following a successful xBestIndex.
1051
1052 </p><p>The xBestIndex method is required for every virtual table implementation.
1053
1054 </p><h3 id="inputs"><span>2.3.1. </span>Inputs</h3>
1055
1056 <p>The main thing that the SQLite core is trying to communicate to
1057 the virtual table is the constraints that are available to limit
1058 the number of rows that need to be searched. The aConstraint[] array
1059 contains one entry for each constraint. There will be exactly
1060 nConstraint entries in that array.
1061
1062 </p><p>Each constraint will usually correspond to a term in the WHERE clause
1063 or in a USING or ON clause that is of the form
1064
1065 </p><blockquote>
1066 column OP EXPR
1067 </blockquote>
1068
1069 <p>Where "column" is a column in the virtual table, OP is an operator
1070 like "=" or "&lt;", and EXPR is an arbitrary expression. So, for example,
1071 if the WHERE clause contained a term like this:
1072
1073 </p><div class="codeblock"><pre>a = 5
1074 </pre></div>
1075
1076 <p>Then one of the constraints would be on the "a" column with
1077 operator "=" and an expression of "5". Constraints need not have a
1078 literal representation of the WHERE clause. The query optimizer might
1079 make transformations to the
1080 WHERE clause in order to extract as many constraints
1081 as it can. So, for example, if the WHERE clause contained something
1082 like this:
1083
1084 </p><div class="codeblock"><pre>x BETWEEN 10 AND 100 AND 999&gt;y
1085 </pre></div>
1086
1087 <p>The query optimizer might translate this into three separate constraints:
1088
1089 </p><div class="codeblock"><pre>x &gt;= 10
1090 x &lt;= 100
1091 y &lt; 999
1092 </pre></div>
1093
1094 <p>For each such constraint, the aConstraint[].iColumn field indicates which
1095 column appears on the left-hand side of the constraint.
1096 The first column of the virtual table is column 0.
1097 The rowid of the virtual table is column -1.
1098 The aConstraint[].op field indicates which operator is used.
1099 The SQLITE_INDEX_CONSTRAINT_* constants map integer constants
1100 into operator values.
1101 Columns occur in the order they were defined by the call to
1102 <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a> in the <a href="vtab.html#xcreate">xCreate</a> or <a href="vtab.html#xconnect">xConnect</a> method.
1103 Hidden columns are counted when determining the column index.
1104
1105 </p><p>If the <a href="vtab.html#xfindfunction">xFindFunction()</a> method for the virtual table is defined, and
1106 if xFindFunction() sometimes returns <a href="c3ref/c_index_constraint_eq.html">SQLITE_INDEX_CONSTRAINT_FUNCTION</a> or
1107 larger, then the constraints might also be of the form:
1108
1109 </p><blockquote>
1110 FUNCTION( column, EXPR)
1111 </blockquote>
1112
1113 <p>In this case the aConstraint[].op value is the same as the value
1114 returned by <a href="vtab.html#xfindfunction">xFindFunction()</a> for FUNCTION.
1115
1116 </p><p>The aConstraint[] array contains information about all constraints
1117 that apply to the virtual table. But some of the constraints might
1118 not be usable because of the way tables are ordered in a join.
1119 The xBestIndex method must therefore only consider constraints
1120 that have an aConstraint[].usable flag which is true.
1121
1122 </p><p>In addition to WHERE clause constraints, the SQLite core also
1123 tells the xBestIndex method about the ORDER BY clause.
1124 (In an aggregate query, the SQLite core might put in GROUP BY clause
1125 information in place of the ORDER BY clause information, but this fact
1126 should not make any difference to the xBestIndex method.)
1127 If all terms of the ORDER BY clause are columns in the virtual table,
1128 then nOrderBy will be the number of terms in the ORDER BY clause
1129 and the aOrderBy[] array will identify the column for each term
1130 in the order by clause and whether or not that column is ASC or DESC.
1131
1132 <a name="colUsed"></a>
1133
1134 </p><p>In SQLite <a href="releaselog/3_10_0.html">version 3.10.0</a> (2016-01-06) and later,
1135 the colUsed field is available
1136 to indicate which fields of the virtual table are actually used by the
1137 statement being prepared. If the lowest bit of colUsed is set, that
1138 means that the first column is used. The second lowest bit corresponds
1139 to the second column. And so forth. If the most significant bit of
1140 colUsed is set, that means that one or more columns other than the
1141 first 63 columns are used. If column usage information is needed by the
1142 <a href="vtab.html#xfilter">xFilter</a> method, then the required bits must be encoded into either
1143 the output idxNum field or idxStr content.
1144
1145 </p><h4 id="like_glob_regexp_and_match_functions"><span>2.3.1.1. </span>LIKE, GLOB, REGEXP, and MATCH functions</h4>
1146
1147 <p>For the LIKE, GLOB, REGEXP, and MATCH operators, the
1148 aConstraint[].iColumn value is the virtual table column that
1149 is the left operand of the operator. However, if these operators
1150 are expressed as function calls instead of operators, then
1151 the aConstraint[].iColumn value references the virtual table
1152 column that is the second argument to that function:
1153
1154 </p><blockquote>
1155 LIKE(<i>EXPR</i>, <i>column</i>)<br>
1156 GLOB(<i>EXPR</i>, <i>column</i>)<br>
1157 REGEXP(<i>EXPR</i>, <i>column</i>)<br>
1158 MATCH(<i>EXPR</i>, <i>column</i>)<br>
1159 </blockquote>
1160
1161 <p>Hence, as far as the xBestIndex() method is concerned, the following
1162 two forms are equivalent:
1163
1164 </p><blockquote>
1165 <i>column</i> LIKE <i>EXPR</i><br>
1166 LIKE(<i>EXPR</i>,<i>column</i>)
1167 </blockquote>
1168
1169 <p>This special behavior of looking at the second argument of a function
1170 only occurs for the LIKE, GLOB, REGEXP, and MATCH functions. For all
1171 other functions, the aConstraint[].iColumn value references the first
1172 argument of the function.
1173
1174 </p><p>This special feature of LIKE, GLOB, REGEXP, and MATCH does not
1175 apply to the <a href="vtab.html#xfindfunction">xFindFunction()</a> method, however. The
1176 <a href="vtab.html#xfindfunction">xFindFunction()</a> method always keys off of the left operand of an
1177 LIKE, GLOB, REGEXP, or MATCH operator but off of the first argument
1178 to function-call equivalents of those operators.
1179
1180 </p><h4 id="limit_and_offset"><span>2.3.1.2. </span>LIMIT and OFFSET</h4>
1181
1182 <p>When aConstraint[].op is one of SQLITE_INDEX_CONSTRAINT_LIMIT or
1183 SQLITE_INDEX_CONSTRAINT_OFFSET, that indicates that there is a
1184 LIMIT or OFFSET clause on the SQL query statement that is using
1185 the virtual table. The LIMIT and OFFSET operators have no
1186 left operand, and so when aConstraint[].op is one of
1187 SQLITE_INDEX_CONSTRAINT_LIMIT or SQLITE_INDEX_CONSTRAINT_OFFSET
1188 then the aConstraint[].iColumn value is meaningless and should
1189 not be used.
1190
1191 </p><h4 id="right_hand_side_values_of_constraints"><span>2.3.1.3. </span>Right-hand side values of constraints</h4>
1192
1193 <p>The <a href="c3ref/vtab_rhs_value.html">sqlite3_vtab_rhs_value()</a> interface can be used to try to
1194 access the right-hand operand of a constraint. However, the value
1195 of a right-hand operator might not be known at the time that
1196 the xBestIndex method is run, so the sqlite3_vtab_rhs_value()
1197 call might not be successful. Usually the right operand of a
1198 constraint is only available to xBestIndex if it is coded as
1199 a literal value in the input SQL. If the right operand is
1200 coded as an expression or a <a href="c3ref/bind_blob.html">host parameter</a>, it probably will
1201 not be accessible to xBestIndex. Some operators, such as
1202 <a href="c3ref/c_index_constraint_eq.html">SQLITE_INDEX_CONSTRAINT_ISNULL</a> and
1203 <a href="c3ref/c_index_constraint_eq.html">SQLITE_INDEX_CONSTRAINT_ISNOTNULL</a> have no right-hand operand.
1204 The sqlite3_vtab_rhs_value() interface always returns
1205 <a href="rescode.html#notfound">SQLITE_NOTFOUND</a> for such operators.
1206
1207 </p><h3 id="outputs"><span>2.3.2. </span>Outputs</h3>
1208
1209 <p>Given all of the information above, the job of the xBestIndex
1210 method is to figure out the best way to search the virtual table.
1211
1212 </p><p>The xBestIndex method conveys an indexing strategy to the <a href="vtab.html#xfilter">xFilter</a>
1213 method through the idxNum and idxStr fields. The idxNum value and
1214 idxStr string content are arbitrary as far as the SQLite core is
1215 concerned and can have any meaning as long as xBestIndex and xFilter
1216 agree on what that meaning is. The SQLite core just copies the
1217 information from xBestIndex through to the <a href="vtab.html#xfilter">xFilter</a> method, assuming
1218 only that the char sequence referenced via idxStr is NUL terminated.
1219
1220 </p><p>The idxStr value may be a string obtained from an SQLite
1221 memory allocation function such as <a href="c3ref/mprintf.html">sqlite3_mprintf()</a>.
1222 If this is the case, then the needToFreeIdxStr flag must be set to
1223 true so that the SQLite core will know to call <a href="c3ref/free.html">sqlite3_free()</a> on
1224 that string when it has finished with it, and thus avoid a memory leak.
1225 The idxStr value may also be a static constant string, in which case
1226 the needToFreeIdxStr boolean should remain false.
1227
1228
1229 </p><p>The estimatedCost field should be set to the estimated number
1230 of disk access operations required to execute this query against
1231 the virtual table. The SQLite core will often call xBestIndex
1232 multiple times with different constraints, obtain multiple cost
1233 estimates, then choose the query plan that gives the lowest estimate.
1234 The SQLite core initializes estimatedCost to a very large value
1235 prior to invoking xBestIndex, so if xBestIndex determines that the
1236 current combination of parameters is undesirable, it can leave the
1237 estimatedCost field unchanged to discourage its use.
1238
1239 </p><p>If the current version of SQLite is 3.8.2 or greater, the estimatedRows
1240 field may be set to an estimate of the number of rows returned by the
1241 proposed query plan. If this value is not explicitly set, the default
1242 estimate of 25 rows is used.
1243
1244 </p><p>If the current version of SQLite is 3.9.0 or greater, the idxFlags field
1245 may be set to SQLITE_INDEX_SCAN_UNIQUE to indicate that the virtual table
1246 will return only zero or one rows given the input constraints. Additional
1247 bits of the idxFlags field might be understood in later versions of SQLite.
1248
1249 </p><p>The aConstraintUsage[] array contains one element for each of
1250 the nConstraint constraints in the inputs section of the
1251 <a href="c3ref/index_info.html">sqlite3_index_info</a> structure.
1252 The aConstraintUsage[] array is used by xBestIndex to tell the
1253 core how it is using the constraints.
1254
1255 </p><p>The xBestIndex method may set aConstraintUsage[].argvIndex
1256 entries to values greater than zero.
1257 Exactly one entry should be set to 1, another to 2, another to 3,
1258 and so forth up to as many or as few as the xBestIndex method wants.
1259 The EXPR of the corresponding constraints will then be passed
1260 in as the argv[] parameters to xFilter.
1261
1262 </p><p>For example, if the aConstraint[3].argvIndex is set to 1, then
1263 when xFilter is called, the argv[0] passed to xFilter will have
1264 the EXPR value of the aConstraint[3] constraint.
1265
1266 </p><h4 id="omit_constraint_checking_in_bytecode"><span>2.3.2.1. </span>Omit constraint checking in bytecode</h4>
1267
1268 <p>By default, the SQLite generates <a href="opcode.html">bytecode</a> that will double-check
1269 all constraints on each row of the virtual table to verify
1270 that they are satisfied. If the virtual table can guarantee
1271 that a constraint will always be satisfied, it can try to
1272 suppress that double-check by setting aConstraintUsage[].omit.
1273 However, with some exceptions, this is only a hint and
1274 there is no guarantee that the redundant check of the constraint
1275 will be suppressed. Key points:
1276
1277 </p><ul>
1278 <li><p>
1279 The omit flag is only honored if the argvIndex value for the
1280 constraint is greater than 0 and less than or equal to 16.
1281 Constraint checking is never suppressed for constraints
1282 that do not pass their right operand into the xFilter method.
1283 The current implementation is only able to suppress redundant
1284 constraint checking for the first 16 values passed to xFilter,
1285 though that limitation might be increased in future releases.
1286
1287 </p></li><li><p>
1288 The omit flag is always honored for <a href="c3ref/c_index_constraint_eq.html">SQLITE_INDEX_CONSTRAINT_OFFSET</a>
1289 constraints as long as argvIndex is greater than 0. Setting the
1290 omit flag on an SQLITE_INDEX_CONSTRAINT_OFFSET constraint indicates
1291 to SQLite that the virtual table will itself suppress the first N
1292 rows of output, where N is the right operand of the OFFSET operator.
1293 If the virtual table implementation sets omit on an
1294 SQLITE_INDEX_CONSTRAINT_OFFSET constraint but then fails to suppress
1295 the first N rows of output, an incorrect answer will result from
1296 the overall query.
1297 </p></li></ul>
1298
1299 <a name="obc"></a>
1300
1301 <h4 id="order_by_and_orderbyconsumed"><span>2.3.2.2. </span>ORDER BY and orderByConsumed</h4>
1302
1303 <p>If the virtual table will output rows in the order specified by
1304 the ORDER BY clause, then the orderByConsumed flag may be set to
1305 true. If the output is not automatically in the correct order
1306 then orderByConsumed must be left in its default false setting.
1307 This will indicate to the SQLite core that it will need to do a
1308 separate sorting pass over the data after it comes out of the virtual table.
1309 Setting orderByConsumed is an optimization. A query will always
1310 get the correct answer if orderByConsumed is left at its default
1311 value (0). Unnecessary sort operations might be avoided resulting
1312 in a faster query if orderByConsumed is set, but setting
1313 orderByConsumed incorrectly can result in an incorrect answer.
1314 It is suggested that new virtual table implementations leave
1315 the orderByConsumed value unset initially, and then after everything
1316 else is known to be working correctly, go back and attempt to
1317 optimize by setting orderByConsumed where appropriate.
1318
1319 </p><p>Sometimes the orderByConsumed flag can be safely set even if
1320 the outputs from the virtual table are not strictly in the order
1321 specified by nOrderBy and aOrderBy. If the
1322 <a href="c3ref/vtab_distinct.html">sqlite3_vtab_distinct()</a> interface returns 1 or 2, that indicates
1323 that the ordering can be relaxed. See the documentation on
1324 <a href="c3ref/vtab_distinct.html">sqlite3_vtab_distinct()</a> for further information.
1325
1326
1327 </p><h3 id="return_value"><span>2.3.3. </span>Return Value</h3>
1328
1329 <p>The xBestIndex method should return SQLITE_OK on success. If any
1330 kind of fatal error occurs, an appropriate error code (ex: <a href="rescode.html#nomem">SQLITE_NOMEM</a>)
1331 should be returned instead.
1332
1333 </p><p>If xBestIndex returns <a href="rescode.html#constraint">SQLITE_CONSTRAINT</a>, that does not indicate an
1334 error. Rather, SQLITE_CONSTRAINT indicates that the particular combination
1335 of input parameters specified is insufficient for the virtual table
1336 to do its job.
1337 This is logically the same as setting the estimatedCost to infinity.
1338 If every call to xBestIndex for a particular query plan returns
1339 SQLITE_CONSTRAINT, that means there is no way for the virtual table
1340 to be safely used, and the <a href="c3ref/prepare.html">sqlite3_prepare()</a> call will fail with
1341 a "no query solution" error.
1342
1343 <a name="erpotvf"></a>
1344
1345 </p><h3 id="enforcing_required_parameters_on_table_valued_functions"><span>2.3.4. </span>Enforcing Required Parameters On Table-Valued Functions</h3>
1346
1347 <p>The SQLITE_CONSTRAINT return from xBestIndex
1348 is useful for <a href="vtab.html#tabfunc2">table-valued functions</a> that
1349 have required parameters. If the aConstraint[].usable field is false
1350 for one of the required parameter, then the xBestIndex method should
1351 return SQLITE_CONSTRAINT. If a required field does not appear in
1352 the aConstraint[] array at all, that means that the corresponding
1353 parameter is omitted from the input SQL. In that case, xBestIndex
1354 should set an error message in pVTab-&gt;zErrMsg and return
1355 SQLITE_ERROR. To summarize:
1356
1357 </p><ol>
1358 <li><p>
1359 The aConstraint[].usable value for a required parameter is
1360 false <big>&rarr;</big> return SQLITE_CONSTRAINT.
1361 </p></li><li><p>
1362 A required parameter does not appear anywhere in
1363 the aConstraint[] array <big>&rarr;</big>
1364 Set an error message in pVTab-&gt;zErrMsg and return
1365 SQLITE_ERROR
1366 </p></li></ol>
1367
1368 <p>The following example will better illustrate the use of SQLITE_CONSTRAINT
1369 as a return value from xBestIndex:
1370
1371 </p><div class="codeblock"><pre>SELECT * FROM realtab, tablevaluedfunc(realtab.x);
1372 </pre></div>
1373
1374 <p>Assuming that the first hidden column of "tablevaluedfunc" is "param1",
1375 the query above is semantically equivalent to this:
1376
1377 </p><div class="codeblock"><pre>SELECT * FROM realtab, tablevaluedfunc
1378 WHERE tablevaluedfunc.param1 = realtab.x;
1379 </pre></div>
1380
1381 <p>The query planner must decide between many possible implementations
1382 of this query, but two plans in particular are of note:
1383
1384 </p><ol>
1385 <li><p>Scan all
1386 rows of realtab and for each row, find rows in tablevaluedfunc where
1387 param1 is equal to realtab.x
1388
1389 </p></li><li><p>Scan all rows of tablevalued func and for each row find rows
1390 in realtab where x is equal to tablevaluedfunc.param1.
1391 </p></li></ol>
1392
1393 <p>The xBestIndex method will be invoked once for each of the potential
1394 plans above. For plan 1, the aConstraint[].usable flag for the
1395 SQLITE_CONSTRAINT_EQ constraint on the param1 column will be true because
1396 the right-hand side value for the "param1 = ?" constraint will be known,
1397 since it is determined by the outer realtab loop.
1398 But for plan 2, the aConstraint[].usable flag for "param1 = ?" will be false
1399 because the right-hand side value is determined by an inner loop and is thus
1400 an unknown quantity. Because param1 is a required input to the table-valued
1401 functions, the xBestIndex method should return SQLITE_CONSTRAINT when presented
1402 with plan 2, indicating that a required input is missing. This forces the
1403 query planner to select plan 1.
1404
1405 <a name="xdisconnect"></a>
1406
1407 </p><h2 id="the_xdisconnect_method"><span>2.4. </span>The xDisconnect Method</h2>
1408
1409 <div class="codeblock"><pre>int (*xDisconnect)(sqlite3_vtab *pVTab);
1410 </pre></div>
1411
1412 <p>This method releases a connection to a virtual table.
1413 Only the <a href="c3ref/vtab.html">sqlite3_vtab</a> object is destroyed.
1414 The virtual table is not destroyed and any backing store
1415 associated with the virtual table persists.
1416
1417 This method undoes the work of <a href="vtab.html#xconnect">xConnect</a>.
1418
1419 </p><p>This method is a destructor for a connection to the virtual table.
1420 Contrast this method with <a href="vtab.html#sqlite3_module.xDestroy">xDestroy</a>. The xDestroy is a destructor
1421 for the entire virtual table.
1422
1423 </p><p>The xDisconnect method is required for every virtual table implementation,
1424 though it is acceptable for the xDisconnect and <a href="vtab.html#sqlite3_module.xDestroy">xDestroy</a> methods to be
1425 the same function if that makes sense for the particular virtual table.
1426
1427 <a name="sqlite3_module.xDestroy"></a>
1428
1429 </p><h2 id="the_xdestroy_method"><span>2.5. </span>The xDestroy Method</h2>
1430
1431 <div class="codeblock"><pre>int (*xDestroy)(sqlite3_vtab *pVTab);
1432 </pre></div>
1433
1434 <p>This method releases a connection to a virtual table, just like
1435 the <a href="vtab.html#xdisconnect">xDisconnect</a> method, and it also destroys the underlying
1436 table implementation. This method undoes the work of <a href="vtab.html#xcreate">xCreate</a>.
1437
1438 </p><p>The <a href="vtab.html#xdisconnect">xDisconnect</a> method is called whenever a database connection
1439 that uses a virtual table is closed. The xDestroy method is only
1440 called when a <a href="lang_droptable.html">DROP TABLE</a> statement is executed against the virtual table.
1441
1442 </p><p>The xDestroy method is required for every virtual table implementation,
1443 though it is acceptable for the <a href="vtab.html#xdisconnect">xDisconnect</a> and xDestroy methods to be
1444 the same function if that makes sense for the particular virtual table.
1445
1446 <a name="xopen"></a>
1447
1448 </p><h2 id="the_xopen_method"><span>2.6. </span>The xOpen Method</h2>
1449
1450 <div class="codeblock"><pre>int (*xOpen)(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor);
1451 </pre></div>
1452
1453 <p>The xOpen method creates a new cursor used for accessing (read and/or
1454 writing) a virtual table. A successful invocation of this method
1455 will allocate the memory for the <a href="c3ref/vtab_cursor.html">sqlite3_vtab_cursor</a> (or a subclass),
1456 initialize the new object, and make *ppCursor point to the new object.
1457 The successful call then returns <a href="rescode.html#ok">SQLITE_OK</a>.
1458
1459 </p><p>For every successful call to this method, the SQLite core will
1460 later invoke the <a href="vtab.html#xclose">xClose</a> method to destroy
1461 the allocated cursor.
1462
1463 </p><p>The xOpen method need not initialize the pVtab field of the
1464 <a href="c3ref/vtab_cursor.html">sqlite3_vtab_cursor</a> structure. The SQLite core will take care
1465 of that chore automatically.
1466
1467 </p><p>A virtual table implementation must be able to support an arbitrary
1468 number of simultaneously open cursors.
1469
1470 </p><p>When initially opened, the cursor is in an undefined state.
1471 The SQLite core will invoke the <a href="vtab.html#xfilter">xFilter</a> method
1472 on the cursor prior to any attempt to position or read from the cursor.
1473
1474 </p><p>The xOpen method is required for every virtual table implementation.
1475
1476 <a name="xclose"></a>
1477
1478 </p><h2 id="the_xclose_method"><span>2.7. </span>The xClose Method</h2>
1479
1480 <div class="codeblock"><pre>int (*xClose)(sqlite3_vtab_cursor*);
1481 </pre></div>
1482
1483 <p>The xClose method closes a cursor previously opened by
1484 <a href="vtab.html#xopen">xOpen</a>.
1485 The SQLite core will always call xClose once for each cursor opened
1486 using xOpen.
1487
1488 </p><p>This method must release all resources allocated by the
1489 corresponding xOpen call. The routine will not be called again even if it
1490 returns an error. The SQLite core will not use the
1491 <a href="c3ref/vtab_cursor.html">sqlite3_vtab_cursor</a> again after it has been closed.
1492
1493 </p><p>The xClose method is required for every virtual table implementation.
1494
1495 <a name="xeof"></a>
1496
1497 </p><h2 id="the_xeof_method"><span>2.8. </span>The xEof Method</h2>
1498
1499 <div class="codeblock"><pre>int (*xEof)(sqlite3_vtab_cursor*);
1500 </pre></div>
1501
1502 <p>The xEof method must return false (zero) if the specified cursor
1503 currently points to a valid row of data, or true (non-zero) otherwise.
1504 This method is called by the SQL engine immediately after each
1505 <a href="vtab.html#xfilter">xFilter</a> and <a href="vtab.html#xnext">xNext</a> invocation.
1506
1507 </p><p>The xEof method is required for every virtual table implementation.
1508
1509 <a name="xfilter"></a>
1510
1511 </p><h2 id="the_xfilter_method"><span>2.9. </span>The xFilter Method</h2>
1512
1513 <div class="codeblock"><pre>int (*xFilter)(sqlite3_vtab_cursor*, int idxNum, const char *idxStr,
1514 int argc, sqlite3_value **argv);
1515 </pre></div>
1516
1517 <p>This method begins a search of a virtual table.
1518 The first argument is a cursor opened by <a href="vtab.html#xopen">xOpen</a>.
1519 The next two arguments define a particular search index previously
1520 chosen by <a href="vtab.html#xbestindex">xBestIndex</a>. The specific meanings of idxNum and idxStr
1521 are unimportant as long as xFilter and xBestIndex agree on what
1522 that meaning is.
1523
1524 </p><p>The xBestIndex function may have requested the values of
1525 certain expressions using the aConstraintUsage[].argvIndex values
1526 of the <a href="c3ref/index_info.html">sqlite3_index_info</a> structure.
1527 Those values are passed to xFilter using the argc and argv parameters.
1528
1529 </p><p>If the virtual table contains one or more rows that match the
1530 search criteria, then the cursor must be left pointing at the first row.
1531 Subsequent calls to <a href="vtab.html#xeof">xEof</a> must return false (zero).
1532 If there are no rows matching, then the cursor must be left in a state
1533 that will cause the <a href="vtab.html#xeof">xEof</a> to return true (non-zero).
1534 The SQLite engine will use
1535 the <a href="vtab.html#xcolumn">xColumn</a> and <a href="vtab.html#xrowid">xRowid</a> methods to access that row content.
1536 The <a href="vtab.html#xnext">xNext</a> method will be used to advance to the next row.
1537
1538 </p><p>This method must return <a href="rescode.html#ok">SQLITE_OK</a> if successful, or an sqlite
1539 <a href="rescode.html">error code</a> if an error occurs.
1540
1541 </p><p>The xFilter method is required for every virtual table implementation.
1542
1543 <a name="xnext"></a>
1544
1545 </p><h2 id="the_xnext_method"><span>2.10. </span>The xNext Method</h2>
1546
1547 <div class="codeblock"><pre>int (*xNext)(sqlite3_vtab_cursor*);
1548 </pre></div>
1549
1550 <p>The xNext method advances a <a href="c3ref/vtab_cursor.html">virtual table cursor</a>
1551 to the next row of a result set initiated by <a href="vtab.html#xfilter">xFilter</a>.
1552 If the cursor is already pointing at the last row when this
1553 routine is called, then the cursor no longer points to valid
1554 data and a subsequent call to the <a href="vtab.html#xeof">xEof</a> method must return true (non-zero).
1555 If the cursor is successfully advanced to another row of content, then
1556 subsequent calls to <a href="vtab.html#xeof">xEof</a> must return false (zero).
1557
1558 </p><p>This method must return <a href="rescode.html#ok">SQLITE_OK</a> if successful, or an sqlite
1559 <a href="rescode.html">error code</a> if an error occurs.
1560
1561 </p><p>The xNext method is required for every virtual table implementation.
1562
1563 <a name="xcolumn"></a>
1564
1565 </p><h2 id="the_xcolumn_method"><span>2.11. </span>The xColumn Method</h2>
1566
1567 <div class="codeblock"><pre>int (*xColumn)(sqlite3_vtab_cursor*, sqlite3_context*, int N);
1568 </pre></div>
1569
1570 <p>The SQLite core invokes this method in order to find the value for
1571 the N-th column of the current row. N is zero-based so the first column
1572 is numbered 0.
1573 The xColumn method may return its result back to SQLite using one of the
1574 following interfaces:
1575
1576 </p><p>
1577 </p><ul>
1578 <li> <a href="c3ref/result_blob.html">sqlite3_result_blob()</a>
1579 </li><li> <a href="c3ref/result_blob.html">sqlite3_result_blob64()</a>
1580 </li><li> <a href="c3ref/result_blob.html">sqlite3_result_double()</a>
1581 </li><li> <a href="c3ref/result_blob.html">sqlite3_result_int()</a>
1582 </li><li> <a href="c3ref/result_blob.html">sqlite3_result_int64()</a>
1583 </li><li> <a href="c3ref/result_blob.html">sqlite3_result_null()</a>
1584 </li><li> <a href="c3ref/result_blob.html">sqlite3_result_pointer()</a>
1585 </li><li> <a href="c3ref/result_blob.html">sqlite3_result_text()</a>
1586 </li><li> <a href="c3ref/result_blob.html">sqlite3_result_text16()</a>
1587 </li><li> <a href="c3ref/result_blob.html">sqlite3_result_text16le()</a>
1588 </li><li> <a href="c3ref/result_blob.html">sqlite3_result_text16be()</a>
1589 </li><li> <a href="c3ref/result_blob.html">sqlite3_result_text64()</a>
1590 </li><li> <a href="c3ref/result_blob.html">sqlite3_result_value()</a>
1591 </li><li> <a href="c3ref/result_blob.html">sqlite3_result_zeroblob()</a>
1592 </li><li> <a href="c3ref/result_blob.html">sqlite3_result_zeroblob64()</a>
1593 </li></ul>
1594
1595
1596 <p>If the xColumn method implementation calls none of the functions above,
1597 then the value of the column defaults to an SQL NULL.
1598
1599 </p><p>To raise an error, the xColumn method should use one of the result_text()
1600 methods to set the error message text, then return an appropriate
1601 <a href="rescode.html">error code</a>. The xColumn method must return <a href="rescode.html#ok">SQLITE_OK</a> on success.
1602
1603 </p><p>The xColumn method is required for every virtual table implementation.
1604
1605 <a name="xrowid"></a>
1606
1607 </p><h2 id="the_xrowid_method"><span>2.12. </span>The xRowid Method</h2>
1608
1609 <div class="codeblock"><pre>int (*xRowid)(sqlite3_vtab_cursor *pCur, sqlite_int64 *pRowid);
1610 </pre></div>
1611
1612 <p>A successful invocation of this method will cause *pRowid to be
1613 filled with the <a href="lang_createtable.html#rowid">rowid</a> of row that the
1614 <a href="c3ref/vtab_cursor.html">virtual table cursor</a> pCur is currently pointing at.
1615 This method returns <a href="rescode.html#ok">SQLITE_OK</a> on success.
1616 It returns an appropriate <a href="rescode.html">error code</a> on failure.</p>
1617
1618 <p>The xRowid method is required for every virtual table implementation.
1619
1620 <a name="xupdate"></a>
1621
1622 </p><h2 id="the_xupdate_method"><span>2.13. </span>The xUpdate Method</h2>
1623
1624 <div class="codeblock"><pre>int (*xUpdate)(
1625 sqlite3_vtab *pVTab,
1626 int argc,
1627 sqlite3_value **argv,
1628 sqlite_int64 *pRowid
1629 );
1630 </pre></div>
1631
1632 <p>All changes to a virtual table are made using the xUpdate method.
1633 This one method can be used to insert, delete, or update.
1634
1635 </p><p>The argc parameter specifies the number of entries in the argv array.
1636 The value of argc will be 1 for a pure delete operation or N+2 for an insert
1637 or replace or update where N is the number of columns in the table.
1638 In the previous sentence, N includes any hidden columns.
1639
1640 </p><p>Every argv entry will have a non-NULL value in C but may contain the
1641 SQL value NULL. In other words, it is always true that
1642 <tt>argv&#91;i&#93;!=0</tt> for <b>i</b> between 0 and <tt>argc-1</tt>.
1643 However, it might be the case that
1644 <tt>sqlite3_value_type(argv&#91;i&#93;)==SQLITE_NULL</tt>.
1645
1646 </p><p>The argv[0] parameter is the <a href="lang_createtable.html#rowid">rowid</a> of a row in the virtual table
1647 to be deleted. If argv[0] is an SQL NULL, then no deletion occurs.
1648
1649 </p><p>The argv[1] parameter is the rowid of a new row to be inserted
1650 into the virtual table. If argv[1] is an SQL NULL, then the implementation
1651 must choose a rowid for the newly inserted row. Subsequent argv[]
1652 entries contain values of the columns of the virtual table, in the
1653 order that the columns were declared. The number of columns will
1654 match the table declaration that the <a href="vtab.html#xconnect">xConnect</a> or <a href="vtab.html#xcreate">xCreate</a> method made
1655 using the <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a> call. All hidden columns are included.
1656
1657 </p><p>When doing an insert without a rowid (argc>1, argv[1] is an SQL NULL),
1658 on a virtual table that uses ROWID (but not on a <a href="vtab.html#worid">WITHOUT ROWID virtual table</a>),
1659 the implementation must set *pRowid to the rowid of the newly inserted row;
1660 this will become the value returned by the <a href="c3ref/last_insert_rowid.html">sqlite3_last_insert_rowid()</a>
1661 function. Setting this value in all the other cases is a harmless no-op;
1662 the SQLite engine ignores the *pRowid return value if argc==1 or
1663 argv[1] is not an SQL NULL.
1664
1665 </p><p>Each call to xUpdate will fall into one of cases shown below.
1666 Not that references to <b>argv&#91;i&#93;</b> mean the SQL value
1667 held within the argv&#91;i&#93; object, not the argv&#91;i&#93;
1668 object itself.
1669
1670 </p><blockquote>
1671 <dl>
1672 <dt><b>argc = 1 <br> argv[0] &ne; NULL</b>
1673 </dt><dd><p>
1674 DELETE: The single row with rowid or PRIMARY KEY equal to argv[0] is deleted.
1675 No insert occurs.
1676
1677 </p></dd><dt><b>argc &gt; 1 <br> argv[0] = NULL</b>
1678 </dt><dd><p>
1679 INSERT: A new row is inserted with column values taken from
1680 argv[2] and following. In a rowid virtual table, if argv[1] is an SQL NULL,
1681 then a new unique rowid is generated automatically. The argv[1] will be NULL
1682 for a <a href="vtab.html#worid">WITHOUT ROWID virtual table</a>, in which case the implementation should
1683 take the PRIMARY KEY value from the appropriate column in argv[2] and following.
1684
1685 </p></dd><dt><b>argc &gt; 1 <br> argv[0] &ne; NULL <br> argv[0] = argv[1]</b>
1686 </dt><dd><p>
1687 UPDATE:
1688 The row with rowid or PRIMARY KEY argv[0] is updated with new values
1689 in argv[2] and following parameters.
1690
1691 </p></dd><dt><b>argc &gt; 1 <br> argv[0] &ne; NULL <br> argv[0] &ne; argv[1]</b>
1692 </dt><dd><p>
1693 UPDATE with rowid or PRIMARY KEY change:
1694 The row with rowid or PRIMARY KEY argv[0] is updated with
1695 the rowid or PRIMARY KEY in argv[1]
1696 and new values in argv[2] and following parameters. This will occur
1697 when an SQL statement updates a rowid, as in the statement:
1698 </p><blockquote>
1699 <a href="lang_update.html">UPDATE</a> table SET rowid=rowid+1 WHERE ...;
1700 </blockquote>
1701 </dd></dl>
1702 </blockquote>
1703
1704 <p>The xUpdate method must return <a href="rescode.html#ok">SQLITE_OK</a> if and only if it is
1705 successful. If a failure occurs, the xUpdate must return an appropriate
1706 <a href="rescode.html">error code</a>. On a failure, the pVTab->zErrMsg element may optionally
1707 be replaced with error message text stored in memory allocated from SQLite
1708 using functions such as <a href="c3ref/mprintf.html">sqlite3_mprintf()</a> or <a href="c3ref/free.html">sqlite3_malloc()</a>.
1709
1710 </p><p>If the xUpdate method violates some constraint of the virtual table
1711 (including, but not limited to, attempting to store a value of the wrong
1712 datatype, attempting to store a value that is too
1713 large or too small, or attempting to change a read-only value) then the
1714 xUpdate must fail with an appropriate <a href="rescode.html">error code</a>.
1715
1716 </p><p>If the xUpdate method is performing an UPDATE, then
1717 <a href="c3ref/value_blob.html">sqlite3_value_nochange(X)</a> can be used to discover which columns
1718 of the virtual table were actually modified by the UPDATE
1719 statement. The <a href="c3ref/value_blob.html">sqlite3_value_nochange(X)</a> interface returns
1720 true for columns that do not change.
1721 On every UPDATE, SQLite will first invoke
1722 <a href="vtab.html#xcolumn">xColumn</a> separately for each unchanging column in the table to
1723 obtain the value for that column. The <a href="vtab.html#xcolumn">xColumn</a> method can
1724 check to see if the column is unchanged at the SQL level
1725 by invoking <a href="c3ref/vtab_nochange.html">sqlite3_vtab_nochange()</a>. If <a href="vtab.html#xcolumn">xColumn</a> sees that
1726 the column is not being modified, it should return without setting
1727 a result using one of the <a href="c3ref/result_blob.html">sqlite3_result_xxxxx()</a>
1728 interfaces. Only in that case <a href="c3ref/value_blob.html">sqlite3_value_nochange()</a> will be
1729 true within the xUpdate method. If <a href="vtab.html#xcolumn">xColumn</a> does
1730 invoke one or more <a href="c3ref/result_blob.html">sqlite3_result_xxxxx()</a>
1731 interfaces, then SQLite understands that as a change in the value
1732 of the column and the <a href="c3ref/value_blob.html">sqlite3_value_nochange()</a> call for that
1733 column within xUpdate will return false.
1734
1735 </p><p>There might be one or more <a href="c3ref/vtab_cursor.html">sqlite3_vtab_cursor</a> objects open and in use
1736 on the virtual table instance and perhaps even on the row of the virtual
1737 table when the xUpdate method is invoked. The implementation of
1738 xUpdate must be prepared for attempts to delete or modify rows of the table
1739 out from other existing cursors. If the virtual table cannot accommodate
1740 such changes, the xUpdate method must return an <a href="rescode.html">error code</a>.
1741
1742 </p><p>The xUpdate method is optional.
1743 If the xUpdate pointer in the <a href="c3ref/module.html">sqlite3_module</a> for a virtual table
1744 is a NULL pointer, then the virtual table is read-only.
1745
1746
1747 <a name="xfindfunction"></a>
1748
1749 </p><h2 id="the_xfindfunction_method"><span>2.14. </span>The xFindFunction Method</h2>
1750
1751 <div class="codeblock"><pre>int (*xFindFunction)(
1752 sqlite3_vtab *pVtab,
1753 int nArg,
1754 const char *zName,
1755 void (**pxFunc)(sqlite3_context*,int,sqlite3_value**),
1756 void **ppArg
1757 );
1758 </pre></div>
1759
1760 <p>This method is called during <a href="c3ref/prepare.html">sqlite3_prepare()</a> to give the virtual
1761 table implementation an opportunity to overload functions.
1762 This method may be set to NULL in which case no overloading occurs.
1763
1764 </p><p>When a function uses a column from a virtual table as its first
1765 argument, this method is called to see if the virtual table would
1766 like to overload the function. The first three parameters are inputs:
1767 the virtual table, the number of arguments to the function, and the
1768 name of the function. If no overloading is desired, this method
1769 returns 0. To overload the function, this method writes the new
1770 function implementation into *pxFunc and writes user data into *ppArg
1771 and returns either 1 or a number between
1772 <a href="c3ref/c_index_constraint_eq.html">SQLITE_INDEX_CONSTRAINT_FUNCTION</a> and 255.
1773
1774 </p><p>Historically, the return value from xFindFunction() was either zero
1775 or one. Zero means that the function is not overloaded and one means that
1776 it is overloaded. The ability to return values of
1777 <a href="c3ref/c_index_constraint_eq.html">SQLITE_INDEX_CONSTRAINT_FUNCTION</a> or greater was added in
1778 version 3.25.0 (2018-09-15). If xFindFunction returns
1779 <a href="c3ref/c_index_constraint_eq.html">SQLITE_INDEX_CONSTRAINT_FUNCTION</a> or greater, than means that the function
1780 takes two arguments and the function
1781 can be used as a boolean in the WHERE clause of a query and that
1782 the virtual table is able to exploit that function to speed up the query
1783 result. When xFindFunction returns <a href="c3ref/c_index_constraint_eq.html">SQLITE_INDEX_CONSTRAINT_FUNCTION</a> or
1784 larger, the value returned becomes the <a href="c3ref/index_info.html">sqlite3_index_info</a>.aConstraint.op
1785 value for one of the constraints passed into <a href="vtab.html#xbestindex">xBestIndex()</a>. The first
1786 argument to the function is the column identified by
1787 aConstraint[].iColumn field of the constraint and the second argument to the
1788 function is the value that will be passed into <a href="vtab.html#xfilter">xFilter()</a> (if the
1789 aConstraintUsage[].argvIndex value is set) or the value returned from
1790 <a href="c3ref/vtab_rhs_value.html">sqlite3_vtab_rhs_value()</a>.
1791
1792 </p><p>The <a href="geopoly.html">Geopoly module</a> is an example of a virtual table that makes use
1793 of <a href="c3ref/c_index_constraint_eq.html">SQLITE_INDEX_CONSTRAINT_FUNCTION</a> to improve performance.
1794 The xFindFunction() method for Geopoly returns
1795 SQLITE_INDEX_CONSTRAINT_FUNCTION for the <a href="geopoly.html#goverlap">geopoly_overlap()</a> SQL function
1796 and it returns
1797 SQLITE_INDEX_CONSTRAINT_FUNCTION+1 for the <a href="geopoly.html#gwithin">geopoly_within()</a> SQL function.
1798 This permits search optimizations for queries such as:
1799
1800 </p><div class="codeblock"><pre>SELECT * FROM geopolytab WHERE geopoly_overlap(_shape, $query_polygon);
1801 SELECT * FROM geopolytab WHERE geopoly_within(_shape, $query_polygon);
1802 </pre></div>
1803
1804 <p>Note that infix functions (<a href="lang_expr.html#like">LIKE</a>, <a href="lang_expr.html#glob">GLOB</a>, <a href="lang_expr.html#regexp">REGEXP</a>, and <a href="lang_expr.html#match">MATCH</a>) reverse
1805 the order of their arguments. So "like(A,B)" would normally work the same
1806 as "B like A".
1807 However, xFindFunction() always looks at the left-most argument, not
1808 the first logical argument.
1809 Hence, for the form "B like A", SQLite looks at the
1810 left operand "B" and if that operand is a virtual table column
1811 it invokes the xFindFunction() method on that virtual table.
1812 But if the form "like(A,B)" is used instead, then SQLite checks
1813 the A term to see if it is column of a virtual table and if so
1814 it invokes the xFindFunction() method for the virtual table of
1815 column A.
1816
1817 </p><p>The function pointer returned by this routine must be valid for
1818 the lifetime of the <a href="c3ref/vtab.html">sqlite3_vtab</a> object given in the first parameter.
1819
1820 <a name="xBegin"></a>
1821
1822 </p><h2 id="the_xbegin_method"><span>2.15. </span>The xBegin Method</h2>
1823
1824 <div class="codeblock"><pre>int (*xBegin)(sqlite3_vtab *pVTab);
1825 </pre></div>
1826
1827 <p>This method begins a transaction on a virtual table.
1828 This method is optional. The xBegin pointer of <a href="c3ref/module.html">sqlite3_module</a>
1829 may be NULL.
1830
1831 </p><p>This method is always followed by one call to either the
1832 <a href="vtab.html#xcommit">xCommit</a> or <a href="vtab.html#xrollback">xRollback</a> method. Virtual table transactions do
1833 not nest, so the xBegin method will not be invoked more than once
1834 on a single virtual table
1835 without an intervening call to either <a href="vtab.html#xcommit">xCommit</a> or <a href="vtab.html#xrollback">xRollback</a>.
1836 Multiple calls to other methods can and likely will occur in between
1837 the xBegin and the corresponding <a href="vtab.html#xcommit">xCommit</a> or <a href="vtab.html#xrollback">xRollback</a>.
1838
1839 <a name="xsync"></a>
1840
1841 </p><h2 id="the_xsync_method"><span>2.16. </span>The xSync Method</h2>
1842
1843 <div class="codeblock"><pre>int (*xSync)(sqlite3_vtab *pVTab);
1844 </pre></div>
1845
1846
1847 <p>This method signals the start of a two-phase commit on a virtual
1848 table.
1849 This method is optional. The xSync pointer of <a href="c3ref/module.html">sqlite3_module</a>
1850 may be NULL.
1851
1852 </p><p>This method is only invoked after a call to the <a href="vtab.html#xBegin">xBegin</a> method and
1853 prior to an <a href="vtab.html#xcommit">xCommit</a> or <a href="vtab.html#xrollback">xRollback</a>. In order to implement two-phase
1854 commit, the xSync method on all virtual tables is invoked prior to
1855 invoking the <a href="vtab.html#xcommit">xCommit</a> method on any virtual table. If any of the
1856 xSync methods fail, the entire transaction is rolled back.
1857
1858 <a name="xcommit"></a>
1859
1860 </p><h2 id="the_xcommit_method"><span>2.17. </span>The xCommit Method</h2>
1861
1862 <div class="codeblock"><pre>int (*xCommit)(sqlite3_vtab *pVTab);
1863 </pre></div>
1864
1865 <p>This method causes a virtual table transaction to commit.
1866 This method is optional. The xCommit pointer of <a href="c3ref/module.html">sqlite3_module</a>
1867 may be NULL.
1868
1869 </p><p>A call to this method always follows a prior call to <a href="vtab.html#xBegin">xBegin</a> and
1870 <a href="vtab.html#xsync">xSync</a>.
1871
1872
1873 <a name="xrollback"></a>
1874
1875 </p><h2 id="the_xrollback_method"><span>2.18. </span>The xRollback Method</h2>
1876
1877 <div class="codeblock"><pre>int (*xRollback)(sqlite3_vtab *pVTab);
1878 </pre></div>
1879
1880 <p>This method causes a virtual table transaction to rollback.
1881 This method is optional. The xRollback pointer of <a href="c3ref/module.html">sqlite3_module</a>
1882 may be NULL.
1883
1884 </p><p>A call to this method always follows a prior call to <a href="vtab.html#xBegin">xBegin</a>.
1885
1886
1887 <a name="xrename"></a>
1888
1889 </p><h2 id="the_xrename_method"><span>2.19. </span>The xRename Method</h2>
1890
1891 <div class="codeblock"><pre>int (*xRename)(sqlite3_vtab *pVtab, const char *zNew);
1892 </pre></div>
1893
1894 <p>This method notifies the virtual table implementation
1895 that the virtual table will be given a new name.
1896 If this method returns <a href="rescode.html#ok">SQLITE_OK</a> then SQLite renames the table.
1897 If this method returns an <a href="rescode.html">error code</a> then the renaming is prevented.
1898
1899 </p><p>The xRename method is optional. If omitted, then the virtual
1900 table may not be renamed using the ALTER TABLE RENAME command.
1901
1902 </p><p>The <a href="pragma.html#pragma_legacy_alter_table">PRAGMA legacy_alter_table</a> setting is enabled prior to invoking this
1903 method, and the value for legacy_alter_table is restored after this
1904 method finishes. This is necessary for the correct operation of virtual
1905 tables that make use of <a href="vtab.html#xshadowname">shadow tables</a> where the shadow tables must be
1906 renamed to match the new virtual table name. If the legacy_alter_format is
1907 off, then the xConnect method will be invoked for the virtual table every
1908 time the xRename method tries to change the name of the shadow table.
1909
1910 <a name="xsavepoint"></a>
1911
1912 </p><h2 id="the_xsavepoint_xrelease_and_xrollbackto_methods"><span>2.20. </span>The xSavepoint, xRelease, and xRollbackTo Methods</h2>
1913
1914 <div class="codeblock"><pre>int (*xSavepoint)(sqlite3_vtab *pVtab, int);
1915 int (*xRelease)(sqlite3_vtab *pVtab, int);
1916 int (*xRollbackTo)(sqlite3_vtab *pVtab, int);
1917 </pre></div>
1918
1919 <p>
1920 These methods provide the virtual table implementation an opportunity to
1921 implement nested transactions. They are always optional and will only be
1922 called in SQLite <a href="releaselog/3_7_7.html">version 3.7.7</a> (2011-06-23) and later.
1923 </p>
1924
1925 <p>
1926 When xSavepoint(X,N) is invoked, that is a signal to the virtual table X
1927 that it should save its current state as savepoint N.
1928 A subsequent call
1929 to xRollbackTo(X,R) means that the state of the virtual table should return
1930 to what it was when xSavepoint(X,R) was last called.
1931 The call
1932 to xRollbackTo(X,R) will invalidate all savepoints with N>R; none of the
1933 invalided savepoints will be rolled back or released without first
1934 being reinitialized by a call to xSavepoint().
1935 A call to xRelease(X,M) invalidates all savepoints where N>=M.
1936 </p>
1937
1938 <p>
1939 None of the xSavepoint(), xRelease(), or xRollbackTo() methods will ever
1940 be called except in between calls to xBegin() and
1941 either xCommit() or xRollback().
1942 </p>
1943
1944 <a name="xshadowname"></a>
1945
1946 <h2 id="the_xshadowname_method"><span>2.21. </span>The xShadowName Method</h2>
1947
1948 <p>Some virtual table implementations (ex: <a href="fts3.html">FTS3</a>, <a href="fts5.html">FTS5</a>, and <a href="rtree.html">RTREE</a>) make
1949 use of real (non-virtual) database tables to store content. For example,
1950 when content is inserted into the FTS3 virtual table, the data is ultimately
1951 stored in real tables named "%_content", "%_segdir", "%_segments", "%_stat",
1952 and "%_docsize" where "%" is the name of the original virtual table. These
1953 auxiliary real tables that store content for a virtual table are called
1954 "shadow tables". See
1955 (<a href="fts3.html#*shadowtab">1</a>),
1956 (<a href="fts5.html#fts5shadowtables">2</a>), and
1957 (<a href="rtree.html#xshadow">3</a>) for additional information.
1958
1959 </p><p>The xShadowName method exists to allow SQLite to determine whether a
1960 certain real table is in fact a shadow table for a virtual table.
1961
1962 </p><p>SQLite understands a real table to be a shadow table if all of
1963 the following are true:
1964 </p><p>
1965 </p><ul>
1966 <li> The name of the table contains one or more "_" characters.
1967 </li><li> The part of the name prior to the last "_" exactly matches
1968 the name of a virtual table that was created using <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a>.
1969 (Shadow tables are not recognized for <a href="vtab.html#epovtab">eponymous virtual tables</a>
1970 and <a href="vtab.html#tabfunc2">table-valued functions</a>.)
1971 </li><li> The virtual table contains an xShadowName method.
1972 </li><li> The xShadowName method returns true when its input is the part
1973 of the table name past the last "_" character.
1974 </li></ul>
1975
1976 <p>
1977 If SQLite recognizes a table as a shadow table, and if the
1978 <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigdefensive">SQLITE_DBCONFIG_DEFENSIVE</a> flag is set, then the shadow table is read-only
1979 for ordinary SQL statements. The shadow table can still be written, but
1980 only by SQL that is invoked from within one of the methods of
1981 some virtual table implementation.
1982
1983 </p><p>
1984 The whole point of the xShadowName method is to protect the content of
1985 shadow tables from being corrupted by hostile SQL. Every virtual table
1986 implementation that uses shadow tables should be able to detect and cope
1987 with corrupted shadow table content. However, bugs in a particular virtual
1988 table implementation might allow a deliberately corrupted shadow table to
1989 cause a crash or other malfunction. The xShadowName mechanism seeks to
1990 avoid zero-day exploits by preventing ordinary SQL statements from
1991 deliberately corrupting shadow tables.
1992
1993 </p><p>
1994 Shadow tables are read/write by default.
1995 Shadow tables only become read-only when the <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigdefensive">SQLITE_DBCONFIG_DEFENSIVE</a>
1996 flag is set using <a href="c3ref/db_config.html">sqlite3_db_config()</a>.
1997 Shadow tables need to be read/write by default in order to maintain
1998 backwards compatibility.
1999 For example, the SQL text generated by the <a href="cli.html#dump">.dump</a> command of the <a href="cli.html">CLI</a>
2000 writes directly into shadow tables.
2001
2002 <a name="xintegrity"></a>
2003
2004 </p><h2 id="the_xintegrity_method"><span>2.22. </span>The xIntegrity Method</h2>
2005
2006 <p>
2007 If the iVersion for an sqlite3_module is 4 or more and the xIntegrity
2008 method is not NULL, then the <a href="pragma.html#pragma_integrity_check">PRAGMA integrity_check</a> and
2009 <a href="pragma.html#pragma_quick_check">PRAGMA quick_check</a> commands will invoke
2010 xIntegrity as part of its processing. If the xIntegrity method writes
2011 an error message string into the fifth parameter, then PRAGMA integrity_check
2012 will report that error as part of its output. So, in other words, the
2013 xIntegrity method allows the <a href="pragma.html#pragma_integrity_check">PRAGMA integrity_check</a> command to verify
2014 the integrity of content stored in a virtual table.
2015
2016 </p><p>
2017 The xIntegrity method is called with five parameters:
2018
2019 </p><ul>
2020 <li><b>pVTab</b> &rarr; A pointer to the <a href="c3ref/vtab.html">sqlite3_vtab</a> object that is the
2021 virtual table being checked.
2022 </li><li><b>zSchema</b> &rarr; The name of the schema ("main", "temp", etc.) in which
2023 the virtual table is defined.
2024 </li><li><b>zTabName</b> &rarr; The name of the virtual table.
2025 </li><li><b>mFlags</b> &rarr; A flag to indicate whether this is an "integrity_check"
2026 or a "quick_check". Currently, this parameter will always be either 0 or 1,
2027 though future versions of SQLite might use other bits of the integer to
2028 indicate additional processing options.
2029 </li><li><b>pzErr</b> &rarr; This parameter points to a "char*" that is initialized
2030 to NULL. The xIntegrity() implementation should make *pzErr point to
2031 an error string obtained from sqlite3_malloc() or equivalent if it finds
2032 any problems.
2033 </li></ul>
2034
2035 <p>
2036 The xIntegrity method should normally return SQLITE_OK - even if it finds
2037 problems in the content of the virtual table. Any other error code
2038 means that the xIntegrity method itself encountered problems while trying
2039 to evaluate the virtual table content. So, for example, if the inverted
2040 index for <a href="fts5.html">FTS5</a> is found to be internally inconsistent, then the xIntegrity
2041 method should write an appropriate error message into the pzErr parameter
2042 and return SQLITE_OK. But if the xIntegrity method is unable to complete its
2043 evaluation of the virtual table content due to running out of memory, then
2044 it should return SQLITE_NOMEM.
2045
2046 </p><p>
2047 If an error message is generated,
2048 space to hold the error message string should be obtained from <a href="c3ref/free.html">sqlite3_malloc64()</a>
2049 or the equivalent. Ownership of the error message string will pass to the
2050 SQLite core when xIntegrity returns. The core will make sure that
2051 <a href="c3ref/free.html">sqlite3_free()</a> is invoked to reclaim the memory when it has finished
2052 with the error message. The PRAGMA integrity_check command that invokes the
2053 xIntegrity method does not change the returned error message. The xIntegrity
2054 method itself should include the name of the virtual table as part of the
2055 message. The zSchema and zName parameters are provided to make that easier.
2056
2057 </p><p>
2058 The mFlags parameter is currently a boolean value (either 0 or 1) that indicates
2059 if the xIntegrity method was called due to <a href="pragma.html#pragma_integrity_check">PRAGMA integrity_check</a> (mFlags==0)
2060 or due to <a href="pragma.html#pragma_quick_check">PRAGMA quick_check</a> (mFlags==1). Generally speaking, the xIntegrity
2061 method should do whatever validity checking it can accomplish in linear time
2062 regardless, but only do checking that requires superlinear time if
2063 <tt>(mFlags&1)==0</tt>. Future versions of SQLite might
2064 use higher-order bits of the mFlags parameter to indicate additional
2065 processing options.
2066
2067 </p><p>
2068 Support for the
2069 xIntegrity method was added in SQLite version 3.44.0 (2023-11-01).
2070 In that same release, the xIntegrity method was added to many built-in
2071 virtual tables, such as <a href="fts3.html">FTS3</a>, <a href="fts5.html">FTS5</a>, and <a href="rtree.html">RTREE</a> so that the content
2072 of those tables will henceforth be automatically checked for consistency
2073 when <a href="pragma.html#pragma_integrity_check">PRAGMA integrity_check</a> is run.
2074 </p><p align="center"><small><i>This page was last updated on 2025-12-22 18:00:39Z </small></i></p>
2075