Debian Codegraph @

test/vt02.c

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