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       "RETURNING iif(bi=%d,'ok',RAISE(ABORT,'wrong trigger'))",
 585     /* The RETURNING clause checks to see that the returning trigger fired
 586     ** for the correct INSERT in the case of nested INSERT RETURNINGs. */
 587     zLogTab, iBI, pInfo->nConstraint, iBI
 588   );
 589   for(i=0; i<pInfo->nConstraint; i++){
 590     sqlite3_value *pVal;
 591     char *zSql;
 592     int iCol = pInfo->aConstraint[i].iColumn;
 593     int op = pInfo->aConstraint[i].op;
 594     const char *zCol;
 595     if( op==SQLITE_INDEX_CONSTRAINT_LIMIT
 596      || op==SQLITE_INDEX_CONSTRAINT_OFFSET
 597     ){
 598       zCol = "";
 599     }else if( iCol<0 ){
 600       zCol = "rowid";
 601     }else{
 602       zCol = azColname[iCol];
 603     }
 604     pStr = sqlite3_str_new(0);
 605     sqlite3_str_appendf(pStr,
 606        "INSERT INTO temp.\"%w\"(bi,vn,ix,cn,op,ux,rx,rhs,cs,inop)"
 607        "VALUES(%d,'aConstraint',%d,%Q,%d,%d",
 608        zLogTab, iBI,
 609        i,
 610        zCol,
 611        op,
 612        pInfo->aConstraint[i].usable);
 613     pVal = 0;
 614     rc = sqlite3_vtab_rhs_value(pInfo, i, &pVal);
 615     assert( pVal!=0 || rc!=SQLITE_OK );
 616     if( rc==SQLITE_OK ){
 617       sqlite3_str_appendf(pStr,",1,?1");
 618     }else{
 619       sqlite3_str_appendf(pStr,",0,NULL");
 620     }
 621     sqlite3_str_appendf(pStr,",%Q,%d)",
 622          sqlite3_vtab_collation(pInfo,i),
 623          sqlite3_vtab_in(pInfo,i,-1));
 624     zSql = sqlite3_str_finish(pStr);
 625     if( zSql==0 ){
 626       if( pVTab->zErrMsg==0 ) pVTab->zErrMsg = sqlite3_mprintf("out of memory");
 627     }else{
 628       sqlite3_stmt *pStmt = 0;
 629       rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
 630       if( rc ){
 631         if( pVTab->zErrMsg==0 ){
 632           pVTab->zErrMsg = sqlite3_mprintf("%s", sqlite3_errmsg(db));
 633         }
 634       }else{
 635         if( pVal ) sqlite3_bind_value(pStmt, 1, pVal);
 636         sqlite3_step(pStmt);
 637         rc = sqlite3_reset(pStmt);
 638         if( rc && pVTab->zErrMsg==0 ){
 639           pVTab->zErrMsg = sqlite3_mprintf("%s", sqlite3_errmsg(db));
 640         }
 641       }
 642       sqlite3_finalize(pStmt);
 643       sqlite3_free(zSql);
 644     }
 645   }
 646   sqlite3RunSql(db,pVTab,
 647     "INSERT INTO temp.\"%w\"(bi,vn,ix) VALUES(%d,'nOrderBy',%d)",
 648     zLogTab, iBI, pInfo->nOrderBy
 649   );
 650   for(i=0; i<pInfo->nOrderBy; i++){
 651     int iCol = pInfo->aOrderBy[i].iColumn;
 652     sqlite3RunSql(db,pVTab,
 653       "INSERT INTO temp.\"%w\"(bi,vn,ix,cn,op)VALUES(%d,'aOrderBy',%d,%Q,%d)",
 654       zLogTab, iBI,
 655       i,
 656       iCol>=0 ? azColname[iCol] : "rowid",
 657       pInfo->aOrderBy[i].desc
 658     );
 659   }
 660   sqlite3RunSql(db,pVTab,
 661     "INSERT INTO temp.\"%w\"(bi,vn,ix) VALUES(%d,'sqlite3_vtab_distinct',%d)",
 662     zLogTab, iBI, sqlite3_vtab_distinct(pInfo)
 663   );
 664   sqlite3RunSql(db,pVTab,
 665     "INSERT INTO temp.\"%w\"(bi,vn,ix) VALUES(%d,'colUsed',%lld)",
 666     zLogTab, iBI, pInfo->colUsed
 667   );
 668   for(i=0; i<pInfo->nConstraint; i++){
 669     int iCol = pInfo->aConstraint[i].iColumn;
 670     int op = pInfo->aConstraint[i].op;
 671     const char *zCol;
 672     if( op==SQLITE_INDEX_CONSTRAINT_LIMIT
 673      || op==SQLITE_INDEX_CONSTRAINT_OFFSET
 674     ){
 675       zCol = "";
 676     }else if( iCol<0 ){
 677       zCol = "rowid";
 678     }else{
 679       zCol = azColname[iCol];
 680     }
 681     sqlite3RunSql(db,pVTab,
 682        "INSERT INTO temp.\"%w\"(bi,vn,ix,cn,op,ux)"
 683        "VALUES(%d,'aConstraintUsage',%d,%Q,%d,%d)",
 684        zLogTab, iBI,
 685        i,
 686        zCol,
 687        pInfo->aConstraintUsage[i].argvIndex,
 688        pInfo->aConstraintUsage[i].omit
 689     );
 690   }
 691   sqlite3RunSql(db,pVTab,
 692     "INSERT INTO temp.\"%w\"(bi,vn,ix)VALUES(%d,'idxNum',%d)",
 693     zLogTab, iBI, pInfo->idxNum
 694   );
 695   sqlite3RunSql(db,pVTab,
 696     "INSERT INTO temp.\"%w\"(bi,vn,ix)VALUES(%d,'estimatedCost',%f)",
 697     zLogTab, iBI, pInfo->estimatedCost
 698   );
 699   sqlite3RunSql(db,pVTab,
 700     "INSERT INTO temp.\"%w\"(bi,vn,ix)VALUES(%d,'estimatedRows',%lld)",
 701     zLogTab, iBI, pInfo->estimatedRows
 702   );
 703   if( pInfo->idxStr ){
 704     sqlite3RunSql(db,pVTab,
 705       "INSERT INTO temp.\"%w\"(bi,vn,ix)VALUES(%d,'idxStr',%Q)",
 706       zLogTab, iBI, pInfo->idxStr
 707     );
 708     sqlite3RunSql(db,pVTab,
 709       "INSERT INTO temp.\"%w\"(bi,vn,ix)VALUES(%d,'needToFreeIdxStr',%d)",
 710       zLogTab, iBI, pInfo->needToFreeIdxStr
 711     );
 712   }
 713   if( pInfo->nOrderBy ){
 714     sqlite3RunSql(db,pVTab,
 715       "INSERT INTO temp.\"%w\"(bi,vn,ix)VALUES(%d,'orderByConsumed',%d)",
 716       zLogTab, iBI, pInfo->orderByConsumed
 717     );
 718   }
 719 }
 720 /*
 721 ** End of Logging Subsystem
 722 *****************************************************************************/
 723
 724
 725 /* Find an estimated cost of running a query against vt02.
 726 */
 727 static int vt02BestIndex(sqlite3_vtab *pVTab, sqlite3_index_info *pInfo){
 728   int i;                      /* Loop counter */
 729   int isEq[5];                /* Equality constraints on X, A, B, C, and D */
 730   int isUsed[5];              /* Other non-== cosntraints X, A, B, C, and D */
 731   int argvIndex = 0;          /* Next available argv[] slot */
 732   int iOffset = -1;           /* Constraint for OFFSET */
 733   void *pX = 0;               /* idxStr value */
 734   int flags = 0;              /* RHS value for flags= */
 735   const char *zLogTab = 0;    /* RHS value for logtab= */
 736   int iFlagTerm = -1;         /* Constraint term for flags= */
 737   int iLogTerm = -1;          /* Constraint term for logtab= */
 738   int iIn = -1;               /* Index of the IN constraint */
 739   vt02_vtab *pSelf;           /* This virtual table */
 740
 741   pSelf = (vt02_vtab*)pVTab;
 742   if( pSelf->busy ){
 743     vt02ErrMsg(pVTab, "recursive use  of vt02 prohibited");
 744     return SQLITE_CONSTRAINT;
 745   }
 746   pSelf->busy++;
 747
 748
 749   /* Do an initial scan for flags=N and logtab=TAB constraints with
 750   ** usable RHS values */
 751   for(i=0; i<pInfo->nConstraint; i++){
 752     sqlite3_value *pVal;
 753     if( !pInfo->aConstraint[i].usable ) continue;
 754     if( pInfo->aConstraint[i].op!=SQLITE_INDEX_CONSTRAINT_EQ ) continue;
 755     switch( pInfo->aConstraint[i].iColumn ){
 756       case VT02_COL_FLAGS:
 757         if( sqlite3_vtab_rhs_value(pInfo, i, &pVal)==SQLITE_OK
 758          && sqlite3_value_type(pVal)==SQLITE_INTEGER
 759         ){
 760           flags = sqlite3_value_int(pVal);
 761         }
 762         iFlagTerm = i;
 763         break;
 764       case VT02_COL_LOGTAB:
 765         if( sqlite3_vtab_rhs_value(pInfo, i, &pVal)==SQLITE_OK
 766          && sqlite3_value_type(pVal)==SQLITE_TEXT
 767         ){
 768           zLogTab = (const char*)sqlite3_value_text(pVal);
 769         }
 770         iLogTerm = i;
 771         break;
 772     }
 773   }
 774
 775   /* Do a second scan to actually analyze the index information */
 776   memset(isEq, 0xff, sizeof(isEq));
 777   memset(isUsed, 0xff, sizeof(isUsed));
 778   for(i=0; i<pInfo->nConstraint; i++){
 779     int j = pInfo->aConstraint[i].iColumn;
 780     if( j>=VT02_COL_FLAGS ) continue;
 781     if( pInfo->aConstraint[i].usable==0
 782      && (flags & VT02_IGNORE_USABLE)==0 ) continue;
 783     if( j<0 ) j = VT02_COL_X;
 784     switch( pInfo->aConstraint[i].op ){
 785       case SQLITE_INDEX_CONSTRAINT_FUNCTION:
 786       case SQLITE_INDEX_CONSTRAINT_EQ:
 787         isEq[j] = i;
 788         break;
 789       case SQLITE_INDEX_CONSTRAINT_LT:
 790       case SQLITE_INDEX_CONSTRAINT_LE:
 791       case SQLITE_INDEX_CONSTRAINT_GT:
 792       case SQLITE_INDEX_CONSTRAINT_GE:
 793         isUsed[j] = i;
 794         break;
 795       case SQLITE_INDEX_CONSTRAINT_OFFSET:
 796         iOffset = i;
 797         break;
 798     }
 799   }
 800
 801   /* Use the analysis to find an appropriate query plan */
 802   if( isEq[0]>=0 ){
 803     /* A constraint of X= takes priority */
 804     pInfo->estimatedCost = 1;
 805     pInfo->aConstraintUsage[isEq[0]].argvIndex = ++argvIndex;
 806     if( flags & 0x20 ) pInfo->aConstraintUsage[isEq[0]].omit = 1;
 807     pInfo->idxNum = 1;
 808   }else if( isEq[1]<0 ){
 809     /* If there is no X= nor A= then we have to do a full scan */
 810     pInfo->idxNum = 0;
 811     pInfo->estimatedCost = 10000;
 812   }else{
 813     int v = 1000;
 814     pInfo->aConstraintUsage[isEq[1]].argvIndex = ++argvIndex;
 815     if( flags & 0x20 ) pInfo->aConstraintUsage[isEq[1]].omit = 1;
 816     for(i=2; i<=4 && isEq[i]>=0; i++){
 817       if( i==4 && sqlite3_vtab_in(pInfo, isEq[4], 0) ) break;
 818       pInfo->aConstraintUsage[isEq[i]].argvIndex = ++argvIndex;
 819       if( flags & 0x20 ) pInfo->aConstraintUsage[isEq[i]].omit = 1;
 820       v /= 10;
 821     }
 822     pInfo->idxNum = i;
 823     if( isEq[4]>=0 && sqlite3_vtab_in(pInfo,isEq[4],1) ){
 824       iIn = isEq[4];
 825       pInfo->aConstraintUsage[iIn].argvIndex = ++argvIndex;
 826       if( flags & 0x20 ) pInfo->aConstraintUsage[iIn].omit = 1;
 827       v /= 5;
 828       i++;
 829       pInfo->idxNum += 4;
 830     }
 831     pInfo->estimatedCost = v;
 832   }
 833   pInfo->estimatedRows = (sqlite3_int64)pInfo->estimatedCost;
 834
 835   /* Attempt to consume the ORDER BY clause.  Except, always leave
 836   ** orderByConsumed set to 0 for vt02_no_sort_opt.  In this way,
 837   ** we can compare vt02 and vt02_no_sort_opt to ensure they get
 838   ** the same answer.
 839   */
 840   if( pInfo->nOrderBy>0 && (flags & VT02_NO_SORT_OPT)==0 ){
 841     if( pInfo->idxNum==1 ){
 842       /* There will only be one row of output.  So it is always sorted. */
 843       pInfo->orderByConsumed = 1;
 844     }else
 845     if( pInfo->aOrderBy[0].iColumn<=0
 846      && pInfo->aOrderBy[0].desc==0
 847     ){
 848       /* First column of order by is X ascending */
 849       pInfo->orderByConsumed = 1;
 850     }else
 851     if( sqlite3_vtab_distinct(pInfo)>=1 ){
 852       unsigned int x = 0;
 853       for(i=0; i<pInfo->nOrderBy; i++){
 854         int iCol = pInfo->aOrderBy[i].iColumn;
 855         if( iCol<0 ) iCol = 0;
 856         x |= 1<<iCol;
 857       }
 858       if( sqlite3_vtab_distinct(pInfo)==2 ){
 859         if( x==0x02 ){
 860           /* DISTINCT A */
 861           pInfo->idxNum += 30;
 862           pInfo->orderByConsumed = 1;
 863         }else if( x==0x06 ){
 864           /* DISTINCT A,B */
 865           pInfo->idxNum += 20;
 866           pInfo->orderByConsumed = 1;
 867         }else if( x==0x0e ){
 868           /* DISTINCT A,B,C */
 869           pInfo->idxNum += 10;
 870           pInfo->orderByConsumed = 1;
 871         }else if( x & 0x01 ){
 872           /* DISTINCT X */
 873           pInfo->orderByConsumed = 1;
 874         }else if( x==0x1e ){
 875           /* DISTINCT A,B,C,D */
 876           pInfo->orderByConsumed = 1;
 877         }
 878       }else{
 879         if( x==0x02 ){
 880           /* GROUP BY A */
 881           pInfo->orderByConsumed = 1;
 882         }else if( x==0x06 ){
 883           /* GROUP BY A,B */
 884           pInfo->orderByConsumed = 1;
 885         }else if( x==0x0e ){
 886           /* GROUP BY A,B,C */
 887           pInfo->orderByConsumed = 1;
 888         }else if( x & 0x01 ){
 889           /* GROUP BY X */
 890           pInfo->orderByConsumed = 1;
 891         }else if( x==0x1e ){
 892           /* GROUP BY A,B,C,D */
 893           pInfo->orderByConsumed = 1;
 894         }
 895       }
 896     }
 897   }
 898
 899   if( flags & VT02_ALLOC_IDXSTR ){
 900     pInfo->idxStr = sqlite3_mprintf("test");
 901     pInfo->needToFreeIdxStr = 1;
 902   }
 903   if( flags & VT02_BAD_IDXNUM ){
 904     pInfo->idxNum += 1000;
 905   }
 906
 907   if( iOffset>=0 ){
 908     pInfo->aConstraintUsage[iOffset].argvIndex = ++argvIndex;
 909     if( (flags & VT02_NO_OFFSET)==0
 910      && (pInfo->nOrderBy==0 || pInfo->orderByConsumed)
 911     ){
 912       pInfo->aConstraintUsage[iOffset].omit = 1;
 913       pInfo->idxNum += 100;
 914     }
 915   }
 916
 917
 918   /* Always omit flags= and logtab= constraints to prevent them from
 919   ** interfering with the bytecode.  Put them at the end of the argv[]
 920   ** array to keep them out of the way.
 921   */
 922   if( iFlagTerm>=0 ){
 923     pInfo->aConstraintUsage[iFlagTerm].omit = 1;
 924     pInfo->aConstraintUsage[iFlagTerm].argvIndex = ++argvIndex;
 925   }
 926   if( iLogTerm>=0 ){
 927     pInfo->aConstraintUsage[iLogTerm].omit = 1;
 928     pInfo->aConstraintUsage[iLogTerm].argvIndex = ++argvIndex;
 929   }
 930
 931   /* The 0x40 flag means add all usable constraints to the output set */
 932   if( flags & 0x40 ){
 933     for(i=0; i<pInfo->nConstraint; i++){
 934       if( pInfo->aConstraint[i].usable
 935        && pInfo->aConstraintUsage[i].argvIndex==0
 936       ){
 937         pInfo->aConstraintUsage[i].argvIndex = ++argvIndex;
 938         if( flags & 0x20 )  pInfo->aConstraintUsage[i].omit = 1;
 939       }
 940     }
 941   }
 942
 943
 944   /* Generate the log if requested */
 945   if( zLogTab ){
 946     static const char *azColname[] = {
 947        "x", "a", "b", "c", "d", "flags", "logtab"
 948     };
 949     sqlite3 *db = ((vt02_vtab*)pVTab)->db;
 950     sqlite3BestIndexLog(pInfo, zLogTab, db, azColname, pVTab);
 951   }
 952   pSelf->busy--;
 953
 954   /* Try to do a memory allocation solely for the purpose of causing
 955   ** an error under OOM testing loops */
 956   pX = sqlite3_malloc(800);
 957   if( pX==0 ) return SQLITE_NOMEM;
 958   sqlite3_free(pX);
 959
 960   return pVTab->zErrMsg!=0 ? SQLITE_ERROR : SQLITE_OK;
 961 }
 962
 963 /* This is the sqlite3_module definition for the the virtual table defined
 964 ** by this include file.
 965 */
 966 const sqlite3_module vt02Module = {
 967   /* iVersion      */  2,
 968   /* xCreate       */  0,   /* This is an eponymous table */
 969   /* xConnect      */  vt02Connect,
 970   /* xBestIndex    */  vt02BestIndex,
 971   /* xDisconnect   */  vt02Disconnect,
 972   /* xDestroy      */  vt02Disconnect,
 973   /* xOpen         */  vt02Open,
 974   /* xClose        */  vt02Close,
 975   /* xFilter       */  vt02Filter,
 976   /* xNext         */  vt02Next,
 977   /* xEof          */  vt02Eof,
 978   /* xColumn       */  vt02Column,
 979   /* xRowid        */  vt02Rowid,
 980   /* xUpdate       */  0,
 981   /* xBegin        */  0,
 982   /* xSync         */  0,
 983   /* xCommit       */  0,
 984   /* xRollback     */  0,
 985   /* xFindFunction */  0,
 986   /* xRename       */  0,
 987   /* xSavepoint    */  0,
 988   /* xRelease      */  0,
 989   /* xRollbackTo   */  0,
 990   /* xShadowName   */  0,
 991   /* xIntegrity    */  0
 992 };
 993
 994 static void vt02CoreInit(sqlite3 *db){
 995   static const char zPkXSchema[] =
 996     "CREATE TABLE x(x INT NOT NULL PRIMARY KEY, a INT, b INT, c INT, d INT,"
 997     " flags INT HIDDEN, logtab TEXT HIDDEN);";
 998   static const char zPkABCDSchema[] =
 999     "CREATE TABLE x(x INT, a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, "
1000     "d INT NOT NULL, flags INT HIDDEN, logtab TEXT HIDDEN, "
1001     "PRIMARY KEY(a,b,c,d));";
1002   sqlite3_create_module(db, "vt02", &vt02Module, 0);
1003   sqlite3_create_module(db, "vt02pkx", &vt02Module, (void*)zPkXSchema);
1004   sqlite3_create_module(db, "vt02pkabcd", &vt02Module, (void*)zPkABCDSchema);
1005 }
1006
1007 #ifdef TH3_VERSION
1008 static void vt02_init(th3state *p, int iDb, char *zArg){
1009   vt02CoreInit(th3dbPointer(p, iDb));
1010 }
1011 #else
1012 #ifdef _WIN32
1013 __declspec(dllexport)
1014 #endif
1015 int sqlite3_vt02_init(
1016   sqlite3 *db,
1017   char **pzErrMsg,
1018   const sqlite3_api_routines *pApi
1019 ){
1020   SQLITE_EXTENSION_INIT2(pApi);
1021   vt02CoreInit(db);
1022   return SQLITE_OK;
1023 }
1024 #endif /* TH3_VERSION */