/ Check-in [7ae20eac]
Login

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

Overview
Comment:Add test cases and associated fixes for swarmvtab.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | union-vtab
Files: files | file ages | folders
SHA3-256: 7ae20eac83fc053dc1bbc42501dd41f77445a6b9a33cfa42b899fc7a18c637ab
User & Date: dan 2017-08-04 16:16:32
Context
2017-08-04
17:39
Add further test cases for swarmvtab. And minor code changes. check-in: 0f82d3b9 user: dan tags: union-vtab
16:16
Add test cases and associated fixes for swarmvtab. check-in: 7ae20eac user: dan tags: union-vtab
2017-08-03
20:13
Modify swarmvtab to use a separate database connection for each database file. check-in: 1f05ad29 user: dan tags: union-vtab
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/misc/unionvtab.c.

     6      6   **
     7      7   **    May you do good and not evil.
     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   **
    13         -** This file contains the implementation of the "unionvtab" virtual
    14         -** table. This module provides read-only access to multiple tables, 
           13  +** This file contains the implementation of the "unionvtab" and "swarmvtab"
           14  +** virtual tables. These modules provide read-only access to multiple tables,
    15     15   ** possibly in multiple database files, via a single database object.
    16     16   ** The source tables must have the following characteristics:
    17     17   **
    18     18   **   * They must all be rowid tables (not VIRTUAL or WITHOUT ROWID
    19     19   **     tables or views).
    20     20   **
    21     21   **   * Each table must have the same set of columns, declared in
    22     22   **     the same order and with the same declared types.
    23     23   **
    24     24   **   * The tables must not feature a user-defined column named "_rowid_".
    25     25   **
    26     26   **   * Each table must contain a distinct range of rowid values.
    27     27   **
    28         -** A "unionvtab" virtual table is created as follows:
    29         -**
    30         -**   CREATE VIRTUAL TABLE <name> USING unionvtab(<sql statement>);
    31         -**
    32         -** The implementation evalutes <sql statement> whenever a unionvtab virtual
    33         -** table is created or opened. It should return one row for each source
    34         -** database table. The four columns required of each row are:
    35         -**
    36         -**   1. The name of the database containing the table ("main" or "temp" or
    37         -**      the name of an attached database). Or NULL to indicate that all
    38         -**      databases should be searched for the table in the usual fashion.
    39         -**
    40         -**   2. The name of the database table.
    41         -**
    42         -**   3. The smallest rowid in the range of rowids that may be stored in the
    43         -**      database table (an integer).
    44         -**
    45         -**   4. The largest rowid in the range of rowids that may be stored in the
    46         -**      database table (an integer).
           28  +** The difference between the two virtual table modules is that for 
           29  +** "unionvtab", all source tables must be located in the main database or
           30  +** in databases ATTACHed to the main database by the user. For "swarmvtab",
           31  +** the tables may be located in any database file on disk. The "swarmvtab"
           32  +** implementation takes care of opening and closing database files
           33  +** automatically.
           34  +**
           35  +** UNIONVTAB
           36  +**
           37  +**   A "unionvtab" virtual table is created as follows:
           38  +**
           39  +**     CREATE VIRTUAL TABLE <name> USING unionvtab(<sql statement>);
           40  +**
           41  +**   The implementation evalutes <sql statement> whenever a unionvtab virtual
           42  +**   table is created or opened. It should return one row for each source
           43  +**   database table. The four columns required of each row are:
           44  +**
           45  +**     1. The name of the database containing the table ("main" or "temp" or
           46  +**        the name of an attached database). Or NULL to indicate that all
           47  +**        databases should be searched for the table in the usual fashion.
           48  +**
           49  +**     2. The name of the database table.
           50  +**
           51  +**     3. The smallest rowid in the range of rowids that may be stored in the
           52  +**        database table (an integer).
           53  +**
           54  +**     4. The largest rowid in the range of rowids that may be stored in the
           55  +**        database table (an integer).
           56  +**
           57  +** SWARMVTAB
           58  +**
           59  +**   A "swarmvtab" virtual table is created similarly to a unionvtab table:
           60  +**
           61  +**     CREATE VIRTUAL TABLE <name> USING swarmvtab(<sql statement>);
           62  +**
           63  +**   The difference is that for a swarmvtab table, the first column returned
           64  +**   by the <sql statement> must return a path or URI that can be used to open
           65  +**   the database file containing the source table.
    47     66   **
    48     67   */
    49     68   
    50     69   #include "sqlite3ext.h"
    51     70   SQLITE_EXTENSION_INIT1
    52     71   #include <assert.h>
    53     72   #include <string.h>
................................................................................
    61     80   #ifndef LARGEST_INT64
    62     81   # define LARGEST_INT64  (0xffffffff|(((sqlite3_int64)0x7fffffff)<<32))
    63     82   #endif
    64     83   #ifndef SMALLEST_INT64
    65     84   # define SMALLEST_INT64 (((sqlite3_int64)-1) - LARGEST_INT64)
    66     85   #endif
    67     86   
    68         -#define SWARMVTAB_MAX_ATTACHED 9
           87  +/*
           88  +** The swarmvtab module attempts to keep the number of open database files
           89  +** at or below this limit. This may not be possible if there are too many
           90  +** simultaneous queries.
           91  +*/
           92  +#define SWARMVTAB_MAX_OPEN 9
    69     93   
    70     94   typedef struct UnionCsr UnionCsr;
    71     95   typedef struct UnionTab UnionTab;
    72     96   typedef struct UnionSrc UnionSrc;
    73     97   
    74     98   /*
    75     99   ** Each source table (row returned by the initialization query) is 
................................................................................
    79    103   struct UnionSrc {
    80    104     char *zDb;                      /* Database containing source table */
    81    105     char *zTab;                     /* Source table name */
    82    106     sqlite3_int64 iMin;             /* Minimum rowid */
    83    107     sqlite3_int64 iMax;             /* Maximum rowid */
    84    108   
    85    109     /* Fields used by swarmvtab only */
    86         -  char *zFile;                    /* File to ATTACH */
          110  +  char *zFile;                    /* Database file containing table zTab */
    87    111     int nUser;                      /* Current number of users */
    88    112     sqlite3 *db;                    /* Database handle */
    89    113     UnionSrc *pNextClosable;        /* Next in list of closable sources */
    90    114   };
    91    115   
    92    116   /*
    93    117   ** Virtual table  type for union vtab.
................................................................................
   115    139     sqlite3_stmt *pStmt;            /* SQL statement to run */
   116    140   
   117    141     /* Used by swarmvtab only */
   118    142     sqlite3_int64 iMaxRowid;        /* Last rowid to visit */
   119    143     int iTab;                       /* Index of table read by pStmt */
   120    144   };
   121    145   
          146  +/*
          147  +** Given UnionTab table pTab and UnionSrc object pSrc, return the database
          148  +** handle that should be used to access the table identified by pSrc. This
          149  +** is the main db handle for "unionvtab" tables, or the source-specific 
          150  +** handle for "swarmvtab".
          151  +*/
   122    152   #define unionGetDb(pTab, pSrc) ((pTab)->bSwarm ? (pSrc)->db : (pTab)->db)
   123    153   
   124    154   /*
   125    155   ** If *pRc is other than SQLITE_OK when this function is called, it
   126    156   ** always returns NULL. Otherwise, it attempts to allocate and return
   127    157   ** a pointer to nByte bytes of zeroed memory. If the memory allocation
   128    158   ** is attempted but fails, NULL is returned and *pRc is set to 
................................................................................
   294    324       if( rc ){
   295    325         *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(db));
   296    326       }
   297    327     }
   298    328   }
   299    329   
   300    330   /*
   301         -** Close one database from the closable list.
          331  +** This function is a no-op for unionvtab. For swarmvtab, it attempts to
          332  +** close open database files until at most nMax are open. An SQLite error
          333  +** code is returned if an error occurs, or SQLITE_OK otherwise.
   302    334   */
   303         -static int unionCloseDatabase(UnionTab *pTab, char **pzErr){
          335  +static int unionCloseSources(UnionTab *pTab, int nMax){
   304    336     int rc = SQLITE_OK;
   305         -  UnionSrc **pp;
   306         -
   307         -  assert( pTab->pClosable && pTab->bSwarm );
   308         -
   309         -  pp = &pTab->pClosable;
   310         -  while( (*pp)->pNextClosable ){
   311         -    pp = &(*pp)->pNextClosable;
          337  +  if( pTab->bSwarm ){
          338  +    while( rc==SQLITE_OK && pTab->pClosable && pTab->nOpen>nMax ){
          339  +      UnionSrc **pp;
          340  +      for(pp=&pTab->pClosable; (*pp)->pNextClosable; pp=&(*pp)->pNextClosable);
          341  +      assert( (*pp)->db );
          342  +      rc = sqlite3_close((*pp)->db);
          343  +      (*pp)->db = 0;
          344  +      *pp = 0;
          345  +      pTab->nOpen--;
          346  +    }
   312    347     }
   313         -
   314         -  assert( (*pp)->db );
   315         -  rc = sqlite3_close((*pp)->db);
   316         -  (*pp)->db = 0;
   317         -  *pp = 0;
   318         -  pTab->nOpen--;
   319         -
   320    348     return rc;
   321    349   }
   322    350   
   323    351   /*
   324    352   ** xDisconnect method.
   325    353   */
   326    354   static int unionDisconnect(sqlite3_vtab *pVtab){
................................................................................
   443    471       sqlite3_free(z);
   444    472     }
   445    473     sqlite3_free(z0);
   446    474   
   447    475     return rc;
   448    476   }
   449    477   
          478  +/*
          479  +** This function may only be called for swarmvtab tables. The results of
          480  +** calling it on a unionvtab table are undefined.
          481  +**
          482  +** For a swarmvtab table, this function ensures that source database iSrc
          483  +** is open. If the database is opened successfully and the schema is as
          484  +** expected, or if it is already open when this function is called, SQLITE_OK
          485  +** is returned.
          486  +**
          487  +** Alternatively If an error occurs while opening the databases, or if the
          488  +** database schema is unsuitable, an SQLite error code is returned and (*pzErr)
          489  +** may be set to point to an English language error message. In this case it is
          490  +** the responsibility of the caller to eventually free the error message buffer
          491  +** using sqlite3_free(). 
          492  +*/
   450    493   static int unionOpenDatabase(UnionTab *pTab, int iSrc, char **pzErr){
   451    494     int rc = SQLITE_OK;
   452    495     UnionSrc *pSrc = &pTab->aSrc[iSrc];
   453    496   
   454    497     assert( pTab->bSwarm && iSrc<pTab->nSrc );
   455    498     if( pSrc->db==0 ){
   456         -    while( rc==SQLITE_OK && pTab->pClosable && pTab->nOpen>=pTab->nMaxOpen ){
   457         -      rc = unionCloseDatabase(pTab, pzErr);
   458         -    }
          499  +    rc = unionCloseSources(pTab, pTab->nMaxOpen-1);
   459    500   
   460    501       if( rc==SQLITE_OK ){
   461         -      rc = sqlite3_open(pSrc->zFile, &pSrc->db);
          502  +      rc = sqlite3_open_v2(pSrc->zFile, &pSrc->db, SQLITE_OPEN_READONLY, 0);
   462    503         if( rc!=SQLITE_OK ){
   463    504           *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(pSrc->db));
   464    505         }else{
   465    506           char *z = unionSourceToStr(&rc, pTab, pSrc, pzErr);
   466    507           if( rc==SQLITE_OK ){
   467    508             if( pTab->zSourceStr==0 ){
   468    509               pTab->zSourceStr = z;
................................................................................
   533    574           pSrc->pNextClosable = pTab->pClosable;
   534    575           pTab->pClosable = pSrc;
   535    576         }
   536    577       }
   537    578     }
   538    579     return rc;
   539    580   }
   540         -
   541         -
   542    581   
   543    582   /* 
   544    583   ** xConnect/xCreate method.
   545    584   **
   546    585   ** The argv[] array contains the following:
   547    586   **
   548    587   **   argv[0]   -> module name  ("unionvtab")
................................................................................
   642    681   
   643    682       /* For unionvtab, verify that all source tables exist and have 
   644    683       ** compatible schemas. For swarmvtab, attach the first database and
   645    684       ** check that the first table is a rowid table only.  */
   646    685       if( rc==SQLITE_OK ){
   647    686         pTab->db = db;
   648    687         pTab->bSwarm = bSwarm;
   649         -      pTab->nMaxOpen = SWARMVTAB_MAX_ATTACHED;
          688  +      pTab->nMaxOpen = SWARMVTAB_MAX_OPEN;
   650    689         if( bSwarm ){
   651    690           rc = unionOpenDatabase(pTab, 0, pzErr);
   652    691         }else{
   653    692           rc = unionSourceCheck(pTab, pzErr);
   654    693         }
   655    694       }
   656    695   
................................................................................
   681    720       pTab = 0;
   682    721     }
   683    722   
   684    723     *ppVtab = (sqlite3_vtab*)pTab;
   685    724     return rc;
   686    725   }
   687    726   
   688         -
   689    727   /*
   690    728   ** xOpen
   691    729   */
   692    730   static int unionOpen(sqlite3_vtab *p, sqlite3_vtab_cursor **ppCursor){
   693    731     UnionCsr *pCsr;
   694    732     int rc = SQLITE_OK;
   695    733     (void)p;  /* Suppress harmless warning */
................................................................................
   704    742   static int unionClose(sqlite3_vtab_cursor *cur){
   705    743     UnionCsr *pCsr = (UnionCsr*)cur;
   706    744     unionFinalizeCsrStmt(pCsr);
   707    745     sqlite3_free(pCsr);
   708    746     return SQLITE_OK;
   709    747   }
   710    748   
   711         -
   712    749   /*
   713         -** xNext
          750  +** This function does the work of the xNext() method. Except that, if it
          751  +** returns SQLITE_ROW, it should be called again within the same xNext()
          752  +** method call. See unionNext() for details.
   714    753   */
   715    754   static int doUnionNext(UnionCsr *pCsr){
   716    755     int rc = SQLITE_OK;
   717    756     assert( pCsr->pStmt );
   718    757     if( sqlite3_step(pCsr->pStmt)!=SQLITE_ROW ){
   719    758       UnionTab *pTab = (UnionTab*)pCsr->base.pVtab;
   720    759       rc = unionFinalizeCsrStmt(pCsr);
................................................................................
   742    781         }
   743    782       }
   744    783     }
   745    784   
   746    785     return rc;
   747    786   }
   748    787   
          788  +/*
          789  +** xNext
          790  +*/
   749    791   static int unionNext(sqlite3_vtab_cursor *cur){
   750    792     int rc;
   751    793     do {
   752    794       rc = doUnionNext((UnionCsr*)cur);
   753    795     }while( rc==SQLITE_ROW );
   754    796     return rc;
   755    797   }
................................................................................
   883    925       if( pTab->bSwarm ){
   884    926         pCsr->iTab = i;
   885    927         pCsr->iMaxRowid = iMax;
   886    928         rc = unionOpenDatabase(pTab, i, &pTab->base.zErrMsg);
   887    929         break;
   888    930       }
   889    931     }
   890         -
   891    932   
   892    933     if( zSql==0 ){
   893    934       return rc;
   894    935     }else{
   895    936       sqlite3 *db = unionGetDb(pTab, &pTab->aSrc[pCsr->iTab]);
   896    937       pCsr->pStmt = unionPrepare(&rc, db, zSql, &pTab->base.zErrMsg);
   897    938       if( pCsr->pStmt ){

Changes to test/swarmvtab.test.

    60     60   do_execsql_test 1.3 {
    61     61     CREATE VIRTUAL TABLE temp.s1 USING swarmvtab('SELECT * FROM dir');
    62     62     SELECT count(*) FROM s1 WHERE rowid<50;
    63     63   } {49}
    64     64   
    65     65   proc do_compare_test {tn where} {
    66     66     set sql [subst {
    67         -    SELECT (
    68         -        SELECT group_concat(a || ',' || b, ',') FROM t0 WHERE $where
    69         -    ) IS (
    70         -        SELECT group_concat(a || ',' || b, ',') FROM s1 WHERE $where
    71         -    )
           67  +    SELECT (SELECT group_concat(a || ',' || b, ',') FROM t0 WHERE $where) 
           68  +           IS 
           69  +           (SELECT group_concat(a || ',' || b, ',') FROM s1 WHERE $where)
    72     70     }]
    73     71   
    74     72     uplevel [list do_execsql_test $tn $sql 1]
    75     73   }
    76     74   
    77         -do_compare_test 1.4 "rowid = 55"
    78         -do_compare_test 1.5 "rowid BETWEEN 20 AND 100"
    79         -do_compare_test 1.6 "rowid > 350"
    80         -do_compare_test 1.7 "rowid >= 350"
    81         -do_compare_test 1.8 "rowid >= 200"
    82         -do_compare_test 1.9 "1"
    83         -do_compare_test 1.10 "rowid = 700"
    84         -do_compare_test 1.11 "rowid = -1"
    85         -do_compare_test 1.12 "rowid = 0"
           75  +do_compare_test 1.4.1 "rowid = 700"
           76  +do_compare_test 1.4.2 "rowid = -1"
           77  +do_compare_test 1.4.3 "rowid = 0"
           78  +do_compare_test 1.4.4 "rowid = 55"
           79  +do_compare_test 1.4.5 "rowid BETWEEN 20 AND 100"
           80  +do_compare_test 1.4.6 "rowid > 350"
           81  +do_compare_test 1.4.7 "rowid >= 350"
           82  +do_compare_test 1.4.8 "rowid >= 200"
           83  +do_compare_test 1.4.9 "1"
           84  +
           85  +# Multiple simultaneous cursors.
           86  +#
           87  +do_execsql_test 1.5.1.(5-seconds-or-so) {
           88  +  SELECT count(*) FROM s1 a, s1 b WHERE b.rowid<=200;
           89  +} {80000}
           90  +do_execsql_test 1.5.2 {
           91  +  SELECT count(*) FROM s1 a, s1 b, s1 c 
           92  +  WHERE a.rowid=b.rowid AND b.rowid=c.rowid;
           93  +} {400}
           94  +
           95  +# Empty source tables.
           96  +#
           97  +do_test 1.6.0 {
           98  +  for {set i 0} {$i < 20} {incr i} {
           99  +    sqlite3 db2 test.db$i
          100  +    db2 eval " DELETE FROM t$i "
          101  +    db2 close
          102  +  }
          103  +  db eval { DELETE FROM t0 WHERE rowid<=200 }
          104  +} {}
          105  +
          106  +do_compare_test 1.6.1 "rowid = 700"
          107  +do_compare_test 1.6.2 "rowid = -1"
          108  +do_compare_test 1.6.3 "rowid = 0"
          109  +do_compare_test 1.6.4 "rowid = 55"
          110  +do_compare_test 1.6.5 "rowid BETWEEN 20 AND 100"
          111  +do_compare_test 1.6.6 "rowid > 350"
          112  +do_compare_test 1.6.7 "rowid >= 350"
          113  +do_compare_test 1.6.8 "rowid >= 200"
          114  +do_compare_test 1.6.9 "1"
          115  +do_compare_test 1.6.10 "rowid >= 5"
    86    116   
    87    117   do_test 1.x {
    88    118     set sqlite_open_file_count
    89    119   } [expr $nFile+9]
    90    120   
    91    121   do_test 1.y { db close } {}
    92    122   
          123  +# Delete all the database files created above.
          124  +#
          125  +for {set i 0} {$i < 40} {incr i} { forcedelete "test.db$i" }
          126  +
          127  +#-------------------------------------------------------------------------
          128  +# Test some error conditions:
          129  +#
          130  +#   2.1: Database file does not exist.
          131  +#   2.2: Table does not exist.
          132  +#   2.3: Table schema does not match.
          133  +#
          134  +reset_db
          135  +load_static_extension db unionvtab
          136  +do_test 2.0.1 {
          137  +  db eval {
          138  +    CREATE TABLE t0(a INTEGER PRIMARY KEY, b TEXT);
          139  +    WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<400) 
          140  +      INSERT INTO t0 SELECT i, hex(randomblob(50)) FROM s;
          141  +    CREATE TABLE dir(f, t, imin, imax);
          142  +  }
          143  +
          144  +  for {set i 0} {$i < 40} {incr i} {
          145  +    set iMin [expr $i*10 + 1]
          146  +    set iMax [expr $iMin+9]
          147  +
          148  +    forcedelete "test.db$i"
          149  +    db eval [subst {
          150  +      ATTACH 'test.db$i' AS aux;
          151  +      CREATE TABLE aux.t$i (a INTEGER PRIMARY KEY, b TEXT);
          152  +      INSERT INTO aux.t$i SELECT * FROM t0 WHERE a BETWEEN $iMin AND $iMax;
          153  +      DETACH aux;
          154  +      INSERT INTO dir VALUES('test.db$i', 't$i', $iMin, $iMax);
          155  +    }]
          156  +  }
          157  +  execsql {
          158  +    CREATE VIRTUAL TABLE temp.s1 USING swarmvtab('SELECT * FROM dir');
          159  +  }
          160  +} {}
          161  +
          162  +do_test 2.0.2 {
          163  +  forcedelete test.db5
          164  +
          165  +  sqlite3 db2 test.db15
          166  +  db2 eval { DROP TABLE t15 }
          167  +  db2 close
          168  +
          169  +  sqlite3 db2 test.db25
          170  +  db2 eval { 
          171  +    DROP TABLE t25;
          172  +    CREATE TABLE t25(x, y, z PRIMARY KEY);
          173  +  }
          174  +  db2 close
          175  +} {}
          176  +
          177  +do_catchsql_test 2.1 {
          178  +  SELECT * FROM s1 WHERE rowid BETWEEN 1 AND 100;
          179  +} {1 {unable to open database file}}
          180  +do_catchsql_test 2.2 {
          181  +  SELECT * FROM s1 WHERE rowid BETWEEN 101 AND 200;
          182  +} {1 {no such rowid table: t15}}
          183  +do_catchsql_test 2.3 {
          184  +  SELECT * FROM s1 WHERE rowid BETWEEN 201 AND 300;
          185  +} {1 {source table schema mismatch}}
    93    186   
    94    187   finish_test
    95    188