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