| 1 |
/* |
| 2 |
** 2017 April 09 |
| 3 |
** |
| 4 |
** The author disclaims copyright to this source code. In place of |
| 5 |
** a legal notice, here is a blessing: |
| 6 |
** |
| 7 |
** May you do good and not evil. |
| 8 |
** May you find forgiveness for yourself and forgive others. |
| 9 |
** May you share freely, never taking more than you give. |
| 10 |
** |
| 11 |
************************************************************************* |
| 12 |
*/ |
| 13 |
#include "sqlite3expert.h" |
| 14 |
#include <assert.h> |
| 15 |
#include <string.h> |
| 16 |
#include <stdio.h> |
| 17 |
|
| 18 |
#if !defined(SQLITE_AMALGAMATION) |
| 19 |
#if defined(SQLITE_COVERAGE_TEST) || defined(SQLITE_MUTATION_TEST) |
| 20 |
# define SQLITE_OMIT_AUXILIARY_SAFETY_CHECKS 1 |
| 21 |
#endif |
| 22 |
#if defined(SQLITE_OMIT_AUXILIARY_SAFETY_CHECKS) |
| 23 |
# define ALWAYS(X) (1) |
| 24 |
# define NEVER(X) (0) |
| 25 |
#elif !defined(NDEBUG) |
| 26 |
# define ALWAYS(X) ((X)?1:(assert(0),0)) |
| 27 |
# define NEVER(X) ((X)?(assert(0),1):0) |
| 28 |
#else |
| 29 |
# define ALWAYS(X) (X) |
| 30 |
# define NEVER(X) (X) |
| 31 |
#endif |
| 32 |
#endif /* !defined(SQLITE_AMALGAMATION) */ |
| 33 |
|
| 34 |
|
| 35 |
#ifndef SQLITE_OMIT_VIRTUALTABLE |
| 36 |
|
| 37 |
typedef sqlite3_int64 i64; |
| 38 |
typedef sqlite3_uint64 u64; |
| 39 |
|
| 40 |
typedef struct IdxColumn IdxColumn; |
| 41 |
typedef struct IdxConstraint IdxConstraint; |
| 42 |
typedef struct IdxScan IdxScan; |
| 43 |
typedef struct IdxStatement IdxStatement; |
| 44 |
typedef struct IdxTable IdxTable; |
| 45 |
typedef struct IdxWrite IdxWrite; |
| 46 |
|
| 47 |
#define STRLEN (int)strlen |
| 48 |
|
| 49 |
/* |
| 50 |
** A temp table name that we assume no user database will actually use. |
| 51 |
** If this assumption proves incorrect triggers on the table with the |
| 52 |
** conflicting name will be ignored. |
| 53 |
*/ |
| 54 |
#define UNIQUE_TABLE_NAME "t592690916721053953805701627921227776" |
| 55 |
|
| 56 |
/* |
| 57 |
** A single constraint. Equivalent to either "col = ?" or "col < ?" (or |
| 58 |
** any other type of single-ended range constraint on a column). |
| 59 |
** |
| 60 |
** pLink: |
| 61 |
** Used to temporarily link IdxConstraint objects into lists while |
| 62 |
** creating candidate indexes. |
| 63 |
*/ |
| 64 |
struct IdxConstraint { |
| 65 |
char *zColl; /* Collation sequence */ |
| 66 |
int bRange; /* True for range, false for eq */ |
| 67 |
int iCol; /* Constrained table column */ |
| 68 |
int bFlag; /* Used by idxFindCompatible() */ |
| 69 |
int bDesc; /* True if ORDER BY <expr> DESC */ |
| 70 |
IdxConstraint *pNext; /* Next constraint in pEq or pRange list */ |
| 71 |
IdxConstraint *pLink; /* See above */ |
| 72 |
}; |
| 73 |
|
| 74 |
/* |
| 75 |
** A single scan of a single table. |
| 76 |
*/ |
| 77 |
struct IdxScan { |
| 78 |
IdxTable *pTab; /* Associated table object */ |
| 79 |
int iDb; /* Database containing table zTable */ |
| 80 |
i64 covering; /* Mask of columns required for cov. index */ |
| 81 |
IdxConstraint *pOrder; /* ORDER BY columns */ |
| 82 |
IdxConstraint *pEq; /* List of == constraints */ |
| 83 |
IdxConstraint *pRange; /* List of < constraints */ |
| 84 |
IdxScan *pNextScan; /* Next IdxScan object for same analysis */ |
| 85 |
}; |
| 86 |
|
| 87 |
/* |
| 88 |
** Information regarding a single database table. Extracted from |
| 89 |
** "PRAGMA table_info" by function idxGetTableInfo(). |
| 90 |
*/ |
| 91 |
struct IdxColumn { |
| 92 |
char *zName; |
| 93 |
char *zColl; |
| 94 |
int iPk; |
| 95 |
}; |
| 96 |
struct IdxTable { |
| 97 |
int nCol; |
| 98 |
char *zName; /* Table name */ |
| 99 |
IdxColumn *aCol; |
| 100 |
IdxTable *pNext; /* Next table in linked list of all tables */ |
| 101 |
}; |
| 102 |
|
| 103 |
/* |
| 104 |
** An object of the following type is created for each unique table/write-op |
| 105 |
** seen. The objects are stored in a singly-linked list beginning at |
| 106 |
** sqlite3expert.pWrite. |
| 107 |
*/ |
| 108 |
struct IdxWrite { |
| 109 |
IdxTable *pTab; |
| 110 |
int eOp; /* SQLITE_UPDATE, DELETE or INSERT */ |
| 111 |
IdxWrite *pNext; |
| 112 |
}; |
| 113 |
|
| 114 |
/* |
| 115 |
** Each statement being analyzed is represented by an instance of this |
| 116 |
** structure. |
| 117 |
*/ |
| 118 |
struct IdxStatement { |
| 119 |
int iId; /* Statement number */ |
| 120 |
char *zSql; /* SQL statement */ |
| 121 |
char *zIdx; /* Indexes */ |
| 122 |
char *zEQP; /* Plan */ |
| 123 |
IdxStatement *pNext; |
| 124 |
}; |
| 125 |
|
| 126 |
|
| 127 |
/* |
| 128 |
** A hash table for storing strings. With space for a payload string |
| 129 |
** with each entry. Methods are: |
| 130 |
** |
| 131 |
** idxHashInit() |
| 132 |
** idxHashClear() |
| 133 |
** idxHashAdd() |
| 134 |
** idxHashSearch() |
| 135 |
*/ |
| 136 |
#define IDX_HASH_SIZE 1023 |
| 137 |
typedef struct IdxHashEntry IdxHashEntry; |
| 138 |
typedef struct IdxHash IdxHash; |
| 139 |
struct IdxHashEntry { |
| 140 |
char *zKey; /* nul-terminated key */ |
| 141 |
char *zVal; /* nul-terminated value string */ |
| 142 |
char *zVal2; /* nul-terminated value string 2 */ |
| 143 |
IdxHashEntry *pHashNext; /* Next entry in same hash bucket */ |
| 144 |
IdxHashEntry *pNext; /* Next entry in hash */ |
| 145 |
}; |
| 146 |
struct IdxHash { |
| 147 |
IdxHashEntry *pFirst; |
| 148 |
IdxHashEntry *aHash[IDX_HASH_SIZE]; |
| 149 |
}; |
| 150 |
|
| 151 |
/* |
| 152 |
** sqlite3expert object. |
| 153 |
*/ |
| 154 |
struct sqlite3expert { |
| 155 |
int iSample; /* Percentage of tables to sample for stat1 */ |
| 156 |
sqlite3 *db; /* User database */ |
| 157 |
sqlite3 *dbm; /* In-memory db for this analysis */ |
| 158 |
sqlite3 *dbv; /* Vtab schema for this analysis */ |
| 159 |
IdxTable *pTable; /* List of all IdxTable objects */ |
| 160 |
IdxScan *pScan; /* List of scan objects */ |
| 161 |
IdxWrite *pWrite; /* List of write objects */ |
| 162 |
IdxStatement *pStatement; /* List of IdxStatement objects */ |
| 163 |
int bRun; /* True once analysis has run */ |
| 164 |
char **pzErrmsg; |
| 165 |
int rc; /* Error code from whereinfo hook */ |
| 166 |
IdxHash hIdx; /* Hash containing all candidate indexes */ |
| 167 |
char *zCandidates; /* For EXPERT_REPORT_CANDIDATES */ |
| 168 |
}; |
| 169 |
|
| 170 |
|
| 171 |
/* |
| 172 |
** Allocate and return nByte bytes of zeroed memory using sqlite3_malloc(). |
| 173 |
** If the allocation fails, set *pRc to SQLITE_NOMEM and return NULL. |
| 174 |
*/ |
| 175 |
static void *idxMalloc(int *pRc, i64 nByte){ |
| 176 |
void *pRet; |
| 177 |
assert( *pRc==SQLITE_OK ); |
| 178 |
assert( nByte>0 ); |
| 179 |
pRet = sqlite3_malloc64(nByte); |
| 180 |
if( pRet ){ |
| 181 |
memset(pRet, 0, nByte); |
| 182 |
}else{ |
| 183 |
*pRc = SQLITE_NOMEM; |
| 184 |
} |
| 185 |
return pRet; |
| 186 |
} |
| 187 |
|
| 188 |
/* |
| 189 |
** Initialize an IdxHash hash table. |
| 190 |
*/ |
| 191 |
static void idxHashInit(IdxHash *pHash){ |
| 192 |
memset(pHash, 0, sizeof(IdxHash)); |
| 193 |
} |
| 194 |
|
| 195 |
/* |
| 196 |
** Reset an IdxHash hash table. |
| 197 |
*/ |
| 198 |
static void idxHashClear(IdxHash *pHash){ |
| 199 |
int i; |
| 200 |
for(i=0; i<IDX_HASH_SIZE; i++){ |
| 201 |
IdxHashEntry *pEntry; |
| 202 |
IdxHashEntry *pNext; |
| 203 |
for(pEntry=pHash->aHash[i]; pEntry; pEntry=pNext){ |
| 204 |
pNext = pEntry->pHashNext; |
| 205 |
sqlite3_free(pEntry->zVal2); |
| 206 |
sqlite3_free(pEntry); |
| 207 |
} |
| 208 |
} |
| 209 |
memset(pHash, 0, sizeof(IdxHash)); |
| 210 |
} |
| 211 |
|
| 212 |
/* |
| 213 |
** Return the index of the hash bucket that the string specified by the |
| 214 |
** arguments to this function belongs. |
| 215 |
*/ |
| 216 |
static int idxHashString(const char *z, int n){ |
| 217 |
unsigned int ret = 0; |
| 218 |
int i; |
| 219 |
for(i=0; i<n; i++){ |
| 220 |
ret += (ret<<3) + (unsigned char)(z[i]); |
| 221 |
} |
| 222 |
return (int)(ret % IDX_HASH_SIZE); |
| 223 |
} |
| 224 |
|
| 225 |
/* |
| 226 |
** If zKey is already present in the hash table, return non-zero and do |
| 227 |
** nothing. Otherwise, add an entry with key zKey and payload string zVal to |
| 228 |
** the hash table passed as the second argument. |
| 229 |
*/ |
| 230 |
static int idxHashAdd( |
| 231 |
int *pRc, |
| 232 |
IdxHash *pHash, |
| 233 |
const char *zKey, |
| 234 |
const char *zVal |
| 235 |
){ |
| 236 |
int nKey = STRLEN(zKey); |
| 237 |
int iHash = idxHashString(zKey, nKey); |
| 238 |
int nVal = (zVal ? STRLEN(zVal) : 0); |
| 239 |
IdxHashEntry *pEntry; |
| 240 |
assert( iHash>=0 ); |
| 241 |
for(pEntry=pHash->aHash[iHash]; pEntry; pEntry=pEntry->pHashNext){ |
| 242 |
if( STRLEN(pEntry->zKey)==nKey && 0==memcmp(pEntry->zKey, zKey, nKey) ){ |
| 243 |
return 1; |
| 244 |
} |
| 245 |
} |
| 246 |
pEntry = idxMalloc(pRc, sizeof(IdxHashEntry) + (i64)nKey+1 + (i64)nVal+1); |
| 247 |
if( pEntry ){ |
| 248 |
pEntry->zKey = (char*)&pEntry[1]; |
| 249 |
memcpy(pEntry->zKey, zKey, nKey); |
| 250 |
if( zVal ){ |
| 251 |
pEntry->zVal = &pEntry->zKey[nKey+1]; |
| 252 |
memcpy(pEntry->zVal, zVal, nVal); |
| 253 |
} |
| 254 |
pEntry->pHashNext = pHash->aHash[iHash]; |
| 255 |
pHash->aHash[iHash] = pEntry; |
| 256 |
|
| 257 |
pEntry->pNext = pHash->pFirst; |
| 258 |
pHash->pFirst = pEntry; |
| 259 |
} |
| 260 |
return 0; |
| 261 |
} |
| 262 |
|
| 263 |
/* |
| 264 |
** If zKey/nKey is present in the hash table, return a pointer to the |
| 265 |
** hash-entry object. |
| 266 |
*/ |
| 267 |
static IdxHashEntry *idxHashFind(IdxHash *pHash, const char *zKey, int nKey){ |
| 268 |
int iHash; |
| 269 |
IdxHashEntry *pEntry; |
| 270 |
if( nKey<0 ) nKey = STRLEN(zKey); |
| 271 |
iHash = idxHashString(zKey, nKey); |
| 272 |
assert( iHash>=0 ); |
| 273 |
for(pEntry=pHash->aHash[iHash]; pEntry; pEntry=pEntry->pHashNext){ |
| 274 |
if( STRLEN(pEntry->zKey)==nKey && 0==memcmp(pEntry->zKey, zKey, nKey) ){ |
| 275 |
return pEntry; |
| 276 |
} |
| 277 |
} |
| 278 |
return 0; |
| 279 |
} |
| 280 |
|
| 281 |
/* |
| 282 |
** If the hash table contains an entry with a key equal to the string |
| 283 |
** passed as the final two arguments to this function, return a pointer |
| 284 |
** to the payload string. Otherwise, if zKey/nKey is not present in the |
| 285 |
** hash table, return NULL. |
| 286 |
*/ |
| 287 |
static const char *idxHashSearch(IdxHash *pHash, const char *zKey, int nKey){ |
| 288 |
IdxHashEntry *pEntry = idxHashFind(pHash, zKey, nKey); |
| 289 |
if( pEntry ) return pEntry->zVal; |
| 290 |
return 0; |
| 291 |
} |
| 292 |
|
| 293 |
/* |
| 294 |
** Allocate and return a new IdxConstraint object. Set the IdxConstraint.zColl |
| 295 |
** variable to point to a copy of nul-terminated string zColl. |
| 296 |
*/ |
| 297 |
static IdxConstraint *idxNewConstraint(int *pRc, const char *zColl){ |
| 298 |
IdxConstraint *pNew; |
| 299 |
int nColl = STRLEN(zColl); |
| 300 |
|
| 301 |
assert( *pRc==SQLITE_OK ); |
| 302 |
pNew = (IdxConstraint*)idxMalloc(pRc, sizeof(IdxConstraint) * nColl + 1); |
| 303 |
if( pNew ){ |
| 304 |
pNew->zColl = (char*)&pNew[1]; |
| 305 |
memcpy(pNew->zColl, zColl, nColl+1); |
| 306 |
} |
| 307 |
return pNew; |
| 308 |
} |
| 309 |
|
| 310 |
/* |
| 311 |
** An error associated with database handle db has just occurred. Pass |
| 312 |
** the error message to callback function xOut. |
| 313 |
*/ |
| 314 |
static void idxDatabaseError( |
| 315 |
sqlite3 *db, /* Database handle */ |
| 316 |
char **pzErrmsg /* Write error here */ |
| 317 |
){ |
| 318 |
*pzErrmsg = sqlite3_mprintf("%s", sqlite3_errmsg(db)); |
| 319 |
} |
| 320 |
|
| 321 |
/* |
| 322 |
** Prepare an SQL statement. |
| 323 |
*/ |
| 324 |
static int idxPrepareStmt( |
| 325 |
sqlite3 *db, /* Database handle to compile against */ |
| 326 |
sqlite3_stmt **ppStmt, /* OUT: Compiled SQL statement */ |
| 327 |
char **pzErrmsg, /* OUT: sqlite3_malloc()ed error message */ |
| 328 |
const char *zSql /* SQL statement to compile */ |
| 329 |
){ |
| 330 |
int rc = sqlite3_prepare_v2(db, zSql, -1, ppStmt, 0); |
| 331 |
if( rc!=SQLITE_OK ){ |
| 332 |
*ppStmt = 0; |
| 333 |
idxDatabaseError(db, pzErrmsg); |
| 334 |
} |
| 335 |
return rc; |
| 336 |
} |
| 337 |
|
| 338 |
/* |
| 339 |
** Prepare an SQL statement using the results of a printf() formatting. |
| 340 |
*/ |
| 341 |
static int idxPrintfPrepareStmt( |
| 342 |
sqlite3 *db, /* Database handle to compile against */ |
| 343 |
sqlite3_stmt **ppStmt, /* OUT: Compiled SQL statement */ |
| 344 |
char **pzErrmsg, /* OUT: sqlite3_malloc()ed error message */ |
| 345 |
const char *zFmt, /* printf() format of SQL statement */ |
| 346 |
... /* Trailing printf() arguments */ |
| 347 |
){ |
| 348 |
va_list ap; |
| 349 |
int rc; |
| 350 |
char *zSql; |
| 351 |
va_start(ap, zFmt); |
| 352 |
zSql = sqlite3_vmprintf(zFmt, ap); |
| 353 |
if( zSql==0 ){ |
| 354 |
rc = SQLITE_NOMEM; |
| 355 |
}else{ |
| 356 |
rc = idxPrepareStmt(db, ppStmt, pzErrmsg, zSql); |
| 357 |
sqlite3_free(zSql); |
| 358 |
} |
| 359 |
va_end(ap); |
| 360 |
return rc; |
| 361 |
} |
| 362 |
|
| 363 |
|
| 364 |
/************************************************************************* |
| 365 |
** Beginning of virtual table implementation. |
| 366 |
*/ |
| 367 |
typedef struct ExpertVtab ExpertVtab; |
| 368 |
struct ExpertVtab { |
| 369 |
sqlite3_vtab base; |
| 370 |
IdxTable *pTab; |
| 371 |
sqlite3expert *pExpert; |
| 372 |
}; |
| 373 |
|
| 374 |
typedef struct ExpertCsr ExpertCsr; |
| 375 |
struct ExpertCsr { |
| 376 |
sqlite3_vtab_cursor base; |
| 377 |
sqlite3_stmt *pData; |
| 378 |
}; |
| 379 |
|
| 380 |
static char *expertDequote(const char *zIn){ |
| 381 |
i64 n = STRLEN(zIn); |
| 382 |
char *zRet = sqlite3_malloc64(n); |
| 383 |
|
| 384 |
assert( zIn[0]=='\'' ); |
| 385 |
assert( zIn[n-1]=='\'' ); |
| 386 |
|
| 387 |
if( zRet ){ |
| 388 |
i64 iOut = 0; |
| 389 |
i64 iIn = 0; |
| 390 |
for(iIn=1; iIn<(n-1); iIn++){ |
| 391 |
if( zIn[iIn]=='\'' ){ |
| 392 |
assert( zIn[iIn+1]=='\'' ); |
| 393 |
iIn++; |
| 394 |
} |
| 395 |
zRet[iOut++] = zIn[iIn]; |
| 396 |
} |
| 397 |
zRet[iOut] = '\0'; |
| 398 |
} |
| 399 |
|
| 400 |
return zRet; |
| 401 |
} |
| 402 |
|
| 403 |
/* |
| 404 |
** This function is the implementation of both the xConnect and xCreate |
| 405 |
** methods of the r-tree virtual table. |
| 406 |
** |
| 407 |
** argv[0] -> module name |
| 408 |
** argv[1] -> database name |
| 409 |
** argv[2] -> table name |
| 410 |
** argv[...] -> column names... |
| 411 |
*/ |
| 412 |
static int expertConnect( |
| 413 |
sqlite3 *db, |
| 414 |
void *pAux, |
| 415 |
int argc, const char *const*argv, |
| 416 |
sqlite3_vtab **ppVtab, |
| 417 |
char **pzErr |
| 418 |
){ |
| 419 |
sqlite3expert *pExpert = (sqlite3expert*)pAux; |
| 420 |
ExpertVtab *p = 0; |
| 421 |
int rc; |
| 422 |
|
| 423 |
if( argc!=4 ){ |
| 424 |
*pzErr = sqlite3_mprintf("internal error!"); |
| 425 |
rc = SQLITE_ERROR; |
| 426 |
}else{ |
| 427 |
char *zCreateTable = expertDequote(argv[3]); |
| 428 |
if( zCreateTable ){ |
| 429 |
rc = sqlite3_declare_vtab(db, zCreateTable); |
| 430 |
if( rc==SQLITE_OK ){ |
| 431 |
p = idxMalloc(&rc, sizeof(ExpertVtab)); |
| 432 |
} |
| 433 |
if( rc==SQLITE_OK ){ |
| 434 |
p->pExpert = pExpert; |
| 435 |
p->pTab = pExpert->pTable; |
| 436 |
assert( sqlite3_stricmp(p->pTab->zName, argv[2])==0 ); |
| 437 |
} |
| 438 |
sqlite3_free(zCreateTable); |
| 439 |
}else{ |
| 440 |
rc = SQLITE_NOMEM; |
| 441 |
} |
| 442 |
} |
| 443 |
|
| 444 |
*ppVtab = (sqlite3_vtab*)p; |
| 445 |
return rc; |
| 446 |
} |
| 447 |
|
| 448 |
static int expertDisconnect(sqlite3_vtab *pVtab){ |
| 449 |
ExpertVtab *p = (ExpertVtab*)pVtab; |
| 450 |
sqlite3_free(p); |
| 451 |
return SQLITE_OK; |
| 452 |
} |
| 453 |
|
| 454 |
static int expertBestIndex(sqlite3_vtab *pVtab, sqlite3_index_info *pIdxInfo){ |
| 455 |
ExpertVtab *p = (ExpertVtab*)pVtab; |
| 456 |
int rc = SQLITE_OK; |
| 457 |
int n = 0; |
| 458 |
IdxScan *pScan; |
| 459 |
const int opmask = |
| 460 |
SQLITE_INDEX_CONSTRAINT_EQ | SQLITE_INDEX_CONSTRAINT_GT | |
| 461 |
SQLITE_INDEX_CONSTRAINT_LT | SQLITE_INDEX_CONSTRAINT_GE | |
| 462 |
SQLITE_INDEX_CONSTRAINT_LE; |
| 463 |
|
| 464 |
pScan = idxMalloc(&rc, sizeof(IdxScan)); |
| 465 |
if( pScan ){ |
| 466 |
int i; |
| 467 |
|
| 468 |
/* Link the new scan object into the list */ |
| 469 |
pScan->pTab = p->pTab; |
| 470 |
pScan->pNextScan = p->pExpert->pScan; |
| 471 |
p->pExpert->pScan = pScan; |
| 472 |
|
| 473 |
/* Add the constraints to the IdxScan object */ |
| 474 |
for(i=0; i<pIdxInfo->nConstraint; i++){ |
| 475 |
struct sqlite3_index_constraint *pCons = &pIdxInfo->aConstraint[i]; |
| 476 |
if( pCons->usable |
| 477 |
&& pCons->iColumn>=0 |
| 478 |
&& p->pTab->aCol[pCons->iColumn].iPk==0 |
| 479 |
&& (pCons->op & opmask) |
| 480 |
){ |
| 481 |
IdxConstraint *pNew; |
| 482 |
const char *zColl = sqlite3_vtab_collation(pIdxInfo, i); |
| 483 |
pNew = idxNewConstraint(&rc, zColl); |
| 484 |
if( pNew ){ |
| 485 |
pNew->iCol = pCons->iColumn; |
| 486 |
if( pCons->op==SQLITE_INDEX_CONSTRAINT_EQ ){ |
| 487 |
pNew->pNext = pScan->pEq; |
| 488 |
pScan->pEq = pNew; |
| 489 |
}else{ |
| 490 |
pNew->bRange = 1; |
| 491 |
pNew->pNext = pScan->pRange; |
| 492 |
pScan->pRange = pNew; |
| 493 |
} |
| 494 |
} |
| 495 |
n++; |
| 496 |
pIdxInfo->aConstraintUsage[i].argvIndex = n; |
| 497 |
} |
| 498 |
} |
| 499 |
|
| 500 |
/* Add the ORDER BY to the IdxScan object */ |
| 501 |
for(i=pIdxInfo->nOrderBy-1; i>=0; i--){ |
| 502 |
int iCol = pIdxInfo->aOrderBy[i].iColumn; |
| 503 |
if( iCol>=0 ){ |
| 504 |
IdxConstraint *pNew = idxNewConstraint(&rc, p->pTab->aCol[iCol].zColl); |
| 505 |
if( pNew ){ |
| 506 |
pNew->iCol = iCol; |
| 507 |
pNew->bDesc = pIdxInfo->aOrderBy[i].desc; |
| 508 |
pNew->pNext = pScan->pOrder; |
| 509 |
pNew->pLink = pScan->pOrder; |
| 510 |
pScan->pOrder = pNew; |
| 511 |
n++; |
| 512 |
} |
| 513 |
} |
| 514 |
} |
| 515 |
} |
| 516 |
|
| 517 |
pIdxInfo->estimatedCost = 1000000.0 / (n+1); |
| 518 |
return rc; |
| 519 |
} |
| 520 |
|
| 521 |
static int expertUpdate( |
| 522 |
sqlite3_vtab *pVtab, |
| 523 |
int nData, |
| 524 |
sqlite3_value **azData, |
| 525 |
sqlite_int64 *pRowid |
| 526 |
){ |
| 527 |
(void)pVtab; |
| 528 |
(void)nData; |
| 529 |
(void)azData; |
| 530 |
(void)pRowid; |
| 531 |
return SQLITE_OK; |
| 532 |
} |
| 533 |
|
| 534 |
/* |
| 535 |
** Virtual table module xOpen method. |
| 536 |
*/ |
| 537 |
static int expertOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){ |
| 538 |
int rc = SQLITE_OK; |
| 539 |
ExpertCsr *pCsr; |
| 540 |
(void)pVTab; |
| 541 |
pCsr = idxMalloc(&rc, sizeof(ExpertCsr)); |
| 542 |
*ppCursor = (sqlite3_vtab_cursor*)pCsr; |
| 543 |
return rc; |
| 544 |
} |
| 545 |
|
| 546 |
/* |
| 547 |
** Virtual table module xClose method. |
| 548 |
*/ |
| 549 |
static int expertClose(sqlite3_vtab_cursor *cur){ |
| 550 |
ExpertCsr *pCsr = (ExpertCsr*)cur; |
| 551 |
sqlite3_finalize(pCsr->pData); |
| 552 |
sqlite3_free(pCsr); |
| 553 |
return SQLITE_OK; |
| 554 |
} |
| 555 |
|
| 556 |
/* |
| 557 |
** Virtual table module xEof method. |
| 558 |
** |
| 559 |
** Return non-zero if the cursor does not currently point to a valid |
| 560 |
** record (i.e if the scan has finished), or zero otherwise. |
| 561 |
*/ |
| 562 |
static int expertEof(sqlite3_vtab_cursor *cur){ |
| 563 |
ExpertCsr *pCsr = (ExpertCsr*)cur; |
| 564 |
return pCsr->pData==0; |
| 565 |
} |
| 566 |
|
| 567 |
/* |
| 568 |
** Virtual table module xNext method. |
| 569 |
*/ |
| 570 |
static int expertNext(sqlite3_vtab_cursor *cur){ |
| 571 |
ExpertCsr *pCsr = (ExpertCsr*)cur; |
| 572 |
int rc = SQLITE_OK; |
| 573 |
|
| 574 |
assert( pCsr->pData ); |
| 575 |
rc = sqlite3_step(pCsr->pData); |
| 576 |
if( rc!=SQLITE_ROW ){ |
| 577 |
rc = sqlite3_finalize(pCsr->pData); |
| 578 |
pCsr->pData = 0; |
| 579 |
}else{ |
| 580 |
rc = SQLITE_OK; |
| 581 |
} |
| 582 |
|
| 583 |
return rc; |
| 584 |
} |
| 585 |
|
| 586 |
/* |
| 587 |
** Virtual table module xRowid method. |
| 588 |
*/ |
| 589 |
static int expertRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){ |
| 590 |
(void)cur; |
| 591 |
*pRowid = 0; |
| 592 |
return SQLITE_OK; |
| 593 |
} |
| 594 |
|
| 595 |
/* |
| 596 |
** Virtual table module xColumn method. |
| 597 |
*/ |
| 598 |
static int expertColumn(sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int i){ |
| 599 |
ExpertCsr *pCsr = (ExpertCsr*)cur; |
| 600 |
sqlite3_value *pVal; |
| 601 |
pVal = sqlite3_column_value(pCsr->pData, i); |
| 602 |
if( pVal ){ |
| 603 |
sqlite3_result_value(ctx, pVal); |
| 604 |
} |
| 605 |
return SQLITE_OK; |
| 606 |
} |
| 607 |
|
| 608 |
/* |
| 609 |
** Virtual table module xFilter method. |
| 610 |
*/ |
| 611 |
static int expertFilter( |
| 612 |
sqlite3_vtab_cursor *cur, |
| 613 |
int idxNum, const char *idxStr, |
| 614 |
int argc, sqlite3_value **argv |
| 615 |
){ |
| 616 |
ExpertCsr *pCsr = (ExpertCsr*)cur; |
| 617 |
ExpertVtab *pVtab = (ExpertVtab*)(cur->pVtab); |
| 618 |
sqlite3expert *pExpert = pVtab->pExpert; |
| 619 |
int rc; |
| 620 |
|
| 621 |
(void)idxNum; |
| 622 |
(void)idxStr; |
| 623 |
(void)argc; |
| 624 |
(void)argv; |
| 625 |
rc = sqlite3_finalize(pCsr->pData); |
| 626 |
pCsr->pData = 0; |
| 627 |
if( rc==SQLITE_OK ){ |
| 628 |
rc = idxPrintfPrepareStmt(pExpert->db, &pCsr->pData, &pVtab->base.zErrMsg, |
| 629 |
"SELECT * FROM main.%Q WHERE sqlite_expert_sample()", pVtab->pTab->zName |
| 630 |
); |
| 631 |
} |
| 632 |
|
| 633 |
if( rc==SQLITE_OK ){ |
| 634 |
rc = expertNext(cur); |
| 635 |
} |
| 636 |
return rc; |
| 637 |
} |
| 638 |
|
| 639 |
static int idxRegisterVtab(sqlite3expert *p){ |
| 640 |
static sqlite3_module expertModule = { |
| 641 |
2, /* iVersion */ |
| 642 |
expertConnect, /* xCreate - create a table */ |
| 643 |
expertConnect, /* xConnect - connect to an existing table */ |
| 644 |
expertBestIndex, /* xBestIndex - Determine search strategy */ |
| 645 |
expertDisconnect, /* xDisconnect - Disconnect from a table */ |
| 646 |
expertDisconnect, /* xDestroy - Drop a table */ |
| 647 |
expertOpen, /* xOpen - open a cursor */ |
| 648 |
expertClose, /* xClose - close a cursor */ |
| 649 |
expertFilter, /* xFilter - configure scan constraints */ |
| 650 |
expertNext, /* xNext - advance a cursor */ |
| 651 |
expertEof, /* xEof */ |
| 652 |
expertColumn, /* xColumn - read data */ |
| 653 |
expertRowid, /* xRowid - read data */ |
| 654 |
expertUpdate, /* xUpdate - write data */ |
| 655 |
0, /* xBegin - begin transaction */ |
| 656 |
0, /* xSync - sync transaction */ |
| 657 |
0, /* xCommit - commit transaction */ |
| 658 |
0, /* xRollback - rollback transaction */ |
| 659 |
0, /* xFindFunction - function overloading */ |
| 660 |
0, /* xRename - rename the table */ |
| 661 |
0, /* xSavepoint */ |
| 662 |
0, /* xRelease */ |
| 663 |
0, /* xRollbackTo */ |
| 664 |
0, /* xShadowName */ |
| 665 |
0, /* xIntegrity */ |
| 666 |
}; |
| 667 |
|
| 668 |
return sqlite3_create_module(p->dbv, "expert", &expertModule, (void*)p); |
| 669 |
} |
| 670 |
/* |
| 671 |
** End of virtual table implementation. |
| 672 |
*************************************************************************/ |
| 673 |
/* |
| 674 |
** Finalize SQL statement pStmt. If (*pRc) is SQLITE_OK when this function |
| 675 |
** is called, set it to the return value of sqlite3_finalize() before |
| 676 |
** returning. Otherwise, discard the sqlite3_finalize() return value. |
| 677 |
*/ |
| 678 |
static void idxFinalize(int *pRc, sqlite3_stmt *pStmt){ |
| 679 |
int rc = sqlite3_finalize(pStmt); |
| 680 |
if( *pRc==SQLITE_OK ) *pRc = rc; |
| 681 |
} |
| 682 |
|
| 683 |
/* |
| 684 |
** Attempt to allocate an IdxTable structure corresponding to table zTab |
| 685 |
** in the main database of connection db. If successful, set (*ppOut) to |
| 686 |
** point to the new object and return SQLITE_OK. Otherwise, return an |
| 687 |
** SQLite error code and set (*ppOut) to NULL. In this case *pzErrmsg may be |
| 688 |
** set to point to an error string. |
| 689 |
** |
| 690 |
** It is the responsibility of the caller to eventually free either the |
| 691 |
** IdxTable object or error message using sqlite3_free(). |
| 692 |
*/ |
| 693 |
static int idxGetTableInfo( |
| 694 |
sqlite3 *db, /* Database connection to read details from */ |
| 695 |
const char *zTab, /* Table name */ |
| 696 |
IdxTable **ppOut, /* OUT: New object (if successful) */ |
| 697 |
char **pzErrmsg /* OUT: Error message (if not) */ |
| 698 |
){ |
| 699 |
sqlite3_stmt *p1 = 0; |
| 700 |
int nCol = 0; |
| 701 |
int nTab; |
| 702 |
i64 nByte; |
| 703 |
IdxTable *pNew = 0; |
| 704 |
int rc, rc2; |
| 705 |
char *pCsr = 0; |
| 706 |
int nPk = 0; |
| 707 |
|
| 708 |
*ppOut = 0; |
| 709 |
if( zTab==0 ) return SQLITE_ERROR; |
| 710 |
nTab = STRLEN(zTab); |
| 711 |
nByte = sizeof(IdxTable) + nTab + 1; |
| 712 |
rc = idxPrintfPrepareStmt(db, &p1, pzErrmsg, "PRAGMA table_xinfo=%Q", zTab); |
| 713 |
while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(p1) ){ |
| 714 |
const char *zCol = (const char*)sqlite3_column_text(p1, 1); |
| 715 |
const char *zColSeq = 0; |
| 716 |
if( zCol==0 ){ |
| 717 |
rc = SQLITE_ERROR; |
| 718 |
break; |
| 719 |
} |
| 720 |
nByte += 1 + STRLEN(zCol); |
| 721 |
rc = sqlite3_table_column_metadata( |
| 722 |
db, "main", zTab, zCol, 0, &zColSeq, 0, 0, 0 |
| 723 |
); |
| 724 |
if( zColSeq==0 ) zColSeq = "binary"; |
| 725 |
nByte += 1 + STRLEN(zColSeq); |
| 726 |
nCol++; |
| 727 |
nPk += (sqlite3_column_int(p1, 5)>0); |
| 728 |
} |
| 729 |
rc2 = sqlite3_reset(p1); |
| 730 |
if( rc==SQLITE_OK ) rc = rc2; |
| 731 |
|
| 732 |
nByte += sizeof(IdxColumn) * nCol; |
| 733 |
if( rc==SQLITE_OK ){ |
| 734 |
pNew = idxMalloc(&rc, nByte); |
| 735 |
} |
| 736 |
if( rc==SQLITE_OK ){ |
| 737 |
pNew->aCol = (IdxColumn*)&pNew[1]; |
| 738 |
pNew->nCol = nCol; |
| 739 |
pCsr = (char*)&pNew->aCol[nCol]; |
| 740 |
} |
| 741 |
|
| 742 |
nCol = 0; |
| 743 |
while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(p1) ){ |
| 744 |
const char *zCol = (const char*)sqlite3_column_text(p1, 1); |
| 745 |
const char *zColSeq = 0; |
| 746 |
int nCopy; |
| 747 |
if( zCol==0 ) continue; |
| 748 |
nCopy = STRLEN(zCol) + 1; |
| 749 |
pNew->aCol[nCol].zName = pCsr; |
| 750 |
pNew->aCol[nCol].iPk = (sqlite3_column_int(p1, 5)==1 && nPk==1); |
| 751 |
memcpy(pCsr, zCol, nCopy); |
| 752 |
pCsr += nCopy; |
| 753 |
|
| 754 |
rc = sqlite3_table_column_metadata( |
| 755 |
db, "main", zTab, zCol, 0, &zColSeq, 0, 0, 0 |
| 756 |
); |
| 757 |
if( rc==SQLITE_OK ){ |
| 758 |
if( zColSeq==0 ) zColSeq = "binary"; |
| 759 |
nCopy = STRLEN(zColSeq) + 1; |
| 760 |
pNew->aCol[nCol].zColl = pCsr; |
| 761 |
memcpy(pCsr, zColSeq, nCopy); |
| 762 |
pCsr += nCopy; |
| 763 |
} |
| 764 |
|
| 765 |
nCol++; |
| 766 |
} |
| 767 |
idxFinalize(&rc, p1); |
| 768 |
|
| 769 |
if( rc!=SQLITE_OK ){ |
| 770 |
sqlite3_free(pNew); |
| 771 |
pNew = 0; |
| 772 |
}else if( ALWAYS(pNew!=0) ){ |
| 773 |
pNew->zName = pCsr; |
| 774 |
if( ALWAYS(pNew->zName!=0) ) memcpy(pNew->zName, zTab, nTab+1); |
| 775 |
} |
| 776 |
|
| 777 |
*ppOut = pNew; |
| 778 |
return rc; |
| 779 |
} |
| 780 |
|
| 781 |
/* |
| 782 |
** This function is a no-op if *pRc is set to anything other than |
| 783 |
** SQLITE_OK when it is called. |
| 784 |
** |
| 785 |
** If *pRc is initially set to SQLITE_OK, then the text specified by |
| 786 |
** the printf() style arguments is appended to zIn and the result returned |
| 787 |
** in a buffer allocated by sqlite3_malloc(). sqlite3_free() is called on |
| 788 |
** zIn before returning. |
| 789 |
*/ |
| 790 |
static char *idxAppendText(int *pRc, char *zIn, const char *zFmt, ...){ |
| 791 |
va_list ap; |
| 792 |
char *zAppend = 0; |
| 793 |
char *zRet = 0; |
| 794 |
i64 nIn = zIn ? STRLEN(zIn) : 0; |
| 795 |
i64 nAppend = 0; |
| 796 |
va_start(ap, zFmt); |
| 797 |
if( *pRc==SQLITE_OK ){ |
| 798 |
zAppend = sqlite3_vmprintf(zFmt, ap); |
| 799 |
if( zAppend ){ |
| 800 |
nAppend = STRLEN(zAppend); |
| 801 |
zRet = (char*)sqlite3_malloc64(nIn + nAppend + 1); |
| 802 |
} |
| 803 |
if( zAppend && zRet ){ |
| 804 |
if( nIn ) memcpy(zRet, zIn, nIn); |
| 805 |
memcpy(&zRet[nIn], zAppend, nAppend+1); |
| 806 |
}else{ |
| 807 |
sqlite3_free(zRet); |
| 808 |
zRet = 0; |
| 809 |
*pRc = SQLITE_NOMEM; |
| 810 |
} |
| 811 |
sqlite3_free(zAppend); |
| 812 |
sqlite3_free(zIn); |
| 813 |
} |
| 814 |
va_end(ap); |
| 815 |
return zRet; |
| 816 |
} |
| 817 |
|
| 818 |
/* |
| 819 |
** Return true if zId must be quoted in order to use it as an SQL |
| 820 |
** identifier, or false otherwise. |
| 821 |
*/ |
| 822 |
static int idxIdentifierRequiresQuotes(const char *zId){ |
| 823 |
int i; |
| 824 |
int nId = STRLEN(zId); |
| 825 |
|
| 826 |
if( sqlite3_keyword_check(zId, nId) ) return 1; |
| 827 |
|
| 828 |
for(i=0; zId[i]; i++){ |
| 829 |
if( !(zId[i]=='_') |
| 830 |
&& !(zId[i]>='0' && zId[i]<='9') |
| 831 |
&& !(zId[i]>='a' && zId[i]<='z') |
| 832 |
&& !(zId[i]>='A' && zId[i]<='Z') |
| 833 |
){ |
| 834 |
return 1; |
| 835 |
} |
| 836 |
} |
| 837 |
return 0; |
| 838 |
} |
| 839 |
|
| 840 |
/* |
| 841 |
** This function appends an index column definition suitable for constraint |
| 842 |
** pCons to the string passed as zIn and returns the result. |
| 843 |
*/ |
| 844 |
static char *idxAppendColDefn( |
| 845 |
int *pRc, /* IN/OUT: Error code */ |
| 846 |
char *zIn, /* Column defn accumulated so far */ |
| 847 |
IdxTable *pTab, /* Table index will be created on */ |
| 848 |
IdxConstraint *pCons |
| 849 |
){ |
| 850 |
char *zRet = zIn; |
| 851 |
IdxColumn *p = &pTab->aCol[pCons->iCol]; |
| 852 |
if( zRet ) zRet = idxAppendText(pRc, zRet, ", "); |
| 853 |
|
| 854 |
if( idxIdentifierRequiresQuotes(p->zName) ){ |
| 855 |
zRet = idxAppendText(pRc, zRet, "%Q", p->zName); |
| 856 |
}else{ |
| 857 |
zRet = idxAppendText(pRc, zRet, "%s", p->zName); |
| 858 |
} |
| 859 |
|
| 860 |
if( sqlite3_stricmp(p->zColl, pCons->zColl) ){ |
| 861 |
if( idxIdentifierRequiresQuotes(pCons->zColl) ){ |
| 862 |
zRet = idxAppendText(pRc, zRet, " COLLATE %Q", pCons->zColl); |
| 863 |
}else{ |
| 864 |
zRet = idxAppendText(pRc, zRet, " COLLATE %s", pCons->zColl); |
| 865 |
} |
| 866 |
} |
| 867 |
|
| 868 |
if( pCons->bDesc ){ |
| 869 |
zRet = idxAppendText(pRc, zRet, " DESC"); |
| 870 |
} |
| 871 |
return zRet; |
| 872 |
} |
| 873 |
|
| 874 |
/* |
| 875 |
** Search database dbm for an index compatible with the one idxCreateFromCons() |
| 876 |
** would create from arguments pScan, pEq and pTail. If no error occurs and |
| 877 |
** such an index is found, return non-zero. Or, if no such index is found, |
| 878 |
** return zero. |
| 879 |
** |
| 880 |
** If an error occurs, set *pRc to an SQLite error code and return zero. |
| 881 |
*/ |
| 882 |
static int idxFindCompatible( |
| 883 |
int *pRc, /* OUT: Error code */ |
| 884 |
sqlite3* dbm, /* Database to search */ |
| 885 |
IdxScan *pScan, /* Scan for table to search for index on */ |
| 886 |
IdxConstraint *pEq, /* List of == constraints */ |
| 887 |
IdxConstraint *pTail /* List of range constraints */ |
| 888 |
){ |
| 889 |
const char *zTbl = pScan->pTab->zName; |
| 890 |
sqlite3_stmt *pIdxList = 0; |
| 891 |
IdxConstraint *pIter; |
| 892 |
int nEq = 0; /* Number of elements in pEq */ |
| 893 |
int rc; |
| 894 |
|
| 895 |
/* Count the elements in list pEq */ |
| 896 |
for(pIter=pEq; pIter; pIter=pIter->pLink) nEq++; |
| 897 |
|
| 898 |
rc = idxPrintfPrepareStmt(dbm, &pIdxList, 0, "PRAGMA index_list=%Q", zTbl); |
| 899 |
while( rc==SQLITE_OK && sqlite3_step(pIdxList)==SQLITE_ROW ){ |
| 900 |
int bMatch = 1; |
| 901 |
IdxConstraint *pT = pTail; |
| 902 |
sqlite3_stmt *pInfo = 0; |
| 903 |
const char *zIdx = (const char*)sqlite3_column_text(pIdxList, 1); |
| 904 |
if( zIdx==0 ) continue; |
| 905 |
|
| 906 |
/* Zero the IdxConstraint.bFlag values in the pEq list */ |
| 907 |
for(pIter=pEq; pIter; pIter=pIter->pLink) pIter->bFlag = 0; |
| 908 |
|
| 909 |
rc = idxPrintfPrepareStmt(dbm, &pInfo, 0, "PRAGMA index_xInfo=%Q", zIdx); |
| 910 |
while( rc==SQLITE_OK && sqlite3_step(pInfo)==SQLITE_ROW ){ |
| 911 |
int iIdx = sqlite3_column_int(pInfo, 0); |
| 912 |
int iCol = sqlite3_column_int(pInfo, 1); |
| 913 |
const char *zColl = (const char*)sqlite3_column_text(pInfo, 4); |
| 914 |
|
| 915 |
if( iIdx<nEq ){ |
| 916 |
for(pIter=pEq; pIter; pIter=pIter->pLink){ |
| 917 |
if( pIter->bFlag ) continue; |
| 918 |
if( pIter->iCol!=iCol ) continue; |
| 919 |
if( sqlite3_stricmp(pIter->zColl, zColl) ) continue; |
| 920 |
pIter->bFlag = 1; |
| 921 |
break; |
| 922 |
} |
| 923 |
if( pIter==0 ){ |
| 924 |
bMatch = 0; |
| 925 |
break; |
| 926 |
} |
| 927 |
}else{ |
| 928 |
if( pT ){ |
| 929 |
if( pT->iCol!=iCol || sqlite3_stricmp(pT->zColl, zColl) ){ |
| 930 |
bMatch = 0; |
| 931 |
break; |
| 932 |
} |
| 933 |
pT = pT->pLink; |
| 934 |
} |
| 935 |
} |
| 936 |
} |
| 937 |
idxFinalize(&rc, pInfo); |
| 938 |
|
| 939 |
if( rc==SQLITE_OK && bMatch ){ |
| 940 |
sqlite3_finalize(pIdxList); |
| 941 |
return 1; |
| 942 |
} |
| 943 |
} |
| 944 |
idxFinalize(&rc, pIdxList); |
| 945 |
|
| 946 |
*pRc = rc; |
| 947 |
return 0; |
| 948 |
} |
| 949 |
|
| 950 |
/* Callback for sqlite3_exec() with query with leading count(*) column. |
| 951 |
* The first argument is expected to be an int*, referent to be incremented |
| 952 |
* if that leading column is not exactly '0'. |
| 953 |
*/ |
| 954 |
static int countNonzeros(void* pCount, int nc, |
| 955 |
char* azResults[], char* azColumns[]){ |
| 956 |
(void)azColumns; /* Suppress unused parameter warning */ |
| 957 |
if( nc>0 && (azResults[0][0]!='0' || azResults[0][1]!=0) ){ |
| 958 |
*((int *)pCount) += 1; |
| 959 |
} |
| 960 |
return 0; |
| 961 |
} |
| 962 |
|
| 963 |
static int idxCreateFromCons( |
| 964 |
sqlite3expert *p, |
| 965 |
IdxScan *pScan, |
| 966 |
IdxConstraint *pEq, |
| 967 |
IdxConstraint *pTail |
| 968 |
){ |
| 969 |
sqlite3 *dbm = p->dbm; |
| 970 |
int rc = SQLITE_OK; |
| 971 |
if( (pEq || pTail) && 0==idxFindCompatible(&rc, dbm, pScan, pEq, pTail) ){ |
| 972 |
IdxTable *pTab = pScan->pTab; |
| 973 |
char *zCols = 0; |
| 974 |
char *zIdx = 0; |
| 975 |
IdxConstraint *pCons; |
| 976 |
unsigned int h = 0; |
| 977 |
const char *zFmt; |
| 978 |
|
| 979 |
for(pCons=pEq; pCons; pCons=pCons->pLink){ |
| 980 |
zCols = idxAppendColDefn(&rc, zCols, pTab, pCons); |
| 981 |
} |
| 982 |
for(pCons=pTail; pCons; pCons=pCons->pLink){ |
| 983 |
zCols = idxAppendColDefn(&rc, zCols, pTab, pCons); |
| 984 |
} |
| 985 |
|
| 986 |
if( rc==SQLITE_OK ){ |
| 987 |
/* Hash the list of columns to come up with a name for the index */ |
| 988 |
const char *zTable = pScan->pTab->zName; |
| 989 |
int quoteTable = idxIdentifierRequiresQuotes(zTable); |
| 990 |
char *zName = 0; /* Index name */ |
| 991 |
int collisions = 0; |
| 992 |
do{ |
| 993 |
int i; |
| 994 |
char *zFind; |
| 995 |
for(i=0; zCols[i]; i++){ |
| 996 |
h += ((h<<3) + zCols[i]); |
| 997 |
} |
| 998 |
sqlite3_free(zName); |
| 999 |
zName = sqlite3_mprintf("%s_idx_%08x", zTable, h); |
| 1000 |
if( zName==0 ) break; |
| 1001 |
/* Is is unique among table, view and index names? */ |
| 1002 |
zFmt = "SELECT count(*) FROM sqlite_schema WHERE name=%Q" |
| 1003 |
" AND type in ('index','table','view')"; |
| 1004 |
zFind = sqlite3_mprintf(zFmt, zName); |
| 1005 |
i = 0; |
| 1006 |
rc = sqlite3_exec(dbm, zFind, countNonzeros, &i, 0); |
| 1007 |
assert(rc==SQLITE_OK); |
| 1008 |
sqlite3_free(zFind); |
| 1009 |
if( i==0 ){ |
| 1010 |
collisions = 0; |
| 1011 |
break; |
| 1012 |
} |
| 1013 |
++collisions; |
| 1014 |
}while( collisions<50 && zName!=0 ); |
| 1015 |
if( collisions ){ |
| 1016 |
/* This return means "Gave up trying to find a unique index name." */ |
| 1017 |
rc = SQLITE_BUSY_TIMEOUT; |
| 1018 |
}else if( zName==0 ){ |
| 1019 |
rc = SQLITE_NOMEM; |
| 1020 |
}else{ |
| 1021 |
if( quoteTable ){ |
| 1022 |
zFmt = "CREATE INDEX \"%w\" ON \"%w\"(%s)"; |
| 1023 |
}else{ |
| 1024 |
zFmt = "CREATE INDEX %s ON %s(%s)"; |
| 1025 |
} |
| 1026 |
zIdx = sqlite3_mprintf(zFmt, zName, zTable, zCols); |
| 1027 |
if( !zIdx ){ |
| 1028 |
rc = SQLITE_NOMEM; |
| 1029 |
}else{ |
| 1030 |
rc = sqlite3_exec(dbm, zIdx, 0, 0, p->pzErrmsg); |
| 1031 |
if( rc!=SQLITE_OK ){ |
| 1032 |
rc = SQLITE_BUSY_TIMEOUT; |
| 1033 |
}else{ |
| 1034 |
idxHashAdd(&rc, &p->hIdx, zName, zIdx); |
| 1035 |
} |
| 1036 |
} |
| 1037 |
sqlite3_free(zName); |
| 1038 |
sqlite3_free(zIdx); |
| 1039 |
} |
| 1040 |
} |
| 1041 |
|
| 1042 |
sqlite3_free(zCols); |
| 1043 |
} |
| 1044 |
return rc; |
| 1045 |
} |
| 1046 |
|
| 1047 |
/* |
| 1048 |
** Return true if list pList (linked by IdxConstraint.pLink) contains |
| 1049 |
** a constraint compatible with *p. Otherwise return false. |
| 1050 |
*/ |
| 1051 |
static int idxFindConstraint(IdxConstraint *pList, IdxConstraint *p){ |
| 1052 |
IdxConstraint *pCmp; |
| 1053 |
for(pCmp=pList; pCmp; pCmp=pCmp->pLink){ |
| 1054 |
if( p->iCol==pCmp->iCol ) return 1; |
| 1055 |
} |
| 1056 |
return 0; |
| 1057 |
} |
| 1058 |
|
| 1059 |
static int idxCreateFromWhere( |
| 1060 |
sqlite3expert *p, |
| 1061 |
IdxScan *pScan, /* Create indexes for this scan */ |
| 1062 |
IdxConstraint *pTail /* range/ORDER BY constraints for inclusion */ |
| 1063 |
){ |
| 1064 |
IdxConstraint *p1 = 0; |
| 1065 |
IdxConstraint *pCon; |
| 1066 |
int rc; |
| 1067 |
|
| 1068 |
/* Gather up all the == constraints. */ |
| 1069 |
for(pCon=pScan->pEq; pCon; pCon=pCon->pNext){ |
| 1070 |
if( !idxFindConstraint(p1, pCon) && !idxFindConstraint(pTail, pCon) ){ |
| 1071 |
pCon->pLink = p1; |
| 1072 |
p1 = pCon; |
| 1073 |
} |
| 1074 |
} |
| 1075 |
|
| 1076 |
/* Create an index using the == constraints collected above. And the |
| 1077 |
** range constraint/ORDER BY terms passed in by the caller, if any. */ |
| 1078 |
rc = idxCreateFromCons(p, pScan, p1, pTail); |
| 1079 |
|
| 1080 |
/* If no range/ORDER BY passed by the caller, create a version of the |
| 1081 |
** index for each range constraint. */ |
| 1082 |
if( pTail==0 ){ |
| 1083 |
for(pCon=pScan->pRange; rc==SQLITE_OK && pCon; pCon=pCon->pNext){ |
| 1084 |
assert( pCon->pLink==0 ); |
| 1085 |
if( !idxFindConstraint(p1, pCon) && !idxFindConstraint(pTail, pCon) ){ |
| 1086 |
rc = idxCreateFromCons(p, pScan, p1, pCon); |
| 1087 |
} |
| 1088 |
} |
| 1089 |
} |
| 1090 |
|
| 1091 |
return rc; |
| 1092 |
} |
| 1093 |
|
| 1094 |
/* |
| 1095 |
** Create candidate indexes in database [dbm] based on the data in |
| 1096 |
** linked-list pScan. |
| 1097 |
*/ |
| 1098 |
static int idxCreateCandidates(sqlite3expert *p){ |
| 1099 |
int rc = SQLITE_OK; |
| 1100 |
IdxScan *pIter; |
| 1101 |
|
| 1102 |
for(pIter=p->pScan; pIter && rc==SQLITE_OK; pIter=pIter->pNextScan){ |
| 1103 |
rc = idxCreateFromWhere(p, pIter, 0); |
| 1104 |
if( rc==SQLITE_OK && pIter->pOrder ){ |
| 1105 |
rc = idxCreateFromWhere(p, pIter, pIter->pOrder); |
| 1106 |
} |
| 1107 |
} |
| 1108 |
|
| 1109 |
return rc; |
| 1110 |
} |
| 1111 |
|
| 1112 |
/* |
| 1113 |
** Free all elements of the linked list starting at pConstraint. |
| 1114 |
*/ |
| 1115 |
static void idxConstraintFree(IdxConstraint *pConstraint){ |
| 1116 |
IdxConstraint *pNext; |
| 1117 |
IdxConstraint *p; |
| 1118 |
|
| 1119 |
for(p=pConstraint; p; p=pNext){ |
| 1120 |
pNext = p->pNext; |
| 1121 |
sqlite3_free(p); |
| 1122 |
} |
| 1123 |
} |
| 1124 |
|
| 1125 |
/* |
| 1126 |
** Free all elements of the linked list starting from pScan up until pLast |
| 1127 |
** (pLast is not freed). |
| 1128 |
*/ |
| 1129 |
static void idxScanFree(IdxScan *pScan, IdxScan *pLast){ |
| 1130 |
IdxScan *p; |
| 1131 |
IdxScan *pNext; |
| 1132 |
for(p=pScan; p!=pLast; p=pNext){ |
| 1133 |
pNext = p->pNextScan; |
| 1134 |
idxConstraintFree(p->pOrder); |
| 1135 |
idxConstraintFree(p->pEq); |
| 1136 |
idxConstraintFree(p->pRange); |
| 1137 |
sqlite3_free(p); |
| 1138 |
} |
| 1139 |
} |
| 1140 |
|
| 1141 |
/* |
| 1142 |
** Free all elements of the linked list starting from pStatement up |
| 1143 |
** until pLast (pLast is not freed). |
| 1144 |
*/ |
| 1145 |
static void idxStatementFree(IdxStatement *pStatement, IdxStatement *pLast){ |
| 1146 |
IdxStatement *p; |
| 1147 |
IdxStatement *pNext; |
| 1148 |
for(p=pStatement; p!=pLast; p=pNext){ |
| 1149 |
pNext = p->pNext; |
| 1150 |
sqlite3_free(p->zEQP); |
| 1151 |
sqlite3_free(p->zIdx); |
| 1152 |
sqlite3_free(p); |
| 1153 |
} |
| 1154 |
} |
| 1155 |
|
| 1156 |
/* |
| 1157 |
** Free the linked list of IdxTable objects starting at pTab. |
| 1158 |
*/ |
| 1159 |
static void idxTableFree(IdxTable *pTab){ |
| 1160 |
IdxTable *pIter; |
| 1161 |
IdxTable *pNext; |
| 1162 |
for(pIter=pTab; pIter; pIter=pNext){ |
| 1163 |
pNext = pIter->pNext; |
| 1164 |
sqlite3_free(pIter); |
| 1165 |
} |
| 1166 |
} |
| 1167 |
|
| 1168 |
/* |
| 1169 |
** Free the linked list of IdxWrite objects starting at pTab. |
| 1170 |
*/ |
| 1171 |
static void idxWriteFree(IdxWrite *pTab){ |
| 1172 |
IdxWrite *pIter; |
| 1173 |
IdxWrite *pNext; |
| 1174 |
for(pIter=pTab; pIter; pIter=pNext){ |
| 1175 |
pNext = pIter->pNext; |
| 1176 |
sqlite3_free(pIter); |
| 1177 |
} |
| 1178 |
} |
| 1179 |
|
| 1180 |
|
| 1181 |
|
| 1182 |
/* |
| 1183 |
** This function is called after candidate indexes have been created. It |
| 1184 |
** runs all the queries to see which indexes they prefer, and populates |
| 1185 |
** IdxStatement.zIdx and IdxStatement.zEQP with the results. |
| 1186 |
*/ |
| 1187 |
static int idxFindIndexes( |
| 1188 |
sqlite3expert *p, |
| 1189 |
char **pzErr /* OUT: Error message (sqlite3_malloc) */ |
| 1190 |
){ |
| 1191 |
IdxStatement *pStmt; |
| 1192 |
sqlite3 *dbm = p->dbm; |
| 1193 |
int rc = SQLITE_OK; |
| 1194 |
|
| 1195 |
IdxHash hIdx; |
| 1196 |
idxHashInit(&hIdx); |
| 1197 |
|
| 1198 |
for(pStmt=p->pStatement; rc==SQLITE_OK && pStmt; pStmt=pStmt->pNext){ |
| 1199 |
IdxHashEntry *pEntry; |
| 1200 |
sqlite3_stmt *pExplain = 0; |
| 1201 |
idxHashClear(&hIdx); |
| 1202 |
rc = idxPrintfPrepareStmt(dbm, &pExplain, pzErr, |
| 1203 |
"EXPLAIN QUERY PLAN %s", pStmt->zSql |
| 1204 |
); |
| 1205 |
while( rc==SQLITE_OK && sqlite3_step(pExplain)==SQLITE_ROW ){ |
| 1206 |
/* int iId = sqlite3_column_int(pExplain, 0); */ |
| 1207 |
/* int iParent = sqlite3_column_int(pExplain, 1); */ |
| 1208 |
/* int iNotUsed = sqlite3_column_int(pExplain, 2); */ |
| 1209 |
const char *zDetail = (const char*)sqlite3_column_text(pExplain, 3); |
| 1210 |
int nDetail; |
| 1211 |
int i; |
| 1212 |
|
| 1213 |
if( !zDetail ) continue; |
| 1214 |
nDetail = STRLEN(zDetail); |
| 1215 |
|
| 1216 |
for(i=0; i<nDetail; i++){ |
| 1217 |
const char *zIdx = 0; |
| 1218 |
if( i+13<nDetail && memcmp(&zDetail[i], " USING INDEX ", 13)==0 ){ |
| 1219 |
zIdx = &zDetail[i+13]; |
| 1220 |
}else if( i+22<nDetail |
| 1221 |
&& memcmp(&zDetail[i], " USING COVERING INDEX ", 22)==0 |
| 1222 |
){ |
| 1223 |
zIdx = &zDetail[i+22]; |
| 1224 |
} |
| 1225 |
if( zIdx ){ |
| 1226 |
const char *zSql; |
| 1227 |
int nIdx = 0; |
| 1228 |
while( zIdx[nIdx]!='\0' && (zIdx[nIdx]!=' ' || zIdx[nIdx+1]!='(') ){ |
| 1229 |
nIdx++; |
| 1230 |
} |
| 1231 |
zSql = idxHashSearch(&p->hIdx, zIdx, nIdx); |
| 1232 |
if( zSql ){ |
| 1233 |
idxHashAdd(&rc, &hIdx, zSql, 0); |
| 1234 |
if( rc ) goto find_indexes_out; |
| 1235 |
} |
| 1236 |
break; |
| 1237 |
} |
| 1238 |
} |
| 1239 |
|
| 1240 |
if( zDetail[0]!='-' ){ |
| 1241 |
pStmt->zEQP = idxAppendText(&rc, pStmt->zEQP, "%s\n", zDetail); |
| 1242 |
} |
| 1243 |
} |
| 1244 |
|
| 1245 |
for(pEntry=hIdx.pFirst; pEntry; pEntry=pEntry->pNext){ |
| 1246 |
pStmt->zIdx = idxAppendText(&rc, pStmt->zIdx, "%s;\n", pEntry->zKey); |
| 1247 |
} |
| 1248 |
|
| 1249 |
idxFinalize(&rc, pExplain); |
| 1250 |
} |
| 1251 |
|
| 1252 |
find_indexes_out: |
| 1253 |
idxHashClear(&hIdx); |
| 1254 |
return rc; |
| 1255 |
} |
| 1256 |
|
| 1257 |
static int idxAuthCallback( |
| 1258 |
void *pCtx, |
| 1259 |
int eOp, |
| 1260 |
const char *z3, |
| 1261 |
const char *z4, |
| 1262 |
const char *zDb, |
| 1263 |
const char *zTrigger |
| 1264 |
){ |
| 1265 |
int rc = SQLITE_OK; |
| 1266 |
(void)z4; |
| 1267 |
(void)zTrigger; |
| 1268 |
if( eOp==SQLITE_INSERT || eOp==SQLITE_UPDATE || eOp==SQLITE_DELETE ){ |
| 1269 |
if( sqlite3_stricmp(zDb, "main")==0 ){ |
| 1270 |
sqlite3expert *p = (sqlite3expert*)pCtx; |
| 1271 |
IdxTable *pTab; |
| 1272 |
for(pTab=p->pTable; pTab; pTab=pTab->pNext){ |
| 1273 |
if( 0==sqlite3_stricmp(z3, pTab->zName) ) break; |
| 1274 |
} |
| 1275 |
if( pTab ){ |
| 1276 |
IdxWrite *pWrite; |
| 1277 |
for(pWrite=p->pWrite; pWrite; pWrite=pWrite->pNext){ |
| 1278 |
if( pWrite->pTab==pTab && pWrite->eOp==eOp ) break; |
| 1279 |
} |
| 1280 |
if( pWrite==0 ){ |
| 1281 |
pWrite = idxMalloc(&rc, sizeof(IdxWrite)); |
| 1282 |
if( rc==SQLITE_OK ){ |
| 1283 |
pWrite->pTab = pTab; |
| 1284 |
pWrite->eOp = eOp; |
| 1285 |
pWrite->pNext = p->pWrite; |
| 1286 |
p->pWrite = pWrite; |
| 1287 |
} |
| 1288 |
} |
| 1289 |
} |
| 1290 |
} |
| 1291 |
} |
| 1292 |
return rc; |
| 1293 |
} |
| 1294 |
|
| 1295 |
static int idxProcessOneTrigger( |
| 1296 |
sqlite3expert *p, |
| 1297 |
IdxWrite *pWrite, |
| 1298 |
char **pzErr |
| 1299 |
){ |
| 1300 |
static const char *zInt = UNIQUE_TABLE_NAME; |
| 1301 |
static const char *zDrop = "DROP TABLE " UNIQUE_TABLE_NAME; |
| 1302 |
IdxTable *pTab = pWrite->pTab; |
| 1303 |
const char *zTab = pTab->zName; |
| 1304 |
const char *zSql = |
| 1305 |
"SELECT 'CREATE TEMP' || substr(sql, 7) FROM sqlite_schema " |
| 1306 |
"WHERE tbl_name = %Q AND type IN ('table', 'trigger') " |
| 1307 |
"ORDER BY type;"; |
| 1308 |
sqlite3_stmt *pSelect = 0; |
| 1309 |
int rc = SQLITE_OK; |
| 1310 |
char *zWrite = 0; |
| 1311 |
|
| 1312 |
/* Create the table and its triggers in the temp schema */ |
| 1313 |
rc = idxPrintfPrepareStmt(p->db, &pSelect, pzErr, zSql, zTab, zTab); |
| 1314 |
while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSelect) ){ |
| 1315 |
const char *zCreate = (const char*)sqlite3_column_text(pSelect, 0); |
| 1316 |
if( zCreate==0 ) continue; |
| 1317 |
rc = sqlite3_exec(p->dbv, zCreate, 0, 0, pzErr); |
| 1318 |
} |
| 1319 |
idxFinalize(&rc, pSelect); |
| 1320 |
|
| 1321 |
/* Rename the table in the temp schema to zInt */ |
| 1322 |
if( rc==SQLITE_OK ){ |
| 1323 |
char *z = sqlite3_mprintf("ALTER TABLE temp.%Q RENAME TO %Q", zTab, zInt); |
| 1324 |
if( z==0 ){ |
| 1325 |
rc = SQLITE_NOMEM; |
| 1326 |
}else{ |
| 1327 |
rc = sqlite3_exec(p->dbv, z, 0, 0, pzErr); |
| 1328 |
sqlite3_free(z); |
| 1329 |
} |
| 1330 |
} |
| 1331 |
|
| 1332 |
switch( pWrite->eOp ){ |
| 1333 |
case SQLITE_INSERT: { |
| 1334 |
int i; |
| 1335 |
zWrite = idxAppendText(&rc, zWrite, "INSERT INTO %Q VALUES(", zInt); |
| 1336 |
for(i=0; i<pTab->nCol; i++){ |
| 1337 |
zWrite = idxAppendText(&rc, zWrite, "%s?", i==0 ? "" : ", "); |
| 1338 |
} |
| 1339 |
zWrite = idxAppendText(&rc, zWrite, ")"); |
| 1340 |
break; |
| 1341 |
} |
| 1342 |
case SQLITE_UPDATE: { |
| 1343 |
int i; |
| 1344 |
zWrite = idxAppendText(&rc, zWrite, "UPDATE %Q SET ", zInt); |
| 1345 |
for(i=0; i<pTab->nCol; i++){ |
| 1346 |
zWrite = idxAppendText(&rc, zWrite, "%s%Q=?", i==0 ? "" : ", ", |
| 1347 |
pTab->aCol[i].zName |
| 1348 |
); |
| 1349 |
} |
| 1350 |
break; |
| 1351 |
} |
| 1352 |
default: { |
| 1353 |
assert( pWrite->eOp==SQLITE_DELETE ); |
| 1354 |
if( rc==SQLITE_OK ){ |
| 1355 |
zWrite = sqlite3_mprintf("DELETE FROM %Q", zInt); |
| 1356 |
if( zWrite==0 ) rc = SQLITE_NOMEM; |
| 1357 |
} |
| 1358 |
} |
| 1359 |
} |
| 1360 |
|
| 1361 |
if( rc==SQLITE_OK ){ |
| 1362 |
sqlite3_stmt *pX = 0; |
| 1363 |
rc = sqlite3_prepare_v2(p->dbv, zWrite, -1, &pX, 0); |
| 1364 |
idxFinalize(&rc, pX); |
| 1365 |
if( rc!=SQLITE_OK ){ |
| 1366 |
idxDatabaseError(p->dbv, pzErr); |
| 1367 |
} |
| 1368 |
} |
| 1369 |
sqlite3_free(zWrite); |
| 1370 |
|
| 1371 |
if( rc==SQLITE_OK ){ |
| 1372 |
rc = sqlite3_exec(p->dbv, zDrop, 0, 0, pzErr); |
| 1373 |
} |
| 1374 |
|
| 1375 |
return rc; |
| 1376 |
} |
| 1377 |
|
| 1378 |
static int idxProcessTriggers(sqlite3expert *p, char **pzErr){ |
| 1379 |
int rc = SQLITE_OK; |
| 1380 |
IdxWrite *pEnd = 0; |
| 1381 |
IdxWrite *pFirst = p->pWrite; |
| 1382 |
|
| 1383 |
while( rc==SQLITE_OK && pFirst!=pEnd ){ |
| 1384 |
IdxWrite *pIter; |
| 1385 |
for(pIter=pFirst; rc==SQLITE_OK && pIter!=pEnd; pIter=pIter->pNext){ |
| 1386 |
rc = idxProcessOneTrigger(p, pIter, pzErr); |
| 1387 |
} |
| 1388 |
pEnd = pFirst; |
| 1389 |
pFirst = p->pWrite; |
| 1390 |
} |
| 1391 |
|
| 1392 |
return rc; |
| 1393 |
} |
| 1394 |
|
| 1395 |
/* |
| 1396 |
** This function tests if the schema of the main database of database handle |
| 1397 |
** db contains an object named zTab. Assuming no error occurs, output parameter |
| 1398 |
** (*pbContains) is set to true if zTab exists, or false if it does not. |
| 1399 |
** |
| 1400 |
** Or, if an error occurs, an SQLite error code is returned. The final value |
| 1401 |
** of (*pbContains) is undefined in this case. |
| 1402 |
*/ |
| 1403 |
static int expertDbContainsObject( |
| 1404 |
sqlite3 *db, |
| 1405 |
const char *zTab, |
| 1406 |
int *pbContains /* OUT: True if object exists */ |
| 1407 |
){ |
| 1408 |
const char *zSql = "SELECT 1 FROM sqlite_schema WHERE name = ?"; |
| 1409 |
sqlite3_stmt *pSql = 0; |
| 1410 |
int rc = SQLITE_OK; |
| 1411 |
int ret = 0; |
| 1412 |
|
| 1413 |
rc = sqlite3_prepare_v2(db, zSql, -1, &pSql, 0); |
| 1414 |
if( rc==SQLITE_OK ){ |
| 1415 |
sqlite3_bind_text(pSql, 1, zTab, -1, SQLITE_STATIC); |
| 1416 |
if( SQLITE_ROW==sqlite3_step(pSql) ){ |
| 1417 |
ret = 1; |
| 1418 |
} |
| 1419 |
rc = sqlite3_finalize(pSql); |
| 1420 |
} |
| 1421 |
|
| 1422 |
*pbContains = ret; |
| 1423 |
return rc; |
| 1424 |
} |
| 1425 |
|
| 1426 |
/* |
| 1427 |
** Execute SQL command zSql using database handle db. If no error occurs, |
| 1428 |
** set (*pzErr) to NULL and return SQLITE_OK. |
| 1429 |
** |
| 1430 |
** If an error does occur, return an SQLite error code and set (*pzErr) to |
| 1431 |
** point to a buffer containing an English language error message. Except, |
| 1432 |
** if the error message begins with "no such module:", then ignore the |
| 1433 |
** error and return as if the SQL statement had succeeded. |
| 1434 |
** |
| 1435 |
** This is used to copy as much of the database schema as possible while |
| 1436 |
** ignoring any errors related to missing virtual table modules. |
| 1437 |
*/ |
| 1438 |
static int expertSchemaSql(sqlite3 *db, const char *zSql, char **pzErr){ |
| 1439 |
int rc = SQLITE_OK; |
| 1440 |
char *zErr = 0; |
| 1441 |
|
| 1442 |
rc = sqlite3_exec(db, zSql, 0, 0, &zErr); |
| 1443 |
if( rc!=SQLITE_OK && zErr ){ |
| 1444 |
int nErr = STRLEN(zErr); |
| 1445 |
if( nErr>=15 && memcmp(zErr, "no such module:", 15)==0 ){ |
| 1446 |
sqlite3_free(zErr); |
| 1447 |
rc = SQLITE_OK; |
| 1448 |
zErr = 0; |
| 1449 |
} |
| 1450 |
} |
| 1451 |
|
| 1452 |
*pzErr = zErr; |
| 1453 |
return rc; |
| 1454 |
} |
| 1455 |
|
| 1456 |
static int idxCreateVtabSchema(sqlite3expert *p, char **pzErrmsg){ |
| 1457 |
int rc = idxRegisterVtab(p); |
| 1458 |
sqlite3_stmt *pSchema = 0; |
| 1459 |
|
| 1460 |
/* For each table in the main db schema: |
| 1461 |
** |
| 1462 |
** 1) Add an entry to the p->pTable list, and |
| 1463 |
** 2) Create the equivalent virtual table in dbv. |
| 1464 |
*/ |
| 1465 |
rc = idxPrepareStmt(p->db, &pSchema, pzErrmsg, |
| 1466 |
"SELECT type, name, sql, 1, " |
| 1467 |
" substr(sql,1,14)=='create virtual' COLLATE nocase " |
| 1468 |
"FROM sqlite_schema " |
| 1469 |
"WHERE type IN ('table','view') AND " |
| 1470 |
" substr(name,1,7)!='sqlite_' COLLATE nocase " |
| 1471 |
" UNION ALL " |
| 1472 |
"SELECT type, name, sql, 2, 0 FROM sqlite_schema " |
| 1473 |
"WHERE type = 'trigger'" |
| 1474 |
" AND tbl_name IN(SELECT name FROM sqlite_schema WHERE type = 'view') " |
| 1475 |
"ORDER BY 4, 5 DESC, 1" |
| 1476 |
); |
| 1477 |
while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSchema) ){ |
| 1478 |
const char *zType = (const char*)sqlite3_column_text(pSchema, 0); |
| 1479 |
const char *zName = (const char*)sqlite3_column_text(pSchema, 1); |
| 1480 |
const char *zSql = (const char*)sqlite3_column_text(pSchema, 2); |
| 1481 |
int bVirtual = sqlite3_column_int(pSchema, 4); |
| 1482 |
int bExists = 0; |
| 1483 |
|
| 1484 |
if( zType==0 || zName==0 ) continue; |
| 1485 |
rc = expertDbContainsObject(p->dbv, zName, &bExists); |
| 1486 |
if( rc || bExists ) continue; |
| 1487 |
|
| 1488 |
if( zType[0]=='v' || zType[1]=='r' || bVirtual ){ |
| 1489 |
/* A view. Or a trigger on a view. */ |
| 1490 |
if( zSql ) rc = expertSchemaSql(p->dbv, zSql, pzErrmsg); |
| 1491 |
}else{ |
| 1492 |
IdxTable *pTab; |
| 1493 |
rc = idxGetTableInfo(p->db, zName, &pTab, pzErrmsg); |
| 1494 |
if( rc==SQLITE_OK && ALWAYS(pTab!=0) ){ |
| 1495 |
int i; |
| 1496 |
char *zInner = 0; |
| 1497 |
char *zOuter = 0; |
| 1498 |
pTab->pNext = p->pTable; |
| 1499 |
p->pTable = pTab; |
| 1500 |
|
| 1501 |
/* The statement the vtab will pass to sqlite3_declare_vtab() */ |
| 1502 |
zInner = idxAppendText(&rc, 0, "CREATE TABLE x("); |
| 1503 |
for(i=0; i<pTab->nCol; i++){ |
| 1504 |
zInner = idxAppendText(&rc, zInner, "%s%Q COLLATE %s", |
| 1505 |
(i==0 ? "" : ", "), pTab->aCol[i].zName, pTab->aCol[i].zColl |
| 1506 |
); |
| 1507 |
} |
| 1508 |
zInner = idxAppendText(&rc, zInner, ")"); |
| 1509 |
|
| 1510 |
/* The CVT statement to create the vtab */ |
| 1511 |
zOuter = idxAppendText(&rc, 0, |
| 1512 |
"CREATE VIRTUAL TABLE %Q USING expert(%Q)", zName, zInner |
| 1513 |
); |
| 1514 |
if( rc==SQLITE_OK ){ |
| 1515 |
rc = sqlite3_exec(p->dbv, zOuter, 0, 0, pzErrmsg); |
| 1516 |
} |
| 1517 |
sqlite3_free(zInner); |
| 1518 |
sqlite3_free(zOuter); |
| 1519 |
} |
| 1520 |
} |
| 1521 |
} |
| 1522 |
idxFinalize(&rc, pSchema); |
| 1523 |
return rc; |
| 1524 |
} |
| 1525 |
|
| 1526 |
struct IdxSampleCtx { |
| 1527 |
int iTarget; |
| 1528 |
double target; /* Target nRet/nRow value */ |
| 1529 |
double nRow; /* Number of rows seen */ |
| 1530 |
double nRet; /* Number of rows returned */ |
| 1531 |
}; |
| 1532 |
|
| 1533 |
static void idxSampleFunc( |
| 1534 |
sqlite3_context *pCtx, |
| 1535 |
int argc, |
| 1536 |
sqlite3_value **argv |
| 1537 |
){ |
| 1538 |
struct IdxSampleCtx *p = (struct IdxSampleCtx*)sqlite3_user_data(pCtx); |
| 1539 |
int bRet; |
| 1540 |
|
| 1541 |
(void)argv; |
| 1542 |
assert( argc==0 ); |
| 1543 |
if( p->nRow==0.0 ){ |
| 1544 |
bRet = 1; |
| 1545 |
}else{ |
| 1546 |
bRet = (p->nRet / p->nRow) <= p->target; |
| 1547 |
if( bRet==0 ){ |
| 1548 |
unsigned short rnd; |
| 1549 |
sqlite3_randomness(2, (void*)&rnd); |
| 1550 |
bRet = ((int)rnd % 100) <= p->iTarget; |
| 1551 |
} |
| 1552 |
} |
| 1553 |
|
| 1554 |
sqlite3_result_int(pCtx, bRet); |
| 1555 |
p->nRow += 1.0; |
| 1556 |
p->nRet += (double)bRet; |
| 1557 |
} |
| 1558 |
|
| 1559 |
struct IdxRemCtx { |
| 1560 |
int nSlot; |
| 1561 |
struct IdxRemSlot { |
| 1562 |
int eType; /* SQLITE_NULL, INTEGER, REAL, TEXT, BLOB */ |
| 1563 |
i64 iVal; /* SQLITE_INTEGER value */ |
| 1564 |
double rVal; /* SQLITE_FLOAT value */ |
| 1565 |
i64 nByte; /* Bytes of space allocated at z */ |
| 1566 |
i64 n; /* Size of buffer z */ |
| 1567 |
char *z; /* SQLITE_TEXT/BLOB value */ |
| 1568 |
} aSlot[1]; |
| 1569 |
}; |
| 1570 |
|
| 1571 |
/* |
| 1572 |
** Implementation of scalar function sqlite_expert_rem(). |
| 1573 |
*/ |
| 1574 |
static void idxRemFunc( |
| 1575 |
sqlite3_context *pCtx, |
| 1576 |
int argc, |
| 1577 |
sqlite3_value **argv |
| 1578 |
){ |
| 1579 |
struct IdxRemCtx *p = (struct IdxRemCtx*)sqlite3_user_data(pCtx); |
| 1580 |
struct IdxRemSlot *pSlot; |
| 1581 |
int iSlot; |
| 1582 |
assert( argc==2 ); |
| 1583 |
|
| 1584 |
iSlot = sqlite3_value_int(argv[0]); |
| 1585 |
assert( iSlot<p->nSlot ); |
| 1586 |
pSlot = &p->aSlot[iSlot]; |
| 1587 |
|
| 1588 |
switch( pSlot->eType ){ |
| 1589 |
case SQLITE_NULL: |
| 1590 |
/* no-op */ |
| 1591 |
break; |
| 1592 |
|
| 1593 |
case SQLITE_INTEGER: |
| 1594 |
sqlite3_result_int64(pCtx, pSlot->iVal); |
| 1595 |
break; |
| 1596 |
|
| 1597 |
case SQLITE_FLOAT: |
| 1598 |
sqlite3_result_double(pCtx, pSlot->rVal); |
| 1599 |
break; |
| 1600 |
|
| 1601 |
case SQLITE_BLOB: |
| 1602 |
assert( pSlot->n <= 0x7fffffff ); |
| 1603 |
sqlite3_result_blob(pCtx, pSlot->z, (int)pSlot->n, SQLITE_TRANSIENT); |
| 1604 |
break; |
| 1605 |
|
| 1606 |
case SQLITE_TEXT: |
| 1607 |
assert( pSlot->n <= 0x7fffffff ); |
| 1608 |
sqlite3_result_text(pCtx, pSlot->z, (int)pSlot->n, SQLITE_TRANSIENT); |
| 1609 |
break; |
| 1610 |
} |
| 1611 |
|
| 1612 |
pSlot->eType = sqlite3_value_type(argv[1]); |
| 1613 |
switch( pSlot->eType ){ |
| 1614 |
case SQLITE_NULL: |
| 1615 |
/* no-op */ |
| 1616 |
break; |
| 1617 |
|
| 1618 |
case SQLITE_INTEGER: |
| 1619 |
pSlot->iVal = sqlite3_value_int64(argv[1]); |
| 1620 |
break; |
| 1621 |
|
| 1622 |
case SQLITE_FLOAT: |
| 1623 |
pSlot->rVal = sqlite3_value_double(argv[1]); |
| 1624 |
break; |
| 1625 |
|
| 1626 |
case SQLITE_BLOB: |
| 1627 |
case SQLITE_TEXT: { |
| 1628 |
i64 nByte = sqlite3_value_bytes(argv[1]); |
| 1629 |
const void *pData = 0; |
| 1630 |
if( nByte>pSlot->nByte ){ |
| 1631 |
char *zNew = (char*)sqlite3_realloc64(pSlot->z, nByte*2); |
| 1632 |
if( zNew==0 ){ |
| 1633 |
sqlite3_result_error_nomem(pCtx); |
| 1634 |
return; |
| 1635 |
} |
| 1636 |
pSlot->nByte = nByte*2; |
| 1637 |
pSlot->z = zNew; |
| 1638 |
} |
| 1639 |
pSlot->n = nByte; |
| 1640 |
if( pSlot->eType==SQLITE_BLOB ){ |
| 1641 |
pData = sqlite3_value_blob(argv[1]); |
| 1642 |
if( pData ) memcpy(pSlot->z, pData, nByte); |
| 1643 |
}else{ |
| 1644 |
pData = sqlite3_value_text(argv[1]); |
| 1645 |
memcpy(pSlot->z, pData, nByte); |
| 1646 |
} |
| 1647 |
break; |
| 1648 |
} |
| 1649 |
} |
| 1650 |
} |
| 1651 |
|
| 1652 |
static int idxLargestIndex(sqlite3 *db, int *pnMax, char **pzErr){ |
| 1653 |
int rc = SQLITE_OK; |
| 1654 |
const char *zMax = |
| 1655 |
"SELECT max(i.seqno) FROM " |
| 1656 |
" sqlite_schema AS s, " |
| 1657 |
" pragma_index_list(s.name) AS l, " |
| 1658 |
" pragma_index_info(l.name) AS i " |
| 1659 |
"WHERE s.type = 'table'"; |
| 1660 |
sqlite3_stmt *pMax = 0; |
| 1661 |
|
| 1662 |
*pnMax = 0; |
| 1663 |
rc = idxPrepareStmt(db, &pMax, pzErr, zMax); |
| 1664 |
if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pMax) ){ |
| 1665 |
*pnMax = sqlite3_column_int(pMax, 0) + 1; |
| 1666 |
} |
| 1667 |
idxFinalize(&rc, pMax); |
| 1668 |
|
| 1669 |
return rc; |
| 1670 |
} |
| 1671 |
|
| 1672 |
static int idxPopulateOneStat1( |
| 1673 |
sqlite3expert *p, |
| 1674 |
sqlite3_stmt *pIndexXInfo, |
| 1675 |
sqlite3_stmt *pWriteStat, |
| 1676 |
const char *zTab, |
| 1677 |
const char *zIdx, |
| 1678 |
char **pzErr |
| 1679 |
){ |
| 1680 |
char *zCols = 0; |
| 1681 |
char *zOrder = 0; |
| 1682 |
char *zQuery = 0; |
| 1683 |
int nCol = 0; |
| 1684 |
int i; |
| 1685 |
sqlite3_stmt *pQuery = 0; |
| 1686 |
i64 *aStat = 0; |
| 1687 |
int rc = SQLITE_OK; |
| 1688 |
|
| 1689 |
assert( p->iSample>0 ); |
| 1690 |
|
| 1691 |
/* Formulate the query text */ |
| 1692 |
sqlite3_bind_text(pIndexXInfo, 1, zIdx, -1, SQLITE_STATIC); |
| 1693 |
while( SQLITE_OK==rc && SQLITE_ROW==sqlite3_step(pIndexXInfo) ){ |
| 1694 |
const char *zComma = zCols==0 ? "" : ", "; |
| 1695 |
const char *zName = (const char*)sqlite3_column_text(pIndexXInfo, 0); |
| 1696 |
const char *zColl = (const char*)sqlite3_column_text(pIndexXInfo, 1); |
| 1697 |
if( zName==0 ){ |
| 1698 |
/* This index contains an expression. Ignore it. */ |
| 1699 |
sqlite3_free(zCols); |
| 1700 |
sqlite3_free(zOrder); |
| 1701 |
return sqlite3_reset(pIndexXInfo); |
| 1702 |
} |
| 1703 |
zCols = idxAppendText(&rc, zCols, |
| 1704 |
"%sx.%Q IS sqlite_expert_rem(%d, x.%Q) COLLATE %s", |
| 1705 |
zComma, zName, nCol, zName, zColl |
| 1706 |
); |
| 1707 |
zOrder = idxAppendText(&rc, zOrder, "%s%d", zComma, ++nCol); |
| 1708 |
} |
| 1709 |
sqlite3_reset(pIndexXInfo); |
| 1710 |
if( rc==SQLITE_OK ){ |
| 1711 |
if( p->iSample==100 ){ |
| 1712 |
zQuery = sqlite3_mprintf( |
| 1713 |
"SELECT %s FROM %Q x ORDER BY %s", zCols, zTab, zOrder |
| 1714 |
); |
| 1715 |
}else{ |
| 1716 |
zQuery = sqlite3_mprintf( |
| 1717 |
"SELECT %s FROM temp."UNIQUE_TABLE_NAME" x ORDER BY %s", zCols, zOrder |
| 1718 |
); |
| 1719 |
} |
| 1720 |
} |
| 1721 |
sqlite3_free(zCols); |
| 1722 |
sqlite3_free(zOrder); |
| 1723 |
|
| 1724 |
/* Formulate the query text */ |
| 1725 |
if( rc==SQLITE_OK ){ |
| 1726 |
sqlite3 *dbrem = (p->iSample==100 ? p->db : p->dbv); |
| 1727 |
rc = idxPrepareStmt(dbrem, &pQuery, pzErr, zQuery); |
| 1728 |
} |
| 1729 |
sqlite3_free(zQuery); |
| 1730 |
|
| 1731 |
if( rc==SQLITE_OK ){ |
| 1732 |
aStat = (i64*)idxMalloc(&rc, sizeof(i64)*(nCol+1)); |
| 1733 |
} |
| 1734 |
if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pQuery) ){ |
| 1735 |
IdxHashEntry *pEntry; |
| 1736 |
char *zStat = 0; |
| 1737 |
for(i=0; i<=nCol; i++) aStat[i] = 1; |
| 1738 |
while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pQuery) ){ |
| 1739 |
aStat[0]++; |
| 1740 |
for(i=0; i<nCol; i++){ |
| 1741 |
if( sqlite3_column_int(pQuery, i)==0 ) break; |
| 1742 |
} |
| 1743 |
for(/*no-op*/; i<nCol; i++){ |
| 1744 |
aStat[i+1]++; |
| 1745 |
} |
| 1746 |
} |
| 1747 |
|
| 1748 |
if( rc==SQLITE_OK ){ |
| 1749 |
i64 s0 = aStat[0]; |
| 1750 |
zStat = sqlite3_mprintf("%lld", s0); |
| 1751 |
if( zStat==0 ) rc = SQLITE_NOMEM; |
| 1752 |
for(i=1; rc==SQLITE_OK && i<=nCol; i++){ |
| 1753 |
zStat = idxAppendText(&rc, zStat, " %lld", (s0+aStat[i]/2) / aStat[i]); |
| 1754 |
} |
| 1755 |
} |
| 1756 |
|
| 1757 |
if( rc==SQLITE_OK ){ |
| 1758 |
sqlite3_bind_text(pWriteStat, 1, zTab, -1, SQLITE_STATIC); |
| 1759 |
sqlite3_bind_text(pWriteStat, 2, zIdx, -1, SQLITE_STATIC); |
| 1760 |
sqlite3_bind_text(pWriteStat, 3, zStat, -1, SQLITE_STATIC); |
| 1761 |
sqlite3_step(pWriteStat); |
| 1762 |
rc = sqlite3_reset(pWriteStat); |
| 1763 |
} |
| 1764 |
|
| 1765 |
pEntry = idxHashFind(&p->hIdx, zIdx, STRLEN(zIdx)); |
| 1766 |
if( pEntry ){ |
| 1767 |
assert( pEntry->zVal2==0 ); |
| 1768 |
pEntry->zVal2 = zStat; |
| 1769 |
}else{ |
| 1770 |
sqlite3_free(zStat); |
| 1771 |
} |
| 1772 |
} |
| 1773 |
sqlite3_free(aStat); |
| 1774 |
idxFinalize(&rc, pQuery); |
| 1775 |
|
| 1776 |
return rc; |
| 1777 |
} |
| 1778 |
|
| 1779 |
static int idxBuildSampleTable(sqlite3expert *p, const char *zTab){ |
| 1780 |
int rc; |
| 1781 |
char *zSql; |
| 1782 |
|
| 1783 |
rc = sqlite3_exec(p->dbv,"DROP TABLE IF EXISTS temp."UNIQUE_TABLE_NAME,0,0,0); |
| 1784 |
if( rc!=SQLITE_OK ) return rc; |
| 1785 |
|
| 1786 |
zSql = sqlite3_mprintf( |
| 1787 |
"CREATE TABLE temp." UNIQUE_TABLE_NAME " AS SELECT * FROM %Q", zTab |
| 1788 |
); |
| 1789 |
if( zSql==0 ) return SQLITE_NOMEM; |
| 1790 |
rc = sqlite3_exec(p->dbv, zSql, 0, 0, 0); |
| 1791 |
sqlite3_free(zSql); |
| 1792 |
|
| 1793 |
return rc; |
| 1794 |
} |
| 1795 |
|
| 1796 |
/* |
| 1797 |
** This function is called as part of sqlite3_expert_analyze(). Candidate |
| 1798 |
** indexes have already been created in database sqlite3expert.dbm, this |
| 1799 |
** function populates sqlite_stat1 table in the same database. |
| 1800 |
** |
| 1801 |
** The stat1 data is generated by querying the |
| 1802 |
*/ |
| 1803 |
static int idxPopulateStat1(sqlite3expert *p, char **pzErr){ |
| 1804 |
int rc = SQLITE_OK; |
| 1805 |
int nMax =0; |
| 1806 |
struct IdxRemCtx *pCtx = 0; |
| 1807 |
struct IdxSampleCtx samplectx; |
| 1808 |
int i; |
| 1809 |
i64 iPrev = -100000; |
| 1810 |
sqlite3_stmt *pAllIndex = 0; |
| 1811 |
sqlite3_stmt *pIndexXInfo = 0; |
| 1812 |
sqlite3_stmt *pWrite = 0; |
| 1813 |
|
| 1814 |
const char *zAllIndex = |
| 1815 |
"SELECT s.rowid, s.name, l.name FROM " |
| 1816 |
" sqlite_schema AS s, " |
| 1817 |
" pragma_index_list(s.name) AS l " |
| 1818 |
"WHERE s.type = 'table'"; |
| 1819 |
const char *zIndexXInfo = |
| 1820 |
"SELECT name, coll FROM pragma_index_xinfo(?) WHERE key"; |
| 1821 |
const char *zWrite = "INSERT INTO sqlite_stat1 VALUES(?, ?, ?)"; |
| 1822 |
|
| 1823 |
/* If iSample==0, no sqlite_stat1 data is required. */ |
| 1824 |
if( p->iSample==0 ) return SQLITE_OK; |
| 1825 |
|
| 1826 |
rc = idxLargestIndex(p->dbm, &nMax, pzErr); |
| 1827 |
if( nMax<=0 || rc!=SQLITE_OK ) return rc; |
| 1828 |
|
| 1829 |
rc = sqlite3_exec(p->dbm, "ANALYZE; PRAGMA writable_schema=1", 0, 0, 0); |
| 1830 |
|
| 1831 |
if( rc==SQLITE_OK ){ |
| 1832 |
i64 nByte = sizeof(struct IdxRemCtx) + (sizeof(struct IdxRemSlot) * nMax); |
| 1833 |
pCtx = (struct IdxRemCtx*)idxMalloc(&rc, nByte); |
| 1834 |
} |
| 1835 |
|
| 1836 |
if( rc==SQLITE_OK ){ |
| 1837 |
sqlite3 *dbrem = (p->iSample==100 ? p->db : p->dbv); |
| 1838 |
rc = sqlite3_create_function(dbrem, "sqlite_expert_rem", |
| 1839 |
2, SQLITE_UTF8, (void*)pCtx, idxRemFunc, 0, 0 |
| 1840 |
); |
| 1841 |
} |
| 1842 |
if( rc==SQLITE_OK ){ |
| 1843 |
rc = sqlite3_create_function(p->db, "sqlite_expert_sample", |
| 1844 |
0, SQLITE_UTF8, (void*)&samplectx, idxSampleFunc, 0, 0 |
| 1845 |
); |
| 1846 |
} |
| 1847 |
|
| 1848 |
if( rc==SQLITE_OK ){ |
| 1849 |
pCtx->nSlot = (i64)nMax+1; |
| 1850 |
rc = idxPrepareStmt(p->dbm, &pAllIndex, pzErr, zAllIndex); |
| 1851 |
} |
| 1852 |
if( rc==SQLITE_OK ){ |
| 1853 |
rc = idxPrepareStmt(p->dbm, &pIndexXInfo, pzErr, zIndexXInfo); |
| 1854 |
} |
| 1855 |
if( rc==SQLITE_OK ){ |
| 1856 |
rc = idxPrepareStmt(p->dbm, &pWrite, pzErr, zWrite); |
| 1857 |
} |
| 1858 |
|
| 1859 |
while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pAllIndex) ){ |
| 1860 |
i64 iRowid = sqlite3_column_int64(pAllIndex, 0); |
| 1861 |
const char *zTab = (const char*)sqlite3_column_text(pAllIndex, 1); |
| 1862 |
const char *zIdx = (const char*)sqlite3_column_text(pAllIndex, 2); |
| 1863 |
if( zTab==0 || zIdx==0 ) continue; |
| 1864 |
if( p->iSample<100 && iPrev!=iRowid ){ |
| 1865 |
samplectx.target = (double)p->iSample / 100.0; |
| 1866 |
samplectx.iTarget = p->iSample; |
| 1867 |
samplectx.nRow = 0.0; |
| 1868 |
samplectx.nRet = 0.0; |
| 1869 |
rc = idxBuildSampleTable(p, zTab); |
| 1870 |
if( rc!=SQLITE_OK ) break; |
| 1871 |
} |
| 1872 |
rc = idxPopulateOneStat1(p, pIndexXInfo, pWrite, zTab, zIdx, pzErr); |
| 1873 |
iPrev = iRowid; |
| 1874 |
} |
| 1875 |
if( rc==SQLITE_OK && p->iSample<100 ){ |
| 1876 |
rc = sqlite3_exec(p->dbv, |
| 1877 |
"DROP TABLE IF EXISTS temp." UNIQUE_TABLE_NAME, 0,0,0 |
| 1878 |
); |
| 1879 |
} |
| 1880 |
|
| 1881 |
idxFinalize(&rc, pAllIndex); |
| 1882 |
idxFinalize(&rc, pIndexXInfo); |
| 1883 |
idxFinalize(&rc, pWrite); |
| 1884 |
|
| 1885 |
if( pCtx ){ |
| 1886 |
for(i=0; i<pCtx->nSlot; i++){ |
| 1887 |
sqlite3_free(pCtx->aSlot[i].z); |
| 1888 |
} |
| 1889 |
sqlite3_free(pCtx); |
| 1890 |
} |
| 1891 |
|
| 1892 |
if( rc==SQLITE_OK ){ |
| 1893 |
rc = sqlite3_exec(p->dbm, "ANALYZE sqlite_schema", 0, 0, 0); |
| 1894 |
} |
| 1895 |
|
| 1896 |
sqlite3_create_function(p->db, "sqlite_expert_rem", 2, SQLITE_UTF8, 0,0,0,0); |
| 1897 |
sqlite3_create_function(p->db, "sqlite_expert_sample", 0,SQLITE_UTF8,0,0,0,0); |
| 1898 |
|
| 1899 |
sqlite3_exec(p->db, "DROP TABLE IF EXISTS temp."UNIQUE_TABLE_NAME,0,0,0); |
| 1900 |
return rc; |
| 1901 |
} |
| 1902 |
|
| 1903 |
/* |
| 1904 |
** Define and possibly pretend to use a useless collation sequence. |
| 1905 |
** This pretense allows expert to accept SQL using custom collations. |
| 1906 |
*/ |
| 1907 |
int dummyCompare(void *up1, int up2, const void *up3, int up4, const void *up5){ |
| 1908 |
(void)up1; |
| 1909 |
(void)up2; |
| 1910 |
(void)up3; |
| 1911 |
(void)up4; |
| 1912 |
(void)up5; |
| 1913 |
assert(0); /* VDBE should never be run. */ |
| 1914 |
return 0; |
| 1915 |
} |
| 1916 |
/* And a callback to register above upon actual need */ |
| 1917 |
void useDummyCS(void *up1, sqlite3 *db, int etr, const char *zName){ |
| 1918 |
(void)up1; |
| 1919 |
sqlite3_create_collation_v2(db, zName, etr, 0, dummyCompare, 0); |
| 1920 |
} |
| 1921 |
|
| 1922 |
#if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) \ |
| 1923 |
&& !defined(SQLITE_OMIT_INTROSPECTION_PRAGMAS) |
| 1924 |
/* |
| 1925 |
** dummy functions for no-op implementation of UDFs during expert's work |
| 1926 |
*/ |
| 1927 |
void dummyUDF(sqlite3_context *up1, int up2, sqlite3_value **up3){ |
| 1928 |
(void)up1; |
| 1929 |
(void)up2; |
| 1930 |
(void)up3; |
| 1931 |
assert(0); /* VDBE should never be run. */ |
| 1932 |
} |
| 1933 |
void dummyUDFvalue(sqlite3_context *up1){ |
| 1934 |
(void)up1; |
| 1935 |
assert(0); /* VDBE should never be run. */ |
| 1936 |
} |
| 1937 |
|
| 1938 |
/* |
| 1939 |
** Register UDFs from user database with another. |
| 1940 |
*/ |
| 1941 |
int registerUDFs(sqlite3 *dbSrc, sqlite3 *dbDst){ |
| 1942 |
sqlite3_stmt *pStmt; |
| 1943 |
int rc = sqlite3_prepare_v2(dbSrc, |
| 1944 |
"SELECT name,type,enc,narg,flags " |
| 1945 |
"FROM pragma_function_list() " |
| 1946 |
"WHERE builtin==0", -1, &pStmt, 0); |
| 1947 |
if( rc==SQLITE_OK ){ |
| 1948 |
while( SQLITE_ROW==(rc = sqlite3_step(pStmt)) ){ |
| 1949 |
int nargs = sqlite3_column_int(pStmt,3); |
| 1950 |
int flags = sqlite3_column_int(pStmt,4); |
| 1951 |
const char *name = (char*)sqlite3_column_text(pStmt,0); |
| 1952 |
const char *type = (char*)sqlite3_column_text(pStmt,1); |
| 1953 |
const char *enc = (char*)sqlite3_column_text(pStmt,2); |
| 1954 |
if( name==0 || type==0 || enc==0 ){ |
| 1955 |
/* no-op. Only happens on OOM */ |
| 1956 |
}else{ |
| 1957 |
int ienc = SQLITE_UTF8; |
| 1958 |
int rcf = SQLITE_ERROR; |
| 1959 |
if( strcmp(enc,"utf16le")==0 ) ienc = SQLITE_UTF16LE; |
| 1960 |
else if( strcmp(enc,"utf16be")==0 ) ienc = SQLITE_UTF16BE; |
| 1961 |
ienc |= (flags & (SQLITE_DETERMINISTIC|SQLITE_DIRECTONLY)); |
| 1962 |
if( strcmp(type,"w")==0 ){ |
| 1963 |
rcf = sqlite3_create_window_function(dbDst,name,nargs,ienc,0, |
| 1964 |
dummyUDF,dummyUDFvalue,0,0,0); |
| 1965 |
}else if( strcmp(type,"a")==0 ){ |
| 1966 |
rcf = sqlite3_create_function(dbDst,name,nargs,ienc,0, |
| 1967 |
0,dummyUDF,dummyUDFvalue); |
| 1968 |
}else if( strcmp(type,"s")==0 ){ |
| 1969 |
rcf = sqlite3_create_function(dbDst,name,nargs,ienc,0, |
| 1970 |
dummyUDF,0,0); |
| 1971 |
} |
| 1972 |
if( rcf!=SQLITE_OK ){ |
| 1973 |
rc = rcf; |
| 1974 |
break; |
| 1975 |
} |
| 1976 |
} |
| 1977 |
} |
| 1978 |
sqlite3_finalize(pStmt); |
| 1979 |
if( rc==SQLITE_DONE ) rc = SQLITE_OK; |
| 1980 |
} |
| 1981 |
return rc; |
| 1982 |
} |
| 1983 |
#endif |
| 1984 |
|
| 1985 |
/* |
| 1986 |
** Allocate a new sqlite3expert object. |
| 1987 |
*/ |
| 1988 |
sqlite3expert *sqlite3_expert_new(sqlite3 *db, char **pzErrmsg){ |
| 1989 |
int rc = SQLITE_OK; |
| 1990 |
sqlite3expert *pNew; |
| 1991 |
|
| 1992 |
pNew = (sqlite3expert*)idxMalloc(&rc, sizeof(sqlite3expert)); |
| 1993 |
|
| 1994 |
/* Open two in-memory databases to work with. The "vtab database" (dbv) |
| 1995 |
** will contain a virtual table corresponding to each real table in |
| 1996 |
** the user database schema, and a copy of each view. It is used to |
| 1997 |
** collect information regarding the WHERE, ORDER BY and other clauses |
| 1998 |
** of the user's query. |
| 1999 |
*/ |
| 2000 |
if( rc==SQLITE_OK ){ |
| 2001 |
pNew->db = db; |
| 2002 |
pNew->iSample = 100; |
| 2003 |
rc = sqlite3_open(":memory:", &pNew->dbv); |
| 2004 |
} |
| 2005 |
if( rc==SQLITE_OK ){ |
| 2006 |
rc = sqlite3_open(":memory:", &pNew->dbm); |
| 2007 |
if( rc==SQLITE_OK ){ |
| 2008 |
sqlite3_db_config(pNew->dbm, SQLITE_DBCONFIG_TRIGGER_EQP, 1, (int*)0); |
| 2009 |
} |
| 2010 |
} |
| 2011 |
|
| 2012 |
/* Allow custom collations to be dealt with through prepare. */ |
| 2013 |
if( rc==SQLITE_OK ) rc = sqlite3_collation_needed(pNew->dbm,0,useDummyCS); |
| 2014 |
if( rc==SQLITE_OK ) rc = sqlite3_collation_needed(pNew->dbv,0,useDummyCS); |
| 2015 |
|
| 2016 |
#if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) \ |
| 2017 |
&& !defined(SQLITE_OMIT_INTROSPECTION_PRAGMAS) |
| 2018 |
/* Register UDFs from database [db] with [dbm] and [dbv]. */ |
| 2019 |
if( rc==SQLITE_OK ){ |
| 2020 |
rc = registerUDFs(pNew->db, pNew->dbm); |
| 2021 |
} |
| 2022 |
if( rc==SQLITE_OK ){ |
| 2023 |
rc = registerUDFs(pNew->db, pNew->dbv); |
| 2024 |
} |
| 2025 |
#endif |
| 2026 |
|
| 2027 |
/* Copy the entire schema of database [db] into [dbm]. */ |
| 2028 |
if( rc==SQLITE_OK ){ |
| 2029 |
sqlite3_stmt *pSql = 0; |
| 2030 |
rc = idxPrintfPrepareStmt(pNew->db, &pSql, pzErrmsg, |
| 2031 |
"SELECT sql, name, substr(sql,1,14)=='create virtual' COLLATE nocase" |
| 2032 |
" FROM sqlite_schema WHERE substr(name,1,7)!='sqlite_' COLLATE nocase" |
| 2033 |
" ORDER BY 3 DESC, rowid" |
| 2034 |
); |
| 2035 |
while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSql) ){ |
| 2036 |
const char *zSql = (const char*)sqlite3_column_text(pSql, 0); |
| 2037 |
const char *zName = (const char*)sqlite3_column_text(pSql, 1); |
| 2038 |
int bExists = 0; |
| 2039 |
rc = expertDbContainsObject(pNew->dbm, zName, &bExists); |
| 2040 |
if( rc==SQLITE_OK && zSql && bExists==0 ){ |
| 2041 |
rc = expertSchemaSql(pNew->dbm, zSql, pzErrmsg); |
| 2042 |
} |
| 2043 |
} |
| 2044 |
idxFinalize(&rc, pSql); |
| 2045 |
} |
| 2046 |
|
| 2047 |
/* Create the vtab schema */ |
| 2048 |
if( rc==SQLITE_OK ){ |
| 2049 |
rc = idxCreateVtabSchema(pNew, pzErrmsg); |
| 2050 |
} |
| 2051 |
|
| 2052 |
/* Register the auth callback with dbv */ |
| 2053 |
if( rc==SQLITE_OK ){ |
| 2054 |
sqlite3_set_authorizer(pNew->dbv, idxAuthCallback, (void*)pNew); |
| 2055 |
} |
| 2056 |
|
| 2057 |
/* If an error has occurred, free the new object and return NULL. Otherwise, |
| 2058 |
** return the new sqlite3expert handle. */ |
| 2059 |
if( rc!=SQLITE_OK ){ |
| 2060 |
sqlite3_expert_destroy(pNew); |
| 2061 |
pNew = 0; |
| 2062 |
} |
| 2063 |
return pNew; |
| 2064 |
} |
| 2065 |
|
| 2066 |
/* |
| 2067 |
** Configure an sqlite3expert object. |
| 2068 |
*/ |
| 2069 |
int sqlite3_expert_config(sqlite3expert *p, int op, ...){ |
| 2070 |
int rc = SQLITE_OK; |
| 2071 |
va_list ap; |
| 2072 |
va_start(ap, op); |
| 2073 |
switch( op ){ |
| 2074 |
case EXPERT_CONFIG_SAMPLE: { |
| 2075 |
int iVal = va_arg(ap, int); |
| 2076 |
if( iVal<0 ) iVal = 0; |
| 2077 |
if( iVal>100 ) iVal = 100; |
| 2078 |
p->iSample = iVal; |
| 2079 |
break; |
| 2080 |
} |
| 2081 |
default: |
| 2082 |
rc = SQLITE_NOTFOUND; |
| 2083 |
break; |
| 2084 |
} |
| 2085 |
|
| 2086 |
va_end(ap); |
| 2087 |
return rc; |
| 2088 |
} |
| 2089 |
|
| 2090 |
/* |
| 2091 |
** Add an SQL statement to the analysis. |
| 2092 |
*/ |
| 2093 |
int sqlite3_expert_sql( |
| 2094 |
sqlite3expert *p, /* From sqlite3_expert_new() */ |
| 2095 |
const char *zSql, /* SQL statement to add */ |
| 2096 |
char **pzErr /* OUT: Error message (if any) */ |
| 2097 |
){ |
| 2098 |
IdxScan *pScanOrig = p->pScan; |
| 2099 |
IdxStatement *pStmtOrig = p->pStatement; |
| 2100 |
int rc = SQLITE_OK; |
| 2101 |
const char *zStmt = zSql; |
| 2102 |
|
| 2103 |
if( p->bRun ) return SQLITE_MISUSE; |
| 2104 |
|
| 2105 |
while( rc==SQLITE_OK && zStmt && zStmt[0] ){ |
| 2106 |
sqlite3_stmt *pStmt = 0; |
| 2107 |
/* Ensure that the provided statement compiles against user's DB. */ |
| 2108 |
rc = idxPrepareStmt(p->db, &pStmt, pzErr, zStmt); |
| 2109 |
if( rc!=SQLITE_OK ) break; |
| 2110 |
sqlite3_finalize(pStmt); |
| 2111 |
rc = sqlite3_prepare_v2(p->dbv, zStmt, -1, &pStmt, &zStmt); |
| 2112 |
if( rc==SQLITE_OK ){ |
| 2113 |
if( pStmt ){ |
| 2114 |
IdxStatement *pNew; |
| 2115 |
const char *z = sqlite3_sql(pStmt); |
| 2116 |
i64 n = STRLEN(z); |
| 2117 |
pNew = (IdxStatement*)idxMalloc(&rc, sizeof(IdxStatement) + n+1); |
| 2118 |
if( rc==SQLITE_OK ){ |
| 2119 |
pNew->zSql = (char*)&pNew[1]; |
| 2120 |
memcpy(pNew->zSql, z, n+1); |
| 2121 |
pNew->pNext = p->pStatement; |
| 2122 |
if( p->pStatement ) pNew->iId = p->pStatement->iId+1; |
| 2123 |
p->pStatement = pNew; |
| 2124 |
} |
| 2125 |
sqlite3_finalize(pStmt); |
| 2126 |
} |
| 2127 |
}else{ |
| 2128 |
idxDatabaseError(p->dbv, pzErr); |
| 2129 |
} |
| 2130 |
} |
| 2131 |
|
| 2132 |
if( rc!=SQLITE_OK ){ |
| 2133 |
idxScanFree(p->pScan, pScanOrig); |
| 2134 |
idxStatementFree(p->pStatement, pStmtOrig); |
| 2135 |
p->pScan = pScanOrig; |
| 2136 |
p->pStatement = pStmtOrig; |
| 2137 |
} |
| 2138 |
|
| 2139 |
return rc; |
| 2140 |
} |
| 2141 |
|
| 2142 |
int sqlite3_expert_analyze(sqlite3expert *p, char **pzErr){ |
| 2143 |
int rc; |
| 2144 |
IdxHashEntry *pEntry; |
| 2145 |
|
| 2146 |
/* Do trigger processing to collect any extra IdxScan structures */ |
| 2147 |
rc = idxProcessTriggers(p, pzErr); |
| 2148 |
|
| 2149 |
/* Create candidate indexes within the in-memory database file */ |
| 2150 |
if( rc==SQLITE_OK ){ |
| 2151 |
rc = idxCreateCandidates(p); |
| 2152 |
}else if ( rc==SQLITE_BUSY_TIMEOUT ){ |
| 2153 |
if( pzErr ) |
| 2154 |
*pzErr = sqlite3_mprintf("Cannot find a unique index name to propose."); |
| 2155 |
return rc; |
| 2156 |
} |
| 2157 |
|
| 2158 |
/* Generate the stat1 data */ |
| 2159 |
if( rc==SQLITE_OK ){ |
| 2160 |
rc = idxPopulateStat1(p, pzErr); |
| 2161 |
} |
| 2162 |
|
| 2163 |
/* Formulate the EXPERT_REPORT_CANDIDATES text */ |
| 2164 |
for(pEntry=p->hIdx.pFirst; pEntry; pEntry=pEntry->pNext){ |
| 2165 |
p->zCandidates = idxAppendText(&rc, p->zCandidates, |
| 2166 |
"%s;%s%s\n", pEntry->zVal, |
| 2167 |
pEntry->zVal2 ? " -- stat1: " : "", pEntry->zVal2 |
| 2168 |
); |
| 2169 |
} |
| 2170 |
|
| 2171 |
/* Figure out which of the candidate indexes are preferred by the query |
| 2172 |
** planner and report the results to the user. */ |
| 2173 |
if( rc==SQLITE_OK ){ |
| 2174 |
rc = idxFindIndexes(p, pzErr); |
| 2175 |
} |
| 2176 |
|
| 2177 |
if( rc==SQLITE_OK ){ |
| 2178 |
p->bRun = 1; |
| 2179 |
} |
| 2180 |
return rc; |
| 2181 |
} |
| 2182 |
|
| 2183 |
/* |
| 2184 |
** Return the total number of statements that have been added to this |
| 2185 |
** sqlite3expert using sqlite3_expert_sql(). |
| 2186 |
*/ |
| 2187 |
int sqlite3_expert_count(sqlite3expert *p){ |
| 2188 |
int nRet = 0; |
| 2189 |
if( p->pStatement ) nRet = p->pStatement->iId+1; |
| 2190 |
return nRet; |
| 2191 |
} |
| 2192 |
|
| 2193 |
/* |
| 2194 |
** Return a component of the report. |
| 2195 |
*/ |
| 2196 |
const char *sqlite3_expert_report(sqlite3expert *p, int iStmt, int eReport){ |
| 2197 |
const char *zRet = 0; |
| 2198 |
IdxStatement *pStmt; |
| 2199 |
|
| 2200 |
if( p->bRun==0 ) return 0; |
| 2201 |
for(pStmt=p->pStatement; pStmt && pStmt->iId!=iStmt; pStmt=pStmt->pNext); |
| 2202 |
switch( eReport ){ |
| 2203 |
case EXPERT_REPORT_SQL: |
| 2204 |
if( pStmt ) zRet = pStmt->zSql; |
| 2205 |
break; |
| 2206 |
case EXPERT_REPORT_INDEXES: |
| 2207 |
if( pStmt ) zRet = pStmt->zIdx; |
| 2208 |
break; |
| 2209 |
case EXPERT_REPORT_PLAN: |
| 2210 |
if( pStmt ) zRet = pStmt->zEQP; |
| 2211 |
break; |
| 2212 |
case EXPERT_REPORT_CANDIDATES: |
| 2213 |
zRet = p->zCandidates; |
| 2214 |
break; |
| 2215 |
} |
| 2216 |
return zRet; |
| 2217 |
} |
| 2218 |
|
| 2219 |
/* |
| 2220 |
** Free an sqlite3expert object. |
| 2221 |
*/ |
| 2222 |
void sqlite3_expert_destroy(sqlite3expert *p){ |
| 2223 |
if( p ){ |
| 2224 |
sqlite3_close(p->dbm); |
| 2225 |
sqlite3_close(p->dbv); |
| 2226 |
idxScanFree(p->pScan, 0); |
| 2227 |
idxStatementFree(p->pStatement, 0); |
| 2228 |
idxTableFree(p->pTable); |
| 2229 |
idxWriteFree(p->pWrite); |
| 2230 |
idxHashClear(&p->hIdx); |
| 2231 |
sqlite3_free(p->zCandidates); |
| 2232 |
sqlite3_free(p); |
| 2233 |
} |
| 2234 |
} |
| 2235 |
|
| 2236 |
#endif /* ifndef SQLITE_OMIT_VIRTUALTABLE */ |