ADDED ext/expert/expert1.test Index: ext/expert/expert1.test ================================================================== --- ext/expert/expert1.test +++ ext/expert/expert1.test @@ -0,0 +1,227 @@ +# 2009 Nov 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. +# +#*********************************************************************** +# +# The focus of this file is testing the CLI shell tool. Specifically, +# the ".recommend" command. +# +# + +# Test plan: +# +# +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix expert1 + +if {$tcl_platform(platform)=="windows"} { + set CMD "sqlite3_expert.exe" +} else { + set CMD ".././sqlite3_expert" +} + +proc squish {txt} { + regsub -all {[[:space:]]+} $txt { } +} + +proc do_setup_rec_test {tn setup sql res} { + reset_db + db eval $setup + uplevel [list do_rec_test $tn $sql $res] +} + +foreach {tn setup} { + 1 { + if {![file executable $CMD]} { continue } + + proc do_rec_test {tn sql res} { + set res [squish [string trim $res]] + set tst [subst -nocommands { + squish [string trim [exec $::CMD -verbose 0 -sql {$sql;} test.db]] + }] + uplevel [list do_test $tn $tst $res] + } + } + 2 { + if {[info commands sqlite3_expert_new]==""} { continue } + + proc do_rec_test {tn sql res} { + set expert [sqlite3_expert_new db] + $expert sql $sql + $expert analyze + + set result [list] + for {set i 0} {$i < [$expert count]} {incr i} { + lappend result [string trim [$expert report $i indexes]] + lappend result [string trim [$expert report $i plan]] + } + + $expert destroy + + set tst [subst -nocommands {set {} [squish [join {$result}]]}] + uplevel [list do_test $tn $tst [string trim [squish $res]]] + } + } +} { + + eval $setup + + +do_setup_rec_test $tn.1.1 { CREATE TABLE t1(a, b, c) } { + SELECT * FROM t1 +} { + (no new indexes) + 0|0|0|SCAN TABLE t1 +} + +do_setup_rec_test $tn.1.2 { + CREATE TABLE t1(a, b, c); +} { + SELECT * FROM t1 WHERE b>?; +} { + CREATE INDEX t1_idx_00000062 ON t1(b); + 0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_00000062 (b>?) +} + +do_setup_rec_test $tn.1.3 { + CREATE TABLE t1(a, b, c); +} { + SELECT * FROM t1 WHERE b COLLATE nocase BETWEEN ? AND ? +} { + CREATE INDEX t1_idx_3e094c27 ON t1(b COLLATE NOCASE); + 0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_3e094c27 (b>? AND b? AND b #include #include typedef sqlite3_int64 i64; typedef sqlite3_uint64 u64; typedef struct IdxConstraint IdxConstraint; -typedef struct IdxContext IdxContext; typedef struct IdxScan IdxScan; typedef struct IdxStatement IdxStatement; typedef struct IdxWhere IdxWhere; typedef struct IdxColumn IdxColumn; @@ -87,23 +88,10 @@ struct IdxTable { int nCol; IdxColumn *aCol; }; -/* -** Context object passed to idxWhereInfo() and other functions. -*/ -struct IdxContext { - char **pzErrmsg; - IdxWhere *pCurrent; /* Current where clause */ - int rc; /* Error code (if error has occurred) */ - IdxScan *pScan; /* List of scan objects */ - sqlite3 *dbm; /* In-memory db for this analysis */ - sqlite3 *db; /* User database under analysis */ - sqlite3_stmt *pInsertMask; /* To write to aux.depmask */ -}; - struct IdxStatement { int iId; /* Statement number */ char *zSql; /* SQL statement */ char *zIdx; /* Indexes */ char *zEQP; /* Plan */ @@ -299,11 +287,11 @@ /* ** sqlite3_whereinfo_hook() callback. */ static void idxWhereInfo( - void *pCtx, /* Pointer to IdxContext structure */ + void *pCtx, /* Pointer to sqlite3expert structure */ int eOp, const char *zVal, int iVal, u64 mask ){ @@ -791,25 +779,51 @@ idxHash64Clear(&hMask); } return rc; } + +static void idxConstraintFree(IdxConstraint *pConstraint){ + IdxConstraint *pNext; + IdxConstraint *p; + + for(p=pConstraint; p; p=pNext){ + pNext = p->pNext; + sqlite3_free(p); + } +} /* ** Free all elements of the linked list starting from pScan up until pLast ** (pLast is not freed). */ static void idxScanFree(IdxScan *pScan, IdxScan *pLast){ - /* TODO! */ + IdxScan *p; + IdxScan *pNext; + for(p=pScan; p!=pLast; p=pNext){ + pNext = p->pNextScan; + idxConstraintFree(p->pOrder); + idxConstraintFree(p->where.pEq); + idxConstraintFree(p->where.pRange); + sqlite3_free(p->pTable); + sqlite3_free(p); + } } /* ** Free all elements of the linked list starting from pStatement up ** until pLast (pLast is not freed). */ static void idxStatementFree(IdxStatement *pStatement, IdxStatement *pLast){ - /* TODO! */ + IdxStatement *p; + IdxStatement *pNext; + for(p=pStatement; p!=pLast; p=pNext){ + pNext = p->pNext; + sqlite3_free(p->zEQP); + sqlite3_free(p->zIdx); + sqlite3_free(p); + } } int idxFindIndexes( sqlite3expert *p, @@ -873,10 +887,11 @@ idxFinalize(&rc, pExplain); } find_indexes_out: + idxHashClear(&hIdx); return rc; } /* ** Allocate a new sqlite3expert object. @@ -972,11 +987,10 @@ /* Load IdxTable objects */ for(pIter=p->pScan; pIter && rc==SQLITE_OK; pIter=pIter->pNextScan){ rc = idxGetTableInfo(p->dbm, pIter, pzErr); } - /* Create candidate indexes within the in-memory database file */ if( rc==SQLITE_OK ){ rc = idxCreateCandidates(p, pzErr); } @@ -990,16 +1004,23 @@ p->bRun = 1; } return rc; } +/* +** Return the total number of statements that have been added to this +** sqlite3expert using sqlite3_expert_sql(). +*/ int sqlite3_expert_count(sqlite3expert *p){ int nRet = 0; if( p->pStatement ) nRet = p->pStatement->iId+1; return nRet; } +/* +** Return a component of the report. +*/ const char *sqlite3_expert_report(sqlite3expert *p, int iStmt, int eReport){ const char *zRet = 0; IdxStatement *pStmt; if( p->bRun==0 ) return 0; @@ -1025,8 +1046,11 @@ */ void sqlite3_expert_destroy(sqlite3expert *p){ sqlite3_close(p->dbm); idxScanFree(p->pScan, 0); idxStatementFree(p->pStatement, 0); + idxHashClear(&p->hIdx); sqlite3_free(p); } + +#endif /* !defined(SQLITE_TEST) || defined(SQLITE_ENABLE_WHEREINFO_HOOK) */ ADDED ext/expert/test_expert.c Index: ext/expert/test_expert.c ================================================================== --- ext/expert/test_expert.c +++ ext/expert/test_expert.c @@ -0,0 +1,218 @@ +/* +** 2017 April 07 +** +** 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. +** +************************************************************************* +*/ + +#if defined(SQLITE_TEST) && defined(SQLITE_ENABLE_WHEREINFO_HOOK) + +#include "sqlite3expert.h" +#include +#include + +#if defined(INCLUDE_SQLITE_TCL_H) +# include "sqlite_tcl.h" +#else +# include "tcl.h" +# ifndef SQLITE_TCLAPI +# define SQLITE_TCLAPI +# endif +#endif + +/* +** Extract an sqlite3* db handle from the object passed as the second +** argument. If successful, set *pDb to point to the db handle and return +** TCL_OK. Otherwise, return TCL_ERROR. +*/ +static int dbHandleFromObj(Tcl_Interp *interp, Tcl_Obj *pObj, sqlite3 **pDb){ + Tcl_CmdInfo info; + if( 0==Tcl_GetCommandInfo(interp, Tcl_GetString(pObj), &info) ){ + Tcl_AppendResult(interp, "no such handle: ", Tcl_GetString(pObj), 0); + return TCL_ERROR; + } + + *pDb = *(sqlite3 **)info.objClientData; + return TCL_OK; +} + + +/* +** Tclcmd: $expert sql SQL +** $expert analyze +** $expert count +** $expert report STMT EREPORT +** $expert destroy +*/ +static int SQLITE_TCLAPI testExpertCmd( + void *clientData, + Tcl_Interp *interp, + int objc, + Tcl_Obj *CONST objv[] +){ + sqlite3expert *pExpert = (sqlite3expert*)clientData; + struct Subcmd { + const char *zSub; + int nArg; + const char *zMsg; + } aSub[] = { + { "sql", 1, "TABLE", }, /* 0 */ + { "analyze", 0, "", }, /* 1 */ + { "count", 0, "", }, /* 2 */ + { "report", 2, "STMT EREPORT", }, /* 3 */ + { "destroy", 0, "", }, /* 4 */ + { 0 } + }; + int iSub; + int rc = TCL_OK; + char *zErr = 0; + + if( objc<2 ){ + Tcl_WrongNumArgs(interp, 1, objv, "SUBCOMMAND ..."); + return TCL_ERROR; + } + rc = Tcl_GetIndexFromObjStruct(interp, + objv[1], aSub, sizeof(aSub[0]), "sub-command", 0, &iSub + ); + if( rc!=TCL_OK ) return rc; + if( objc!=2+aSub[iSub].nArg ){ + Tcl_WrongNumArgs(interp, 2, objv, aSub[iSub].zMsg); + return TCL_ERROR; + } + + switch( iSub ){ + case 0: { /* sql */ + char *zArg = Tcl_GetString(objv[2]); + rc = sqlite3_expert_sql(pExpert, zArg, &zErr); + break; + } + + case 1: { /* analyze */ + rc = sqlite3_expert_analyze(pExpert, &zErr); + break; + } + + case 2: { /* count */ + int n = sqlite3_expert_count(pExpert); + Tcl_SetObjResult(interp, Tcl_NewIntObj(n)); + break; + } + + case 3: { /* report */ + const char *aEnum[] = { + "sql", "indexes", "plan", 0 + }; + int iEnum; + int iStmt; + const char *zReport; + + if( Tcl_GetIntFromObj(interp, objv[2], &iStmt) + || Tcl_GetIndexFromObj(interp, objv[3], aEnum, "report", 0, &iEnum) + ){ + return TCL_ERROR; + } + + assert( EXPERT_REPORT_SQL==1 ); + assert( EXPERT_REPORT_INDEXES==2 ); + assert( EXPERT_REPORT_PLAN==3 ); + zReport = sqlite3_expert_report(pExpert, iStmt, 1+iEnum); + Tcl_SetObjResult(interp, Tcl_NewStringObj(zReport, -1)); + break; + } + + default: /* destroy */ + assert( iSub==4 ); + Tcl_DeleteCommand(interp, Tcl_GetString(objv[0])); + break; + } + + if( rc!=TCL_OK ){ + if( zErr ){ + Tcl_SetObjResult(interp, Tcl_NewStringObj(zErr, -1)); + }else{ + extern const char *sqlite3ErrName(int); + Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3ErrName(rc), -1)); + } + } + sqlite3_free(zErr); + return rc; +} + +static void SQLITE_TCLAPI testExpertDel(void *clientData){ + sqlite3expert *pExpert = (sqlite3expert*)clientData; + sqlite3_expert_destroy(pExpert); +} + +/* +** sqlite3_expert_new DB +*/ +static int SQLITE_TCLAPI test_sqlite3_expert_new( + void * clientData, + Tcl_Interp *interp, + int objc, + Tcl_Obj *CONST objv[] +){ + static int iCmd = 0; + sqlite3 *db; + char *zCmd = 0; + char *zErr = 0; + sqlite3expert *pExpert; + int rc = TCL_OK; + + if( objc!=2 ){ + Tcl_WrongNumArgs(interp, 1, objv, "DB"); + return TCL_ERROR; + } + if( dbHandleFromObj(interp, objv[1], &db) ){ + return TCL_ERROR; + } + + zCmd = sqlite3_mprintf("sqlite3expert%d", ++iCmd); + if( zCmd==0 ){ + Tcl_AppendResult(interp, "out of memory", (char*)0); + return TCL_ERROR; + } + + pExpert = sqlite3_expert_new(db, &zErr); + if( pExpert==0 ){ + Tcl_AppendResult(interp, zErr, (char*)0); + rc = TCL_ERROR; + }else{ + void *p = (void*)pExpert; + Tcl_CreateObjCommand(interp, zCmd, testExpertCmd, p, testExpertDel); + Tcl_SetObjResult(interp, Tcl_NewStringObj(zCmd, -1)); + } + + sqlite3_free(zCmd); + sqlite3_free(zErr); + return rc; +} + +int TestExpert_Init(Tcl_Interp *interp){ + struct Cmd { + const char *zCmd; + Tcl_ObjCmdProc *xProc; + } aCmd[] = { + { "sqlite3_expert_new", test_sqlite3_expert_new }, + }; + int i; + + for(i=0; izCmd, p->xProc, 0, 0); + } + + return TCL_OK; +} + +#else /* defined(SQLITE_TEST) && defined(SQLITE_ENABLE_WHEREINFO_HOOK) */ +int TestExpert_Init(Tcl_Interp *interp){ + return TCL_OK; +} +#endif Index: main.mk ================================================================== --- main.mk +++ main.mk @@ -272,10 +272,12 @@ # Source code to the test files. # TESTSRC = \ + $(TOP)/ext/expert/sqlite3expert.c \ + $(TOP)/ext/expert/test_expert.c \ $(TOP)/ext/fts3/fts3_term.c \ $(TOP)/ext/fts3/fts3_test.c \ $(TOP)/ext/rbu/test_rbu.c \ $(TOP)/src/test1.c \ $(TOP)/src/test2.c \ Index: src/tclsqlite.c ================================================================== --- src/tclsqlite.c +++ src/tclsqlite.c @@ -4127,10 +4127,11 @@ extern int SqliteSuperlock_Init(Tcl_Interp*); extern int SqlitetestSyscall_Init(Tcl_Interp*); #if defined(SQLITE_ENABLE_SESSION) && defined(SQLITE_ENABLE_PREUPDATE_HOOK) extern int TestSession_Init(Tcl_Interp*); #endif + extern int TestExpert_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); @@ -4175,10 +4176,11 @@ SqliteSuperlock_Init(interp); SqlitetestSyscall_Init(interp); #if defined(SQLITE_ENABLE_SESSION) && defined(SQLITE_ENABLE_PREUPDATE_HOOK) TestSession_Init(interp); #endif + TestExpert_Init(interp); Fts5tcl_Init(interp); SqliteRbu_Init(interp); Sqlitetesttcl_Init(interp); #if defined(SQLITE_ENABLE_FTS3) || defined(SQLITE_ENABLE_FTS4) DELETED test/expert1.test Index: test/expert1.test ================================================================== --- test/expert1.test +++ test/expert1.test @@ -1,202 +0,0 @@ -# 2009 Nov 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. -# -#*********************************************************************** -# -# The focus of this file is testing the CLI shell tool. Specifically, -# the ".recommend" command. -# -# - -# Test plan: -# -# -set testdir [file dirname $argv0] -source $testdir/tester.tcl -set testprefix expert1 - -if {$tcl_platform(platform)=="windows"} { - set CMD "sqlite3_expert.exe" -} else { - set CMD ".././sqlite3_expert" -} -if {![file executable $CMD]} { - finish_test - return -} - - -proc squish {txt} { - regsub -all {[[:space:]]+} $txt { } -} - -proc do_rec_test {tn sql res} { - set res [squish [string trim $res]] - set tst [subst -nocommands { - squish [string trim [exec $::CMD -verbose 0 -sql {$sql;} test.db]] - }] - uplevel [list do_test $tn $tst $res] -} - -proc do_setup_rec_test {tn setup sql res} { - reset_db - db eval $setup - uplevel [list do_rec_test $tn $sql $res] -} - - -do_setup_rec_test 1.1 { CREATE TABLE t1(a, b, c) } { - SELECT * FROM t1 -} { - (no new indexes) - 0|0|0|SCAN TABLE t1 -} - -do_setup_rec_test 1.2 { - CREATE TABLE t1(a, b, c); -} { - SELECT * FROM t1 WHERE b>?; -} { - CREATE INDEX t1_idx_00000062 ON t1(b); - 0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_00000062 (b>?) -} - -do_setup_rec_test 1.3 { - CREATE TABLE t1(a, b, c); -} { - SELECT * FROM t1 WHERE b COLLATE nocase BETWEEN ? AND ? -} { - CREATE INDEX t1_idx_3e094c27 ON t1(b COLLATE NOCASE); - 0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_3e094c27 (b>? AND b? AND b