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