test/vt02.c

   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_ERROR
 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 && pCur->i<pCur->iEof ) 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 */