Index: ext/misc/unionvtab.c ================================================================== --- ext/misc/unionvtab.c +++ ext/misc/unionvtab.c @@ -53,10 +53,12 @@ ** ** 4. The largest rowid in the range of rowids that may be stored in the ** database table (an integer). ** ** SWARMVTAB +** +** LEGACY SYNTAX: ** ** A "swarmvtab" virtual table is created similarly to a unionvtab table: ** ** CREATE VIRTUAL TABLE ** USING swarmvtab(, ); @@ -64,17 +66,82 @@ ** The difference is that for a swarmvtab table, the first column returned ** by the must return a path or URI that can be used to open ** the database file containing the source table. The option ** is optional. If included, it is the name of an application-defined ** SQL function that is invoked with the URI of the file, if the file -** does not already exist on disk. +** does not already exist on disk when required by swarmvtab. +** +** NEW SYNTAX: +** +** Using the new syntax, a swarmvtab table is created with: +** +** CREATE VIRTUAL TABLE USING swarmvtab( +** [, ] +** ); +** +** where valid are: +** +** missing= +** openclose= +** maxopen= +** = +** +** The must return the same 4 columns as for a swarmvtab +** table in legacy mode. However, it may also return a 5th column - the +** "context" column. The text value returned in this column is not used +** at all by the swarmvtab implementation, except that it is passed as +** an additional argument to the two UDF functions that may be invoked +** (see below). +** +** The "missing" option, if present, specifies the name of an SQL UDF +** function to be invoked if a database file is not already present on +** disk when required by swarmvtab. If the did not provide +** a context column, it is invoked as: +** +** SELECT (); +** +** Or, if there was a context column: +** +** SELECT (, ); +** +** The "openclose" option may also specify a UDF function. This function +** is invoked right before swarmvtab opens a database, and right after +** it closes one. The first argument - or first two arguments, if +** supplied the context column - is the same as for +** the "missing" UDF. Following this, the UDF is passed integer value +** 0 before a db is opened, and 1 right after it is closed. If both +** a missing and openclose UDF is supplied, the application should expect +** the following sequence of calls (for a single database): +** +** SELECT (, , 0); +** if( db not already on disk ){ +** SELECT (, ); +** } +** ... swarmvtab uses database ... +** SELECT (, , 1); +** +** The "maxopen" option is used to configure the maximum number of +** database files swarmvtab will hold open simultaneously (default 9). +** +** If an option name begins with a ":" character, then it is assumed +** to be an SQL parameter. In this case, the specified text value is +** bound to the same variable of the before it is +** executed. It is an error of the named SQL parameter does not exist. +** For example: +** +** CREATE VIRTUAL TABLE swarm USING swarmvtab( +** 'SELECT :path || localfile, tbl, min, max FROM swarmdir', +** :path='/home/user/databases/' +** missing='missing_func' +** ); */ #include "sqlite3ext.h" SQLITE_EXTENSION_INIT1 #include #include +#include #ifndef SQLITE_OMIT_VIRTUALTABLE /* ** Largest and smallest possible 64-bit signed integers. These macros @@ -126,10 +193,11 @@ sqlite3_int64 iMin; /* Minimum rowid */ sqlite3_int64 iMax; /* Maximum rowid */ /* Fields used by swarmvtab only */ char *zFile; /* Database file containing table zTab */ + char *zContext; /* Context string, if any */ int nUser; /* Current number of users */ sqlite3 *db; /* Database handle */ UnionSrc *pNextClosable; /* Next in list of closable sources */ }; @@ -143,12 +211,15 @@ int iPK; /* INTEGER PRIMARY KEY column, or -1 */ int nSrc; /* Number of elements in the aSrc[] array */ UnionSrc *aSrc; /* Array of source tables, sorted by rowid */ /* Used by swarmvtab only */ + int bHasContext; /* Has context strings */ char *zSourceStr; /* Expected unionSourceToStr() value */ - char *zNotFoundCallback; /* UDF to invoke if file not found on open */ + sqlite3_stmt *pNotFound; /* UDF to invoke if file not found on open */ + sqlite3_stmt *pOpenClose; /* UDF to invoke on open and close */ + UnionSrc *pClosable; /* First in list of closable sources */ int nOpen; /* Current number of open sources */ int nMaxOpen; /* Maximum number of open sources */ }; @@ -348,25 +419,61 @@ if( rc ){ *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(db)); } } } + +/* +** If an "openclose" UDF was supplied when this virtual table was created, +** invoke it now. The first argument passed is the name of the database +** file for source pSrc. The second is integer value bClose. +** +** If successful, return SQLITE_OK. Otherwise an SQLite error code. In this +** case if argument pzErr is not NULL, also set (*pzErr) to an English +** language error message. The caller is responsible for eventually freeing +** any error message using sqlite3_free(). +*/ +static int unionInvokeOpenClose( + UnionTab *pTab, + UnionSrc *pSrc, + int bClose, + char **pzErr +){ + int rc = SQLITE_OK; + if( pTab->pOpenClose ){ + sqlite3_bind_text(pTab->pOpenClose, 1, pSrc->zFile, -1, SQLITE_STATIC); + if( pTab->bHasContext ){ + sqlite3_bind_text(pTab->pOpenClose, 2, pSrc->zContext, -1, SQLITE_STATIC); + } + sqlite3_bind_int(pTab->pOpenClose, 2+pTab->bHasContext, bClose); + sqlite3_step(pTab->pOpenClose); + if( SQLITE_OK!=(rc = sqlite3_reset(pTab->pOpenClose)) ){ + if( pzErr ){ + *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(pTab->db)); + } + } + } + return rc; +} /* ** This function is a no-op for unionvtab. For swarmvtab, it attempts to ** close open database files until at most nMax are open. An SQLite error ** code is returned if an error occurs, or SQLITE_OK otherwise. */ static void unionCloseSources(UnionTab *pTab, int nMax){ while( pTab->pClosable && pTab->nOpen>nMax ){ + UnionSrc *p; UnionSrc **pp; for(pp=&pTab->pClosable; (*pp)->pNextClosable; pp=&(*pp)->pNextClosable); - assert( (*pp)->db ); - sqlite3_close((*pp)->db); - (*pp)->db = 0; + p = *pp; + assert( p->db ); + sqlite3_close(p->db); + p->db = 0; *pp = 0; pTab->nOpen--; + unionInvokeOpenClose(pTab, p, 1, 0); } } /* ** xDisconnect method. @@ -375,17 +482,22 @@ if( pVtab ){ UnionTab *pTab = (UnionTab*)pVtab; int i; for(i=0; inSrc; i++){ UnionSrc *pSrc = &pTab->aSrc[i]; + if( pSrc->db ){ + unionInvokeOpenClose(pTab, pSrc, 1, 0); + } sqlite3_free(pSrc->zDb); sqlite3_free(pSrc->zTab); sqlite3_free(pSrc->zFile); + sqlite3_free(pSrc->zContext); sqlite3_close(pSrc->db); } + sqlite3_finalize(pTab->pNotFound); + sqlite3_finalize(pTab->pOpenClose); sqlite3_free(pTab->zSourceStr); - sqlite3_free(pTab->zNotFoundCallback); sqlite3_free(pTab->aSrc); sqlite3_free(pTab); } return SQLITE_OK; } @@ -494,33 +606,35 @@ sqlite3_free(z0); return rc; } - /* ** Try to open the swarmvtab database. If initially unable, invoke the ** not-found callback UDF and then try again. */ static int unionOpenDatabaseInner(UnionTab *pTab, UnionSrc *pSrc, char **pzErr){ - int rc = SQLITE_OK; - static const int openFlags = - SQLITE_OPEN_READONLY | SQLITE_OPEN_URI; + static const int openFlags = SQLITE_OPEN_READONLY | SQLITE_OPEN_URI; + int rc; + + rc = unionInvokeOpenClose(pTab, pSrc, 0, pzErr); + if( rc!=SQLITE_OK ) return rc; + rc = sqlite3_open_v2(pSrc->zFile, &pSrc->db, openFlags, 0); if( rc==SQLITE_OK ) return rc; - if( pTab->zNotFoundCallback ){ - char *zSql = sqlite3_mprintf("SELECT \"%w\"(%Q);", - pTab->zNotFoundCallback, pSrc->zFile); + if( pTab->pNotFound ){ sqlite3_close(pSrc->db); pSrc->db = 0; - if( zSql==0 ){ - *pzErr = sqlite3_mprintf("out of memory"); - return SQLITE_NOMEM; + sqlite3_bind_text(pTab->pNotFound, 1, pSrc->zFile, -1, SQLITE_STATIC); + if( pTab->bHasContext ){ + sqlite3_bind_text(pTab->pNotFound, 2, pSrc->zContext, -1, SQLITE_STATIC); } - rc = sqlite3_exec(pTab->db, zSql, 0, 0, pzErr); - sqlite3_free(zSql); - if( rc ) return rc; + sqlite3_step(pTab->pNotFound); + if( SQLITE_OK!=(rc = sqlite3_reset(pTab->pNotFound)) ){ + *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(pTab->db)); + return rc; + } rc = sqlite3_open_v2(pSrc->zFile, &pSrc->db, openFlags, 0); } if( rc!=SQLITE_OK ){ *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(pSrc->db)); } @@ -570,10 +684,11 @@ pTab->pClosable = pSrc; pTab->nOpen++; }else{ sqlite3_close(pSrc->db); pSrc->db = 0; + unionInvokeOpenClose(pTab, pSrc, 1, 0); } } return rc; } @@ -624,10 +739,136 @@ unionCloseSources(pTab, pTab->nMaxOpen); } } return rc; } + +/* +** Return true if the argument is a space, tab, CR or LF character. +*/ +static int union_isspace(char c){ + return (c==' ' || c=='\n' || c=='\r' || c=='\t'); +} + +/* +** Return true if the argument is an alphanumeric character in the +** ASCII range. +*/ +static int union_isidchar(char c){ + return ((c>='a' && c<='z') || (c>='A' && c<'Z') || (c>='0' && c<='9')); +} + +/* +** This function is called to handle all arguments following the first +** (the SQL statement) passed to a swarmvtab (not unionvtab) CREATE +** VIRTUAL TABLE statement. It may bind parameters to the SQL statement +** or configure members of the UnionTab object passed as the second +** argument. +** +** Refer to header comments at the top of this file for a description +** of the arguments parsed. +** +** This function is a no-op if *pRc is other than SQLITE_OK when it is +** called. Otherwise, if an error occurs, *pRc is set to an SQLite error +** code. In this case *pzErr may be set to point to a buffer containing +** an English language error message. It is the responsibility of the +** caller to eventually free the buffer using sqlite3_free(). +*/ +static void unionConfigureVtab( + int *pRc, /* IN/OUT: Error code */ + UnionTab *pTab, /* Table to configure */ + sqlite3_stmt *pStmt, /* SQL statement to find sources */ + int nArg, /* Number of entries in azArg[] array */ + const char * const *azArg, /* Array of arguments to consider */ + char **pzErr /* OUT: Error message */ +){ + int rc = *pRc; + int i; + if( rc==SQLITE_OK ){ + pTab->bHasContext = (sqlite3_column_count(pStmt)>4); + } + for(i=0; rc==SQLITE_OK && inMaxOpen = atoi(zVal); + if( pTab->nMaxOpen<=0 ){ + *pzErr = sqlite3_mprintf("swarmvtab: illegal maxopen value"); + rc = SQLITE_ERROR; + } + }else if( nOpt==7 && 0==sqlite3_strnicmp(zOpt, "missing", 7) ){ + if( pTab->pNotFound ){ + *pzErr = sqlite3_mprintf( + "swarmvtab: duplicate \"missing\" option"); + rc = SQLITE_ERROR; + }else{ + pTab->pNotFound = unionPreparePrintf(&rc, pzErr, pTab->db, + "SELECT \"%w\"(?%s)", zVal, pTab->bHasContext ? ",?" : "" + ); + } + }else if( nOpt==9 && 0==sqlite3_strnicmp(zOpt, "openclose", 9) ){ + if( pTab->pOpenClose ){ + *pzErr = sqlite3_mprintf( + "swarmvtab: duplicate \"openclose\" option"); + rc = SQLITE_ERROR; + }else{ + pTab->pOpenClose = unionPreparePrintf(&rc, pzErr, pTab->db, + "SELECT \"%w\"(?,?%s)", zVal, pTab->bHasContext ? ",?" : "" + ); + } + }else{ + *pzErr = sqlite3_mprintf("swarmvtab: unrecognized option: %s",zOpt); + rc = SQLITE_ERROR; + } + sqlite3_free(zVal); + } + }else{ + if( i==0 && nArg==1 ){ + pTab->pNotFound = unionPreparePrintf(&rc, pzErr, pTab->db, + "SELECT \"%w\"(?)", zArg + ); + }else{ + *pzErr = sqlite3_mprintf( "swarmvtab: parse error: %s", azArg[i]); + rc = SQLITE_ERROR; + } + } + sqlite3_free(zArg); + } + } + *pRc = rc; +} /* ** xConnect/xCreate method. ** ** The argv[] array contains the following: @@ -652,11 +893,11 @@ if( sqlite3_stricmp("temp", argv[1]) ){ /* unionvtab tables may only be created in the temp schema */ *pzErr = sqlite3_mprintf("%s tables must be created in TEMP schema", zVtab); rc = SQLITE_ERROR; - }else if( argc!=4 && argc!=5 ){ + }else if( argc<4 || (argc>4 && bSwarm==0) ){ *pzErr = sqlite3_mprintf("wrong number of arguments for %s", zVtab); rc = SQLITE_ERROR; }else{ int nAlloc = 0; /* Allocated size of pTab->aSrc[] */ sqlite3_stmt *pStmt = 0; /* Argument statement */ @@ -671,10 +912,21 @@ "SELECT * FROM (%z) ORDER BY 3", zArg ); /* Allocate the UnionTab structure */ pTab = unionMalloc(&rc, sizeof(UnionTab)); + if( pTab ){ + assert( rc==SQLITE_OK ); + pTab->db = db; + pTab->bSwarm = bSwarm; + pTab->nMaxOpen = SWARMVTAB_MAX_OPEN; + } + + /* Parse other CVT arguments, if any */ + if( bSwarm ){ + unionConfigureVtab(&rc, pTab, pStmt, argc-4, &argv[4], pzErr); + } /* Iterate through the rows returned by the SQL statement specified ** as an argument to the CREATE VIRTUAL TABLE statement. */ while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){ const char *zDb = (const char*)sqlite3_column_text(pStmt, 0); @@ -713,21 +965,19 @@ if( bSwarm ){ pSrc->zFile = unionStrdup(&rc, zDb); }else{ pSrc->zDb = unionStrdup(&rc, zDb); } + if( pTab->bHasContext ){ + const char *zContext = (const char*)sqlite3_column_text(pStmt, 4); + pSrc->zContext = unionStrdup(&rc, zContext); + } } } unionFinalize(&rc, pStmt, pzErr); pStmt = 0; - /* Capture the not-found callback UDF name */ - if( rc==SQLITE_OK && argc>=5 ){ - pTab->zNotFoundCallback = unionStrdup(&rc, argv[4]); - unionDequote(pTab->zNotFoundCallback); - } - /* It is an error if the SELECT statement returned zero rows. If only ** because there is no way to determine the schema of the virtual ** table in this case. */ if( rc==SQLITE_OK && pTab->nSrc==0 ){ *pzErr = sqlite3_mprintf("no source tables configured"); @@ -736,13 +986,10 @@ /* For unionvtab, verify that all source tables exist and have ** compatible schemas. For swarmvtab, attach the first database and ** check that the first table is a rowid table only. */ if( rc==SQLITE_OK ){ - pTab->db = db; - pTab->bSwarm = bSwarm; - pTab->nMaxOpen = SWARMVTAB_MAX_OPEN; if( bSwarm ){ rc = unionOpenDatabase(pTab, 0, pzErr); }else{ rc = unionSourceCheck(pTab, pzErr); } Index: test/swarmvtab.test ================================================================== --- test/swarmvtab.test +++ test/swarmvtab.test @@ -211,11 +211,11 @@ 'VALUES ("test.db1", "t1", 1, 10), ("test.db2", "t1", 11, 20) ', 'fetch_db_no_such_function' ); -} {1 {no such function: fetch_db_no_such_function}} +} {1 {sql error: no such function: fetch_db_no_such_function}} do_catchsql_test 3.2 { CREATE VIRTUAL TABLE temp.xyz USING swarmvtab( 'VALUES ("test.db1", "t1", 1, 10), Index: test/swarmvtab2.test ================================================================== --- test/swarmvtab2.test +++ test/swarmvtab2.test @@ -12,11 +12,11 @@ # focus of this file is the "swarmvtab" extension # set testdir [file dirname $argv0] source $testdir/tester.tcl -set testprefix swarmvtab +set testprefix swarmvtab2 do_not_use_codec ifcapable !vtab { finish_test return ADDED test/swarmvtab3.test Index: test/swarmvtab3.test ================================================================== --- test/swarmvtab3.test +++ test/swarmvtab3.test @@ -0,0 +1,233 @@ +# 2017-07-15 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. The +# focus of this file is the "swarmvtab" extension +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix swarmvtab3 +do_not_use_codec + +ifcapable !vtab { + finish_test + return +} + +load_static_extension db unionvtab + +set nFile $sqlite_open_file_count + +do_execsql_test 1.0 { + CREATE TEMP TABLE swarm(id, tbl, minval, maxval); +} + +# Set up 100 databases with filenames "remote_test.dbN", where N is between +# 0 and 99. +do_test 1.1 { + for {set i 0} {$i < 100} {incr i} { + set file remote_test.db$i + forcedelete $file + forcedelete test.db$i + sqlite3 rrr $file + rrr eval { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b); + INSERT INTO t1 VALUES($i, $i); + } + rrr close + db eval { + INSERT INTO swarm VALUES($i, 't1', $i, $i); + } + set ::dbcache(test.db$i) 0 + } +} {} + +proc missing_db {filename} { + set remote "remote_$filename" + forcedelete $filename + file copy $remote $filename +} +db func missing_db missing_db + +proc openclose_db {filename bClose} { + if {$bClose} { + incr ::dbcache($filename) -1 + } else { + incr ::dbcache($filename) 1 + } + if {$::dbcache($filename)==0} { + forcedelete $filename + } +} +db func openclose_db openclose_db + +proc check_dbcache {} { + set n 0 + for {set i 0} {$i<100} {incr i} { + set exists [file exists test.db$i] + if {$exists!=($::dbcache(test.db$i)!=0)} { + error "inconsistent ::dbcache and disk" + } + incr n $exists + } + return $n +} + +foreach {tn nMaxOpen cvt} { + 1 5 { + CREATE VIRTUAL TABLE temp.s USING swarmvtab( + 'SELECT :prefix || id, tbl, minval, minval FROM swarm', + :prefix='test.db', + missing=missing_db, + openclose=openclose_db, + maxopen=5 + ) + } + + 2 3 { + CREATE VIRTUAL TABLE temp.s USING swarmvtab( + 'SELECT :prefix || id, tbl, minval, minval FROM swarm', + :prefix='test.db', + missing = 'missing_db', + openclose=[openclose_db], + maxopen = 3 + ) + } + + 3 1 { + CREATE VIRTUAL TABLE temp.s USING swarmvtab( + 'SELECT :prefix||''.''||:suffix||id, tbl, minval, minval FROM swarm', + :prefix=test, :suffix=db, + missing = 'missing_db', + openclose=[openclose_db], + maxopen = 1 + ) + } + +} { + execsql { DROP TABLE IF EXISTS s } + + do_execsql_test 1.$tn.1 $cvt + + do_execsql_test 1.$tn.2 { + SELECT b FROM s WHERE a<10; + } {0 1 2 3 4 5 6 7 8 9} + + do_test 1.$tn.3 { check_dbcache } $nMaxOpen + + do_execsql_test 1.$tn.4 { + SELECT b FROM s WHERE (b%10)=0; + } {0 10 20 30 40 50 60 70 80 90} + + do_test 1.$tn.5 { check_dbcache } $nMaxOpen +} + +execsql { DROP TABLE IF EXISTS s } +for {set i 0} {$i < 100} {incr i} { + forcedelete remote_test.db$i +} + +#---------------------------------------------------------------------------- +# +do_execsql_test 2.0 { + DROP TABLE IF EXISTS swarm; + CREATE TEMP TABLE swarm(file, tbl, minval, maxval, ctx); +} + +catch { array unset ::dbcache } + +# Set up 100 databases with filenames "remote_test.dbN", where N is a +# random integer between 0 and 1,000,000 +# 0 and 99. +do_test 2.1 { + for {set i 0} {$i < 100} {incr i} { + while 1 { + set ctx [expr abs(int(rand() *1000000))] + if {[info exists ::dbcache($ctx)]==0} break + } + + set file test_remote.db$ctx + forcedelete $file + forcedelete test.db$i + sqlite3 rrr $file + rrr eval { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b); + INSERT INTO t1 VALUES($i, $i); + } + rrr close + db eval { + INSERT INTO swarm VALUES('test.db' || $i, 't1', $i, $i, $file) + } + set ::dbcache(test.db$i) 0 + } +} {} + +proc missing_db {filename ctx} { + file copy $ctx $filename +} +db func missing_db missing_db + +proc openclose_db {filename ctx bClose} { + if {$bClose} { + incr ::dbcache($filename) -1 + } else { + incr ::dbcache($filename) 1 + } + if {$::dbcache($filename)==0} { + forcedelete $filename + } +} +db func openclose_db openclose_db + +proc check_dbcache {} { + set n 0 + foreach k [array names ::dbcache] { + set exists [file exists $k] + if {$exists!=($::dbcache($k)!=0)} { + error "inconsistent ::dbcache and disk ($k)" + } + incr n $exists + } + return $n +} + +foreach {tn nMaxOpen cvt} { + 2 5 { + CREATE VIRTUAL TABLE temp.s USING swarmvtab( + 'SELECT file, tbl, minval, minval, ctx FROM swarm', + missing=missing_db, + openclose=openclose_db, + maxopen=5 + ) + } +} { + execsql { DROP TABLE IF EXISTS s } + + do_execsql_test 1.$tn.1 $cvt + + do_execsql_test 1.$tn.2 { + SELECT b FROM s WHERE a<10; + } {0 1 2 3 4 5 6 7 8 9} + + do_test 1.$tn.3 { check_dbcache } $nMaxOpen + + do_execsql_test 1.$tn.4 { + SELECT b FROM s WHERE (b%10)=0; + } {0 10 20 30 40 50 60 70 80 90} + + do_test 1.$tn.5 { check_dbcache } $nMaxOpen +} + +db close +forcedelete {*}[glob test_remote.db*] + +finish_test +