/ Check-in [bf10e68d]
Login

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

Overview
Comment:Use hash tables instead of in-memory database tables for a few purposes in sqlite3expert.c.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | schemalint
Files: files | file ages | folders
SHA3-256: bf10e68d9e4d5eae7ae6148a7ad64c9596f2ed8ccd36065adb09a1f9e7dae50b
User & Date: dan 2017-04-08 17:41:24
Context
2017-04-08
18:56
Rename shell6.test to expert1.test. Have it invoke the sqlite3_expert binary if it is present. check-in: be0deff9 user: dan tags: schemalint
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
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/expert/sqlite3expert.c.

    96     96     char **pzErrmsg;
    97     97     IdxWhere *pCurrent;             /* Current where clause */
    98     98     int rc;                         /* Error code (if error has occurred) */
    99     99     IdxScan *pScan;                 /* List of scan objects */
   100    100     sqlite3 *dbm;                   /* In-memory db for this analysis */
   101    101     sqlite3 *db;                    /* User database under analysis */
   102    102     sqlite3_stmt *pInsertMask;      /* To write to aux.depmask */
   103         -  i64 iIdxRowid;                  /* Rowid of first index created */
   104    103   };
   105    104   
   106    105   struct IdxStatement {
   107    106     int iId;                        /* Statement number */
   108    107     char *zSql;                     /* SQL statement */
   109    108     char *zIdx;                     /* Indexes */
   110    109     char *zEQP;                     /* Plan */
   111    110     IdxStatement *pNext;
   112    111   };
   113    112   
          113  +
          114  +#define IDX_HASH_SIZE 1023
          115  +typedef struct IdxHashEntry IdxHashEntry;
          116  +typedef struct IdxHash IdxHash;
          117  +struct IdxHashEntry {
          118  +  char *zKey;                     /* nul-terminated key */
          119  +  char *zVal;                     /* nul-terminated value string */
          120  +  IdxHashEntry *pHashNext;        /* Next entry in same hash bucket */
          121  +  IdxHashEntry *pNext;            /* Next entry in hash */
          122  +};
          123  +struct IdxHash {
          124  +  IdxHashEntry *pFirst;
          125  +  IdxHashEntry *aHash[IDX_HASH_SIZE];
          126  +};
          127  +
   114    128   /*
   115    129   ** sqlite3expert object.
   116    130   */
   117    131   struct sqlite3expert {
   118    132     sqlite3 *db;                    /* Users database */
   119    133     sqlite3 *dbm;                   /* In-memory db for this analysis */
   120    134   
   121    135     int bRun;                       /* True once analysis has run */
   122    136     char **pzErrmsg;
   123    137   
   124    138     IdxScan *pScan;                 /* List of scan objects */
   125    139     IdxStatement *pStatement;       /* List of IdxStatement objects */
   126    140     int rc;                         /* Error code from whereinfo hook */
   127         -  i64 iIdxRowid;                  /* Rowid of first index created */
          141  +
          142  +  IdxHash hIdx;                   /* Hash containing all candidate indexes */
   128    143   };
   129    144   
   130    145   
   131    146   /*
   132    147   ** Allocate and return nByte bytes of zeroed memory using sqlite3_malloc(). 
   133    148   ** If the allocation fails, set *pRc to SQLITE_NOMEM and return NULL.
   134    149   */
................................................................................
   141    156       memset(pRet, 0, nByte);
   142    157     }else{
   143    158       *pRc = SQLITE_NOMEM;
   144    159     }
   145    160     return pRet;
   146    161   }
   147    162   
          163  +/*************************************************************************
          164  +** Start of hash table implementations.
          165  +*/
          166  +typedef struct IdxHash64Entry IdxHash64Entry;
          167  +typedef struct IdxHash64 IdxHash64;
          168  +struct IdxHash64Entry {
          169  +  u64 iVal;
          170  +  IdxHash64Entry *pNext;          /* Next entry in hash table */
          171  +  IdxHash64Entry *pHashNext;      /* Next entry in same hash bucket */
          172  +};
          173  +struct IdxHash64 {
          174  +  IdxHash64Entry *pFirst;         /* Most recently added entry in hash table */
          175  +  IdxHash64Entry *aHash[IDX_HASH_SIZE];
          176  +};
          177  +
          178  +static void idxHash64Init(IdxHash64 *pHash){
          179  +  memset(pHash, 0, sizeof(IdxHash64));
          180  +}
          181  +static void idxHash64Clear(IdxHash64 *pHash){
          182  +  IdxHash64Entry *pEntry;
          183  +  IdxHash64Entry *pNext;
          184  +  for(pEntry=pHash->pFirst; pEntry; pEntry=pNext){
          185  +    pNext = pEntry->pNext;
          186  +    sqlite3_free(pEntry);
          187  +  }
          188  +  memset(pHash, 0, sizeof(IdxHash64));
          189  +}
          190  +static void idxHash64Add(int *pRc, IdxHash64 *pHash, u64 iVal){
          191  +  int iHash = (int)((iVal*7) % IDX_HASH_SIZE);
          192  +  IdxHash64Entry *pEntry;
          193  +  assert( iHash>=0 );
          194  +
          195  +  for(pEntry=pHash->aHash[iHash]; pEntry; pEntry=pEntry->pHashNext){
          196  +    if( pEntry->iVal==iVal ) return;
          197  +  }
          198  +  pEntry = idxMalloc(pRc, sizeof(IdxHash64Entry));
          199  +  if( pEntry ){
          200  +    pEntry->iVal = iVal;
          201  +    pEntry->pHashNext = pHash->aHash[iHash];
          202  +    pHash->aHash[iHash] = pEntry;
          203  +    pEntry->pNext = pHash->pFirst;
          204  +    pHash->pFirst = pEntry;
          205  +  }
          206  +}
          207  +
          208  +static void idxHashInit(IdxHash *pHash){
          209  +  memset(pHash, 0, sizeof(IdxHash));
          210  +}
          211  +static void idxHashClear(IdxHash *pHash){
          212  +  int i;
          213  +  for(i=0; i<IDX_HASH_SIZE; i++){
          214  +    IdxHashEntry *pEntry;
          215  +    IdxHashEntry *pNext;
          216  +    for(pEntry=pHash->aHash[i]; pEntry; pEntry=pNext){
          217  +      pNext = pEntry->pHashNext;
          218  +      sqlite3_free(pEntry);
          219  +    }
          220  +  }
          221  +  memset(pHash, 0, sizeof(IdxHash));
          222  +}
          223  +static int idxHashString(const char *z, int n){
          224  +  unsigned int ret = 0;
          225  +  int i;
          226  +  for(i=0; i<n; i++){
          227  +    ret += (ret<<3) + (unsigned char)(z[i]);
          228  +  }
          229  +  return (int)(ret % IDX_HASH_SIZE);
          230  +}
          231  +
          232  +static int idxHashAdd(
          233  +  int *pRc, 
          234  +  IdxHash *pHash, 
          235  +  const char *zKey,
          236  +  const char *zVal
          237  +){
          238  +  int nKey = strlen(zKey);
          239  +  int iHash = idxHashString(zKey, nKey);
          240  +  int nVal = (zVal ? strlen(zVal) : 0);
          241  +  IdxHashEntry *pEntry;
          242  +  assert( iHash>=0 );
          243  +  for(pEntry=pHash->aHash[iHash]; pEntry; pEntry=pEntry->pHashNext){
          244  +    if( strlen(pEntry->zKey)==nKey && 0==memcmp(pEntry->zKey, zKey, nKey) ){
          245  +      return 1;
          246  +    }
          247  +  }
          248  +  pEntry = idxMalloc(pRc, sizeof(IdxHashEntry) + nKey+1 + nVal+1);
          249  +  if( pEntry ){
          250  +    pEntry->zKey = (char*)&pEntry[1];
          251  +    memcpy(pEntry->zKey, zKey, nKey);
          252  +    if( zVal ){
          253  +      pEntry->zVal = &pEntry->zKey[nKey+1];
          254  +      memcpy(pEntry->zVal, zVal, nVal);
          255  +    }
          256  +    pEntry->pHashNext = pHash->aHash[iHash];
          257  +    pHash->aHash[iHash] = pEntry;
          258  +
          259  +    pEntry->pNext = pHash->pFirst;
          260  +    pHash->pFirst = pEntry;
          261  +  }
          262  +  return 0;
          263  +}
          264  +
          265  +static const char *idxHashSearch(IdxHash *pHash, const char *zKey, int nKey){
          266  +  int iHash;
          267  +  IdxHashEntry *pEntry;
          268  +  if( nKey<0 ) nKey = strlen(zKey);
          269  +  iHash = idxHashString(zKey, nKey);
          270  +  assert( iHash>=0 );
          271  +  for(pEntry=pHash->aHash[iHash]; pEntry; pEntry=pEntry->pHashNext){
          272  +    if( strlen(pEntry->zKey)==nKey && 0==memcmp(pEntry->zKey, zKey, nKey) ){
          273  +      return pEntry->zVal;
          274  +    }
          275  +  }
          276  +  return 0;
          277  +}
          278  +
          279  +/*
          280  +** End of hash table implementations.
          281  +**************************************************************************/
          282  +
   148    283   /*
   149    284   ** Allocate and return a new IdxConstraint object. Set the IdxConstraint.zColl
   150    285   ** variable to point to a copy of nul-terminated string zColl.
   151    286   */
   152    287   static IdxConstraint *idxNewConstraint(int *pRc, const char *zColl){
   153    288     IdxConstraint *pNew;
   154    289     int nColl = strlen(zColl);
................................................................................
   225    360           if( eOp==SQLITE_WHEREINFO_RANGE ){
   226    361             pNew->pNext = p->pScan->where.pRange;
   227    362             p->pScan->where.pRange = pNew;
   228    363           }else{
   229    364             pNew->pNext = p->pScan->where.pEq;
   230    365             p->pScan->where.pEq = pNew;
   231    366           }
   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    367           break;
   238    368         }
   239    369       }
   240    370     }
   241    371   }
   242    372   
   243    373   /*
................................................................................
   282    412     }else{
   283    413       rc = idxPrepareStmt(db, ppStmt, pzErrmsg, zSql);
   284    414       sqlite3_free(zSql);
   285    415     }
   286    416     va_end(ap);
   287    417     return rc;
   288    418   }
          419  +
          420  +static void idxFinalize(int *pRc, sqlite3_stmt *pStmt){
          421  +  int rc = sqlite3_finalize(pStmt);
          422  +  if( *pRc==SQLITE_OK ) *pRc = rc;
          423  +}
   289    424   
   290    425   static int idxGetTableInfo(
   291    426     sqlite3 *db,
   292    427     IdxScan *pScan,
   293    428     char **pzErrmsg
   294    429   ){
   295    430     const char *zTbl = pScan->zTable;
................................................................................
   340    475         pNew->aCol[nCol].zColl = pCsr;
   341    476         memcpy(pCsr, zCol, nCopy);
   342    477         pCsr += nCopy;
   343    478       }
   344    479   
   345    480       nCol++;
   346    481     }
   347         -  rc2 = sqlite3_finalize(p1);
   348         -  if( rc==SQLITE_OK ) rc = rc2;
          482  +  idxFinalize(&rc, p1);
   349    483   
   350    484     if( rc==SQLITE_OK ){
   351    485       pScan->pTable = pNew;
   352    486     }else{
   353    487       sqlite3_free(pNew);
   354    488     }
   355    489   
................................................................................
   452    586     IdxConstraint *pEq,             /* List of == constraints */
   453    587     IdxConstraint *pTail            /* List of range constraints */
   454    588   ){
   455    589     const char *zTbl = pScan->zTable;
   456    590     sqlite3_stmt *pIdxList = 0;
   457    591     IdxConstraint *pIter;
   458    592     int nEq = 0;                    /* Number of elements in pEq */
   459         -  int rc, rc2;
          593  +  int rc;
   460    594   
   461    595     /* Count the elements in list pEq */
   462    596     for(pIter=pEq; pIter; pIter=pIter->pLink) nEq++;
   463    597   
   464    598     rc = idxPrintfPrepareStmt(dbm, &pIdxList, 0, "PRAGMA index_list=%Q", zTbl);
   465    599     while( rc==SQLITE_OK && sqlite3_step(pIdxList)==SQLITE_ROW ){
   466    600       int bMatch = 1;
................................................................................
   495    629               bMatch = 0;
   496    630               break;
   497    631             }
   498    632             pT = pT->pLink;
   499    633           }
   500    634         }
   501    635       }
   502         -    rc2 = sqlite3_finalize(pInfo);
   503         -    if( rc==SQLITE_OK ) rc = rc2;
          636  +    idxFinalize(&rc, pInfo);
   504    637   
   505    638       if( rc==SQLITE_OK && bMatch ){
   506    639         sqlite3_finalize(pIdxList);
   507    640         return 1;
   508    641       }
   509    642     }
   510         -  rc2 = sqlite3_finalize(pIdxList);
   511         -  if( rc==SQLITE_OK ) rc = rc2;
          643  +  idxFinalize(&rc, pIdxList);
   512    644   
   513    645     *pRc = rc;
   514    646     return 0;
   515    647   }
   516    648   
   517    649   static int idxCreateFromCons(
   518    650     sqlite3expert *p,
................................................................................
   535    667       }
   536    668       for(pCons=pTail; pCons; pCons=pCons->pLink){
   537    669         zCols = idxAppendColDefn(&rc, zCols, pTab, pCons);
   538    670       }
   539    671   
   540    672       if( rc==SQLITE_OK ){
   541    673         /* Hash the list of columns to come up with a name for the index */
          674  +      char *zName;                /* Index name */
   542    675         int i;
   543    676         for(i=0; zCols[i]; i++){
   544    677           h += ((h<<3) + zCols[i]);
   545    678         }
   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 ){
          679  +      zName = sqlite3_mprintf("%s_idx_%08x", pScan->zTable, h);
          680  +      if( zName==0 ){ 
   554    681           rc = SQLITE_NOMEM;
   555    682         }else{
   556         -        rc = sqlite3_exec(dbm, zIdx, 0, 0, p->pzErrmsg);
   557         -#if 1
   558         -        printf("CANDIDATE: %s\n", zIdx);
   559         -#endif
          683  +        if( idxIdentifierRequiresQuotes(pScan->zTable) ){
          684  +          zFmt = "CREATE INDEX '%q' ON %Q(%s)";
          685  +        }else{
          686  +          zFmt = "CREATE INDEX %s ON %s(%s)";
          687  +        }
          688  +        zIdx = sqlite3_mprintf(zFmt, zName, pScan->zTable, zCols);
          689  +        if( !zIdx ){
          690  +          rc = SQLITE_NOMEM;
          691  +        }else{
          692  +          rc = sqlite3_exec(dbm, zIdx, 0, 0, p->pzErrmsg);
          693  +          idxHashAdd(&rc, &p->hIdx, zName, zIdx);
          694  +        }
          695  +        sqlite3_free(zName);
          696  +        sqlite3_free(zIdx);
   560    697         }
   561    698       }
   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    699   
   575         -    sqlite3_free(zIdx);
   576    700       sqlite3_free(zCols);
   577    701     }
   578    702     return rc;
   579    703   }
   580    704   
   581         -static int idxCreateFromWhere(
   582         -    sqlite3expert*, i64, IdxScan*, IdxWhere*, IdxConstraint*, IdxConstraint*
   583         -);
   584         -
   585    705   /*
   586    706   ** Return true if list pList (linked by IdxConstraint.pLink) contains
   587    707   ** a constraint compatible with *p. Otherwise return false.
   588    708   */
   589    709   static int idxFindConstraint(IdxConstraint *pList, IdxConstraint *p){
   590    710     IdxConstraint *pCmp;
   591    711     for(pCmp=pList; pCmp; pCmp=pCmp->pLink){
................................................................................
   639    759   }
   640    760   
   641    761   /*
   642    762   ** Create candidate indexes in database [dbm] based on the data in 
   643    763   ** linked-list pScan.
   644    764   */
   645    765   static int idxCreateCandidates(sqlite3expert *p, char **pzErr){
   646         -  sqlite3 *dbm = p->dbm;
   647         -  int rc2;
   648    766     int rc = SQLITE_OK;
   649         -  sqlite3_stmt *pDepmask = 0;     /* Foreach depmask */
   650         -  sqlite3_stmt *pInsert = 0;      /* insert */
   651    767     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         -  }
          768  +  IdxHash64 hMask;
          769  +  idxHash64Init(&hMask);
   659    770   
   660    771     for(pIter=p->pScan; pIter && rc==SQLITE_OK; pIter=pIter->pNextScan){
          772  +    IdxHash64Entry *pEntry;
   661    773       IdxWhere *pWhere = &pIter->where;
   662    774       IdxConstraint *pCons;
   663         -    rc = sqlite3_exec(dbm, 
   664         -        "DELETE FROM aux.depmask;"
   665         -        "INSERT INTO aux.depmask VALUES(0);"
   666         -        , 0, 0, pzErr
   667         -    );
          775  +
          776  +    idxHash64Add(&rc, &hMask, 0);
   668    777       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);
          778  +      for(pEntry=hMask.pFirst; pEntry; pEntry=pEntry->pNext){
          779  +        idxHash64Add(&rc, &hMask, pEntry->iVal | (u64)pCons->depmask);
          780  +      }
   672    781       }
   673    782   
   674         -    while( SQLITE_ROW==sqlite3_step(pDepmask) && rc==SQLITE_OK ){
   675         -      i64 mask = sqlite3_column_int64(pDepmask, 0);
          783  +    for(pEntry=hMask.pFirst; pEntry; pEntry=pEntry->pNext){
          784  +      i64 mask = (i64)pEntry->iVal;
   676    785         rc = idxCreateFromWhere(p, mask, pIter, pWhere, 0, 0);
   677    786         if( rc==SQLITE_OK && pIter->pOrder ){
   678    787           rc = idxCreateFromWhere(p, mask, pIter, pWhere, 0, pIter->pOrder);
   679    788         }
   680    789       }
   681         -    rc2 = sqlite3_reset(pDepmask);
   682         -    if( rc==SQLITE_OK ) rc = rc2;
          790  +
          791  +    idxHash64Clear(&hMask);
   683    792     }
   684    793   
   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    794     return rc;
   690    795   }
   691    796   
   692    797   /*
   693    798   ** Free all elements of the linked list starting from pScan up until pLast
   694    799   ** (pLast is not freed).
   695    800   */
................................................................................
   701    806   ** Free all elements of the linked list starting from pStatement up 
   702    807   ** until pLast (pLast is not freed).
   703    808   */
   704    809   static void idxStatementFree(IdxStatement *pStatement, IdxStatement *pLast){
   705    810     /* TODO! */
   706    811   }
   707    812   
   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    813   
   718    814   int idxFindIndexes(
   719    815     sqlite3expert *p,
   720    816     char **pzErr                         /* OUT: Error message (sqlite3_malloc) */
   721    817   ){
   722    818     IdxStatement *pStmt;
   723    819     sqlite3 *dbm = p->dbm;
   724         -  sqlite3_stmt *pSelect = 0;
   725         -  sqlite3_stmt *pInsert = 0;
   726         -  int rc, rc2;
   727         -  int bFound = 0;
          820  +  int rc = SQLITE_OK;
   728    821   
   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         -  }
          822  +  IdxHash hIdx;
          823  +  idxHashInit(&hIdx);
   739    824   
   740    825     for(pStmt=p->pStatement; rc==SQLITE_OK && pStmt; pStmt=pStmt->pNext){
          826  +    IdxHashEntry *pEntry;
   741    827       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         -    }
          828  +    idxHashClear(&hIdx);
          829  +    rc = idxPrintfPrepareStmt(dbm, &pExplain, pzErr,
          830  +        "EXPLAIN QUERY PLAN %s", pStmt->zSql
          831  +    );
   748    832       while( rc==SQLITE_OK && sqlite3_step(pExplain)==SQLITE_ROW ){
   749         -      int i;
          833  +      int iSelectid = sqlite3_column_int(pExplain, 0);
          834  +      int iOrder = sqlite3_column_int(pExplain, 1);
          835  +      int iFrom = sqlite3_column_int(pExplain, 2);
   750    836         const char *zDetail = (const char*)sqlite3_column_text(pExplain, 3);
   751    837         int nDetail = strlen(zDetail);
          838  +      int i;
   752    839   
   753    840         for(i=0; i<nDetail; i++){
   754    841           const char *zIdx = 0;
   755    842           if( memcmp(&zDetail[i], " USING INDEX ", 13)==0 ){
   756    843             zIdx = &zDetail[i+13];
   757    844           }else if( memcmp(&zDetail[i], " USING COVERING INDEX ", 22)==0 ){
   758    845             zIdx = &zDetail[i+22];
   759    846           }
   760    847           if( zIdx ){
          848  +          const char *zSql;
   761    849             int nIdx = 0;
   762    850             while( zIdx[nIdx]!='\0' && (zIdx[nIdx]!=' ' || zIdx[nIdx+1]!='(') ){
   763    851               nIdx++;
   764    852             }
   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         -            }
          853  +          zSql = idxHashSearch(&p->hIdx, zIdx, nIdx);
          854  +          if( zSql ){
          855  +            idxHashAdd(&rc, &hIdx, zSql, 0);
          856  +            if( rc ) goto find_indexes_out;
   775    857             }
   776         -          rc = sqlite3_reset(pSelect);
   777    858             break;
   778    859           }
   779    860         }
   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    861   
   805    862         pStmt->zEQP = idxAppendText(&rc, pStmt->zEQP, "%d|%d|%d|%s\n", 
   806    863             iSelectid, iOrder, iFrom, zDetail
   807    864         );
   808    865       }
   809    866   
   810         -    rc2 = sqlite3_finalize(pExplain);
   811         -    if( rc==SQLITE_OK ) rc = rc2;
          867  +    for(pEntry=hIdx.pFirst; pEntry; pEntry=pEntry->pNext){
          868  +      pStmt->zIdx = idxAppendText(&rc, pStmt->zIdx, "%s\n", pEntry->zKey);
          869  +    }
          870  +    if( pStmt->zIdx==0 ){
          871  +      pStmt->zIdx = idxAppendText(&rc, 0, "(no new indexes)\n");
          872  +    }
          873  +
          874  +    idxFinalize(&rc, pExplain);
   812    875     }
   813    876   
   814    877    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    878     return rc;
   821    879   }
   822    880   
   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    881   /*
   905    882   ** Allocate a new sqlite3expert object.
   906    883   */
   907    884   sqlite3expert *sqlite3_expert_new(sqlite3 *db, char **pzErrmsg){
   908    885     int rc = SQLITE_OK;
   909    886     sqlite3expert *pNew;
   910    887   
   911    888     pNew = (sqlite3expert*)idxMalloc(&rc, sizeof(sqlite3expert));
   912    889     pNew->db = db;
   913    890   
   914    891     /* Open an in-memory database to work with. The main in-memory 
   915    892     ** 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.  */
          893  +  ** database (handle db).  */
   918    894     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    895   
   928    896     /* Copy the entire schema of database [db] into [dbm]. */
   929    897     if( rc==SQLITE_OK ){
   930    898       sqlite3_stmt *pSql;
   931         -    int rc2;
   932    899       rc = idxPrintfPrepareStmt(pNew->db, &pSql, pzErrmsg, 
   933    900           "SELECT sql FROM sqlite_master WHERE name NOT LIKE 'sqlite_%%'"
   934    901       );
   935    902       while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSql) ){
   936    903         const char *zSql = (const char*)sqlite3_column_text(pSql, 0);
   937    904         rc = sqlite3_exec(pNew->dbm, zSql, 0, 0, pzErrmsg);
   938    905       }
   939         -    rc2 = sqlite3_finalize(pSql);
   940         -    if( rc==SQLITE_OK ) rc = rc2;
          906  +    idxFinalize(&rc, pSql);
   941    907     }
   942    908   
   943    909     /* If an error has occurred, free the new object and reutrn NULL. Otherwise,
   944    910     ** return the new sqlite3expert handle.  */
   945    911     if( rc!=SQLITE_OK ){
   946    912       sqlite3_expert_destroy(pNew);
   947    913       pNew = 0;