Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add documentation for the "swarmvtab" extension. |
---|---|
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
9cc758b32637b3e8c7504d1ec9c89551 |
User & Date: | dan 2017-12-16 17:20:11 |
Context
2017-12-21
| ||
18:50 | Add docs for experimental ".expert" command. check-in: 7f57c342de user: dan tags: trunk | |
2017-12-19
| ||
17:34 | Add docs for a proposed addition to the SQLite shell tool. Closed-Leaf check-in: cd6f445782 user: dan tags: expert-in-shell | |
2017-12-16
| ||
17:20 | Add documentation for the "swarmvtab" extension. check-in: 9cc758b326 user: dan tags: trunk | |
2017-12-12
| ||
01:16 | Changes to walformat.html document. check-in: c0a1fede37 user: drh tags: trunk | |
Changes
Added pages/swarmvtab.in.
1 + 2 +<title>Swarmvtab Virtual Table</title> 3 + 4 +<table_of_contents> 5 + 6 +<h1 tags="swarmvtab">Overview</h1> 7 + 8 +<p>The "swarmvtab" virtual table allows the user to query a large number 9 +of tables (hereafter "component" tables) with similar schemas but distinct 10 +ranges of rowid values as if they were a single database table. The tables may 11 +be (and usually are) located in different databases. Swarmvtab tables are 12 +read-only. 13 + 14 +<p>Component tables must not be declared WITHOUT ROWID, and must all have 15 +the same schema, but may have different names within their databases. In 16 +this context, "the same schema" means that: 17 + 18 +<ul> 19 + <li>All component tables must have the same set of columns, in the same 20 + order. 21 + <li>The types and default collation sequences attached to each column 22 + must be the same for all component tables. 23 + <li>All component tables must have the same PRIMARY KEY declaration (if any). 24 +</ul> 25 + 26 +<p>A swarmvtab table has the same schema as each of its component tables. 27 + 28 +<p>A swarmvtab virtual table is created as follows: 29 + 30 +<codeblock> 31 + CREATE VIRTUAL TABLE temp.<name> USING swarmvtab(<sql-statement>); 32 +</codeblock> 33 + 34 +<p>Swarmvtab virtual tables must be created in the temp schema. Attempting 35 +to create a swarmvtab in the main or an attached database is an error. 36 + 37 +<p>The SQL statement supplied as the argument to the CREATE VIRTUAL TABLE 38 +statement is executed when the table is created. It must return either four 39 +or five columns. Each row returned describes one of the component tables. The 40 +first four columns are interpreted, from first to last, as: 41 + 42 +<ul> 43 + <li> <b>Database URI</b>. A filename or URI that can be used to open the 44 + database containing the component table. 45 + 46 + <li> <b>Table name</b>. The name of the component table within its database. 47 + 48 + <li> <b>Minimum rowid</b>. The smallest rowid value that the component 49 + table may contain. 50 + 51 + <li> <b>Maximum rowid</b>. The smallest rowid value that the component 52 + table may contain. 53 +</ul> 54 + 55 +<p>The interpretation of the the final column, if it is present, is 56 +[swarmvtab context|described here]. 57 + 58 +<p>For example, say the SQL statement returns the following data when 59 +executed: 60 + 61 +<table striped=1> 62 +<tr><th>Database URI<th>Table name<th>Mimimum rowid<th>Maximum rowid 63 +<tr><td>test.db1 <td>t1 <td>0 <td>10 64 +<tr><td>test.db2 <td>t2 <td>11 <td>20 65 +<tr><td>test.db3 <td>t1 <td>21 <td>30 66 +<tr><td>test.db4 <td>t1 <td>31 <td>40 67 +</table> 68 + 69 +<p>and the user queries the swarmvtab table for the row with rowid value 70 +25. The swarmvtab table will open database file "test.db3" and read the 71 +data to return from table "t1" (as 25 falls within the range of rowids 72 +assigned to table "t1" in "test.db3"). 73 + 74 +<p>Swarmvtab efficiently handles range and equality constraints on the 75 +rowid (or other INTEGER PRIMARY KEY) field only. If a query does not 76 +contain such a constraint, then swarmvtab finds the results by opening 77 +each database in turn and linearly scanning the component table. Which 78 +generates a correct result, but is often slow. 79 + 80 +<p>There must be no overlapping rowid ranges in the rows returned by 81 +the SQL statement. It is an error if there are. 82 + 83 +<p>The swarmvtab implementation may open or close databases at any 84 +point. By default, it attempts to limit the maximum number of 85 +simultaneously open database files to nine. This is not a hard limit - 86 +it is possible to construct a scenario that will cause swarmvtab to 87 +exceed it. 88 + 89 +<h1 tags="compilation">Compiling and Using Swarmvtab</h1> 90 + 91 +<p>The code for the swarmvtab virtual table is found in the 92 +ext/misc/unionvtab.c file of the main SQLite source tree. It may be compiled 93 +into an SQLite [loadable extension] using a command like: 94 + 95 +<codeblock> 96 + gcc -g -fPIC -shared unionvtab.c -o unionvtab.so 97 +</codeblock> 98 + 99 +<p>Alternatively, the unionvtab.c file may be compiled into the application. 100 +In this case, the following function should be invoked to register the 101 +extension with each new database connection: 102 + 103 +<codeblock> 104 + int sqlite3_unionvtab_init(sqlite3 *db, void*, void*); 105 +</codeblock> 106 + 107 +<p> The first argument passed should be the database handle to register the 108 +extension with. The second and third arguments should both be passed 0. 109 + 110 +<p> The source file and entry point are named for "unionvtab" instead of 111 +"swarmvtab". Unionvtab is a [unionvtab|separately documented] virtual table 112 +that is bundled with swarmvtab. 113 + 114 +<h1 tags="advanced">Advanced Usage</h1> 115 + 116 +<p>Most users of swarmvtab will only use the features described above. 117 +This section describes features designed for more esoteric use cases. These 118 +features all involve specifying extra optional parameters following the SQL 119 +statement as part of the CREATE VIRTUAL TABLE command. An optional parameter 120 +is specified using its name, followed by an "=" character, followed by an 121 +optionally quoted value. Whitespace may separate the name, "=" character 122 +and value. For example: 123 + 124 +<codeblock> 125 + CREATE VIRTUAL TABLE temp.sv USING swarmvtab ( 126 + 'SELECT ...', <i>-- the SELECT statement</i> 127 + maxopen = 20, <i>-- An optional parameter</i> 128 + missing='missing_udf' <i>-- Another optional parameter</i> 129 + ); 130 +</codeblock> 131 + 132 +<p>The following sections describe the supported parameters. Specifying 133 +an unrecognized parameter name is an error. 134 + 135 +<h2 tags="sql parameters">SQL Parameters</h2> 136 + 137 +<p>If a parameter name begins with a ":", then it is assumed to be a 138 +value to bind to the SQL statement before executing it. The value is always 139 +bound as text. It is an error if the specified SQL parameter does not 140 +exist. For example: 141 + 142 +<codeblock> 143 + CREATE VIRTUAL TABLE temp.x1 USING swarmvtab ( 144 + "SELECT :dir || local_filename, tbl, min, max FROM components", 145 + :dir = '/home/user/app/databases/' 146 + ); 147 +</codeblock> 148 + 149 +<p>When the above CREATE VIRTUAL TABLE statement is executed, swarmvtab binds 150 +the text value "/home/user/app/databases/" to the :dir parameter of the 151 +SQL statement before executing it. 152 + 153 +<p>A single CREATE VIRTUAL TABLE statement may contain any number of SQL 154 +parameters. 155 + 156 +<h2 tags="maxopen parameter">The "maxopen" Parameter</h2> 157 + 158 +<p>By default, swarmvtab attempts to limit the number of simultaneously 159 +open databases to nine. This parameter allows that limit to be changed. 160 +For example, to create a swarmvtab table that may hold up to 30 databases 161 +open simultaneously: 162 + 163 +<codeblock> 164 + CREATE VIRTUAL TABLE temp.x1 USING swarmvtab ( 165 + "SELECT ...", 166 + maxopen=30 167 + ); 168 +</codeblock> 169 + 170 +<p>Raising the number of open databases may improve performance in some 171 +scenarios. 172 + 173 +<h2 tags="openclose callback">The "openclose" Callback</h2> 174 + 175 +<p>The "openclose" parameter allows the user to specify the name of a 176 +[application-defined SQL function] that will be invoked just before 177 +swarmvtab opens a database, and again just after it closes one. The first 178 +argument passed to the open close function is the filename or URI 179 +identifying the database to be opened or just recently closed (the same 180 +value returned in the leftmost column of the SQL statement provided to 181 +the CREATE VIRTUAL TABLE command). The second argument is integer value 182 +0 when the function is invoked before opening a database, and 1 when it 183 +is invoked after one is closed. For example, if: 184 + 185 +<codeblock> 186 + CREATE VIRTUAL TABLE temp.x1 USING swarmvtab ( 187 + "SELECT ...", 188 + openclose = 'openclose_udf' 189 + ); 190 +</codeblock> 191 + 192 +<p>then before each database containing a component table is opened, 193 +swarmvtab effectively executes: 194 + 195 +<codeblock> 196 + SELECT openclose_udf(<database-name>, 0); 197 +</codeblock> 198 + 199 +<p>After a database is closed, swarmvtab runs the equivalent of: 200 + 201 +<codeblock> 202 + SELECT openclose_udf(<database-name>, 1); 203 +</codeblock> 204 + 205 +<p>Any value returned by the openclose function is ignored. If an invocation 206 +made before opening a database returns an error, then the database file is 207 +not opened and the error returned to the user. This is the only scenario 208 +in which swarmvtab will issue an "open" invocation without also eventually 209 +issuing a corresponding "close" call. If there are still databases open, 210 +"close" calls may be issued from within the eventual sqlite3_close() call 211 +on the applications database that deletes the temp schema in which the 212 +swarmvtab table resides. 213 + 214 +<p>Errors returned by "close" invocations are always ignored. 215 + 216 +<h2 tags="missing callback">The "missing" Callback</h2> 217 + 218 +<p>The "missing" parameter allows the user to specify the name of a 219 +[application-defined SQL function] that will be invoked just before 220 +swarmvtab opens a database if it finds that the required database file 221 +is not present on disk. This provides the application with an opportunity 222 +to retrieve the required database from a remote source before swarmvtab 223 +attempts to open it. The only argument passed to the "missing" function 224 +is the name or URI that identifies the database being opened. Assuming: 225 + 226 +<codeblock> 227 + CREATE VIRTUAL TABLE temp.x1 USING swarmvtab ( 228 + "SELECT ...", 229 + openclose = 'openclose_udf', 230 + missing='missing_udf' 231 + ); 232 +</codeblock> 233 + 234 +<p>then the missing function is invoked as follows: 235 + 236 +<codeblock> 237 + SELECT missing_udf(<database-name>); 238 +</codeblock> 239 + 240 +<p>If the missing function returns an error, then the database is not 241 +opened and the error returned to the user. If an openclose function is 242 +configured, then a "close" invocation is issued at this point to match 243 +the earlier "open". The following pseudo-code illustrates the procedure used 244 +by a swarmvtab instance with both missing and openclose functions configured 245 +when a component database is opened. 246 + 247 +<codeblock> 248 + SELECT openclose_udf(<database-name>, 0); 249 + if( error ) return error; 250 + if( db does not exist ){ 251 + SELECT missing_udf(<database-name>); 252 + if( error ){ 253 + SELECT openclose_udf(<database-name>, 1); 254 + return error; 255 + } 256 + } 257 + sqlite3_open_v2(<database-name>); 258 + if( error ){ 259 + SELECT openclose_udf(<database-name>, 1); 260 + return error; 261 + } 262 + // db successfully opened! 263 +</codeblock> 264 + 265 +<h2 tags="swarmvtab context">Component table "context" values</h2> 266 + 267 +<p> If the SELECT statement specified as part of the CREATE VIRTUAL 268 +TABLE command returns five columns, then the final column is used 269 +for application context only. Swarmvtab does not use this value at 270 +all, except that it is passed after <database-name> to both 271 +the openclose and missing functions, if specified. In other words, 272 +instead of invoking the functions as described above, if the "context" 273 +column is present swarmvtab instead invokes: 274 + 275 +<codeblock> 276 + SELECT missing_udf(<database-name>, <context>); 277 + SELECT openclose_udf(<database-name>, <context>, 0); 278 + SELECT openclose_udf(<database-name>, <context>, 1); 279 +</codeblock> 280 + 281 +<p>as required. 282 +