/ Check-in [ed49f297]
Login

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

Overview
Comment:Experimental integration of schemalint functionality with the shell tool. Does not work yet.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | schemalint
Files: files | file ages | folders
SHA1: ed49f297bcee86674ed673e195610b8cc1d35647
User & Date: dan 2016-02-11 21:01:16
Context
2016-02-15
20:12
Progress towards integrating schemalint into the shell tool. Some cases work now. check-in: 58d4cf26 user: dan tags: schemalint
2016-02-11
21:01
Experimental integration of schemalint functionality with the shell tool. Does not work yet. check-in: ed49f297 user: dan tags: schemalint
2016-02-09
15:10
Merge latest trunk changes with this branch. check-in: 1a4182ee user: dan tags: schemalint
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to main.mk.

   468    468   #
   469    469   all:	sqlite3.h libsqlite3.a sqlite3$(EXE)
   470    470   
   471    471   libsqlite3.a:	$(LIBOBJ)
   472    472   	$(AR) libsqlite3.a $(LIBOBJ)
   473    473   	$(RANLIB) libsqlite3.a
   474    474   
   475         -sqlite3$(EXE):	$(TOP)/src/shell.c libsqlite3.a sqlite3.h
          475  +sqlite3$(EXE):	$(TOP)/src/shell.c libsqlite3.a sqlite3.h $(TOP)/src/shell_indexes.c
   476    476   	$(TCCX) $(READLINE_FLAGS) -o sqlite3$(EXE) $(SHELL_OPT) \
   477    477   		$(TOP)/src/shell.c libsqlite3.a $(LIBREADLINE) $(TLIBS) $(THREADLIB)
   478    478   
   479    479   sqldiff$(EXE):	$(TOP)/tool/sqldiff.c sqlite3.c sqlite3.h
   480    480   	$(TCCX) -o sqldiff$(EXE) -DSQLITE_THREADSAFE=0 \
   481    481   		$(TOP)/tool/sqldiff.c sqlite3.c $(TLIBS) $(THREADLIB)
   482    482   

Changes to src/main.c.

   788    788       case SQLITE_DBCONFIG_LOOKASIDE: {
   789    789         void *pBuf = va_arg(ap, void*); /* IMP: R-26835-10964 */
   790    790         int sz = va_arg(ap, int);       /* IMP: R-47871-25994 */
   791    791         int cnt = va_arg(ap, int);      /* IMP: R-04460-53386 */
   792    792         rc = setupLookaside(db, pBuf, sz, cnt);
   793    793         break;
   794    794       }
          795  +#ifdef SQLITE_SCHEMA_LINT
          796  +    case SQLITE_DBCONFIG_WHEREINFO: {
          797  +      db->xWhereInfo = va_arg(ap, void(*)(void*, int, const char*, int, i64));
          798  +      db->pWhereInfoCtx = va_arg(ap, void*);
          799  +      break;
          800  +    }
          801  +#endif
   795    802       default: {
   796    803         static const struct {
   797    804           int op;      /* The opcode */
   798    805           u32 mask;    /* Mask of the bit in sqlite3.flags to set/clear */
   799    806         } aFlagOp[] = {
   800    807           { SQLITE_DBCONFIG_ENABLE_FKEY,    SQLITE_ForeignKeys    },
   801    808           { SQLITE_DBCONFIG_ENABLE_TRIGGER, SQLITE_EnableTrigger  },

Changes to src/pragma.c.

  1044   1044     ** the returned data set are:
  1045   1045     **
  1046   1046     ** cid:        Column id (numbered from left to right, starting at 0)
  1047   1047     ** name:       Column name
  1048   1048     ** type:       Column declaration type.
  1049   1049     ** notnull:    True if 'NOT NULL' is part of column declaration
  1050   1050     ** dflt_value: The default value for the column, if any.
         1051  +  ** pk:         Non-zero for PK fields.
  1051   1052     */
  1052   1053     case PragTyp_TABLE_INFO: if( zRight ){
  1053   1054       Table *pTab;
  1054   1055       pTab = sqlite3FindTable(db, zRight, zDb);
  1055   1056       if( pTab ){
  1056   1057         static const char *azCol[] = {
  1057   1058            "cid", "name", "type", "notnull", "dflt_value", "pk"

Changes to src/shell.c.

   152    152     _setmode(_fileno(out), _O_TEXT);
   153    153   }
   154    154   #else
   155    155   # define setBinaryMode(X)
   156    156   # define setTextMode(X)
   157    157   #endif
   158    158   
          159  +#include "shell_indexes.c"
   159    160   
   160    161   /* True if the timer is enabled */
   161    162   static int enableTimer = 0;
   162    163   
   163    164   /* Return the current wall-clock time */
   164    165   static sqlite3_int64 timeOfDay(void){
   165    166     static sqlite3_vfs *clockVfs = 0;
................................................................................
   588    589   ** State information about the database connection is contained in an
   589    590   ** instance of the following structure.
   590    591   */
   591    592   typedef struct ShellState ShellState;
   592    593   struct ShellState {
   593    594     sqlite3 *db;           /* The database */
   594    595     int echoOn;            /* True to echo input commands */
   595         -  int autoEQP;           /* Run EXPLAIN QUERY PLAN prior to seach SQL stmt */
          596  +  int autoEQP;           /* Run EXPLAIN QUERY PLAN prior to each SQL stmt */
          597  +  int bRecommend;        /* Instead of sqlite3_exec(), recommend indexes */
   596    598     int statsOn;           /* True to display memory stats before each finalize */
   597    599     int scanstatsOn;       /* True to display scan stats before each finalize */
   598    600     int countChanges;      /* True to display change counts */
   599    601     int backslashOn;       /* Resolve C-style \x escapes in SQL input text */
   600    602     int outCount;          /* Revert to stdout when reaching zero */
   601    603     int cnt;               /* Number of records displayed so far */
   602    604     FILE *out;             /* Write results here */
................................................................................
  1539   1541   */
  1540   1542   static void explain_data_delete(ShellState *p){
  1541   1543     sqlite3_free(p->aiIndent);
  1542   1544     p->aiIndent = 0;
  1543   1545     p->nIndent = 0;
  1544   1546     p->iIndent = 0;
  1545   1547   }
         1548  +
         1549  +typedef struct RecCommandCtx RecCommandCtx;
         1550  +struct RecCommandCtx {
         1551  +  int (*xCallback)(void*,int,char**,char**,int*);
         1552  +  ShellState *pArg;
         1553  +};
         1554  +
         1555  +static void recCommandOut(void *pCtx, const char *zLine){
         1556  +  const char *zCol = "output";
         1557  +  RecCommandCtx *p = (RecCommandCtx*)pCtx;
         1558  +  int t = SQLITE_TEXT;
         1559  +  p->xCallback(p->pArg, 1, (char**)&zLine, (char**)&zCol, &t);
         1560  +}
  1546   1561   
  1547   1562   /*
  1548   1563   ** Execute a statement or set of statements.  Print 
  1549   1564   ** any result rows/columns depending on the current mode 
  1550   1565   ** set via the supplied callback.
  1551   1566   **
  1552   1567   ** This is very similar to SQLite's built-in sqlite3_exec() 
................................................................................
  1565   1580     int rc = SQLITE_OK;             /* Return Code */
  1566   1581     int rc2;
  1567   1582     const char *zLeftover;          /* Tail of unprocessed SQL */
  1568   1583   
  1569   1584     if( pzErrMsg ){
  1570   1585       *pzErrMsg = NULL;
  1571   1586     }
         1587  +
         1588  +  if( pArg->bRecommend ){
         1589  +    RecCommandCtx ctx;
         1590  +    ctx.xCallback = xCallback;
         1591  +    ctx.pArg = pArg;
         1592  +    rc = shellIndexesCommand(db, zSql, recCommandOut, &ctx, pzErrMsg);
         1593  +  }else
  1572   1594   
  1573   1595     while( zSql[0] && (SQLITE_OK == rc) ){
  1574   1596       rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, &zLeftover);
  1575   1597       if( SQLITE_OK != rc ){
  1576   1598         if( pzErrMsg ){
  1577   1599           *pzErrMsg = save_err_msg(db);
  1578   1600         }
................................................................................
  3605   3627       }else{
  3606   3628         utf8_printf(stderr, "Error: %s\n", sqlite3_errmsg(p->db));
  3607   3629         rc = 1;
  3608   3630       }
  3609   3631       sqlite3_close(pSrc);
  3610   3632     }else
  3611   3633   
         3634  +  if( c=='r' && n>=2 && strncmp(azArg[0], "recommend", n)==0 ){
         3635  +    if( nArg==2 ){
         3636  +      p->bRecommend = booleanValue(azArg[1]);
         3637  +    }else{
         3638  +      raw_printf(stderr, "Usage: .recommend on|off\n");
         3639  +      rc = 1;
         3640  +    }
         3641  +  }else
         3642  +
  3612   3643   
  3613   3644     if( c=='s' && strncmp(azArg[0], "scanstats", n)==0 ){
  3614   3645       if( nArg==2 ){
  3615   3646         p->scanstatsOn = booleanValue(azArg[1]);
  3616   3647   #ifndef SQLITE_ENABLE_STMT_SCANSTATUS
  3617   3648         raw_printf(stderr, "Warning: .scanstats not available in this build.\n");
  3618   3649   #endif
................................................................................
  4899   4930             utf8_printf(stderr,"Error: %s\n", zErrMsg);
  4900   4931             if( bail_on_error ) return rc!=0 ? rc : 1;
  4901   4932           }else if( rc!=0 ){
  4902   4933             utf8_printf(stderr,"Error: unable to process SQL \"%s\"\n", z);
  4903   4934             if( bail_on_error ) return rc;
  4904   4935           }
  4905   4936         }
         4937  +
         4938  +    }else if( strcmp(z, "-recommend") ){
         4939  +      data.bRecommend = 1;
  4906   4940       }else{
  4907   4941         utf8_printf(stderr,"%s: Error: unknown option: %s\n", Argv0, z);
  4908   4942         raw_printf(stderr,"Use -help for a list of options.\n");
  4909   4943         return 1;
  4910   4944       }
  4911   4945     }
  4912   4946   

Added 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  +
           16  +typedef struct IdxConstraint IdxConstraint;
           17  +typedef struct IdxContext IdxContext;
           18  +typedef struct IdxScan IdxScan;
           19  +typedef struct IdxWhere IdxWhere;
           20  +
           21  +/*
           22  +** A single constraint. Equivalent to either "col = ?" or "col < ?".
           23  +**
           24  +** pLink:
           25  +**   ... todo ...
           26  +*/
           27  +struct IdxConstraint {
           28  +  char *zColl;                    /* Collation sequence */
           29  +  int bRange;                     /* True for range, false for eq */
           30  +  int iCol;                       /* Constrained table column */
           31  +  i64 depmask;                    /* Dependency mask */
           32  +  IdxConstraint *pNext;           /* Next constraint in pEq or pRange list */
           33  +  IdxConstraint *pLink;           /* See above */
           34  +};
           35  +
           36  +/*
           37  +** A WHERE clause. Made up of IdxConstraint objects.
           38  +**
           39  +**   a=? AND b=? AND (c=? OR d=?) AND (e=? OR f=?)
           40  +**
           41  +*/
           42  +struct IdxWhere {
           43  +  IdxConstraint *pEq;             /* List of == constraints */
           44  +  IdxConstraint *pRange;          /* List of < constraints */
           45  +  IdxWhere **apOr;                /* Array of OR branches (joined by pNextOr) */
           46  +  IdxWhere *pNextOr;              /* Next in OR'd terms */
           47  +  IdxWhere *pParent;              /* Parent object (or NULL) */
           48  +};
           49  +
           50  +/*
           51  +** A single scan of a single table.
           52  +*/
           53  +struct IdxScan {
           54  +  char *zTable;                   /* Name of table to scan */
           55  +  int iDb;                        /* Database containing table zTable */
           56  +  i64 covering;                   /* Mask of columns required for cov. index */
           57  +  IdxConstraint *pOrder;          /* ORDER BY columns */
           58  +  IdxWhere where;                 /* WHERE Constraints */
           59  +  IdxScan *pNextScan;             /* Next IdxScan object for same query */
           60  +};
           61  +
           62  +/*
           63  +** Context object passed to idxWhereInfo()
           64  +*/
           65  +struct IdxContext {
           66  +  IdxWhere *pCurrent;             /* Current where clause */
           67  +  IdxScan *pScan;                 /* List of scan objects */
           68  +  sqlite3 *dbm;                   /* In-memory db for this analysis */
           69  +  int rc;                         /* Error code (if error has occurred) */
           70  +};
           71  +
           72  +typedef struct PragmaTable PragmaTable;
           73  +typedef struct PragmaCursor PragmaCursor;
           74  +
           75  +struct PragmaTable {
           76  +  sqlite3_vtab base;
           77  +  sqlite3 *db;
           78  +};
           79  +
           80  +struct PragmaCursor {
           81  +  sqlite3_vtab_cursor base;
           82  +  sqlite3_stmt *pStmt;
           83  +  i64 iRowid;
           84  +};
           85  +
           86  +/*
           87  +** Connect to or create a pragma virtual table.
           88  +*/
           89  +static int pragmaConnect(
           90  +  sqlite3 *db,
           91  +  void *pAux,
           92  +  int argc, const char *const*argv,
           93  +  sqlite3_vtab **ppVtab,
           94  +  char **pzErr
           95  +){
           96  +  const char *zSchema = 
           97  +    "CREATE TABLE a(tbl HIDDEN, cid, name, type, notnull, dflt_value, pk)";
           98  +  PragmaTable *pTab = 0;
           99  +  int rc = SQLITE_OK;
          100  +
          101  +  rc = sqlite3_declare_vtab(db, zSchema);
          102  +  if( rc==SQLITE_OK ){
          103  +    pTab = (PragmaTable *)sqlite3_malloc64(sizeof(PragmaTable));
          104  +    if( pTab==0 ) rc = SQLITE_NOMEM;
          105  +  }
          106  +
          107  +  assert( rc==SQLITE_OK || pTab==0 );
          108  +  if( rc==SQLITE_OK ){
          109  +    memset(pTab, 0, sizeof(PragmaTable));
          110  +    pTab->db = db;
          111  +  }
          112  +
          113  +  *ppVtab = (sqlite3_vtab*)pTab;
          114  +  return rc;
          115  +}
          116  +
          117  +/*
          118  +** Disconnect from or destroy a pragma virtual table.
          119  +*/
          120  +static int pragmaDisconnect(sqlite3_vtab *pVtab){
          121  +  sqlite3_free(pVtab);
          122  +  return SQLITE_OK;
          123  +}
          124  +
          125  +/*
          126  +** xBestIndex method for pragma virtual tables.
          127  +*/
          128  +static int pragmaBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
          129  +  int i;
          130  +
          131  +  pIdxInfo->estimatedCost = 1.0e6;  /* Initial cost estimate */
          132  +
          133  +  /* Look for a valid tbl=? constraint. */
          134  +  for(i=0; i<pIdxInfo->nConstraint; i++){
          135  +    if( pIdxInfo->aConstraint[i].usable==0 ) continue;
          136  +    if( pIdxInfo->aConstraint[i].op!=SQLITE_INDEX_CONSTRAINT_EQ ) continue;
          137  +    if( pIdxInfo->aConstraint[i].iColumn!=0 ) continue;
          138  +    pIdxInfo->idxNum = 1;
          139  +    pIdxInfo->estimatedCost = 1.0;
          140  +    pIdxInfo->aConstraintUsage[i].argvIndex = 1;
          141  +    pIdxInfo->aConstraintUsage[i].omit = 1;
          142  +    break;
          143  +  }
          144  +  if( i==pIdxInfo->nConstraint ){
          145  +    tab->zErrMsg = sqlite3_mprintf("missing required tbl=? constraint");
          146  +    return SQLITE_ERROR;
          147  +  }
          148  +  return SQLITE_OK;
          149  +}
          150  +
          151  +/*
          152  +** Open a new pragma cursor.
          153  +*/
          154  +static int pragmaOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){
          155  +  PragmaTable *pTab = (PragmaTable *)pVTab;
          156  +  PragmaCursor *pCsr;
          157  +
          158  +  pCsr = (PragmaCursor*)sqlite3_malloc64(sizeof(PragmaCursor));
          159  +  if( pCsr==0 ){
          160  +    return SQLITE_NOMEM;
          161  +  }else{
          162  +    memset(pCsr, 0, sizeof(PragmaCursor));
          163  +    pCsr->base.pVtab = pVTab;
          164  +  }
          165  +
          166  +  *ppCursor = (sqlite3_vtab_cursor*)pCsr;
          167  +  return SQLITE_OK;
          168  +}
          169  +
          170  +/*
          171  +** Move a statvfs cursor to the next entry in the file.
          172  +*/
          173  +static int pragmaNext(sqlite3_vtab_cursor *pCursor){
          174  +  PragmaCursor *pCsr = (PragmaCursor*)pCursor;
          175  +  int rc = SQLITE_OK;
          176  +
          177  +  if( sqlite3_step(pCsr->pStmt)!=SQLITE_ROW ){
          178  +    rc = sqlite3_finalize(pCsr->pStmt);
          179  +    pCsr->pStmt = 0;
          180  +  }
          181  +  pCsr->iRowid++;
          182  +  return rc;
          183  +}
          184  +
          185  +static int pragmaEof(sqlite3_vtab_cursor *pCursor){
          186  +  PragmaCursor *pCsr = (PragmaCursor*)pCursor;
          187  +  return pCsr->pStmt==0;
          188  +}
          189  +
          190  +static int pragmaFilter(
          191  +  sqlite3_vtab_cursor *pCursor, 
          192  +  int idxNum, const char *idxStr,
          193  +  int argc, sqlite3_value **argv
          194  +){
          195  +  PragmaCursor *pCsr = (PragmaCursor*)pCursor;
          196  +  PragmaTable *pTab = (PragmaTable*)(pCursor->pVtab);
          197  +  char *zSql;
          198  +  const char *zTbl;
          199  +  int rc = SQLITE_OK;
          200  +
          201  +  if( pCsr->pStmt ){
          202  +    sqlite3_finalize(pCsr->pStmt);
          203  +    pCsr->pStmt = 0;
          204  +  }
          205  +  pCsr->iRowid = 0;
          206  +
          207  +  assert( argc==1 );
          208  +  zTbl = (const char*)sqlite3_value_text(argv[0]);
          209  +  zSql = sqlite3_mprintf("PRAGMA table_info(%Q)", zTbl);
          210  +  if( zSql==0 ){
          211  +    rc = SQLITE_NOMEM;
          212  +  }else{
          213  +    rc = sqlite3_prepare_v2(pTab->db, zSql, -1, &pCsr->pStmt, 0);
          214  +  }
          215  +  return pragmaNext(pCursor);;
          216  +}
          217  +
          218  +/*
          219  +** xColumn method.
          220  +*/
          221  +static int pragmaColumn(
          222  +  sqlite3_vtab_cursor *pCursor, 
          223  +  sqlite3_context *ctx, 
          224  +  int iCol
          225  +){
          226  +  PragmaCursor *pCsr = (PragmaCursor *)pCursor;
          227  +  if( iCol>0 ){
          228  +    sqlite3_result_value(ctx, sqlite3_column_value(pCsr->pStmt, iCol-1));
          229  +  }
          230  +  return SQLITE_OK;
          231  +}
          232  +
          233  +static int pragmaRowid(sqlite3_vtab_cursor *pCursor, sqlite_int64 *pRowid){
          234  +  PragmaCursor *pCsr = (PragmaCursor *)pCursor;
          235  +  *pRowid = pCsr->iRowid;
          236  +  return SQLITE_OK;
          237  +}
          238  +
          239  +static int registerPragmaVtabs(sqlite3 *db){
          240  +  static sqlite3_module pragma_module = {
          241  +    0,                            /* iVersion */
          242  +    pragmaConnect,                /* xCreate */
          243  +    pragmaConnect,                /* xConnect */
          244  +    pragmaBestIndex,              /* xBestIndex */
          245  +    pragmaDisconnect,             /* xDisconnect */
          246  +    pragmaDisconnect,             /* xDestroy */
          247  +    pragmaOpen,                   /* xOpen - open a cursor */
          248  +    pragmaClose,                  /* xClose - close a cursor */
          249  +    pragmaFilter,                 /* xFilter - configure scan constraints */
          250  +    pragmaNext,                   /* xNext - advance a cursor */
          251  +    pragmaEof,                    /* xEof - check for end of scan */
          252  +    pragmaColumn,                 /* xColumn - read data */
          253  +    pragmaRowid,                  /* xRowid - read data */
          254  +    0,                            /* xUpdate */
          255  +    0,                            /* xBegin */
          256  +    0,                            /* xSync */
          257  +    0,                            /* xCommit */
          258  +    0,                            /* xRollback */
          259  +    0,                            /* xFindMethod */
          260  +    0,                            /* xRename */
          261  +  };
          262  +  return sqlite3_create_module(db, "pragma_table_info", &pragma_module, 0);
          263  +}
          264  +
          265  +/*
          266  +** Allocate and return nByte bytes of zeroed memory using sqlite3_malloc(). 
          267  +** If the allocation fails, set *pRc to SQLITE_NOMEM and return NULL.
          268  +*/
          269  +static void *idxMalloc(int *pRc, int nByte){
          270  +  void *pRet;
          271  +  assert( *pRc==SQLITE_OK );
          272  +  assert( nByte>0 );
          273  +  pRet = sqlite3_malloc(nByte);
          274  +  if( pRet ){
          275  +    memset(pRet, 0, nByte);
          276  +  }else{
          277  +    *pRc = SQLITE_NOMEM;
          278  +  }
          279  +  return pRet;
          280  +}
          281  +
          282  +/*
          283  +** Allocate and return a new IdxConstraint object. Set the IdxConstraint.zColl
          284  +** variable to point to a copy of nul-terminated string zColl.
          285  +*/
          286  +static IdxConstraint *idxNewConstraint(int *pRc, const char *zColl){
          287  +  IdxConstraint *pNew;
          288  +  int nColl = strlen(zColl);
          289  +
          290  +  assert( *pRc==SQLITE_OK );
          291  +  pNew = (IdxConstraint*)idxMalloc(pRc, sizeof(IdxConstraint) * nColl + 1);
          292  +  if( pNew ){
          293  +    pNew->zColl = (char*)&pNew[1];
          294  +    memcpy(pNew->zColl, zColl, nColl+1);
          295  +  }
          296  +  return pNew;
          297  +}
          298  +
          299  +/*
          300  +** SQLITE_DBCONFIG_WHEREINFO callback.
          301  +*/
          302  +static void idxWhereInfo(
          303  +  void *pCtx,                     /* Pointer to IdxContext structure */
          304  +  int eOp, 
          305  +  const char *zVal, 
          306  +  int iVal, 
          307  +  i64 mask
          308  +){
          309  +  IdxContext *p = (IdxContext*)pCtx;
          310  +
          311  +#if 1
          312  +  const char *zOp = 
          313  +    eOp==SQLITE_WHEREINFO_TABLE ? "TABLE" :
          314  +    eOp==SQLITE_WHEREINFO_EQUALS ? "EQUALS" :
          315  +    eOp==SQLITE_WHEREINFO_RANGE ? "RANGE" :
          316  +    eOp==SQLITE_WHEREINFO_ORDERBY ? "ORDERBY" :
          317  +    eOp==SQLITE_WHEREINFO_NEXTOR ? "NEXTOR" :
          318  +    eOp==SQLITE_WHEREINFO_ENDOR ? "ENDOR" :
          319  +    eOp==SQLITE_WHEREINFO_BEGINOR ? "BEGINOR" :
          320  +    "!error!";
          321  +  printf("op=%s zVal=%s iVal=%d mask=%llx\n", zOp, zVal, iVal, mask);
          322  +#endif
          323  +
          324  +  if( p->rc==SQLITE_OK ){
          325  +    assert( eOp==SQLITE_WHEREINFO_TABLE || p->pScan!=0 );
          326  +    switch( eOp ){
          327  +      case SQLITE_WHEREINFO_TABLE: {
          328  +        int nVal = strlen(zVal);
          329  +        IdxScan *pNew = (IdxScan*)idxMalloc(&p->rc, sizeof(IdxScan) + nVal + 1);
          330  +        if( !pNew ) return;
          331  +        pNew->zTable = (char*)&pNew[1];
          332  +        memcpy(pNew->zTable, zVal, nVal+1);
          333  +        pNew->pNextScan = p->pScan;
          334  +        pNew->covering = mask;
          335  +        p->pScan = pNew;
          336  +        p->pCurrent = &pNew->where;
          337  +        break;
          338  +      }
          339  +
          340  +      case SQLITE_WHEREINFO_ORDERBY: {
          341  +        IdxConstraint *pNew = idxNewConstraint(&p->rc, zVal);
          342  +        IdxConstraint **pp;
          343  +        if( pNew==0 ) return;
          344  +        pNew->iCol = iVal;
          345  +        for(pp=&p->pScan->pOrder; *pp; pp=&(*pp)->pNext);
          346  +        *pp = pNew;
          347  +        break;
          348  +      }
          349  +
          350  +      case SQLITE_WHEREINFO_EQUALS:
          351  +      case SQLITE_WHEREINFO_RANGE: {
          352  +        IdxConstraint *pNew = idxNewConstraint(&p->rc, zVal);
          353  +        if( pNew==0 ) return;
          354  +        pNew->iCol = iVal;
          355  +        pNew->depmask = mask;
          356  +
          357  +        if( eOp==SQLITE_WHEREINFO_RANGE ){
          358  +          pNew->pNext = p->pCurrent->pRange;
          359  +          p->pCurrent->pRange = pNew;
          360  +        }else{
          361  +          pNew->pNext = p->pCurrent->pEq;
          362  +          p->pCurrent->pEq = pNew;
          363  +        }
          364  +        break;
          365  +      }
          366  +
          367  +      case SQLITE_WHEREINFO_BEGINOR: {
          368  +        assert( 0 );
          369  +        break;
          370  +      }
          371  +      case SQLITE_WHEREINFO_ENDOR: {
          372  +        assert( 0 );
          373  +        break;
          374  +      }
          375  +      case SQLITE_WHEREINFO_NEXTOR: {
          376  +        assert( 0 );
          377  +        break;
          378  +      }
          379  +    }
          380  +  }
          381  +}
          382  +
          383  +/*
          384  +** An error associated with database handle db has just occurred. Pass
          385  +** the error message to callback function xOut.
          386  +*/
          387  +static void idxDatabaseError(
          388  +  sqlite3 *db,                    /* Database handle */
          389  +  char **pzErrmsg                 /* Write error here */
          390  +){
          391  +  *pzErrmsg = sqlite3_mprintf("%s", sqlite3_errmsg(db));
          392  +}
          393  +
          394  +static int idxCreateTables(sqlite3 *db, sqlite3 *dbm, IdxScan *pScan){
          395  +  int rc = SQLITE_OK;
          396  +  IdxScan *pIter;
          397  +  for(pIter=pScan; pIter; pIter=pIter->pNextScan){
          398  +  }
          399  +}
          400  +
          401  +static void idxScanFree(IdxScan *pScan){
          402  +}
          403  +
          404  +/*
          405  +** The xOut callback is invoked to return command output to the user. The
          406  +** second argument is always a nul-terminated string. The first argument is
          407  +** passed zero if the string contains normal output or non-zero if it is an
          408  +** error message.
          409  +*/
          410  +int shellIndexesCommand(
          411  +  sqlite3 *db,                         /* Database handle */
          412  +  const char *zSql,                    /* SQL to find indexes for */
          413  +  void (*xOut)(void*, const char*),    /* Output callback */
          414  +  void *pOutCtx,                       /* Context for xOut() */
          415  +  char **pzErrmsg                      /* OUT: Error message (sqlite3_malloc) */
          416  +){
          417  +  int rc = SQLITE_OK;
          418  +  sqlite3 *dbm = 0;
          419  +  IdxContext ctx;
          420  +  sqlite3_stmt *pStmt = 0;        /* Statement compiled from zSql */
          421  +
          422  +  memset(&ctx, 0, sizeof(IdxContext));
          423  +
          424  +  /* Open an in-memory database to work with. The main in-memory 
          425  +  ** database schema contains tables similar to those in the users 
          426  +  ** database (handle db). The attached in-memory db (aux) contains
          427  +  ** application tables used by the code in this file.  */
          428  +  rc = sqlite3_open(":memory:", &dbm);
          429  +  if( rc==SQLITE_OK ){
          430  +    rc = sqlite3_exec(dbm, 
          431  +        "ATTACH ':memory:' AS aux;"
          432  +        "CREATE TABLE aux.depmask(mask PRIMARY KEY) WITHOUT ROWID;"
          433  +        , 0, 0, 0
          434  +    );
          435  +  }
          436  +  if( rc!=SQLITE_OK ){
          437  +    idxDatabaseError(dbm, pzErrmsg);
          438  +    goto indexes_out;
          439  +  }
          440  +
          441  +  /* Analyze the SELECT statement in zSql. */
          442  +  ctx.dbm = dbm;
          443  +  sqlite3_db_config(db, SQLITE_DBCONFIG_WHEREINFO, idxWhereInfo, (void*)&ctx);
          444  +  rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
          445  +  sqlite3_db_config(db, SQLITE_DBCONFIG_WHEREINFO, (void*)0, (void*)0);
          446  +  if( rc!=SQLITE_OK ){
          447  +    idxDatabaseError(db, pzErrmsg);
          448  +    goto indexes_out;
          449  +  }
          450  +
          451  +  /* Create tables within the main in-memory database. These tables
          452  +  ** have the same names, columns and declared types as the tables in
          453  +  ** the user database. All constraints except for PRIMARY KEY are
          454  +  ** removed. */
          455  +  rc = idxCreateTables(db, dbm, ctx.pScan);
          456  +  if( rc!=SQLITE_OK ){
          457  +    goto indexes_out;
          458  +  }
          459  +
          460  +  /* Create candidate indexes within the in-memory database file */
          461  +
          462  + indexes_out:
          463  +  idxScanFree(ctx.pScan);
          464  +  sqlite3_close(dbm);
          465  +  return rc;
          466  +}
          467  +
          468  +

Changes to src/sqlite.h.in.

  1905   1905   ** which case the trigger setting is not reported back. </dd>
  1906   1906   **
  1907   1907   ** </dl>
  1908   1908   */
  1909   1909   #define SQLITE_DBCONFIG_LOOKASIDE       1001  /* void* int int */
  1910   1910   #define SQLITE_DBCONFIG_ENABLE_FKEY     1002  /* int int* */
  1911   1911   #define SQLITE_DBCONFIG_ENABLE_TRIGGER  1003  /* int int* */
         1912  +#define SQLITE_DBCONFIG_WHEREINFO       1004  /* xWhereInfo void* */
         1913  +
         1914  +#define SQLITE_WHEREINFO_TABLE   1
         1915  +#define SQLITE_WHEREINFO_EQUALS  2
         1916  +#define SQLITE_WHEREINFO_RANGE   3
         1917  +#define SQLITE_WHEREINFO_ORDERBY 4
         1918  +#define SQLITE_WHEREINFO_BEGINOR 5
         1919  +#define SQLITE_WHEREINFO_ENDOR   6
         1920  +#define SQLITE_WHEREINFO_NEXTOR  7
  1912   1921   
  1913   1922   
  1914   1923   /*
  1915   1924   ** CAPI3REF: Enable Or Disable Extended Result Codes
  1916   1925   ** METHOD: sqlite3
  1917   1926   **
  1918   1927   ** ^The sqlite3_extended_result_codes() routine enables or disables the

Changes to src/sqliteInt.h.

  1269   1269     sqlite3 *pUnlockConnection;           /* Connection to watch for unlock */
  1270   1270     void *pUnlockArg;                     /* Argument to xUnlockNotify */
  1271   1271     void (*xUnlockNotify)(void **, int);  /* Unlock notify callback */
  1272   1272     sqlite3 *pNextBlocked;        /* Next in list of all blocked connections */
  1273   1273   #endif
  1274   1274   #ifdef SQLITE_USER_AUTHENTICATION
  1275   1275     sqlite3_userauth auth;        /* User authentication information */
         1276  +#endif
         1277  +#ifdef SQLITE_SCHEMA_LINT
         1278  +  void (*xWhereInfo)(void*, int, const char*, int, i64);
         1279  +  void *pWhereInfoCtx;
  1276   1280   #endif
  1277   1281   };
  1278   1282   
  1279   1283   /*
  1280   1284   ** A macro to discover the encoding of a database.
  1281   1285   */
  1282   1286   #define SCHEMA_ENC(db) ((db)->aDb[0].pSchema->enc)

Changes to src/where.c.

  3902   3902   #endif
  3903   3903       return 1;
  3904   3904     }
  3905   3905     return 0;
  3906   3906   }
  3907   3907   
  3908   3908   #ifdef SQLITE_SCHEMA_LINT
  3909         -static char *whereAppendPrintf(sqlite3 *db, const char *zFmt, ...){
  3910         -  va_list ap;
  3911         -  char *zRes = 0;
  3912         -  va_start(ap, zFmt);
  3913         -  zRes = sqlite3_vmprintf(zFmt, ap);
  3914         -  if( zRes==0 ){
  3915         -    db->mallocFailed = 1;
  3916         -  }else if( db->mallocFailed ){
  3917         -    sqlite3_free(zRes);
  3918         -    zRes = 0;
  3919         -  }
  3920         -  va_end(ap);
  3921         -  return zRes;
  3922         -}
  3923         -
  3924         -/*
  3925         -** Append a representation of term pTerm to the string in zIn and return
  3926         -** the result. Or, if an OOM occurs, free zIn and return a NULL pointer.
  3927         -*/
  3928         -static char *whereAppendSingleTerm(
  3929         -  Parse *pParse,
  3930         -  Table *pTab,
  3931         -  int iCol,
  3932         -  int bOr,
  3933         -  char *zIn,
  3934         -  WhereTerm *pTerm
  3935         -){
  3936         -  char *zBuf;
  3937         -  sqlite3 *db = pParse->db;
  3938         -  Expr *pX = pTerm->pExpr;
  3939         -  CollSeq *pColl;
  3940         -  const char *zOp = 0;
  3941         -
  3942         -  if( pTerm->eOperator & (WO_IS|WO_EQ|WO_IN) ){
  3943         -    zOp = "eq";
  3944         -  }else if( pTerm->eOperator & (WO_LT|WO_LE|WO_GE|WO_GT) ){
  3945         -    zOp = "range";
  3946         -  }
  3947         -  pColl = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight);
  3948         -
  3949         -  if( zOp ){
  3950         -    const char *zFmt = bOr ? "%z{{%s \"%w\" \"%w\" %lld}}" :
  3951         -                             "%z{%s \"%w\" \"%w\" %lld}";
  3952         -    zBuf = whereAppendPrintf(db, zFmt, zIn, 
  3953         -        zOp, pTab->aCol[iCol].zName, 
  3954         -        (pColl ? pColl->zName : "BINARY"),
  3955         -        pTerm->prereqRight
  3956         -    );
  3957         -  }else{
  3958         -    zBuf = zIn;
  3959         -  }
  3960         -
  3961         -  return zBuf;
  3962         -}
  3963         -
  3964         -static char *whereTraceWC(
         3909  +static void whereTraceWC(
  3965   3910     Parse *pParse, 
  3966         -  int bInitialSpace,
  3967   3911     struct SrcList_item *pItem,
  3968         -  char *zIn,
  3969         -  WhereClause *pWC
         3912  +  WhereClause *pWC,
         3913  +  int bOr
  3970   3914   ){
  3971   3915     sqlite3 *db = pParse->db;
  3972   3916     Table *pTab = pItem->pTab;
  3973         -  char *zBuf = zIn;
  3974         -  int iCol;
         3917  +  void (*x)(void*, int, const char*, int, i64) = db->xWhereInfo;
         3918  +  void *pCtx = db->pWhereInfoCtx;
         3919  +  int bFirst = 1;                 /* True until first callback is made */
  3975   3920     int ii;
  3976         -  int bFirst = !bInitialSpace;
  3977         -  int bOr = (pWC->op==TK_OR);
  3978   3921   
  3979         -  /* List of WO_SINGLE constraints */
  3980         -  for(iCol=0; iCol<pTab->nCol; iCol++){
         3922  +  /* Issue callbacks for WO_SINGLE constraints */
         3923  +  for(ii=0; ii<pTab->nCol; ii++){
  3981   3924       int opMask = WO_SINGLE; 
  3982   3925       WhereScan scan;
  3983   3926       WhereTerm *pTerm;
  3984         -    for(pTerm=whereScanInit(&scan, pWC, pItem->iCursor, iCol, opMask, 0);
         3927  +    for(pTerm=whereScanInit(&scan, pWC, pItem->iCursor, ii, opMask, 0);
  3985   3928           pTerm;
  3986   3929           pTerm=whereScanNext(&scan)
  3987   3930       ){
  3988         -      /* assert( iCol==pTerm->u.leftColumn ); */
  3989         -      if( bFirst==0 ) zBuf = whereAppendPrintf(db, "%z ", zBuf);
  3990         -      zBuf = whereAppendSingleTerm(pParse, pTab, iCol, bOr, zBuf, pTerm);
         3931  +      int eOp;
         3932  +      Expr *pX = pTerm->pExpr;
         3933  +      CollSeq *pC = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight);
         3934  +      if( pTerm->eOperator & (WO_IS|WO_EQ|WO_IN) ){
         3935  +        eOp = SQLITE_WHEREINFO_EQUALS;
         3936  +      }else{
         3937  +        eOp = SQLITE_WHEREINFO_RANGE;
         3938  +      }
         3939  +      if( bOr && !bFirst ) x(pCtx, SQLITE_WHEREINFO_NEXTOR, 0, 0, 0);
         3940  +      x(pCtx, eOp, (pC ? pC->zName : "BINARY"), ii, pTerm->prereqRight);
         3941  +      bFirst = 0;
         3942  +    }
         3943  +  }
         3944  +
         3945  +  /* Callbacks for composite - (WO_OR|WO_AND) - constraints */
         3946  +  for(ii=0; ii<pWC->nTerm; ii++){
         3947  +    WhereTerm *pTerm = &pWC->a[ii];
         3948  +    if( pTerm->eOperator & WO_OR ){
         3949  +      assert( bOr==0 );
         3950  +      x(pCtx, SQLITE_WHEREINFO_BEGINOR, 0, 0, 0);
         3951  +      whereTraceWC(pParse, pItem, &pTerm->u.pOrInfo->wc, 1);
         3952  +      x(pCtx, SQLITE_WHEREINFO_ENDOR, 0, 0, 0);
         3953  +    }
         3954  +    if( pTerm->eOperator & WO_AND ){
         3955  +      if( bOr && !bFirst ) x(pCtx, SQLITE_WHEREINFO_NEXTOR, 0, 0, 0);
         3956  +      whereTraceWC(pParse, pItem, &pTerm->u.pAndInfo->wc, 0);
  3991   3957         bFirst = 0;
  3992   3958       }
  3993   3959     }
  3994         -
  3995         -  /* Add composite - (WO_OR|WO_AND) - constraints */
  3996         -  for(ii=0; ii<pWC->nTerm; ii++){
  3997         -    WhereTerm *pTerm = &pWC->a[ii];
  3998         -    if( pTerm->eOperator & (WO_OR|WO_AND) ){
  3999         -      const char *zFmt = ((pTerm->eOperator&WO_OR) ? "%z%s{or " : "%z%s{");
  4000         -      zBuf = whereAppendPrintf(db, zFmt, zBuf, bFirst ? "" : " ");
  4001         -      zBuf = whereTraceWC(pParse, 0, pItem, zBuf, &pTerm->u.pOrInfo->wc);
  4002         -      zBuf = whereAppendPrintf(db, "%z}", zBuf);
  4003         -      bFirst = 0;
  4004         -    }
  4005         -  }
  4006         -
  4007         -  return zBuf;
  4008   3960   }
         3961  +
  4009   3962   
  4010   3963   static void whereTraceBuilder(
  4011   3964     Parse *pParse,
  4012   3965     WhereLoopBuilder *p
  4013   3966   ){
  4014   3967     sqlite3 *db = pParse->db;
  4015         -  if( db->xTrace ){
  4016         -    ExprList *pOrderBy = p->pOrderBy;
  4017         -    WhereInfo *pWInfo = p->pWInfo;
  4018         -    int nTablist = pWInfo->pTabList->nSrc;
         3968  +  if( db->xWhereInfo && db->init.busy==0 ){
         3969  +    void (*x)(void*, int, const char*, int, i64) = db->xWhereInfo;
         3970  +    void *pCtx = db->pWhereInfoCtx;
  4019   3971       int ii;
         3972  +    int nTab = p->pWInfo->pTabList->nSrc;
  4020   3973   
  4021   3974       /* Loop through each element of the FROM clause. Ignore any sub-selects
  4022         -    ** or views. Invoke the xTrace() callback once for each real table. */
  4023         -    for(ii=0; ii<nTablist; ii++){
  4024         -      char *zBuf = 0;
  4025         -      int iCol;
  4026         -      int nCol;
  4027         -      Table *pTab;
  4028         -
  4029         -      struct SrcList_item *pItem = &pWInfo->pTabList->a[ii];
  4030         -      if( pItem->pSelect ) continue;
  4031         -      pTab = pItem->pTab;
  4032         -      nCol = pTab->nCol;
  4033         -
  4034         -      /* Append the table name to the buffer. */
  4035         -      zBuf = whereAppendPrintf(db, "\"%w\"", pTab->zName);
  4036         -
  4037         -      /* Append the list of columns required to create a covering index */
  4038         -      zBuf = whereAppendPrintf(db, "%z {cols", zBuf);
  4039         -      if( 0==(pItem->colUsed & ((u64)1 << (sizeof(Bitmask)*8-1))) ){
  4040         -        for(iCol=0; iCol<nCol; iCol++){
  4041         -          if( iCol==(sizeof(Bitmask)*8-1) ) break;
  4042         -          if( pItem->colUsed & ((u64)1 << iCol) ){
  4043         -            const char *zName = pTab->aCol[iCol].zName;
  4044         -            zBuf = whereAppendPrintf(db, "%z \"%w\"", zBuf, zName);
  4045         -          }
  4046         -        }
  4047         -      }
  4048         -      zBuf = whereAppendPrintf(db, "%z}",zBuf);
  4049         -
  4050         -      /* Append the contents of WHERE clause */
  4051         -      zBuf = whereTraceWC(pParse, 1, pItem, zBuf, p->pWC);
  4052         -
  4053         -      /* Append the ORDER BY clause, if any */
  4054         -      if( pOrderBy ){
  4055         -        int i;
  4056         -        int bFirst = 1;
  4057         -        for(i=0; i<pOrderBy->nExpr; i++){
  4058         -          Expr *pExpr = pOrderBy->a[i].pExpr; 
  4059         -          CollSeq *pColl = sqlite3ExprCollSeq(pParse, pExpr);
  4060         -
  4061         -          pExpr = sqlite3ExprSkipCollate(pExpr);
  4062         -          if( pExpr->op==TK_COLUMN && pExpr->iTable==pItem->iCursor ){
  4063         -            if( pExpr->iColumn>=0 ){
  4064         -              const char *zName = pTab->aCol[pExpr->iColumn].zName;
  4065         -              zBuf = whereAppendPrintf(db, "%z%s\"%w\" \"%w\" %s", zBuf,
  4066         -                  bFirst ? " {orderby " : " ", zName, pColl->zName,
  4067         -                  (pOrderBy->a[i].sortOrder ? "DESC" : "ASC")
  4068         -              );
  4069         -              bFirst = 0;
         3975  +    ** or views. Invoke the xWhereInfo() callback multiple times for each
         3976  +    ** real table.  */
         3977  +    for(ii=0; ii<p->pWInfo->pTabList->nSrc; ii++){
         3978  +      struct SrcList_item *pItem = &p->pWInfo->pTabList->a[ii];
         3979  +      if( pItem->pSelect==0 ){
         3980  +        Table *pTab = pItem->pTab;
         3981  +        int iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
         3982  +
         3983  +        /* Table name callback */
         3984  +        x(pCtx, SQLITE_WHEREINFO_TABLE, pTab->zName, iDb, pItem->colUsed);
         3985  +
         3986  +        /* ORDER BY callbacks */
         3987  +        if( p->pOrderBy ){
         3988  +          int i;
         3989  +          int bFirst = 1;
         3990  +          for(i=0; i<p->pOrderBy->nExpr; i++){
         3991  +            Expr *pExpr = p->pOrderBy->a[i].pExpr; 
         3992  +            CollSeq *pColl = sqlite3ExprCollSeq(pParse, pExpr);
         3993  +            pExpr = sqlite3ExprSkipCollate(pExpr);
         3994  +            if( pExpr->op==TK_COLUMN && pExpr->iTable==pItem->iCursor ){
         3995  +              int iCol = pExpr->iColumn;
         3996  +              if( iCol>=0 ){
         3997  +                x(pCtx, SQLITE_WHEREINFO_ORDERBY, pColl->zName, iCol, 0); 
         3998  +              }
  4070   3999               }
  4071   4000             }
  4072   4001           }
  4073         -        if( bFirst==0 ) zBuf = whereAppendPrintf(db, "%z}", zBuf);
         4002  +
         4003  +        /* WHERE callbacks */
         4004  +        whereTraceWC(pParse, pItem, p->pWC, 0);
  4074   4005         }
  4075         -
  4076         -      /* Pass the buffer to the xTrace() callback, then free it */
  4077         -      db->xTrace(db->pTraceArg, zBuf);
  4078         -      sqlite3DbFree(db, zBuf);
  4079   4006       }
  4080   4007     }
  4081   4008   }
  4082   4009   #else
  4083   4010   # define whereTraceBuilder(x,y)
  4084   4011   #endif
  4085   4012