SQLite
Check-in [ca644a2877]
Not logged in
Overview
Comment:Fix a bug causing all ALTER TABLE RENAME COLUMN commands to fail if ANALYZE had been run on the database. Also prevent the user from renaming the columns of system tables.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | alter-table-rename-column
Files: files | file ages | folders
SHA3-256:ca644a2877c26561f8fb3b3feb74a070cd46621acb105577f04bc936c9b72a95
User & Date: dan 2018-08-11 17:34:38
Context
2018-08-11
17:49
Fix a bug causing ALTER TABLE RENAME COLUMN to fail when renaming an IPK column that is used in a CHECK constraint. check-in: 6595c8811f user: dan tags: alter-table-rename-column
17:34
Fix a bug causing all ALTER TABLE RENAME COLUMN commands to fail if ANALYZE had been run on the database. Also prevent the user from renaming the columns of system tables. check-in: ca644a2877 user: dan tags: alter-table-rename-column
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
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/alter.c.

802
803
804
805
806
807
808


809
810
811
812
813
814
815
...
819
820
821
822
823
824
825
826
827
828
829

830

831
832
833
834
835
836
837
838
839
840
841
842
843
  char *zOld = 0;
  char *zNew = 0;
  const char *zDb;
  int iSchema;

  pTab = sqlite3LocateTableItem(pParse, 0, &pSrc->a[0]);
  if( !pTab ) goto exit_rename_column;


  iSchema = sqlite3SchemaToIndex(db, pTab->pSchema);
  assert( iSchema>=0 );
  zDb = db->aDb[iSchema].zDbSName;

  zOld = sqlite3NameFromToken(db, pOld);
  if( !zOld ) goto exit_rename_column;
  for(iCol=0; iCol<pTab->nCol; iCol++){
................................................................................
    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 database schema. */
  if( db->mallocFailed==0 ){
    assert( pParse->pVdbe );
    sqlite3ChangeCookie(pParse, iSchema);
    sqlite3VdbeAddParseSchemaOp(pParse->pVdbe, iSchema, 0);
  }

 exit_rename_column:
  sqlite3SrcListDelete(db, pSrc);
  sqlite3DbFree(db, zOld);







>
>







 







<



>
|
>




<
|







802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
...
821
822
823
824
825
826
827

828
829
830
831
832
833
834
835
836
837

838
839
840
841
842
843
844
845
  char *zOld = 0;
  char *zNew = 0;
  const char *zDb;
  int iSchema;

  pTab = sqlite3LocateTableItem(pParse, 0, &pSrc->a[0]);
  if( !pTab ) goto exit_rename_column;
  if( SQLITE_OK!=isSystemTable(pParse, pTab->zName) ) goto exit_rename_column;
  
  iSchema = sqlite3SchemaToIndex(db, pTab->pSchema);
  assert( iSchema>=0 );
  zDb = db->aDb[iSchema].zDbSName;

  zOld = sqlite3NameFromToken(db, pOld);
  if( !zOld ) goto exit_rename_column;
  for(iCol=0; iCol<pTab->nCol; iCol++){
................................................................................
    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 name NOT LIKE 'sqlite_%%' AND ("
      "   type = 'table' OR (type='index' AND tbl_name = %Q)"
      ")",
      zDb, MASTER_NAME, iCol, zNew, pTab->zName, zOld, pTab->zName
  );

  /* Drop and reload the database schema. */

  if( pParse->pVdbe ){
    sqlite3ChangeCookie(pParse, iSchema);
    sqlite3VdbeAddParseSchemaOp(pParse->pVdbe, iSchema, 0);
  }

 exit_rename_column:
  sqlite3SrcListDelete(db, pSrc);
  sqlite3DbFree(db, zOld);

Changes to test/altercol.test.

160
161
162
163
164
165
166


















167
168
169
  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








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
  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)}
}

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

do_execsql_test 5.0 {
  CREATE TABLE t5(a, b, c);
  CREATE INDEX t5a ON t5(a);
  INSERT INTO t5 VALUES(1, 2, 3), (4, 5, 6);
  ANALYZE;
}

do_execsql_test 5.1 {
  ALTER TABLE t5 RENAME b TO big;
  SELECT big FROM t5;
} {2 5}

do_catchsql_test 6.1 {
  ALTER TABLE sqlite_stat1 RENAME tbl TO thetable;
} {1 {table sqlite_stat1 may not be altered}}

finish_test