Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | More complete parsing of UPSERT, including UPSERT within a trigger. The sqlite3Insert() logic to actually perform the UPSERT is not yet implemented, however. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | upsert |
Files: | files | file ages | folders |
SHA3-256: |
5cc2a5a315a2f26b392811de45b3dc35 |
User & Date: | drh 2018-04-07 15:04:05.872 |
Context
2018-04-10
| ||
00:30 | Update the top-level README.md file to point to the whynotget.html document. (check-in: 7dfd4543da user: drh tags: upsert) | |
2018-04-07
| ||
15:04 | More complete parsing of UPSERT, including UPSERT within a trigger. The sqlite3Insert() logic to actually perform the UPSERT is not yet implemented, however. (check-in: 5cc2a5a315 user: drh tags: upsert) | |
2018-04-06
| ||
19:36 | Demonstration of how the parser can be augmented to recognize a PostgreSQL-style UPSERT. This check-in implements parsing only. (check-in: 9b22905b15 user: drh tags: upsert) | |
Changes
Changes to src/insert.c.
︙ | |||
484 485 486 487 488 489 490 | 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 | - + + | ** D: cleanup */ void sqlite3Insert( Parse *pParse, /* Parser context */ SrcList *pTabList, /* Name of table into which we are inserting */ Select *pSelect, /* A SELECT statement to use as the data source */ IdList *pColumn, /* Column names corresponding to IDLIST. */ |
︙ | |||
522 523 524 525 526 527 528 529 530 531 532 533 534 535 | 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 | + + + + + | int *aRegIdx = 0; /* One register allocated to each index */ #ifndef SQLITE_OMIT_TRIGGER int isView; /* True if attempting to insert into a view */ Trigger *pTrigger; /* List of triggers on pTab, if required */ int tmask; /* Mask of trigger times */ #endif /* The conflict resolution type is always OE_Update or OE_Replace when ** there is an upsert clause */ assert( onError==OE_Update || pUpsert==0 ); assert( OE_Update==OE_Replace ); db = pParse->db; if( pParse->nErr || db->mallocFailed ){ goto insert_cleanup; } dest.iSDParm = 0; /* Suppress a harmless compiler warning */ |
︙ | |||
1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 | 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 | + | sqlite3VdbeSetNumCols(v, 1); sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows inserted", SQLITE_STATIC); } insert_cleanup: sqlite3SrcListDelete(db, pTabList); sqlite3ExprListDelete(db, pList); sqlite3ExprListDelete(db, pUpsert); sqlite3SelectDelete(db, pSelect); sqlite3IdListDelete(db, pColumn); sqlite3DbFree(db, aRegIdx); } /* Make sure "isView" and other macros defined above are undefined. Otherwise ** they may interfere with compilation of other functions in this file |
︙ |
Changes to src/parse.y.
︙ | |||
94 95 96 97 98 99 100 101 102 103 104 105 106 107 | 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 | + + + + + + + + + + + + + + + + | ** ** UPDATE ON (a,b,c) ** ** Then the "b" IdList records the list "a,b,c". */ struct TrigEvent { int a; IdList * b; }; /* ** An instance of this object holds the argument of the ON CONFLICT ** clause of an UPSERT. ** ** The ON CONFLICT clause takes three forms, identified by the Upsert.e ** field: ** ** OE_None: No ON CONFLICT clause ** OE_Ignore: ON CONFLICT DO NOTHING ** OE_Update: ON CONFLICT DO UPDATE ... */ struct Upsert { ExprList *p; /* column=expr entries for the UPDATE. Or NULL */ int e; /* OE_None, OE_Replace, or OE_Ignore */ }; /* ** Disable lookaside memory allocation for objects that might be ** shared across database connections. */ static void disableLookaside(Parse *pParse){ pParse->disableLookaside++; pParse->db->lookaside.bDisable++; |
︙ | |||
201 202 203 204 205 206 207 | 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 | - + | // The following directive causes tokens ABORT, AFTER, ASC, etc. to // fallback to ID if they will not parse as their original value. // This obviates the need for the "id" nonterminal. // %fallback ID ABORT ACTION AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST COLUMNKW |
︙ | |||
838 839 840 841 842 843 844 | 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 | - + + - + - + + + - - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + | } setlist(A) ::= LP idlist(X) RP EQ expr(Y). { A = sqlite3ExprListAppendVector(pParse, 0, X, Y); } ////////////////////////// The INSERT command ///////////////////////////////// // |
︙ | |||
1396 1397 1398 1399 1400 1401 1402 | 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 | - - - + + + + | // UPDATE trigger_cmd(A) ::= UPDATE(B) orconf(R) trnm(X) tridxby SET setlist(Y) where_opt(Z) scanpt(E). {A = sqlite3TriggerUpdateStep(pParse->db, &X, Y, Z, R, B.z, E);} // INSERT trigger_cmd(A) ::= scanpt(B) insert_cmd(R) INTO |
︙ |
Changes to src/sqliteInt.h.
︙ | |||
2042 2043 2044 2045 2046 2047 2048 2049 2050 2051 2052 2053 2054 2055 | 2042 2043 2044 2045 2046 2047 2048 2049 2050 2051 2052 2053 2054 2055 2056 | + | */ #define OE_None 0 /* There is no constraint to check */ #define OE_Rollback 1 /* Fail the operation and rollback the transaction */ #define OE_Abort 2 /* Back out changes but do no rollback transaction */ #define OE_Fail 3 /* Stop the operation but leave all prior changes */ #define OE_Ignore 4 /* Ignore the error. Do not do the INSERT or UPDATE */ #define OE_Replace 5 /* Delete existing record, then do INSERT or UPDATE */ #define OE_Update 5 /* An UPSERT. Same value as OE_Replace. */ #define OE_Restrict 6 /* OE_Abort for IMMEDIATE, OE_Rollback for DEFERRED */ #define OE_SetNull 7 /* Set the foreign key value to NULL */ #define OE_SetDflt 8 /* Set the foreign key value to its default */ #define OE_Cascade 9 /* Cascade the changes */ #define OE_Default 10 /* Do whatever the default action is */ |
︙ | |||
3203 3204 3205 3206 3207 3208 3209 | 3204 3205 3206 3207 3208 3209 3210 3211 3212 3213 3214 3215 3216 3217 3218 | - + | struct TriggerStep { u8 op; /* One of TK_DELETE, TK_UPDATE, TK_INSERT, TK_SELECT */ u8 orconf; /* OE_Rollback etc. */ Trigger *pTrig; /* The trigger that this step is a part of */ Select *pSelect; /* SELECT statement or RHS of INSERT INTO SELECT ... */ char *zTarget; /* Target table for DELETE, UPDATE, INSERT */ Expr *pWhere; /* The WHERE clause for DELETE or UPDATE steps */ |
︙ | |||
3736 3737 3738 3739 3740 3741 3742 | 3737 3738 3739 3740 3741 3742 3743 3744 3745 3746 3747 3748 3749 3750 3751 | - + | #ifndef SQLITE_OMIT_AUTOINCREMENT void sqlite3AutoincrementBegin(Parse *pParse); void sqlite3AutoincrementEnd(Parse *pParse); #else # define sqlite3AutoincrementBegin(X) # define sqlite3AutoincrementEnd(X) #endif |
︙ | |||
3912 3913 3914 3915 3916 3917 3918 | 3913 3914 3915 3916 3917 3918 3919 3920 3921 3922 3923 3924 3925 3926 3927 3928 | + - + | 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(sqlite3*,Token*, IdList*, Select*,u8,ExprList*, |
︙ |
Changes to src/trigger.c.
︙ | |||
412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 | 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 | + + | */ TriggerStep *sqlite3TriggerInsertStep( sqlite3 *db, /* The database connection */ 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.) */ ExprList *pUpsert, /* Upsert values */ const char *zStart, /* Start of SQL text */ const char *zEnd /* End of SQL text */ ){ TriggerStep *pTriggerStep; assert(pSelect != 0 || db->mallocFailed); pTriggerStep = triggerStepAllocate(db, TK_INSERT, pTableName, zStart, zEnd); if( pTriggerStep ){ pTriggerStep->pSelect = sqlite3SelectDup(db, pSelect, EXPRDUP_REDUCE); pTriggerStep->pIdList = pColumn; pTriggerStep->orconf = orconf; }else{ sqlite3IdListDelete(db, pColumn); sqlite3ExprListDelete(db, pUpsert); } sqlite3SelectDelete(db, pSelect); return pTriggerStep; } /* |
︙ | |||
751 752 753 754 755 756 757 | 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 | - + + | break; } case TK_INSERT: { sqlite3Insert(pParse, targetSrcList(pParse, pStep), sqlite3SelectDup(db, pStep->pSelect, 0), sqlite3IdListDup(db, pStep->pIdList), |
︙ |
Changes to tool/mkkeywordhash.c.
︙ | |||
182 183 184 185 186 187 188 189 190 191 192 193 194 195 | 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 | + | { "DEFAULT", "TK_DEFAULT", ALWAYS }, { "DEFERRED", "TK_DEFERRED", ALWAYS }, { "DEFERRABLE", "TK_DEFERRABLE", FKEY }, { "DELETE", "TK_DELETE", ALWAYS }, { "DESC", "TK_DESC", ALWAYS }, { "DETACH", "TK_DETACH", ATTACH }, { "DISTINCT", "TK_DISTINCT", ALWAYS }, { "DO", "TK_DO", ALWAYS }, { "DROP", "TK_DROP", ALWAYS }, { "END", "TK_END", ALWAYS }, { "EACH", "TK_EACH", TRIGGER }, { "ELSE", "TK_ELSE", ALWAYS }, { "ESCAPE", "TK_ESCAPE", ALWAYS }, { "EXCEPT", "TK_EXCEPT", COMPOUND }, { "EXCLUSIVE", "TK_EXCLUSIVE", ALWAYS }, |
︙ | |||
222 223 224 225 226 227 228 229 230 231 232 233 234 235 | 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 | + | { "LEFT", "TK_JOIN_KW", ALWAYS }, { "LIKE", "TK_LIKE_KW", ALWAYS }, { "LIMIT", "TK_LIMIT", ALWAYS }, { "MATCH", "TK_MATCH", ALWAYS }, { "NATURAL", "TK_JOIN_KW", ALWAYS }, { "NO", "TK_NO", FKEY }, { "NOT", "TK_NOT", ALWAYS }, { "NOTHING", "TK_NOTHING", ALWAYS }, { "NOTNULL", "TK_NOTNULL", ALWAYS }, { "NULL", "TK_NULL", ALWAYS }, { "OF", "TK_OF", ALWAYS }, { "OFFSET", "TK_OFFSET", ALWAYS }, { "ON", "TK_ON", ALWAYS }, { "OR", "TK_OR", ALWAYS }, { "ORDER", "TK_ORDER", ALWAYS }, |
︙ |