/ Check-in [e272dc2b1c]
Login

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

Overview
Comment:Have ALTER TABLE RENAME COLUMN edit trigger programs. Only partly working.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | edit-trigger-wrapper
Files: files | file ages | folders
SHA3-256: e272dc2b1c0edab59a40f32c77c81a3e636937280524161eff5669cb0046ad84
User & Date: dan 2018-08-15 20:28:39
Original Comment: (no comment)
Context
2018-08-16
19:49
Further progress on updating trigger programs as part of ALTER TABLE RENAME COLUMN. check-in: 3f47222b6e user: dan tags: edit-trigger-wrapper
2018-08-15
20:28
Have ALTER TABLE RENAME COLUMN edit trigger programs. Only partly working. check-in: e272dc2b1c user: dan tags: edit-trigger-wrapper
2018-08-14
21:05
Fix a problem when renaming an IPK column that is also part of a child key. check-in: 6e6a2bfdbb user: dan tags: edit-trigger-wrapper
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/alter.c.

   978    978   ** if the column being references is the column being renamed by an
   979    979   ** ALTER TABLE statement.  If it is, then attach its associated
   980    980   ** RenameToken object to the list of RenameToken objects being
   981    981   ** constructed in RenameCtx object at pWalker->u.pRename.
   982    982   */
   983    983   static int renameColumnExprCb(Walker *pWalker, Expr *pExpr){
   984    984     RenameCtx *p = pWalker->u.pRename;
          985  +  if( pExpr->op==TK_TRIGGER && pExpr->iColumn==p->iCol ){
          986  +    renameTokenFind(pWalker->pParse, p, (void*)pExpr);
          987  +  }else
          988  +
   985    989     if( p->zOld && pExpr->op==TK_DOT ){
   986    990       Expr *pLeft = pExpr->pLeft;
   987    991       Expr *pRight = pExpr->pRight;
   988    992       assert( pLeft->op==TK_ID && pRight->op==TK_ID );
   989    993       if( 0==sqlite3_stricmp(pLeft->u.zToken, "old")
   990    994        || 0==sqlite3_stricmp(pLeft->u.zToken, "new")
   991    995       ){
................................................................................
  1141   1145         sCtx.pTab = pTab;
  1142   1146         sParse.rc = SQLITE_OK;
  1143   1147         sqlite3SelectPrep(&sParse, sParse.pNewTable->pSelect, 0);
  1144   1148         rc = (db->mallocFailed ? SQLITE_NOMEM : sParse.rc);
  1145   1149         if( rc==SQLITE_OK ){
  1146   1150           sqlite3WalkSelect(&sWalker, pSelect);
  1147   1151         }else if( rc==SQLITE_ERROR ){
  1148         -        /* Failed to resolve all symboles in the view. This is not an 
         1152  +        /* Failed to resolve all symbols in the view. This is not an 
  1149   1153           ** error, but it will not be edited. */
  1150   1154           sqlite3DbFree(db, sParse.zErrMsg);
  1151   1155           sParse.zErrMsg = 0;
  1152   1156           rc = SQLITE_OK;
  1153   1157         }
  1154   1158         if( rc!=SQLITE_OK ) goto renameColumnFunc_done;
  1155   1159       }else{
................................................................................
  1183   1187           }
  1184   1188         }
  1185   1189       }
  1186   1190     }else if( sParse.pNewIndex ){
  1187   1191       sqlite3WalkExprList(&sWalker, sParse.pNewIndex->aColExpr);
  1188   1192       sqlite3WalkExpr(&sWalker, sParse.pNewIndex->pPartIdxWhere);
  1189   1193     }else{
  1190         -    sCtx.zOld = zOld;
  1191         -    sqlite3WalkExpr(&sWalker, sParse.pNewTrigger->pWhen);
  1192         -    if( sParse.pNewTrigger->pColumns ){
         1194  +    /* A trigger */
         1195  +    TriggerStep *pStep;
         1196  +    NameContext sNC;
         1197  +    memset(&sNC, 0, sizeof(sNC));
         1198  +    sNC.pParse = &sParse;
         1199  +    sParse.pTriggerTab = pTab;
         1200  +    sParse.eTriggerOp = sParse.pNewTrigger->op;
         1201  +
         1202  +      /* Resolve symbols in WHEN clause */
         1203  +    if( sParse.pTriggerTab==pTab && sParse.pNewTrigger->pWhen ){
         1204  +      rc = sqlite3ResolveExprNames(&sNC, sParse.pNewTrigger->pWhen);
         1205  +    }
         1206  +
         1207  +    for(pStep=sParse.pNewTrigger->step_list; 
         1208  +        rc==SQLITE_OK && pStep; 
         1209  +        pStep=pStep->pNext
         1210  +    ){
         1211  +      if( pStep->pSelect ) sqlite3SelectPrep(&sParse, pStep->pSelect, &sNC);
         1212  +      if( pStep->zTarget ){ 
         1213  +        Table *pTarget = sqlite3FindTable(db, pStep->zTarget, zDb);
         1214  +        if( pTarget==0 ){
         1215  +          rc = SQLITE_ERROR;
         1216  +        }else{
         1217  +          SrcList sSrc;
         1218  +          memset(&sSrc, 0, sizeof(sSrc));
         1219  +          sSrc.nSrc = 1;
         1220  +          sSrc.a[0].zName = pStep->zTarget;
         1221  +          sSrc.a[0].pTab = pTarget;
         1222  +          sNC.pSrcList = &sSrc;
         1223  +          if( pStep->pWhere ){
         1224  +            rc = sqlite3ResolveExprNames(&sNC, pStep->pWhere);
         1225  +          }
         1226  +          if( rc==SQLITE_OK ){
         1227  +            rc = sqlite3ResolveExprListNames(&sNC, pStep->pExprList);
         1228  +          }
         1229  +
         1230  +          if( rc==SQLITE_OK && pTarget==pTab ){
         1231  +            if( pStep->pIdList ){
         1232  +              for(i=0; i<pStep->pIdList->nId; i++){
         1233  +                char *zName = pStep->pIdList->a[i].zName;
         1234  +                if( 0==sqlite3_stricmp(zName, zOld) ){
         1235  +                  renameTokenFind(&sParse, &sCtx, (void*)zName);
         1236  +                }
         1237  +              }
         1238  +            }
         1239  +            if( pStep->op==TK_UPDATE ){
         1240  +              assert( pStep->pExprList );
         1241  +              for(i=0; i<pStep->pExprList->nExpr; i++){
         1242  +                char *zName = pStep->pExprList->a[i].zName;
         1243  +                if( 0==sqlite3_stricmp(zName, zOld) ){
         1244  +                  renameTokenFind(&sParse, &sCtx, (void*)zName);
         1245  +                }
         1246  +              }
         1247  +            }
         1248  +          }
         1249  +        }
         1250  +      }
         1251  +    }
         1252  +
         1253  +    if( rc!=SQLITE_OK ) goto renameColumnFunc_done;
         1254  +
         1255  +    /* Find tokens to edit in UPDATE OF clause */
         1256  +    if( sParse.pTriggerTab==pTab && sParse.pNewTrigger->pColumns ){
  1193   1257         for(i=0; i<sParse.pNewTrigger->pColumns->nId; i++){
  1194   1258           char *zName = sParse.pNewTrigger->pColumns->a[i].zName;
  1195   1259           if( 0==sqlite3_stricmp(zName, zOld) ){
  1196   1260             renameTokenFind(&sParse, &sCtx, (void*)zName);
  1197   1261           }
  1198   1262         }
  1199   1263       }
         1264  +
         1265  +    /* Find tokens to edit in WHEN clause */
         1266  +    sqlite3WalkExpr(&sWalker, sParse.pNewTrigger->pWhen);
         1267  +
         1268  +    /* Find tokens to edit in trigger steps */
         1269  +    for(pStep=sParse.pNewTrigger->step_list; pStep; pStep=pStep->pNext){
         1270  +      sqlite3WalkSelect(&sWalker, pStep->pSelect);
         1271  +      sqlite3WalkExpr(&sWalker, pStep->pWhere);
         1272  +      sqlite3WalkExprList(&sWalker, pStep->pExprList);
         1273  +    }
  1200   1274     }
  1201   1275   
  1202   1276     assert( rc==SQLITE_OK );
  1203   1277     assert( nQuot>=nNew );
  1204   1278     zOut = sqlite3DbMallocZero(db, nSql + sCtx.nList*nQuot + 1);
  1205   1279     if( zOut ){
  1206   1280       int nOut = nSql;

Changes to src/expr.c.

  1662   1662     if( pList ){
  1663   1663       struct ExprList_item *pItem;
  1664   1664       assert( pList->nExpr>0 );
  1665   1665       pItem = &pList->a[pList->nExpr-1];
  1666   1666       assert( pItem->zName==0 );
  1667   1667       pItem->zName = sqlite3DbStrNDup(pParse->db, pName->z, pName->n);
  1668   1668       if( dequote ) sqlite3Dequote(pItem->zName);
         1669  +    if( IN_RENAME_COLUMN ){
         1670  +      sqlite3RenameToken(pParse, (void*)pItem->zName, pName);
         1671  +    }
  1669   1672     }
  1670   1673   }
  1671   1674   
  1672   1675   /*
  1673   1676   ** Set the ExprList.a[].zSpan element of the most recently added item
  1674   1677   ** on the expression list.
  1675   1678   **

Changes to src/parse.y.

  1447   1447   
  1448   1448   
  1449   1449   %type trigger_cmd {TriggerStep*}
  1450   1450   %destructor trigger_cmd {sqlite3DeleteTriggerStep(pParse->db, $$);}
  1451   1451   // UPDATE 
  1452   1452   trigger_cmd(A) ::=
  1453   1453      UPDATE(B) orconf(R) trnm(X) tridxby SET setlist(Y) where_opt(Z) scanpt(E).  
  1454         -   {A = sqlite3TriggerUpdateStep(pParse->db, &X, Y, Z, R, B.z, E);}
         1454  +   {A = sqlite3TriggerUpdateStep(pParse, &X, Y, Z, R, B.z, E);}
  1455   1455   
  1456   1456   // INSERT
  1457   1457   trigger_cmd(A) ::= scanpt(B) insert_cmd(R) INTO
  1458   1458                         trnm(X) idlist_opt(F) select(S) upsert(U) scanpt(Z). {
  1459         -   A = sqlite3TriggerInsertStep(pParse->db,&X,F,S,R,U,B,Z);/*A-overwrites-R*/
         1459  +   A = sqlite3TriggerInsertStep(pParse,&X,F,S,R,U,B,Z);/*A-overwrites-R*/
  1460   1460   }
  1461   1461   // DELETE
  1462   1462   trigger_cmd(A) ::= DELETE(B) FROM trnm(X) tridxby where_opt(Y) scanpt(E).
  1463         -   {A = sqlite3TriggerDeleteStep(pParse->db, &X, Y, B.z, E);}
         1463  +   {A = sqlite3TriggerDeleteStep(pParse, &X, Y, B.z, E);}
  1464   1464   
  1465   1465   // SELECT
  1466   1466   trigger_cmd(A) ::= scanpt(B) select(X) scanpt(E).
  1467   1467      {A = sqlite3TriggerSelectStep(pParse->db, X, B, E); /*A-overwrites-X*/}
  1468   1468   
  1469   1469   // The special RAISE expression that may occur in trigger programs
  1470   1470   expr(A) ::= RAISE LP IGNORE RP.  {

Changes to src/sqliteInt.h.

  4045   4045     void sqlite3CodeRowTrigger(Parse*, Trigger *, int, ExprList*, int, Table *,
  4046   4046                               int, int, int);
  4047   4047     void sqlite3CodeRowTriggerDirect(Parse *, Trigger *, Table *, int, int, int);
  4048   4048     void sqliteViewTriggers(Parse*, Table*, Expr*, int, ExprList*);
  4049   4049     void sqlite3DeleteTriggerStep(sqlite3*, TriggerStep*);
  4050   4050     TriggerStep *sqlite3TriggerSelectStep(sqlite3*,Select*,
  4051   4051                                           const char*,const char*);
  4052         -  TriggerStep *sqlite3TriggerInsertStep(sqlite3*,Token*, IdList*,
         4052  +  TriggerStep *sqlite3TriggerInsertStep(Parse*,Token*, IdList*,
  4053   4053                                           Select*,u8,Upsert*,
  4054   4054                                           const char*,const char*);
  4055         -  TriggerStep *sqlite3TriggerUpdateStep(sqlite3*,Token*,ExprList*, Expr*, u8,
         4055  +  TriggerStep *sqlite3TriggerUpdateStep(Parse*,Token*,ExprList*, Expr*, u8,
  4056   4056                                           const char*,const char*);
  4057         -  TriggerStep *sqlite3TriggerDeleteStep(sqlite3*,Token*, Expr*,
         4057  +  TriggerStep *sqlite3TriggerDeleteStep(Parse*,Token*, Expr*,
  4058   4058                                           const char*,const char*);
  4059   4059     void sqlite3DeleteTrigger(sqlite3*, Trigger*);
  4060   4060     void sqlite3UnlinkAndDeleteTrigger(sqlite3*,int,const char*);
  4061   4061     u32 sqlite3TriggerColmask(Parse*,Trigger*,ExprList*,int,int,Table*,int);
  4062   4062   # define sqlite3ParseToplevel(p) ((p)->pToplevel ? (p)->pToplevel : (p))
  4063   4063   # define sqlite3IsToplevel(p) ((p)->pToplevel==0)
  4064   4064   #else

Changes to src/trigger.c.

   424    424   ** Build a trigger step out of an INSERT statement.  Return a pointer
   425    425   ** to the new trigger step.
   426    426   **
   427    427   ** The parser calls this routine when it sees an INSERT inside the
   428    428   ** body of a trigger.
   429    429   */
   430    430   TriggerStep *sqlite3TriggerInsertStep(
   431         -  sqlite3 *db,        /* The database connection */
          431  +  Parse *pParse,      /* Parser */
   432    432     Token *pTableName,  /* Name of the table into which we insert */
   433    433     IdList *pColumn,    /* List of columns in pTableName to insert into */
   434    434     Select *pSelect,    /* A SELECT statement that supplies values */
   435    435     u8 orconf,          /* The conflict algorithm (OE_Abort, OE_Replace, etc.) */
   436    436     Upsert *pUpsert,    /* ON CONFLICT clauses for upsert */
   437    437     const char *zStart, /* Start of SQL text */
   438    438     const char *zEnd    /* End of SQL text */
   439    439   ){
          440  +  sqlite3 *db = pParse->db;
   440    441     TriggerStep *pTriggerStep;
   441    442   
   442    443     assert(pSelect != 0 || db->mallocFailed);
   443    444   
   444    445     pTriggerStep = triggerStepAllocate(db, TK_INSERT, pTableName, zStart, zEnd);
   445    446     if( pTriggerStep ){
   446         -    pTriggerStep->pSelect = sqlite3SelectDup(db, pSelect, EXPRDUP_REDUCE);
          447  +    if( IN_RENAME_COLUMN ){
          448  +      pTriggerStep->pSelect = pSelect;
          449  +      pSelect = 0;
          450  +    }else{
          451  +      pTriggerStep->pSelect = sqlite3SelectDup(db, pSelect, EXPRDUP_REDUCE);
          452  +    }
   447    453       pTriggerStep->pIdList = pColumn;
   448    454       pTriggerStep->pUpsert = pUpsert;
   449    455       pTriggerStep->orconf = orconf;
   450    456     }else{
   451    457       testcase( pColumn );
   452    458       sqlite3IdListDelete(db, pColumn);
   453    459       testcase( pUpsert );
................................................................................
   460    466   
   461    467   /*
   462    468   ** Construct a trigger step that implements an UPDATE statement and return
   463    469   ** a pointer to that trigger step.  The parser calls this routine when it
   464    470   ** sees an UPDATE statement inside the body of a CREATE TRIGGER.
   465    471   */
   466    472   TriggerStep *sqlite3TriggerUpdateStep(
   467         -  sqlite3 *db,         /* The database connection */
          473  +  Parse *pParse,          /* Parser */
   468    474     Token *pTableName,   /* Name of the table to be updated */
   469    475     ExprList *pEList,    /* The SET clause: list of column and new values */
   470    476     Expr *pWhere,        /* The WHERE clause */
   471    477     u8 orconf,           /* The conflict algorithm. (OE_Abort, OE_Ignore, etc) */
   472    478     const char *zStart,  /* Start of SQL text */
   473    479     const char *zEnd     /* End of SQL text */
   474    480   ){
          481  +  sqlite3 *db = pParse->db;
   475    482     TriggerStep *pTriggerStep;
   476    483   
   477    484     pTriggerStep = triggerStepAllocate(db, TK_UPDATE, pTableName, zStart, zEnd);
   478    485     if( pTriggerStep ){
   479         -    pTriggerStep->pExprList = sqlite3ExprListDup(db, pEList, EXPRDUP_REDUCE);
   480         -    pTriggerStep->pWhere = sqlite3ExprDup(db, pWhere, EXPRDUP_REDUCE);
          486  +    if( IN_RENAME_COLUMN ){
          487  +      pTriggerStep->pExprList = pEList;
          488  +      pTriggerStep->pWhere = pWhere;
          489  +      pEList = 0;
          490  +      pWhere = 0;
          491  +    }else{
          492  +      pTriggerStep->pExprList = sqlite3ExprListDup(db, pEList, EXPRDUP_REDUCE);
          493  +      pTriggerStep->pWhere = sqlite3ExprDup(db, pWhere, EXPRDUP_REDUCE);
          494  +    }
   481    495       pTriggerStep->orconf = orconf;
   482    496     }
   483    497     sqlite3ExprListDelete(db, pEList);
   484    498     sqlite3ExprDelete(db, pWhere);
   485    499     return pTriggerStep;
   486    500   }
   487    501   
   488    502   /*
   489    503   ** Construct a trigger step that implements a DELETE statement and return
   490    504   ** a pointer to that trigger step.  The parser calls this routine when it
   491    505   ** sees a DELETE statement inside the body of a CREATE TRIGGER.
   492    506   */
   493    507   TriggerStep *sqlite3TriggerDeleteStep(
   494         -  sqlite3 *db,            /* Database connection */
          508  +  Parse *pParse,          /* Parser */
   495    509     Token *pTableName,      /* The table from which rows are deleted */
   496    510     Expr *pWhere,           /* The WHERE clause */
   497    511     const char *zStart,     /* Start of SQL text */
   498    512     const char *zEnd        /* End of SQL text */
   499    513   ){
          514  +  sqlite3 *db = pParse->db;
   500    515     TriggerStep *pTriggerStep;
   501    516   
   502    517     pTriggerStep = triggerStepAllocate(db, TK_DELETE, pTableName, zStart, zEnd);
   503    518     if( pTriggerStep ){
   504         -    pTriggerStep->pWhere = sqlite3ExprDup(db, pWhere, EXPRDUP_REDUCE);
          519  +    if( IN_RENAME_COLUMN ){
          520  +      pTriggerStep->pWhere = pWhere;
          521  +      pWhere = 0;
          522  +    }else{
          523  +      pTriggerStep->pWhere = sqlite3ExprDup(db, pWhere, EXPRDUP_REDUCE);
          524  +    }
   505    525       pTriggerStep->orconf = OE_Default;
   506    526     }
   507    527     sqlite3ExprDelete(db, pWhere);
   508    528     return pTriggerStep;
   509    529   }
   510    530   
   511    531   /* 

Changes to test/altercol.test.

   121    121   do_execsql_test -db db2 2.3 { SELECT biglongname FROM t3 }
   122    122   
   123    123   #-------------------------------------------------------------------------
   124    124   #
   125    125   do_execsql_test 3.0 {
   126    126     CREATE TABLE t4(x, y, z);
   127    127     CREATE TRIGGER ttt AFTER INSERT ON t4 WHEN new.y<0 BEGIN
   128         -    SELECT 1, 2, 3, 4;
          128  +    SELECT x, y, z FROM t4;
          129  +    DELETE FROM t4 WHERE y=32;
          130  +    UPDATE t4 SET x=y+1, y=0 WHERE y=32;
          131  +    INSERT INTO t4(x, y, z) SELECT 4, 5, 6 WHERE 0;
   129    132     END;
   130    133     INSERT INTO t4 VALUES(3, 2, 1);
   131    134   }
   132    135   
          136  +breakpoint
   133    137   do_execsql_test 3.1 {
   134    138     ALTER TABLE t4 RENAME y TO abc;
   135    139     SELECT sql FROM sqlite_master WHERE name='t4';
   136    140   } {{CREATE TABLE t4(x, abc, z)}}
   137    141   
   138         -db close
   139         -sqlite3 db test.db
   140         -
   141    142   do_execsql_test 3.2 {
   142    143     SELECT * FROM t4;
   143    144   } {3 2 1}
   144    145   
   145    146   do_execsql_test 3.3 { INSERT INTO t4 VALUES(6, 5, 4); } {}
          147  +
          148  +do_execsql_test 3.4 { SELECT sql FROM sqlite_master WHERE type='trigger' } {
          149  +{CREATE TRIGGER ttt AFTER INSERT ON t4 WHEN new.abc<0 BEGIN
          150  +    SELECT x, abc, z FROM t4;
          151  +    DELETE FROM t4 WHERE abc=32;
          152  +    UPDATE t4 SET x=abc+1, abc=0 WHERE abc=32;
          153  +    INSERT INTO t4(x, abc, z) SELECT 4, 5, 6 WHERE 0;
          154  +  END}
          155  +}
   146    156   
   147    157   #-------------------------------------------------------------------------
   148    158   #
   149    159   do_execsql_test 4.0 {
   150    160     CREATE TABLE c1(a, b, FOREIGN KEY (a, b) REFERENCES p1(c, d));
   151    161     CREATE TABLE p1(c, d, PRIMARY KEY(c, d));
   152    162     PRAGMA foreign_keys = 1;
................................................................................
   229    239     INSERT INTO c VALUES(0);
   230    240     CREATE TABLE t6("col a", "col b", "col c");
   231    241     CREATE TRIGGER zzz AFTER UPDATE OF "col a", "col c" ON t6 BEGIN
   232    242       UPDATE c SET x=x+1;
   233    243     END;
   234    244   }
   235    245   
   236         -do_execsql_test 7.1 {
          246  +do_execsql_test 7.1.1 {
   237    247     INSERT INTO t6 VALUES(0, 0, 0);
   238    248     UPDATE t6 SET "col c" = 1;
   239    249     SELECT * FROM c;
   240    250   } {1}
   241    251   
   242         -do_execsql_test 7.2 {
          252  +do_execsql_test 7.1.2 {
   243    253     ALTER TABLE t6 RENAME "col c" TO "col 3";
   244    254   }
   245    255   
   246         -do_execsql_test 7.3 {
          256  +do_execsql_test 7.1.3 {
   247    257     UPDATE t6 SET "col 3" = 0;
   248    258     SELECT * FROM c;
   249    259   } {2}
   250    260   
   251    261   #-------------------------------------------------------------------------
   252    262   # Views.
   253    263   #