/ Check-in [3f47222b]
Login

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

Overview
Comment:Further progress on updating trigger programs as part of ALTER TABLE RENAME COLUMN.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | edit-trigger-wrapper
Files: files | file ages | folders
SHA3-256:3f47222b6e20e1c1ef41c0f7391c8b5c7648e2a2d6013303d155ad59ce27143a
User & Date: dan 2018-08-16 19:49:16
Context
2018-08-17
17:18
Ensure that CREATE VIEW, TRIGGER or INDEX statements can be edited by ALTER TABLE RENAME COLUMN even if they use collation-sequences or user-defined-functions that are not available. check-in: 540014ef user: dan tags: edit-trigger-wrapper
2018-08-16
19:49
Further progress on updating trigger programs as part of ALTER TABLE RENAME COLUMN. check-in: 3f47222b user: dan tags: edit-trigger-wrapper
2018-08-15
20:28
Have ALTER TABLE RENAME COLUMN edit trigger programs. Only partly working. check-in: e272dc2b user: dan tags: edit-trigger-wrapper
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/alter.c.

841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
...
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000

1001
1002
1003
1004
1005
1006
1007
1008
....
1134
1135
1136
1137
1138
1139
1140

1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
....
1157
1158
1159
1160
1161
1162
1163

1164
1165
1166
1167
1168
1169
1170
....
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
....
1221
1222
1223
1224
1225
1226
1227
























1228
1229
1230
1231
1232
1233
1234
....
1266
1267
1268
1269
1270
1271
1272







1273
1274
1275
1276
1277
1278
1279
  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, %Q, %Q, %d, %Q, %d) "
      "WHERE name NOT LIKE 'sqlite_%%' AND ("
      "       type IN ('table', 'view') "
      "   OR (type IN ('index', 'trigger') AND tbl_name = %Q)"
      ")",
      zDb, MASTER_NAME, 
      zDb, pTab->zName, iCol, zNew, bQuote,
      pTab->zName
  );

  /* Drop and reload the database schema. */
  if( pParse->pVdbe ){
................................................................................
** if the column being references is the column being renamed by an
** ALTER TABLE statement.  If it is, then attach its associated
** RenameToken object to the list of RenameToken objects being
** constructed in RenameCtx object at pWalker->u.pRename.
*/
static int renameColumnExprCb(Walker *pWalker, Expr *pExpr){
  RenameCtx *p = pWalker->u.pRename;
  if( pExpr->op==TK_TRIGGER && pExpr->iColumn==p->iCol ){
    renameTokenFind(pWalker->pParse, p, (void*)pExpr);
  }else

  if( p->zOld && pExpr->op==TK_DOT ){
    Expr *pLeft = pExpr->pLeft;
    Expr *pRight = pExpr->pRight;
    assert( pLeft->op==TK_ID && pRight->op==TK_ID );
    if( 0==sqlite3_stricmp(pLeft->u.zToken, "old")
     || 0==sqlite3_stricmp(pLeft->u.zToken, "new")
    ){
      if( 0==sqlite3_stricmp(pRight->u.zToken, p->zOld) ){
        renameTokenFind(pWalker->pParse, p, (void*)pRight);
      }
    }
  }else if( pExpr->op==TK_COLUMN && pExpr->iColumn==p->iCol 

         && (p->pTab==0 || p->pTab==pExpr->pTab)
  ){
    renameTokenFind(pWalker->pParse, p, (void*)pExpr);
  }
  return WRC_Continue;
}

/*
................................................................................
  /* Find tokens that need to be replaced. */
  memset(&sWalker, 0, sizeof(Walker));
  sWalker.pParse = &sParse;
  sWalker.xExprCallback = renameColumnExprCb;
  sWalker.xSelectCallback = renameColumnSelectCb;
  sWalker.u.pRename = &sCtx;


  if( rc!=SQLITE_OK ) goto renameColumnFunc_done;
  if( sParse.pNewTable ){
    Select *pSelect = sParse.pNewTable->pSelect;
    if( pSelect ){
      sCtx.pTab = pTab;
      sParse.rc = SQLITE_OK;
      sqlite3SelectPrep(&sParse, sParse.pNewTable->pSelect, 0);
      rc = (db->mallocFailed ? SQLITE_NOMEM : sParse.rc);
      if( rc==SQLITE_OK ){
        sqlite3WalkSelect(&sWalker, pSelect);
      }else if( rc==SQLITE_ERROR ){
        /* Failed to resolve all symbols in the view. This is not an 
................................................................................
      }
      if( rc!=SQLITE_OK ) goto renameColumnFunc_done;
    }else{
      /* A regular table */
      int bFKOnly = sqlite3_stricmp(zTable, sParse.pNewTable->zName);
      FKey *pFKey;
      assert( sParse.pNewTable->pSelect==0 );

      if( bFKOnly==0 ){
        renameTokenFind(
            &sParse, &sCtx, (void*)sParse.pNewTable->aCol[iCol].zName
        );
        if( sCtx.iCol<0 ){
          renameTokenFind(&sParse, &sCtx, (void*)&sParse.pNewTable->iPKey);
        }
................................................................................
    sqlite3WalkExpr(&sWalker, sParse.pNewIndex->pPartIdxWhere);
  }else{
    /* A trigger */
    TriggerStep *pStep;
    NameContext sNC;
    memset(&sNC, 0, sizeof(sNC));
    sNC.pParse = &sParse;
    sParse.pTriggerTab = pTab;
    sParse.eTriggerOp = sParse.pNewTrigger->op;

      /* Resolve symbols in WHEN clause */
    if( sParse.pTriggerTab==pTab && sParse.pNewTrigger->pWhen ){
      rc = sqlite3ResolveExprNames(&sNC, sParse.pNewTrigger->pWhen);
    }

    for(pStep=sParse.pNewTrigger->step_list; 
        rc==SQLITE_OK && pStep; 
        pStep=pStep->pNext
    ){
................................................................................
          sSrc.a[0].pTab = pTarget;
          sNC.pSrcList = &sSrc;
          if( pStep->pWhere ){
            rc = sqlite3ResolveExprNames(&sNC, pStep->pWhere);
          }
          if( rc==SQLITE_OK ){
            rc = sqlite3ResolveExprListNames(&sNC, pStep->pExprList);
























          }

          if( rc==SQLITE_OK && pTarget==pTab ){
            if( pStep->pIdList ){
              for(i=0; i<pStep->pIdList->nId; i++){
                char *zName = pStep->pIdList->a[i].zName;
                if( 0==sqlite3_stricmp(zName, zOld) ){
................................................................................
    sqlite3WalkExpr(&sWalker, sParse.pNewTrigger->pWhen);

    /* Find tokens to edit in trigger steps */
    for(pStep=sParse.pNewTrigger->step_list; pStep; pStep=pStep->pNext){
      sqlite3WalkSelect(&sWalker, pStep->pSelect);
      sqlite3WalkExpr(&sWalker, pStep->pWhere);
      sqlite3WalkExprList(&sWalker, pStep->pExprList);







    }
  }

  assert( rc==SQLITE_OK );
  assert( nQuot>=nNew );
  zOut = sqlite3DbMallocZero(db, nSql + sCtx.nList*nQuot + 1);
  if( zOut ){







|
<
<
<







 







|
|
|
<
<
<
<
<
<
<
|
<
|
<
<
|
>
|







 







>




<







 







>







 







|


|
|







 







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







 







>
>
>
>
>
>
>







841
842
843
844
845
846
847
848



849
850
851
852
853
854
855
...
975
976
977
978
979
980
981
982
983
984







985

986


987
988
989
990
991
992
993
994
995
996
....
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133

1134
1135
1136
1137
1138
1139
1140
....
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
....
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
....
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
....
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
  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, %Q, %Q, %d, %Q, %d) "
      "WHERE name NOT LIKE 'sqlite_%%' AND (type != 'index' OR tbl_name = %Q)",



      zDb, MASTER_NAME, 
      zDb, pTab->zName, iCol, zNew, bQuote,
      pTab->zName
  );

  /* Drop and reload the database schema. */
  if( pParse->pVdbe ){
................................................................................
** if the column being references is the column being renamed by an
** ALTER TABLE statement.  If it is, then attach its associated
** RenameToken object to the list of RenameToken objects being
** constructed in RenameCtx object at pWalker->u.pRename.
*/
static int renameColumnExprCb(Walker *pWalker, Expr *pExpr){
  RenameCtx *p = pWalker->u.pRename;
  if( pExpr->op==TK_TRIGGER 
   && pExpr->iColumn==p->iCol 
   && pWalker->pParse->pTriggerTab==p->pTab







  ){

    renameTokenFind(pWalker->pParse, p, (void*)pExpr);


  }else if( pExpr->op==TK_COLUMN 
   && pExpr->iColumn==p->iCol 
   && p->pTab==pExpr->pTab
  ){
    renameTokenFind(pWalker->pParse, p, (void*)pExpr);
  }
  return WRC_Continue;
}

/*
................................................................................
  /* Find tokens that need to be replaced. */
  memset(&sWalker, 0, sizeof(Walker));
  sWalker.pParse = &sParse;
  sWalker.xExprCallback = renameColumnExprCb;
  sWalker.xSelectCallback = renameColumnSelectCb;
  sWalker.u.pRename = &sCtx;

  sCtx.pTab = pTab;
  if( rc!=SQLITE_OK ) goto renameColumnFunc_done;
  if( sParse.pNewTable ){
    Select *pSelect = sParse.pNewTable->pSelect;
    if( pSelect ){

      sParse.rc = SQLITE_OK;
      sqlite3SelectPrep(&sParse, sParse.pNewTable->pSelect, 0);
      rc = (db->mallocFailed ? SQLITE_NOMEM : sParse.rc);
      if( rc==SQLITE_OK ){
        sqlite3WalkSelect(&sWalker, pSelect);
      }else if( rc==SQLITE_ERROR ){
        /* Failed to resolve all symbols in the view. This is not an 
................................................................................
      }
      if( rc!=SQLITE_OK ) goto renameColumnFunc_done;
    }else{
      /* A regular table */
      int bFKOnly = sqlite3_stricmp(zTable, sParse.pNewTable->zName);
      FKey *pFKey;
      assert( sParse.pNewTable->pSelect==0 );
      sCtx.pTab = sParse.pNewTable;
      if( bFKOnly==0 ){
        renameTokenFind(
            &sParse, &sCtx, (void*)sParse.pNewTable->aCol[iCol].zName
        );
        if( sCtx.iCol<0 ){
          renameTokenFind(&sParse, &sCtx, (void*)&sParse.pNewTable->iPKey);
        }
................................................................................
    sqlite3WalkExpr(&sWalker, sParse.pNewIndex->pPartIdxWhere);
  }else{
    /* A trigger */
    TriggerStep *pStep;
    NameContext sNC;
    memset(&sNC, 0, sizeof(sNC));
    sNC.pParse = &sParse;
    sParse.pTriggerTab = sqlite3FindTable(db, sParse.pNewTrigger->table, zDb);
    sParse.eTriggerOp = sParse.pNewTrigger->op;

    /* Resolve symbols in WHEN clause */
    if( sParse.pNewTrigger->pWhen ){
      rc = sqlite3ResolveExprNames(&sNC, sParse.pNewTrigger->pWhen);
    }

    for(pStep=sParse.pNewTrigger->step_list; 
        rc==SQLITE_OK && pStep; 
        pStep=pStep->pNext
    ){
................................................................................
          sSrc.a[0].pTab = pTarget;
          sNC.pSrcList = &sSrc;
          if( pStep->pWhere ){
            rc = sqlite3ResolveExprNames(&sNC, pStep->pWhere);
          }
          if( rc==SQLITE_OK ){
            rc = sqlite3ResolveExprListNames(&sNC, pStep->pExprList);
          }
          if( pStep->pUpsert ){
            Upsert *pUpsert = pStep->pUpsert;
            if( rc==SQLITE_OK ){
              rc = sqlite3ResolveExprListNames(&sNC, pUpsert->pUpsertTarget);
            }
            if( rc==SQLITE_OK && pUpsert->pUpsertSet){
              ExprList *pUpsertSet = pUpsert->pUpsertSet;
              rc = sqlite3ResolveExprListNames(&sNC, pUpsertSet);
              if( rc==SQLITE_OK && pTarget==pTab ){
                for(i=0; i<pUpsertSet->nExpr; i++){
                  char *zName = pUpsertSet->a[i].zName;
                  if( 0==sqlite3_stricmp(zName, zOld) ){
                    renameTokenFind(&sParse, &sCtx, (void*)zName);
                  }
                }
              }
            }
            if( rc==SQLITE_OK ){
              rc = sqlite3ResolveExprNames(&sNC, pUpsert->pUpsertWhere);
            }
            if( rc==SQLITE_OK ){
              rc = sqlite3ResolveExprNames(&sNC, pUpsert->pUpsertTargetWhere);
            }
          }

          if( rc==SQLITE_OK && pTarget==pTab ){
            if( pStep->pIdList ){
              for(i=0; i<pStep->pIdList->nId; i++){
                char *zName = pStep->pIdList->a[i].zName;
                if( 0==sqlite3_stricmp(zName, zOld) ){
................................................................................
    sqlite3WalkExpr(&sWalker, sParse.pNewTrigger->pWhen);

    /* Find tokens to edit in trigger steps */
    for(pStep=sParse.pNewTrigger->step_list; pStep; pStep=pStep->pNext){
      sqlite3WalkSelect(&sWalker, pStep->pSelect);
      sqlite3WalkExpr(&sWalker, pStep->pWhere);
      sqlite3WalkExprList(&sWalker, pStep->pExprList);
      if( pStep->pUpsert ){
        Upsert *pUpsert = pStep->pUpsert;
        sqlite3WalkExprList(&sWalker, pUpsert->pUpsertTarget);
        sqlite3WalkExprList(&sWalker, pUpsert->pUpsertSet);
        sqlite3WalkExpr(&sWalker, pUpsert->pUpsertWhere);
        sqlite3WalkExpr(&sWalker, pUpsert->pUpsertTargetWhere);
      }
    }
  }

  assert( rc==SQLITE_OK );
  assert( nQuot>=nNew );
  zOut = sqlite3DbMallocZero(db, nSql + sCtx.nList*nQuot + 1);
  if( zOut ){

Changes to test/altercol.test.

129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
...
327
328
329
330
331
332
333


334






















































335
    DELETE FROM t4 WHERE y=32;
    UPDATE t4 SET x=y+1, y=0 WHERE y=32;
    INSERT INTO t4(x, y, z) SELECT 4, 5, 6 WHERE 0;
  END;
  INSERT INTO t4 VALUES(3, 2, 1);
}

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

do_execsql_test 3.2 {
  SELECT * FROM t4;
................................................................................

do_execsql_test 8.4.5 {
  CREATE VIEW zzz AS SELECT george, ringo FROM b1;
  ALTER TABLE b1 RENAME a TO aaa;
  SELECT sql FROM sqlite_master WHERE name = 'zzz'
} {{CREATE VIEW zzz AS SELECT george, ringo FROM b1}}


























































finish_test







<







 







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

129
130
131
132
133
134
135

136
137
138
139
140
141
142
...
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
    DELETE FROM t4 WHERE y=32;
    UPDATE t4 SET x=y+1, y=0 WHERE y=32;
    INSERT INTO t4(x, y, z) SELECT 4, 5, 6 WHERE 0;
  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)}}

do_execsql_test 3.2 {
  SELECT * FROM t4;
................................................................................

do_execsql_test 8.4.5 {
  CREATE VIEW zzz AS SELECT george, ringo FROM b1;
  ALTER TABLE b1 RENAME a TO aaa;
  SELECT sql FROM sqlite_master WHERE name = 'zzz'
} {{CREATE VIEW zzz AS SELECT george, ringo FROM b1}}

#-------------------------------------------------------------------------
# More triggers.
#
foreach {tn old new lSchema} {
  1 _x_ _xxx_ {
    { CREATE TABLE t1(a, b, _x_) }
    { CREATE TRIGGER AFTER INSERT ON t1 BEGIN
        SELECT _x_ FROM t1;
      END }
  }

  2 _x_ _xxx_ {
    { CREATE TABLE t1(a, b, _x_) }
    { CREATE TABLE t2(c, d, e) }
    { CREATE TRIGGER ttt AFTER INSERT ON t2 BEGIN
        SELECT _x_ FROM t1;
      END }
  }

  3 _x_ _xxx_ {
    { CREATE TABLE t1(a, b, _x_ INTEGER, PRIMARY KEY(_x_), CHECK(_x_>0)) }
    { CREATE TABLE t2(c, d, e) }
    { CREATE TRIGGER ttt AFTER UPDATE  ON t1 BEGIN
        INSERT INTO t2 VALUES(new.a, new.b, new._x_);
      END }
  }

  4 _x_ _xxx_ {
    { CREATE TABLE t1(a, b, _x_ INTEGER, PRIMARY KEY(_x_), CHECK(_x_>0)) }
    { CREATE TRIGGER ttt AFTER UPDATE  ON t1 BEGIN
        INSERT INTO t1 VALUES(new.a, new.b, new._x_)
          ON CONFLICT (_x_) WHERE _x_>10 DO UPDATE SET _x_ = _x_+1;
      END }
  }
} {
  reset_db
  set lSorted [list]
  foreach sql $lSchema { 
    execsql $sql 
    lappend lSorted [string trim $sql]
  }
  set lSorted [lsort $lSorted]

  do_execsql_test 9.$tn.1 {
    SELECT sql FROM sqlite_master WHERE sql!='' ORDER BY 1
  } $lSorted

if { $tn==3 } breakpoint
  do_execsql_test 9.$tn.2 "ALTER TABLE t1 RENAME $old TO $new"

  do_execsql_test 9.$tn.1 {
    SELECT sql FROM sqlite_master ORDER BY 1
  } [string map [list $old $new] $lSorted]
}

reset_db

finish_test