?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
Test case for the assert() problem fixed by [9ba3cff9d48fe8fc].
[sqlite.git] / ext / expert / sqlite3expert.c
blob276c2cc9fe9e6adcd624455feff4e035dee7494c
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, int nByte){
176 void *pRet;
177 assert( *pRc==SQLITE_OK );
178 assert( nByte>0 );
179 pRet = sqlite3_malloc(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) + nKey+1 + 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 int n = STRLEN(zIn);
382 char *zRet = sqlite3_malloc(n);
383
384 assert( zIn[0]=='\'' );
385 assert( zIn[n-1]=='\'' );
386
387 if( zRet ){
388 int iOut = 0;
389 int 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 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 int 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 int nIn = zIn ? STRLEN(zIn) : 0;
795 int 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_malloc(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 static int idxCreateVtabSchema(sqlite3expert *p, char **pzErrmsg){
1397 int rc = idxRegisterVtab(p);
1398 sqlite3_stmt *pSchema = 0;
1399
1400 /* For each table in the main db schema:
1401 **
1402 ** 1) Add an entry to the p->pTable list, and
1403 ** 2) Create the equivalent virtual table in dbv.
1404 */
1405 rc = idxPrepareStmt(p->db, &pSchema, pzErrmsg,
1406 "SELECT type, name, sql, 1 FROM sqlite_schema "
1407 "WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%%' "
1408 " UNION ALL "
1409 "SELECT type, name, sql, 2 FROM sqlite_schema "
1410 "WHERE type = 'trigger'"
1411 " AND tbl_name IN(SELECT name FROM sqlite_schema WHERE type = 'view') "
1412 "ORDER BY 4, 1"
1413 );
1414 while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSchema) ){
1415 const char *zType = (const char*)sqlite3_column_text(pSchema, 0);
1416 const char *zName = (const char*)sqlite3_column_text(pSchema, 1);
1417 const char *zSql = (const char*)sqlite3_column_text(pSchema, 2);
1418
1419 if( zType==0 || zName==0 ) continue;
1420 if( zType[0]=='v' || zType[1]=='r' ){
1421 if( zSql ) rc = sqlite3_exec(p->dbv, zSql, 0, 0, pzErrmsg);
1422 }else{
1423 IdxTable *pTab;
1424 rc = idxGetTableInfo(p->db, zName, &pTab, pzErrmsg);
1425 if( rc==SQLITE_OK ){
1426 int i;
1427 char *zInner = 0;
1428 char *zOuter = 0;
1429 pTab->pNext = p->pTable;
1430 p->pTable = pTab;
1431
1432 /* The statement the vtab will pass to sqlite3_declare_vtab() */
1433 zInner = idxAppendText(&rc, 0, "CREATE TABLE x(");
1434 for(i=0; i<pTab->nCol; i++){
1435 zInner = idxAppendText(&rc, zInner, "%s%Q COLLATE %s",
1436 (i==0 ? "" : ", "), pTab->aCol[i].zName, pTab->aCol[i].zColl
1437 );
1438 }
1439 zInner = idxAppendText(&rc, zInner, ")");
1440
1441 /* The CVT statement to create the vtab */
1442 zOuter = idxAppendText(&rc, 0,
1443 "CREATE VIRTUAL TABLE %Q USING expert(%Q)", zName, zInner
1444 );
1445 if( rc==SQLITE_OK ){
1446 rc = sqlite3_exec(p->dbv, zOuter, 0, 0, pzErrmsg);
1447 }
1448 sqlite3_free(zInner);
1449 sqlite3_free(zOuter);
1450 }
1451 }
1452 }
1453 idxFinalize(&rc, pSchema);
1454 return rc;
1455 }
1456
1457 struct IdxSampleCtx {
1458 int iTarget;
1459 double target; /* Target nRet/nRow value */
1460 double nRow; /* Number of rows seen */
1461 double nRet; /* Number of rows returned */
1462 };
1463
1464 static void idxSampleFunc(
1465 sqlite3_context *pCtx,
1466 int argc,
1467 sqlite3_value **argv
1468 ){
1469 struct IdxSampleCtx *p = (struct IdxSampleCtx*)sqlite3_user_data(pCtx);
1470 int bRet;
1471
1472 (void)argv;
1473 assert( argc==0 );
1474 if( p->nRow==0.0 ){
1475 bRet = 1;
1476 }else{
1477 bRet = (p->nRet / p->nRow) <= p->target;
1478 if( bRet==0 ){
1479 unsigned short rnd;
1480 sqlite3_randomness(2, (void*)&rnd);
1481 bRet = ((int)rnd % 100) <= p->iTarget;
1482 }
1483 }
1484
1485 sqlite3_result_int(pCtx, bRet);
1486 p->nRow += 1.0;
1487 p->nRet += (double)bRet;
1488 }
1489
1490 struct IdxRemCtx {
1491 int nSlot;
1492 struct IdxRemSlot {
1493 int eType; /* SQLITE_NULL, INTEGER, REAL, TEXT, BLOB */
1494 i64 iVal; /* SQLITE_INTEGER value */
1495 double rVal; /* SQLITE_FLOAT value */
1496 int nByte; /* Bytes of space allocated at z */
1497 int n; /* Size of buffer z */
1498 char *z; /* SQLITE_TEXT/BLOB value */
1499 } aSlot[1];
1500 };
1501
1502 /*
1503 ** Implementation of scalar function rem().
1504 */
1505 static void idxRemFunc(
1506 sqlite3_context *pCtx,
1507 int argc,
1508 sqlite3_value **argv
1509 ){
1510 struct IdxRemCtx *p = (struct IdxRemCtx*)sqlite3_user_data(pCtx);
1511 struct IdxRemSlot *pSlot;
1512 int iSlot;
1513 assert( argc==2 );
1514
1515 iSlot = sqlite3_value_int(argv[0]);
1516 assert( iSlot<=p->nSlot );
1517 pSlot = &p->aSlot[iSlot];
1518
1519 switch( pSlot->eType ){
1520 case SQLITE_NULL:
1521 /* no-op */
1522 break;
1523
1524 case SQLITE_INTEGER:
1525 sqlite3_result_int64(pCtx, pSlot->iVal);
1526 break;
1527
1528 case SQLITE_FLOAT:
1529 sqlite3_result_double(pCtx, pSlot->rVal);
1530 break;
1531
1532 case SQLITE_BLOB:
1533 sqlite3_result_blob(pCtx, pSlot->z, pSlot->n, SQLITE_TRANSIENT);
1534 break;
1535
1536 case SQLITE_TEXT:
1537 sqlite3_result_text(pCtx, pSlot->z, pSlot->n, SQLITE_TRANSIENT);
1538 break;
1539 }
1540
1541 pSlot->eType = sqlite3_value_type(argv[1]);
1542 switch( pSlot->eType ){
1543 case SQLITE_NULL:
1544 /* no-op */
1545 break;
1546
1547 case SQLITE_INTEGER:
1548 pSlot->iVal = sqlite3_value_int64(argv[1]);
1549 break;
1550
1551 case SQLITE_FLOAT:
1552 pSlot->rVal = sqlite3_value_double(argv[1]);
1553 break;
1554
1555 case SQLITE_BLOB:
1556 case SQLITE_TEXT: {
1557 int nByte = sqlite3_value_bytes(argv[1]);
1558 const void *pData = 0;
1559 if( nByte>pSlot->nByte ){
1560 char *zNew = (char*)sqlite3_realloc(pSlot->z, nByte*2);
1561 if( zNew==0 ){
1562 sqlite3_result_error_nomem(pCtx);
1563 return;
1564 }
1565 pSlot->nByte = nByte*2;
1566 pSlot->z = zNew;
1567 }
1568 pSlot->n = nByte;
1569 if( pSlot->eType==SQLITE_BLOB ){
1570 pData = sqlite3_value_blob(argv[1]);
1571 if( pData ) memcpy(pSlot->z, pData, nByte);
1572 }else{
1573 pData = sqlite3_value_text(argv[1]);
1574 memcpy(pSlot->z, pData, nByte);
1575 }
1576 break;
1577 }
1578 }
1579 }
1580
1581 static int idxLargestIndex(sqlite3 *db, int *pnMax, char **pzErr){
1582 int rc = SQLITE_OK;
1583 const char *zMax =
1584 "SELECT max(i.seqno) FROM "
1585 " sqlite_schema AS s, "
1586 " pragma_index_list(s.name) AS l, "
1587 " pragma_index_info(l.name) AS i "
1588 "WHERE s.type = 'table'";
1589 sqlite3_stmt *pMax = 0;
1590
1591 *pnMax = 0;
1592 rc = idxPrepareStmt(db, &pMax, pzErr, zMax);
1593 if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pMax) ){
1594 *pnMax = sqlite3_column_int(pMax, 0) + 1;
1595 }
1596 idxFinalize(&rc, pMax);
1597
1598 return rc;
1599 }
1600
1601 static int idxPopulateOneStat1(
1602 sqlite3expert *p,
1603 sqlite3_stmt *pIndexXInfo,
1604 sqlite3_stmt *pWriteStat,
1605 const char *zTab,
1606 const char *zIdx,
1607 char **pzErr
1608 ){
1609 char *zCols = 0;
1610 char *zOrder = 0;
1611 char *zQuery = 0;
1612 int nCol = 0;
1613 int i;
1614 sqlite3_stmt *pQuery = 0;
1615 int *aStat = 0;
1616 int rc = SQLITE_OK;
1617
1618 assert( p->iSample>0 );
1619
1620 /* Formulate the query text */
1621 sqlite3_bind_text(pIndexXInfo, 1, zIdx, -1, SQLITE_STATIC);
1622 while( SQLITE_OK==rc && SQLITE_ROW==sqlite3_step(pIndexXInfo) ){
1623 const char *zComma = zCols==0 ? "" : ", ";
1624 const char *zName = (const char*)sqlite3_column_text(pIndexXInfo, 0);
1625 const char *zColl = (const char*)sqlite3_column_text(pIndexXInfo, 1);
1626 zCols = idxAppendText(&rc, zCols,
1627 "%sx.%Q IS rem(%d, x.%Q) COLLATE %s", zComma, zName, nCol, zName, zColl
1628 );
1629 zOrder = idxAppendText(&rc, zOrder, "%s%d", zComma, ++nCol);
1630 }
1631 sqlite3_reset(pIndexXInfo);
1632 if( rc==SQLITE_OK ){
1633 if( p->iSample==100 ){
1634 zQuery = sqlite3_mprintf(
1635 "SELECT %s FROM %Q x ORDER BY %s", zCols, zTab, zOrder
1636 );
1637 }else{
1638 zQuery = sqlite3_mprintf(
1639 "SELECT %s FROM temp."UNIQUE_TABLE_NAME" x ORDER BY %s", zCols, zOrder
1640 );
1641 }
1642 }
1643 sqlite3_free(zCols);
1644 sqlite3_free(zOrder);
1645
1646 /* Formulate the query text */
1647 if( rc==SQLITE_OK ){
1648 sqlite3 *dbrem = (p->iSample==100 ? p->db : p->dbv);
1649 rc = idxPrepareStmt(dbrem, &pQuery, pzErr, zQuery);
1650 }
1651 sqlite3_free(zQuery);
1652
1653 if( rc==SQLITE_OK ){
1654 aStat = (int*)idxMalloc(&rc, sizeof(int)*(nCol+1));
1655 }
1656 if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pQuery) ){
1657 IdxHashEntry *pEntry;
1658 char *zStat = 0;
1659 for(i=0; i<=nCol; i++) aStat[i] = 1;
1660 while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pQuery) ){
1661 aStat[0]++;
1662 for(i=0; i<nCol; i++){
1663 if( sqlite3_column_int(pQuery, i)==0 ) break;
1664 }
1665 for(/*no-op*/; i<nCol; i++){
1666 aStat[i+1]++;
1667 }
1668 }
1669
1670 if( rc==SQLITE_OK ){
1671 int s0 = aStat[0];
1672 zStat = sqlite3_mprintf("%d", s0);
1673 if( zStat==0 ) rc = SQLITE_NOMEM;
1674 for(i=1; rc==SQLITE_OK && i<=nCol; i++){
1675 zStat = idxAppendText(&rc, zStat, " %d", (s0+aStat[i]/2) / aStat[i]);
1676 }
1677 }
1678
1679 if( rc==SQLITE_OK ){
1680 sqlite3_bind_text(pWriteStat, 1, zTab, -1, SQLITE_STATIC);
1681 sqlite3_bind_text(pWriteStat, 2, zIdx, -1, SQLITE_STATIC);
1682 sqlite3_bind_text(pWriteStat, 3, zStat, -1, SQLITE_STATIC);
1683 sqlite3_step(pWriteStat);
1684 rc = sqlite3_reset(pWriteStat);
1685 }
1686
1687 pEntry = idxHashFind(&p->hIdx, zIdx, STRLEN(zIdx));
1688 if( pEntry ){
1689 assert( pEntry->zVal2==0 );
1690 pEntry->zVal2 = zStat;
1691 }else{
1692 sqlite3_free(zStat);
1693 }
1694 }
1695 sqlite3_free(aStat);
1696 idxFinalize(&rc, pQuery);
1697
1698 return rc;
1699 }
1700
1701 static int idxBuildSampleTable(sqlite3expert *p, const char *zTab){
1702 int rc;
1703 char *zSql;
1704
1705 rc = sqlite3_exec(p->dbv,"DROP TABLE IF EXISTS temp."UNIQUE_TABLE_NAME,0,0,0);
1706 if( rc!=SQLITE_OK ) return rc;
1707
1708 zSql = sqlite3_mprintf(
1709 "CREATE TABLE temp." UNIQUE_TABLE_NAME " AS SELECT * FROM %Q", zTab
1710 );
1711 if( zSql==0 ) return SQLITE_NOMEM;
1712 rc = sqlite3_exec(p->dbv, zSql, 0, 0, 0);
1713 sqlite3_free(zSql);
1714
1715 return rc;
1716 }
1717
1718 /*
1719 ** This function is called as part of sqlite3_expert_analyze(). Candidate
1720 ** indexes have already been created in database sqlite3expert.dbm, this
1721 ** function populates sqlite_stat1 table in the same database.
1722 **
1723 ** The stat1 data is generated by querying the
1724 */
1725 static int idxPopulateStat1(sqlite3expert *p, char **pzErr){
1726 int rc = SQLITE_OK;
1727 int nMax =0;
1728 struct IdxRemCtx *pCtx = 0;
1729 struct IdxSampleCtx samplectx;
1730 int i;
1731 i64 iPrev = -100000;
1732 sqlite3_stmt *pAllIndex = 0;
1733 sqlite3_stmt *pIndexXInfo = 0;
1734 sqlite3_stmt *pWrite = 0;
1735
1736 const char *zAllIndex =
1737 "SELECT s.rowid, s.name, l.name FROM "
1738 " sqlite_schema AS s, "
1739 " pragma_index_list(s.name) AS l "
1740 "WHERE s.type = 'table'";
1741 const char *zIndexXInfo =
1742 "SELECT name, coll FROM pragma_index_xinfo(?) WHERE key";
1743 const char *zWrite = "INSERT INTO sqlite_stat1 VALUES(?, ?, ?)";
1744
1745 /* If iSample==0, no sqlite_stat1 data is required. */
1746 if( p->iSample==0 ) return SQLITE_OK;
1747
1748 rc = idxLargestIndex(p->dbm, &nMax, pzErr);
1749 if( nMax<=0 || rc!=SQLITE_OK ) return rc;
1750
1751 rc = sqlite3_exec(p->dbm, "ANALYZE; PRAGMA writable_schema=1", 0, 0, 0);
1752
1753 if( rc==SQLITE_OK ){
1754 int nByte = sizeof(struct IdxRemCtx) + (sizeof(struct IdxRemSlot) * nMax);
1755 pCtx = (struct IdxRemCtx*)idxMalloc(&rc, nByte);
1756 }
1757
1758 if( rc==SQLITE_OK ){
1759 sqlite3 *dbrem = (p->iSample==100 ? p->db : p->dbv);
1760 rc = sqlite3_create_function(
1761 dbrem, "rem", 2, SQLITE_UTF8, (void*)pCtx, idxRemFunc, 0, 0
1762 );
1763 }
1764 if( rc==SQLITE_OK ){
1765 rc = sqlite3_create_function(
1766 p->db, "sample", 0, SQLITE_UTF8, (void*)&samplectx, idxSampleFunc, 0, 0
1767 );
1768 }
1769
1770 if( rc==SQLITE_OK ){
1771 pCtx->nSlot = nMax+1;
1772 rc = idxPrepareStmt(p->dbm, &pAllIndex, pzErr, zAllIndex);
1773 }
1774 if( rc==SQLITE_OK ){
1775 rc = idxPrepareStmt(p->dbm, &pIndexXInfo, pzErr, zIndexXInfo);
1776 }
1777 if( rc==SQLITE_OK ){
1778 rc = idxPrepareStmt(p->dbm, &pWrite, pzErr, zWrite);
1779 }
1780
1781 while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pAllIndex) ){
1782 i64 iRowid = sqlite3_column_int64(pAllIndex, 0);
1783 const char *zTab = (const char*)sqlite3_column_text(pAllIndex, 1);
1784 const char *zIdx = (const char*)sqlite3_column_text(pAllIndex, 2);
1785 if( zTab==0 || zIdx==0 ) continue;
1786 if( p->iSample<100 && iPrev!=iRowid ){
1787 samplectx.target = (double)p->iSample / 100.0;
1788 samplectx.iTarget = p->iSample;
1789 samplectx.nRow = 0.0;
1790 samplectx.nRet = 0.0;
1791 rc = idxBuildSampleTable(p, zTab);
1792 if( rc!=SQLITE_OK ) break;
1793 }
1794 rc = idxPopulateOneStat1(p, pIndexXInfo, pWrite, zTab, zIdx, pzErr);
1795 iPrev = iRowid;
1796 }
1797 if( rc==SQLITE_OK && p->iSample<100 ){
1798 rc = sqlite3_exec(p->dbv,
1799 "DROP TABLE IF EXISTS temp." UNIQUE_TABLE_NAME, 0,0,0
1800 );
1801 }
1802
1803 idxFinalize(&rc, pAllIndex);
1804 idxFinalize(&rc, pIndexXInfo);
1805 idxFinalize(&rc, pWrite);
1806
1807 if( pCtx ){
1808 for(i=0; i<pCtx->nSlot; i++){
1809 sqlite3_free(pCtx->aSlot[i].z);
1810 }
1811 sqlite3_free(pCtx);
1812 }
1813
1814 if( rc==SQLITE_OK ){
1815 rc = sqlite3_exec(p->dbm, "ANALYZE sqlite_schema", 0, 0, 0);
1816 }
1817
1818 sqlite3_exec(p->db, "DROP TABLE IF EXISTS temp."UNIQUE_TABLE_NAME,0,0,0);
1819 return rc;
1820 }
1821
1822 /*
1823 ** Define and possibly pretend to use a useless collation sequence.
1824 ** This pretense allows expert to accept SQL using custom collations.
1825 */
1826 int dummyCompare(void *up1, int up2, const void *up3, int up4, const void *up5){
1827 (void)up1;
1828 (void)up2;
1829 (void)up3;
1830 (void)up4;
1831 (void)up5;
1832 assert(0); /* VDBE should never be run. */
1833 return 0;
1834 }
1835 /* And a callback to register above upon actual need */
1836 void useDummyCS(void *up1, sqlite3 *db, int etr, const char *zName){
1837 (void)up1;
1838 sqlite3_create_collation_v2(db, zName, etr, 0, dummyCompare, 0);
1839 }
1840
1841 #if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) \
1842 && !defined(SQLITE_OMIT_INTROSPECTION_PRAGMAS)
1843 /*
1844 ** dummy functions for no-op implementation of UDFs during expert's work
1845 */
1846 void dummyUDF(sqlite3_context *up1, int up2, sqlite3_value **up3){
1847 (void)up1;
1848 (void)up2;
1849 (void)up3;
1850 assert(0); /* VDBE should never be run. */
1851 }
1852 void dummyUDFvalue(sqlite3_context *up1){
1853 (void)up1;
1854 assert(0); /* VDBE should never be run. */
1855 }
1856
1857 /*
1858 ** Register UDFs from user database with another.
1859 */
1860 int registerUDFs(sqlite3 *dbSrc, sqlite3 *dbDst){
1861 sqlite3_stmt *pStmt;
1862 int rc = sqlite3_prepare_v2(dbSrc,
1863 "SELECT name,type,enc,narg,flags "
1864 "FROM pragma_function_list() "
1865 "WHERE builtin==0", -1, &pStmt, 0);
1866 if( rc==SQLITE_OK ){
1867 while( SQLITE_ROW==(rc = sqlite3_step(pStmt)) ){
1868 int nargs = sqlite3_column_int(pStmt,3);
1869 int flags = sqlite3_column_int(pStmt,4);
1870 const char *name = (char*)sqlite3_column_text(pStmt,0);
1871 const char *type = (char*)sqlite3_column_text(pStmt,1);
1872 const char *enc = (char*)sqlite3_column_text(pStmt,2);
1873 if( name==0 || type==0 || enc==0 ){
1874 /* no-op. Only happens on OOM */
1875 }else{
1876 int ienc = SQLITE_UTF8;
1877 int rcf = SQLITE_ERROR;
1878 if( strcmp(enc,"utf16le")==0 ) ienc = SQLITE_UTF16LE;
1879 else if( strcmp(enc,"utf16be")==0 ) ienc = SQLITE_UTF16BE;
1880 ienc |= (flags & (SQLITE_DETERMINISTIC|SQLITE_DIRECTONLY));
1881 if( strcmp(type,"w")==0 ){
1882 rcf = sqlite3_create_window_function(dbDst,name,nargs,ienc,0,
1883 dummyUDF,dummyUDFvalue,0,0,0);
1884 }else if( strcmp(type,"a")==0 ){
1885 rcf = sqlite3_create_function(dbDst,name,nargs,ienc,0,
1886 0,dummyUDF,dummyUDFvalue);
1887 }else if( strcmp(type,"s")==0 ){
1888 rcf = sqlite3_create_function(dbDst,name,nargs,ienc,0,
1889 dummyUDF,0,0);
1890 }
1891 if( rcf!=SQLITE_OK ){
1892 rc = rcf;
1893 break;
1894 }
1895 }
1896 }
1897 sqlite3_finalize(pStmt);
1898 if( rc==SQLITE_DONE ) rc = SQLITE_OK;
1899 }
1900 return rc;
1901 }
1902 #endif
1903
1904 /*
1905 ** Allocate a new sqlite3expert object.
1906 */
1907 sqlite3expert *sqlite3_expert_new(sqlite3 *db, char **pzErrmsg){
1908 int rc = SQLITE_OK;
1909 sqlite3expert *pNew;
1910
1911 pNew = (sqlite3expert*)idxMalloc(&rc, sizeof(sqlite3expert));
1912
1913 /* Open two in-memory databases to work with. The "vtab database" (dbv)
1914 ** will contain a virtual table corresponding to each real table in
1915 ** the user database schema, and a copy of each view. It is used to
1916 ** collect information regarding the WHERE, ORDER BY and other clauses
1917 ** of the user's query.
1918 */
1919 if( rc==SQLITE_OK ){
1920 pNew->db = db;
1921 pNew->iSample = 100;
1922 rc = sqlite3_open(":memory:", &pNew->dbv);
1923 }
1924 if( rc==SQLITE_OK ){
1925 rc = sqlite3_open(":memory:", &pNew->dbm);
1926 if( rc==SQLITE_OK ){
1927 sqlite3_db_config(pNew->dbm, SQLITE_DBCONFIG_TRIGGER_EQP, 1, (int*)0);
1928 }
1929 }
1930
1931 /* Allow custom collations to be dealt with through prepare. */
1932 if( rc==SQLITE_OK ) rc = sqlite3_collation_needed(pNew->dbm,0,useDummyCS);
1933 if( rc==SQLITE_OK ) rc = sqlite3_collation_needed(pNew->dbv,0,useDummyCS);
1934
1935 #if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) \
1936 && !defined(SQLITE_OMIT_INTROSPECTION_PRAGMAS)
1937 /* Register UDFs from database [db] with [dbm] and [dbv]. */
1938 if( rc==SQLITE_OK ){
1939 rc = registerUDFs(pNew->db, pNew->dbm);
1940 }
1941 if( rc==SQLITE_OK ){
1942 rc = registerUDFs(pNew->db, pNew->dbv);
1943 }
1944 #endif
1945
1946 /* Copy the entire schema of database [db] into [dbm]. */
1947 if( rc==SQLITE_OK ){
1948 sqlite3_stmt *pSql = 0;
1949 rc = idxPrintfPrepareStmt(pNew->db, &pSql, pzErrmsg,
1950 "SELECT sql FROM sqlite_schema WHERE name NOT LIKE 'sqlite_%%'"
1951 " AND sql NOT LIKE 'CREATE VIRTUAL %%' ORDER BY rowid"
1952 );
1953 while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSql) ){
1954 const char *zSql = (const char*)sqlite3_column_text(pSql, 0);
1955 if( zSql ) rc = sqlite3_exec(pNew->dbm, zSql, 0, 0, pzErrmsg);
1956 }
1957 idxFinalize(&rc, pSql);
1958 }
1959
1960 /* Create the vtab schema */
1961 if( rc==SQLITE_OK ){
1962 rc = idxCreateVtabSchema(pNew, pzErrmsg);
1963 }
1964
1965 /* Register the auth callback with dbv */
1966 if( rc==SQLITE_OK ){
1967 sqlite3_set_authorizer(pNew->dbv, idxAuthCallback, (void*)pNew);
1968 }
1969
1970 /* If an error has occurred, free the new object and reutrn NULL. Otherwise,
1971 ** return the new sqlite3expert handle. */
1972 if( rc!=SQLITE_OK ){
1973 sqlite3_expert_destroy(pNew);
1974 pNew = 0;
1975 }
1976 return pNew;
1977 }
1978
1979 /*
1980 ** Configure an sqlite3expert object.
1981 */
1982 int sqlite3_expert_config(sqlite3expert *p, int op, ...){
1983 int rc = SQLITE_OK;
1984 va_list ap;
1985 va_start(ap, op);
1986 switch( op ){
1987 case EXPERT_CONFIG_SAMPLE: {
1988 int iVal = va_arg(ap, int);
1989 if( iVal<0 ) iVal = 0;
1990 if( iVal>100 ) iVal = 100;
1991 p->iSample = iVal;
1992 break;
1993 }
1994 default:
1995 rc = SQLITE_NOTFOUND;
1996 break;
1997 }
1998
1999 va_end(ap);
2000 return rc;
2001 }
2002
2003 /*
2004 ** Add an SQL statement to the analysis.
2005 */
2006 int sqlite3_expert_sql(
2007 sqlite3expert *p, /* From sqlite3_expert_new() */
2008 const char *zSql, /* SQL statement to add */
2009 char **pzErr /* OUT: Error message (if any) */
2010 ){
2011 IdxScan *pScanOrig = p->pScan;
2012 IdxStatement *pStmtOrig = p->pStatement;
2013 int rc = SQLITE_OK;
2014 const char *zStmt = zSql;
2015
2016 if( p->bRun ) return SQLITE_MISUSE;
2017
2018 while( rc==SQLITE_OK && zStmt && zStmt[0] ){
2019 sqlite3_stmt *pStmt = 0;
2020 /* Ensure that the provided statement compiles against user's DB. */
2021 rc = idxPrepareStmt(p->db, &pStmt, pzErr, zStmt);
2022 if( rc!=SQLITE_OK ) break;
2023 sqlite3_finalize(pStmt);
2024 rc = sqlite3_prepare_v2(p->dbv, zStmt, -1, &pStmt, &zStmt);
2025 if( rc==SQLITE_OK ){
2026 if( pStmt ){
2027 IdxStatement *pNew;
2028 const char *z = sqlite3_sql(pStmt);
2029 int n = STRLEN(z);
2030 pNew = (IdxStatement*)idxMalloc(&rc, sizeof(IdxStatement) + n+1);
2031 if( rc==SQLITE_OK ){
2032 pNew->zSql = (char*)&pNew[1];
2033 memcpy(pNew->zSql, z, n+1);
2034 pNew->pNext = p->pStatement;
2035 if( p->pStatement ) pNew->iId = p->pStatement->iId+1;
2036 p->pStatement = pNew;
2037 }
2038 sqlite3_finalize(pStmt);
2039 }
2040 }else{
2041 idxDatabaseError(p->dbv, pzErr);
2042 }
2043 }
2044
2045 if( rc!=SQLITE_OK ){
2046 idxScanFree(p->pScan, pScanOrig);
2047 idxStatementFree(p->pStatement, pStmtOrig);
2048 p->pScan = pScanOrig;
2049 p->pStatement = pStmtOrig;
2050 }
2051
2052 return rc;
2053 }
2054
2055 int sqlite3_expert_analyze(sqlite3expert *p, char **pzErr){
2056 int rc;
2057 IdxHashEntry *pEntry;
2058
2059 /* Do trigger processing to collect any extra IdxScan structures */
2060 rc = idxProcessTriggers(p, pzErr);
2061
2062 /* Create candidate indexes within the in-memory database file */
2063 if( rc==SQLITE_OK ){
2064 rc = idxCreateCandidates(p);
2065 }else if ( rc==SQLITE_BUSY_TIMEOUT ){
2066 if( pzErr )
2067 *pzErr = sqlite3_mprintf("Cannot find a unique index name to propose.");
2068 return rc;
2069 }
2070
2071 /* Generate the stat1 data */
2072 if( rc==SQLITE_OK ){
2073 rc = idxPopulateStat1(p, pzErr);
2074 }
2075
2076 /* Formulate the EXPERT_REPORT_CANDIDATES text */
2077 for(pEntry=p->hIdx.pFirst; pEntry; pEntry=pEntry->pNext){
2078 p->zCandidates = idxAppendText(&rc, p->zCandidates,
2079 "%s;%s%s\n", pEntry->zVal,
2080 pEntry->zVal2 ? " -- stat1: " : "", pEntry->zVal2
2081 );
2082 }
2083
2084 /* Figure out which of the candidate indexes are preferred by the query
2085 ** planner and report the results to the user. */
2086 if( rc==SQLITE_OK ){
2087 rc = idxFindIndexes(p, pzErr);
2088 }
2089
2090 if( rc==SQLITE_OK ){
2091 p->bRun = 1;
2092 }
2093 return rc;
2094 }
2095
2096 /*
2097 ** Return the total number of statements that have been added to this
2098 ** sqlite3expert using sqlite3_expert_sql().
2099 */
2100 int sqlite3_expert_count(sqlite3expert *p){
2101 int nRet = 0;
2102 if( p->pStatement ) nRet = p->pStatement->iId+1;
2103 return nRet;
2104 }
2105
2106 /*
2107 ** Return a component of the report.
2108 */
2109 const char *sqlite3_expert_report(sqlite3expert *p, int iStmt, int eReport){
2110 const char *zRet = 0;
2111 IdxStatement *pStmt;
2112
2113 if( p->bRun==0 ) return 0;
2114 for(pStmt=p->pStatement; pStmt && pStmt->iId!=iStmt; pStmt=pStmt->pNext);
2115 switch( eReport ){
2116 case EXPERT_REPORT_SQL:
2117 if( pStmt ) zRet = pStmt->zSql;
2118 break;
2119 case EXPERT_REPORT_INDEXES:
2120 if( pStmt ) zRet = pStmt->zIdx;
2121 break;
2122 case EXPERT_REPORT_PLAN:
2123 if( pStmt ) zRet = pStmt->zEQP;
2124 break;
2125 case EXPERT_REPORT_CANDIDATES:
2126 zRet = p->zCandidates;
2127 break;
2128 }
2129 return zRet;
2130 }
2131
2132 /*
2133 ** Free an sqlite3expert object.
2134 */
2135 void sqlite3_expert_destroy(sqlite3expert *p){
2136 if( p ){
2137 sqlite3_close(p->dbm);
2138 sqlite3_close(p->dbv);
2139 idxScanFree(p->pScan, 0);
2140 idxStatementFree(p->pStatement, 0);
2141 idxTableFree(p->pTable);
2142 idxWriteFree(p->pWrite);
2143 idxHashClear(&p->hIdx);
2144 sqlite3_free(p->zCandidates);
2145 sqlite3_free(p);
2146 }
2147 }
2148
2149 #endif /* ifndef SQLITE_OMIT_VIRTUALTABLE */
Unofficial git mirror of the SQLite sources