/ Check-in [3f47222b]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/alter.c.

   841    841     zNew = sqlite3NameFromToken(db, pNew);
   842    842     if( !zNew ) goto exit_rename_column;
   843    843     assert( pNew->n>0 );
   844    844     bQuote = sqlite3Isquote(pNew->z[0]);
   845    845     sqlite3NestedParse(pParse, 
   846    846         "UPDATE \"%w\".%s SET "
   847    847         "sql = sqlite_rename_column(sql, %Q, %Q, %d, %Q, %d) "
   848         -      "WHERE name NOT LIKE 'sqlite_%%' AND ("
   849         -      "       type IN ('table', 'view') "
   850         -      "   OR (type IN ('index', 'trigger') AND tbl_name = %Q)"
   851         -      ")",
          848  +      "WHERE name NOT LIKE 'sqlite_%%' AND (type != 'index' OR tbl_name = %Q)",
   852    849         zDb, MASTER_NAME, 
   853    850         zDb, pTab->zName, iCol, zNew, bQuote,
   854    851         pTab->zName
   855    852     );
   856    853   
   857    854     /* Drop and reload the database schema. */
   858    855     if( pParse->pVdbe ){
................................................................................
   978    975   ** if the column being references is the column being renamed by an
   979    976   ** ALTER TABLE statement.  If it is, then attach its associated
   980    977   ** RenameToken object to the list of RenameToken objects being
   981    978   ** constructed in RenameCtx object at pWalker->u.pRename.
   982    979   */
   983    980   static int renameColumnExprCb(Walker *pWalker, Expr *pExpr){
   984    981     RenameCtx *p = pWalker->u.pRename;
   985         -  if( pExpr->op==TK_TRIGGER && pExpr->iColumn==p->iCol ){
          982  +  if( pExpr->op==TK_TRIGGER 
          983  +   && pExpr->iColumn==p->iCol 
          984  +   && pWalker->pParse->pTriggerTab==p->pTab
          985  +  ){
   986    986       renameTokenFind(pWalker->pParse, p, (void*)pExpr);
   987         -  }else
   988         -
   989         -  if( p->zOld && pExpr->op==TK_DOT ){
   990         -    Expr *pLeft = pExpr->pLeft;
   991         -    Expr *pRight = pExpr->pRight;
   992         -    assert( pLeft->op==TK_ID && pRight->op==TK_ID );
   993         -    if( 0==sqlite3_stricmp(pLeft->u.zToken, "old")
   994         -     || 0==sqlite3_stricmp(pLeft->u.zToken, "new")
   995         -    ){
   996         -      if( 0==sqlite3_stricmp(pRight->u.zToken, p->zOld) ){
   997         -        renameTokenFind(pWalker->pParse, p, (void*)pRight);
   998         -      }
   999         -    }
  1000         -  }else if( pExpr->op==TK_COLUMN && pExpr->iColumn==p->iCol 
  1001         -         && (p->pTab==0 || p->pTab==pExpr->pTab)
          987  +  }else if( pExpr->op==TK_COLUMN 
          988  +   && pExpr->iColumn==p->iCol 
          989  +   && p->pTab==pExpr->pTab
  1002    990     ){
  1003    991       renameTokenFind(pWalker->pParse, p, (void*)pExpr);
  1004    992     }
  1005    993     return WRC_Continue;
  1006    994   }
  1007    995   
  1008    996   /*
................................................................................
  1134   1122     /* Find tokens that need to be replaced. */
  1135   1123     memset(&sWalker, 0, sizeof(Walker));
  1136   1124     sWalker.pParse = &sParse;
  1137   1125     sWalker.xExprCallback = renameColumnExprCb;
  1138   1126     sWalker.xSelectCallback = renameColumnSelectCb;
  1139   1127     sWalker.u.pRename = &sCtx;
  1140   1128   
         1129  +  sCtx.pTab = pTab;
  1141   1130     if( rc!=SQLITE_OK ) goto renameColumnFunc_done;
  1142   1131     if( sParse.pNewTable ){
  1143   1132       Select *pSelect = sParse.pNewTable->pSelect;
  1144   1133       if( pSelect ){
  1145         -      sCtx.pTab = pTab;
  1146   1134         sParse.rc = SQLITE_OK;
  1147   1135         sqlite3SelectPrep(&sParse, sParse.pNewTable->pSelect, 0);
  1148   1136         rc = (db->mallocFailed ? SQLITE_NOMEM : sParse.rc);
  1149   1137         if( rc==SQLITE_OK ){
  1150   1138           sqlite3WalkSelect(&sWalker, pSelect);
  1151   1139         }else if( rc==SQLITE_ERROR ){
  1152   1140           /* Failed to resolve all symbols in the view. This is not an 
................................................................................
  1157   1145         }
  1158   1146         if( rc!=SQLITE_OK ) goto renameColumnFunc_done;
  1159   1147       }else{
  1160   1148         /* A regular table */
  1161   1149         int bFKOnly = sqlite3_stricmp(zTable, sParse.pNewTable->zName);
  1162   1150         FKey *pFKey;
  1163   1151         assert( sParse.pNewTable->pSelect==0 );
         1152  +      sCtx.pTab = sParse.pNewTable;
  1164   1153         if( bFKOnly==0 ){
  1165   1154           renameTokenFind(
  1166   1155               &sParse, &sCtx, (void*)sParse.pNewTable->aCol[iCol].zName
  1167   1156           );
  1168   1157           if( sCtx.iCol<0 ){
  1169   1158             renameTokenFind(&sParse, &sCtx, (void*)&sParse.pNewTable->iPKey);
  1170   1159           }
................................................................................
  1192   1181       sqlite3WalkExpr(&sWalker, sParse.pNewIndex->pPartIdxWhere);
  1193   1182     }else{
  1194   1183       /* A trigger */
  1195   1184       TriggerStep *pStep;
  1196   1185       NameContext sNC;
  1197   1186       memset(&sNC, 0, sizeof(sNC));
  1198   1187       sNC.pParse = &sParse;
  1199         -    sParse.pTriggerTab = pTab;
         1188  +    sParse.pTriggerTab = sqlite3FindTable(db, sParse.pNewTrigger->table, zDb);
  1200   1189       sParse.eTriggerOp = sParse.pNewTrigger->op;
  1201   1190   
  1202         -      /* Resolve symbols in WHEN clause */
  1203         -    if( sParse.pTriggerTab==pTab && sParse.pNewTrigger->pWhen ){
         1191  +    /* Resolve symbols in WHEN clause */
         1192  +    if( sParse.pNewTrigger->pWhen ){
  1204   1193         rc = sqlite3ResolveExprNames(&sNC, sParse.pNewTrigger->pWhen);
  1205   1194       }
  1206   1195   
  1207   1196       for(pStep=sParse.pNewTrigger->step_list; 
  1208   1197           rc==SQLITE_OK && pStep; 
  1209   1198           pStep=pStep->pNext
  1210   1199       ){
................................................................................
  1221   1210             sSrc.a[0].pTab = pTarget;
  1222   1211             sNC.pSrcList = &sSrc;
  1223   1212             if( pStep->pWhere ){
  1224   1213               rc = sqlite3ResolveExprNames(&sNC, pStep->pWhere);
  1225   1214             }
  1226   1215             if( rc==SQLITE_OK ){
  1227   1216               rc = sqlite3ResolveExprListNames(&sNC, pStep->pExprList);
         1217  +          }
         1218  +          if( pStep->pUpsert ){
         1219  +            Upsert *pUpsert = pStep->pUpsert;
         1220  +            if( rc==SQLITE_OK ){
         1221  +              rc = sqlite3ResolveExprListNames(&sNC, pUpsert->pUpsertTarget);
         1222  +            }
         1223  +            if( rc==SQLITE_OK && pUpsert->pUpsertSet){
         1224  +              ExprList *pUpsertSet = pUpsert->pUpsertSet;
         1225  +              rc = sqlite3ResolveExprListNames(&sNC, pUpsertSet);
         1226  +              if( rc==SQLITE_OK && pTarget==pTab ){
         1227  +                for(i=0; i<pUpsertSet->nExpr; i++){
         1228  +                  char *zName = pUpsertSet->a[i].zName;
         1229  +                  if( 0==sqlite3_stricmp(zName, zOld) ){
         1230  +                    renameTokenFind(&sParse, &sCtx, (void*)zName);
         1231  +                  }
         1232  +                }
         1233  +              }
         1234  +            }
         1235  +            if( rc==SQLITE_OK ){
         1236  +              rc = sqlite3ResolveExprNames(&sNC, pUpsert->pUpsertWhere);
         1237  +            }
         1238  +            if( rc==SQLITE_OK ){
         1239  +              rc = sqlite3ResolveExprNames(&sNC, pUpsert->pUpsertTargetWhere);
         1240  +            }
  1228   1241             }
  1229   1242   
  1230   1243             if( rc==SQLITE_OK && pTarget==pTab ){
  1231   1244               if( pStep->pIdList ){
  1232   1245                 for(i=0; i<pStep->pIdList->nId; i++){
  1233   1246                   char *zName = pStep->pIdList->a[i].zName;
  1234   1247                   if( 0==sqlite3_stricmp(zName, zOld) ){
................................................................................
  1266   1279       sqlite3WalkExpr(&sWalker, sParse.pNewTrigger->pWhen);
  1267   1280   
  1268   1281       /* Find tokens to edit in trigger steps */
  1269   1282       for(pStep=sParse.pNewTrigger->step_list; pStep; pStep=pStep->pNext){
  1270   1283         sqlite3WalkSelect(&sWalker, pStep->pSelect);
  1271   1284         sqlite3WalkExpr(&sWalker, pStep->pWhere);
  1272   1285         sqlite3WalkExprList(&sWalker, pStep->pExprList);
         1286  +      if( pStep->pUpsert ){
         1287  +        Upsert *pUpsert = pStep->pUpsert;
         1288  +        sqlite3WalkExprList(&sWalker, pUpsert->pUpsertTarget);
         1289  +        sqlite3WalkExprList(&sWalker, pUpsert->pUpsertSet);
         1290  +        sqlite3WalkExpr(&sWalker, pUpsert->pUpsertWhere);
         1291  +        sqlite3WalkExpr(&sWalker, pUpsert->pUpsertTargetWhere);
         1292  +      }
  1273   1293       }
  1274   1294     }
  1275   1295   
  1276   1296     assert( rc==SQLITE_OK );
  1277   1297     assert( nQuot>=nNew );
  1278   1298     zOut = sqlite3DbMallocZero(db, nSql + sCtx.nList*nQuot + 1);
  1279   1299     if( zOut ){

Changes to test/altercol.test.

   129    129       DELETE FROM t4 WHERE y=32;
   130    130       UPDATE t4 SET x=y+1, y=0 WHERE y=32;
   131    131       INSERT INTO t4(x, y, z) SELECT 4, 5, 6 WHERE 0;
   132    132     END;
   133    133     INSERT INTO t4 VALUES(3, 2, 1);
   134    134   }
   135    135   
   136         -breakpoint
   137    136   do_execsql_test 3.1 {
   138    137     ALTER TABLE t4 RENAME y TO abc;
   139    138     SELECT sql FROM sqlite_master WHERE name='t4';
   140    139   } {{CREATE TABLE t4(x, abc, z)}}
   141    140   
   142    141   do_execsql_test 3.2 {
   143    142     SELECT * FROM t4;
................................................................................
   327    326   
   328    327   do_execsql_test 8.4.5 {
   329    328     CREATE VIEW zzz AS SELECT george, ringo FROM b1;
   330    329     ALTER TABLE b1 RENAME a TO aaa;
   331    330     SELECT sql FROM sqlite_master WHERE name = 'zzz'
   332    331   } {{CREATE VIEW zzz AS SELECT george, ringo FROM b1}}
   333    332   
          333  +#-------------------------------------------------------------------------
          334  +# More triggers.
          335  +#
          336  +foreach {tn old new lSchema} {
          337  +  1 _x_ _xxx_ {
          338  +    { CREATE TABLE t1(a, b, _x_) }
          339  +    { CREATE TRIGGER AFTER INSERT ON t1 BEGIN
          340  +        SELECT _x_ FROM t1;
          341  +      END }
          342  +  }
          343  +
          344  +  2 _x_ _xxx_ {
          345  +    { CREATE TABLE t1(a, b, _x_) }
          346  +    { CREATE TABLE t2(c, d, e) }
          347  +    { CREATE TRIGGER ttt AFTER INSERT ON t2 BEGIN
          348  +        SELECT _x_ FROM t1;
          349  +      END }
          350  +  }
          351  +
          352  +  3 _x_ _xxx_ {
          353  +    { CREATE TABLE t1(a, b, _x_ INTEGER, PRIMARY KEY(_x_), CHECK(_x_>0)) }
          354  +    { CREATE TABLE t2(c, d, e) }
          355  +    { CREATE TRIGGER ttt AFTER UPDATE  ON t1 BEGIN
          356  +        INSERT INTO t2 VALUES(new.a, new.b, new._x_);
          357  +      END }
          358  +  }
          359  +
          360  +  4 _x_ _xxx_ {
          361  +    { CREATE TABLE t1(a, b, _x_ INTEGER, PRIMARY KEY(_x_), CHECK(_x_>0)) }
          362  +    { CREATE TRIGGER ttt AFTER UPDATE  ON t1 BEGIN
          363  +        INSERT INTO t1 VALUES(new.a, new.b, new._x_)
          364  +          ON CONFLICT (_x_) WHERE _x_>10 DO UPDATE SET _x_ = _x_+1;
          365  +      END }
          366  +  }
          367  +} {
          368  +  reset_db
          369  +  set lSorted [list]
          370  +  foreach sql $lSchema { 
          371  +    execsql $sql 
          372  +    lappend lSorted [string trim $sql]
          373  +  }
          374  +  set lSorted [lsort $lSorted]
          375  +
          376  +  do_execsql_test 9.$tn.1 {
          377  +    SELECT sql FROM sqlite_master WHERE sql!='' ORDER BY 1
          378  +  } $lSorted
          379  +
          380  +if { $tn==3 } breakpoint
          381  +  do_execsql_test 9.$tn.2 "ALTER TABLE t1 RENAME $old TO $new"
          382  +
          383  +  do_execsql_test 9.$tn.1 {
          384  +    SELECT sql FROM sqlite_master ORDER BY 1
          385  +  } [string map [list $old $new] $lSorted]
          386  +}
          387  +
          388  +reset_db
   334    389   
   335    390   finish_test