SQLite

Check-in [d2d2825156]
Login

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: d2d28251566d2a0ec1a07fe5b8ed047136840bfd
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
Unified Diff 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
** 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







|
|
>







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


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
}

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







>
>
|
>



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








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


|
>
>
>
>
>
>



|
>
>



|
>
|







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