Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Allow INDEXED BY and NOT INDEXED to be used in UPDATE and DELETE statements. (CVS 5772) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
83a7e446b2d4846a6f92bd831a2adaa2 |
User & Date: | danielk1977 2008-10-06 16:18:40.000 |
Context
2008-10-07
| ||
01:18 | Removed redundant assert(). (CVS 5773) (check-in: 486b1124f7 user: shane tags: trunk) | |
2008-10-06
| ||
16:18 | Allow INDEXED BY and NOT INDEXED to be used in UPDATE and DELETE statements. (CVS 5772) (check-in: 83a7e446b2 user: danielk1977 tags: trunk) | |
15:31 | Add a test script to verify that ticket #3419 stays fixed. (CVS 5771) (check-in: c3cf54a1b6 user: drh tags: trunk) | |
Changes
Changes to src/build.c.
︙ | ︙ | |||
18 19 20 21 22 23 24 | ** CREATE INDEX ** DROP INDEX ** creating ID lists ** BEGIN TRANSACTION ** COMMIT ** ROLLBACK ** | | | 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | ** CREATE INDEX ** DROP INDEX ** creating ID lists ** BEGIN TRANSACTION ** COMMIT ** ROLLBACK ** ** $Id: build.c,v 1.498 2008/10/06 16:18:40 danielk1977 Exp $ */ #include "sqliteInt.h" #include <ctype.h> /* ** This routine is called when a new SQL statement is beginning to ** be parsed. Initialize the pParse structure as needed. |
︙ | ︙ | |||
3112 3113 3114 3115 3116 3117 3118 | */ SrcList *sqlite3SrcListAppendFromTerm( Parse *pParse, /* Parsing context */ SrcList *p, /* The left part of the FROM clause already seen */ 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 */ | < < < < < < < < > > > > > > > > > > > > > > > > > > | 3112 3113 3114 3115 3116 3117 3118 3119 3120 3121 3122 3123 3124 3125 3126 3127 3128 3129 3130 3131 3132 3133 3134 3135 3136 3137 3138 3139 3140 3141 3142 3143 3144 3145 3146 3147 3148 3149 3150 3151 3152 3153 3154 3155 3156 3157 3158 3159 3160 3161 3162 3163 3164 3165 | */ SrcList *sqlite3SrcListAppendFromTerm( Parse *pParse, /* Parsing context */ SrcList *p, /* The left part of the FROM clause already seen */ 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 */ 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 */ ){ struct SrcList_item *pItem; sqlite3 *db = pParse->db; p = sqlite3SrcListAppend(db, p, pTable, pDatabase); if( p==0 || p->nSrc==0 ){ sqlite3ExprDelete(db, pOn); sqlite3IdListDelete(db, pUsing); sqlite3SelectDelete(db, pSubquery); return p; } pItem = &p->a[p->nSrc-1]; if( pAlias && pAlias->n ){ pItem->zAlias = sqlite3NameFromToken(db, pAlias); } 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 ** expects the join operator to be on the right operand. This routine ** Shifts all join operators from left to right for an entire FROM ** clause. |
︙ | ︙ |
Changes to src/delete.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** in order to generate code for DELETE FROM statements. ** | | < < | > | | | | | | | > > | 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 34 35 36 37 38 39 40 41 42 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** 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.177 2008/10/06 16:18:40 danielk1977 Exp $ */ #include "sqliteInt.h" /* ** Look up every table that is named in pSrc. If any table is not found, ** add an error message to pParse->zErrMsg and return NULL. If all tables ** are found, return a pointer to the last table. */ Table *sqlite3SrcListLookup(Parse *pParse, SrcList *pSrc){ 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; } /* ** Check to make sure the given table is writable. If it is not ** writable, generate an error message and return 1. If it is |
︙ | ︙ | |||
102 103 104 105 106 107 108 | if( pWhere ){ SrcList *pFrom; Token viewName; pWhere = sqlite3ExprDup(db, pWhere); viewName.z = (u8*)pView->zName; viewName.n = (unsigned int)strlen((const char*)viewName.z); | | | 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 | if( pWhere ){ SrcList *pFrom; Token viewName; 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, pDup, 0,0); pDup = sqlite3SelectNew(pParse, 0, pFrom, pWhere, 0, 0, 0, 0, 0, 0); } sqlite3SelectDestInit(&dest, SRT_EphemTab, iCur); sqlite3Select(pParse, pDup, &dest); sqlite3SelectDelete(db, pDup); } #endif /* !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) */ |
︙ | ︙ |
Changes to src/parse.y.
︙ | ︙ | |||
10 11 12 13 14 15 16 | ** ************************************************************************* ** This file contains SQLite's grammar for SQL. Process this file ** using the lemon parser generator to generate C code that runs ** the parser. Lemon will also generate a header file containing ** numeric codes for all of the tokens. ** | | | 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | ** ************************************************************************* ** This file contains SQLite's grammar for SQL. Process this file ** using the lemon parser generator to generate C code that runs ** the parser. Lemon will also generate a header file containing ** numeric codes for all of the tokens. ** ** @(#) $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_" %token_prefix TK_ // The type of the data attached to each token is Token. This is also the // default type for non-terminals. |
︙ | ︙ | |||
453 454 455 456 457 458 459 | // stl_prefix(A) ::= seltablist(X) joinop(Y). { A = X; if( A && A->nSrc>0 ) A->a[A->nSrc-1].jointype = 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). { | | > | | 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 | // stl_prefix(A) ::= seltablist(X) joinop(Y). { A = X; if( A && A->nSrc>0 ) A->a[A->nSrc-1].jointype = 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,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,S,N,U); } // A seltablist_paren nonterminal represents anything in a FROM that // is contained inside parentheses. This can be either a subquery or // a grouping of table and subqueries. // %type seltablist_paren {Select*} |
︙ | ︙ | |||
502 503 504 505 506 507 508 | // Note that this block abuses the Token type just a little. If there is // no "INDEXED BY" clause, the returned token is empty (z==0 && n==0). If // there is an INDEXED BY clause, then the token is populated as per normal, // with z pointing to the token data and n containing the number of bytes // in the token. // // If there is a "NOT INDEXED" clause, then (z==0 && n==1), which is | | | 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 | // Note that this block abuses the Token type just a little. If there is // no "INDEXED BY" clause, the returned token is empty (z==0 && n==0). If // there is an INDEXED BY clause, then the token is populated as per normal, // with z pointing to the token data and n containing the number of bytes // in the token. // // If there is a "NOT INDEXED" clause, then (z==0 && n==1), which is // normally illegal. The sqlite3SrcListIndexedBy() function // recognizes and interprets this as a special case. // %type indexed_opt {Token} indexed_opt(A) ::= . {A.z=0; A.n=0;} indexed_opt(A) ::= INDEXED BY nm(X). {A = X;} indexed_opt(A) ::= NOT INDEXED. {A.z=0; A.n=1;} |
︙ | ︙ | |||
573 574 575 576 577 578 579 | limit_opt(A) ::= LIMIT expr(X) OFFSET expr(Y). {A.pLimit = X; A.pOffset = Y;} limit_opt(A) ::= LIMIT expr(X) COMMA expr(Y). {A.pOffset = X; A.pLimit = Y;} /////////////////////////// The DELETE statement ///////////////////////////// // | | > > > | > | 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 | limit_opt(A) ::= LIMIT expr(X) OFFSET expr(Y). {A.pLimit = X; A.pOffset = Y;} limit_opt(A) ::= LIMIT expr(X) COMMA expr(Y). {A.pOffset = X; A.pLimit = Y;} /////////////////////////// The DELETE statement ///////////////////////////// // 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, $$);} where_opt(A) ::= . {A = 0;} where_opt(A) ::= WHERE expr(X). {A = X;} ////////////////////////// The UPDATE command //////////////////////////////// // 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); } %type setlist {ExprList*} %destructor setlist {sqlite3ExprListDelete(pParse->db, $$);} |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** ** $Id: select.c,v 1.478 2008/10/06 16:18:40 danielk1977 Exp $ */ #include "sqliteInt.h" /* ** Delete all the content of a Select structure but do not deallocate ** the select structure itself. |
︙ | ︙ | |||
2877 2878 2879 2880 2881 2882 2883 2884 2885 2886 2887 2888 2889 2890 | if( sqlite3StrNICmp((char*)pExpr->token.z,"min",3)==0 ){ return WHERE_ORDERBY_MIN; }else if( sqlite3StrNICmp((char*)pExpr->token.z,"max",3)==0 ){ return WHERE_ORDERBY_MAX; } return WHERE_ORDERBY_NORMAL; } /* ** This routine is a Walker callback for "expanding" a SELECT statement. ** "Expanding" means to do the following: ** ** (1) Make sure VDBE cursor numbers have been assigned to every ** element of the FROM clause. | > > > > > > > > > > > > > > > > > > > > > > > > > | 2877 2878 2879 2880 2881 2882 2883 2884 2885 2886 2887 2888 2889 2890 2891 2892 2893 2894 2895 2896 2897 2898 2899 2900 2901 2902 2903 2904 2905 2906 2907 2908 2909 2910 2911 2912 2913 2914 2915 | if( sqlite3StrNICmp((char*)pExpr->token.z,"min",3)==0 ){ return WHERE_ORDERBY_MIN; }else if( sqlite3StrNICmp((char*)pExpr->token.z,"max",3)==0 ){ return WHERE_ORDERBY_MAX; } 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: ** ** (1) Make sure VDBE cursor numbers have been assigned to every ** element of the FROM clause. |
︙ | ︙ | |||
2980 2981 2982 2983 2984 2985 2986 | sqlite3WalkSelect(pWalker, pFrom->pSelect); } } #endif } /* Locate the index named by the INDEXED BY clause, if any. */ | < < < < | < < < < | < < | 3005 3006 3007 3008 3009 3010 3011 3012 3013 3014 3015 3016 3017 3018 3019 3020 | sqlite3WalkSelect(pWalker, pFrom->pSelect); } } #endif } /* Locate the index named by the INDEXED BY clause, if any. */ if( sqlite3IndexedByLookup(pParse, pFrom) ){ return WRC_Abort; } } /* Process NATURAL keywords, and ON and USING clauses of joins. */ if( db->mallocFailed || sqliteProcessJoin(pParse, p) ){ return WRC_Abort; |
︙ | ︙ |
Changes to src/sqliteInt.h.
1 2 3 4 5 6 7 8 9 10 11 12 13 | /* ** 2001 September 15 ** ** 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. ** ************************************************************************* ** Internal interface definitions for SQLite. ** | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | /* ** 2001 September 15 ** ** 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. ** ************************************************************************* ** Internal interface definitions for SQLite. ** ** @(#) $Id: sqliteInt.h,v 1.775 2008/10/06 16:18:40 danielk1977 Exp $ */ #ifndef _SQLITEINT_H_ #define _SQLITEINT_H_ /* ** Include the configuration header output by 'configure' if we're using the ** autoconf-based build |
︙ | ︙ | |||
2137 2138 2139 2140 2141 2142 2143 | void sqlite3DropTable(Parse*, SrcList*, int, int); void sqlite3DeleteTable(Table*); void sqlite3Insert(Parse*, SrcList*, ExprList*, Select*, IdList*, int); 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*); | | > > | 2137 2138 2139 2140 2141 2142 2143 2144 2145 2146 2147 2148 2149 2150 2151 2152 2153 2154 | void sqlite3DropTable(Parse*, SrcList*, int, int); void sqlite3DeleteTable(Table*); void sqlite3Insert(Parse*, SrcList*, ExprList*, Select*, IdList*, int); 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*, 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*); void sqlite3SrcListDelete(sqlite3*, SrcList*); void sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*, Token*, int, int); void sqlite3DropIndex(Parse*, SrcList*, int); |
︙ | ︙ |
Changes to test/indexedby.test.
1 2 3 4 5 6 7 8 9 10 11 | # 2008 October 4 # # 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. # #*********************************************************************** # | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | # 2008 October 4 # # 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. # #*********************************************************************** # # $Id: indexedby.test,v 1.3 2008/10/06 16:18:40 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a schema with some indexes. # do_test indexedby-1.1 { |
︙ | ︙ | |||
157 158 159 160 161 162 163 164 165 166 | # do_test indexedby-6.1 { EQP { SELECT * FROM t1 WHERE b = 10 ORDER BY rowid } } {0 0 {TABLE t1 WITH INDEX i2 ORDER BY}} 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}} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 | # do_test indexedby-6.1 { EQP { SELECT * FROM t1 WHERE b = 10 ORDER BY rowid } } {0 0 {TABLE t1 WITH INDEX i2 ORDER BY}} 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 |