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 |
Timelines: | family | ancestors | descendants | both | edit-trigger-wrapper |
Files: | files | file ages | folders |
SHA3-256: |
e272dc2b1c0edab59a40f32c77c81a3e |
User & Date: | dan 2018-08-15 20:28:39.044 |
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
Changes to src/alter.c.
︙ | ︙ | |||
978 979 980 981 982 983 984 985 986 987 988 989 990 991 | ** 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( 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") ){ | > > > > | 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 | ** 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") ){ |
︙ | ︙ | |||
1141 1142 1143 1144 1145 1146 1147 | 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 ){ | | | 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 | 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 ** error, but it will not be edited. */ sqlite3DbFree(db, sParse.zErrMsg); sParse.zErrMsg = 0; rc = SQLITE_OK; } if( rc!=SQLITE_OK ) goto renameColumnFunc_done; }else{ |
︙ | ︙ | |||
1183 1184 1185 1186 1187 1188 1189 | } } } }else if( sParse.pNewIndex ){ sqlite3WalkExprList(&sWalker, sParse.pNewIndex->aColExpr); sqlite3WalkExpr(&sWalker, sParse.pNewIndex->pPartIdxWhere); }else{ | > > > > > > > | > > | > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 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 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 | } } } }else if( sParse.pNewIndex ){ sqlite3WalkExprList(&sWalker, sParse.pNewIndex->aColExpr); 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 ){ if( pStep->pSelect ) sqlite3SelectPrep(&sParse, pStep->pSelect, &sNC); if( pStep->zTarget ){ Table *pTarget = sqlite3FindTable(db, pStep->zTarget, zDb); if( pTarget==0 ){ rc = SQLITE_ERROR; }else{ SrcList sSrc; memset(&sSrc, 0, sizeof(sSrc)); sSrc.nSrc = 1; sSrc.a[0].zName = pStep->zTarget; 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) ){ renameTokenFind(&sParse, &sCtx, (void*)zName); } } } if( pStep->op==TK_UPDATE ){ assert( pStep->pExprList ); for(i=0; i<pStep->pExprList->nExpr; i++){ char *zName = pStep->pExprList->a[i].zName; if( 0==sqlite3_stricmp(zName, zOld) ){ renameTokenFind(&sParse, &sCtx, (void*)zName); } } } } } } } if( rc!=SQLITE_OK ) goto renameColumnFunc_done; /* Find tokens to edit in UPDATE OF clause */ if( sParse.pTriggerTab==pTab && sParse.pNewTrigger->pColumns ){ for(i=0; i<sParse.pNewTrigger->pColumns->nId; i++){ char *zName = sParse.pNewTrigger->pColumns->a[i].zName; if( 0==sqlite3_stricmp(zName, zOld) ){ renameTokenFind(&sParse, &sCtx, (void*)zName); } } } /* Find tokens to edit in WHEN clause */ 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 ){ int nOut = nSql; |
︙ | ︙ |
Changes to src/expr.c.
︙ | ︙ | |||
1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 | if( pList ){ struct ExprList_item *pItem; assert( pList->nExpr>0 ); pItem = &pList->a[pList->nExpr-1]; assert( pItem->zName==0 ); pItem->zName = sqlite3DbStrNDup(pParse->db, pName->z, pName->n); if( dequote ) sqlite3Dequote(pItem->zName); } } /* ** Set the ExprList.a[].zSpan element of the most recently added item ** on the expression list. ** | > > > | 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 | if( pList ){ struct ExprList_item *pItem; assert( pList->nExpr>0 ); pItem = &pList->a[pList->nExpr-1]; assert( pItem->zName==0 ); pItem->zName = sqlite3DbStrNDup(pParse->db, pName->z, pName->n); if( dequote ) sqlite3Dequote(pItem->zName); if( IN_RENAME_COLUMN ){ sqlite3RenameToken(pParse, (void*)pItem->zName, pName); } } } /* ** Set the ExprList.a[].zSpan element of the most recently added item ** on the expression list. ** |
︙ | ︙ |
Changes to src/parse.y.
︙ | ︙ | |||
1447 1448 1449 1450 1451 1452 1453 | %type trigger_cmd {TriggerStep*} %destructor trigger_cmd {sqlite3DeleteTriggerStep(pParse->db, $$);} // UPDATE trigger_cmd(A) ::= UPDATE(B) orconf(R) trnm(X) tridxby SET setlist(Y) where_opt(Z) scanpt(E). | | | | | 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 | %type trigger_cmd {TriggerStep*} %destructor trigger_cmd {sqlite3DeleteTriggerStep(pParse->db, $$);} // UPDATE trigger_cmd(A) ::= UPDATE(B) orconf(R) trnm(X) tridxby SET setlist(Y) where_opt(Z) scanpt(E). {A = sqlite3TriggerUpdateStep(pParse, &X, Y, Z, R, B.z, E);} // INSERT trigger_cmd(A) ::= scanpt(B) insert_cmd(R) INTO trnm(X) idlist_opt(F) select(S) upsert(U) scanpt(Z). { A = sqlite3TriggerInsertStep(pParse,&X,F,S,R,U,B,Z);/*A-overwrites-R*/ } // DELETE trigger_cmd(A) ::= DELETE(B) FROM trnm(X) tridxby where_opt(Y) scanpt(E). {A = sqlite3TriggerDeleteStep(pParse, &X, Y, B.z, E);} // SELECT trigger_cmd(A) ::= scanpt(B) select(X) scanpt(E). {A = sqlite3TriggerSelectStep(pParse->db, X, B, E); /*A-overwrites-X*/} // The special RAISE expression that may occur in trigger programs expr(A) ::= RAISE LP IGNORE RP. { |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
4045 4046 4047 4048 4049 4050 4051 | void sqlite3CodeRowTrigger(Parse*, Trigger *, int, ExprList*, int, Table *, int, int, int); void sqlite3CodeRowTriggerDirect(Parse *, Trigger *, Table *, int, int, int); void sqliteViewTriggers(Parse*, Table*, Expr*, int, ExprList*); void sqlite3DeleteTriggerStep(sqlite3*, TriggerStep*); TriggerStep *sqlite3TriggerSelectStep(sqlite3*,Select*, const char*,const char*); | | | | | 4045 4046 4047 4048 4049 4050 4051 4052 4053 4054 4055 4056 4057 4058 4059 4060 4061 4062 4063 4064 | void sqlite3CodeRowTrigger(Parse*, Trigger *, int, ExprList*, int, Table *, int, int, int); void sqlite3CodeRowTriggerDirect(Parse *, Trigger *, Table *, int, int, int); void sqliteViewTriggers(Parse*, Table*, Expr*, int, ExprList*); void sqlite3DeleteTriggerStep(sqlite3*, TriggerStep*); TriggerStep *sqlite3TriggerSelectStep(sqlite3*,Select*, const char*,const char*); TriggerStep *sqlite3TriggerInsertStep(Parse*,Token*, IdList*, Select*,u8,Upsert*, const char*,const char*); TriggerStep *sqlite3TriggerUpdateStep(Parse*,Token*,ExprList*, Expr*, u8, const char*,const char*); TriggerStep *sqlite3TriggerDeleteStep(Parse*,Token*, Expr*, const char*,const char*); void sqlite3DeleteTrigger(sqlite3*, Trigger*); void sqlite3UnlinkAndDeleteTrigger(sqlite3*,int,const char*); u32 sqlite3TriggerColmask(Parse*,Trigger*,ExprList*,int,int,Table*,int); # define sqlite3ParseToplevel(p) ((p)->pToplevel ? (p)->pToplevel : (p)) # define sqlite3IsToplevel(p) ((p)->pToplevel==0) #else |
︙ | ︙ |
Changes to src/trigger.c.
︙ | ︙ | |||
424 425 426 427 428 429 430 | ** Build a trigger step out of an INSERT statement. Return a pointer ** to the new trigger step. ** ** The parser calls this routine when it sees an INSERT inside the ** body of a trigger. */ TriggerStep *sqlite3TriggerInsertStep( | | > > > > > | > | > > > > > > > | | > | > > > > > | > | 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 | ** Build a trigger step out of an INSERT statement. Return a pointer ** to the new trigger step. ** ** The parser calls this routine when it sees an INSERT inside the ** body of a trigger. */ TriggerStep *sqlite3TriggerInsertStep( Parse *pParse, /* Parser */ Token *pTableName, /* Name of the table into which we insert */ IdList *pColumn, /* List of columns in pTableName to insert into */ Select *pSelect, /* A SELECT statement that supplies values */ u8 orconf, /* The conflict algorithm (OE_Abort, OE_Replace, etc.) */ Upsert *pUpsert, /* ON CONFLICT clauses for upsert */ const char *zStart, /* Start of SQL text */ const char *zEnd /* End of SQL text */ ){ sqlite3 *db = pParse->db; TriggerStep *pTriggerStep; assert(pSelect != 0 || db->mallocFailed); pTriggerStep = triggerStepAllocate(db, TK_INSERT, pTableName, zStart, zEnd); if( pTriggerStep ){ if( IN_RENAME_COLUMN ){ pTriggerStep->pSelect = pSelect; pSelect = 0; }else{ pTriggerStep->pSelect = sqlite3SelectDup(db, pSelect, EXPRDUP_REDUCE); } pTriggerStep->pIdList = pColumn; pTriggerStep->pUpsert = pUpsert; pTriggerStep->orconf = orconf; }else{ testcase( pColumn ); sqlite3IdListDelete(db, pColumn); testcase( pUpsert ); sqlite3UpsertDelete(db, pUpsert); } sqlite3SelectDelete(db, pSelect); return pTriggerStep; } /* ** Construct a trigger step that implements an UPDATE statement and return ** a pointer to that trigger step. The parser calls this routine when it ** sees an UPDATE statement inside the body of a CREATE TRIGGER. */ TriggerStep *sqlite3TriggerUpdateStep( Parse *pParse, /* Parser */ Token *pTableName, /* Name of the table to be updated */ ExprList *pEList, /* The SET clause: list of column and new values */ Expr *pWhere, /* The WHERE clause */ u8 orconf, /* The conflict algorithm. (OE_Abort, OE_Ignore, etc) */ const char *zStart, /* Start of SQL text */ const char *zEnd /* End of SQL text */ ){ sqlite3 *db = pParse->db; TriggerStep *pTriggerStep; pTriggerStep = triggerStepAllocate(db, TK_UPDATE, pTableName, zStart, zEnd); if( pTriggerStep ){ if( IN_RENAME_COLUMN ){ pTriggerStep->pExprList = pEList; pTriggerStep->pWhere = pWhere; pEList = 0; pWhere = 0; }else{ pTriggerStep->pExprList = sqlite3ExprListDup(db, pEList, EXPRDUP_REDUCE); pTriggerStep->pWhere = sqlite3ExprDup(db, pWhere, EXPRDUP_REDUCE); } pTriggerStep->orconf = orconf; } sqlite3ExprListDelete(db, pEList); sqlite3ExprDelete(db, pWhere); return pTriggerStep; } /* ** Construct a trigger step that implements a DELETE statement and return ** a pointer to that trigger step. The parser calls this routine when it ** sees a DELETE statement inside the body of a CREATE TRIGGER. */ TriggerStep *sqlite3TriggerDeleteStep( Parse *pParse, /* Parser */ Token *pTableName, /* The table from which rows are deleted */ Expr *pWhere, /* The WHERE clause */ const char *zStart, /* Start of SQL text */ const char *zEnd /* End of SQL text */ ){ sqlite3 *db = pParse->db; TriggerStep *pTriggerStep; pTriggerStep = triggerStepAllocate(db, TK_DELETE, pTableName, zStart, zEnd); if( pTriggerStep ){ if( IN_RENAME_COLUMN ){ pTriggerStep->pWhere = pWhere; pWhere = 0; }else{ pTriggerStep->pWhere = sqlite3ExprDup(db, pWhere, EXPRDUP_REDUCE); } pTriggerStep->orconf = OE_Default; } sqlite3ExprDelete(db, pWhere); return pTriggerStep; } /* |
︙ | ︙ |
Changes to test/altercol.test.
︙ | ︙ | |||
121 122 123 124 125 126 127 | do_execsql_test -db db2 2.3 { SELECT biglongname FROM t3 } #------------------------------------------------------------------------- # do_execsql_test 3.0 { CREATE TABLE t4(x, y, z); CREATE TRIGGER ttt AFTER INSERT ON t4 WHEN new.y<0 BEGIN | | > > > > < < < > > > > > > > > > | 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 | do_execsql_test -db db2 2.3 { SELECT biglongname FROM t3 } #------------------------------------------------------------------------- # do_execsql_test 3.0 { CREATE TABLE t4(x, y, z); CREATE TRIGGER ttt AFTER INSERT ON t4 WHEN new.y<0 BEGIN SELECT x, y, z FROM t4; 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; } {3 2 1} do_execsql_test 3.3 { INSERT INTO t4 VALUES(6, 5, 4); } {} do_execsql_test 3.4 { SELECT sql FROM sqlite_master WHERE type='trigger' } { {CREATE TRIGGER ttt AFTER INSERT ON t4 WHEN new.abc<0 BEGIN SELECT x, abc, z FROM t4; DELETE FROM t4 WHERE abc=32; UPDATE t4 SET x=abc+1, abc=0 WHERE abc=32; INSERT INTO t4(x, abc, z) SELECT 4, 5, 6 WHERE 0; END} } #------------------------------------------------------------------------- # 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; |
︙ | ︙ | |||
229 230 231 232 233 234 235 | INSERT INTO c VALUES(0); CREATE TABLE t6("col a", "col b", "col c"); CREATE TRIGGER zzz AFTER UPDATE OF "col a", "col c" ON t6 BEGIN UPDATE c SET x=x+1; END; } | | | | | 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 | INSERT INTO c VALUES(0); CREATE TABLE t6("col a", "col b", "col c"); CREATE TRIGGER zzz AFTER UPDATE OF "col a", "col c" ON t6 BEGIN UPDATE c SET x=x+1; END; } do_execsql_test 7.1.1 { INSERT INTO t6 VALUES(0, 0, 0); UPDATE t6 SET "col c" = 1; SELECT * FROM c; } {1} do_execsql_test 7.1.2 { ALTER TABLE t6 RENAME "col c" TO "col 3"; } do_execsql_test 7.1.3 { UPDATE t6 SET "col 3" = 0; SELECT * FROM c; } {2} #------------------------------------------------------------------------- # Views. # |
︙ | ︙ |