From b1c685b0c8f85b361642df2e8db296e3102adf40 Mon Sep 17 00:00:00 2001 From: danielk1977 Date: Mon, 6 Oct 2008 16:18:39 +0000 Subject: [PATCH] Allow INDEXED BY and NOT INDEXED to be used in UPDATE and DELETE statements. (CVS 5772) FossilOrigin-Name: 83a7e446b2d4846a6f92bd831a2adaa265f5a786 --- manifest | 24 ++++++++++++------------ manifest.uuid | 2 +- src/build.c | 28 +++++++++++++++++++--------- src/delete.c | 25 +++++++++++++------------ src/parse.y | 17 +++++++++++------ src/select.c | 41 ++++++++++++++++++++++++++++------------- src/sqliteInt.h | 6 ++++-- test/indexedby.test | 44 +++++++++++++++++++++++++++++++++++++++++++- 8 files changed, 131 insertions(+), 56 deletions(-) diff --git a/manifest b/manifest index 0eced9d07e..83c4ba23ff 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Add\sa\stest\sscript\sto\sverify\sthat\sticket\s#3419\sstays\sfixed.\s(CVS\s5771) -D 2008-10-06T15:31:13 +C Allow\sINDEXED\sBY\sand\sNOT\sINDEXED\sto\sbe\sused\sin\sUPDATE\sand\sDELETE\sstatements.\s(CVS\s5772) +D 2008-10-06T16:18:40 F Makefile.arm-wince-mingw32ce-gcc fcd5e9cd67fe88836360bb4f9ef4cb7f8e2fb5a0 F Makefile.in e4ab842f9a64ef61d57093539a8aab76b12810db F Makefile.linux-gcc d53183f4aa6a9192d249731c90dbdffbd2c68654 @@ -102,11 +102,11 @@ F src/btmutex.c 709cad2cdca0afd013f0f612363810e53f59ec53 F src/btree.c 64a38df6f0a9997563418ed194984b81e4ab3694 F src/btree.h 6371c5e599fab391a150c96afbc10062b276d107 F src/btreeInt.h e38e9b2b285f40f5bc0a6664f630d4a141622f16 -F src/build.c 5097bb7978bfdae4ae052abc16aa00c1054b7956 +F src/build.c 8714bd809583bbe07bf22d0e1808a3fc31abe330 F src/callback.c 7a40fd44da3eb89e7f6eff30aa6f940c45d73a97 F src/complete.c cb14e06dbe79dee031031f0d9e686ff306afe07c F src/date.c 5c092296c03d658e84884121a694150964d6861d -F src/delete.c 52c3614b1dc815f54d64a98dc65a393737f68b91 +F src/delete.c 924c43df7533479d0aa93dc0eed32c9b8cf64c9f F src/expr.c 30973b017bf95ca767f5eec64918c8afc425488d F src/fault.c dc88c821842157460750d2d61a8a8b4197d047ff F src/func.c 8431b40a7843d1024145684d303c55b4ee087bbe @@ -139,7 +139,7 @@ F src/os_unix.c f33b69d8a85372b270fe37ee664a4c2140a5217d F src/os_win.c 04033a86a39f49cb8e348f515eb0116aa9d36678 F src/pager.c 44eba010e81dcc9b772401b90d6a1c61ec24345b F src/pager.h 9c1917be28fff58118e1fe0ddbc7adfb8dd4f44d -F src/parse.y c69312dff9285f6638b209189d4166dd3a985c81 +F src/parse.y d8edf095cd9f1a0083e6ff6b964396870523dc0d F src/pcache.c f8d7beceba164a34441ac37e88abb3a404f968a7 F src/pcache.h 28d9ce2d66909db1f01652586450b62b64793093 F src/pragma.c 0b1c2d2a241dd79a7361bbeb8ff575a9e9d7cd71 @@ -147,11 +147,11 @@ F src/prepare.c c7e00ed1b0bdcf699b1aad651247d4dc3d281b0b F src/printf.c 785f87120589c1db672e37c6eb1087c456e6f84d F src/random.c 11bbdf7def3746a762fbdb56c9d04648135ad6d8 F src/resolve.c 1971ff6996f1a73303339acf7f1de8497546045d -F src/select.c 75d4ffe971e25831125ea165c0c580df00f4c403 +F src/select.c 34f32ee034289d72aa459fef40565bb74c76af24 F src/shell.c d83b578a8ccdd3e0e7fef4388a0887ce9f810967 F src/sqlite.h.in ea235b37a691b32e7941baa70fb0afaf6377dbb4 F src/sqlite3ext.h 1e3887c9bd3ae66cb599e922824b04cd0d0f2c3e -F src/sqliteInt.h 1806947aac72ba3ea6de3b9de5c66e3d257e8fe7 +F src/sqliteInt.h 6c08ddc70e1c9b0581778543af6985342c5516f0 F src/sqliteLimit.h f435e728c6b620ef7312814d660a81f9356eb5c8 F src/status.c 237b193efae0cf6ac3f0817a208de6c6c6ef6d76 F src/table.c 22744786199c9195720c15a7a42cb97b2e2728d8 @@ -372,7 +372,7 @@ F test/incrvacuum_ioerr.test 57d2f5777ab13fa03b87b262a4ea1bad5cfc0291 F test/index.test cbf301cdb2da43e4eac636c3400c2439af1834ad F test/index2.test ee83c6b5e3173a3d7137140d945d9a5d4fdfb9d6 F test/index3.test 727d55dceb9a4ec36675057bb5becfc265e28ca6 -F test/indexedby.test 5e54a6b25ac619502a4960c1bf7ca93bb23da5df +F test/indexedby.test 03af52375e50d146e78f56442b6677d2932c4963 F test/insert.test aef273dd1cee84cc92407469e6bd1b3cdcb76908 F test/insert2.test 4f3a04d168c728ed5ec2c88842e772606c7ce435 F test/insert3.test 9a4ef3526fd3cca8b05278020ec3100448b4c677 @@ -640,7 +640,7 @@ F tool/speedtest16.c c8a9c793df96db7e4933f0852abb7a03d48f2e81 F tool/speedtest2.tcl ee2149167303ba8e95af97873c575c3e0fab58ff F tool/speedtest8.c 1dbced29de5f59ba2ebf877edcadf171540374d1 F tool/speedtest8inst1.c 293327bc76823f473684d589a8160bde1f52c14e -P 24891902fa625445aa47ae930f0cb340cc73d25d -R 078d46958e9975c6cde4a1174680baa8 -U drh -Z 32ce6a1b9f1606eebd07bae951617e0e +P c3cf54a1b6bbaaeab4cab701c6cbbed02695facc +R ca83b644149fe533cdd795f52e48a148 +U danielk1977 +Z c3b964ea90e3130652cb73cc4202d059 diff --git a/manifest.uuid b/manifest.uuid index b67602f149..2d94ee7748 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -c3cf54a1b6bbaaeab4cab701c6cbbed02695facc \ No newline at end of file +83a7e446b2d4846a6f92bd831a2adaa265f5a786 \ No newline at end of file diff --git a/src/build.c b/src/build.c index 2ed5d8692b..220f2566e1 100644 --- a/src/build.c +++ b/src/build.c @@ -22,7 +22,7 @@ ** COMMIT ** ROLLBACK ** -** $Id: build.c,v 1.497 2008/10/06 05:32:19 danielk1977 Exp $ +** $Id: build.c,v 1.498 2008/10/06 16:18:40 danielk1977 Exp $ */ #include "sqliteInt.h" #include @@ -3116,7 +3116,6 @@ SrcList *sqlite3SrcListAppendFromTerm( Token *pTable, /* Name of the table to add to the FROM clause */ Token *pDatabase, /* Name of the database containing pTable */ Token *pAlias, /* The right-hand side of the AS subexpression */ - Token *pIndexedBy, /* Token from INDEXED BY clause, if any */ Select *pSubquery, /* A subquery used in place of a table name */ Expr *pOn, /* The ON clause of a join */ IdList *pUsing /* The USING clause of a join */ @@ -3134,19 +3133,30 @@ SrcList *sqlite3SrcListAppendFromTerm( if( pAlias && pAlias->n ){ pItem->zAlias = sqlite3NameFromToken(db, pAlias); } - if( pIndexedBy && pIndexedBy->n==1 && !pIndexedBy->z ){ - /* A "NOT INDEXED" clause was supplied. See parse.y - ** construct "indexed_opt" for details. */ - pItem->notIndexed = 1; - }else{ - pItem->zIndex = sqlite3NameFromToken(db, pIndexedBy); - } pItem->pSelect = pSubquery; pItem->pOn = pOn; pItem->pUsing = pUsing; return p; } +/* +** Add an INDEXED BY or NOT INDEXED clause to the most recently added +** element of the source-list passed as the second argument. +*/ +void sqlite3SrcListIndexedBy(Parse *pParse, SrcList *p, Token *pIndexedBy){ + if( pIndexedBy && p && p->nSrc>0 ){ + struct SrcList_item *pItem = &p->a[p->nSrc-1]; + assert( pItem->notIndexed==0 && pItem->zIndex==0 ); + if( pIndexedBy->n==1 && !pIndexedBy->z ){ + /* A "NOT INDEXED" clause was supplied. See parse.y + ** construct "indexed_opt" for details. */ + pItem->notIndexed = 1; + }else{ + pItem->zIndex = sqlite3NameFromToken(pParse->db, pIndexedBy); + } + } +} + /* ** When building up a FROM clause in the parser, the join operator ** is initially attached to the left operand. But the code generator diff --git a/src/delete.c b/src/delete.c index a9a887ec0e..e125fe6238 100644 --- a/src/delete.c +++ b/src/delete.c @@ -12,7 +12,7 @@ ** This file contains C code routines that are called by the parser ** in order to generate code for DELETE FROM statements. ** -** $Id: delete.c,v 1.176 2008/10/06 05:32:19 danielk1977 Exp $ +** $Id: delete.c,v 1.177 2008/10/06 16:18:40 danielk1977 Exp $ */ #include "sqliteInt.h" @@ -22,16 +22,17 @@ ** are found, return a pointer to the last table. */ Table *sqlite3SrcListLookup(Parse *pParse, SrcList *pSrc){ - Table *pTab = 0; - int i; - struct SrcList_item *pItem; - for(i=0, pItem=pSrc->a; inSrc; i++, pItem++){ - pTab = sqlite3LocateTable(pParse, 0, pItem->zName, pItem->zDatabase); - sqlite3DeleteTable(pItem->pTab); - pItem->pTab = pTab; - if( pTab ){ - pTab->nRef++; - } + struct SrcList_item *pItem = pSrc->a; + Table *pTab; + assert( pItem && pSrc->nSrc==1 ); + pTab = sqlite3LocateTable(pParse, 0, pItem->zName, pItem->zDatabase); + sqlite3DeleteTable(pItem->pTab); + pItem->pTab = pTab; + if( pTab ){ + pTab->nRef++; + } + if( sqlite3IndexedByLookup(pParse, pItem) ){ + pTab = 0; } return pTab; } @@ -106,7 +107,7 @@ void sqlite3MaterializeView( pWhere = sqlite3ExprDup(db, pWhere); viewName.z = (u8*)pView->zName; viewName.n = (unsigned int)strlen((const char*)viewName.z); - pFrom = sqlite3SrcListAppendFromTerm(pParse, 0, 0, 0, &viewName,0,pDup,0,0); + pFrom = sqlite3SrcListAppendFromTerm(pParse, 0, 0, 0, &viewName, pDup, 0,0); pDup = sqlite3SelectNew(pParse, 0, pFrom, pWhere, 0, 0, 0, 0, 0, 0); } sqlite3SelectDestInit(&dest, SRT_EphemTab, iCur); diff --git a/src/parse.y b/src/parse.y index 4af16d422e..65d968310a 100644 --- a/src/parse.y +++ b/src/parse.y @@ -14,7 +14,7 @@ ** the parser. Lemon will also generate a header file containing ** numeric codes for all of the tokens. ** -** @(#) $Id: parse.y,v 1.253 2008/10/06 05:32:19 danielk1977 Exp $ +** @(#) $Id: parse.y,v 1.254 2008/10/06 16:18:40 danielk1977 Exp $ */ // All token codes are small integers with #defines that begin with "TK_" @@ -457,12 +457,13 @@ stl_prefix(A) ::= seltablist(X) joinop(Y). { } stl_prefix(A) ::= . {A = 0;} seltablist(A) ::= stl_prefix(X) nm(Y) dbnm(D) as(Z) indexed_opt(I) on_opt(N) using_opt(U). { - A = sqlite3SrcListAppendFromTerm(pParse,X,&Y,&D,&Z,&I,0,N,U); + A = sqlite3SrcListAppendFromTerm(pParse,X,&Y,&D,&Z,0,N,U); + sqlite3SrcListIndexedBy(pParse, A, &I); } %ifndef SQLITE_OMIT_SUBQUERY seltablist(A) ::= stl_prefix(X) LP seltablist_paren(S) RP as(Z) on_opt(N) using_opt(U). { - A = sqlite3SrcListAppendFromTerm(pParse,X,0,0,&Z,0,S,N,U); + A = sqlite3SrcListAppendFromTerm(pParse,X,0,0,&Z,S,N,U); } // A seltablist_paren nonterminal represents anything in a FROM that @@ -506,7 +507,7 @@ on_opt(N) ::= . {N = 0;} // in the token. // // If there is a "NOT INDEXED" clause, then (z==0 && n==1), which is -// normally illegal. The sqlite3SrcListAppendFromTerm() function +// normally illegal. The sqlite3SrcListIndexedBy() function // recognizes and interprets this as a special case. // %type indexed_opt {Token} @@ -577,7 +578,10 @@ limit_opt(A) ::= LIMIT expr(X) COMMA expr(Y). /////////////////////////// The DELETE statement ///////////////////////////// // -cmd ::= DELETE FROM fullname(X) where_opt(Y). {sqlite3DeleteFrom(pParse,X,Y);} +cmd ::= DELETE FROM fullname(X) indexed_opt(I) where_opt(Y). { + sqlite3SrcListIndexedBy(pParse, X, &I); + sqlite3DeleteFrom(pParse,X,Y); +} %type where_opt {Expr*} %destructor where_opt {sqlite3ExprDelete(pParse->db, $$);} @@ -587,7 +591,8 @@ where_opt(A) ::= WHERE expr(X). {A = X;} ////////////////////////// The UPDATE command //////////////////////////////// // -cmd ::= UPDATE orconf(R) fullname(X) SET setlist(Y) where_opt(Z). { +cmd ::= UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y) where_opt(Z). { + sqlite3SrcListIndexedBy(pParse, X, &I); sqlite3ExprListCheckLength(pParse,Y,"set list"); sqlite3Update(pParse,X,Y,Z,R); } diff --git a/src/select.c b/src/select.c index eebeb4cf41..9e57fd9d25 100644 --- a/src/select.c +++ b/src/select.c @@ -12,7 +12,7 @@ ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** -** $Id: select.c,v 1.477 2008/10/06 05:32:19 danielk1977 Exp $ +** $Id: select.c,v 1.478 2008/10/06 16:18:40 danielk1977 Exp $ */ #include "sqliteInt.h" @@ -2882,6 +2882,31 @@ static int minMaxQuery(Parse *pParse, Select *p){ return WHERE_ORDERBY_NORMAL; } +/* +** If the source-list item passed as an argument was augmented with an +** INDEXED BY clause, then try to locate the specified index. If there +** was such a clause and the named index cannot be found, return +** SQLITE_ERROR and leave an error in pParse. Otherwise, populate +** pFrom->pIndex and return SQLITE_OK. +*/ +int sqlite3IndexedByLookup(Parse *pParse, struct SrcList_item *pFrom){ + if( pFrom->pTab && pFrom->zIndex ){ + Table *pTab = pFrom->pTab; + char *zIndex = pFrom->zIndex; + Index *pIdx; + for(pIdx=pTab->pIndex; + pIdx && sqlite3StrICmp(pIdx->zName, zIndex); + pIdx=pIdx->pNext + ); + if( !pIdx ){ + sqlite3ErrorMsg(pParse, "no such index: %s", zIndex, 0); + return SQLITE_ERROR; + } + pFrom->pIndex = pIdx; + } + return SQLITE_OK; +} + /* ** This routine is a Walker callback for "expanding" a SELECT statement. ** "Expanding" means to do the following: @@ -2984,18 +3009,8 @@ static int selectExpander(Walker *pWalker, Select *p){ } /* Locate the index named by the INDEXED BY clause, if any. */ - if( pFrom->zIndex ){ - char *zIndex = pFrom->zIndex; - Index *pIdx; - for(pIdx=pTab->pIndex; - pIdx && sqlite3StrICmp(pIdx->zName, zIndex); - pIdx=pIdx->pNext - ); - if( !pIdx ){ - sqlite3ErrorMsg(pParse, "no such index: %s", zIndex, 0); - return WRC_Abort; - } - pFrom->pIndex = pIdx; + if( sqlite3IndexedByLookup(pParse, pFrom) ){ + return WRC_Abort; } } diff --git a/src/sqliteInt.h b/src/sqliteInt.h index 134886f4d4..ac370c94e0 100644 --- a/src/sqliteInt.h +++ b/src/sqliteInt.h @@ -11,7 +11,7 @@ ************************************************************************* ** Internal interface definitions for SQLite. ** -** @(#) $Id: sqliteInt.h,v 1.774 2008/10/06 05:32:19 danielk1977 Exp $ +** @(#) $Id: sqliteInt.h,v 1.775 2008/10/06 16:18:40 danielk1977 Exp $ */ #ifndef _SQLITEINT_H_ #define _SQLITEINT_H_ @@ -2141,8 +2141,10 @@ void *sqlite3ArrayAllocate(sqlite3*,void*,int,int,int*,int*,int*); IdList *sqlite3IdListAppend(sqlite3*, IdList*, Token*); int sqlite3IdListIndex(IdList*,const char*); SrcList *sqlite3SrcListAppend(sqlite3*, SrcList*, Token*, Token*); -SrcList *sqlite3SrcListAppendFromTerm(Parse*, SrcList*, Token*, Token*, Token*, +SrcList *sqlite3SrcListAppendFromTerm(Parse*, SrcList*, Token*, Token*, Token*, Select*, Expr*, IdList*); +void sqlite3SrcListIndexedBy(Parse *, SrcList *, Token *); +int sqlite3IndexedByLookup(Parse *, struct SrcList_item *); void sqlite3SrcListShiftJoinType(SrcList*); void sqlite3SrcListAssignCursors(Parse*, SrcList*); void sqlite3IdListDelete(sqlite3*, IdList*); diff --git a/test/indexedby.test b/test/indexedby.test index 6696462f68..85fcd2f0cf 100644 --- a/test/indexedby.test +++ b/test/indexedby.test @@ -9,7 +9,7 @@ # #*********************************************************************** # -# $Id: indexedby.test,v 1.2 2008/10/06 11:29:49 danielk1977 Exp $ +# $Id: indexedby.test,v 1.3 2008/10/06 16:18:40 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl @@ -162,5 +162,47 @@ do_test indexedby-6.2 { EQP { SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid } } {0 0 {TABLE t1 USING PRIMARY KEY ORDER BY}} +# Test that "INDEXED BY" can be used in a DELETE statement. +# +do_test indexedby-7.1 { + EQP { DELETE FROM t1 WHERE a = 5 } +} {0 0 {TABLE t1 WITH INDEX i1}} +do_test indexedby-7.2 { + EQP { DELETE FROM t1 NOT INDEXED WHERE a = 5 } +} {0 0 {TABLE t1}} +do_test indexedby-7.3 { + EQP { DELETE FROM t1 INDEXED BY i1 WHERE a = 5 } +} {0 0 {TABLE t1 WITH INDEX i1}} +do_test indexedby-7.4 { + EQP { DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10} +} {0 0 {TABLE t1 WITH INDEX i1}} +do_test indexedby-7.5 { + EQP { DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10} +} {0 0 {TABLE t1 WITH INDEX i2}} +do_test indexedby-7.6 { + catchsql { DELETE FROM t1 INDEXED BY i2 WHERE a = 5} +} {1 {cannot use index: i2}} + +# Test that "INDEXED BY" can be used in an UPDATE statement. +# +do_test indexedby-8.1 { + EQP { UPDATE t1 SET rowid=rowid+1 WHERE a = 5 } +} {0 0 {TABLE t1 WITH INDEX i1}} +do_test indexedby-8.2 { + EQP { UPDATE t1 NOT INDEXED SET rowid=rowid+1 WHERE a = 5 } +} {0 0 {TABLE t1}} +do_test indexedby-8.3 { + EQP { UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 } +} {0 0 {TABLE t1 WITH INDEX i1}} +do_test indexedby-8.4 { + EQP { UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 AND b = 10} +} {0 0 {TABLE t1 WITH INDEX i1}} +do_test indexedby-8.5 { + EQP { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5 AND b = 10} +} {0 0 {TABLE t1 WITH INDEX i2}} +do_test indexedby-8.6 { + catchsql { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5} +} {1 {cannot use index: i2}} + finish_test -- 2.47.2