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