/ Check-in [a94e2f60]
Login

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

Overview
Comment:Add the optional non-found-callback to the swarm-vtab.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | union-vtab
Files: files | file ages | folders
SHA3-256: a94e2f600bc766fb459418e674b842628ba21e27cf9942c00cd533507d7b35fe
User & Date: drh 2017-08-04 20:15:08
Context
2017-08-04
20:27
Add the swarm virtual table to the existing union virtual table module. check-in: 0f6f6f03 user: drh tags: trunk
20:15
Add the optional non-found-callback to the swarm-vtab. Closed-Leaf check-in: a94e2f60 user: drh tags: union-vtab
17:39
Add further test cases for swarmvtab. And minor code changes. check-in: 0f82d3b9 user: dan tags: union-vtab
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/misc/unionvtab.c.

    32     32   ** implementation takes care of opening and closing database files
    33     33   ** automatically.
    34     34   **
    35     35   ** UNIONVTAB
    36     36   **
    37     37   **   A "unionvtab" virtual table is created as follows:
    38     38   **
    39         -**     CREATE VIRTUAL TABLE <name> USING unionvtab(<sql statement>);
           39  +**     CREATE VIRTUAL TABLE <name> USING unionvtab(<sql-statement>);
    40     40   **
    41     41   **   The implementation evalutes <sql statement> whenever a unionvtab virtual
    42     42   **   table is created or opened. It should return one row for each source
    43     43   **   database table. The four columns required of each row are:
    44     44   **
    45     45   **     1. The name of the database containing the table ("main" or "temp" or
    46     46   **        the name of an attached database). Or NULL to indicate that all
................................................................................
    54     54   **     4. The largest rowid in the range of rowids that may be stored in the
    55     55   **        database table (an integer).
    56     56   **
    57     57   ** SWARMVTAB
    58     58   **
    59     59   **   A "swarmvtab" virtual table is created similarly to a unionvtab table:
    60     60   **
    61         -**     CREATE VIRTUAL TABLE <name> USING swarmvtab(<sql statement>);
           61  +**     CREATE VIRTUAL TABLE <name>
           62  +**      USING swarmvtab(<sql-statement>, <callback>);
    62     63   **
    63     64   **   The difference is that for a swarmvtab table, the first column returned
    64     65   **   by the <sql statement> must return a path or URI that can be used to open
    65         -**   the database file containing the source table.
    66         -**
           66  +**   the database file containing the source table.  The <callback> option
           67  +**   is optional.  If included, it is the name of an application-defined
           68  +**   SQL function that is invoked with the URI of the file, if the file
           69  +**   does not already exist on disk.
    67     70   */
    68     71   
    69     72   #include "sqlite3ext.h"
    70     73   SQLITE_EXTENSION_INIT1
    71     74   #include <assert.h>
    72     75   #include <string.h>
    73     76   
................................................................................
   139    142     int bSwarm;                     /* 1 for "swarmvtab", 0 for "unionvtab" */
   140    143     int iPK;                        /* INTEGER PRIMARY KEY column, or -1 */
   141    144     int nSrc;                       /* Number of elements in the aSrc[] array */
   142    145     UnionSrc *aSrc;                 /* Array of source tables, sorted by rowid */
   143    146   
   144    147     /* Used by swarmvtab only */
   145    148     char *zSourceStr;               /* Expected unionSourceToStr() value */
          149  +  char *zNotFoundCallback;        /* UDF to invoke if file not found on open */
   146    150     UnionSrc *pClosable;            /* First in list of closable sources */
   147    151     int nOpen;                      /* Current number of open sources */
   148    152     int nMaxOpen;                   /* Maximum number of open sources */
   149    153   };
   150    154   
   151    155   /*
   152    156   ** Virtual table cursor type for union vtab.
................................................................................
   375    379         UnionSrc *pSrc = &pTab->aSrc[i];
   376    380         sqlite3_free(pSrc->zDb);
   377    381         sqlite3_free(pSrc->zTab);
   378    382         sqlite3_free(pSrc->zFile);
   379    383         sqlite3_close(pSrc->db);
   380    384       }
   381    385       sqlite3_free(pTab->zSourceStr);
          386  +    sqlite3_free(pTab->zNotFoundCallback);
   382    387       sqlite3_free(pTab->aSrc);
   383    388       sqlite3_free(pTab);
   384    389     }
   385    390     return SQLITE_OK;
   386    391   }
   387    392   
   388    393   /*
................................................................................
   487    492       sqlite3_free(z);
   488    493     }
   489    494     sqlite3_free(z0);
   490    495   
   491    496     return rc;
   492    497   }
   493    498   
          499  +
          500  +/*
          501  +** Try to open the swarmvtab database.  If initially unable, invoke the
          502  +** not-found callback UDF and then try again.
          503  +*/
          504  +static int unionOpenDatabaseInner(UnionTab *pTab, UnionSrc *pSrc, char **pzErr){
          505  +  int rc = SQLITE_OK;
          506  +  static const int openFlags = 
          507  +       SQLITE_OPEN_READONLY | SQLITE_OPEN_URI;
          508  +  rc = sqlite3_open_v2(pSrc->zFile, &pSrc->db, openFlags, 0);
          509  +  if( rc==SQLITE_OK ) return rc;
          510  +  if( pTab->zNotFoundCallback ){
          511  +    char *zSql = sqlite3_mprintf("SELECT \"%w\"(%Q);",
          512  +                    pTab->zNotFoundCallback, pSrc->zFile);
          513  +    if( zSql==0 ){
          514  +      *pzErr = sqlite3_mprintf("out of memory");
          515  +      return SQLITE_NOMEM;
          516  +    }
          517  +    rc = sqlite3_exec(pTab->db, zSql, 0, 0, pzErr);
          518  +    sqlite3_free(zSql);
          519  +    if( rc ) return rc;
          520  +    rc = sqlite3_open_v2(pSrc->zFile, &pSrc->db, openFlags, 0);
          521  +  }
          522  +  if( rc!=SQLITE_OK ){
          523  +    *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(pSrc->db));
          524  +  }
          525  +  return rc;
          526  +}
          527  +
   494    528   /*
   495    529   ** This function may only be called for swarmvtab tables. The results of
   496    530   ** calling it on a unionvtab table are undefined.
   497    531   **
   498    532   ** For a swarmvtab table, this function ensures that source database iSrc
   499    533   ** is open. If the database is opened successfully and the schema is as
   500    534   ** expected, or if it is already open when this function is called, SQLITE_OK
................................................................................
   509    543   static int unionOpenDatabase(UnionTab *pTab, int iSrc, char **pzErr){
   510    544     int rc = SQLITE_OK;
   511    545     UnionSrc *pSrc = &pTab->aSrc[iSrc];
   512    546   
   513    547     assert( pTab->bSwarm && iSrc<pTab->nSrc );
   514    548     if( pSrc->db==0 ){
   515    549       unionCloseSources(pTab, pTab->nMaxOpen-1);
   516         -    rc = sqlite3_open_v2(pSrc->zFile, &pSrc->db, SQLITE_OPEN_READONLY, 0);
   517         -    if( rc!=SQLITE_OK ){
   518         -      *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(pSrc->db));
   519         -    }else{
          550  +    rc = unionOpenDatabaseInner(pTab, pSrc, pzErr);
          551  +    if( rc==SQLITE_OK ){
   520    552         char *z = unionSourceToStr(&rc, pTab, pSrc, pzErr);
   521    553         if( rc==SQLITE_OK ){
   522    554           if( pTab->zSourceStr==0 ){
   523    555             pTab->zSourceStr = z;
   524    556           }else{
   525    557             if( sqlite3_stricmp(z, pTab->zSourceStr) ){
   526    558               *pzErr = sqlite3_mprintf("source table schema mismatch");
................................................................................
   594    626   }
   595    627   
   596    628   /* 
   597    629   ** xConnect/xCreate method.
   598    630   **
   599    631   ** The argv[] array contains the following:
   600    632   **
   601         -**   argv[0]   -> module name  ("unionvtab")
          633  +**   argv[0]   -> module name  ("unionvtab" or "swarmvtab")
   602    634   **   argv[1]   -> database name
   603    635   **   argv[2]   -> table name
   604    636   **   argv[3]   -> SQL statement
          637  +**   argv[4]   -> not-found callback UDF name
   605    638   */
   606    639   static int unionConnect(
   607    640     sqlite3 *db,
   608    641     void *pAux,
   609    642     int argc, const char *const*argv,
   610    643     sqlite3_vtab **ppVtab,
   611    644     char **pzErr
................................................................................
   615    648     int bSwarm = (pAux==0 ? 0 : 1);
   616    649     const char *zVtab = (bSwarm ? "swarmvtab" : "unionvtab");
   617    650   
   618    651     if( sqlite3_stricmp("temp", argv[1]) ){
   619    652       /* unionvtab tables may only be created in the temp schema */
   620    653       *pzErr = sqlite3_mprintf("%s tables must be created in TEMP schema", zVtab);
   621    654       rc = SQLITE_ERROR;
   622         -  }else if( argc!=4 ){
          655  +  }else if( argc!=4 && argc!=5 ){
   623    656       *pzErr = sqlite3_mprintf("wrong number of arguments for %s", zVtab);
   624    657       rc = SQLITE_ERROR;
   625    658     }else{
   626    659       int nAlloc = 0;               /* Allocated size of pTab->aSrc[] */
   627    660       sqlite3_stmt *pStmt = 0;      /* Argument statement */
   628    661       char *zArg = unionStrdup(&rc, argv[3]);      /* Copy of argument to CVT */
   629    662   
................................................................................
   680    713           }else{
   681    714             pSrc->zDb = unionStrdup(&rc, zDb);
   682    715           }
   683    716         }
   684    717       }
   685    718       unionFinalize(&rc, pStmt, pzErr);
   686    719       pStmt = 0;
          720  +
          721  +    /* Capture the not-found callback UDF name */
          722  +    if( argc>=5 ){
          723  +      pTab->zNotFoundCallback = unionStrdup(&rc, argv[4]);
          724  +      unionDequote(pTab->zNotFoundCallback);
          725  +    }
   687    726   
   688    727       /* It is an error if the SELECT statement returned zero rows. If only
   689    728       ** because there is no way to determine the schema of the virtual 
   690    729       ** table in this case.  */
   691    730       if( rc==SQLITE_OK && pTab->nSrc==0 ){
   692    731         *pzErr = sqlite3_mprintf("no source tables configured");
   693    732         rc = SQLITE_ERROR;

Added test/swarmvtab2.test.

            1  +# 2017-07-15
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.  The
           12  +# focus of this file is the "swarmvtab" extension
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +set testprefix swarmvtab
           18  +
           19  +ifcapable !vtab {
           20  +  finish_test
           21  +  return
           22  +}
           23  +
           24  +
           25  +db close
           26  +foreach name [glob -nocomplain test*.db] {
           27  +  forcedelete $name
           28  +}
           29  +sqlite3 db test.db
           30  +load_static_extension db unionvtab
           31  +proc create_database {filename} {
           32  +  sqlite3 dbx $filename
           33  +  set num [regsub -all {[^0-9]+} $filename {}]
           34  +  set num [string trimleft $num 0]
           35  +  set start [expr {$num*1000}]
           36  +  set end [expr {$start+999}]
           37  +  dbx eval {
           38  +    CREATE TABLE t2(a INTEGER PRIMARY KEY,b);
           39  +    WITH RECURSIVE c(x) AS (
           40  +      VALUES($start) UNION ALL SELECT x+1 FROM c WHERE x<$end
           41  +    )
           42  +    INSERT INTO t2(a,b) SELECT x, printf('**%05d**',x) FROM c;
           43  +  }
           44  +  dbx close
           45  +}
           46  +db func create_database create_database
           47  +do_execsql_test 100 {
           48  +  CREATE TABLE t1(filename, tablename, istart, iend);
           49  +  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<99)
           50  +  INSERT INTO t1 SELECT printf('test%03d.db',x),'t2',x*1000,x*1000+999 FROM c;
           51  +  CREATE VIRTUAL TABLE temp.v1 USING swarmvtab(
           52  +    'SELECT * FROM t1', 'create_database'
           53  +  );
           54  +} {}
           55  +do_execsql_test 110 {
           56  +  SELECT b FROM v1 WHERE a=3875;
           57  +} {**03875**}
           58  +do_test 120 {
           59  +  lsort [glob -nocomplain test?*.db]
           60  +} {test001.db test003.db}
           61  +do_execsql_test 130 {
           62  +  SELECT b FROM v1 WHERE a BETWEEN 3999 AND 4000 ORDER BY a;
           63  +} {**03999** **04000**}
           64  +do_test 140 {
           65  +  lsort [glob -nocomplain test?*.db]
           66  +} {test001.db test003.db test004.db}
           67  +do_execsql_test 150 {
           68  +  SELECT b FROM v1 WHERE a>=99998;
           69  +} {**99998** **99999**}
           70  +do_test 160 {
           71  +  lsort -dictionary [glob -nocomplain test?*.db]
           72  +} {test001.db test003.db test004.db test099.db}
           73  +
           74  +finish_test