Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Improve the error messages emitted by RENAME TABLE. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | alter-table-rename-column |
| Files: | files | file ages | folders |
| SHA3-256: |
6805b5900df5e6d3329cbad2660875eb |
| User & Date: | dan 2018-09-03 20:05:15 |
Context
|
2018-09-04
| ||
| 18:23 | Merge latest trunk changes into this branch. check-in: ef9e088290 user: dan tags: alter-table-rename-column | |
|
2018-09-03
| ||
| 20:05 | Improve the error messages emitted by RENAME TABLE. check-in: 6805b5900d user: dan tags: alter-table-rename-column | |
|
2018-09-01
| ||
| 20:38 | Add another test case to altertab.test. check-in: 828e8849fa user: dan tags: alter-table-rename-column | |
Changes
Changes to src/alter.c.
187 187 zTabName = pTab->zName; 188 188 nTabName = sqlite3Utf8CharLen(zTabName, -1); 189 189 190 190 /* Rewrite all CREATE TABLE, INDEX, TRIGGER or VIEW statements in 191 191 ** the schema to use the new table name. */ 192 192 sqlite3NestedParse(pParse, 193 193 "UPDATE \"%w\".%s SET " 194 - "sql = sqlite_rename_table(%Q, sql, %Q, %Q, %d) " 194 + "sql = sqlite_rename_table(%Q, type, name, sql, %Q, %Q, %d) " 195 195 "WHERE (type!='index' OR tbl_name=%Q COLLATE nocase)" 196 196 "AND name NOT LIKE 'sqlite_%%'" 197 197 , zDb, MASTER_NAME, zDb, zTabName, zName, (iDb==1), zTabName 198 198 ); 199 199 200 200 /* Update the tbl_name and name columns of the sqlite_master table 201 201 ** as required. */ ................................................................................ 227 227 228 228 /* If the table being renamed is not itself part of the temp database, 229 229 ** edit view and trigger definitions within the temp database 230 230 ** as required. */ 231 231 if( iDb!=1 ){ 232 232 sqlite3NestedParse(pParse, 233 233 "UPDATE sqlite_temp_master SET " 234 - "sql = sqlite_rename_table(%Q, sql, %Q, %Q, 1), " 234 + "sql = sqlite_rename_table(%Q, type, name, sql, %Q, %Q, 1), " 235 235 "tbl_name = " 236 236 "CASE WHEN tbl_name=%Q COLLATE nocase THEN %Q ELSE tbl_name END " 237 237 "WHERE type IN ('view', 'trigger')" 238 238 , zDb, zTabName, zName, zTabName, zTabName, zName); 239 239 } 240 240 241 241 renameReloadSchema(pParse, iDb); ................................................................................ 1271 1271 /* 1272 1272 ** This C function implements an SQL user function that is used by SQL code 1273 1273 ** generated by the ALTER TABLE ... RENAME command to modify the definition 1274 1274 ** of any foreign key constraints that use the table being renamed as the 1275 1275 ** parent table. It is passed three arguments: 1276 1276 ** 1277 1277 ** 0: The database containing the table being renamed. 1278 -** 1: The complete text of the schema statement being modified, 1279 -** 2: The old name of the table being renamed, and 1280 -** 3: The new name of the table being renamed. 1281 -** 4: True if the schema statement comes from the temp db. 1278 +** 1. type: Type of object ("table", "view" etc.) 1279 +** 2. object: Name of object 1280 +** 3: The complete text of the schema statement being modified, 1281 +** 4: The old name of the table being renamed, and 1282 +** 5: The new name of the table being renamed. 1283 +** 6: True if the schema statement comes from the temp db. 1282 1284 ** 1283 1285 ** It returns the new schema statement. For example: 1284 1286 ** 1285 1287 ** sqlite_rename_table('main', 'CREATE TABLE t1(a REFERENCES t2)','t2','t3',0) 1286 1288 ** -> 'CREATE TABLE t1(a REFERENCES t3)' 1287 1289 */ 1288 1290 static void renameTableFunc( 1289 1291 sqlite3_context *context, 1290 1292 int NotUsed, 1291 1293 sqlite3_value **argv 1292 1294 ){ 1293 1295 sqlite3 *db = sqlite3_context_db_handle(context); 1294 1296 const char *zDb = (const char*)sqlite3_value_text(argv[0]); 1295 - const char *zInput = (const char*)sqlite3_value_text(argv[1]); 1296 - const char *zOld = (const char*)sqlite3_value_text(argv[2]); 1297 - const char *zNew = (const char*)sqlite3_value_text(argv[3]); 1298 - int bTemp = sqlite3_value_int(argv[4]); 1297 + const char *zInput = (const char*)sqlite3_value_text(argv[3]); 1298 + const char *zOld = (const char*)sqlite3_value_text(argv[4]); 1299 + const char *zNew = (const char*)sqlite3_value_text(argv[5]); 1300 + int bTemp = sqlite3_value_int(argv[6]); 1299 1301 UNUSED_PARAMETER(NotUsed); 1300 1302 1301 1303 if( zInput && zOld && zNew ){ 1302 1304 Parse sParse; 1303 1305 int rc; 1304 1306 int bQuote = 1; 1305 1307 RenameCtx sCtx; ................................................................................ 1384 1386 #endif 1385 1387 } 1386 1388 1387 1389 if( rc==SQLITE_OK ){ 1388 1390 rc = renameEditSql(context, &sCtx, zInput, zNew, bQuote); 1389 1391 } 1390 1392 if( rc!=SQLITE_OK ){ 1391 - sqlite3_result_error_code(context, rc); 1393 + if( sParse.zErrMsg ){ 1394 + renameColumnParseError(context, 0, argv[1], argv[2], &sParse); 1395 + }else{ 1396 + sqlite3_result_error_code(context, rc); 1397 + } 1392 1398 } 1393 1399 1394 1400 renameParseCleanup(&sParse); 1395 1401 renameTokenFree(db, sCtx.pList); 1396 1402 sqlite3BtreeLeaveAll(db); 1397 1403 #ifndef SQLITE_OMIT_AUTHORIZATION 1398 1404 db->xAuth = xAuth; ................................................................................ 1449 1455 1450 1456 /* 1451 1457 ** Register built-in functions used to help implement ALTER TABLE 1452 1458 */ 1453 1459 void sqlite3AlterFunctions(void){ 1454 1460 static FuncDef aAlterTableFuncs[] = { 1455 1461 FUNCTION(sqlite_rename_column, 9, 0, 0, renameColumnFunc), 1456 - FUNCTION(sqlite_rename_table, 5, 0, 0, renameTableFunc), 1462 + FUNCTION(sqlite_rename_table, 7, 0, 0, renameTableFunc), 1457 1463 FUNCTION(sqlite_rename_test, 5, 0, 0, renameTableTest), 1458 1464 }; 1459 1465 sqlite3InsertBuiltinFuncs(aAlterTableFuncs, ArraySize(aAlterTableFuncs)); 1460 1466 } 1461 1467 #endif /* SQLITE_ALTER_TABLE */
Changes to test/alter.test.
684 684 # alter-9.X - Special test: Make sure the sqlite_rename_column() and 685 685 # rename_table() functions do not crash when handed bad input. 686 686 # 687 687 do_test alter-9.1 { 688 688 execsql {SELECT SQLITE_RENAME_COLUMN(0,0,0,0,0,0,0,0,0)} 689 689 } {{}} 690 690 foreach {tn sql} { 691 - 1 { SELECT SQLITE_RENAME_TABLE(0,0,0,0,0) } 692 - 2 { SELECT SQLITE_RENAME_TABLE(10,20,30,40,50) } 693 - 3 { SELECT SQLITE_RENAME_TABLE('foo', 'foo', 'foo', 'foo', 'foo') } 691 + 1 { SELECT SQLITE_RENAME_TABLE(0,0,0,0,0,0,0) } 692 + 2 { SELECT SQLITE_RENAME_TABLE(10,20,30,40,50,60,70) } 693 + 3 { SELECT SQLITE_RENAME_TABLE('foo','foo','foo','foo','foo','foo','foo') } 694 694 } { 695 - do_catchsql_test alter-9.2.$tn $sql {1 {SQL logic error}} 695 + do_test alter-9.2.$tn { 696 + catch { execsql $sql } 697 + } 1 696 698 } 697 699 698 700 #------------------------------------------------------------------------ 699 701 # alter-10.X - Make sure ALTER TABLE works with multi-byte UTF-8 characters 700 702 # in the names. 701 703 # 702 704 do_test alter-10.1 {
Changes to test/altertab.test.
235 235 ), ( 236 236 'main', NULL, 'ddd', 'eee', 0 237 237 ); 238 238 } {} 239 239 240 240 do_execsql_test 7.2 { 241 241 SELECT 242 - sqlite_rename_table(db, sql, zOld, zNew, bTemp) 242 + sqlite_rename_table(db, 0, 0, sql, zOld, zNew, bTemp) 243 243 FROM ddd; 244 244 } {{} {} {}} 245 245 246 246 #------------------------------------------------------------------------- 247 247 # 248 248 reset_db 249 249 forcedelete test.db2 ................................................................................ 262 262 ALTER TABLE aux.p1 RENAME TO ppp; 263 263 } 264 264 265 265 do_execsql_test 8.2 { 266 266 INSERT INTO aux.c1 VALUES(NULL, 1); 267 267 SELECT sql FROM aux.sqlite_master WHERE name = 'c1'; 268 268 } {{CREATE TABLE c1(x INTEGER PRIMARY KEY, y REFERENCES "ppp"(a))}} 269 + 270 +reset_db 271 +do_execsql_test 9.0 { 272 + CREATE TABLE t1(a, b, c); 273 + CREATE VIEW v1 AS SELECT * FROM t2; 274 +} 275 +do_catchsql_test 9.1 { 276 + ALTER TABLE t1 RENAME TO t3; 277 +} {1 {error in view v1: no such table: main.t2}} 278 +do_execsql_test 9.2 { 279 + DROP VIEW v1; 280 + CREATE TRIGGER tr AFTER INSERT ON t1 BEGIN 281 + INSERT INTO t2 VALUES(new.a); 282 + END; 283 +} 284 +do_catchsql_test 9.3 { 285 + ALTER TABLE t1 RENAME TO t3; 286 +} {1 {error in trigger tr: no such table: main.t2}} 287 + 288 +forcedelete test.db2 289 +do_execsql_test 9.4 { 290 + DROP TRIGGER tr; 291 + 292 + ATTACH 'test.db2' AS aux; 293 + CREATE TRIGGER tr AFTER INSERT ON t1 BEGIN SELECT 1, 2, 3; END; 294 + 295 + CREATE TABLE aux.t1(x); 296 + CREATE TEMP TRIGGER tr AFTER INSERT ON aux.t1 BEGIN SELECT 1, 2, 3; END; 297 +} 298 +do_execsql_test 9.5 { 299 + ALTER TABLE main.t1 RENAME TO t3; 300 +} 301 +do_execsql_test 9.6 { 302 + SELECT sql FROM sqlite_temp_master; 303 + SELECT sql FROM sqlite_master WHERE type='trigger'; 304 +} { 305 + {CREATE TRIGGER tr AFTER INSERT ON aux.t1 BEGIN SELECT 1, 2, 3; END} 306 + {CREATE TRIGGER tr AFTER INSERT ON "t3" BEGIN SELECT 1, 2, 3; END} 307 +} 269 308 270 309 finish_test 271 310 272 311