/ Check-in [a0e06d2c5e]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:After modifying and reparsing the schema as part of an ALTER TABLE RENAME COLUMN, check that no new schema errors have been introduced (e.g. ambiguous column names in views) before committing the operation.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | alter-table-rename-column
Files: files | file ages | folders
SHA3-256: a0e06d2c5e3abb3f300491e7651bb177a436899efd4506de9239359096b6a9e7
User & Date: dan 2018-08-24 17:55:49
Context
2018-08-24
20:10
Fix a problem with renaming a column that occurs as an "excluded.colname" construction in an UPSERT that is part of a trigger program. check-in: bb2f723496 user: dan tags: alter-table-rename-column
17:55
After modifying and reparsing the schema as part of an ALTER TABLE RENAME COLUMN, check that no new schema errors have been introduced (e.g. ambiguous column names in views) before committing the operation. check-in: a0e06d2c5e user: dan tags: alter-table-rename-column
16:04
Avoid incorrectly replacing tokens that refer to a column being renamed via an alias. For example, do not overwrite "xyz" when "a" is renamed in "CREATE VIEW v1 AS SELECT a AS xyz FROM tbl WHERE xyz=1" check-in: ad072a835f user: dan tags: alter-table-rename-column
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/alter.c.

   887    887     );
   888    888   
   889    889     /* Drop and reload the database schema. */
   890    890     if( pParse->pVdbe ){
   891    891       sqlite3ChangeCookie(pParse, iSchema);
   892    892       sqlite3VdbeAddParseSchemaOp(pParse->pVdbe, iSchema, 0);
   893    893     }
          894  +
          895  +  sqlite3NestedParse(pParse, 
          896  +      "SELECT 1 "
          897  +      "FROM \"%w\".%s "
          898  +      "WHERE name NOT LIKE 'sqlite_%%' AND (type != 'index' OR tbl_name = %Q)"
          899  +      " AND sql NOT LIKE 'create virtual%%'"
          900  +      " AND sqlite_rename_column(sql, type, name, %Q, %Q, %d, %Q, -1)=0 ",
          901  +      zDb, MASTER_NAME, 
          902  +      pTab->zName,
          903  +      zDb, pTab->zName, iCol, zNew
          904  +  );
   894    905   
   895    906    exit_rename_column:
   896    907     sqlite3SrcListDelete(db, pSrc);
   897    908     sqlite3DbFree(db, zOld);
   898    909     sqlite3DbFree(db, zNew);
   899    910     return;
   900    911   }
................................................................................
  1066   1077   ** object (either pParse->pNewTable, pNewIndex or pNewTrigger) as part of an
  1067   1078   ** ALTER TABLE RENAME COLUMN program. The error message emitted by the
  1068   1079   ** sub-routine is currently stored in pParse->zErrMsg. This function
  1069   1080   ** adds context to the error message and then stores it in pCtx.
  1070   1081   */
  1071   1082   static void renameColumnParseError(
  1072   1083     sqlite3_context *pCtx, 
         1084  +  int bPost,
  1073   1085     sqlite3_value *pType,
  1074   1086     sqlite3_value *pObject,
  1075   1087     Parse *pParse
  1076   1088   ){
  1077   1089     const char *zT = (const char*)sqlite3_value_text(pType);
  1078   1090     const char *zN = (const char*)sqlite3_value_text(pObject);
  1079   1091     char *zErr;
  1080   1092   
  1081         -  zErr = sqlite3_mprintf("error processing %s %s: %s", zT, zN, pParse->zErrMsg);
         1093  +  zErr = sqlite3_mprintf("error in %s %s%s: %s", 
         1094  +      zT, zN, (bPost ? " after rename" : ""),
         1095  +      pParse->zErrMsg
         1096  +  );
  1082   1097     sqlite3_result_error(pCtx, zErr, -1);
  1083   1098     sqlite3_free(zErr);
  1084   1099   }
  1085   1100   
  1086   1101   /*
  1087   1102   ** For each name in the the expression-list pEList (i.e. each
  1088   1103   ** pEList->a[i].zName) that matches the string in zOld, extract the 
................................................................................
  1136   1151   **   0. zSql:     SQL statement to rewrite
  1137   1152   **   1. type:     Type of object ("table", "view" etc.)
  1138   1153   **   2. object:   Name of object
  1139   1154   **   3. Database: Database name (e.g. "main")
  1140   1155   **   4. Table:    Table name
  1141   1156   **   5. iCol:     Index of column to rename
  1142   1157   **   6. zNew:     New column name
  1143         -**   7. bQuote:   True if the new column name should be quoted
         1158  +**   7. bQuote:   Non-zero if the new column name should be quoted. Negative
         1159  +**                if this function is being called to check that the schema
         1160  +**                can still be parsed and symbols resolved after the column
         1161  +**                has been renamed.
  1144   1162   **
  1145   1163   ** Do a column rename operation on the CREATE statement given in zSql.
  1146   1164   ** The iCol-th column (left-most is 0) of table zTable is renamed from zCol
  1147   1165   ** into zNew.  The name should be quoted if bQuote is true.
  1148   1166   **
  1149   1167   ** This function is used internally by the ALTER TABLE RENAME COLUMN command.
  1150   1168   ** Though accessible to application code, it is not intended for use by
................................................................................
  1433   1451     }else{
  1434   1452       rc = SQLITE_NOMEM;
  1435   1453     }
  1436   1454   
  1437   1455   renameColumnFunc_done:
  1438   1456     if( rc!=SQLITE_OK ){
  1439   1457       if( sParse.zErrMsg ){
  1440         -      renameColumnParseError(context, argv[1], argv[2], &sParse);
         1458  +      renameColumnParseError(context, (bQuote<0), argv[1], argv[2], &sParse);
  1441   1459       }else{
  1442   1460         sqlite3_result_error_code(context, rc);
  1443   1461       }
  1444   1462     }
  1445   1463   
  1446   1464     if( sParse.pVdbe ){
  1447   1465       sqlite3VdbeFinalize(sParse.pVdbe);

Changes to src/vdbe.c.

  5750   5750   
  5751   5751   #ifndef SQLITE_OMIT_ALTERTABLE
  5752   5752     if( pOp->p4.z==0 ){
  5753   5753       sqlite3SchemaClear(db->aDb[iDb].pSchema);
  5754   5754       db->mDbFlags &= ~DBFLAG_SchemaKnownOk;
  5755   5755       rc = sqlite3InitOne(db, iDb, &p->zErrMsg, INITFLAG_AlterTable);
  5756   5756       db->mDbFlags |= DBFLAG_SchemaChange;
         5757  +    p->expired = 0;
  5757   5758     }else
  5758   5759   #endif
  5759   5760     {
  5760   5761       zMaster = MASTER_NAME;
  5761   5762       initData.db = db;
  5762   5763       initData.iDb = pOp->p1;
  5763   5764       initData.pzErrMsg = &p->zErrMsg;

Changes to test/altercol.test.

   337    337     ALTER TABLE b2 RENAME x TO hello;
   338    338     SELECT sql FROM sqlite_master WHERE name='xxx';
   339    339   } {{CREATE VIEW xxx AS SELECT a FROM b1 UNION SELECT hello FROM b2 ORDER BY 1 COLLATE nocase}}
   340    340   
   341    341   do_catchsql_test 8.4.5 {
   342    342     CREATE VIEW zzz AS SELECT george, ringo FROM b1;
   343    343     ALTER TABLE b1 RENAME a TO aaa;
   344         -} {1 {error processing view zzz: no such column: george}}
          344  +} {1 {error in view zzz: no such column: george}}
   345    345   
   346    346   #-------------------------------------------------------------------------
   347    347   # More triggers.
   348    348   #
   349    349   proc do_rename_column_test {tn old new lSchema} {
   350    350     for {set i 0} {$i < 2} {incr i} {
   351    351       drop_all_tables_and_views db
................................................................................
   468    468   
   469    469   do_execsql_test 11.2 {
   470    470     CREATE VIEW v1 AS SELECT e1.*, x1.c FROM e1, x1;
   471    471   }
   472    472   
   473    473   do_catchsql_test 11.3 {
   474    474     ALTER TABLE x1 RENAME c TO ccc;
   475         -} {1 {error processing view v1: no such module: echo}}
          475  +} {1 {error in view v1: no such module: echo}}
   476    476   
   477    477   #-------------------------------------------------------------------------
   478    478   # Test some error conditions:
   479    479   #
   480    480   #   1. Renaming a column of a system table,
   481    481   #   2. Renaming a column of a VIEW,
   482    482   #   3. Renaming a column of a virtual table.
................................................................................
   537    537     CREATE TRIGGER tr1 AFTER INSERT ON x1 BEGIN
   538    538       SELECT * FROM nosuchtable;
   539    539     END;
   540    540   }
   541    541   
   542    542   do_catchsql_test 13.1.2 {
   543    543     ALTER TABLE x1 RENAME COLUMN t TO ttt;
   544         -} {1 {error processing trigger tr1: no such table: main.nosuchtable}}
          544  +} {1 {error in trigger tr1: no such table: main.nosuchtable}}
   545    545   
   546    546   do_execsql_test 13.1.3 {
   547    547     DROP TRIGGER tr1;
   548    548     CREATE INDEX x1i ON x1(i);
   549    549     SELECT sql FROM sqlite_master WHERE name='x1i';
   550    550   } {{CREATE INDEX x1i ON x1(i)}}
   551    551   
................................................................................
   552    552   do_execsql_test 13.1.4 {
   553    553     PRAGMA writable_schema = 1;
   554    554     UPDATE sqlite_master SET sql = 'CREATE INDEX x1i ON x1(j)' WHERE name='x1i';
   555    555   } {}
   556    556   
   557    557   do_catchsql_test 13.1.5 {
   558    558     ALTER TABLE x1 RENAME COLUMN t TO ttt;
   559         -} {1 {error processing index x1i: no such column: j}}
          559  +} {1 {error in index x1i: no such column: j}}
   560    560   
   561    561   do_execsql_test 13.1.6 {
   562    562     UPDATE sqlite_master SET sql = '' WHERE name='x1i';
   563    563   } {}
   564    564   
   565    565   do_catchsql_test 13.1.7 {
   566    566     ALTER TABLE x1 RENAME COLUMN t TO ttt;
................................................................................
   603    603     do_execsql_test 13.2.$tn.1 "
   604    604       DROP TRIGGER IF EXISTS tr1;
   605    605       $trigger
   606    606     "
   607    607   
   608    608     do_catchsql_test 13.2.$tn.2 {
   609    609       ALTER TABLE x1 RENAME COLUMN t TO ttt;
   610         -  } "1 {error processing trigger tr1: $error}"
          610  +  } "1 {error in trigger tr1: $error}"
   611    611   }
   612    612   
   613    613   #-------------------------------------------------------------------------
   614    614   # Passing invalid parameters directly to sqlite_rename_column().
   615    615   #
   616    616   do_execsql_test 14.1 {
   617    617     CREATE TABLE ddd(sql, type, object, db, tbl, icol, znew, bquote);
................................................................................
   645    645     CREATE VIEW vvv AS SELECT a AS d FROM xxx WHERE d=0;
   646    646     ALTER TABLE xxx RENAME a TO xyz;
   647    647   }
   648    648   
   649    649   do_execsql_test 15.2 {
   650    650     SELECT sql FROM sqlite_master WHERE type='view';
   651    651   } {{CREATE VIEW vvv AS SELECT xyz AS d FROM xxx WHERE d=0}}
          652  +
          653  +#-------------------------------------------------------------------------
          654  +#
          655  +do_execsql_test 16.0 {
          656  +  CREATE TABLE t1(a,b,c);
          657  +  CREATE TABLE t2(d,e,f);
          658  +  INSERT INTO t1 VALUES(1,2,3);
          659  +  INSERT INTO t2 VALUES(4,5,6);
          660  +  CREATE VIEW v4 AS SELECT a, d FROM t1, t2;
          661  +  SELECT * FROM v4;
          662  +} {1 4}
          663  +
          664  +do_catchsql_test 16.1 {
          665  +  ALTER TABLE t2 RENAME d TO a;
          666  +} {1 {error in view v4 after rename: ambiguous column name: a}}
          667  +
          668  +do_execsql_test 16.2 {
          669  +  SELECT * FROM v4;
          670  +} {1 4}
          671  +
          672  +reset_db
   652    673   
   653    674   finish_test