/ Check-in [305e19f9]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Refactor code to suggest indexes from the shell tool into an extension in ext/expert. Unfinished.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | schemalint
Files: files | file ages | folders
SHA3-256: 305e19f976ca064638a294e0817bf547ea745e1eb74746c5855514e6ced9c5fa
User & Date: dan 2017-04-07 20:14:22
Context
2017-04-08
17:41
Use hash tables instead of in-memory database tables for a few purposes in sqlite3expert.c. check-in: bf10e68d user: dan tags: schemalint
2017-04-07
20:14
Refactor code to suggest indexes from the shell tool into an extension in ext/expert. Unfinished. check-in: 305e19f9 user: dan tags: schemalint
2017-04-06
18:44
Changes to allow indexes to be recommended for queries on SQL views. check-in: 0884ff1d user: dan tags: schemalint
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added ext/expert/expert.c.

            1  +/*
            2  +** 2017 April 07
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +*************************************************************************
           12  +*/
           13  +
           14  +
           15  +#include <sqlite3.h>
           16  +#include <stdio.h>
           17  +#include <stdlib.h>
           18  +#include <string.h>
           19  +#include "sqlite3expert.h"
           20  +
           21  +
           22  +static void option_requires_argument(const char *zOpt){
           23  +  fprintf(stderr, "Option requires an argument: %s\n", zOpt);
           24  +  exit(-3);
           25  +}
           26  +
           27  +static void usage(char **argv){
           28  +  fprintf(stderr, "\n");
           29  +  fprintf(stderr, "Usage %s ?OPTIONS? DATABASE\n", argv[0]);
           30  +  fprintf(stderr, "\n");
           31  +  fprintf(stderr, "Options are:\n");
           32  +  fprintf(stderr, "  -sql SQL   (analyze SQL statements passed as argument)\n");
           33  +  fprintf(stderr, "  -file FILE (read SQL statements from file FILE)\n");
           34  +  exit(-1);
           35  +}
           36  +
           37  +static int readSqlFromFile(sqlite3expert *p, const char *zFile, char **pzErr){
           38  +  return SQLITE_OK;
           39  +}
           40  +
           41  +int main(int argc, char **argv){
           42  +  const char *zDb;
           43  +  int rc = 0;
           44  +  char *zErr = 0;
           45  +  int i;
           46  +
           47  +  sqlite3 *db = 0;
           48  +  sqlite3expert *p = 0;
           49  +
           50  +  if( argc<2 ) usage(argv);
           51  +  zDb = argv[argc-1];
           52  +  rc = sqlite3_open(zDb, &db);
           53  +  if( rc!=SQLITE_OK ){
           54  +    fprintf(stderr, "Cannot open db file: %s - %s\n", zDb, sqlite3_errmsg(db));
           55  +    exit(-2);
           56  +  }
           57  +
           58  +  p = sqlite3_expert_new(db, &zErr);
           59  +  if( p==0 ){
           60  +    fprintf(stderr, "Cannot run analysis: %s\n", zErr);
           61  +    rc = 1;
           62  +  }else{
           63  +    for(i=1; i<(argc-1); i++){
           64  +      char *zArg = argv[i];
           65  +      int nArg = strlen(zArg);
           66  +      if( nArg>=2 && 0==sqlite3_strnicmp(zArg, "-file", nArg) ){
           67  +        if( ++i==(argc-1) ) option_requires_argument("-file");
           68  +        rc = readSqlFromFile(p, argv[i], &zErr);
           69  +      }
           70  +
           71  +      else if( nArg>=2 && 0==sqlite3_strnicmp(zArg, "-sql", nArg) ){
           72  +        if( ++i==(argc-1) ) option_requires_argument("-sql");
           73  +        rc = sqlite3_expert_sql(p, argv[i], &zErr);
           74  +      }
           75  +
           76  +      else{
           77  +        usage(argv);
           78  +      }
           79  +    }
           80  +  }
           81  +
           82  +  if( rc==SQLITE_OK ){
           83  +    rc = sqlite3_expert_analyze(p, &zErr);
           84  +  }
           85  +
           86  +  if( rc==SQLITE_OK ){
           87  +    int nQuery = sqlite3_expert_count(p);
           88  +    for(i=0; i<nQuery; i++){
           89  +      const char *zSql = sqlite3_expert_report(p, i, EXPERT_REPORT_SQL);
           90  +      const char *zIdx = sqlite3_expert_report(p, i, EXPERT_REPORT_INDEXES);
           91  +      const char *zEQP = sqlite3_expert_report(p, i, EXPERT_REPORT_PLAN);
           92  +      fprintf(stdout, "-- query %d ----------------------------------\n", i+1);
           93  +      fprintf(stdout, "%s\n\n%s\n%s\n", zSql, zIdx, zEQP);
           94  +    }
           95  +  }else if( zErr ){
           96  +    fprintf(stderr, "Error: %s\n", zErr);
           97  +  }
           98  +
           99  +  sqlite3_expert_destroy(p);
          100  +  sqlite3_free(zErr);
          101  +  return rc;
          102  +}
          103  +
          104  +

Added ext/expert/sqlite3expert.c.

            1  +/*
            2  +** 2016 February 10
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +*************************************************************************
           12  +*/
           13  +
           14  +#include "sqlite3expert.h"
           15  +#include <assert.h>
           16  +#include <string.h>
           17  +#include <stdio.h>
           18  +
           19  +typedef sqlite3_int64 i64;
           20  +typedef sqlite3_uint64 u64;
           21  +
           22  +typedef struct IdxConstraint IdxConstraint;
           23  +typedef struct IdxContext IdxContext;
           24  +typedef struct IdxScan IdxScan;
           25  +typedef struct IdxStatement IdxStatement;
           26  +typedef struct IdxWhere IdxWhere;
           27  +
           28  +typedef struct IdxColumn IdxColumn;
           29  +typedef struct IdxTable IdxTable;
           30  +
           31  +/*
           32  +** A single constraint. Equivalent to either "col = ?" or "col < ?".
           33  +**
           34  +** pLink:
           35  +**   Used to temporarily link IdxConstraint objects into lists while
           36  +**   creating candidate indexes.
           37  +*/
           38  +struct IdxConstraint {
           39  +  char *zColl;                    /* Collation sequence */
           40  +  int bRange;                     /* True for range, false for eq */
           41  +  int iCol;                       /* Constrained table column */
           42  +  i64 depmask;                    /* Dependency mask */
           43  +  int bFlag;                      /* Used by idxFindCompatible() */
           44  +  int bDesc;                      /* True if ORDER BY <expr> DESC */
           45  +  IdxConstraint *pNext;           /* Next constraint in pEq or pRange list */
           46  +  IdxConstraint *pLink;           /* See above */
           47  +};
           48  +
           49  +/*
           50  +** A WHERE clause. Made up of IdxConstraint objects. Example WHERE clause:
           51  +**
           52  +**   a=? AND b=? AND c=? AND d=? AND e>? AND f<?
           53  +**
           54  +** The above is decomposed into 6 AND connected clauses. The first four are
           55  +** added to the IdxWhere.pEq linked list, the following two into 
           56  +** IdxWhere.pRange.
           57  +**
           58  +** IdxWhere.pEq and IdxWhere.pRange are simple linked lists of IdxConstraint
           59  +** objects linked by the IdxConstraint.pNext field.
           60  +*/
           61  +struct IdxWhere {
           62  +  IdxConstraint *pEq;             /* List of == constraints */
           63  +  IdxConstraint *pRange;          /* List of < constraints */
           64  +};
           65  +
           66  +/*
           67  +** A single scan of a single table.
           68  +*/
           69  +struct IdxScan {
           70  +  IdxTable *pTable;               /* Table-info */
           71  +  char *zTable;                   /* Name of table to scan */
           72  +  int iDb;                        /* Database containing table zTable */
           73  +  i64 covering;                   /* Mask of columns required for cov. index */
           74  +  IdxConstraint *pOrder;          /* ORDER BY columns */
           75  +  IdxWhere where;                 /* WHERE Constraints */
           76  +  IdxScan *pNextScan;             /* Next IdxScan object for same query */
           77  +};
           78  +
           79  +/*
           80  +** Data regarding a database table. Extracted from "PRAGMA table_info"
           81  +*/
           82  +struct IdxColumn {
           83  +  char *zName;
           84  +  char *zColl;
           85  +  int iPk;
           86  +};
           87  +struct IdxTable {
           88  +  int nCol;
           89  +  IdxColumn *aCol;
           90  +};
           91  +
           92  +/*
           93  +** Context object passed to idxWhereInfo() and other functions.
           94  +*/
           95  +struct IdxContext {
           96  +  char **pzErrmsg;
           97  +  IdxWhere *pCurrent;             /* Current where clause */
           98  +  int rc;                         /* Error code (if error has occurred) */
           99  +  IdxScan *pScan;                 /* List of scan objects */
          100  +  sqlite3 *dbm;                   /* In-memory db for this analysis */
          101  +  sqlite3 *db;                    /* User database under analysis */
          102  +  sqlite3_stmt *pInsertMask;      /* To write to aux.depmask */
          103  +  i64 iIdxRowid;                  /* Rowid of first index created */
          104  +};
          105  +
          106  +struct IdxStatement {
          107  +  int iId;                        /* Statement number */
          108  +  char *zSql;                     /* SQL statement */
          109  +  char *zIdx;                     /* Indexes */
          110  +  char *zEQP;                     /* Plan */
          111  +  IdxStatement *pNext;
          112  +};
          113  +
          114  +/*
          115  +** sqlite3expert object.
          116  +*/
          117  +struct sqlite3expert {
          118  +  sqlite3 *db;                    /* Users database */
          119  +  sqlite3 *dbm;                   /* In-memory db for this analysis */
          120  +
          121  +  int bRun;                       /* True once analysis has run */
          122  +  char **pzErrmsg;
          123  +
          124  +  IdxScan *pScan;                 /* List of scan objects */
          125  +  IdxStatement *pStatement;       /* List of IdxStatement objects */
          126  +  int rc;                         /* Error code from whereinfo hook */
          127  +  i64 iIdxRowid;                  /* Rowid of first index created */
          128  +};
          129  +
          130  +
          131  +/*
          132  +** Allocate and return nByte bytes of zeroed memory using sqlite3_malloc(). 
          133  +** If the allocation fails, set *pRc to SQLITE_NOMEM and return NULL.
          134  +*/
          135  +static void *idxMalloc(int *pRc, int nByte){
          136  +  void *pRet;
          137  +  assert( *pRc==SQLITE_OK );
          138  +  assert( nByte>0 );
          139  +  pRet = sqlite3_malloc(nByte);
          140  +  if( pRet ){
          141  +    memset(pRet, 0, nByte);
          142  +  }else{
          143  +    *pRc = SQLITE_NOMEM;
          144  +  }
          145  +  return pRet;
          146  +}
          147  +
          148  +/*
          149  +** Allocate and return a new IdxConstraint object. Set the IdxConstraint.zColl
          150  +** variable to point to a copy of nul-terminated string zColl.
          151  +*/
          152  +static IdxConstraint *idxNewConstraint(int *pRc, const char *zColl){
          153  +  IdxConstraint *pNew;
          154  +  int nColl = strlen(zColl);
          155  +
          156  +  assert( *pRc==SQLITE_OK );
          157  +  pNew = (IdxConstraint*)idxMalloc(pRc, sizeof(IdxConstraint) * nColl + 1);
          158  +  if( pNew ){
          159  +    pNew->zColl = (char*)&pNew[1];
          160  +    memcpy(pNew->zColl, zColl, nColl+1);
          161  +  }
          162  +  return pNew;
          163  +}
          164  +
          165  +/*
          166  +** sqlite3_whereinfo_hook() callback.
          167  +*/
          168  +static void idxWhereInfo(
          169  +  void *pCtx,                     /* Pointer to IdxContext structure */
          170  +  int eOp, 
          171  +  const char *zVal, 
          172  +  int iVal, 
          173  +  u64 mask
          174  +){
          175  +  sqlite3expert *p = (sqlite3expert*)pCtx;
          176  +
          177  +#if 0
          178  +  const char *zOp = 
          179  +    eOp==SQLITE_WHEREINFO_TABLE ? "TABLE" :
          180  +    eOp==SQLITE_WHEREINFO_EQUALS ? "EQUALS" :
          181  +    eOp==SQLITE_WHEREINFO_RANGE ? "RANGE" :
          182  +    eOp==SQLITE_WHEREINFO_ORDERBY ? "ORDERBY" :
          183  +    "!error!";
          184  +  printf("op=%s zVal=%s iVal=%d mask=%llx\n", zOp, zVal, iVal, mask);
          185  +#endif
          186  +
          187  +  if( p->rc==SQLITE_OK ){
          188  +    assert( eOp==SQLITE_WHEREINFO_TABLE || p->pScan!=0 );
          189  +    switch( eOp ){
          190  +      case SQLITE_WHEREINFO_TABLE: {
          191  +        int nVal = strlen(zVal);
          192  +        IdxScan *pNew = (IdxScan*)idxMalloc(&p->rc, sizeof(IdxScan) + nVal + 1);
          193  +        if( !pNew ) return;
          194  +        pNew->zTable = (char*)&pNew[1];
          195  +        memcpy(pNew->zTable, zVal, nVal+1);
          196  +        pNew->pNextScan = p->pScan;
          197  +        pNew->covering = mask;
          198  +        p->pScan = pNew;
          199  +        break;
          200  +      }
          201  +
          202  +      case SQLITE_WHEREINFO_ORDERBY: {
          203  +        IdxConstraint *pNew = idxNewConstraint(&p->rc, zVal);
          204  +        if( pNew==0 ) return;
          205  +        pNew->iCol = iVal;
          206  +        pNew->bDesc = (int)mask;
          207  +        if( p->pScan->pOrder==0 ){
          208  +          p->pScan->pOrder = pNew;
          209  +        }else{
          210  +          IdxConstraint *pIter;
          211  +          for(pIter=p->pScan->pOrder; pIter->pNext; pIter=pIter->pNext);
          212  +          pIter->pNext = pNew;
          213  +          pIter->pLink = pNew;
          214  +        }
          215  +        break;
          216  +      }
          217  +
          218  +      case SQLITE_WHEREINFO_EQUALS:
          219  +      case SQLITE_WHEREINFO_RANGE: {
          220  +        IdxConstraint *pNew = idxNewConstraint(&p->rc, zVal);
          221  +        if( pNew==0 ) return;
          222  +        pNew->iCol = iVal;
          223  +        pNew->depmask = mask;
          224  +
          225  +        if( eOp==SQLITE_WHEREINFO_RANGE ){
          226  +          pNew->pNext = p->pScan->where.pRange;
          227  +          p->pScan->where.pRange = pNew;
          228  +        }else{
          229  +          pNew->pNext = p->pScan->where.pEq;
          230  +          p->pScan->where.pEq = pNew;
          231  +        }
          232  +#if 0
          233  +        sqlite3_bind_int64(p->pInsertMask, 1, mask);
          234  +        sqlite3_step(p->pInsertMask);
          235  +        p->rc = sqlite3_reset(p->pInsertMask);
          236  +#endif
          237  +        break;
          238  +      }
          239  +    }
          240  +  }
          241  +}
          242  +
          243  +/*
          244  +** An error associated with database handle db has just occurred. Pass
          245  +** the error message to callback function xOut.
          246  +*/
          247  +static void idxDatabaseError(
          248  +  sqlite3 *db,                    /* Database handle */
          249  +  char **pzErrmsg                 /* Write error here */
          250  +){
          251  +  *pzErrmsg = sqlite3_mprintf("%s", sqlite3_errmsg(db));
          252  +}
          253  +
          254  +static int idxPrepareStmt(
          255  +  sqlite3 *db,                    /* Database handle to compile against */
          256  +  sqlite3_stmt **ppStmt,          /* OUT: Compiled SQL statement */
          257  +  char **pzErrmsg,                /* OUT: sqlite3_malloc()ed error message */
          258  +  const char *zSql                /* SQL statement to compile */
          259  +){
          260  +  int rc = sqlite3_prepare_v2(db, zSql, -1, ppStmt, 0);
          261  +  if( rc!=SQLITE_OK ){
          262  +    *ppStmt = 0;
          263  +    idxDatabaseError(db, pzErrmsg);
          264  +  }
          265  +  return rc;
          266  +}
          267  +
          268  +static int idxPrintfPrepareStmt(
          269  +  sqlite3 *db,                    /* Database handle to compile against */
          270  +  sqlite3_stmt **ppStmt,          /* OUT: Compiled SQL statement */
          271  +  char **pzErrmsg,                /* OUT: sqlite3_malloc()ed error message */
          272  +  const char *zFmt,               /* printf() format of SQL statement */
          273  +  ...                             /* Trailing printf() arguments */
          274  +){
          275  +  va_list ap;
          276  +  int rc;
          277  +  char *zSql;
          278  +  va_start(ap, zFmt);
          279  +  zSql = sqlite3_vmprintf(zFmt, ap);
          280  +  if( zSql==0 ){
          281  +    rc = SQLITE_NOMEM;
          282  +  }else{
          283  +    rc = idxPrepareStmt(db, ppStmt, pzErrmsg, zSql);
          284  +    sqlite3_free(zSql);
          285  +  }
          286  +  va_end(ap);
          287  +  return rc;
          288  +}
          289  +
          290  +static int idxGetTableInfo(
          291  +  sqlite3 *db,
          292  +  IdxScan *pScan,
          293  +  char **pzErrmsg
          294  +){
          295  +  const char *zTbl = pScan->zTable;
          296  +  sqlite3_stmt *p1 = 0;
          297  +  int nCol = 0;
          298  +  int nByte = sizeof(IdxTable);
          299  +  IdxTable *pNew = 0;
          300  +  int rc, rc2;
          301  +  char *pCsr;
          302  +
          303  +  rc = idxPrintfPrepareStmt(db, &p1, pzErrmsg, "PRAGMA table_info=%Q", zTbl);
          304  +  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(p1) ){
          305  +    const char *zCol = (const char*)sqlite3_column_text(p1, 1);
          306  +    nByte += 1 + strlen(zCol);
          307  +    rc = sqlite3_table_column_metadata(
          308  +        db, "main", zTbl, zCol, 0, &zCol, 0, 0, 0
          309  +    );
          310  +    nByte += 1 + strlen(zCol);
          311  +    nCol++;
          312  +  }
          313  +  rc2 = sqlite3_reset(p1);
          314  +  if( rc==SQLITE_OK ) rc = rc2;
          315  +
          316  +  nByte += sizeof(IdxColumn) * nCol;
          317  +  if( rc==SQLITE_OK ){
          318  +    pNew = idxMalloc(&rc, nByte);
          319  +  }
          320  +  if( rc==SQLITE_OK ){
          321  +    pNew->aCol = (IdxColumn*)&pNew[1];
          322  +    pNew->nCol = nCol;
          323  +    pCsr = (char*)&pNew->aCol[nCol];
          324  +  }
          325  +
          326  +  nCol = 0;
          327  +  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(p1) ){
          328  +    const char *zCol = (const char*)sqlite3_column_text(p1, 1);
          329  +    int nCopy = strlen(zCol) + 1;
          330  +    pNew->aCol[nCol].zName = pCsr;
          331  +    pNew->aCol[nCol].iPk = sqlite3_column_int(p1, 5);
          332  +    memcpy(pCsr, zCol, nCopy);
          333  +    pCsr += nCopy;
          334  +
          335  +    rc = sqlite3_table_column_metadata(
          336  +        db, "main", zTbl, zCol, 0, &zCol, 0, 0, 0
          337  +    );
          338  +    if( rc==SQLITE_OK ){
          339  +      nCopy = strlen(zCol) + 1;
          340  +      pNew->aCol[nCol].zColl = pCsr;
          341  +      memcpy(pCsr, zCol, nCopy);
          342  +      pCsr += nCopy;
          343  +    }
          344  +
          345  +    nCol++;
          346  +  }
          347  +  rc2 = sqlite3_finalize(p1);
          348  +  if( rc==SQLITE_OK ) rc = rc2;
          349  +
          350  +  if( rc==SQLITE_OK ){
          351  +    pScan->pTable = pNew;
          352  +  }else{
          353  +    sqlite3_free(pNew);
          354  +  }
          355  +
          356  +  return rc;
          357  +}
          358  +
          359  +/*
          360  +** This function is a no-op if *pRc is set to anything other than 
          361  +** SQLITE_OK when it is called.
          362  +**
          363  +** If *pRc is initially set to SQLITE_OK, then the text specified by
          364  +** the printf() style arguments is appended to zIn and the result returned
          365  +** in a buffer allocated by sqlite3_malloc(). sqlite3_free() is called on
          366  +** zIn before returning.
          367  +*/
          368  +static char *idxAppendText(int *pRc, char *zIn, const char *zFmt, ...){
          369  +  va_list ap;
          370  +  char *zAppend = 0;
          371  +  char *zRet = 0;
          372  +  int nIn = zIn ? strlen(zIn) : 0;
          373  +  int nAppend = 0;
          374  +  va_start(ap, zFmt);
          375  +  if( *pRc==SQLITE_OK ){
          376  +    zAppend = sqlite3_vmprintf(zFmt, ap);
          377  +    if( zAppend ){
          378  +      nAppend = strlen(zAppend);
          379  +      zRet = (char*)sqlite3_malloc(nIn + nAppend + 1);
          380  +    }
          381  +    if( zAppend && zRet ){
          382  +      memcpy(zRet, zIn, nIn);
          383  +      memcpy(&zRet[nIn], zAppend, nAppend+1);
          384  +    }else{
          385  +      sqlite3_free(zRet);
          386  +      zRet = 0;
          387  +      *pRc = SQLITE_NOMEM;
          388  +    }
          389  +    sqlite3_free(zAppend);
          390  +    sqlite3_free(zIn);
          391  +  }
          392  +  va_end(ap);
          393  +  return zRet;
          394  +}
          395  +
          396  +static int idxIdentifierRequiresQuotes(const char *zId){
          397  +  int i;
          398  +  for(i=0; zId[i]; i++){
          399  +    if( !(zId[i]=='_')
          400  +     && !(zId[i]>='0' && zId[i]<='9')
          401  +     && !(zId[i]>='a' && zId[i]<='z')
          402  +     && !(zId[i]>='A' && zId[i]<='Z')
          403  +    ){
          404  +      return 1;
          405  +    }
          406  +  }
          407  +  return 0;
          408  +}
          409  +
          410  +static char *idxAppendColDefn(
          411  +  int *pRc, 
          412  +  char *zIn, 
          413  +  IdxTable *pTab, 
          414  +  IdxConstraint *pCons
          415  +){
          416  +  char *zRet = zIn;
          417  +  IdxColumn *p = &pTab->aCol[pCons->iCol];
          418  +  if( zRet ) zRet = idxAppendText(pRc, zRet, ", ");
          419  +
          420  +  if( idxIdentifierRequiresQuotes(p->zName) ){
          421  +    zRet = idxAppendText(pRc, zRet, "%Q", p->zName);
          422  +  }else{
          423  +    zRet = idxAppendText(pRc, zRet, "%s", p->zName);
          424  +  }
          425  +
          426  +  if( sqlite3_stricmp(p->zColl, pCons->zColl) ){
          427  +    if( idxIdentifierRequiresQuotes(pCons->zColl) ){
          428  +      zRet = idxAppendText(pRc, zRet, " COLLATE %Q", pCons->zColl);
          429  +    }else{
          430  +      zRet = idxAppendText(pRc, zRet, " COLLATE %s", pCons->zColl);
          431  +    }
          432  +  }
          433  +
          434  +  if( pCons->bDesc ){
          435  +    zRet = idxAppendText(pRc, zRet, " DESC");
          436  +  }
          437  +  return zRet;
          438  +}
          439  +
          440  +/*
          441  +** Search database dbm for an index compatible with the one idxCreateFromCons()
          442  +** would create from arguments pScan, pEq and pTail. If no error occurs and 
          443  +** such an index is found, return non-zero. Or, if no such index is found,
          444  +** return zero.
          445  +**
          446  +** If an error occurs, set *pRc to an SQLite error code and return zero.
          447  +*/
          448  +static int idxFindCompatible(
          449  +  int *pRc,                       /* OUT: Error code */
          450  +  sqlite3* dbm,                   /* Database to search */
          451  +  IdxScan *pScan,                 /* Scan for table to search for index on */
          452  +  IdxConstraint *pEq,             /* List of == constraints */
          453  +  IdxConstraint *pTail            /* List of range constraints */
          454  +){
          455  +  const char *zTbl = pScan->zTable;
          456  +  sqlite3_stmt *pIdxList = 0;
          457  +  IdxConstraint *pIter;
          458  +  int nEq = 0;                    /* Number of elements in pEq */
          459  +  int rc, rc2;
          460  +
          461  +  /* Count the elements in list pEq */
          462  +  for(pIter=pEq; pIter; pIter=pIter->pLink) nEq++;
          463  +
          464  +  rc = idxPrintfPrepareStmt(dbm, &pIdxList, 0, "PRAGMA index_list=%Q", zTbl);
          465  +  while( rc==SQLITE_OK && sqlite3_step(pIdxList)==SQLITE_ROW ){
          466  +    int bMatch = 1;
          467  +    IdxConstraint *pT = pTail;
          468  +    sqlite3_stmt *pInfo = 0;
          469  +    const char *zIdx = (const char*)sqlite3_column_text(pIdxList, 1);
          470  +
          471  +    /* Zero the IdxConstraint.bFlag values in the pEq list */
          472  +    for(pIter=pEq; pIter; pIter=pIter->pLink) pIter->bFlag = 0;
          473  +
          474  +    rc = idxPrintfPrepareStmt(dbm, &pInfo, 0, "PRAGMA index_xInfo=%Q", zIdx);
          475  +    while( rc==SQLITE_OK && sqlite3_step(pInfo)==SQLITE_ROW ){
          476  +      int iIdx = sqlite3_column_int(pInfo, 0);
          477  +      int iCol = sqlite3_column_int(pInfo, 1);
          478  +      const char *zColl = (const char*)sqlite3_column_text(pInfo, 4);
          479  +
          480  +      if( iIdx<nEq ){
          481  +        for(pIter=pEq; pIter; pIter=pIter->pLink){
          482  +          if( pIter->bFlag ) continue;
          483  +          if( pIter->iCol!=iCol ) continue;
          484  +          if( sqlite3_stricmp(pIter->zColl, zColl) ) continue;
          485  +          pIter->bFlag = 1;
          486  +          break;
          487  +        }
          488  +        if( pIter==0 ){
          489  +          bMatch = 0;
          490  +          break;
          491  +        }
          492  +      }else{
          493  +        if( pT ){
          494  +          if( pT->iCol!=iCol || sqlite3_stricmp(pT->zColl, zColl) ){
          495  +            bMatch = 0;
          496  +            break;
          497  +          }
          498  +          pT = pT->pLink;
          499  +        }
          500  +      }
          501  +    }
          502  +    rc2 = sqlite3_finalize(pInfo);
          503  +    if( rc==SQLITE_OK ) rc = rc2;
          504  +
          505  +    if( rc==SQLITE_OK && bMatch ){
          506  +      sqlite3_finalize(pIdxList);
          507  +      return 1;
          508  +    }
          509  +  }
          510  +  rc2 = sqlite3_finalize(pIdxList);
          511  +  if( rc==SQLITE_OK ) rc = rc2;
          512  +
          513  +  *pRc = rc;
          514  +  return 0;
          515  +}
          516  +
          517  +static int idxCreateFromCons(
          518  +  sqlite3expert *p,
          519  +  IdxScan *pScan,
          520  +  IdxConstraint *pEq, 
          521  +  IdxConstraint *pTail
          522  +){
          523  +  sqlite3 *dbm = p->dbm;
          524  +  int rc = SQLITE_OK;
          525  +  if( (pEq || pTail) && 0==idxFindCompatible(&rc, dbm, pScan, pEq, pTail) ){
          526  +    IdxTable *pTab = pScan->pTable;
          527  +    char *zCols = 0;
          528  +    char *zIdx = 0;
          529  +    IdxConstraint *pCons;
          530  +    int h = 0;
          531  +    const char *zFmt;
          532  +
          533  +    for(pCons=pEq; pCons; pCons=pCons->pLink){
          534  +      zCols = idxAppendColDefn(&rc, zCols, pTab, pCons);
          535  +    }
          536  +    for(pCons=pTail; pCons; pCons=pCons->pLink){
          537  +      zCols = idxAppendColDefn(&rc, zCols, pTab, pCons);
          538  +    }
          539  +
          540  +    if( rc==SQLITE_OK ){
          541  +      /* Hash the list of columns to come up with a name for the index */
          542  +      int i;
          543  +      for(i=0; zCols[i]; i++){
          544  +        h += ((h<<3) + zCols[i]);
          545  +      }
          546  +
          547  +      if( idxIdentifierRequiresQuotes(pScan->zTable) ){
          548  +        zFmt = "CREATE INDEX '%q_idx_%08x' ON %Q(%s)";
          549  +      }else{
          550  +        zFmt = "CREATE INDEX %s_idx_%08x ON %s(%s)";
          551  +      }
          552  +      zIdx = sqlite3_mprintf(zFmt, pScan->zTable, h, pScan->zTable, zCols);
          553  +      if( !zIdx ){
          554  +        rc = SQLITE_NOMEM;
          555  +      }else{
          556  +        rc = sqlite3_exec(dbm, zIdx, 0, 0, p->pzErrmsg);
          557  +#if 1
          558  +        printf("CANDIDATE: %s\n", zIdx);
          559  +#endif
          560  +      }
          561  +    }
          562  +    if( rc==SQLITE_OK && p->iIdxRowid==0 ){
          563  +      int rc2;
          564  +      sqlite3_stmt *pLast = 0;
          565  +      rc = idxPrepareStmt(dbm, &pLast, p->pzErrmsg, 
          566  +          "SELECT max(rowid) FROM sqlite_master"
          567  +      );
          568  +      if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pLast) ){
          569  +        p->iIdxRowid = sqlite3_column_int64(pLast, 0);
          570  +      }
          571  +      rc2 = sqlite3_finalize(pLast);
          572  +      if( rc==SQLITE_OK ) rc = rc2;
          573  +    }
          574  +
          575  +    sqlite3_free(zIdx);
          576  +    sqlite3_free(zCols);
          577  +  }
          578  +  return rc;
          579  +}
          580  +
          581  +static int idxCreateFromWhere(
          582  +    sqlite3expert*, i64, IdxScan*, IdxWhere*, IdxConstraint*, IdxConstraint*
          583  +);
          584  +
          585  +/*
          586  +** Return true if list pList (linked by IdxConstraint.pLink) contains
          587  +** a constraint compatible with *p. Otherwise return false.
          588  +*/
          589  +static int idxFindConstraint(IdxConstraint *pList, IdxConstraint *p){
          590  +  IdxConstraint *pCmp;
          591  +  for(pCmp=pList; pCmp; pCmp=pCmp->pLink){
          592  +    if( p->iCol==pCmp->iCol ) return 1;
          593  +  }
          594  +  return 0;
          595  +}
          596  +
          597  +static int idxCreateFromWhere(
          598  +  sqlite3expert *p, 
          599  +  i64 mask,                       /* Consider only these constraints */
          600  +  IdxScan *pScan,                 /* Create indexes for this scan */
          601  +  IdxWhere *pWhere,               /* Read constraints from here */
          602  +  IdxConstraint *pEq,             /* == constraints for inclusion */
          603  +  IdxConstraint *pTail            /* range/ORDER BY constraints for inclusion */
          604  +){
          605  +  IdxConstraint *p1 = pEq;
          606  +  IdxConstraint *pCon;
          607  +  int rc;
          608  +
          609  +  /* Gather up all the == constraints that match the mask. */
          610  +  for(pCon=pWhere->pEq; pCon; pCon=pCon->pNext){
          611  +    if( (mask & pCon->depmask)==pCon->depmask 
          612  +     && idxFindConstraint(p1, pCon)==0
          613  +     && idxFindConstraint(pTail, pCon)==0
          614  +    ){
          615  +      pCon->pLink = p1;
          616  +      p1 = pCon;
          617  +    }
          618  +  }
          619  +
          620  +  /* Create an index using the == constraints collected above. And the
          621  +  ** range constraint/ORDER BY terms passed in by the caller, if any. */
          622  +  rc = idxCreateFromCons(p, pScan, p1, pTail);
          623  +
          624  +  /* If no range/ORDER BY passed by the caller, create a version of the
          625  +  ** index for each range constraint that matches the mask. */
          626  +  if( pTail==0 ){
          627  +    for(pCon=pWhere->pRange; rc==SQLITE_OK && pCon; pCon=pCon->pNext){
          628  +      assert( pCon->pLink==0 );
          629  +      if( (mask & pCon->depmask)==pCon->depmask
          630  +        && idxFindConstraint(pEq, pCon)==0
          631  +        && idxFindConstraint(pTail, pCon)==0
          632  +      ){
          633  +        rc = idxCreateFromCons(p, pScan, p1, pCon);
          634  +      }
          635  +    }
          636  +  }
          637  +
          638  +  return rc;
          639  +}
          640  +
          641  +/*
          642  +** Create candidate indexes in database [dbm] based on the data in 
          643  +** linked-list pScan.
          644  +*/
          645  +static int idxCreateCandidates(sqlite3expert *p, char **pzErr){
          646  +  sqlite3 *dbm = p->dbm;
          647  +  int rc2;
          648  +  int rc = SQLITE_OK;
          649  +  sqlite3_stmt *pDepmask = 0;     /* Foreach depmask */
          650  +  sqlite3_stmt *pInsert = 0;      /* insert */
          651  +  IdxScan *pIter;
          652  +
          653  +  rc = idxPrepareStmt(dbm, &pInsert, pzErr, 
          654  +      "INSERT OR IGNORE INTO aux.depmask SELECT mask | ?1 FROM aux.depmask;"
          655  +  );
          656  +  if( rc==SQLITE_OK ){
          657  +    rc = idxPrepareStmt(dbm, &pDepmask, pzErr, "SELECT mask FROM depmask");
          658  +  }
          659  +
          660  +  for(pIter=p->pScan; pIter && rc==SQLITE_OK; pIter=pIter->pNextScan){
          661  +    IdxWhere *pWhere = &pIter->where;
          662  +    IdxConstraint *pCons;
          663  +    rc = sqlite3_exec(dbm, 
          664  +        "DELETE FROM aux.depmask;"
          665  +        "INSERT INTO aux.depmask VALUES(0);"
          666  +        , 0, 0, pzErr
          667  +    );
          668  +    for(pCons=pIter->where.pEq; pCons; pCons=pCons->pNext){
          669  +      sqlite3_bind_int64(pInsert, 1, pCons->depmask);
          670  +      sqlite3_step(pInsert);
          671  +      rc = sqlite3_reset(pInsert);
          672  +    }
          673  +
          674  +    while( SQLITE_ROW==sqlite3_step(pDepmask) && rc==SQLITE_OK ){
          675  +      i64 mask = sqlite3_column_int64(pDepmask, 0);
          676  +      rc = idxCreateFromWhere(p, mask, pIter, pWhere, 0, 0);
          677  +      if( rc==SQLITE_OK && pIter->pOrder ){
          678  +        rc = idxCreateFromWhere(p, mask, pIter, pWhere, 0, pIter->pOrder);
          679  +      }
          680  +    }
          681  +    rc2 = sqlite3_reset(pDepmask);
          682  +    if( rc==SQLITE_OK ) rc = rc2;
          683  +  }
          684  +
          685  +  rc2 = sqlite3_finalize(pDepmask);
          686  +  if( rc==SQLITE_OK ) rc = rc2;
          687  +  rc2 = sqlite3_finalize(pInsert);
          688  +  if( rc==SQLITE_OK ) rc = rc2;
          689  +  return rc;
          690  +}
          691  +
          692  +/*
          693  +** Free all elements of the linked list starting from pScan up until pLast
          694  +** (pLast is not freed).
          695  +*/
          696  +static void idxScanFree(IdxScan *pScan, IdxScan *pLast){
          697  +  /* TODO! */
          698  +}
          699  +
          700  +/*
          701  +** Free all elements of the linked list starting from pStatement up 
          702  +** until pLast (pLast is not freed).
          703  +*/
          704  +static void idxStatementFree(IdxStatement *pStatement, IdxStatement *pLast){
          705  +  /* TODO! */
          706  +}
          707  +
          708  +static void idxFinalize(int *pRc, sqlite3_stmt *pStmt){
          709  +  int rc = sqlite3_finalize(pStmt);
          710  +  if( *pRc==SQLITE_OK ) *pRc = rc;
          711  +}
          712  +static void idxReset(int *pRc, sqlite3_stmt *pStmt){
          713  +  int rc = sqlite3_reset(pStmt);
          714  +  if( *pRc==SQLITE_OK ) *pRc = rc;
          715  +}
          716  +
          717  +
          718  +int idxFindIndexes(
          719  +  sqlite3expert *p,
          720  +  char **pzErr                         /* OUT: Error message (sqlite3_malloc) */
          721  +){
          722  +  IdxStatement *pStmt;
          723  +  sqlite3 *dbm = p->dbm;
          724  +  sqlite3_stmt *pSelect = 0;
          725  +  sqlite3_stmt *pInsert = 0;
          726  +  int rc, rc2;
          727  +  int bFound = 0;
          728  +
          729  +  if( rc==SQLITE_OK ){
          730  +    rc = idxPrepareStmt(dbm, &pSelect, pzErr, 
          731  +        "SELECT rowid, sql FROM sqlite_master WHERE name = ?"
          732  +    );
          733  +  }
          734  +  if( rc==SQLITE_OK ){
          735  +    rc = idxPrepareStmt(dbm, &pInsert, pzErr,
          736  +        "INSERT OR IGNORE INTO aux.indexes VALUES(?)"
          737  +    );
          738  +  }
          739  +
          740  +  for(pStmt=p->pStatement; rc==SQLITE_OK && pStmt; pStmt=pStmt->pNext){
          741  +    sqlite3_stmt *pExplain = 0;
          742  +    rc = sqlite3_exec(dbm, "DELETE FROM aux.indexes", 0, 0, 0);
          743  +    if( rc==SQLITE_OK ){
          744  +      rc = idxPrintfPrepareStmt(dbm, &pExplain, pzErr,
          745  +          "EXPLAIN QUERY PLAN %s", pStmt->zSql
          746  +      );
          747  +    }
          748  +    while( rc==SQLITE_OK && sqlite3_step(pExplain)==SQLITE_ROW ){
          749  +      int i;
          750  +      const char *zDetail = (const char*)sqlite3_column_text(pExplain, 3);
          751  +      int nDetail = strlen(zDetail);
          752  +
          753  +      for(i=0; i<nDetail; i++){
          754  +        const char *zIdx = 0;
          755  +        if( memcmp(&zDetail[i], " USING INDEX ", 13)==0 ){
          756  +          zIdx = &zDetail[i+13];
          757  +        }else if( memcmp(&zDetail[i], " USING COVERING INDEX ", 22)==0 ){
          758  +          zIdx = &zDetail[i+22];
          759  +        }
          760  +        if( zIdx ){
          761  +          int nIdx = 0;
          762  +          while( zIdx[nIdx]!='\0' && (zIdx[nIdx]!=' ' || zIdx[nIdx+1]!='(') ){
          763  +            nIdx++;
          764  +          }
          765  +          sqlite3_bind_text(pSelect, 1, zIdx, nIdx, SQLITE_STATIC);
          766  +          if( SQLITE_ROW==sqlite3_step(pSelect) ){
          767  +            i64 iRowid = sqlite3_column_int64(pSelect, 0);
          768  +            const char *zSql = (const char*)sqlite3_column_text(pSelect, 1);
          769  +            if( iRowid>=p->iIdxRowid ){
          770  +              sqlite3_bind_text(pInsert, 1, zSql, -1, SQLITE_STATIC);
          771  +              sqlite3_step(pInsert);
          772  +              rc = sqlite3_reset(pInsert);
          773  +              if( rc ) goto find_indexes_out;
          774  +            }
          775  +          }
          776  +          rc = sqlite3_reset(pSelect);
          777  +          break;
          778  +        }
          779  +      }
          780  +    }
          781  +    idxReset(&rc, pExplain);
          782  +    if( rc==SQLITE_OK ){
          783  +      sqlite3_stmt *pLoop = 0;
          784  +      rc = idxPrepareStmt(dbm,&pLoop,pzErr,"SELECT name||';' FROM aux.indexes");
          785  +      if( rc==SQLITE_OK ){
          786  +        while( SQLITE_ROW==sqlite3_step(pLoop) ){
          787  +          bFound = 1;
          788  +          pStmt->zIdx = idxAppendText(&rc, pStmt->zIdx, "%s\n",
          789  +              (const char*)sqlite3_column_text(pLoop, 0)
          790  +          );
          791  +        }
          792  +        idxFinalize(&rc, pLoop);
          793  +      }
          794  +      if( bFound==0 ){
          795  +        pStmt->zIdx = idxAppendText(&rc, pStmt->zIdx, "(no new indexes)\n");
          796  +      }
          797  +    }
          798  +
          799  +    while( rc==SQLITE_OK && sqlite3_step(pExplain)==SQLITE_ROW ){
          800  +      int iSelectid = sqlite3_column_int(pExplain, 0);
          801  +      int iOrder = sqlite3_column_int(pExplain, 1);
          802  +      int iFrom = sqlite3_column_int(pExplain, 2);
          803  +      const char *zDetail = (const char*)sqlite3_column_text(pExplain, 3);
          804  +
          805  +      pStmt->zEQP = idxAppendText(&rc, pStmt->zEQP, "%d|%d|%d|%s\n", 
          806  +          iSelectid, iOrder, iFrom, zDetail
          807  +      );
          808  +    }
          809  +
          810  +    rc2 = sqlite3_finalize(pExplain);
          811  +    if( rc==SQLITE_OK ) rc = rc2;
          812  +  }
          813  +
          814  + find_indexes_out:
          815  +  rc2 = sqlite3_finalize(pSelect);
          816  +  if( rc==SQLITE_OK ) rc = rc2;
          817  +  rc2 = sqlite3_finalize(pInsert);
          818  +  if( rc==SQLITE_OK ) rc = rc2;
          819  +
          820  +  return rc;
          821  +}
          822  +
          823  +/*
          824  +** The xOut callback is invoked to return command output to the user. The
          825  +** second argument is always a nul-terminated string. The first argument is
          826  +** passed zero if the string contains normal output or non-zero if it is an
          827  +** error message.
          828  +*/
          829  +int shellIndexesCommand(
          830  +  sqlite3 *db,                         /* Database handle */
          831  +  const char *zSql,                    /* SQL to find indexes for */
          832  +  void (*xOut)(void*, const char*),    /* Output callback */
          833  +  void *pOutCtx,                       /* Context for xOut() */
          834  +  char **pzErrmsg                      /* OUT: Error message (sqlite3_malloc) */
          835  +){
          836  +  int rc = SQLITE_OK;
          837  +#if 0
          838  +  sqlite3 *dbm = 0;
          839  +  IdxContext ctx;
          840  +  sqlite3_stmt *pStmt = 0;        /* Statement compiled from zSql */
          841  +
          842  +  memset(&ctx, 0, sizeof(IdxContext));
          843  +  ctx.pzErrmsg = pzErrmsg;
          844  +
          845  +  /* Open an in-memory database to work with. The main in-memory 
          846  +  ** database schema contains tables similar to those in the users 
          847  +  ** database (handle db). The attached in-memory db (aux) contains
          848  +  ** application tables used by the code in this file.  */
          849  +  rc = sqlite3_open(":memory:", &dbm);
          850  +  if( rc==SQLITE_OK ){
          851  +    rc = sqlite3_exec(dbm, 
          852  +        "ATTACH ':memory:' AS aux;"
          853  +        "CREATE TABLE aux.depmask(mask PRIMARY KEY) WITHOUT ROWID;"
          854  +        "CREATE TABLE aux.indexes(name PRIMARY KEY) WITHOUT ROWID;"
          855  +        "INSERT INTO aux.depmask VALUES(0);"
          856  +        , 0, 0, pzErrmsg
          857  +    );
          858  +  }
          859  +
          860  +  /* Prepare an INSERT statement for writing to aux.depmask */
          861  +  if( rc==SQLITE_OK ){
          862  +    rc = idxPrepareStmt(dbm, &ctx.pInsertMask, pzErrmsg,
          863  +        "INSERT OR IGNORE INTO aux.depmask SELECT mask | ?1 FROM aux.depmask;"
          864  +    );
          865  +  }
          866  +
          867  +  /* Analyze the SELECT statement in zSql. */
          868  +  if( rc==SQLITE_OK ){
          869  +    ctx.dbm = dbm;
          870  +    sqlite3_whereinfo_hook(db, idxWhereInfo, (void*)&ctx);
          871  +    rc = idxPrepareStmt(db, &pStmt, pzErrmsg, zSql);
          872  +    sqlite3_whereinfo_hook(db, 0, 0);
          873  +    sqlite3_finalize(pStmt);
          874  +  }
          875  +
          876  +  /* Create tables within the main in-memory database. These tables
          877  +  ** have the same names, columns and declared types as the tables in
          878  +  ** the user database. All constraints except for PRIMARY KEY are
          879  +  ** removed. */
          880  +  if( rc==SQLITE_OK ){
          881  +    rc = idxCreateTables(db, dbm, ctx.pScan, pzErrmsg);
          882  +  }
          883  +
          884  +  /* Create candidate indexes within the in-memory database file */
          885  +  if( rc==SQLITE_OK ){
          886  +    rc = idxCreateCandidates(&ctx);
          887  +  }
          888  +
          889  +  /* Figure out which of the candidate indexes are preferred by the query
          890  +  ** planner and report the results to the user.  */
          891  +  if( rc==SQLITE_OK ){
          892  +    rc = idxFindIndexes(&ctx, zSql, xOut, pOutCtx, pzErrmsg);
          893  +  }
          894  +
          895  +  idxScanFree(ctx.pScan, 0);
          896  +  sqlite3_finalize(ctx.pInsertMask);
          897  +  sqlite3_close(dbm);
          898  +#endif
          899  +  return rc;
          900  +}
          901  +
          902  +/*************************************************************************/
          903  +
          904  +/*
          905  +** Allocate a new sqlite3expert object.
          906  +*/
          907  +sqlite3expert *sqlite3_expert_new(sqlite3 *db, char **pzErrmsg){
          908  +  int rc = SQLITE_OK;
          909  +  sqlite3expert *pNew;
          910  +
          911  +  pNew = (sqlite3expert*)idxMalloc(&rc, sizeof(sqlite3expert));
          912  +  pNew->db = db;
          913  +
          914  +  /* Open an in-memory database to work with. The main in-memory 
          915  +  ** database schema contains tables similar to those in the users 
          916  +  ** database (handle db). The attached in-memory db (aux) contains
          917  +  ** application tables used by the code in this file.  */
          918  +  rc = sqlite3_open(":memory:", &pNew->dbm);
          919  +  if( rc==SQLITE_OK ){
          920  +    rc = sqlite3_exec(pNew->dbm,
          921  +        "ATTACH ':memory:' AS aux;"
          922  +        "CREATE TABLE aux.depmask(mask PRIMARY KEY) WITHOUT ROWID;"
          923  +        "CREATE TABLE aux.indexes(name PRIMARY KEY) WITHOUT ROWID;"
          924  +        , 0, 0, pzErrmsg
          925  +    );
          926  +  }
          927  +
          928  +  /* Copy the entire schema of database [db] into [dbm]. */
          929  +  if( rc==SQLITE_OK ){
          930  +    sqlite3_stmt *pSql;
          931  +    int rc2;
          932  +    rc = idxPrintfPrepareStmt(pNew->db, &pSql, pzErrmsg, 
          933  +        "SELECT sql FROM sqlite_master WHERE name NOT LIKE 'sqlite_%%'"
          934  +    );
          935  +    while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSql) ){
          936  +      const char *zSql = (const char*)sqlite3_column_text(pSql, 0);
          937  +      rc = sqlite3_exec(pNew->dbm, zSql, 0, 0, pzErrmsg);
          938  +    }
          939  +    rc2 = sqlite3_finalize(pSql);
          940  +    if( rc==SQLITE_OK ) rc = rc2;
          941  +  }
          942  +
          943  +  /* If an error has occurred, free the new object and reutrn NULL. Otherwise,
          944  +  ** return the new sqlite3expert handle.  */
          945  +  if( rc!=SQLITE_OK ){
          946  +    sqlite3_expert_destroy(pNew);
          947  +    pNew = 0;
          948  +  }
          949  +  return pNew;
          950  +}
          951  +
          952  +/*
          953  +** Add an SQL statement to the analysis.
          954  +*/
          955  +int sqlite3_expert_sql(
          956  +  sqlite3expert *p,               /* From sqlite3_expert_new() */
          957  +  const char *zSql,               /* SQL statement to add */
          958  +  char **pzErr                    /* OUT: Error message (if any) */
          959  +){
          960  +  IdxScan *pScanOrig = p->pScan;
          961  +  IdxStatement *pStmtOrig = p->pStatement;
          962  +  int rc = SQLITE_OK;
          963  +  const char *zStmt = zSql;
          964  +
          965  +  if( p->bRun ) return SQLITE_MISUSE;
          966  +
          967  +  sqlite3_whereinfo_hook(p->db, idxWhereInfo, p);
          968  +  while( rc==SQLITE_OK && zStmt && zStmt[0] ){
          969  +    sqlite3_stmt *pStmt = 0;
          970  +    rc = sqlite3_prepare_v2(p->db, zStmt, -1, &pStmt, &zStmt);
          971  +    if( rc==SQLITE_OK ){
          972  +      if( pStmt ){
          973  +        IdxStatement *pNew;
          974  +        const char *z = sqlite3_sql(pStmt);
          975  +        int n = strlen(z);
          976  +        pNew = (IdxStatement*)idxMalloc(&rc, sizeof(IdxStatement) + n+1);
          977  +        if( rc==SQLITE_OK ){
          978  +          pNew->zSql = (char*)&pNew[1];
          979  +          memcpy(pNew->zSql, z, n+1);
          980  +          pNew->pNext = p->pStatement;
          981  +          if( p->pStatement ) pNew->iId = p->pStatement->iId+1;
          982  +          p->pStatement = pNew;
          983  +        }
          984  +        sqlite3_finalize(pStmt);
          985  +      }
          986  +    }else{
          987  +      idxDatabaseError(p->db, pzErr);
          988  +    }
          989  +  }
          990  +  sqlite3_whereinfo_hook(p->db, 0, 0);
          991  +
          992  +  if( rc!=SQLITE_OK ){
          993  +    idxScanFree(p->pScan, pScanOrig);
          994  +    idxStatementFree(p->pStatement, pStmtOrig);
          995  +    p->pScan = pScanOrig;
          996  +    p->pStatement = pStmtOrig;
          997  +  }
          998  +
          999  +  return rc;
         1000  +}
         1001  +
         1002  +int sqlite3_expert_analyze(sqlite3expert *p, char **pzErr){
         1003  +  int rc = SQLITE_OK;
         1004  +  IdxScan *pIter;
         1005  +
         1006  +  /* Load IdxTable objects */
         1007  +  for(pIter=p->pScan; pIter && rc==SQLITE_OK; pIter=pIter->pNextScan){
         1008  +    rc = idxGetTableInfo(p->dbm, pIter, pzErr);
         1009  +  }
         1010  +
         1011  +
         1012  +  /* Create candidate indexes within the in-memory database file */
         1013  +  if( rc==SQLITE_OK ){
         1014  +    rc = idxCreateCandidates(p, pzErr);
         1015  +  }
         1016  +
         1017  +  /* Figure out which of the candidate indexes are preferred by the query
         1018  +  ** planner and report the results to the user.  */
         1019  +  if( rc==SQLITE_OK ){
         1020  +    rc = idxFindIndexes(p, pzErr);
         1021  +  }
         1022  +
         1023  +  if( rc==SQLITE_OK ){
         1024  +    p->bRun = 1;
         1025  +  }
         1026  +  return rc;
         1027  +}
         1028  +
         1029  +int sqlite3_expert_count(sqlite3expert *p){
         1030  +  int nRet = 0;
         1031  +  if( p->pStatement ) nRet = p->pStatement->iId+1;
         1032  +  return nRet;
         1033  +}
         1034  +
         1035  +const char *sqlite3_expert_report(sqlite3expert *p, int iStmt, int eReport){
         1036  +  const char *zRet = 0;
         1037  +  IdxStatement *pStmt;
         1038  +
         1039  +  if( p->bRun==0 ) return 0;
         1040  +  for(pStmt=p->pStatement; pStmt && pStmt->iId!=iStmt; pStmt=pStmt->pNext);
         1041  +  if( pStmt ){
         1042  +    switch( eReport ){
         1043  +      case EXPERT_REPORT_SQL:
         1044  +        zRet = pStmt->zSql;
         1045  +        break;
         1046  +      case EXPERT_REPORT_INDEXES:
         1047  +        zRet = pStmt->zIdx;
         1048  +        break;
         1049  +      case EXPERT_REPORT_PLAN:
         1050  +        zRet = pStmt->zEQP;
         1051  +        break;
         1052  +    }
         1053  +  }
         1054  +  return zRet;
         1055  +}
         1056  +
         1057  +/*
         1058  +** Free an sqlite3expert object.
         1059  +*/
         1060  +void sqlite3_expert_destroy(sqlite3expert *p){
         1061  +  sqlite3_close(p->dbm);
         1062  +  idxScanFree(p->pScan, 0);
         1063  +  idxStatementFree(p->pStatement, 0);
         1064  +  sqlite3_free(p);
         1065  +}
         1066  +

Added ext/expert/sqlite3expert.h.

            1  +/*
            2  +** 2017 April 07
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +*************************************************************************
           12  +*/
           13  +
           14  +
           15  +#include "sqlite3.h"
           16  +
           17  +typedef struct sqlite3expert sqlite3expert;
           18  +
           19  +/*
           20  +** Create a new sqlite3expert object.
           21  +*/
           22  +sqlite3expert *sqlite3_expert_new(sqlite3 *db, char **pzErr);
           23  +
           24  +/*
           25  +** Add an SQL statement to the analysis.
           26  +*/
           27  +int sqlite3_expert_sql(
           28  +  sqlite3expert *p,               /* From sqlite3_expert_new() */
           29  +  const char *zSql,               /* SQL statement to add */
           30  +  char **pzErr                    /* OUT: Error message (if any) */
           31  +);
           32  +
           33  +int sqlite3_expert_analyze(sqlite3expert *p, char **pzErr);
           34  +
           35  +/*
           36  +** Return the total number of SQL queries loaded via sqlite3_expert_sql().
           37  +*/
           38  +int sqlite3_expert_count(sqlite3expert*);
           39  +
           40  +/*
           41  +** Return a component of the report.
           42  +*/
           43  +const char *sqlite3_expert_report(sqlite3expert*, int iStmt, int eReport);
           44  +
           45  +/*
           46  +** Values for the third argument passed to sqlite3_expert_report().
           47  +*/
           48  +#define EXPERT_REPORT_SQL        1
           49  +#define EXPERT_REPORT_INDEXES    2
           50  +#define EXPERT_REPORT_PLAN       3
           51  +
           52  +/*
           53  +** Free an (sqlite3expert*) handle allocated by sqlite3-expert_new().
           54  +*/
           55  +void sqlite3_expert_destroy(sqlite3expert*);
           56  +
           57  +

Changes to main.mk.

   487    487   #
   488    488   all:	sqlite3.h libsqlite3.a sqlite3$(EXE)
   489    489   
   490    490   libsqlite3.a:	$(LIBOBJ)
   491    491   	$(AR) libsqlite3.a $(LIBOBJ)
   492    492   	$(RANLIB) libsqlite3.a
   493    493   
   494         -sqlite3$(EXE):	$(TOP)/src/shell.c libsqlite3.a sqlite3.h $(TOP)/src/shell_indexes.c
          494  +sqlite3$(EXE):	$(TOP)/src/shell.c libsqlite3.a sqlite3.h
   495    495   	$(TCCX) $(READLINE_FLAGS) -o sqlite3$(EXE) $(SHELL_OPT) \
   496    496   		$(TOP)/src/shell.c libsqlite3.a $(LIBREADLINE) $(TLIBS) $(THREADLIB)
   497    497   
   498    498   sqldiff$(EXE):	$(TOP)/tool/sqldiff.c sqlite3.c sqlite3.h
   499    499   	$(TCCX) -o sqldiff$(EXE) -DSQLITE_THREADSAFE=0 \
   500    500   		$(TOP)/tool/sqldiff.c sqlite3.c $(TLIBS) $(THREADLIB)
   501    501   
................................................................................
   757    757   	echo "static const char *zMainloop = " >> $@
   758    758   	tclsh $(TOP)/tool/tostr.tcl $(TOP)/tool/spaceanal.tcl >> $@
   759    759   	echo "; return zMainloop; }" >> $@
   760    760   
   761    761   sqlite3_analyzer$(EXE): sqlite3_analyzer.c
   762    762   	$(TCCX) $(TCL_FLAGS) sqlite3_analyzer.c -o $@ $(LIBTCL) $(THREADLIB) 
   763    763   
          764  +sqlite3_expert$(EXE): $(TOP)/ext/expert/sqlite3expert.h $(TOP)/ext/expert/sqlite3expert.c $(TOP)/ext/expert/expert.c sqlite3.c
          765  +	$(TCCX) -DSQLITE_ENABLE_WHEREINFO_HOOK $(TOP)/ext/expert/sqlite3expert.h $(TOP)/ext/expert/sqlite3expert.c $(TOP)/ext/expert/expert.c sqlite3.c -o sqlite3_expert $(THREADLIB)
          766  +
   764    767   sqlite3_schemalint.c: sqlite3.c $(TOP)/src/tclsqlite.c $(TOP)/tool/schemalint.tcl
   765    768   	echo "#define TCLSH 2" > $@
   766    769   	echo "#define SQLITE_ENABLE_DBSTAT_VTAB 1" >> $@
   767    770   	cat sqlite3.c $(TOP)/src/tclsqlite.c >> $@
   768    771   	echo "static const char *tclsh_main_loop(void){" >> $@
   769    772   	echo "static const char *zMainloop = " >> $@
   770    773   	tclsh $(TOP)/tool/tostr.tcl $(TOP)/tool/schemalint.tcl >> $@
................................................................................
   975    978   	rm -f wordcount wordcount.exe
   976    979   	rm -f rbu rbu.exe
   977    980   	rm -f srcck1 srcck1.exe
   978    981   	rm -f sqlite3.c sqlite3-*.c fts?amal.c tclsqlite3.c
   979    982   	rm -f sqlite3rc.h
   980    983   	rm -f shell.c sqlite3ext.h
   981    984   	rm -f sqlite3_analyzer sqlite3_analyzer.exe sqlite3_analyzer.c
          985  +	rm -f sqlite3_expert sqlite3_expert.exe 
   982    986   	rm -f sqlite-*-output.vsix
   983    987   	rm -f mptester mptester.exe
   984    988   	rm -f fuzzershell fuzzershell.exe
   985    989   	rm -f fuzzcheck fuzzcheck.exe
   986    990   	rm -f sqldiff sqldiff.exe
   987    991   	rm -f fts5.* fts5parse.*

Changes to src/shell.c.

   162    162     _setmode(_fileno(file), _O_TEXT);
   163    163   }
   164    164   #else
   165    165   # define setBinaryMode(X,Y)
   166    166   # define setTextMode(X,Y)
   167    167   #endif
   168    168   
   169         -#include "shell_indexes.c"
   170    169   
   171    170   /* True if the timer is enabled */
   172    171   static int enableTimer = 0;
   173    172   
   174    173   /* Return the current wall-clock time */
   175    174   static sqlite3_int64 timeOfDay(void){
   176    175     static sqlite3_vfs *clockVfs = 0;
................................................................................
  1360   1359   ** instance of the following structure.
  1361   1360   */
  1362   1361   typedef struct ShellState ShellState;
  1363   1362   struct ShellState {
  1364   1363     sqlite3 *db;           /* The database */
  1365   1364     int autoExplain;       /* Automatically turn on .explain mode */
  1366   1365     int autoEQP;           /* Run EXPLAIN QUERY PLAN prior to seach SQL stmt */
  1367         -  int bRecommend;        /* Instead of sqlite3_exec(), recommend indexes */
  1368   1366     int statsOn;           /* True to display memory stats before each finalize */
  1369   1367     int scanstatsOn;       /* True to display scan stats before each finalize */
  1370   1368     int outCount;          /* Revert to stdout when reaching zero */
  1371   1369     int cnt;               /* Number of records displayed so far */
  1372   1370     FILE *out;             /* Write results here */
  1373   1371     FILE *traceOut;        /* Output for sqlite3_trace() */
  1374   1372     int nErr;              /* Number of errors seen */
................................................................................
  2582   2580   static void explain_data_delete(ShellState *p){
  2583   2581     sqlite3_free(p->aiIndent);
  2584   2582     p->aiIndent = 0;
  2585   2583     p->nIndent = 0;
  2586   2584     p->iIndent = 0;
  2587   2585   }
  2588   2586   
  2589         -typedef struct RecCommandCtx RecCommandCtx;
  2590         -struct RecCommandCtx {
  2591         -  int (*xCallback)(void*,int,char**,char**,int*);
  2592         -  ShellState *pArg;
  2593         -};
  2594         -
  2595         -static void recCommandOut(void *pCtx, const char *zLine){
  2596         -  const char *zCol = "output";
  2597         -  RecCommandCtx *p = (RecCommandCtx*)pCtx;
  2598         -  int t = SQLITE_TEXT;
  2599         -  p->xCallback(p->pArg, 1, (char**)&zLine, (char**)&zCol, &t);
  2600         -}
  2601         -
  2602   2587   /*
  2603   2588   ** Disable and restore .wheretrace and .selecttrace settings.
  2604   2589   */
  2605   2590   #if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_SELECTTRACE)
  2606   2591   extern int sqlite3SelectTrace;
  2607   2592   static int savedSelectTrace;
  2608   2593   #endif
................................................................................
  2719   2704     int rc2;
  2720   2705     const char *zLeftover;          /* Tail of unprocessed SQL */
  2721   2706   
  2722   2707     if( pzErrMsg ){
  2723   2708       *pzErrMsg = NULL;
  2724   2709     }
  2725   2710   
  2726         -  if( pArg->bRecommend ){
  2727         -    RecCommandCtx ctx;
  2728         -    ctx.xCallback = xCallback;
  2729         -    ctx.pArg = pArg;
  2730         -    rc = shellIndexesCommand(db, zSql, recCommandOut, &ctx, pzErrMsg);
  2731         -  }else
  2732         -
  2733   2711     while( zSql[0] && (SQLITE_OK == rc) ){
  2734   2712       static const char *zStmtSql;
  2735   2713       rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, &zLeftover);
  2736   2714       if( SQLITE_OK != rc ){
  2737   2715         if( pzErrMsg ){
  2738   2716           *pzErrMsg = save_err_msg(db);
  2739   2717         }
................................................................................
  5518   5496       }else{
  5519   5497         utf8_printf(stderr, "Error: %s\n", sqlite3_errmsg(p->db));
  5520   5498         rc = 1;
  5521   5499       }
  5522   5500       sqlite3_close(pSrc);
  5523   5501     }else
  5524   5502   
  5525         -  if( c=='r' && n>=2 && strncmp(azArg[0], "recommend", n)==0 ){
  5526         -    if( nArg==2 ){
  5527         -      p->bRecommend = booleanValue(azArg[1]);
  5528         -    }else{
  5529         -      raw_printf(stderr, "Usage: .recommend on|off\n");
  5530         -      rc = 1;
  5531         -    }
  5532         -  }else
  5533         -
  5534   5503   
  5535   5504     if( c=='s' && strncmp(azArg[0], "scanstats", n)==0 ){
  5536   5505       if( nArg==2 ){
  5537   5506         p->scanstatsOn = booleanValue(azArg[1]);
  5538   5507   #ifndef SQLITE_ENABLE_STMT_SCANSTATUS
  5539   5508         raw_printf(stderr, "Warning: .scanstats not available in this build.\n");
  5540   5509   #endif
................................................................................
  7333   7302             utf8_printf(stderr,"Error: %s\n", zErrMsg);
  7334   7303             if( bail_on_error ) return rc!=0 ? rc : 1;
  7335   7304           }else if( rc!=0 ){
  7336   7305             utf8_printf(stderr,"Error: unable to process SQL \"%s\"\n", z);
  7337   7306             if( bail_on_error ) return rc;
  7338   7307           }
  7339   7308         }
  7340         -
  7341         -    }else if( strcmp(z, "-recommend") ){
  7342         -      data.bRecommend = 1;
  7343   7309       }else{
  7344   7310         utf8_printf(stderr,"%s: Error: unknown option: %s\n", Argv0, z);
  7345   7311         raw_printf(stderr,"Use -help for a list of options.\n");
  7346   7312         return 1;
  7347   7313       }
  7348   7314       data.cMode = data.mode;
  7349   7315     }

Deleted src/shell_indexes.c.

     1         -/*
     2         -** 2016 February 10
     3         -**
     4         -** The author disclaims copyright to this source code.  In place of
     5         -** a legal notice, here is a blessing:
     6         -**
     7         -**    May you do good and not evil.
     8         -**    May you find forgiveness for yourself and forgive others.
     9         -**    May you share freely, never taking more than you give.
    10         -**
    11         -*************************************************************************
    12         -*/
    13         -
    14         -typedef sqlite3_int64 i64;
    15         -typedef sqlite3_uint64 u64;
    16         -
    17         -typedef struct IdxConstraint IdxConstraint;
    18         -typedef struct IdxContext IdxContext;
    19         -typedef struct IdxScan IdxScan;
    20         -typedef struct IdxWhere IdxWhere;
    21         -
    22         -typedef struct IdxColumn IdxColumn;
    23         -typedef struct IdxTable IdxTable;
    24         -
    25         -/*
    26         -** A single constraint. Equivalent to either "col = ?" or "col < ?".
    27         -**
    28         -** pLink:
    29         -**   Used to temporarily link IdxConstraint objects into lists while
    30         -**   creating candidate indexes.
    31         -*/
    32         -struct IdxConstraint {
    33         -  char *zColl;                    /* Collation sequence */
    34         -  int bRange;                     /* True for range, false for eq */
    35         -  int iCol;                       /* Constrained table column */
    36         -  i64 depmask;                    /* Dependency mask */
    37         -  int bFlag;                      /* Used by idxFindCompatible() */
    38         -  int bDesc;                      /* True if ORDER BY <expr> DESC */
    39         -  IdxConstraint *pNext;           /* Next constraint in pEq or pRange list */
    40         -  IdxConstraint *pLink;           /* See above */
    41         -};
    42         -
    43         -/*
    44         -** A WHERE clause. Made up of IdxConstraint objects. Example WHERE clause:
    45         -**
    46         -**   a=? AND b=? AND c=? AND d=? AND e>? AND f<?
    47         -**
    48         -** The above is decomposed into 6 AND connected clauses. The first four are
    49         -** added to the IdxWhere.pEq linked list, the following two into 
    50         -** IdxWhere.pRange.
    51         -**
    52         -** IdxWhere.pEq and IdxWhere.pRange are simple linked lists of IdxConstraint
    53         -** objects linked by the IdxConstraint.pNext field.
    54         -*/
    55         -struct IdxWhere {
    56         -  IdxConstraint *pEq;             /* List of == constraints */
    57         -  IdxConstraint *pRange;          /* List of < constraints */
    58         -};
    59         -
    60         -/*
    61         -** A single scan of a single table.
    62         -*/
    63         -struct IdxScan {
    64         -  IdxTable *pTable;               /* Table-info */
    65         -  char *zTable;                   /* Name of table to scan */
    66         -  int iDb;                        /* Database containing table zTable */
    67         -  i64 covering;                   /* Mask of columns required for cov. index */
    68         -  IdxConstraint *pOrder;          /* ORDER BY columns */
    69         -  IdxWhere where;                 /* WHERE Constraints */
    70         -  IdxScan *pNextScan;             /* Next IdxScan object for same query */
    71         -};
    72         -
    73         -/*
    74         -** Context object passed to idxWhereInfo() and other functions.
    75         -*/
    76         -struct IdxContext {
    77         -  char **pzErrmsg;
    78         -  IdxWhere *pCurrent;             /* Current where clause */
    79         -  int rc;                         /* Error code (if error has occurred) */
    80         -  IdxScan *pScan;                 /* List of scan objects */
    81         -  sqlite3 *dbm;                   /* In-memory db for this analysis */
    82         -  sqlite3 *db;                    /* User database under analysis */
    83         -  sqlite3_stmt *pInsertMask;      /* To write to aux.depmask */
    84         -  i64 iIdxRowid;                  /* Rowid of first index created */
    85         -};
    86         -
    87         -/*
    88         -** Data regarding a database table. Extracted from "PRAGMA table_info"
    89         -*/
    90         -struct IdxColumn {
    91         -  char *zName;
    92         -  char *zColl;
    93         -  int iPk;
    94         -};
    95         -struct IdxTable {
    96         -  int nCol;
    97         -  IdxColumn *aCol;
    98         -};
    99         -
   100         -/*
   101         -** Allocate and return nByte bytes of zeroed memory using sqlite3_malloc(). 
   102         -** If the allocation fails, set *pRc to SQLITE_NOMEM and return NULL.
   103         -*/
   104         -static void *idxMalloc(int *pRc, int nByte){
   105         -  void *pRet;
   106         -  assert( *pRc==SQLITE_OK );
   107         -  assert( nByte>0 );
   108         -  pRet = sqlite3_malloc(nByte);
   109         -  if( pRet ){
   110         -    memset(pRet, 0, nByte);
   111         -  }else{
   112         -    *pRc = SQLITE_NOMEM;
   113         -  }
   114         -  return pRet;
   115         -}
   116         -
   117         -/*
   118         -** Allocate and return a new IdxConstraint object. Set the IdxConstraint.zColl
   119         -** variable to point to a copy of nul-terminated string zColl.
   120         -*/
   121         -static IdxConstraint *idxNewConstraint(int *pRc, const char *zColl){
   122         -  IdxConstraint *pNew;
   123         -  int nColl = strlen(zColl);
   124         -
   125         -  assert( *pRc==SQLITE_OK );
   126         -  pNew = (IdxConstraint*)idxMalloc(pRc, sizeof(IdxConstraint) * nColl + 1);
   127         -  if( pNew ){
   128         -    pNew->zColl = (char*)&pNew[1];
   129         -    memcpy(pNew->zColl, zColl, nColl+1);
   130         -  }
   131         -  return pNew;
   132         -}
   133         -
   134         -/*
   135         -** SQLITE_DBCONFIG_WHEREINFO callback.
   136         -*/
   137         -static void idxWhereInfo(
   138         -  void *pCtx,                     /* Pointer to IdxContext structure */
   139         -  int eOp, 
   140         -  const char *zVal, 
   141         -  int iVal, 
   142         -  u64 mask
   143         -){
   144         -  IdxContext *p = (IdxContext*)pCtx;
   145         -
   146         -#if 0
   147         -  const char *zOp = 
   148         -    eOp==SQLITE_WHEREINFO_TABLE ? "TABLE" :
   149         -    eOp==SQLITE_WHEREINFO_EQUALS ? "EQUALS" :
   150         -    eOp==SQLITE_WHEREINFO_RANGE ? "RANGE" :
   151         -    eOp==SQLITE_WHEREINFO_ORDERBY ? "ORDERBY" :
   152         -    "!error!";
   153         -  printf("op=%s zVal=%s iVal=%d mask=%llx\n", zOp, zVal, iVal, mask);
   154         -#endif
   155         -
   156         -  if( p->rc==SQLITE_OK ){
   157         -    assert( eOp==SQLITE_WHEREINFO_TABLE || p->pScan!=0 );
   158         -    switch( eOp ){
   159         -      case SQLITE_WHEREINFO_TABLE: {
   160         -        int nVal = strlen(zVal);
   161         -        IdxScan *pNew = (IdxScan*)idxMalloc(&p->rc, sizeof(IdxScan) + nVal + 1);
   162         -        if( !pNew ) return;
   163         -        pNew->zTable = (char*)&pNew[1];
   164         -        memcpy(pNew->zTable, zVal, nVal+1);
   165         -        pNew->pNextScan = p->pScan;
   166         -        pNew->covering = mask;
   167         -        p->pScan = pNew;
   168         -        p->pCurrent = &pNew->where;
   169         -        break;
   170         -      }
   171         -
   172         -      case SQLITE_WHEREINFO_ORDERBY: {
   173         -        IdxConstraint *pNew = idxNewConstraint(&p->rc, zVal);
   174         -        if( pNew==0 ) return;
   175         -        pNew->iCol = iVal;
   176         -        pNew->bDesc = (int)mask;
   177         -        if( p->pScan->pOrder==0 ){
   178         -          p->pScan->pOrder = pNew;
   179         -        }else{
   180         -          IdxConstraint *pIter;
   181         -          for(pIter=p->pScan->pOrder; pIter->pNext; pIter=pIter->pNext);
   182         -          pIter->pNext = pNew;
   183         -          pIter->pLink = pNew;
   184         -        }
   185         -        break;
   186         -      }
   187         -
   188         -      case SQLITE_WHEREINFO_EQUALS:
   189         -      case SQLITE_WHEREINFO_RANGE: {
   190         -        IdxConstraint *pNew = idxNewConstraint(&p->rc, zVal);
   191         -        if( pNew==0 ) return;
   192         -        pNew->iCol = iVal;
   193         -        pNew->depmask = mask;
   194         -
   195         -        if( eOp==SQLITE_WHEREINFO_RANGE ){
   196         -          pNew->pNext = p->pCurrent->pRange;
   197         -          p->pCurrent->pRange = pNew;
   198         -        }else{
   199         -          pNew->pNext = p->pCurrent->pEq;
   200         -          p->pCurrent->pEq = pNew;
   201         -        }
   202         -
   203         -        sqlite3_bind_int64(p->pInsertMask, 1, mask);
   204         -        sqlite3_step(p->pInsertMask);
   205         -        p->rc = sqlite3_reset(p->pInsertMask);
   206         -        break;
   207         -      }
   208         -    }
   209         -  }
   210         -}
   211         -
   212         -/*
   213         -** An error associated with database handle db has just occurred. Pass
   214         -** the error message to callback function xOut.
   215         -*/
   216         -static void idxDatabaseError(
   217         -  sqlite3 *db,                    /* Database handle */
   218         -  char **pzErrmsg                 /* Write error here */
   219         -){
   220         -  *pzErrmsg = sqlite3_mprintf("%s", sqlite3_errmsg(db));
   221         -}
   222         -
   223         -static int idxPrepareStmt(
   224         -  sqlite3 *db,                    /* Database handle to compile against */
   225         -  sqlite3_stmt **ppStmt,          /* OUT: Compiled SQL statement */
   226         -  char **pzErrmsg,                /* OUT: sqlite3_malloc()ed error message */
   227         -  const char *zSql                /* SQL statement to compile */
   228         -){
   229         -  int rc = sqlite3_prepare_v2(db, zSql, -1, ppStmt, 0);
   230         -  if( rc!=SQLITE_OK ){
   231         -    *ppStmt = 0;
   232         -    idxDatabaseError(db, pzErrmsg);
   233         -  }
   234         -  return rc;
   235         -}
   236         -
   237         -static int idxPrintfPrepareStmt(
   238         -  sqlite3 *db,                    /* Database handle to compile against */
   239         -  sqlite3_stmt **ppStmt,          /* OUT: Compiled SQL statement */
   240         -  char **pzErrmsg,                /* OUT: sqlite3_malloc()ed error message */
   241         -  const char *zFmt,               /* printf() format of SQL statement */
   242         -  ...                             /* Trailing printf() arguments */
   243         -){
   244         -  va_list ap;
   245         -  int rc;
   246         -  char *zSql;
   247         -  va_start(ap, zFmt);
   248         -  zSql = sqlite3_vmprintf(zFmt, ap);
   249         -  if( zSql==0 ){
   250         -    rc = SQLITE_NOMEM;
   251         -  }else{
   252         -    rc = idxPrepareStmt(db, ppStmt, pzErrmsg, zSql);
   253         -    sqlite3_free(zSql);
   254         -  }
   255         -  va_end(ap);
   256         -  return rc;
   257         -}
   258         -
   259         -static int idxGetTableInfo(
   260         -  sqlite3 *db,
   261         -  IdxScan *pScan,
   262         -  char **pzErrmsg
   263         -){
   264         -  const char *zTbl = pScan->zTable;
   265         -  sqlite3_stmt *p1 = 0;
   266         -  int nCol = 0;
   267         -  int nByte = sizeof(IdxTable);
   268         -  IdxTable *pNew = 0;
   269         -  int rc, rc2;
   270         -  char *pCsr;
   271         -
   272         -  rc = idxPrintfPrepareStmt(db, &p1, pzErrmsg, "PRAGMA table_info=%Q", zTbl);
   273         -  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(p1) ){
   274         -    const char *zCol = (const char*)sqlite3_column_text(p1, 1);
   275         -    nByte += 1 + strlen(zCol);
   276         -    rc = sqlite3_table_column_metadata(
   277         -        db, "main", zTbl, zCol, 0, &zCol, 0, 0, 0
   278         -    );
   279         -    nByte += 1 + strlen(zCol);
   280         -    nCol++;
   281         -  }
   282         -  rc2 = sqlite3_reset(p1);
   283         -  if( rc==SQLITE_OK ) rc = rc2;
   284         -
   285         -  nByte += sizeof(IdxColumn) * nCol;
   286         -  if( rc==SQLITE_OK ){
   287         -    pNew = idxMalloc(&rc, nByte);
   288         -  }
   289         -  if( rc==SQLITE_OK ){
   290         -    pNew->aCol = (IdxColumn*)&pNew[1];
   291         -    pNew->nCol = nCol;
   292         -    pCsr = (char*)&pNew->aCol[nCol];
   293         -  }
   294         -
   295         -  nCol = 0;
   296         -  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(p1) ){
   297         -    const char *zCol = (const char*)sqlite3_column_text(p1, 1);
   298         -    int nCopy = strlen(zCol) + 1;
   299         -    pNew->aCol[nCol].zName = pCsr;
   300         -    pNew->aCol[nCol].iPk = sqlite3_column_int(p1, 5);
   301         -    memcpy(pCsr, zCol, nCopy);
   302         -    pCsr += nCopy;
   303         -
   304         -    rc = sqlite3_table_column_metadata(
   305         -        db, "main", zTbl, zCol, 0, &zCol, 0, 0, 0
   306         -    );
   307         -    if( rc==SQLITE_OK ){
   308         -      nCopy = strlen(zCol) + 1;
   309         -      pNew->aCol[nCol].zColl = pCsr;
   310         -      memcpy(pCsr, zCol, nCopy);
   311         -      pCsr += nCopy;
   312         -    }
   313         -
   314         -    nCol++;
   315         -  }
   316         -  rc2 = sqlite3_finalize(p1);
   317         -  if( rc==SQLITE_OK ) rc = rc2;
   318         -
   319         -  if( rc==SQLITE_OK ){
   320         -    pScan->pTable = pNew;
   321         -  }else{
   322         -    sqlite3_free(pNew);
   323         -  }
   324         -
   325         -  return rc;
   326         -}
   327         -
   328         -static int idxCreateTables(
   329         -  sqlite3 *db,                    /* User database */
   330         -  sqlite3 *dbm,                   /* In-memory database to create tables in */
   331         -  IdxScan *pScan,                 /* List of scans */
   332         -  char **pzErrmsg                 /* OUT: Error message */
   333         -){
   334         -  int rc = SQLITE_OK;
   335         -  int rc2;
   336         -  IdxScan *pIter;
   337         -  sqlite3_stmt *pSql = 0;
   338         -
   339         -  /* Copy the entire schema of database [db] into [dbm]. */
   340         -  rc = idxPrintfPrepareStmt(db, &pSql, pzErrmsg, 
   341         -      "SELECT sql FROM sqlite_master WHERE name NOT LIKE 'sqlite_%%'"
   342         -  );
   343         -  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSql) ){
   344         -    const char *zSql = (const char*)sqlite3_column_text(pSql, 0);
   345         -    rc = sqlite3_exec(dbm, zSql, 0, 0, pzErrmsg);
   346         -  }
   347         -  rc2 = sqlite3_finalize(pSql);
   348         -  if( rc==SQLITE_OK ) rc = rc2;
   349         -
   350         -  /* Load IdxTable objects */
   351         -  for(pIter=pScan; pIter && rc==SQLITE_OK; pIter=pIter->pNextScan){
   352         -    rc = idxGetTableInfo(db, pIter, pzErrmsg);
   353         -  }
   354         -  return rc;
   355         -}
   356         -
   357         -/*
   358         -** This function is a no-op if *pRc is set to anything other than 
   359         -** SQLITE_OK when it is called.
   360         -**
   361         -** If *pRc is initially set to SQLITE_OK, then the text specified by
   362         -** the printf() style arguments is appended to zIn and the result returned
   363         -** in a buffer allocated by sqlite3_malloc(). sqlite3_free() is called on
   364         -** zIn before returning.
   365         -*/
   366         -static char *idxAppendText(int *pRc, char *zIn, const char *zFmt, ...){
   367         -  va_list ap;
   368         -  char *zAppend = 0;
   369         -  char *zRet = 0;
   370         -  int nIn = zIn ? strlen(zIn) : 0;
   371         -  int nAppend = 0;
   372         -  va_start(ap, zFmt);
   373         -  if( *pRc==SQLITE_OK ){
   374         -    zAppend = sqlite3_vmprintf(zFmt, ap);
   375         -    if( zAppend ){
   376         -      nAppend = strlen(zAppend);
   377         -      zRet = (char*)sqlite3_malloc(nIn + nAppend + 1);
   378         -    }
   379         -    if( zAppend && zRet ){
   380         -      memcpy(zRet, zIn, nIn);
   381         -      memcpy(&zRet[nIn], zAppend, nAppend+1);
   382         -    }else{
   383         -      sqlite3_free(zRet);
   384         -      zRet = 0;
   385         -      *pRc = SQLITE_NOMEM;
   386         -    }
   387         -    sqlite3_free(zAppend);
   388         -    sqlite3_free(zIn);
   389         -  }
   390         -  va_end(ap);
   391         -  return zRet;
   392         -}
   393         -
   394         -static int idxIdentifierRequiresQuotes(const char *zId){
   395         -  int i;
   396         -  for(i=0; zId[i]; i++){
   397         -    if( !(zId[i]=='_')
   398         -     && !(zId[i]>='0' && zId[i]<='9')
   399         -     && !(zId[i]>='a' && zId[i]<='z')
   400         -     && !(zId[i]>='A' && zId[i]<='Z')
   401         -    ){
   402         -      return 1;
   403         -    }
   404         -  }
   405         -  return 0;
   406         -}
   407         -
   408         -static char *idxAppendColDefn(
   409         -  int *pRc, 
   410         -  char *zIn, 
   411         -  IdxTable *pTab, 
   412         -  IdxConstraint *pCons
   413         -){
   414         -  char *zRet = zIn;
   415         -  IdxColumn *p = &pTab->aCol[pCons->iCol];
   416         -  if( zRet ) zRet = idxAppendText(pRc, zRet, ", ");
   417         -
   418         -  if( idxIdentifierRequiresQuotes(p->zName) ){
   419         -    zRet = idxAppendText(pRc, zRet, "%Q", p->zName);
   420         -  }else{
   421         -    zRet = idxAppendText(pRc, zRet, "%s", p->zName);
   422         -  }
   423         -
   424         -  if( sqlite3_stricmp(p->zColl, pCons->zColl) ){
   425         -    if( idxIdentifierRequiresQuotes(pCons->zColl) ){
   426         -      zRet = idxAppendText(pRc, zRet, " COLLATE %Q", pCons->zColl);
   427         -    }else{
   428         -      zRet = idxAppendText(pRc, zRet, " COLLATE %s", pCons->zColl);
   429         -    }
   430         -  }
   431         -
   432         -  if( pCons->bDesc ){
   433         -    zRet = idxAppendText(pRc, zRet, " DESC");
   434         -  }
   435         -  return zRet;
   436         -}
   437         -
   438         -/*
   439         -** Search database dbm for an index compatible with the one idxCreateFromCons()
   440         -** would create from arguments pScan, pEq and pTail. If no error occurs and 
   441         -** such an index is found, return non-zero. Or, if no such index is found,
   442         -** return zero.
   443         -**
   444         -** If an error occurs, set *pRc to an SQLite error code and return zero.
   445         -*/
   446         -static int idxFindCompatible(
   447         -  int *pRc,                       /* OUT: Error code */
   448         -  sqlite3* dbm,                   /* Database to search */
   449         -  IdxScan *pScan,                 /* Scan for table to search for index on */
   450         -  IdxConstraint *pEq,             /* List of == constraints */
   451         -  IdxConstraint *pTail            /* List of range constraints */
   452         -){
   453         -  const char *zTbl = pScan->zTable;
   454         -  sqlite3_stmt *pIdxList = 0;
   455         -  IdxConstraint *pIter;
   456         -  int nEq = 0;                    /* Number of elements in pEq */
   457         -  int rc, rc2;
   458         -
   459         -
   460         -  /* Count the elements in list pEq */
   461         -  for(pIter=pEq; pIter; pIter=pIter->pLink) nEq++;
   462         -
   463         -  rc = idxPrintfPrepareStmt(dbm, &pIdxList, 0, "PRAGMA index_list=%Q", zTbl);
   464         -  while( rc==SQLITE_OK && sqlite3_step(pIdxList)==SQLITE_ROW ){
   465         -    int bMatch = 1;
   466         -    IdxConstraint *pT = pTail;
   467         -    sqlite3_stmt *pInfo = 0;
   468         -    const char *zIdx = (const char*)sqlite3_column_text(pIdxList, 1);
   469         -
   470         -    /* Zero the IdxConstraint.bFlag values in the pEq list */
   471         -    for(pIter=pEq; pIter; pIter=pIter->pLink) pIter->bFlag = 0;
   472         -
   473         -    rc = idxPrintfPrepareStmt(dbm, &pInfo, 0, "PRAGMA index_xInfo=%Q", zIdx);
   474         -    while( rc==SQLITE_OK && sqlite3_step(pInfo)==SQLITE_ROW ){
   475         -      int iIdx = sqlite3_column_int(pInfo, 0);
   476         -      int iCol = sqlite3_column_int(pInfo, 1);
   477         -      const char *zColl = (const char*)sqlite3_column_text(pInfo, 4);
   478         -
   479         -      if( iIdx<nEq ){
   480         -        for(pIter=pEq; pIter; pIter=pIter->pLink){
   481         -          if( pIter->bFlag ) continue;
   482         -          if( pIter->iCol!=iCol ) continue;
   483         -          if( sqlite3_stricmp(pIter->zColl, zColl) ) continue;
   484         -          pIter->bFlag = 1;
   485         -          break;
   486         -        }
   487         -        if( pIter==0 ){
   488         -          bMatch = 0;
   489         -          break;
   490         -        }
   491         -      }else{
   492         -        if( pT ){
   493         -          if( pT->iCol!=iCol || sqlite3_stricmp(pT->zColl, zColl) ){
   494         -            bMatch = 0;
   495         -            break;
   496         -          }
   497         -          pT = pT->pLink;
   498         -        }
   499         -      }
   500         -    }
   501         -    rc2 = sqlite3_finalize(pInfo);
   502         -    if( rc==SQLITE_OK ) rc = rc2;
   503         -
   504         -    if( rc==SQLITE_OK && bMatch ){
   505         -      sqlite3_finalize(pIdxList);
   506         -      return 1;
   507         -    }
   508         -  }
   509         -  rc2 = sqlite3_finalize(pIdxList);
   510         -  if( rc==SQLITE_OK ) rc = rc2;
   511         -
   512         -  *pRc = rc;
   513         -  return 0;
   514         -}
   515         -
   516         -static int idxCreateFromCons(
   517         -  IdxContext *pCtx,
   518         -  IdxScan *pScan,
   519         -  IdxConstraint *pEq, 
   520         -  IdxConstraint *pTail
   521         -){
   522         -  sqlite3 *dbm = pCtx->dbm;
   523         -  int rc = SQLITE_OK;
   524         -  if( (pEq || pTail) && 0==idxFindCompatible(&rc, dbm, pScan, pEq, pTail) ){
   525         -    IdxTable *pTab = pScan->pTable;
   526         -    char *zCols = 0;
   527         -    char *zIdx = 0;
   528         -    IdxConstraint *pCons;
   529         -    int h = 0;
   530         -    const char *zFmt;
   531         -
   532         -    for(pCons=pEq; pCons; pCons=pCons->pLink){
   533         -      zCols = idxAppendColDefn(&rc, zCols, pTab, pCons);
   534         -    }
   535         -    for(pCons=pTail; pCons; pCons=pCons->pLink){
   536         -      zCols = idxAppendColDefn(&rc, zCols, pTab, pCons);
   537         -    }
   538         -
   539         -    if( rc==SQLITE_OK ){
   540         -      /* Hash the list of columns to come up with a name for the index */
   541         -      int i;
   542         -      for(i=0; zCols[i]; i++){
   543         -        h += ((h<<3) + zCols[i]);
   544         -      }
   545         -
   546         -      if( idxIdentifierRequiresQuotes(pScan->zTable) ){
   547         -        zFmt = "CREATE INDEX '%q_idx_%08x' ON %Q(%s)";
   548         -      }else{
   549         -        zFmt = "CREATE INDEX %s_idx_%08x ON %s(%s)";
   550         -      }
   551         -      zIdx = sqlite3_mprintf(zFmt, pScan->zTable, h, pScan->zTable, zCols);
   552         -      if( !zIdx ){
   553         -        rc = SQLITE_NOMEM;
   554         -      }else{
   555         -        rc = sqlite3_exec(dbm, zIdx, 0, 0, pCtx->pzErrmsg);
   556         -#if 0
   557         -        printf("CANDIDATE: %s\n", zIdx);
   558         -#endif
   559         -      }
   560         -    }
   561         -    if( rc==SQLITE_OK && pCtx->iIdxRowid==0 ){
   562         -      int rc2;
   563         -      sqlite3_stmt *pLast = 0;
   564         -      rc = idxPrepareStmt(dbm, &pLast, pCtx->pzErrmsg, 
   565         -          "SELECT max(rowid) FROM sqlite_master"
   566         -      );
   567         -      if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pLast) ){
   568         -        pCtx->iIdxRowid = sqlite3_column_int64(pLast, 0);
   569         -      }
   570         -      rc2 = sqlite3_finalize(pLast);
   571         -      if( rc==SQLITE_OK ) rc = rc2;
   572         -    }
   573         -
   574         -    sqlite3_free(zIdx);
   575         -    sqlite3_free(zCols);
   576         -  }
   577         -  return rc;
   578         -}
   579         -
   580         -static int idxCreateFromWhere(
   581         -    IdxContext*, i64, IdxScan*, IdxWhere*, IdxConstraint*, IdxConstraint*
   582         -);
   583         -
   584         -/*
   585         -** Return true if list pList (linked by IdxConstraint.pLink) contains
   586         -** a constraint compatible with *p. Otherwise return false.
   587         -*/
   588         -static int idxFindConstraint(IdxConstraint *pList, IdxConstraint *p){
   589         -  IdxConstraint *pCmp;
   590         -  for(pCmp=pList; pCmp; pCmp=pCmp->pLink){
   591         -    if( p->iCol==pCmp->iCol ) return 1;
   592         -  }
   593         -  return 0;
   594         -}
   595         -
   596         -static int idxCreateFromWhere(
   597         -  IdxContext *pCtx, 
   598         -  i64 mask,                       /* Consider only these constraints */
   599         -  IdxScan *pScan,                 /* Create indexes for this scan */
   600         -  IdxWhere *pWhere,               /* Read constraints from here */
   601         -  IdxConstraint *pEq,             /* == constraints for inclusion */
   602         -  IdxConstraint *pTail            /* range/ORDER BY constraints for inclusion */
   603         -){
   604         -  IdxConstraint *p1 = pEq;
   605         -  IdxConstraint *pCon;
   606         -  int rc;
   607         -
   608         -  /* Gather up all the == constraints that match the mask. */
   609         -  for(pCon=pWhere->pEq; pCon; pCon=pCon->pNext){
   610         -    if( (mask & pCon->depmask)==pCon->depmask 
   611         -     && idxFindConstraint(p1, pCon)==0
   612         -     && idxFindConstraint(pTail, pCon)==0
   613         -    ){
   614         -      pCon->pLink = p1;
   615         -      p1 = pCon;
   616         -    }
   617         -  }
   618         -
   619         -  /* Create an index using the == constraints collected above. And the
   620         -  ** range constraint/ORDER BY terms passed in by the caller, if any. */
   621         -  rc = idxCreateFromCons(pCtx, pScan, p1, pTail);
   622         -
   623         -  /* If no range/ORDER BY passed by the caller, create a version of the
   624         -  ** index for each range constraint that matches the mask. */
   625         -  if( pTail==0 ){
   626         -    for(pCon=pWhere->pRange; rc==SQLITE_OK && pCon; pCon=pCon->pNext){
   627         -      assert( pCon->pLink==0 );
   628         -      if( (mask & pCon->depmask)==pCon->depmask
   629         -        && idxFindConstraint(pEq, pCon)==0
   630         -        && idxFindConstraint(pTail, pCon)==0
   631         -      ){
   632         -        rc = idxCreateFromCons(pCtx, pScan, p1, pCon);
   633         -      }
   634         -    }
   635         -  }
   636         -
   637         -  return rc;
   638         -}
   639         -
   640         -/*
   641         -** Create candidate indexes in database [dbm] based on the data in 
   642         -** linked-list pScan.
   643         -*/
   644         -static int idxCreateCandidates(IdxContext *pCtx){
   645         -  sqlite3 *dbm = pCtx->dbm;
   646         -  int rc2;
   647         -  int rc = SQLITE_OK;
   648         -  sqlite3_stmt *pDepmask;         /* Foreach depmask */
   649         -  IdxScan *pIter;
   650         -
   651         -  rc = idxPrepareStmt(dbm, &pDepmask, pCtx->pzErrmsg, 
   652         -      "SELECT mask FROM depmask"
   653         -  );
   654         -
   655         -  for(pIter=pCtx->pScan; pIter && rc==SQLITE_OK; pIter=pIter->pNextScan){
   656         -    IdxWhere *pWhere = &pIter->where;
   657         -    while( SQLITE_ROW==sqlite3_step(pDepmask) && rc==SQLITE_OK ){
   658         -      i64 mask = sqlite3_column_int64(pDepmask, 0);
   659         -      rc = idxCreateFromWhere(pCtx, mask, pIter, pWhere, 0, 0);
   660         -      if( rc==SQLITE_OK && pIter->pOrder ){
   661         -        rc = idxCreateFromWhere(pCtx, mask, pIter, pWhere, 0, pIter->pOrder);
   662         -      }
   663         -    }
   664         -  }
   665         -
   666         -  rc2 = sqlite3_finalize(pDepmask);
   667         -  if( rc==SQLITE_OK ) rc = rc2;
   668         -  return rc;
   669         -}
   670         -
   671         -static void idxScanFree(IdxScan *pScan){
   672         -  IdxScan *pIter;
   673         -  IdxScan *pNext;
   674         -  for(pIter=pScan; pIter; pIter=pNext){
   675         -    pNext = pIter->pNextScan;
   676         -  }
   677         -}
   678         -
   679         -int idxFindIndexes(
   680         -  IdxContext *pCtx,
   681         -  const char *zSql,                    /* SQL to find indexes for */
   682         -  void (*xOut)(void*, const char*),    /* Output callback */
   683         -  void *pOutCtx,                       /* Context for xOut() */
   684         -  char **pzErr                         /* OUT: Error message (sqlite3_malloc) */
   685         -){
   686         -  sqlite3 *dbm = pCtx->dbm;
   687         -  sqlite3_stmt *pExplain = 0;
   688         -  sqlite3_stmt *pSelect = 0;
   689         -  sqlite3_stmt *pInsert = 0;
   690         -  int rc, rc2;
   691         -  int bFound = 0;
   692         -
   693         -  rc = idxPrintfPrepareStmt(dbm, &pExplain, pzErr,"EXPLAIN QUERY PLAN %s",zSql);
   694         -  if( rc==SQLITE_OK ){
   695         -    rc = idxPrepareStmt(dbm, &pSelect, pzErr, 
   696         -        "SELECT rowid, sql FROM sqlite_master WHERE name = ?"
   697         -    );
   698         -  }
   699         -  if( rc==SQLITE_OK ){
   700         -    rc = idxPrepareStmt(dbm, &pInsert, pzErr,
   701         -        "INSERT OR IGNORE INTO aux.indexes VALUES(?)"
   702         -    );
   703         -  }
   704         -
   705         -  while( rc==SQLITE_OK && sqlite3_step(pExplain)==SQLITE_ROW ){
   706         -    int i;
   707         -    const char *zDetail = (const char*)sqlite3_column_text(pExplain, 3);
   708         -    int nDetail = strlen(zDetail);
   709         -
   710         -    for(i=0; i<nDetail; i++){
   711         -      const char *zIdx = 0;
   712         -      if( memcmp(&zDetail[i], " USING INDEX ", 13)==0 ){
   713         -        zIdx = &zDetail[i+13];
   714         -      }else if( memcmp(&zDetail[i], " USING COVERING INDEX ", 22)==0 ){
   715         -        zIdx = &zDetail[i+22];
   716         -      }
   717         -      if( zIdx ){
   718         -        int nIdx = 0;
   719         -        while( zIdx[nIdx]!='\0' && (zIdx[nIdx]!=' ' || zIdx[nIdx+1]!='(') ){
   720         -          nIdx++;
   721         -        }
   722         -        sqlite3_bind_text(pSelect, 1, zIdx, nIdx, SQLITE_STATIC);
   723         -        if( SQLITE_ROW==sqlite3_step(pSelect) ){
   724         -          i64 iRowid = sqlite3_column_int64(pSelect, 0);
   725         -          const char *zSql = (const char*)sqlite3_column_text(pSelect, 1);
   726         -          if( iRowid>=pCtx->iIdxRowid ){
   727         -            sqlite3_bind_text(pInsert, 1, zSql, -1, SQLITE_STATIC);
   728         -            sqlite3_step(pInsert);
   729         -            rc = sqlite3_reset(pInsert);
   730         -            if( rc ) goto find_indexes_out;
   731         -          }
   732         -        }
   733         -        rc = sqlite3_reset(pSelect);
   734         -        break;
   735         -      }
   736         -    }
   737         -  }
   738         -  rc2 = sqlite3_reset(pExplain);
   739         -  if( rc==SQLITE_OK ) rc = rc2;
   740         -  if( rc==SQLITE_OK ){
   741         -    sqlite3_stmt *pLoop = 0;
   742         -    rc = idxPrepareStmt(dbm, &pLoop, pzErr,"SELECT name||';' FROM aux.indexes");
   743         -    if( rc==SQLITE_OK ){
   744         -      while( SQLITE_ROW==sqlite3_step(pLoop) ){
   745         -        bFound = 1;
   746         -        xOut(pOutCtx, (const char*)sqlite3_column_text(pLoop, 0));
   747         -      }
   748         -      rc = sqlite3_finalize(pLoop);
   749         -    }
   750         -    if( rc==SQLITE_OK ){
   751         -      if( bFound==0 ) xOut(pOutCtx, "(no new indexes)");
   752         -      xOut(pOutCtx, "");
   753         -    }
   754         -  }
   755         -
   756         -  while( rc==SQLITE_OK && sqlite3_step(pExplain)==SQLITE_ROW ){
   757         -    int iSelectid = sqlite3_column_int(pExplain, 0);
   758         -    int iOrder = sqlite3_column_int(pExplain, 1);
   759         -    int iFrom = sqlite3_column_int(pExplain, 2);
   760         -    const char *zDetail = (const char*)sqlite3_column_text(pExplain, 3);
   761         -    char *zOut;
   762         -
   763         -    zOut = sqlite3_mprintf("%d|%d|%d|%s", iSelectid, iOrder, iFrom, zDetail);
   764         -    if( zOut==0 ){
   765         -      rc = SQLITE_NOMEM;
   766         -    }else{
   767         -      xOut(pOutCtx, zOut);
   768         -      sqlite3_free(zOut);
   769         -    }
   770         -  }
   771         -
   772         - find_indexes_out:
   773         -  rc2 = sqlite3_finalize(pExplain);
   774         -  if( rc==SQLITE_OK ) rc = rc2;
   775         -  rc2 = sqlite3_finalize(pSelect);
   776         -  if( rc==SQLITE_OK ) rc = rc2;
   777         -  rc2 = sqlite3_finalize(pInsert);
   778         -  if( rc==SQLITE_OK ) rc = rc2;
   779         -
   780         -  return rc;
   781         -}
   782         -
   783         -/*
   784         -** The xOut callback is invoked to return command output to the user. The
   785         -** second argument is always a nul-terminated string. The first argument is
   786         -** passed zero if the string contains normal output or non-zero if it is an
   787         -** error message.
   788         -*/
   789         -int shellIndexesCommand(
   790         -  sqlite3 *db,                         /* Database handle */
   791         -  const char *zSql,                    /* SQL to find indexes for */
   792         -  void (*xOut)(void*, const char*),    /* Output callback */
   793         -  void *pOutCtx,                       /* Context for xOut() */
   794         -  char **pzErrmsg                      /* OUT: Error message (sqlite3_malloc) */
   795         -){
   796         -  int rc = SQLITE_OK;
   797         -  sqlite3 *dbm = 0;
   798         -  IdxContext ctx;
   799         -  sqlite3_stmt *pStmt = 0;        /* Statement compiled from zSql */
   800         -
   801         -  memset(&ctx, 0, sizeof(IdxContext));
   802         -  ctx.pzErrmsg = pzErrmsg;
   803         -
   804         -  /* Open an in-memory database to work with. The main in-memory 
   805         -  ** database schema contains tables similar to those in the users 
   806         -  ** database (handle db). The attached in-memory db (aux) contains
   807         -  ** application tables used by the code in this file.  */
   808         -  rc = sqlite3_open(":memory:", &dbm);
   809         -  if( rc==SQLITE_OK ){
   810         -    rc = sqlite3_exec(dbm, 
   811         -        "ATTACH ':memory:' AS aux;"
   812         -        "CREATE TABLE aux.depmask(mask PRIMARY KEY) WITHOUT ROWID;"
   813         -        "CREATE TABLE aux.indexes(name PRIMARY KEY) WITHOUT ROWID;"
   814         -        "INSERT INTO aux.depmask VALUES(0);"
   815         -        , 0, 0, pzErrmsg
   816         -    );
   817         -  }
   818         -
   819         -  /* Prepare an INSERT statement for writing to aux.depmask */
   820         -  if( rc==SQLITE_OK ){
   821         -    rc = idxPrepareStmt(dbm, &ctx.pInsertMask, pzErrmsg,
   822         -        "INSERT OR IGNORE INTO aux.depmask SELECT mask | ?1 FROM aux.depmask;"
   823         -    );
   824         -  }
   825         -
   826         -  /* Analyze the SELECT statement in zSql. */
   827         -  if( rc==SQLITE_OK ){
   828         -    ctx.dbm = dbm;
   829         -    sqlite3_whereinfo_hook(db, idxWhereInfo, (void*)&ctx);
   830         -    rc = idxPrepareStmt(db, &pStmt, pzErrmsg, zSql);
   831         -    sqlite3_whereinfo_hook(db, 0, 0);
   832         -    sqlite3_finalize(pStmt);
   833         -  }
   834         -
   835         -  /* Create tables within the main in-memory database. These tables
   836         -  ** have the same names, columns and declared types as the tables in
   837         -  ** the user database. All constraints except for PRIMARY KEY are
   838         -  ** removed. */
   839         -  if( rc==SQLITE_OK ){
   840         -    rc = idxCreateTables(db, dbm, ctx.pScan, pzErrmsg);
   841         -  }
   842         -
   843         -  /* Create candidate indexes within the in-memory database file */
   844         -  if( rc==SQLITE_OK ){
   845         -    rc = idxCreateCandidates(&ctx);
   846         -  }
   847         -
   848         -  /* Figure out which of the candidate indexes are preferred by the query
   849         -  ** planner and report the results to the user.  */
   850         -  if( rc==SQLITE_OK ){
   851         -    rc = idxFindIndexes(&ctx, zSql, xOut, pOutCtx, pzErrmsg);
   852         -  }
   853         -
   854         -  idxScanFree(ctx.pScan);
   855         -  sqlite3_finalize(ctx.pInsertMask);
   856         -  sqlite3_close(dbm);
   857         -  return rc;
   858         -}
   859         -
   860         -