SQLite

Check-in [2dec9ea4ab]
Login

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

Overview
Comment:Ensure the schema cookie is changed when a column is renamed.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | alter-table-rename-column
Files: files | file ages | folders
SHA3-256: 2dec9ea4ab1779d00bb22add0aa9a8536661d8abc9f4c2fb8628e04d4cd84326
User & Date: dan 2018-08-10 20:19:34.822
Context
2018-08-11
13:40
Reload the entire schema after renaming a column in order to ensure that the schema for any tables for which parent key definitions were changed are reloaded. (check-in: f4497b0136 user: dan tags: alter-table-rename-column)
2018-08-10
20:19
Ensure the schema cookie is changed when a column is renamed. (check-in: 2dec9ea4ab user: dan tags: alter-table-rename-column)
19:33
Fix harmless compiler warnings. (check-in: 9564d7008c user: drh tags: alter-table-rename-column)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/alter.c.
︙︙
818
819
820
821
822
823
824

825
826
827
828
829
830
831
832
833

834
835
836
837
838
839
840

  if( iCol==pTab->nCol ){
    sqlite3ErrorMsg(pParse, "no such column: \"%s\"", zOld);
    goto exit_rename_column;
  }

  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);
  sqlite3DbFree(db, zOld);
  sqlite3DbFree(db, zNew);
  return;








>









>








818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842

  if( iCol==pTab->nCol ){
    sqlite3ErrorMsg(pParse, "no such column: \"%s\"", zOld);
    goto exit_rename_column;
  }

  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. */
  sqlite3ChangeCookie(pParse, iSchema);
  reloadTableSchema(pParse, pTab, pTab->zName);

 exit_rename_column:
  sqlite3SrcListDelete(db, pSrc);
  sqlite3DbFree(db, zOld);
  sqlite3DbFree(db, zNew);
  return;
︙︙
Changes to test/altercol.test.
︙︙
94
95
96
97
98
99
100



101
102
103
104
105

106
107
108
109
110
111
112


#-------------------------------------------------------------------------
#
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;








>
>
>
|




>








94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116


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

sqlite3 db2 test.db
do_execsql_test -db db2 2.1 { SELECT b FROM t3 }

do_execsql_test 2.2 {
  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 -db db2 2.3 { SELECT biglongname FROM t3 }

#-------------------------------------------------------------------------
#
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;
︙︙
142
143
144
145
146
147
148


149
150
151
152
153
154
155
156
157
158
159
160
161
162
163

  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









>
>
|



|











146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169

  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 { INSERT INTO c1 VALUES(1, 2); }

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

do_execsql_test 4.4 {
  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