Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix bugs in ALTER TABLE related to (a) whitespace in table defn, (b) temp triggers. (CVS 2112) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
1fd8e835a3656799c23f4ef6ea1311fe |
User & Date: | danielk1977 2004-11-19 05:14:55.000 |
Context
2004-11-19
| ||
07:07 | When dropping a table, update the sqlite_sequence table first, as auto-vacuum mode may need to move sqlite_sequence when the btree table is dropped. (CVS 2113) (check-in: 0514107bff user: danielk1977 tags: trunk) | |
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) | |
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.281 2004/11/19 05:14:55 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 |
︙ | ︙ | |||
2927 2928 2929 2930 2931 2932 2933 2934 2935 2936 2937 2938 2939 2940 | int iDb; /* Database that contains the table */ char *zDb; /* Name of database iDb */ Table *pTab; /* Table being renamed */ char *zName = 0; /* NULL-terminated version of pName */ char *zWhere = 0; /* Where clause of schema elements to reparse */ sqlite3 *db = pParse->db; /* Database connection */ Vdbe *v; assert( pSrc->nSrc==1 ); pTab = sqlite3LocateTable(pParse, pSrc->a[0].zName, pSrc->a[0].zDatabase); if( !pTab ) return; iDb = pTab->iDb; zDb = db->aDb[iDb].zName; | > > > | 2927 2928 2929 2930 2931 2932 2933 2934 2935 2936 2937 2938 2939 2940 2941 2942 2943 | int iDb; /* Database that contains the table */ char *zDb; /* Name of database iDb */ Table *pTab; /* Table being renamed */ char *zName = 0; /* NULL-terminated version of pName */ char *zWhere = 0; /* Where clause of schema elements to reparse */ sqlite3 *db = pParse->db; /* Database connection */ Vdbe *v; #ifndef SQLITE_OMIT_TRIGGER char *zTempTrig = 0; /* Where clause to locate temp triggers */ #endif assert( pSrc->nSrc==1 ); pTab = sqlite3LocateTable(pParse, pSrc->a[0].zName, pSrc->a[0].zDatabase); if( !pTab ) return; iDb = pTab->iDb; zDb = db->aDb[iDb].zName; |
︙ | ︙ | |||
2989 2990 2991 2992 2993 2994 2995 2996 2997 2998 2999 3000 3001 3002 3003 | "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 ){ | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > > > > > > > | 2992 2993 2994 2995 2996 2997 2998 2999 3000 3001 3002 3003 3004 3005 3006 3007 3008 3009 3010 3011 3012 3013 3014 3015 3016 3017 3018 3019 3020 3021 3022 3023 3024 3025 3026 3027 3028 3029 3030 3031 3032 3033 3034 3035 3036 3037 3038 3039 3040 3041 3042 3043 3044 3045 3046 3047 3048 3049 3050 3051 3052 3053 3054 3055 3056 3057 3058 | "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 ); #ifndef SQLITE_OMIT_TRIGGER /* If there are TEMP triggers on this table, modify the sqlite_temp_master ** table. Don't do this if the table being ALTERed is itself located in ** the temp database. */ if( iDb!=1 ){ Trigger *pTrig; char *tmp = 0; for( pTrig=pTab->pTrigger; pTrig; pTrig=pTrig->pNext ){ if( pTrig->iDb==1 ){ if( !zTempTrig ){ zTempTrig = sqlite3MPrintf("type = 'trigger' AND name IN(%Q", pTrig->name); }else{ tmp = zTempTrig; zTempTrig = sqlite3MPrintf("%s, %Q", zTempTrig, pTrig->name); sqliteFree(tmp); } } } if( zTempTrig ){ tmp = zTempTrig; zTempTrig = sqlite3MPrintf("%s)", zTempTrig); sqliteFree(tmp); sqlite3NestedParse(pParse, "UPDATE sqlite_temp_master SET " "sql = sqlite_alter_trigger(sql, %Q), " "tbl_name = %Q " "WHERE %s;", zName, zName, zTempTrig); } } #endif /* 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 ){ assert( pTrig->iDb==iDb || pTrig->iDb==1 ); sqlite3VdbeOp3(v, OP_DropTrigger, pTrig->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); #ifndef SQLITE_OMIT_TRIGGER if( zTempTrig ){ sqlite3VdbeOp3(v, OP_ParseSchema, 1, 0, zTempTrig, P3_DYNAMIC); } }else{ sqliteFree(zTempTrig); #endif } 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.91 2004/11/19 05:14:55 danielk1977 Exp $ */ #include <ctype.h> #include <math.h> #include <stdlib.h> #include <assert.h> #include "sqliteInt.h" #include "vdbeInt.h" |
︙ | ︙ | |||
548 549 550 551 552 553 554 | ** -> 'CREATE INDEX i ON def(a, b, c)' */ static void altertableFunc( sqlite3_context *context, int argc, sqlite3_value **argv ){ | | | > > | | < < | > > > > | | < | | > > > > > > > > > | > | < | 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 | ** -> 'CREATE INDEX i ON def(a, b, c)' */ static void altertableFunc( 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; char const *zCsr = zSql; int len = 0; char *zRet; /* The principle used to locate the table name in the CREATE TABLE ** statement is that the table name is the first token that is immediatedly ** followed by a left parenthesis - TK_LP. */ 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 ); } while( token!=TK_LP ); zRet = sqlite3MPrintf("%.*s%Q%s", tname.z - zSql, zSql, zTableName, tname.z+tname.n); sqlite3_result_text(context, zRet, -1, sqlite3FreeX); } } #endif #ifndef SQLITE_OMIT_ALTERTABLE #ifndef SQLITE_OMIT_TRIGGER /* This function is used by SQL generated to implement the ALTER TABLE |
︙ | ︙ | |||
601 602 603 604 605 606 607 | 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. */ | < | 613 614 615 616 617 618 619 620 621 622 623 624 625 626 | 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. */ 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', |
︙ | ︙ | |||
637 638 639 640 641 642 643 | } 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); | | < | 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 | } 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, sqlite3FreeX); } } #endif /* !SQLITE_OMIT_TRIGGER */ #endif /* !SQLITE_OMIT_ALTERTABLE */ /* ** EXPERIMENTAL - This is not an official function. The interface may |
︙ | ︙ |
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 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | # # 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.4 2004/11/19 05:14:56 danielk1977 Exp $ # set testdir [file dirname $argv0] source $testdir/tester.tcl # If SQLITE_OMIT_ALTERTABLE is defined, omit this file. ifcapable !altertable { finish_test return } #---------------------------------------------------------------------- # Test organization: # # alter-1.1.* - alter-1.7.*: Basic tests of ALTER TABLE, including tables # with implicit and explicit indices. These tests came from an earlier # fork of SQLite that also supported ALTER TABLE. # alter-1.8.*: Tests for ALTER TABLE when the table resides in an # attached database. # alter-1.9.*: Tests for ALTER TABLE when their is whitespace between the # table name and left parenthesis token. i.e: # "CREATE TABLE abc (a, b, c);" # alter-2.*: Test error conditions and messages. # alter-3.*: Test ALTER TABLE on tables that have TRIGGERs attached to them. # # Create some tables to rename. Be sure to include some TEMP tables # and some tables with odd names. # do_test alter-1.1 { execsql { CREATE TABLE t1(a,b); |
︙ | ︙ | |||
191 192 193 194 195 196 197 198 199 200 201 202 203 204 | } {main main main} do_test alter-1.8.7 { execsql { ALTER TABLE aux.t4 RENAME TO t5; SELECT * FROM aux.t5 WHERE b = 'aux'; } } {aux aux aux} # Test error messages # do_test alter-2.1 { catchsql { ALTER TABLE none RENAME TO hi; } | > > > > > > > > > > > > > > > > > > > > > > > | 206 207 208 209 210 211 212 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 | } {main main main} do_test alter-1.8.7 { execsql { ALTER TABLE aux.t4 RENAME TO t5; SELECT * FROM aux.t5 WHERE b = 'aux'; } } {aux aux aux} do_test alter-1.9.1 { execsql { CREATE TABLE tbl1 (a, b, c); INSERT INTO tbl1 VALUES(1, 2, 3); } } {} do_test alter-1.9.2 { execsql { SELECT * FROM tbl1; } } {1 2 3} do_test alter-1.9.3 { execsql { ALTER TABLE tbl1 RENAME TO tbl2; SELECT * FROM tbl2; } } {1 2 3} do_test alter-1.9.4 { execsql { DROP TABLE tbl2; } } {} # Test error messages # do_test alter-2.1 { catchsql { ALTER TABLE none RENAME TO hi; } |
︙ | ︙ | |||
219 220 221 222 223 224 225 226 227 228 229 230 231 232 | # 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 | > > > > > > > > > > > | 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 | # If this compilation does not include triggers, omit the remainder # of this file. ifcapable !trigger { finish_test return } #----------------------------------------------------------------------- # Tests alter-3.* test ALTER TABLE on tables that have triggers. # # alter-3.1.*: ALTER TABLE with triggers. # alter-3.2.*: Test that the ON keyword cannot be used as a database, # table or column name unquoted. This is done because part of the # ALTER TABLE code (specifically the implementation of SQL function # "sqlite_alter_trigger") will break in this case. # alter-3.3.*: ALTER TABLE with TEMP triggers (todo). # # 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 |
︙ | ︙ | |||
345 346 347 348 349 350 351 | } } {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 {}} | > > > > > | > > > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 | } } {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 {}} do_test alter-3.2.10 { execsql { DROP TABLE t10; } } {} do_test alter-3.3.1 { execsql { CREATE TABLE tbl1(a, b, c); CREATE TEMP TRIGGER trig1 AFTER INSERT ON tbl1 BEGIN SELECT trigfunc('trig1', new.a, new.b, new.c); END; } } {} do_test alter-3.3.2 { execsql { INSERT INTO tbl1 VALUES('a', 'b', 'c'); } set ::TRIGGER } {trig1 a b c} do_test alter-3.3.3 { execsql { ALTER TABLE tbl1 RENAME TO tbl2; INSERT INTO tbl2 VALUES('d', 'e', 'f'); } set ::TRIGGER } {trig1 d e f} do_test alter-3.3.4 { execsql { CREATE TEMP TRIGGER trig2 AFTER UPDATE ON tbl2 BEGIN SELECT trigfunc('trig2', new.a, new.b, new.c); END; } } {} do_test alter-3.3.5 { execsql { ALTER TABLE tbl2 RENAME TO tbl3; INSERT INTO tbl3 VALUES('g', 'h', 'i'); } set ::TRIGGER } {trig1 g h i} do_test alter-3.3.6 { execsql { UPDATE tbl3 SET a = 'G' where a = 'g'; } set ::TRIGGER } {trig2 G h i} do_test alter-3.3.7 { execsql { DROP TABLE tbl3; SELECT * FROM sqlite_temp_master WHERE type = 'trigger'; } } {} finish_test |