/ Check-in [191079bd]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:When a column is renamed, update any references to it in REFERENCES clauses that belong to other tables.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | alter-table-rename-column
Files: files | file ages | folders
SHA3-256: 191079bd4f774528a1e39b71b4a4ce7859dee0edf0ce72e4b6968ae889dc35c8
User & Date: dan 2018-08-10 19:19:33
Context
2018-08-10
19:33
Fix harmless compiler warnings. check-in: 9564d700 user: drh tags: alter-table-rename-column
19:19
When a column is renamed, update any references to it in REFERENCES clauses that belong to other tables. check-in: 191079bd user: dan tags: alter-table-rename-column
15:27
Merge latest trunk changes with this branch. check-in: c355a837 user: dan tags: alter-table-rename-column
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/alter.c.

   821    821     }
   822    822   
   823    823     zNew = sqlite3NameFromToken(db, pNew);
   824    824     if( !zNew ) goto exit_rename_column;
   825    825   
   826    826     sqlite3NestedParse(pParse, 
   827    827         "UPDATE \"%w\".%s SET "
   828         -      "sql = sqlite_rename_column(sql, %d, %Q) "
   829         -      "WHERE type IN ('table', 'index') AND tbl_name = %Q AND sql!=''",
   830         -      zDb, MASTER_NAME, iCol, zNew, pTab->zName
          828  +      "sql = sqlite_rename_column(sql, %d, %Q, %Q, %Q) "
          829  +      "WHERE type = 'table' OR (type='index' AND tbl_name = %Q AND sql!='')",
          830  +      zDb, MASTER_NAME, iCol, zNew, pTab->zName, zOld, pTab->zName
   831    831     );
   832    832   
   833    833     /* Drop and reload the internal table schema. */
   834    834     reloadTableSchema(pParse, pTab, pTab->zName);
   835    835   
   836    836    exit_rename_column:
   837    837     sqlite3SrcListDelete(db, pSrc);
................................................................................
   919    919     }
   920    920     for(pp=&pCtx->pList; *pp!=pBest; pp=&(*pp)->pNext);
   921    921     *pp = pBest->pNext;
   922    922   
   923    923     return pBest;
   924    924   }
   925    925   
          926  +/*
          927  +** sqlite_rename_table(SQL, iCol, zNew, zTable, zOld)
          928  +*/
   926    929   static void renameColumnFunc(
   927    930     sqlite3_context *context,
   928    931     int NotUsed,
   929    932     sqlite3_value **argv
   930    933   ){
   931    934     sqlite3 *db = sqlite3_context_db_handle(context);
   932    935     struct RenameCtx sCtx;
   933    936     const char *zSql = sqlite3_value_text(argv[0]);
   934    937     int nSql = sqlite3_value_bytes(argv[0]);
   935    938     const char *zNew = sqlite3_value_text(argv[2]);
   936    939     int nNew = sqlite3_value_bytes(argv[2]);
          940  +  const char *zTable = sqlite3_value_text(argv[3]);
          941  +  int nTable = sqlite3_value_bytes(argv[3]);
          942  +  const char *zOld = sqlite3_value_text(argv[4]);
          943  +  int nOld = sqlite3_value_bytes(argv[4]);
          944  +
   937    945     int rc;
   938    946     char *zErr = 0;
   939    947     Parse sParse;
   940    948     Walker sWalker;
   941    949     Table *pTab;
   942    950     Index *pIdx;
   943    951     char *zOut = 0;
................................................................................
  1000   1008     /* Find tokens that need to be replaced. */
  1001   1009     memset(&sWalker, 0, sizeof(Walker));
  1002   1010     sWalker.pParse = &sParse;
  1003   1011     sWalker.xExprCallback = renameColumnExprCb;
  1004   1012     sWalker.u.pRename = &sCtx;
  1005   1013   
  1006   1014     if( sParse.pNewTable ){
         1015  +    int bFKOnly = sqlite3_stricmp(zTable, sParse.pNewTable->zName);
  1007   1016       FKey *pFKey;
  1008         -    sCtx.pList = renameTokenFind(
  1009         -        &sParse, (void*)sParse.pNewTable->aCol[sCtx.iCol].zName
  1010         -    );
  1011         -    sCtx.nList = 1;
  1012         -    sqlite3WalkExprList(&sWalker, sParse.pNewTable->pCheck);
  1013         -    for(pIdx=sParse.pNewTable->pIndex; pIdx; pIdx=pIdx->pNext){
  1014         -      sqlite3WalkExprList(&sWalker, pIdx->aColExpr);
         1017  +    if( bFKOnly==0 ){
         1018  +      sCtx.pList = renameTokenFind(
         1019  +          &sParse, (void*)sParse.pNewTable->aCol[sCtx.iCol].zName
         1020  +      );
         1021  +      sCtx.nList = 1;
         1022  +      sqlite3WalkExprList(&sWalker, sParse.pNewTable->pCheck);
         1023  +      for(pIdx=sParse.pNewTable->pIndex; pIdx; pIdx=pIdx->pNext){
         1024  +        sqlite3WalkExprList(&sWalker, pIdx->aColExpr);
         1025  +      }
  1015   1026       }
  1016   1027   
  1017   1028       for(pFKey=sParse.pNewTable->pFKey; pFKey; pFKey=pFKey->pNextFrom){
  1018   1029         for(i=0; i<pFKey->nCol; i++){
  1019         -        if( pFKey->aCol[i].iFrom==sCtx.iCol ){
  1020         -          RenameToken *pTok = renameTokenFind(&sParse, (void*)&pFKey->aCol[i]);
         1030  +        RenameToken *pTok = 0;
         1031  +        if( bFKOnly==0 && pFKey->aCol[i].iFrom==sCtx.iCol ){
         1032  +          pTok = renameTokenFind(&sParse, (void*)&pFKey->aCol[i]);
  1021   1033             if( pTok ){
  1022   1034               pTok->pNext = sCtx.pList;
  1023   1035               sCtx.pList = pTok;
  1024   1036               sCtx.nList++;
  1025   1037             }
         1038  +        }
         1039  +        if( 0==sqlite3_stricmp(pFKey->zTo, zTable)
         1040  +         && 0==sqlite3_stricmp(pFKey->aCol[i].zCol, zOld)
         1041  +        ){
         1042  +          pTok = renameTokenFind(&sParse, (void*)pFKey->aCol[i].zCol);
         1043  +          pTok->pNext = sCtx.pList;
         1044  +          sCtx.pList = pTok;
         1045  +          sCtx.nList++;
  1026   1046           }
  1027   1047         }
  1028   1048       }
  1029   1049     }else{
  1030   1050       sqlite3WalkExprList(&sWalker, sParse.pNewIndex->aColExpr);
  1031   1051       sqlite3WalkExpr(&sWalker, sParse.pNewIndex->pPartIdxWhere);
  1032   1052     }
................................................................................
  1077   1097   
  1078   1098   /*
  1079   1099   ** Register built-in functions used to help implement ALTER TABLE
  1080   1100   */
  1081   1101   void sqlite3AlterFunctions(void){
  1082   1102     static FuncDef aAlterTableFuncs[] = {
  1083   1103       FUNCTION(sqlite_rename_table,   2, 0, 0, renameTableFunc),
  1084         -    FUNCTION(sqlite_rename_column,   3, 0, 0, renameColumnFunc),
         1104  +    FUNCTION(sqlite_rename_column,   5, 0, 0, renameColumnFunc),
  1085   1105   #ifndef SQLITE_OMIT_TRIGGER
  1086   1106       FUNCTION(sqlite_rename_trigger, 2, 0, 0, renameTriggerFunc),
  1087   1107   #endif
  1088   1108   #ifndef SQLITE_OMIT_FOREIGN_KEY
  1089   1109       FUNCTION(sqlite_rename_parent,  3, 0, 0, renameParentFunc),
  1090   1110   #endif
  1091   1111     };
  1092   1112     sqlite3InsertBuiltinFuncs(aAlterTableFuncs, ArraySize(aAlterTableFuncs));
  1093   1113   }
  1094   1114   #endif  /* SQLITE_ALTER_TABLE */

Changes to src/build.c.

  2767   2767         }
  2768   2768       }
  2769   2769     }
  2770   2770     if( pToCol ){
  2771   2771       for(i=0; i<nCol; i++){
  2772   2772         int n = sqlite3Strlen30(pToCol->a[i].zName);
  2773   2773         pFKey->aCol[i].zCol = z;
         2774  +      if( IN_RENAME_COLUMN ){
         2775  +        sqlite3MoveRenameToken(pParse, z, pToCol->a[i].zName);
         2776  +      }
  2774   2777         memcpy(z, pToCol->a[i].zName, n);
  2775   2778         z[n] = 0;
  2776   2779         z += n+1;
  2777   2780       }
  2778   2781     }
  2779   2782     pFKey->isDeferred = 0;
  2780   2783     pFKey->aAction[0] = (u8)(flags & 0xff);            /* ON DELETE action */

Changes to test/altercol.test.

    89     89     }
    90     90     do_execsql_test 1.$tn.4 {
    91     91       SELECT sql FROM sqlite_master WHERE tbl_name='t1' AND sql!=''
    92     92     } $res
    93     93   }
    94     94   
    95     95   #-------------------------------------------------------------------------
           96  +#
    96     97   do_execsql_test 2.0 {
    97     98     CREATE TABLE t3(a, b, c, d, e, f, g, h, i, j, k, l, m, FOREIGN KEY (b, c, d, e, f, g, h, i, j, k, l, m) REFERENCES t4);
    98     99   }
    99    100   
   100    101   do_execsql_test 2.1 {
   101    102     ALTER TABLE t3 RENAME b TO biglongname;
   102    103     SELECT sql FROM sqlite_master WHERE name='t3';
   103    104   } {{CREATE TABLE t3(a, biglongname, c, d, e, f, g, h, i, j, k, l, m, FOREIGN KEY (biglongname, c, d, e, f, g, h, i, j, k, l, m) REFERENCES t4)}}
   104    105   
   105         -finish_test
          106  +
          107  +#-------------------------------------------------------------------------
          108  +#
          109  +do_execsql_test 3.0 {
          110  +  CREATE TABLE t4(x, y, z);
          111  +  CREATE TRIGGER ttt AFTER INSERT ON t4 WHEN new.y<0 BEGIN
          112  +    SELECT 1, 2, 3, 4;
          113  +  END;
          114  +  INSERT INTO t4 VALUES(3, 2, 1);
          115  +}
          116  +
          117  +do_execsql_test 3.1 {
          118  +  ALTER TABLE t4 RENAME y TO abc;
          119  +  SELECT sql FROM sqlite_master WHERE name='t4';
          120  +} {{CREATE TABLE t4(x, abc, z)}}
          121  +
          122  +db close
          123  +sqlite3 db test.db
          124  +
          125  +do_execsql_test 3.2 {
          126  +  SELECT * FROM t4;
          127  +} {3 2 1}
          128  +
          129  +# do_execsql_test 3.3 { INSERT INTO t4 VALUES(6, 5, 4); } {}
          130  +
          131  +#-------------------------------------------------------------------------
          132  +#
          133  +do_execsql_test 4.0 {
          134  +  CREATE TABLE c1(a, b, FOREIGN KEY (a, b) REFERENCES p1(c, d));
          135  +  CREATE TABLE p1(c, d, PRIMARY KEY(c, d));
          136  +  PRAGMA foreign_keys = 1;
          137  +  INSERT INTO p1 VALUES(1, 2);
          138  +  INSERT INTO p1 VALUES(3, 4);
          139  +}
          140  +
          141  +do_execsql_test 4.1 {
          142  +  ALTER TABLE p1 RENAME d TO "silly name";
          143  +  SELECT sql FROM sqlite_master WHERE name IN ('c1', 'p1');
          144  +} {
          145  +  {CREATE TABLE c1(a, b, FOREIGN KEY (a, b) REFERENCES p1(c, "silly name"))}
          146  +  {CREATE TABLE p1(c, "silly name", PRIMARY KEY(c, "silly name"))}
          147  +}
          148  +
          149  +do_execsql_test 4.2 {
          150  +  CREATE TABLE c2(a, b, FOREIGN KEY (a, b) REFERENCES p1);
          151  +}
          152  +
          153  +do_execsql_test 4.1 {
          154  +  ALTER TABLE p1 RENAME "silly name" TO reasonable;
          155  +  SELECT sql FROM sqlite_master WHERE name IN ('c1', 'c2', 'p1');
          156  +} {
          157  +  {CREATE TABLE c1(a, b, FOREIGN KEY (a, b) REFERENCES p1(c, "reasonable"))}
          158  +  {CREATE TABLE p1(c, "reasonable", PRIMARY KEY(c, "reasonable"))}
          159  +  {CREATE TABLE c2(a, b, FOREIGN KEY (a, b) REFERENCES p1)}
          160  +}
   106    161   
          162  +finish_test
   107    163