Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Further simplifications of the code for the LIMIT clause on an UPDATE or DELETE. Added a few test cases to wherelimit.test. (CVS 5797) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
282c6a46b25f4e4278fd4c8b0b1cde1d |
User & Date: | shane 2008-10-10 18:25:46.000 |
Context
2008-10-10
| ||
23:48 | Add the SQLITE_OMIT_TRUNCATE_OPTIMIZATION option. Other unrelated documentation enhancements. (CVS 5798) (check-in: fab4940d54 user: drh tags: trunk) | |
18:25 | Further simplifications of the code for the LIMIT clause on an UPDATE or DELETE. Added a few test cases to wherelimit.test. (CVS 5797) (check-in: 282c6a46b2 user: shane tags: trunk) | |
17:58 | Add the genfkey program to the tool/ directory. (CVS 5796) (check-in: 84e73fe8a6 user: danielk1977 tags: trunk) | |
Changes
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 | ** 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.181 2008/10/10 18:25:46 shane 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. |
︙ | ︙ | |||
122 123 124 125 126 127 128 | ** and LIMIT/OFFSET portion of DELETE and UPDATE statements. ** ** DELETE FROM table_wxyz WHERE a<5 ORDER BY a LIMIT 1; ** \__________________________/ ** pLimitWhere (pInClause) */ Expr *sqlite3LimitWhere( | | | | | | | > | | | | | | > > > > > > > > > > > | > | | | > | | | > > > > > > > | 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 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 209 210 211 212 213 214 | ** and LIMIT/OFFSET portion of DELETE and UPDATE statements. ** ** DELETE FROM table_wxyz WHERE a<5 ORDER BY a LIMIT 1; ** \__________________________/ ** pLimitWhere (pInClause) */ Expr *sqlite3LimitWhere( Parse *pParse, /* The parser context */ SrcList *pSrc, /* the FROM clause -- which tables to scan */ Expr *pWhere, /* The WHERE clause. May be null */ ExprList *pOrderBy, /* The ORDER BY clause. May be null */ Expr *pLimit, /* The LIMIT clause. May be null */ Expr *pOffset, /* The OFFSET clause. May be null */ char *zStmtType /* Either DELETE or UPDATE. For error messages. */ ){ Expr *pWhereRowid = NULL; /* WHERE rowid .. */ Expr *pInClause = NULL; /* WHERE rowid IN ( select ) */ Expr *pSelectRowid = NULL; /* SELECT rowid ... */ ExprList *pEList = NULL; /* Expression list contaning only pSelectRowid */ SrcList *pSelectSrc = NULL; /* SELECT rowid FROM x ... (dup of pSrc) */ Select *pSelect = NULL; /* Complete SELECT tree */ /* Check that there isn't an ORDER BY without a LIMIT clause. */ if( pOrderBy && (pLimit == 0) ) { sqlite3ErrorMsg(pParse, "ORDER BY without LIMIT on %s", zStmtType); pParse->parseError = 1; goto limit_where_cleanup_2; } /* We only need to generate a select expression if there ** is a limit/offset term to enforce. */ if( pLimit == 0 ) { /* if pLimit is null, pOffset will always be null as well. */ assert( pOffset == 0 ); return pWhere; } /* Generate a select expression tree to enforce the limit/offset ** term for the DELETE or UPDATE statement. For example: ** DELETE FROM table_a WHERE col1=1 ORDER BY col2 LIMIT 1 OFFSET 1 ** becomes: ** DELETE FROM table_a WHERE rowid IN ( ** SELECT rowid FROM table_a WHERE col1=1 ORDER BY col2 LIMIT 1 OFFSET 1 ** ); */ pSelectRowid = sqlite3Expr(pParse->db, TK_ROW, 0, 0, 0); if( pSelectRowid == 0 ) goto limit_where_cleanup_2; pEList = sqlite3ExprListAppend(pParse, 0, pSelectRowid, 0); if( pEList == 0 ) goto limit_where_cleanup_2; /* duplicate the FROM clause as it is needed by both the DELETE/UPDATE tree ** and the SELECT subtree. */ pSelectSrc = sqlite3SrcListDup(pParse->db, pSrc); if( pSelectSrc == 0 ) { sqlite3ExprListDelete(pParse->db, pEList); goto limit_where_cleanup_2; } /* generate the SELECT expression tree. */ pSelect = sqlite3SelectNew(pParse,pEList,pSelectSrc,pWhere,0,0,pOrderBy,0,pLimit,pOffset); if( pSelect == 0 ) return 0; /* now generate the new WHERE rowid IN clause for the DELETE/UDPATE */ pWhereRowid = sqlite3Expr(pParse->db, TK_ROW, 0, 0, 0); if( pWhereRowid == 0 ) goto limit_where_cleanup_1; pInClause = sqlite3PExpr(pParse, TK_IN, pWhereRowid, 0, 0); if( pInClause == 0 ) goto limit_where_cleanup_1; pInClause->pSelect = pSelect; sqlite3ExprSetHeight(pParse, pInClause); return pInClause; /* something went wrong. clean up anything allocated. */ limit_where_cleanup_1: sqlite3SelectDelete(pParse->db, pSelect); return 0; limit_where_cleanup_2: sqlite3ExprDelete(pParse->db, pWhere); sqlite3ExprListDelete(pParse->db, pOrderBy); sqlite3ExprDelete(pParse->db, pLimit); sqlite3ExprDelete(pParse->db, pOffset); return 0; } #endif /* defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY) */ /* ** Generate code for a DELETE FROM statement. ** ** DELETE FROM table_wxyz WHERE a<5 AND b NOT NULL; |
︙ | ︙ |
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.259 2008/10/10 18:25:46 shane 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. |
︙ | ︙ | |||
578 579 580 581 582 583 584 | /////////////////////////// The DELETE statement ///////////////////////////// // %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT cmd ::= DELETE FROM fullname(X) indexed_opt(I) where_opt(W) orderby_opt(O) limit_opt(L). { sqlite3SrcListIndexedBy(pParse, X, &I); | < < < < | | < | 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 | /////////////////////////// The DELETE statement ///////////////////////////// // %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT cmd ::= DELETE FROM fullname(X) indexed_opt(I) where_opt(W) orderby_opt(O) limit_opt(L). { sqlite3SrcListIndexedBy(pParse, X, &I); W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "DELETE"); sqlite3DeleteFrom(pParse,X,W); } %endif %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT cmd ::= DELETE FROM fullname(X) indexed_opt(I) where_opt(W). { sqlite3SrcListIndexedBy(pParse, X, &I); sqlite3DeleteFrom(pParse,X,W); } |
︙ | ︙ | |||
606 607 608 609 610 611 612 | ////////////////////////// The UPDATE command //////////////////////////////// // %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT cmd ::= UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y) where_opt(W) orderby_opt(O) limit_opt(L). { sqlite3SrcListIndexedBy(pParse, X, &I); sqlite3ExprListCheckLength(pParse,Y,"set list"); | < < < < | | < | 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 | ////////////////////////// The UPDATE command //////////////////////////////// // %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT cmd ::= UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y) where_opt(W) orderby_opt(O) limit_opt(L). { sqlite3SrcListIndexedBy(pParse, X, &I); sqlite3ExprListCheckLength(pParse,Y,"set list"); W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "UPDATE"); sqlite3Update(pParse,X,Y,W,R); } %endif %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT cmd ::= UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y) where_opt(W). { sqlite3SrcListIndexedBy(pParse, X, &I); sqlite3ExprListCheckLength(pParse,Y,"set list"); sqlite3Update(pParse,X,Y,W,R); |
︙ | ︙ |
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.781 2008/10/10 18:25:46 shane Exp $ */ #ifndef _SQLITEINT_H_ #define _SQLITEINT_H_ /* ** Include the configuration header output by 'configure' if we're using the ** autoconf-based build |
︙ | ︙ | |||
2158 2159 2160 2161 2162 2163 2164 | Select *sqlite3SelectNew(Parse*,ExprList*,SrcList*,Expr*,ExprList*, Expr*,ExprList*,int,Expr*,Expr*); void sqlite3SelectDelete(sqlite3*, Select*); Table *sqlite3SrcListLookup(Parse*, SrcList*); int sqlite3IsReadOnly(Parse*, Table*, int); void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int); #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY) | | | 2158 2159 2160 2161 2162 2163 2164 2165 2166 2167 2168 2169 2170 2171 2172 | Select *sqlite3SelectNew(Parse*,ExprList*,SrcList*,Expr*,ExprList*, Expr*,ExprList*,int,Expr*,Expr*); void sqlite3SelectDelete(sqlite3*, Select*); Table *sqlite3SrcListLookup(Parse*, SrcList*); int sqlite3IsReadOnly(Parse*, Table*, int); void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int); #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY) Expr *sqlite3LimitWhere(Parse *, SrcList *, Expr *, ExprList *, Expr *, Expr *, char *); #endif void sqlite3DeleteFrom(Parse*, SrcList*, Expr*); void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int); WhereInfo *sqlite3WhereBegin(Parse*, SrcList*, Expr*, ExprList**, u8); void sqlite3WhereEnd(WhereInfo*); int sqlite3ExprCodeGetColumn(Parse*, Table*, int, int, int, int); void sqlite3ExprCodeMove(Parse*, int, int, int); |
︙ | ︙ |
Changes to test/wherelimit.test.
︙ | ︙ | |||
8 9 10 11 12 13 14 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the LIMIT ... OFFSET ... clause # of UPDATE and DELETE statements. # | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the LIMIT ... OFFSET ... clause # of UPDATE and DELETE statements. # # $Id: wherelimit.test,v 1.2 2008/10/10 18:25:46 shane Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl proc create_test_data {size} { # Build some test data # |
︙ | ︙ | |||
52 53 54 55 56 57 58 59 60 61 62 63 64 65 | # no AS on table sources do_test wherelimit-0.4 { catchsql {DELETE FROM t1 AS a WHERE x=1} } {1 {near "AS": syntax error}} do_test wherelimit-0.5 { catchsql {UPDATE t1 AS a SET y=1 WHERE x=1} } {1 {near "AS": syntax error}} # check deletes w/o where clauses but with limit/offsets create_test_data 5 do_test wherelimit-1.0 { execsql {SELECT count(*) FROM t1} } {25} do_test wherelimit-1.1 { | > > > > > > > > > | 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 | # no AS on table sources do_test wherelimit-0.4 { catchsql {DELETE FROM t1 AS a WHERE x=1} } {1 {near "AS": syntax error}} do_test wherelimit-0.5 { catchsql {UPDATE t1 AS a SET y=1 WHERE x=1} } {1 {near "AS": syntax error}} # OFFSET w/o LIMIT do_test wherelimit-0.6 { catchsql {DELETE FROM t1 WHERE x=1 OFFSET 2} } {1 {near "OFFSET": syntax error}} do_test wherelimit-0.7 { catchsql {UPDATE t1 SET y=1 WHERE x=1 OFFSET 2} } {1 {near "OFFSET": syntax error}} # check deletes w/o where clauses but with limit/offsets create_test_data 5 do_test wherelimit-1.0 { execsql {SELECT count(*) FROM t1} } {25} do_test wherelimit-1.1 { |
︙ | ︙ |