/ Check-in [6794b2dc]
Login

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

Overview
Comment:Here begins an experimental branch for exploring the idea of a partial index. This check-in is able to parse a WHERE clause on a CREATE INDEX statement, but does not actually do anythingn with that WHERE clause yet.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | partial-indices
Files: files | file ages | folders
SHA1: 6794b2dcb48b3507caccfc7867fc185818cf8291
User & Date: drh 2013-07-31 18:12:26
Context
2013-07-31
19:05
Resolve names in CREATE INDEX WHERE clauses and detect errors. Disallow expressions that contain variables, subqueries, or functions. The expression is still not used for anything, however. still unused. check-in: f2aa7842 user: drh tags: partial-indices
18:12
Here begins an experimental branch for exploring the idea of a partial index. This check-in is able to parse a WHERE clause on a CREATE INDEX statement, but does not actually do anythingn with that WHERE clause yet. check-in: 6794b2dc user: drh tags: partial-indices
2013-07-30
15:10
Reduce the size of the stack required by the codeOneLoopStart() function in where.c. check-in: eb6d4278 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

   378    378   /*
   379    379   ** Reclaim the memory used by an index
   380    380   */
   381    381   static void freeIndex(sqlite3 *db, Index *p){
   382    382   #ifndef SQLITE_OMIT_ANALYZE
   383    383     sqlite3DeleteIndexSamples(db, p);
   384    384   #endif
          385  +  sqlite3ExprDelete(db, p->pPartIdxWhere);
   385    386     sqlite3DbFree(db, p->zColAff);
   386    387     sqlite3DbFree(db, p);
   387    388   }
   388    389   
   389    390   /*
   390    391   ** For the index called zIdxName which is found in the database iDb,
   391    392   ** unlike that index from its Table then remove the index from
................................................................................
  1221   1222     }else if( autoInc ){
  1222   1223   #ifndef SQLITE_OMIT_AUTOINCREMENT
  1223   1224       sqlite3ErrorMsg(pParse, "AUTOINCREMENT is only allowed on an "
  1224   1225          "INTEGER PRIMARY KEY");
  1225   1226   #endif
  1226   1227     }else{
  1227   1228       Index *p;
  1228         -    p = sqlite3CreateIndex(pParse, 0, 0, 0, pList, onError, 0, 0, sortOrder, 0);
         1229  +    p = sqlite3CreateIndex(pParse, 0, 0, 0, pList, onError, 0, 0,
         1230  +                           0, sortOrder, 0);
  1229   1231       if( p ){
  1230   1232         p->autoIndex = 2;
  1231   1233       }
  1232   1234       pList = 0;
  1233   1235     }
  1234   1236   
  1235   1237   primary_key_exit:
................................................................................
  2479   2481     Token *pName1,     /* First part of index name. May be NULL */
  2480   2482     Token *pName2,     /* Second part of index name. May be NULL */
  2481   2483     SrcList *pTblName, /* Table to index. Use pParse->pNewTable if 0 */
  2482   2484     ExprList *pList,   /* A list of columns to be indexed */
  2483   2485     int onError,       /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
  2484   2486     Token *pStart,     /* The CREATE token that begins this statement */
  2485   2487     Token *pEnd,       /* The ")" that closes the CREATE INDEX statement */
         2488  +  Expr *pPIWhere,    /* WHERE clause for partial indices */
  2486   2489     int sortOrder,     /* Sort order of primary key when pList==NULL */
  2487   2490     int ifNotExist     /* Omit error if index already exists */
  2488   2491   ){
  2489   2492     Index *pRet = 0;     /* Pointer to return */
  2490   2493     Table *pTab = 0;     /* Table to be indexed */
  2491   2494     Index *pIndex = 0;   /* The index to be created */
  2492   2495     char *zName = 0;     /* Name of the index */
................................................................................
  2695   2698     memcpy(pIndex->zName, zName, nName+1);
  2696   2699     pIndex->pTable = pTab;
  2697   2700     pIndex->nColumn = pList->nExpr;
  2698   2701     pIndex->onError = (u8)onError;
  2699   2702     pIndex->uniqNotNull = onError==OE_Abort;
  2700   2703     pIndex->autoIndex = (u8)(pName==0);
  2701   2704     pIndex->pSchema = db->aDb[iDb].pSchema;
         2705  +  pIndex->pPartIdxWhere = pPIWhere;
         2706  +  pPIWhere = 0;
  2702   2707     assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
  2703   2708   
  2704   2709     /* Check to see if we should honor DESC requests on index columns
  2705   2710     */
  2706   2711     if( pDb->pSchema->file_format>=4 ){
  2707   2712       sortOrderMask = -1;   /* Honor DESC */
  2708   2713     }else{
................................................................................
  2929   2934       }
  2930   2935       pRet = pIndex;
  2931   2936       pIndex = 0;
  2932   2937     }
  2933   2938   
  2934   2939     /* Clean up before exiting */
  2935   2940   exit_create_index:
  2936         -  if( pIndex ){
  2937         -    sqlite3DbFree(db, pIndex->zColAff);
  2938         -    sqlite3DbFree(db, pIndex);
  2939         -  }
         2941  +  if( pIndex ) freeIndex(db, pIndex);
         2942  +  sqlite3ExprDelete(db, pPIWhere);
  2940   2943     sqlite3ExprListDelete(db, pList);
  2941   2944     sqlite3SrcListDelete(db, pTblName);
  2942   2945     sqlite3DbFree(db, zName);
  2943   2946     return pRet;
  2944   2947   }
  2945   2948   
  2946   2949   /*

Changes to src/parse.y.

   296    296   // In addition to the type name, we also care about the primary key and
   297    297   // UNIQUE constraints.
   298    298   //
   299    299   ccons ::= NULL onconf.
   300    300   ccons ::= NOT NULL onconf(R).    {sqlite3AddNotNull(pParse, R);}
   301    301   ccons ::= PRIMARY KEY sortorder(Z) onconf(R) autoinc(I).
   302    302                                    {sqlite3AddPrimaryKey(pParse,0,R,I,Z);}
   303         -ccons ::= UNIQUE onconf(R).      {sqlite3CreateIndex(pParse,0,0,0,0,R,0,0,0,0);}
          303  +ccons ::= UNIQUE onconf(R).      {sqlite3CreateIndex(pParse,0,0,0,0,R,0,
          304  +                                                     0,0,0,0);}
   304    305   ccons ::= CHECK LP expr(X) RP.   {sqlite3AddCheckConstraint(pParse,X.pExpr);}
   305    306   ccons ::= REFERENCES nm(T) idxlist_opt(TA) refargs(R).
   306    307                                    {sqlite3CreateForeignKey(pParse,0,&T,TA,R);}
   307    308   ccons ::= defer_subclause(D).    {sqlite3DeferForeignKey(pParse,D);}
   308    309   ccons ::= COLLATE ids(C).        {sqlite3AddCollateType(pParse, &C);}
   309    310   
   310    311   // The optional AUTOINCREMENT keyword
................................................................................
   345    346   conslist ::= tcons.
   346    347   tconscomma ::= COMMA.            {pParse->constraintName.n = 0;}
   347    348   tconscomma ::= .
   348    349   tcons ::= CONSTRAINT nm(X).      {pParse->constraintName = X;}
   349    350   tcons ::= PRIMARY KEY LP idxlist(X) autoinc(I) RP onconf(R).
   350    351                                    {sqlite3AddPrimaryKey(pParse,X,R,I,0);}
   351    352   tcons ::= UNIQUE LP idxlist(X) RP onconf(R).
   352         -                                 {sqlite3CreateIndex(pParse,0,0,0,X,R,0,0,0,0);}
          353  +                               {sqlite3CreateIndex(pParse,0,0,0,X,R,0,0,0,0,0);}
   353    354   tcons ::= CHECK LP expr(E) RP onconf.
   354    355                                    {sqlite3AddCheckConstraint(pParse,E.pExpr);}
   355    356   tcons ::= FOREIGN KEY LP idxlist(FA) RP
   356    357             REFERENCES nm(T) idxlist_opt(TA) refargs(R) defer_subclause_opt(D). {
   357    358       sqlite3CreateForeignKey(pParse, FA, &T, TA, R);
   358    359       sqlite3DeferForeignKey(pParse, D);
   359    360   }
................................................................................
  1121   1122   nexprlist(A) ::= expr(Y).
  1122   1123       {A = sqlite3ExprListAppend(pParse,0,Y.pExpr);}
  1123   1124   
  1124   1125   
  1125   1126   ///////////////////////////// The CREATE INDEX command ///////////////////////
  1126   1127   //
  1127   1128   cmd ::= createkw(S) uniqueflag(U) INDEX ifnotexists(NE) nm(X) dbnm(D)
  1128         -        ON nm(Y) LP idxlist(Z) RP(E). {
         1129  +        ON nm(Y) LP idxlist(Z) RP(E) where_opt(W). {
  1129   1130     sqlite3CreateIndex(pParse, &X, &D, 
  1130   1131                        sqlite3SrcListAppend(pParse->db,0,&Y,0), Z, U,
  1131         -                      &S, &E, SQLITE_SO_ASC, NE);
         1132  +                      &S, &E, W, SQLITE_SO_ASC, NE);
  1132   1133   }
  1133   1134   
  1134   1135   %type uniqueflag {int}
  1135   1136   uniqueflag(A) ::= UNIQUE.  {A = OE_Abort;}
  1136   1137   uniqueflag(A) ::= .        {A = OE_None;}
  1137   1138   
  1138   1139   %type idxlist {ExprList*}

Changes to src/sqliteInt.h.

  1534   1534     tRowcnt *aiRowEst;       /* From ANALYZE: Est. rows selected by each column */
  1535   1535     Table *pTable;           /* The SQL table being indexed */
  1536   1536     char *zColAff;           /* String defining the affinity of each column */
  1537   1537     Index *pNext;            /* The next index associated with the same table */
  1538   1538     Schema *pSchema;         /* Schema containing this index */
  1539   1539     u8 *aSortOrder;          /* for each column: True==DESC, False==ASC */
  1540   1540     char **azColl;           /* Array of collation sequence names for index */
         1541  +  Expr *pPartIdxWhere;     /* WHERE clause for partial indices */
  1541   1542     int tnum;                /* DB Page containing root of this index */
  1542   1543     u16 nColumn;             /* Number of columns in table used by this index */
  1543   1544     u8 onError;              /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
  1544   1545     unsigned autoIndex:2;    /* 1==UNIQUE, 2==PRIMARY KEY, 0==CREATE INDEX */
  1545   1546     unsigned bUnordered:1;   /* Use this index for == or IN queries only */
  1546   1547     unsigned uniqNotNull:1;  /* True if UNIQUE and NOT NULL for all columns */
  1547   1548   #ifdef SQLITE_ENABLE_STAT3
................................................................................
  2779   2780   void sqlite3SrcListIndexedBy(Parse *, SrcList *, Token *);
  2780   2781   int sqlite3IndexedByLookup(Parse *, struct SrcList_item *);
  2781   2782   void sqlite3SrcListShiftJoinType(SrcList*);
  2782   2783   void sqlite3SrcListAssignCursors(Parse*, SrcList*);
  2783   2784   void sqlite3IdListDelete(sqlite3*, IdList*);
  2784   2785   void sqlite3SrcListDelete(sqlite3*, SrcList*);
  2785   2786   Index *sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*,
  2786         -                        Token*, int, int);
         2787  +                        Token*, Expr*, int, int);
  2787   2788   void sqlite3DropIndex(Parse*, SrcList*, int);
  2788   2789   int sqlite3Select(Parse*, Select*, SelectDest*);
  2789   2790   Select *sqlite3SelectNew(Parse*,ExprList*,SrcList*,Expr*,ExprList*,
  2790   2791                            Expr*,ExprList*,u16,Expr*,Expr*);
  2791   2792   void sqlite3SelectDelete(sqlite3*, Select*);
  2792   2793   Table *sqlite3SrcListLookup(Parse*, SrcList*);
  2793   2794   int sqlite3IsReadOnly(Parse*, Table*, int);

Added test/index6.test.

            1  +# 2013-07-31
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# Test cases for partial indices
           13  +#
           14  +
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +
           19  +load_static_extension db wholenumber;
           20  +do_test index6-1.1 {
           21  +  execsql {
           22  +    CREATE TABLE t1(a,b);
           23  +    CREATE INDEX t1a ON t1(a) WHERE a IS NOT NULL;
           24  +    CREATE INDEX t1b ON t1(b) WHERE b>10;
           25  +    CREATE VIRTUAL TABLE nums USING wholenumber;
           26  +    INSERT INTO t1(a,b)
           27  +       SELECT CASE WHEN value%3!=0 THEN value END, value
           28  +         FROM nums WHERE value<=20;
           29  +    SELECT count(a), count(b) FROM t1;
           30  +  }
           31  +} {14 20}
           32  +
           33  +finish_test