/ Check-in [8c1c701f]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Add support for indexes on expressions to incremental_index_check.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | checkindex
Files: files | file ages | folders
SHA3-256: 8c1c701fdbe0d56ee7f6f7d7b583aafde9fa14acc93ee8ecaddc8bb311e2bf52
User & Date: dan 2017-10-30 19:38:41
Context
2017-10-31
12:01
Fix a couple of issues in incremental_index_check to do with CREATE INDEX statements that contain embedded SQL comments. check-in: 2aef4181 user: dan tags: checkindex
2017-10-30
19:38
Add support for indexes on expressions to incremental_index_check. check-in: 8c1c701f user: dan tags: checkindex
17:05
In checkindex.c, use C code instead of SQL/group_concat() to compose various SQL clauses. This is to make it easier to support indexes on expressions. check-in: 940606b3 user: dan tags: checkindex
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/repair/checkindex.c.

   269    269       int i;
   270    270       for(i=0; i<pIdx->nCol; i++){
   271    271         sqlite3_free(pIdx->aCol[i].zExpr);
   272    272       }
   273    273       sqlite3_free(pIdx);
   274    274     }
   275    275   }
          276  +
          277  +#define CIDX_PARSE_EOF   0
          278  +#define CIDX_PARSE_COMMA 1      /*  "," */
          279  +#define CIDX_PARSE_OPEN  2      /*  "(" */
          280  +#define CIDX_PARSE_CLOSE 3      /*  ")" */
          281  +
          282  +static int cidxFindNext(const char *zIn, const char **pzOut){
          283  +  const char *z = zIn;
          284  +
          285  +  while( 1 ){
          286  +    *pzOut = z;
          287  +    switch( *z ){
          288  +      case '\0':
          289  +        return CIDX_PARSE_EOF;
          290  +      case '(':
          291  +        return CIDX_PARSE_OPEN;
          292  +      case ')':
          293  +        return CIDX_PARSE_CLOSE;
          294  +      case ',':
          295  +        return CIDX_PARSE_COMMA;
          296  +
          297  +      case '"': 
          298  +      case '\'': 
          299  +      case '`': {
          300  +        char q = *z;
          301  +        z++;
          302  +        while( *z ){
          303  +          if( *z==q ){
          304  +            z++;
          305  +            if( *z!=q ) break;
          306  +          }
          307  +          z++;
          308  +        }
          309  +        break;
          310  +      }
          311  +
          312  +      case '[':
          313  +        while( *z++!=']' );
          314  +        break;
          315  +
          316  +      default:
          317  +        z++;
          318  +    }
          319  +  }
          320  +
          321  +  assert( 0 );
          322  +  return -1;
          323  +}
          324  +
          325  +static int cidx_isspace(char c){
          326  +  return c==' ' || c=='\t' || c=='\r' || c=='\n';
          327  +}
          328  +
          329  +static int cidx_isident(char c){
          330  +  return c<0 
          331  +    || (c>='0' && c<='9') || (c>='a' && c<='z') 
          332  +    || (c>='A' && c<='Z') || c=='_';
          333  +}
          334  +
          335  +static int cidxParseSQL(CidxCursor *pCsr, CidxIndex *pIdx, const char *zSql){
          336  +  const char *z = zSql;
          337  +  const char *z1;
          338  +  int e;
          339  +  int rc = SQLITE_OK;
          340  +  int nParen = 1;
          341  +  CidxColumn *pCol = pIdx->aCol;
          342  +
          343  +  e = cidxFindNext(z, &z);
          344  +  if( e!=CIDX_PARSE_OPEN ) goto parse_error;
          345  +  z1 = z+1;
          346  +  z++;
          347  +  while( nParen>0 ){
          348  +    e = cidxFindNext(z, &z);
          349  +    if( e==CIDX_PARSE_EOF ) goto parse_error;
          350  +    if( (e==CIDX_PARSE_COMMA || e==CIDX_PARSE_CLOSE) && nParen==1 ){
          351  +      const char *z2 = z;
          352  +      if( pCol->zExpr ) goto parse_error;
          353  +
          354  +      while( cidx_isspace(z[-1]) ) z--;
          355  +      if( 0==sqlite3_strnicmp(&z[-3], "asc", 3) && 0==cidx_isident(z[-4]) ){
          356  +        z -= 3;
          357  +        while( cidx_isspace(z[-1]) ) z--;
          358  +      }else
          359  +      if( 0==sqlite3_strnicmp(&z[-4], "desc", 4) && 0==cidx_isident(z[-5]) ){
          360  +        z -= 4;
          361  +        while( cidx_isspace(z[-1]) ) z--;
          362  +      }
          363  +
          364  +      while( cidx_isspace(z1[0]) ) z1++;
          365  +      pCol->zExpr = cidxMprintf(&rc, "%.*s", z-z1, z1);
          366  +      pCol++;
          367  +      z = z1 = z2+1;
          368  +    }
          369  +    if( e==CIDX_PARSE_OPEN ) nParen++;
          370  +    if( e==CIDX_PARSE_CLOSE ) nParen--;
          371  +    z++;
          372  +  }
          373  +
          374  +  return rc;
          375  +
          376  + parse_error:
          377  +  cidxCursorError(pCsr, "Parse error in: %s", zSql);
          378  +  return SQLITE_ERROR;
          379  +}
   276    380   
   277    381   static int cidxLookupIndex(
   278    382     CidxCursor *pCsr,               /* Cursor object */
   279    383     const char *zIdx,               /* Name of index to look up */
   280    384     CidxIndex **ppIdx,              /* OUT: Description of columns */
   281    385     char **pzTab                    /* OUT: Table name */
   282    386   ){
................................................................................
   285    389     CidxIndex *pIdx = 0;
   286    390   
   287    391     sqlite3_stmt *pFindTab = 0;
   288    392     sqlite3_stmt *pInfo = 0;
   289    393       
   290    394     /* Find the table for this index. */
   291    395     pFindTab = cidxPrepare(&rc, pCsr, 
   292         -      "SELECT tbl_name FROM sqlite_master WHERE name=%Q AND type='index'",
          396  +      "SELECT tbl_name, sql FROM sqlite_master WHERE name=%Q AND type='index'",
   293    397         zIdx
   294    398     );
   295    399     if( rc==SQLITE_OK && sqlite3_step(pFindTab)==SQLITE_ROW ){
          400  +    const char *zSql = (const char*)sqlite3_column_text(pFindTab, 1);
   296    401       zTab = cidxStrdup(&rc, (const char*)sqlite3_column_text(pFindTab, 0));
          402  +
          403  +    pInfo = cidxPrepare(&rc, pCsr, "PRAGMA index_xinfo(%Q)", zIdx);
          404  +    if( rc==SQLITE_OK ){
          405  +      int nAlloc = 0;
          406  +      int iCol = 0;
          407  +
          408  +      while( sqlite3_step(pInfo)==SQLITE_ROW ){
          409  +        const char *zName = (const char*)sqlite3_column_text(pInfo, 2);
          410  +        const char *zColl = (const char*)sqlite3_column_text(pInfo, 4);
          411  +        CidxColumn *p;
          412  +        if( zName==0 ) zName = "rowid";
          413  +        if( iCol==nAlloc ){
          414  +          int nByte = sizeof(CidxIndex) + sizeof(CidxColumn)*(nAlloc+8);
          415  +          pIdx = (CidxIndex*)sqlite3_realloc(pIdx, nByte);
          416  +          nAlloc += 8;
          417  +        }
          418  +        p = &pIdx->aCol[iCol++];
          419  +        p->bDesc = sqlite3_column_int(pInfo, 3);
          420  +        p->bKey = sqlite3_column_int(pInfo, 5);
          421  +        if( zSql==0 || p->bKey==0 ){
          422  +          p->zExpr = cidxMprintf(&rc, "\"%w\" COLLATE %s",zName,zColl);
          423  +        }else{
          424  +          p->zExpr = 0;
          425  +        }
          426  +        pIdx->nCol = iCol;
          427  +      }
          428  +      cidxFinalize(&rc, pInfo);
          429  +    }
          430  +
          431  +    if( rc==SQLITE_OK && zSql ){
          432  +      rc = cidxParseSQL(pCsr, pIdx, zSql);
          433  +    }
   297    434     }
          435  +
   298    436     cidxFinalize(&rc, pFindTab);
   299    437     if( rc==SQLITE_OK && zTab==0 ){
   300    438       rc = SQLITE_ERROR;
   301    439     }
   302         -
   303         -  pInfo = cidxPrepare(&rc, pCsr, "PRAGMA index_xinfo(%Q)", zIdx);
   304         -  if( rc==SQLITE_OK ){
   305         -    int nAlloc = 0;
   306         -    int iCol = 0;
   307         -
   308         -    while( sqlite3_step(pInfo)==SQLITE_ROW ){
   309         -      const char *zName = (const char*)sqlite3_column_text(pInfo, 2);
   310         -      const char *zColl = (const char*)sqlite3_column_text(pInfo, 4);
   311         -      CidxColumn *p;
   312         -      if( zName==0 ) zName = "rowid";
   313         -      if( iCol==nAlloc ){
   314         -        int nByte = sizeof(CidxIndex) + sizeof(CidxColumn)*(nAlloc+8);
   315         -        pIdx = (CidxIndex*)sqlite3_realloc(pIdx, nByte);
   316         -      }
   317         -      p = &pIdx->aCol[iCol++];
   318         -      p->zExpr = cidxMprintf(&rc, "\"%w\" COLLATE %s",zName,zColl);
   319         -      p->bDesc = sqlite3_column_int(pInfo, 3);
   320         -      p->bKey = sqlite3_column_int(pInfo, 5);
   321         -      pIdx->nCol = iCol;
   322         -    }
   323         -    cidxFinalize(&rc, pInfo);
   324         -  }
   325    440     
   326    441     if( rc!=SQLITE_OK ){
   327    442       sqlite3_free(zTab);
   328    443       cidxFreeIndex(pIdx);
   329    444     }else{
   330    445       *pzTab = zTab;
   331    446       *ppIdx = pIdx;
................................................................................
   407    522     int *pRc, CidxColumn *aCol, char **azAfter, int iGt, int bLastIsNull
   408    523   ){
   409    524     char *zRet = 0;
   410    525     const char *zSep = "";
   411    526     int i;
   412    527   
   413    528     for(i=0; i<iGt; i++){
   414         -    zRet = cidxMprintf(pRc, "%z%s%s IS %s", zRet, 
          529  +    zRet = cidxMprintf(pRc, "%z%s(%s) IS %s", zRet, 
   415    530           zSep, aCol[i].zExpr, (azAfter[i] ? azAfter[i] : "NULL")
   416    531       );
   417    532       zSep = " AND ";
   418    533     }
   419    534   
   420    535     if( bLastIsNull ){
   421         -    zRet = cidxMprintf(pRc, "%z%s%s IS NULL", zRet, zSep, aCol[iGt].zExpr);
          536  +    zRet = cidxMprintf(pRc, "%z%s(%s) IS NULL", zRet, zSep, aCol[iGt].zExpr);
   422    537     }
   423    538     else if( azAfter[iGt] ){
   424         -    zRet = cidxMprintf(pRc, "%z%s%s %s %s", zRet, 
          539  +    zRet = cidxMprintf(pRc, "%z%s(%s) %s %s", zRet, 
   425    540           zSep, aCol[iGt].zExpr, (aCol[iGt].bDesc ? "<" : ">"), 
   426    541           azAfter[iGt]
   427    542       );
   428    543     }else{
   429         -    zRet = cidxMprintf(pRc, "%z%s%s IS NOT NULL", zRet, zSep, aCol[iGt].zExpr);
          544  +    zRet = cidxMprintf(pRc, "%z%s(%s) IS NOT NULL", zRet, zSep,aCol[iGt].zExpr);
   430    545     }
   431    546   
   432    547     return zRet;
   433    548   }
   434    549   
   435    550   #define CIDX_CLIST_ALL         0
   436    551   #define CIDX_CLIST_ORDERBY     1
................................................................................
   446    561     int *pRc,                       /* IN/OUT: Error code */
   447    562     const char *zIdx,
   448    563     CidxIndex *pIdx,                /* Indexed columns */
   449    564     int eType                       /* True to include ASC/DESC */
   450    565   ){
   451    566     char *zRet = 0;
   452    567     if( *pRc==SQLITE_OK ){
   453         -    const char *aDir[2] = {" ASC", " DESC"};
          568  +    const char *aDir[2] = {"", " DESC"};
   454    569       int i;
   455    570       const char *zSep = "";
   456    571   
   457    572       for(i=0; i<pIdx->nCol; i++){
   458    573         CidxColumn *p = &pIdx->aCol[i];
   459    574         assert( pIdx->aCol[i].bDesc==0 || pIdx->aCol[i].bDesc==1 );
   460    575         switch( eType ){
   461    576   
   462    577           case CIDX_CLIST_ORDERBY:
   463         -          zRet = cidxMprintf(pRc, "%z%s%s%s",zRet,zSep,p->zExpr,aDir[p->bDesc]);
          578  +          zRet = cidxMprintf(pRc, "%z%s%d%s", zRet, zSep, i+1, aDir[p->bDesc]);
   464    579             zSep = ",";
   465    580             break;
   466    581   
   467    582           case CIDX_CLIST_CURRENT_KEY:
   468         -          zRet = cidxMprintf(pRc, "%z%squote(%s)", zRet, zSep, p->zExpr);
          583  +          zRet = cidxMprintf(pRc, "%z%squote(i%d)", zRet, zSep, i);
   469    584             zSep = "||','||";
   470    585             break;
   471    586   
   472    587           case CIDX_CLIST_SUBWHERE:
   473    588             if( p->bKey==0 ){
   474         -            zRet = cidxMprintf(pRc, "%z%s%s IS \"%w\".%s", zRet, 
   475         -                zSep, p->zExpr, zIdx, p->zExpr
          589  +            zRet = cidxMprintf(pRc, "%z%s%s IS i.i%d", zRet, 
          590  +                zSep, p->zExpr, i
   476    591               );
   477    592               zSep = " AND ";
   478    593             }
   479    594             break;
   480    595   
   481    596           case CIDX_CLIST_SUBEXPR:
   482    597             if( p->bKey==1 ){
   483         -            zRet = cidxMprintf(pRc, "%z%s%s IS \"%w\".%s", zRet, 
   484         -                zSep, p->zExpr, zIdx, p->zExpr
          598  +            zRet = cidxMprintf(pRc, "%z%s%s IS i.i%d", zRet, 
          599  +                zSep, p->zExpr, i
   485    600               );
   486    601               zSep = " AND ";
   487    602             }
   488    603             break;
   489    604   
   490    605           default:
   491    606             assert( eType==CIDX_CLIST_ALL );
   492         -          zRet = cidxMprintf(pRc, "%z%s%s", zRet, zSep, p->zExpr);
   493         -          zSep = ",";
          607  +          zRet = cidxMprintf(pRc, "%z%s(%s) AS i%d", zRet, zSep, p->zExpr, i);
          608  +          zSep = ", ";
   494    609             break;
   495    610         }
   496    611       }
   497    612     }
   498    613   
   499    614     return zRet;
   500    615   }
................................................................................
   532    647   
   533    648       rc = cidxLookupIndex(pCsr, zIdxName, &pIdx, &zTab);
   534    649   
   535    650       zOrderBy = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_ORDERBY);
   536    651       zCurrentKey = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_CURRENT_KEY);
   537    652       zSubWhere = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_SUBWHERE);
   538    653       zSubExpr = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_SUBEXPR);
   539         -    /* zSrcList = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_ALL); */
          654  +    zSrcList = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_ALL);
   540    655   
   541    656       if( rc==SQLITE_OK && zAfterKey ){
   542    657         rc = cidxDecodeAfter(pCsr, pIdx->nCol, zAfterKey, &azAfter);
   543    658       }
   544    659   
   545    660       if( rc || zAfterKey==0 ){
   546    661         pCsr->pStmt = cidxPrepare(&rc, pCsr, 
   547         -          "SELECT (SELECT %s FROM %Q WHERE %s), %s FROM %Q AS %Q ORDER BY %s",
   548         -          zSubExpr, zTab, zSubWhere, zCurrentKey, zTab, zIdxName, zOrderBy
          662  +          "SELECT (SELECT %s FROM %Q AS t WHERE %s), %s "
          663  +          "FROM (SELECT %s FROM %Q ORDER BY %s) AS i",
          664  +          zSubExpr, zTab, zSubWhere, zCurrentKey, 
          665  +          zSrcList, zTab, zOrderBy
   549    666         );
   550         -      /* printf("SQL: %s\n", sqlite3_sql(pCsr->pStmt));  */
          667  +      /* printf("SQL: %s\n", sqlite3_sql(pCsr->pStmt)); */
   551    668       }else{
   552         -      char *zList = cidxColumnList(&rc, zIdxName, pIdx, 0);
   553    669         const char *zSep = "";
   554    670         char *zSql;
   555    671         int i;
   556    672   
   557         -      zSql = cidxMprintf(&rc, "SELECT (SELECT %s FROM %Q WHERE %s), %s FROM (",
          673  +      zSql = cidxMprintf(&rc, 
          674  +          "SELECT (SELECT %s FROM %Q WHERE %s), %s FROM (",
   558    675             zSubExpr, zTab, zSubWhere, zCurrentKey
   559    676         );
   560    677         for(i=pIdx->nCol-1; i>=0; i--){
   561    678           int j;
   562    679           if( pIdx->aCol[i].bDesc && azAfter[i]==0 ) continue;
   563    680           for(j=0; j<2; j++){
   564    681             char *zWhere = cidxWhere(&rc, pIdx->aCol, azAfter, i, j);
   565         -          zSql = cidxMprintf(&rc, 
   566         -              "%z%s SELECT * FROM (SELECT %s FROM %Q WHERE %z ORDER BY %s)",
   567         -              zSql, zSep, zList, zTab, zWhere, zOrderBy
   568         -              );
          682  +          zSql = cidxMprintf(&rc, "%z"
          683  +              "%sSELECT * FROM (SELECT %s FROM %Q WHERE %z ORDER BY %s)",
          684  +              zSql, zSep, zSrcList, zTab, zWhere, zOrderBy
          685  +          );
   569    686             zSep = " UNION ALL ";
   570    687             if( pIdx->aCol[i].bDesc==0 ) break;
   571    688           }
   572    689         }
   573         -      zSql = cidxMprintf(&rc, "%z) AS %Q", zSql, zIdxName);
   574         -      sqlite3_free(zList);
          690  +      zSql = cidxMprintf(&rc, "%z) AS i", zSql);
   575    691   
   576    692         /* printf("SQL: %s\n", zSql); */
   577    693         pCsr->pStmt = cidxPrepare(&rc, pCsr, "%z", zSql);
   578    694       }
   579    695   
   580    696       sqlite3_free(zTab);
   581    697       sqlite3_free(zCurrentKey);
   582    698       sqlite3_free(zOrderBy);
   583    699       sqlite3_free(zSubWhere);
   584    700       sqlite3_free(zSubExpr);
          701  +    sqlite3_free(zSrcList);
   585    702       cidxFreeIndex(pIdx);
   586    703       sqlite3_free(azAfter);
   587    704     }
   588    705   
   589    706     if( pCsr->pStmt ){
   590    707       assert( rc==SQLITE_OK );
   591    708       rc = cidxNext(pCursor);

Changes to test/checkindex.test.

   255    255   do_index_check_test 4.3 t4cc {
   256    256     {} 'aaa','bbb',1 
   257    257     {row data mismatch} 'AAA','CCC',2 
   258    258     {row missing} 'aab','ddd',3 
   259    259     {} 'AAB','EEE',4
   260    260   }
   261    261   
          262  +#--------------------------------------------------------------------------
          263  +# Test an index on an expression.
          264  +#
          265  +do_execsql_test 5.0 {
          266  +  CREATE TABLE t5(x INTEGER PRIMARY KEY, y TEXT, UNIQUE(y));
          267  +  INSERT INTO t5 VALUES(1, '{"x":1, "y":1}');
          268  +  INSERT INTO t5 VALUES(2, '{"x":2, "y":2}');
          269  +  INSERT INTO t5 VALUES(3, '{"x":3, "y":3}');
          270  +  INSERT INTO t5 VALUES(4, '{"w":4, "z":4}');
          271  +  INSERT INTO t5 VALUES(5, '{"x":5, "y":5}');
          272  +
          273  +  CREATE INDEX t5x ON t5( json_extract(y, '$.x') );
          274  +  CREATE INDEX t5y ON t5( json_extract(y, '$.y') DESC );
          275  +}
          276  +
          277  +do_index_check_test 5.1.1 t5x {
          278  +  {} NULL,4 {} 1,1 {} 2,2 {} 3,3 {} 5,5
          279  +}
          280  +
          281  +do_index_check_test 5.1.2 t5y {
          282  +  {} 5,5 {} 3,3 {} 2,2 {} 1,1 {} NULL,4
          283  +}
          284  +
          285  +do_index_check_test 5.1.3 sqlite_autoindex_t5_1 {
          286  +  {} {'{"w":4, "z":4}',4} 
          287  +  {} {'{"x":1, "y":1}',1} 
          288  +  {} {'{"x":2, "y":2}',2} 
          289  +  {} {'{"x":3, "y":3}',3} 
          290  +  {} {'{"x":5, "y":5}',5}
          291  +}
          292  +
          293  +do_test 5.2 {
          294  +  set tblroot [db one { SELECT rootpage FROM sqlite_master WHERE name='t5' }]
          295  +  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 1 $tblroot
          296  +  db eval {CREATE TABLE xt5(a INTEGER PRIMARY KEY, c1 TEXT);}
          297  +  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 0
          298  +  execsql {
          299  +    UPDATE xt5 SET c1='{"x":22, "y":11}' WHERE rowid=1;
          300  +    DELETE FROM xt5 WHERE rowid = 4;
          301  +  }
          302  +  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 1
          303  +} {}
          304  +
          305  +do_index_check_test 5.3.1 t5x {
          306  +  {row missing} NULL,4 
          307  +  {row data mismatch} 1,1 
          308  +  {} 2,2 
          309  +  {} 3,3 
          310  +  {} 5,5
          311  +}
          312  +
          313  +do_index_check_test 5.3.2 sqlite_autoindex_t5_1 {
          314  +  {row missing} {'{"w":4, "z":4}',4} 
          315  +  {row data mismatch} {'{"x":1, "y":1}',1} 
          316  +  {} {'{"x":2, "y":2}',2} 
          317  +  {} {'{"x":3, "y":3}',3} 
          318  +  {} {'{"x":5, "y":5}',5}
          319  +}
   262    320   
   263    321   
   264    322   finish_test
   265    323