Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Test the schema after renaming a table. Ensure that temp database triggers and views are updated when renaming a column. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | alter-table-rename-table |
| Files: | files | file ages | folders |
| SHA3-256: |
f3c27d916d4837f8fc3dd812bd004535 |
| User & Date: | dan 2018-08-30 20:03:44 |
Context
|
2018-08-31
| ||
| 18:23 | Ensure b-tree mutexes are always held when sqlite3FindTable() is called. Do not invoke the authorizer callback when parsing schema items as part of ALTER TABLE commands. Fix test script issues. check-in: eac2aa7dce user: dan tags: alter-table-rename-table | |
|
2018-08-30
| ||
| 20:03 | Test the schema after renaming a table. Ensure that temp database triggers and views are updated when renaming a column. check-in: f3c27d916d user: dan tags: alter-table-rename-table | |
| 16:26 | Fix an ALTER TABLE problem with processing temp schema views and triggers. check-in: 72cfb1be29 user: dan tags: alter-table-rename-table | |
Changes
Changes to src/alter.c.
156 157 158 159 160 161 162 163 164 165 166 167 168 169 ... 320 321 322 323 324 325 326 327 328 329 330 331 332 333 ... 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 .... 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 .... 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 .... 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 |
static int isSystemTable(Parse *pParse, const char *zName){
if( 0==sqlite3StrNICmp(zName, "sqlite_", 7) ){
sqlite3ErrorMsg(pParse, "table %s may not be altered", zName);
return 1;
}
return 0;
}
/*
** Generate code to implement the "ALTER TABLE xxx RENAME TO yyy"
** command.
*/
void sqlite3AlterRenameTable(
Parse *pParse, /* Parser context. */
................................................................................
"CASE WHEN tbl_name=%Q COLLATE nocase THEN %Q ELSE tbl_name END "
"WHERE type IN ('view', 'trigger')"
, zDb, zTabName, zName, zTabName, zTabName, zName);
}
sqlite3VdbeAddParseSchemaOp(pParse->pVdbe, iDb, 0);
if( iDb!=1 ) sqlite3VdbeAddParseSchemaOp(pParse->pVdbe, 1, 0);
exit_rename_table:
sqlite3SrcListDelete(db, pSrc);
sqlite3DbFree(db, zName);
db->mDbFlags = savedDbFlags;
}
................................................................................
"sql = sqlite_rename_column(sql, type, name, %Q, %Q, %d, %Q, %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,
pTab->zName
);
/* Drop and reload the database schema. */
if( pParse->pVdbe ){
sqlite3ChangeCookie(pParse, iSchema);
sqlite3VdbeAddParseSchemaOp(pParse->pVdbe, iSchema, 0);
}
sqlite3NestedParse(pParse,
"SELECT 1 "
"FROM \"%w\".%s "
"WHERE name NOT LIKE 'sqlite_%%' AND (type != 'index' OR tbl_name = %Q)"
" AND sql NOT LIKE 'create virtual%%'"
" AND sqlite_rename_column(sql, type, name, %Q, %Q, %d, %Q, -1)=0 ",
zDb, MASTER_NAME,
pTab->zName,
zDb, pTab->zName, iCol, zNew
);
exit_rename_column:
sqlite3SrcListDelete(db, pSrc);
sqlite3DbFree(db, zOld);
sqlite3DbFree(db, zNew);
return;
}
................................................................................
** 0. zSql: SQL statement to rewrite
** 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. Negative
** if this function is being called to check that the schema
** can still be parsed and symbols resolved after the column
** has been renamed.
**
** 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
................................................................................
assert( rc==SQLITE_OK );
rc = renameEditSql(context, &sCtx, zSql, zNew, bQuote);
renameColumnFunc_done:
if( rc!=SQLITE_OK ){
if( sParse.zErrMsg ){
renameColumnParseError(context, (bQuote<0), argv[1], argv[2], &sParse);
}else{
sqlite3_result_error_code(context, rc);
}
}
if( sParse.pVdbe ){
sqlite3VdbeFinalize(sParse.pVdbe);
................................................................................
renameTokenFree(db, sCtx.pList);
sqlite3DbFree(db, sParse.zErrMsg);
sqlite3ParserReset(&sParse);
sqlite3BtreeLeaveAll(db);
return;
}
/*
** Register built-in functions used to help implement ALTER TABLE
*/
void sqlite3AlterFunctions(void){
static FuncDef aAlterTableFuncs[] = {
FUNCTION(sqlite_rename_column, 8, 0, 0, renameColumnFunc),
FUNCTION(sqlite_rename_table, 5, 0, 0, renameTableFunc),
};
sqlite3InsertBuiltinFuncs(aAlterTableFuncs, ArraySize(aAlterTableFuncs));
}
#endif /* SQLITE_ALTER_TABLE */
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > < | < < < < < < < < | < < < | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
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 ... 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 ... 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 .... 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 .... 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 .... 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 1549 1550 1551 1552 1553 1554 1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 |
static int isSystemTable(Parse *pParse, const char *zName){
if( 0==sqlite3StrNICmp(zName, "sqlite_", 7) ){
sqlite3ErrorMsg(pParse, "table %s may not be altered", zName);
return 1;
}
return 0;
}
void renameTestSchema(Parse *pParse, const char *zDb, int bTemp){
sqlite3NestedParse(pParse,
"SELECT 1 "
"FROM \"%w\".%s "
"WHERE name NOT LIKE 'sqlite_%%'"
" AND sql NOT LIKE 'create virtual%%'"
" AND sqlite_rename_test(%Q, sql, type, name, %d)=0 ",
zDb, MASTER_NAME,
zDb, bTemp
);
if( bTemp==0 ){
sqlite3NestedParse(pParse,
"SELECT 1 "
"FROM temp.%s "
"WHERE name NOT LIKE 'sqlite_%%'"
" AND sql NOT LIKE 'create virtual%%'"
" AND sqlite_rename_test(%Q, sql, type, name, 1)=0 ",
MASTER_NAME, zDb
);
}
}
/*
** Generate code to implement the "ALTER TABLE xxx RENAME TO yyy"
** command.
*/
void sqlite3AlterRenameTable(
Parse *pParse, /* Parser context. */
................................................................................
"CASE WHEN tbl_name=%Q COLLATE nocase THEN %Q ELSE tbl_name END "
"WHERE type IN ('view', 'trigger')"
, zDb, zTabName, zName, zTabName, zTabName, zName);
}
sqlite3VdbeAddParseSchemaOp(pParse->pVdbe, iDb, 0);
if( iDb!=1 ) sqlite3VdbeAddParseSchemaOp(pParse->pVdbe, 1, 0);
renameTestSchema(pParse, zDb, iDb==1);
exit_rename_table:
sqlite3SrcListDelete(db, pSrc);
sqlite3DbFree(db, zName);
db->mDbFlags = savedDbFlags;
}
................................................................................
"sql = sqlite_rename_column(sql, type, name, %Q, %Q, %d, %Q, %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,
pTab->zName
);
sqlite3NestedParse(pParse,
"UPDATE temp.%s SET "
"sql = sqlite_rename_column(sql, type, name, %Q, %Q, %d, %Q, %d) "
"WHERE type IN ('trigger', 'view')",
MASTER_NAME,
zDb, pTab->zName, iCol, zNew, bQuote
);
/* Drop and reload the database schema. */
if( pParse->pVdbe ){
sqlite3ChangeCookie(pParse, iSchema);
sqlite3VdbeAddParseSchemaOp(pParse->pVdbe, iSchema, 0);
if( iSchema!=1 ) sqlite3VdbeAddParseSchemaOp(pParse->pVdbe, 1, 0);
}
renameTestSchema(pParse, zDb, iSchema==1);
exit_rename_column:
sqlite3SrcListDelete(db, pSrc);
sqlite3DbFree(db, zOld);
sqlite3DbFree(db, zNew);
return;
}
................................................................................
** 0. zSql: SQL statement to rewrite
** 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
................................................................................
assert( rc==SQLITE_OK );
rc = renameEditSql(context, &sCtx, zSql, zNew, bQuote);
renameColumnFunc_done:
if( rc!=SQLITE_OK ){
if( sParse.zErrMsg ){
renameColumnParseError(context, 0, argv[1], argv[2], &sParse);
}else{
sqlite3_result_error_code(context, rc);
}
}
if( sParse.pVdbe ){
sqlite3VdbeFinalize(sParse.pVdbe);
................................................................................
renameTokenFree(db, sCtx.pList);
sqlite3DbFree(db, sParse.zErrMsg);
sqlite3ParserReset(&sParse);
sqlite3BtreeLeaveAll(db);
return;
}
static void renameTableTest(
sqlite3_context *context,
int NotUsed,
sqlite3_value **argv
){
sqlite3 *db = sqlite3_context_db_handle(context);
unsigned char const *zDb = sqlite3_value_text(argv[0]);
unsigned char const *zInput = sqlite3_value_text(argv[1]);
int bTemp = sqlite3_value_int(argv[4]);
int rc;
Parse sParse;
rc = renameParseSql(&sParse, zDb, 1, db, zInput, bTemp);
if( rc==SQLITE_OK ){
if( sParse.pNewTable && sParse.pNewTable->pSelect ){
NameContext sNC;
memset(&sNC, 0, sizeof(sNC));
sNC.pParse = &sParse;
sqlite3SelectPrep(&sParse, sParse.pNewTable->pSelect, &sNC);
if( sParse.nErr ) rc = sParse.rc;
}
else if( sParse.pNewTrigger ){
rc = renameResolveTrigger(&sParse, bTemp ? 0 : zDb);
}
}
if( rc!=SQLITE_OK ){
renameColumnParseError(context, 1, argv[2], argv[3], &sParse);
}
if( sParse.pVdbe ){
sqlite3VdbeFinalize(sParse.pVdbe);
}
sqlite3DeleteTable(db, sParse.pNewTable);
if( sParse.pNewIndex ) sqlite3FreeIndex(db, sParse.pNewIndex);
sqlite3DeleteTrigger(db, sParse.pNewTrigger);
sqlite3DbFree(db, sParse.zErrMsg);
renameTokenFree(db, sParse.pRename);
sqlite3ParserReset(&sParse);
}
/*
** Register built-in functions used to help implement ALTER TABLE
*/
void sqlite3AlterFunctions(void){
static FuncDef aAlterTableFuncs[] = {
FUNCTION(sqlite_rename_column, 8, 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 test/altercol.test.
648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 |
do_execsql_test 15.2 {
SELECT sql FROM sqlite_master WHERE type='view';
} {{CREATE VIEW vvv AS SELECT xyz AS d FROM xxx WHERE d=0}}
#-------------------------------------------------------------------------
#
do_execsql_test 16.0 {
CREATE TABLE t1(a,b,c);
CREATE TABLE t2(d,e,f);
INSERT INTO t1 VALUES(1,2,3);
INSERT INTO t2 VALUES(4,5,6);
CREATE VIEW v4 AS SELECT a, d FROM t1, t2;
SELECT * FROM v4;
} {1 4}
do_catchsql_test 16.1 {
ALTER TABLE t2 RENAME d TO a;
} {1 {error in view v4 after rename: ambiguous column name: a}}
do_execsql_test 16.2 {
SELECT * FROM v4;
} {1 4}
do_execsql_test 16.3 {
CREATE UNIQUE INDEX t2d ON t2(d);
CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
INSERT INTO t2 VALUES(new.a, new.b, new.c)
ON CONFLICT(d) DO UPDATE SET f = excluded.f;
END;
}
do_execsql_test 16.4 {
INSERT INTO t1 VALUES(4, 8, 456);
SELECT * FROM t2;
} {4 5 456}
do_execsql_test 16.5 {
ALTER TABLE t2 RENAME COLUMN f TO "big f";
INSERT INTO t1 VALUES(4, 0, 20456);
SELECT * FROM t2;
} {4 5 20456}
do_execsql_test 16.6 {
ALTER TABLE t1 RENAME COLUMN c TO "big c";
INSERT INTO t1 VALUES(4, 0, 0);
SELECT * FROM t2;
} {4 5 0}
finish_test
|
| | | | | | | > > > > > > > > > > > > > |
648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 |
do_execsql_test 15.2 {
SELECT sql FROM sqlite_master WHERE type='view';
} {{CREATE VIEW vvv AS SELECT xyz AS d FROM xxx WHERE d=0}}
#-------------------------------------------------------------------------
#
do_execsql_test 16.1.0 {
CREATE TABLE t1(a,b,c);
CREATE TABLE t2(d,e,f);
INSERT INTO t1 VALUES(1,2,3);
INSERT INTO t2 VALUES(4,5,6);
CREATE VIEW v4 AS SELECT a, d FROM t1, t2;
SELECT * FROM v4;
} {1 4}
do_catchsql_test 16.1.1 {
ALTER TABLE t2 RENAME d TO a;
} {1 {error in view v4 after rename: ambiguous column name: a}}
do_execsql_test 16.1.2 {
SELECT * FROM v4;
} {1 4}
do_execsql_test 16.1.3 {
CREATE UNIQUE INDEX t2d ON t2(d);
CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
INSERT INTO t2 VALUES(new.a, new.b, new.c)
ON CONFLICT(d) DO UPDATE SET f = excluded.f;
END;
}
do_execsql_test 16.1.4 {
INSERT INTO t1 VALUES(4, 8, 456);
SELECT * FROM t2;
} {4 5 456}
do_execsql_test 16.1.5 {
ALTER TABLE t2 RENAME COLUMN f TO "big f";
INSERT INTO t1 VALUES(4, 0, 20456);
SELECT * FROM t2;
} {4 5 20456}
do_execsql_test 16.1.6 {
ALTER TABLE t1 RENAME COLUMN c TO "big c";
INSERT INTO t1 VALUES(4, 0, 0);
SELECT * FROM t2;
} {4 5 0}
do_execsql_test 16.2.1 {
CREATE VIEW temp.v5 AS SELECT "big c" FROM t1;
SELECT * FROM v5;
} {3 456 20456 0}
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
|
Changes to test/altertab.test.
174 175 176 177 178 179 180 181 182 183 |
SELECT * FROM t10;
} {1 2 3}
do_execsql_test 5.1 {
ALTER TABLE temp.t9 RENAME TO 't1234567890'
}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > |
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 |
SELECT * FROM t10;
} {1 2 3}
do_execsql_test 5.1 {
ALTER TABLE temp.t9 RENAME TO 't1234567890'
}
do_execsql_test 5.2 {
CREATE TABLE t1(a, b);
CREATE TABLE t2(a, b);
INSERT INTO t1 VALUES(1, 2);
INSERT INTO t2 VALUES(3, 4);
CREATE VIEW v AS SELECT one.a, one.b, t2.a, t2.b FROM t1 AS one, t2;
SELECT * FROM v;
} {1 2 3 4}
do_catchsql_test 5.3 {
ALTER TABLE t2 RENAME TO one;
} {1 {error in view v after rename: ambiguous column name: one.a}}
do_execsql_test 5.4 {
SELECT * FROM v
} {1 2 3 4}
do_execsql_test 5.5 {
DROP VIEW v;
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
|