diff options
Diffstat (limited to 'www/vtab.html')
| -rw-r--r-- | www/vtab.html | 1969 |
1 files changed, 1969 insertions, 0 deletions
diff --git a/www/vtab.html b/www/vtab.html new file mode 100644 index 0000000..ecace6a --- /dev/null +++ b/www/vtab.html @@ -0,0 +1,1969 @@ +<!DOCTYPE html> +<html><head> +<meta name="viewport" content="width=device-width, initial-scale=1.0"> +<meta http-equiv="content-type" content="text/html; charset=UTF-8"> +<link href="sqlite.css" rel="stylesheet"> +<title>The Virtual Table Mechanism Of SQLite</title> +<!-- path= --> +</head> +<body> +<div class=nosearch> +<a href="index.html"> +<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0"> +</a> +<div><!-- IE hack to prevent disappearing logo --></div> +<div class="tagline desktoponly"> +Small. Fast. Reliable.<br>Choose any three. +</div> +<div class="menu mainmenu"> +<ul> +<li><a href="index.html">Home</a> +<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a> +<li class='wideonly'><a href='about.html'>About</a> +<li class='desktoponly'><a href="docs.html">Documentation</a> +<li class='desktoponly'><a href="download.html">Download</a> +<li class='wideonly'><a href='copyright.html'>License</a> +<li class='desktoponly'><a href="support.html">Support</a> +<li class='desktoponly'><a href="prosupport.html">Purchase</a> +<li class='search' id='search_menubutton'> +<a href="javascript:void(0)" onclick='toggle_search()'>Search</a> +</ul> +</div> +<div class="menu submenu" id="submenu"> +<ul> +<li><a href='about.html'>About</a> +<li><a href='docs.html'>Documentation</a> +<li><a href='download.html'>Download</a> +<li><a href='support.html'>Support</a> +<li><a href='prosupport.html'>Purchase</a> +</ul> +</div> +<div class="searchmenu" id="searchmenu"> +<form method="GET" action="search"> +<select name="s" id="searchtype"> +<option value="d">Search Documentation</option> +<option value="c">Search Changelog</option> +</select> +<input type="text" name="q" id="searchbox" value=""> +<input type="submit" value="Go"> +</form> +</div> +</div> +<script> +function toggle_div(nm) { +var w = document.getElementById(nm); +if( w.style.display=="block" ){ +w.style.display = "none"; +}else{ +w.style.display = "block"; +} +} +function toggle_search() { +var w = document.getElementById("searchmenu"); +if( w.style.display=="block" ){ +w.style.display = "none"; +} else { +w.style.display = "block"; +setTimeout(function(){ +document.getElementById("searchbox").focus() +}, 30); +} +} +function div_off(nm){document.getElementById(nm).style.display="none";} +window.onbeforeunload = function(e){div_off("submenu");} +/* Disable the Search feature if we are not operating from CGI, since */ +/* Search is accomplished using CGI and will not work without it. */ +if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){ +document.getElementById("search_menubutton").style.display = "none"; +} +/* Used by the Hide/Show button beside syntax diagrams, to toggle the */ +function hideorshow(btn,obj){ +var x = document.getElementById(obj); +var b = document.getElementById(btn); +if( x.style.display!='none' ){ +x.style.display = 'none'; +b.innerHTML='show'; +}else{ +x.style.display = ''; +b.innerHTML='hide'; +} +return false; +} +var antiRobot = 0; +function antiRobotGo(){ +if( antiRobot!=3 ) return; +antiRobot = 7; +var j = document.getElementById("mtimelink"); +if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href"); +} +function antiRobotDefense(){ +document.body.onmousedown=function(){ +antiRobot |= 2; +antiRobotGo(); +document.body.onmousedown=null; +} +document.body.onmousemove=function(){ +antiRobot |= 2; +antiRobotGo(); +document.body.onmousemove=null; +} +setTimeout(function(){ +antiRobot |= 1; +antiRobotGo(); +}, 100) +antiRobotGo(); +} +antiRobotDefense(); +</script> +<div class=fancy> +<div class=nosearch> +<div class="fancy_title"> +The Virtual Table Mechanism Of SQLite +</div> +<div class="fancy_toc"> +<a onclick="toggle_toc()"> +<span class="fancy_toc_mark" id="toc_mk">►</span> +Table Of Contents +</a> +<div id="toc_sub"><div class="fancy-toc1"><a href="#introduction">1. Introduction</a></div> +<div class="fancy-toc2"><a href="#usage">1.1. Usage</a></div> +<div class="fancy-toc3"><a href="#temporary_virtual_tables">1.1.1. Temporary virtual tables</a></div> +<div class="fancy-toc3"><a href="#eponymous_virtual_tables">1.1.2. Eponymous virtual tables</a></div> +<div class="fancy-toc3"><a href="#eponymous_only_virtual_tables">1.1.3. Eponymous-only virtual tables</a></div> +<div class="fancy-toc2"><a href="#implementation">1.2. Implementation</a></div> +<div class="fancy-toc2"><a href="#virtual_tables_and_shared_cache">1.3. Virtual Tables And Shared Cache</a></div> +<div class="fancy-toc2"><a href="#creating_new_virtual_table_implementations">1.4. Creating New Virtual Table Implementations</a></div> +<div class="fancy-toc1"><a href="#virtual_table_methods">2. Virtual Table Methods</a></div> +<div class="fancy-toc2"><a href="#the_xcreate_method">2.1. The xCreate Method</a></div> +<div class="fancy-toc3"><a href="#hidden_columns_in_virtual_tables">2.1.1. Hidden columns in virtual tables</a></div> +<div class="fancy-toc3"><a href="#table_valued_functions">2.1.2. Table-valued functions</a></div> +<div class="fancy-toc3"><a href="#_without_rowid_virtual_tables_">2.1.3. WITHOUT ROWID Virtual Tables </a></div> +<div class="fancy-toc2"><a href="#the_xconnect_method">2.2. The xConnect Method</a></div> +<div class="fancy-toc2"><a href="#the_xbestindex_method">2.3. The xBestIndex Method</a></div> +<div class="fancy-toc3"><a href="#inputs">2.3.1. Inputs</a></div> +<div class="fancy-toc4"><a href="#like_glob_regexp_and_match_functions">2.3.1.1. LIKE, GLOB, REGEXP, and MATCH functions</a></div> +<div class="fancy-toc4"><a href="#limit_and_offset">2.3.1.2. LIMIT and OFFSET</a></div> +<div class="fancy-toc4"><a href="#right_hand_side_values_of_constraints">2.3.1.3. Right-hand side values of constraints</a></div> +<div class="fancy-toc3"><a href="#outputs">2.3.2. Outputs</a></div> +<div class="fancy-toc4"><a href="#omit_constraint_checking_in_bytecode">2.3.2.1. Omit constraint checking in bytecode</a></div> +<div class="fancy-toc4"><a href="#order_by_and_orderbyconsumed">2.3.2.2. ORDER BY and orderByConsumed</a></div> +<div class="fancy-toc3"><a href="#return_value">2.3.3. Return Value</a></div> +<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> +<div class="fancy-toc2"><a href="#the_xdisconnect_method">2.4. The xDisconnect Method</a></div> +<div class="fancy-toc2"><a href="#the_xdestroy_method">2.5. The xDestroy Method</a></div> +<div class="fancy-toc2"><a href="#the_xopen_method">2.6. The xOpen Method</a></div> +<div class="fancy-toc2"><a href="#the_xclose_method">2.7. The xClose Method</a></div> +<div class="fancy-toc2"><a href="#the_xeof_method">2.8. The xEof Method</a></div> +<div class="fancy-toc2"><a href="#the_xfilter_method">2.9. The xFilter Method</a></div> +<div class="fancy-toc2"><a href="#the_xnext_method">2.10. The xNext Method</a></div> +<div class="fancy-toc2"><a href="#the_xcolumn_method">2.11. The xColumn Method</a></div> +<div class="fancy-toc2"><a href="#the_xrowid_method">2.12. The xRowid Method</a></div> +<div class="fancy-toc2"><a href="#the_xupdate_method">2.13. The xUpdate Method</a></div> +<div class="fancy-toc2"><a href="#the_xfindfunction_method">2.14. The xFindFunction Method</a></div> +<div class="fancy-toc2"><a href="#the_xbegin_method">2.15. The xBegin Method</a></div> +<div class="fancy-toc2"><a href="#the_xsync_method">2.16. The xSync Method</a></div> +<div class="fancy-toc2"><a href="#the_xcommit_method">2.17. The xCommit Method</a></div> +<div class="fancy-toc2"><a href="#the_xrollback_method">2.18. The xRollback Method</a></div> +<div class="fancy-toc2"><a href="#the_xrename_method">2.19. The xRename Method</a></div> +<div class="fancy-toc2"><a href="#the_xsavepoint_xrelease_and_xrollbackto_methods">2.20. The xSavepoint, xRelease, and xRollbackTo Methods</a></div> +<div class="fancy-toc2"><a href="#the_xshadowname_method">2.21. The xShadowName Method</a></div> +</div> +</div> +<script> +function toggle_toc(){ +var sub = document.getElementById("toc_sub") +var mk = document.getElementById("toc_mk") +if( sub.style.display!="block" ){ +sub.style.display = "block"; +mk.innerHTML = "▼"; +} else { +sub.style.display = "none"; +mk.innerHTML = "►"; +} +} +</script> +</div> + + + + + + +<h1 id="introduction"><span>1. </span>Introduction</h1> + +<p>A virtual table is an object that is registered with an open SQLite +<a href="c3ref/sqlite3.html">database connection</a>. From the perspective of an SQL statement, +the virtual table object looks like any other table or view. +But behind the scenes, queries and updates on a virtual table +invoke callback methods of the virtual table object instead of +reading and writing on the database file. + +</p><p>The virtual table mechanism allows an application to publish +interfaces that are accessible from SQL statements as if they were +tables. SQL statements can do almost anything to a +virtual table that they can do to a real table, with the following +exceptions: + +</p><p> +</p><ul> +<li> One cannot create a trigger on a virtual table. +</li><li> One cannot create additional indices on a virtual table. + (Virtual tables can have indices but that must be built into + the virtual table implementation. Indices cannot be added + separately using <a href="lang_createindex.html">CREATE INDEX</a> statements.) +</li><li> One cannot run <a href="lang_altertable.html">ALTER TABLE ... ADD COLUMN</a> + commands against a virtual table. +</li></ul> + +<p>Individual virtual table implementations might impose additional +constraints. For example, some virtual implementations might provide +read-only tables. Or some virtual table implementations might allow +<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 +might limit the kinds of UPDATEs that can be made. + +</p><p>A virtual table might represent an in-memory data structures. +Or it might represent a view of data on disk that is not in the +SQLite format. Or the application might compute the content of the +virtual table on demand. + +</p><p>Here are some existing and postulated uses for virtual tables: + +</p><ul> +<li> A <a href="fts3.html">full-text search</a> interface +</li><li> Spatial indices using <a href="rtree.html">R-Trees</a> +</li><li> Introspect the disk content of an SQLite database file + (the <a href="dbstat.html">dbstat virtual table</a>) +</li><li> Read and/or write the content of a comma-separated value (CSV) + file +</li><li> Access the filesystem of the host computer as if it were a database table +</li><li> Enabling SQL manipulation of data in statistics packages like R +</li></ul> + +<p>See the <a href="vtablist.html">list of virtual tables</a> page for a longer list of actual +virtual table implementations. + + +</p><h2 id="usage"><span>1.1. </span>Usage</h2> + +<p>A virtual table is created using a <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement. + +<p><b><a href="syntax/create-virtual-table-stmt.html">create-virtual-table-stmt:</a></b> +<button id='x2353' onclick='hideorshow("x2353","x2354")'>hide</button></p> + <div id='x2354' class='imgcontainer'> + <div style="max-width:624px"><svg xmlns='http://www.w3.org/2000/svg' class="pikchr" viewBox="0 0 624.096 259.848"> +<circle cx="5" cy="17" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<polygon points="32,17 20,21 20,12" style="fill:rgb(0,0,0)"/> +<path d="M9,17L26,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<path d="M47,32L100,32A15 15 0 0 0 116 17A15 15 0 0 0 100 2L47,2A15 15 0 0 0 32 17A15 15 0 0 0 47 32Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="74" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">CREATE</text> +<polygon points="139,17 127,21 127,12" style="fill:rgb(0,0,0)"/> +<path d="M116,17L133,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<path d="M154,32L213,32A15 15 0 0 0 228 17A15 15 0 0 0 213 2L154,2A15 15 0 0 0 139 17A15 15 0 0 0 154 32Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="183" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">VIRTUAL</text> +<polygon points="251,17 240,21 240,12" style="fill:rgb(0,0,0)"/> +<path d="M228,17L246,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<path d="M266,32L306,32A15 15 0 0 0 321 17A15 15 0 0 0 306 2L266,2A15 15 0 0 0 251 17A15 15 0 0 0 266 32Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="286" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">TABLE</text> +<polygon points="357,17 345,21 345,12" style="fill:rgb(0,0,0)"/> +<path d="M321,17L351,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<path d="M372,32A15 15 0 0 0 387 17A15 15 0 0 0 372 2A15 15 0 0 0 357 17A15 15 0 0 0 372 32Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="372" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">IF</text> +<polygon points="410,17 399,21 399,12" style="fill:rgb(0,0,0)"/> +<path d="M387,17L404,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<path d="M425,32L445,32A15 15 0 0 0 461 17A15 15 0 0 0 445 2L425,2A15 15 0 0 0 410 17A15 15 0 0 0 425 32Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="435" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">NOT</text> +<polygon points="484,17 472,21 472,12" style="fill:rgb(0,0,0)"/> +<path d="M461,17L478,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<path d="M499,32L543,32A15 15 0 0 0 558 17A15 15 0 0 0 543 2L499,2A15 15 0 0 0 484 17A15 15 0 0 0 499 32Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="521" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">EXISTS</text> +<path d="M47,108L143,108A15 15 0 0 0 159 92A15 15 0 0 0 143 77L47,77A15 15 0 0 0 32 92A15 15 0 0 0 47 108Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="95" y="92" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">schema-name</text> +<polygon points="182,92 170,97 170,88" style="fill:rgb(0,0,0)"/> +<path d="M159,92L176,92" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<path d="M197,108A15 15 0 0 0 212 92A15 15 0 0 0 197 77A15 15 0 0 0 182 92A15 15 0 0 0 197 108Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="197" y="92" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">.</text> +<polygon points="248,92 236,97 236,88" style="fill:rgb(0,0,0)"/> +<path d="M212,92L242,92" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<path d="M263,108L338,108A15 15 0 0 0 353 92A15 15 0 0 0 338 77L263,77A15 15 0 0 0 248 92A15 15 0 0 0 263 108Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="300" y="92" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">table-name</text> +<path d="M47,219L86,219A15 15 0 0 0 101 204A15 15 0 0 0 86 189L47,189A15 15 0 0 0 32 204A15 15 0 0 0 47 219Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="67" y="204" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">USING</text> +<polygon points="124,204 113,209 113,200" style="fill:rgb(0,0,0)"/> +<path d="M101,204L118,204" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<path d="M139,219L234,219A15 15 0 0 0 249 204A15 15 0 0 0 234 189L139,189A15 15 0 0 0 124 204A15 15 0 0 0 139 219Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="187" y="204" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">module-name</text> +<polygon points="285,204 274,209 274,200" style="fill:rgb(0,0,0)"/> +<path d="M249,204L279,204" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<path d="M300,219A15 15 0 0 0 315 204A15 15 0 0 0 300 189A15 15 0 0 0 285 204A15 15 0 0 0 300 219Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="300" y="204" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">(</text> +<polygon points="351,204 340,209 340,200" style="fill:rgb(0,0,0)"/> +<path d="M315,204L346,204" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<path d="M366,219L497,219A15 15 0 0 0 512 204A15 15 0 0 0 497 189L366,189A15 15 0 0 0 351 204A15 15 0 0 0 366 219Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="432" y="204" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">module-argument</text> +<polygon points="548,204 536,209 536,200" style="fill:rgb(0,0,0)"/> +<path d="M512,204L542,204" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<path d="M563,219A15 15 0 0 0 578 204A15 15 0 0 0 563 189A15 15 0 0 0 548 204A15 15 0 0 0 563 219Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="563" y="204" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">)</text> +<polygon points="614,204 603,209 603,200" style="fill:rgb(0,0,0)"/> +<path d="M578,204L608,204" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<circle cx="618" cy="204" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<path d="M432,257A15 15 0 0 0 447 242A15 15 0 0 0 432 227A15 15 0 0 0 417 242A15 15 0 0 0 432 257Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<text x="432" y="242" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">,</text> +<polygon points="447,242 458,238 458,246" style="fill:rgb(0,0,0)"/> +<path d="M512,204 L 519,204 Q 527,204 527,219 L 527,227 Q 527,242 512,242 L 468,242 L 453,242" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<path d="M417,242 L 343,242 Q 328,242 328,227 L 328,219 Q 328,204 336,204 L 343,204" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<polygon points="432,180 420,184 420,176" style="fill:rgb(0,0,0)"/> +<path d="M249,204 L 257,204 Q 264,204 264,192 Q 264,180 279,180 L 411,180 L 426,180" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<path d="M432,180 L 576,180 Q 591,180 591,192 Q 591,204 599,204 L 606,204" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<polygon points="187,153 198,149 198,157" style="fill:rgb(0,0,0)"/> +<path d="M353,92 L 361,92 Q 368,92 368,107 L 368,138 Q 368,153 353,153 L 207,153 L 192,153" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<polygon points="32,204 20,209 20,200" style="fill:rgb(0,0,0)"/> +<path d="M187,153 L 20,153 Q 5,153 5,168 L 5,189 Q 5,204 16,204 L 26,204" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<polygon points="429,62 441,58 441,66" style="fill:rgb(0,0,0)"/> +<path d="M558,17 L 566,17 Q 573,17 573,32 L 573,47 Q 573,62 558,62 L 450,62 L 435,62" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<polygon points="32,92 20,97 20,88" style="fill:rgb(0,0,0)"/> +<path d="M429,62 L 20,62 Q 5,62 5,77 L 5,77 Q 5,92 16,92 L 26,92" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<polygon points="336,47 332,35 340,35" style="fill:rgb(0,0,0)"/> +<path d="M321,17 L 328,17 Q 336,17 336,29 L 336,41" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<path d="M336,47 L 336,54 Q 336,62 328,62 L 321,62" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<polygon points="95,123 84,127 84,118" style="fill:rgb(0,0,0)"/> +<path d="M5,77 L 5,108 Q 5,123 20,123 L 74,123 L 89,123" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +<path d="M95,123 L 210,123 Q 225,123 225,108 L 225,107 Q 225,92 231,92 L 236,92" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" /> +</svg> +</div> +</div> + + +</p><p>The CREATE VIRTUAL TABLE statement creates a new table +called <span class='yyterm'>table-name</span> derived from the class +<span class='yyterm'>module-name</span>. The <span class='yyterm'>module-name</span> +is the name that is registered for the virtual table by +the <a href="c3ref/create_module.html">sqlite3_create_module()</a> interface. + +</p><div class="codeblock"><pre>CREATE VIRTUAL TABLE tablename USING modulename; +</pre></div> + +<p>One can also provide comma-separated arguments to the module following +the module name: + +</p><div class="codeblock"><pre>CREATE VIRTUAL TABLE tablename USING modulename(arg1, arg2, ...); +</pre></div> + +<p>The format of the arguments to the module is very general. Each +<span class='yyterm'>module-argument</span> +may contain keywords, string literals, identifiers, numbers, and +punctuation. Each <span class='yyterm'>module-argument</span> is passed as +written (as text) into the +<a href="vtab.html#xcreate">constructor method</a> of the virtual table implementation +when the virtual +table is created and that constructor is responsible for parsing and +interpreting the arguments. The argument syntax is sufficiently general +that a virtual table implementation can, if it wants to, interpret its +arguments as <a href="lang_createtable.html#tablecoldef">column definitions</a> in an ordinary <a href="lang_createtable.html">CREATE TABLE</a> statement. +The implementation could also impose some other interpretation on the +arguments. + +</p><p>Once a virtual table has been created, it can be used like any other +table with the exceptions noted above and imposed by specific virtual +table implementations. A virtual table is destroyed using the ordinary +<a href="lang_droptable.html">DROP TABLE</a> syntax. + +</p><h3 id="temporary_virtual_tables"><span>1.1.1. </span>Temporary virtual tables</h3> + +<p>There is no "CREATE TEMP VIRTUAL TABLE" statement. To create a +temporary virtual table, add the "temp" schema +before the virtual table name. + +</p><div class="codeblock"><pre>CREATE VIRTUAL TABLE <b>temp.</b>tablename USING module(arg1, ...); +</pre></div> + +<a name="epovtab"></a> + +<h3 id="eponymous_virtual_tables"><span>1.1.2. </span>Eponymous virtual tables</h3> + +<p>Some virtual tables exist automatically in the "main" schema of +every database connection in which their +module is registered, even without a <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement. +Such virtual tables are called "eponymous virtual tables". +To use an eponymous virtual table, simply use the +module name as if it were a table. +Eponymous virtual tables exist in the "main" schema only, so they will +not work if prefixed with a different schema name. + +</p><p>An example of an eponymous virtual table is the <a href="dbstat.html">dbstat virtual table</a>. +To use the dbstat virtual table as an eponymous virtual table, +simply query against the "dbstat" +module name, as if it were an ordinary table. (Note that SQLite +must be compiled with the <a href="compile.html#enable_dbstat_vtab">SQLITE_ENABLE_DBSTAT_VTAB</a> option to include +the dbstat virtual table in the build.) + +</p><div class="codeblock"><pre>SELECT * FROM dbstat; +</pre></div> + +<p>A virtual table is eponymous if its <a href="vtab.html#xcreate">xCreate</a> method is the exact same +function as the <a href="vtab.html#xconnect">xConnect</a> method, or if the <a href="vtab.html#xcreate">xCreate</a> method is NULL. +The <a href="vtab.html#xcreate">xCreate</a> method is called when a virtual table is first created +using the <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement. The <a href="vtab.html#xconnect">xConnect</a> method +is invoked whenever +a database connection attaches to or reparses a schema. When these two methods +are the same, that indicates that the virtual table has no persistent +state that needs to be created and destroyed. + +<a name="epoonlyvtab"></a> + +</p><h3 id="eponymous_only_virtual_tables"><span>1.1.3. </span>Eponymous-only virtual tables</h3> +<p>If the <a href="vtab.html#xcreate">xCreate</a> method is NULL, then +<a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statements are prohibited for that virtual table, +and the virtual table is an "eponymous-only virtual table". +Eponymous-only virtual tables are useful as +<a href="vtab.html#tabfunc2">table-valued functions</a>. + +</p><p> +Note that prior to <a href="releaselog/3_9_0.html">version 3.9.0</a> (2015-10-14), +SQLite did not check the xCreate method +for NULL before invoking it. So if an eponymous-only virtual table is +registered with SQLite <a href="releaselog/3_8_11_1.html">version 3.8.11.1</a> (2015-07-29) +or earlier and a <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> +command is attempted against that virtual table module, a jump to a NULL +pointer will occur, resulting in a crash. + +</p><h2 id="implementation"><span>1.2. </span>Implementation</h2> + +<p>Several new C-level objects are used by the virtual table implementation: + +</p><div class="codeblock"><pre>typedef struct sqlite3_vtab sqlite3_vtab; +typedef struct sqlite3_index_info sqlite3_index_info; +typedef struct sqlite3_vtab_cursor sqlite3_vtab_cursor; +typedef struct sqlite3_module sqlite3_module; +</pre></div> + +<p>The <a href="c3ref/module.html">sqlite3_module</a> structure defines a module object used to implement +a virtual table. Think of a module as a class from which one can +construct multiple virtual tables having similar properties. For example, +one might have a module that provides read-only access to +comma-separated-value (CSV) files on disk. That one module can then be +used to create several virtual tables where each virtual table refers +to a different CSV file. + +</p><p>The module structure contains methods that are invoked by SQLite to +perform various actions on the virtual table such as creating new +instances of a virtual table or destroying old ones, reading and +writing data, searching for and deleting, updating, or inserting rows. +The module structure is explained in more detail below. + +</p><p>Each virtual table instance is represented by an <a href="c3ref/vtab.html">sqlite3_vtab</a> structure. +The sqlite3_vtab structure looks like this: + +</p><div class="codeblock"><pre>struct sqlite3_vtab { + const sqlite3_module *pModule; + int nRef; + char *zErrMsg; +}; +</pre></div> + +<p>Virtual table implementations will normally subclass this structure +to add additional private and implementation-specific fields. +The nRef field is used internally by the SQLite core and should not +be altered by the virtual table implementation. The virtual table +implementation may pass error message text to the core by putting +an error message string in zErrMsg. +Space to hold this error message string must be obtained from an +SQLite memory allocation function such as <a href="c3ref/mprintf.html">sqlite3_mprintf()</a> or +<a href="c3ref/free.html">sqlite3_malloc()</a>. +Prior to assigning a new value to zErrMsg, the virtual table +implementation must free any preexisting content of zErrMsg using +<a href="c3ref/free.html">sqlite3_free()</a>. Failure to do this will result in a memory leak. +The SQLite core will free and zero the content of zErrMsg when it +delivers the error message text to the client application or when +it destroys the virtual table. The virtual table implementation only +needs to worry about freeing the zErrMsg content when it overwrites +the content with a new, different error message. + +</p><p>The <a href="c3ref/vtab_cursor.html">sqlite3_vtab_cursor</a> structure represents a pointer to a specific +row of a virtual table. This is what an sqlite3_vtab_cursor looks like: + +</p><div class="codeblock"><pre>struct sqlite3_vtab_cursor { + sqlite3_vtab *pVtab; +}; +</pre></div> + +<p>Once again, practical implementations will likely subclass this +structure to add additional private fields. + +</p><p>The <a href="c3ref/index_info.html">sqlite3_index_info</a> structure is used to pass information into +and out of the xBestIndex method of the module that implements a +virtual table. + +</p><p>Before a <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement can be run, the module +specified in that statement must be registered with the database +connection. This is accomplished using either of the <a href="c3ref/create_module.html">sqlite3_create_module()</a> +or <a href="c3ref/create_module.html">sqlite3_create_module_v2()</a> interfaces: + +</p><div class="codeblock"><pre>int sqlite3_create_module( + sqlite3 *db, /* SQLite connection to register module with */ + const char *zName, /* Name of the module */ + const sqlite3_module *, /* Methods for the module */ + void * /* Client data for xCreate/xConnect */ +); +int sqlite3_create_module_v2( + sqlite3 *db, /* SQLite connection to register module with */ + const char *zName, /* Name of the module */ + const sqlite3_module *, /* Methods for the module */ + void *, /* Client data for xCreate/xConnect */ + void(*xDestroy)(void*) /* Client data destructor function */ +); +</pre></div> + +<p>The <a href="c3ref/create_module.html">sqlite3_create_module()</a> and <a href="c3ref/create_module.html">sqlite3_create_module_v2()</a> +routines associates a module name with +an <a href="c3ref/module.html">sqlite3_module</a> structure and a separate client data that is specific +to each module. The only difference between the two create_module methods +is that the _v2 method includes an extra parameter that specifies a +destructor for client data pointer. The module structure is what defines +the behavior of a virtual table. The module structure looks like this: + +</p><div class="codeblock"><pre> +struct sqlite3_module { + int iVersion; + int (*xCreate)(sqlite3*, void *pAux, + int argc, char *const*argv, + sqlite3_vtab **ppVTab, + char **pzErr); + int (*xConnect)(sqlite3*, void *pAux, + int argc, char *const*argv, + sqlite3_vtab **ppVTab, + char **pzErr); + int (*xBestIndex)(sqlite3_vtab *pVTab, sqlite3_index_info*); + int (*xDisconnect)(sqlite3_vtab *pVTab); + int (*xDestroy)(sqlite3_vtab *pVTab); + int (*xOpen)(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor); + int (*xClose)(sqlite3_vtab_cursor*); + int (*xFilter)(sqlite3_vtab_cursor*, int idxNum, const char *idxStr, + int argc, sqlite3_value **argv); + int (*xNext)(sqlite3_vtab_cursor*); + int (*xEof)(sqlite3_vtab_cursor*); + int (*xColumn)(sqlite3_vtab_cursor*, sqlite3_context*, int); + int (*xRowid)(sqlite3_vtab_cursor*, sqlite_int64 *pRowid); + int (*xUpdate)(sqlite3_vtab *, int, sqlite3_value **, sqlite_int64 *); + int (*xBegin)(sqlite3_vtab *pVTab); + int (*xSync)(sqlite3_vtab *pVTab); + int (*xCommit)(sqlite3_vtab *pVTab); + int (*xRollback)(sqlite3_vtab *pVTab); + int (*xFindFunction)(sqlite3_vtab *pVtab, int nArg, const char *zName, + void (**pxFunc)(sqlite3_context*,int,sqlite3_value**), + void **ppArg); + int (*xRename)(sqlite3_vtab *pVtab, const char *zNew); + /* The methods above are in version 1 of the sqlite_module object. Those + ** below are for version 2 and greater. */ + int (*xSavepoint)(sqlite3_vtab *pVTab, int); + int (*xRelease)(sqlite3_vtab *pVTab, int); + int (*xRollbackTo)(sqlite3_vtab *pVTab, int); + /* The methods above are in versions 1 and 2 of the sqlite_module object. + ** Those below are for version 3 and greater. */ + int (*xShadowName)(const char*); +}; +</pre></div> + +<p>The module structure defines all of the methods for each virtual +table object. The module structure also contains the iVersion field which +defines the particular edition of the module table structure. Currently, +iVersion is always 3 or less, but in future releases of SQLite the module +structure definition might be extended with additional methods and in +that case the maximum iVersion value will be increased. + +</p><p>The rest of the module structure consists of methods used to implement +various features of the virtual table. Details on what each of these +methods do are provided in the sequel. + +</p><h2 id="virtual_tables_and_shared_cache"><span>1.3. </span>Virtual Tables And Shared Cache</h2> + +<p>Prior to SQLite <a href="releaselog/3_6_17.html">version 3.6.17</a> (2009-08-10), +the virtual table mechanism assumes +that each <a href="c3ref/sqlite3.html">database connection</a> kept +its own copy of the database schema. Hence, the virtual table mechanism +could not be used in a database that has <a href="sharedcache.html">shared cache mode</a> enabled. +The <a href="c3ref/create_module.html">sqlite3_create_module()</a> interface would return an error if +<a href="sharedcache.html">shared cache mode</a> is enabled. That restriction was relaxed +beginning with SQLite <a href="releaselog/3_6_17.html">version 3.6.17</a>. + +<a name="customvtab"></a> + +</p><h2 id="creating_new_virtual_table_implementations"><span>1.4. </span>Creating New Virtual Table Implementations</h2> + +<p>Follow these steps to create your own virtual table: + +</p><p> +</p><ol> +<li> Write all necessary methods. +</li><li> Create an instance of the <a href="c3ref/module.html">sqlite3_module</a> structure containing pointers + to all the methods from step 1. +</li><li> Register your <a href="c3ref/module.html">sqlite3_module</a> structure using one of the + <a href="c3ref/create_module.html">sqlite3_create_module()</a> or <a href="c3ref/create_module.html">sqlite3_create_module_v2()</a> interfaces. +</li><li> Run a <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> command that specifies the new module in + the USING clause. +</li></ol> + +<p>The only really hard part is step 1. You might want to start with an +existing virtual table implementation and modify it to suit your needs. +The <a href="https://sqlite.org/src/dir?ci=trunk&type=tree">SQLite source tree</a> +contains many virtual table implementations that are suitable for copying, +including: + +</p><p> +</p><ul> +<li> <b><a href="https://sqlite.org/src/file/ext/misc/templatevtab.c">templatevtab.c</a></b> +→ A virtual table created specifically to serve as a template for +other custom virtual tables. +</li><li> <b><a href="https://sqlite.org/src/file/ext/misc/series.c">series.c</a></b> +→ Implementation of the generate_series() table-valued function. +</li><li> <b><a href="https://sqlite.org/src/file/src/json.c">json.c</a></b> → +Contains the sources for the <a href="json1.html#jeach">json_each()</a> and <a href="json1.html#jtree">json_tree()</a> table-valued +functions. +</li><li> <b><a href="https://sqlite.org/src/file/ext/misc/csv.c">csv.c</a></b> → +A virtual table that reads CSV files. +</li></ul> + + +<p>There are <a href="vtablist.html">many other virtual table implementations</a> +in the SQLite source tree that can be used as examples. Locate +these other virtual table implementations by searching +for "sqlite3_create_module". + +</p><p>You might also want to implement your new virtual table as a +<a href="loadext.html">loadable extension</a>. + +</p><h1 id="virtual_table_methods"><span>2. </span>Virtual Table Methods</h1> + +<a name="xcreate"></a> + +<h2 id="the_xcreate_method"><span>2.1. </span>The xCreate Method</h2> + +<div class="codeblock"><pre>int (*xCreate)(sqlite3 *db, void *pAux, + int argc, char *const*argv, + sqlite3_vtab **ppVTab, + char **pzErr); +</pre></div> + +<p>The xCreate method is called to create a new instance of a virtual table +in response to a <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement. +If the xCreate method is the same pointer as the <a href="vtab.html#xconnect">xConnect</a> method, then the +virtual table is an <a href="vtab.html#epovtab">eponymous virtual table</a>. +If the xCreate method is omitted (if it is a NULL pointer) then the virtual +table is an <a href="vtab.html#epoonlyvtab">eponymous-only virtual table</a>. + + +</p><p>The db parameter is a pointer to the SQLite <a href="c3ref/sqlite3.html">database connection</a> that +is executing the <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement. +The pAux argument is the copy of the client data pointer that was the +fourth argument to the <a href="c3ref/create_module.html">sqlite3_create_module()</a> or +<a href="c3ref/create_module.html">sqlite3_create_module_v2()</a> call that registered the +<a href="c3ref/module.html">virtual table module</a>. +The argv parameter is an array of argc pointers to null terminated strings. +The first string, argv[0], is the name of the module being invoked. The +module name is the name provided as the second argument to +<a href="c3ref/create_module.html">sqlite3_create_module()</a> and as the argument to the USING clause of the +<a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement that is running. +The second, argv[1], is the name of the database in which the new virtual +table is being created. The database name is "main" for the primary database, or +"temp" for TEMP database, or the name given at the end of the <a href="lang_attach.html">ATTACH</a> +statement for attached databases. The third element of the array, argv[2], +is the name of the new virtual table, as specified following the TABLE +keyword in the <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement. +If present, the fourth and subsequent strings in the argv[] array report +the arguments to the module name in the <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement. + +</p><p>The job of this method is to construct the new virtual table object +(an <a href="c3ref/vtab.html">sqlite3_vtab</a> object) and return a pointer to it in *ppVTab. + +</p><p>As part of the task of creating a new <a href="c3ref/vtab.html">sqlite3_vtab</a> structure, this +method <u>must</u> invoke <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a> to tell the SQLite +core about the columns and datatypes in the virtual table. +The <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a> API has the following prototype: + +</p><div class="codeblock"><pre>int sqlite3_declare_vtab(sqlite3 *db, const char *zCreateTable) +</pre></div> + +<p>The first argument to <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a> must be the same +<a href="c3ref/sqlite3.html">database connection</a> pointer as the first parameter to this method. +The second argument to <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a> must a zero-terminated +UTF-8 string that contains a well-formed <a href="lang_createtable.html">CREATE TABLE</a> statement that +defines the columns in the virtual table and their data types. +The name of the table in this CREATE TABLE statement is ignored, +as are all constraints. Only the column names and datatypes matter. +The CREATE TABLE statement string need not to be +held in persistent memory. The string can be +deallocated and/or reused as soon as the <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a> +routine returns. + +</p><p>The xConnect method can also optionally request special features +for the virtual table by making one or more calls to +the <a href="c3ref/vtab_config.html">sqlite3_vtab_config()</a> interface: + +</p><div class="codeblock"><pre>int sqlite3_vtab_config(sqlite3 *db, int op, ...); +</pre></div> + +<p>Calls to sqlite3_vtab_config() are optional. But for maximum +security, it is recommended that virtual table implementations +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 +virtual table will not be used from inside of triggers or views. + +</p><p>The xCreate method need not initialize the pModule, nRef, and zErrMsg +fields of the <a href="c3ref/vtab.html">sqlite3_vtab</a> object. The SQLite core will take care of +that chore. + +</p><p>The xCreate should return <a href="rescode.html#ok">SQLITE_OK</a> if it is successful in +creating the new virtual table, or <a href="rescode.html#error">SQLITE_ERROR</a> if it is not successful. +If not successful, the <a href="c3ref/vtab.html">sqlite3_vtab</a> structure must not be allocated. +An error message may optionally be returned in *pzErr if unsuccessful. +Space to hold the error message string must be allocated using +an SQLite memory allocation function like +<a href="c3ref/free.html">sqlite3_malloc()</a> or <a href="c3ref/mprintf.html">sqlite3_mprintf()</a> as the SQLite core will +attempt to free the space using <a href="c3ref/free.html">sqlite3_free()</a> after the error has +been reported up to the application. + +</p><p> +If the xCreate method is omitted (left as a NULL pointer) then the +virtual table is an <a href="vtab.html#epoonlyvtab">eponymous-only virtual table</a>. New instances of +the virtual table cannot be created using <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> and the +virtual table can only be used via its module name. +Note that SQLite versions prior to 3.9.0 (2015-10-14) do not understand +eponymous-only virtual tables and will segfault if an attempt is made +to <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> on an eponymous-only virtual table because +the xCreate method was not checked for null. + +</p><p> +If the xCreate method is the exact same pointer as the <a href="vtab.html#xconnect">xConnect</a> method, +that indicates that the virtual table does not need to initialize backing +store. Such a virtual table can be used as an <a href="vtab.html#epovtab">eponymous virtual table</a> +or as a named virtual table using <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> or both. + +<a name="hiddencol"></a> + +</p><h3 id="hidden_columns_in_virtual_tables"><span>2.1.1. </span>Hidden columns in virtual tables</h3> +<p>If a column datatype contains the special keyword "HIDDEN" +(in any combination of upper and lower case letters) then that keyword +it is omitted from the column datatype name and the column is marked +as a hidden column internally. +A hidden column differs from a normal column in three respects: + +</p><p> +</p><ul> +<li> Hidden columns are not listed in the dataset returned by + "<a href="pragma.html#pragma_table_info">PRAGMA table_info</a>", +</li><li> Hidden columns are not included in the expansion of a "*" + expression in the result set of a <a href="lang_select.html">SELECT</a>, and +</li><li> Hidden columns are not included in the implicit column-list + used by an <a href="lang_insert.html">INSERT</a> statement that lacks an explicit column-list. +</li></ul> + +<p>For example, if the following SQL is passed to <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a>: + +</p><div class="codeblock"><pre>CREATE TABLE x(a HIDDEN VARCHAR(12), b INTEGER, c INTEGER Hidden); +</pre></div> + +<p>Then the virtual table would be created with two hidden columns, +and with datatypes of "VARCHAR(12)" and "INTEGER". + +</p><p>An example use of hidden columns can be seen in the <a href="fts3.html">FTS3</a> virtual +table implementation, where every FTS virtual table +contains an <a href="fts3.html#hiddencol">FTS hidden column</a> that is used to pass information from the +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. + +<a name="tabfunc2"></a> + +</p><h3 id="table_valued_functions"><span>2.1.2. </span>Table-valued functions</h3> + +<p>A <a href="vtab.html">virtual table</a> that contains <a href="vtab.html#hiddencol">hidden columns</a> can be used like +a table-valued function in the FROM clause of a <a href="lang_select.html">SELECT</a> statement. +The arguments to the table-valued function become constraints on +the HIDDEN columns of the virtual table. + +</p><p>For example, the "generate_series" extension (located in the +<a href="http://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/series.c">ext/misc/series.c</a> +file in the <a href="http://www.sqlite.org/src/tree?ci=trunk">source tree</a>) +implements an <a href="vtab.html#epovtab">eponymous virtual table</a> with the following schema: + +</p><div class="codeblock"><pre>CREATE TABLE generate_series( + value, + start HIDDEN, + stop HIDDEN, + step HIDDEN +); +</pre></div> + +<p>The <a href="vtab.html#xbestindex">sqlite3_module.xBestIndex</a> method in the implementation of this +table checks for equality constraints against the HIDDEN columns, and uses +those as input parameters to determine the range of integer "value" outputs +to generate. Reasonable defaults are used for any unconstrained columns. +For example, to list all integers between 5 and 50: + +</p><div class="codeblock"><pre>SELECT value FROM generate_series(5,50); +</pre></div> + +<p>The previous query is equivalent to the following: + +</p><div class="codeblock"><pre>SELECT value FROM generate_series WHERE start=5 AND stop=50; +</pre></div> + +<p>Arguments on the virtual table name are matched to <a href="vtab.html#hiddencol">hidden columns</a> +in order. The number of arguments can be less than the +number of hidden columns, in which case the latter hidden columns are +unconstrained. However, an error results if there are more arguments +than there are hidden columns in the virtual table. + +<a name="worid"></a> + +</p><h3 id="_without_rowid_virtual_tables_"><span>2.1.3. </span> WITHOUT ROWID Virtual Tables </h3> + +<p>Beginning with SQLite <a href="releaselog/3_14.html">version 3.14.0</a> (2016-08-08), +the CREATE TABLE statement that +is passed into <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a> may contain a <a href="withoutrowid.html">WITHOUT ROWID</a> clause. +This is useful for cases where the virtual table rows +cannot easily be mapped into unique integers. A CREATE TABLE +statement that includes WITHOUT ROWID must define one or more columns as +the PRIMARY KEY. Every column of the PRIMARY KEY must individually be +NOT NULL and all columns for each row must be collectively unique. + +</p><p>Note that SQLite does not enforce the PRIMARY KEY for a WITHOUT ROWID +virtual table. Enforcement is the responsibility of the underlying +virtual table implementation. But SQLite does assume that the PRIMARY KEY +constraint is valid - that the identified columns really are UNIQUE and +NOT NULL - and it uses that assumption to optimize queries against the +virtual table. + +</p><p>The rowid column is not accessible on a +WITHOUT ROWID virtual table (of course). + +</p><p>The <a href="vtab.html#xupdate">xUpdate</a> method was originally designed around having a +<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 +accommodate an arbitrary PRIMARY KEY in place of the ROWID, but the +PRIMARY KEY must still be only one column. For this reason, SQLite +will reject any WITHOUT ROWID virtual table that has more than one +PRIMARY KEY column and a non-NULL xUpdate method. + +<a name="xconnect"></a> + +</p><h2 id="the_xconnect_method"><span>2.2. </span>The xConnect Method</h2> + +<div class="codeblock"><pre>int (*xConnect)(sqlite3*, void *pAux, + int argc, char *const*argv, + sqlite3_vtab **ppVTab, + char **pzErr); +</pre></div> + +<p>The xConnect method is very similar to <a href="vtab.html#xcreate">xCreate</a>. +It has the same parameters and constructs a new <a href="c3ref/vtab.html">sqlite3_vtab</a> structure +just like xCreate. +And it must also call <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a> like xCreate. It +should also make all of the same <a href="c3ref/vtab_config.html">sqlite3_vtab_config()</a> calls as +xCreate. + +</p><p>The difference is that xConnect is called to establish a new +connection to an existing virtual table whereas xCreate is called +to create a new virtual table from scratch. + +</p><p>The xCreate and xConnect methods are only different when the +virtual table has some kind of backing store that must be initialized +the first time the virtual table is created. The xCreate method creates +and initializes the backing store. The xConnect method just connects +to an existing backing store. When xCreate and xConnect are the same, +the table is an <a href="vtab.html#epovtab">eponymous virtual table</a>. + +</p><p>As an example, consider a virtual table implementation that +provides read-only access to existing comma-separated-value (CSV) +files on disk. There is no backing store that needs to be created +or initialized for such a virtual table (since the CSV files already +exist on disk) so the xCreate and xConnect methods will be identical +for that module. + +</p><p>Another example is a virtual table that implements a full-text index. +The xCreate method must create and initialize data structures to hold +the dictionary and posting lists for that index. The xConnect method, +on the other hand, only has to locate and use an existing dictionary +and posting lists that were created by a prior xCreate call. + +</p><p>The xConnect method must return <a href="rescode.html#ok">SQLITE_OK</a> if it is successful +in creating the new virtual table, or <a href="rescode.html#error">SQLITE_ERROR</a> if it is not +successful. If not successful, the <a href="c3ref/vtab.html">sqlite3_vtab</a> structure must not be +allocated. An error message may optionally be returned in *pzErr if +unsuccessful. +Space to hold the error message string must be allocated using +an SQLite memory allocation function like +<a href="c3ref/free.html">sqlite3_malloc()</a> or <a href="c3ref/mprintf.html">sqlite3_mprintf()</a> as the SQLite core will +attempt to free the space using <a href="c3ref/free.html">sqlite3_free()</a> after the error has +been reported up to the application. + +</p><p>The xConnect method is required for every virtual table implementation, +though the <a href="vtab.html#xcreate">xCreate</a> and xConnect pointers of the <a href="c3ref/module.html">sqlite3_module</a> object +may point to the same function if the virtual table does not need to +initialize backing store. + +<a name="xbestindex"></a> + +</p><h2 id="the_xbestindex_method"><span>2.3. </span>The xBestIndex Method</h2> + +<p>SQLite uses the xBestIndex method of a virtual table module to determine +the best way to access the virtual table. +The xBestIndex method has a prototype like this: + +</p><div class="codeblock"><pre>int (*xBestIndex)(sqlite3_vtab *pVTab, sqlite3_index_info*); +</pre></div> + +<p>The SQLite core communicates with the xBestIndex method by filling +in certain fields of the <a href="c3ref/index_info.html">sqlite3_index_info</a> structure and passing a +pointer to that structure into xBestIndex as the second parameter. +The xBestIndex method fills out other fields of this structure which +forms the reply. The <a href="c3ref/index_info.html">sqlite3_index_info</a> structure looks like this: + +</p><div class="codeblock"><pre>struct sqlite3_index_info { + /* Inputs */ + const int nConstraint; /* Number of entries in aConstraint */ + const struct sqlite3_index_constraint { + int iColumn; /* Column constrained. -1 for ROWID */ + unsigned char op; /* Constraint operator */ + unsigned char usable; /* True if this constraint is usable */ + int iTermOffset; /* Used internally - xBestIndex should ignore */ + } *const aConstraint; /* Table of WHERE clause constraints */ + const int nOrderBy; /* Number of terms in the ORDER BY clause */ + const struct sqlite3_index_orderby { + int iColumn; /* Column number */ + unsigned char desc; /* True for DESC. False for ASC. */ + } *const aOrderBy; /* The ORDER BY clause */ + + /* Outputs */ + struct sqlite3_index_constraint_usage { + int argvIndex; /* if >0, constraint is part of argv to xFilter */ + unsigned char omit; /* Do not code a test for this constraint */ + } *const aConstraintUsage; + int idxNum; /* Number used to identify the index */ + char *idxStr; /* String, possibly obtained from sqlite3_malloc */ + int needToFreeIdxStr; /* Free idxStr using sqlite3_free() if true */ + int orderByConsumed; /* True if output is already ordered */ + double estimatedCost; /* Estimated cost of using this index */ + <b>/* Fields below are only available in SQLite 3.8.2 and later */</b> + sqlite3_int64 estimatedRows; /* Estimated number of rows returned */ + <b>/* Fields below are only available in SQLite 3.9.0 and later */</b> + int idxFlags; /* Mask of SQLITE_INDEX_SCAN_* flags */ + <b>/* Fields below are only available in SQLite 3.10.0 and later */</b> + sqlite3_uint64 colUsed; /* Input: Mask of columns used by statement */ +}; +</pre></div> + +<p>Note the warnings on the "estimatedRows", "idxFlags", and colUsed fields. +These fields were added with SQLite versions 3.8.2, 3.9.0, and 3.10.0, respectively. +Any extension that reads or writes these fields must first check that the +version of the SQLite library in use is greater than or equal to appropriate +version - perhaps comparing the value returned from <a href="c3ref/libversion.html">sqlite3_libversion_number()</a> +against constants 3008002, 3009000, and/or 3010000. The result of attempting +to access these fields in an sqlite3_index_info structure created by an +older version of SQLite are undefined. + +</p><p>In addition, there are some defined constants: + +</p><div class="codeblock"><pre>#define SQLITE_INDEX_CONSTRAINT_EQ 2 +#define SQLITE_INDEX_CONSTRAINT_GT 4 +#define SQLITE_INDEX_CONSTRAINT_LE 8 +#define SQLITE_INDEX_CONSTRAINT_LT 16 +#define SQLITE_INDEX_CONSTRAINT_GE 32 +#define SQLITE_INDEX_CONSTRAINT_MATCH 64 +#define SQLITE_INDEX_CONSTRAINT_LIKE 65 /* 3.10.0 and later */ +#define SQLITE_INDEX_CONSTRAINT_GLOB 66 /* 3.10.0 and later */ +#define SQLITE_INDEX_CONSTRAINT_REGEXP 67 /* 3.10.0 and later */ +#define SQLITE_INDEX_CONSTRAINT_NE 68 /* 3.21.0 and later */ +#define SQLITE_INDEX_CONSTRAINT_ISNOT 69 /* 3.21.0 and later */ +#define SQLITE_INDEX_CONSTRAINT_ISNOTNULL 70 /* 3.21.0 and later */ +#define SQLITE_INDEX_CONSTRAINT_ISNULL 71 /* 3.21.0 and later */ +#define SQLITE_INDEX_CONSTRAINT_IS 72 /* 3.21.0 and later */ +#define SQLITE_INDEX_CONSTRAINT_LIMIT 73 /* 3.38.0 and later */ +#define SQLITE_INDEX_CONSTRAINT_OFFSET 74 /* 3.38.0 and later */ +#define SQLITE_INDEX_CONSTRAINT_FUNCTION 150 /* 3.25.0 and later */ +#define SQLITE_INDEX_SCAN_UNIQUE 1 /* Scan visits at most 1 row */ +</pre></div> + +<p>Use the <a href="c3ref/vtab_collation.html">sqlite3_vtab_collation()</a> interface to find the name of +the <a href="datatype3.html#collation">collating sequence</a> that should be used when evaluating the i-th +constraint: + +</p><div class="codeblock"><pre>const char *sqlite3_vtab_collation(sqlite3_index_info*, int i); +</pre></div> + +<p>The SQLite core calls the xBestIndex method when it is compiling a query +that involves a virtual table. In other words, SQLite calls this method +when it is running <a href="c3ref/prepare.html">sqlite3_prepare()</a> or the equivalent. +By calling this method, the +SQLite core is saying to the virtual table that it needs to access +some subset of the rows in the virtual table and it wants to know the +most efficient way to do that access. The xBestIndex method replies +with information that the SQLite core can then use to conduct an +efficient search of the virtual table. + +</p><p>While compiling a single SQL query, the SQLite core might call +xBestIndex multiple times with different settings in <a href="c3ref/index_info.html">sqlite3_index_info</a>. +The SQLite core will then select the combination that appears to +give the best performance. + +</p><p>Before calling this method, the SQLite core initializes an instance +of the <a href="c3ref/index_info.html">sqlite3_index_info</a> structure with information about the +query that it is currently trying to process. This information +derives mainly from the WHERE clause and ORDER BY or GROUP BY clauses +of the query, but also from any ON or USING clauses if the query is a +join. The information that the SQLite core provides to the xBestIndex +method is held in the part of the structure that is marked as "Inputs". +The "Outputs" section is initialized to zero. + +</p><p>The information in the <a href="c3ref/index_info.html">sqlite3_index_info</a> structure is ephemeral +and may be overwritten or deallocated as soon as the xBestIndex method +returns. If the xBestIndex method needs to remember any part of the +<a href="c3ref/index_info.html">sqlite3_index_info</a> structure, it should make a copy. Care must be +take to store the copy in a place where it will be deallocated, such +as in the idxStr field with needToFreeIdxStr set to 1. + +</p><p>Note that xBestIndex will always be called before <a href="vtab.html#xfilter">xFilter</a>, since +the idxNum and idxStr outputs from xBestIndex are required inputs to +xFilter. However, there is no guarantee that xFilter will be called +following a successful xBestIndex. + +</p><p>The xBestIndex method is required for every virtual table implementation. + +</p><h3 id="inputs"><span>2.3.1. </span>Inputs</h3> + +<p>The main thing that the SQLite core is trying to communicate to +the virtual table is the constraints that are available to limit +the number of rows that need to be searched. The aConstraint[] array +contains one entry for each constraint. There will be exactly +nConstraint entries in that array. + +</p><p>Each constraint will usually correspond to a term in the WHERE clause +or in a USING or ON clause that is of the form + +</p><blockquote> + column OP EXPR +</blockquote> + +<p>Where "column" is a column in the virtual table, OP is an operator +like "=" or "<", and EXPR is an arbitrary expression. So, for example, +if the WHERE clause contained a term like this: + +</p><div class="codeblock"><pre>a = 5 +</pre></div> + +<p>Then one of the constraints would be on the "a" column with +operator "=" and an expression of "5". Constraints need not have a +literal representation of the WHERE clause. The query optimizer might +make transformations to the +WHERE clause in order to extract as many constraints +as it can. So, for example, if the WHERE clause contained something +like this: + +</p><div class="codeblock"><pre>x BETWEEN 10 AND 100 AND 999>y +</pre></div> + +<p>The query optimizer might translate this into three separate constraints: + +</p><div class="codeblock"><pre>x >= 10 +x <= 100 +y < 999 +</pre></div> + +<p>For each such constraint, the aConstraint[].iColumn field indicates which +column appears on the left-hand side of the constraint. +The first column of the virtual table is column 0. +The rowid of the virtual table is column -1. +The aConstraint[].op field indicates which operator is used. +The SQLITE_INDEX_CONSTRAINT_* constants map integer constants +into operator values. +Columns occur in the order they were defined by the call to +<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. +Hidden columns are counted when determining the column index. + +</p><p>If the <a href="vtab.html#xfindfunction">xFindFunction()</a> method for the virtual table is defined, and +if xFindFunction() sometimes returns <a href="c3ref/c_index_constraint_eq.html">SQLITE_INDEX_CONSTRAINT_FUNCTION</a> or +larger, then the constraints might also be of the form: + +</p><blockquote> + FUNCTION( column, EXPR) +</blockquote> + +<p>In this case the aConstraint[].op value is the same as the value +returned by <a href="vtab.html#xfindfunction">xFindFunction()</a> for FUNCTION. + +</p><p>The aConstraint[] array contains information about all constraints +that apply to the virtual table. But some of the constraints might +not be usable because of the way tables are ordered in a join. +The xBestIndex method must therefore only consider constraints +that have an aConstraint[].usable flag which is true. + +</p><p>In addition to WHERE clause constraints, the SQLite core also +tells the xBestIndex method about the ORDER BY clause. +(In an aggregate query, the SQLite core might put in GROUP BY clause +information in place of the ORDER BY clause information, but this fact +should not make any difference to the xBestIndex method.) +If all terms of the ORDER BY clause are columns in the virtual table, +then nOrderBy will be the number of terms in the ORDER BY clause +and the aOrderBy[] array will identify the column for each term +in the order by clause and whether or not that column is ASC or DESC. + +<a name="colUsed"></a> + +</p><p>In SQLite <a href="releaselog/3_10_0.html">version 3.10.0</a> (2016-01-06) and later, +the colUsed field is available +to indicate which fields of the virtual table are actually used by the +statement being prepared. If the lowest bit of colUsed is set, that +means that the first column is used. The second lowest bit corresponds +to the second column. And so forth. If the most significant bit of +colUsed is set, that means that one or more columns other than the +first 63 columns are used. If column usage information is needed by the +<a href="vtab.html#xfilter">xFilter</a> method, then the required bits must be encoded into either +the output idxNum field or idxStr content. + +</p><h4 id="like_glob_regexp_and_match_functions"><span>2.3.1.1. </span>LIKE, GLOB, REGEXP, and MATCH functions</h4> + +<p>For the LIKE, GLOB, REGEXP, and MATCH operators, the +aConstraint[].iColumn value is the virtual table column that +is the left operand of the operator. However, if these operators +are expressed as function calls instead of operators, then +the aConstraint[].iColumn value references the virtual table +column that is the second argument to that function: + +</p><blockquote> +LIKE(<i>EXPR</i>, <i>column</i>)<br> +GLOB(<i>EXPR</i>, <i>column</i>)<br> +REGEXP(<i>EXPR</i>, <i>column</i>)<br> +MATCH(<i>EXPR</i>, <i>column</i>)<br> +</blockquote> + +<p>Hence, as far as the xBestIndex() method is concerned, the following +two forms are equivalent: + +</p><blockquote> +<i>column</i> LIKE <i>EXPR</i><br> +LIKE(<i>EXPR</i>,<i>column</i>) +</blockquote> + +<p>This special behavior of looking at the second argument of a function +only occurs for the LIKE, GLOB, REGEXP, and MATCH functions. For all +other functions, the aConstraint[].iColumn value references the first +argument of the function. + +</p><p>This special feature of LIKE, GLOB, REGEXP, and MATCH does not +apply to the <a href="vtab.html#xfindfunction">xFindFunction()</a> method, however. The +<a href="vtab.html#xfindfunction">xFindFunction()</a> method always keys off of the left operand of an +LIKE, GLOB, REGEXP, or MATCH operator but off of the first argument +to function-call equivalents of those operators. + +</p><h4 id="limit_and_offset"><span>2.3.1.2. </span>LIMIT and OFFSET</h4> + +<p>When aConstraint[].op is one of SQLITE_INDEX_CONSTRAINT_LIMIT or +SQLITE_INDEX_CONSTRAINT_OFFSET, that indicates that there is a +LIMIT or OFFSET clause on the SQL query statement that is using +the virtual table. The LIMIT and OFFSET operators have no +left operand, and so when aConstraint[].op is one of +SQLITE_INDEX_CONSTRAINT_LIMIT or SQLITE_INDEX_CONSTRAINT_OFFSET +then the aConstraint[].iColumn value is meaningless and should +not be used. + +</p><h4 id="right_hand_side_values_of_constraints"><span>2.3.1.3. </span>Right-hand side values of constraints</h4> + +<p>The <a href="c3ref/vtab_rhs_value.html">sqlite3_vtab_rhs_value()</a> interface can be used to try to +access the right-hand operand of a constraint. However, the value +of a right-hand operator might not be known at the time that +the xBestIndex method is run, so the sqlite3_vtab_rhs_value() +call might not be successful. Usually the right operand of a +constraint is only available to xBestIndex if it is coded as +a literal value in the input SQL. If the right operand is +coded as an expression or a <a href="c3ref/bind_blob.html">host parameter</a>, it probably will +not be accessible to xBestIndex. Some operators, such as +<a href="c3ref/c_index_constraint_eq.html">SQLITE_INDEX_CONSTRAINT_ISNULL</a> and +<a href="c3ref/c_index_constraint_eq.html">SQLITE_INDEX_CONSTRAINT_ISNOTNULL</a> have no right-hand operand. +The sqlite3_vtab_rhs_value() interface always returns +<a href="rescode.html#notfound">SQLITE_NOTFOUND</a> for such operators. + +</p><h3 id="outputs"><span>2.3.2. </span>Outputs</h3> + +<p>Given all of the information above, the job of the xBestIndex +method it to figure out the best way to search the virtual table. + +</p><p>The xBestIndex method conveys an indexing strategy to the <a href="vtab.html#xfilter">xFilter</a> +method through the idxNum and idxStr fields. The idxNum value and +idxStr string content are arbitrary as far as the SQLite core is +concerned and can have any meaning as long as xBestIndex and xFilter +agree on what that meaning is. The SQLite core just copies the +information from xBestIndex through to the <a href="vtab.html#xfilter">xFilter</a> method, assuming +only that the char sequence referenced via idxStr is NUL terminated. + +</p><p>The idxStr value may be a string obtained from an SQLite +memory allocation function such as <a href="c3ref/mprintf.html">sqlite3_mprintf()</a>. +If this is the case, then the needToFreeIdxStr flag must be set to +true so that the SQLite core will know to call <a href="c3ref/free.html">sqlite3_free()</a> on +that string when it has finished with it, and thus avoid a memory leak. +The idxStr value may also be a static constant string, in which case +the needToFreeIdxStr boolean should remain false. + + +</p><p>The estimatedCost field should be set to the estimated number +of disk access operations required to execute this query against +the virtual table. The SQLite core will often call xBestIndex +multiple times with different constraints, obtain multiple cost +estimates, then choose the query plan that gives the lowest estimate. +The SQLite core initializes estimatedCost to a very large value +prior to invoking xBestIndex, so if xBestIndex determines that the +current combination of parameters is undesirable, it can leave the +estimatedCost field unchanged to discourage its use. + +</p><p>If the current version of SQLite is 3.8.2 or greater, the estimatedRows +field may be set to an estimate of the number of rows returned by the +proposed query plan. If this value is not explicitly set, the default +estimate of 25 rows is used. + +</p><p>If the current version of SQLite is 3.9.0 or greater, the idxFlags field +may be set to SQLITE_INDEX_SCAN_UNIQUE to indicate that the virtual table +will return only zero or one rows given the input constraints. Additional +bits of the idxFlags field might be understood in later versions of SQLite. + +</p><p>The aConstraintUsage[] array contains one element for each of +the nConstraint constraints in the inputs section of the +<a href="c3ref/index_info.html">sqlite3_index_info</a> structure. +The aConstraintUsage[] array is used by xBestIndex to tell the +core how it is using the constraints. + +</p><p>The xBestIndex method may set aConstraintUsage[].argvIndex +entries to values greater than zero. +Exactly one entry should be set to 1, another to 2, another to 3, +and so forth up to as many or as few as the xBestIndex method wants. +The EXPR of the corresponding constraints will then be passed +in as the argv[] parameters to xFilter. + +</p><p>For example, if the aConstraint[3].argvIndex is set to 1, then +when xFilter is called, the argv[0] passed to xFilter will have +the EXPR value of the aConstraint[3] constraint. + +</p><h4 id="omit_constraint_checking_in_bytecode"><span>2.3.2.1. </span>Omit constraint checking in bytecode</h4> + +<p>By default, the SQLite generates <a href="opcode.html">bytecode</a> that will double +checks all constraints on each row of the virtual table to verify +that they are satisfied. If the virtual table can guarantee +that a constraint will always be satisfied, it can try to +suppress that double-check by setting aConstraintUsage[].omit. +However, with some exceptions, this is only a hint and +there is no guarantee that the redundant check of the constraint +will be suppressed. Key points: + +</p><ul> +<li><p> +The omit flag is only honored if the argvIndex value for the +constraint is greater than 0 and less than or equal to 16. +Constraint checking is never suppressed for constraints +that do not pass their right operand into the xFilter method. +The current implementation is only able to suppress redundant +constraint checking for the first 16 values passed to xFilter, +though that limitation might be increased in future releases. + +</p></li><li><p> +The omit flag is always honored for <a href="c3ref/c_index_constraint_eq.html">SQLITE_INDEX_CONSTRAINT_OFFSET</a> +constraints as long as argvIndex is greater than 0. Setting the +omit flag on an SQLITE_INDEX_CONSTRAINT_OFFSET constraint indicates +to SQLite that the virtual table will itself suppress the first N +rows of output, where N is the right operand of the OFFSET operator. +If the virtual table implementation sets omit on an +SQLITE_INDEX_CONSTRAINT_OFFSET constraint but then fails to suppress +the first N rows of output, an incorrect answer will result from +the overall query. +</p></li></ul> + +<a name="obc"></a> + +<h4 id="order_by_and_orderbyconsumed"><span>2.3.2.2. </span>ORDER BY and orderByConsumed</h4> + +<p>If the virtual table will output rows in the order specified by +the ORDER BY clause, then the orderByConsumed flag may be set to +true. If the output is not automatically in the correct order +then orderByConsumed must be left in its default false setting. +This will indicate to the SQLite core that it will need to do a +separate sorting pass over the data after it comes out of the virtual table. +Setting orderByConsumed is an optimization. A query will always +get the correct answer if orderByConsumed is left at its default +value (0). Unnecessary sort operations might be avoided resulting +in a faster query if orderByConsumed is set, but setting +orderByConsumed incorrectly can result in an incorrect answer. +It is suggested that new virtual table implementations leave +the orderByConsumed value unset initially, and then after everything +else is known to be working correctly, go back and attempt to +optimize by setting orderByConsumed where appropriate. + +</p><p>Sometimes the orderByConsumed flag can be safely set even if +the outputs from the virtual table are not strictly in the order +specified by nOrderBy and aOrderBy. If the +<a href="c3ref/vtab_distinct.html">sqlite3_vtab_distinct()</a> interface returns 1 or 2, that indicates +that the ordering can be relaxed. See the documentation on +<a href="c3ref/vtab_distinct.html">sqlite3_vtab_distinct()</a> for further information. + + +</p><h3 id="return_value"><span>2.3.3. </span>Return Value</h3> + +<p>The xBestIndex method should return SQLITE_OK on success. If any +kind of fatal error occurs, an appropriate error code (ex: <a href="rescode.html#nomem">SQLITE_NOMEM</a>) +should be returned instead. + +</p><p>If xBestIndex returns <a href="rescode.html#constraint">SQLITE_CONSTRAINT</a>, that does not indicate an +error. Rather, SQLITE_CONSTRAINT indicates that the particular combination +of input parameters specified is insufficient for the virtual table +to do its job. +This is logically the same as setting the estimatedCost to infinity. +If every call to xBestIndex for a particular query plan returns +SQLITE_CONSTRAINT, that means there is no way for the virtual table +to be safely used, and the <a href="c3ref/prepare.html">sqlite3_prepare()</a> call will fail with +a "no query solution" error. + +</p><h3 id="enforcing_required_parameters_on_table_valued_functions"><span>2.3.4. </span>Enforcing Required Parameters On Table-Valued Functions</h3> + +<p>The SQLITE_CONSTRAINT return from xBestIndex +is useful for <a href="vtab.html#tabfunc2">table-valued functions</a> that +have required parameters. If the aConstraint[].usable field is false +for one of the required parameter, then the xBestIndex method should +return SQLITE_CONSTRAINT. If a required field does not appear in +the aConstraint[] array at all, that means that the corresponding +parameter is omitted from the input SQL. In that case, xBestIndex +should set an error message in pVTab->zErrMsg and return +SQLITE_ERROR. To summarize: + +</p><ol> +<li><p> +The aConstraint[].usable value for a required parameter is +false <big>→</big> return SQLITE_CONSTRAINT. +</p></li><li><p> +A required parameter does not appears anywhere in +the aConstraint[] array <big>→</big> +Set an error message in pVTab->zErrMsg and return +SQLITE_ERROR +</p></li></ol> + +<p>The following example will better illustrate the use of SQLITE_CONSTRAINT +as a return value from xBestIndex: + +</p><div class="codeblock"><pre>SELECT * FROM realtab, tablevaluedfunc(realtab.x); +</pre></div> + +<p>Assuming that the first hidden column of "tablevaluedfunc" is "param1", +the query above is semantically equivalent to this: + +</p><div class="codeblock"><pre>SELECT * FROM realtab, tablevaluedfunc + WHERE tablevaluedfunc.param1 = realtab.x; +</pre></div> + +<p>The query planner must decide between many possible implementations +of this query, but two plans in particular are of note: + +</p><ol> +<li><p>Scan all +rows of realtab and for each row, find rows in tablevaluedfunc where +param1 is equal to realtab.x + +</p></li><li><p>Scan all rows of tablevalued func and for each row find rows +in realtab where x is equal to tablevaluedfunc.param1. +</p></li></ol> + +<p>The xBestIndex method will be invoked once for each of the potential +plans above. For plan 1, the aConstraint[].usable flag for the +SQLITE_CONSTRAINT_EQ constraint on the param1 column will be true because +the right-hand side value for the "param1 = ?" constraint will be known, +since it is determined by the outer realtab loop. +But for plan 2, the aConstraint[].usable flag for "param1 = ?" will be false +because the right-hand side value is determined by an inner loop and is thus +an unknown quantity. Because param1 is a required input to the table-valued +functions, the xBestIndex method should return SQLITE_CONSTRAINT when presented +with plan 2, indicating that a required input is missing. This forces the +query planner to select plan 1. + +<a name="xdisconnect"></a> + +</p><h2 id="the_xdisconnect_method"><span>2.4. </span>The xDisconnect Method</h2> + +<div class="codeblock"><pre>int (*xDisconnect)(sqlite3_vtab *pVTab); +</pre></div> + +<p>This method releases a connection to a virtual table. +Only the <a href="c3ref/vtab.html">sqlite3_vtab</a> object is destroyed. +The virtual table is not destroyed and any backing store +associated with the virtual table persists. + +This method undoes the work of <a href="vtab.html#xconnect">xConnect</a>. + +</p><p>This method is a destructor for a connection to the virtual table. +Contrast this method with <a href="vtab.html#sqlite3_module.xDestroy">xDestroy</a>. The xDestroy is a destructor +for the entire virtual table. + +</p><p>The xDisconnect method is required for every virtual table implementation, +though it is acceptable for the xDisconnect and <a href="vtab.html#sqlite3_module.xDestroy">xDestroy</a> methods to be +the same function if that makes sense for the particular virtual table. + +<a name="sqlite3_module.xDestroy"></a> + +</p><h2 id="the_xdestroy_method"><span>2.5. </span>The xDestroy Method</h2> + +<div class="codeblock"><pre>int (*xDestroy)(sqlite3_vtab *pVTab); +</pre></div> + +<p>This method releases a connection to a virtual table, just like +the <a href="vtab.html#xdisconnect">xDisconnect</a> method, and it also destroys the underlying +table implementation. This method undoes the work of <a href="vtab.html#xcreate">xCreate</a>. + +</p><p>The <a href="vtab.html#xdisconnect">xDisconnect</a> method is called whenever a database connection +that uses a virtual table is closed. The xDestroy method is only +called when a <a href="lang_droptable.html">DROP TABLE</a> statement is executed against the virtual table. + +</p><p>The xDestroy method is required for every virtual table implementation, +though it is acceptable for the <a href="vtab.html#xdisconnect">xDisconnect</a> and xDestroy methods to be +the same function if that makes sense for the particular virtual table. + +<a name="xopen"></a> + +</p><h2 id="the_xopen_method"><span>2.6. </span>The xOpen Method</h2> + +<div class="codeblock"><pre>int (*xOpen)(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor); +</pre></div> + +<p>The xOpen method creates a new cursor used for accessing (read and/or +writing) a virtual table. A successful invocation of this method +will allocate the memory for the <a href="c3ref/vtab_cursor.html">sqlite3_vtab_cursor</a> (or a subclass), +initialize the new object, and make *ppCursor point to the new object. +The successful call then returns <a href="rescode.html#ok">SQLITE_OK</a>. + +</p><p>For every successful call to this method, the SQLite core will +later invoke the <a href="vtab.html#xclose">xClose</a> method to destroy +the allocated cursor. + +</p><p>The xOpen method need not initialize the pVtab field of the +<a href="c3ref/vtab_cursor.html">sqlite3_vtab_cursor</a> structure. The SQLite core will take care +of that chore automatically. + +</p><p>A virtual table implementation must be able to support an arbitrary +number of simultaneously open cursors. + +</p><p>When initially opened, the cursor is in an undefined state. +The SQLite core will invoke the <a href="vtab.html#xfilter">xFilter</a> method +on the cursor prior to any attempt to position or read from the cursor. + +</p><p>The xOpen method is required for every virtual table implementation. + +<a name="xclose"></a> + +</p><h2 id="the_xclose_method"><span>2.7. </span>The xClose Method</h2> + +<div class="codeblock"><pre>int (*xClose)(sqlite3_vtab_cursor*); +</pre></div> + +<p>The xClose method closes a cursor previously opened by +<a href="vtab.html#xopen">xOpen</a>. +The SQLite core will always call xClose once for each cursor opened +using xOpen. + +</p><p>This method must release all resources allocated by the +corresponding xOpen call. The routine will not be called again even if it +returns an error. The SQLite core will not use the +<a href="c3ref/vtab_cursor.html">sqlite3_vtab_cursor</a> again after it has been closed. + +</p><p>The xClose method is required for every virtual table implementation. + +<a name="xeof"></a> + +</p><h2 id="the_xeof_method"><span>2.8. </span>The xEof Method</h2> + +<div class="codeblock"><pre>int (*xEof)(sqlite3_vtab_cursor*); +</pre></div> + +<p>The xEof method must return false (zero) if the specified cursor +currently points to a valid row of data, or true (non-zero) otherwise. +This method is called by the SQL engine immediately after each +<a href="vtab.html#xfilter">xFilter</a> and <a href="vtab.html#xnext">xNext</a> invocation. + +</p><p>The xEof method is required for every virtual table implementation. + +<a name="xfilter"></a> + +</p><h2 id="the_xfilter_method"><span>2.9. </span>The xFilter Method</h2> + +<div class="codeblock"><pre>int (*xFilter)(sqlite3_vtab_cursor*, int idxNum, const char *idxStr, + int argc, sqlite3_value **argv); +</pre></div> + +<p>This method begins a search of a virtual table. +The first argument is a cursor opened by <a href="vtab.html#xopen">xOpen</a>. +The next two arguments define a particular search index previously +chosen by <a href="vtab.html#xbestindex">xBestIndex</a>. The specific meanings of idxNum and idxStr +are unimportant as long as xFilter and xBestIndex agree on what +that meaning is. + +</p><p>The xBestIndex function may have requested the values of +certain expressions using the aConstraintUsage[].argvIndex values +of the <a href="c3ref/index_info.html">sqlite3_index_info</a> structure. +Those values are passed to xFilter using the argc and argv parameters. + +</p><p>If the virtual table contains one or more rows that match the +search criteria, then the cursor must be left point at the first row. +Subsequent calls to <a href="vtab.html#xeof">xEof</a> must return false (zero). +If there are no rows match, then the cursor must be left in a state +that will cause the <a href="vtab.html#xeof">xEof</a> to return true (non-zero). +The SQLite engine will use +the <a href="vtab.html#xcolumn">xColumn</a> and <a href="vtab.html#xrowid">xRowid</a> methods to access that row content. +The <a href="vtab.html#xnext">xNext</a> method will be used to advance to the next row. + +</p><p>This method must return <a href="rescode.html#ok">SQLITE_OK</a> if successful, or an sqlite +<a href="rescode.html">error code</a> if an error occurs. + +</p><p>The xFilter method is required for every virtual table implementation. + +<a name="xnext"></a> + +</p><h2 id="the_xnext_method"><span>2.10. </span>The xNext Method</h2> + +<div class="codeblock"><pre>int (*xNext)(sqlite3_vtab_cursor*); +</pre></div> + +<p>The xNext method advances a <a href="c3ref/vtab_cursor.html">virtual table cursor</a> +to the next row of a result set initiated by <a href="vtab.html#xfilter">xFilter</a>. +If the cursor is already pointing at the last row when this +routine is called, then the cursor no longer points to valid +data and a subsequent call to the <a href="vtab.html#xeof">xEof</a> method must return true (non-zero). +If the cursor is successfully advanced to another row of content, then +subsequent calls to <a href="vtab.html#xeof">xEof</a> must return false (zero). + +</p><p>This method must return <a href="rescode.html#ok">SQLITE_OK</a> if successful, or an sqlite +<a href="rescode.html">error code</a> if an error occurs. + +</p><p>The xNext method is required for every virtual table implementation. + +<a name="xcolumn"></a> + +</p><h2 id="the_xcolumn_method"><span>2.11. </span>The xColumn Method</h2> + +<div class="codeblock"><pre>int (*xColumn)(sqlite3_vtab_cursor*, sqlite3_context*, int N); +</pre></div> + +<p>The SQLite core invokes this method in order to find the value for +the N-th column of the current row. N is zero-based so the first column +is numbered 0. +The xColumn method may return its result back to SQLite using one of the +following interface: + +</p><p> +</p><ul> +<li> <a href="c3ref/result_blob.html">sqlite3_result_blob()</a> +</li><li> <a href="c3ref/result_blob.html">sqlite3_result_double()</a> +</li><li> <a href="c3ref/result_blob.html">sqlite3_result_int()</a> +</li><li> <a href="c3ref/result_blob.html">sqlite3_result_int64()</a> +</li><li> <a href="c3ref/result_blob.html">sqlite3_result_null()</a> +</li><li> <a href="c3ref/result_blob.html">sqlite3_result_text()</a> +</li><li> <a href="c3ref/result_blob.html">sqlite3_result_text16()</a> +</li><li> <a href="c3ref/result_blob.html">sqlite3_result_text16le()</a> +</li><li> <a href="c3ref/result_blob.html">sqlite3_result_text16be()</a> +</li><li> <a href="c3ref/result_blob.html">sqlite3_result_zeroblob()</a> +</li></ul> + + +<p>If the xColumn method implementation calls none of the functions above, +then the value of the column defaults to an SQL NULL. + +</p><p>To raise an error, the xColumn method should use one of the result_text() +methods to set the error message text, then return an appropriate +<a href="rescode.html">error code</a>. The xColumn method must return <a href="rescode.html#ok">SQLITE_OK</a> on success. + +</p><p>The xColumn method is required for every virtual table implementation. + +<a name="xrowid"></a> + +</p><h2 id="the_xrowid_method"><span>2.12. </span>The xRowid Method</h2> + +<div class="codeblock"><pre>int (*xRowid)(sqlite3_vtab_cursor *pCur, sqlite_int64 *pRowid); +</pre></div> + +<p>A successful invocation of this method will cause *pRowid to be +filled with the <a href="lang_createtable.html#rowid">rowid</a> of row that the +<a href="c3ref/vtab_cursor.html">virtual table cursor</a> pCur is currently pointing at. +This method returns <a href="rescode.html#ok">SQLITE_OK</a> on success. +It returns an appropriate <a href="rescode.html">error code</a> on failure.</p> + +<p>The xRowid method is required for every virtual table implementation. + +<a name="xupdate"></a> + +</p><h2 id="the_xupdate_method"><span>2.13. </span>The xUpdate Method</h2> + +<div class="codeblock"><pre>int (*xUpdate)( + sqlite3_vtab *pVTab, + int argc, + sqlite3_value **argv, + sqlite_int64 *pRowid +); +</pre></div> + +<p>All changes to a virtual table are made using the xUpdate method. +This one method can be used to insert, delete, or update. + +</p><p>The argc parameter specifies the number of entries in the argv array. +The value of argc will be 1 for a pure delete operation or N+2 for an insert +or replace or update where N is the number of columns in the table. +In the previous sentence, N includes any hidden columns. + +</p><p>Every argv entry will have a non-NULL value in C but may contain the +SQL value NULL. In other words, it is always true that +<tt>argv[i]!=0</tt> for <b>i</b> between 0 and <tt>argc-1</tt>. +However, it might be the case that +<tt>sqlite3_value_type(argv[i])==SQLITE_NULL</tt>. + +</p><p>The argv[0] parameter is the <a href="lang_createtable.html#rowid">rowid</a> of a row in the virtual table +to be deleted. If argv[0] is an SQL NULL, then no deletion occurs. + +</p><p>The argv[1] parameter is the rowid of a new row to be inserted +into the virtual table. If argv[1] is an SQL NULL, then the implementation +must choose a rowid for the newly inserted row. Subsequent argv[] +entries contain values of the columns of the virtual table, in the +order that the columns were declared. The number of columns will +match the table declaration that the <a href="vtab.html#xconnect">xConnect</a> or <a href="vtab.html#xcreate">xCreate</a> method made +using the <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a> call. All hidden columns are included. + +</p><p>When doing an insert without a rowid (argc>1, argv[1] is an SQL NULL), +on a virtual table that uses ROWID (but not on a <a href="vtab.html#worid">WITHOUT ROWID virtual table</a>), +the implementation must set *pRowid to the rowid of the newly inserted row; +this will become the value returned by the <a href="c3ref/last_insert_rowid.html">sqlite3_last_insert_rowid()</a> +function. Setting this value in all the other cases is a harmless no-op; +the SQLite engine ignores the *pRowid return value if argc==1 or +argv[1] is not an SQL NULL. + +</p><p>Each call to xUpdate will fall into one of cases shown below. +Not that references to <b>argv[i]</b> mean the SQL value +held within the argv[i] object, not the argv[i] +object itself. + +</p><blockquote> +<dl> +<dt><b>argc = 1 <br> argv[0] ≠ NULL</b> +</dt><dd><p> +DELETE: The single row with rowid or PRIMARY KEY equal to argv[0] is deleted. +No insert occurs. + +</p></dd><dt><b>argc > 1 <br> argv[0] = NULL</b> +</dt><dd><p> +INSERT: A new row is inserted with column values taken from +argv[2] and following. In a rowid virtual table, if argv[1] is an SQL NULL, +then a new unique rowid is generated automatically. The argv[1] will be NULL +for a <a href="vtab.html#worid">WITHOUT ROWID virtual table</a>, in which case the implementation should +take the PRIMARY KEY value from the appropriate column in argv[2] and following. + +</p></dd><dt><b>argc > 1 <br> argv[0] ≠ NULL <br> argv[0] = argv[1]</b> +</dt><dd><p> +UPDATE: +The row with rowid or PRIMARY KEY argv[0] is updated with new values +in argv[2] and following parameters. + +</p></dd><dt><b>argc > 1 <br> argv[0] ≠ NULL <br> argv[0] ≠ argv[1]</b> +</dt><dd><p> +UPDATE with rowid or PRIMARY KEY change: +The row with rowid or PRIMARY KEY argv[0] is updated with +the rowid or PRIMARY KEY in argv[1] +and new values in argv[2] and following parameters. This will occur +when an SQL statement updates a rowid, as in the statement: +</p><blockquote> + <a href="lang_update.html">UPDATE</a> table SET rowid=rowid+1 WHERE ...; +</blockquote> +</dd></dl> +</blockquote> + +<p>The xUpdate method must return <a href="rescode.html#ok">SQLITE_OK</a> if and only if it is +successful. If a failure occurs, the xUpdate must return an appropriate +<a href="rescode.html">error code</a>. On a failure, the pVTab->zErrMsg element may optionally +be replaced with error message text stored in memory allocated from SQLite +using functions such as <a href="c3ref/mprintf.html">sqlite3_mprintf()</a> or <a href="c3ref/free.html">sqlite3_malloc()</a>. + +</p><p>If the xUpdate method violates some constraint of the virtual table +(including, but not limited to, attempting to store a value of the wrong +datatype, attempting to store a value that is too +large or too small, or attempting to change a read-only value) then the +xUpdate must fail with an appropriate <a href="rescode.html">error code</a>. + +</p><p>If the xUpdate method is performing an UPDATE, then +<a href="c3ref/value_blob.html">sqlite3_value_nochange(X)</a> can be used to discover which columns +of the virtual table were actually modified by the UPDATE +statement. The <a href="c3ref/value_blob.html">sqlite3_value_nochange(X)</a> interface returns +true for columns that do not change. +On every UPDATE, SQLite will first invoke +<a href="vtab.html#xcolumn">xColumn</a> separately for each unchanging column in the table to +obtain the value for that column. The <a href="vtab.html#xcolumn">xColumn</a> method can +check to see if the column is unchanged at the SQL level +by invoking <a href="c3ref/vtab_nochange.html">sqlite3_vtab_nochange()</a>. If <a href="vtab.html#xcolumn">xColumn</a> sees that +the column is not being modified, it should return without setting +a result using one of the <a href="c3ref/result_blob.html">sqlite3_result_xxxxx()</a> +interfaces. Only in that case <a href="c3ref/value_blob.html">sqlite3_value_nochange()</a> will be +true within the xUpdate method. If <a href="vtab.html#xcolumn">xColumn</a> does +invoke one or more <a href="c3ref/result_blob.html">sqlite3_result_xxxxx()</a> +interfaces, then SQLite understands that as a change in the value +of the column and the <a href="c3ref/value_blob.html">sqlite3_value_nochange()</a> call for that +column within xUpdate will return false. + +</p><p>There might be one or more <a href="c3ref/vtab_cursor.html">sqlite3_vtab_cursor</a> objects open and in use +on the virtual table instance and perhaps even on the row of the virtual +table when the xUpdate method is invoked. The implementation of +xUpdate must be prepared for attempts to delete or modify rows of the table +out from other existing cursors. If the virtual table cannot accommodate +such changes, the xUpdate method must return an <a href="rescode.html">error code</a>. + +</p><p>The xUpdate method is optional. +If the xUpdate pointer in the <a href="c3ref/module.html">sqlite3_module</a> for a virtual table +is a NULL pointer, then the virtual table is read-only. + + +<a name="xfindfunction"></a> + +</p><h2 id="the_xfindfunction_method"><span>2.14. </span>The xFindFunction Method</h2> + +<div class="codeblock"><pre>int (*xFindFunction)( + sqlite3_vtab *pVtab, + int nArg, + const char *zName, + void (**pxFunc)(sqlite3_context*,int,sqlite3_value**), + void **ppArg +); +</pre></div> + +<p>This method is called during <a href="c3ref/prepare.html">sqlite3_prepare()</a> to give the virtual +table implementation an opportunity to overload functions. +This method may be set to NULL in which case no overloading occurs. + +</p><p>When a function uses a column from a virtual table as its first +argument, this method is called to see if the virtual table would +like to overload the function. The first three parameters are inputs: +the virtual table, the number of arguments to the function, and the +name of the function. If no overloading is desired, this method +returns 0. To overload the function, this method writes the new +function implementation into *pxFunc and writes user data into *ppArg +and returns either 1 or a number between +<a href="c3ref/c_index_constraint_eq.html">SQLITE_INDEX_CONSTRAINT_FUNCTION</a> and 255. + +</p><p>Historically, the return value from xFindFunction() was either zero +or one. Zero means that the function is not overloaded and one means that +it is overload. The ability to return values of +<a href="c3ref/c_index_constraint_eq.html">SQLITE_INDEX_CONSTRAINT_FUNCTION</a> or greater was added in +version 3.25.0 (2018-09-15). If xFindFunction returns +<a href="c3ref/c_index_constraint_eq.html">SQLITE_INDEX_CONSTRAINT_FUNCTION</a> or greater, than means that the function +takes two arguments and the function +can be used as a boolean in the WHERE clause of a query and that +the virtual table is able to exploit that function to speed up the query +result. When xFindFunction returns <a href="c3ref/c_index_constraint_eq.html">SQLITE_INDEX_CONSTRAINT_FUNCTION</a> or +larger, the value returned becomes the <a href="c3ref/index_info.html">sqlite3_index_info</a>.aConstraint.op +value for one of the constraints passed into <a href="vtab.html#xbestindex">xBestIndex()</a>. The first +argument to the function is the column identified by +aConstraint[].iColumn field of the constraint and the second argument to the +function is the value that will be passed into <a href="vtab.html#xfilter">xFilter()</a> (if the +aConstraintUsage[].argvIndex value is set) or the value returned from +<a href="c3ref/vtab_rhs_value.html">sqlite3_vtab_rhs_value()</a>. + +</p><p>The <a href="geopoly.html">Geopoly module</a> is an example of a virtual table that makes use +of <a href="c3ref/c_index_constraint_eq.html">SQLITE_INDEX_CONSTRAINT_FUNCTION</a> to improve performance. +The xFindFunction() method for Geopoly returns +SQLITE_INDEX_CONSTRAINT_FUNCTION for the <a href="geopoly.html#goverlap">geopoly_overlap()</a> SQL function +and it returns +SQLITE_INDEX_CONSTRAINT_FUNCTION+1 for the <a href="geopoly.html#gwithin">geopoly_within()</a> SQL function. +This permits search optimizations for queries such as: + +</p><div class="codeblock"><pre>SELECT * FROM geopolytab WHERE geopoly_overlap(_shape, $query_polygon); +SELECT * FROM geopolytab WHERE geopoly_within(_shape, $query_polygon); +</pre></div> + +<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 +the order of their arguments. So "like(A,B)" would normally work the same +as "B like A". +However, xFindFunction() always looks a the left-most argument, not +the first logical argument. +Hence, for the form "B like A", SQLite looks at the +left operand "B" and if that operand is a virtual table column +it invokes the xFindFunction() method on that virtual table. +But if the form "like(A,B)" is used instead, then SQLite checks +the A term to see if it is column of a virtual table and if so +it invokes the xFindFunction() method for the virtual table of +column A. + +</p><p>The function pointer returned by this routine must be valid for +the lifetime of the <a href="c3ref/vtab.html">sqlite3_vtab</a> object given in the first parameter. + +<a name="xBegin"></a> + +</p><h2 id="the_xbegin_method"><span>2.15. </span>The xBegin Method</h2> + +<div class="codeblock"><pre>int (*xBegin)(sqlite3_vtab *pVTab); +</pre></div> + +<p>This method begins a transaction on a virtual table. +This is method is optional. The xBegin pointer of <a href="c3ref/module.html">sqlite3_module</a> +may be NULL. + +</p><p>This method is always followed by one call to either the +<a href="vtab.html#xcommit">xCommit</a> or <a href="vtab.html#xrollback">xRollback</a> method. Virtual table transactions do +not nest, so the xBegin method will not be invoked more than once +on a single virtual table +without an intervening call to either <a href="vtab.html#xcommit">xCommit</a> or <a href="vtab.html#xrollback">xRollback</a>. +Multiple calls to other methods can and likely will occur in between +the xBegin and the corresponding <a href="vtab.html#xcommit">xCommit</a> or <a href="vtab.html#xrollback">xRollback</a>. + +<a name="xsync"></a> + +</p><h2 id="the_xsync_method"><span>2.16. </span>The xSync Method</h2> + +<div class="codeblock"><pre>int (*xSync)(sqlite3_vtab *pVTab); +</pre></div> + + +<p>This method signals the start of a two-phase commit on a virtual +table. +This is method is optional. The xSync pointer of <a href="c3ref/module.html">sqlite3_module</a> +may be NULL. + +</p><p>This method is only invoked after call to the <a href="vtab.html#xBegin">xBegin</a> method and +prior to an <a href="vtab.html#xcommit">xCommit</a> or <a href="vtab.html#xrollback">xRollback</a>. In order to implement two-phase +commit, the xSync method on all virtual tables is invoked prior to +invoking the <a href="vtab.html#xcommit">xCommit</a> method on any virtual table. If any of the +xSync methods fail, the entire transaction is rolled back. + +<a name="xcommit"></a> + +</p><h2 id="the_xcommit_method"><span>2.17. </span>The xCommit Method</h2> + +<div class="codeblock"><pre>int (*xCommit)(sqlite3_vtab *pVTab); +</pre></div> + +<p>This method causes a virtual table transaction to commit. +This is method is optional. The xCommit pointer of <a href="c3ref/module.html">sqlite3_module</a> +may be NULL. + +</p><p>A call to this method always follows a prior call to <a href="vtab.html#xBegin">xBegin</a> and +<a href="vtab.html#xsync">xSync</a>. + + +<a name="xrollback"></a> + +</p><h2 id="the_xrollback_method"><span>2.18. </span>The xRollback Method</h2> + +<div class="codeblock"><pre>int (*xRollback)(sqlite3_vtab *pVTab); +</pre></div> + +<p>This method causes a virtual table transaction to rollback. +This is method is optional. The xRollback pointer of <a href="c3ref/module.html">sqlite3_module</a> +may be NULL. + +</p><p>A call to this method always follows a prior call to <a href="vtab.html#xBegin">xBegin</a>. + + +<a name="xrename"></a> + +</p><h2 id="the_xrename_method"><span>2.19. </span>The xRename Method</h2> + +<div class="codeblock"><pre>int (*xRename)(sqlite3_vtab *pVtab, const char *zNew); +</pre></div> + +<p>This method provides notification that the virtual table implementation +that the virtual table will be given a new name. +If this method returns <a href="rescode.html#ok">SQLITE_OK</a> then SQLite renames the table. +If this method returns an <a href="rescode.html">error code</a> then the renaming is prevented. + +</p><p>The xRename method is optional. If omitted, then the virtual +table may not be renamed using the ALTER TABLE RENAME command. + +</p><p>The <a href="pragma.html#pragma_legacy_alter_table">PRAGMA legacy_alter_table</a> setting is enabled prior to invoking this +method, and the value for legacy_alter_table is restored after this +method finishes. This is necessary for the correct operation of virtual +tables that make use of <a href="vtab.html#xshadowname">shadow tables</a> where the shadow tables must be +renamed to match the new virtual table name. If the legacy_alter_format is +off, then the xConnect method will be invoked for the virtual table every +time the xRename method tries to change the name of the shadow table. + +<a name="xsavepoint"></a> + +</p><h2 id="the_xsavepoint_xrelease_and_xrollbackto_methods"><span>2.20. </span>The xSavepoint, xRelease, and xRollbackTo Methods</h2> + +<div class="codeblock"><pre>int (*xSavepoint)(sqlite3_vtab *pVtab, int); +int (*xRelease)(sqlite3_vtab *pVtab, int); +int (*xRollbackTo)(sqlite3_vtab *pVtab, int); +</pre></div> + +<p> +These methods provide the virtual table implementation an opportunity to +implement nested transactions. They are always optional and will only be +called in SQLite <a href="releaselog/3_7_7.html">version 3.7.7</a> (2011-06-23) and later. +</p> + +<p> +When xSavepoint(X,N) is invoked, that is a signal to the virtual table X +that it should save its current state as savepoint N. +A subsequent call +to xRollbackTo(X,R) means that the state of the virtual table should return +to what it was when xSavepoint(X,R) was last called. +The call +to xRollbackTo(X,R) will invalidate all savepoints with N>R; none of the +invalided savepoints will be rolled back or released without first +being reinitialized by a call to xSavepoint(). +A call to xRelease(X,M) invalidates all savepoints where N>=M. +</p> + +<p> +None of the xSavepoint(), xRelease(), or xRollbackTo() methods will ever +be called except in between calls to xBegin() and +either xCommit() or xRollback(). +</p> + +<a name="xshadowname"></a> + +<h2 id="the_xshadowname_method"><span>2.21. </span>The xShadowName Method</h2> + +<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 +use of real (non-virtual) database tables to store content. For example, +when content is inserted into the FTS3 virtual table, the data is ultimately +stored in real tables named "%_content", "%_segdir", "%_segments", "%_stat", +and "%_docsize" where "%" is the name of the original virtual table. This +auxiliary real tables that store content for a virtual table are called +"shadow tables". See +(<a href="fts3.html#*shadowtab">1</a>), +(<a href="fts5.html#fts5shadowtables">2</a>), and +(<a href="rtree.html#xshadow">3</a>) for additional information. + +</p><p>The xShadowName method exists to allow SQLite to determine whether a +certain real table is in fact a shadow table for a virtual table. + +</p><p>SQLite understands a real table to be a shadow table if all of +the following are true: +</p><p> +</p><ul> +<li> The name of the table contains one or more "_" characters. +</li><li> The part of the name prior to the last "_" exactly matches + the name of a virtual table that was created using <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a>. + (Shadow tables are not recognized for <a href="vtab.html#epovtab">eponymous virtual tables</a> + and <a href="vtab.html#tabfunc2">table-valued functions</a>.) +</li><li> The virtual table contains an xShadowName method. +</li><li> The xShadowName method returns true when its input is the part + of the table name past the last "_" character. +</li></ul> + +<p> +If SQLite recognizes a table as a shadow table, and if the +<a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigdefensive">SQLITE_DBCONFIG_DEFENSIVE</a> flag is set, then the shadow table is read-only +for ordinary SQL statements. The shadow table can still be written, but +only by SQL that is invoked from within one of the methods of +some virtual table implementation. + +</p><p> +The whole point of the xShadowName method is to protect the content of +shadow tables from being corrupted by hostile SQL. Every virtual table +implementation that uses shadow tables should be able to detect and cope +with corrupted shadow table content. However, bugs in particular virtual +table implementation might allow a deliberately corrupted shadow table to +cause a crash or other malfunction. The xShadowName mechanism seeks to +avoid zero-day exploits by preventing ordinary SQL statements from +deliberately corrupting shadow tables. + +</p><p> +Shadow tables are read/write by default. +Shadow tables only become read-only when the <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigdefensive">SQLITE_DBCONFIG_DEFENSIVE</a> +flag is set using <a href="c3ref/db_config.html">sqlite3_db_config()</a>. +Shadow tables need to be read/write by default in order to maintain +backwards compatibility. +For example, the SQL text generated by the <a href="cli.html#dump">.dump</a> command of the <a href="cli.html">CLI</a> +writes directly into shadow tables. +</p><p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/vtab.in?m=6780b9cf467a646d9">2022-12-05 12:38:46</a> UTC </small></i></p> + |