SQLite

Check-in [ca5184a25f]
Login

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

Overview
Comment:If the new column name in an ALTER TABLE RENAME COLUMN statement is quoted, then also use quotes for the column name in the edited SQL statements.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | alter-table-rename-column
Files: files | file ages | folders
SHA3-256: ca5184a25f9150540a3e401ef67df0606efa7a294d70e3fa5edad9854003eb36
User & Date: dan 2018-08-11 20:38:33.809
Context
2018-08-11
20:46
Add the "atrc" test program. "Atrc" is short for "ALTER TABLE RENAME COLUMN". See the header comment on the program itself for further information. (check-in: ed64a55a22 user: drh tags: alter-table-rename-column)
20:38
If the new column name in an ALTER TABLE RENAME COLUMN statement is quoted, then also use quotes for the column name in the edited SQL statements. (check-in: ca5184a25f user: dan tags: alter-table-rename-column)
18:34
Avoid an assert() sometimes triggered by ALTER TABLE RENAME COLUMN in non-debug builds. (check-in: 520c1c75da user: dan tags: alter-table-rename-column)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/alter.c.
︙︙
799
800
801
802
803
804
805

806
807
808
809
810
811
812

  sqlite3 *db = pParse->db;
  Table *pTab;                    /* Table being updated */
  int iCol;                       /* Index of column being renamed */
  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 );








>








799
800
801
802
803
804
805
806
807
808
809
810
811
812
813

  sqlite3 *db = pParse->db;
  Table *pTab;                    /* Table being updated */
  int iCol;                       /* Index of column being renamed */
  char *zOld = 0;
  char *zNew = 0;
  const char *zDb;
  int iSchema;
  int bQuote;

  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 );
︙︙
820
821
822
823
824
825
826
827

828
829
830
831
832
833
834
835
836
837
838
839
840
841

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








|
>


|



|








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

  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;
  assert( pNew->n>0 );
  bQuote = sqlite3Isquote(pNew->z[0]);
  sqlite3NestedParse(pParse, 
      "UPDATE \"%w\".%s SET "
      "sql = sqlite_rename_column(sql, %d, %d, %Q, %Q, %Q) "
      "WHERE name NOT LIKE 'sqlite_%%' AND ("
      "   type = 'table' OR (type='index' AND tbl_name = %Q)"
      ")",
      zDb, MASTER_NAME, iCol, bQuote, zNew, pTab->zName, zOld, pTab->zName
  );

  /* Drop and reload the database schema. */
  if( pParse->pVdbe ){
    sqlite3ChangeCookie(pParse, iSchema);
    sqlite3VdbeAddParseSchemaOp(pParse->pVdbe, iSchema, 0);
  }
︙︙
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
952
953
954
955

  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 = (const char*)sqlite3_value_text(argv[0]);
  int nSql = sqlite3_value_bytes(argv[0]);

  const char *zNew = (const char*)sqlite3_value_text(argv[2]);
  int nNew = sqlite3_value_bytes(argv[2]);
  const char *zTable = (const char*)sqlite3_value_text(argv[3]);
  const char *zOld = (const char*)sqlite3_value_text(argv[4]);

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








|










>
|
|
|
|








929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958

  for(pp=&pCtx->pList; *pp!=pBest; pp=&(*pp)->pNext);
  *pp = pBest->pNext;

  return pBest;
}

/*
** sqlite_rename_column(SQL, iCol, bQuote, 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 = (const char*)sqlite3_value_text(argv[0]);
  int nSql = sqlite3_value_bytes(argv[0]);
  int bQuote = sqlite3_value_int(argv[2]);
  const char *zNew = (const char*)sqlite3_value_text(argv[3]);
  int nNew = sqlite3_value_bytes(argv[3]);
  const char *zTable = (const char*)sqlite3_value_text(argv[4]);
  const char *zOld = (const char*)sqlite3_value_text(argv[5]);

  int rc;
  char *zErr = 0;
  Parse sParse;
  Walker sWalker;
  Index *pIdx;
  char *zOut = 0;
︙︙
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006

      sqlite3_result_error_code(context, rc);
    }
    sqlite3DbFree(db, zErr);
    sqlite3_free(zQuot);
    return;
  }

  for(i=0; i<nNew; i++){
    if( sqlite3IsIdChar(zNew[i])==0 ){
      zNew = zQuot;
      nNew = nQuot;
      break;
    }
  }

#ifdef SQLITE_DEBUG
  assert( sqlite3Strlen30(zSql)==nSql );
  {
    RenameToken *pToken;
    for(pToken=sParse.pRename; pToken; pToken=pToken->pNext){








<
|
|
|
<
<








990
991
992
993
994
995
996

997
998
999


1000
1001
1002
1003
1004
1005
1006

      sqlite3_result_error_code(context, rc);
    }
    sqlite3DbFree(db, zErr);
    sqlite3_free(zQuot);
    return;
  }


  if( bQuote ){
    zNew = zQuot;
    nNew = nQuot;


  }

#ifdef SQLITE_DEBUG
  assert( sqlite3Strlen30(zSql)==nSql );
  {
    RenameToken *pToken;
    for(pToken=sParse.pRename; pToken; pToken=pToken->pNext){
︙︙
1055
1056
1057
1058
1059
1060
1061

1062
1063
1064
1065
1066
1067
1068
1069

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


  zOut = sqlite3DbMallocZero(db, nSql + sCtx.nList*nNew + 1);
  if( zOut ){
    int nOut = nSql;
    memcpy(zOut, zSql, nSql);
    while( sCtx.pList ){
      int iOff;                   /* Offset of token to replace in zOut */
      RenameToken *pBest = renameColumnTokenNext(&sCtx);









>
|








1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070

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

  assert( nQuot>=nNew );
  zOut = sqlite3DbMallocZero(db, nSql + sCtx.nList*nQuot + 1);
  if( zOut ){
    int nOut = nSql;
    memcpy(zOut, zSql, nSql);
    while( sCtx.pList ){
      int iOff;                   /* Offset of token to replace in zOut */
      RenameToken *pBest = renameColumnTokenNext(&sCtx);
︙︙
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122


/*
** 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 */








|











1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123


/*
** 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,   6, 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 test/altercol.test.
︙︙
195
196
197
198
199
200
201








202
203
204

    CHECK( length(uuid)>=40 AND rid>0 )
  );
}

do_execsql_test 6.1 {
  ALTER TABLE "blob" RENAME COLUMN "rid" TO "a1";
}









finish_test









>
>
>
>
>
>
>
>




195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212

    CHECK( length(uuid)>=40 AND rid>0 )
  );
}

do_execsql_test 6.1 {
  ALTER TABLE "blob" RENAME COLUMN "rid" TO "a1";
}

do_catchsql_test 6.2 {
  ALTER TABLE "blob" RENAME COLUMN "a1" TO [where];
} {0 {}}

do_execsql_test 6.3 {
  SELECT "where" FROM blob;
} {}

finish_test