aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-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
8 files changed, 526 insertions, 50 deletions
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 */
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-19 12:47:42 +0000