/ Check-in [6805b5900d]
Login

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: 6805b5900df5e6d3329cbad2660875ebe4069efe37b19575f527d153dc0882a1
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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