Debian Codegraph @

ext/expert/sqlite3expert.c

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