diff options
Diffstat (limited to 'test/vt02.c')
| -rw-r--r-- | test/vt02.c | 1019 |
1 files changed, 1019 insertions, 0 deletions
diff --git a/test/vt02.c b/test/vt02.c new file mode 100644 index 0000000..40bd6fc --- /dev/null +++ b/test/vt02.c @@ -0,0 +1,1019 @@ +/* +** This file implements an eponymous, read-only table-valued function +** (a virtual table) designed to be used for testing. We are not aware +** of any practical real-world use case for the virtual table. +** +** This virtual table originated in the TH3 test suite. It is still used +** there, but has now been copied into the public SQLite source tree and +** reused for a variety of testing purpose. The name "vt02" comes from the +** fact that there are many different testing virtual tables in TH3, of which +** this one is the second. +** +** ## SUBJECT TO CHANGE +** +** Because this virtual table is intended for testing, its interface is not +** guaranteed to be stable across releases. Future releases may contain +** changes in the vt02 design and interface. +** +** ## OVERVIEW +** +** The vt02 table-valued function has 10000 rows with 5 data columns. +** Column X contains all integer values between 0 and 9999 inclusive. +** Columns A, B, C, and D contain the individual base-10 digits associated +** with each X value: +** +** X A B C D +** ---- - - - - +** 0 0 0 0 0 +** 1 0 0 0 1 +** 2 0 0 0 2 +** ... +** 4998 4 9 9 8 +** 4999 4 9 9 9 +** 5000 5 0 0 0 +** ... +** 9995 9 9 9 5 +** 9996 9 9 9 6 +** 9997 9 9 9 7 +** +** The xBestIndex method recognizes a variety of equality constraints +** and attempts to optimize its output accordingly. +** +** x=... +** a=... +** a=... AND b=... +** a=... AND b=... AND c=... +** a=... AND b=... AND c=... AND d=... +** +** Various ORDER BY constraints are also recognized and consumed. The +** OFFSET constraint is recognized and consumed. +** +** ## TABLE-VALUED FUNCTION +** +** The vt02 virtual table is eponymous and has two hidden columns, meaning +** that it can functions a table-valued function. The two hidden columns +** are "flags" and "logtab", in that order. The "flags" column can be set +** to an integer where various bits enable or disable behaviors of the +** virtual table. The "logtab" can set to the name of an ordinary SQLite +** table into which is written information about each call to xBestIndex. +** +** The bits of "flags" are as follows: +** +** 0x01 Ignore the aConstraint[].usable flag. This might +** result in the xBestIndex method incorrectly using +** unusable entries in the aConstraint[] array, which +** should result in the SQLite core detecting and +** reporting that the virtual table is not behaving +** to spec. +** +** 0x02 Do not set the orderByConsumed flag, even if it +** could be set. +** +** 0x04 Do not consume the OFFSET constraint, if there is +** one. Instead, let the generated byte-code visit +** and ignore the first few columns of output. +** +** 0x08 Use sqlite3_mprintf() to allocate an idxStr string. +** The string is never used, but allocating it does +** test the idxStr deallocation logic inside of the +** SQLite core. +** +** 0x10 Cause the xBestIndex method to generate an idxNum +** that xFilter does not understand, thus causing +** the OP_VFilter opcode to raise an error. +** +** 0x20 Set the omit flag for all equality constraints on +** columns X, A, B, C, and D that are used to limit +** the search. +** +** 0x40 Add all constraints against X,A,B,C,D to the +** vector of results sent to xFilter. Only the first +** few are used, as required by idxNum. +** +** Because these flags take effect during xBestIndex, the RHS of the +** flag= constraint must be accessible. In other words, the RHS of flag= +** needs to be an integer literal, not another column of a join or a +** bound parameter. +** +** ## LOGGING OUTPUT +** +** If the "logtab" columns is set, then each call to the xBestIndex method +** inserts multiple rows into the table identified by "logtab". These +** rows collectively show the content of the sqlite3_index_info object and +** other context associated with the xBestIndex call. +** +** If the table named by "logtab" does not previously exist, it is created +** automatically. The schema for the logtab table is like this: +** +** CREATE TEMP TABLE vt02_log( +** bi INT, -- BestIndex call counter +** vn TEXT, -- Variable Name +** ix INT, -- Index or value +** cn TEXT, -- Column Name +** op INT, -- Opcode or "DESC" value +** ux INT, -- "Usable" flag +** ra BOOLEAN, -- Right-hand side Available. +** rhs ANY, -- Right-Hand Side value +** cs TEXT -- Collating Sequence for this constraint +** ); +** +** Because logging happens during xBestIindex, the RHS value of "logtab" must +** be known to xBestIndex, which means it must be a string literal, not a +** column in a join, or a bound parameter. +** +** ## VIRTUAL TABLE SCHEMA +** +** CREATE TABLE vt02( +** x INT, -- integer between 0 and 9999 inclusive +** a INT, -- The 1000s digit +** b INT, -- The 100s digit +** c INT, -- The 10s digit +** d INT, -- The 1s digit +** flags INT HIDDEN, -- Option flags +** logtab TEXT HIDDEN, -- Name of table into which to log xBestIndex +** ); +** +** ## COMPILING AND RUNNING +** +** This file can also be compiled separately as a loadable extension +** for SQLite (as long as the -DTH3_VERSION is not defined). To compile as a +** loadable extension do his: +** +** gcc -Wall -g -shared -fPIC -I. -DSQLITE_DEBUG vt02.c -o vt02.so +** +** Or on Windows: +** +** cl vt02.c -link -dll -out:vt02.dll +** +** Then load into the CLI using: +** +** .load ./vt02 sqlite3_vt02_init +** +** ## IDXNUM SUMMARY +** +** The xBestIndex method communicates the query plan to xFilter using +** the idxNum value, as follows: +** +** 0 unconstrained +** 1 X=argv[0] +** 2 A=argv[0] +** 3 A=argv[0], B=argv[1] +** 4 A=argv[0], B=argv[1], C=argv[2] +** 5 A=argv[0], B=argv[1], C=argv[2], D=argv[3] +** 6 A=argv[0], D IN argv[2] +** 7 A=argv[0], B=argv[2], D IN argv[3] +** 8 A=argv[0], B=argv[2], C=argv[3], D IN argv[4] +** 1x increment by 10 +** 2x increment by 100 +** 3x increment by 1000 +** 1xx Use offset provided by argv[N] +*/ +#ifndef TH3_VERSION + /* These bits for separate compilation as a loadable extension, only */ + #include "sqlite3ext.h" + SQLITE_EXTENSION_INIT1 + #include <stdlib.h> + #include <string.h> + #include <assert.h> +#endif + +/* Forward declarations */ +typedef struct vt02_vtab vt02_vtab; +typedef struct vt02_cur vt02_cur; + +/* +** The complete virtual table +*/ +struct vt02_vtab { + sqlite3_vtab parent; /* Base clase. Must be first. */ + sqlite3 *db; /* Database connection */ + int busy; /* Currently running xBestIndex */ +}; + +#define VT02_IGNORE_USABLE 0x0001 /* Ignore usable flags */ +#define VT02_NO_SORT_OPT 0x0002 /* Do not do any sorting optimizations */ +#define VT02_NO_OFFSET 0x0004 /* Omit the offset optimization */ +#define VT02_ALLOC_IDXSTR 0x0008 /* Alloate an idxStr */ +#define VT02_BAD_IDXNUM 0x0010 /* Generate an invalid idxNum */ + +/* +** A cursor +*/ +struct vt02_cur { + sqlite3_vtab_cursor parent; /* Base class. Must be first */ + sqlite3_int64 i; /* Current entry */ + sqlite3_int64 iEof; /* Indicate EOF when reaching this value */ + int iIncr; /* Amount by which to increment */ + unsigned int mD; /* Mask of allowed D-column values */ +}; + +/* The xConnect method */ +int vt02Connect( + sqlite3 *db, /* The database connection */ + void *pAux, /* Pointer to an alternative schema */ + int argc, /* Number of arguments */ + const char *const*argv, /* Text of the arguments */ + sqlite3_vtab **ppVTab, /* Write the new vtab here */ + char **pzErr /* Error message written here */ +){ + vt02_vtab *pVtab; + int rc; + const char *zSchema = (const char*)pAux; + static const char zDefaultSchema[] = + "CREATE TABLE x(x INT, a INT, b INT, c INT, d INT," + " flags INT HIDDEN, logtab TEXT HIDDEN);"; +#define VT02_COL_X 0 +#define VT02_COL_A 1 +#define VT02_COL_B 2 +#define VT02_COL_C 3 +#define VT02_COL_D 4 +#define VT02_COL_FLAGS 5 +#define VT02_COL_LOGTAB 6 +#define VT02_COL_NONE 7 + + pVtab = sqlite3_malloc( sizeof(*pVtab) ); + if( pVtab==0 ){ + *pzErr = sqlite3_mprintf("out of memory"); + return SQLITE_NOMEM; + } + memset(pVtab, 0, sizeof(*pVtab)); + pVtab->db = db; + rc = sqlite3_declare_vtab(db, zSchema ? zSchema : zDefaultSchema); + if( rc ){ + sqlite3_free(pVtab); + }else{ + *ppVTab = &pVtab->parent; + } + return rc; +} + +/* the xDisconnect method +*/ +int vt02Disconnect(sqlite3_vtab *pVTab){ + sqlite3_free(pVTab); + return SQLITE_OK; +} + +/* Put an error message into the zErrMsg string of the virtual table. +*/ +static void vt02ErrMsg(sqlite3_vtab *pVtab, const char *zFormat, ...){ + va_list ap; + sqlite3_free(pVtab->zErrMsg); + va_start(ap, zFormat); + pVtab->zErrMsg = sqlite3_vmprintf(zFormat, ap); + va_end(ap); +} + + +/* Open a cursor for scanning +*/ +static int vt02Open(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){ + vt02_cur *pCur; + pCur = sqlite3_malloc( sizeof(*pCur) ); + if( pCur==0 ){ + vt02ErrMsg(pVTab, "out of memory"); + return SQLITE_NOMEM; + } + *ppCursor = &pCur->parent; + pCur->i = -1; + return SQLITE_OK; +} + +/* Close a cursor +*/ +static int vt02Close(sqlite3_vtab_cursor *pCursor){ + vt02_cur *pCur = (vt02_cur*)pCursor; + sqlite3_free(pCur); + return SQLITE_OK; +} + +/* Return TRUE if we are at the end of the BVS and there are +** no more entries. +*/ +static int vt02Eof(sqlite3_vtab_cursor *pCursor){ + vt02_cur *pCur = (vt02_cur*)pCursor; + return pCur->i<0 || pCur->i>=pCur->iEof; +} + +/* Advance the cursor to the next row in the table +*/ +static int vt02Next(sqlite3_vtab_cursor *pCursor){ + vt02_cur *pCur = (vt02_cur*)pCursor; + do{ + pCur->i += pCur->iIncr; + if( pCur->i<0 ) pCur->i = pCur->iEof; + }while( (pCur->mD & (1<<(pCur->i%10)))==0 && pCur->i<pCur->iEof ); + return SQLITE_OK; +} + +/* Rewind a cursor back to the beginning of its scan. +** +** Scanning is always increasing. +** +** idxNum +** 0 unconstrained +** 1 X=argv[0] +** 2 A=argv[0] +** 3 A=argv[0], B=argv[1] +** 4 A=argv[0], B=argv[1], C=argv[2] +** 5 A=argv[0], B=argv[1], C=argv[2], D=argv[3] +** 6 A=argv[0], D IN argv[2] +** 7 A=argv[0], B=argv[2], D IN argv[3] +** 8 A=argv[0], B=argv[2], C=argv[3], D IN argv[4] +** 1x increment by 10 +** 2x increment by 100 +** 3x increment by 1000 +** 1xx Use offset provided by argv[N] +*/ +static int vt02Filter( + sqlite3_vtab_cursor *pCursor, /* The cursor to rewind */ + int idxNum, /* Search strategy */ + const char *idxStr, /* Not used */ + int argc, /* Not used */ + sqlite3_value **argv /* Not used */ +){ + vt02_cur *pCur = (vt02_cur*)pCursor; /* The vt02 cursor */ + int bUseOffset = 0; /* True to use OFFSET value */ + int iArg = 0; /* argv[] values used so far */ + int iOrigIdxNum = idxNum; /* Original value for idxNum */ + + pCur->iIncr = 1; + pCur->mD = 0x3ff; + if( idxNum>=100 ){ + bUseOffset = 1; + idxNum -= 100; + } + if( idxNum<0 || idxNum>38 ) goto vt02_bad_idxnum; + while( idxNum>=10 ){ + pCur->iIncr *= 10; + idxNum -= 10; + } + if( idxNum==0 ){ + pCur->i = 0; + pCur->iEof = 10000; + }else if( idxNum==1 ){ + pCur->i = sqlite3_value_int64(argv[0]); + if( pCur->i<0 ) pCur->i = -1; + if( pCur->i>9999 ) pCur->i = 10000; + pCur->iEof = pCur->i+1; + if( pCur->i<0 || pCur->i>9999 ) pCur->i = pCur->iEof; + }else if( idxNum>=2 && idxNum<=5 ){ + int i, e, m; + e = idxNum - 2; + assert( e<=argc-1 ); + pCur->i = 0; + for(m=1000, i=0; i<=e; i++, m /= 10){ + sqlite3_int64 v = sqlite3_value_int64(argv[iArg++]); + if( v<0 ) v = 0; + if( v>9 ) v = 9; + pCur->i += m*v; + pCur->iEof = pCur->i+m; + } + }else if( idxNum>=6 && idxNum<=8 ){ + int i, e, m, rc; + sqlite3_value *pIn, *pVal; + e = idxNum - 6; + assert( e<=argc-2 ); + pCur->i = 0; + for(m=1000, i=0; i<=e; i++, m /= 10){ + sqlite3_int64 v; + pVal = 0; + if( sqlite3_vtab_in_first(0, &pVal)!=SQLITE_MISUSE + || sqlite3_vtab_in_first(argv[iArg], &pVal)!=SQLITE_MISUSE + ){ + vt02ErrMsg(pCursor->pVtab, + "unexpected success from sqlite3_vtab_in_first()"); + return SQLITE_ERROR; + } + v = sqlite3_value_int64(argv[iArg++]); + if( v<0 ) v = 0; + if( v>9 ) v = 9; + pCur->i += m*v; + pCur->iEof = pCur->i+m; + } + pCur->mD = 0; + pIn = argv[iArg++]; + assert( sqlite3_value_type(pIn)==SQLITE_NULL ); + for( rc = sqlite3_vtab_in_first(pIn, &pVal); + rc==SQLITE_OK && pVal!=0; + rc = sqlite3_vtab_in_next(pIn, &pVal) + ){ + int eType = sqlite3_value_numeric_type(pVal); + if( eType==SQLITE_FLOAT ){ + double r = sqlite3_value_double(pVal); + if( r<0.0 || r>9.0 || r!=(int)r ) continue; + }else if( eType!=SQLITE_INTEGER ){ + continue; + } + i = sqlite3_value_int(pVal); + if( i<0 || i>9 ) continue; + pCur->mD |= 1<<i; + } + if( rc!=SQLITE_OK && rc!=SQLITE_DONE ){ + vt02ErrMsg(pCursor->pVtab, "Error from sqlite3_vtab_in_first/next()"); + return rc; + } + }else{ + goto vt02_bad_idxnum; + } + if( bUseOffset ){ + int nSkip = sqlite3_value_int(argv[iArg]); + while( nSkip-- > 0 ) vt02Next(pCursor); + } + return SQLITE_OK; + +vt02_bad_idxnum: + vt02ErrMsg(pCursor->pVtab, "invalid idxNum for vt02: %d", iOrigIdxNum); + return SQLITE_ERROR; +} + +/* Return the Nth column of the current row. +*/ +static int vt02Column( + sqlite3_vtab_cursor *pCursor, + sqlite3_context *context, + int N +){ + vt02_cur *pCur = (vt02_cur*)pCursor; + int v = pCur->i; + if( N==VT02_COL_X ){ + sqlite3_result_int(context, v); + }else if( N>=VT02_COL_A && N<=VT02_COL_D ){ + static const int iDivisor[] = { 1, 1000, 100, 10, 1 }; + v = (v/iDivisor[N])%10; + sqlite3_result_int(context, v); + } + return SQLITE_OK; +} + +/* Return the rowid of the current row +*/ +static int vt02Rowid(sqlite3_vtab_cursor *pCursor, sqlite3_int64 *pRowid){ + vt02_cur *pCur = (vt02_cur*)pCursor; + *pRowid = pCur->i+1; + return SQLITE_OK; +} + +/************************************************************************* +** Logging Subsystem +** +** The sqlite3BestIndexLog() routine implements a logging system for +** xBestIndex calls. This code is portable to any virtual table. +** +** sqlite3BestIndexLog() is the main routine, sqlite3RunSql() is a +** helper routine used for running various SQL statements as part of +** creating the log. +** +** These two routines should be portable to other virtual tables. Simply +** extract this code and call sqlite3BestIndexLog() near the end of the +** xBestIndex method in cases where logging is desired. +*/ +/* +** Run SQL on behalf of sqlite3BestIndexLog. +** +** Construct the SQL using the zFormat string and subsequent arguments. +** Or if zFormat is NULL, take the SQL as the first argument after the +** zFormat. In either case, the dynamically allocated SQL string is +** freed after it has been run. If something goes wrong with the SQL, +** then an error is left in pVTab->zErrMsg. +*/ +static void sqlite3RunSql( + sqlite3 *db, /* Run the SQL on this database connection */ + sqlite3_vtab *pVTab, /* Report errors to this virtual table */ + const char *zFormat, /* Format string for SQL, or NULL */ + ... /* Arguments, according to the format string */ +){ + char *zSql; + + va_list ap; + va_start(ap, zFormat); + if( zFormat==0 ){ + zSql = va_arg(ap, char*); + }else{ + zSql = sqlite3_vmprintf(zFormat, ap); + } + va_end(ap); + if( zSql ){ + char *zErrMsg = 0; + (void)sqlite3_exec(db, zSql, 0, 0, &zErrMsg); + if( zErrMsg ){ + if( pVTab->zErrMsg==0 ){ + pVTab->zErrMsg = sqlite3_mprintf("%s in [%s]", zErrMsg, zSql); + } + sqlite3_free(zErrMsg); + } + sqlite3_free(zSql); + } +} + +/* +** Record information about each xBestIndex method call in a separate +** table: +** +** CREATE TEMP TABLE [log-table-name] ( +** bi INT, -- BestIndex call number +** vn TEXT, -- Variable Name +** ix INT, -- Index or value +** cn TEXT, -- Column Name +** op INT, -- Opcode or argvIndex +** ux INT, -- "usable" or "omit" flag +** rx BOOLEAN, -- True if has a RHS value +** rhs ANY, -- The RHS value +** cs TEXT, -- Collating Sequence +** inop BOOLEAN -- True if this is a batchable IN operator +** ); +** +** If an error occurs, leave an error message in pVTab->zErrMsg. +*/ +static void sqlite3BestIndexLog( + sqlite3_index_info *pInfo, /* The sqlite3_index_info object */ + const char *zLogTab, /* Log into this table */ + sqlite3 *db, /* Database connection containing zLogTab */ + const char **azColname, /* Names of columns in the virtual table */ + sqlite3_vtab *pVTab /* Record errors into this object */ +){ + int i, rc; + sqlite3_str *pStr; + int iBI; + + if( sqlite3_table_column_metadata(db,0,zLogTab,0,0,0,0,0,0) ){ + /* The log table does not previously exist. Create it. */ + sqlite3RunSql(db,pVTab, + "CREATE TABLE IF NOT EXISTS temp.\"%w\"(\n" + " bi INT, -- BestIndex call number\n" + " vn TEXT, -- Variable Name\n" + " ix INT, -- Index or value\n" + " cn TEXT, -- Column Name\n" + " op INT, -- Opcode or argvIndex\n" + " ux INT, -- usable for omit flag\n" + " rx BOOLEAN, -- Right-hand side value is available\n" + " rhs ANY, -- RHS value\n" + " cs TEXT, -- Collating Sequence\n" + " inop BOOLEAN -- IN operator capable of batch reads\n" + ");", zLogTab + ); + iBI = 1; + }else{ + /* The log table does already exist. We assume that it has the + ** correct schema and proceed to find the largest prior "bi" value. + ** If the schema is wrong, errors might result. The code is able + ** to deal with this. */ + sqlite3_stmt *pStmt; + char *zSql; + zSql = sqlite3_mprintf("SELECT max(bi) FROM temp.\"%w\"",zLogTab); + if( zSql==0 ){ + sqlite3_free(pVTab->zErrMsg); + pVTab->zErrMsg = sqlite3_mprintf("out of memory"); + return; + } + rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0); + sqlite3_free(zSql); + if( rc ){ + sqlite3_free(pVTab->zErrMsg); + pVTab->zErrMsg = sqlite3_mprintf("%s", sqlite3_errmsg(db)); + iBI = 0; + }else if( sqlite3_step(pStmt)==SQLITE_ROW ){ + iBI = sqlite3_column_int(pStmt, 0)+1; + }else{ + iBI = 1; + } + sqlite3_finalize(pStmt); + } + sqlite3RunSql(db,pVTab, + "INSERT INTO temp.\"%w\"(bi,vn,ix) VALUES(%d,'nConstraint',%d)", + zLogTab, iBI, pInfo->nConstraint + ); + for(i=0; i<pInfo->nConstraint; i++){ + sqlite3_value *pVal; + char *zSql; + int iCol = pInfo->aConstraint[i].iColumn; + int op = pInfo->aConstraint[i].op; + const char *zCol; + if( op==SQLITE_INDEX_CONSTRAINT_LIMIT + || op==SQLITE_INDEX_CONSTRAINT_OFFSET + ){ + zCol = ""; + }else if( iCol<0 ){ + zCol = "rowid"; + }else{ + zCol = azColname[iCol]; + } + pStr = sqlite3_str_new(0); + sqlite3_str_appendf(pStr, + "INSERT INTO temp.\"%w\"(bi,vn,ix,cn,op,ux,rx,rhs,cs,inop)" + "VALUES(%d,'aConstraint',%d,%Q,%d,%d", + zLogTab, iBI, + i, + zCol, + op, + pInfo->aConstraint[i].usable); + pVal = 0; + rc = sqlite3_vtab_rhs_value(pInfo, i, &pVal); + assert( pVal!=0 || rc!=SQLITE_OK ); + if( rc==SQLITE_OK ){ + sqlite3_str_appendf(pStr,",1,?1"); + }else{ + sqlite3_str_appendf(pStr,",0,NULL"); + } + sqlite3_str_appendf(pStr,",%Q,%d)", + sqlite3_vtab_collation(pInfo,i), + sqlite3_vtab_in(pInfo,i,-1)); + zSql = sqlite3_str_finish(pStr); + if( zSql==0 ){ + if( pVTab->zErrMsg==0 ) pVTab->zErrMsg = sqlite3_mprintf("out of memory"); + }else{ + sqlite3_stmt *pStmt = 0; + rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0); + if( rc ){ + if( pVTab->zErrMsg==0 ){ + pVTab->zErrMsg = sqlite3_mprintf("%s", sqlite3_errmsg(db)); + } + }else{ + if( pVal ) sqlite3_bind_value(pStmt, 1, pVal); + sqlite3_step(pStmt); + rc = sqlite3_reset(pStmt); + if( rc && pVTab->zErrMsg==0 ){ + pVTab->zErrMsg = sqlite3_mprintf("%s", sqlite3_errmsg(db)); + } + } + sqlite3_finalize(pStmt); + sqlite3_free(zSql); + } + } + sqlite3RunSql(db,pVTab, + "INSERT INTO temp.\"%w\"(bi,vn,ix) VALUES(%d,'nOrderBy',%d)", + zLogTab, iBI, pInfo->nOrderBy + ); + for(i=0; i<pInfo->nOrderBy; i++){ + int iCol = pInfo->aOrderBy[i].iColumn; + sqlite3RunSql(db,pVTab, + "INSERT INTO temp.\"%w\"(bi,vn,ix,cn,op)VALUES(%d,'aOrderBy',%d,%Q,%d)", + zLogTab, iBI, + i, + iCol>=0 ? azColname[iCol] : "rowid", + pInfo->aOrderBy[i].desc + ); + } + sqlite3RunSql(db,pVTab, + "INSERT INTO temp.\"%w\"(bi,vn,ix) VALUES(%d,'sqlite3_vtab_distinct',%d)", + zLogTab, iBI, sqlite3_vtab_distinct(pInfo) + ); + sqlite3RunSql(db,pVTab, + "INSERT INTO temp.\"%w\"(bi,vn,ix) VALUES(%d,'colUsed',%lld)", + zLogTab, iBI, pInfo->colUsed + ); + for(i=0; i<pInfo->nConstraint; i++){ + int iCol = pInfo->aConstraint[i].iColumn; + int op = pInfo->aConstraint[i].op; + const char *zCol; + if( op==SQLITE_INDEX_CONSTRAINT_LIMIT + || op==SQLITE_INDEX_CONSTRAINT_OFFSET + ){ + zCol = ""; + }else if( iCol<0 ){ + zCol = "rowid"; + }else{ + zCol = azColname[iCol]; + } + sqlite3RunSql(db,pVTab, + "INSERT INTO temp.\"%w\"(bi,vn,ix,cn,op,ux)" + "VALUES(%d,'aConstraintUsage',%d,%Q,%d,%d)", + zLogTab, iBI, + i, + zCol, + pInfo->aConstraintUsage[i].argvIndex, + pInfo->aConstraintUsage[i].omit + ); + } + sqlite3RunSql(db,pVTab, + "INSERT INTO temp.\"%w\"(bi,vn,ix)VALUES(%d,'idxNum',%d)", + zLogTab, iBI, pInfo->idxNum + ); + sqlite3RunSql(db,pVTab, + "INSERT INTO temp.\"%w\"(bi,vn,ix)VALUES(%d,'estimatedCost',%f)", + zLogTab, iBI, pInfo->estimatedCost + ); + sqlite3RunSql(db,pVTab, + "INSERT INTO temp.\"%w\"(bi,vn,ix)VALUES(%d,'estimatedRows',%lld)", + zLogTab, iBI, pInfo->estimatedRows + ); + if( pInfo->idxStr ){ + sqlite3RunSql(db,pVTab, + "INSERT INTO temp.\"%w\"(bi,vn,ix)VALUES(%d,'idxStr',%Q)", + zLogTab, iBI, pInfo->idxStr + ); + sqlite3RunSql(db,pVTab, + "INSERT INTO temp.\"%w\"(bi,vn,ix)VALUES(%d,'needToFreeIdxStr',%d)", + zLogTab, iBI, pInfo->needToFreeIdxStr + ); + } + if( pInfo->nOrderBy ){ + sqlite3RunSql(db,pVTab, + "INSERT INTO temp.\"%w\"(bi,vn,ix)VALUES(%d,'orderByConsumed',%d)", + zLogTab, iBI, pInfo->orderByConsumed + ); + } +} +/* +** End of Logging Subsystem +*****************************************************************************/ + + +/* Find an estimated cost of running a query against vt02. +*/ +static int vt02BestIndex(sqlite3_vtab *pVTab, sqlite3_index_info *pInfo){ + int i; /* Loop counter */ + int isEq[5]; /* Equality constraints on X, A, B, C, and D */ + int isUsed[5]; /* Other non-== cosntraints X, A, B, C, and D */ + int argvIndex = 0; /* Next available argv[] slot */ + int iOffset = -1; /* Constraint for OFFSET */ + void *pX = 0; /* idxStr value */ + int flags = 0; /* RHS value for flags= */ + const char *zLogTab = 0; /* RHS value for logtab= */ + int iFlagTerm = -1; /* Constraint term for flags= */ + int iLogTerm = -1; /* Constraint term for logtab= */ + int iIn = -1; /* Index of the IN constraint */ + vt02_vtab *pSelf; /* This virtual table */ + + pSelf = (vt02_vtab*)pVTab; + if( pSelf->busy ){ + vt02ErrMsg(pVTab, "recursive use of vt02 prohibited"); + return SQLITE_CONSTRAINT; + } + pSelf->busy++; + + + /* Do an initial scan for flags=N and logtab=TAB constraints with + ** usable RHS values */ + for(i=0; i<pInfo->nConstraint; i++){ + sqlite3_value *pVal; + if( !pInfo->aConstraint[i].usable ) continue; + if( pInfo->aConstraint[i].op!=SQLITE_INDEX_CONSTRAINT_EQ ) continue; + switch( pInfo->aConstraint[i].iColumn ){ + case VT02_COL_FLAGS: + if( sqlite3_vtab_rhs_value(pInfo, i, &pVal)==SQLITE_OK + && sqlite3_value_type(pVal)==SQLITE_INTEGER + ){ + flags = sqlite3_value_int(pVal); + } + iFlagTerm = i; + break; + case VT02_COL_LOGTAB: + if( sqlite3_vtab_rhs_value(pInfo, i, &pVal)==SQLITE_OK + && sqlite3_value_type(pVal)==SQLITE_TEXT + ){ + zLogTab = (const char*)sqlite3_value_text(pVal); + } + iLogTerm = i; + break; + } + } + + /* Do a second scan to actually analyze the index information */ + memset(isEq, 0xff, sizeof(isEq)); + memset(isUsed, 0xff, sizeof(isUsed)); + for(i=0; i<pInfo->nConstraint; i++){ + int j = pInfo->aConstraint[i].iColumn; + if( j>=VT02_COL_FLAGS ) continue; + if( pInfo->aConstraint[i].usable==0 + && (flags & VT02_IGNORE_USABLE)==0 ) continue; + if( j<0 ) j = VT02_COL_X; + switch( pInfo->aConstraint[i].op ){ + case SQLITE_INDEX_CONSTRAINT_FUNCTION: + case SQLITE_INDEX_CONSTRAINT_EQ: + isEq[j] = i; + break; + case SQLITE_INDEX_CONSTRAINT_LT: + case SQLITE_INDEX_CONSTRAINT_LE: + case SQLITE_INDEX_CONSTRAINT_GT: + case SQLITE_INDEX_CONSTRAINT_GE: + isUsed[j] = i; + break; + case SQLITE_INDEX_CONSTRAINT_OFFSET: + iOffset = i; + break; + } + } + + /* Use the analysis to find an appropriate query plan */ + if( isEq[0]>=0 ){ + /* A constraint of X= takes priority */ + pInfo->estimatedCost = 1; + pInfo->aConstraintUsage[isEq[0]].argvIndex = ++argvIndex; + if( flags & 0x20 ) pInfo->aConstraintUsage[isEq[0]].omit = 1; + pInfo->idxNum = 1; + }else if( isEq[1]<0 ){ + /* If there is no X= nor A= then we have to do a full scan */ + pInfo->idxNum = 0; + pInfo->estimatedCost = 10000; + }else{ + int v = 1000; + pInfo->aConstraintUsage[isEq[1]].argvIndex = ++argvIndex; + if( flags & 0x20 ) pInfo->aConstraintUsage[isEq[1]].omit = 1; + for(i=2; i<=4 && isEq[i]>=0; i++){ + if( i==4 && sqlite3_vtab_in(pInfo, isEq[4], 0) ) break; + pInfo->aConstraintUsage[isEq[i]].argvIndex = ++argvIndex; + if( flags & 0x20 ) pInfo->aConstraintUsage[isEq[i]].omit = 1; + v /= 10; + } + pInfo->idxNum = i; + if( isEq[4]>=0 && sqlite3_vtab_in(pInfo,isEq[4],1) ){ + iIn = isEq[4]; + pInfo->aConstraintUsage[iIn].argvIndex = ++argvIndex; + if( flags & 0x20 ) pInfo->aConstraintUsage[iIn].omit = 1; + v /= 5; + i++; + pInfo->idxNum += 4; + } + pInfo->estimatedCost = v; + } + pInfo->estimatedRows = (sqlite3_int64)pInfo->estimatedCost; + + /* Attempt to consume the ORDER BY clause. Except, always leave + ** orderByConsumed set to 0 for vt02_no_sort_opt. In this way, + ** we can compare vt02 and vt02_no_sort_opt to ensure they get + ** the same answer. + */ + if( pInfo->nOrderBy>0 && (flags & VT02_NO_SORT_OPT)==0 ){ + if( pInfo->idxNum==1 ){ + /* There will only be one row of output. So it is always sorted. */ + pInfo->orderByConsumed = 1; + }else + if( pInfo->aOrderBy[0].iColumn<=0 + && pInfo->aOrderBy[0].desc==0 + ){ + /* First column of order by is X ascending */ + pInfo->orderByConsumed = 1; + }else + if( sqlite3_vtab_distinct(pInfo)>=1 ){ + unsigned int x = 0; + for(i=0; i<pInfo->nOrderBy; i++){ + int iCol = pInfo->aOrderBy[i].iColumn; + if( iCol<0 ) iCol = 0; + x |= 1<<iCol; + } + if( sqlite3_vtab_distinct(pInfo)==2 ){ + if( x==0x02 ){ + /* DISTINCT A */ + pInfo->idxNum += 30; + pInfo->orderByConsumed = 1; + }else if( x==0x06 ){ + /* DISTINCT A,B */ + pInfo->idxNum += 20; + pInfo->orderByConsumed = 1; + }else if( x==0x0e ){ + /* DISTINCT A,B,C */ + pInfo->idxNum += 10; + pInfo->orderByConsumed = 1; + }else if( x & 0x01 ){ + /* DISTINCT X */ + pInfo->orderByConsumed = 1; + }else if( x==0x1e ){ + /* DISTINCT A,B,C,D */ + pInfo->orderByConsumed = 1; + } + }else{ + if( x==0x02 ){ + /* GROUP BY A */ + pInfo->orderByConsumed = 1; + }else if( x==0x06 ){ + /* GROUP BY A,B */ + pInfo->orderByConsumed = 1; + }else if( x==0x0e ){ + /* GROUP BY A,B,C */ + pInfo->orderByConsumed = 1; + }else if( x & 0x01 ){ + /* GROUP BY X */ + pInfo->orderByConsumed = 1; + }else if( x==0x1e ){ + /* GROUP BY A,B,C,D */ + pInfo->orderByConsumed = 1; + } + } + } + } + + if( flags & VT02_ALLOC_IDXSTR ){ + pInfo->idxStr = sqlite3_mprintf("test"); + pInfo->needToFreeIdxStr = 1; + } + if( flags & VT02_BAD_IDXNUM ){ + pInfo->idxNum += 1000; + } + + if( iOffset>=0 ){ + pInfo->aConstraintUsage[iOffset].argvIndex = ++argvIndex; + if( (flags & VT02_NO_OFFSET)==0 + && (pInfo->nOrderBy==0 || pInfo->orderByConsumed) + ){ + pInfo->aConstraintUsage[iOffset].omit = 1; + pInfo->idxNum += 100; + } + } + + + /* Always omit flags= and logtab= constraints to prevent them from + ** interfering with the bytecode. Put them at the end of the argv[] + ** array to keep them out of the way. + */ + if( iFlagTerm>=0 ){ + pInfo->aConstraintUsage[iFlagTerm].omit = 1; + pInfo->aConstraintUsage[iFlagTerm].argvIndex = ++argvIndex; + } + if( iLogTerm>=0 ){ + pInfo->aConstraintUsage[iLogTerm].omit = 1; + pInfo->aConstraintUsage[iLogTerm].argvIndex = ++argvIndex; + } + + /* The 0x40 flag means add all usable constraints to the output set */ + if( flags & 0x40 ){ + for(i=0; i<pInfo->nConstraint; i++){ + if( pInfo->aConstraint[i].usable + && pInfo->aConstraintUsage[i].argvIndex==0 + ){ + pInfo->aConstraintUsage[i].argvIndex = ++argvIndex; + if( flags & 0x20 ) pInfo->aConstraintUsage[i].omit = 1; + } + } + } + + + /* Generate the log if requested */ + if( zLogTab ){ + static const char *azColname[] = { + "x", "a", "b", "c", "d", "flags", "logtab" + }; + sqlite3 *db = ((vt02_vtab*)pVTab)->db; + sqlite3BestIndexLog(pInfo, zLogTab, db, azColname, pVTab); + } + pSelf->busy--; + + /* Try to do a memory allocation solely for the purpose of causing + ** an error under OOM testing loops */ + pX = sqlite3_malloc(800); + if( pX==0 ) return SQLITE_NOMEM; + sqlite3_free(pX); + + return pVTab->zErrMsg!=0 ? SQLITE_ERROR : SQLITE_OK; +} + +/* This is the sqlite3_module definition for the the virtual table defined +** by this include file. +*/ +const sqlite3_module vt02Module = { + /* iVersion */ 2, + /* xCreate */ 0, /* This is an eponymous table */ + /* xConnect */ vt02Connect, + /* xBestIndex */ vt02BestIndex, + /* xDisconnect */ vt02Disconnect, + /* xDestroy */ vt02Disconnect, + /* xOpen */ vt02Open, + /* xClose */ vt02Close, + /* xFilter */ vt02Filter, + /* xNext */ vt02Next, + /* xEof */ vt02Eof, + /* xColumn */ vt02Column, + /* xRowid */ vt02Rowid, + /* xUpdate */ 0, + /* xBegin */ 0, + /* xSync */ 0, + /* xCommit */ 0, + /* xRollback */ 0, + /* xFindFunction */ 0, + /* xRename */ 0, + /* xSavepoint */ 0, + /* xRelease */ 0, + /* xRollbackTo */ 0 +}; + +static void vt02CoreInit(sqlite3 *db){ + static const char zPkXSchema[] = + "CREATE TABLE x(x INT NOT NULL PRIMARY KEY, a INT, b INT, c INT, d INT," + " flags INT HIDDEN, logtab TEXT HIDDEN);"; + static const char zPkABCDSchema[] = + "CREATE TABLE x(x INT, a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, " + "d INT NOT NULL, flags INT HIDDEN, logtab TEXT HIDDEN, " + "PRIMARY KEY(a,b,c,d));"; + sqlite3_create_module(db, "vt02", &vt02Module, 0); + sqlite3_create_module(db, "vt02pkx", &vt02Module, (void*)zPkXSchema); + sqlite3_create_module(db, "vt02pkabcd", &vt02Module, (void*)zPkABCDSchema); +} + +#ifdef TH3_VERSION +static void vt02_init(th3state *p, int iDb, char *zArg){ + vt02CoreInit(th3dbPointer(p, iDb)); +} +#else +#ifdef _WIN32 +__declspec(dllexport) +#endif +int sqlite3_vt02_init( + sqlite3 *db, + char **pzErrMsg, + const sqlite3_api_routines *pApi +){ + SQLITE_EXTENSION_INIT2(pApi); + vt02CoreInit(db); + return SQLITE_OK; +} +#endif /* TH3_VERSION */ |