Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add an extra test for the change on this branch. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | vtab-IN-opt |
Files: | files | file ages | folders |
SHA1: |
d2d28251566d2a0ec1a07fe5b8ed0471 |
User & Date: | dan 2016-03-02 16:13:53.809 |
Context
2016-03-02
| ||
17:57 | Add new test script bestindex1.test to the utf16 permutation. (Closed-Leaf check-in: 5893e97244 user: dan tags: vtab-IN-opt) | |
16:13 | Add an extra test for the change on this branch. (check-in: d2d2825156 user: dan tags: vtab-IN-opt) | |
16:01 | Enhance test_bestindex.c so that it can be used to test plans generated by xBestIndex. (check-in: 3c15a9bf45 user: dan tags: trunk) | |
03:28 | Allow the left-hand side of IN operators on virtual tables to have the aConstraintUsage[].omit flag clear. (check-in: 1622623cbb user: drh tags: vtab-IN-opt) | |
Changes
Changes to src/test_bestindex.c.
︙ | ︙ | |||
24 25 26 27 28 29 30 | ** 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 | | | > | 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | ** 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), when the xBestIndex() method is invoked and when the xFilter() ** method is called. 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 |
︙ | ︙ | |||
63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 | ** populate the output fields of the sqlite3_index_info structure. Possible ** keys and the usage of the accompanying values are: ** ** "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) ** ** Refer to code below for further details. */ #include "sqliteInt.h" #include "tcl.h" #ifndef SQLITE_OMIT_VIRTUALTABLE typedef struct tcl_vtab tcl_vtab; typedef struct tcl_cursor tcl_cursor; /* ** A fs virtual-table object */ struct tcl_vtab { sqlite3_vtab base; Tcl_Interp *interp; Tcl_Obj *pCmd; }; /* A tcl cursor object */ struct tcl_cursor { sqlite3_vtab_cursor base; }; /* ** This function is the implementation of both the xConnect and xCreate ** methods of the fs virtual table. ** ** The argv[] array contains the following: | > > > > > > > > > > > > > > > > > > > | 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 | ** populate the output fields of the sqlite3_index_info structure. Possible ** keys and the usage of the accompanying values are: ** ** "orderby" (value of orderByConsumed flag) ** "cost" (value of estimatedCost field) ** "rows" (value of estimatedRows field) ** "use" (index of used constraint in aConstraint[]) ** "omit" (like "use", but also sets omit flag) ** "idxnum" (value of idxNum field) ** "idxstr" (value of idxStr field) ** ** Refer to code below for further details. ** ** When SQLite calls the xFilter() method, this module invokes the following ** Tcl script: ** ** tcl_command xFilter IDXNUM IDXSTR ARGLIST ** ** IDXNUM and IDXSTR are the values of the idxNum and idxStr parameters ** passed to xFilter. ARGLIST is a Tcl list containing each of the arguments ** passed to xFilter in text form. ** ** As with xBestIndex(), the return value of the script is interpreted as a ** list of key-value pairs. There is currently only one key defined - "sql". ** The value must be the full text of an SQL statement that returns the data ** for the current scan. The leftmost column returned by the SELECT is assumed ** to contain the rowid. Other columns must follow, in order from left to ** right. */ #include "sqliteInt.h" #include "tcl.h" #ifndef SQLITE_OMIT_VIRTUALTABLE typedef struct tcl_vtab tcl_vtab; typedef struct tcl_cursor tcl_cursor; /* ** A fs virtual-table object */ struct tcl_vtab { sqlite3_vtab base; Tcl_Interp *interp; Tcl_Obj *pCmd; sqlite3 *db; }; /* A tcl cursor object */ struct tcl_cursor { sqlite3_vtab_cursor base; sqlite3_stmt *pStmt; /* Read data from here */ }; /* ** This function is the implementation of both the xConnect and xCreate ** methods of the fs virtual table. ** ** The argv[] array contains the following: |
︙ | ︙ | |||
128 129 130 131 132 133 134 135 136 137 138 139 140 141 | 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); | > | 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 | 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; pTab->db = db; 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); |
︙ | ︙ | |||
176 177 178 179 180 181 182 | } /* ** Close a tcl cursor. */ static int tclClose(sqlite3_vtab_cursor *cur){ tcl_cursor *pCur = (tcl_cursor *)cur; | > > | > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | > > > > > > | > > | > | | 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 | } /* ** Close a tcl cursor. */ static int tclClose(sqlite3_vtab_cursor *cur){ tcl_cursor *pCur = (tcl_cursor *)cur; if( pCur ){ sqlite3_finalize(pCur->pStmt); sqlite3_free(pCur); } return SQLITE_OK; } static int tclNext(sqlite3_vtab_cursor *pVtabCursor){ tcl_cursor *pCsr = (tcl_cursor*)pVtabCursor; tcl_vtab *pTab = (tcl_vtab*)(pVtabCursor->pVtab); if( pCsr->pStmt ){ tcl_vtab *pTab = (tcl_vtab*)(pVtabCursor->pVtab); int rc = sqlite3_step(pCsr->pStmt); if( rc!=SQLITE_ROW ){ const char *zErr; rc = sqlite3_finalize(pCsr->pStmt); pCsr->pStmt = 0; if( rc!=SQLITE_OK ){ zErr = sqlite3_errmsg(pTab->db); pTab->base.zErrMsg = sqlite3_mprintf("%s", zErr); } } } return SQLITE_OK; } static int tclFilter( sqlite3_vtab_cursor *pVtabCursor, int idxNum, const char *idxStr, int argc, sqlite3_value **argv ){ tcl_cursor *pCsr = (tcl_cursor*)pVtabCursor; tcl_vtab *pTab = (tcl_vtab*)(pVtabCursor->pVtab); Tcl_Interp *interp = pTab->interp; Tcl_Obj *pScript; Tcl_Obj *pArg; int ii; int rc; pScript = Tcl_DuplicateObj(pTab->pCmd); Tcl_IncrRefCount(pScript); Tcl_ListObjAppendElement(interp, pScript, Tcl_NewStringObj("xFilter", -1)); Tcl_ListObjAppendElement(interp, pScript, Tcl_NewIntObj(idxNum)); if( idxStr ){ Tcl_ListObjAppendElement(interp, pScript, Tcl_NewStringObj(idxStr, -1)); }else{ Tcl_ListObjAppendElement(interp, pScript, Tcl_NewStringObj("", -1)); } pArg = Tcl_NewObj(); Tcl_IncrRefCount(pArg); for(ii=0; ii<argc; ii++){ const char *zVal = (const char*)sqlite3_value_text(argv[ii]); Tcl_Obj *pVal; if( zVal==0 ){ pVal = Tcl_NewObj(); }else{ pVal = Tcl_NewStringObj(zVal, -1); } Tcl_ListObjAppendElement(interp, pArg, pVal); } Tcl_ListObjAppendElement(interp, pScript, pArg); Tcl_DecrRefCount(pArg); rc = Tcl_EvalObjEx(interp, pScript, TCL_EVAL_GLOBAL); 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: ** ** "sql" (SQL statement to return data) */ 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 && ii<nElem; ii+=2){ const char *zCmd = Tcl_GetString(apElem[ii]); Tcl_Obj *p = apElem[ii+1]; if( sqlite3_stricmp("sql", zCmd)==0 ){ const char *zSql = Tcl_GetString(p); rc = sqlite3_prepare_v2(pTab->db, zSql, -1, &pCsr->pStmt, 0); if( rc!=SQLITE_OK ){ const char *zErr = sqlite3_errmsg(pTab->db); pTab->base.zErrMsg = sqlite3_mprintf("unexpected: %s", zErr); } }else{ rc = SQLITE_ERROR; pTab->base.zErrMsg = sqlite3_mprintf("unexpected: %s", zCmd); } } } } if( rc==SQLITE_OK ){ rc = tclNext(pVtabCursor); } return rc; } static int tclColumn( sqlite3_vtab_cursor *pVtabCursor, sqlite3_context *ctx, int i ){ tcl_cursor *pCsr = (tcl_cursor*)pVtabCursor; sqlite3_result_value(ctx, sqlite3_column_value(pCsr->pStmt, i+1)); return SQLITE_OK; } static int tclRowid(sqlite3_vtab_cursor *pVtabCursor, sqlite_int64 *pRowid){ tcl_cursor *pCsr = (tcl_cursor*)pVtabCursor; *pRowid = sqlite3_column_int64(pCsr->pStmt, 0); return SQLITE_OK; } static int tclEof(sqlite3_vtab_cursor *pVtabCursor){ tcl_cursor *pCsr = (tcl_cursor*)pVtabCursor; return (pCsr->pStmt==0); } 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; |
︙ | ︙ |
Changes to test/bestindex1.test.
︙ | ︙ | |||
52 53 54 55 56 57 58 59 60 | do_eqp_test 1.2 { SELECT * FROM x1 WHERE a IN ('abc', 'def'); } { 0 0 0 {SCAN TABLE x1 VIRTUAL TABLE INDEX 555:eq!} 0 0 0 {EXECUTE LIST SUBQUERY 1} } finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 | do_eqp_test 1.2 { SELECT * FROM x1 WHERE a IN ('abc', 'def'); } { 0 0 0 {SCAN TABLE x1 VIRTUAL TABLE INDEX 555:eq!} 0 0 0 {EXECUTE LIST SUBQUERY 1} } #------------------------------------------------------------------------- # reset_db register_tcl_module db # Parameter $mode may be one of: # # "omit" - Implement filtering. Set the omit flag. # "use" - Implement filtering. Use the constraint, but do not set omit. # "use2" - Do not implement filtering. Use the constraint anyway. # # proc t1_vtab {mode method args} { switch -- $method { xConnect { return "CREATE TABLE t1(a, b)" } xBestIndex { set SQL_FILTER {SELECT * FROM t1x WHERE a='%1%'} set SQL_SCAN {SELECT * FROM t1x} set clist [lindex $args 0] set idx 0 for {set idx 0} {$idx < [llength $clist]} {incr idx} { array unset C array set C [lindex $clist $idx] if {$C(column)==0 && $C(op)=="eq" && $C(usable)} { switch -- $mode { "omit" { return [list omit $idx rows 10 cost 10 idxstr $SQL_FILTER] } "use" { return [list use $idx rows 10 cost 10 idxstr $SQL_FILTER] } "use2" { return [list use $idx rows 10 cost 10 idxstr $SQL_SCAN] } default { error "Bad mode - $mode" } } } } return [list idxstr {SELECT * FROM t1x}] } xFilter { set map [list %1% [lindex $args 2 0]] set sql [string map $map [lindex $args 1]] return [list sql $sql] } } return {} } do_execsql_test 2.1 { CREATE TABLE t1x(i INTEGER PRIMARY KEY, a, b); INSERT INTO t1x VALUES(1, 'one', 1); INSERT INTO t1x VALUES(2, 'two', 2); INSERT INTO t1x VALUES(3, 'three', 3); INSERT INTO t1x VALUES(4, 'four', 4); } foreach {tn mode} { 1 use 2 omit 3 use2 } { do_execsql_test 2.2.$mode.1 " DROP TABLE IF EXISTS t1; CREATE VIRTUAL TABLE t1 USING tcl(t1_vtab $mode); " do_execsql_test 2.2.$mode.2 {SELECT * FROM t1} {one 1 two 2 three 3 four 4} do_execsql_test 2.2.$mode.3 {SELECT rowid FROM t1} {1 2 3 4} do_execsql_test 2.2.$mode.4 {SELECT rowid FROM t1 WHERE a='two'} {2} do_execsql_test 2.2.$mode.5 { SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid } {1 4} set plan(use) { 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%'} 0 0 0 {EXECUTE LIST SUBQUERY 1} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } set plan(omit) { 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%'} 0 0 0 {EXECUTE LIST SUBQUERY 1} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } set plan(use2) { 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x} 0 0 0 {EXECUTE LIST SUBQUERY 1} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } do_eqp_test 2.2.$mode.6 { SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid } $plan($mode) } finish_test |