SQLite

Check-in [191079bd4f]
Login

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
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.246
Context
2018-08-10
19:33
Fix harmless compiler warnings. (check-in: 9564d7008c 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: 191079bd4f user: dan tags: alter-table-rename-column)
15:27
Merge latest trunk changes with this branch. (check-in: c355a83707 user: dan tags: alter-table-rename-column)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/alter.c.
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
  }

  zNew = sqlite3NameFromToken(db, pNew);
  if( !zNew ) goto exit_rename_column;

  sqlite3NestedParse(pParse, 
      "UPDATE \"%w\".%s SET "
      "sql = sqlite_rename_column(sql, %d, %Q) "
      "WHERE type IN ('table', 'index') AND tbl_name = %Q AND sql!=''",
      zDb, MASTER_NAME, iCol, zNew, pTab->zName
  );

  /* Drop and reload the internal table schema. */
  reloadTableSchema(pParse, pTab, pTab->zName);

 exit_rename_column:
  sqlite3SrcListDelete(db, pSrc);







|
|
|







821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
  }

  zNew = sqlite3NameFromToken(db, pNew);
  if( !zNew ) goto exit_rename_column;

  sqlite3NestedParse(pParse, 
      "UPDATE \"%w\".%s SET "
      "sql = sqlite_rename_column(sql, %d, %Q, %Q, %Q) "
      "WHERE type = 'table' OR (type='index' AND tbl_name = %Q AND sql!='')",
      zDb, MASTER_NAME, iCol, zNew, pTab->zName, zOld, pTab->zName
  );

  /* Drop and reload the internal table schema. */
  reloadTableSchema(pParse, pTab, pTab->zName);

 exit_rename_column:
  sqlite3SrcListDelete(db, pSrc);
919
920
921
922
923
924
925



926
927
928
929
930
931
932
933
934
935
936





937
938
939
940
941
942
943
  }
  for(pp=&pCtx->pList; *pp!=pBest; pp=&(*pp)->pNext);
  *pp = pBest->pNext;

  return pBest;
}




static void renameColumnFunc(
  sqlite3_context *context,
  int NotUsed,
  sqlite3_value **argv
){
  sqlite3 *db = sqlite3_context_db_handle(context);
  struct RenameCtx sCtx;
  const char *zSql = sqlite3_value_text(argv[0]);
  int nSql = sqlite3_value_bytes(argv[0]);
  const char *zNew = sqlite3_value_text(argv[2]);
  int nNew = sqlite3_value_bytes(argv[2]);





  int rc;
  char *zErr = 0;
  Parse sParse;
  Walker sWalker;
  Table *pTab;
  Index *pIdx;
  char *zOut = 0;







>
>
>











>
>
>
>
>







919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
  }
  for(pp=&pCtx->pList; *pp!=pBest; pp=&(*pp)->pNext);
  *pp = pBest->pNext;

  return pBest;
}

/*
** sqlite_rename_table(SQL, iCol, zNew, zTable, zOld)
*/
static void renameColumnFunc(
  sqlite3_context *context,
  int NotUsed,
  sqlite3_value **argv
){
  sqlite3 *db = sqlite3_context_db_handle(context);
  struct RenameCtx sCtx;
  const char *zSql = sqlite3_value_text(argv[0]);
  int nSql = sqlite3_value_bytes(argv[0]);
  const char *zNew = sqlite3_value_text(argv[2]);
  int nNew = sqlite3_value_bytes(argv[2]);
  const char *zTable = sqlite3_value_text(argv[3]);
  int nTable = sqlite3_value_bytes(argv[3]);
  const char *zOld = sqlite3_value_text(argv[4]);
  int nOld = sqlite3_value_bytes(argv[4]);

  int rc;
  char *zErr = 0;
  Parse sParse;
  Walker sWalker;
  Table *pTab;
  Index *pIdx;
  char *zOut = 0;
1000
1001
1002
1003
1004
1005
1006

1007

1008
1009
1010
1011
1012
1013
1014

1015
1016
1017
1018

1019
1020
1021
1022
1023
1024
1025








1026
1027
1028
1029
1030
1031
1032
  /* Find tokens that need to be replaced. */
  memset(&sWalker, 0, sizeof(Walker));
  sWalker.pParse = &sParse;
  sWalker.xExprCallback = renameColumnExprCb;
  sWalker.u.pRename = &sCtx;

  if( sParse.pNewTable ){

    FKey *pFKey;

    sCtx.pList = renameTokenFind(
        &sParse, (void*)sParse.pNewTable->aCol[sCtx.iCol].zName
    );
    sCtx.nList = 1;
    sqlite3WalkExprList(&sWalker, sParse.pNewTable->pCheck);
    for(pIdx=sParse.pNewTable->pIndex; pIdx; pIdx=pIdx->pNext){
      sqlite3WalkExprList(&sWalker, pIdx->aColExpr);

    }

    for(pFKey=sParse.pNewTable->pFKey; pFKey; pFKey=pFKey->pNextFrom){
      for(i=0; i<pFKey->nCol; i++){

        if( pFKey->aCol[i].iFrom==sCtx.iCol ){
          RenameToken *pTok = renameTokenFind(&sParse, (void*)&pFKey->aCol[i]);
          if( pTok ){
            pTok->pNext = sCtx.pList;
            sCtx.pList = pTok;
            sCtx.nList++;
          }








        }
      }
    }
  }else{
    sqlite3WalkExprList(&sWalker, sParse.pNewIndex->aColExpr);
    sqlite3WalkExpr(&sWalker, sParse.pNewIndex->pPartIdxWhere);
  }







>

>
|
|
|
|
|
|
|
>




>
|
|





>
>
>
>
>
>
>
>







1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
  /* Find tokens that need to be replaced. */
  memset(&sWalker, 0, sizeof(Walker));
  sWalker.pParse = &sParse;
  sWalker.xExprCallback = renameColumnExprCb;
  sWalker.u.pRename = &sCtx;

  if( sParse.pNewTable ){
    int bFKOnly = sqlite3_stricmp(zTable, sParse.pNewTable->zName);
    FKey *pFKey;
    if( bFKOnly==0 ){
      sCtx.pList = renameTokenFind(
          &sParse, (void*)sParse.pNewTable->aCol[sCtx.iCol].zName
      );
      sCtx.nList = 1;
      sqlite3WalkExprList(&sWalker, sParse.pNewTable->pCheck);
      for(pIdx=sParse.pNewTable->pIndex; pIdx; pIdx=pIdx->pNext){
        sqlite3WalkExprList(&sWalker, pIdx->aColExpr);
      }
    }

    for(pFKey=sParse.pNewTable->pFKey; pFKey; pFKey=pFKey->pNextFrom){
      for(i=0; i<pFKey->nCol; i++){
        RenameToken *pTok = 0;
        if( bFKOnly==0 && pFKey->aCol[i].iFrom==sCtx.iCol ){
          pTok = renameTokenFind(&sParse, (void*)&pFKey->aCol[i]);
          if( pTok ){
            pTok->pNext = sCtx.pList;
            sCtx.pList = pTok;
            sCtx.nList++;
          }
        }
        if( 0==sqlite3_stricmp(pFKey->zTo, zTable)
         && 0==sqlite3_stricmp(pFKey->aCol[i].zCol, zOld)
        ){
          pTok = renameTokenFind(&sParse, (void*)pFKey->aCol[i].zCol);
          pTok->pNext = sCtx.pList;
          sCtx.pList = pTok;
          sCtx.nList++;
        }
      }
    }
  }else{
    sqlite3WalkExprList(&sWalker, sParse.pNewIndex->aColExpr);
    sqlite3WalkExpr(&sWalker, sParse.pNewIndex->pPartIdxWhere);
  }
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094

/*
** Register built-in functions used to help implement ALTER TABLE
*/
void sqlite3AlterFunctions(void){
  static FuncDef aAlterTableFuncs[] = {
    FUNCTION(sqlite_rename_table,   2, 0, 0, renameTableFunc),
    FUNCTION(sqlite_rename_column,   3, 0, 0, renameColumnFunc),
#ifndef SQLITE_OMIT_TRIGGER
    FUNCTION(sqlite_rename_trigger, 2, 0, 0, renameTriggerFunc),
#endif
#ifndef SQLITE_OMIT_FOREIGN_KEY
    FUNCTION(sqlite_rename_parent,  3, 0, 0, renameParentFunc),
#endif
  };
  sqlite3InsertBuiltinFuncs(aAlterTableFuncs, ArraySize(aAlterTableFuncs));
}
#endif  /* SQLITE_ALTER_TABLE */







|










1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114

/*
** Register built-in functions used to help implement ALTER TABLE
*/
void sqlite3AlterFunctions(void){
  static FuncDef aAlterTableFuncs[] = {
    FUNCTION(sqlite_rename_table,   2, 0, 0, renameTableFunc),
    FUNCTION(sqlite_rename_column,   5, 0, 0, renameColumnFunc),
#ifndef SQLITE_OMIT_TRIGGER
    FUNCTION(sqlite_rename_trigger, 2, 0, 0, renameTriggerFunc),
#endif
#ifndef SQLITE_OMIT_FOREIGN_KEY
    FUNCTION(sqlite_rename_parent,  3, 0, 0, renameParentFunc),
#endif
  };
  sqlite3InsertBuiltinFuncs(aAlterTableFuncs, ArraySize(aAlterTableFuncs));
}
#endif  /* SQLITE_ALTER_TABLE */
Changes to src/build.c.
2767
2768
2769
2770
2771
2772
2773



2774
2775
2776
2777
2778
2779
2780
      }
    }
  }
  if( pToCol ){
    for(i=0; i<nCol; i++){
      int n = sqlite3Strlen30(pToCol->a[i].zName);
      pFKey->aCol[i].zCol = z;



      memcpy(z, pToCol->a[i].zName, n);
      z[n] = 0;
      z += n+1;
    }
  }
  pFKey->isDeferred = 0;
  pFKey->aAction[0] = (u8)(flags & 0xff);            /* ON DELETE action */







>
>
>







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

96
97
98
99
100
101
102
103
104
105

106






















































107
  }
  do_execsql_test 1.$tn.4 {
    SELECT sql FROM sqlite_master WHERE tbl_name='t1' AND sql!=''
  } $res
}

#-------------------------------------------------------------------------

do_execsql_test 2.0 {
  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);
}

do_execsql_test 2.1 {
  ALTER TABLE t3 RENAME b TO biglongname;
  SELECT sql FROM sqlite_master WHERE name='t3';
} {{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)}}

finish_test
































































>









|
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
  }
  do_execsql_test 1.$tn.4 {
    SELECT sql FROM sqlite_master WHERE tbl_name='t1' AND sql!=''
  } $res
}

#-------------------------------------------------------------------------
#
do_execsql_test 2.0 {
  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);
}

do_execsql_test 2.1 {
  ALTER TABLE t3 RENAME b TO biglongname;
  SELECT sql FROM sqlite_master WHERE name='t3';
} {{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)}}


#-------------------------------------------------------------------------
#
do_execsql_test 3.0 {
  CREATE TABLE t4(x, y, z);
  CREATE TRIGGER ttt AFTER INSERT ON t4 WHEN new.y<0 BEGIN
    SELECT 1, 2, 3, 4;
  END;
  INSERT INTO t4 VALUES(3, 2, 1);
}

do_execsql_test 3.1 {
  ALTER TABLE t4 RENAME y TO abc;
  SELECT sql FROM sqlite_master WHERE name='t4';
} {{CREATE TABLE t4(x, abc, z)}}

db close
sqlite3 db test.db

do_execsql_test 3.2 {
  SELECT * FROM t4;
} {3 2 1}

# do_execsql_test 3.3 { INSERT INTO t4 VALUES(6, 5, 4); } {}

#-------------------------------------------------------------------------
#
do_execsql_test 4.0 {
  CREATE TABLE c1(a, b, FOREIGN KEY (a, b) REFERENCES p1(c, d));
  CREATE TABLE p1(c, d, PRIMARY KEY(c, d));
  PRAGMA foreign_keys = 1;
  INSERT INTO p1 VALUES(1, 2);
  INSERT INTO p1 VALUES(3, 4);
}

do_execsql_test 4.1 {
  ALTER TABLE p1 RENAME d TO "silly name";
  SELECT sql FROM sqlite_master WHERE name IN ('c1', 'p1');
} {
  {CREATE TABLE c1(a, b, FOREIGN KEY (a, b) REFERENCES p1(c, "silly name"))}
  {CREATE TABLE p1(c, "silly name", PRIMARY KEY(c, "silly name"))}
}

do_execsql_test 4.2 {
  CREATE TABLE c2(a, b, FOREIGN KEY (a, b) REFERENCES p1);
}

do_execsql_test 4.1 {
  ALTER TABLE p1 RENAME "silly name" TO reasonable;
  SELECT sql FROM sqlite_master WHERE name IN ('c1', 'c2', 'p1');
} {
  {CREATE TABLE c1(a, b, FOREIGN KEY (a, b) REFERENCES p1(c, "reasonable"))}
  {CREATE TABLE p1(c, "reasonable", PRIMARY KEY(c, "reasonable"))}
  {CREATE TABLE c2(a, b, FOREIGN KEY (a, b) REFERENCES p1)}
}

finish_test