Index: main.mk ================================================================== --- main.mk +++ main.mk @@ -283,10 +283,11 @@ $(TOP)/src/test8.c \ $(TOP)/src/test9.c \ $(TOP)/src/test_autoext.c \ $(TOP)/src/test_async.c \ $(TOP)/src/test_backup.c \ + $(TOP)/src/test_bestindex.c \ $(TOP)/src/test_blob.c \ $(TOP)/src/test_btree.c \ $(TOP)/src/test_config.c \ $(TOP)/src/test_demovfs.c \ $(TOP)/src/test_devsym.c \ Index: src/tclsqlite.c ================================================================== --- src/tclsqlite.c +++ src/tclsqlite.c @@ -3772,10 +3772,11 @@ extern int Sqlitemultiplex_Init(Tcl_Interp*); extern int SqliteSuperlock_Init(Tcl_Interp*); extern int SqlitetestSyscall_Init(Tcl_Interp*); extern int Fts5tcl_Init(Tcl_Interp *); extern int SqliteRbu_Init(Tcl_Interp*); + extern int Sqlitetesttcl_Init(Tcl_Interp*); #if defined(SQLITE_ENABLE_FTS3) || defined(SQLITE_ENABLE_FTS4) extern int Sqlitetestfts3_Init(Tcl_Interp *interp); #endif #ifdef SQLITE_ENABLE_ZIPVFS @@ -3816,10 +3817,11 @@ Sqlitemultiplex_Init(interp); SqliteSuperlock_Init(interp); SqlitetestSyscall_Init(interp); Fts5tcl_Init(interp); SqliteRbu_Init(interp); + Sqlitetesttcl_Init(interp); #if defined(SQLITE_ENABLE_FTS3) || defined(SQLITE_ENABLE_FTS4) Sqlitetestfts3_Init(interp); #endif ADDED src/test_bestindex.c Index: src/test_bestindex.c ================================================================== --- /dev/null +++ src/test_bestindex.c @@ -0,0 +1,442 @@ +/* +** 2013 Jan 11 +** +** 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. +** +************************************************************************* +** Code for testing the virtual table xBestIndex method and the query +** planner. +*/ + + +/* +** INSTRUCTIONS +** +** This module exports a single tcl command - [register_tcl_module]. When +** invoked, it registers a special virtual table module with a database +** connection. +** +** The virtual table is currently read-only. And always returns zero rows. +** It is created with a single argument - the name of a Tcl command - as +** follows: +** +** CREATE VIRTUAL TABLE x1 USING tcl(tcl_command); +** +** The command [tcl_command] is invoked when the table is first created (or +** connected) and when the xBestIndex() method is invoked. When it is created +** (or connected), it is invoked as follows: +** +** tcl_command xConnect +** +** In this case the return value of the script is passed to the +** sqlite3_declare_vtab() function to create the virtual table schema. +** +** When the xBestIndex() method is called by SQLite, the Tcl command is +** invoked as: +** +** tcl_command xBestIndex CONSTRAINTS ORDERBY MASK +** +** where CONSTRAINTS is a tcl representation of the aConstraints[] array, +** ORDERBY is a representation of the contents of the aOrderBy[] array and +** MASK is a copy of sqlite3_index_info.colUsed. For example if the virtual +** table is declared as: +** +** CREATE TABLE x1(a, b, c) +** +** and the query is: +** +** SELECT * FROM x1 WHERE a=? AND c module name ("fs") +** argv[1] -> database name +** argv[2] -> table name +** argv[...] -> other module argument fields. +*/ +static int tclConnect( + sqlite3 *db, + void *pAux, + int argc, const char *const*argv, + sqlite3_vtab **ppVtab, + char **pzErr +){ + Tcl_Interp *interp = (Tcl_Interp*)pAux; + tcl_vtab *pTab; + const char *zCmd; + Tcl_Obj *pScript = 0; + int rc; + + if( argc!=4 ){ + *pzErr = sqlite3_mprintf("wrong number of arguments"); + return SQLITE_ERROR; + } + zCmd = argv[3]; + + pTab = (tcl_vtab*)sqlite3_malloc(sizeof(tcl_vtab)); + if( pTab==0 ) return SQLITE_NOMEM; + memset(pTab, 0, sizeof(tcl_vtab)); + + pTab->pCmd = Tcl_NewStringObj(zCmd, -1); + pTab->interp = interp; + Tcl_IncrRefCount(pTab->pCmd); + + pScript = Tcl_DuplicateObj(pTab->pCmd); + Tcl_IncrRefCount(pScript); + Tcl_ListObjAppendElement(interp, pScript, Tcl_NewStringObj("xConnect", -1)); + + rc = Tcl_EvalObjEx(interp, pScript, TCL_EVAL_GLOBAL); + if( rc!=TCL_OK ){ + *pzErr = sqlite3_mprintf("%s", Tcl_GetStringResult(interp)); + rc = SQLITE_ERROR; + }else{ + rc = sqlite3_declare_vtab(db, Tcl_GetStringResult(interp)); + } + + if( rc!=SQLITE_OK ){ + sqlite3_free(pTab); + pTab = 0; + } + + *ppVtab = &pTab->base; + return rc; +} + +/* The xDisconnect and xDestroy methods are also the same */ +static int tclDisconnect(sqlite3_vtab *pVtab){ + tcl_vtab *pTab = (tcl_vtab*)pVtab; + Tcl_DecrRefCount(pTab->pCmd); + sqlite3_free(pTab); + return SQLITE_OK; +} + +/* +** Open a new tcl cursor. +*/ +static int tclOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){ + tcl_cursor *pCur; + pCur = sqlite3_malloc(sizeof(tcl_cursor)); + if( pCur==0 ) return SQLITE_NOMEM; + memset(pCur, 0, sizeof(tcl_cursor)); + *ppCursor = &pCur->base; + return SQLITE_OK; +} + +/* +** Close a tcl cursor. +*/ +static int tclClose(sqlite3_vtab_cursor *cur){ + tcl_cursor *pCur = (tcl_cursor *)cur; + sqlite3_free(pCur); + return SQLITE_OK; +} + +static int tclNext(sqlite3_vtab_cursor *cur){ + return SQLITE_OK; +} + +static int tclFilter( + sqlite3_vtab_cursor *pVtabCursor, + int idxNum, const char *idxStr, + int argc, sqlite3_value **argv +){ + return SQLITE_OK; +} + +static int tclColumn(sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int i){ + return SQLITE_OK; +} + +static int tclRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){ + return SQLITE_OK; +} + +static int tclEof(sqlite3_vtab_cursor *cur){ + return 1; +} + +static int tclBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){ + tcl_vtab *pTab = (tcl_vtab*)tab; + Tcl_Interp *interp = pTab->interp; + Tcl_Obj *pArg; + Tcl_Obj *pScript; + int ii; + int rc = SQLITE_OK; + + pScript = Tcl_DuplicateObj(pTab->pCmd); + Tcl_IncrRefCount(pScript); + Tcl_ListObjAppendElement(interp, pScript, Tcl_NewStringObj("xBestIndex", -1)); + + pArg = Tcl_NewObj(); + Tcl_IncrRefCount(pArg); + for(ii=0; iinConstraint; ii++){ + struct sqlite3_index_constraint const *pCons = &pIdxInfo->aConstraint[ii]; + Tcl_Obj *pElem = Tcl_NewObj(); + const char *zOp = "?"; + + Tcl_IncrRefCount(pElem); + + switch( pCons->op ){ + case SQLITE_INDEX_CONSTRAINT_EQ: + zOp = "eq"; break; + case SQLITE_INDEX_CONSTRAINT_GT: + zOp = "gt"; break; + case SQLITE_INDEX_CONSTRAINT_LE: + zOp = "le"; break; + case SQLITE_INDEX_CONSTRAINT_LT: + zOp = "lt"; break; + case SQLITE_INDEX_CONSTRAINT_GE: + zOp = "ge"; break; + case SQLITE_INDEX_CONSTRAINT_MATCH: + zOp = "match"; break; + case SQLITE_INDEX_CONSTRAINT_LIKE: + zOp = "like"; break; + case SQLITE_INDEX_CONSTRAINT_GLOB: + zOp = "glob"; break; + case SQLITE_INDEX_CONSTRAINT_REGEXP: + zOp = "regexp"; break; + } + + Tcl_ListObjAppendElement(0, pElem, Tcl_NewStringObj("op", -1)); + Tcl_ListObjAppendElement(0, pElem, Tcl_NewStringObj(zOp, -1)); + Tcl_ListObjAppendElement(0, pElem, Tcl_NewStringObj("column", -1)); + Tcl_ListObjAppendElement(0, pElem, Tcl_NewIntObj(pCons->iColumn)); + Tcl_ListObjAppendElement(0, pElem, Tcl_NewStringObj("usable", -1)); + Tcl_ListObjAppendElement(0, pElem, Tcl_NewIntObj(pCons->usable)); + + Tcl_ListObjAppendElement(0, pArg, pElem); + Tcl_DecrRefCount(pElem); + } + + Tcl_ListObjAppendElement(0, pScript, pArg); + Tcl_DecrRefCount(pArg); + + pArg = Tcl_NewObj(); + Tcl_IncrRefCount(pArg); + for(ii=0; iinOrderBy; ii++){ + struct sqlite3_index_orderby const *pOrder = &pIdxInfo->aOrderBy[ii]; + Tcl_Obj *pElem = Tcl_NewObj(); + Tcl_IncrRefCount(pElem); + + Tcl_ListObjAppendElement(0, pElem, Tcl_NewStringObj("column", -1)); + Tcl_ListObjAppendElement(0, pElem, Tcl_NewIntObj(pOrder->iColumn)); + Tcl_ListObjAppendElement(0, pElem, Tcl_NewStringObj("desc", -1)); + Tcl_ListObjAppendElement(0, pElem, Tcl_NewIntObj(pOrder->desc)); + + Tcl_ListObjAppendElement(0, pArg, pElem); + Tcl_DecrRefCount(pElem); + } + + Tcl_ListObjAppendElement(0, pScript, pArg); + Tcl_DecrRefCount(pArg); + + Tcl_ListObjAppendElement(0, pScript, Tcl_NewWideIntObj(pIdxInfo->colUsed)); + + rc = Tcl_EvalObjEx(interp, pScript, TCL_EVAL_GLOBAL); + Tcl_DecrRefCount(pScript); + if( rc!=TCL_OK ){ + const char *zErr = Tcl_GetStringResult(interp); + rc = SQLITE_ERROR; + pTab->base.zErrMsg = sqlite3_mprintf("%s", zErr); + }else{ + /* Analyze the scripts return value. The return value should be a tcl + ** list object with an even number of elements. The first element of each + ** pair must be one of: + ** + ** "orderby" (value of orderByConsumed flag) + ** "cost" (value of estimatedCost field) + ** "rows" (value of estimatedRows field) + ** "use" (index of used constraint in aConstraint[]) + ** "idxnum" (value of idxNum field) + ** "idxstr" (value of idxStr field) + ** "omit" (index of omitted constraint in aConstraint[]) + */ + Tcl_Obj *pRes = Tcl_GetObjResult(interp); + Tcl_Obj **apElem = 0; + int nElem; + rc = Tcl_ListObjGetElements(interp, pRes, &nElem, &apElem); + if( rc!=TCL_OK ){ + const char *zErr = Tcl_GetStringResult(interp); + rc = SQLITE_ERROR; + pTab->base.zErrMsg = sqlite3_mprintf("%s", zErr); + }else{ + int iArgv = 1; + for(ii=0; rc==SQLITE_OK && iiestimatedCost); + }else + if( sqlite3_stricmp("orderby", zCmd)==0 ){ + rc = Tcl_GetIntFromObj(interp, p, &pIdxInfo->orderByConsumed); + }else + if( sqlite3_stricmp("idxnum", zCmd)==0 ){ + rc = Tcl_GetIntFromObj(interp, p, &pIdxInfo->idxNum); + }else + if( sqlite3_stricmp("idxstr", zCmd)==0 ){ + sqlite3_free(pIdxInfo->idxStr); + pIdxInfo->idxStr = sqlite3_mprintf("%s", Tcl_GetString(p)); + pIdxInfo->needToFreeIdxStr = 1; + }else + if( sqlite3_stricmp("rows", zCmd)==0 ){ + rc = Tcl_GetWideIntFromObj(interp, p, &pIdxInfo->estimatedRows); + }else + if( sqlite3_stricmp("use", zCmd)==0 + || sqlite3_stricmp("omit", zCmd)==0 + ){ + int iCons; + rc = Tcl_GetIntFromObj(interp, p, &iCons); + if( rc==SQLITE_OK ){ + if( iCons<0 || iCons>=pIdxInfo->nConstraint ){ + rc = SQLITE_ERROR; + pTab->base.zErrMsg = sqlite3_mprintf("unexpected: %d", iCons); + }else{ + int bOmit = (zCmd[0]=='o' || zCmd[0]=='O'); + pIdxInfo->aConstraintUsage[iCons].argvIndex = iArgv++; + pIdxInfo->aConstraintUsage[iCons].omit = bOmit; + } + } + }else{ + rc = SQLITE_ERROR; + pTab->base.zErrMsg = sqlite3_mprintf("unexpected: %s", zCmd); + } + if( rc!=SQLITE_OK && pTab->base.zErrMsg==0 ){ + const char *zErr = Tcl_GetStringResult(interp); + pTab->base.zErrMsg = sqlite3_mprintf("%s", zErr); + } + } + } + } + + return rc; +} + +/* +** A virtual table module that provides read-only access to a +** Tcl global variable namespace. +*/ +static sqlite3_module tclModule = { + 0, /* iVersion */ + tclConnect, + tclConnect, + tclBestIndex, + tclDisconnect, + tclDisconnect, + tclOpen, /* xOpen - open a cursor */ + tclClose, /* xClose - close a cursor */ + tclFilter, /* xFilter - configure scan constraints */ + tclNext, /* xNext - advance a cursor */ + tclEof, /* xEof - check for end of scan */ + tclColumn, /* xColumn - read data */ + tclRowid, /* xRowid - read data */ + 0, /* xUpdate */ + 0, /* xBegin */ + 0, /* xSync */ + 0, /* xCommit */ + 0, /* xRollback */ + 0, /* xFindMethod */ + 0, /* xRename */ +}; + +/* +** Decode a pointer to an sqlite3 object. +*/ +extern int getDbPointer(Tcl_Interp *interp, const char *zA, sqlite3 **ppDb); + +/* +** Register the echo virtual table module. +*/ +static int register_tcl_module( + ClientData clientData, /* Pointer to sqlite3_enable_XXX function */ + Tcl_Interp *interp, /* The TCL interpreter that invoked this command */ + int objc, /* Number of arguments */ + Tcl_Obj *CONST objv[] /* Command arguments */ +){ + sqlite3 *db; + if( objc!=2 ){ + Tcl_WrongNumArgs(interp, 1, objv, "DB"); + return TCL_ERROR; + } + if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR; +#ifndef SQLITE_OMIT_VIRTUALTABLE + sqlite3_create_module(db, "tcl", &tclModule, (void *)interp); +#endif + return TCL_OK; +} + +#endif + + +/* +** Register commands with the TCL interpreter. +*/ +int Sqlitetesttcl_Init(Tcl_Interp *interp){ +#ifndef SQLITE_OMIT_VIRTUALTABLE + static struct { + char *zName; + Tcl_ObjCmdProc *xProc; + void *clientData; + } aObjCmd[] = { + { "register_tcl_module", register_tcl_module, 0 }, + }; + int i; + for(i=0; i