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 |
Timelines: | family | ancestors | descendants | both | partial-indices |
Files: | files | file ages | folders |
SHA1: |
6794b2dcb48b3507caccfc7867fc1858 |
User & Date: | drh 2013-07-31 18:12:26.006 |
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: f2aa7842c8 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: 6794b2dcb4 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: eb6d4278b8 user: drh tags: trunk) | |
Changes
Changes to src/build.c.
︙ | ︙ | |||
378 379 380 381 382 383 384 385 386 387 388 389 390 391 | /* ** Reclaim the memory used by an index */ static void freeIndex(sqlite3 *db, Index *p){ #ifndef SQLITE_OMIT_ANALYZE sqlite3DeleteIndexSamples(db, p); #endif sqlite3DbFree(db, p->zColAff); sqlite3DbFree(db, p); } /* ** For the index called zIdxName which is found in the database iDb, ** unlike that index from its Table then remove the index from | > | 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 | /* ** Reclaim the memory used by an index */ static void freeIndex(sqlite3 *db, Index *p){ #ifndef SQLITE_OMIT_ANALYZE sqlite3DeleteIndexSamples(db, p); #endif sqlite3ExprDelete(db, p->pPartIdxWhere); sqlite3DbFree(db, p->zColAff); sqlite3DbFree(db, p); } /* ** For the index called zIdxName which is found in the database iDb, ** unlike that index from its Table then remove the index from |
︙ | ︙ | |||
1221 1222 1223 1224 1225 1226 1227 | }else if( autoInc ){ #ifndef SQLITE_OMIT_AUTOINCREMENT sqlite3ErrorMsg(pParse, "AUTOINCREMENT is only allowed on an " "INTEGER PRIMARY KEY"); #endif }else{ Index *p; | | > | 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 | }else if( autoInc ){ #ifndef SQLITE_OMIT_AUTOINCREMENT sqlite3ErrorMsg(pParse, "AUTOINCREMENT is only allowed on an " "INTEGER PRIMARY KEY"); #endif }else{ Index *p; p = sqlite3CreateIndex(pParse, 0, 0, 0, pList, onError, 0, 0, 0, sortOrder, 0); if( p ){ p->autoIndex = 2; } pList = 0; } primary_key_exit: |
︙ | ︙ | |||
2479 2480 2481 2482 2483 2484 2485 2486 2487 2488 2489 2490 2491 2492 | Token *pName1, /* First part of index name. May be NULL */ Token *pName2, /* Second part of index name. May be NULL */ SrcList *pTblName, /* Table to index. Use pParse->pNewTable if 0 */ ExprList *pList, /* A list of columns to be indexed */ int onError, /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */ Token *pStart, /* The CREATE token that begins this statement */ Token *pEnd, /* The ")" that closes the CREATE INDEX statement */ int sortOrder, /* Sort order of primary key when pList==NULL */ int ifNotExist /* Omit error if index already exists */ ){ Index *pRet = 0; /* Pointer to return */ Table *pTab = 0; /* Table to be indexed */ Index *pIndex = 0; /* The index to be created */ char *zName = 0; /* Name of the index */ | > | 2481 2482 2483 2484 2485 2486 2487 2488 2489 2490 2491 2492 2493 2494 2495 | Token *pName1, /* First part of index name. May be NULL */ Token *pName2, /* Second part of index name. May be NULL */ SrcList *pTblName, /* Table to index. Use pParse->pNewTable if 0 */ ExprList *pList, /* A list of columns to be indexed */ int onError, /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */ Token *pStart, /* The CREATE token that begins this statement */ Token *pEnd, /* The ")" that closes the CREATE INDEX statement */ Expr *pPIWhere, /* WHERE clause for partial indices */ int sortOrder, /* Sort order of primary key when pList==NULL */ int ifNotExist /* Omit error if index already exists */ ){ Index *pRet = 0; /* Pointer to return */ Table *pTab = 0; /* Table to be indexed */ Index *pIndex = 0; /* The index to be created */ char *zName = 0; /* Name of the index */ |
︙ | ︙ | |||
2695 2696 2697 2698 2699 2700 2701 2702 2703 2704 2705 2706 2707 2708 | memcpy(pIndex->zName, zName, nName+1); pIndex->pTable = pTab; pIndex->nColumn = pList->nExpr; pIndex->onError = (u8)onError; pIndex->uniqNotNull = onError==OE_Abort; pIndex->autoIndex = (u8)(pName==0); pIndex->pSchema = db->aDb[iDb].pSchema; assert( sqlite3SchemaMutexHeld(db, iDb, 0) ); /* Check to see if we should honor DESC requests on index columns */ if( pDb->pSchema->file_format>=4 ){ sortOrderMask = -1; /* Honor DESC */ }else{ | > > | 2698 2699 2700 2701 2702 2703 2704 2705 2706 2707 2708 2709 2710 2711 2712 2713 | memcpy(pIndex->zName, zName, nName+1); pIndex->pTable = pTab; pIndex->nColumn = pList->nExpr; pIndex->onError = (u8)onError; pIndex->uniqNotNull = onError==OE_Abort; pIndex->autoIndex = (u8)(pName==0); pIndex->pSchema = db->aDb[iDb].pSchema; pIndex->pPartIdxWhere = pPIWhere; pPIWhere = 0; assert( sqlite3SchemaMutexHeld(db, iDb, 0) ); /* Check to see if we should honor DESC requests on index columns */ if( pDb->pSchema->file_format>=4 ){ sortOrderMask = -1; /* Honor DESC */ }else{ |
︙ | ︙ | |||
2929 2930 2931 2932 2933 2934 2935 | } pRet = pIndex; pIndex = 0; } /* Clean up before exiting */ exit_create_index: | | < | < | 2934 2935 2936 2937 2938 2939 2940 2941 2942 2943 2944 2945 2946 2947 2948 2949 | } pRet = pIndex; pIndex = 0; } /* Clean up before exiting */ exit_create_index: if( pIndex ) freeIndex(db, pIndex); sqlite3ExprDelete(db, pPIWhere); sqlite3ExprListDelete(db, pList); sqlite3SrcListDelete(db, pTblName); sqlite3DbFree(db, zName); return pRet; } /* |
︙ | ︙ |
Changes to src/parse.y.
︙ | ︙ | |||
296 297 298 299 300 301 302 | // In addition to the type name, we also care about the primary key and // UNIQUE constraints. // ccons ::= NULL onconf. ccons ::= NOT NULL onconf(R). {sqlite3AddNotNull(pParse, R);} ccons ::= PRIMARY KEY sortorder(Z) onconf(R) autoinc(I). {sqlite3AddPrimaryKey(pParse,0,R,I,Z);} | | > | 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 | // In addition to the type name, we also care about the primary key and // UNIQUE constraints. // ccons ::= NULL onconf. ccons ::= NOT NULL onconf(R). {sqlite3AddNotNull(pParse, R);} ccons ::= PRIMARY KEY sortorder(Z) onconf(R) autoinc(I). {sqlite3AddPrimaryKey(pParse,0,R,I,Z);} ccons ::= UNIQUE onconf(R). {sqlite3CreateIndex(pParse,0,0,0,0,R,0, 0,0,0,0);} ccons ::= CHECK LP expr(X) RP. {sqlite3AddCheckConstraint(pParse,X.pExpr);} ccons ::= REFERENCES nm(T) idxlist_opt(TA) refargs(R). {sqlite3CreateForeignKey(pParse,0,&T,TA,R);} ccons ::= defer_subclause(D). {sqlite3DeferForeignKey(pParse,D);} ccons ::= COLLATE ids(C). {sqlite3AddCollateType(pParse, &C);} // The optional AUTOINCREMENT keyword |
︙ | ︙ | |||
345 346 347 348 349 350 351 | conslist ::= tcons. tconscomma ::= COMMA. {pParse->constraintName.n = 0;} tconscomma ::= . tcons ::= CONSTRAINT nm(X). {pParse->constraintName = X;} tcons ::= PRIMARY KEY LP idxlist(X) autoinc(I) RP onconf(R). {sqlite3AddPrimaryKey(pParse,X,R,I,0);} tcons ::= UNIQUE LP idxlist(X) RP onconf(R). | | | 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 | conslist ::= tcons. tconscomma ::= COMMA. {pParse->constraintName.n = 0;} tconscomma ::= . tcons ::= CONSTRAINT nm(X). {pParse->constraintName = X;} tcons ::= PRIMARY KEY LP idxlist(X) autoinc(I) RP onconf(R). {sqlite3AddPrimaryKey(pParse,X,R,I,0);} tcons ::= UNIQUE LP idxlist(X) RP onconf(R). {sqlite3CreateIndex(pParse,0,0,0,X,R,0,0,0,0,0);} tcons ::= CHECK LP expr(E) RP onconf. {sqlite3AddCheckConstraint(pParse,E.pExpr);} tcons ::= FOREIGN KEY LP idxlist(FA) RP REFERENCES nm(T) idxlist_opt(TA) refargs(R) defer_subclause_opt(D). { sqlite3CreateForeignKey(pParse, FA, &T, TA, R); sqlite3DeferForeignKey(pParse, D); } |
︙ | ︙ | |||
1121 1122 1123 1124 1125 1126 1127 | nexprlist(A) ::= expr(Y). {A = sqlite3ExprListAppend(pParse,0,Y.pExpr);} ///////////////////////////// The CREATE INDEX command /////////////////////// // cmd ::= createkw(S) uniqueflag(U) INDEX ifnotexists(NE) nm(X) dbnm(D) | | | | 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 | nexprlist(A) ::= expr(Y). {A = sqlite3ExprListAppend(pParse,0,Y.pExpr);} ///////////////////////////// The CREATE INDEX command /////////////////////// // cmd ::= createkw(S) uniqueflag(U) INDEX ifnotexists(NE) nm(X) dbnm(D) ON nm(Y) LP idxlist(Z) RP(E) where_opt(W). { sqlite3CreateIndex(pParse, &X, &D, sqlite3SrcListAppend(pParse->db,0,&Y,0), Z, U, &S, &E, W, SQLITE_SO_ASC, NE); } %type uniqueflag {int} uniqueflag(A) ::= UNIQUE. {A = OE_Abort;} uniqueflag(A) ::= . {A = OE_None;} %type idxlist {ExprList*} |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 1547 | tRowcnt *aiRowEst; /* From ANALYZE: Est. rows selected by each column */ Table *pTable; /* The SQL table being indexed */ char *zColAff; /* String defining the affinity of each column */ Index *pNext; /* The next index associated with the same table */ Schema *pSchema; /* Schema containing this index */ u8 *aSortOrder; /* for each column: True==DESC, False==ASC */ char **azColl; /* Array of collation sequence names for index */ int tnum; /* DB Page containing root of this index */ u16 nColumn; /* Number of columns in table used by this index */ u8 onError; /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */ unsigned autoIndex:2; /* 1==UNIQUE, 2==PRIMARY KEY, 0==CREATE INDEX */ unsigned bUnordered:1; /* Use this index for == or IN queries only */ unsigned uniqNotNull:1; /* True if UNIQUE and NOT NULL for all columns */ #ifdef SQLITE_ENABLE_STAT3 | > | 1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 | tRowcnt *aiRowEst; /* From ANALYZE: Est. rows selected by each column */ Table *pTable; /* The SQL table being indexed */ char *zColAff; /* String defining the affinity of each column */ Index *pNext; /* The next index associated with the same table */ Schema *pSchema; /* Schema containing this index */ u8 *aSortOrder; /* for each column: True==DESC, False==ASC */ char **azColl; /* Array of collation sequence names for index */ Expr *pPartIdxWhere; /* WHERE clause for partial indices */ int tnum; /* DB Page containing root of this index */ u16 nColumn; /* Number of columns in table used by this index */ u8 onError; /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */ unsigned autoIndex:2; /* 1==UNIQUE, 2==PRIMARY KEY, 0==CREATE INDEX */ unsigned bUnordered:1; /* Use this index for == or IN queries only */ unsigned uniqNotNull:1; /* True if UNIQUE and NOT NULL for all columns */ #ifdef SQLITE_ENABLE_STAT3 |
︙ | ︙ | |||
2779 2780 2781 2782 2783 2784 2785 | void sqlite3SrcListIndexedBy(Parse *, SrcList *, Token *); int sqlite3IndexedByLookup(Parse *, struct SrcList_item *); void sqlite3SrcListShiftJoinType(SrcList*); void sqlite3SrcListAssignCursors(Parse*, SrcList*); void sqlite3IdListDelete(sqlite3*, IdList*); void sqlite3SrcListDelete(sqlite3*, SrcList*); Index *sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*, | | | 2780 2781 2782 2783 2784 2785 2786 2787 2788 2789 2790 2791 2792 2793 2794 | void sqlite3SrcListIndexedBy(Parse *, SrcList *, Token *); int sqlite3IndexedByLookup(Parse *, struct SrcList_item *); void sqlite3SrcListShiftJoinType(SrcList*); void sqlite3SrcListAssignCursors(Parse*, SrcList*); void sqlite3IdListDelete(sqlite3*, IdList*); void sqlite3SrcListDelete(sqlite3*, SrcList*); Index *sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*, Token*, Expr*, int, int); void sqlite3DropIndex(Parse*, SrcList*, int); int sqlite3Select(Parse*, Select*, SelectDest*); Select *sqlite3SelectNew(Parse*,ExprList*,SrcList*,Expr*,ExprList*, Expr*,ExprList*,u16,Expr*,Expr*); void sqlite3SelectDelete(sqlite3*, Select*); Table *sqlite3SrcListLookup(Parse*, SrcList*); int sqlite3IsReadOnly(Parse*, Table*, int); |
︙ | ︙ |
Added test/index6.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | # 2013-07-31 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # Test cases for partial indices # set testdir [file dirname $argv0] source $testdir/tester.tcl load_static_extension db wholenumber; do_test index6-1.1 { execsql { CREATE TABLE t1(a,b); CREATE INDEX t1a ON t1(a) WHERE a IS NOT NULL; CREATE INDEX t1b ON t1(b) WHERE b>10; CREATE VIRTUAL TABLE nums USING wholenumber; INSERT INTO t1(a,b) SELECT CASE WHEN value%3!=0 THEN value END, value FROM nums WHERE value<=20; SELECT count(a), count(b) FROM t1; } } {14 20} finish_test |