Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Refactor code to suggest indexes from the shell tool into an extension in ext/expert. Unfinished. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | schemalint |
| Files: | files | file ages | folders |
| SHA3-256: |
305e19f976ca064638a294e0817bf547 |
| User & Date: | dan 2017-04-07 20:14:22 |
Context
|
2017-04-08
| ||
| 17:41 | Use hash tables instead of in-memory database tables for a few purposes in sqlite3expert.c. check-in: bf10e68d user: dan tags: schemalint | |
|
2017-04-07
| ||
| 20:14 | Refactor code to suggest indexes from the shell tool into an extension in ext/expert. Unfinished. check-in: 305e19f9 user: dan tags: schemalint | |
|
2017-04-06
| ||
| 18:44 | Changes to allow indexes to be recommended for queries on SQL views. check-in: 0884ff1d user: dan tags: schemalint | |
Changes
Added ext/expert/expert.c.
1 +/* 2 +** 2017 April 07 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 + 14 + 15 +#include <sqlite3.h> 16 +#include <stdio.h> 17 +#include <stdlib.h> 18 +#include <string.h> 19 +#include "sqlite3expert.h" 20 + 21 + 22 +static void option_requires_argument(const char *zOpt){ 23 + fprintf(stderr, "Option requires an argument: %s\n", zOpt); 24 + exit(-3); 25 +} 26 + 27 +static void usage(char **argv){ 28 + fprintf(stderr, "\n"); 29 + fprintf(stderr, "Usage %s ?OPTIONS? DATABASE\n", argv[0]); 30 + fprintf(stderr, "\n"); 31 + fprintf(stderr, "Options are:\n"); 32 + fprintf(stderr, " -sql SQL (analyze SQL statements passed as argument)\n"); 33 + fprintf(stderr, " -file FILE (read SQL statements from file FILE)\n"); 34 + exit(-1); 35 +} 36 + 37 +static int readSqlFromFile(sqlite3expert *p, const char *zFile, char **pzErr){ 38 + return SQLITE_OK; 39 +} 40 + 41 +int main(int argc, char **argv){ 42 + const char *zDb; 43 + int rc = 0; 44 + char *zErr = 0; 45 + int i; 46 + 47 + sqlite3 *db = 0; 48 + sqlite3expert *p = 0; 49 + 50 + if( argc<2 ) usage(argv); 51 + zDb = argv[argc-1]; 52 + rc = sqlite3_open(zDb, &db); 53 + if( rc!=SQLITE_OK ){ 54 + fprintf(stderr, "Cannot open db file: %s - %s\n", zDb, sqlite3_errmsg(db)); 55 + exit(-2); 56 + } 57 + 58 + p = sqlite3_expert_new(db, &zErr); 59 + if( p==0 ){ 60 + fprintf(stderr, "Cannot run analysis: %s\n", zErr); 61 + rc = 1; 62 + }else{ 63 + for(i=1; i<(argc-1); i++){ 64 + char *zArg = argv[i]; 65 + int nArg = strlen(zArg); 66 + if( nArg>=2 && 0==sqlite3_strnicmp(zArg, "-file", nArg) ){ 67 + if( ++i==(argc-1) ) option_requires_argument("-file"); 68 + rc = readSqlFromFile(p, argv[i], &zErr); 69 + } 70 + 71 + else if( nArg>=2 && 0==sqlite3_strnicmp(zArg, "-sql", nArg) ){ 72 + if( ++i==(argc-1) ) option_requires_argument("-sql"); 73 + rc = sqlite3_expert_sql(p, argv[i], &zErr); 74 + } 75 + 76 + else{ 77 + usage(argv); 78 + } 79 + } 80 + } 81 + 82 + if( rc==SQLITE_OK ){ 83 + rc = sqlite3_expert_analyze(p, &zErr); 84 + } 85 + 86 + if( rc==SQLITE_OK ){ 87 + int nQuery = sqlite3_expert_count(p); 88 + for(i=0; i<nQuery; i++){ 89 + const char *zSql = sqlite3_expert_report(p, i, EXPERT_REPORT_SQL); 90 + const char *zIdx = sqlite3_expert_report(p, i, EXPERT_REPORT_INDEXES); 91 + const char *zEQP = sqlite3_expert_report(p, i, EXPERT_REPORT_PLAN); 92 + fprintf(stdout, "-- query %d ----------------------------------\n", i+1); 93 + fprintf(stdout, "%s\n\n%s\n%s\n", zSql, zIdx, zEQP); 94 + } 95 + }else if( zErr ){ 96 + fprintf(stderr, "Error: %s\n", zErr); 97 + } 98 + 99 + sqlite3_expert_destroy(p); 100 + sqlite3_free(zErr); 101 + return rc; 102 +} 103 + 104 +
Added ext/expert/sqlite3expert.c.
1 +/* 2 +** 2016 February 10 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 + 14 +#include "sqlite3expert.h" 15 +#include <assert.h> 16 +#include <string.h> 17 +#include <stdio.h> 18 + 19 +typedef sqlite3_int64 i64; 20 +typedef sqlite3_uint64 u64; 21 + 22 +typedef struct IdxConstraint IdxConstraint; 23 +typedef struct IdxContext IdxContext; 24 +typedef struct IdxScan IdxScan; 25 +typedef struct IdxStatement IdxStatement; 26 +typedef struct IdxWhere IdxWhere; 27 + 28 +typedef struct IdxColumn IdxColumn; 29 +typedef struct IdxTable IdxTable; 30 + 31 +/* 32 +** A single constraint. Equivalent to either "col = ?" or "col < ?". 33 +** 34 +** pLink: 35 +** Used to temporarily link IdxConstraint objects into lists while 36 +** creating candidate indexes. 37 +*/ 38 +struct IdxConstraint { 39 + char *zColl; /* Collation sequence */ 40 + int bRange; /* True for range, false for eq */ 41 + int iCol; /* Constrained table column */ 42 + i64 depmask; /* Dependency mask */ 43 + int bFlag; /* Used by idxFindCompatible() */ 44 + int bDesc; /* True if ORDER BY <expr> DESC */ 45 + IdxConstraint *pNext; /* Next constraint in pEq or pRange list */ 46 + IdxConstraint *pLink; /* See above */ 47 +}; 48 + 49 +/* 50 +** A WHERE clause. Made up of IdxConstraint objects. Example WHERE clause: 51 +** 52 +** a=? AND b=? AND c=? AND d=? AND e>? AND f<? 53 +** 54 +** The above is decomposed into 6 AND connected clauses. The first four are 55 +** added to the IdxWhere.pEq linked list, the following two into 56 +** IdxWhere.pRange. 57 +** 58 +** IdxWhere.pEq and IdxWhere.pRange are simple linked lists of IdxConstraint 59 +** objects linked by the IdxConstraint.pNext field. 60 +*/ 61 +struct IdxWhere { 62 + IdxConstraint *pEq; /* List of == constraints */ 63 + IdxConstraint *pRange; /* List of < constraints */ 64 +}; 65 + 66 +/* 67 +** A single scan of a single table. 68 +*/ 69 +struct IdxScan { 70 + IdxTable *pTable; /* Table-info */ 71 + char *zTable; /* Name of table to scan */ 72 + int iDb; /* Database containing table zTable */ 73 + i64 covering; /* Mask of columns required for cov. index */ 74 + IdxConstraint *pOrder; /* ORDER BY columns */ 75 + IdxWhere where; /* WHERE Constraints */ 76 + IdxScan *pNextScan; /* Next IdxScan object for same query */ 77 +}; 78 + 79 +/* 80 +** Data regarding a database table. Extracted from "PRAGMA table_info" 81 +*/ 82 +struct IdxColumn { 83 + char *zName; 84 + char *zColl; 85 + int iPk; 86 +}; 87 +struct IdxTable { 88 + int nCol; 89 + IdxColumn *aCol; 90 +}; 91 + 92 +/* 93 +** Context object passed to idxWhereInfo() and other functions. 94 +*/ 95 +struct IdxContext { 96 + char **pzErrmsg; 97 + IdxWhere *pCurrent; /* Current where clause */ 98 + int rc; /* Error code (if error has occurred) */ 99 + IdxScan *pScan; /* List of scan objects */ 100 + sqlite3 *dbm; /* In-memory db for this analysis */ 101 + sqlite3 *db; /* User database under analysis */ 102 + sqlite3_stmt *pInsertMask; /* To write to aux.depmask */ 103 + i64 iIdxRowid; /* Rowid of first index created */ 104 +}; 105 + 106 +struct IdxStatement { 107 + int iId; /* Statement number */ 108 + char *zSql; /* SQL statement */ 109 + char *zIdx; /* Indexes */ 110 + char *zEQP; /* Plan */ 111 + IdxStatement *pNext; 112 +}; 113 + 114 +/* 115 +** sqlite3expert object. 116 +*/ 117 +struct sqlite3expert { 118 + sqlite3 *db; /* Users database */ 119 + sqlite3 *dbm; /* In-memory db for this analysis */ 120 + 121 + int bRun; /* True once analysis has run */ 122 + char **pzErrmsg; 123 + 124 + IdxScan *pScan; /* List of scan objects */ 125 + IdxStatement *pStatement; /* List of IdxStatement objects */ 126 + int rc; /* Error code from whereinfo hook */ 127 + i64 iIdxRowid; /* Rowid of first index created */ 128 +}; 129 + 130 + 131 +/* 132 +** Allocate and return nByte bytes of zeroed memory using sqlite3_malloc(). 133 +** If the allocation fails, set *pRc to SQLITE_NOMEM and return NULL. 134 +*/ 135 +static void *idxMalloc(int *pRc, int nByte){ 136 + void *pRet; 137 + assert( *pRc==SQLITE_OK ); 138 + assert( nByte>0 ); 139 + pRet = sqlite3_malloc(nByte); 140 + if( pRet ){ 141 + memset(pRet, 0, nByte); 142 + }else{ 143 + *pRc = SQLITE_NOMEM; 144 + } 145 + return pRet; 146 +} 147 + 148 +/* 149 +** Allocate and return a new IdxConstraint object. Set the IdxConstraint.zColl 150 +** variable to point to a copy of nul-terminated string zColl. 151 +*/ 152 +static IdxConstraint *idxNewConstraint(int *pRc, const char *zColl){ 153 + IdxConstraint *pNew; 154 + int nColl = strlen(zColl); 155 + 156 + assert( *pRc==SQLITE_OK ); 157 + pNew = (IdxConstraint*)idxMalloc(pRc, sizeof(IdxConstraint) * nColl + 1); 158 + if( pNew ){ 159 + pNew->zColl = (char*)&pNew[1]; 160 + memcpy(pNew->zColl, zColl, nColl+1); 161 + } 162 + return pNew; 163 +} 164 + 165 +/* 166 +** sqlite3_whereinfo_hook() callback. 167 +*/ 168 +static void idxWhereInfo( 169 + void *pCtx, /* Pointer to IdxContext structure */ 170 + int eOp, 171 + const char *zVal, 172 + int iVal, 173 + u64 mask 174 +){ 175 + sqlite3expert *p = (sqlite3expert*)pCtx; 176 + 177 +#if 0 178 + const char *zOp = 179 + eOp==SQLITE_WHEREINFO_TABLE ? "TABLE" : 180 + eOp==SQLITE_WHEREINFO_EQUALS ? "EQUALS" : 181 + eOp==SQLITE_WHEREINFO_RANGE ? "RANGE" : 182 + eOp==SQLITE_WHEREINFO_ORDERBY ? "ORDERBY" : 183 + "!error!"; 184 + printf("op=%s zVal=%s iVal=%d mask=%llx\n", zOp, zVal, iVal, mask); 185 +#endif 186 + 187 + if( p->rc==SQLITE_OK ){ 188 + assert( eOp==SQLITE_WHEREINFO_TABLE || p->pScan!=0 ); 189 + switch( eOp ){ 190 + case SQLITE_WHEREINFO_TABLE: { 191 + int nVal = strlen(zVal); 192 + IdxScan *pNew = (IdxScan*)idxMalloc(&p->rc, sizeof(IdxScan) + nVal + 1); 193 + if( !pNew ) return; 194 + pNew->zTable = (char*)&pNew[1]; 195 + memcpy(pNew->zTable, zVal, nVal+1); 196 + pNew->pNextScan = p->pScan; 197 + pNew->covering = mask; 198 + p->pScan = pNew; 199 + break; 200 + } 201 + 202 + case SQLITE_WHEREINFO_ORDERBY: { 203 + IdxConstraint *pNew = idxNewConstraint(&p->rc, zVal); 204 + if( pNew==0 ) return; 205 + pNew->iCol = iVal; 206 + pNew->bDesc = (int)mask; 207 + if( p->pScan->pOrder==0 ){ 208 + p->pScan->pOrder = pNew; 209 + }else{ 210 + IdxConstraint *pIter; 211 + for(pIter=p->pScan->pOrder; pIter->pNext; pIter=pIter->pNext); 212 + pIter->pNext = pNew; 213 + pIter->pLink = pNew; 214 + } 215 + break; 216 + } 217 + 218 + case SQLITE_WHEREINFO_EQUALS: 219 + case SQLITE_WHEREINFO_RANGE: { 220 + IdxConstraint *pNew = idxNewConstraint(&p->rc, zVal); 221 + if( pNew==0 ) return; 222 + pNew->iCol = iVal; 223 + pNew->depmask = mask; 224 + 225 + if( eOp==SQLITE_WHEREINFO_RANGE ){ 226 + pNew->pNext = p->pScan->where.pRange; 227 + p->pScan->where.pRange = pNew; 228 + }else{ 229 + pNew->pNext = p->pScan->where.pEq; 230 + p->pScan->where.pEq = pNew; 231 + } 232 +#if 0 233 + sqlite3_bind_int64(p->pInsertMask, 1, mask); 234 + sqlite3_step(p->pInsertMask); 235 + p->rc = sqlite3_reset(p->pInsertMask); 236 +#endif 237 + break; 238 + } 239 + } 240 + } 241 +} 242 + 243 +/* 244 +** An error associated with database handle db has just occurred. Pass 245 +** the error message to callback function xOut. 246 +*/ 247 +static void idxDatabaseError( 248 + sqlite3 *db, /* Database handle */ 249 + char **pzErrmsg /* Write error here */ 250 +){ 251 + *pzErrmsg = sqlite3_mprintf("%s", sqlite3_errmsg(db)); 252 +} 253 + 254 +static int idxPrepareStmt( 255 + sqlite3 *db, /* Database handle to compile against */ 256 + sqlite3_stmt **ppStmt, /* OUT: Compiled SQL statement */ 257 + char **pzErrmsg, /* OUT: sqlite3_malloc()ed error message */ 258 + const char *zSql /* SQL statement to compile */ 259 +){ 260 + int rc = sqlite3_prepare_v2(db, zSql, -1, ppStmt, 0); 261 + if( rc!=SQLITE_OK ){ 262 + *ppStmt = 0; 263 + idxDatabaseError(db, pzErrmsg); 264 + } 265 + return rc; 266 +} 267 + 268 +static int idxPrintfPrepareStmt( 269 + sqlite3 *db, /* Database handle to compile against */ 270 + sqlite3_stmt **ppStmt, /* OUT: Compiled SQL statement */ 271 + char **pzErrmsg, /* OUT: sqlite3_malloc()ed error message */ 272 + const char *zFmt, /* printf() format of SQL statement */ 273 + ... /* Trailing printf() arguments */ 274 +){ 275 + va_list ap; 276 + int rc; 277 + char *zSql; 278 + va_start(ap, zFmt); 279 + zSql = sqlite3_vmprintf(zFmt, ap); 280 + if( zSql==0 ){ 281 + rc = SQLITE_NOMEM; 282 + }else{ 283 + rc = idxPrepareStmt(db, ppStmt, pzErrmsg, zSql); 284 + sqlite3_free(zSql); 285 + } 286 + va_end(ap); 287 + return rc; 288 +} 289 + 290 +static int idxGetTableInfo( 291 + sqlite3 *db, 292 + IdxScan *pScan, 293 + char **pzErrmsg 294 +){ 295 + const char *zTbl = pScan->zTable; 296 + sqlite3_stmt *p1 = 0; 297 + int nCol = 0; 298 + int nByte = sizeof(IdxTable); 299 + IdxTable *pNew = 0; 300 + int rc, rc2; 301 + char *pCsr; 302 + 303 + rc = idxPrintfPrepareStmt(db, &p1, pzErrmsg, "PRAGMA table_info=%Q", zTbl); 304 + while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(p1) ){ 305 + const char *zCol = (const char*)sqlite3_column_text(p1, 1); 306 + nByte += 1 + strlen(zCol); 307 + rc = sqlite3_table_column_metadata( 308 + db, "main", zTbl, zCol, 0, &zCol, 0, 0, 0 309 + ); 310 + nByte += 1 + strlen(zCol); 311 + nCol++; 312 + } 313 + rc2 = sqlite3_reset(p1); 314 + if( rc==SQLITE_OK ) rc = rc2; 315 + 316 + nByte += sizeof(IdxColumn) * nCol; 317 + if( rc==SQLITE_OK ){ 318 + pNew = idxMalloc(&rc, nByte); 319 + } 320 + if( rc==SQLITE_OK ){ 321 + pNew->aCol = (IdxColumn*)&pNew[1]; 322 + pNew->nCol = nCol; 323 + pCsr = (char*)&pNew->aCol[nCol]; 324 + } 325 + 326 + nCol = 0; 327 + while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(p1) ){ 328 + const char *zCol = (const char*)sqlite3_column_text(p1, 1); 329 + int nCopy = strlen(zCol) + 1; 330 + pNew->aCol[nCol].zName = pCsr; 331 + pNew->aCol[nCol].iPk = sqlite3_column_int(p1, 5); 332 + memcpy(pCsr, zCol, nCopy); 333 + pCsr += nCopy; 334 + 335 + rc = sqlite3_table_column_metadata( 336 + db, "main", zTbl, zCol, 0, &zCol, 0, 0, 0 337 + ); 338 + if( rc==SQLITE_OK ){ 339 + nCopy = strlen(zCol) + 1; 340 + pNew->aCol[nCol].zColl = pCsr; 341 + memcpy(pCsr, zCol, nCopy); 342 + pCsr += nCopy; 343 + } 344 + 345 + nCol++; 346 + } 347 + rc2 = sqlite3_finalize(p1); 348 + if( rc==SQLITE_OK ) rc = rc2; 349 + 350 + if( rc==SQLITE_OK ){ 351 + pScan->pTable = pNew; 352 + }else{ 353 + sqlite3_free(pNew); 354 + } 355 + 356 + return rc; 357 +} 358 + 359 +/* 360 +** This function is a no-op if *pRc is set to anything other than 361 +** SQLITE_OK when it is called. 362 +** 363 +** If *pRc is initially set to SQLITE_OK, then the text specified by 364 +** the printf() style arguments is appended to zIn and the result returned 365 +** in a buffer allocated by sqlite3_malloc(). sqlite3_free() is called on 366 +** zIn before returning. 367 +*/ 368 +static char *idxAppendText(int *pRc, char *zIn, const char *zFmt, ...){ 369 + va_list ap; 370 + char *zAppend = 0; 371 + char *zRet = 0; 372 + int nIn = zIn ? strlen(zIn) : 0; 373 + int nAppend = 0; 374 + va_start(ap, zFmt); 375 + if( *pRc==SQLITE_OK ){ 376 + zAppend = sqlite3_vmprintf(zFmt, ap); 377 + if( zAppend ){ 378 + nAppend = strlen(zAppend); 379 + zRet = (char*)sqlite3_malloc(nIn + nAppend + 1); 380 + } 381 + if( zAppend && zRet ){ 382 + memcpy(zRet, zIn, nIn); 383 + memcpy(&zRet[nIn], zAppend, nAppend+1); 384 + }else{ 385 + sqlite3_free(zRet); 386 + zRet = 0; 387 + *pRc = SQLITE_NOMEM; 388 + } 389 + sqlite3_free(zAppend); 390 + sqlite3_free(zIn); 391 + } 392 + va_end(ap); 393 + return zRet; 394 +} 395 + 396 +static int idxIdentifierRequiresQuotes(const char *zId){ 397 + int i; 398 + for(i=0; zId[i]; i++){ 399 + if( !(zId[i]=='_') 400 + && !(zId[i]>='0' && zId[i]<='9') 401 + && !(zId[i]>='a' && zId[i]<='z') 402 + && !(zId[i]>='A' && zId[i]<='Z') 403 + ){ 404 + return 1; 405 + } 406 + } 407 + return 0; 408 +} 409 + 410 +static char *idxAppendColDefn( 411 + int *pRc, 412 + char *zIn, 413 + IdxTable *pTab, 414 + IdxConstraint *pCons 415 +){ 416 + char *zRet = zIn; 417 + IdxColumn *p = &pTab->aCol[pCons->iCol]; 418 + if( zRet ) zRet = idxAppendText(pRc, zRet, ", "); 419 + 420 + if( idxIdentifierRequiresQuotes(p->zName) ){ 421 + zRet = idxAppendText(pRc, zRet, "%Q", p->zName); 422 + }else{ 423 + zRet = idxAppendText(pRc, zRet, "%s", p->zName); 424 + } 425 + 426 + if( sqlite3_stricmp(p->zColl, pCons->zColl) ){ 427 + if( idxIdentifierRequiresQuotes(pCons->zColl) ){ 428 + zRet = idxAppendText(pRc, zRet, " COLLATE %Q", pCons->zColl); 429 + }else{ 430 + zRet = idxAppendText(pRc, zRet, " COLLATE %s", pCons->zColl); 431 + } 432 + } 433 + 434 + if( pCons->bDesc ){ 435 + zRet = idxAppendText(pRc, zRet, " DESC"); 436 + } 437 + return zRet; 438 +} 439 + 440 +/* 441 +** Search database dbm for an index compatible with the one idxCreateFromCons() 442 +** would create from arguments pScan, pEq and pTail. If no error occurs and 443 +** such an index is found, return non-zero. Or, if no such index is found, 444 +** return zero. 445 +** 446 +** If an error occurs, set *pRc to an SQLite error code and return zero. 447 +*/ 448 +static int idxFindCompatible( 449 + int *pRc, /* OUT: Error code */ 450 + sqlite3* dbm, /* Database to search */ 451 + IdxScan *pScan, /* Scan for table to search for index on */ 452 + IdxConstraint *pEq, /* List of == constraints */ 453 + IdxConstraint *pTail /* List of range constraints */ 454 +){ 455 + const char *zTbl = pScan->zTable; 456 + sqlite3_stmt *pIdxList = 0; 457 + IdxConstraint *pIter; 458 + int nEq = 0; /* Number of elements in pEq */ 459 + int rc, rc2; 460 + 461 + /* Count the elements in list pEq */ 462 + for(pIter=pEq; pIter; pIter=pIter->pLink) nEq++; 463 + 464 + rc = idxPrintfPrepareStmt(dbm, &pIdxList, 0, "PRAGMA index_list=%Q", zTbl); 465 + while( rc==SQLITE_OK && sqlite3_step(pIdxList)==SQLITE_ROW ){ 466 + int bMatch = 1; 467 + IdxConstraint *pT = pTail; 468 + sqlite3_stmt *pInfo = 0; 469 + const char *zIdx = (const char*)sqlite3_column_text(pIdxList, 1); 470 + 471 + /* Zero the IdxConstraint.bFlag values in the pEq list */ 472 + for(pIter=pEq; pIter; pIter=pIter->pLink) pIter->bFlag = 0; 473 + 474 + rc = idxPrintfPrepareStmt(dbm, &pInfo, 0, "PRAGMA index_xInfo=%Q", zIdx); 475 + while( rc==SQLITE_OK && sqlite3_step(pInfo)==SQLITE_ROW ){ 476 + int iIdx = sqlite3_column_int(pInfo, 0); 477 + int iCol = sqlite3_column_int(pInfo, 1); 478 + const char *zColl = (const char*)sqlite3_column_text(pInfo, 4); 479 + 480 + if( iIdx<nEq ){ 481 + for(pIter=pEq; pIter; pIter=pIter->pLink){ 482 + if( pIter->bFlag ) continue; 483 + if( pIter->iCol!=iCol ) continue; 484 + if( sqlite3_stricmp(pIter->zColl, zColl) ) continue; 485 + pIter->bFlag = 1; 486 + break; 487 + } 488 + if( pIter==0 ){ 489 + bMatch = 0; 490 + break; 491 + } 492 + }else{ 493 + if( pT ){ 494 + if( pT->iCol!=iCol || sqlite3_stricmp(pT->zColl, zColl) ){ 495 + bMatch = 0; 496 + break; 497 + } 498 + pT = pT->pLink; 499 + } 500 + } 501 + } 502 + rc2 = sqlite3_finalize(pInfo); 503 + if( rc==SQLITE_OK ) rc = rc2; 504 + 505 + if( rc==SQLITE_OK && bMatch ){ 506 + sqlite3_finalize(pIdxList); 507 + return 1; 508 + } 509 + } 510 + rc2 = sqlite3_finalize(pIdxList); 511 + if( rc==SQLITE_OK ) rc = rc2; 512 + 513 + *pRc = rc; 514 + return 0; 515 +} 516 + 517 +static int idxCreateFromCons( 518 + sqlite3expert *p, 519 + IdxScan *pScan, 520 + IdxConstraint *pEq, 521 + IdxConstraint *pTail 522 +){ 523 + sqlite3 *dbm = p->dbm; 524 + int rc = SQLITE_OK; 525 + if( (pEq || pTail) && 0==idxFindCompatible(&rc, dbm, pScan, pEq, pTail) ){ 526 + IdxTable *pTab = pScan->pTable; 527 + char *zCols = 0; 528 + char *zIdx = 0; 529 + IdxConstraint *pCons; 530 + int h = 0; 531 + const char *zFmt; 532 + 533 + for(pCons=pEq; pCons; pCons=pCons->pLink){ 534 + zCols = idxAppendColDefn(&rc, zCols, pTab, pCons); 535 + } 536 + for(pCons=pTail; pCons; pCons=pCons->pLink){ 537 + zCols = idxAppendColDefn(&rc, zCols, pTab, pCons); 538 + } 539 + 540 + if( rc==SQLITE_OK ){ 541 + /* Hash the list of columns to come up with a name for the index */ 542 + int i; 543 + for(i=0; zCols[i]; i++){ 544 + h += ((h<<3) + zCols[i]); 545 + } 546 + 547 + if( idxIdentifierRequiresQuotes(pScan->zTable) ){ 548 + zFmt = "CREATE INDEX '%q_idx_%08x' ON %Q(%s)"; 549 + }else{ 550 + zFmt = "CREATE INDEX %s_idx_%08x ON %s(%s)"; 551 + } 552 + zIdx = sqlite3_mprintf(zFmt, pScan->zTable, h, pScan->zTable, zCols); 553 + if( !zIdx ){ 554 + rc = SQLITE_NOMEM; 555 + }else{ 556 + rc = sqlite3_exec(dbm, zIdx, 0, 0, p->pzErrmsg); 557 +#if 1 558 + printf("CANDIDATE: %s\n", zIdx); 559 +#endif 560 + } 561 + } 562 + if( rc==SQLITE_OK && p->iIdxRowid==0 ){ 563 + int rc2; 564 + sqlite3_stmt *pLast = 0; 565 + rc = idxPrepareStmt(dbm, &pLast, p->pzErrmsg, 566 + "SELECT max(rowid) FROM sqlite_master" 567 + ); 568 + if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pLast) ){ 569 + p->iIdxRowid = sqlite3_column_int64(pLast, 0); 570 + } 571 + rc2 = sqlite3_finalize(pLast); 572 + if( rc==SQLITE_OK ) rc = rc2; 573 + } 574 + 575 + sqlite3_free(zIdx); 576 + sqlite3_free(zCols); 577 + } 578 + return rc; 579 +} 580 + 581 +static int idxCreateFromWhere( 582 + sqlite3expert*, i64, IdxScan*, IdxWhere*, IdxConstraint*, IdxConstraint* 583 +); 584 + 585 +/* 586 +** Return true if list pList (linked by IdxConstraint.pLink) contains 587 +** a constraint compatible with *p. Otherwise return false. 588 +*/ 589 +static int idxFindConstraint(IdxConstraint *pList, IdxConstraint *p){ 590 + IdxConstraint *pCmp; 591 + for(pCmp=pList; pCmp; pCmp=pCmp->pLink){ 592 + if( p->iCol==pCmp->iCol ) return 1; 593 + } 594 + return 0; 595 +} 596 + 597 +static int idxCreateFromWhere( 598 + sqlite3expert *p, 599 + i64 mask, /* Consider only these constraints */ 600 + IdxScan *pScan, /* Create indexes for this scan */ 601 + IdxWhere *pWhere, /* Read constraints from here */ 602 + IdxConstraint *pEq, /* == constraints for inclusion */ 603 + IdxConstraint *pTail /* range/ORDER BY constraints for inclusion */ 604 +){ 605 + IdxConstraint *p1 = pEq; 606 + IdxConstraint *pCon; 607 + int rc; 608 + 609 + /* Gather up all the == constraints that match the mask. */ 610 + for(pCon=pWhere->pEq; pCon; pCon=pCon->pNext){ 611 + if( (mask & pCon->depmask)==pCon->depmask 612 + && idxFindConstraint(p1, pCon)==0 613 + && idxFindConstraint(pTail, pCon)==0 614 + ){ 615 + pCon->pLink = p1; 616 + p1 = pCon; 617 + } 618 + } 619 + 620 + /* Create an index using the == constraints collected above. And the 621 + ** range constraint/ORDER BY terms passed in by the caller, if any. */ 622 + rc = idxCreateFromCons(p, pScan, p1, pTail); 623 + 624 + /* If no range/ORDER BY passed by the caller, create a version of the 625 + ** index for each range constraint that matches the mask. */ 626 + if( pTail==0 ){ 627 + for(pCon=pWhere->pRange; rc==SQLITE_OK && pCon; pCon=pCon->pNext){ 628 + assert( pCon->pLink==0 ); 629 + if( (mask & pCon->depmask)==pCon->depmask 630 + && idxFindConstraint(pEq, pCon)==0 631 + && idxFindConstraint(pTail, pCon)==0 632 + ){ 633 + rc = idxCreateFromCons(p, pScan, p1, pCon); 634 + } 635 + } 636 + } 637 + 638 + return rc; 639 +} 640 + 641 +/* 642 +** Create candidate indexes in database [dbm] based on the data in 643 +** linked-list pScan. 644 +*/ 645 +static int idxCreateCandidates(sqlite3expert *p, char **pzErr){ 646 + sqlite3 *dbm = p->dbm; 647 + int rc2; 648 + int rc = SQLITE_OK; 649 + sqlite3_stmt *pDepmask = 0; /* Foreach depmask */ 650 + sqlite3_stmt *pInsert = 0; /* insert */ 651 + IdxScan *pIter; 652 + 653 + rc = idxPrepareStmt(dbm, &pInsert, pzErr, 654 + "INSERT OR IGNORE INTO aux.depmask SELECT mask | ?1 FROM aux.depmask;" 655 + ); 656 + if( rc==SQLITE_OK ){ 657 + rc = idxPrepareStmt(dbm, &pDepmask, pzErr, "SELECT mask FROM depmask"); 658 + } 659 + 660 + for(pIter=p->pScan; pIter && rc==SQLITE_OK; pIter=pIter->pNextScan){ 661 + IdxWhere *pWhere = &pIter->where; 662 + IdxConstraint *pCons; 663 + rc = sqlite3_exec(dbm, 664 + "DELETE FROM aux.depmask;" 665 + "INSERT INTO aux.depmask VALUES(0);" 666 + , 0, 0, pzErr 667 + ); 668 + for(pCons=pIter->where.pEq; pCons; pCons=pCons->pNext){ 669 + sqlite3_bind_int64(pInsert, 1, pCons->depmask); 670 + sqlite3_step(pInsert); 671 + rc = sqlite3_reset(pInsert); 672 + } 673 + 674 + while( SQLITE_ROW==sqlite3_step(pDepmask) && rc==SQLITE_OK ){ 675 + i64 mask = sqlite3_column_int64(pDepmask, 0); 676 + rc = idxCreateFromWhere(p, mask, pIter, pWhere, 0, 0); 677 + if( rc==SQLITE_OK && pIter->pOrder ){ 678 + rc = idxCreateFromWhere(p, mask, pIter, pWhere, 0, pIter->pOrder); 679 + } 680 + } 681 + rc2 = sqlite3_reset(pDepmask); 682 + if( rc==SQLITE_OK ) rc = rc2; 683 + } 684 + 685 + rc2 = sqlite3_finalize(pDepmask); 686 + if( rc==SQLITE_OK ) rc = rc2; 687 + rc2 = sqlite3_finalize(pInsert); 688 + if( rc==SQLITE_OK ) rc = rc2; 689 + return rc; 690 +} 691 + 692 +/* 693 +** Free all elements of the linked list starting from pScan up until pLast 694 +** (pLast is not freed). 695 +*/ 696 +static void idxScanFree(IdxScan *pScan, IdxScan *pLast){ 697 + /* TODO! */ 698 +} 699 + 700 +/* 701 +** Free all elements of the linked list starting from pStatement up 702 +** until pLast (pLast is not freed). 703 +*/ 704 +static void idxStatementFree(IdxStatement *pStatement, IdxStatement *pLast){ 705 + /* TODO! */ 706 +} 707 + 708 +static void idxFinalize(int *pRc, sqlite3_stmt *pStmt){ 709 + int rc = sqlite3_finalize(pStmt); 710 + if( *pRc==SQLITE_OK ) *pRc = rc; 711 +} 712 +static void idxReset(int *pRc, sqlite3_stmt *pStmt){ 713 + int rc = sqlite3_reset(pStmt); 714 + if( *pRc==SQLITE_OK ) *pRc = rc; 715 +} 716 + 717 + 718 +int idxFindIndexes( 719 + sqlite3expert *p, 720 + char **pzErr /* OUT: Error message (sqlite3_malloc) */ 721 +){ 722 + IdxStatement *pStmt; 723 + sqlite3 *dbm = p->dbm; 724 + sqlite3_stmt *pSelect = 0; 725 + sqlite3_stmt *pInsert = 0; 726 + int rc, rc2; 727 + int bFound = 0; 728 + 729 + if( rc==SQLITE_OK ){ 730 + rc = idxPrepareStmt(dbm, &pSelect, pzErr, 731 + "SELECT rowid, sql FROM sqlite_master WHERE name = ?" 732 + ); 733 + } 734 + if( rc==SQLITE_OK ){ 735 + rc = idxPrepareStmt(dbm, &pInsert, pzErr, 736 + "INSERT OR IGNORE INTO aux.indexes VALUES(?)" 737 + ); 738 + } 739 + 740 + for(pStmt=p->pStatement; rc==SQLITE_OK && pStmt; pStmt=pStmt->pNext){ 741 + sqlite3_stmt *pExplain = 0; 742 + rc = sqlite3_exec(dbm, "DELETE FROM aux.indexes", 0, 0, 0); 743 + if( rc==SQLITE_OK ){ 744 + rc = idxPrintfPrepareStmt(dbm, &pExplain, pzErr, 745 + "EXPLAIN QUERY PLAN %s", pStmt->zSql 746 + ); 747 + } 748 + while( rc==SQLITE_OK && sqlite3_step(pExplain)==SQLITE_ROW ){ 749 + int i; 750 + const char *zDetail = (const char*)sqlite3_column_text(pExplain, 3); 751 + int nDetail = strlen(zDetail); 752 + 753 + for(i=0; i<nDetail; i++){ 754 + const char *zIdx = 0; 755 + if( memcmp(&zDetail[i], " USING INDEX ", 13)==0 ){ 756 + zIdx = &zDetail[i+13]; 757 + }else if( memcmp(&zDetail[i], " USING COVERING INDEX ", 22)==0 ){ 758 + zIdx = &zDetail[i+22]; 759 + } 760 + if( zIdx ){ 761 + int nIdx = 0; 762 + while( zIdx[nIdx]!='\0' && (zIdx[nIdx]!=' ' || zIdx[nIdx+1]!='(') ){ 763 + nIdx++; 764 + } 765 + sqlite3_bind_text(pSelect, 1, zIdx, nIdx, SQLITE_STATIC); 766 + if( SQLITE_ROW==sqlite3_step(pSelect) ){ 767 + i64 iRowid = sqlite3_column_int64(pSelect, 0); 768 + const char *zSql = (const char*)sqlite3_column_text(pSelect, 1); 769 + if( iRowid>=p->iIdxRowid ){ 770 + sqlite3_bind_text(pInsert, 1, zSql, -1, SQLITE_STATIC); 771 + sqlite3_step(pInsert); 772 + rc = sqlite3_reset(pInsert); 773 + if( rc ) goto find_indexes_out; 774 + } 775 + } 776 + rc = sqlite3_reset(pSelect); 777 + break; 778 + } 779 + } 780 + } 781 + idxReset(&rc, pExplain); 782 + if( rc==SQLITE_OK ){ 783 + sqlite3_stmt *pLoop = 0; 784 + rc = idxPrepareStmt(dbm,&pLoop,pzErr,"SELECT name||';' FROM aux.indexes"); 785 + if( rc==SQLITE_OK ){ 786 + while( SQLITE_ROW==sqlite3_step(pLoop) ){ 787 + bFound = 1; 788 + pStmt->zIdx = idxAppendText(&rc, pStmt->zIdx, "%s\n", 789 + (const char*)sqlite3_column_text(pLoop, 0) 790 + ); 791 + } 792 + idxFinalize(&rc, pLoop); 793 + } 794 + if( bFound==0 ){ 795 + pStmt->zIdx = idxAppendText(&rc, pStmt->zIdx, "(no new indexes)\n"); 796 + } 797 + } 798 + 799 + while( rc==SQLITE_OK && sqlite3_step(pExplain)==SQLITE_ROW ){ 800 + int iSelectid = sqlite3_column_int(pExplain, 0); 801 + int iOrder = sqlite3_column_int(pExplain, 1); 802 + int iFrom = sqlite3_column_int(pExplain, 2); 803 + const char *zDetail = (const char*)sqlite3_column_text(pExplain, 3); 804 + 805 + pStmt->zEQP = idxAppendText(&rc, pStmt->zEQP, "%d|%d|%d|%s\n", 806 + iSelectid, iOrder, iFrom, zDetail 807 + ); 808 + } 809 + 810 + rc2 = sqlite3_finalize(pExplain); 811 + if( rc==SQLITE_OK ) rc = rc2; 812 + } 813 + 814 + find_indexes_out: 815 + rc2 = sqlite3_finalize(pSelect); 816 + if( rc==SQLITE_OK ) rc = rc2; 817 + rc2 = sqlite3_finalize(pInsert); 818 + if( rc==SQLITE_OK ) rc = rc2; 819 + 820 + return rc; 821 +} 822 + 823 +/* 824 +** The xOut callback is invoked to return command output to the user. The 825 +** second argument is always a nul-terminated string. The first argument is 826 +** passed zero if the string contains normal output or non-zero if it is an 827 +** error message. 828 +*/ 829 +int shellIndexesCommand( 830 + sqlite3 *db, /* Database handle */ 831 + const char *zSql, /* SQL to find indexes for */ 832 + void (*xOut)(void*, const char*), /* Output callback */ 833 + void *pOutCtx, /* Context for xOut() */ 834 + char **pzErrmsg /* OUT: Error message (sqlite3_malloc) */ 835 +){ 836 + int rc = SQLITE_OK; 837 +#if 0 838 + sqlite3 *dbm = 0; 839 + IdxContext ctx; 840 + sqlite3_stmt *pStmt = 0; /* Statement compiled from zSql */ 841 + 842 + memset(&ctx, 0, sizeof(IdxContext)); 843 + ctx.pzErrmsg = pzErrmsg; 844 + 845 + /* Open an in-memory database to work with. The main in-memory 846 + ** database schema contains tables similar to those in the users 847 + ** database (handle db). The attached in-memory db (aux) contains 848 + ** application tables used by the code in this file. */ 849 + rc = sqlite3_open(":memory:", &dbm); 850 + if( rc==SQLITE_OK ){ 851 + rc = sqlite3_exec(dbm, 852 + "ATTACH ':memory:' AS aux;" 853 + "CREATE TABLE aux.depmask(mask PRIMARY KEY) WITHOUT ROWID;" 854 + "CREATE TABLE aux.indexes(name PRIMARY KEY) WITHOUT ROWID;" 855 + "INSERT INTO aux.depmask VALUES(0);" 856 + , 0, 0, pzErrmsg 857 + ); 858 + } 859 + 860 + /* Prepare an INSERT statement for writing to aux.depmask */ 861 + if( rc==SQLITE_OK ){ 862 + rc = idxPrepareStmt(dbm, &ctx.pInsertMask, pzErrmsg, 863 + "INSERT OR IGNORE INTO aux.depmask SELECT mask | ?1 FROM aux.depmask;" 864 + ); 865 + } 866 + 867 + /* Analyze the SELECT statement in zSql. */ 868 + if( rc==SQLITE_OK ){ 869 + ctx.dbm = dbm; 870 + sqlite3_whereinfo_hook(db, idxWhereInfo, (void*)&ctx); 871 + rc = idxPrepareStmt(db, &pStmt, pzErrmsg, zSql); 872 + sqlite3_whereinfo_hook(db, 0, 0); 873 + sqlite3_finalize(pStmt); 874 + } 875 + 876 + /* Create tables within the main in-memory database. These tables 877 + ** have the same names, columns and declared types as the tables in 878 + ** the user database. All constraints except for PRIMARY KEY are 879 + ** removed. */ 880 + if( rc==SQLITE_OK ){ 881 + rc = idxCreateTables(db, dbm, ctx.pScan, pzErrmsg); 882 + } 883 + 884 + /* Create candidate indexes within the in-memory database file */ 885 + if( rc==SQLITE_OK ){ 886 + rc = idxCreateCandidates(&ctx); 887 + } 888 + 889 + /* Figure out which of the candidate indexes are preferred by the query 890 + ** planner and report the results to the user. */ 891 + if( rc==SQLITE_OK ){ 892 + rc = idxFindIndexes(&ctx, zSql, xOut, pOutCtx, pzErrmsg); 893 + } 894 + 895 + idxScanFree(ctx.pScan, 0); 896 + sqlite3_finalize(ctx.pInsertMask); 897 + sqlite3_close(dbm); 898 +#endif 899 + return rc; 900 +} 901 + 902 +/*************************************************************************/ 903 + 904 +/* 905 +** Allocate a new sqlite3expert object. 906 +*/ 907 +sqlite3expert *sqlite3_expert_new(sqlite3 *db, char **pzErrmsg){ 908 + int rc = SQLITE_OK; 909 + sqlite3expert *pNew; 910 + 911 + pNew = (sqlite3expert*)idxMalloc(&rc, sizeof(sqlite3expert)); 912 + pNew->db = db; 913 + 914 + /* Open an in-memory database to work with. The main in-memory 915 + ** database schema contains tables similar to those in the users 916 + ** database (handle db). The attached in-memory db (aux) contains 917 + ** application tables used by the code in this file. */ 918 + rc = sqlite3_open(":memory:", &pNew->dbm); 919 + if( rc==SQLITE_OK ){ 920 + rc = sqlite3_exec(pNew->dbm, 921 + "ATTACH ':memory:' AS aux;" 922 + "CREATE TABLE aux.depmask(mask PRIMARY KEY) WITHOUT ROWID;" 923 + "CREATE TABLE aux.indexes(name PRIMARY KEY) WITHOUT ROWID;" 924 + , 0, 0, pzErrmsg 925 + ); 926 + } 927 + 928 + /* Copy the entire schema of database [db] into [dbm]. */ 929 + if( rc==SQLITE_OK ){ 930 + sqlite3_stmt *pSql; 931 + int rc2; 932 + rc = idxPrintfPrepareStmt(pNew->db, &pSql, pzErrmsg, 933 + "SELECT sql FROM sqlite_master WHERE name NOT LIKE 'sqlite_%%'" 934 + ); 935 + while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSql) ){ 936 + const char *zSql = (const char*)sqlite3_column_text(pSql, 0); 937 + rc = sqlite3_exec(pNew->dbm, zSql, 0, 0, pzErrmsg); 938 + } 939 + rc2 = sqlite3_finalize(pSql); 940 + if( rc==SQLITE_OK ) rc = rc2; 941 + } 942 + 943 + /* If an error has occurred, free the new object and reutrn NULL. Otherwise, 944 + ** return the new sqlite3expert handle. */ 945 + if( rc!=SQLITE_OK ){ 946 + sqlite3_expert_destroy(pNew); 947 + pNew = 0; 948 + } 949 + return pNew; 950 +} 951 + 952 +/* 953 +** Add an SQL statement to the analysis. 954 +*/ 955 +int sqlite3_expert_sql( 956 + sqlite3expert *p, /* From sqlite3_expert_new() */ 957 + const char *zSql, /* SQL statement to add */ 958 + char **pzErr /* OUT: Error message (if any) */ 959 +){ 960 + IdxScan *pScanOrig = p->pScan; 961 + IdxStatement *pStmtOrig = p->pStatement; 962 + int rc = SQLITE_OK; 963 + const char *zStmt = zSql; 964 + 965 + if( p->bRun ) return SQLITE_MISUSE; 966 + 967 + sqlite3_whereinfo_hook(p->db, idxWhereInfo, p); 968 + while( rc==SQLITE_OK && zStmt && zStmt[0] ){ 969 + sqlite3_stmt *pStmt = 0; 970 + rc = sqlite3_prepare_v2(p->db, zStmt, -1, &pStmt, &zStmt); 971 + if( rc==SQLITE_OK ){ 972 + if( pStmt ){ 973 + IdxStatement *pNew; 974 + const char *z = sqlite3_sql(pStmt); 975 + int n = strlen(z); 976 + pNew = (IdxStatement*)idxMalloc(&rc, sizeof(IdxStatement) + n+1); 977 + if( rc==SQLITE_OK ){ 978 + pNew->zSql = (char*)&pNew[1]; 979 + memcpy(pNew->zSql, z, n+1); 980 + pNew->pNext = p->pStatement; 981 + if( p->pStatement ) pNew->iId = p->pStatement->iId+1; 982 + p->pStatement = pNew; 983 + } 984 + sqlite3_finalize(pStmt); 985 + } 986 + }else{ 987 + idxDatabaseError(p->db, pzErr); 988 + } 989 + } 990 + sqlite3_whereinfo_hook(p->db, 0, 0); 991 + 992 + if( rc!=SQLITE_OK ){ 993 + idxScanFree(p->pScan, pScanOrig); 994 + idxStatementFree(p->pStatement, pStmtOrig); 995 + p->pScan = pScanOrig; 996 + p->pStatement = pStmtOrig; 997 + } 998 + 999 + return rc; 1000 +} 1001 + 1002 +int sqlite3_expert_analyze(sqlite3expert *p, char **pzErr){ 1003 + int rc = SQLITE_OK; 1004 + IdxScan *pIter; 1005 + 1006 + /* Load IdxTable objects */ 1007 + for(pIter=p->pScan; pIter && rc==SQLITE_OK; pIter=pIter->pNextScan){ 1008 + rc = idxGetTableInfo(p->dbm, pIter, pzErr); 1009 + } 1010 + 1011 + 1012 + /* Create candidate indexes within the in-memory database file */ 1013 + if( rc==SQLITE_OK ){ 1014 + rc = idxCreateCandidates(p, pzErr); 1015 + } 1016 + 1017 + /* Figure out which of the candidate indexes are preferred by the query 1018 + ** planner and report the results to the user. */ 1019 + if( rc==SQLITE_OK ){ 1020 + rc = idxFindIndexes(p, pzErr); 1021 + } 1022 + 1023 + if( rc==SQLITE_OK ){ 1024 + p->bRun = 1; 1025 + } 1026 + return rc; 1027 +} 1028 + 1029 +int sqlite3_expert_count(sqlite3expert *p){ 1030 + int nRet = 0; 1031 + if( p->pStatement ) nRet = p->pStatement->iId+1; 1032 + return nRet; 1033 +} 1034 + 1035 +const char *sqlite3_expert_report(sqlite3expert *p, int iStmt, int eReport){ 1036 + const char *zRet = 0; 1037 + IdxStatement *pStmt; 1038 + 1039 + if( p->bRun==0 ) return 0; 1040 + for(pStmt=p->pStatement; pStmt && pStmt->iId!=iStmt; pStmt=pStmt->pNext); 1041 + if( pStmt ){ 1042 + switch( eReport ){ 1043 + case EXPERT_REPORT_SQL: 1044 + zRet = pStmt->zSql; 1045 + break; 1046 + case EXPERT_REPORT_INDEXES: 1047 + zRet = pStmt->zIdx; 1048 + break; 1049 + case EXPERT_REPORT_PLAN: 1050 + zRet = pStmt->zEQP; 1051 + break; 1052 + } 1053 + } 1054 + return zRet; 1055 +} 1056 + 1057 +/* 1058 +** Free an sqlite3expert object. 1059 +*/ 1060 +void sqlite3_expert_destroy(sqlite3expert *p){ 1061 + sqlite3_close(p->dbm); 1062 + idxScanFree(p->pScan, 0); 1063 + idxStatementFree(p->pStatement, 0); 1064 + sqlite3_free(p); 1065 +} 1066 +
Added ext/expert/sqlite3expert.h.
1 +/* 2 +** 2017 April 07 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 + 14 + 15 +#include "sqlite3.h" 16 + 17 +typedef struct sqlite3expert sqlite3expert; 18 + 19 +/* 20 +** Create a new sqlite3expert object. 21 +*/ 22 +sqlite3expert *sqlite3_expert_new(sqlite3 *db, char **pzErr); 23 + 24 +/* 25 +** Add an SQL statement to the analysis. 26 +*/ 27 +int sqlite3_expert_sql( 28 + sqlite3expert *p, /* From sqlite3_expert_new() */ 29 + const char *zSql, /* SQL statement to add */ 30 + char **pzErr /* OUT: Error message (if any) */ 31 +); 32 + 33 +int sqlite3_expert_analyze(sqlite3expert *p, char **pzErr); 34 + 35 +/* 36 +** Return the total number of SQL queries loaded via sqlite3_expert_sql(). 37 +*/ 38 +int sqlite3_expert_count(sqlite3expert*); 39 + 40 +/* 41 +** Return a component of the report. 42 +*/ 43 +const char *sqlite3_expert_report(sqlite3expert*, int iStmt, int eReport); 44 + 45 +/* 46 +** Values for the third argument passed to sqlite3_expert_report(). 47 +*/ 48 +#define EXPERT_REPORT_SQL 1 49 +#define EXPERT_REPORT_INDEXES 2 50 +#define EXPERT_REPORT_PLAN 3 51 + 52 +/* 53 +** Free an (sqlite3expert*) handle allocated by sqlite3-expert_new(). 54 +*/ 55 +void sqlite3_expert_destroy(sqlite3expert*); 56 + 57 +
Changes to main.mk.
487 487 # 488 488 all: sqlite3.h libsqlite3.a sqlite3$(EXE) 489 489 490 490 libsqlite3.a: $(LIBOBJ) 491 491 $(AR) libsqlite3.a $(LIBOBJ) 492 492 $(RANLIB) libsqlite3.a 493 493 494 -sqlite3$(EXE): $(TOP)/src/shell.c libsqlite3.a sqlite3.h $(TOP)/src/shell_indexes.c 494 +sqlite3$(EXE): $(TOP)/src/shell.c libsqlite3.a sqlite3.h 495 495 $(TCCX) $(READLINE_FLAGS) -o sqlite3$(EXE) $(SHELL_OPT) \ 496 496 $(TOP)/src/shell.c libsqlite3.a $(LIBREADLINE) $(TLIBS) $(THREADLIB) 497 497 498 498 sqldiff$(EXE): $(TOP)/tool/sqldiff.c sqlite3.c sqlite3.h 499 499 $(TCCX) -o sqldiff$(EXE) -DSQLITE_THREADSAFE=0 \ 500 500 $(TOP)/tool/sqldiff.c sqlite3.c $(TLIBS) $(THREADLIB) 501 501 ................................................................................ 757 757 echo "static const char *zMainloop = " >> $@ 758 758 tclsh $(TOP)/tool/tostr.tcl $(TOP)/tool/spaceanal.tcl >> $@ 759 759 echo "; return zMainloop; }" >> $@ 760 760 761 761 sqlite3_analyzer$(EXE): sqlite3_analyzer.c 762 762 $(TCCX) $(TCL_FLAGS) sqlite3_analyzer.c -o $@ $(LIBTCL) $(THREADLIB) 763 763 764 +sqlite3_expert$(EXE): $(TOP)/ext/expert/sqlite3expert.h $(TOP)/ext/expert/sqlite3expert.c $(TOP)/ext/expert/expert.c sqlite3.c 765 + $(TCCX) -DSQLITE_ENABLE_WHEREINFO_HOOK $(TOP)/ext/expert/sqlite3expert.h $(TOP)/ext/expert/sqlite3expert.c $(TOP)/ext/expert/expert.c sqlite3.c -o sqlite3_expert $(THREADLIB) 766 + 764 767 sqlite3_schemalint.c: sqlite3.c $(TOP)/src/tclsqlite.c $(TOP)/tool/schemalint.tcl 765 768 echo "#define TCLSH 2" > $@ 766 769 echo "#define SQLITE_ENABLE_DBSTAT_VTAB 1" >> $@ 767 770 cat sqlite3.c $(TOP)/src/tclsqlite.c >> $@ 768 771 echo "static const char *tclsh_main_loop(void){" >> $@ 769 772 echo "static const char *zMainloop = " >> $@ 770 773 tclsh $(TOP)/tool/tostr.tcl $(TOP)/tool/schemalint.tcl >> $@ ................................................................................ 975 978 rm -f wordcount wordcount.exe 976 979 rm -f rbu rbu.exe 977 980 rm -f srcck1 srcck1.exe 978 981 rm -f sqlite3.c sqlite3-*.c fts?amal.c tclsqlite3.c 979 982 rm -f sqlite3rc.h 980 983 rm -f shell.c sqlite3ext.h 981 984 rm -f sqlite3_analyzer sqlite3_analyzer.exe sqlite3_analyzer.c 985 + rm -f sqlite3_expert sqlite3_expert.exe 982 986 rm -f sqlite-*-output.vsix 983 987 rm -f mptester mptester.exe 984 988 rm -f fuzzershell fuzzershell.exe 985 989 rm -f fuzzcheck fuzzcheck.exe 986 990 rm -f sqldiff sqldiff.exe 987 991 rm -f fts5.* fts5parse.*
Changes to src/shell.c.
162 162 _setmode(_fileno(file), _O_TEXT); 163 163 } 164 164 #else 165 165 # define setBinaryMode(X,Y) 166 166 # define setTextMode(X,Y) 167 167 #endif 168 168 169 -#include "shell_indexes.c" 170 169 171 170 /* True if the timer is enabled */ 172 171 static int enableTimer = 0; 173 172 174 173 /* Return the current wall-clock time */ 175 174 static sqlite3_int64 timeOfDay(void){ 176 175 static sqlite3_vfs *clockVfs = 0; ................................................................................ 1360 1359 ** instance of the following structure. 1361 1360 */ 1362 1361 typedef struct ShellState ShellState; 1363 1362 struct ShellState { 1364 1363 sqlite3 *db; /* The database */ 1365 1364 int autoExplain; /* Automatically turn on .explain mode */ 1366 1365 int autoEQP; /* Run EXPLAIN QUERY PLAN prior to seach SQL stmt */ 1367 - int bRecommend; /* Instead of sqlite3_exec(), recommend indexes */ 1368 1366 int statsOn; /* True to display memory stats before each finalize */ 1369 1367 int scanstatsOn; /* True to display scan stats before each finalize */ 1370 1368 int outCount; /* Revert to stdout when reaching zero */ 1371 1369 int cnt; /* Number of records displayed so far */ 1372 1370 FILE *out; /* Write results here */ 1373 1371 FILE *traceOut; /* Output for sqlite3_trace() */ 1374 1372 int nErr; /* Number of errors seen */ ................................................................................ 2582 2580 static void explain_data_delete(ShellState *p){ 2583 2581 sqlite3_free(p->aiIndent); 2584 2582 p->aiIndent = 0; 2585 2583 p->nIndent = 0; 2586 2584 p->iIndent = 0; 2587 2585 } 2588 2586 2589 -typedef struct RecCommandCtx RecCommandCtx; 2590 -struct RecCommandCtx { 2591 - int (*xCallback)(void*,int,char**,char**,int*); 2592 - ShellState *pArg; 2593 -}; 2594 - 2595 -static void recCommandOut(void *pCtx, const char *zLine){ 2596 - const char *zCol = "output"; 2597 - RecCommandCtx *p = (RecCommandCtx*)pCtx; 2598 - int t = SQLITE_TEXT; 2599 - p->xCallback(p->pArg, 1, (char**)&zLine, (char**)&zCol, &t); 2600 -} 2601 - 2602 2587 /* 2603 2588 ** Disable and restore .wheretrace and .selecttrace settings. 2604 2589 */ 2605 2590 #if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_SELECTTRACE) 2606 2591 extern int sqlite3SelectTrace; 2607 2592 static int savedSelectTrace; 2608 2593 #endif ................................................................................ 2719 2704 int rc2; 2720 2705 const char *zLeftover; /* Tail of unprocessed SQL */ 2721 2706 2722 2707 if( pzErrMsg ){ 2723 2708 *pzErrMsg = NULL; 2724 2709 } 2725 2710 2726 - if( pArg->bRecommend ){ 2727 - RecCommandCtx ctx; 2728 - ctx.xCallback = xCallback; 2729 - ctx.pArg = pArg; 2730 - rc = shellIndexesCommand(db, zSql, recCommandOut, &ctx, pzErrMsg); 2731 - }else 2732 - 2733 2711 while( zSql[0] && (SQLITE_OK == rc) ){ 2734 2712 static const char *zStmtSql; 2735 2713 rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, &zLeftover); 2736 2714 if( SQLITE_OK != rc ){ 2737 2715 if( pzErrMsg ){ 2738 2716 *pzErrMsg = save_err_msg(db); 2739 2717 } ................................................................................ 5518 5496 }else{ 5519 5497 utf8_printf(stderr, "Error: %s\n", sqlite3_errmsg(p->db)); 5520 5498 rc = 1; 5521 5499 } 5522 5500 sqlite3_close(pSrc); 5523 5501 }else 5524 5502 5525 - if( c=='r' && n>=2 && strncmp(azArg[0], "recommend", n)==0 ){ 5526 - if( nArg==2 ){ 5527 - p->bRecommend = booleanValue(azArg[1]); 5528 - }else{ 5529 - raw_printf(stderr, "Usage: .recommend on|off\n"); 5530 - rc = 1; 5531 - } 5532 - }else 5533 - 5534 5503 5535 5504 if( c=='s' && strncmp(azArg[0], "scanstats", n)==0 ){ 5536 5505 if( nArg==2 ){ 5537 5506 p->scanstatsOn = booleanValue(azArg[1]); 5538 5507 #ifndef SQLITE_ENABLE_STMT_SCANSTATUS 5539 5508 raw_printf(stderr, "Warning: .scanstats not available in this build.\n"); 5540 5509 #endif ................................................................................ 7333 7302 utf8_printf(stderr,"Error: %s\n", zErrMsg); 7334 7303 if( bail_on_error ) return rc!=0 ? rc : 1; 7335 7304 }else if( rc!=0 ){ 7336 7305 utf8_printf(stderr,"Error: unable to process SQL \"%s\"\n", z); 7337 7306 if( bail_on_error ) return rc; 7338 7307 } 7339 7308 } 7340 - 7341 - }else if( strcmp(z, "-recommend") ){ 7342 - data.bRecommend = 1; 7343 7309 }else{ 7344 7310 utf8_printf(stderr,"%s: Error: unknown option: %s\n", Argv0, z); 7345 7311 raw_printf(stderr,"Use -help for a list of options.\n"); 7346 7312 return 1; 7347 7313 } 7348 7314 data.cMode = data.mode; 7349 7315 }
Deleted src/shell_indexes.c.
1 -/* 2 -** 2016 February 10 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 - 14 -typedef sqlite3_int64 i64; 15 -typedef sqlite3_uint64 u64; 16 - 17 -typedef struct IdxConstraint IdxConstraint; 18 -typedef struct IdxContext IdxContext; 19 -typedef struct IdxScan IdxScan; 20 -typedef struct IdxWhere IdxWhere; 21 - 22 -typedef struct IdxColumn IdxColumn; 23 -typedef struct IdxTable IdxTable; 24 - 25 -/* 26 -** A single constraint. Equivalent to either "col = ?" or "col < ?". 27 -** 28 -** pLink: 29 -** Used to temporarily link IdxConstraint objects into lists while 30 -** creating candidate indexes. 31 -*/ 32 -struct IdxConstraint { 33 - char *zColl; /* Collation sequence */ 34 - int bRange; /* True for range, false for eq */ 35 - int iCol; /* Constrained table column */ 36 - i64 depmask; /* Dependency mask */ 37 - int bFlag; /* Used by idxFindCompatible() */ 38 - int bDesc; /* True if ORDER BY <expr> DESC */ 39 - IdxConstraint *pNext; /* Next constraint in pEq or pRange list */ 40 - IdxConstraint *pLink; /* See above */ 41 -}; 42 - 43 -/* 44 -** A WHERE clause. Made up of IdxConstraint objects. Example WHERE clause: 45 -** 46 -** a=? AND b=? AND c=? AND d=? AND e>? AND f<? 47 -** 48 -** The above is decomposed into 6 AND connected clauses. The first four are 49 -** added to the IdxWhere.pEq linked list, the following two into 50 -** IdxWhere.pRange. 51 -** 52 -** IdxWhere.pEq and IdxWhere.pRange are simple linked lists of IdxConstraint 53 -** objects linked by the IdxConstraint.pNext field. 54 -*/ 55 -struct IdxWhere { 56 - IdxConstraint *pEq; /* List of == constraints */ 57 - IdxConstraint *pRange; /* List of < constraints */ 58 -}; 59 - 60 -/* 61 -** A single scan of a single table. 62 -*/ 63 -struct IdxScan { 64 - IdxTable *pTable; /* Table-info */ 65 - char *zTable; /* Name of table to scan */ 66 - int iDb; /* Database containing table zTable */ 67 - i64 covering; /* Mask of columns required for cov. index */ 68 - IdxConstraint *pOrder; /* ORDER BY columns */ 69 - IdxWhere where; /* WHERE Constraints */ 70 - IdxScan *pNextScan; /* Next IdxScan object for same query */ 71 -}; 72 - 73 -/* 74 -** Context object passed to idxWhereInfo() and other functions. 75 -*/ 76 -struct IdxContext { 77 - char **pzErrmsg; 78 - IdxWhere *pCurrent; /* Current where clause */ 79 - int rc; /* Error code (if error has occurred) */ 80 - IdxScan *pScan; /* List of scan objects */ 81 - sqlite3 *dbm; /* In-memory db for this analysis */ 82 - sqlite3 *db; /* User database under analysis */ 83 - sqlite3_stmt *pInsertMask; /* To write to aux.depmask */ 84 - i64 iIdxRowid; /* Rowid of first index created */ 85 -}; 86 - 87 -/* 88 -** Data regarding a database table. Extracted from "PRAGMA table_info" 89 -*/ 90 -struct IdxColumn { 91 - char *zName; 92 - char *zColl; 93 - int iPk; 94 -}; 95 -struct IdxTable { 96 - int nCol; 97 - IdxColumn *aCol; 98 -}; 99 - 100 -/* 101 -** Allocate and return nByte bytes of zeroed memory using sqlite3_malloc(). 102 -** If the allocation fails, set *pRc to SQLITE_NOMEM and return NULL. 103 -*/ 104 -static void *idxMalloc(int *pRc, int nByte){ 105 - void *pRet; 106 - assert( *pRc==SQLITE_OK ); 107 - assert( nByte>0 ); 108 - pRet = sqlite3_malloc(nByte); 109 - if( pRet ){ 110 - memset(pRet, 0, nByte); 111 - }else{ 112 - *pRc = SQLITE_NOMEM; 113 - } 114 - return pRet; 115 -} 116 - 117 -/* 118 -** Allocate and return a new IdxConstraint object. Set the IdxConstraint.zColl 119 -** variable to point to a copy of nul-terminated string zColl. 120 -*/ 121 -static IdxConstraint *idxNewConstraint(int *pRc, const char *zColl){ 122 - IdxConstraint *pNew; 123 - int nColl = strlen(zColl); 124 - 125 - assert( *pRc==SQLITE_OK ); 126 - pNew = (IdxConstraint*)idxMalloc(pRc, sizeof(IdxConstraint) * nColl + 1); 127 - if( pNew ){ 128 - pNew->zColl = (char*)&pNew[1]; 129 - memcpy(pNew->zColl, zColl, nColl+1); 130 - } 131 - return pNew; 132 -} 133 - 134 -/* 135 -** SQLITE_DBCONFIG_WHEREINFO callback. 136 -*/ 137 -static void idxWhereInfo( 138 - void *pCtx, /* Pointer to IdxContext structure */ 139 - int eOp, 140 - const char *zVal, 141 - int iVal, 142 - u64 mask 143 -){ 144 - IdxContext *p = (IdxContext*)pCtx; 145 - 146 -#if 0 147 - const char *zOp = 148 - eOp==SQLITE_WHEREINFO_TABLE ? "TABLE" : 149 - eOp==SQLITE_WHEREINFO_EQUALS ? "EQUALS" : 150 - eOp==SQLITE_WHEREINFO_RANGE ? "RANGE" : 151 - eOp==SQLITE_WHEREINFO_ORDERBY ? "ORDERBY" : 152 - "!error!"; 153 - printf("op=%s zVal=%s iVal=%d mask=%llx\n", zOp, zVal, iVal, mask); 154 -#endif 155 - 156 - if( p->rc==SQLITE_OK ){ 157 - assert( eOp==SQLITE_WHEREINFO_TABLE || p->pScan!=0 ); 158 - switch( eOp ){ 159 - case SQLITE_WHEREINFO_TABLE: { 160 - int nVal = strlen(zVal); 161 - IdxScan *pNew = (IdxScan*)idxMalloc(&p->rc, sizeof(IdxScan) + nVal + 1); 162 - if( !pNew ) return; 163 - pNew->zTable = (char*)&pNew[1]; 164 - memcpy(pNew->zTable, zVal, nVal+1); 165 - pNew->pNextScan = p->pScan; 166 - pNew->covering = mask; 167 - p->pScan = pNew; 168 - p->pCurrent = &pNew->where; 169 - break; 170 - } 171 - 172 - case SQLITE_WHEREINFO_ORDERBY: { 173 - IdxConstraint *pNew = idxNewConstraint(&p->rc, zVal); 174 - if( pNew==0 ) return; 175 - pNew->iCol = iVal; 176 - pNew->bDesc = (int)mask; 177 - if( p->pScan->pOrder==0 ){ 178 - p->pScan->pOrder = pNew; 179 - }else{ 180 - IdxConstraint *pIter; 181 - for(pIter=p->pScan->pOrder; pIter->pNext; pIter=pIter->pNext); 182 - pIter->pNext = pNew; 183 - pIter->pLink = pNew; 184 - } 185 - break; 186 - } 187 - 188 - case SQLITE_WHEREINFO_EQUALS: 189 - case SQLITE_WHEREINFO_RANGE: { 190 - IdxConstraint *pNew = idxNewConstraint(&p->rc, zVal); 191 - if( pNew==0 ) return; 192 - pNew->iCol = iVal; 193 - pNew->depmask = mask; 194 - 195 - if( eOp==SQLITE_WHEREINFO_RANGE ){ 196 - pNew->pNext = p->pCurrent->pRange; 197 - p->pCurrent->pRange = pNew; 198 - }else{ 199 - pNew->pNext = p->pCurrent->pEq; 200 - p->pCurrent->pEq = pNew; 201 - } 202 - 203 - sqlite3_bind_int64(p->pInsertMask, 1, mask); 204 - sqlite3_step(p->pInsertMask); 205 - p->rc = sqlite3_reset(p->pInsertMask); 206 - break; 207 - } 208 - } 209 - } 210 -} 211 - 212 -/* 213 -** An error associated with database handle db has just occurred. Pass 214 -** the error message to callback function xOut. 215 -*/ 216 -static void idxDatabaseError( 217 - sqlite3 *db, /* Database handle */ 218 - char **pzErrmsg /* Write error here */ 219 -){ 220 - *pzErrmsg = sqlite3_mprintf("%s", sqlite3_errmsg(db)); 221 -} 222 - 223 -static int idxPrepareStmt( 224 - sqlite3 *db, /* Database handle to compile against */ 225 - sqlite3_stmt **ppStmt, /* OUT: Compiled SQL statement */ 226 - char **pzErrmsg, /* OUT: sqlite3_malloc()ed error message */ 227 - const char *zSql /* SQL statement to compile */ 228 -){ 229 - int rc = sqlite3_prepare_v2(db, zSql, -1, ppStmt, 0); 230 - if( rc!=SQLITE_OK ){ 231 - *ppStmt = 0; 232 - idxDatabaseError(db, pzErrmsg); 233 - } 234 - return rc; 235 -} 236 - 237 -static int idxPrintfPrepareStmt( 238 - sqlite3 *db, /* Database handle to compile against */ 239 - sqlite3_stmt **ppStmt, /* OUT: Compiled SQL statement */ 240 - char **pzErrmsg, /* OUT: sqlite3_malloc()ed error message */ 241 - const char *zFmt, /* printf() format of SQL statement */ 242 - ... /* Trailing printf() arguments */ 243 -){ 244 - va_list ap; 245 - int rc; 246 - char *zSql; 247 - va_start(ap, zFmt); 248 - zSql = sqlite3_vmprintf(zFmt, ap); 249 - if( zSql==0 ){ 250 - rc = SQLITE_NOMEM; 251 - }else{ 252 - rc = idxPrepareStmt(db, ppStmt, pzErrmsg, zSql); 253 - sqlite3_free(zSql); 254 - } 255 - va_end(ap); 256 - return rc; 257 -} 258 - 259 -static int idxGetTableInfo( 260 - sqlite3 *db, 261 - IdxScan *pScan, 262 - char **pzErrmsg 263 -){ 264 - const char *zTbl = pScan->zTable; 265 - sqlite3_stmt *p1 = 0; 266 - int nCol = 0; 267 - int nByte = sizeof(IdxTable); 268 - IdxTable *pNew = 0; 269 - int rc, rc2; 270 - char *pCsr; 271 - 272 - rc = idxPrintfPrepareStmt(db, &p1, pzErrmsg, "PRAGMA table_info=%Q", zTbl); 273 - while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(p1) ){ 274 - const char *zCol = (const char*)sqlite3_column_text(p1, 1); 275 - nByte += 1 + strlen(zCol); 276 - rc = sqlite3_table_column_metadata( 277 - db, "main", zTbl, zCol, 0, &zCol, 0, 0, 0 278 - ); 279 - nByte += 1 + strlen(zCol); 280 - nCol++; 281 - } 282 - rc2 = sqlite3_reset(p1); 283 - if( rc==SQLITE_OK ) rc = rc2; 284 - 285 - nByte += sizeof(IdxColumn) * nCol; 286 - if( rc==SQLITE_OK ){ 287 - pNew = idxMalloc(&rc, nByte); 288 - } 289 - if( rc==SQLITE_OK ){ 290 - pNew->aCol = (IdxColumn*)&pNew[1]; 291 - pNew->nCol = nCol; 292 - pCsr = (char*)&pNew->aCol[nCol]; 293 - } 294 - 295 - nCol = 0; 296 - while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(p1) ){ 297 - const char *zCol = (const char*)sqlite3_column_text(p1, 1); 298 - int nCopy = strlen(zCol) + 1; 299 - pNew->aCol[nCol].zName = pCsr; 300 - pNew->aCol[nCol].iPk = sqlite3_column_int(p1, 5); 301 - memcpy(pCsr, zCol, nCopy); 302 - pCsr += nCopy; 303 - 304 - rc = sqlite3_table_column_metadata( 305 - db, "main", zTbl, zCol, 0, &zCol, 0, 0, 0 306 - ); 307 - if( rc==SQLITE_OK ){ 308 - nCopy = strlen(zCol) + 1; 309 - pNew->aCol[nCol].zColl = pCsr; 310 - memcpy(pCsr, zCol, nCopy); 311 - pCsr += nCopy; 312 - } 313 - 314 - nCol++; 315 - } 316 - rc2 = sqlite3_finalize(p1); 317 - if( rc==SQLITE_OK ) rc = rc2; 318 - 319 - if( rc==SQLITE_OK ){ 320 - pScan->pTable = pNew; 321 - }else{ 322 - sqlite3_free(pNew); 323 - } 324 - 325 - return rc; 326 -} 327 - 328 -static int idxCreateTables( 329 - sqlite3 *db, /* User database */ 330 - sqlite3 *dbm, /* In-memory database to create tables in */ 331 - IdxScan *pScan, /* List of scans */ 332 - char **pzErrmsg /* OUT: Error message */ 333 -){ 334 - int rc = SQLITE_OK; 335 - int rc2; 336 - IdxScan *pIter; 337 - sqlite3_stmt *pSql = 0; 338 - 339 - /* Copy the entire schema of database [db] into [dbm]. */ 340 - rc = idxPrintfPrepareStmt(db, &pSql, pzErrmsg, 341 - "SELECT sql FROM sqlite_master WHERE name NOT LIKE 'sqlite_%%'" 342 - ); 343 - while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSql) ){ 344 - const char *zSql = (const char*)sqlite3_column_text(pSql, 0); 345 - rc = sqlite3_exec(dbm, zSql, 0, 0, pzErrmsg); 346 - } 347 - rc2 = sqlite3_finalize(pSql); 348 - if( rc==SQLITE_OK ) rc = rc2; 349 - 350 - /* Load IdxTable objects */ 351 - for(pIter=pScan; pIter && rc==SQLITE_OK; pIter=pIter->pNextScan){ 352 - rc = idxGetTableInfo(db, pIter, pzErrmsg); 353 - } 354 - return rc; 355 -} 356 - 357 -/* 358 -** This function is a no-op if *pRc is set to anything other than 359 -** SQLITE_OK when it is called. 360 -** 361 -** If *pRc is initially set to SQLITE_OK, then the text specified by 362 -** the printf() style arguments is appended to zIn and the result returned 363 -** in a buffer allocated by sqlite3_malloc(). sqlite3_free() is called on 364 -** zIn before returning. 365 -*/ 366 -static char *idxAppendText(int *pRc, char *zIn, const char *zFmt, ...){ 367 - va_list ap; 368 - char *zAppend = 0; 369 - char *zRet = 0; 370 - int nIn = zIn ? strlen(zIn) : 0; 371 - int nAppend = 0; 372 - va_start(ap, zFmt); 373 - if( *pRc==SQLITE_OK ){ 374 - zAppend = sqlite3_vmprintf(zFmt, ap); 375 - if( zAppend ){ 376 - nAppend = strlen(zAppend); 377 - zRet = (char*)sqlite3_malloc(nIn + nAppend + 1); 378 - } 379 - if( zAppend && zRet ){ 380 - memcpy(zRet, zIn, nIn); 381 - memcpy(&zRet[nIn], zAppend, nAppend+1); 382 - }else{ 383 - sqlite3_free(zRet); 384 - zRet = 0; 385 - *pRc = SQLITE_NOMEM; 386 - } 387 - sqlite3_free(zAppend); 388 - sqlite3_free(zIn); 389 - } 390 - va_end(ap); 391 - return zRet; 392 -} 393 - 394 -static int idxIdentifierRequiresQuotes(const char *zId){ 395 - int i; 396 - for(i=0; zId[i]; i++){ 397 - if( !(zId[i]=='_') 398 - && !(zId[i]>='0' && zId[i]<='9') 399 - && !(zId[i]>='a' && zId[i]<='z') 400 - && !(zId[i]>='A' && zId[i]<='Z') 401 - ){ 402 - return 1; 403 - } 404 - } 405 - return 0; 406 -} 407 - 408 -static char *idxAppendColDefn( 409 - int *pRc, 410 - char *zIn, 411 - IdxTable *pTab, 412 - IdxConstraint *pCons 413 -){ 414 - char *zRet = zIn; 415 - IdxColumn *p = &pTab->aCol[pCons->iCol]; 416 - if( zRet ) zRet = idxAppendText(pRc, zRet, ", "); 417 - 418 - if( idxIdentifierRequiresQuotes(p->zName) ){ 419 - zRet = idxAppendText(pRc, zRet, "%Q", p->zName); 420 - }else{ 421 - zRet = idxAppendText(pRc, zRet, "%s", p->zName); 422 - } 423 - 424 - if( sqlite3_stricmp(p->zColl, pCons->zColl) ){ 425 - if( idxIdentifierRequiresQuotes(pCons->zColl) ){ 426 - zRet = idxAppendText(pRc, zRet, " COLLATE %Q", pCons->zColl); 427 - }else{ 428 - zRet = idxAppendText(pRc, zRet, " COLLATE %s", pCons->zColl); 429 - } 430 - } 431 - 432 - if( pCons->bDesc ){ 433 - zRet = idxAppendText(pRc, zRet, " DESC"); 434 - } 435 - return zRet; 436 -} 437 - 438 -/* 439 -** Search database dbm for an index compatible with the one idxCreateFromCons() 440 -** would create from arguments pScan, pEq and pTail. If no error occurs and 441 -** such an index is found, return non-zero. Or, if no such index is found, 442 -** return zero. 443 -** 444 -** If an error occurs, set *pRc to an SQLite error code and return zero. 445 -*/ 446 -static int idxFindCompatible( 447 - int *pRc, /* OUT: Error code */ 448 - sqlite3* dbm, /* Database to search */ 449 - IdxScan *pScan, /* Scan for table to search for index on */ 450 - IdxConstraint *pEq, /* List of == constraints */ 451 - IdxConstraint *pTail /* List of range constraints */ 452 -){ 453 - const char *zTbl = pScan->zTable; 454 - sqlite3_stmt *pIdxList = 0; 455 - IdxConstraint *pIter; 456 - int nEq = 0; /* Number of elements in pEq */ 457 - int rc, rc2; 458 - 459 - 460 - /* Count the elements in list pEq */ 461 - for(pIter=pEq; pIter; pIter=pIter->pLink) nEq++; 462 - 463 - rc = idxPrintfPrepareStmt(dbm, &pIdxList, 0, "PRAGMA index_list=%Q", zTbl); 464 - while( rc==SQLITE_OK && sqlite3_step(pIdxList)==SQLITE_ROW ){ 465 - int bMatch = 1; 466 - IdxConstraint *pT = pTail; 467 - sqlite3_stmt *pInfo = 0; 468 - const char *zIdx = (const char*)sqlite3_column_text(pIdxList, 1); 469 - 470 - /* Zero the IdxConstraint.bFlag values in the pEq list */ 471 - for(pIter=pEq; pIter; pIter=pIter->pLink) pIter->bFlag = 0; 472 - 473 - rc = idxPrintfPrepareStmt(dbm, &pInfo, 0, "PRAGMA index_xInfo=%Q", zIdx); 474 - while( rc==SQLITE_OK && sqlite3_step(pInfo)==SQLITE_ROW ){ 475 - int iIdx = sqlite3_column_int(pInfo, 0); 476 - int iCol = sqlite3_column_int(pInfo, 1); 477 - const char *zColl = (const char*)sqlite3_column_text(pInfo, 4); 478 - 479 - if( iIdx<nEq ){ 480 - for(pIter=pEq; pIter; pIter=pIter->pLink){ 481 - if( pIter->bFlag ) continue; 482 - if( pIter->iCol!=iCol ) continue; 483 - if( sqlite3_stricmp(pIter->zColl, zColl) ) continue; 484 - pIter->bFlag = 1; 485 - break; 486 - } 487 - if( pIter==0 ){ 488 - bMatch = 0; 489 - break; 490 - } 491 - }else{ 492 - if( pT ){ 493 - if( pT->iCol!=iCol || sqlite3_stricmp(pT->zColl, zColl) ){ 494 - bMatch = 0; 495 - break; 496 - } 497 - pT = pT->pLink; 498 - } 499 - } 500 - } 501 - rc2 = sqlite3_finalize(pInfo); 502 - if( rc==SQLITE_OK ) rc = rc2; 503 - 504 - if( rc==SQLITE_OK && bMatch ){ 505 - sqlite3_finalize(pIdxList); 506 - return 1; 507 - } 508 - } 509 - rc2 = sqlite3_finalize(pIdxList); 510 - if( rc==SQLITE_OK ) rc = rc2; 511 - 512 - *pRc = rc; 513 - return 0; 514 -} 515 - 516 -static int idxCreateFromCons( 517 - IdxContext *pCtx, 518 - IdxScan *pScan, 519 - IdxConstraint *pEq, 520 - IdxConstraint *pTail 521 -){ 522 - sqlite3 *dbm = pCtx->dbm; 523 - int rc = SQLITE_OK; 524 - if( (pEq || pTail) && 0==idxFindCompatible(&rc, dbm, pScan, pEq, pTail) ){ 525 - IdxTable *pTab = pScan->pTable; 526 - char *zCols = 0; 527 - char *zIdx = 0; 528 - IdxConstraint *pCons; 529 - int h = 0; 530 - const char *zFmt; 531 - 532 - for(pCons=pEq; pCons; pCons=pCons->pLink){ 533 - zCols = idxAppendColDefn(&rc, zCols, pTab, pCons); 534 - } 535 - for(pCons=pTail; pCons; pCons=pCons->pLink){ 536 - zCols = idxAppendColDefn(&rc, zCols, pTab, pCons); 537 - } 538 - 539 - if( rc==SQLITE_OK ){ 540 - /* Hash the list of columns to come up with a name for the index */ 541 - int i; 542 - for(i=0; zCols[i]; i++){ 543 - h += ((h<<3) + zCols[i]); 544 - } 545 - 546 - if( idxIdentifierRequiresQuotes(pScan->zTable) ){ 547 - zFmt = "CREATE INDEX '%q_idx_%08x' ON %Q(%s)"; 548 - }else{ 549 - zFmt = "CREATE INDEX %s_idx_%08x ON %s(%s)"; 550 - } 551 - zIdx = sqlite3_mprintf(zFmt, pScan->zTable, h, pScan->zTable, zCols); 552 - if( !zIdx ){ 553 - rc = SQLITE_NOMEM; 554 - }else{ 555 - rc = sqlite3_exec(dbm, zIdx, 0, 0, pCtx->pzErrmsg); 556 -#if 0 557 - printf("CANDIDATE: %s\n", zIdx); 558 -#endif 559 - } 560 - } 561 - if( rc==SQLITE_OK && pCtx->iIdxRowid==0 ){ 562 - int rc2; 563 - sqlite3_stmt *pLast = 0; 564 - rc = idxPrepareStmt(dbm, &pLast, pCtx->pzErrmsg, 565 - "SELECT max(rowid) FROM sqlite_master" 566 - ); 567 - if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pLast) ){ 568 - pCtx->iIdxRowid = sqlite3_column_int64(pLast, 0); 569 - } 570 - rc2 = sqlite3_finalize(pLast); 571 - if( rc==SQLITE_OK ) rc = rc2; 572 - } 573 - 574 - sqlite3_free(zIdx); 575 - sqlite3_free(zCols); 576 - } 577 - return rc; 578 -} 579 - 580 -static int idxCreateFromWhere( 581 - IdxContext*, i64, IdxScan*, IdxWhere*, IdxConstraint*, IdxConstraint* 582 -); 583 - 584 -/* 585 -** Return true if list pList (linked by IdxConstraint.pLink) contains 586 -** a constraint compatible with *p. Otherwise return false. 587 -*/ 588 -static int idxFindConstraint(IdxConstraint *pList, IdxConstraint *p){ 589 - IdxConstraint *pCmp; 590 - for(pCmp=pList; pCmp; pCmp=pCmp->pLink){ 591 - if( p->iCol==pCmp->iCol ) return 1; 592 - } 593 - return 0; 594 -} 595 - 596 -static int idxCreateFromWhere( 597 - IdxContext *pCtx, 598 - i64 mask, /* Consider only these constraints */ 599 - IdxScan *pScan, /* Create indexes for this scan */ 600 - IdxWhere *pWhere, /* Read constraints from here */ 601 - IdxConstraint *pEq, /* == constraints for inclusion */ 602 - IdxConstraint *pTail /* range/ORDER BY constraints for inclusion */ 603 -){ 604 - IdxConstraint *p1 = pEq; 605 - IdxConstraint *pCon; 606 - int rc; 607 - 608 - /* Gather up all the == constraints that match the mask. */ 609 - for(pCon=pWhere->pEq; pCon; pCon=pCon->pNext){ 610 - if( (mask & pCon->depmask)==pCon->depmask 611 - && idxFindConstraint(p1, pCon)==0 612 - && idxFindConstraint(pTail, pCon)==0 613 - ){ 614 - pCon->pLink = p1; 615 - p1 = pCon; 616 - } 617 - } 618 - 619 - /* Create an index using the == constraints collected above. And the 620 - ** range constraint/ORDER BY terms passed in by the caller, if any. */ 621 - rc = idxCreateFromCons(pCtx, pScan, p1, pTail); 622 - 623 - /* If no range/ORDER BY passed by the caller, create a version of the 624 - ** index for each range constraint that matches the mask. */ 625 - if( pTail==0 ){ 626 - for(pCon=pWhere->pRange; rc==SQLITE_OK && pCon; pCon=pCon->pNext){ 627 - assert( pCon->pLink==0 ); 628 - if( (mask & pCon->depmask)==pCon->depmask 629 - && idxFindConstraint(pEq, pCon)==0 630 - && idxFindConstraint(pTail, pCon)==0 631 - ){ 632 - rc = idxCreateFromCons(pCtx, pScan, p1, pCon); 633 - } 634 - } 635 - } 636 - 637 - return rc; 638 -} 639 - 640 -/* 641 -** Create candidate indexes in database [dbm] based on the data in 642 -** linked-list pScan. 643 -*/ 644 -static int idxCreateCandidates(IdxContext *pCtx){ 645 - sqlite3 *dbm = pCtx->dbm; 646 - int rc2; 647 - int rc = SQLITE_OK; 648 - sqlite3_stmt *pDepmask; /* Foreach depmask */ 649 - IdxScan *pIter; 650 - 651 - rc = idxPrepareStmt(dbm, &pDepmask, pCtx->pzErrmsg, 652 - "SELECT mask FROM depmask" 653 - ); 654 - 655 - for(pIter=pCtx->pScan; pIter && rc==SQLITE_OK; pIter=pIter->pNextScan){ 656 - IdxWhere *pWhere = &pIter->where; 657 - while( SQLITE_ROW==sqlite3_step(pDepmask) && rc==SQLITE_OK ){ 658 - i64 mask = sqlite3_column_int64(pDepmask, 0); 659 - rc = idxCreateFromWhere(pCtx, mask, pIter, pWhere, 0, 0); 660 - if( rc==SQLITE_OK && pIter->pOrder ){ 661 - rc = idxCreateFromWhere(pCtx, mask, pIter, pWhere, 0, pIter->pOrder); 662 - } 663 - } 664 - } 665 - 666 - rc2 = sqlite3_finalize(pDepmask); 667 - if( rc==SQLITE_OK ) rc = rc2; 668 - return rc; 669 -} 670 - 671 -static void idxScanFree(IdxScan *pScan){ 672 - IdxScan *pIter; 673 - IdxScan *pNext; 674 - for(pIter=pScan; pIter; pIter=pNext){ 675 - pNext = pIter->pNextScan; 676 - } 677 -} 678 - 679 -int idxFindIndexes( 680 - IdxContext *pCtx, 681 - const char *zSql, /* SQL to find indexes for */ 682 - void (*xOut)(void*, const char*), /* Output callback */ 683 - void *pOutCtx, /* Context for xOut() */ 684 - char **pzErr /* OUT: Error message (sqlite3_malloc) */ 685 -){ 686 - sqlite3 *dbm = pCtx->dbm; 687 - sqlite3_stmt *pExplain = 0; 688 - sqlite3_stmt *pSelect = 0; 689 - sqlite3_stmt *pInsert = 0; 690 - int rc, rc2; 691 - int bFound = 0; 692 - 693 - rc = idxPrintfPrepareStmt(dbm, &pExplain, pzErr,"EXPLAIN QUERY PLAN %s",zSql); 694 - if( rc==SQLITE_OK ){ 695 - rc = idxPrepareStmt(dbm, &pSelect, pzErr, 696 - "SELECT rowid, sql FROM sqlite_master WHERE name = ?" 697 - ); 698 - } 699 - if( rc==SQLITE_OK ){ 700 - rc = idxPrepareStmt(dbm, &pInsert, pzErr, 701 - "INSERT OR IGNORE INTO aux.indexes VALUES(?)" 702 - ); 703 - } 704 - 705 - while( rc==SQLITE_OK && sqlite3_step(pExplain)==SQLITE_ROW ){ 706 - int i; 707 - const char *zDetail = (const char*)sqlite3_column_text(pExplain, 3); 708 - int nDetail = strlen(zDetail); 709 - 710 - for(i=0; i<nDetail; i++){ 711 - const char *zIdx = 0; 712 - if( memcmp(&zDetail[i], " USING INDEX ", 13)==0 ){ 713 - zIdx = &zDetail[i+13]; 714 - }else if( memcmp(&zDetail[i], " USING COVERING INDEX ", 22)==0 ){ 715 - zIdx = &zDetail[i+22]; 716 - } 717 - if( zIdx ){ 718 - int nIdx = 0; 719 - while( zIdx[nIdx]!='\0' && (zIdx[nIdx]!=' ' || zIdx[nIdx+1]!='(') ){ 720 - nIdx++; 721 - } 722 - sqlite3_bind_text(pSelect, 1, zIdx, nIdx, SQLITE_STATIC); 723 - if( SQLITE_ROW==sqlite3_step(pSelect) ){ 724 - i64 iRowid = sqlite3_column_int64(pSelect, 0); 725 - const char *zSql = (const char*)sqlite3_column_text(pSelect, 1); 726 - if( iRowid>=pCtx->iIdxRowid ){ 727 - sqlite3_bind_text(pInsert, 1, zSql, -1, SQLITE_STATIC); 728 - sqlite3_step(pInsert); 729 - rc = sqlite3_reset(pInsert); 730 - if( rc ) goto find_indexes_out; 731 - } 732 - } 733 - rc = sqlite3_reset(pSelect); 734 - break; 735 - } 736 - } 737 - } 738 - rc2 = sqlite3_reset(pExplain); 739 - if( rc==SQLITE_OK ) rc = rc2; 740 - if( rc==SQLITE_OK ){ 741 - sqlite3_stmt *pLoop = 0; 742 - rc = idxPrepareStmt(dbm, &pLoop, pzErr,"SELECT name||';' FROM aux.indexes"); 743 - if( rc==SQLITE_OK ){ 744 - while( SQLITE_ROW==sqlite3_step(pLoop) ){ 745 - bFound = 1; 746 - xOut(pOutCtx, (const char*)sqlite3_column_text(pLoop, 0)); 747 - } 748 - rc = sqlite3_finalize(pLoop); 749 - } 750 - if( rc==SQLITE_OK ){ 751 - if( bFound==0 ) xOut(pOutCtx, "(no new indexes)"); 752 - xOut(pOutCtx, ""); 753 - } 754 - } 755 - 756 - while( rc==SQLITE_OK && sqlite3_step(pExplain)==SQLITE_ROW ){ 757 - int iSelectid = sqlite3_column_int(pExplain, 0); 758 - int iOrder = sqlite3_column_int(pExplain, 1); 759 - int iFrom = sqlite3_column_int(pExplain, 2); 760 - const char *zDetail = (const char*)sqlite3_column_text(pExplain, 3); 761 - char *zOut; 762 - 763 - zOut = sqlite3_mprintf("%d|%d|%d|%s", iSelectid, iOrder, iFrom, zDetail); 764 - if( zOut==0 ){ 765 - rc = SQLITE_NOMEM; 766 - }else{ 767 - xOut(pOutCtx, zOut); 768 - sqlite3_free(zOut); 769 - } 770 - } 771 - 772 - find_indexes_out: 773 - rc2 = sqlite3_finalize(pExplain); 774 - if( rc==SQLITE_OK ) rc = rc2; 775 - rc2 = sqlite3_finalize(pSelect); 776 - if( rc==SQLITE_OK ) rc = rc2; 777 - rc2 = sqlite3_finalize(pInsert); 778 - if( rc==SQLITE_OK ) rc = rc2; 779 - 780 - return rc; 781 -} 782 - 783 -/* 784 -** The xOut callback is invoked to return command output to the user. The 785 -** second argument is always a nul-terminated string. The first argument is 786 -** passed zero if the string contains normal output or non-zero if it is an 787 -** error message. 788 -*/ 789 -int shellIndexesCommand( 790 - sqlite3 *db, /* Database handle */ 791 - const char *zSql, /* SQL to find indexes for */ 792 - void (*xOut)(void*, const char*), /* Output callback */ 793 - void *pOutCtx, /* Context for xOut() */ 794 - char **pzErrmsg /* OUT: Error message (sqlite3_malloc) */ 795 -){ 796 - int rc = SQLITE_OK; 797 - sqlite3 *dbm = 0; 798 - IdxContext ctx; 799 - sqlite3_stmt *pStmt = 0; /* Statement compiled from zSql */ 800 - 801 - memset(&ctx, 0, sizeof(IdxContext)); 802 - ctx.pzErrmsg = pzErrmsg; 803 - 804 - /* Open an in-memory database to work with. The main in-memory 805 - ** database schema contains tables similar to those in the users 806 - ** database (handle db). The attached in-memory db (aux) contains 807 - ** application tables used by the code in this file. */ 808 - rc = sqlite3_open(":memory:", &dbm); 809 - if( rc==SQLITE_OK ){ 810 - rc = sqlite3_exec(dbm, 811 - "ATTACH ':memory:' AS aux;" 812 - "CREATE TABLE aux.depmask(mask PRIMARY KEY) WITHOUT ROWID;" 813 - "CREATE TABLE aux.indexes(name PRIMARY KEY) WITHOUT ROWID;" 814 - "INSERT INTO aux.depmask VALUES(0);" 815 - , 0, 0, pzErrmsg 816 - ); 817 - } 818 - 819 - /* Prepare an INSERT statement for writing to aux.depmask */ 820 - if( rc==SQLITE_OK ){ 821 - rc = idxPrepareStmt(dbm, &ctx.pInsertMask, pzErrmsg, 822 - "INSERT OR IGNORE INTO aux.depmask SELECT mask | ?1 FROM aux.depmask;" 823 - ); 824 - } 825 - 826 - /* Analyze the SELECT statement in zSql. */ 827 - if( rc==SQLITE_OK ){ 828 - ctx.dbm = dbm; 829 - sqlite3_whereinfo_hook(db, idxWhereInfo, (void*)&ctx); 830 - rc = idxPrepareStmt(db, &pStmt, pzErrmsg, zSql); 831 - sqlite3_whereinfo_hook(db, 0, 0); 832 - sqlite3_finalize(pStmt); 833 - } 834 - 835 - /* Create tables within the main in-memory database. These tables 836 - ** have the same names, columns and declared types as the tables in 837 - ** the user database. All constraints except for PRIMARY KEY are 838 - ** removed. */ 839 - if( rc==SQLITE_OK ){ 840 - rc = idxCreateTables(db, dbm, ctx.pScan, pzErrmsg); 841 - } 842 - 843 - /* Create candidate indexes within the in-memory database file */ 844 - if( rc==SQLITE_OK ){ 845 - rc = idxCreateCandidates(&ctx); 846 - } 847 - 848 - /* Figure out which of the candidate indexes are preferred by the query 849 - ** planner and report the results to the user. */ 850 - if( rc==SQLITE_OK ){ 851 - rc = idxFindIndexes(&ctx, zSql, xOut, pOutCtx, pzErrmsg); 852 - } 853 - 854 - idxScanFree(ctx.pScan); 855 - sqlite3_finalize(ctx.pInsertMask); 856 - sqlite3_close(dbm); 857 - return rc; 858 -} 859 - 860 -