Debian Codegraph @

www/swarmvtab.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>Swarmvtab Virtual Table</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118 <div class=fancy>
119 <div class=nosearch>
120 <div class="fancy_title">
121 Swarmvtab Virtual Table
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#overview">1. Overview</a></div>
126 <div class="fancy-toc1"><a href="#compiling_and_using_swarmvtab">2. Compiling and Using Swarmvtab</a></div>
127 <div class="fancy-toc1"><a href="#advanced_usage">3. Advanced Usage</a></div>
128 <div class="fancy-toc2"><a href="#sql_parameters">3.1. SQL Parameters</a></div>
129 <div class="fancy-toc2"><a href="#the_maxopen_parameter">3.2. The "maxopen" Parameter</a></div>
130 <div class="fancy-toc2"><a href="#the_openclose_callback">3.3. The "openclose" Callback</a></div>
131 <div class="fancy-toc2"><a href="#the_missing_callback">3.4. The "missing" Callback</a></div>
132 <div class="fancy-toc2"><a href="#component_table_context_values">3.5. Component table "context" values</a></div>
133 </div>
134 </details>
135 </div>
136
137
138
139
140
141 <a name="overview"></a>
142 <h1 tags="swarmvtab" id="overview"><span>1. </span>Overview</h1>
143
144 <p>The "swarmvtab" virtual table allows the user to query a large number
145 of tables (hereafter "component" tables) with similar schemas but distinct
146 ranges of rowid values as if they were a single database table. The tables may
147 be (and usually are) located in different databases. Swarmvtab tables are
148 read-only.
149
150 </p><p>Component tables must not be declared WITHOUT ROWID, and must all have
151 the same schema, but may have different names within their databases. In
152 this context, "the same schema" means that:
153
154 </p><ul>
155 <li>All component tables must have the same set of columns, in the same
156 order.
157 </li><li>The types and default collation sequences attached to each column
158 must be the same for all component tables.
159 </li><li>All component tables must have the same PRIMARY KEY declaration (if any).
160 </li></ul>
161
162 <p>A swarmvtab table has the same schema as each of its component tables.
163
164 </p><p>A swarmvtab virtual table is created as follows:
165
166 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE temp.&lt;name&gt; USING swarmvtab(&lt;sql-statement&gt;);
167 </pre></div>
168
169 <p>Swarmvtab virtual tables must be created in the temp schema. Attempting
170 to create a swarmvtab in the main or an attached database is an error.
171
172 </p><p>The SQL statement supplied as the argument to the CREATE VIRTUAL TABLE
173 statement is executed when the table is created. It must return either four
174 or five columns. Each row returned describes one of the component tables. The
175 first four columns are interpreted, from first to last, as:
176
177 </p><ul>
178 <li> <b>Database URI</b>. A filename or URI that can be used to open the
179 database containing the component table.
180
181 </li><li> <b>Table name</b>. The name of the component table within its database.
182
183 </li><li> <b>Minimum rowid</b>. The smallest rowid value that the component
184 table may contain.
185
186 </li><li> <b>Maximum rowid</b>. The largest rowid value that the component
187 table may contain.
188 </li></ul>
189
190 <p>The interpretation of the final column, if it is present, is
191 <a href="swarmvtab.html#component_table_context_values">described here</a>.
192
193 </p><p>For example, say the SQL statement returns the following data when
194 executed:
195
196 </p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0">
197 <tr style="text-align:left"><th>Database URI</th><th>Table name</th><th>Minimum rowid</th><th>Maximum rowid
198 </th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>test.db1 </td><td>t1 </td><td>0 </td><td>10
199 </td></tr><tr style="text-align:left"><td>test.db2 </td><td>t2 </td><td>11 </td><td>20
200 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>test.db3 </td><td>t1 </td><td>21 </td><td>30
201 </td></tr><tr style="text-align:left"><td>test.db4 </td><td>t1 </td><td>31 </td><td>40
202 </td></tr></table>
203
204 <p>and the user queries the swarmvtab table for the row with rowid value
205 25. The swarmvtab table will open database file "test.db3" and read the
206 data to return from table "t1" (as 25 falls within the range of rowids
207 assigned to table "t1" in "test.db3").
208
209 </p><p>Swarmvtab efficiently handles range and equality constraints on the
210 rowid (or other INTEGER PRIMARY KEY) field only. If a query does not
211 contain such a constraint, then swarmvtab finds the results by opening
212 each database in turn and linearly scanning the component table. Which
213 generates a correct result, but is often slow.
214
215 </p><p>There must be no overlapping rowid ranges in the rows returned by
216 the SQL statement. It is an error if there are.
217
218 </p><p>The swarmvtab implementation may open or close databases at any
219 point. By default, it attempts to limit the maximum number of
220 simultaneously open database files to nine. This is not a hard limit -
221 it is possible to construct a scenario that will cause swarmvtab to
222 exceed it.
223
224 </p><a name="compiling_and_using_swarmvtab"></a>
225 <h1 tags="compilation" id="compiling_and_using_swarmvtab"><span>2. </span>Compiling and Using Swarmvtab</h1>
226
227 <p>The code for the swarmvtab virtual table is found in the
228 ext/misc/unionvtab.c file of the main SQLite source tree. It may be compiled
229 into an SQLite <a href="loadext.html">loadable extension</a> using a command like:
230
231 </p><div class="codeblock"><pre>gcc -g -fPIC -shared unionvtab.c -o unionvtab.so
232 </pre></div>
233
234 <p>Alternatively, the unionvtab.c file may be compiled into the application.
235 In this case, the following function should be invoked to register the
236 extension with each new database connection:
237
238 </p><div class="codeblock"><pre>int sqlite3_unionvtab_init(sqlite3 *db, void*, void*);
239 </pre></div>
240
241 <p> The first argument passed should be the database handle to register the
242 extension with. The second and third arguments should both be passed 0.
243
244 </p><p> The source file and entry point are named for "unionvtab" instead of
245 "swarmvtab". Unionvtab is a <a href="unionvtab.html">separately documented</a> virtual table
246 that is bundled with swarmvtab.
247
248 </p><a name="advanced_usage"></a>
249 <h1 tags="advanced" id="advanced_usage"><span>3. </span>Advanced Usage</h1>
250
251 <p>Most users of swarmvtab will only use the features described above.
252 This section describes features designed for more esoteric use cases. These
253 features all involve specifying extra optional parameters following the SQL
254 statement as part of the CREATE VIRTUAL TABLE command. An optional parameter
255 is specified using its name, followed by an "=" character, followed by an
256 optionally quoted value. Whitespace may separate the name, "=" character
257 and value. For example:
258
259 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE temp.sv USING swarmvtab (
260 'SELECT ...', <i>-- the SELECT statement</i>
261 maxopen = 20, <i>-- An optional parameter</i>
262 missing='missing_udf' <i>-- Another optional parameter</i>
263 );
264 </pre></div>
265
266 <p>The following sections describe the supported parameters. Specifying
267 an unrecognized parameter name is an error.
268
269 </p><a name="sql_parameters"></a>
270 <h2 tags="sql parameters" id="sql_parameters"><span>3.1. </span>SQL Parameters</h2>
271
272 <p>If a parameter name begins with a ":", then it is assumed to be a
273 value to bind to the SQL statement before executing it. The value is always
274 bound as text. It is an error if the specified SQL parameter does not
275 exist. For example:
276
277 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE temp.x1 USING swarmvtab (
278 "SELECT :dir || local_filename, tbl, min, max FROM components",
279 :dir = '/home/user/app/databases/'
280 );
281 </pre></div>
282
283 <p>When the above CREATE VIRTUAL TABLE statement is executed, swarmvtab binds
284 the text value "/home/user/app/databases/" to the :dir parameter of the
285 SQL statement before executing it.
286
287 </p><p>A single CREATE VIRTUAL TABLE statement may contain any number of SQL
288 parameters.
289
290 </p><a name="the_maxopen_parameter"></a>
291 <h2 tags="maxopen parameter" id="the_maxopen_parameter"><span>3.2. </span>The "maxopen" Parameter</h2>
292
293 <p>By default, swarmvtab attempts to limit the number of simultaneously
294 open databases to nine. This parameter allows that limit to be changed.
295 For example, to create a swarmvtab table that may hold up to 30 databases
296 open simultaneously:
297
298 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE temp.x1 USING swarmvtab (
299 "SELECT ...",
300 maxopen=30
301 );
302 </pre></div>
303
304 <p>Raising the number of open databases may improve performance in some
305 scenarios.
306
307 </p><a name="the_openclose_callback"></a>
308 <h2 tags="openclose callback" id="the_openclose_callback"><span>3.3. </span>The "openclose" Callback</h2>
309
310 <p>The "openclose" parameter allows the user to specify the name of a
311 <a href="appfunc.html">application-defined SQL function</a> that will be invoked just before
312 swarmvtab opens a database, and again just after it closes one. The first
313 argument passed to the open close function is the filename or URI
314 identifying the database to be opened or just recently closed (the same
315 value returned in the leftmost column of the SQL statement provided to
316 the CREATE VIRTUAL TABLE command). The second argument is integer value
317 0 when the function is invoked before opening a database, and 1 when it
318 is invoked after one is closed. For example, if:
319
320 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE temp.x1 USING swarmvtab (
321 "SELECT ...",
322 openclose = 'openclose_udf'
323 );
324 </pre></div>
325
326 <p>then before each database containing a component table is opened,
327 swarmvtab effectively executes:
328
329 </p><div class="codeblock"><pre>SELECT openclose_udf(&lt;database-name&gt;, 0);
330 </pre></div>
331
332 <p>After a database is closed, swarmvtab runs the equivalent of:
333
334 </p><div class="codeblock"><pre>SELECT openclose_udf(&lt;database-name&gt;, 1);
335 </pre></div>
336
337 <p>Any value returned by the openclose function is ignored. If an invocation
338 made before opening a database returns an error, then the database file is
339 not opened and the error returned to the user. This is the only scenario
340 in which swarmvtab will issue an "open" invocation without also eventually
341 issuing a corresponding "close" call. If there are still databases open,
342 "close" calls may be issued from within the eventual sqlite3_close() call
343 on the applications database that deletes the temp schema in which the
344 swarmvtab table resides.
345
346 </p><p>Errors returned by "close" invocations are always ignored.
347
348 </p><a name="the_missing_callback"></a>
349 <h2 tags="missing callback" id="the_missing_callback"><span>3.4. </span>The "missing" Callback</h2>
350
351 <p>The "missing" parameter allows the user to specify the name of a
352 <a href="appfunc.html">application-defined SQL function</a> that will be invoked just before
353 swarmvtab opens a database if it finds that the required database file
354 is not present on disk. This provides the application with an opportunity
355 to retrieve the required database from a remote source before swarmvtab
356 attempts to open it. The only argument passed to the "missing" function
357 is the name or URI that identifies the database being opened. Assuming:
358
359 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE temp.x1 USING swarmvtab (
360 "SELECT ...",
361 openclose = 'openclose_udf',
362 missing='missing_udf'
363 );
364 </pre></div>
365
366 <p>then the missing function is invoked as follows:
367
368 </p><div class="codeblock"><pre>SELECT missing_udf(&lt;database-name&gt;);
369 </pre></div>
370
371 <p>If the missing function returns an error, then the database is not
372 opened and the error returned to the user. If an openclose function is
373 configured, then a "close" invocation is issued at this point to match
374 the earlier "open". The following pseudo-code illustrates the procedure used
375 by a swarmvtab instance with both missing and openclose functions configured
376 when a component database is opened.
377
378 </p><div class="codeblock"><pre>SELECT openclose_udf(&lt;database-name&gt;, 0);
379 if( error ) return error;
380 if( db does not exist ){
381 SELECT missing_udf(&lt;database-name&gt;);
382 if( error ){
383 SELECT openclose_udf(&lt;database-name&gt;, 1);
384 return error;
385 }
386 }
387 sqlite3_open_v2(&lt;database-name&gt;);
388 if( error ){
389 SELECT openclose_udf(&lt;database-name&gt;, 1);
390 return error;
391 }
392 // db successfully opened!
393 </pre></div>
394
395 <a name="component_table_context_values"></a>
396 <h2 tags="swarmvtab context" id="component_table_context_values"><span>3.5. </span>Component table "context" values</h2>
397
398 <p> If the SELECT statement specified as part of the CREATE VIRTUAL
399 TABLE command returns five columns, then the final column is used
400 for application context only. Swarmvtab does not use this value at
401 all, except that it is passed after &lt;database-name&gt; to both
402 the openclose and missing functions, if specified. In other words,
403 instead of invoking the functions as described above, if the "context"
404 column is present swarmvtab instead invokes:
405
406 </p><div class="codeblock"><pre>SELECT missing_udf(&lt;database-name&gt;, &lt;context&gt;);
407 SELECT openclose_udf(&lt;database-name&gt;, &lt;context&gt;, 0);
408 SELECT openclose_udf(&lt;database-name&gt;, &lt;context&gt;, 1);
409 </pre></div>
410
411 <p>as required.
412 </p><p align="center"><small><i>This page was last updated on 2025-06-12 09:10:23Z </small></i></p>
413