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: | 191079bd4f774528a1e39b71b4a4ce78 |
| 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
Changes to src/alter.c.
821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 ... 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 .... 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 .... 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 |
}
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);
................................................................................
}
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;
................................................................................
/* 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);
}
................................................................................
/*
** 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 */
|
| | | > > > > > > > > > > | | | | | | | > > | | > > > > > > > > | |
821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 ... 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 .... 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 .... 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 |
}
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);
................................................................................
}
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;
................................................................................
/* 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);
}
................................................................................
/*
** 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
|