/ Check-in [3c15a9bf]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Enhance test_bestindex.c so that it can be used to test plans generated by xBestIndex.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:3c15a9bf45cd7dae2fbd99123b8dd75ce278d6e4
User & Date: dan 2016-03-02 16:01:41
Context
2016-03-03
17:54
Merge 3.11.1 changes into trunk. check-in: 7abc2dd9 user: drh tags: trunk
2016-03-02
16:13
Add an extra test for the change on this branch. check-in: d2d28251 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: 3c15a9bf user: dan tags: trunk
00:58
Add the optional -DSERIES_OMIT_CONSTRAINT_VERIFY=0 option to the series.c extension that implements the generate_series() virtual table. check-in: 3d9daa92 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/test_bestindex.c.

24
25
26
27
28
29
30
31
32

33
34
35
36
37
38
39
..
63
64
65
66
67
68
69

70
71
72
73
















74
75
76
77
78
79
80
..
85
86
87
88
89
90
91

92
93
94
95
96

97
98
99
100
101
102
103
...
128
129
130
131
132
133
134

135
136
137
138
139
140
141
...
176
177
178
179
180
181
182


183

184
185
186
187















188
189
190
191
192
193
194
195












































































196
197
198
199






200
201
202
203


204
205
206
207

208
209
210
211
212
213
214
215
** 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
................................................................................
** 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
................................................................................
/* 
** 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:
................................................................................

  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);
................................................................................
}

/*
** 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;







|
|
>







 







>




>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>





>







 







>







 







>
>
|
>



|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|


|
>
>
>
>
>
>



|
>
>



|
>
|







24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
..
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
...
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
...
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
...
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
** 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
................................................................................
** 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
................................................................................
/* 
** 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:
................................................................................

  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);
................................................................................
}

/*
** 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

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}}
  set plan(use2) {0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x}}
  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}
  }

  do_eqp_test 2.2.$mode.6 { 
    SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid
  } [concat $plan($mode) {0 0 0 {USE TEMP B-TREE FOR ORDER BY}}]
}

finish_test