Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Handle triggers correctly in ALTER TABLE. (CVS 2111) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
c61b7de107cea76b561d0d6cd90c752b |
User & Date: | danielk1977 2004-11-18 15:44:29.000 |
Context
2004-11-19
| ||
05:14 | Fix bugs in ALTER TABLE related to (a) whitespace in table defn, (b) temp triggers. (CVS 2112) (check-in: 1fd8e835a3 user: danielk1977 tags: trunk) | |
2004-11-18
| ||
15:44 | Handle triggers correctly in ALTER TABLE. (CVS 2111) (check-in: c61b7de107 user: danielk1977 tags: trunk) | |
13:49 | Speed improvement on the LIKE command. (CVS 2110) (check-in: 85d56beb74 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.280 2004/11/18 15:44:29 danielk1977 Exp $ */ #include "sqliteInt.h" #include <ctype.h> /* ** This routine is called when a new SQL statement is beginning to ** be parsed. Check to see if the schema for the database needs |
︙ | ︙ | |||
2972 2973 2974 2975 2976 2977 2978 2979 2980 2981 2982 2983 2984 2985 | } sqlite3BeginWriteOperation(pParse, 0, iDb); sqlite3ChangeCookie(db, v, iDb); /* Modify the sqlite_master table to use the new table name. */ sqlite3NestedParse(pParse, "UPDATE %Q.%s SET " "sql = sqlite_alter_table(sql, %Q), " "tbl_name = %Q, " "name = CASE " "WHEN type='table' THEN %Q " "WHEN name LIKE 'sqlite_autoindex%%' AND type='index' THEN " "'sqlite_autoindex_' || %Q || substr(name, %d+18,10) " "ELSE name END " | > > > > > > | | > > > > > > | 2972 2973 2974 2975 2976 2977 2978 2979 2980 2981 2982 2983 2984 2985 2986 2987 2988 2989 2990 2991 2992 2993 2994 2995 2996 2997 2998 2999 3000 3001 3002 3003 3004 3005 3006 3007 3008 3009 3010 3011 3012 3013 3014 | } sqlite3BeginWriteOperation(pParse, 0, iDb); sqlite3ChangeCookie(db, v, iDb); /* Modify the sqlite_master table to use the new table name. */ sqlite3NestedParse(pParse, "UPDATE %Q.%s SET " #ifdef SQLITE_OMIT_TRIGGER "sql = sqlite_alter_table(sql, %Q), " #else "sql = CASE " "WHEN type = 'trigger' THEN sqlite_alter_trigger(sql, %Q)" "ELSE sqlite_alter_table(sql, %Q) END, " #endif "tbl_name = %Q, " "name = CASE " "WHEN type='table' THEN %Q " "WHEN name LIKE 'sqlite_autoindex%%' AND type='index' THEN " "'sqlite_autoindex_' || %Q || substr(name, %d+18,10) " "ELSE name END " "WHERE tbl_name=%Q AND type IN ('table', 'index', 'trigger');", db->aDb[iDb].zName, SCHEMA_TABLE(iDb), zName, zName, zName, zName, zName, strlen(pTab->zName), pTab->zName ); /* Drop the elements of the in-memory schema that refered to the table ** renamed and load the new versions from the database. */ if( pParse->nErr==0 ){ #ifndef SQLITE_OMIT_TRIGGER Trigger *pTrig; for( pTrig=pTab->pTrigger; pTrig; pTrig=pTrig->pNext ){ sqlite3VdbeOp3(v, OP_DropTrigger, iDb, 0, pTrig->name, 0); } #endif sqlite3VdbeOp3(v, OP_DropTable, iDb, 0, pTab->zName, 0); zWhere = sqlite3MPrintf("tbl_name=%Q", zName); sqlite3VdbeOp3(v, OP_ParseSchema, iDb, 0, zWhere, P3_DYNAMIC); } sqliteFree(zName); } #endif |
Changes to src/func.c.
︙ | ︙ | |||
12 13 14 15 16 17 18 | ** This file contains the C functions that implement various SQL ** functions of SQLite. ** ** There is only one exported symbol in this file - the function ** sqliteRegisterBuildinFunctions() found at the bottom of the file. ** All other code has file scope. ** | | | 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | ** This file contains the C functions that implement various SQL ** functions of SQLite. ** ** There is only one exported symbol in this file - the function ** sqliteRegisterBuildinFunctions() found at the bottom of the file. ** All other code has file scope. ** ** $Id: func.c,v 1.90 2004/11/18 15:44:29 danielk1977 Exp $ */ #include <ctype.h> #include <math.h> #include <stdlib.h> #include <assert.h> #include "sqliteInt.h" #include "vdbeInt.h" |
︙ | ︙ | |||
572 573 574 575 576 577 578 579 580 581 582 583 584 585 | zRet = sqlite3MPrintf("%.*s%Q(%s", zPrev-zSql, zSql, zTableName, zCsr); sqlite3_result_text(context, zRet, -1, SQLITE_TRANSIENT); sqliteFree(zRet); } } #endif /* ** EXPERIMENTAL - This is not an official function. The interface may ** change. This function may disappear. Do not write code that depends ** on this function. ** ** Implementation of the QUOTE() function. This function takes a single ** argument. If the argument is numeric, the return value is the same as | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 572 573 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 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 | zRet = sqlite3MPrintf("%.*s%Q(%s", zPrev-zSql, zSql, zTableName, zCsr); sqlite3_result_text(context, zRet, -1, SQLITE_TRANSIENT); sqliteFree(zRet); } } #endif #ifndef SQLITE_OMIT_ALTERTABLE #ifndef SQLITE_OMIT_TRIGGER /* This function is used by SQL generated to implement the ALTER TABLE ** ALTER TABLE command. The first argument is the text of a CREATE TRIGGER ** statement. The second is a table name. The table name in the CREATE ** TRIGGER statement is replaced with the second argument and the result ** returned. This is analagous to altertableFunc() above, except for CREATE ** TRIGGER, not CREATE INDEX and CREATE TABLE. */ static void altertriggerFunc( sqlite3_context *context, int argc, sqlite3_value **argv ){ unsigned char const *zSql = sqlite3_value_text(argv[0]); unsigned char const *zTableName = sqlite3_value_text(argv[1]); int token; Token tname; int dist = 3; char const *zCsr = zSql; int len = 0; char *zRet; /* The principle used to locate the table name in the CREATE TRIGGER ** statement is that the table name is the first token that is immediatedly ** preceded by either TK_ON or TK_DOT and immediatedly followed by one ** of TK_WHEN, TK_BEGIN or TK_FOR. */ assert( argc==2 ); if( zSql ){ do { /* Store the token that zCsr points to in tname. */ tname.z = zCsr; tname.n = len; /* Advance zCsr to the next token. Store that token type in 'token', ** and it's length in 'len' (to be used next iteration of this loop). */ do { zCsr += len; len = sqlite3GetToken(zCsr, &token); }while( token==TK_SPACE ); assert( len>0 ); /* Variable 'dist' stores the number of tokens read since the most ** recent TK_DOT or TK_ON. This means that when a WHEN, FOR or BEGIN ** token is read and 'dist' equals 2, the condition stated above ** to be met. ** ** Note that ON cannot be a database, table or column name, so ** there is no need to worry about syntax like ** "CREATE TRIGGER ... ON ON.ON BEGIN ..." etc. */ dist++; if( token==TK_DOT || token==TK_ON ){ dist = 0; } } while( dist!=2 || (token!=TK_WHEN && token!=TK_FOR && token!=TK_BEGIN) ); /* Variable tname now contains the token that is the old table-name ** in the CREATE TRIGGER statement. */ zRet = sqlite3MPrintf("%.*s%Q%s", tname.z - zSql, zSql, zTableName, tname.z+tname.n); sqlite3_result_text(context, zRet, -1, SQLITE_TRANSIENT); sqliteFree(zRet); } } #endif /* !SQLITE_OMIT_TRIGGER */ #endif /* !SQLITE_OMIT_ALTERTABLE */ /* ** EXPERIMENTAL - This is not an official function. The interface may ** change. This function may disappear. Do not write code that depends ** on this function. ** ** Implementation of the QUOTE() function. This function takes a single ** argument. If the argument is numeric, the return value is the same as |
︙ | ︙ | |||
1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 | { "sqlite_version", 0, 0, SQLITE_UTF8, 0, versionFunc}, { "quote", 1, 0, SQLITE_UTF8, 0, quoteFunc }, { "last_insert_rowid", 0, 1, SQLITE_UTF8, 0, last_insert_rowid }, { "changes", 0, 1, SQLITE_UTF8, 0, changes }, { "total_changes", 0, 1, SQLITE_UTF8, 0, total_changes }, #ifndef SQLITE_OMIT_ALTERTABLE { "sqlite_alter_table", 2, 0, SQLITE_UTF8, 0, altertableFunc}, #endif #ifdef SQLITE_SOUNDEX { "soundex", 1, 0, SQLITE_UTF8, 0, soundexFunc}, #endif #ifdef SQLITE_TEST { "randstr", 2, 0, SQLITE_UTF8, 0, randStr }, { "test_destructor", 1, 1, SQLITE_UTF8, 0, test_destructor}, | > > > | 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 | { "sqlite_version", 0, 0, SQLITE_UTF8, 0, versionFunc}, { "quote", 1, 0, SQLITE_UTF8, 0, quoteFunc }, { "last_insert_rowid", 0, 1, SQLITE_UTF8, 0, last_insert_rowid }, { "changes", 0, 1, SQLITE_UTF8, 0, changes }, { "total_changes", 0, 1, SQLITE_UTF8, 0, total_changes }, #ifndef SQLITE_OMIT_ALTERTABLE { "sqlite_alter_table", 2, 0, SQLITE_UTF8, 0, altertableFunc}, #ifndef SQLITE_OMIT_TRIGGER { "sqlite_alter_trigger", 2, 0, SQLITE_UTF8, 0, altertriggerFunc}, #endif #endif #ifdef SQLITE_SOUNDEX { "soundex", 1, 0, SQLITE_UTF8, 0, soundexFunc}, #endif #ifdef SQLITE_TEST { "randstr", 2, 0, SQLITE_UTF8, 0, randStr }, { "test_destructor", 1, 1, SQLITE_UTF8, 0, test_destructor}, |
︙ | ︙ |
Changes to test/alter.test.
1 2 3 4 5 6 7 8 9 10 | # # The author or author's hereby grant to the public domain a non-exclusive, # fully paid-up, perpetual, license in the software and all related # intellectual property to make, have made, use, have used, reproduce, # prepare derivative works, distribute, perform and display the work. # #************************************************************************* # This file implements regression tests for SQLite library. The # focus of this script is testing the ALTER TABLE statement. # | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | # # The author or author's hereby grant to the public domain a non-exclusive, # fully paid-up, perpetual, license in the software and all related # intellectual property to make, have made, use, have used, reproduce, # prepare derivative works, distribute, perform and display the work. # #************************************************************************* # This file implements regression tests for SQLite library. The # focus of this script is testing the ALTER TABLE statement. # # $Id: alter.test,v 1.3 2004/11/18 15:44:30 danielk1977 Exp $ # set testdir [file dirname $argv0] source $testdir/tester.tcl # If SQLITE_OMIT_ALTERTABLE is defined, omit this file. ifcapable !altertable { |
︙ | ︙ | |||
213 214 215 216 217 218 219 | } {1 {there is already another table or index with this name: t3}} do_test alter-2.3 { catchsql { ALTER TABLE [<t2>] RENAME TO i3; } } {1 {there is already another table or index with this name: i3}} | > > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 | } {1 {there is already another table or index with this name: t3}} do_test alter-2.3 { catchsql { ALTER TABLE [<t2>] RENAME TO i3; } } {1 {there is already another table or index with this name: i3}} # If this compilation does not include triggers, omit the remainder # of this file. ifcapable !trigger { finish_test return } # An SQL user-function for triggers to fire, so that we know they # are working. proc trigfunc {args} { set ::TRIGGER $args } db func trigfunc trigfunc do_test alter-3.1.0 { execsql { CREATE TABLE t6(a, b, c); CREATE TRIGGER trig1 AFTER INSERT ON t6 BEGIN SELECT trigfunc('trig1', new.a, new.b, new.c); END; } } {} do_test alter-3.1.1 { execsql { INSERT INTO t6 VALUES(1, 2, 3); } set ::TRIGGER } {trig1 1 2 3} do_test alter-3.1.2 { execsql { ALTER TABLE t6 RENAME TO t7; INSERT INTO t7 VALUES(4, 5, 6); } set ::TRIGGER } {trig1 4 5 6} do_test alter-3.1.3 { execsql { DROP TRIGGER trig1; } } {} do_test alter-3.1.4 { execsql { CREATE TRIGGER trig2 AFTER INSERT ON main.t7 BEGIN SELECT trigfunc('trig2', new.a, new.b, new.c); END; INSERT INTO t7 VALUES(1, 2, 3); } set ::TRIGGER } {trig2 1 2 3} do_test alter-3.1.5 { execsql { ALTER TABLE t7 RENAME TO t8; INSERT INTO t8 VALUES(4, 5, 6); } set ::TRIGGER } {trig2 4 5 6} do_test alter-3.1.6 { execsql { DROP TRIGGER trig2; } } {} do_test alter-3.1.7 { execsql { CREATE TRIGGER trig3 AFTER INSERT ON main.'t8'BEGIN SELECT trigfunc('trig3', new.a, new.b, new.c); END; INSERT INTO t8 VALUES(1, 2, 3); } set ::TRIGGER } {trig3 1 2 3} do_test alter-3.1.8 { execsql { ALTER TABLE t8 RENAME TO t9; INSERT INTO t9 VALUES(4, 5, 6); } set ::TRIGGER } {trig3 4 5 6} # Make sure "ON" cannot be used as a database, table or column name without # quoting. Otherwise the sqlite_alter_trigger() function might not work. file delete -force test3.db file delete -force test3.db-journal do_test alter-3.2.1 { catchsql { ATTACH 'test3.db' AS ON; } } {1 {near "ON": syntax error}} do_test alter-3.2.2 { catchsql { ATTACH 'test3.db' AS 'ON'; } } {0 {}} do_test alter-3.2.3 { catchsql { CREATE TABLE ON.t1(a, b, c); } } {1 {near "ON": syntax error}} do_test alter-3.2.4 { catchsql { CREATE TABLE 'ON'.t1(a, b, c); } } {0 {}} do_test alter-3.2.4 { catchsql { CREATE TABLE 'ON'.ON(a, b, c); } } {1 {near "ON": syntax error}} do_test alter-3.2.5 { catchsql { CREATE TABLE 'ON'.'ON'(a, b, c); } } {0 {}} do_test alter-3.2.6 { catchsql { CREATE TABLE t10(a, ON, c); } } {1 {near "ON": syntax error}} do_test alter-3.2.7 { catchsql { CREATE TABLE t10(a, 'ON', c); } } {0 {}} do_test alter-3.2.8 { catchsql { CREATE TRIGGER trig4 AFTER INSERT ON ON BEGIN SELECT 1; END; } } {1 {near "ON": syntax error}} do_test alter-3.2.9 { catchsql { CREATE TRIGGER 'on'.trig4 AFTER INSERT ON 'ON' BEGIN SELECT 1; END; } } {0 {}} finish_test |