diff options
Diffstat (limited to '')
| -rw-r--r-- | www/swarmvtab.html | 429 |
1 files changed, 429 insertions, 0 deletions
diff --git a/www/swarmvtab.html b/www/swarmvtab.html new file mode 100644 index 0000000..f5409a1 --- /dev/null +++ b/www/swarmvtab.html @@ -0,0 +1,429 @@ +<!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>Swarmvtab Virtual Table</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"> +Swarmvtab Virtual Table +</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="#overview">1. Overview</a></div> +<div class="fancy-toc1"><a href="#compiling_and_using_swarmvtab">2. Compiling and Using Swarmvtab</a></div> +<div class="fancy-toc1"><a href="#advanced_usage">3. Advanced Usage</a></div> +<div class="fancy-toc2"><a href="#sql_parameters">3.1. SQL Parameters</a></div> +<div class="fancy-toc2"><a href="#the_maxopen_parameter">3.2. The "maxopen" Parameter</a></div> +<div class="fancy-toc2"><a href="#the_openclose_callback">3.3. The "openclose" Callback</a></div> +<div class="fancy-toc2"><a href="#the_missing_callback">3.4. The "missing" Callback</a></div> +<div class="fancy-toc2"><a href="#component_table_context_values">3.5. Component table "context" values</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> + + + + + +<a name="overview"></a> +<h1 tags="swarmvtab" id="overview"><span>1. </span>Overview</h1> + +<p>The "swarmvtab" virtual table allows the user to query a large number +of tables (hereafter "component" tables) with similar schemas but distinct +ranges of rowid values as if they were a single database table. The tables may +be (and usually are) located in different databases. Swarmvtab tables are +read-only. + +</p><p>Component tables must not be declared WITHOUT ROWID, and must all have +the same schema, but may have different names within their databases. In +this context, "the same schema" means that: + +</p><ul> + <li>All component tables must have the same set of columns, in the same + order. + </li><li>The types and default collation sequences attached to each column + must be the same for all component tables. + </li><li>All component tables must have the same PRIMARY KEY declaration (if any). +</li></ul> + +<p>A swarmvtab table has the same schema as each of its component tables. + +</p><p>A swarmvtab virtual table is created as follows: + +</p><div class="codeblock"><pre>CREATE VIRTUAL TABLE temp.<name> USING swarmvtab(<sql-statement>); +</pre></div> + +<p>Swarmvtab virtual tables must be created in the temp schema. Attempting +to create a swarmvtab in the main or an attached database is an error. + +</p><p>The SQL statement supplied as the argument to the CREATE VIRTUAL TABLE +statement is executed when the table is created. It must return either four +or five columns. Each row returned describes one of the component tables. The +first four columns are interpreted, from first to last, as: + +</p><ul> + <li> <b>Database URI</b>. A filename or URI that can be used to open the + database containing the component table. + + </li><li> <b>Table name</b>. The name of the component table within its database. + + </li><li> <b>Minimum rowid</b>. The smallest rowid value that the component + table may contain. + + </li><li> <b>Maximum rowid</b>. The smallest rowid value that the component + table may contain. +</li></ul> + +<p>The interpretation of the final column, if it is present, is +<a href="swarmvtab.html#component_table_context_values">described here</a>. + +</p><p>For example, say the SQL statement returns the following data when +executed: + +</p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0"> +<tr style="text-align:left"><th>Database URI</th><th>Table name</th><th>Minimum rowid</th><th>Maximum rowid +</th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>test.db1 </td><td>t1 </td><td>0 </td><td>10 +</td></tr><tr style="text-align:left"><td>test.db2 </td><td>t2 </td><td>11 </td><td>20 +</td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>test.db3 </td><td>t1 </td><td>21 </td><td>30 +</td></tr><tr style="text-align:left"><td>test.db4 </td><td>t1 </td><td>31 </td><td>40 +</td></tr></table> + +<p>and the user queries the swarmvtab table for the row with rowid value +25. The swarmvtab table will open database file "test.db3" and read the +data to return from table "t1" (as 25 falls within the range of rowids +assigned to table "t1" in "test.db3"). + +</p><p>Swarmvtab efficiently handles range and equality constraints on the +rowid (or other INTEGER PRIMARY KEY) field only. If a query does not +contain such a constraint, then swarmvtab finds the results by opening +each database in turn and linearly scanning the component table. Which +generates a correct result, but is often slow. + +</p><p>There must be no overlapping rowid ranges in the rows returned by +the SQL statement. It is an error if there are. + +</p><p>The swarmvtab implementation may open or close databases at any +point. By default, it attempts to limit the maximum number of +simultaneously open database files to nine. This is not a hard limit - +it is possible to construct a scenario that will cause swarmvtab to +exceed it. + +</p><a name="compiling_and_using_swarmvtab"></a> +<h1 tags="compilation" id="compiling_and_using_swarmvtab"><span>2. </span>Compiling and Using Swarmvtab</h1> + +<p>The code for the swarmvtab virtual table is found in the +ext/misc/unionvtab.c file of the main SQLite source tree. It may be compiled +into an SQLite <a href="loadext.html">loadable extension</a> using a command like: + +</p><div class="codeblock"><pre>gcc -g -fPIC -shared unionvtab.c -o unionvtab.so +</pre></div> + +<p>Alternatively, the unionvtab.c file may be compiled into the application. +In this case, the following function should be invoked to register the +extension with each new database connection: + +</p><div class="codeblock"><pre>int sqlite3_unionvtab_init(sqlite3 *db, void*, void*); +</pre></div> + +<p> The first argument passed should be the database handle to register the +extension with. The second and third arguments should both be passed 0. + +</p><p> The source file and entry point are named for "unionvtab" instead of +"swarmvtab". Unionvtab is a <a href="unionvtab.html">separately documented</a> virtual table +that is bundled with swarmvtab. + +</p><a name="advanced_usage"></a> +<h1 tags="advanced" id="advanced_usage"><span>3. </span>Advanced Usage</h1> + +<p>Most users of swarmvtab will only use the features described above. +This section describes features designed for more esoteric use cases. These +features all involve specifying extra optional parameters following the SQL +statement as part of the CREATE VIRTUAL TABLE command. An optional parameter +is specified using its name, followed by an "=" character, followed by an +optionally quoted value. Whitespace may separate the name, "=" character +and value. For example: + +</p><div class="codeblock"><pre>CREATE VIRTUAL TABLE temp.sv USING swarmvtab ( + 'SELECT ...', <i>-- the SELECT statement</i> + maxopen = 20, <i>-- An optional parameter</i> + missing='missing_udf' <i>-- Another optional parameter</i> +); +</pre></div> + +<p>The following sections describe the supported parameters. Specifying +an unrecognized parameter name is an error. + +</p><a name="sql_parameters"></a> +<h2 tags="sql parameters" id="sql_parameters"><span>3.1. </span>SQL Parameters</h2> + +<p>If a parameter name begins with a ":", then it is assumed to be a +value to bind to the SQL statement before executing it. The value is always +bound as text. It is an error if the specified SQL parameter does not +exist. For example: + +</p><div class="codeblock"><pre>CREATE VIRTUAL TABLE temp.x1 USING swarmvtab ( + "SELECT :dir || local_filename, tbl, min, max FROM components", + :dir = '/home/user/app/databases/' +); +</pre></div> + +<p>When the above CREATE VIRTUAL TABLE statement is executed, swarmvtab binds +the text value "/home/user/app/databases/" to the :dir parameter of the +SQL statement before executing it. + +</p><p>A single CREATE VIRTUAL TABLE statement may contain any number of SQL +parameters. + +</p><a name="the_maxopen_parameter"></a> +<h2 tags="maxopen parameter" id="the_maxopen_parameter"><span>3.2. </span>The "maxopen" Parameter</h2> + +<p>By default, swarmvtab attempts to limit the number of simultaneously +open databases to nine. This parameter allows that limit to be changed. +For example, to create a swarmvtab table that may hold up to 30 databases +open simultaneously: + +</p><div class="codeblock"><pre>CREATE VIRTUAL TABLE temp.x1 USING swarmvtab ( + "SELECT ...", + maxopen=30 +); +</pre></div> + +<p>Raising the number of open databases may improve performance in some +scenarios. + +</p><a name="the_openclose_callback"></a> +<h2 tags="openclose callback" id="the_openclose_callback"><span>3.3. </span>The "openclose" Callback</h2> + +<p>The "openclose" parameter allows the user to specify the name of a +<a href="appfunc.html">application-defined SQL function</a> that will be invoked just before +swarmvtab opens a database, and again just after it closes one. The first +argument passed to the open close function is the filename or URI +identifying the database to be opened or just recently closed (the same +value returned in the leftmost column of the SQL statement provided to +the CREATE VIRTUAL TABLE command). The second argument is integer value +0 when the function is invoked before opening a database, and 1 when it +is invoked after one is closed. For example, if: + +</p><div class="codeblock"><pre>CREATE VIRTUAL TABLE temp.x1 USING swarmvtab ( + "SELECT ...", + openclose = 'openclose_udf' +); +</pre></div> + +<p>then before each database containing a component table is opened, +swarmvtab effectively executes: + +</p><div class="codeblock"><pre>SELECT openclose_udf(<database-name>, 0); +</pre></div> + +<p>After a database is closed, swarmvtab runs the equivalent of: + +</p><div class="codeblock"><pre>SELECT openclose_udf(<database-name>, 1); +</pre></div> + +<p>Any value returned by the openclose function is ignored. If an invocation +made before opening a database returns an error, then the database file is +not opened and the error returned to the user. This is the only scenario +in which swarmvtab will issue an "open" invocation without also eventually +issuing a corresponding "close" call. If there are still databases open, +"close" calls may be issued from within the eventual sqlite3_close() call +on the applications database that deletes the temp schema in which the +swarmvtab table resides. + +</p><p>Errors returned by "close" invocations are always ignored. + +</p><a name="the_missing_callback"></a> +<h2 tags="missing callback" id="the_missing_callback"><span>3.4. </span>The "missing" Callback</h2> + +<p>The "missing" parameter allows the user to specify the name of a +<a href="appfunc.html">application-defined SQL function</a> that will be invoked just before +swarmvtab opens a database if it finds that the required database file +is not present on disk. This provides the application with an opportunity +to retrieve the required database from a remote source before swarmvtab +attempts to open it. The only argument passed to the "missing" function +is the name or URI that identifies the database being opened. Assuming: + +</p><div class="codeblock"><pre>CREATE VIRTUAL TABLE temp.x1 USING swarmvtab ( + "SELECT ...", + openclose = 'openclose_udf', + missing='missing_udf' +); +</pre></div> + +<p>then the missing function is invoked as follows: + +</p><div class="codeblock"><pre>SELECT missing_udf(<database-name>); +</pre></div> + +<p>If the missing function returns an error, then the database is not +opened and the error returned to the user. If an openclose function is +configured, then a "close" invocation is issued at this point to match +the earlier "open". The following pseudo-code illustrates the procedure used +by a swarmvtab instance with both missing and openclose functions configured +when a component database is opened. + +</p><div class="codeblock"><pre>SELECT openclose_udf(<database-name>, 0); +if( error ) return error; +if( db does not exist ){ + SELECT missing_udf(<database-name>); + if( error ){ + SELECT openclose_udf(<database-name>, 1); + return error; + } +} +sqlite3_open_v2(<database-name>); +if( error ){ + SELECT openclose_udf(<database-name>, 1); + return error; +} +// db successfully opened! +</pre></div> + +<a name="component_table_context_values"></a> +<h2 tags="swarmvtab context" id="component_table_context_values"><span>3.5. </span>Component table "context" values</h2> + +<p> If the SELECT statement specified as part of the CREATE VIRTUAL +TABLE command returns five columns, then the final column is used +for application context only. Swarmvtab does not use this value at +all, except that it is passed after <database-name> to both +the openclose and missing functions, if specified. In other words, +instead of invoking the functions as described above, if the "context" +column is present swarmvtab instead invokes: + +</p><div class="codeblock"><pre>SELECT missing_udf(<database-name>, <context>); +SELECT openclose_udf(<database-name>, <context>, 0); +SELECT openclose_udf(<database-name>, <context>, 1); +</pre></div> + +<p>as required. +</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/swarmvtab.in?m=e40b220bf8">2022-01-08 05:02:57</a> UTC </small></i></p> + |