1 /*
2 ** This file implements an eponymous, read-only table-valued function
3 ** (a virtual table) designed to be used for testing. We are not aware
4 ** of any practical real-world use case for the virtual table.
5 **
6 ** This virtual table originated in the TH3 test suite. It is still used
7 ** there, but has now been copied into the public SQLite source tree and
8 ** reused for a variety of testing purpose. The name "vt02" comes from the
9 ** fact that there are many different testing virtual tables in TH3, of which
10 ** this one is the second.
11 **
12 ** ## SUBJECT TO CHANGE
13 **
14 ** Because this virtual table is intended for testing, its interface is not
15 ** guaranteed to be stable across releases. Future releases may contain
16 ** changes in the vt02 design and interface.
17 **
18 ** ## OVERVIEW
19 **
20 ** The vt02 table-valued function has 10000 rows with 5 data columns.
21 ** Column X contains all integer values between 0 and 9999 inclusive.
22 ** Columns A, B, C, and D contain the individual base-10 digits associated
23 ** with each X value:
24 **
25 ** X A B C D
26 ** ---- - - - -
27 ** 0 0 0 0 0
28 ** 1 0 0 0 1
29 ** 2 0 0 0 2
30 ** ...
31 ** 4998 4 9 9 8
32 ** 4999 4 9 9 9
33 ** 5000 5 0 0 0
34 ** ...
35 ** 9995 9 9 9 5
36 ** 9996 9 9 9 6
37 ** 9997 9 9 9 7
38 **
39 ** The xBestIndex method recognizes a variety of equality constraints
40 ** and attempts to optimize its output accordingly.
41 **
42 ** x=...
43 ** a=...
44 ** a=... AND b=...
45 ** a=... AND b=... AND c=...
46 ** a=... AND b=... AND c=... AND d=...
47 **
48 ** Various ORDER BY constraints are also recognized and consumed. The
49 ** OFFSET constraint is recognized and consumed.
50 **
51 ** ## TABLE-VALUED FUNCTION
52 **
53 ** The vt02 virtual table is eponymous and has two hidden columns, meaning
54 ** that it can functions a table-valued function. The two hidden columns
55 ** are "flags" and "logtab", in that order. The "flags" column can be set
56 ** to an integer where various bits enable or disable behaviors of the
57 ** virtual table. The "logtab" can set to the name of an ordinary SQLite
58 ** table into which is written information about each call to xBestIndex.
59 **
60 ** The bits of "flags" are as follows:
61 **
62 ** 0x01 Ignore the aConstraint[].usable flag. This might
63 ** result in the xBestIndex method incorrectly using
64 ** unusable entries in the aConstraint[] array, which
65 ** should result in the SQLite core detecting and
66 ** reporting that the virtual table is not behaving
67 ** to spec.
68 **
69 ** 0x02 Do not set the orderByConsumed flag, even if it
70 ** could be set.
71 **
72 ** 0x04 Do not consume the OFFSET constraint, if there is
73 ** one. Instead, let the generated byte-code visit
74 ** and ignore the first few columns of output.
75 **
76 ** 0x08 Use sqlite3_mprintf() to allocate an idxStr string.
77 ** The string is never used, but allocating it does
78 ** test the idxStr deallocation logic inside of the
79 ** SQLite core.
80 **
81 ** 0x10 Cause the xBestIndex method to generate an idxNum
82 ** that xFilter does not understand, thus causing
83 ** the OP_VFilter opcode to raise an error.
84 **
85 ** 0x20 Set the omit flag for all equality constraints on
86 ** columns X, A, B, C, and D that are used to limit
87 ** the search.
88 **
89 ** 0x40 Add all constraints against X,A,B,C,D to the
90 ** vector of results sent to xFilter. Only the first
91 ** few are used, as required by idxNum.
92 **
93 ** Because these flags take effect during xBestIndex, the RHS of the
94 ** flag= constraint must be accessible. In other words, the RHS of flag=
95 ** needs to be an integer literal, not another column of a join or a
96 ** bound parameter.
97 **
98 ** ## LOGGING OUTPUT
99 **
100 ** If the "logtab" columns is set, then each call to the xBestIndex method
101 ** inserts multiple rows into the table identified by "logtab". These
102 ** rows collectively show the content of the sqlite3_index_info object and
103 ** other context associated with the xBestIndex call.
104 **
105 ** If the table named by "logtab" does not previously exist, it is created
106 ** automatically. The schema for the logtab table is like this:
107 **
108 ** CREATE TEMP TABLE vt02_log(
109 ** bi INT, -- BestIndex call counter
110 ** vn TEXT, -- Variable Name
111 ** ix INT, -- Index or value
112 ** cn TEXT, -- Column Name
113 ** op INT, -- Opcode or "DESC" value
114 ** ux INT, -- "Usable" flag
115 ** ra BOOLEAN, -- Right-hand side Available.
116 ** rhs ANY, -- Right-Hand Side value
117 ** cs TEXT -- Collating Sequence for this constraint
118 ** );
119 **
120 ** Because logging happens during xBestIindex, the RHS value of "logtab" must
121 ** be known to xBestIndex, which means it must be a string literal, not a
122 ** column in a join, or a bound parameter.
123 **
124 ** ## VIRTUAL TABLE SCHEMA
125 **
126 ** CREATE TABLE vt02(
127 ** x INT, -- integer between 0 and 9999 inclusive
128 ** a INT, -- The 1000s digit
129 ** b INT, -- The 100s digit
130 ** c INT, -- The 10s digit
131 ** d INT, -- The 1s digit
132 ** flags INT HIDDEN, -- Option flags
133 ** logtab TEXT HIDDEN, -- Name of table into which to log xBestIndex
134 ** );
135 **
136 ** ## COMPILING AND RUNNING
137 **
138 ** This file can also be compiled separately as a loadable extension
139 ** for SQLite (as long as the -DTH3_VERSION is not defined). To compile as a
140 ** loadable extension do his:
141 **
142 ** gcc -Wall -g -shared -fPIC -I. -DSQLITE_DEBUG vt02.c -o vt02.so
143 **
144 ** Or on Windows:
145 **
146 ** cl vt02.c -link -dll -out:vt02.dll
147 **
148 ** Then load into the CLI using:
149 **
150 ** .load ./vt02 sqlite3_vt02_init
151 **
152 ** ## IDXNUM SUMMARY
153 **
154 ** The xBestIndex method communicates the query plan to xFilter using
155 ** the idxNum value, as follows:
156 **
157 ** 0 unconstrained
158 ** 1 X=argv[0]
159 ** 2 A=argv[0]
160 ** 3 A=argv[0], B=argv[1]
161 ** 4 A=argv[0], B=argv[1], C=argv[2]
162 ** 5 A=argv[0], B=argv[1], C=argv[2], D=argv[3]
163 ** 6 A=argv[0], D IN argv[2]
164 ** 7 A=argv[0], B=argv[2], D IN argv[3]
165 ** 8 A=argv[0], B=argv[2], C=argv[3], D IN argv[4]
166 ** 1x increment by 10
167 ** 2x increment by 100
168 ** 3x increment by 1000
169 ** 1xx Use offset provided by argv[N]
170 */
171 #ifndef TH3_VERSION
172 /* These bits for separate compilation as a loadable extension, only */
173 #include "sqlite3ext.h"
174 SQLITE_EXTENSION_INIT1
175 #include <stdlib.h>
176 #include <string.h>
177 #include <assert.h>
178 #endif
179
180 /* Forward declarations */
181 typedef struct vt02_vtab vt02_vtab;
182 typedef struct vt02_cur vt02_cur;
183
184 /*
185 ** The complete virtual table
186 */
187 struct vt02_vtab {
188 sqlite3_vtab parent; /* Base clase. Must be first. */
189 sqlite3 *db; /* Database connection */
190 int busy; /* Currently running xBestIndex */
191 };
192
193 #define VT02_IGNORE_USABLE 0x0001 /* Ignore usable flags */
194 #define VT02_NO_SORT_OPT 0x0002 /* Do not do any sorting optimizations */
195 #define VT02_NO_OFFSET 0x0004 /* Omit the offset optimization */
196 #define VT02_ALLOC_IDXSTR 0x0008 /* Alloate an idxStr */
197 #define VT02_BAD_IDXNUM 0x0010 /* Generate an invalid idxNum */
198
199 /*
200 ** A cursor
201 */
202 struct vt02_cur {
203 sqlite3_vtab_cursor parent; /* Base class. Must be first */
204 sqlite3_int64 i; /* Current entry */
205 sqlite3_int64 iEof; /* Indicate EOF when reaching this value */
206 int iIncr; /* Amount by which to increment */
207 unsigned int mD; /* Mask of allowed D-column values */
208 };
209
210 /* The xConnect method */
vt02Connect(sqlite3 * db,void * pAux,int argc,const char * const * argv,sqlite3_vtab ** ppVTab,char ** pzErr)211 int vt02Connect(
212 sqlite3 *db, /* The database connection */
213 void *pAux, /* Pointer to an alternative schema */
214 int argc, /* Number of arguments */
215 const char *const*argv, /* Text of the arguments */
216 sqlite3_vtab **ppVTab, /* Write the new vtab here */
217 char **pzErr /* Error message written here */
218 ){
219 vt02_vtab *pVtab;
220 int rc;
221 const char *zSchema = (const char*)pAux;
222 static const char zDefaultSchema[] =
223 "CREATE TABLE x(x INT, a INT, b INT, c INT, d INT,"
224 " flags INT HIDDEN, logtab TEXT HIDDEN);";
225 #define VT02_COL_X 0
226 #define VT02_COL_A 1
227 #define VT02_COL_B 2
228 #define VT02_COL_C 3
229 #define VT02_COL_D 4
230 #define VT02_COL_FLAGS 5
231 #define VT02_COL_LOGTAB 6
232 #define VT02_COL_NONE 7
233
234 pVtab = sqlite3_malloc( sizeof(*pVtab) );
235 if( pVtab==0 ){
236 *pzErr = sqlite3_mprintf("out of memory");
237 return SQLITE_NOMEM;
238 }
239 memset(pVtab, 0, sizeof(*pVtab));
240 pVtab->db = db;
241 rc = sqlite3_declare_vtab(db, zSchema ? zSchema : zDefaultSchema);
242 if( rc ){
243 sqlite3_free(pVtab);
244 }else{
245 *ppVTab = &pVtab->parent;
246 }
247 return rc;
248 }
249
250 /* the xDisconnect method
251 */
vt02Disconnect(sqlite3_vtab * pVTab)252 int vt02Disconnect(sqlite3_vtab *pVTab){
253 sqlite3_free(pVTab);
254 return SQLITE_OK;
255 }
256
257 /* Put an error message into the zErrMsg string of the virtual table.
258 */
vt02ErrMsg(sqlite3_vtab * pVtab,const char * zFormat,...)259 static void vt02ErrMsg(sqlite3_vtab *pVtab, const char *zFormat, ...){
260 va_list ap;
261 sqlite3_free(pVtab->zErrMsg);
262 va_start(ap, zFormat);
263 pVtab->zErrMsg = sqlite3_vmprintf(zFormat, ap);
264 va_end(ap);
265 }
266
267
268 /* Open a cursor for scanning
269 */
vt02Open(sqlite3_vtab * pVTab,sqlite3_vtab_cursor ** ppCursor)270 static int vt02Open(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){
271 vt02_cur *pCur;
272 pCur = sqlite3_malloc( sizeof(*pCur) );
273 if( pCur==0 ){
274 vt02ErrMsg(pVTab, "out of memory");
275 return SQLITE_NOMEM;
276 }
277 *ppCursor = &pCur->parent;
278 pCur->i = -1;
279 return SQLITE_OK;
280 }
281
282 /* Close a cursor
283 */
vt02Close(sqlite3_vtab_cursor * pCursor)284 static int vt02Close(sqlite3_vtab_cursor *pCursor){
285 vt02_cur *pCur = (vt02_cur*)pCursor;
286 sqlite3_free(pCur);
287 return SQLITE_OK;
288 }
289
290 /* Return TRUE if we are at the end of the BVS and there are
291 ** no more entries.
292 */
vt02Eof(sqlite3_vtab_cursor * pCursor)293 static int vt02Eof(sqlite3_vtab_cursor *pCursor){
294 vt02_cur *pCur = (vt02_cur*)pCursor;
295 return pCur->i<0 || pCur->i>=pCur->iEof;
296 }
297
298 /* Advance the cursor to the next row in the table
299 */
vt02Next(sqlite3_vtab_cursor * pCursor)300 static int vt02Next(sqlite3_vtab_cursor *pCursor){
301 vt02_cur *pCur = (vt02_cur*)pCursor;
302 do{
303 pCur->i += pCur->iIncr;
304 if( pCur->i<0 ) pCur->i = pCur->iEof;
305 }while( (pCur->mD & (1<<(pCur->i%10)))==0 && pCur->i<pCur->iEof );
306 return SQLITE_OK;
307 }
308
309 /* Rewind a cursor back to the beginning of its scan.
310 **
311 ** Scanning is always increasing.
312 **
313 ** idxNum
314 ** 0 unconstrained
315 ** 1 X=argv[0]
316 ** 2 A=argv[0]
317 ** 3 A=argv[0], B=argv[1]
318 ** 4 A=argv[0], B=argv[1], C=argv[2]
319 ** 5 A=argv[0], B=argv[1], C=argv[2], D=argv[3]
320 ** 6 A=argv[0], D IN argv[2]
321 ** 7 A=argv[0], B=argv[2], D IN argv[3]
322 ** 8 A=argv[0], B=argv[2], C=argv[3], D IN argv[4]
323 ** 1x increment by 10
324 ** 2x increment by 100
325 ** 3x increment by 1000
326 ** 1xx Use offset provided by argv[N]
327 */
vt02Filter(sqlite3_vtab_cursor * pCursor,int idxNum,const char * idxStr,int argc,sqlite3_value ** argv)328 static int vt02Filter(
329 sqlite3_vtab_cursor *pCursor, /* The cursor to rewind */
330 int idxNum, /* Search strategy */
331 const char *idxStr, /* Not used */
332 int argc, /* Not used */
333 sqlite3_value **argv /* Not used */
334 ){
335 vt02_cur *pCur = (vt02_cur*)pCursor; /* The vt02 cursor */
336 int bUseOffset = 0; /* True to use OFFSET value */
337 int iArg = 0; /* argv[] values used so far */
338 int iOrigIdxNum = idxNum; /* Original value for idxNum */
339
340 pCur->iIncr = 1;
341 pCur->mD = 0x3ff;
342 if( idxNum>=100 ){
343 bUseOffset = 1;
344 idxNum -= 100;
345 }
346 if( idxNum<0 || idxNum>38 ) goto vt02_bad_idxnum;
347 while( idxNum>=10 ){
348 pCur->iIncr *= 10;
349 idxNum -= 10;
350 }
351 if( idxNum==0 ){
352 pCur->i = 0;
353 pCur->iEof = 10000;
354 }else if( idxNum==1 ){
355 pCur->i = sqlite3_value_int64(argv[0]);
356 if( pCur->i<0 ) pCur->i = -1;
357 if( pCur->i>9999 ) pCur->i = 10000;
358 pCur->iEof = pCur->i+1;
359 if( pCur->i<0 || pCur->i>9999 ) pCur->i = pCur->iEof;
360 }else if( idxNum>=2 && idxNum<=5 ){
361 int i, e, m;
362 e = idxNum - 2;
363 assert( e<=argc-1 );
364 pCur->i = 0;
365 for(m=1000, i=0; i<=e; i++, m /= 10){
366 sqlite3_int64 v = sqlite3_value_int64(argv[iArg++]);
367 if( v<0 ) v = 0;
368 if( v>9 ) v = 9;
369 pCur->i += m*v;
370 pCur->iEof = pCur->i+m;
371 }
372 }else if( idxNum>=6 && idxNum<=8 ){
373 int i, e, m, rc;
374 sqlite3_value *pIn, *pVal;
375 e = idxNum - 6;
376 assert( e<=argc-2 );
377 pCur->i = 0;
378 for(m=1000, i=0; i<=e; i++, m /= 10){
379 sqlite3_int64 v;
380 pVal = 0;
381 if( sqlite3_vtab_in_first(0, &pVal)!=SQLITE_MISUSE
382 || sqlite3_vtab_in_first(argv[iArg], &pVal)!=SQLITE_MISUSE
383 ){
384 vt02ErrMsg(pCursor->pVtab,
385 "unexpected success from sqlite3_vtab_in_first()");
386 return SQLITE_ERROR;
387 }
388 v = sqlite3_value_int64(argv[iArg++]);
389 if( v<0 ) v = 0;
390 if( v>9 ) v = 9;
391 pCur->i += m*v;
392 pCur->iEof = pCur->i+m;
393 }
394 pCur->mD = 0;
395 pIn = argv[iArg++];
396 assert( sqlite3_value_type(pIn)==SQLITE_NULL );
397 for( rc = sqlite3_vtab_in_first(pIn, &pVal);
398 rc==SQLITE_OK && pVal!=0;
399 rc = sqlite3_vtab_in_next(pIn, &pVal)
400 ){
401 int eType = sqlite3_value_numeric_type(pVal);
402 if( eType==SQLITE_FLOAT ){
403 double r = sqlite3_value_double(pVal);
404 if( r<0.0 || r>9.0 || r!=(int)r ) continue;
405 }else if( eType!=SQLITE_INTEGER ){
406 continue;
407 }
408 i = sqlite3_value_int(pVal);
409 if( i<0 || i>9 ) continue;
410 pCur->mD |= 1<<i;
411 }
412 if( rc!=SQLITE_OK && rc!=SQLITE_DONE ){
413 vt02ErrMsg(pCursor->pVtab, "Error from sqlite3_vtab_in_first/next()");
414 return rc;
415 }
416 }else{
417 goto vt02_bad_idxnum;
418 }
419 if( bUseOffset ){
420 int nSkip = sqlite3_value_int(argv[iArg]);
421 while( nSkip-- > 0 ) vt02Next(pCursor);
422 }
423 return SQLITE_OK;
424
425 vt02_bad_idxnum:
426 vt02ErrMsg(pCursor->pVtab, "invalid idxNum for vt02: %d", iOrigIdxNum);
427 return SQLITE_ERROR;
428 }
429
430 /* Return the Nth column of the current row.
431 */
vt02Column(sqlite3_vtab_cursor * pCursor,sqlite3_context * context,int N)432 static int vt02Column(
433 sqlite3_vtab_cursor *pCursor,
434 sqlite3_context *context,
435 int N
436 ){
437 vt02_cur *pCur = (vt02_cur*)pCursor;
438 int v = pCur->i;
439 if( N==VT02_COL_X ){
440 sqlite3_result_int(context, v);
441 }else if( N>=VT02_COL_A && N<=VT02_COL_D ){
442 static const int iDivisor[] = { 1, 1000, 100, 10, 1 };
443 v = (v/iDivisor[N])%10;
444 sqlite3_result_int(context, v);
445 }
446 return SQLITE_OK;
447 }
448
449 /* Return the rowid of the current row
450 */
vt02Rowid(sqlite3_vtab_cursor * pCursor,sqlite3_int64 * pRowid)451 static int vt02Rowid(sqlite3_vtab_cursor *pCursor, sqlite3_int64 *pRowid){
452 vt02_cur *pCur = (vt02_cur*)pCursor;
453 *pRowid = pCur->i+1;
454 return SQLITE_OK;
455 }
456
457 /*************************************************************************
458 ** Logging Subsystem
459 **
460 ** The sqlite3BestIndexLog() routine implements a logging system for
461 ** xBestIndex calls. This code is portable to any virtual table.
462 **
463 ** sqlite3BestIndexLog() is the main routine, sqlite3RunSql() is a
464 ** helper routine used for running various SQL statements as part of
465 ** creating the log.
466 **
467 ** These two routines should be portable to other virtual tables. Simply
468 ** extract this code and call sqlite3BestIndexLog() near the end of the
469 ** xBestIndex method in cases where logging is desired.
470 */
471 /*
472 ** Run SQL on behalf of sqlite3BestIndexLog.
473 **
474 ** Construct the SQL using the zFormat string and subsequent arguments.
475 ** Or if zFormat is NULL, take the SQL as the first argument after the
476 ** zFormat. In either case, the dynamically allocated SQL string is
477 ** freed after it has been run. If something goes wrong with the SQL,
478 ** then an error is left in pVTab->zErrMsg.
479 */
sqlite3RunSql(sqlite3 * db,sqlite3_vtab * pVTab,const char * zFormat,...)480 static void sqlite3RunSql(
481 sqlite3 *db, /* Run the SQL on this database connection */
482 sqlite3_vtab *pVTab, /* Report errors to this virtual table */
483 const char *zFormat, /* Format string for SQL, or NULL */
484 ... /* Arguments, according to the format string */
485 ){
486 char *zSql;
487
488 va_list ap;
489 va_start(ap, zFormat);
490 if( zFormat==0 ){
491 zSql = va_arg(ap, char*);
492 }else{
493 zSql = sqlite3_vmprintf(zFormat, ap);
494 }
495 va_end(ap);
496 if( zSql ){
497 char *zErrMsg = 0;
498 (void)sqlite3_exec(db, zSql, 0, 0, &zErrMsg);
499 if( zErrMsg ){
500 if( pVTab->zErrMsg==0 ){
501 pVTab->zErrMsg = sqlite3_mprintf("%s in [%s]", zErrMsg, zSql);
502 }
503 sqlite3_free(zErrMsg);
504 }
505 sqlite3_free(zSql);
506 }
507 }
508
509 /*
510 ** Record information about each xBestIndex method call in a separate
511 ** table:
512 **
513 ** CREATE TEMP TABLE [log-table-name] (
514 ** bi INT, -- BestIndex call number
515 ** vn TEXT, -- Variable Name
516 ** ix INT, -- Index or value
517 ** cn TEXT, -- Column Name
518 ** op INT, -- Opcode or argvIndex
519 ** ux INT, -- "usable" or "omit" flag
520 ** rx BOOLEAN, -- True if has a RHS value
521 ** rhs ANY, -- The RHS value
522 ** cs TEXT, -- Collating Sequence
523 ** inop BOOLEAN -- True if this is a batchable IN operator
524 ** );
525 **
526 ** If an error occurs, leave an error message in pVTab->zErrMsg.
527 */
sqlite3BestIndexLog(sqlite3_index_info * pInfo,const char * zLogTab,sqlite3 * db,const char ** azColname,sqlite3_vtab * pVTab)528 static void sqlite3BestIndexLog(
529 sqlite3_index_info *pInfo, /* The sqlite3_index_info object */
530 const char *zLogTab, /* Log into this table */
531 sqlite3 *db, /* Database connection containing zLogTab */
532 const char **azColname, /* Names of columns in the virtual table */
533 sqlite3_vtab *pVTab /* Record errors into this object */
534 ){
535 int i, rc;
536 sqlite3_str *pStr;
537 int iBI;
538
539 if( sqlite3_table_column_metadata(db,0,zLogTab,0,0,0,0,0,0) ){
540 /* The log table does not previously exist. Create it. */
541 sqlite3RunSql(db,pVTab,
542 "CREATE TABLE IF NOT EXISTS temp.\"%w\"(\n"
543 " bi INT, -- BestIndex call number\n"
544 " vn TEXT, -- Variable Name\n"
545 " ix INT, -- Index or value\n"
546 " cn TEXT, -- Column Name\n"
547 " op INT, -- Opcode or argvIndex\n"
548 " ux INT, -- usable for omit flag\n"
549 " rx BOOLEAN, -- Right-hand side value is available\n"
550 " rhs ANY, -- RHS value\n"
551 " cs TEXT, -- Collating Sequence\n"
552 " inop BOOLEAN -- IN operator capable of batch reads\n"
553 ");", zLogTab
554 );
555 iBI = 1;
556 }else{
557 /* The log table does already exist. We assume that it has the
558 ** correct schema and proceed to find the largest prior "bi" value.
559 ** If the schema is wrong, errors might result. The code is able
560 ** to deal with this. */
561 sqlite3_stmt *pStmt;
562 char *zSql;
563 zSql = sqlite3_mprintf("SELECT max(bi) FROM temp.\"%w\"",zLogTab);
564 if( zSql==0 ){
565 sqlite3_free(pVTab->zErrMsg);
566 pVTab->zErrMsg = sqlite3_mprintf("out of memory");
567 return;
568 }
569 rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
570 sqlite3_free(zSql);
571 if( rc ){
572 sqlite3_free(pVTab->zErrMsg);
573 pVTab->zErrMsg = sqlite3_mprintf("%s", sqlite3_errmsg(db));
574 iBI = 0;
575 }else if( sqlite3_step(pStmt)==SQLITE_ROW ){
576 iBI = sqlite3_column_int(pStmt, 0)+1;
577 }else{
578 iBI = 1;
579 }
580 sqlite3_finalize(pStmt);
581 }
582 sqlite3RunSql(db,pVTab,
583 "INSERT INTO temp.\"%w\"(bi,vn,ix) VALUES(%d,'nConstraint',%d)",
584 zLogTab, iBI, pInfo->nConstraint
585 );
586 for(i=0; i<pInfo->nConstraint; i++){
587 sqlite3_value *pVal;
588 char *zSql;
589 int iCol = pInfo->aConstraint[i].iColumn;
590 int op = pInfo->aConstraint[i].op;
591 const char *zCol;
592 if( op==SQLITE_INDEX_CONSTRAINT_LIMIT
593 || op==SQLITE_INDEX_CONSTRAINT_OFFSET
594 ){
595 zCol = "";
596 }else if( iCol<0 ){
597 zCol = "rowid";
598 }else{
599 zCol = azColname[iCol];
600 }
601 pStr = sqlite3_str_new(0);
602 sqlite3_str_appendf(pStr,
603 "INSERT INTO temp.\"%w\"(bi,vn,ix,cn,op,ux,rx,rhs,cs,inop)"
604 "VALUES(%d,'aConstraint',%d,%Q,%d,%d",
605 zLogTab, iBI,
606 i,
607 zCol,
608 op,
609 pInfo->aConstraint[i].usable);
610 pVal = 0;
611 rc = sqlite3_vtab_rhs_value(pInfo, i, &pVal);
612 assert( pVal!=0 || rc!=SQLITE_OK );
613 if( rc==SQLITE_OK ){
614 sqlite3_str_appendf(pStr,",1,?1");
615 }else{
616 sqlite3_str_appendf(pStr,",0,NULL");
617 }
618 sqlite3_str_appendf(pStr,",%Q,%d)",
619 sqlite3_vtab_collation(pInfo,i),
620 sqlite3_vtab_in(pInfo,i,-1));
621 zSql = sqlite3_str_finish(pStr);
622 if( zSql==0 ){
623 if( pVTab->zErrMsg==0 ) pVTab->zErrMsg = sqlite3_mprintf("out of memory");
624 }else{
625 sqlite3_stmt *pStmt = 0;
626 rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
627 if( rc ){
628 if( pVTab->zErrMsg==0 ){
629 pVTab->zErrMsg = sqlite3_mprintf("%s", sqlite3_errmsg(db));
630 }
631 }else{
632 if( pVal ) sqlite3_bind_value(pStmt, 1, pVal);
633 sqlite3_step(pStmt);
634 rc = sqlite3_reset(pStmt);
635 if( rc && pVTab->zErrMsg==0 ){
636 pVTab->zErrMsg = sqlite3_mprintf("%s", sqlite3_errmsg(db));
637 }
638 }
639 sqlite3_finalize(pStmt);
640 sqlite3_free(zSql);
641 }
642 }
643 sqlite3RunSql(db,pVTab,
644 "INSERT INTO temp.\"%w\"(bi,vn,ix) VALUES(%d,'nOrderBy',%d)",
645 zLogTab, iBI, pInfo->nOrderBy
646 );
647 for(i=0; i<pInfo->nOrderBy; i++){
648 int iCol = pInfo->aOrderBy[i].iColumn;
649 sqlite3RunSql(db,pVTab,
650 "INSERT INTO temp.\"%w\"(bi,vn,ix,cn,op)VALUES(%d,'aOrderBy',%d,%Q,%d)",
651 zLogTab, iBI,
652 i,
653 iCol>=0 ? azColname[iCol] : "rowid",
654 pInfo->aOrderBy[i].desc
655 );
656 }
657 sqlite3RunSql(db,pVTab,
658 "INSERT INTO temp.\"%w\"(bi,vn,ix) VALUES(%d,'sqlite3_vtab_distinct',%d)",
659 zLogTab, iBI, sqlite3_vtab_distinct(pInfo)
660 );
661 sqlite3RunSql(db,pVTab,
662 "INSERT INTO temp.\"%w\"(bi,vn,ix) VALUES(%d,'colUsed',%lld)",
663 zLogTab, iBI, pInfo->colUsed
664 );
665 for(i=0; i<pInfo->nConstraint; i++){
666 int iCol = pInfo->aConstraint[i].iColumn;
667 int op = pInfo->aConstraint[i].op;
668 const char *zCol;
669 if( op==SQLITE_INDEX_CONSTRAINT_LIMIT
670 || op==SQLITE_INDEX_CONSTRAINT_OFFSET
671 ){
672 zCol = "";
673 }else if( iCol<0 ){
674 zCol = "rowid";
675 }else{
676 zCol = azColname[iCol];
677 }
678 sqlite3RunSql(db,pVTab,
679 "INSERT INTO temp.\"%w\"(bi,vn,ix,cn,op,ux)"
680 "VALUES(%d,'aConstraintUsage',%d,%Q,%d,%d)",
681 zLogTab, iBI,
682 i,
683 zCol,
684 pInfo->aConstraintUsage[i].argvIndex,
685 pInfo->aConstraintUsage[i].omit
686 );
687 }
688 sqlite3RunSql(db,pVTab,
689 "INSERT INTO temp.\"%w\"(bi,vn,ix)VALUES(%d,'idxNum',%d)",
690 zLogTab, iBI, pInfo->idxNum
691 );
692 sqlite3RunSql(db,pVTab,
693 "INSERT INTO temp.\"%w\"(bi,vn,ix)VALUES(%d,'estimatedCost',%f)",
694 zLogTab, iBI, pInfo->estimatedCost
695 );
696 sqlite3RunSql(db,pVTab,
697 "INSERT INTO temp.\"%w\"(bi,vn,ix)VALUES(%d,'estimatedRows',%lld)",
698 zLogTab, iBI, pInfo->estimatedRows
699 );
700 if( pInfo->idxStr ){
701 sqlite3RunSql(db,pVTab,
702 "INSERT INTO temp.\"%w\"(bi,vn,ix)VALUES(%d,'idxStr',%Q)",
703 zLogTab, iBI, pInfo->idxStr
704 );
705 sqlite3RunSql(db,pVTab,
706 "INSERT INTO temp.\"%w\"(bi,vn,ix)VALUES(%d,'needToFreeIdxStr',%d)",
707 zLogTab, iBI, pInfo->needToFreeIdxStr
708 );
709 }
710 if( pInfo->nOrderBy ){
711 sqlite3RunSql(db,pVTab,
712 "INSERT INTO temp.\"%w\"(bi,vn,ix)VALUES(%d,'orderByConsumed',%d)",
713 zLogTab, iBI, pInfo->orderByConsumed
714 );
715 }
716 }
717 /*
718 ** End of Logging Subsystem
719 *****************************************************************************/
720
721
722 /* Find an estimated cost of running a query against vt02.
723 */
vt02BestIndex(sqlite3_vtab * pVTab,sqlite3_index_info * pInfo)724 static int vt02BestIndex(sqlite3_vtab *pVTab, sqlite3_index_info *pInfo){
725 int i; /* Loop counter */
726 int isEq[5]; /* Equality constraints on X, A, B, C, and D */
727 int isUsed[5]; /* Other non-== cosntraints X, A, B, C, and D */
728 int argvIndex = 0; /* Next available argv[] slot */
729 int iOffset = -1; /* Constraint for OFFSET */
730 void *pX = 0; /* idxStr value */
731 int flags = 0; /* RHS value for flags= */
732 const char *zLogTab = 0; /* RHS value for logtab= */
733 int iFlagTerm = -1; /* Constraint term for flags= */
734 int iLogTerm = -1; /* Constraint term for logtab= */
735 int iIn = -1; /* Index of the IN constraint */
736 vt02_vtab *pSelf; /* This virtual table */
737
738 pSelf = (vt02_vtab*)pVTab;
739 if( pSelf->busy ){
740 vt02ErrMsg(pVTab, "recursive use of vt02 prohibited");
741 return SQLITE_CONSTRAINT;
742 }
743 pSelf->busy++;
744
745
746 /* Do an initial scan for flags=N and logtab=TAB constraints with
747 ** usable RHS values */
748 for(i=0; i<pInfo->nConstraint; i++){
749 sqlite3_value *pVal;
750 if( !pInfo->aConstraint[i].usable ) continue;
751 if( pInfo->aConstraint[i].op!=SQLITE_INDEX_CONSTRAINT_EQ ) continue;
752 switch( pInfo->aConstraint[i].iColumn ){
753 case VT02_COL_FLAGS:
754 if( sqlite3_vtab_rhs_value(pInfo, i, &pVal)==SQLITE_OK
755 && sqlite3_value_type(pVal)==SQLITE_INTEGER
756 ){
757 flags = sqlite3_value_int(pVal);
758 }
759 iFlagTerm = i;
760 break;
761 case VT02_COL_LOGTAB:
762 if( sqlite3_vtab_rhs_value(pInfo, i, &pVal)==SQLITE_OK
763 && sqlite3_value_type(pVal)==SQLITE_TEXT
764 ){
765 zLogTab = (const char*)sqlite3_value_text(pVal);
766 }
767 iLogTerm = i;
768 break;
769 }
770 }
771
772 /* Do a second scan to actually analyze the index information */
773 memset(isEq, 0xff, sizeof(isEq));
774 memset(isUsed, 0xff, sizeof(isUsed));
775 for(i=0; i<pInfo->nConstraint; i++){
776 int j = pInfo->aConstraint[i].iColumn;
777 if( j>=VT02_COL_FLAGS ) continue;
778 if( pInfo->aConstraint[i].usable==0
779 && (flags & VT02_IGNORE_USABLE)==0 ) continue;
780 if( j<0 ) j = VT02_COL_X;
781 switch( pInfo->aConstraint[i].op ){
782 case SQLITE_INDEX_CONSTRAINT_FUNCTION:
783 case SQLITE_INDEX_CONSTRAINT_EQ:
784 isEq[j] = i;
785 break;
786 case SQLITE_INDEX_CONSTRAINT_LT:
787 case SQLITE_INDEX_CONSTRAINT_LE:
788 case SQLITE_INDEX_CONSTRAINT_GT:
789 case SQLITE_INDEX_CONSTRAINT_GE:
790 isUsed[j] = i;
791 break;
792 case SQLITE_INDEX_CONSTRAINT_OFFSET:
793 iOffset = i;
794 break;
795 }
796 }
797
798 /* Use the analysis to find an appropriate query plan */
799 if( isEq[0]>=0 ){
800 /* A constraint of X= takes priority */
801 pInfo->estimatedCost = 1;
802 pInfo->aConstraintUsage[isEq[0]].argvIndex = ++argvIndex;
803 if( flags & 0x20 ) pInfo->aConstraintUsage[isEq[0]].omit = 1;
804 pInfo->idxNum = 1;
805 }else if( isEq[1]<0 ){
806 /* If there is no X= nor A= then we have to do a full scan */
807 pInfo->idxNum = 0;
808 pInfo->estimatedCost = 10000;
809 }else{
810 int v = 1000;
811 pInfo->aConstraintUsage[isEq[1]].argvIndex = ++argvIndex;
812 if( flags & 0x20 ) pInfo->aConstraintUsage[isEq[1]].omit = 1;
813 for(i=2; i<=4 && isEq[i]>=0; i++){
814 if( i==4 && sqlite3_vtab_in(pInfo, isEq[4], 0) ) break;
815 pInfo->aConstraintUsage[isEq[i]].argvIndex = ++argvIndex;
816 if( flags & 0x20 ) pInfo->aConstraintUsage[isEq[i]].omit = 1;
817 v /= 10;
818 }
819 pInfo->idxNum = i;
820 if( isEq[4]>=0 && sqlite3_vtab_in(pInfo,isEq[4],1) ){
821 iIn = isEq[4];
822 pInfo->aConstraintUsage[iIn].argvIndex = ++argvIndex;
823 if( flags & 0x20 ) pInfo->aConstraintUsage[iIn].omit = 1;
824 v /= 5;
825 i++;
826 pInfo->idxNum += 4;
827 }
828 pInfo->estimatedCost = v;
829 }
830 pInfo->estimatedRows = (sqlite3_int64)pInfo->estimatedCost;
831
832 /* Attempt to consume the ORDER BY clause. Except, always leave
833 ** orderByConsumed set to 0 for vt02_no_sort_opt. In this way,
834 ** we can compare vt02 and vt02_no_sort_opt to ensure they get
835 ** the same answer.
836 */
837 if( pInfo->nOrderBy>0 && (flags & VT02_NO_SORT_OPT)==0 ){
838 if( pInfo->idxNum==1 ){
839 /* There will only be one row of output. So it is always sorted. */
840 pInfo->orderByConsumed = 1;
841 }else
842 if( pInfo->aOrderBy[0].iColumn<=0
843 && pInfo->aOrderBy[0].desc==0
844 ){
845 /* First column of order by is X ascending */
846 pInfo->orderByConsumed = 1;
847 }else
848 if( sqlite3_vtab_distinct(pInfo)>=1 ){
849 unsigned int x = 0;
850 for(i=0; i<pInfo->nOrderBy; i++){
851 int iCol = pInfo->aOrderBy[i].iColumn;
852 if( iCol<0 ) iCol = 0;
853 x |= 1<<iCol;
854 }
855 if( sqlite3_vtab_distinct(pInfo)==2 ){
856 if( x==0x02 ){
857 /* DISTINCT A */
858 pInfo->idxNum += 30;
859 pInfo->orderByConsumed = 1;
860 }else if( x==0x06 ){
861 /* DISTINCT A,B */
862 pInfo->idxNum += 20;
863 pInfo->orderByConsumed = 1;
864 }else if( x==0x0e ){
865 /* DISTINCT A,B,C */
866 pInfo->idxNum += 10;
867 pInfo->orderByConsumed = 1;
868 }else if( x & 0x01 ){
869 /* DISTINCT X */
870 pInfo->orderByConsumed = 1;
871 }else if( x==0x1e ){
872 /* DISTINCT A,B,C,D */
873 pInfo->orderByConsumed = 1;
874 }
875 }else{
876 if( x==0x02 ){
877 /* GROUP BY A */
878 pInfo->orderByConsumed = 1;
879 }else if( x==0x06 ){
880 /* GROUP BY A,B */
881 pInfo->orderByConsumed = 1;
882 }else if( x==0x0e ){
883 /* GROUP BY A,B,C */
884 pInfo->orderByConsumed = 1;
885 }else if( x & 0x01 ){
886 /* GROUP BY X */
887 pInfo->orderByConsumed = 1;
888 }else if( x==0x1e ){
889 /* GROUP BY A,B,C,D */
890 pInfo->orderByConsumed = 1;
891 }
892 }
893 }
894 }
895
896 if( flags & VT02_ALLOC_IDXSTR ){
897 pInfo->idxStr = sqlite3_mprintf("test");
898 pInfo->needToFreeIdxStr = 1;
899 }
900 if( flags & VT02_BAD_IDXNUM ){
901 pInfo->idxNum += 1000;
902 }
903
904 if( iOffset>=0 ){
905 pInfo->aConstraintUsage[iOffset].argvIndex = ++argvIndex;
906 if( (flags & VT02_NO_OFFSET)==0
907 && (pInfo->nOrderBy==0 || pInfo->orderByConsumed)
908 ){
909 pInfo->aConstraintUsage[iOffset].omit = 1;
910 pInfo->idxNum += 100;
911 }
912 }
913
914
915 /* Always omit flags= and logtab= constraints to prevent them from
916 ** interfering with the bytecode. Put them at the end of the argv[]
917 ** array to keep them out of the way.
918 */
919 if( iFlagTerm>=0 ){
920 pInfo->aConstraintUsage[iFlagTerm].omit = 1;
921 pInfo->aConstraintUsage[iFlagTerm].argvIndex = ++argvIndex;
922 }
923 if( iLogTerm>=0 ){
924 pInfo->aConstraintUsage[iLogTerm].omit = 1;
925 pInfo->aConstraintUsage[iLogTerm].argvIndex = ++argvIndex;
926 }
927
928 /* The 0x40 flag means add all usable constraints to the output set */
929 if( flags & 0x40 ){
930 for(i=0; i<pInfo->nConstraint; i++){
931 if( pInfo->aConstraint[i].usable
932 && pInfo->aConstraintUsage[i].argvIndex==0
933 ){
934 pInfo->aConstraintUsage[i].argvIndex = ++argvIndex;
935 if( flags & 0x20 ) pInfo->aConstraintUsage[i].omit = 1;
936 }
937 }
938 }
939
940
941 /* Generate the log if requested */
942 if( zLogTab ){
943 static const char *azColname[] = {
944 "x", "a", "b", "c", "d", "flags", "logtab"
945 };
946 sqlite3 *db = ((vt02_vtab*)pVTab)->db;
947 sqlite3BestIndexLog(pInfo, zLogTab, db, azColname, pVTab);
948 }
949 pSelf->busy--;
950
951 /* Try to do a memory allocation solely for the purpose of causing
952 ** an error under OOM testing loops */
953 pX = sqlite3_malloc(800);
954 if( pX==0 ) return SQLITE_NOMEM;
955 sqlite3_free(pX);
956
957 return pVTab->zErrMsg!=0 ? SQLITE_ERROR : SQLITE_OK;
958 }
959
960 /* This is the sqlite3_module definition for the the virtual table defined
961 ** by this include file.
962 */
963 const sqlite3_module vt02Module = {
964 /* iVersion */ 2,
965 /* xCreate */ 0, /* This is an eponymous table */
966 /* xConnect */ vt02Connect,
967 /* xBestIndex */ vt02BestIndex,
968 /* xDisconnect */ vt02Disconnect,
969 /* xDestroy */ vt02Disconnect,
970 /* xOpen */ vt02Open,
971 /* xClose */ vt02Close,
972 /* xFilter */ vt02Filter,
973 /* xNext */ vt02Next,
974 /* xEof */ vt02Eof,
975 /* xColumn */ vt02Column,
976 /* xRowid */ vt02Rowid,
977 /* xUpdate */ 0,
978 /* xBegin */ 0,
979 /* xSync */ 0,
980 /* xCommit */ 0,
981 /* xRollback */ 0,
982 /* xFindFunction */ 0,
983 /* xRename */ 0,
984 /* xSavepoint */ 0,
985 /* xRelease */ 0,
986 /* xRollbackTo */ 0
987 };
988
vt02CoreInit(sqlite3 * db)989 static void vt02CoreInit(sqlite3 *db){
990 static const char zPkXSchema[] =
991 "CREATE TABLE x(x INT NOT NULL PRIMARY KEY, a INT, b INT, c INT, d INT,"
992 " flags INT HIDDEN, logtab TEXT HIDDEN);";
993 static const char zPkABCDSchema[] =
994 "CREATE TABLE x(x INT, a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, "
995 "d INT NOT NULL, flags INT HIDDEN, logtab TEXT HIDDEN, "
996 "PRIMARY KEY(a,b,c,d));";
997 sqlite3_create_module(db, "vt02", &vt02Module, 0);
998 sqlite3_create_module(db, "vt02pkx", &vt02Module, (void*)zPkXSchema);
999 sqlite3_create_module(db, "vt02pkabcd", &vt02Module, (void*)zPkABCDSchema);
1000 }
1001
1002 #ifdef TH3_VERSION
vt02_init(th3state * p,int iDb,char * zArg)1003 static void vt02_init(th3state *p, int iDb, char *zArg){
1004 vt02CoreInit(th3dbPointer(p, iDb));
1005 }
1006 #else
1007 #ifdef _WIN32
1008 __declspec(dllexport)
1009 #endif
sqlite3_vt02_init(sqlite3 * db,char ** pzErrMsg,const sqlite3_api_routines * pApi)1010 int sqlite3_vt02_init(
1011 sqlite3 *db,
1012 char **pzErrMsg,
1013 const sqlite3_api_routines *pApi
1014 ){
1015 SQLITE_EXTENSION_INIT2(pApi);
1016 vt02CoreInit(db);
1017 return SQLITE_OK;
1018 }
1019 #endif /* TH3_VERSION */
1020