Index: ext/misc/unionvtab.c ================================================================== --- ext/misc/unionvtab.c +++ ext/misc/unionvtab.c @@ -8,12 +8,12 @@ ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** -** This file contains the implementation of the "unionvtab" virtual -** table. This module provides read-only access to multiple tables, +** This file contains the implementation of the "unionvtab" and "swarmvtab" +** virtual tables. These modules provide read-only access to multiple tables, ** possibly in multiple database files, via a single database object. ** The source tables must have the following characteristics: ** ** * They must all be rowid tables (not VIRTUAL or WITHOUT ROWID ** tables or views). @@ -23,30 +23,52 @@ ** ** * The tables must not feature a user-defined column named "_rowid_". ** ** * Each table must contain a distinct range of rowid values. ** -** A "unionvtab" virtual table is created as follows: -** -** CREATE VIRTUAL TABLE USING unionvtab(); -** -** The implementation evalutes whenever a unionvtab virtual -** table is created or opened. It should return one row for each source -** database table. The four columns required of each row are: -** -** 1. The name of the database containing the table ("main" or "temp" or -** the name of an attached database). Or NULL to indicate that all -** databases should be searched for the table in the usual fashion. -** -** 2. The name of the database table. -** -** 3. The smallest rowid in the range of rowids that may be stored in the -** database table (an integer). -** -** 4. The largest rowid in the range of rowids that may be stored in the -** database table (an integer). -** +** The difference between the two virtual table modules is that for +** "unionvtab", all source tables must be located in the main database or +** in databases ATTACHed to the main database by the user. For "swarmvtab", +** the tables may be located in any database file on disk. The "swarmvtab" +** implementation takes care of opening and closing database files +** automatically. +** +** UNIONVTAB +** +** A "unionvtab" virtual table is created as follows: +** +** CREATE VIRTUAL TABLE USING unionvtab(); +** +** The implementation evalutes whenever a unionvtab virtual +** table is created or opened. It should return one row for each source +** database table. The four columns required of each row are: +** +** 1. The name of the database containing the table ("main" or "temp" or +** the name of an attached database). Or NULL to indicate that all +** databases should be searched for the table in the usual fashion. +** +** 2. The name of the database table. +** +** 3. The smallest rowid in the range of rowids that may be stored in the +** database table (an integer). +** +** 4. The largest rowid in the range of rowids that may be stored in the +** database table (an integer). +** +** SWARMVTAB +** +** A "swarmvtab" virtual table is created similarly to a unionvtab table: +** +** CREATE VIRTUAL TABLE +** USING swarmvtab(, ); +** +** 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. */ #include "sqlite3ext.h" SQLITE_EXTENSION_INIT1 #include @@ -63,10 +85,34 @@ #endif #ifndef SMALLEST_INT64 # define SMALLEST_INT64 (((sqlite3_int64)-1) - LARGEST_INT64) #endif +/* +** The following is also copied from sqliteInt.h. To facilitate coverage +** testing. +*/ +#ifndef ALWAYS +# if defined(SQLITE_COVERAGE_TEST) || defined(SQLITE_MUTATION_TEST) +# define ALWAYS(X) (1) +# define NEVER(X) (0) +# elif !defined(NDEBUG) +# define ALWAYS(X) ((X)?1:(assert(0),0)) +# define NEVER(X) ((X)?(assert(0),1):0) +# else +# define ALWAYS(X) (X) +# define NEVER(X) (X) +# endif +#endif + +/* +** The swarmvtab module attempts to keep the number of open database files +** at or below this limit. This may not be possible if there are too many +** simultaneous queries. +*/ +#define SWARMVTAB_MAX_OPEN 9 + typedef struct UnionCsr UnionCsr; typedef struct UnionTab UnionTab; typedef struct UnionSrc UnionSrc; /* @@ -77,31 +123,57 @@ struct UnionSrc { char *zDb; /* Database containing source table */ char *zTab; /* Source table name */ sqlite3_int64 iMin; /* Minimum rowid */ sqlite3_int64 iMax; /* Maximum rowid */ + + /* Fields used by swarmvtab only */ + char *zFile; /* Database file containing table zTab */ + int nUser; /* Current number of users */ + sqlite3 *db; /* Database handle */ + UnionSrc *pNextClosable; /* Next in list of closable sources */ }; /* ** Virtual table type for union vtab. */ struct UnionTab { sqlite3_vtab base; /* Base class - must be first */ sqlite3 *db; /* Database handle */ + int bSwarm; /* 1 for "swarmvtab", 0 for "unionvtab" */ 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 */ + char *zSourceStr; /* Expected unionSourceToStr() value */ + char *zNotFoundCallback; /* UDF to invoke if file not found on open */ + UnionSrc *pClosable; /* First in list of closable sources */ + int nOpen; /* Current number of open sources */ + int nMaxOpen; /* Maximum number of open sources */ }; /* ** Virtual table cursor type for union vtab. */ struct UnionCsr { sqlite3_vtab_cursor base; /* Base class - must be first */ sqlite3_stmt *pStmt; /* SQL statement to run */ + + /* Used by swarmvtab only */ + sqlite3_int64 iMaxRowid; /* Last rowid to visit */ + int iTab; /* Index of table read by pStmt */ }; +/* +** Given UnionTab table pTab and UnionSrc object pSrc, return the database +** handle that should be used to access the table identified by pSrc. This +** is the main db handle for "unionvtab" tables, or the source-specific +** handle for "swarmvtab". +*/ +#define unionGetDb(pTab, pSrc) ((pTab)->bSwarm ? (pSrc)->db : (pTab)->db) + /* ** If *pRc is other than SQLITE_OK when this function is called, it ** always returns NULL. Otherwise, it attempts to allocate and return ** a pointer to nByte bytes of zeroed memory. If the memory allocation ** is attempted but fails, NULL is returned and *pRc is set to @@ -158,11 +230,11 @@ /* Set stack variable q to the close-quote character */ if( q=='[' || q=='\'' || q=='"' || q=='`' ){ int iIn = 1; int iOut = 0; if( q=='[' ) q = ']'; - while( z[iIn] ){ + while( ALWAYS(z[iIn]) ){ if( z[iIn]==q ){ if( z[iIn+1]!=q ){ /* Character iIn was the close quote. */ iIn++; break; @@ -200,10 +272,11 @@ sqlite3 *db, /* Database handle */ const char *zSql, /* SQL statement to prepare */ char **pzErr /* OUT: Error message */ ){ sqlite3_stmt *pRet = 0; + assert( pzErr ); if( *pRc==SQLITE_OK ){ int rc = sqlite3_prepare_v2(db, zSql, -1, &pRet, 0); if( rc!=SQLITE_OK ){ *pzErr = sqlite3_mprintf("sql error: %s", sqlite3_errmsg(db)); *pRc = rc; @@ -248,28 +321,53 @@ ** SQLITE_OK when this function is called, then it is set to the ** value returned by sqlite3_reset() before this function exits. ** In this case, *pzErr may be set to point to an error message ** buffer allocated by sqlite3_malloc(). */ +#if 0 static void unionReset(int *pRc, sqlite3_stmt *pStmt, char **pzErr){ int rc = sqlite3_reset(pStmt); if( *pRc==SQLITE_OK ){ *pRc = rc; if( rc ){ *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(sqlite3_db_handle(pStmt))); } } } +#endif /* ** Call sqlite3_finalize() on SQL statement pStmt. If *pRc is set to ** SQLITE_OK when this function is called, then it is set to the ** value returned by sqlite3_finalize() before this function exits. */ -static void unionFinalize(int *pRc, sqlite3_stmt *pStmt){ +static void unionFinalize(int *pRc, sqlite3_stmt *pStmt, char **pzErr){ + sqlite3 *db = sqlite3_db_handle(pStmt); int rc = sqlite3_finalize(pStmt); - if( *pRc==SQLITE_OK ) *pRc = rc; + if( *pRc==SQLITE_OK ){ + *pRc = rc; + if( rc ){ + *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(db)); + } + } +} + +/* +** 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 **pp; + for(pp=&pTab->pClosable; (*pp)->pNextClosable; pp=&(*pp)->pNextClosable); + assert( (*pp)->db ); + sqlite3_close((*pp)->db); + (*pp)->db = 0; + *pp = 0; + pTab->nOpen--; + } } /* ** xDisconnect method. */ @@ -276,18 +374,55 @@ static int unionDisconnect(sqlite3_vtab *pVtab){ if( pVtab ){ UnionTab *pTab = (UnionTab*)pVtab; int i; for(i=0; inSrc; i++){ - sqlite3_free(pTab->aSrc[i].zDb); - sqlite3_free(pTab->aSrc[i].zTab); + UnionSrc *pSrc = &pTab->aSrc[i]; + sqlite3_free(pSrc->zDb); + sqlite3_free(pSrc->zTab); + sqlite3_free(pSrc->zFile); + sqlite3_close(pSrc->db); } + sqlite3_free(pTab->zSourceStr); + sqlite3_free(pTab->zNotFoundCallback); sqlite3_free(pTab->aSrc); sqlite3_free(pTab); } return SQLITE_OK; } + +/* +** Check that the table identified by pSrc is a rowid table. If not, +** return SQLITE_ERROR and set (*pzErr) to point to an English language +** error message. If the table is a rowid table and no error occurs, +** return SQLITE_OK and leave (*pzErr) unmodified. +*/ +static int unionIsIntkeyTable( + sqlite3 *db, /* Database handle */ + UnionSrc *pSrc, /* Source table to test */ + char **pzErr /* OUT: Error message */ +){ + int bPk = 0; + const char *zType = 0; + int rc; + + sqlite3_table_column_metadata( + db, pSrc->zDb, pSrc->zTab, "_rowid_", &zType, 0, 0, &bPk, 0 + ); + rc = sqlite3_errcode(db); + if( rc==SQLITE_ERROR + || (rc==SQLITE_OK && (!bPk || sqlite3_stricmp("integer", zType))) + ){ + rc = SQLITE_ERROR; + *pzErr = sqlite3_mprintf("no such rowid table: %s%s%s", + (pSrc->zDb ? pSrc->zDb : ""), + (pSrc->zDb ? "." : ""), + pSrc->zTab + ); + } + return rc; +} /* ** This function is a no-op if *pRc is other than SQLITE_OK when it is ** called. In this case it returns NULL. ** @@ -304,45 +439,31 @@ ** of the caller to free the returned string using sqlite3_free() when ** it is no longer required. */ static char *unionSourceToStr( int *pRc, /* IN/OUT: Error code */ - sqlite3 *db, /* Database handle */ + UnionTab *pTab, /* Virtual table object */ UnionSrc *pSrc, /* Source table to test */ - sqlite3_stmt *pStmt, char **pzErr /* OUT: Error message */ ){ char *zRet = 0; if( *pRc==SQLITE_OK ){ - int bPk = 0; - const char *zType = 0; - int rc; - - sqlite3_table_column_metadata( - db, pSrc->zDb, pSrc->zTab, "_rowid_", &zType, 0, 0, &bPk, 0 - ); - rc = sqlite3_errcode(db); - if( rc==SQLITE_ERROR - || (rc==SQLITE_OK && (!bPk || sqlite3_stricmp("integer", zType))) - ){ - rc = SQLITE_ERROR; - *pzErr = sqlite3_mprintf("no such rowid table: %s%s%s", - (pSrc->zDb ? pSrc->zDb : ""), - (pSrc->zDb ? "." : ""), - pSrc->zTab - ); - } - + sqlite3 *db = unionGetDb(pTab, pSrc); + int rc = unionIsIntkeyTable(db, pSrc, pzErr); + sqlite3_stmt *pStmt = unionPrepare(&rc, db, + "SELECT group_concat(quote(name) || '.' || quote(type)) " + "FROM pragma_table_info(?, ?)", pzErr + ); if( rc==SQLITE_OK ){ sqlite3_bind_text(pStmt, 1, pSrc->zTab, -1, SQLITE_STATIC); sqlite3_bind_text(pStmt, 2, pSrc->zDb, -1, SQLITE_STATIC); if( SQLITE_ROW==sqlite3_step(pStmt) ){ - zRet = unionStrdup(&rc, (const char*)sqlite3_column_text(pStmt, 0)); + const char *z = (const char*)sqlite3_column_text(pStmt, 0); + zRet = unionStrdup(&rc, z); } - unionReset(&rc, pStmt, pzErr); + unionFinalize(&rc, pStmt, pzErr); } - *pRc = rc; } return zRet; } @@ -354,51 +475,168 @@ ** to point to an error message buffer allocated by sqlite3_mprintf(). ** Or, if no problems regarding the source tables are detected and no ** other error occurs, SQLITE_OK is returned. */ static int unionSourceCheck(UnionTab *pTab, char **pzErr){ - const char *zSql = - "SELECT group_concat(quote(name) || '.' || quote(type)) " - "FROM pragma_table_info(?, ?)"; + int rc = SQLITE_OK; + char *z0 = 0; + int i; + + assert( *pzErr==0 ); + z0 = unionSourceToStr(&rc, pTab, &pTab->aSrc[0], pzErr); + for(i=1; inSrc; i++){ + char *z = unionSourceToStr(&rc, pTab, &pTab->aSrc[i], pzErr); + if( rc==SQLITE_OK && sqlite3_stricmp(z, z0) ){ + *pzErr = sqlite3_mprintf("source table schema mismatch"); + rc = SQLITE_ERROR; + } + sqlite3_free(z); + } + 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; + 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( zSql==0 ){ + *pzErr = sqlite3_mprintf("out of memory"); + return SQLITE_NOMEM; + } + rc = sqlite3_exec(pTab->db, zSql, 0, 0, pzErr); + sqlite3_free(zSql); + if( rc ) return rc; + rc = sqlite3_open_v2(pSrc->zFile, &pSrc->db, openFlags, 0); + } + if( rc!=SQLITE_OK ){ + *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(pSrc->db)); + } + return rc; +} + +/* +** This function may only be called for swarmvtab tables. The results of +** calling it on a unionvtab table are undefined. +** +** For a swarmvtab table, this function ensures that source database iSrc +** is open. If the database is opened successfully and the schema is as +** expected, or if it is already open when this function is called, SQLITE_OK +** is returned. +** +** Alternatively If an error occurs while opening the databases, or if the +** database schema is unsuitable, an SQLite error code is returned and (*pzErr) +** may be set to point to an English language error message. In this case it is +** the responsibility of the caller to eventually free the error message buffer +** using sqlite3_free(). +*/ +static int unionOpenDatabase(UnionTab *pTab, int iSrc, char **pzErr){ int rc = SQLITE_OK; - - if( pTab->nSrc==0 ){ - *pzErr = sqlite3_mprintf("no source tables configured"); - rc = SQLITE_ERROR; - }else{ - sqlite3_stmt *pStmt = 0; - char *z0 = 0; - int i; - - pStmt = unionPrepare(&rc, pTab->db, zSql, pzErr); + UnionSrc *pSrc = &pTab->aSrc[iSrc]; + + assert( pTab->bSwarm && iSrcnSrc ); + if( pSrc->db==0 ){ + unionCloseSources(pTab, pTab->nMaxOpen-1); + rc = unionOpenDatabaseInner(pTab, pSrc, pzErr); + if( rc==SQLITE_OK ){ + char *z = unionSourceToStr(&rc, pTab, pSrc, pzErr); + if( rc==SQLITE_OK ){ + if( pTab->zSourceStr==0 ){ + pTab->zSourceStr = z; + }else{ + if( sqlite3_stricmp(z, pTab->zSourceStr) ){ + *pzErr = sqlite3_mprintf("source table schema mismatch"); + rc = SQLITE_ERROR; + } + sqlite3_free(z); + } + } + } + if( rc==SQLITE_OK ){ - z0 = unionSourceToStr(&rc, pTab->db, &pTab->aSrc[0], pStmt, pzErr); - } - for(i=1; inSrc; i++){ - char *z = unionSourceToStr(&rc, pTab->db, &pTab->aSrc[i], pStmt, pzErr); - if( rc==SQLITE_OK && sqlite3_stricmp(z, z0) ){ - *pzErr = sqlite3_mprintf("source table schema mismatch"); - rc = SQLITE_ERROR; - } - sqlite3_free(z); - } - - unionFinalize(&rc, pStmt); - sqlite3_free(z0); + pSrc->pNextClosable = pTab->pClosable; + pTab->pClosable = pSrc; + pTab->nOpen++; + }else{ + sqlite3_close(pSrc->db); + pSrc->db = 0; + } + } + + return rc; +} + + +/* +** This function is a no-op for unionvtab tables. For swarmvtab, increment +** the reference count for source table iTab. If the reference count was +** zero before it was incremented, also remove the source from the closable +** list. +*/ +static void unionIncrRefcount(UnionTab *pTab, int iTab){ + if( pTab->bSwarm ){ + UnionSrc *pSrc = &pTab->aSrc[iTab]; + assert( pSrc->nUser>=0 && pSrc->db ); + if( pSrc->nUser==0 ){ + UnionSrc **pp; + for(pp=&pTab->pClosable; *pp!=pSrc; pp=&(*pp)->pNextClosable); + *pp = pSrc->pNextClosable; + pSrc->pNextClosable = 0; + } + pSrc->nUser++; + } +} + +/* +** Finalize the SQL statement pCsr->pStmt and return the result. +** +** If this is a swarmvtab table (not unionvtab) and pCsr->pStmt was not +** NULL when this function was called, also decrement the reference +** count on the associated source table. If this means the source tables +** refcount is now zero, add it to the closable list. +*/ +static int unionFinalizeCsrStmt(UnionCsr *pCsr){ + int rc = SQLITE_OK; + if( pCsr->pStmt ){ + UnionTab *pTab = (UnionTab*)pCsr->base.pVtab; + UnionSrc *pSrc = &pTab->aSrc[pCsr->iTab]; + rc = sqlite3_finalize(pCsr->pStmt); + pCsr->pStmt = 0; + if( pTab->bSwarm ){ + pSrc->nUser--; + assert( pSrc->nUser>=0 ); + if( pSrc->nUser==0 ){ + pSrc->pNextClosable = pTab->pClosable; + pTab->pClosable = pSrc; + } + unionCloseSources(pTab, pTab->nMaxOpen); + } } return rc; } /* ** xConnect/xCreate method. ** ** The argv[] array contains the following: ** -** argv[0] -> module name ("unionvtab") +** argv[0] -> module name ("unionvtab" or "swarmvtab") ** argv[1] -> database name ** argv[2] -> table name ** argv[3] -> SQL statement +** argv[4] -> not-found callback UDF name */ static int unionConnect( sqlite3 *db, void *pAux, int argc, const char *const*argv, @@ -405,18 +643,19 @@ sqlite3_vtab **ppVtab, char **pzErr ){ UnionTab *pTab = 0; int rc = SQLITE_OK; + int bSwarm = (pAux==0 ? 0 : 1); + const char *zVtab = (bSwarm ? "swarmvtab" : "unionvtab"); - (void)pAux; /* Suppress harmless 'unused parameter' warning */ if( sqlite3_stricmp("temp", argv[1]) ){ /* unionvtab tables may only be created in the temp schema */ - *pzErr = sqlite3_mprintf("unionvtab tables must be created in TEMP schema"); + *pzErr = sqlite3_mprintf("%s tables must be created in TEMP schema", zVtab); rc = SQLITE_ERROR; - }else if( argc!=4 ){ - *pzErr = sqlite3_mprintf("wrong number of arguments for unionvtab"); + }else if( argc!=4 && argc!=5 ){ + *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 */ char *zArg = unionStrdup(&rc, argv[3]); /* Copy of argument to CVT */ @@ -462,43 +701,73 @@ if( iMaxnSrc>0 && iMin<=pTab->aSrc[pTab->nSrc-1].iMax) ){ *pzErr = sqlite3_mprintf("rowid range mismatch error"); rc = SQLITE_ERROR; } - pSrc = &pTab->aSrc[pTab->nSrc++]; - pSrc->zDb = unionStrdup(&rc, zDb); - pSrc->zTab = unionStrdup(&rc, zTab); - pSrc->iMin = iMin; - pSrc->iMax = iMax; + if( rc==SQLITE_OK ){ + pSrc = &pTab->aSrc[pTab->nSrc++]; + pSrc->zTab = unionStrdup(&rc, zTab); + pSrc->iMin = iMin; + pSrc->iMax = iMax; + if( bSwarm ){ + pSrc->zFile = unionStrdup(&rc, zDb); + }else{ + pSrc->zDb = unionStrdup(&rc, zDb); + } + } } - unionFinalize(&rc, pStmt); + unionFinalize(&rc, pStmt, pzErr); pStmt = 0; - /* Verify that all source tables exist and have compatible schemas. */ + /* Capture the not-found callback UDF name */ + if( 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"); + rc = SQLITE_ERROR; + } + + /* 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; - rc = unionSourceCheck(pTab, pzErr); + pTab->bSwarm = bSwarm; + pTab->nMaxOpen = SWARMVTAB_MAX_OPEN; + if( bSwarm ){ + rc = unionOpenDatabase(pTab, 0, pzErr); + }else{ + rc = unionSourceCheck(pTab, pzErr); + } } /* Compose a CREATE TABLE statement and pass it to declare_vtab() */ if( rc==SQLITE_OK ){ - pStmt = unionPreparePrintf(&rc, pzErr, db, "SELECT " + UnionSrc *pSrc = &pTab->aSrc[0]; + sqlite3 *tdb = unionGetDb(pTab, pSrc); + pStmt = unionPreparePrintf(&rc, pzErr, tdb, "SELECT " "'CREATE TABLE xyz('" " || group_concat(quote(name) || ' ' || type, ', ')" " || ')'," "max((cid+1) * (type='INTEGER' COLLATE nocase AND pk=1))-1 " "FROM pragma_table_info(%Q, ?)", - pTab->aSrc[0].zTab, pTab->aSrc[0].zDb + pSrc->zTab, pSrc->zDb ); } if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){ const char *zDecl = (const char*)sqlite3_column_text(pStmt, 0); rc = sqlite3_declare_vtab(db, zDecl); pTab->iPK = sqlite3_column_int(pStmt, 1); } - unionFinalize(&rc, pStmt); + unionFinalize(&rc, pStmt, pzErr); } if( rc!=SQLITE_OK ){ unionDisconnect((sqlite3_vtab*)pTab); pTab = 0; @@ -505,11 +774,10 @@ } *ppVtab = (sqlite3_vtab*)pTab; return rc; } - /* ** xOpen */ static int unionOpen(sqlite3_vtab *p, sqlite3_vtab_cursor **ppCursor){ @@ -524,29 +792,60 @@ /* ** xClose */ static int unionClose(sqlite3_vtab_cursor *cur){ UnionCsr *pCsr = (UnionCsr*)cur; - sqlite3_finalize(pCsr->pStmt); + unionFinalizeCsrStmt(pCsr); sqlite3_free(pCsr); return SQLITE_OK; } +/* +** This function does the work of the xNext() method. Except that, if it +** returns SQLITE_ROW, it should be called again within the same xNext() +** method call. See unionNext() for details. +*/ +static int doUnionNext(UnionCsr *pCsr){ + int rc = SQLITE_OK; + assert( pCsr->pStmt ); + if( sqlite3_step(pCsr->pStmt)!=SQLITE_ROW ){ + UnionTab *pTab = (UnionTab*)pCsr->base.pVtab; + rc = unionFinalizeCsrStmt(pCsr); + if( rc==SQLITE_OK && pTab->bSwarm ){ + pCsr->iTab++; + if( pCsr->iTabnSrc ){ + UnionSrc *pSrc = &pTab->aSrc[pCsr->iTab]; + if( pCsr->iMaxRowid>=pSrc->iMin ){ + /* It is necessary to scan the next table. */ + rc = unionOpenDatabase(pTab, pCsr->iTab, &pTab->base.zErrMsg); + pCsr->pStmt = unionPreparePrintf(&rc, &pTab->base.zErrMsg, pSrc->db, + "SELECT rowid, * FROM %Q %s %lld", + pSrc->zTab, + (pSrc->iMax>pCsr->iMaxRowid ? "WHERE _rowid_ <=" : "-- "), + pCsr->iMaxRowid + ); + if( rc==SQLITE_OK ){ + assert( pCsr->pStmt ); + unionIncrRefcount(pTab, pCsr->iTab); + rc = SQLITE_ROW; + } + } + } + } + } + + return rc; +} /* ** xNext */ static int unionNext(sqlite3_vtab_cursor *cur){ - UnionCsr *pCsr = (UnionCsr*)cur; int rc; - assert( pCsr->pStmt ); - if( sqlite3_step(pCsr->pStmt)!=SQLITE_ROW ){ - rc = sqlite3_finalize(pCsr->pStmt); - pCsr->pStmt = 0; - }else{ - rc = SQLITE_OK; - } + do { + rc = doUnionNext((UnionCsr*)cur); + }while( rc==SQLITE_ROW ); return rc; } /* ** xColumn @@ -635,12 +934,11 @@ } } } } - sqlite3_finalize(pCsr->pStmt); - pCsr->pStmt = 0; + unionFinalizeCsrStmt(pCsr); if( bZero ){ return SQLITE_OK; } for(i=0; inSrc; i++){ @@ -672,16 +970,29 @@ } if( iMax!=LARGEST_INT64 && iMaxiMax ){ zSql = sqlite3_mprintf("%z %s rowid<=%lld", zSql, zWhere, iMax); } } + + if( pTab->bSwarm ){ + pCsr->iTab = i; + pCsr->iMaxRowid = iMax; + rc = unionOpenDatabase(pTab, i, &pTab->base.zErrMsg); + break; + } } - - if( zSql==0 ) return rc; - pCsr->pStmt = unionPrepare(&rc, pTab->db, zSql, &pTab->base.zErrMsg); - sqlite3_free(zSql); + if( zSql==0 ){ + return rc; + }else{ + sqlite3 *db = unionGetDb(pTab, &pTab->aSrc[pCsr->iTab]); + pCsr->pStmt = unionPrepare(&rc, db, zSql, &pTab->base.zErrMsg); + if( pCsr->pStmt ){ + unionIncrRefcount(pTab, pCsr->iTab); + } + sqlite3_free(zSql); + } if( rc!=SQLITE_OK ) return rc; return unionNext(pVtabCursor); } /* @@ -789,12 +1100,17 @@ 0, /* xRename */ 0, /* xSavepoint */ 0, /* xRelease */ 0 /* xRollbackTo */ }; + int rc; - return sqlite3_create_module(db, "unionvtab", &unionModule, 0); + rc = sqlite3_create_module(db, "unionvtab", &unionModule, 0); + if( rc==SQLITE_OK ){ + rc = sqlite3_create_module(db, "swarmvtab", &unionModule, (void*)db); + } + return rc; } #endif /* SQLITE_OMIT_VIRTUALTABLE */ #ifdef _WIN32 ADDED test/swarmvtab.test Index: test/swarmvtab.test ================================================================== --- /dev/null +++ test/swarmvtab.test @@ -0,0 +1,188 @@ +# 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 swarmvtab + +ifcapable !vtab { + finish_test + return +} + +load_static_extension db unionvtab + +set nFile $sqlite_open_file_count + +do_execsql_test 1.0 { + CREATE TABLE t0(a INTEGER PRIMARY KEY, b TEXT); + WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<400) + INSERT INTO t0 SELECT i, hex(randomblob(50)) FROM s; + + CREATE TABLE dir(f, t, imin, imax); +} + +do_test 1.1 { + for {set i 0} {$i < 40} {incr i} { + set iMin [expr $i*10 + 1] + set iMax [expr $iMin+9] + + forcedelete "test.db$i" + execsql [subst { + ATTACH 'test.db$i' AS aux; + CREATE TABLE aux.t$i (a INTEGER PRIMARY KEY, b TEXT); + INSERT INTO aux.t$i SELECT * FROM t0 WHERE a BETWEEN $iMin AND $iMax; + DETACH aux; + INSERT INTO dir VALUES('test.db$i', 't$i', $iMin, $iMax); + }] + } + + execsql { + CREATE VIRTUAL TABLE temp.s1 USING swarmvtab('SELECT * FROM dir'); + } +} {} + +do_execsql_test 1.2 { + DROP TABLE s1; +} {} + +do_execsql_test 1.3 { + CREATE VIRTUAL TABLE temp.s1 USING swarmvtab('SELECT * FROM dir'); + SELECT count(*) FROM s1 WHERE rowid<50; +} {49} + +proc do_compare_test {tn where} { + set sql [subst { + SELECT (SELECT group_concat(a || ',' || b, ',') FROM t0 WHERE $where) + IS + (SELECT group_concat(a || ',' || b, ',') FROM s1 WHERE $where) + }] + + uplevel [list do_execsql_test $tn $sql 1] +} + +do_compare_test 1.4.1 "rowid = 700" +do_compare_test 1.4.2 "rowid = -1" +do_compare_test 1.4.3 "rowid = 0" +do_compare_test 1.4.4 "rowid = 55" +do_compare_test 1.4.5 "rowid BETWEEN 20 AND 100" +do_compare_test 1.4.6 "rowid > 350" +do_compare_test 1.4.7 "rowid >= 350" +do_compare_test 1.4.8 "rowid >= 200" +do_compare_test 1.4.9 "1" + +# Multiple simultaneous cursors. +# +do_execsql_test 1.5.1.(5-seconds-or-so) { + SELECT count(*) FROM s1 a, s1 b WHERE b.rowid<=200; +} {80000} +do_execsql_test 1.5.2 { + SELECT count(*) FROM s1 a, s1 b, s1 c + WHERE a.rowid=b.rowid AND b.rowid=c.rowid; +} {400} + +# Empty source tables. +# +do_test 1.6.0 { + for {set i 0} {$i < 20} {incr i} { + sqlite3 db2 test.db$i + db2 eval " DELETE FROM t$i " + db2 close + } + db eval { DELETE FROM t0 WHERE rowid<=200 } +} {} + +do_compare_test 1.6.1 "rowid = 700" +do_compare_test 1.6.2 "rowid = -1" +do_compare_test 1.6.3 "rowid = 0" +do_compare_test 1.6.4 "rowid = 55" +do_compare_test 1.6.5 "rowid BETWEEN 20 AND 100" +do_compare_test 1.6.6 "rowid > 350" +do_compare_test 1.6.7 "rowid >= 350" +do_compare_test 1.6.8 "rowid >= 200" +do_compare_test 1.6.9 "1" +do_compare_test 1.6.10 "rowid >= 5" + +do_test 1.x { + set sqlite_open_file_count +} [expr $nFile+9] + +do_test 1.y { db close } {} + +# Delete all the database files created above. +# +for {set i 0} {$i < 40} {incr i} { forcedelete "test.db$i" } + +#------------------------------------------------------------------------- +# Test some error conditions: +# +# 2.1: Database file does not exist. +# 2.2: Table does not exist. +# 2.3: Table schema does not match. +# +reset_db +load_static_extension db unionvtab +do_test 2.0.1 { + db eval { + CREATE TABLE t0(a INTEGER PRIMARY KEY, b TEXT); + WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<400) + INSERT INTO t0 SELECT i, hex(randomblob(50)) FROM s; + CREATE TABLE dir(f, t, imin, imax); + } + + for {set i 0} {$i < 40} {incr i} { + set iMin [expr $i*10 + 1] + set iMax [expr $iMin+9] + + forcedelete "test.db$i" + db eval [subst { + ATTACH 'test.db$i' AS aux; + CREATE TABLE aux.t$i (a INTEGER PRIMARY KEY, b TEXT); + INSERT INTO aux.t$i SELECT * FROM t0 WHERE a BETWEEN $iMin AND $iMax; + DETACH aux; + INSERT INTO dir VALUES('test.db$i', 't$i', $iMin, $iMax); + }] + } + execsql { + CREATE VIRTUAL TABLE temp.s1 USING swarmvtab('SELECT * FROM dir'); + } +} {} + +do_test 2.0.2 { + forcedelete test.db5 + + sqlite3 db2 test.db15 + db2 eval { DROP TABLE t15 } + db2 close + + sqlite3 db2 test.db25 + db2 eval { + DROP TABLE t25; + CREATE TABLE t25(x, y, z PRIMARY KEY); + } + db2 close +} {} + +do_catchsql_test 2.1 { + SELECT * FROM s1 WHERE rowid BETWEEN 1 AND 100; +} {1 {unable to open database file}} +do_catchsql_test 2.2 { + SELECT * FROM s1 WHERE rowid BETWEEN 101 AND 200; +} {1 {no such rowid table: t15}} +do_catchsql_test 2.3 { + SELECT * FROM s1 WHERE rowid BETWEEN 201 AND 300; +} {1 {source table schema mismatch}} + +finish_test + ADDED test/swarmvtab2.test Index: test/swarmvtab2.test ================================================================== --- /dev/null +++ test/swarmvtab2.test @@ -0,0 +1,74 @@ +# 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 swarmvtab + +ifcapable !vtab { + finish_test + return +} + + +db close +foreach name [glob -nocomplain test*.db] { + forcedelete $name +} +sqlite3 db test.db +load_static_extension db unionvtab +proc create_database {filename} { + sqlite3 dbx $filename + set num [regsub -all {[^0-9]+} $filename {}] + set num [string trimleft $num 0] + set start [expr {$num*1000}] + set end [expr {$start+999}] + dbx eval { + CREATE TABLE t2(a INTEGER PRIMARY KEY,b); + WITH RECURSIVE c(x) AS ( + VALUES($start) UNION ALL SELECT x+1 FROM c WHERE x<$end + ) + INSERT INTO t2(a,b) SELECT x, printf('**%05d**',x) FROM c; + } + dbx close +} +db func create_database create_database +do_execsql_test 100 { + CREATE TABLE t1(filename, tablename, istart, iend); + WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<99) + INSERT INTO t1 SELECT printf('test%03d.db',x),'t2',x*1000,x*1000+999 FROM c; + CREATE VIRTUAL TABLE temp.v1 USING swarmvtab( + 'SELECT * FROM t1', 'create_database' + ); +} {} +do_execsql_test 110 { + SELECT b FROM v1 WHERE a=3875; +} {**03875**} +do_test 120 { + lsort [glob -nocomplain test?*.db] +} {test001.db test003.db} +do_execsql_test 130 { + SELECT b FROM v1 WHERE a BETWEEN 3999 AND 4000 ORDER BY a; +} {**03999** **04000**} +do_test 140 { + lsort [glob -nocomplain test?*.db] +} {test001.db test003.db test004.db} +do_execsql_test 150 { + SELECT b FROM v1 WHERE a>=99998; +} {**99998** **99999**} +do_test 160 { + lsort -dictionary [glob -nocomplain test?*.db] +} {test001.db test003.db test004.db test099.db} + +finish_test Index: test/unionvtabfault.test ================================================================== --- test/unionvtabfault.test +++ test/unionvtabfault.test @@ -64,9 +64,21 @@ } -body { execsql { SELECT * FROM uuu } } -test { faultsim_test_result {0 {1 one 2 two 3 three 10 ten 11 eleven 12 twelve 20 twenty 21 twenty-one 22 twenty-two}} } + +#------------------------------------------------------------------------- +# Error while registering the two vtab modules. +do_faultsim_test 2.0 -faults * -prep { + catch { db close } + sqlite3 db :memory: +} -body { + load_static_extension db unionvtab +} -test { + faultsim_test_result {0 {}} {1 {initialization of unionvtab failed: }} +} + finish_test