/ Check-in [82c4c10a]
Login

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

Overview
Comment:Add further tests for RENAME COLUMN.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | alter-table-rename-column
Files: files | file ages | folders
SHA3-256: 82c4c10a96db76f39edf5f6d027294d449d75b5a18da70da3667786da90e184d
User & Date: dan 2018-08-20 20:01:01
Context
2018-08-21
08:29
Minor changes to function tokenExpr() in order to claw back cycles lost to the rename-column change. check-in: 47997695 user: dan tags: alter-table-rename-column
2018-08-20
20:01
Add further tests for RENAME COLUMN. check-in: 82c4c10a user: dan tags: alter-table-rename-column
16:16
Ensure that it is not possible to rename columns of system tables, views or virtual tables. check-in: 786b5991 user: dan tags: alter-table-rename-column
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/alter.c.

   874    874     */
   875    875     zNew = sqlite3NameFromToken(db, pNew);
   876    876     if( !zNew ) goto exit_rename_column;
   877    877     assert( pNew->n>0 );
   878    878     bQuote = sqlite3Isquote(pNew->z[0]);
   879    879     sqlite3NestedParse(pParse, 
   880    880         "UPDATE \"%w\".%s SET "
   881         -      "sql = sqlite_rename_column(sql, %Q, %Q, %d, %Q, %d) "
          881  +      "sql = sqlite_rename_column(sql, type, name, %Q, %Q, %d, %Q, %d) "
   882    882         "WHERE name NOT LIKE 'sqlite_%%' AND (type != 'index' OR tbl_name = %Q)"
   883    883         " AND sql NOT LIKE 'create virtual%%'",
   884    884         zDb, MASTER_NAME, 
   885    885         zDb, pTab->zName, iCol, zNew, bQuote,
   886    886         pTab->zName
   887    887     );
   888    888   
................................................................................
   953    953   /*
   954    954   ** If there is a RenameToken object associated with parse tree element
   955    955   ** pFrom, then remap that object over to pTo due to a transformation
   956    956   ** in the parse tree.
   957    957   */
   958    958   void sqlite3MoveRenameToken(Parse *pParse, void *pTo, void *pFrom){
   959    959     RenameToken *p;
   960         -  for(p=pParse->pRename; p; p=p->pNext){
          960  +  for(p=pParse->pRename; ALWAYS(p); p=p->pNext){
   961    961       if( p->p==pFrom ){
   962    962         p->p = pTo;
   963    963         break;
   964    964       }
   965    965     }
   966    966     assert( p );
   967    967   }
................................................................................
  1060   1060   /*
  1061   1061   ** An error occured while parsing or otherwise processing a database
  1062   1062   ** object (either pParse->pNewTable, pNewIndex or pNewTrigger) as part of an
  1063   1063   ** ALTER TABLE RENAME COLUMN program. The error message emitted by the
  1064   1064   ** sub-routine is currently stored in pParse->zErrMsg. This function
  1065   1065   ** adds context to the error message and then stores it in pCtx.
  1066   1066   */
  1067         -static void renameColumnParseError(sqlite3_context *pCtx, Parse *pParse){
  1068         -  const char *zT;
  1069         -  const char *zN;
         1067  +static void renameColumnParseError(
         1068  +  sqlite3_context *pCtx, 
         1069  +  sqlite3_value *pType,
         1070  +  sqlite3_value *pObject,
         1071  +  Parse *pParse
         1072  +){
         1073  +  const char *zT = sqlite3_value_text(pType);
         1074  +  const char *zN = sqlite3_value_text(pObject);
  1070   1075     char *zErr;
  1071         -  if( pParse->pNewTable ){
  1072         -    zT = pParse->pNewTable->pSelect ? "view" : "table";
  1073         -    zN = pParse->pNewTable->zName;
  1074         -  }else if( pParse->pNewIndex ){
  1075         -    zT = "index";
  1076         -    zN = pParse->pNewIndex->zName;
  1077         -  }else{
  1078         -    assert( pParse->pNewTrigger );
  1079         -    zT = "trigger";
  1080         -    zN = pParse->pNewTrigger->zName;
  1081         -  }
         1076  +
  1082   1077     zErr = sqlite3_mprintf("error processing %s %s: %s", zT, zN, pParse->zErrMsg);
  1083   1078     sqlite3_result_error(pCtx, zErr, -1);
  1084   1079     sqlite3_free(zErr);
  1085   1080   }
  1086   1081   
  1087   1082   /*
  1088   1083   ** SQL function:
  1089   1084   **
  1090   1085   **     sqlite_rename_column(zSql, iCol, bQuote, zNew, zTable, zOld)
  1091   1086   **
  1092   1087   **   0. zSql:     SQL statement to rewrite
  1093         -**   1. Database: Database name (e.g. "main")
  1094         -**   2. Table:    Table name
  1095         -**   3. iCol:     Index of column to rename
  1096         -**   4. zNew:     New column name
  1097         -**   5. bQuote: True if the new column name should be quoted
         1088  +**   1. type:     Type of object ("table", "view" etc.)
         1089  +**   2. object:   Name of object
         1090  +**   3. Database: Database name (e.g. "main")
         1091  +**   4. Table:    Table name
         1092  +**   5. iCol:     Index of column to rename
         1093  +**   6. zNew:     New column name
         1094  +**   7. bQuote:   True if the new column name should be quoted
  1098   1095   **
  1099   1096   ** Do a column rename operation on the CREATE statement given in zSql.
  1100   1097   ** The iCol-th column (left-most is 0) of table zTable is renamed from zCol
  1101   1098   ** into zNew.  The name should be quoted if bQuote is true.
  1102   1099   **
  1103   1100   ** This function is used internally by the ALTER TABLE RENAME COLUMN command.
  1104   1101   ** Though accessible to application code, it is not intended for use by
................................................................................
  1116   1113     int NotUsed,
  1117   1114     sqlite3_value **argv
  1118   1115   ){
  1119   1116     sqlite3 *db = sqlite3_context_db_handle(context);
  1120   1117     RenameCtx sCtx;
  1121   1118     const char *zSql = (const char*)sqlite3_value_text(argv[0]);
  1122   1119     int nSql = sqlite3_value_bytes(argv[0]);
  1123         -  const char *zDb = (const char*)sqlite3_value_text(argv[1]);
  1124         -  const char *zTable = (const char*)sqlite3_value_text(argv[2]);
  1125         -  int iCol = sqlite3_value_int(argv[3]);
  1126         -  const char *zNew = (const char*)sqlite3_value_text(argv[4]);
  1127         -  int nNew = sqlite3_value_bytes(argv[4]);
  1128         -  int bQuote = sqlite3_value_int(argv[5]);
         1120  +  const char *zDb = (const char*)sqlite3_value_text(argv[3]);
         1121  +  const char *zTable = (const char*)sqlite3_value_text(argv[4]);
         1122  +  int iCol = sqlite3_value_int(argv[5]);
         1123  +  const char *zNew = (const char*)sqlite3_value_text(argv[6]);
         1124  +  int nNew = sqlite3_value_bytes(argv[6]);
         1125  +  int bQuote = sqlite3_value_int(argv[7]);
  1129   1126     const char *zOld;
  1130   1127   
  1131   1128     int rc;
  1132   1129     char *zErr = 0;
  1133   1130     Parse sParse;
  1134   1131     Walker sWalker;
  1135   1132     Index *pIdx;
................................................................................
  1138   1135     char *zQuot = 0;                /* Quoted version of zNew */
  1139   1136     int nQuot = 0;                  /* Length of zQuot in bytes */
  1140   1137     int i;
  1141   1138     Table *pTab;
  1142   1139   
  1143   1140     UNUSED_PARAMETER(NotUsed);
  1144   1141     if( zSql==0 ) return;
  1145         -  if( zNew==0 ) return;
  1146   1142     if( zTable==0 ) return;
         1143  +  if( zNew==0 ) return;
  1147   1144     if( iCol<0 ) return;
  1148   1145     pTab = sqlite3FindTable(db, zTable, zDb);
  1149   1146     if( pTab==0 || iCol>=pTab->nCol ) return;
  1150   1147     zOld = pTab->aCol[iCol].zName;
  1151   1148     memset(&sCtx, 0, sizeof(sCtx));
  1152   1149     sCtx.iCol = ((iCol==pTab->iPKey) ? -1 : iCol);
  1153   1150   
................................................................................
  1268   1265         rc = sqlite3ResolveExprNames(&sNC, sParse.pNewTrigger->pWhen);
  1269   1266       }
  1270   1267   
  1271   1268       for(pStep=sParse.pNewTrigger->step_list; 
  1272   1269           rc==SQLITE_OK && pStep; 
  1273   1270           pStep=pStep->pNext
  1274   1271       ){
  1275         -      if( pStep->pSelect ) sqlite3SelectPrep(&sParse, pStep->pSelect, &sNC);
  1276         -      if( pStep->zTarget ){ 
         1272  +      if( pStep->pSelect ){
         1273  +        sqlite3SelectPrep(&sParse, pStep->pSelect, &sNC);
         1274  +        if( sParse.nErr ) rc = sParse.rc;
         1275  +      }
         1276  +      if( rc==SQLITE_OK && pStep->zTarget ){ 
  1277   1277           Table *pTarget = sqlite3FindTable(db, pStep->zTarget, zDb);
  1278   1278           if( pTarget==0 ){
  1279   1279             rc = SQLITE_ERROR;
  1280   1280           }else{
  1281   1281             SrcList sSrc;
  1282   1282             memset(&sSrc, 0, sizeof(sSrc));
  1283   1283             sSrc.nSrc = 1;
................................................................................
  1286   1286             sNC.pSrcList = &sSrc;
  1287   1287             if( pStep->pWhere ){
  1288   1288               rc = sqlite3ResolveExprNames(&sNC, pStep->pWhere);
  1289   1289             }
  1290   1290             if( rc==SQLITE_OK ){
  1291   1291               rc = sqlite3ResolveExprListNames(&sNC, pStep->pExprList);
  1292   1292             }
         1293  +          assert( !pStep->pUpsert || (!pStep->pWhere && !pStep->pExprList) );
  1293   1294             if( pStep->pUpsert ){
  1294   1295               Upsert *pUpsert = pStep->pUpsert;
  1295         -            if( rc==SQLITE_OK ){
  1296         -              rc = sqlite3ResolveExprListNames(&sNC, pUpsert->pUpsertTarget);
  1297         -            }
         1296  +            assert( rc==SQLITE_OK );
         1297  +            rc = sqlite3ResolveExprListNames(&sNC, pUpsert->pUpsertTarget);
  1298   1298               if( rc==SQLITE_OK && pUpsert->pUpsertSet){
  1299   1299                 ExprList *pUpsertSet = pUpsert->pUpsertSet;
  1300   1300                 rc = sqlite3ResolveExprListNames(&sNC, pUpsertSet);
  1301   1301                 if( rc==SQLITE_OK && pTarget==pTab ){
  1302   1302                   for(i=0; i<pUpsertSet->nExpr; i++){
  1303   1303                     char *zName = pUpsertSet->a[i].zName;
  1304   1304                     if( 0==sqlite3_stricmp(zName, zOld) ){
................................................................................
  1409   1409     }else{
  1410   1410       rc = SQLITE_NOMEM;
  1411   1411     }
  1412   1412   
  1413   1413   renameColumnFunc_done:
  1414   1414     if( rc!=SQLITE_OK ){
  1415   1415       if( sParse.zErrMsg ){
  1416         -      renameColumnParseError(context, &sParse);
         1416  +      renameColumnParseError(context, argv[1], argv[2], &sParse);
  1417   1417       }else{
  1418   1418         sqlite3_result_error_code(context, rc);
  1419   1419       }
  1420   1420     }
  1421   1421   
  1422   1422     if( sParse.pVdbe ){
  1423   1423       sqlite3VdbeFinalize(sParse.pVdbe);
................................................................................
  1434   1434   
  1435   1435   /*
  1436   1436   ** Register built-in functions used to help implement ALTER TABLE
  1437   1437   */
  1438   1438   void sqlite3AlterFunctions(void){
  1439   1439     static FuncDef aAlterTableFuncs[] = {
  1440   1440       FUNCTION(sqlite_rename_table,   2, 0, 0, renameTableFunc),
  1441         -    FUNCTION(sqlite_rename_column,   6, 0, 0, renameColumnFunc),
         1441  +    FUNCTION(sqlite_rename_column,   8, 0, 0, renameColumnFunc),
  1442   1442   #ifndef SQLITE_OMIT_TRIGGER
  1443   1443       FUNCTION(sqlite_rename_trigger, 2, 0, 0, renameTriggerFunc),
  1444   1444   #endif
  1445   1445   #ifndef SQLITE_OMIT_FOREIGN_KEY
  1446   1446       FUNCTION(sqlite_rename_parent,  3, 0, 0, renameParentFunc),
  1447   1447   #endif
  1448   1448     };
  1449   1449     sqlite3InsertBuiltinFuncs(aAlterTableFuncs, ArraySize(aAlterTableFuncs));
  1450   1450   }
  1451   1451   #endif  /* SQLITE_ALTER_TABLE */

Changes to test/altercol.test.

   401    401     4 _x_ _xxx_ {
   402    402       { CREATE TABLE t1(a, b, _x_ INTEGER, PRIMARY KEY(_x_), CHECK(_x_>0)) }
   403    403       { CREATE TRIGGER ttt AFTER UPDATE  ON t1 BEGIN
   404    404           INSERT INTO t1 VALUES(new.a, new.b, new._x_)
   405    405             ON CONFLICT (_x_) WHERE _x_>10 DO UPDATE SET _x_ = _x_+1;
   406    406         END }
   407    407     }
          408  +
          409  +  4 _x_ _xxx_ {
          410  +    { CREATE TABLE t1(a, b, _x_ INTEGER, PRIMARY KEY(_x_), CHECK(_x_>0)) }
          411  +    { CREATE TRIGGER ttt AFTER UPDATE  ON t1 BEGIN
          412  +        INSERT INTO t1 VALUES(new.a, new.b, new._x_)
          413  +          ON CONFLICT (_x_) WHERE _x_>10 DO NOTHING;
          414  +      END }
          415  +  }
   408    416   } {
   409    417     do_rename_column_test 9.$tn $old $new $lSchema
   410    418   }
   411    419   
   412    420   #-------------------------------------------------------------------------
   413    421   # Test that views can be edited even if there are missing collation 
   414    422   # sequences or user defined functions.
................................................................................
   468    476   
   469    477   #-------------------------------------------------------------------------
   470    478   # Test some error conditions:
   471    479   #
   472    480   #   1. Renaming a column of a system table,
   473    481   #   2. Renaming a column of a VIEW,
   474    482   #   3. Renaming a column of a virtual table.
          483  +#   4. Renaming a column that does not exist.
          484  +#   5. Renaming a column of a table that does not exist.
   475    485   #
   476    486   reset_db
   477    487   do_execsql_test 12.1.1 {
   478    488     CREATE TABLE t1(a, b);
   479    489     CREATE INDEX t1a ON t1(a);
   480    490     INSERT INTO t1 VALUES(1, 1), (2, 2), (3, 4);
   481    491     ANALYZE;
................................................................................
   498    508     ALTER TABLE v2 RENAME c TO y;
   499    509   } {1 {columns of view v2 may not be renamed}}
   500    510   
   501    511   ifcapable fts5 {
   502    512     do_execsql_test 12.3.1 {
   503    513       CREATE VIRTUAL TABLE ft USING fts5(a, b, c);
   504    514     }
   505         -  do_catchsql_test 12.2.2 {
          515  +  do_catchsql_test 12.3.2 {
   506    516       ALTER TABLE ft RENAME a TO z;
   507    517     } {1 {columns of virtual table ft may not be renamed}}
   508    518   }
          519  +
          520  +do_execsql_test 12.4.1 {
          521  +  CREATE TABLE t2(x, y, z);
          522  +}
          523  +do_catchsql_test 12.4.2 {
          524  +  ALTER TABLE t2 RENAME COLUMN a TO b;
          525  +} {1 {no such column: "a"}}
          526  +
          527  +do_catchsql_test 12.5.1 {
          528  +  ALTER TABLE t3 RENAME COLUMN a TO b;
          529  +} {1 {no such table: t3}}
          530  +
          531  +#-------------------------------------------------------------------------
          532  +# Test the effect of some parse/resolve errors.
          533  +#
          534  +reset_db
          535  +do_execsql_test 13.1.1 {
          536  +  CREATE TABLE x1(i INTEGER, t TEXT UNIQUE);
          537  +  CREATE TRIGGER tr1 AFTER INSERT ON x1 BEGIN
          538  +    SELECT * FROM nosuchtable;
          539  +  END;
          540  +}
          541  +
          542  +do_catchsql_test 13.1.2 {
          543  +  ALTER TABLE x1 RENAME COLUMN t TO ttt;
          544  +} {1 {error processing trigger tr1: no such table: main.nosuchtable}}
          545  +
          546  +do_execsql_test 13.1.3 {
          547  +  DROP TRIGGER tr1;
          548  +  CREATE INDEX x1i ON x1(i);
          549  +  SELECT sql FROM sqlite_master WHERE name='x1i';
          550  +} {{CREATE INDEX x1i ON x1(i)}}
          551  +
          552  +do_execsql_test 13.1.4 {
          553  +  PRAGMA writable_schema = 1;
          554  +  UPDATE sqlite_master SET sql = 'CREATE INDEX x1i ON x1(j)' WHERE name='x1i';
          555  +} {}
          556  +
          557  +do_catchsql_test 13.1.5 {
          558  +  ALTER TABLE x1 RENAME COLUMN t TO ttt;
          559  +} {1 {error processing index x1i: no such column: j}}
          560  +
          561  +do_execsql_test 13.1.6 {
          562  +  UPDATE sqlite_master SET sql = '' WHERE name='x1i';
          563  +} {}
          564  +
          565  +do_catchsql_test 13.1.7 {
          566  +  ALTER TABLE x1 RENAME COLUMN t TO ttt;
          567  +} {1 {database disk image is malformed}}
          568  +
          569  +do_execsql_test 13.1.8 {
          570  +  DELETE FROM sqlite_master WHERE name = 'x1i';
          571  +}
          572  +
          573  +do_execsql_test 13.2.0 {
          574  +  CREATE TABLE data(x UNIQUE, y, z);
          575  +}
          576  +foreach {tn trigger error} {
          577  +  1 {
          578  +    CREATE TRIGGER tr1 AFTER INSERT ON x1 BEGIN
          579  +      UPDATE data SET x=x+1 WHERE zzz=new.i;
          580  +    END;
          581  +  } {no such column: zzz}
          582  +
          583  +  2 {
          584  +    CREATE TRIGGER tr1 AFTER INSERT ON x1 BEGIN
          585  +      INSERT INTO data(x, y) VALUES(new.i, new.t, 1) 
          586  +        ON CONFLICT (x) DO UPDATE SET z=zz+1;
          587  +    END;
          588  +  } {no such column: zz}
          589  +
          590  +  3 {
          591  +    CREATE TRIGGER tr1 AFTER INSERT ON x1 BEGIN
          592  +      INSERT INTO x1(i, t) VALUES(new.i+1, new.t||'1') 
          593  +        ON CONFLICT (tttttt) DO UPDATE SET t=i+1;
          594  +    END;
          595  +  } {no such column: tttttt}
          596  +
          597  +
          598  +} {
          599  +  do_execsql_test 13.2.$tn.1 "
          600  +    DROP TRIGGER IF EXISTS tr1;
          601  +    $trigger
          602  +  "
          603  +
          604  +  do_catchsql_test 13.2.$tn.2 {
          605  +    ALTER TABLE x1 RENAME COLUMN t TO ttt;
          606  +  } "1 {error processing trigger tr1: $error}"
          607  +}
          608  +
          609  +#-------------------------------------------------------------------------
          610  +# Passing invalid parameters directly to sqlite_rename_column().
          611  +#
          612  +do_execsql_test 14.1 {
          613  +  CREATE TABLE ddd(sql, type, object, db, tbl, icol, znew, bquote);
          614  +  INSERT INTO ddd VALUES(
          615  +      'CREATE TABLE x1(i INTEGER, t TEXT)',
          616  +      'table', 'x1', 'main', 'x1', -1, 'zzz', 0
          617  +  ), (
          618  +      'CREATE TABLE x1(i INTEGER, t TEXT)',
          619  +      'table', 'x1', 'main', 'x1', 2, 'zzz', 0
          620  +  ), (
          621  +      'CREATE TABLE x1(i INTEGER, t TEXT)',
          622  +      'table', 'x1', 'main', 'notable', 0, 'zzz', 0
          623  +  );
          624  +} {}
          625  +
          626  +do_execsql_test 14.2 {
          627  +  SELECT 
          628  +  sqlite_rename_column(sql, type, object, db, tbl, icol, znew, bquote)
          629  +  FROM ddd;
          630  +} {{} {} {}}
          631  +
   509    632   
   510    633   finish_test
   511    634   

Added test/altermalloc2.test.

            1  +# 2018 August 20
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#*************************************************************************
           11  +#
           12  +
           13  +set testdir [file dirname $argv0]
           14  +source $testdir/tester.tcl
           15  +source $testdir/malloc_common.tcl
           16  +set testprefix altermalloc2
           17  +
           18  +# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
           19  +ifcapable !altertable {
           20  +  finish_test
           21  +  return
           22  +}
           23  +
           24  +do_execsql_test 1.0 {
           25  +  CREATE TABLE t1(abcd, efgh);
           26  +}
           27  +faultsim_save_and_close
           28  +
           29  +do_faultsim_test 1 -prep {
           30  +  faultsim_restore_and_reopen
           31  +} -body {
           32  +  execsql {
           33  +    ALTER TABLE t1 RENAME abcd TO dcba
           34  +  }
           35  +} -test {
           36  +  faultsim_test_result {0 {}}
           37  +}
           38  +
           39  +catch {db close}
           40  +forcedelete test.db
           41  +sqlite3 db test.db
           42  +do_execsql_test 2.0 {
           43  +  PRAGMA encoding = 'utf-16';
           44  +  CREATE TABLE t1(abcd, efgh);
           45  +}
           46  +faultsim_save_and_close
           47  +
           48  +do_faultsim_test 2 -prep {
           49  +  faultsim_restore_and_reopen
           50  +} -body {
           51  +  execsql {
           52  +    ALTER TABLE t1 RENAME abcd TO dcba
           53  +  }
           54  +} -test {
           55  +  faultsim_test_result {0 {}}
           56  +}
           57  +
           58  +
           59  +reset_db
           60  +do_execsql_test 3.0 {
           61  +  CREATE TABLE t1(abcd, efgh);
           62  +  CREATE VIEW v1 AS SELECT * FROM t1 WHERE abcd>efgh;
           63  +}
           64  +faultsim_save_and_close
           65  +
           66  +do_faultsim_test 3 -prep {
           67  +  faultsim_restore_and_reopen
           68  +} -body {
           69  +  execsql {
           70  +    ALTER TABLE t1 RENAME abcd TO dcba
           71  +  }
           72  +} -test {
           73  +  faultsim_test_result {0 {}}
           74  +}
           75  +finish_test