| /* |
| ** 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_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<<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 && pCur->i<pCur->iEof ) 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 */ |