Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Test cases and bug fixes for the partial index logic. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | partial-indices |
Files: | files | file ages | folders |
SHA1: |
6b73ae7c123801787c8994113cbeb87e |
User & Date: | drh 2013-08-01 03:36:59.964 |
Context
2013-08-01
| ||
04:39 | Fix the ANALYZE command to work with partial indices. (check-in: 60353124f4 user: drh tags: partial-indices) | |
03:36 | Test cases and bug fixes for the partial index logic. (check-in: 6b73ae7c12 user: drh tags: partial-indices) | |
01:14 | Add the logic to keep partial indices up to date through DML statements and when new partial indices are created. This new logic is untested except to verify that it does not interfere with full indices. (check-in: fb9044d15a user: drh tags: partial-indices) | |
Changes
Changes to src/build.c.
︙ | ︙ | |||
1222 1223 1224 1225 1226 1227 1228 | }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 | }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, sortOrder, 0); if( p ){ p->autoIndex = 2; } pList = 0; } |
︙ | ︙ | |||
2463 2464 2465 2466 2467 2468 2469 | Parse *pParse, /* All information about this parse */ 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 */ | < | 2463 2464 2465 2466 2467 2468 2469 2470 2471 2472 2473 2474 2475 2476 | Parse *pParse, /* All information about this parse */ 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 */ 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 */ |
︙ | ︙ | |||
2486 2487 2488 2489 2490 2491 2492 | int iDb; /* Index of the database that is being written */ Token *pName = 0; /* Unqualified name of the index to create */ struct ExprList_item *pListItem; /* For looping over pList */ int nCol; int nExtra = 0; char *zExtra; | < | 2485 2486 2487 2488 2489 2490 2491 2492 2493 2494 2495 2496 2497 2498 | int iDb; /* Index of the database that is being written */ Token *pName = 0; /* Unqualified name of the index to create */ struct ExprList_item *pListItem; /* For looping over pList */ int nCol; int nExtra = 0; char *zExtra; assert( pParse->nErr==0 ); /* Never called with prior errors */ if( db->mallocFailed || IN_DECLARE_VTAB ){ goto exit_create_index; } if( SQLITE_OK!=sqlite3ReadSchema(pParse) ){ goto exit_create_index; } |
︙ | ︙ | |||
2859 2860 2861 2862 2863 2864 2865 | sqlite3BeginWriteOperation(pParse, 1, iDb); sqlite3VdbeAddOp2(v, OP_CreateIndex, iDb, iMem); /* Gather the complete text of the CREATE INDEX statement into ** the zStmt variable */ if( pStart ){ | > | | < < | 2857 2858 2859 2860 2861 2862 2863 2864 2865 2866 2867 2868 2869 2870 2871 2872 2873 2874 2875 | sqlite3BeginWriteOperation(pParse, 1, iDb); sqlite3VdbeAddOp2(v, OP_CreateIndex, iDb, iMem); /* Gather the complete text of the CREATE INDEX statement into ** the zStmt variable */ if( pStart ){ int n = (pParse->sLastToken.z - pName->z) + 1; if( pName->z[n-1]==';' ) n--; /* A named index with an explicit CREATE INDEX statement */ zStmt = sqlite3MPrintf(db, "CREATE%s INDEX %.*s", onError==OE_None ? "" : " UNIQUE", n, pName->z); }else{ /* An automatic index created by a PRIMARY KEY or UNIQUE constraint */ /* zStmt = sqlite3MPrintf(""); */ zStmt = 0; } /* Add an entry in sqlite_master for this index |
︙ | ︙ |
Changes to src/insert.c.
︙ | ︙ | |||
1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 | sqlite3VdbeChangeP4(v, -1, sqlite3IndexAffinityStr(v, pIdx), P4_TRANSIENT); sqlite3ExprCacheAffinityChange(pParse, regIdx, pIdx->nColumn+1); /* Find out what action to take in case there is an indexing conflict */ onError = pIdx->onError; if( onError==OE_None ){ sqlite3ReleaseTempRange(pParse, regIdx, pIdx->nColumn+1); continue; /* pIdx is not a UNIQUE index */ } if( overrideError!=OE_Default ){ onError = overrideError; }else if( onError==OE_Default ){ onError = OE_Abort; } | > | 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 | sqlite3VdbeChangeP4(v, -1, sqlite3IndexAffinityStr(v, pIdx), P4_TRANSIENT); sqlite3ExprCacheAffinityChange(pParse, regIdx, pIdx->nColumn+1); /* Find out what action to take in case there is an indexing conflict */ onError = pIdx->onError; if( onError==OE_None ){ sqlite3ReleaseTempRange(pParse, regIdx, pIdx->nColumn+1); sqlite3VdbeResolveLabel(v, addrSkipRow); continue; /* pIdx is not a UNIQUE index */ } if( overrideError!=OE_Default ){ onError = overrideError; }else if( onError==OE_Default ){ onError = OE_Abort; } |
︙ | ︙ |
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 | // 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);} 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 |
︙ | ︙ | |||
346 347 348 349 350 351 352 | 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). | | | 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 | 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);} 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); } |
︙ | ︙ | |||
1122 1123 1124 1125 1126 1127 1128 | 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) | | | | 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 | 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 where_opt(W). { sqlite3CreateIndex(pParse, &X, &D, sqlite3SrcListAppend(pParse->db,0,&Y,0), Z, U, &S, 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/pragma.c.
︙ | ︙ | |||
1396 1397 1398 1399 1400 1401 1402 | for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ sqlite3VdbeAddOp2(v, OP_Integer, pIdx->tnum, 2+cnt); cnt++; } } /* Make sure sufficient number of registers have been allocated */ | < | < | 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 | for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ sqlite3VdbeAddOp2(v, OP_Integer, pIdx->tnum, 2+cnt); cnt++; } } /* Make sure sufficient number of registers have been allocated */ pParse->nMem = MAX( pParse->nMem, cnt+4 ); /* Do the b-tree integrity checks */ sqlite3VdbeAddOp3(v, OP_IntegrityCk, 2, cnt, 1); sqlite3VdbeChangeP5(v, (u8)i); addr = sqlite3VdbeAddOp1(v, OP_IsNull, 2); sqlite3VdbeAddOp4(v, OP_String8, 0, 3, 0, sqlite3MPrintf(db, "*** in database %s ***\n", db->aDb[i].zName), |
︙ | ︙ | |||
1424 1425 1426 1427 1428 1429 1430 | int loopTop; if( pTab->pIndex==0 ) continue; addr = sqlite3VdbeAddOp1(v, OP_IfPos, 1); /* Stop if out of errors */ sqlite3VdbeAddOp2(v, OP_Halt, 0, 0); sqlite3VdbeJumpHere(v, addr); sqlite3OpenTableAndIndices(pParse, pTab, 1, OP_OpenRead); | > | > > | < > | | > > > < < < < < < < < < < < < | | | | | < | | | | < < > | 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 | int loopTop; if( pTab->pIndex==0 ) continue; addr = sqlite3VdbeAddOp1(v, OP_IfPos, 1); /* Stop if out of errors */ sqlite3VdbeAddOp2(v, OP_Halt, 0, 0); sqlite3VdbeJumpHere(v, addr); sqlite3OpenTableAndIndices(pParse, pTab, 1, OP_OpenRead); for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){ sqlite3VdbeAddOp2(v, OP_Integer, 0, 7+j); /* index entries counter */ } pParse->nMem = MAX(pParse->nMem, 7+j); loopTop = sqlite3VdbeAddOp2(v, OP_Rewind, 1, 0) + 1; for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){ int jmp2, jmp3; int r1; static const VdbeOpList idxErr[] = { { OP_AddImm, 1, -1, 0}, { OP_String8, 0, 3, 0}, /* 1 */ { OP_Rowid, 1, 4, 0}, { OP_String8, 0, 5, 0}, /* 3 */ { OP_String8, 0, 6, 0}, /* 4 */ { OP_Concat, 4, 3, 3}, { OP_Concat, 5, 3, 3}, { OP_Concat, 6, 3, 3}, { OP_ResultRow, 3, 1, 0}, { OP_IfPos, 1, 0, 0}, /* 9 */ { OP_Halt, 0, 0, 0}, }; r1 = sqlite3GenerateIndexKey(pParse, pIdx, 1, 3, 0, &jmp3); sqlite3VdbeAddOp2(v, OP_AddImm, 7+j, 1); /* increment entry count */ jmp2 = sqlite3VdbeAddOp4Int(v, OP_Found, j+2, 0, r1, pIdx->nColumn+1); addr = sqlite3VdbeAddOpList(v, ArraySize(idxErr), idxErr); sqlite3VdbeChangeP4(v, addr+1, "rowid ", P4_STATIC); sqlite3VdbeChangeP4(v, addr+3, " missing from index ", P4_STATIC); sqlite3VdbeChangeP4(v, addr+4, pIdx->zName, P4_TRANSIENT); sqlite3VdbeJumpHere(v, addr+9); sqlite3VdbeJumpHere(v, jmp2); sqlite3VdbeResolveLabel(v, jmp3); } sqlite3VdbeAddOp2(v, OP_Next, 1, loopTop); sqlite3VdbeJumpHere(v, loopTop-1); #ifndef SQLITE_OMIT_BTREECOUNT sqlite3VdbeAddOp4(v, OP_String8, 0, 2, 0, "wrong # of entries in index ", P4_STATIC); for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){ addr = sqlite3VdbeCurrentAddr(v); sqlite3VdbeAddOp2(v, OP_IfPos, 1, addr+2); sqlite3VdbeAddOp2(v, OP_Halt, 0, 0); sqlite3VdbeAddOp2(v, OP_Count, j+2, 3); sqlite3VdbeAddOp3(v, OP_Eq, 7+j, addr+8, 3); sqlite3VdbeAddOp2(v, OP_AddImm, 1, -1); sqlite3VdbeAddOp4(v, OP_String8, 0, 3, 0, pIdx->zName, P4_TRANSIENT); sqlite3VdbeAddOp3(v, OP_Concat, 3, 2, 7); sqlite3VdbeAddOp2(v, OP_ResultRow, 7, 1); } #endif /* SQLITE_OMIT_BTREECOUNT */ } } addr = sqlite3VdbeAddOpList(v, ArraySize(endCode), endCode); sqlite3VdbeChangeP2(v, addr, -mxErr); sqlite3VdbeJumpHere(v, addr+1); sqlite3VdbeChangeP4(v, addr+2, "ok", P4_STATIC); }else |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
2782 2783 2784 2785 2786 2787 2788 | 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*, | | | 2782 2783 2784 2785 2786 2787 2788 2789 2790 2791 2792 2793 2794 2795 2796 | 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*, 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); |
︙ | ︙ |
Changes to test/index6.test.
︙ | ︙ | |||
52 53 54 55 56 57 58 59 60 | } {1 {functions prohibited in partial index WHERE clauses}} do_test index6-1.6 { catchsql { CREATE INDEX bad1 ON t1(a,b) WHERE a NOT LIKE 'abc%'; } } {1 {functions prohibited in partial index WHERE clauses}} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 | } {1 {functions prohibited in partial index WHERE clauses}} do_test index6-1.6 { catchsql { CREATE INDEX bad1 ON t1(a,b) WHERE a NOT LIKE 'abc%'; } } {1 {functions prohibited in partial index WHERE clauses}} do_test index6-1.10 { execsql { ANALYZE; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; PRAGMA integrity_check; } } {t1a {14 1} t1b {10 1} ok} do_test index6-1.11 { execsql { UPDATE t1 SET a=b; ANALYZE; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; PRAGMA integrity_check; } } {t1a {20 1} t1b {10 1} ok} do_test index6-1.11 { execsql { UPDATE t1 SET a=NULL WHERE b%3!=0; UPDATE t1 SET b=b+100; ANALYZE; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; PRAGMA integrity_check; } } {t1a {6 1} t1b {20 1} ok} do_test index6-1.12 { execsql { UPDATE t1 SET a=CASE WHEN b%3!=0 THEN b END; UPDATE t1 SET b=b-100; ANALYZE; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; PRAGMA integrity_check; } } {t1a {13 1} t1b {10 1} ok} do_test index6-1.13 { execsql { DELETE FROM t1 WHERE b BETWEEN 8 AND 12; ANALYZE; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; PRAGMA integrity_check; } } {t1a {10 1} t1b {8 1} ok} do_test index6-1.14 { execsql { REINDEX; ANALYZE; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; PRAGMA integrity_check; } } {t1a {10 1} t1b {8 1} ok} finish_test |