Documentation Source Text

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

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.&lt;name&gt; USING swarmvtab(&lt;sql-statement&gt;);
           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(&lt;database-name&gt;, 0);
          197  +</codeblock>
          198  +
          199  +<p>After a database is closed, swarmvtab runs the equivalent of:
          200  +
          201  +<codeblock>
          202  +  SELECT openclose_udf(&lt;database-name&gt;, 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(&lt;database-name&gt;);
          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(&lt;database-name&gt;, 0);
          249  +  if( error ) return error;
          250  +  if( db does not exist ){
          251  +    SELECT missing_udf(&lt;database-name&gt;);
          252  +    if( error ){
          253  +      SELECT openclose_udf(&lt;database-name&gt;, 1);
          254  +      return error;
          255  +    }
          256  +  }
          257  +  sqlite3_open_v2(&lt;database-name&gt;);
          258  +  if( error ){
          259  +    SELECT openclose_udf(&lt;database-name&gt;, 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 &lt;database-name&gt; 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(&lt;database-name&gt;, &lt;context&gt;);
          277  +  SELECT openclose_udf(&lt;database-name&gt;, &lt;context&gt;, 0);
          278  +  SELECT openclose_udf(&lt;database-name&gt;, &lt;context&gt;, 1);
          279  +</codeblock>
          280  +
          281  +<p>as required.
          282  +