1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
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=e40b220bf8ba3413c">2018-01-22 17:51:55</a> UTC </small></i></p>
|
