| 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 |
→ 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 |
→ 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> → |
| 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> → |
| 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 "<", 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>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 >= 10 |
| 1090 |
x <= 100 |
| 1091 |
y < 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->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>→</big> return SQLITE_CONSTRAINT. |
| 1361 |
</p></li><li><p> |
| 1362 |
A required parameter does not appear anywhere in |
| 1363 |
the aConstraint[] array <big>→</big> |
| 1364 |
Set an error message in pVTab->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[i]!=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[i])==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[i]</b> mean the SQL value |
| 1667 |
held within the argv[i] object, not the argv[i] |
| 1668 |
object itself. |
| 1669 |
|
| 1670 |
</p><blockquote> |
| 1671 |
<dl> |
| 1672 |
<dt><b>argc = 1 <br> argv[0] ≠ 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 > 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 > 1 <br> argv[0] ≠ 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 > 1 <br> argv[0] ≠ NULL <br> argv[0] ≠ 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> → 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> → The name of the schema ("main", "temp", etc.) in which |
| 2023 |
the virtual table is defined. |
| 2024 |
</li><li><b>zTabName</b> → The name of the virtual table. |
| 2025 |
</li><li><b>mFlags</b> → 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> → 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 |
|