aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--Makefile.in6
-rw-r--r--Makefile.msc6
-rw-r--r--main.mk3
-rw-r--r--manifest36
-rw-r--r--manifest.uuid2
-rw-r--r--src/expr.c4
-rw-r--r--src/insert.c187
-rw-r--r--src/parse.y53
-rw-r--r--src/resolve.c2
-rw-r--r--src/sqliteInt.h59
-rw-r--r--src/trigger.c7
-rw-r--r--src/update.c6
-rw-r--r--src/upsert.c258
-rw-r--r--test/upsert1.test92
-rw-r--r--tool/mkkeywordhash.c8
-rw-r--r--tool/mksqlite3c.tcl1
16 files changed, 659 insertions, 71 deletions
diff --git a/Makefile.in b/Makefile.in
index 96ef34009..a3ec86964 100644
--- a/Makefile.in
+++ b/Makefile.in
@@ -187,7 +187,7 @@ LIBOBJS0 = alter.lo analyze.lo attach.lo auth.lo \
random.lo resolve.lo rowset.lo rtree.lo \
sqlite3session.lo select.lo sqlite3rbu.lo status.lo stmt.lo \
table.lo threads.lo tokenize.lo treeview.lo trigger.lo \
- update.lo util.lo vacuum.lo \
+ update.lo upsert.lo util.lo vacuum.lo \
vdbe.lo vdbeapi.lo vdbeaux.lo vdbeblob.lo vdbemem.lo vdbesort.lo \
vdbetrace.lo wal.lo walker.lo where.lo wherecode.lo whereexpr.lo \
utf.lo vtab.lo
@@ -284,6 +284,7 @@ SRC = \
$(TOP)/src/trigger.c \
$(TOP)/src/utf.c \
$(TOP)/src/update.c \
+ $(TOP)/src/upsert.c \
$(TOP)/src/util.c \
$(TOP)/src/vacuum.c \
$(TOP)/src/vdbe.c \
@@ -914,6 +915,9 @@ trigger.lo: $(TOP)/src/trigger.c $(HDR)
update.lo: $(TOP)/src/update.c $(HDR)
$(LTCOMPILE) $(TEMP_STORE) -c $(TOP)/src/update.c
+upsert.lo: $(TOP)/src/upsert.c $(HDR)
+ $(LTCOMPILE) $(TEMP_STORE) -c $(TOP)/src/upsert.c
+
utf.lo: $(TOP)/src/utf.c $(HDR)
$(LTCOMPILE) $(TEMP_STORE) -c $(TOP)/src/utf.c
diff --git a/Makefile.msc b/Makefile.msc
index 7d32af239..50119d836 100644
--- a/Makefile.msc
+++ b/Makefile.msc
@@ -1193,7 +1193,7 @@ LIBOBJS0 = vdbe.lo parse.lo alter.lo analyze.lo attach.lo auth.lo \
random.lo resolve.lo rowset.lo rtree.lo \
sqlite3session.lo select.lo sqlite3rbu.lo status.lo \
table.lo threads.lo tokenize.lo treeview.lo trigger.lo \
- update.lo util.lo vacuum.lo \
+ update.lo upsert.lo util.lo vacuum.lo \
vdbeapi.lo vdbeaux.lo vdbeblob.lo vdbemem.lo vdbesort.lo \
vdbetrace.lo wal.lo walker.lo where.lo wherecode.lo whereexpr.lo \
utf.lo vtab.lo
@@ -1292,6 +1292,7 @@ SRC01 = \
$(TOP)\src\trigger.c \
$(TOP)\src\utf.c \
$(TOP)\src\update.c \
+ $(TOP)\src\upsert.c \
$(TOP)\src\util.c \
$(TOP)\src\vacuum.c \
$(TOP)\src\vdbe.c \
@@ -1993,6 +1994,9 @@ trigger.lo: $(TOP)\src\trigger.c $(HDR)
update.lo: $(TOP)\src\update.c $(HDR)
$(LTCOMPILE) $(CORE_COMPILE_OPTS) -c $(TOP)\src\update.c
+upsert.lo: $(TOP)\src\upsert.c $(HDR)
+ $(LTCOMPILE) $(CORE_COMPILE_OPTS) -c $(TOP)\src\upsert.c
+
utf.lo: $(TOP)\src\utf.c $(HDR)
$(LTCOMPILE) $(CORE_COMPILE_OPTS) -c $(TOP)\src\utf.c
diff --git a/main.mk b/main.mk
index e5722c582..b590b79cb 100644
--- a/main.mk
+++ b/main.mk
@@ -72,7 +72,7 @@ LIBOBJ+= vdbe.o parse.o \
random.o resolve.o rowset.o rtree.o \
select.o sqlite3rbu.o status.o stmt.o \
table.o threads.o tokenize.o treeview.o trigger.o \
- update.o userauth.o util.o vacuum.o \
+ update.o upsert.o userauth.o util.o vacuum.o \
vdbeapi.o vdbeaux.o vdbeblob.o vdbemem.o vdbesort.o \
vdbetrace.o wal.o walker.o where.o wherecode.o whereexpr.o \
utf.o vtab.o
@@ -162,6 +162,7 @@ SRC = \
$(TOP)/src/trigger.c \
$(TOP)/src/utf.c \
$(TOP)/src/update.c \
+ $(TOP)/src/upsert.c \
$(TOP)/src/util.c \
$(TOP)/src/vacuum.c \
$(TOP)/src/vdbe.c \
diff --git a/manifest b/manifest
index db3eba976..be91125bd 100644
--- a/manifest
+++ b/manifest
@@ -1,10 +1,10 @@
-C Increase\sthe\sversion\snumber\sto\s3.24.0
-D 2018-04-16T10:41:15.351
+C Merge\schanges\sfrom\strunk.
+D 2018-04-16T10:47:38.084
F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1
F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea
-F Makefile.in 7016fc56c6b9bfe5daac4f34be8be38d8c0b5fab79ccbfb764d3b23bf1c6fff3
+F Makefile.in 5ce9343cba9c189046f1afe6d2bcc1f68079439febc05267b98aec6ecc752439
F Makefile.linux-gcc 7bc79876b875010e8c8f9502eb935ca92aa3c434
-F Makefile.msc 43dd6ae2e5a2bb8df7bfc9ed85935129caffeafb8c1803d24c5d038b1e74c8ca
+F Makefile.msc 59179295f6a9b433e3f59a6dc2fcf6db6fcac35d92015294beb5d27f2924ebb9
F README.md 7764d56778d567913ef11c82da9ab94aefa0826f7c243351e4e2d7adaef6f373
F VERSION b7c9d1d11cb70ef8e90cfcf3c944aa58a9f801cc2ad487eebb0a110c16dfc2df
F aclocal.m4 a5c22d164aff7ed549d53a90fa56d56955281f50
@@ -413,7 +413,7 @@ F ext/userauth/userauth.c 3410be31283abba70255d71fd24734e017a4497f
F install-sh 9d4de14ab9fb0facae2f48780b874848cbf2f895 x
F ltmain.sh 3ff0879076df340d2e23ae905484d8c15d5fdea8
F magic.txt 8273bf49ba3b0c8559cb2774495390c31fd61c60
-F main.mk 63668484c95454af7fc04a384da27ac556f27368d6d0c345e405e1677c66768f
+F main.mk 068618fe288bf9d93091b808be73e26a93fb2db8435328cc5760f9dd35ba168b
F mkso.sh fd21c06b063bb16a5d25deea1752c2da6ac3ed83
F mptest/config01.test 3c6adcbc50b991866855f1977ff172eb6d901271
F mptest/config02.test 4415dfe36c48785f751e16e32c20b077c28ae504
@@ -443,7 +443,7 @@ F src/date.c ebe1dc7c8a347117bb02570f1a931c62dd78f4a2b1b516f4837d45b7d6426957
F src/dbpage.c 8db4c97f630e7d83f884ea75caf1ffd0988c160e9d530194d93721c80821e0f6
F src/dbstat.c edabb82611143727511a45ca0859b8cd037851ebe756ae3db289859dd18b6f91
F src/delete.c 20c8788451dc737a967c87ea53ad43544d617f5b57d32ccce8bd52a0daf9e89b
-F src/expr.c ca52b09125b9619062202eac54b69df5fff170a84dcbc961c19402de6dd6dfeb
+F src/expr.c fc607dd6338ac73c7fdc962734575d7136966cd9dbb69f8c75104d3d272bea96
F src/fault.c 460f3e55994363812d9d60844b2a6de88826e007
F src/fkey.c d617daf66b5515e2b42c1405b2b4984c30ca50fb705ab164271a9bf66c69e331
F src/func.c 94f42cba2cc1c34aeaa441022ba0170ec3fec4bba54db4e0ded085c6dc0fdc51
@@ -452,7 +452,7 @@ F src/hash.c a12580e143f10301ed5166ea4964ae2853d3905a511d4e0c44497245c7ce1f7a
F src/hash.h ab34c5c54a9e9de2e790b24349ba5aab3dbb4fd4
F src/hwtime.h 747c1bbe9df21a92e9c50f3bbec1de841dc5e5da
F src/in-operator.md 10cd8f4bcd225a32518407c2fb2484089112fd71
-F src/insert.c 752740e4619416d4262f6e9e51cdb6af5965eb0c8e943832a5af77d41e2839c7
+F src/insert.c be6ece1650d14f4aa850786f7e08376fc16841423b273759167584c4cd0fda0a
F src/legacy.c 134ab3e3fae00a0f67a5187981d6935b24b337bcf0f4b3e5c9fa5763da95bf4e
F src/loadext.c f6e4e416a736369f9e80eba609f0acda97148a8b0453784d670c78d3eed2f302
F src/main.c 1648fc7a9bcfdbfd9a9a04af96ff2796c3164b3f3c7e56ed63a3c51cd11d198d
@@ -480,7 +480,7 @@ F src/os_win.c eb03c6d52f893bcd7fdd4c6006674c13c1b5e49543fec98d605201af2997171c
F src/os_win.h 7b073010f1451abe501be30d12f6bc599824944a
F src/pager.c 1bb6a57fa0465296a4d6109a1a64610a0e7adde1f3acf3ef539a9d972908ce8f
F src/pager.h c571b064df842ec8f2e90855dead9acf4cbe0d1b2c05afe0ef0d0145f7fd0388
-F src/parse.y e3c4116efb7d693df412bd42a96c88c6463704d31a29342c2fa671f6e91ecb26
+F src/parse.y 5fde091c63a99b6f0867b03a4b1941b862c8866aa91478b78dca84ff24b535ac
F src/pcache.c 135ef0bc6fb2e3b7178d49ab5c9176254c8a691832c1bceb1156b2fbdd0869bd
F src/pcache.h 072f94d29281cffd99e46c1539849f248c4b56ae7684c1f36626797fee375170
F src/pcache1.c 716975564c15eb6679e97f734cec1bfd6c16ac3d4010f05f1f8e509fc7d19880
@@ -489,14 +489,14 @@ F src/pragma.h bb83728944b42f6d409c77f5838a8edbdb0fe83046c5496ffc9602b40340a324
F src/prepare.c b086fea6a1952db88beca31fdd621201ee5e4ce3f02905248cc3035a8174aa89
F src/printf.c d3b7844ddeb11fbbdd38dd84d09c9c1ac171d21fb038473c3aa97981201cc660
F src/random.c 80f5d666f23feb3e6665a6ce04c7197212a88384
-F src/resolve.c b3ace63b0470cb0063c2acc3a1dcb95eb432be45ed3c931854694f8a000cd763
+F src/resolve.c 515ffc6678e47185a77f4fb724d934a29b991c84bf261980001e254e5e6e61ad
F src/rowset.c 7b7e7e479212e65b723bf40128c7b36dc5afdfac
F src/select.c 3e84cb869930aa8fcacd3acbb1a0ec2d82736c8479d6a4367a5f1a926fb8a763
F src/shell.c.in cc960721e56ebc1a78773bb5d2f5608b54275f945cbe49e4afe919d6888062a7
F src/sqlite.h.in e0be726ea6e4e6571724d39d242472ecd8bd1ba6f84ade88e1641bde98a6d02b
F src/sqlite3.rc 5121c9e10c3964d5755191c80dd1180c122fc3a8
F src/sqlite3ext.h 83a3c4ce93d650bedfd1aa558cb85a516bd6d094445ee989740827d0d944368d
-F src/sqliteInt.h 155bd93e3d6dd215cf3f48c7817576ae086fcb5acc9d59cfefa3f720959928af
+F src/sqliteInt.h 1e32dd08eac0f988f775fa33015d12666f443a0e4eabf3b490cf41aad0b611de
F src/sqliteLimit.h 1513bfb7b20378aa0041e7022d04acb73525de35b80b252f1b83fedb4de6a76b
F src/status.c 46e7aec11f79dad50965a5ca5fa9de009f7d6bde08be2156f1538a0a296d4d0e
F src/table.c b46ad567748f24a326d9de40e5b9659f96ffff34
@@ -555,8 +555,9 @@ F src/test_wsd.c 41cadfd9d97fe8e3e4e44f61a4a8ccd6f7ca8fe9
F src/threads.c 4ae07fa022a3dc7c5beb373cf744a85d3c5c6c3c
F src/tokenize.c 5b0c661a85f783d35b9883830736eeb63be4aefc4f6b7d9cd081d48782c041e2
F src/treeview.c 14d5d1254702ec96876aa52642cb31548612384134970409fae333b25b39d6bb
-F src/trigger.c a34539c69433276d37b0da9a89c117726ff2d292c0902895af1f393a983cd3a1
-F src/update.c 97d4c9514229f540f8c441e124d5af7f93c5b030c9574539d01e99462e273998
+F src/trigger.c 00ef0b16ab3f0063439e6582086f57f3beb93cd7e7ba46569a8bdc490c16283d
+F src/update.c f5210fb55d26e20d14d0106c9479a83c63a005b70b1b5291481c48d6dac6fb9f
+F src/upsert.c 3baeab51df965e8442ff0c338be89d1b940f0e56cfe2b0a59ef90b4bb5f55da2
F src/utf.c 810fbfebe12359f10bc2a011520a6e10879ab2a163bcb26c74768eab82ea62a5
F src/util.c d9eb0a6c4aae1b00a7369eadd7ca0bbe946cb4c953b6751aa20d357c2f482157
F src/vacuum.c 762ee9bbf8733d87d8cd06f58d950e881982e416f8c767334a40ffd341b6bff5
@@ -1506,6 +1507,7 @@ F test/unixexcl.test d936ba2b06794018e136418addd59a2354eeae97
F test/unordered.test ca7adce0419e4ca0c50f039885e76ed2c531eda8
F test/update.test 6c68446b8a0a33d522a7c72b320934596a2d7d32
F test/update2.test 5e67667e1c54017d964e626db765cf8bedcf87483c184f4c575bdb8c1dd2313e
+F test/upsert1.test 3b4e8e5932516115bfffb2269a44c416c5c26d0d57cc7dd16954d0b77fbc4cd9
F test/uri.test 3481026f00ade6dfe8adb7acb6e1e47b04369568
F test/uri2.test 9d3ba7a53ee167572d53a298ee4a5d38ec4a8fb7
F test/userauth01.test e740a2697a7b40d7c5003a7d7edaee16acd349a9
@@ -1645,7 +1647,7 @@ F tool/max-limits.c cbb635fbb37ae4d05f240bfb5b5270bb63c54439
F tool/mkautoconfamal.sh 422fc365358a2e92876ffc62971a0ff28ed472fc8bcf9de0df921c736fdeca5e
F tool/mkccode.tcl 86463e68ce9c15d3041610fedd285ce32a5cf7a58fc88b3202b8b76837650dbe x
F tool/mkctimec.tcl dd183b73ae1c28249669741c250525f0407e579a70482371668fd5f130d9feb3
-F tool/mkkeywordhash.c 2e852ac0dfdc5af18886dc1ce7e9676d11714ae3df0a282dc7d90b3a0fe2033c
+F tool/mkkeywordhash.c 9daf08059d76b27b7d7b5729763ad01c5479d04ffe9d5ead87a864a01607e619
F tool/mkmsvcmin.tcl cad0c7b54d7dd92bc87d59f36d4cc4f070eb2e625f14159dc2f5c4204e6a13ea
F tool/mkopcodec.tcl d1b6362bd3aa80d5520d4d6f3765badf01f6c43c
F tool/mkopcodeh.tcl 4ee2a30ccbd900dc4d5cdb61bdab87cd2166cd2affcc78c9cc0b8d22a65b2eee
@@ -1655,7 +1657,7 @@ F tool/mkshellc.tcl 1f45770aea226ac093a9c72f718efbb88a2a2833409ec2e1c4cecae42026
F tool/mksourceid.c d458f9004c837bee87a6382228ac20d3eae3c49ea3b0a5aace936f8b60748d3b
F tool/mkspeedsql.tcl a1a334d288f7adfe6e996f2e712becf076745c97
F tool/mksqlite3c-noext.tcl fef88397668ae83166735c41af99d79f56afaabb
-F tool/mksqlite3c.tcl a03cee30de81a2e67b93e5c659f24113a003677c557daeb008205c8e6d4345d6
+F tool/mksqlite3c.tcl d532ccbe81234f766bab6e5c0451c99529debcea138caccc1862a9645b2d54f2
F tool/mksqlite3h.tcl 080873e3856eceb9d289a08a00c4b30f875ea3feadcbece796bd509b1532792c
F tool/mksqlite3internalh.tcl eb994013e833359137eb53a55acdad0b5ae1049b
F tool/mkvsix.tcl b9e0777a213c23156b6542842c238479e496ebf5
@@ -1717,7 +1719,7 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93
F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc
F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e
F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0
-P dba3095feeeb55b5c0ebe33bdd4be8ba1f24478406685d3a042a69d9c380e742
-R 50577be4101ba6800e0ceb3fdb5a7f41
+P d3c53fd3177946f50137d48da871de43d78d10ef9990cc4ea6750f7020f89b6a f94528e1c70765c8649750b2d9c17f23e81f191554ff56d905a9abe3fb6037b7
+R bf422b59ff7f1c6f6ea70f77a810e010
U drh
-Z fd8d37ec919dd409461d7d6bfec613a5
+Z 82a412abb60d38c0dadc6c5d2ed63354
diff --git a/manifest.uuid b/manifest.uuid
index a7a4e3500..a1fc6a18f 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-f94528e1c70765c8649750b2d9c17f23e81f191554ff56d905a9abe3fb6037b7 \ No newline at end of file
+54d96772e78b7f57d5b590aebe34a139ade73629aebe16677372650b33513b1d \ No newline at end of file
diff --git a/src/expr.c b/src/expr.c
index 73a441644..63b79c2f0 100644
--- a/src/expr.c
+++ b/src/expr.c
@@ -4899,8 +4899,10 @@ int sqlite3ExprCompare(Parse *pParse, Expr *pA, Expr *pB, int iTab){
if( pA->op!=TK_COLUMN && pA->op!=TK_AGG_COLUMN && pA->u.zToken ){
if( pA->op==TK_FUNCTION ){
if( sqlite3StrICmp(pA->u.zToken,pB->u.zToken)!=0 ) return 2;
+ }else if( pA->op==TK_COLLATE ){
+ return sqlite3_stricmp(pA->u.zToken,pB->u.zToken)!=0 ? 2 : 0;
}else if( strcmp(pA->u.zToken,pB->u.zToken)!=0 ){
- return pA->op==TK_COLLATE ? 1 : 2;
+ return 2;
}
}
if( (pA->flags & EP_Distinct)!=(pB->flags & EP_Distinct) ) return 2;
diff --git a/src/insert.c b/src/insert.c
index 7124e6658..d98f127c5 100644
--- a/src/insert.c
+++ b/src/insert.c
@@ -488,7 +488,8 @@ void sqlite3Insert(
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. */
- int onError /* How to handle constraint errors */
+ int onError, /* How to handle constraint errors */
+ Upsert *pUpsert /* ON CONFLICT clauses for upsert, or NULL */
){
sqlite3 *db; /* The main database structure */
Table *pTab; /* The table to insert into. aka TABLE */
@@ -803,6 +804,16 @@ void sqlite3Insert(
pParse->nMem += pIdx->nColumn;
}
}
+#ifndef SQLITE_OMIT_UPSERT
+ if( pUpsert ){
+ pTabList->a[0].iCursor = iDataCur;
+ pUpsert->pUpsertSrc = pTabList;
+ if( pUpsert->pUpsertTarget ){
+ sqlite3UpsertAnalyzeTarget(pParse, pTabList, pUpsert);
+ }
+ }
+#endif
+
/* This is the top of the main insertion loop */
if( useTempTable ){
@@ -1005,7 +1016,7 @@ void sqlite3Insert(
int isReplace; /* Set to true if constraints may cause a replace */
int bUseSeek; /* True to use OPFLAG_SEEKRESULT */
sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur,
- regIns, 0, ipkColumn>=0, onError, endOfLoop, &isReplace, 0
+ regIns, 0, ipkColumn>=0, onError, endOfLoop, &isReplace, 0, pUpsert
);
sqlite3FkCheck(pParse, pTab, 0, regIns, 0, 0);
@@ -1074,6 +1085,7 @@ insert_end:
insert_cleanup:
sqlite3SrcListDelete(db, pTabList);
sqlite3ExprListDelete(db, pList);
+ sqlite3UpsertDelete(db, pUpsert);
sqlite3SelectDelete(db, pSelect);
sqlite3IdListDelete(db, pColumn);
sqlite3DbFree(db, aRegIdx);
@@ -1146,6 +1158,42 @@ static int checkConstraintUnchanged(Expr *pExpr, int *aiChng, int chngRowid){
}
/*
+** An instance of the ConstraintAddr object remembers the byte-code addresses
+** for sections of the constraint checks that deal with uniqueness constraints
+** on the rowid and on the upsert constraint.
+**
+** This information is passed into checkReorderConstraintChecks() to insert
+** some OP_Goto operations so that the rowid and upsert constraints occur
+** in the correct order relative to other constraints.
+*/
+typedef struct ConstraintAddr ConstraintAddr;
+struct ConstraintAddr {
+ int ipkTop; /* Subroutine for rowid constraint check */
+ int upsertTop; /* Label for upsert constraint check subroutine */
+ int ipkBtm; /* Return opcode rowid constraint check */
+ int upsertBtm; /* upsert constraint returns to this label */
+};
+
+/*
+** Generate any OP_Goto operations needed to cause constraints to be
+** run that haven't already been run.
+*/
+static void reorderConstraintChecks(Vdbe *v, ConstraintAddr *p){
+ if( p->upsertTop ){
+ sqlite3VdbeGoto(v, p->upsertTop);
+ VdbeComment((v, "call upsert subroutine"));
+ sqlite3VdbeResolveLabel(v, p->upsertBtm);
+ p->upsertTop = 0;
+ }
+ if( p->ipkTop ){
+ sqlite3VdbeGoto(v, p->ipkTop);
+ VdbeComment((v, "call rowid constraint-check subroutine"));
+ sqlite3VdbeJumpHere(v, p->ipkBtm);
+ p->ipkTop = 0;
+ }
+}
+
+/*
** Generate code to do constraint checks prior to an INSERT or an UPDATE
** on table pTab.
**
@@ -1240,7 +1288,8 @@ void sqlite3GenerateConstraintChecks(
u8 overrideError, /* Override onError to this if not OE_Default */
int ignoreDest, /* Jump to this label on an OE_Ignore resolution */
int *pbMayReplace, /* OUT: Set to true if constraint may cause a replace */
- int *aiChng /* column i is unchanged if aiChng[i]<0 */
+ int *aiChng, /* column i is unchanged if aiChng[i]<0 */
+ Upsert *pUpsert /* ON CONFLICT clauses, if any. NULL otherwise */
){
Vdbe *v; /* VDBE under constrution */
Index *pIdx; /* Pointer to one of the indices */
@@ -1253,10 +1302,11 @@ void sqlite3GenerateConstraintChecks(
int addr1; /* Address of jump instruction */
int seenReplace = 0; /* True if REPLACE is used to resolve INT PK conflict */
int nPkField; /* Number of fields in PRIMARY KEY. 1 for ROWID tables */
- int ipkTop = 0; /* Top of the rowid change constraint check */
- int ipkBottom = 0; /* Bottom of the rowid change constraint check */
+ ConstraintAddr sAddr;/* Address information for constraint reordering */
+ Index *pUpIdx = 0; /* Index to which to apply the upsert */
u8 isUpdate; /* True if this is an UPDATE operation */
u8 bAffinityDone = 0; /* True if the OP_Affinity operation has been run */
+ int upsertBypass = 0; /* Address of Goto to bypass upsert subroutine */
isUpdate = regOldData!=0;
db = pParse->db;
@@ -1264,6 +1314,8 @@ void sqlite3GenerateConstraintChecks(
assert( v!=0 );
assert( pTab->pSelect==0 ); /* This table is not a VIEW */
nCol = pTab->nCol;
+ sAddr.ipkTop = 0;
+ sAddr.upsertTop = 0;
/* pPk is the PRIMARY KEY index for WITHOUT ROWID tables and NULL for
** normal rowid tables. nPkField is the number of key fields in the
@@ -1363,6 +1415,53 @@ void sqlite3GenerateConstraintChecks(
}
#endif /* !defined(SQLITE_OMIT_CHECK) */
+ /* UNIQUE and PRIMARY KEY constraints should be handled in the following
+ ** order:
+ **
+ ** (1) OE_Abort, OE_Fail, OE_Rollback, OE_Ignore
+ ** (2) OE_Update
+ ** (3) OE_Replace
+ **
+ ** OE_Fail and OE_Ignore must happen before any changes are made.
+ ** OE_Update guarantees that only a single row will change, so it
+ ** must happen before OE_Replace. Technically, OE_Abort and OE_Rollback
+ ** could happen in any order, but they are grouped up front for
+ ** convenience.
+ **
+ ** Constraint checking code is generated in this order:
+ ** (A) The rowid constraint
+ ** (B) Unique index constraints that do not have OE_Replace as their
+ ** default conflict resolution strategy
+ ** (C) Unique index that do use OE_Replace by default.
+ **
+ ** The ordering of (2) and (3) is accomplished by making sure the linked
+ ** list of indexes attached to a table puts all OE_Replace indexes last
+ ** in the list. See sqlite3CreateIndex() for where that happens.
+ */
+
+ /* If there is an ON CONFLICT clause without a constraint-target
+ ** (In other words, one of "ON CONFLICT DO NOTHING" or
+ ** "ON DUPLICATION KEY UPDATE") then change the overrideError to
+ ** whichever is appropriate.
+ */
+ if( pUpsert ){
+ if( pUpsert->pUpsertTarget==0 ){
+ if( pUpsert->pUpsertSet==0 ){
+ /* An ON CONFLICT DO NOTHING clause, without a constraint-target.
+ ** Make all unique constraint resolution be OE_Ignore */
+ overrideError = OE_Ignore;
+ pUpsert = 0;
+ }else{
+ /* An ON DUPLICATE KEY UPDATE clause. All unique constraints
+ ** do upsert processing */
+ overrideError = OE_Update;
+ }
+ }else if( (pUpIdx = pUpsert->pUpsertIdx)!=0 ){
+ sAddr.upsertTop = sqlite3VdbeMakeLabel(v);
+ sAddr.upsertBtm = sqlite3VdbeMakeLabel(v);
+ }
+ }
+
/* If rowid is changing, make sure the new rowid does not previously
** exist in the table.
*/
@@ -1386,26 +1485,38 @@ void sqlite3GenerateConstraintChecks(
VdbeCoverage(v);
}
+ /* figure out whether or not upsert applies in this case */
+ if( pUpsert && pUpsert->pUpsertIdx==0 ){
+ if( pUpsert->pUpsertSet==0 ){
+ onError = OE_Ignore; /* DO NOTHING is the same as INSERT OR IGNORE */
+ }else{
+ onError = OE_Update; /* DO UPDATE */
+ }
+ }
+
/* If the response to a rowid conflict is REPLACE but the response
** to some other UNIQUE constraint is FAIL or IGNORE, then we need
** to defer the running of the rowid conflict checking until after
** the UNIQUE constraints have run.
*/
- if( onError==OE_Replace && overrideError!=OE_Replace ){
- for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
- if( pIdx->onError==OE_Ignore || pIdx->onError==OE_Fail ){
- ipkTop = sqlite3VdbeAddOp0(v, OP_Goto);
- break;
- }
- }
+ assert( OE_Update>OE_Replace );
+ assert( OE_Ignore<OE_Replace );
+ assert( OE_Fail<OE_Replace );
+ assert( OE_Abort<OE_Replace );
+ assert( OE_Rollback<OE_Replace );
+ if( onError>=OE_Replace
+ && onError!=overrideError
+ && pTab->pIndex
+ ){
+ sAddr.ipkTop = sqlite3VdbeAddOp0(v, OP_Goto)+1;
}
/* Check to see if the new rowid already exists in the table. Skip
** the following conflict logic if it does not. */
+ VdbeNoopComment((v, "constraint checks for ROWID"));
sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, addrRowidOk, regNewData);
VdbeCoverage(v);
- /* Generate code that deals with a rowid collision */
switch( onError ){
default: {
onError = OE_Abort;
@@ -1466,16 +1577,21 @@ void sqlite3GenerateConstraintChecks(
seenReplace = 1;
break;
}
+#ifndef SQLITE_OMIT_UPSERT
+ case OE_Update: {
+ sqlite3UpsertDoUpdate(pParse, pUpsert, pTab, 0, iDataCur, 0);
+ /* Fall through */
+ }
+#endif
case OE_Ignore: {
- /*assert( seenReplace==0 );*/
sqlite3VdbeGoto(v, ignoreDest);
break;
}
}
sqlite3VdbeResolveLabel(v, addrRowidOk);
- if( ipkTop ){
- ipkBottom = sqlite3VdbeAddOp0(v, OP_Goto);
- sqlite3VdbeJumpHere(v, ipkTop);
+ if( sAddr.ipkTop ){
+ sAddr.ipkBtm = sqlite3VdbeAddOp0(v, OP_Goto);
+ sqlite3VdbeJumpHere(v, sAddr.ipkTop-1);
}
}
@@ -1493,12 +1609,17 @@ void sqlite3GenerateConstraintChecks(
int addrUniqueOk; /* Jump here if the UNIQUE constraint is satisfied */
if( aRegIdx[ix]==0 ) continue; /* Skip indices that do not change */
+ VdbeNoopComment((v, "constraint checks for %s", pIdx->zName));
if( bAffinityDone==0 ){
sqlite3TableAffinity(v, pTab, regNewData+1);
bAffinityDone = 1;
}
iThisCur = iIdxCur+ix;
- addrUniqueOk = sqlite3VdbeMakeLabel(v);
+ if( pUpIdx==pIdx ){
+ addrUniqueOk = sAddr.upsertBtm;
+ }else{
+ addrUniqueOk = sqlite3VdbeMakeLabel(v);
+ }
/* Skip partial indices for which the WHERE clause is not true */
if( pIdx->pPartIdxWhere ){
@@ -1557,6 +1678,21 @@ void sqlite3GenerateConstraintChecks(
}else if( onError==OE_Default ){
onError = OE_Abort;
}
+ if( onError==OE_Replace ){
+ reorderConstraintChecks(v, &sAddr);
+ }
+
+ /* Figure out if the upsert clause applies to this index */
+ if( pUpIdx==pIdx ){
+ if( pUpsert->pUpsertSet==0 ){
+ onError = OE_Ignore; /* DO NOTHING is the same as INSERT OR IGNORE */
+ }else{
+ onError = OE_Update; /* DO UPDATE */
+ }
+ upsertBypass = sqlite3VdbeGoto(v, 0);
+ VdbeComment((v, "Upsert bypass"));
+ sqlite3VdbeResolveLabel(v, sAddr.upsertTop);
+ }
/* Collision detection may be omitted if all of the following are true:
** (1) The conflict resolution algorithm is REPLACE
@@ -1640,7 +1776,7 @@ void sqlite3GenerateConstraintChecks(
/* Generate code that executes if the new index entry is not unique */
assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail
- || onError==OE_Ignore || onError==OE_Replace );
+ || onError==OE_Ignore || onError==OE_Replace || onError==OE_Update );
switch( onError ){
case OE_Rollback:
case OE_Abort:
@@ -1648,6 +1784,12 @@ void sqlite3GenerateConstraintChecks(
sqlite3UniqueConstraint(pParse, onError, pIdx);
break;
}
+#ifndef SQLITE_OMIT_UPSERT
+ case OE_Update: {
+ sqlite3UpsertDoUpdate(pParse, pUpsert, pTab, pIdx, iDataCur, iIdxCur);
+ /* Fall through */
+ }
+#endif
case OE_Ignore: {
sqlite3VdbeGoto(v, ignoreDest);
break;
@@ -1669,11 +1811,10 @@ void sqlite3GenerateConstraintChecks(
sqlite3VdbeResolveLabel(v, addrUniqueOk);
sqlite3ExprCachePop(pParse);
if( regR!=regIdx ) sqlite3ReleaseTempRange(pParse, regR, nPkField);
+ if( pUpIdx==pIdx ) sqlite3VdbeJumpHere(v, upsertBypass);
+
}
- if( ipkTop ){
- sqlite3VdbeGoto(v, ipkTop+1);
- sqlite3VdbeJumpHere(v, ipkBottom);
- }
+ reorderConstraintChecks(v, &sAddr);
*pbMayReplace = seenReplace;
VdbeModuleComment((v, "END: GenCnstCks(%d)", seenReplace));
diff --git a/src/parse.y b/src/parse.y
index 26dde6ee2..e7a48d020 100644
--- a/src/parse.y
+++ b/src/parse.y
@@ -205,7 +205,8 @@ columnname(A) ::= nm(A) typetoken(Y). {sqlite3AddColumn(pParse,&A,&Y);}
//
%fallback ID
ABORT ACTION AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST COLUMNKW
- CONFLICT DATABASE DEFERRED DESC DETACH EACH END EXCLUSIVE EXPLAIN FAIL FOR
+ CONFLICT DATABASE DEFERRED DESC DETACH DO DUPLICATE
+ EACH END EXCLUSIVE EXPLAIN FAIL FOR
IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH NO PLAN
QUERY KEY OF OFFSET PRAGMA RAISE RECURSIVE RELEASE REPLACE RESTRICT ROW
ROLLBACK SAVEPOINT TEMP TRIGGER VACUUM VIEW VIRTUAL WITH WITHOUT
@@ -239,6 +240,7 @@ columnname(A) ::= nm(A) typetoken(Y). {sqlite3AddColumn(pParse,&A,&Y);}
%left CONCAT.
%left COLLATE.
%right BITNOT.
+%nonassoc ON.
// An IDENTIFIER can be a generic identifier, or one of several
// keywords. Any non-standard keyword can also be an identifier.
@@ -697,10 +699,27 @@ joinop(X) ::= JOIN_KW(A) nm(B) JOIN.
joinop(X) ::= JOIN_KW(A) nm(B) nm(C) JOIN.
{X = sqlite3JoinType(pParse,&A,&B,&C);/*X-overwrites-A*/}
+// There is a parsing abiguity in an upsert statement that uses a
+// SELECT on the RHS of a the INSERT:
+//
+// INSERT INTO tab SELECT * FROM aaa JOIN bbb ON CONFLICT ...
+// here ----^^
+//
+// When the ON token is encountered, the parser does not know if it is
+// the beginning of an ON CONFLICT clause, or the beginning of an ON
+// clause associated with the JOIN. The conflict is resolved in favor
+// of the JOIN. If an ON CONFLICT clause is intended, insert a dummy
+// WHERE clause in between, like this:
+//
+// INSERT INTO tab SELECT * FROM aaa JOIN bbb WHERE true ON CONFLICT ...
+//
+// The [AND] and [OR] precedence marks in the rules for on_opt cause the
+// ON in this context to always be interpreted as belonging to the JOIN.
+//
%type on_opt {Expr*}
%destructor on_opt {sqlite3ExprDelete(pParse->db, $$);}
-on_opt(N) ::= ON expr(E). {N = E;}
-on_opt(N) ::= . {N = 0;}
+on_opt(N) ::= ON expr(E). {N = E;}
+on_opt(N) ::= . [OR] {N = 0;}
// Note that this block abuses the Token type just a little. If there is
// no "INDEXED BY" clause, the returned token is empty (z==0 && n==0). If
@@ -839,13 +858,27 @@ setlist(A) ::= LP idlist(X) RP EQ expr(Y). {
////////////////////////// The INSERT command /////////////////////////////////
//
-cmd ::= with insert_cmd(R) INTO fullname(X) idlist_opt(F) select(S). {
- sqlite3Insert(pParse, X, S, F, R);
+cmd ::= with insert_cmd(R) INTO fullname(X) idlist_opt(F) select(S)
+ upsert(U). {
+ sqlite3Insert(pParse, X, S, F, R, U);
}
cmd ::= with insert_cmd(R) INTO fullname(X) idlist_opt(F) DEFAULT VALUES.
{
- sqlite3Insert(pParse, X, 0, F, R);
-}
+ sqlite3Insert(pParse, X, 0, F, R, 0);
+}
+
+%type upsert {Upsert*}
+%destructor upsert {sqlite3UpsertDelete(pParse->db,$$);}
+upsert(A) ::= . { A = 0; }
+upsert(A) ::= ON CONFLICT LP sortlist(T) RP where_opt(TW)
+ DO UPDATE SET setlist(Z) where_opt(W).
+ { A = sqlite3UpsertNew(pParse->db,T,TW,Z,W);}
+upsert(A) ::= ON DUPLICATE KEY UPDATE setlist(Z) where_opt(W).
+ { A = sqlite3UpsertNew(pParse->db,0,0,Z,W); }
+upsert(A) ::= ON CONFLICT LP sortlist(T) RP where_opt(TW) DO NOTHING.
+ { A = sqlite3UpsertNew(pParse->db,T,TW,0,0); }
+upsert(A) ::= ON CONFLICT DO NOTHING.
+ { A = sqlite3UpsertNew(pParse->db,0,0,0,0); }
%type insert_cmd {int}
insert_cmd(A) ::= INSERT orconf(R). {A = R;}
@@ -1392,9 +1425,9 @@ trigger_cmd(A) ::=
// INSERT
trigger_cmd(A) ::= scanpt(B) insert_cmd(R) INTO
- trnm(X) idlist_opt(F) select(S) scanpt(Z).
- {A = sqlite3TriggerInsertStep(pParse->db,&X,F,S,R,B,Z);/*A-overwrites-R*/}
-
+ trnm(X) idlist_opt(F) select(S) upsert(U) scanpt(Z). {
+ A = sqlite3TriggerInsertStep(pParse->db,&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->db, &X, Y, B.z, E);}
diff --git a/src/resolve.c b/src/resolve.c
index c8059df97..aa60b0478 100644
--- a/src/resolve.c
+++ b/src/resolve.c
@@ -1536,7 +1536,7 @@ void sqlite3ResolveSelfReference(
Table *pTab, /* The table being referenced */
int type, /* NC_IsCheck or NC_PartIdx or NC_IdxExpr */
Expr *pExpr, /* Expression to resolve. May be NULL. */
- ExprList *pList /* Expression list to resolve. May be NUL. */
+ ExprList *pList /* Expression list to resolve. May be NULL. */
){
SrcList sSrc; /* Fake SrcList for pParse->pNewTable */
NameContext sNC; /* Name context for pParse->pNewTable */
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index 45cefb95a..cd65ffb39 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -1095,6 +1095,7 @@ typedef struct Trigger Trigger;
typedef struct TriggerPrg TriggerPrg;
typedef struct TriggerStep TriggerStep;
typedef struct UnpackedRecord UnpackedRecord;
+typedef struct Upsert Upsert;
typedef struct VTable VTable;
typedef struct VtabCtx VtabCtx;
typedef struct Walker Walker;
@@ -2046,13 +2047,12 @@ struct FKey {
#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_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 */
+#define OE_Update 6 /* Process as a DO UPDATE in an upsert */
+#define OE_Restrict 7 /* OE_Abort for IMMEDIATE, OE_Rollback for DEFERRED */
+#define OE_SetNull 8 /* Set the foreign key value to NULL */
+#define OE_SetDflt 9 /* Set the foreign key value to its default */
+#define OE_Cascade 10 /* Cascade the changes */
+#define OE_Default 11 /* Do whatever the default action is */
/*
@@ -2713,6 +2713,29 @@ struct NameContext {
#define NC_Complex 0x2000 /* True if a function or subquery seen */
/*
+** An instance of the following object describes a single ON CONFLICT
+** clause in an upsert.
+**
+** The pUpsertTarget field is only set if the ON CONFLICT clause includes
+** conflict-target clause. (In "ON CONFLICT(a,b)" the "(a,b)" is the
+** conflict-target clause.) The pUpsertTargetWhere is the optional
+** WHERE clause used to identify partial unique indexes.
+**
+** pUpsertSet is the list of column=expr terms of the UPDATE statement.
+** The pUpsertSet field is NULL for a ON CONFLICT DO NOTHING. The
+** pUpsertWhere is the WHERE clause for the UPDATE and is NULL if the
+** WHERE clause is omitted.
+*/
+struct Upsert {
+ ExprList *pUpsertTarget; /* Optional description of conflicting index */
+ Expr *pUpsertTargetWhere; /* WHERE clause for partial index targets */
+ Index *pUpsertIdx; /* Constraint that pUpsertTarget identifies */
+ ExprList *pUpsertSet; /* The SET clause from an ON CONFLICT UPDATE */
+ Expr *pUpsertWhere; /* WHERE clause for the ON CONFLICT UPDATE */
+ SrcList *pUpsertSrc; /* Table to be updated */
+};
+
+/*
** An instance of the following structure contains all information
** needed to generate code for a single SELECT statement.
**
@@ -3211,8 +3234,9 @@ struct TriggerStep {
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 */
- ExprList *pExprList; /* SET clause for UPDATE. */
+ ExprList *pExprList; /* SET clause for UPDATE */
IdList *pIdList; /* Column names for INSERT */
+ Upsert *pUpsert; /* Upsert clauses on an INSERT */
char *zSpan; /* Original SQL text of this command */
TriggerStep *pNext; /* Next in the link-list */
TriggerStep *pLast; /* Last element in link-list. Valid for 1st elem only */
@@ -3744,7 +3768,7 @@ void sqlite3DeleteTable(sqlite3*, Table*);
# define sqlite3AutoincrementBegin(X)
# define sqlite3AutoincrementEnd(X)
#endif
-void sqlite3Insert(Parse*, SrcList*, Select*, IdList*, int);
+void sqlite3Insert(Parse*, SrcList*, Select*, IdList*, int, Upsert*);
void *sqlite3ArrayAllocate(sqlite3*,void*,int,int*,int*);
IdList *sqlite3IdListAppend(sqlite3*, IdList*, Token*);
int sqlite3IdListIndex(IdList*,const char*);
@@ -3867,7 +3891,7 @@ void sqlite3GenerateRowIndexDelete(Parse*, Table*, int, int, int*, int);
int sqlite3GenerateIndexKey(Parse*, Index*, int, int, int, int*,Index*,int);
void sqlite3ResolvePartIdxLabel(Parse*,int);
void sqlite3GenerateConstraintChecks(Parse*,Table*,int*,int,int,int,int,
- u8,u8,int,int*,int*);
+ u8,u8,int,int*,int*,Upsert*);
#ifdef SQLITE_ENABLE_NULL_TRIM
void sqlite3SetMakeRecordP5(Vdbe*,Table*);
#else
@@ -3920,7 +3944,8 @@ void sqlite3MaterializeView(Parse*, Table*, Expr*, ExprList*,Expr*,int);
TriggerStep *sqlite3TriggerSelectStep(sqlite3*,Select*,
const char*,const char*);
TriggerStep *sqlite3TriggerInsertStep(sqlite3*,Token*, IdList*,
- Select*,u8,const char*,const char*);
+ Select*,u8,Upsert*,
+ const char*,const char*);
TriggerStep *sqlite3TriggerUpdateStep(sqlite3*,Token*,ExprList*, Expr*, u8,
const char*,const char*);
TriggerStep *sqlite3TriggerDeleteStep(sqlite3*,Token*, Expr*,
@@ -4259,6 +4284,18 @@ const char *sqlite3JournalModename(int);
#define sqlite3WithPush(x,y,z)
#define sqlite3WithDelete(x,y)
#endif
+#ifndef SQLITE_OMIT_UPSERT
+ Upsert *sqlite3UpsertNew(sqlite3*,ExprList*,Expr*,ExprList*,Expr*);
+ void sqlite3UpsertDelete(sqlite3*,Upsert*);
+ Upsert *sqlite3UpsertDup(sqlite3*,Upsert*);
+ int sqlite3UpsertAnalyzeTarget(Parse*,SrcList*,Upsert*);
+ void sqlite3UpsertDoUpdate(Parse*,Upsert*,Table*,Index*,int,int);
+#else
+#define sqlite3UpsertNew(x,y,z,w) ((Upsert*)0)
+#define sqlite3UpsertDelete(x,y)
+#define sqlite3UpsertDup(x,y) ((Upsert*)0)
+#endif
+
/* Declarations for functions in fkey.c. All of these are replaced by
** no-op macros if OMIT_FOREIGN_KEY is defined. In this case no foreign
diff --git a/src/trigger.c b/src/trigger.c
index 9f7bff505..bd5711f07 100644
--- a/src/trigger.c
+++ b/src/trigger.c
@@ -25,6 +25,7 @@ void sqlite3DeleteTriggerStep(sqlite3 *db, TriggerStep *pTriggerStep){
sqlite3ExprListDelete(db, pTmp->pExprList);
sqlite3SelectDelete(db, pTmp->pSelect);
sqlite3IdListDelete(db, pTmp->pIdList);
+ sqlite3UpsertDelete(db, pTmp->pUpsert);
sqlite3DbFree(db, pTmp->zSpan);
sqlite3DbFree(db, pTmp);
@@ -416,6 +417,7 @@ TriggerStep *sqlite3TriggerInsertStep(
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 */
){
@@ -427,9 +429,11 @@ TriggerStep *sqlite3TriggerInsertStep(
if( pTriggerStep ){
pTriggerStep->pSelect = sqlite3SelectDup(db, pSelect, EXPRDUP_REDUCE);
pTriggerStep->pIdList = pColumn;
+ pTriggerStep->pUpsert = pUpsert;
pTriggerStep->orconf = orconf;
}else{
sqlite3IdListDelete(db, pColumn);
+ sqlite3UpsertDelete(db, pUpsert);
}
sqlite3SelectDelete(db, pSelect);
@@ -755,7 +759,8 @@ static int codeTriggerProgram(
targetSrcList(pParse, pStep),
sqlite3SelectDup(db, pStep->pSelect, 0),
sqlite3IdListDup(db, pStep->pIdList),
- pParse->eOrconf
+ pParse->eOrconf,
+ sqlite3UpsertDup(db, pStep->pUpsert)
);
break;
}
diff --git a/src/update.c b/src/update.c
index 4b8207d36..6b3d5f494 100644
--- a/src/update.c
+++ b/src/update.c
@@ -514,8 +514,8 @@ void sqlite3Update(
VdbeCoverage(v);
}
- /* If the record number will change, set register regNewRowid to
- ** contain the new value. If the record number is not being modified,
+ /* If the rowid value will change, set register regNewRowid to
+ ** contain the new value. If the rowid is not being modified,
** then regNewRowid is the same register as regOldRowid, which is
** already populated. */
assert( chngKey || pTrigger || hasFK || regOldRowid==regNewRowid );
@@ -626,7 +626,7 @@ void sqlite3Update(
assert( regOldRowid>0 );
sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur,
regNewRowid, regOldRowid, chngKey, onError, labelContinue, &bReplace,
- aXRef);
+ aXRef, 0);
/* Do FK constraint checks. */
if( hasFK ){
diff --git a/src/upsert.c b/src/upsert.c
new file mode 100644
index 000000000..a92824f8f
--- /dev/null
+++ b/src/upsert.c
@@ -0,0 +1,258 @@
+/*
+** 2018-04-12
+**
+** The author disclaims copyright to this source code. In place of
+** a legal notice, here is a blessing:
+**
+** May you do good and not evil.
+** May you find forgiveness for yourself and forgive others.
+** May you share freely, never taking more than you give.
+**
+*************************************************************************
+** This file contains code to implement various aspects of UPSERT
+** processing and handling of the Upsert object.
+*/
+#include "sqliteInt.h"
+
+#ifndef SQLITE_OMIT_UPSERT
+/*
+** Free a list of Upsert objects
+*/
+void sqlite3UpsertDelete(sqlite3 *db, Upsert *p){
+ if( p ){
+ sqlite3ExprListDelete(db, p->pUpsertTarget);
+ sqlite3ExprDelete(db, p->pUpsertTargetWhere);
+ sqlite3ExprListDelete(db, p->pUpsertSet);
+ sqlite3ExprDelete(db, p->pUpsertWhere);
+ sqlite3DbFree(db, p);
+ }
+}
+
+/*
+** Duplicate an Upsert object.
+*/
+Upsert *sqlite3UpsertDup(sqlite3 *db, Upsert *p){
+ if( p==0 ) return 0;
+ return sqlite3UpsertNew(db,
+ sqlite3ExprListDup(db, p->pUpsertTarget, 0),
+ sqlite3ExprDup(db, p->pUpsertTargetWhere, 0),
+ sqlite3ExprListDup(db, p->pUpsertSet, 0),
+ sqlite3ExprDup(db, p->pUpsertWhere, 0)
+ );
+}
+
+/*
+** Create a new Upsert object.
+*/
+Upsert *sqlite3UpsertNew(
+ sqlite3 *db, /* Determines which memory allocator to use */
+ ExprList *pTarget, /* Target argument to ON CONFLICT, or NULL */
+ Expr *pTargetWhere, /* Optional WHERE clause on the target */
+ ExprList *pSet, /* UPDATE columns, or NULL for a DO NOTHING */
+ Expr *pWhere /* WHERE clause for the ON CONFLICT UPDATE */
+){
+ Upsert *pNew;
+ pNew = sqlite3DbMallocRaw(db, sizeof(Upsert));
+ if( pNew==0 ){
+ sqlite3ExprListDelete(db, pTarget);
+ sqlite3ExprDelete(db, pTargetWhere);
+ sqlite3ExprListDelete(db, pSet);
+ sqlite3ExprDelete(db, pWhere);
+ return 0;
+ }else{
+ pNew->pUpsertTarget = pTarget;
+ pNew->pUpsertTargetWhere = pTargetWhere;
+ pNew->pUpsertSet = pSet;
+ pNew->pUpsertWhere = pWhere;
+ pNew->pUpsertIdx = 0;
+ }
+ return pNew;
+}
+
+/*
+** Analyze the ON CONFLICT clause described by pUpsert. Resolve all
+** symbols in the conflict-target.
+**
+** Return SQLITE_OK if everything works, or an error code is something
+** is wrong.
+*/
+int sqlite3UpsertAnalyzeTarget(
+ Parse *pParse, /* The parsing context */
+ SrcList *pTabList, /* Table into which we are inserting */
+ Upsert *pUpsert /* The ON CONFLICT clauses */
+){
+ Table *pTab; /* That table into which we are inserting */
+ int rc; /* Result code */
+ int iCursor; /* Cursor used by pTab */
+ Index *pIdx; /* One of the indexes of pTab */
+ ExprList *pTarget; /* The conflict-target clause */
+ Expr *pTerm; /* One term of the conflict-target clause */
+ NameContext sNC; /* Context for resolving symbolic names */
+ Expr sCol[2]; /* Index column converted into an Expr */
+
+ assert( pTabList->nSrc==1 );
+ assert( pTabList->a[0].pTab!=0 );
+ assert( pUpsert!=0 );
+ assert( pUpsert->pUpsertTarget!=0 );
+
+ /* Resolve all symbolic names in the conflict-target clause, which
+ ** includes both the list of columns and the optional partial-index
+ ** WHERE clause.
+ */
+ memset(&sNC, 0, sizeof(sNC));
+ sNC.pParse = pParse;
+ sNC.pSrcList = pTabList;
+ rc = sqlite3ResolveExprListNames(&sNC, pUpsert->pUpsertTarget);
+ if( rc ) return rc;
+ rc = sqlite3ResolveExprNames(&sNC, pUpsert->pUpsertTargetWhere);
+ if( rc ) return rc;
+
+ /* Check to see if the conflict target matches the rowid. */
+ pTab = pTabList->a[0].pTab;
+ pTarget = pUpsert->pUpsertTarget;
+ iCursor = pTabList->a[0].iCursor;
+ if( HasRowid(pTab)
+ && pTarget->nExpr==1
+ && (pTerm = pTarget->a[0].pExpr)->op==TK_COLUMN
+ && (pTerm->iColumn==XN_ROWID || pTerm->iColumn==pTab->iPKey)
+ ){
+ /* The conflict-target is the rowid of the primary table */
+ assert( pUpsert->pUpsertIdx==0 );
+ return SQLITE_OK;
+ }
+
+ /* Initialize sCol[0..1] to be an expression parse tree for a
+ ** single column of an index. The sCol[0] node will be the TK_COLLATE
+ ** operator and sCol[1] will be the TK_COLUMN operator. Code below
+ ** will populate the specific collation and column number values
+ ** prior to comparing against the conflict-target expression.
+ */
+ memset(sCol, 0, sizeof(sCol));
+ sCol[0].op = TK_COLLATE;
+ sCol[0].pLeft = &sCol[1];
+ sCol[1].op = TK_COLUMN;
+ sCol[1].iTable = pTabList->a[0].iCursor;
+
+ /* Check for matches against other indexes */
+ for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
+ int ii, jj, nn;
+ if( !IsUniqueIndex(pIdx) ) continue;
+ if( pTarget->nExpr!=pIdx->nKeyCol ) continue;
+ if( pIdx->pPartIdxWhere ){
+ if( pUpsert->pUpsertTargetWhere==0 ) continue;
+ if( sqlite3ExprCompare(pParse, pUpsert->pUpsertTargetWhere,
+ pIdx->pPartIdxWhere, iCursor)!=0 ){
+ continue;
+ }
+ }
+ nn = pIdx->nKeyCol;
+ for(ii=0; ii<nn; ii++){
+ Expr *pExpr;
+ if( pIdx->aiColumn[ii]==XN_EXPR ){
+ assert( pIdx->aColExpr!=0 );
+ assert( pIdx->aColExpr->nExpr>ii );
+ pExpr = pIdx->aColExpr->a[ii].pExpr;
+ }else{
+ sCol[1].iColumn = pIdx->aiColumn[ii];
+ sCol[0].u.zToken = (char*)pIdx->azColl[ii];
+ pExpr = &sCol[0];
+ }
+ for(jj=0; jj<nn; jj++){
+ if( sqlite3ExprCompare(pParse, pTarget->a[jj].pExpr, pExpr,iCursor)<2 ){
+ break; /* Column ii of the index matches column jj of target */
+ }
+ }
+ if( jj>=nn ){
+ /* The target contains no match for column jj of the index */
+ break;
+ }
+ }
+ if( ii<nn ){
+ /* Column ii of the index did not match any term of the conflict target.
+ ** Continue the search with the next index. */
+ continue;
+ }
+ pUpsert->pUpsertIdx = pIdx;
+ return SQLITE_OK;
+ }
+ sqlite3ErrorMsg(pParse, "ON CONFLICT clause does not match any "
+ "PRIMARY KEY or UNIQUE constraint");
+ return SQLITE_ERROR;
+}
+
+/*
+** Generate bytecode that does an UPDATE as part of an upsert.
+*/
+void sqlite3UpsertDoUpdate(
+ Parse *pParse, /* The parsing and code-generating context */
+ Upsert *pUpsert, /* The ON CONFLICT clause for the upsert */
+ Table *pTab, /* The table being updated */
+ Index *pIdx, /* The UNIQUE constraint that failed */
+ int iDataCur, /* Cursor for the pTab, table being updated */
+ int iIdxCur /* Cursor for pIdx */
+){
+ Vdbe *v = pParse->pVdbe;
+ sqlite3 *db = pParse->db;
+ int regKey; /* Register(s) containing the key */
+ Expr *pWhere; /* Where clause for the UPDATE */
+ Expr *pE1, *pE2;
+ SrcList *pSrc; /* FROM clause for the UPDATE */
+
+ assert( v!=0 );
+ VdbeNoopComment((v, "Begin DO UPDATE of UPSERT"));
+ pWhere = sqlite3ExprDup(db, pUpsert->pUpsertWhere, 0);
+ if( pIdx==0 || HasRowid(pTab) ){
+ /* We are dealing with an IPK */
+ regKey = ++pParse->nMem;
+ if( pIdx ){
+ sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur, regKey);
+ }else{
+ sqlite3VdbeAddOp2(v, OP_Rowid, iDataCur, regKey);
+ }
+ pE1 = sqlite3ExprAlloc(db, TK_COLUMN, 0, 0);
+ if( pE1 ){
+ pE1->pTab = pTab;
+ pE1->iTable = pParse->nTab;
+ pE1->iColumn = -1;
+ }
+ pE2 = sqlite3ExprAlloc(db, TK_REGISTER, 0, 0);
+ if( pE2 ){
+ pE2->iTable = regKey;
+ pE2->affinity = SQLITE_AFF_INTEGER;
+ }
+ pWhere = sqlite3ExprAnd(db,pWhere,sqlite3PExpr(pParse, TK_EQ, pE1, pE2));
+ }else{
+ /* a WITHOUT ROWID table */
+ int i, j;
+ int iTab = pParse->nTab+1;
+ Index *pX;
+ for(pX=pTab->pIndex; ALWAYS(pX) && !IsPrimaryKeyIndex(pX); pX=pX->pNext){
+ iTab++;
+ }
+ for(i=0; i<pIdx->nKeyCol; i++){
+ regKey = ++pParse->nMem;
+ sqlite3VdbeAddOp3(v, OP_Column, iDataCur, i, regKey);
+ j = pIdx->aiColumn[i];
+ VdbeComment((v, "%s", pTab->aCol[j].zName));
+ pE1 = sqlite3ExprAlloc(db, TK_COLUMN, 0, 0);
+ if( pE1 ){
+ pE1->pTab = pTab;
+ pE1->iTable = iTab;
+ pE1->iColumn = j;
+ }
+ pE2 = sqlite3ExprAlloc(db, TK_REGISTER, 0, 0);
+ if( pE2 ){
+ pE2->iTable = regKey;
+ pE2->affinity = pTab->zColAff[j];
+ }
+ pWhere = sqlite3ExprAnd(db,pWhere,sqlite3PExpr(pParse, TK_EQ, pE1, pE2));
+ }
+ }
+ pSrc = sqlite3SrcListDup(db, pUpsert->pUpsertSrc, 0);
+ sqlite3Update(pParse, pSrc,
+ sqlite3ExprListDup(db, pUpsert->pUpsertSet, 0),
+ pWhere, OE_Abort, 0, 0);
+ VdbeNoopComment((v, "End DO UPDATE of UPSERT"));
+}
+
+#endif /* SQLITE_OMIT_UPSERT */
diff --git a/test/upsert1.test b/test/upsert1.test
new file mode 100644
index 000000000..8b53fe0c1
--- /dev/null
+++ b/test/upsert1.test
@@ -0,0 +1,92 @@
+# 2018-04-12
+#
+# The author disclaims copyright to this source code. In place of
+# a legal notice, here is a blessing:
+#
+# May you do good and not evil.
+# May you find forgiveness for yourself and forgive others.
+# May you share freely, never taking more than you give.
+#
+#***********************************************************************
+#
+# Test cases for UPSERT
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+set testprefix zipfile
+
+do_execsql_test upsert1-100 {
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c DEFAULT 0);
+ CREATE UNIQUE INDEX t1x1 ON t1(b);
+ INSERT INTO t1(a,b) VALUES(1,2) ON CONFLICT DO NOTHING;
+ INSERT INTO t1(a,b) VALUES(1,99),(99,2) ON CONFLICT DO NOTHING;
+ SELECT * FROM t1;
+} {1 2 0}
+do_execsql_test upsert1-101 {
+ DELETE FROM t1;
+ INSERT INTO t1(a,b) VALUES(2,3) ON CONFLICT(a) DO NOTHING;
+ INSERT INTO t1(a,b) VALUES(2,99) ON CONFLICT(a) DO NOTHING;
+ SELECT * FROM t1;
+} {2 3 0}
+do_execsql_test upsert1-102 {
+ DELETE FROM t1;
+ INSERT INTO t1(a,b) VALUES(3,4) ON CONFLICT(b) DO NOTHING;
+ INSERT INTO t1(a,b) VALUES(99,4) ON CONFLICT(b) DO NOTHING;
+ SELECT * FROM t1;
+} {3 4 0}
+do_catchsql_test upsert1-110 {
+ INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(x) DO NOTHING;
+ SELECT * FROM t1;
+} {1 {no such column: x}}
+do_catchsql_test upsert1-120 {
+ INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(c) DO NOTHING;
+ SELECT * FROM t1;
+} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
+breakpoint
+do_catchsql_test upsert1-130 {
+ INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b COLLATE nocase) DO NOTHING;
+ SELECT * FROM t1;
+} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
+do_execsql_test upsert1-140 {
+ DELETE FROM t1;
+ INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b COLLATE binary) DO NOTHING;
+ SELECT * FROM t1;
+} {5 6 0}
+
+do_catchsql_test upsert1-200 {
+ DROP TABLE t1;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c DEFAULT 0);
+ CREATE UNIQUE INDEX t1x1 ON t1(a+b);
+ INSERT INTO t1(a,b) VALUES(7,8) ON CONFLICT(a+b) DO NOTHING;
+ INSERT INTO t1(a,b) VALUES(8,7),(9,6) ON CONFLICT(a+b) DO NOTHING;
+ SELECT * FROM t1;
+} {0 {7 8 0}}
+do_catchsql_test upsert1-201 {
+ INSERT INTO t1(a,b) VALUES(8,7),(9,6) ON CONFLICT(a) DO NOTHING;
+} {1 {UNIQUE constraint failed: index 't1x1'}}
+do_catchsql_test upsert1-210 {
+ DELETE FROM t1;
+ INSERT INTO t1(a,b) VALUES(9,10) ON CONFLICT(a+(+b)) DO NOTHING;
+ SELECT * FROM t1;
+} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
+
+do_catchsql_test upsert1-300 {
+ DROP INDEX t1x1;
+ DELETE FROM t1;
+ CREATE UNIQUE INDEX t1x1 ON t1(b) WHERE b>10;
+ INSERT INTO t1(a,b) VALUES(1,2),(3,2) ON CONFLICT(b) DO NOTHING;
+ SELECT * FROM t1;
+} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
+do_catchsql_test upsert1-310 {
+ DELETE FROM t1;
+ INSERT INTO t1(a,b) VALUES(1,2),(3,2) ON CONFLICT(b) WHERE b!=10 DO NOTHING;
+ SELECT * FROM t1;
+} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
+do_execsql_test upsert1-320 {
+ DELETE FROM t1;
+ INSERT INTO t1(a,b) VALUES(1,2),(3,2),(4,20),(5,20)
+ ON CONFLICT(b) WHERE b>10 DO NOTHING;
+ SELECT *, 'x' FROM t1 ORDER BY b, a;
+} {1 2 0 x 3 2 0 x 4 20 0 x}
+
+finish_test
diff --git a/tool/mkkeywordhash.c b/tool/mkkeywordhash.c
index 42112718d..6e4f9ba10 100644
--- a/tool/mkkeywordhash.c
+++ b/tool/mkkeywordhash.c
@@ -143,6 +143,11 @@ struct Keyword {
#else
# define CTE 0x00040000
#endif
+#ifdef SQLITE_OMIT_UPSERT
+# define UPSERT 0
+#else
+# define UPSERT 0x00080000
+#endif
/*
** These are the keywords
@@ -186,7 +191,9 @@ static Keyword aKeywordTable[] = {
{ "DESC", "TK_DESC", ALWAYS },
{ "DETACH", "TK_DETACH", ATTACH },
{ "DISTINCT", "TK_DISTINCT", ALWAYS },
+ { "DO", "TK_DO", UPSERT },
{ "DROP", "TK_DROP", ALWAYS },
+ { "DUPLICATE", "TK_DUPLICATE", UPSERT },
{ "END", "TK_END", ALWAYS },
{ "EACH", "TK_EACH", TRIGGER },
{ "ELSE", "TK_ELSE", ALWAYS },
@@ -226,6 +233,7 @@ static Keyword aKeywordTable[] = {
{ "NATURAL", "TK_JOIN_KW", ALWAYS },
{ "NO", "TK_NO", FKEY },
{ "NOT", "TK_NOT", ALWAYS },
+ { "NOTHING", "TK_NOTHING", UPSERT },
{ "NOTNULL", "TK_NOTNULL", ALWAYS },
{ "NULL", "TK_NULL", ALWAYS },
{ "OF", "TK_OF", ALWAYS },
diff --git a/tool/mksqlite3c.tcl b/tool/mksqlite3c.tcl
index f2d93aadd..6c1dab6ac 100644
--- a/tool/mksqlite3c.tcl
+++ b/tool/mksqlite3c.tcl
@@ -363,6 +363,7 @@ foreach file {
table.c
trigger.c
update.c
+ upsert.c
vacuum.c
vtab.c
wherecode.c
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-19 18:52:05 +0000