4 ** The author disclaims copyright to this source code. In place of
5 ** a legal notice, here is a blessing:
7 ** May you do good and not evil.
8 ** May you find forgiveness for yourself and forgive others.
9 ** May you share freely, never taking more than you give.
11 *************************************************************************
12 ** This file contains code to implement various aspects of UPSERT
13 ** processing and handling of the Upsert object.
15 #include "sqliteInt.h"
17 #ifndef SQLITE_OMIT_UPSERT
19 ** Free a list of Upsert objects
21 void sqlite3UpsertDelete(sqlite3
*db
, Upsert
*p
){
23 sqlite3ExprListDelete(db
, p
->pUpsertTarget
);
24 sqlite3ExprDelete(db
, p
->pUpsertTargetWhere
);
25 sqlite3ExprListDelete(db
, p
->pUpsertSet
);
26 sqlite3ExprDelete(db
, p
->pUpsertWhere
);
32 ** Duplicate an Upsert object.
34 Upsert
*sqlite3UpsertDup(sqlite3
*db
, Upsert
*p
){
36 return sqlite3UpsertNew(db
,
37 sqlite3ExprListDup(db
, p
->pUpsertTarget
, 0),
38 sqlite3ExprDup(db
, p
->pUpsertTargetWhere
, 0),
39 sqlite3ExprListDup(db
, p
->pUpsertSet
, 0),
40 sqlite3ExprDup(db
, p
->pUpsertWhere
, 0)
45 ** Create a new Upsert object.
47 Upsert
*sqlite3UpsertNew(
48 sqlite3
*db
, /* Determines which memory allocator to use */
49 ExprList
*pTarget
, /* Target argument to ON CONFLICT, or NULL */
50 Expr
*pTargetWhere
, /* Optional WHERE clause on the target */
51 ExprList
*pSet
, /* UPDATE columns, or NULL for a DO NOTHING */
52 Expr
*pWhere
/* WHERE clause for the ON CONFLICT UPDATE */
55 pNew
= sqlite3DbMallocRaw(db
, sizeof(Upsert
));
57 sqlite3ExprListDelete(db
, pTarget
);
58 sqlite3ExprDelete(db
, pTargetWhere
);
59 sqlite3ExprListDelete(db
, pSet
);
60 sqlite3ExprDelete(db
, pWhere
);
63 pNew
->pUpsertTarget
= pTarget
;
64 pNew
->pUpsertTargetWhere
= pTargetWhere
;
65 pNew
->pUpsertSet
= pSet
;
66 pNew
->pUpsertWhere
= pWhere
;
73 ** Analyze the ON CONFLICT clause described by pUpsert. Resolve all
74 ** symbols in the conflict-target.
76 ** Return SQLITE_OK if everything works, or an error code is something
79 int sqlite3UpsertAnalyzeTarget(
80 Parse
*pParse
, /* The parsing context */
81 SrcList
*pTabList
, /* Table into which we are inserting */
82 Upsert
*pUpsert
/* The ON CONFLICT clauses */
84 Table
*pTab
; /* That table into which we are inserting */
85 int rc
; /* Result code */
86 int iCursor
; /* Cursor used by pTab */
87 Index
*pIdx
; /* One of the indexes of pTab */
88 ExprList
*pTarget
; /* The conflict-target clause */
89 Expr
*pTerm
; /* One term of the conflict-target clause */
90 NameContext sNC
; /* Context for resolving symbolic names */
91 Expr sCol
[2]; /* Index column converted into an Expr */
93 assert( pTabList
->nSrc
==1 );
94 assert( pTabList
->a
[0].pTab
!=0 );
96 assert( pUpsert
->pUpsertTarget
!=0 );
98 /* Resolve all symbolic names in the conflict-target clause, which
99 ** includes both the list of columns and the optional partial-index
102 memset(&sNC
, 0, sizeof(sNC
));
104 sNC
.pSrcList
= pTabList
;
105 rc
= sqlite3ResolveExprListNames(&sNC
, pUpsert
->pUpsertTarget
);
107 rc
= sqlite3ResolveExprNames(&sNC
, pUpsert
->pUpsertTargetWhere
);
110 /* Check to see if the conflict target matches the rowid. */
111 pTab
= pTabList
->a
[0].pTab
;
112 pTarget
= pUpsert
->pUpsertTarget
;
113 iCursor
= pTabList
->a
[0].iCursor
;
116 && (pTerm
= pTarget
->a
[0].pExpr
)->op
==TK_COLUMN
117 && pTerm
->iColumn
==XN_ROWID
119 /* The conflict-target is the rowid of the primary table */
120 assert( pUpsert
->pUpsertIdx
==0 );
124 /* Initialize sCol[0..1] to be an expression parse tree for a
125 ** single column of an index. The sCol[0] node will be the TK_COLLATE
126 ** operator and sCol[1] will be the TK_COLUMN operator. Code below
127 ** will populate the specific collation and column number values
128 ** prior to comparing against the conflict-target expression.
130 memset(sCol
, 0, sizeof(sCol
));
131 sCol
[0].op
= TK_COLLATE
;
132 sCol
[0].pLeft
= &sCol
[1];
133 sCol
[1].op
= TK_COLUMN
;
134 sCol
[1].iTable
= pTabList
->a
[0].iCursor
;
136 /* Check for matches against other indexes */
137 for(pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
){
139 if( !IsUniqueIndex(pIdx
) ) continue;
140 if( pTarget
->nExpr
!=pIdx
->nKeyCol
) continue;
141 if( pIdx
->pPartIdxWhere
){
142 if( pUpsert
->pUpsertTargetWhere
==0 ) continue;
143 if( sqlite3ExprCompare(pParse
, pUpsert
->pUpsertTargetWhere
,
144 pIdx
->pPartIdxWhere
, iCursor
)!=0 ){
149 for(ii
=0; ii
<nn
; ii
++){
151 sCol
[0].u
.zToken
= (char*)pIdx
->azColl
[ii
];
152 if( pIdx
->aiColumn
[ii
]==XN_EXPR
){
153 assert( pIdx
->aColExpr
!=0 );
154 assert( pIdx
->aColExpr
->nExpr
>ii
);
155 pExpr
= pIdx
->aColExpr
->a
[ii
].pExpr
;
156 if( pExpr
->op
!=TK_COLLATE
){
157 sCol
[0].pLeft
= pExpr
;
161 sCol
[0].pLeft
= &sCol
[1];
162 sCol
[1].iColumn
= pIdx
->aiColumn
[ii
];
165 for(jj
=0; jj
<nn
; jj
++){
166 if( sqlite3ExprCompare(pParse
, pTarget
->a
[jj
].pExpr
, pExpr
,iCursor
)<2 ){
167 break; /* Column ii of the index matches column jj of target */
171 /* The target contains no match for column jj of the index */
176 /* Column ii of the index did not match any term of the conflict target.
177 ** Continue the search with the next index. */
180 pUpsert
->pUpsertIdx
= pIdx
;
183 sqlite3ErrorMsg(pParse
, "ON CONFLICT clause does not match any "
184 "PRIMARY KEY or UNIQUE constraint");
189 ** Generate bytecode that does an UPDATE as part of an upsert.
191 ** If pIdx is NULL, then the UNIQUE constraint that failed was the IPK.
192 ** In this case parameter iCur is a cursor open on the table b-tree that
193 ** currently points to the conflicting table row. Otherwise, if pIdx
194 ** is not NULL, then pIdx is the constraint that failed and iCur is a
195 ** cursor points to the conflicting row.
197 void sqlite3UpsertDoUpdate(
198 Parse
*pParse
, /* The parsing and code-generating context */
199 Upsert
*pUpsert
, /* The ON CONFLICT clause for the upsert */
200 Table
*pTab
, /* The table being updated */
201 Index
*pIdx
, /* The UNIQUE constraint that failed */
202 int iCur
/* Cursor for pIdx (or pTab if pIdx==NULL) */
204 Vdbe
*v
= pParse
->pVdbe
;
205 sqlite3
*db
= pParse
->db
;
206 SrcList
*pSrc
; /* FROM clause for the UPDATE */
211 assert( pUpsert
!=0 );
212 VdbeNoopComment((v
, "Begin DO UPDATE of UPSERT"));
213 iDataCur
= pUpsert
->iDataCur
;
214 if( pIdx
&& iCur
!=iDataCur
){
215 if( HasRowid(pTab
) ){
216 int regRowid
= sqlite3GetTempReg(pParse
);
217 sqlite3VdbeAddOp2(v
, OP_IdxRowid
, iCur
, regRowid
);
218 sqlite3VdbeAddOp3(v
, OP_SeekRowid
, iDataCur
, 0, regRowid
);
220 sqlite3ReleaseTempReg(pParse
, regRowid
);
222 Index
*pPk
= sqlite3PrimaryKeyIndex(pTab
);
223 int nPk
= pPk
->nKeyCol
;
224 int iPk
= pParse
->nMem
+1;
226 for(i
=0; i
<nPk
; i
++){
228 assert( pPk
->aiColumn
[i
]>=0 );
229 k
= sqlite3TableColumnToIndex(pIdx
, pPk
->aiColumn
[i
]);
230 sqlite3VdbeAddOp3(v
, OP_Column
, iCur
, k
, iPk
+i
);
231 VdbeComment((v
, "%s.%s", pIdx
->zName
,
232 pTab
->aCol
[pPk
->aiColumn
[i
]].zName
));
234 sqlite3VdbeVerifyAbortable(v
, OE_Abort
);
235 i
= sqlite3VdbeAddOp4Int(v
, OP_Found
, iDataCur
, 0, iPk
, nPk
);
237 sqlite3VdbeAddOp4(v
, OP_Halt
, SQLITE_CORRUPT
, OE_Abort
, 0,
238 "corrupt database", P4_STATIC
);
239 sqlite3MayAbort(pParse
);
240 sqlite3VdbeJumpHere(v
, i
);
243 /* pUpsert does not own pUpsertSrc - the outer INSERT statement does. So
244 ** we have to make a copy before passing it down into sqlite3Update() */
245 pSrc
= sqlite3SrcListDup(db
, pUpsert
->pUpsertSrc
, 0);
246 /* excluded.* columns of type REAL need to be converted to a hard real */
247 for(i
=0; i
<pTab
->nCol
; i
++){
248 if( pTab
->aCol
[i
].affinity
==SQLITE_AFF_REAL
){
249 sqlite3VdbeAddOp1(v
, OP_RealAffinity
, pUpsert
->regData
+i
);
252 sqlite3Update(pParse
, pSrc
, pUpsert
->pUpsertSet
,
253 pUpsert
->pUpsertWhere
, OE_Abort
, 0, 0, pUpsert
);
254 pUpsert
->pUpsertSet
= 0; /* Will have been deleted by sqlite3Update() */
255 pUpsert
->pUpsertWhere
= 0; /* Will have been deleted by sqlite3Update() */
256 VdbeNoopComment((v
, "End DO UPDATE of UPSERT"));
259 #endif /* SQLITE_OMIT_UPSERT */