Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix a problem with renaming a column in a table that has a temp trigger that references another attached database. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | alter-table-rename-column |
Files: | files | file ages | folders |
SHA3-256: |
336b8a0923bf65b7a2c35811cb6dec0e |
User & Date: | dan 2018-09-01 20:23:28.072 |
Context
2018-09-01
| ||
20:38 | Add another test case to altertab.test. (check-in: 828e8849fa user: dan tags: alter-table-rename-column) | |
20:23 | Fix a problem with renaming a column in a table that has a temp trigger that references another attached database. (check-in: 336b8a0923 user: dan tags: alter-table-rename-column) | |
20:02 | Fixes for harmless compiler warnings. (check-in: 41b8f38b97 user: drh tags: alter-table-rename-column) | |
Changes
Changes to src/alter.c.
︙ | ︙ | |||
557 558 559 560 561 562 563 | */ zNew = sqlite3NameFromToken(db, pNew); if( !zNew ) goto exit_rename_column; assert( pNew->n>0 ); bQuote = sqlite3Isquote(pNew->z[0]); sqlite3NestedParse(pParse, "UPDATE \"%w\".%s SET " | | | | | 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 | */ zNew = sqlite3NameFromToken(db, pNew); if( !zNew ) goto exit_rename_column; assert( pNew->n>0 ); bQuote = sqlite3Isquote(pNew->z[0]); sqlite3NestedParse(pParse, "UPDATE \"%w\".%s SET " "sql = sqlite_rename_column(sql, type, name, %Q, %Q, %d, %Q, %d, %d) " "WHERE name NOT LIKE 'sqlite_%%' AND (type != 'index' OR tbl_name = %Q)" " AND sql NOT LIKE 'create virtual%%'", zDb, MASTER_NAME, zDb, pTab->zName, iCol, zNew, bQuote, iSchema==1, pTab->zName ); sqlite3NestedParse(pParse, "UPDATE temp.%s SET " "sql = sqlite_rename_column(sql, type, name, %Q, %Q, %d, %Q, %d, 1) " "WHERE type IN ('trigger', 'view')", MASTER_NAME, zDb, pTab->zName, iCol, zNew, bQuote ); /* Drop and reload the database schema. */ renameReloadSchema(pParse, iSchema); |
︙ | ︙ | |||
1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 | ** 1. type: Type of object ("table", "view" etc.) ** 2. object: Name of object ** 3. Database: Database name (e.g. "main") ** 4. Table: Table name ** 5. iCol: Index of column to rename ** 6. zNew: New column name ** 7. bQuote: Non-zero if the new column name should be quoted. ** ** Do a column rename operation on the CREATE statement given in zSql. ** The iCol-th column (left-most is 0) of table zTable is renamed from zCol ** into zNew. The name should be quoted if bQuote is true. ** ** This function is used internally by the ALTER TABLE RENAME COLUMN command. ** Though accessible to application code, it is not intended for use by | > | 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 | ** 1. type: Type of object ("table", "view" etc.) ** 2. object: Name of object ** 3. Database: Database name (e.g. "main") ** 4. Table: Table name ** 5. iCol: Index of column to rename ** 6. zNew: New column name ** 7. bQuote: Non-zero if the new column name should be quoted. ** 8. bTemp: True if zSql comes from temp schema ** ** Do a column rename operation on the CREATE statement given in zSql. ** The iCol-th column (left-most is 0) of table zTable is renamed from zCol ** into zNew. The name should be quoted if bQuote is true. ** ** This function is used internally by the ALTER TABLE RENAME COLUMN command. ** Though accessible to application code, it is not intended for use by |
︙ | ︙ | |||
1101 1102 1103 1104 1105 1106 1107 1108 | RenameCtx sCtx; const char *zSql = (const char*)sqlite3_value_text(argv[0]); const char *zDb = (const char*)sqlite3_value_text(argv[3]); const char *zTable = (const char*)sqlite3_value_text(argv[4]); int iCol = sqlite3_value_int(argv[5]); const char *zNew = (const char*)sqlite3_value_text(argv[6]); int bQuote = sqlite3_value_int(argv[7]); const char *zOld; | > < | 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 | RenameCtx sCtx; const char *zSql = (const char*)sqlite3_value_text(argv[0]); const char *zDb = (const char*)sqlite3_value_text(argv[3]); const char *zTable = (const char*)sqlite3_value_text(argv[4]); int iCol = sqlite3_value_int(argv[5]); const char *zNew = (const char*)sqlite3_value_text(argv[6]); int bQuote = sqlite3_value_int(argv[7]); int bTemp = sqlite3_value_int(argv[8]); const char *zOld; int rc; Parse sParse; Walker sWalker; Index *pIdx; int i; Table *pTab; #ifndef SQLITE_OMIT_AUTHORIZATION |
︙ | ︙ | |||
1357 1358 1359 1360 1361 1362 1363 | else if( sParse.pNewIndex ){ renameTokenFind(&sParse, &sCtx, sParse.pNewIndex->zName); sqlite3WalkExpr(&sWalker, sParse.pNewIndex->pPartIdxWhere); } #ifndef SQLITE_OMIT_TRIGGER | | | 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 | else if( sParse.pNewIndex ){ renameTokenFind(&sParse, &sCtx, sParse.pNewIndex->zName); sqlite3WalkExpr(&sWalker, sParse.pNewIndex->pPartIdxWhere); } #ifndef SQLITE_OMIT_TRIGGER else{ Trigger *pTrigger = sParse.pNewTrigger; TriggerStep *pStep; if( 0==sqlite3_stricmp(sParse.pNewTrigger->table, zOld) && sCtx.pTab->pSchema==pTrigger->pTabSchema ){ renameTokenFind(&sParse, &sCtx, sParse.pNewTrigger->table); } |
︙ | ︙ | |||
1447 1448 1449 1450 1451 1452 1453 | } /* ** Register built-in functions used to help implement ALTER TABLE */ void sqlite3AlterFunctions(void){ static FuncDef aAlterTableFuncs[] = { | | | 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 | } /* ** Register built-in functions used to help implement ALTER TABLE */ void sqlite3AlterFunctions(void){ static FuncDef aAlterTableFuncs[] = { FUNCTION(sqlite_rename_column, 9, 0, 0, renameColumnFunc), FUNCTION(sqlite_rename_table, 5, 0, 0, renameTableFunc), FUNCTION(sqlite_rename_test, 5, 0, 0, renameTableTest), }; sqlite3InsertBuiltinFuncs(aAlterTableFuncs, ArraySize(aAlterTableFuncs)); } #endif /* SQLITE_ALTER_TABLE */ |
Changes to src/vdbeaux.c.
︙ | ︙ | |||
1658 1659 1660 1661 1662 1663 1664 | ** to 6.6 percent. The test case is inserting 1000 rows into a table ** with no indexes using a single prepared INSERT statement, bind() ** and reset(). Inserts are grouped into a transaction. */ testcase( p->flags & MEM_Agg ); testcase( p->flags & MEM_Dyn ); testcase( p->xDel==sqlite3VdbeFrameMemDel ); | < | 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 | ** to 6.6 percent. The test case is inserting 1000 rows into a table ** with no indexes using a single prepared INSERT statement, bind() ** and reset(). Inserts are grouped into a transaction. */ testcase( p->flags & MEM_Agg ); testcase( p->flags & MEM_Dyn ); testcase( p->xDel==sqlite3VdbeFrameMemDel ); if( p->flags&(MEM_Agg|MEM_Dyn) ){ sqlite3VdbeMemRelease(p); }else if( p->szMalloc ){ sqlite3DbFreeNN(db, p->zMalloc); p->szMalloc = 0; } |
︙ | ︙ |
Changes to test/alter.test.
︙ | ︙ | |||
681 682 683 684 685 686 687 | } {1 18 2 9} #-------------------------------------------------------------------------- # alter-9.X - Special test: Make sure the sqlite_rename_column() and # rename_table() functions do not crash when handed bad input. # do_test alter-9.1 { | | | 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 | } {1 18 2 9} #-------------------------------------------------------------------------- # alter-9.X - Special test: Make sure the sqlite_rename_column() and # rename_table() functions do not crash when handed bad input. # do_test alter-9.1 { execsql {SELECT SQLITE_RENAME_COLUMN(0,0,0,0,0,0,0,0,0)} } {{}} foreach {tn sql} { 1 { SELECT SQLITE_RENAME_TABLE(0,0,0,0,0) } 2 { SELECT SQLITE_RENAME_TABLE(10,20,30,40,50) } 3 { SELECT SQLITE_RENAME_TABLE('foo', 'foo', 'foo', 'foo', 'foo') } } { do_catchsql_test alter-9.2.$tn $sql {1 {SQL logic error}} |
︙ | ︙ |
Changes to test/alter4.test.
︙ | ︙ | |||
389 390 391 392 393 394 395 396 397 | CREATE INDEX t1a ON t1(a DESC); INSERT INTO t1 VALUES(1,2,3); INSERT INTO t1 VALUES(2,3,4); ALTER TABLE t1 ADD COLUMN d; PRAGMA integrity_check; } } {ok} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 389 390 391 392 393 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 | CREATE INDEX t1a ON t1(a DESC); INSERT INTO t1 VALUES(1,2,3); INSERT INTO t1 VALUES(2,3,4); ALTER TABLE t1 ADD COLUMN d; PRAGMA integrity_check; } } {ok} reset_db do_execsql_test alter4-11.0 { CREATE TABLE t1(c INTEGER PRIMARY KEY, d); PRAGMA foreign_keys = on; ALTER TABLE t1 ADD COLUMN e; } do_execsql_test alter4-11.1 { ALTER TABLE t1 ADD COLUMN f REFERENCES t1; } do_catchsql_test alter4-11.2 { ALTER TABLE t1 ADD COLUMN g REFERENCES t1 DEFAULT 4; } {1 {Cannot add a REFERENCES column with non-NULL default value}} do_catchsql_test alter4-11.3 { ALTER TABLE t2 ADD COLUMN g; } {1 {no such table: t2}} ifcapable fts5 { do_execsql_test alter4-11.4 { CREATE VIRTUAL TABLE fff USING fts5(f); } do_catchsql_test alter4-11.2 { ALTER TABLE fff ADD COLUMN g; } {1 {virtual tables may not be altered}} } finish_test |
Changes to test/altercol.test.
︙ | ︙ | |||
512 513 514 515 516 517 518 | ifcapable fts5 { do_execsql_test 12.3.1 { CREATE VIRTUAL TABLE ft USING fts5(a, b, c); } do_catchsql_test 12.3.2 { ALTER TABLE ft RENAME a TO z; | | | 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 | ifcapable fts5 { do_execsql_test 12.3.1 { CREATE VIRTUAL TABLE ft USING fts5(a, b, c); } do_catchsql_test 12.3.2 { ALTER TABLE ft RENAME a TO z; } {1 {cannot rename columns of virtual table "ft"}} } do_execsql_test 12.4.1 { CREATE TABLE t2(x, y, z); } do_catchsql_test 12.4.2 { ALTER TABLE t2 RENAME COLUMN a TO b; |
︙ | ︙ | |||
622 623 624 625 626 627 628 629 630 631 632 633 | 'table', 'x1', 'main', 'x1', -1, 'zzz', 0 ), ( 'CREATE TABLE x1(i INTEGER, t TEXT)', 'table', 'x1', 'main', 'x1', 2, 'zzz', 0 ), ( 'CREATE TABLE x1(i INTEGER, t TEXT)', 'table', 'x1', 'main', 'notable', 0, 'zzz', 0 ); } {} do_execsql_test 14.2 { SELECT | > > > | | | 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 | 'table', 'x1', 'main', 'x1', -1, 'zzz', 0 ), ( 'CREATE TABLE x1(i INTEGER, t TEXT)', 'table', 'x1', 'main', 'x1', 2, 'zzz', 0 ), ( 'CREATE TABLE x1(i INTEGER, t TEXT)', 'table', 'x1', 'main', 'notable', 0, 'zzz', 0 ), ( 'CREATE TABLE x1(i INTEGER, t TEXT)', 'table', 'x1', 'main', 'ddd', -1, 'zzz', 0 ); } {} do_execsql_test 14.2 { SELECT sqlite_rename_column(sql, type, object, db, tbl, icol, znew, bquote, 0) FROM ddd; } {{} {} {} {}} #------------------------------------------------------------------------- # reset_db do_execsql_test 15.0 { CREATE TABLE xxx(a, b, c); SELECT a AS d FROM xxx WHERE d=0; |
︙ | ︙ | |||
704 705 706 707 708 709 710 711 712 | do_execsql_test 16.2.2 { ALTER TABLE t1 RENAME COLUMN "big c" TO reallybigc; } {} do_execsql_test 16.2.3 { SELECT * FROM v5; } {3 456 20456 0} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 | do_execsql_test 16.2.2 { ALTER TABLE t1 RENAME COLUMN "big c" TO reallybigc; } {} do_execsql_test 16.2.3 { SELECT * FROM v5; } {3 456 20456 0} #------------------------------------------------------------------------- # do_execsql_test 17.0 { CREATE TABLE u7(x, y, z); CREATE TRIGGER u7t AFTER INSERT ON u7 BEGIN INSERT INTO u8 VALUES(new.x, new.y, new.z); END; } {} do_catchsql_test 17.1 { ALTER TABLE u7 RENAME x TO xxx; } {1 {error in trigger u7t: no such table: main.u8}} do_execsql_test 17.2 { CREATE TEMP TABLE uu7(x, y, z); CREATE TRIGGER uu7t AFTER INSERT ON uu7 BEGIN INSERT INTO u8 VALUES(new.x, new.y, new.z); END; } {} do_catchsql_test 17.3 { ALTER TABLE uu7 RENAME x TO xxx; } {1 {error in trigger uu7t: no such table: u8}} reset_db forcedelete test.db2 do_execsql_test 18.0 { ATTACH 'test.db2' AS aux; CREATE TABLE t1(a); CREATE TABLE aux.log(v); CREATE TEMP TRIGGER tr1 AFTER INSERT ON t1 BEGIN INSERT INTO log VALUES(new.a); END; INSERT INTO t1 VALUES(111); SELECT v FROM log; } {111} do_execsql_test 18.1 { ALTER TABLE t1 RENAME a TO b; } finish_test |
Changes to test/altertab.test.
︙ | ︙ | |||
200 201 202 203 204 205 206 207 208 209 210 | CREATE VIEW temp.vv AS SELECT one.a, one.b, t2.a, t2.b FROM t1 AS one, t2; SELECT * FROM vv; } {1 2 3 4} do_catchsql_test 5.6 { ALTER TABLE t2 RENAME TO one; } {1 {error in view vv after rename: ambiguous column name: one.a}} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 200 201 202 203 204 205 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 243 244 245 246 247 248 | CREATE VIEW temp.vv AS SELECT one.a, one.b, t2.a, t2.b FROM t1 AS one, t2; SELECT * FROM vv; } {1 2 3 4} do_catchsql_test 5.6 { ALTER TABLE t2 RENAME TO one; } {1 {error in view vv after rename: ambiguous column name: one.a}} #------------------------------------------------------------------------- register_tcl_module db proc tcl_command {method args} { switch -- $method { xConnect { return "CREATE TABLE t1(a, b, c)" } } return {} } do_execsql_test 6.0 { CREATE VIRTUAL TABLE x1 USING tcl(tcl_command); } do_execsql_test 6.1 { ALTER TABLE x1 RENAME TO x2; SELECT sql FROM sqlite_master WHERE name = 'x2' } {{CREATE VIRTUAL TABLE "x2" USING tcl(tcl_command)}} do_execsql_test 7.1 { CREATE TABLE ddd(db, sql, zOld, zNew, bTemp); INSERT INTO ddd VALUES( 'main', 'CREATE TABLE x1(i INTEGER, t TEXT)', 'ddd', NULL, 0 ), ( 'main', 'CREATE TABLE x1(i INTEGER, t TEXT)', NULL, 'eee', 0 ), ( 'main', NULL, 'ddd', 'eee', 0 ); } {} do_execsql_test 7.2 { SELECT sqlite_rename_table(db, sql, zOld, zNew, bTemp) FROM ddd; } {{} {} {}} finish_test |