/ Check-in [336b8a09]
Login

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 | SQL archive
Timelines: family | ancestors | descendants | both | alter-table-rename-column
Files: files | file ages | folders
SHA3-256: 336b8a0923bf65b7a2c35811cb6dec0e262a0b31e534d2a6ab093d2afdb05c1f
User & Date: dan 2018-09-01 20:23:28
Context
2018-09-01
20:38
Add another test case to altertab.test. check-in: 828e8849 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: 336b8a09 user: dan tags: alter-table-rename-column
20:02
Fixes for harmless compiler warnings. check-in: 41b8f38b user: drh tags: alter-table-rename-column
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/alter.c.

   557    557     */
   558    558     zNew = sqlite3NameFromToken(db, pNew);
   559    559     if( !zNew ) goto exit_rename_column;
   560    560     assert( pNew->n>0 );
   561    561     bQuote = sqlite3Isquote(pNew->z[0]);
   562    562     sqlite3NestedParse(pParse, 
   563    563         "UPDATE \"%w\".%s SET "
   564         -      "sql = sqlite_rename_column(sql, type, name, %Q, %Q, %d, %Q, %d) "
          564  +      "sql = sqlite_rename_column(sql, type, name, %Q, %Q, %d, %Q, %d, %d) "
   565    565         "WHERE name NOT LIKE 'sqlite_%%' AND (type != 'index' OR tbl_name = %Q)"
   566    566         " AND sql NOT LIKE 'create virtual%%'",
   567    567         zDb, MASTER_NAME, 
   568         -      zDb, pTab->zName, iCol, zNew, bQuote,
          568  +      zDb, pTab->zName, iCol, zNew, bQuote, iSchema==1,
   569    569         pTab->zName
   570    570     );
   571    571   
   572    572     sqlite3NestedParse(pParse, 
   573    573         "UPDATE temp.%s SET "
   574         -      "sql = sqlite_rename_column(sql, type, name, %Q, %Q, %d, %Q, %d) "
          574  +      "sql = sqlite_rename_column(sql, type, name, %Q, %Q, %d, %Q, %d, 1) "
   575    575         "WHERE type IN ('trigger', 'view')",
   576    576         MASTER_NAME, 
   577    577         zDb, pTab->zName, iCol, zNew, bQuote
   578    578     );
   579    579   
   580    580     /* Drop and reload the database schema. */
   581    581     renameReloadSchema(pParse, iSchema);
................................................................................
  1072   1072   **   1. type:     Type of object ("table", "view" etc.)
  1073   1073   **   2. object:   Name of object
  1074   1074   **   3. Database: Database name (e.g. "main")
  1075   1075   **   4. Table:    Table name
  1076   1076   **   5. iCol:     Index of column to rename
  1077   1077   **   6. zNew:     New column name
  1078   1078   **   7. bQuote:   Non-zero if the new column name should be quoted.
         1079  +**   8. bTemp:    True if zSql comes from temp schema
  1079   1080   **
  1080   1081   ** Do a column rename operation on the CREATE statement given in zSql.
  1081   1082   ** The iCol-th column (left-most is 0) of table zTable is renamed from zCol
  1082   1083   ** into zNew.  The name should be quoted if bQuote is true.
  1083   1084   **
  1084   1085   ** This function is used internally by the ALTER TABLE RENAME COLUMN command.
  1085   1086   ** Though accessible to application code, it is not intended for use by
................................................................................
  1101   1102     RenameCtx sCtx;
  1102   1103     const char *zSql = (const char*)sqlite3_value_text(argv[0]);
  1103   1104     const char *zDb = (const char*)sqlite3_value_text(argv[3]);
  1104   1105     const char *zTable = (const char*)sqlite3_value_text(argv[4]);
  1105   1106     int iCol = sqlite3_value_int(argv[5]);
  1106   1107     const char *zNew = (const char*)sqlite3_value_text(argv[6]);
  1107   1108     int bQuote = sqlite3_value_int(argv[7]);
         1109  +  int bTemp = sqlite3_value_int(argv[8]);
  1108   1110     const char *zOld;
  1109         -  int bTemp = 0;
  1110   1111     int rc;
  1111   1112     Parse sParse;
  1112   1113     Walker sWalker;
  1113   1114     Index *pIdx;
  1114   1115     int i;
  1115   1116     Table *pTab;
  1116   1117   #ifndef SQLITE_OMIT_AUTHORIZATION
................................................................................
  1357   1358   
  1358   1359         else if( sParse.pNewIndex ){
  1359   1360           renameTokenFind(&sParse, &sCtx, sParse.pNewIndex->zName);
  1360   1361           sqlite3WalkExpr(&sWalker, sParse.pNewIndex->pPartIdxWhere);
  1361   1362         }
  1362   1363   
  1363   1364   #ifndef SQLITE_OMIT_TRIGGER
  1364         -      else if( sParse.pNewTrigger ){
         1365  +      else{
  1365   1366           Trigger *pTrigger = sParse.pNewTrigger;
  1366   1367           TriggerStep *pStep;
  1367   1368           if( 0==sqlite3_stricmp(sParse.pNewTrigger->table, zOld) 
  1368   1369               && sCtx.pTab->pSchema==pTrigger->pTabSchema
  1369   1370             ){
  1370   1371             renameTokenFind(&sParse, &sCtx, sParse.pNewTrigger->table);
  1371   1372           }
................................................................................
  1447   1448   }
  1448   1449   
  1449   1450   /*
  1450   1451   ** Register built-in functions used to help implement ALTER TABLE
  1451   1452   */
  1452   1453   void sqlite3AlterFunctions(void){
  1453   1454     static FuncDef aAlterTableFuncs[] = {
  1454         -    FUNCTION(sqlite_rename_column,  8, 0, 0, renameColumnFunc),
         1455  +    FUNCTION(sqlite_rename_column,  9, 0, 0, renameColumnFunc),
  1455   1456       FUNCTION(sqlite_rename_table,  5, 0, 0, renameTableFunc),
  1456   1457       FUNCTION(sqlite_rename_test,  5, 0, 0, renameTableTest),
  1457   1458     };
  1458   1459     sqlite3InsertBuiltinFuncs(aAlterTableFuncs, ArraySize(aAlterTableFuncs));
  1459   1460   }
  1460   1461   #endif  /* SQLITE_ALTER_TABLE */

Changes to src/vdbeaux.c.

  1658   1658         ** to 6.6 percent. The test case is inserting 1000 rows into a table 
  1659   1659         ** with no indexes using a single prepared INSERT statement, bind() 
  1660   1660         ** and reset(). Inserts are grouped into a transaction.
  1661   1661         */
  1662   1662         testcase( p->flags & MEM_Agg );
  1663   1663         testcase( p->flags & MEM_Dyn );
  1664   1664         testcase( p->xDel==sqlite3VdbeFrameMemDel );
  1665         -      testcase( p->flags & MEM_RowSet );
  1666   1665         if( p->flags&(MEM_Agg|MEM_Dyn) ){
  1667   1666           sqlite3VdbeMemRelease(p);
  1668   1667         }else if( p->szMalloc ){
  1669   1668           sqlite3DbFreeNN(db, p->zMalloc);
  1670   1669           p->szMalloc = 0;
  1671   1670         }
  1672   1671   

Changes to test/alter.test.

   681    681   } {1 18 2 9}
   682    682   
   683    683   #--------------------------------------------------------------------------
   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         -  execsql {SELECT SQLITE_RENAME_COLUMN(0,0,0,0,0,0,0,0)}
          688  +  execsql {SELECT SQLITE_RENAME_COLUMN(0,0,0,0,0,0,0,0,0)}
   689    689   } {{}}
   690    690   foreach {tn sql} {
   691    691       1 { SELECT SQLITE_RENAME_TABLE(0,0,0,0,0) }
   692    692       2 { SELECT SQLITE_RENAME_TABLE(10,20,30,40,50) }
   693    693       3 { SELECT SQLITE_RENAME_TABLE('foo', 'foo', 'foo', 'foo', 'foo') }
   694    694   } {
   695    695     do_catchsql_test alter-9.2.$tn $sql {1 {SQL logic error}}

Changes to test/alter4.test.

   389    389       CREATE INDEX t1a ON t1(a DESC);
   390    390       INSERT INTO t1 VALUES(1,2,3);
   391    391       INSERT INTO t1 VALUES(2,3,4);
   392    392       ALTER TABLE t1 ADD COLUMN d;
   393    393       PRAGMA integrity_check;
   394    394     }
   395    395   } {ok}
          396  +
          397  +reset_db
          398  +do_execsql_test alter4-11.0 {
          399  +  CREATE TABLE t1(c INTEGER PRIMARY KEY, d);
          400  +  PRAGMA foreign_keys = on;
          401  +  ALTER TABLE t1 ADD COLUMN e;
          402  +}
          403  +
          404  +do_execsql_test alter4-11.1 {
          405  +  ALTER TABLE t1 ADD COLUMN f REFERENCES t1;
          406  +}
          407  +
          408  +do_catchsql_test alter4-11.2 {
          409  +  ALTER TABLE t1 ADD COLUMN g REFERENCES t1 DEFAULT 4;
          410  +} {1 {Cannot add a REFERENCES column with non-NULL default value}}
          411  +
          412  +do_catchsql_test alter4-11.3 {
          413  +  ALTER TABLE t2 ADD COLUMN g;
          414  +} {1 {no such table: t2}}
          415  +
          416  +ifcapable fts5 {
          417  +  do_execsql_test alter4-11.4 {
          418  +    CREATE VIRTUAL TABLE fff USING fts5(f);
          419  +  }
          420  +  do_catchsql_test alter4-11.2 {
          421  +    ALTER TABLE fff ADD COLUMN g;
          422  +  } {1 {virtual tables may not be altered}}
          423  +}
   396    424   
   397    425   finish_test

Changes to test/altercol.test.

   512    512   
   513    513   ifcapable fts5 {
   514    514     do_execsql_test 12.3.1 {
   515    515       CREATE VIRTUAL TABLE ft USING fts5(a, b, c);
   516    516     }
   517    517     do_catchsql_test 12.3.2 {
   518    518       ALTER TABLE ft RENAME a TO z;
   519         -  } {1 {columns of virtual table ft may not be renamed}}
          519  +  } {1 {cannot rename columns of virtual table "ft"}}
   520    520   }
   521    521   
   522    522   do_execsql_test 12.4.1 {
   523    523     CREATE TABLE t2(x, y, z);
   524    524   }
   525    525   do_catchsql_test 12.4.2 {
   526    526     ALTER TABLE t2 RENAME COLUMN a TO b;
................................................................................
   622    622         'table', 'x1', 'main', 'x1', -1, 'zzz', 0
   623    623     ), (
   624    624         'CREATE TABLE x1(i INTEGER, t TEXT)',
   625    625         'table', 'x1', 'main', 'x1', 2, 'zzz', 0
   626    626     ), (
   627    627         'CREATE TABLE x1(i INTEGER, t TEXT)',
   628    628         'table', 'x1', 'main', 'notable', 0, 'zzz', 0
          629  +  ), (
          630  +      'CREATE TABLE x1(i INTEGER, t TEXT)',
          631  +      'table', 'x1', 'main', 'ddd', -1, 'zzz', 0
   629    632     );
   630    633   } {}
   631    634   
   632    635   do_execsql_test 14.2 {
   633    636     SELECT 
   634         -  sqlite_rename_column(sql, type, object, db, tbl, icol, znew, bquote)
          637  +  sqlite_rename_column(sql, type, object, db, tbl, icol, znew, bquote, 0)
   635    638     FROM ddd;
   636         -} {{} {} {}}
          639  +} {{} {} {} {}}
   637    640   
   638    641   #-------------------------------------------------------------------------
   639    642   #
   640    643   reset_db
   641    644   do_execsql_test 15.0 {
   642    645     CREATE TABLE xxx(a, b, c);
   643    646     SELECT a AS d FROM xxx WHERE d=0;
................................................................................
   704    707   do_execsql_test 16.2.2 {
   705    708     ALTER TABLE t1 RENAME COLUMN "big c" TO reallybigc;
   706    709   } {}
   707    710   
   708    711   do_execsql_test 16.2.3 {
   709    712     SELECT * FROM v5;
   710    713   } {3 456 20456 0}
          714  +
          715  +#-------------------------------------------------------------------------
          716  +#
          717  +do_execsql_test 17.0 {
          718  +  CREATE TABLE u7(x, y, z);
          719  +  CREATE TRIGGER u7t AFTER INSERT ON u7 BEGIN
          720  +    INSERT INTO u8 VALUES(new.x, new.y, new.z);
          721  +  END;
          722  +} {}
          723  +do_catchsql_test 17.1 {
          724  +  ALTER TABLE u7 RENAME x TO xxx;
          725  +} {1 {error in trigger u7t: no such table: main.u8}}
          726  +
          727  +do_execsql_test 17.2 {
          728  +  CREATE TEMP TABLE uu7(x, y, z);
          729  +  CREATE TRIGGER uu7t AFTER INSERT ON uu7 BEGIN
          730  +    INSERT INTO u8 VALUES(new.x, new.y, new.z);
          731  +  END;
          732  +} {}
          733  +do_catchsql_test 17.3 {
          734  +  ALTER TABLE uu7 RENAME x TO xxx;
          735  +} {1 {error in trigger uu7t: no such table: u8}}
          736  +
          737  +reset_db
          738  +forcedelete test.db2
          739  +do_execsql_test 18.0 {
          740  +  ATTACH 'test.db2' AS aux;
          741  +  CREATE TABLE t1(a);
          742  +  CREATE TABLE aux.log(v);
          743  +  CREATE TEMP TRIGGER tr1 AFTER INSERT ON t1 BEGIN
          744  +    INSERT INTO log VALUES(new.a);
          745  +  END;
          746  +  INSERT INTO t1 VALUES(111);
          747  +  SELECT v FROM log;
          748  +} {111}
          749  +
          750  +do_execsql_test 18.1 {
          751  +  ALTER TABLE t1 RENAME a TO b;
          752  +}
          753  +
   711    754   
   712    755   finish_test

Changes to test/altertab.test.

   200    200     CREATE VIEW temp.vv AS SELECT one.a, one.b, t2.a, t2.b FROM t1 AS one, t2;
   201    201     SELECT * FROM vv;
   202    202   } {1 2 3 4}
   203    203   
   204    204   do_catchsql_test 5.6 {
   205    205     ALTER TABLE t2 RENAME TO one;
   206    206   } {1 {error in view vv after rename: ambiguous column name: one.a}}
          207  +
          208  +#-------------------------------------------------------------------------
          209  +
          210  +register_tcl_module db
          211  +proc tcl_command {method args} {
          212  +  switch -- $method {
          213  +    xConnect {
          214  +      return "CREATE TABLE t1(a, b, c)"
          215  +    }
          216  +  }
          217  +  return {}
          218  +}
          219  +
          220  +do_execsql_test 6.0 {
          221  +  CREATE VIRTUAL TABLE x1 USING tcl(tcl_command);
          222  +}
          223  +
          224  +do_execsql_test 6.1 {
          225  +  ALTER TABLE x1 RENAME TO x2;
          226  +  SELECT sql FROM sqlite_master WHERE name = 'x2'
          227  +} {{CREATE VIRTUAL TABLE "x2" USING tcl(tcl_command)}}
          228  +
          229  +do_execsql_test 7.1 {
          230  +  CREATE TABLE ddd(db, sql, zOld, zNew, bTemp);
          231  +  INSERT INTO ddd VALUES(
          232  +      'main', 'CREATE TABLE x1(i INTEGER, t TEXT)', 'ddd', NULL, 0
          233  +  ), (
          234  +      'main', 'CREATE TABLE x1(i INTEGER, t TEXT)', NULL, 'eee', 0
          235  +  ), (
          236  +      'main', NULL, 'ddd', 'eee', 0
          237  +  );
          238  +} {}
          239  +
          240  +do_execsql_test 7.2 {
          241  +  SELECT 
          242  +  sqlite_rename_table(db, sql, zOld, zNew, bTemp)
          243  +  FROM ddd;
          244  +} {{} {} {}}
   207    245   
   208    246   finish_test
   209    247   
   210    248