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 int nPk = 0;
689
690 rc = idxPrintfPrepareStmt(db, &p1, pzErrmsg, "PRAGMA table_xinfo=%Q", zTab);
691 while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(p1) ){
692 const char *zCol = (const char*)sqlite3_column_text(p1, 1);
693 nByte += 1 + STRLEN(zCol);
694 rc = sqlite3_table_column_metadata(
695 db, "main", zTab, zCol, 0, &zCol, 0, 0, 0
696 );
697 nByte += 1 + STRLEN(zCol);
698 nCol++;
699 nPk += (sqlite3_column_int(p1, 5)>0);
700 }
701 rc2 = sqlite3_reset(p1);
702 if( rc==SQLITE_OK ) rc = rc2;
703
704 nByte += sizeof(IdxColumn) * nCol;
705 if( rc==SQLITE_OK ){
706 pNew = idxMalloc(&rc, nByte);
707 }
708 if( rc==SQLITE_OK ){
709 pNew->aCol = (IdxColumn*)&pNew[1];
710 pNew->nCol = nCol;
711 pCsr = (char*)&pNew->aCol[nCol];
712 }
713
714 nCol = 0;
715 while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(p1) ){
716 const char *zCol = (const char*)sqlite3_column_text(p1, 1);
717 int nCopy = STRLEN(zCol) + 1;
718 pNew->aCol[nCol].zName = pCsr;
719 pNew->aCol[nCol].iPk = (sqlite3_column_int(p1, 5)==1 && nPk==1);
720 memcpy(pCsr, zCol, nCopy);
721 pCsr += nCopy;
722
723 rc = sqlite3_table_column_metadata(
724 db, "main", zTab, zCol, 0, &zCol, 0, 0, 0
725 );
726 if( rc==SQLITE_OK ){
727 nCopy = STRLEN(zCol) + 1;
728 pNew->aCol[nCol].zColl = pCsr;
729 memcpy(pCsr, zCol, nCopy);
730 pCsr += nCopy;
731 }
732
733 nCol++;
734 }
735 idxFinalize(&rc, p1);
736
737 if( rc!=SQLITE_OK ){
738 sqlite3_free(pNew);
739 pNew = 0;
740 }else{
741 pNew->zName = pCsr;
742 memcpy(pNew->zName, zTab, nTab+1);
743 }
744
745 *ppOut = pNew;
746 return rc;
747 }
748
749 /*
750 ** This function is a no-op if *pRc is set to anything other than
751 ** SQLITE_OK when it is called.
752 **
753 ** If *pRc is initially set to SQLITE_OK, then the text specified by
754 ** the printf() style arguments is appended to zIn and the result returned
755 ** in a buffer allocated by sqlite3_malloc(). sqlite3_free() is called on
756 ** zIn before returning.
757 */
idxAppendText(int * pRc,char * zIn,const char * zFmt,...)758 static char *idxAppendText(int *pRc, char *zIn, const char *zFmt, ...){
759 va_list ap;
760 char *zAppend = 0;
761 char *zRet = 0;
762 int nIn = zIn ? STRLEN(zIn) : 0;
763 int nAppend = 0;
764 va_start(ap, zFmt);
765 if( *pRc==SQLITE_OK ){
766 zAppend = sqlite3_vmprintf(zFmt, ap);
767 if( zAppend ){
768 nAppend = STRLEN(zAppend);
769 zRet = (char*)sqlite3_malloc(nIn + nAppend + 1);
770 }
771 if( zAppend && zRet ){
772 if( nIn ) memcpy(zRet, zIn, nIn);
773 memcpy(&zRet[nIn], zAppend, nAppend+1);
774 }else{
775 sqlite3_free(zRet);
776 zRet = 0;
777 *pRc = SQLITE_NOMEM;
778 }
779 sqlite3_free(zAppend);
780 sqlite3_free(zIn);
781 }
782 va_end(ap);
783 return zRet;
784 }
785
786 /*
787 ** Return true if zId must be quoted in order to use it as an SQL
788 ** identifier, or false otherwise.
789 */
idxIdentifierRequiresQuotes(const char * zId)790 static int idxIdentifierRequiresQuotes(const char *zId){
791 int i;
792 for(i=0; zId[i]; i++){
793 if( !(zId[i]=='_')
794 && !(zId[i]>='0' && zId[i]<='9')
795 && !(zId[i]>='a' && zId[i]<='z')
796 && !(zId[i]>='A' && zId[i]<='Z')
797 ){
798 return 1;
799 }
800 }
801 return 0;
802 }
803
804 /*
805 ** This function appends an index column definition suitable for constraint
806 ** pCons to the string passed as zIn and returns the result.
807 */
idxAppendColDefn(int * pRc,char * zIn,IdxTable * pTab,IdxConstraint * pCons)808 static char *idxAppendColDefn(
809 int *pRc, /* IN/OUT: Error code */
810 char *zIn, /* Column defn accumulated so far */
811 IdxTable *pTab, /* Table index will be created on */
812 IdxConstraint *pCons
813 ){
814 char *zRet = zIn;
815 IdxColumn *p = &pTab->aCol[pCons->iCol];
816 if( zRet ) zRet = idxAppendText(pRc, zRet, ", ");
817
818 if( idxIdentifierRequiresQuotes(p->zName) ){
819 zRet = idxAppendText(pRc, zRet, "%Q", p->zName);
820 }else{
821 zRet = idxAppendText(pRc, zRet, "%s", p->zName);
822 }
823
824 if( sqlite3_stricmp(p->zColl, pCons->zColl) ){
825 if( idxIdentifierRequiresQuotes(pCons->zColl) ){
826 zRet = idxAppendText(pRc, zRet, " COLLATE %Q", pCons->zColl);
827 }else{
828 zRet = idxAppendText(pRc, zRet, " COLLATE %s", pCons->zColl);
829 }
830 }
831
832 if( pCons->bDesc ){
833 zRet = idxAppendText(pRc, zRet, " DESC");
834 }
835 return zRet;
836 }
837
838 /*
839 ** Search database dbm for an index compatible with the one idxCreateFromCons()
840 ** would create from arguments pScan, pEq and pTail. If no error occurs and
841 ** such an index is found, return non-zero. Or, if no such index is found,
842 ** return zero.
843 **
844 ** If an error occurs, set *pRc to an SQLite error code and return zero.
845 */
idxFindCompatible(int * pRc,sqlite3 * dbm,IdxScan * pScan,IdxConstraint * pEq,IdxConstraint * pTail)846 static int idxFindCompatible(
847 int *pRc, /* OUT: Error code */
848 sqlite3* dbm, /* Database to search */
849 IdxScan *pScan, /* Scan for table to search for index on */
850 IdxConstraint *pEq, /* List of == constraints */
851 IdxConstraint *pTail /* List of range constraints */
852 ){
853 const char *zTbl = pScan->pTab->zName;
854 sqlite3_stmt *pIdxList = 0;
855 IdxConstraint *pIter;
856 int nEq = 0; /* Number of elements in pEq */
857 int rc;
858
859 /* Count the elements in list pEq */
860 for(pIter=pEq; pIter; pIter=pIter->pLink) nEq++;
861
862 rc = idxPrintfPrepareStmt(dbm, &pIdxList, 0, "PRAGMA index_list=%Q", zTbl);
863 while( rc==SQLITE_OK && sqlite3_step(pIdxList)==SQLITE_ROW ){
864 int bMatch = 1;
865 IdxConstraint *pT = pTail;
866 sqlite3_stmt *pInfo = 0;
867 const char *zIdx = (const char*)sqlite3_column_text(pIdxList, 1);
868
869 /* Zero the IdxConstraint.bFlag values in the pEq list */
870 for(pIter=pEq; pIter; pIter=pIter->pLink) pIter->bFlag = 0;
871
872 rc = idxPrintfPrepareStmt(dbm, &pInfo, 0, "PRAGMA index_xInfo=%Q", zIdx);
873 while( rc==SQLITE_OK && sqlite3_step(pInfo)==SQLITE_ROW ){
874 int iIdx = sqlite3_column_int(pInfo, 0);
875 int iCol = sqlite3_column_int(pInfo, 1);
876 const char *zColl = (const char*)sqlite3_column_text(pInfo, 4);
877
878 if( iIdx<nEq ){
879 for(pIter=pEq; pIter; pIter=pIter->pLink){
880 if( pIter->bFlag ) continue;
881 if( pIter->iCol!=iCol ) continue;
882 if( sqlite3_stricmp(pIter->zColl, zColl) ) continue;
883 pIter->bFlag = 1;
884 break;
885 }
886 if( pIter==0 ){
887 bMatch = 0;
888 break;
889 }
890 }else{
891 if( pT ){
892 if( pT->iCol!=iCol || sqlite3_stricmp(pT->zColl, zColl) ){
893 bMatch = 0;
894 break;
895 }
896 pT = pT->pLink;
897 }
898 }
899 }
900 idxFinalize(&rc, pInfo);
901
902 if( rc==SQLITE_OK && bMatch ){
903 sqlite3_finalize(pIdxList);
904 return 1;
905 }
906 }
907 idxFinalize(&rc, pIdxList);
908
909 *pRc = rc;
910 return 0;
911 }
912
idxCreateFromCons(sqlite3expert * p,IdxScan * pScan,IdxConstraint * pEq,IdxConstraint * pTail)913 static int idxCreateFromCons(
914 sqlite3expert *p,
915 IdxScan *pScan,
916 IdxConstraint *pEq,
917 IdxConstraint *pTail
918 ){
919 sqlite3 *dbm = p->dbm;
920 int rc = SQLITE_OK;
921 if( (pEq || pTail) && 0==idxFindCompatible(&rc, dbm, pScan, pEq, pTail) ){
922 IdxTable *pTab = pScan->pTab;
923 char *zCols = 0;
924 char *zIdx = 0;
925 IdxConstraint *pCons;
926 unsigned int h = 0;
927 const char *zFmt;
928
929 for(pCons=pEq; pCons; pCons=pCons->pLink){
930 zCols = idxAppendColDefn(&rc, zCols, pTab, pCons);
931 }
932 for(pCons=pTail; pCons; pCons=pCons->pLink){
933 zCols = idxAppendColDefn(&rc, zCols, pTab, pCons);
934 }
935
936 if( rc==SQLITE_OK ){
937 /* Hash the list of columns to come up with a name for the index */
938 const char *zTable = pScan->pTab->zName;
939 char *zName; /* Index name */
940 int i;
941 for(i=0; zCols[i]; i++){
942 h += ((h<<3) + zCols[i]);
943 }
944 zName = sqlite3_mprintf("%s_idx_%08x", zTable, h);
945 if( zName==0 ){
946 rc = SQLITE_NOMEM;
947 }else{
948 if( idxIdentifierRequiresQuotes(zTable) ){
949 zFmt = "CREATE INDEX '%q' ON %Q(%s)";
950 }else{
951 zFmt = "CREATE INDEX %s ON %s(%s)";
952 }
953 zIdx = sqlite3_mprintf(zFmt, zName, zTable, zCols);
954 if( !zIdx ){
955 rc = SQLITE_NOMEM;
956 }else{
957 rc = sqlite3_exec(dbm, zIdx, 0, 0, p->pzErrmsg);
958 idxHashAdd(&rc, &p->hIdx, zName, zIdx);
959 }
960 sqlite3_free(zName);
961 sqlite3_free(zIdx);
962 }
963 }
964
965 sqlite3_free(zCols);
966 }
967 return rc;
968 }
969
970 /*
971 ** Return true if list pList (linked by IdxConstraint.pLink) contains
972 ** a constraint compatible with *p. Otherwise return false.
973 */
idxFindConstraint(IdxConstraint * pList,IdxConstraint * p)974 static int idxFindConstraint(IdxConstraint *pList, IdxConstraint *p){
975 IdxConstraint *pCmp;
976 for(pCmp=pList; pCmp; pCmp=pCmp->pLink){
977 if( p->iCol==pCmp->iCol ) return 1;
978 }
979 return 0;
980 }
981
idxCreateFromWhere(sqlite3expert * p,IdxScan * pScan,IdxConstraint * pTail)982 static int idxCreateFromWhere(
983 sqlite3expert *p,
984 IdxScan *pScan, /* Create indexes for this scan */
985 IdxConstraint *pTail /* range/ORDER BY constraints for inclusion */
986 ){
987 IdxConstraint *p1 = 0;
988 IdxConstraint *pCon;
989 int rc;
990
991 /* Gather up all the == constraints. */
992 for(pCon=pScan->pEq; pCon; pCon=pCon->pNext){
993 if( !idxFindConstraint(p1, pCon) && !idxFindConstraint(pTail, pCon) ){
994 pCon->pLink = p1;
995 p1 = pCon;
996 }
997 }
998
999 /* Create an index using the == constraints collected above. And the
1000 ** range constraint/ORDER BY terms passed in by the caller, if any. */
1001 rc = idxCreateFromCons(p, pScan, p1, pTail);
1002
1003 /* If no range/ORDER BY passed by the caller, create a version of the
1004 ** index for each range constraint. */
1005 if( pTail==0 ){
1006 for(pCon=pScan->pRange; rc==SQLITE_OK && pCon; pCon=pCon->pNext){
1007 assert( pCon->pLink==0 );
1008 if( !idxFindConstraint(p1, pCon) && !idxFindConstraint(pTail, pCon) ){
1009 rc = idxCreateFromCons(p, pScan, p1, pCon);
1010 }
1011 }
1012 }
1013
1014 return rc;
1015 }
1016
1017 /*
1018 ** Create candidate indexes in database [dbm] based on the data in
1019 ** linked-list pScan.
1020 */
idxCreateCandidates(sqlite3expert * p)1021 static int idxCreateCandidates(sqlite3expert *p){
1022 int rc = SQLITE_OK;
1023 IdxScan *pIter;
1024
1025 for(pIter=p->pScan; pIter && rc==SQLITE_OK; pIter=pIter->pNextScan){
1026 rc = idxCreateFromWhere(p, pIter, 0);
1027 if( rc==SQLITE_OK && pIter->pOrder ){
1028 rc = idxCreateFromWhere(p, pIter, pIter->pOrder);
1029 }
1030 }
1031
1032 return rc;
1033 }
1034
1035 /*
1036 ** Free all elements of the linked list starting at pConstraint.
1037 */
idxConstraintFree(IdxConstraint * pConstraint)1038 static void idxConstraintFree(IdxConstraint *pConstraint){
1039 IdxConstraint *pNext;
1040 IdxConstraint *p;
1041
1042 for(p=pConstraint; p; p=pNext){
1043 pNext = p->pNext;
1044 sqlite3_free(p);
1045 }
1046 }
1047
1048 /*
1049 ** Free all elements of the linked list starting from pScan up until pLast
1050 ** (pLast is not freed).
1051 */
idxScanFree(IdxScan * pScan,IdxScan * pLast)1052 static void idxScanFree(IdxScan *pScan, IdxScan *pLast){
1053 IdxScan *p;
1054 IdxScan *pNext;
1055 for(p=pScan; p!=pLast; p=pNext){
1056 pNext = p->pNextScan;
1057 idxConstraintFree(p->pOrder);
1058 idxConstraintFree(p->pEq);
1059 idxConstraintFree(p->pRange);
1060 sqlite3_free(p);
1061 }
1062 }
1063
1064 /*
1065 ** Free all elements of the linked list starting from pStatement up
1066 ** until pLast (pLast is not freed).
1067 */
idxStatementFree(IdxStatement * pStatement,IdxStatement * pLast)1068 static void idxStatementFree(IdxStatement *pStatement, IdxStatement *pLast){
1069 IdxStatement *p;
1070 IdxStatement *pNext;
1071 for(p=pStatement; p!=pLast; p=pNext){
1072 pNext = p->pNext;
1073 sqlite3_free(p->zEQP);
1074 sqlite3_free(p->zIdx);
1075 sqlite3_free(p);
1076 }
1077 }
1078
1079 /*
1080 ** Free the linked list of IdxTable objects starting at pTab.
1081 */
idxTableFree(IdxTable * pTab)1082 static void idxTableFree(IdxTable *pTab){
1083 IdxTable *pIter;
1084 IdxTable *pNext;
1085 for(pIter=pTab; pIter; pIter=pNext){
1086 pNext = pIter->pNext;
1087 sqlite3_free(pIter);
1088 }
1089 }
1090
1091 /*
1092 ** Free the linked list of IdxWrite objects starting at pTab.
1093 */
idxWriteFree(IdxWrite * pTab)1094 static void idxWriteFree(IdxWrite *pTab){
1095 IdxWrite *pIter;
1096 IdxWrite *pNext;
1097 for(pIter=pTab; pIter; pIter=pNext){
1098 pNext = pIter->pNext;
1099 sqlite3_free(pIter);
1100 }
1101 }
1102
1103
1104
1105 /*
1106 ** This function is called after candidate indexes have been created. It
1107 ** runs all the queries to see which indexes they prefer, and populates
1108 ** IdxStatement.zIdx and IdxStatement.zEQP with the results.
1109 */
idxFindIndexes(sqlite3expert * p,char ** pzErr)1110 int idxFindIndexes(
1111 sqlite3expert *p,
1112 char **pzErr /* OUT: Error message (sqlite3_malloc) */
1113 ){
1114 IdxStatement *pStmt;
1115 sqlite3 *dbm = p->dbm;
1116 int rc = SQLITE_OK;
1117
1118 IdxHash hIdx;
1119 idxHashInit(&hIdx);
1120
1121 for(pStmt=p->pStatement; rc==SQLITE_OK && pStmt; pStmt=pStmt->pNext){
1122 IdxHashEntry *pEntry;
1123 sqlite3_stmt *pExplain = 0;
1124 idxHashClear(&hIdx);
1125 rc = idxPrintfPrepareStmt(dbm, &pExplain, pzErr,
1126 "EXPLAIN QUERY PLAN %s", pStmt->zSql
1127 );
1128 while( rc==SQLITE_OK && sqlite3_step(pExplain)==SQLITE_ROW ){
1129 /* int iId = sqlite3_column_int(pExplain, 0); */
1130 /* int iParent = sqlite3_column_int(pExplain, 1); */
1131 /* int iNotUsed = sqlite3_column_int(pExplain, 2); */
1132 const char *zDetail = (const char*)sqlite3_column_text(pExplain, 3);
1133 int nDetail;
1134 int i;
1135
1136 if( !zDetail ) continue;
1137 nDetail = STRLEN(zDetail);
1138
1139 for(i=0; i<nDetail; i++){
1140 const char *zIdx = 0;
1141 if( i+13<nDetail && memcmp(&zDetail[i], " USING INDEX ", 13)==0 ){
1142 zIdx = &zDetail[i+13];
1143 }else if( i+22<nDetail
1144 && memcmp(&zDetail[i], " USING COVERING INDEX ", 22)==0
1145 ){
1146 zIdx = &zDetail[i+22];
1147 }
1148 if( zIdx ){
1149 const char *zSql;
1150 int nIdx = 0;
1151 while( zIdx[nIdx]!='\0' && (zIdx[nIdx]!=' ' || zIdx[nIdx+1]!='(') ){
1152 nIdx++;
1153 }
1154 zSql = idxHashSearch(&p->hIdx, zIdx, nIdx);
1155 if( zSql ){
1156 idxHashAdd(&rc, &hIdx, zSql, 0);
1157 if( rc ) goto find_indexes_out;
1158 }
1159 break;
1160 }
1161 }
1162
1163 if( zDetail[0]!='-' ){
1164 pStmt->zEQP = idxAppendText(&rc, pStmt->zEQP, "%s\n", zDetail);
1165 }
1166 }
1167
1168 for(pEntry=hIdx.pFirst; pEntry; pEntry=pEntry->pNext){
1169 pStmt->zIdx = idxAppendText(&rc, pStmt->zIdx, "%s;\n", pEntry->zKey);
1170 }
1171
1172 idxFinalize(&rc, pExplain);
1173 }
1174
1175 find_indexes_out:
1176 idxHashClear(&hIdx);
1177 return rc;
1178 }
1179
idxAuthCallback(void * pCtx,int eOp,const char * z3,const char * z4,const char * zDb,const char * zTrigger)1180 static int idxAuthCallback(
1181 void *pCtx,
1182 int eOp,
1183 const char *z3,
1184 const char *z4,
1185 const char *zDb,
1186 const char *zTrigger
1187 ){
1188 int rc = SQLITE_OK;
1189 (void)z4;
1190 (void)zTrigger;
1191 if( eOp==SQLITE_INSERT || eOp==SQLITE_UPDATE || eOp==SQLITE_DELETE ){
1192 if( sqlite3_stricmp(zDb, "main")==0 ){
1193 sqlite3expert *p = (sqlite3expert*)pCtx;
1194 IdxTable *pTab;
1195 for(pTab=p->pTable; pTab; pTab=pTab->pNext){
1196 if( 0==sqlite3_stricmp(z3, pTab->zName) ) break;
1197 }
1198 if( pTab ){
1199 IdxWrite *pWrite;
1200 for(pWrite=p->pWrite; pWrite; pWrite=pWrite->pNext){
1201 if( pWrite->pTab==pTab && pWrite->eOp==eOp ) break;
1202 }
1203 if( pWrite==0 ){
1204 pWrite = idxMalloc(&rc, sizeof(IdxWrite));
1205 if( rc==SQLITE_OK ){
1206 pWrite->pTab = pTab;
1207 pWrite->eOp = eOp;
1208 pWrite->pNext = p->pWrite;
1209 p->pWrite = pWrite;
1210 }
1211 }
1212 }
1213 }
1214 }
1215 return rc;
1216 }
1217
idxProcessOneTrigger(sqlite3expert * p,IdxWrite * pWrite,char ** pzErr)1218 static int idxProcessOneTrigger(
1219 sqlite3expert *p,
1220 IdxWrite *pWrite,
1221 char **pzErr
1222 ){
1223 static const char *zInt = UNIQUE_TABLE_NAME;
1224 static const char *zDrop = "DROP TABLE " UNIQUE_TABLE_NAME;
1225 IdxTable *pTab = pWrite->pTab;
1226 const char *zTab = pTab->zName;
1227 const char *zSql =
1228 "SELECT 'CREATE TEMP' || substr(sql, 7) FROM sqlite_schema "
1229 "WHERE tbl_name = %Q AND type IN ('table', 'trigger') "
1230 "ORDER BY type;";
1231 sqlite3_stmt *pSelect = 0;
1232 int rc = SQLITE_OK;
1233 char *zWrite = 0;
1234
1235 /* Create the table and its triggers in the temp schema */
1236 rc = idxPrintfPrepareStmt(p->db, &pSelect, pzErr, zSql, zTab, zTab);
1237 while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSelect) ){
1238 const char *zCreate = (const char*)sqlite3_column_text(pSelect, 0);
1239 rc = sqlite3_exec(p->dbv, zCreate, 0, 0, pzErr);
1240 }
1241 idxFinalize(&rc, pSelect);
1242
1243 /* Rename the table in the temp schema to zInt */
1244 if( rc==SQLITE_OK ){
1245 char *z = sqlite3_mprintf("ALTER TABLE temp.%Q RENAME TO %Q", zTab, zInt);
1246 if( z==0 ){
1247 rc = SQLITE_NOMEM;
1248 }else{
1249 rc = sqlite3_exec(p->dbv, z, 0, 0, pzErr);
1250 sqlite3_free(z);
1251 }
1252 }
1253
1254 switch( pWrite->eOp ){
1255 case SQLITE_INSERT: {
1256 int i;
1257 zWrite = idxAppendText(&rc, zWrite, "INSERT INTO %Q VALUES(", zInt);
1258 for(i=0; i<pTab->nCol; i++){
1259 zWrite = idxAppendText(&rc, zWrite, "%s?", i==0 ? "" : ", ");
1260 }
1261 zWrite = idxAppendText(&rc, zWrite, ")");
1262 break;
1263 }
1264 case SQLITE_UPDATE: {
1265 int i;
1266 zWrite = idxAppendText(&rc, zWrite, "UPDATE %Q SET ", zInt);
1267 for(i=0; i<pTab->nCol; i++){
1268 zWrite = idxAppendText(&rc, zWrite, "%s%Q=?", i==0 ? "" : ", ",
1269 pTab->aCol[i].zName
1270 );
1271 }
1272 break;
1273 }
1274 default: {
1275 assert( pWrite->eOp==SQLITE_DELETE );
1276 if( rc==SQLITE_OK ){
1277 zWrite = sqlite3_mprintf("DELETE FROM %Q", zInt);
1278 if( zWrite==0 ) rc = SQLITE_NOMEM;
1279 }
1280 }
1281 }
1282
1283 if( rc==SQLITE_OK ){
1284 sqlite3_stmt *pX = 0;
1285 rc = sqlite3_prepare_v2(p->dbv, zWrite, -1, &pX, 0);
1286 idxFinalize(&rc, pX);
1287 if( rc!=SQLITE_OK ){
1288 idxDatabaseError(p->dbv, pzErr);
1289 }
1290 }
1291 sqlite3_free(zWrite);
1292
1293 if( rc==SQLITE_OK ){
1294 rc = sqlite3_exec(p->dbv, zDrop, 0, 0, pzErr);
1295 }
1296
1297 return rc;
1298 }
1299
idxProcessTriggers(sqlite3expert * p,char ** pzErr)1300 static int idxProcessTriggers(sqlite3expert *p, char **pzErr){
1301 int rc = SQLITE_OK;
1302 IdxWrite *pEnd = 0;
1303 IdxWrite *pFirst = p->pWrite;
1304
1305 while( rc==SQLITE_OK && pFirst!=pEnd ){
1306 IdxWrite *pIter;
1307 for(pIter=pFirst; rc==SQLITE_OK && pIter!=pEnd; pIter=pIter->pNext){
1308 rc = idxProcessOneTrigger(p, pIter, pzErr);
1309 }
1310 pEnd = pFirst;
1311 pFirst = p->pWrite;
1312 }
1313
1314 return rc;
1315 }
1316
1317
idxCreateVtabSchema(sqlite3expert * p,char ** pzErrmsg)1318 static int idxCreateVtabSchema(sqlite3expert *p, char **pzErrmsg){
1319 int rc = idxRegisterVtab(p);
1320 sqlite3_stmt *pSchema = 0;
1321
1322 /* For each table in the main db schema:
1323 **
1324 ** 1) Add an entry to the p->pTable list, and
1325 ** 2) Create the equivalent virtual table in dbv.
1326 */
1327 rc = idxPrepareStmt(p->db, &pSchema, pzErrmsg,
1328 "SELECT type, name, sql, 1 FROM sqlite_schema "
1329 "WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%%' "
1330 " UNION ALL "
1331 "SELECT type, name, sql, 2 FROM sqlite_schema "
1332 "WHERE type = 'trigger'"
1333 " AND tbl_name IN(SELECT name FROM sqlite_schema WHERE type = 'view') "
1334 "ORDER BY 4, 1"
1335 );
1336 while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSchema) ){
1337 const char *zType = (const char*)sqlite3_column_text(pSchema, 0);
1338 const char *zName = (const char*)sqlite3_column_text(pSchema, 1);
1339 const char *zSql = (const char*)sqlite3_column_text(pSchema, 2);
1340
1341 if( zType[0]=='v' || zType[1]=='r' ){
1342 rc = sqlite3_exec(p->dbv, zSql, 0, 0, pzErrmsg);
1343 }else{
1344 IdxTable *pTab;
1345 rc = idxGetTableInfo(p->db, zName, &pTab, pzErrmsg);
1346 if( rc==SQLITE_OK ){
1347 int i;
1348 char *zInner = 0;
1349 char *zOuter = 0;
1350 pTab->pNext = p->pTable;
1351 p->pTable = pTab;
1352
1353 /* The statement the vtab will pass to sqlite3_declare_vtab() */
1354 zInner = idxAppendText(&rc, 0, "CREATE TABLE x(");
1355 for(i=0; i<pTab->nCol; i++){
1356 zInner = idxAppendText(&rc, zInner, "%s%Q COLLATE %s",
1357 (i==0 ? "" : ", "), pTab->aCol[i].zName, pTab->aCol[i].zColl
1358 );
1359 }
1360 zInner = idxAppendText(&rc, zInner, ")");
1361
1362 /* The CVT statement to create the vtab */
1363 zOuter = idxAppendText(&rc, 0,
1364 "CREATE VIRTUAL TABLE %Q USING expert(%Q)", zName, zInner
1365 );
1366 if( rc==SQLITE_OK ){
1367 rc = sqlite3_exec(p->dbv, zOuter, 0, 0, pzErrmsg);
1368 }
1369 sqlite3_free(zInner);
1370 sqlite3_free(zOuter);
1371 }
1372 }
1373 }
1374 idxFinalize(&rc, pSchema);
1375 return rc;
1376 }
1377
1378 struct IdxSampleCtx {
1379 int iTarget;
1380 double target; /* Target nRet/nRow value */
1381 double nRow; /* Number of rows seen */
1382 double nRet; /* Number of rows returned */
1383 };
1384
idxSampleFunc(sqlite3_context * pCtx,int argc,sqlite3_value ** argv)1385 static void idxSampleFunc(
1386 sqlite3_context *pCtx,
1387 int argc,
1388 sqlite3_value **argv
1389 ){
1390 struct IdxSampleCtx *p = (struct IdxSampleCtx*)sqlite3_user_data(pCtx);
1391 int bRet;
1392
1393 (void)argv;
1394 assert( argc==0 );
1395 if( p->nRow==0.0 ){
1396 bRet = 1;
1397 }else{
1398 bRet = (p->nRet / p->nRow) <= p->target;
1399 if( bRet==0 ){
1400 unsigned short rnd;
1401 sqlite3_randomness(2, (void*)&rnd);
1402 bRet = ((int)rnd % 100) <= p->iTarget;
1403 }
1404 }
1405
1406 sqlite3_result_int(pCtx, bRet);
1407 p->nRow += 1.0;
1408 p->nRet += (double)bRet;
1409 }
1410
1411 struct IdxRemCtx {
1412 int nSlot;
1413 struct IdxRemSlot {
1414 int eType; /* SQLITE_NULL, INTEGER, REAL, TEXT, BLOB */
1415 i64 iVal; /* SQLITE_INTEGER value */
1416 double rVal; /* SQLITE_FLOAT value */
1417 int nByte; /* Bytes of space allocated at z */
1418 int n; /* Size of buffer z */
1419 char *z; /* SQLITE_TEXT/BLOB value */
1420 } aSlot[1];
1421 };
1422
1423 /*
1424 ** Implementation of scalar function rem().
1425 */
idxRemFunc(sqlite3_context * pCtx,int argc,sqlite3_value ** argv)1426 static void idxRemFunc(
1427 sqlite3_context *pCtx,
1428 int argc,
1429 sqlite3_value **argv
1430 ){
1431 struct IdxRemCtx *p = (struct IdxRemCtx*)sqlite3_user_data(pCtx);
1432 struct IdxRemSlot *pSlot;
1433 int iSlot;
1434 assert( argc==2 );
1435
1436 iSlot = sqlite3_value_int(argv[0]);
1437 assert( iSlot<=p->nSlot );
1438 pSlot = &p->aSlot[iSlot];
1439
1440 switch( pSlot->eType ){
1441 case SQLITE_NULL:
1442 /* no-op */
1443 break;
1444
1445 case SQLITE_INTEGER:
1446 sqlite3_result_int64(pCtx, pSlot->iVal);
1447 break;
1448
1449 case SQLITE_FLOAT:
1450 sqlite3_result_double(pCtx, pSlot->rVal);
1451 break;
1452
1453 case SQLITE_BLOB:
1454 sqlite3_result_blob(pCtx, pSlot->z, pSlot->n, SQLITE_TRANSIENT);
1455 break;
1456
1457 case SQLITE_TEXT:
1458 sqlite3_result_text(pCtx, pSlot->z, pSlot->n, SQLITE_TRANSIENT);
1459 break;
1460 }
1461
1462 pSlot->eType = sqlite3_value_type(argv[1]);
1463 switch( pSlot->eType ){
1464 case SQLITE_NULL:
1465 /* no-op */
1466 break;
1467
1468 case SQLITE_INTEGER:
1469 pSlot->iVal = sqlite3_value_int64(argv[1]);
1470 break;
1471
1472 case SQLITE_FLOAT:
1473 pSlot->rVal = sqlite3_value_double(argv[1]);
1474 break;
1475
1476 case SQLITE_BLOB:
1477 case SQLITE_TEXT: {
1478 int nByte = sqlite3_value_bytes(argv[1]);
1479 if( nByte>pSlot->nByte ){
1480 char *zNew = (char*)sqlite3_realloc(pSlot->z, nByte*2);
1481 if( zNew==0 ){
1482 sqlite3_result_error_nomem(pCtx);
1483 return;
1484 }
1485 pSlot->nByte = nByte*2;
1486 pSlot->z = zNew;
1487 }
1488 pSlot->n = nByte;
1489 if( pSlot->eType==SQLITE_BLOB ){
1490 memcpy(pSlot->z, sqlite3_value_blob(argv[1]), nByte);
1491 }else{
1492 memcpy(pSlot->z, sqlite3_value_text(argv[1]), nByte);
1493 }
1494 break;
1495 }
1496 }
1497 }
1498
idxLargestIndex(sqlite3 * db,int * pnMax,char ** pzErr)1499 static int idxLargestIndex(sqlite3 *db, int *pnMax, char **pzErr){
1500 int rc = SQLITE_OK;
1501 const char *zMax =
1502 "SELECT max(i.seqno) FROM "
1503 " sqlite_schema AS s, "
1504 " pragma_index_list(s.name) AS l, "
1505 " pragma_index_info(l.name) AS i "
1506 "WHERE s.type = 'table'";
1507 sqlite3_stmt *pMax = 0;
1508
1509 *pnMax = 0;
1510 rc = idxPrepareStmt(db, &pMax, pzErr, zMax);
1511 if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pMax) ){
1512 *pnMax = sqlite3_column_int(pMax, 0) + 1;
1513 }
1514 idxFinalize(&rc, pMax);
1515
1516 return rc;
1517 }
1518
idxPopulateOneStat1(sqlite3expert * p,sqlite3_stmt * pIndexXInfo,sqlite3_stmt * pWriteStat,const char * zTab,const char * zIdx,char ** pzErr)1519 static int idxPopulateOneStat1(
1520 sqlite3expert *p,
1521 sqlite3_stmt *pIndexXInfo,
1522 sqlite3_stmt *pWriteStat,
1523 const char *zTab,
1524 const char *zIdx,
1525 char **pzErr
1526 ){
1527 char *zCols = 0;
1528 char *zOrder = 0;
1529 char *zQuery = 0;
1530 int nCol = 0;
1531 int i;
1532 sqlite3_stmt *pQuery = 0;
1533 int *aStat = 0;
1534 int rc = SQLITE_OK;
1535
1536 assert( p->iSample>0 );
1537
1538 /* Formulate the query text */
1539 sqlite3_bind_text(pIndexXInfo, 1, zIdx, -1, SQLITE_STATIC);
1540 while( SQLITE_OK==rc && SQLITE_ROW==sqlite3_step(pIndexXInfo) ){
1541 const char *zComma = zCols==0 ? "" : ", ";
1542 const char *zName = (const char*)sqlite3_column_text(pIndexXInfo, 0);
1543 const char *zColl = (const char*)sqlite3_column_text(pIndexXInfo, 1);
1544 zCols = idxAppendText(&rc, zCols,
1545 "%sx.%Q IS rem(%d, x.%Q) COLLATE %s", zComma, zName, nCol, zName, zColl
1546 );
1547 zOrder = idxAppendText(&rc, zOrder, "%s%d", zComma, ++nCol);
1548 }
1549 sqlite3_reset(pIndexXInfo);
1550 if( rc==SQLITE_OK ){
1551 if( p->iSample==100 ){
1552 zQuery = sqlite3_mprintf(
1553 "SELECT %s FROM %Q x ORDER BY %s", zCols, zTab, zOrder
1554 );
1555 }else{
1556 zQuery = sqlite3_mprintf(
1557 "SELECT %s FROM temp."UNIQUE_TABLE_NAME" x ORDER BY %s", zCols, zOrder
1558 );
1559 }
1560 }
1561 sqlite3_free(zCols);
1562 sqlite3_free(zOrder);
1563
1564 /* Formulate the query text */
1565 if( rc==SQLITE_OK ){
1566 sqlite3 *dbrem = (p->iSample==100 ? p->db : p->dbv);
1567 rc = idxPrepareStmt(dbrem, &pQuery, pzErr, zQuery);
1568 }
1569 sqlite3_free(zQuery);
1570
1571 if( rc==SQLITE_OK ){
1572 aStat = (int*)idxMalloc(&rc, sizeof(int)*(nCol+1));
1573 }
1574 if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pQuery) ){
1575 IdxHashEntry *pEntry;
1576 char *zStat = 0;
1577 for(i=0; i<=nCol; i++) aStat[i] = 1;
1578 while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pQuery) ){
1579 aStat[0]++;
1580 for(i=0; i<nCol; i++){
1581 if( sqlite3_column_int(pQuery, i)==0 ) break;
1582 }
1583 for(/*no-op*/; i<nCol; i++){
1584 aStat[i+1]++;
1585 }
1586 }
1587
1588 if( rc==SQLITE_OK ){
1589 int s0 = aStat[0];
1590 zStat = sqlite3_mprintf("%d", s0);
1591 if( zStat==0 ) rc = SQLITE_NOMEM;
1592 for(i=1; rc==SQLITE_OK && i<=nCol; i++){
1593 zStat = idxAppendText(&rc, zStat, " %d", (s0+aStat[i]/2) / aStat[i]);
1594 }
1595 }
1596
1597 if( rc==SQLITE_OK ){
1598 sqlite3_bind_text(pWriteStat, 1, zTab, -1, SQLITE_STATIC);
1599 sqlite3_bind_text(pWriteStat, 2, zIdx, -1, SQLITE_STATIC);
1600 sqlite3_bind_text(pWriteStat, 3, zStat, -1, SQLITE_STATIC);
1601 sqlite3_step(pWriteStat);
1602 rc = sqlite3_reset(pWriteStat);
1603 }
1604
1605 pEntry = idxHashFind(&p->hIdx, zIdx, STRLEN(zIdx));
1606 if( pEntry ){
1607 assert( pEntry->zVal2==0 );
1608 pEntry->zVal2 = zStat;
1609 }else{
1610 sqlite3_free(zStat);
1611 }
1612 }
1613 sqlite3_free(aStat);
1614 idxFinalize(&rc, pQuery);
1615
1616 return rc;
1617 }
1618
idxBuildSampleTable(sqlite3expert * p,const char * zTab)1619 static int idxBuildSampleTable(sqlite3expert *p, const char *zTab){
1620 int rc;
1621 char *zSql;
1622
1623 rc = sqlite3_exec(p->dbv,"DROP TABLE IF EXISTS temp."UNIQUE_TABLE_NAME,0,0,0);
1624 if( rc!=SQLITE_OK ) return rc;
1625
1626 zSql = sqlite3_mprintf(
1627 "CREATE TABLE temp." UNIQUE_TABLE_NAME " AS SELECT * FROM %Q", zTab
1628 );
1629 if( zSql==0 ) return SQLITE_NOMEM;
1630 rc = sqlite3_exec(p->dbv, zSql, 0, 0, 0);
1631 sqlite3_free(zSql);
1632
1633 return rc;
1634 }
1635
1636 /*
1637 ** This function is called as part of sqlite3_expert_analyze(). Candidate
1638 ** indexes have already been created in database sqlite3expert.dbm, this
1639 ** function populates sqlite_stat1 table in the same database.
1640 **
1641 ** The stat1 data is generated by querying the
1642 */
idxPopulateStat1(sqlite3expert * p,char ** pzErr)1643 static int idxPopulateStat1(sqlite3expert *p, char **pzErr){
1644 int rc = SQLITE_OK;
1645 int nMax =0;
1646 struct IdxRemCtx *pCtx = 0;
1647 struct IdxSampleCtx samplectx;
1648 int i;
1649 i64 iPrev = -100000;
1650 sqlite3_stmt *pAllIndex = 0;
1651 sqlite3_stmt *pIndexXInfo = 0;
1652 sqlite3_stmt *pWrite = 0;
1653
1654 const char *zAllIndex =
1655 "SELECT s.rowid, s.name, l.name FROM "
1656 " sqlite_schema AS s, "
1657 " pragma_index_list(s.name) AS l "
1658 "WHERE s.type = 'table'";
1659 const char *zIndexXInfo =
1660 "SELECT name, coll FROM pragma_index_xinfo(?) WHERE key";
1661 const char *zWrite = "INSERT INTO sqlite_stat1 VALUES(?, ?, ?)";
1662
1663 /* If iSample==0, no sqlite_stat1 data is required. */
1664 if( p->iSample==0 ) return SQLITE_OK;
1665
1666 rc = idxLargestIndex(p->dbm, &nMax, pzErr);
1667 if( nMax<=0 || rc!=SQLITE_OK ) return rc;
1668
1669 rc = sqlite3_exec(p->dbm, "ANALYZE; PRAGMA writable_schema=1", 0, 0, 0);
1670
1671 if( rc==SQLITE_OK ){
1672 int nByte = sizeof(struct IdxRemCtx) + (sizeof(struct IdxRemSlot) * nMax);
1673 pCtx = (struct IdxRemCtx*)idxMalloc(&rc, nByte);
1674 }
1675
1676 if( rc==SQLITE_OK ){
1677 sqlite3 *dbrem = (p->iSample==100 ? p->db : p->dbv);
1678 rc = sqlite3_create_function(
1679 dbrem, "rem", 2, SQLITE_UTF8, (void*)pCtx, idxRemFunc, 0, 0
1680 );
1681 }
1682 if( rc==SQLITE_OK ){
1683 rc = sqlite3_create_function(
1684 p->db, "sample", 0, SQLITE_UTF8, (void*)&samplectx, idxSampleFunc, 0, 0
1685 );
1686 }
1687
1688 if( rc==SQLITE_OK ){
1689 pCtx->nSlot = nMax+1;
1690 rc = idxPrepareStmt(p->dbm, &pAllIndex, pzErr, zAllIndex);
1691 }
1692 if( rc==SQLITE_OK ){
1693 rc = idxPrepareStmt(p->dbm, &pIndexXInfo, pzErr, zIndexXInfo);
1694 }
1695 if( rc==SQLITE_OK ){
1696 rc = idxPrepareStmt(p->dbm, &pWrite, pzErr, zWrite);
1697 }
1698
1699 while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pAllIndex) ){
1700 i64 iRowid = sqlite3_column_int64(pAllIndex, 0);
1701 const char *zTab = (const char*)sqlite3_column_text(pAllIndex, 1);
1702 const char *zIdx = (const char*)sqlite3_column_text(pAllIndex, 2);
1703 if( p->iSample<100 && iPrev!=iRowid ){
1704 samplectx.target = (double)p->iSample / 100.0;
1705 samplectx.iTarget = p->iSample;
1706 samplectx.nRow = 0.0;
1707 samplectx.nRet = 0.0;
1708 rc = idxBuildSampleTable(p, zTab);
1709 if( rc!=SQLITE_OK ) break;
1710 }
1711 rc = idxPopulateOneStat1(p, pIndexXInfo, pWrite, zTab, zIdx, pzErr);
1712 iPrev = iRowid;
1713 }
1714 if( rc==SQLITE_OK && p->iSample<100 ){
1715 rc = sqlite3_exec(p->dbv,
1716 "DROP TABLE IF EXISTS temp." UNIQUE_TABLE_NAME, 0,0,0
1717 );
1718 }
1719
1720 idxFinalize(&rc, pAllIndex);
1721 idxFinalize(&rc, pIndexXInfo);
1722 idxFinalize(&rc, pWrite);
1723
1724 if( pCtx ){
1725 for(i=0; i<pCtx->nSlot; i++){
1726 sqlite3_free(pCtx->aSlot[i].z);
1727 }
1728 sqlite3_free(pCtx);
1729 }
1730
1731 if( rc==SQLITE_OK ){
1732 rc = sqlite3_exec(p->dbm, "ANALYZE sqlite_schema", 0, 0, 0);
1733 }
1734
1735 sqlite3_exec(p->db, "DROP TABLE IF EXISTS temp."UNIQUE_TABLE_NAME,0,0,0);
1736 return rc;
1737 }
1738
1739 /*
1740 ** Allocate a new sqlite3expert object.
1741 */
sqlite3_expert_new(sqlite3 * db,char ** pzErrmsg)1742 sqlite3expert *sqlite3_expert_new(sqlite3 *db, char **pzErrmsg){
1743 int rc = SQLITE_OK;
1744 sqlite3expert *pNew;
1745
1746 pNew = (sqlite3expert*)idxMalloc(&rc, sizeof(sqlite3expert));
1747
1748 /* Open two in-memory databases to work with. The "vtab database" (dbv)
1749 ** will contain a virtual table corresponding to each real table in
1750 ** the user database schema, and a copy of each view. It is used to
1751 ** collect information regarding the WHERE, ORDER BY and other clauses
1752 ** of the user's query.
1753 */
1754 if( rc==SQLITE_OK ){
1755 pNew->db = db;
1756 pNew->iSample = 100;
1757 rc = sqlite3_open(":memory:", &pNew->dbv);
1758 }
1759 if( rc==SQLITE_OK ){
1760 rc = sqlite3_open(":memory:", &pNew->dbm);
1761 if( rc==SQLITE_OK ){
1762 sqlite3_db_config(pNew->dbm, SQLITE_DBCONFIG_TRIGGER_EQP, 1, (int*)0);
1763 }
1764 }
1765
1766
1767 /* Copy the entire schema of database [db] into [dbm]. */
1768 if( rc==SQLITE_OK ){
1769 sqlite3_stmt *pSql;
1770 rc = idxPrintfPrepareStmt(pNew->db, &pSql, pzErrmsg,
1771 "SELECT sql FROM sqlite_schema WHERE name NOT LIKE 'sqlite_%%'"
1772 " AND sql NOT LIKE 'CREATE VIRTUAL %%'"
1773 );
1774 while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSql) ){
1775 const char *zSql = (const char*)sqlite3_column_text(pSql, 0);
1776 rc = sqlite3_exec(pNew->dbm, zSql, 0, 0, pzErrmsg);
1777 }
1778 idxFinalize(&rc, pSql);
1779 }
1780
1781 /* Create the vtab schema */
1782 if( rc==SQLITE_OK ){
1783 rc = idxCreateVtabSchema(pNew, pzErrmsg);
1784 }
1785
1786 /* Register the auth callback with dbv */
1787 if( rc==SQLITE_OK ){
1788 sqlite3_set_authorizer(pNew->dbv, idxAuthCallback, (void*)pNew);
1789 }
1790
1791 /* If an error has occurred, free the new object and reutrn NULL. Otherwise,
1792 ** return the new sqlite3expert handle. */
1793 if( rc!=SQLITE_OK ){
1794 sqlite3_expert_destroy(pNew);
1795 pNew = 0;
1796 }
1797 return pNew;
1798 }
1799
1800 /*
1801 ** Configure an sqlite3expert object.
1802 */
sqlite3_expert_config(sqlite3expert * p,int op,...)1803 int sqlite3_expert_config(sqlite3expert *p, int op, ...){
1804 int rc = SQLITE_OK;
1805 va_list ap;
1806 va_start(ap, op);
1807 switch( op ){
1808 case EXPERT_CONFIG_SAMPLE: {
1809 int iVal = va_arg(ap, int);
1810 if( iVal<0 ) iVal = 0;
1811 if( iVal>100 ) iVal = 100;
1812 p->iSample = iVal;
1813 break;
1814 }
1815 default:
1816 rc = SQLITE_NOTFOUND;
1817 break;
1818 }
1819
1820 va_end(ap);
1821 return rc;
1822 }
1823
1824 /*
1825 ** Add an SQL statement to the analysis.
1826 */
sqlite3_expert_sql(sqlite3expert * p,const char * zSql,char ** pzErr)1827 int sqlite3_expert_sql(
1828 sqlite3expert *p, /* From sqlite3_expert_new() */
1829 const char *zSql, /* SQL statement to add */
1830 char **pzErr /* OUT: Error message (if any) */
1831 ){
1832 IdxScan *pScanOrig = p->pScan;
1833 IdxStatement *pStmtOrig = p->pStatement;
1834 int rc = SQLITE_OK;
1835 const char *zStmt = zSql;
1836
1837 if( p->bRun ) return SQLITE_MISUSE;
1838
1839 while( rc==SQLITE_OK && zStmt && zStmt[0] ){
1840 sqlite3_stmt *pStmt = 0;
1841 rc = sqlite3_prepare_v2(p->dbv, zStmt, -1, &pStmt, &zStmt);
1842 if( rc==SQLITE_OK ){
1843 if( pStmt ){
1844 IdxStatement *pNew;
1845 const char *z = sqlite3_sql(pStmt);
1846 int n = STRLEN(z);
1847 pNew = (IdxStatement*)idxMalloc(&rc, sizeof(IdxStatement) + n+1);
1848 if( rc==SQLITE_OK ){
1849 pNew->zSql = (char*)&pNew[1];
1850 memcpy(pNew->zSql, z, n+1);
1851 pNew->pNext = p->pStatement;
1852 if( p->pStatement ) pNew->iId = p->pStatement->iId+1;
1853 p->pStatement = pNew;
1854 }
1855 sqlite3_finalize(pStmt);
1856 }
1857 }else{
1858 idxDatabaseError(p->dbv, pzErr);
1859 }
1860 }
1861
1862 if( rc!=SQLITE_OK ){
1863 idxScanFree(p->pScan, pScanOrig);
1864 idxStatementFree(p->pStatement, pStmtOrig);
1865 p->pScan = pScanOrig;
1866 p->pStatement = pStmtOrig;
1867 }
1868
1869 return rc;
1870 }
1871
sqlite3_expert_analyze(sqlite3expert * p,char ** pzErr)1872 int sqlite3_expert_analyze(sqlite3expert *p, char **pzErr){
1873 int rc;
1874 IdxHashEntry *pEntry;
1875
1876 /* Do trigger processing to collect any extra IdxScan structures */
1877 rc = idxProcessTriggers(p, pzErr);
1878
1879 /* Create candidate indexes within the in-memory database file */
1880 if( rc==SQLITE_OK ){
1881 rc = idxCreateCandidates(p);
1882 }
1883
1884 /* Generate the stat1 data */
1885 if( rc==SQLITE_OK ){
1886 rc = idxPopulateStat1(p, pzErr);
1887 }
1888
1889 /* Formulate the EXPERT_REPORT_CANDIDATES text */
1890 for(pEntry=p->hIdx.pFirst; pEntry; pEntry=pEntry->pNext){
1891 p->zCandidates = idxAppendText(&rc, p->zCandidates,
1892 "%s;%s%s\n", pEntry->zVal,
1893 pEntry->zVal2 ? " -- stat1: " : "", pEntry->zVal2
1894 );
1895 }
1896
1897 /* Figure out which of the candidate indexes are preferred by the query
1898 ** planner and report the results to the user. */
1899 if( rc==SQLITE_OK ){
1900 rc = idxFindIndexes(p, pzErr);
1901 }
1902
1903 if( rc==SQLITE_OK ){
1904 p->bRun = 1;
1905 }
1906 return rc;
1907 }
1908
1909 /*
1910 ** Return the total number of statements that have been added to this
1911 ** sqlite3expert using sqlite3_expert_sql().
1912 */
sqlite3_expert_count(sqlite3expert * p)1913 int sqlite3_expert_count(sqlite3expert *p){
1914 int nRet = 0;
1915 if( p->pStatement ) nRet = p->pStatement->iId+1;
1916 return nRet;
1917 }
1918
1919 /*
1920 ** Return a component of the report.
1921 */
sqlite3_expert_report(sqlite3expert * p,int iStmt,int eReport)1922 const char *sqlite3_expert_report(sqlite3expert *p, int iStmt, int eReport){
1923 const char *zRet = 0;
1924 IdxStatement *pStmt;
1925
1926 if( p->bRun==0 ) return 0;
1927 for(pStmt=p->pStatement; pStmt && pStmt->iId!=iStmt; pStmt=pStmt->pNext);
1928 switch( eReport ){
1929 case EXPERT_REPORT_SQL:
1930 if( pStmt ) zRet = pStmt->zSql;
1931 break;
1932 case EXPERT_REPORT_INDEXES:
1933 if( pStmt ) zRet = pStmt->zIdx;
1934 break;
1935 case EXPERT_REPORT_PLAN:
1936 if( pStmt ) zRet = pStmt->zEQP;
1937 break;
1938 case EXPERT_REPORT_CANDIDATES:
1939 zRet = p->zCandidates;
1940 break;
1941 }
1942 return zRet;
1943 }
1944
1945 /*
1946 ** Free an sqlite3expert object.
1947 */
sqlite3_expert_destroy(sqlite3expert * p)1948 void sqlite3_expert_destroy(sqlite3expert *p){
1949 if( p ){
1950 sqlite3_close(p->dbm);
1951 sqlite3_close(p->dbv);
1952 idxScanFree(p->pScan, 0);
1953 idxStatementFree(p->pStatement, 0);
1954 idxTableFree(p->pTable);
1955 idxWriteFree(p->pWrite);
1956 idxHashClear(&p->hIdx);
1957 sqlite3_free(p->zCandidates);
1958 sqlite3_free(p);
1959 }
1960 }
1961
1962 #endif /* ifndef SQLITE_OMIT_VIRTUALTABLE */
1963