/* ** 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 #include #include #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->iiEof ); 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_ERROR ){ 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<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 && pCur->iiEof ) 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)" "RETURNING iif(bi=%d,'ok',RAISE(ABORT,'wrong trigger'))", /* The RETURNING clause checks to see that the returning trigger fired ** for the correct INSERT in the case of nested INSERT RETURNINGs. */ zLogTab, iBI, pInfo->nConstraint, iBI ); for(i=0; inConstraint; 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; inOrderBy; 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; inConstraint; 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; inConstraint; 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; inConstraint; 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; inOrderBy; i++){ int iCol = pInfo->aOrderBy[i].iColumn; if( iCol<0 ) iCol = 0; x |= 1<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; inConstraint; 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, /* xShadowName */ 0, /* xIntegrity */ 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 */