blob: 2a40acaa0c9a819c3573072c9332e10f863bcd66 [file] [log] [blame]
drhcce7d172000-05-31 15:34:51 +00001/*
drhb19a2bc2001-09-16 00:13:26 +00002** 2001 September 15
drhcce7d172000-05-31 15:34:51 +00003**
drhb19a2bc2001-09-16 00:13:26 +00004** The author disclaims copyright to this source code. In place of
5** a legal notice, here is a blessing:
drhcce7d172000-05-31 15:34:51 +00006**
drhb19a2bc2001-09-16 00:13:26 +00007** 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.
drhcce7d172000-05-31 15:34:51 +000010**
11*************************************************************************
12** This file contains C code routines that are called by the parser
drhb19a2bc2001-09-16 00:13:26 +000013** to handle INSERT statements in SQLite.
drhcce7d172000-05-31 15:34:51 +000014*/
15#include "sqliteInt.h"
16
17/*
drh26198bb2013-10-31 11:15:09 +000018** Generate code that will
drhdd9930e2013-10-23 23:37:02 +000019**
drh26198bb2013-10-31 11:15:09 +000020** (1) acquire a lock for table pTab then
21** (2) open pTab as cursor iCur.
22**
23** If pTab is a WITHOUT ROWID table, then it is the PRIMARY KEY index
24** for that table that is actually opened.
drhbbb5e4e2009-04-30 00:11:09 +000025*/
26void sqlite3OpenTable(
drh2ec2fb22013-11-06 19:59:23 +000027 Parse *pParse, /* Generate code into this VDBE */
drhbbb5e4e2009-04-30 00:11:09 +000028 int iCur, /* The cursor number of the table */
29 int iDb, /* The database index in sqlite3.aDb[] */
30 Table *pTab, /* The table to be opened */
31 int opcode /* OP_OpenRead or OP_OpenWrite */
32){
33 Vdbe *v;
drh5f53aac2012-12-03 19:42:39 +000034 assert( !IsVirtual(pTab) );
drh289a0c82020-08-15 22:23:00 +000035 assert( pParse->pVdbe!=0 );
36 v = pParse->pVdbe;
drhbbb5e4e2009-04-30 00:11:09 +000037 assert( opcode==OP_OpenWrite || opcode==OP_OpenRead );
drh2ec2fb22013-11-06 19:59:23 +000038 sqlite3TableLock(pParse, iDb, pTab->tnum,
39 (opcode==OP_OpenWrite)?1:0, pTab->zName);
drhec95c442013-10-23 01:57:32 +000040 if( HasRowid(pTab) ){
drh0b0b3a92019-10-17 18:35:57 +000041 sqlite3VdbeAddOp4Int(v, opcode, iCur, pTab->tnum, iDb, pTab->nNVCol);
drhdd9930e2013-10-23 23:37:02 +000042 VdbeComment((v, "%s", pTab->zName));
drh26198bb2013-10-31 11:15:09 +000043 }else{
drhdd9930e2013-10-23 23:37:02 +000044 Index *pPk = sqlite3PrimaryKeyIndex(pTab);
45 assert( pPk!=0 );
drhafe028a2015-05-22 13:09:50 +000046 assert( pPk->tnum==pTab->tnum );
drh2ec2fb22013-11-06 19:59:23 +000047 sqlite3VdbeAddOp3(v, opcode, iCur, pPk->tnum, iDb);
48 sqlite3VdbeSetP4KeyInfo(pParse, pPk);
drhec95c442013-10-23 01:57:32 +000049 VdbeComment((v, "%s", pTab->zName));
50 }
drhbbb5e4e2009-04-30 00:11:09 +000051}
52
53/*
dan69f8bb92009-08-13 19:21:16 +000054** Return a pointer to the column affinity string associated with index
55** pIdx. A column affinity string has one character for each column in
56** the table, according to the affinity of the column:
danielk19773d1bfea2004-05-14 11:00:53 +000057**
58** Character Column affinity
59** ------------------------------
drh05883a32015-06-02 15:32:08 +000060** 'A' BLOB
drh4583c372014-09-19 20:13:25 +000061** 'B' TEXT
62** 'C' NUMERIC
63** 'D' INTEGER
64** 'F' REAL
drh2d401ab2008-01-10 23:50:11 +000065**
drh4583c372014-09-19 20:13:25 +000066** An extra 'D' is appended to the end of the string to cover the
drh2d401ab2008-01-10 23:50:11 +000067** rowid that appears as the last column in every index.
dan69f8bb92009-08-13 19:21:16 +000068**
69** Memory for the buffer containing the column index affinity string
70** is managed along with the rest of the Index structure. It will be
71** released when sqlite3DeleteIndex() is called.
danielk19773d1bfea2004-05-14 11:00:53 +000072*/
drhe9107692015-08-25 19:20:04 +000073const char *sqlite3IndexAffinityStr(sqlite3 *db, Index *pIdx){
danielk1977a37cdde2004-05-16 11:15:36 +000074 if( !pIdx->zColAff ){
danielk1977e014a832004-05-17 10:48:57 +000075 /* The first time a column affinity string for a particular index is
danielk1977a37cdde2004-05-16 11:15:36 +000076 ** required, it is allocated and populated here. It is then stored as
danielk1977e014a832004-05-17 10:48:57 +000077 ** a member of the Index structure for subsequent use.
danielk1977a37cdde2004-05-16 11:15:36 +000078 **
79 ** The column affinity string will eventually be deleted by
danielk1977e014a832004-05-17 10:48:57 +000080 ** sqliteDeleteIndex() when the Index structure itself is cleaned
danielk1977a37cdde2004-05-16 11:15:36 +000081 ** up.
82 */
83 int n;
84 Table *pTab = pIdx->pTable;
drhad124322013-10-23 13:30:58 +000085 pIdx->zColAff = (char *)sqlite3DbMallocRaw(0, pIdx->nColumn+1);
danielk1977a37cdde2004-05-16 11:15:36 +000086 if( !pIdx->zColAff ){
drh4a642b62016-02-05 01:55:27 +000087 sqlite3OomFault(db);
dan69f8bb92009-08-13 19:21:16 +000088 return 0;
danielk1977a37cdde2004-05-16 11:15:36 +000089 }
90 for(n=0; n<pIdx->nColumn; n++){
drhad124322013-10-23 13:30:58 +000091 i16 x = pIdx->aiColumn[n];
drh81506b82019-08-05 19:32:06 +000092 char aff;
drh1f9ca2c2015-08-25 16:57:52 +000093 if( x>=0 ){
drh81506b82019-08-05 19:32:06 +000094 aff = pTab->aCol[x].affinity;
drh4b92f982015-09-29 17:20:14 +000095 }else if( x==XN_ROWID ){
drh81506b82019-08-05 19:32:06 +000096 aff = SQLITE_AFF_INTEGER;
drh1f9ca2c2015-08-25 16:57:52 +000097 }else{
drh4b92f982015-09-29 17:20:14 +000098 assert( x==XN_EXPR );
drh1f9ca2c2015-08-25 16:57:52 +000099 assert( pIdx->aColExpr!=0 );
drh6860e6f2015-08-27 18:24:02 +0000100 aff = sqlite3ExprAffinity(pIdx->aColExpr->a[n].pExpr);
drh1f9ca2c2015-08-25 16:57:52 +0000101 }
drh96fb16e2019-08-06 14:37:24 +0000102 if( aff<SQLITE_AFF_BLOB ) aff = SQLITE_AFF_BLOB;
drh73144952019-08-30 23:56:34 +0000103 if( aff>SQLITE_AFF_NUMERIC) aff = SQLITE_AFF_NUMERIC;
drh81506b82019-08-05 19:32:06 +0000104 pIdx->zColAff[n] = aff;
danielk1977a37cdde2004-05-16 11:15:36 +0000105 }
drh2d401ab2008-01-10 23:50:11 +0000106 pIdx->zColAff[n] = 0;
danielk1977a37cdde2004-05-16 11:15:36 +0000107 }
danielk19773d1bfea2004-05-14 11:00:53 +0000108
dan69f8bb92009-08-13 19:21:16 +0000109 return pIdx->zColAff;
danielk1977a37cdde2004-05-16 11:15:36 +0000110}
111
112/*
drh57bf4a82014-02-17 14:59:22 +0000113** Compute the affinity string for table pTab, if it has not already been
drh05883a32015-06-02 15:32:08 +0000114** computed. As an optimization, omit trailing SQLITE_AFF_BLOB affinities.
drh57bf4a82014-02-17 14:59:22 +0000115**
drh05883a32015-06-02 15:32:08 +0000116** If the affinity exists (if it is no entirely SQLITE_AFF_BLOB values) and
drh57bf4a82014-02-17 14:59:22 +0000117** if iReg>0 then code an OP_Affinity opcode that will set the affinities
118** for register iReg and following. Or if affinities exists and iReg==0,
119** then just set the P4 operand of the previous opcode (which should be
120** an OP_MakeRecord) to the affinity string.
121**
drhb6e8fd12014-03-06 01:56:33 +0000122** A column affinity string has one character per column:
danielk1977a37cdde2004-05-16 11:15:36 +0000123**
124** Character Column affinity
125** ------------------------------
drh05883a32015-06-02 15:32:08 +0000126** 'A' BLOB
drh4583c372014-09-19 20:13:25 +0000127** 'B' TEXT
128** 'C' NUMERIC
129** 'D' INTEGER
130** 'E' REAL
danielk1977a37cdde2004-05-16 11:15:36 +0000131*/
drh57bf4a82014-02-17 14:59:22 +0000132void sqlite3TableAffinity(Vdbe *v, Table *pTab, int iReg){
drhab45fc02019-10-16 22:01:56 +0000133 int i, j;
drh57bf4a82014-02-17 14:59:22 +0000134 char *zColAff = pTab->zColAff;
135 if( zColAff==0 ){
drhabb6fca2007-08-16 12:24:01 +0000136 sqlite3 *db = sqlite3VdbeDb(v);
drhb9755982010-07-24 16:34:37 +0000137 zColAff = (char *)sqlite3DbMallocRaw(0, pTab->nCol+1);
danielk19773d1bfea2004-05-14 11:00:53 +0000138 if( !zColAff ){
drh4a642b62016-02-05 01:55:27 +0000139 sqlite3OomFault(db);
danielk1977a37cdde2004-05-16 11:15:36 +0000140 return;
danielk19773d1bfea2004-05-14 11:00:53 +0000141 }
142
drhab45fc02019-10-16 22:01:56 +0000143 for(i=j=0; i<pTab->nCol; i++){
drh96fb16e2019-08-06 14:37:24 +0000144 assert( pTab->aCol[i].affinity!=0 );
drhab45fc02019-10-16 22:01:56 +0000145 if( (pTab->aCol[i].colFlags & COLFLAG_VIRTUAL)==0 ){
146 zColAff[j++] = pTab->aCol[i].affinity;
147 }
danielk19773d1bfea2004-05-14 11:00:53 +0000148 }
drh57bf4a82014-02-17 14:59:22 +0000149 do{
drhab45fc02019-10-16 22:01:56 +0000150 zColAff[j--] = 0;
151 }while( j>=0 && zColAff[j]<=SQLITE_AFF_BLOB );
danielk19773d1bfea2004-05-14 11:00:53 +0000152 pTab->zColAff = zColAff;
153 }
drh7301e772018-10-31 20:52:00 +0000154 assert( zColAff!=0 );
155 i = sqlite3Strlen30NN(zColAff);
drh57bf4a82014-02-17 14:59:22 +0000156 if( i ){
157 if( iReg ){
158 sqlite3VdbeAddOp4(v, OP_Affinity, iReg, i, 0, zColAff, i);
159 }else{
160 sqlite3VdbeChangeP4(v, -1, zColAff, i);
161 }
162 }
danielk19773d1bfea2004-05-14 11:00:53 +0000163}
164
danielk19774d887782005-02-08 08:42:27 +0000165/*
drh48d11782007-11-23 15:02:19 +0000166** Return non-zero if the table pTab in database iDb or any of its indices
drhb6e8fd12014-03-06 01:56:33 +0000167** have been opened at any point in the VDBE program. This is used to see if
drh48d11782007-11-23 15:02:19 +0000168** a statement of the form "INSERT INTO <iDb, pTab> SELECT ..." can
drhb6e8fd12014-03-06 01:56:33 +0000169** run without using a temporary table for the results of the SELECT.
danielk19774d887782005-02-08 08:42:27 +0000170*/
drh05a86c52014-02-16 01:55:49 +0000171static int readsTable(Parse *p, int iDb, Table *pTab){
danielk1977595a5232009-07-24 17:58:53 +0000172 Vdbe *v = sqlite3GetVdbe(p);
danielk19774d887782005-02-08 08:42:27 +0000173 int i;
drh48d11782007-11-23 15:02:19 +0000174 int iEnd = sqlite3VdbeCurrentAddr(v);
danielk1977595a5232009-07-24 17:58:53 +0000175#ifndef SQLITE_OMIT_VIRTUALTABLE
176 VTable *pVTab = IsVirtual(pTab) ? sqlite3GetVTable(p->db, pTab) : 0;
177#endif
178
drh05a86c52014-02-16 01:55:49 +0000179 for(i=1; i<iEnd; i++){
drh48d11782007-11-23 15:02:19 +0000180 VdbeOp *pOp = sqlite3VdbeGetOp(v, i);
drhef0bea92007-12-14 16:11:09 +0000181 assert( pOp!=0 );
danielk1977207872a2008-01-03 07:54:23 +0000182 if( pOp->opcode==OP_OpenRead && pOp->p3==iDb ){
183 Index *pIndex;
drh8deae5a2020-07-29 12:23:20 +0000184 Pgno tnum = pOp->p2;
danielk1977207872a2008-01-03 07:54:23 +0000185 if( tnum==pTab->tnum ){
186 return 1;
187 }
188 for(pIndex=pTab->pIndex; pIndex; pIndex=pIndex->pNext){
189 if( tnum==pIndex->tnum ){
drh48d11782007-11-23 15:02:19 +0000190 return 1;
191 }
drh48d11782007-11-23 15:02:19 +0000192 }
193 }
drh543165e2007-11-27 14:46:41 +0000194#ifndef SQLITE_OMIT_VIRTUALTABLE
danielk1977595a5232009-07-24 17:58:53 +0000195 if( pOp->opcode==OP_VOpen && pOp->p4.pVtab==pVTab ){
danielk19772dca4ac2008-01-03 11:50:29 +0000196 assert( pOp->p4.pVtab!=0 );
drh66a51672008-01-03 00:01:23 +0000197 assert( pOp->p4type==P4_VTAB );
drh48d11782007-11-23 15:02:19 +0000198 return 1;
danielk19774d887782005-02-08 08:42:27 +0000199 }
drh543165e2007-11-27 14:46:41 +0000200#endif
danielk19774d887782005-02-08 08:42:27 +0000201 }
202 return 0;
203}
danielk19773d1bfea2004-05-14 11:00:53 +0000204
drhdfa15272019-11-06 22:19:07 +0000205/* This walker callback will compute the union of colFlags flags for all
drh7dc76d82019-11-21 20:10:31 +0000206** referenced columns in a CHECK constraint or generated column expression.
drhdfa15272019-11-06 22:19:07 +0000207*/
208static int exprColumnFlagUnion(Walker *pWalker, Expr *pExpr){
drh7dc76d82019-11-21 20:10:31 +0000209 if( pExpr->op==TK_COLUMN && pExpr->iColumn>=0 ){
210 assert( pExpr->iColumn < pWalker->u.pTab->nCol );
drhdfa15272019-11-06 22:19:07 +0000211 pWalker->eCode |= pWalker->u.pTab->aCol[pExpr->iColumn].colFlags;
212 }
213 return WRC_Continue;
214}
215
drhc1431142019-10-17 17:54:05 +0000216#ifndef SQLITE_OMIT_GENERATED_COLUMNS
217/*
218** All regular columns for table pTab have been puts into registers
219** starting with iRegStore. The registers that correspond to STORED
drhdd6cc9b2019-10-19 18:47:27 +0000220** or VIRTUAL columns have not yet been initialized. This routine goes
221** back and computes the values for those columns based on the previously
222** computed normal columns.
drhc1431142019-10-17 17:54:05 +0000223*/
drhdd6cc9b2019-10-19 18:47:27 +0000224void sqlite3ComputeGeneratedColumns(
drhc1431142019-10-17 17:54:05 +0000225 Parse *pParse, /* Parsing context */
226 int iRegStore, /* Register holding the first column */
227 Table *pTab /* The table */
228){
229 int i;
drhdfa15272019-11-06 22:19:07 +0000230 Walker w;
231 Column *pRedo;
232 int eProgress;
drhb5f62432019-12-10 02:48:41 +0000233 VdbeOp *pOp;
234
235 assert( pTab->tabFlags & TF_HasGenerated );
236 testcase( pTab->tabFlags & TF_HasVirtual );
237 testcase( pTab->tabFlags & TF_HasStored );
238
239 /* Before computing generated columns, first go through and make sure
240 ** that appropriate affinity has been applied to the regular columns
241 */
242 sqlite3TableAffinity(pParse->pVdbe, pTab, iRegStore);
243 if( (pTab->tabFlags & TF_HasStored)!=0
244 && (pOp = sqlite3VdbeGetOp(pParse->pVdbe,-1))->opcode==OP_Affinity
245 ){
246 /* Change the OP_Affinity argument to '@' (NONE) for all stored
247 ** columns. '@' is the no-op affinity and those columns have not
248 ** yet been computed. */
249 int ii, jj;
250 char *zP4 = pOp->p4.z;
251 assert( zP4!=0 );
252 assert( pOp->p4type==P4_DYNAMIC );
253 for(ii=jj=0; zP4[jj]; ii++){
254 if( pTab->aCol[ii].colFlags & COLFLAG_VIRTUAL ){
255 continue;
256 }
257 if( pTab->aCol[ii].colFlags & COLFLAG_STORED ){
258 zP4[jj] = SQLITE_AFF_NONE;
259 }
260 jj++;
261 }
262 }
drhdfa15272019-11-06 22:19:07 +0000263
drhdd6cc9b2019-10-19 18:47:27 +0000264 /* Because there can be multiple generated columns that refer to one another,
265 ** this is a two-pass algorithm. On the first pass, mark all generated
266 ** columns as "not available".
drh9942ef02019-10-18 02:19:18 +0000267 */
268 for(i=0; i<pTab->nCol; i++){
drhdd6cc9b2019-10-19 18:47:27 +0000269 if( pTab->aCol[i].colFlags & COLFLAG_GENERATED ){
drhab0992f2019-10-23 03:53:10 +0000270 testcase( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL );
271 testcase( pTab->aCol[i].colFlags & COLFLAG_STORED );
drh9942ef02019-10-18 02:19:18 +0000272 pTab->aCol[i].colFlags |= COLFLAG_NOTAVAIL;
273 }
274 }
drhdfa15272019-11-06 22:19:07 +0000275
276 w.u.pTab = pTab;
277 w.xExprCallback = exprColumnFlagUnion;
278 w.xSelectCallback = 0;
279 w.xSelectCallback2 = 0;
280
drh9942ef02019-10-18 02:19:18 +0000281 /* On the second pass, compute the value of each NOT-AVAILABLE column.
282 ** Companion code in the TK_COLUMN case of sqlite3ExprCodeTarget() will
283 ** compute dependencies and mark remove the COLSPAN_NOTAVAIL mark, as
284 ** they are needed.
285 */
drhc1431142019-10-17 17:54:05 +0000286 pParse->iSelfTab = -iRegStore;
drhdfa15272019-11-06 22:19:07 +0000287 do{
288 eProgress = 0;
289 pRedo = 0;
290 for(i=0; i<pTab->nCol; i++){
291 Column *pCol = pTab->aCol + i;
292 if( (pCol->colFlags & COLFLAG_NOTAVAIL)!=0 ){
293 int x;
294 pCol->colFlags |= COLFLAG_BUSY;
295 w.eCode = 0;
296 sqlite3WalkExpr(&w, pCol->pDflt);
297 pCol->colFlags &= ~COLFLAG_BUSY;
298 if( w.eCode & COLFLAG_NOTAVAIL ){
299 pRedo = pCol;
300 continue;
301 }
302 eProgress = 1;
303 assert( pCol->colFlags & COLFLAG_GENERATED );
304 x = sqlite3TableColumnToStorage(pTab, i) + iRegStore;
305 sqlite3ExprCodeGeneratedColumn(pParse, pCol, x);
306 pCol->colFlags &= ~COLFLAG_NOTAVAIL;
drhdd6cc9b2019-10-19 18:47:27 +0000307 }
drhc1431142019-10-17 17:54:05 +0000308 }
drhdfa15272019-11-06 22:19:07 +0000309 }while( pRedo && eProgress );
310 if( pRedo ){
311 sqlite3ErrorMsg(pParse, "generated column loop on \"%s\"", pRedo->zName);
drhc1431142019-10-17 17:54:05 +0000312 }
313 pParse->iSelfTab = 0;
314}
315#endif /* SQLITE_OMIT_GENERATED_COLUMNS */
316
317
drh9d9cf222007-02-13 15:01:11 +0000318#ifndef SQLITE_OMIT_AUTOINCREMENT
319/*
drh0b9f50d2009-06-23 20:28:53 +0000320** Locate or create an AutoincInfo structure associated with table pTab
321** which is in database iDb. Return the register number for the register
drh9ef5e772016-08-19 14:20:56 +0000322** that holds the maximum rowid. Return zero if pTab is not an AUTOINCREMENT
323** table. (Also return zero when doing a VACUUM since we do not want to
324** update the AUTOINCREMENT counters during a VACUUM.)
drh9d9cf222007-02-13 15:01:11 +0000325**
drh0b9f50d2009-06-23 20:28:53 +0000326** There is at most one AutoincInfo structure per table even if the
327** same table is autoincremented multiple times due to inserts within
328** triggers. A new AutoincInfo structure is created if this is the
329** first use of table pTab. On 2nd and subsequent uses, the original
330** AutoincInfo structure is used.
drh9d9cf222007-02-13 15:01:11 +0000331**
drhc8abbc12018-03-16 18:46:30 +0000332** Four consecutive registers are allocated:
drh0b9f50d2009-06-23 20:28:53 +0000333**
drhc8abbc12018-03-16 18:46:30 +0000334** (1) The name of the pTab table.
335** (2) The maximum ROWID of pTab.
336** (3) The rowid in sqlite_sequence of pTab
337** (4) The original value of the max ROWID in pTab, or NULL if none
drh0b9f50d2009-06-23 20:28:53 +0000338**
339** The 2nd register is the one that is returned. That is all the
340** insert routine needs to know about.
drh9d9cf222007-02-13 15:01:11 +0000341*/
342static int autoIncBegin(
343 Parse *pParse, /* Parsing context */
344 int iDb, /* Index of the database holding pTab */
345 Table *pTab /* The table we are writing to */
346){
drh6a288a32008-01-07 19:20:24 +0000347 int memId = 0; /* Register holding maximum rowid */
drh186ebd42018-05-23 16:50:21 +0000348 assert( pParse->db->aDb[iDb].pSchema!=0 );
drh9ef5e772016-08-19 14:20:56 +0000349 if( (pTab->tabFlags & TF_Autoincrement)!=0
drh8257aa82017-07-26 19:59:13 +0000350 && (pParse->db->mDbFlags & DBFLAG_Vacuum)==0
drh9ef5e772016-08-19 14:20:56 +0000351 ){
dan65a7cd12009-09-01 12:16:01 +0000352 Parse *pToplevel = sqlite3ParseToplevel(pParse);
drh0b9f50d2009-06-23 20:28:53 +0000353 AutoincInfo *pInfo;
drh186ebd42018-05-23 16:50:21 +0000354 Table *pSeqTab = pParse->db->aDb[iDb].pSchema->pSeqTab;
355
356 /* Verify that the sqlite_sequence table exists and is an ordinary
357 ** rowid table with exactly two columns.
358 ** Ticket d8dc2b3a58cd5dc2918a1d4acb 2018-05-23 */
359 if( pSeqTab==0
360 || !HasRowid(pSeqTab)
361 || IsVirtual(pSeqTab)
362 || pSeqTab->nCol!=2
363 ){
364 pParse->nErr++;
365 pParse->rc = SQLITE_CORRUPT_SEQUENCE;
366 return 0;
367 }
drh0b9f50d2009-06-23 20:28:53 +0000368
dan65a7cd12009-09-01 12:16:01 +0000369 pInfo = pToplevel->pAinc;
drh0b9f50d2009-06-23 20:28:53 +0000370 while( pInfo && pInfo->pTab!=pTab ){ pInfo = pInfo->pNext; }
371 if( pInfo==0 ){
drh575fad62016-02-05 13:38:36 +0000372 pInfo = sqlite3DbMallocRawNN(pParse->db, sizeof(*pInfo));
drh0b9f50d2009-06-23 20:28:53 +0000373 if( pInfo==0 ) return 0;
dan65a7cd12009-09-01 12:16:01 +0000374 pInfo->pNext = pToplevel->pAinc;
375 pToplevel->pAinc = pInfo;
drh0b9f50d2009-06-23 20:28:53 +0000376 pInfo->pTab = pTab;
377 pInfo->iDb = iDb;
dan65a7cd12009-09-01 12:16:01 +0000378 pToplevel->nMem++; /* Register to hold name of table */
379 pInfo->regCtr = ++pToplevel->nMem; /* Max rowid register */
drhc8abbc12018-03-16 18:46:30 +0000380 pToplevel->nMem +=2; /* Rowid in sqlite_sequence + orig max val */
drh0b9f50d2009-06-23 20:28:53 +0000381 }
382 memId = pInfo->regCtr;
drh9d9cf222007-02-13 15:01:11 +0000383 }
384 return memId;
385}
386
387/*
drh0b9f50d2009-06-23 20:28:53 +0000388** This routine generates code that will initialize all of the
389** register used by the autoincrement tracker.
390*/
391void sqlite3AutoincrementBegin(Parse *pParse){
392 AutoincInfo *p; /* Information about an AUTOINCREMENT */
393 sqlite3 *db = pParse->db; /* The database connection */
394 Db *pDb; /* Database only autoinc table */
395 int memId; /* Register holding max rowid */
drh0b9f50d2009-06-23 20:28:53 +0000396 Vdbe *v = pParse->pVdbe; /* VDBE under construction */
397
drh345ba7d2009-09-08 13:40:16 +0000398 /* This routine is never called during trigger-generation. It is
399 ** only called from the top-level */
400 assert( pParse->pTriggerTab==0 );
drhc149f182015-09-29 13:25:15 +0000401 assert( sqlite3IsToplevel(pParse) );
dan76d462e2009-08-30 11:42:51 +0000402
drh0b9f50d2009-06-23 20:28:53 +0000403 assert( v ); /* We failed long ago if this is not so */
404 for(p = pParse->pAinc; p; p = p->pNext){
drh1b325542016-02-03 01:55:44 +0000405 static const int iLn = VDBE_OFFSET_LINENO(2);
406 static const VdbeOpList autoInc[] = {
407 /* 0 */ {OP_Null, 0, 0, 0},
drhc8abbc12018-03-16 18:46:30 +0000408 /* 1 */ {OP_Rewind, 0, 10, 0},
drh1b325542016-02-03 01:55:44 +0000409 /* 2 */ {OP_Column, 0, 0, 0},
drhc8abbc12018-03-16 18:46:30 +0000410 /* 3 */ {OP_Ne, 0, 9, 0},
drh1b325542016-02-03 01:55:44 +0000411 /* 4 */ {OP_Rowid, 0, 0, 0},
412 /* 5 */ {OP_Column, 0, 1, 0},
drhc8abbc12018-03-16 18:46:30 +0000413 /* 6 */ {OP_AddImm, 0, 0, 0},
414 /* 7 */ {OP_Copy, 0, 0, 0},
415 /* 8 */ {OP_Goto, 0, 11, 0},
416 /* 9 */ {OP_Next, 0, 2, 0},
417 /* 10 */ {OP_Integer, 0, 0, 0},
418 /* 11 */ {OP_Close, 0, 0, 0}
drh1b325542016-02-03 01:55:44 +0000419 };
420 VdbeOp *aOp;
drh0b9f50d2009-06-23 20:28:53 +0000421 pDb = &db->aDb[p->iDb];
422 memId = p->regCtr;
drh21206082011-04-04 18:22:02 +0000423 assert( sqlite3SchemaMutexHeld(db, 0, pDb->pSchema) );
drh0b9f50d2009-06-23 20:28:53 +0000424 sqlite3OpenTable(pParse, 0, p->iDb, pDb->pSchema->pSeqTab, OP_OpenRead);
drh076e85f2015-09-03 13:46:12 +0000425 sqlite3VdbeLoadString(v, memId-1, p->pTab->zName);
drh1b325542016-02-03 01:55:44 +0000426 aOp = sqlite3VdbeAddOpList(v, ArraySize(autoInc), autoInc, iLn);
427 if( aOp==0 ) break;
428 aOp[0].p2 = memId;
drhc8abbc12018-03-16 18:46:30 +0000429 aOp[0].p3 = memId+2;
drh1b325542016-02-03 01:55:44 +0000430 aOp[2].p3 = memId;
431 aOp[3].p1 = memId-1;
432 aOp[3].p3 = memId;
433 aOp[3].p5 = SQLITE_JUMPIFNULL;
434 aOp[4].p2 = memId+1;
435 aOp[5].p3 = memId;
drhc8abbc12018-03-16 18:46:30 +0000436 aOp[6].p1 = memId;
437 aOp[7].p2 = memId+2;
438 aOp[7].p1 = memId;
439 aOp[10].p2 = memId;
drh04ab5862018-12-01 21:13:41 +0000440 if( pParse->nTab==0 ) pParse->nTab = 1;
drh0b9f50d2009-06-23 20:28:53 +0000441 }
442}
443
444/*
drh9d9cf222007-02-13 15:01:11 +0000445** Update the maximum rowid for an autoincrement calculation.
446**
drh1b325542016-02-03 01:55:44 +0000447** This routine should be called when the regRowid register holds a
drh9d9cf222007-02-13 15:01:11 +0000448** new rowid that is about to be inserted. If that new rowid is
449** larger than the maximum rowid in the memId memory cell, then the
drh1b325542016-02-03 01:55:44 +0000450** memory cell is updated.
drh9d9cf222007-02-13 15:01:11 +0000451*/
drh6a288a32008-01-07 19:20:24 +0000452static void autoIncStep(Parse *pParse, int memId, int regRowid){
drh9d9cf222007-02-13 15:01:11 +0000453 if( memId>0 ){
drh6a288a32008-01-07 19:20:24 +0000454 sqlite3VdbeAddOp2(pParse->pVdbe, OP_MemMax, memId, regRowid);
drh9d9cf222007-02-13 15:01:11 +0000455 }
456}
457
458/*
drh0b9f50d2009-06-23 20:28:53 +0000459** This routine generates the code needed to write autoincrement
460** maximum rowid values back into the sqlite_sequence register.
461** Every statement that might do an INSERT into an autoincrement
462** table (either directly or through triggers) needs to call this
463** routine just before the "exit" code.
drh9d9cf222007-02-13 15:01:11 +0000464*/
drh1b325542016-02-03 01:55:44 +0000465static SQLITE_NOINLINE void autoIncrementEnd(Parse *pParse){
drh0b9f50d2009-06-23 20:28:53 +0000466 AutoincInfo *p;
467 Vdbe *v = pParse->pVdbe;
468 sqlite3 *db = pParse->db;
drh6a288a32008-01-07 19:20:24 +0000469
drh0b9f50d2009-06-23 20:28:53 +0000470 assert( v );
471 for(p = pParse->pAinc; p; p = p->pNext){
drh1b325542016-02-03 01:55:44 +0000472 static const int iLn = VDBE_OFFSET_LINENO(2);
473 static const VdbeOpList autoIncEnd[] = {
474 /* 0 */ {OP_NotNull, 0, 2, 0},
475 /* 1 */ {OP_NewRowid, 0, 0, 0},
476 /* 2 */ {OP_MakeRecord, 0, 2, 0},
477 /* 3 */ {OP_Insert, 0, 0, 0},
478 /* 4 */ {OP_Close, 0, 0, 0}
479 };
480 VdbeOp *aOp;
drh0b9f50d2009-06-23 20:28:53 +0000481 Db *pDb = &db->aDb[p->iDb];
drh0b9f50d2009-06-23 20:28:53 +0000482 int iRec;
483 int memId = p->regCtr;
484
485 iRec = sqlite3GetTempReg(pParse);
drh21206082011-04-04 18:22:02 +0000486 assert( sqlite3SchemaMutexHeld(db, 0, pDb->pSchema) );
drhc8abbc12018-03-16 18:46:30 +0000487 sqlite3VdbeAddOp3(v, OP_Le, memId+2, sqlite3VdbeCurrentAddr(v)+7, memId);
488 VdbeCoverage(v);
drh0b9f50d2009-06-23 20:28:53 +0000489 sqlite3OpenTable(pParse, 0, p->iDb, pDb->pSchema->pSeqTab, OP_OpenWrite);
drh1b325542016-02-03 01:55:44 +0000490 aOp = sqlite3VdbeAddOpList(v, ArraySize(autoIncEnd), autoIncEnd, iLn);
491 if( aOp==0 ) break;
492 aOp[0].p1 = memId+1;
493 aOp[1].p2 = memId+1;
494 aOp[2].p1 = memId-1;
495 aOp[2].p3 = iRec;
496 aOp[3].p2 = iRec;
497 aOp[3].p3 = memId+1;
498 aOp[3].p5 = OPFLAG_APPEND;
drh0b9f50d2009-06-23 20:28:53 +0000499 sqlite3ReleaseTempReg(pParse, iRec);
drh9d9cf222007-02-13 15:01:11 +0000500 }
501}
drh1b325542016-02-03 01:55:44 +0000502void sqlite3AutoincrementEnd(Parse *pParse){
503 if( pParse->pAinc ) autoIncrementEnd(pParse);
504}
drh9d9cf222007-02-13 15:01:11 +0000505#else
506/*
507** If SQLITE_OMIT_AUTOINCREMENT is defined, then the three routines
508** above are all no-ops
509*/
510# define autoIncBegin(A,B,C) (0)
danielk1977287fb612008-01-04 19:10:28 +0000511# define autoIncStep(A,B,C)
drh9d9cf222007-02-13 15:01:11 +0000512#endif /* SQLITE_OMIT_AUTOINCREMENT */
513
514
515/* Forward declaration */
516static int xferOptimization(
517 Parse *pParse, /* Parser context */
518 Table *pDest, /* The table we are inserting into */
519 Select *pSelect, /* A SELECT statement to use as the data source */
520 int onError, /* How to handle constraint errors */
521 int iDbDest /* The database of pDest */
522);
523
danielk19773d1bfea2004-05-14 11:00:53 +0000524/*
drhd82b5022013-10-26 00:58:34 +0000525** This routine is called to handle SQL of the following forms:
drhcce7d172000-05-31 15:34:51 +0000526**
drha21f78b2015-04-19 18:32:43 +0000527** insert into TABLE (IDLIST) values(EXPRLIST),(EXPRLIST),...
drh1ccde152000-06-17 13:12:39 +0000528** insert into TABLE (IDLIST) select
drha21f78b2015-04-19 18:32:43 +0000529** insert into TABLE (IDLIST) default values
drhcce7d172000-05-31 15:34:51 +0000530**
drh1ccde152000-06-17 13:12:39 +0000531** The IDLIST following the table name is always optional. If omitted,
drha21f78b2015-04-19 18:32:43 +0000532** then a list of all (non-hidden) columns for the table is substituted.
533** The IDLIST appears in the pColumn parameter. pColumn is NULL if IDLIST
534** is omitted.
drh1ccde152000-06-17 13:12:39 +0000535**
drha21f78b2015-04-19 18:32:43 +0000536** For the pSelect parameter holds the values to be inserted for the
537** first two forms shown above. A VALUES clause is really just short-hand
538** for a SELECT statement that omits the FROM clause and everything else
539** that follows. If the pSelect parameter is NULL, that means that the
540** DEFAULT VALUES form of the INSERT statement is intended.
drh142e30d2002-08-28 03:00:58 +0000541**
drh9d9cf222007-02-13 15:01:11 +0000542** The code generated follows one of four templates. For a simple
drha21f78b2015-04-19 18:32:43 +0000543** insert with data coming from a single-row VALUES clause, the code executes
drhe00ee6e2008-06-20 15:24:01 +0000544** once straight down through. Pseudo-code follows (we call this
545** the "1st template"):
drh142e30d2002-08-28 03:00:58 +0000546**
547** open write cursor to <table> and its indices
drhec95c442013-10-23 01:57:32 +0000548** put VALUES clause expressions into registers
drh142e30d2002-08-28 03:00:58 +0000549** write the resulting record into <table>
550** cleanup
551**
drh9d9cf222007-02-13 15:01:11 +0000552** The three remaining templates assume the statement is of the form
drh142e30d2002-08-28 03:00:58 +0000553**
554** INSERT INTO <table> SELECT ...
555**
drh9d9cf222007-02-13 15:01:11 +0000556** If the SELECT clause is of the restricted form "SELECT * FROM <table2>" -
557** in other words if the SELECT pulls all columns from a single table
558** and there is no WHERE or LIMIT or GROUP BY or ORDER BY clauses, and
559** if <table2> and <table1> are distinct tables but have identical
560** schemas, including all the same indices, then a special optimization
561** is invoked that copies raw records from <table2> over to <table1>.
562** See the xferOptimization() function for the implementation of this
drhe00ee6e2008-06-20 15:24:01 +0000563** template. This is the 2nd template.
drh9d9cf222007-02-13 15:01:11 +0000564**
565** open a write cursor to <table>
566** open read cursor on <table2>
567** transfer all records in <table2> over to <table>
568** close cursors
569** foreach index on <table>
570** open a write cursor on the <table> index
571** open a read cursor on the corresponding <table2> index
572** transfer all records from the read to the write cursors
573** close cursors
574** end foreach
575**
drhe00ee6e2008-06-20 15:24:01 +0000576** The 3rd template is for when the second template does not apply
drh9d9cf222007-02-13 15:01:11 +0000577** and the SELECT clause does not read from <table> at any time.
578** The generated code follows this template:
drh142e30d2002-08-28 03:00:58 +0000579**
drhe00ee6e2008-06-20 15:24:01 +0000580** X <- A
drh142e30d2002-08-28 03:00:58 +0000581** goto B
582** A: setup for the SELECT
drh9d9cf222007-02-13 15:01:11 +0000583** loop over the rows in the SELECT
drhe00ee6e2008-06-20 15:24:01 +0000584** load values into registers R..R+n
585** yield X
drh142e30d2002-08-28 03:00:58 +0000586** end loop
587** cleanup after the SELECT
drh81cf13e2014-02-07 18:27:53 +0000588** end-coroutine X
drhe00ee6e2008-06-20 15:24:01 +0000589** B: open write cursor to <table> and its indices
drh81cf13e2014-02-07 18:27:53 +0000590** C: yield X, at EOF goto D
drhe00ee6e2008-06-20 15:24:01 +0000591** insert the select result into <table> from R..R+n
592** goto C
drh142e30d2002-08-28 03:00:58 +0000593** D: cleanup
594**
drhe00ee6e2008-06-20 15:24:01 +0000595** The 4th template is used if the insert statement takes its
drh142e30d2002-08-28 03:00:58 +0000596** values from a SELECT but the data is being inserted into a table
597** that is also read as part of the SELECT. In the third form,
peter.d.reid60ec9142014-09-06 16:39:46 +0000598** we have to use an intermediate table to store the results of
drh142e30d2002-08-28 03:00:58 +0000599** the select. The template is like this:
600**
drhe00ee6e2008-06-20 15:24:01 +0000601** X <- A
drh142e30d2002-08-28 03:00:58 +0000602** goto B
603** A: setup for the SELECT
604** loop over the tables in the SELECT
drhe00ee6e2008-06-20 15:24:01 +0000605** load value into register R..R+n
606** yield X
drh142e30d2002-08-28 03:00:58 +0000607** end loop
608** cleanup after the SELECT
drh81cf13e2014-02-07 18:27:53 +0000609** end co-routine R
drhe00ee6e2008-06-20 15:24:01 +0000610** B: open temp table
drh81cf13e2014-02-07 18:27:53 +0000611** L: yield X, at EOF goto M
drhe00ee6e2008-06-20 15:24:01 +0000612** insert row from R..R+n into temp table
613** goto L
614** M: open write cursor to <table> and its indices
615** rewind temp table
616** C: loop over rows of intermediate table
drh142e30d2002-08-28 03:00:58 +0000617** transfer values form intermediate table into <table>
drhe00ee6e2008-06-20 15:24:01 +0000618** end loop
619** D: cleanup
drhcce7d172000-05-31 15:34:51 +0000620*/
danielk19774adee202004-05-08 08:23:19 +0000621void sqlite3Insert(
drhcce7d172000-05-31 15:34:51 +0000622 Parse *pParse, /* Parser context */
drh113088e2003-03-20 01:16:58 +0000623 SrcList *pTabList, /* Name of table into which we are inserting */
drh5974a302000-06-07 14:42:26 +0000624 Select *pSelect, /* A SELECT statement to use as the data source */
drhf5f19152019-10-21 01:04:11 +0000625 IdList *pColumn, /* Column names corresponding to IDLIST, or NULL. */
drh2c2e8442018-04-07 15:04:05 +0000626 int onError, /* How to handle constraint errors */
drh46d2e5c2018-04-12 13:15:43 +0000627 Upsert *pUpsert /* ON CONFLICT clauses for upsert, or NULL */
drhcce7d172000-05-31 15:34:51 +0000628){
drh6a288a32008-01-07 19:20:24 +0000629 sqlite3 *db; /* The main database structure */
630 Table *pTab; /* The table to insert into. aka TABLE */
drh60ffc802016-11-21 21:33:46 +0000631 int i, j; /* Loop counters */
drh5974a302000-06-07 14:42:26 +0000632 Vdbe *v; /* Generate code into this virtual machine */
633 Index *pIdx; /* For looping over indices of the table */
drh967e8b72000-06-21 13:59:10 +0000634 int nColumn; /* Number of columns in the data */
drh6a288a32008-01-07 19:20:24 +0000635 int nHidden = 0; /* Number of hidden columns if TABLE is virtual */
drh26198bb2013-10-31 11:15:09 +0000636 int iDataCur = 0; /* VDBE cursor that is the main data repository */
637 int iIdxCur = 0; /* First index cursor */
drhd82b5022013-10-26 00:58:34 +0000638 int ipkColumn = -1; /* Column that is the INTEGER PRIMARY KEY */
drh0ca3e242002-01-29 23:07:02 +0000639 int endOfLoop; /* Label for the end of the insertion loop */
danielk1977cfe9a692004-06-16 12:00:29 +0000640 int srcTab = 0; /* Data comes from this temporary cursor if >=0 */
drhe00ee6e2008-06-20 15:24:01 +0000641 int addrInsTop = 0; /* Jump to label "D" */
642 int addrCont = 0; /* Top of insert loop. Label "C" in templates 3 and 4 */
drh2eb95372008-06-06 15:04:36 +0000643 SelectDest dest; /* Destination for SELECT on rhs of INSERT */
drh6a288a32008-01-07 19:20:24 +0000644 int iDb; /* Index of database holding TABLE */
drh05a86c52014-02-16 01:55:49 +0000645 u8 useTempTable = 0; /* Store SELECT results in intermediate table */
646 u8 appendFlag = 0; /* True if the insert is likely to be an append */
647 u8 withoutRowid; /* 0 for normal table. 1 for WITHOUT ROWID table */
drha21f78b2015-04-19 18:32:43 +0000648 u8 bIdListInOrder; /* True if IDLIST is in table order */
drh75593d92014-01-10 20:46:55 +0000649 ExprList *pList = 0; /* List of VALUES() to be inserted */
drhc27ea2a2019-10-16 20:05:56 +0000650 int iRegStore; /* Register in which to store next column */
drhcce7d172000-05-31 15:34:51 +0000651
drh6a288a32008-01-07 19:20:24 +0000652 /* Register allocations */
drh1bd10f82008-12-10 21:19:56 +0000653 int regFromSelect = 0;/* Base register for data coming from SELECT */
drh6a288a32008-01-07 19:20:24 +0000654 int regAutoinc = 0; /* Register holding the AUTOINCREMENT counter */
655 int regRowCount = 0; /* Memory cell used for the row counter */
656 int regIns; /* Block of regs holding rowid+data being inserted */
657 int regRowid; /* registers holding insert rowid */
658 int regData; /* register holding first column to insert */
drhaa9b8962008-01-08 02:57:55 +0000659 int *aRegIdx = 0; /* One register allocated to each index */
drh6a288a32008-01-07 19:20:24 +0000660
drh798da522004-11-04 04:42:28 +0000661#ifndef SQLITE_OMIT_TRIGGER
662 int isView; /* True if attempting to insert into a view */
danielk19772f886d12009-02-28 10:47:41 +0000663 Trigger *pTrigger; /* List of triggers on pTab, if required */
664 int tmask; /* Mask of trigger times */
drh798da522004-11-04 04:42:28 +0000665#endif
danielk1977c3f9bad2002-05-15 08:30:12 +0000666
drh17435752007-08-16 04:30:38 +0000667 db = pParse->db;
668 if( pParse->nErr || db->mallocFailed ){
drh6f7adc82006-01-11 21:41:20 +0000669 goto insert_cleanup;
670 }
drh4c883482017-06-03 20:09:37 +0000671 dest.iSDParm = 0; /* Suppress a harmless compiler warning */
drhdaffd0e2001-04-11 14:28:42 +0000672
drh75593d92014-01-10 20:46:55 +0000673 /* If the Select object is really just a simple VALUES() list with a
drha21f78b2015-04-19 18:32:43 +0000674 ** single row (the common case) then keep that one row of values
675 ** and discard the other (unused) parts of the pSelect object
drh75593d92014-01-10 20:46:55 +0000676 */
677 if( pSelect && (pSelect->selFlags & SF_Values)!=0 && pSelect->pPrior==0 ){
678 pList = pSelect->pEList;
679 pSelect->pEList = 0;
680 sqlite3SelectDelete(db, pSelect);
681 pSelect = 0;
682 }
683
drh1ccde152000-06-17 13:12:39 +0000684 /* Locate the table into which we will be inserting new information.
685 */
drh113088e2003-03-20 01:16:58 +0000686 assert( pTabList->nSrc==1 );
danielk19774adee202004-05-08 08:23:19 +0000687 pTab = sqlite3SrcListLookup(pParse, pTabList);
danielk1977c3f9bad2002-05-15 08:30:12 +0000688 if( pTab==0 ){
danielk1977c3f9bad2002-05-15 08:30:12 +0000689 goto insert_cleanup;
690 }
danielk1977da184232006-01-05 11:34:32 +0000691 iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
692 assert( iDb<db->nDb );
drha0daa752016-09-16 11:53:10 +0000693 if( sqlite3AuthCheck(pParse, SQLITE_INSERT, pTab->zName, 0,
694 db->aDb[iDb].zDbSName) ){
drh1962bda2003-01-12 19:33:52 +0000695 goto insert_cleanup;
696 }
drhec95c442013-10-23 01:57:32 +0000697 withoutRowid = !HasRowid(pTab);
danielk1977c3f9bad2002-05-15 08:30:12 +0000698
drhb7f91642004-10-31 02:22:47 +0000699 /* Figure out if we have any triggers and if the table being
700 ** inserted into is a view
701 */
702#ifndef SQLITE_OMIT_TRIGGER
danielk19772f886d12009-02-28 10:47:41 +0000703 pTrigger = sqlite3TriggersExist(pParse, pTab, TK_INSERT, 0, &tmask);
drhb7f91642004-10-31 02:22:47 +0000704 isView = pTab->pSelect!=0;
705#else
danielk19772f886d12009-02-28 10:47:41 +0000706# define pTrigger 0
707# define tmask 0
drhb7f91642004-10-31 02:22:47 +0000708# define isView 0
709#endif
710#ifdef SQLITE_OMIT_VIEW
711# undef isView
712# define isView 0
713#endif
danielk19772f886d12009-02-28 10:47:41 +0000714 assert( (pTrigger && tmask) || (pTrigger==0 && tmask==0) );
drhb7f91642004-10-31 02:22:47 +0000715
drhf573c992002-07-31 00:32:50 +0000716 /* If pTab is really a view, make sure it has been initialized.
drhd82b5022013-10-26 00:58:34 +0000717 ** ViewGetColumnNames() is a no-op if pTab is not a view.
drhf573c992002-07-31 00:32:50 +0000718 */
danielk1977b3d24bf2006-06-19 03:05:10 +0000719 if( sqlite3ViewGetColumnNames(pParse, pTab) ){
drh5cf590c2003-04-24 01:45:04 +0000720 goto insert_cleanup;
drhf573c992002-07-31 00:32:50 +0000721 }
722
drhd82b5022013-10-26 00:58:34 +0000723 /* Cannot insert into a read-only table.
danielk1977595a5232009-07-24 17:58:53 +0000724 */
725 if( sqlite3IsReadOnly(pParse, pTab, tmask) ){
726 goto insert_cleanup;
727 }
728
drh1ccde152000-06-17 13:12:39 +0000729 /* Allocate a VDBE
730 */
danielk19774adee202004-05-08 08:23:19 +0000731 v = sqlite3GetVdbe(pParse);
drh5974a302000-06-07 14:42:26 +0000732 if( v==0 ) goto insert_cleanup;
drh4794f732004-11-05 17:17:50 +0000733 if( pParse->nested==0 ) sqlite3VdbeCountChanges(v);
danielk19772f886d12009-02-28 10:47:41 +0000734 sqlite3BeginWriteOperation(pParse, pSelect || pTrigger, iDb);
drh1ccde152000-06-17 13:12:39 +0000735
drh9d9cf222007-02-13 15:01:11 +0000736#ifndef SQLITE_OMIT_XFER_OPT
737 /* If the statement is of the form
738 **
739 ** INSERT INTO <table1> SELECT * FROM <table2>;
740 **
741 ** Then special optimizations can be applied that make the transfer
742 ** very fast and which reduce fragmentation of indices.
drhe00ee6e2008-06-20 15:24:01 +0000743 **
744 ** This is the 2nd template.
drh9d9cf222007-02-13 15:01:11 +0000745 */
746 if( pColumn==0 && xferOptimization(pParse, pTab, pSelect, onError, iDb) ){
danielk19772f886d12009-02-28 10:47:41 +0000747 assert( !pTrigger );
drh9d9cf222007-02-13 15:01:11 +0000748 assert( pList==0 );
drh0b9f50d2009-06-23 20:28:53 +0000749 goto insert_end;
drh9d9cf222007-02-13 15:01:11 +0000750 }
751#endif /* SQLITE_OMIT_XFER_OPT */
752
drh2958a4e2004-11-12 03:56:15 +0000753 /* If this is an AUTOINCREMENT table, look up the sequence number in the
drh6a288a32008-01-07 19:20:24 +0000754 ** sqlite_sequence table and store it in memory cell regAutoinc.
drh2958a4e2004-11-12 03:56:15 +0000755 */
drh6a288a32008-01-07 19:20:24 +0000756 regAutoinc = autoIncBegin(pParse, iDb, pTab);
drh2958a4e2004-11-12 03:56:15 +0000757
drhf5f19152019-10-21 01:04:11 +0000758 /* Allocate a block registers to hold the rowid and the values
759 ** for all columns of the new row.
drh1ccde152000-06-17 13:12:39 +0000760 */
drh05a86c52014-02-16 01:55:49 +0000761 regRowid = regIns = pParse->nMem+1;
762 pParse->nMem += pTab->nCol + 1;
danielk1977034ca142007-06-26 10:38:54 +0000763 if( IsVirtual(pTab) ){
drh05a86c52014-02-16 01:55:49 +0000764 regRowid++;
765 pParse->nMem++;
danielk1977034ca142007-06-26 10:38:54 +0000766 }
drh05a86c52014-02-16 01:55:49 +0000767 regData = regRowid+1;
drh1ccde152000-06-17 13:12:39 +0000768
769 /* If the INSERT statement included an IDLIST term, then make sure
770 ** all elements of the IDLIST really are columns of the table and
771 ** remember the column indices.
drhc8392582001-12-31 02:48:51 +0000772 **
773 ** If the table has an INTEGER PRIMARY KEY column and that column
drhd82b5022013-10-26 00:58:34 +0000774 ** is named in the IDLIST, then record in the ipkColumn variable
775 ** the index into IDLIST of the primary key column. ipkColumn is
drhc8392582001-12-31 02:48:51 +0000776 ** the index of the primary key as it appears in IDLIST, not as
drhd82b5022013-10-26 00:58:34 +0000777 ** is appears in the original table. (The index of the INTEGER
drhf5f19152019-10-21 01:04:11 +0000778 ** PRIMARY KEY in the original table is pTab->iPKey.) After this
779 ** loop, if ipkColumn==(-1), that means that integer primary key
780 ** is unspecified, and hence the table is either WITHOUT ROWID or
781 ** it will automatically generated an integer primary key.
782 **
783 ** bIdListInOrder is true if the columns in IDLIST are in storage
784 ** order. This enables an optimization that avoids shuffling the
785 ** columns into storage order. False negatives are harmless,
786 ** but false positives will cause database corruption.
drh1ccde152000-06-17 13:12:39 +0000787 */
drhd4cd2922019-10-17 18:07:22 +0000788 bIdListInOrder = (pTab->tabFlags & (TF_OOOHidden|TF_HasStored))==0;
drh967e8b72000-06-21 13:59:10 +0000789 if( pColumn ){
790 for(i=0; i<pColumn->nId; i++){
791 pColumn->a[i].idx = -1;
drhcce7d172000-05-31 15:34:51 +0000792 }
drh967e8b72000-06-21 13:59:10 +0000793 for(i=0; i<pColumn->nId; i++){
drhcce7d172000-05-31 15:34:51 +0000794 for(j=0; j<pTab->nCol; j++){
danielk19774adee202004-05-08 08:23:19 +0000795 if( sqlite3StrICmp(pColumn->a[i].zName, pTab->aCol[j].zName)==0 ){
drh967e8b72000-06-21 13:59:10 +0000796 pColumn->a[i].idx = j;
drh05a86c52014-02-16 01:55:49 +0000797 if( i!=j ) bIdListInOrder = 0;
drh4a324312001-12-21 14:30:42 +0000798 if( j==pTab->iPKey ){
drhd82b5022013-10-26 00:58:34 +0000799 ipkColumn = i; assert( !withoutRowid );
drh4a324312001-12-21 14:30:42 +0000800 }
drh7e508f12019-10-16 19:31:46 +0000801#ifndef SQLITE_OMIT_GENERATED_COLUMNS
802 if( pTab->aCol[j].colFlags & (COLFLAG_STORED|COLFLAG_VIRTUAL) ){
803 sqlite3ErrorMsg(pParse,
804 "cannot INSERT into generated column \"%s\"",
805 pTab->aCol[j].zName);
806 goto insert_cleanup;
807 }
808#endif
drhcce7d172000-05-31 15:34:51 +0000809 break;
810 }
811 }
812 if( j>=pTab->nCol ){
drhec95c442013-10-23 01:57:32 +0000813 if( sqlite3IsRowid(pColumn->a[i].zName) && !withoutRowid ){
drhd82b5022013-10-26 00:58:34 +0000814 ipkColumn = i;
drhe48ae712014-05-23 11:48:57 +0000815 bIdListInOrder = 0;
drha0217ba2003-06-01 01:10:33 +0000816 }else{
danielk19774adee202004-05-08 08:23:19 +0000817 sqlite3ErrorMsg(pParse, "table %S has no column named %s",
drha0217ba2003-06-01 01:10:33 +0000818 pTabList, 0, pColumn->a[i].zName);
dan1db95102010-06-28 10:15:19 +0000819 pParse->checkSchema = 1;
drha0217ba2003-06-01 01:10:33 +0000820 goto insert_cleanup;
821 }
drhcce7d172000-05-31 15:34:51 +0000822 }
823 }
824 }
drh1ccde152000-06-17 13:12:39 +0000825
drhcce7d172000-05-31 15:34:51 +0000826 /* Figure out how many columns of data are supplied. If the data
827 ** is coming from a SELECT statement, then generate a co-routine that
828 ** produces a single row of the SELECT on each invocation. The
829 ** co-routine is the common header to the 3rd and 4th templates.
830 */
drh5f085262012-09-15 13:29:23 +0000831 if( pSelect ){
drha21f78b2015-04-19 18:32:43 +0000832 /* Data is coming from a SELECT or from a multi-row VALUES clause.
833 ** Generate a co-routine to run the SELECT. */
drh05a86c52014-02-16 01:55:49 +0000834 int regYield; /* Register holding co-routine entry-point */
835 int addrTop; /* Top of the co-routine */
836 int rc; /* Result code */
drhcce7d172000-05-31 15:34:51 +0000837
drh05a86c52014-02-16 01:55:49 +0000838 regYield = ++pParse->nMem;
839 addrTop = sqlite3VdbeCurrentAddr(v) + 1;
840 sqlite3VdbeAddOp3(v, OP_InitCoroutine, regYield, 0, addrTop);
841 sqlite3SelectDestInit(&dest, SRT_Coroutine, regYield);
842 dest.iSdst = bIdListInOrder ? regData : 0;
843 dest.nSdst = pTab->nCol;
844 rc = sqlite3Select(pParse, pSelect, &dest);
drh2b596da2012-07-23 21:43:19 +0000845 regFromSelect = dest.iSdst;
drh992590b2015-04-19 22:41:22 +0000846 if( rc || db->mallocFailed || pParse->nErr ) goto insert_cleanup;
drh2fade2f2016-02-09 02:12:20 +0000847 sqlite3VdbeEndCoroutine(v, regYield);
drh05a86c52014-02-16 01:55:49 +0000848 sqlite3VdbeJumpHere(v, addrTop - 1); /* label B: */
drhcce7d172000-05-31 15:34:51 +0000849 assert( pSelect->pEList );
850 nColumn = pSelect->pEList->nExpr;
drhcce7d172000-05-31 15:34:51 +0000851
852 /* Set useTempTable to TRUE if the result of the SELECT statement
853 ** should be written into a temporary table (template 4). Set to
854 ** FALSE if each output row of the SELECT can be written directly into
855 ** the destination table (template 3).
856 **
857 ** A temp table must be used if the table being updated is also one
858 ** of the tables being read by the SELECT statement. Also use a
859 ** temp table in the case of row triggers.
860 */
drh05a86c52014-02-16 01:55:49 +0000861 if( pTrigger || readsTable(pParse, iDb, pTab) ){
drhcce7d172000-05-31 15:34:51 +0000862 useTempTable = 1;
863 }
864
865 if( useTempTable ){
866 /* Invoke the coroutine to extract information from the SELECT
867 ** and add it to a transient table srcTab. The code generated
868 ** here is from the 4th template:
869 **
870 ** B: open temp table
drh81cf13e2014-02-07 18:27:53 +0000871 ** L: yield X, goto M at EOF
drhcce7d172000-05-31 15:34:51 +0000872 ** insert row from R..R+n into temp table
873 ** goto L
874 ** M: ...
875 */
876 int regRec; /* Register to hold packed record */
877 int regTempRowid; /* Register to hold temp table ROWID */
drh06280ee2014-02-20 19:32:38 +0000878 int addrL; /* Label "L" */
drhcce7d172000-05-31 15:34:51 +0000879
880 srcTab = pParse->nTab++;
881 regRec = sqlite3GetTempReg(pParse);
882 regTempRowid = sqlite3GetTempReg(pParse);
883 sqlite3VdbeAddOp2(v, OP_OpenEphemeral, srcTab, nColumn);
drh06280ee2014-02-20 19:32:38 +0000884 addrL = sqlite3VdbeAddOp1(v, OP_Yield, dest.iSDParm); VdbeCoverage(v);
drhcce7d172000-05-31 15:34:51 +0000885 sqlite3VdbeAddOp3(v, OP_MakeRecord, regFromSelect, nColumn, regRec);
886 sqlite3VdbeAddOp2(v, OP_NewRowid, srcTab, regTempRowid);
887 sqlite3VdbeAddOp3(v, OP_Insert, srcTab, regRec, regTempRowid);
drh076e85f2015-09-03 13:46:12 +0000888 sqlite3VdbeGoto(v, addrL);
drh06280ee2014-02-20 19:32:38 +0000889 sqlite3VdbeJumpHere(v, addrL);
drhcce7d172000-05-31 15:34:51 +0000890 sqlite3ReleaseTempReg(pParse, regRec);
891 sqlite3ReleaseTempReg(pParse, regTempRowid);
892 }
893 }else{
drha21f78b2015-04-19 18:32:43 +0000894 /* This is the case if the data for the INSERT is coming from a
895 ** single-row VALUES clause
drhcce7d172000-05-31 15:34:51 +0000896 */
897 NameContext sNC;
898 memset(&sNC, 0, sizeof(sNC));
899 sNC.pParse = pParse;
900 srcTab = -1;
901 assert( useTempTable==0 );
drhfea870b2015-08-24 20:54:06 +0000902 if( pList ){
903 nColumn = pList->nExpr;
904 if( sqlite3ResolveExprListNames(&sNC, pList) ){
drhcce7d172000-05-31 15:34:51 +0000905 goto insert_cleanup;
906 }
drhfea870b2015-08-24 20:54:06 +0000907 }else{
908 nColumn = 0;
drhcce7d172000-05-31 15:34:51 +0000909 }
910 }
911
drhaacc5432002-01-06 17:07:40 +0000912 /* If there is no IDLIST term but the table has an integer primary
drhd82b5022013-10-26 00:58:34 +0000913 ** key, the set the ipkColumn variable to the integer primary key
914 ** column index in the original table definition.
drh4a324312001-12-21 14:30:42 +0000915 */
drh147d0cc2006-08-25 23:42:53 +0000916 if( pColumn==0 && nColumn>0 ){
drhd82b5022013-10-26 00:58:34 +0000917 ipkColumn = pTab->iPKey;
drh427b96a2019-10-22 13:01:24 +0000918#ifndef SQLITE_OMIT_GENERATED_COLUMNS
drh6ab61d72019-10-23 15:47:33 +0000919 if( ipkColumn>=0 && (pTab->tabFlags & TF_HasGenerated)!=0 ){
drh427b96a2019-10-22 13:01:24 +0000920 testcase( pTab->tabFlags & TF_HasVirtual );
drh6ab61d72019-10-23 15:47:33 +0000921 testcase( pTab->tabFlags & TF_HasStored );
drh427b96a2019-10-22 13:01:24 +0000922 for(i=ipkColumn-1; i>=0; i--){
923 if( pTab->aCol[i].colFlags & COLFLAG_GENERATED ){
924 testcase( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL );
drh6ab61d72019-10-23 15:47:33 +0000925 testcase( pTab->aCol[i].colFlags & COLFLAG_STORED );
drh427b96a2019-10-22 13:01:24 +0000926 ipkColumn--;
927 }
928 }
929 }
930#endif
drh4a324312001-12-21 14:30:42 +0000931 }
drh05a86c52014-02-16 01:55:49 +0000932
drhcce7d172000-05-31 15:34:51 +0000933 /* Make sure the number of columns in the source data matches the number
934 ** of columns to be inserted into the table.
935 */
drhf0c91452015-11-18 18:43:15 +0000936 for(i=0; i<pTab->nCol; i++){
drh7e508f12019-10-16 19:31:46 +0000937 if( pTab->aCol[i].colFlags & COLFLAG_NOINSERT ) nHidden++;
drhcce7d172000-05-31 15:34:51 +0000938 }
939 if( pColumn==0 && nColumn && nColumn!=(pTab->nCol-nHidden) ){
940 sqlite3ErrorMsg(pParse,
941 "table %S has %d columns but %d values were supplied",
942 pTabList, 0, pTab->nCol-nHidden, nColumn);
943 goto insert_cleanup;
944 }
945 if( pColumn!=0 && nColumn!=pColumn->nId ){
946 sqlite3ErrorMsg(pParse, "%d values for %d columns", nColumn, pColumn->nId);
947 goto insert_cleanup;
948 }
danielk1977c3f9bad2002-05-15 08:30:12 +0000949
drhfeeb1392002-04-09 03:28:01 +0000950 /* Initialize the count of rows to be inserted
951 */
drh79636912018-04-19 23:52:39 +0000952 if( (db->flags & SQLITE_CountRows)!=0
953 && !pParse->nested
954 && !pParse->pTriggerTab
955 ){
drh6a288a32008-01-07 19:20:24 +0000956 regRowCount = ++pParse->nMem;
957 sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount);
drhfeeb1392002-04-09 03:28:01 +0000958 }
959
danielk1977e448dc42008-01-02 11:50:51 +0000960 /* If this is not a view, open the table and and all indices */
961 if( !isView ){
drhaa9b8962008-01-08 02:57:55 +0000962 int nIdx;
danfd261ec2015-10-22 20:54:33 +0000963 nIdx = sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, 0, -1, 0,
drh26198bb2013-10-31 11:15:09 +0000964 &iDataCur, &iIdxCur);
drha7c3b932019-05-07 19:13:42 +0000965 aRegIdx = sqlite3DbMallocRawNN(db, sizeof(int)*(nIdx+2));
drhaa9b8962008-01-08 02:57:55 +0000966 if( aRegIdx==0 ){
967 goto insert_cleanup;
968 }
drh2c4dfc32016-11-10 14:24:04 +0000969 for(i=0, pIdx=pTab->pIndex; i<nIdx; pIdx=pIdx->pNext, i++){
970 assert( pIdx );
drhaa9b8962008-01-08 02:57:55 +0000971 aRegIdx[i] = ++pParse->nMem;
drh2c4dfc32016-11-10 14:24:04 +0000972 pParse->nMem += pIdx->nColumn;
drhaa9b8962008-01-08 02:57:55 +0000973 }
drha7c3b932019-05-07 19:13:42 +0000974 aRegIdx[i] = ++pParse->nMem; /* Register to store the table record */
danielk1977c3f9bad2002-05-15 08:30:12 +0000975 }
drh788d55a2018-04-13 01:15:09 +0000976#ifndef SQLITE_OMIT_UPSERT
drh0b30a112018-04-13 21:55:22 +0000977 if( pUpsert ){
drhb042d922019-01-04 23:39:37 +0000978 if( IsVirtual(pTab) ){
979 sqlite3ErrorMsg(pParse, "UPSERT not implemented for virtual table \"%s\"",
980 pTab->zName);
981 goto insert_cleanup;
982 }
drhc6b24ab2019-12-06 01:23:38 +0000983 if( pTab->pSelect ){
984 sqlite3ErrorMsg(pParse, "cannot UPSERT a view");
985 goto insert_cleanup;
986 }
dan9105fd52019-08-19 17:26:32 +0000987 if( sqlite3HasExplicitNulls(pParse, pUpsert->pUpsertTarget) ){
988 goto insert_cleanup;
989 }
drh788d55a2018-04-13 01:15:09 +0000990 pTabList->a[0].iCursor = iDataCur;
drh0b30a112018-04-13 21:55:22 +0000991 pUpsert->pUpsertSrc = pTabList;
drheac9fab2018-04-16 13:00:50 +0000992 pUpsert->regData = regData;
drh7fc3aba2018-04-20 13:18:51 +0000993 pUpsert->iDataCur = iDataCur;
994 pUpsert->iIdxCur = iIdxCur;
drh0b30a112018-04-13 21:55:22 +0000995 if( pUpsert->pUpsertTarget ){
996 sqlite3UpsertAnalyzeTarget(pParse, pTabList, pUpsert);
997 }
drh788d55a2018-04-13 01:15:09 +0000998 }
999#endif
1000
danielk1977c3f9bad2002-05-15 08:30:12 +00001001
drhe00ee6e2008-06-20 15:24:01 +00001002 /* This is the top of the main insertion loop */
drh142e30d2002-08-28 03:00:58 +00001003 if( useTempTable ){
drhe00ee6e2008-06-20 15:24:01 +00001004 /* This block codes the top of loop only. The complete loop is the
1005 ** following pseudocode (template 4):
1006 **
drh81cf13e2014-02-07 18:27:53 +00001007 ** rewind temp table, if empty goto D
drhe00ee6e2008-06-20 15:24:01 +00001008 ** C: loop over rows of intermediate table
1009 ** transfer values form intermediate table into <table>
1010 ** end loop
1011 ** D: ...
1012 */
drh688852a2014-02-17 22:40:43 +00001013 addrInsTop = sqlite3VdbeAddOp1(v, OP_Rewind, srcTab); VdbeCoverage(v);
drhe00ee6e2008-06-20 15:24:01 +00001014 addrCont = sqlite3VdbeCurrentAddr(v);
drh142e30d2002-08-28 03:00:58 +00001015 }else if( pSelect ){
drhe00ee6e2008-06-20 15:24:01 +00001016 /* This block codes the top of loop only. The complete loop is the
1017 ** following pseudocode (template 3):
1018 **
drh81cf13e2014-02-07 18:27:53 +00001019 ** C: yield X, at EOF goto D
drhe00ee6e2008-06-20 15:24:01 +00001020 ** insert the select result into <table> from R..R+n
1021 ** goto C
1022 ** D: ...
1023 */
drh3aef2fb2020-01-02 17:46:02 +00001024 sqlite3VdbeReleaseRegisters(pParse, regData, pTab->nCol, 0, 0);
drh81cf13e2014-02-07 18:27:53 +00001025 addrInsTop = addrCont = sqlite3VdbeAddOp1(v, OP_Yield, dest.iSDParm);
drh688852a2014-02-17 22:40:43 +00001026 VdbeCoverage(v);
drhf5f19152019-10-21 01:04:11 +00001027 if( ipkColumn>=0 ){
1028 /* tag-20191021-001: If the INTEGER PRIMARY KEY is being generated by the
1029 ** SELECT, go ahead and copy the value into the rowid slot now, so that
1030 ** the value does not get overwritten by a NULL at tag-20191021-002. */
1031 sqlite3VdbeAddOp2(v, OP_Copy, regFromSelect+ipkColumn, regRowid);
1032 }
drh5974a302000-06-07 14:42:26 +00001033 }
drh1ccde152000-06-17 13:12:39 +00001034
drhf5f19152019-10-21 01:04:11 +00001035 /* Compute data for ordinary columns of the new entry. Values
1036 ** are written in storage order into registers starting with regData.
1037 ** Only ordinary columns are computed in this loop. The rowid
1038 ** (if there is one) is computed later and generated columns are
1039 ** computed after the rowid since they might depend on the value
1040 ** of the rowid.
1041 */
1042 nHidden = 0;
1043 iRegStore = regData; assert( regData==regRowid+1 );
1044 for(i=0; i<pTab->nCol; i++, iRegStore++){
1045 int k;
1046 u32 colFlags;
1047 assert( i>=nHidden );
1048 if( i==pTab->iPKey ){
1049 /* tag-20191021-002: References to the INTEGER PRIMARY KEY are filled
1050 ** using the rowid. So put a NULL in the IPK slot of the record to avoid
1051 ** using excess space. The file format definition requires this extra
1052 ** NULL - we cannot optimize further by skipping the column completely */
1053 sqlite3VdbeAddOp1(v, OP_SoftNull, iRegStore);
1054 continue;
1055 }
1056 if( ((colFlags = pTab->aCol[i].colFlags) & COLFLAG_NOINSERT)!=0 ){
1057 nHidden++;
1058 if( (colFlags & COLFLAG_VIRTUAL)!=0 ){
1059 /* Virtual columns do not participate in OP_MakeRecord. So back up
1060 ** iRegStore by one slot to compensate for the iRegStore++ in the
1061 ** outer for() loop */
1062 iRegStore--;
1063 continue;
1064 }else if( (colFlags & COLFLAG_STORED)!=0 ){
1065 /* Stored columns are computed later. But if there are BEFORE
1066 ** triggers, the slots used for stored columns will be OP_Copy-ed
1067 ** to a second block of registers, so the register needs to be
1068 ** initialized to NULL to avoid an uninitialized register read */
1069 if( tmask & TRIGGER_BEFORE ){
1070 sqlite3VdbeAddOp1(v, OP_SoftNull, iRegStore);
1071 }
1072 continue;
1073 }else if( pColumn==0 ){
1074 /* Hidden columns that are not explicitly named in the INSERT
1075 ** get there default value */
1076 sqlite3ExprCodeFactorable(pParse, pTab->aCol[i].pDflt, iRegStore);
1077 continue;
1078 }
1079 }
1080 if( pColumn ){
1081 for(j=0; j<pColumn->nId && pColumn->a[j].idx!=i; j++){}
1082 if( j>=pColumn->nId ){
1083 /* A column not named in the insert column list gets its
1084 ** default value */
1085 sqlite3ExprCodeFactorable(pParse, pTab->aCol[i].pDflt, iRegStore);
1086 continue;
1087 }
1088 k = j;
1089 }else if( nColumn==0 ){
1090 /* This is INSERT INTO ... DEFAULT VALUES. Load the default value. */
1091 sqlite3ExprCodeFactorable(pParse, pTab->aCol[i].pDflt, iRegStore);
1092 continue;
1093 }else{
1094 k = i - nHidden;
1095 }
1096
1097 if( useTempTable ){
1098 sqlite3VdbeAddOp3(v, OP_Column, srcTab, k, iRegStore);
1099 }else if( pSelect ){
1100 if( regFromSelect!=regData ){
1101 sqlite3VdbeAddOp2(v, OP_SCopy, regFromSelect+k, iRegStore);
1102 }
1103 }else{
1104 sqlite3ExprCode(pParse, pList->a[k].pExpr, iRegStore);
1105 }
1106 }
1107
1108
drh5cf590c2003-04-24 01:45:04 +00001109 /* Run the BEFORE and INSTEAD OF triggers, if there are any
drh70ce3f02003-04-15 19:22:22 +00001110 */
drhec4ccdb2018-12-29 02:26:59 +00001111 endOfLoop = sqlite3VdbeMakeLabel(pParse);
danielk19772f886d12009-02-28 10:47:41 +00001112 if( tmask & TRIGGER_BEFORE ){
dan76d462e2009-08-30 11:42:51 +00001113 int regCols = sqlite3GetTempRange(pParse, pTab->nCol+1);
danielk1977c3f9bad2002-05-15 08:30:12 +00001114
drh70ce3f02003-04-15 19:22:22 +00001115 /* build the NEW.* reference row. Note that if there is an INTEGER
1116 ** PRIMARY KEY into which a NULL is being inserted, that NULL will be
1117 ** translated into a unique ID for the row. But on a BEFORE trigger,
1118 ** we do not know what the unique ID will be (because the insert has
1119 ** not happened yet) so we substitute a rowid of -1
1120 */
drhd82b5022013-10-26 00:58:34 +00001121 if( ipkColumn<0 ){
dan76d462e2009-08-30 11:42:51 +00001122 sqlite3VdbeAddOp2(v, OP_Integer, -1, regCols);
drh70ce3f02003-04-15 19:22:22 +00001123 }else{
drh728e0f92015-10-10 14:41:28 +00001124 int addr1;
drhec95c442013-10-23 01:57:32 +00001125 assert( !withoutRowid );
drh7fe45902009-05-01 02:08:04 +00001126 if( useTempTable ){
drhd82b5022013-10-26 00:58:34 +00001127 sqlite3VdbeAddOp3(v, OP_Column, srcTab, ipkColumn, regCols);
drh7fe45902009-05-01 02:08:04 +00001128 }else{
1129 assert( pSelect==0 ); /* Otherwise useTempTable is true */
drhd82b5022013-10-26 00:58:34 +00001130 sqlite3ExprCode(pParse, pList->a[ipkColumn].pExpr, regCols);
drh7fe45902009-05-01 02:08:04 +00001131 }
drh728e0f92015-10-10 14:41:28 +00001132 addr1 = sqlite3VdbeAddOp1(v, OP_NotNull, regCols); VdbeCoverage(v);
dan76d462e2009-08-30 11:42:51 +00001133 sqlite3VdbeAddOp2(v, OP_Integer, -1, regCols);
drh728e0f92015-10-10 14:41:28 +00001134 sqlite3VdbeJumpHere(v, addr1);
drh688852a2014-02-17 22:40:43 +00001135 sqlite3VdbeAddOp1(v, OP_MustBeInt, regCols); VdbeCoverage(v);
drh70ce3f02003-04-15 19:22:22 +00001136 }
1137
danielk1977034ca142007-06-26 10:38:54 +00001138 /* Cannot have triggers on a virtual table. If it were possible,
1139 ** this block would have to account for hidden column.
1140 */
dan165921a2009-08-28 18:53:45 +00001141 assert( !IsVirtual(pTab) );
danielk1977034ca142007-06-26 10:38:54 +00001142
drhf5f19152019-10-21 01:04:11 +00001143 /* Copy the new data already generated. */
1144 assert( pTab->nNVCol>0 );
1145 sqlite3VdbeAddOp3(v, OP_Copy, regRowid+1, regCols+1, pTab->nNVCol-1);
1146
1147#ifndef SQLITE_OMIT_GENERATED_COLUMNS
1148 /* Compute the new value for generated columns after all other
1149 ** columns have already been computed. This must be done after
1150 ** computing the ROWID in case one of the generated columns
1151 ** refers to the ROWID. */
drh427b96a2019-10-22 13:01:24 +00001152 if( pTab->tabFlags & TF_HasGenerated ){
1153 testcase( pTab->tabFlags & TF_HasVirtual );
1154 testcase( pTab->tabFlags & TF_HasStored );
drhf5f19152019-10-21 01:04:11 +00001155 sqlite3ComputeGeneratedColumns(pParse, regCols+1, pTab);
danielk1977c3f9bad2002-05-15 08:30:12 +00001156 }
drhf5f19152019-10-21 01:04:11 +00001157#endif
danielk1977a37cdde2004-05-16 11:15:36 +00001158
1159 /* If this is an INSERT on a view with an INSTEAD OF INSERT trigger,
1160 ** do not attempt any conversions before assembling the record.
1161 ** If this is a real table, attempt conversions as required by the
1162 ** table column affinities.
1163 */
1164 if( !isView ){
drh57bf4a82014-02-17 14:59:22 +00001165 sqlite3TableAffinity(v, pTab, regCols+1);
danielk1977a37cdde2004-05-16 11:15:36 +00001166 }
danielk1977c3f9bad2002-05-15 08:30:12 +00001167
drh5cf590c2003-04-24 01:45:04 +00001168 /* Fire BEFORE or INSTEAD OF triggers */
dan165921a2009-08-28 18:53:45 +00001169 sqlite3CodeRowTrigger(pParse, pTrigger, TK_INSERT, 0, TRIGGER_BEFORE,
dan94d7f502009-09-24 09:05:49 +00001170 pTab, regCols-pTab->nCol-1, onError, endOfLoop);
dan165921a2009-08-28 18:53:45 +00001171
dan76d462e2009-08-30 11:42:51 +00001172 sqlite3ReleaseTempRange(pParse, regCols, pTab->nCol+1);
drh70ce3f02003-04-15 19:22:22 +00001173 }
danielk1977c3f9bad2002-05-15 08:30:12 +00001174
drh5cf590c2003-04-24 01:45:04 +00001175 if( !isView ){
drh4cbdda92006-06-14 19:00:20 +00001176 if( IsVirtual(pTab) ){
danielk1977287fb612008-01-04 19:10:28 +00001177 /* The row that the VUpdate opcode will delete: none */
drh6a288a32008-01-07 19:20:24 +00001178 sqlite3VdbeAddOp2(v, OP_Null, 0, regIns);
drh4cbdda92006-06-14 19:00:20 +00001179 }
drhd82b5022013-10-26 00:58:34 +00001180 if( ipkColumn>=0 ){
drhf5f19152019-10-21 01:04:11 +00001181 /* Compute the new rowid */
drh142e30d2002-08-28 03:00:58 +00001182 if( useTempTable ){
drhd82b5022013-10-26 00:58:34 +00001183 sqlite3VdbeAddOp3(v, OP_Column, srcTab, ipkColumn, regRowid);
drh142e30d2002-08-28 03:00:58 +00001184 }else if( pSelect ){
drhf5f19152019-10-21 01:04:11 +00001185 /* Rowid already initialized at tag-20191021-001 */
danielk1977c3f9bad2002-05-15 08:30:12 +00001186 }else{
drh04fcef02019-01-17 04:40:04 +00001187 Expr *pIpk = pList->a[ipkColumn].pExpr;
1188 if( pIpk->op==TK_NULL && !IsVirtual(pTab) ){
1189 sqlite3VdbeAddOp3(v, OP_NewRowid, iDataCur, regRowid, regAutoinc);
drhe4d90812007-03-29 05:51:49 +00001190 appendFlag = 1;
drh04fcef02019-01-17 04:40:04 +00001191 }else{
1192 sqlite3ExprCode(pParse, pList->a[ipkColumn].pExpr, regRowid);
drhe4d90812007-03-29 05:51:49 +00001193 }
danielk1977c3f9bad2002-05-15 08:30:12 +00001194 }
drhf0863fe2005-06-12 21:35:51 +00001195 /* If the PRIMARY KEY expression is NULL, then use OP_NewRowid
drh27a32782002-06-19 20:32:43 +00001196 ** to generate a unique primary key value.
1197 */
drhe4d90812007-03-29 05:51:49 +00001198 if( !appendFlag ){
drh728e0f92015-10-10 14:41:28 +00001199 int addr1;
danielk1977bb50e7a2008-07-04 10:56:07 +00001200 if( !IsVirtual(pTab) ){
drh728e0f92015-10-10 14:41:28 +00001201 addr1 = sqlite3VdbeAddOp1(v, OP_NotNull, regRowid); VdbeCoverage(v);
drh26198bb2013-10-31 11:15:09 +00001202 sqlite3VdbeAddOp3(v, OP_NewRowid, iDataCur, regRowid, regAutoinc);
drh728e0f92015-10-10 14:41:28 +00001203 sqlite3VdbeJumpHere(v, addr1);
danielk1977bb50e7a2008-07-04 10:56:07 +00001204 }else{
drh728e0f92015-10-10 14:41:28 +00001205 addr1 = sqlite3VdbeCurrentAddr(v);
1206 sqlite3VdbeAddOp2(v, OP_IsNull, regRowid, addr1+2); VdbeCoverage(v);
danielk1977bb50e7a2008-07-04 10:56:07 +00001207 }
drh688852a2014-02-17 22:40:43 +00001208 sqlite3VdbeAddOp1(v, OP_MustBeInt, regRowid); VdbeCoverage(v);
drhe4d90812007-03-29 05:51:49 +00001209 }
drhec95c442013-10-23 01:57:32 +00001210 }else if( IsVirtual(pTab) || withoutRowid ){
drh6a288a32008-01-07 19:20:24 +00001211 sqlite3VdbeAddOp2(v, OP_Null, 0, regRowid);
danielk1977c3f9bad2002-05-15 08:30:12 +00001212 }else{
drh26198bb2013-10-31 11:15:09 +00001213 sqlite3VdbeAddOp3(v, OP_NewRowid, iDataCur, regRowid, regAutoinc);
drhe4d90812007-03-29 05:51:49 +00001214 appendFlag = 1;
drh4a324312001-12-21 14:30:42 +00001215 }
drh6a288a32008-01-07 19:20:24 +00001216 autoIncStep(pParse, regAutoinc, regRowid);
drh4a324312001-12-21 14:30:42 +00001217
drhc1431142019-10-17 17:54:05 +00001218#ifndef SQLITE_OMIT_GENERATED_COLUMNS
drhdd6cc9b2019-10-19 18:47:27 +00001219 /* Compute the new value for generated columns after all other
drhf5f19152019-10-21 01:04:11 +00001220 ** columns have already been computed. This must be done after
1221 ** computing the ROWID in case one of the generated columns
drhb5f62432019-12-10 02:48:41 +00001222 ** is derived from the INTEGER PRIMARY KEY. */
drh427b96a2019-10-22 13:01:24 +00001223 if( pTab->tabFlags & TF_HasGenerated ){
drhdd6cc9b2019-10-19 18:47:27 +00001224 sqlite3ComputeGeneratedColumns(pParse, regRowid+1, pTab);
drhbed86902000-06-02 13:27:59 +00001225 }
drhc1431142019-10-17 17:54:05 +00001226#endif
danielk1977c3f9bad2002-05-15 08:30:12 +00001227
1228 /* Generate code to check constraints and generate index keys and
danielk1977f29ce552002-05-19 23:43:12 +00001229 ** do the insertion.
1230 */
drh4cbdda92006-06-14 19:00:20 +00001231#ifndef SQLITE_OMIT_VIRTUALTABLE
1232 if( IsVirtual(pTab) ){
danielk1977595a5232009-07-24 17:58:53 +00001233 const char *pVTab = (const char *)sqlite3GetVTable(db, pTab);
drh4f3dd152008-04-28 18:46:43 +00001234 sqlite3VtabMakeWritable(pParse, pTab);
danielk1977595a5232009-07-24 17:58:53 +00001235 sqlite3VdbeAddOp4(v, OP_VUpdate, 1, pTab->nCol+2, regIns, pVTab, P4_VTAB);
danb061d052011-04-25 18:49:57 +00001236 sqlite3VdbeChangeP5(v, onError==OE_Default ? OE_Abort : onError);
dane0af83a2009-09-08 19:15:01 +00001237 sqlite3MayAbort(pParse);
drh4cbdda92006-06-14 19:00:20 +00001238 }else
1239#endif
1240 {
danielk1977de630352009-05-04 11:42:29 +00001241 int isReplace; /* Set to true if constraints may cause a replace */
dan3b908d42016-11-08 19:22:32 +00001242 int bUseSeek; /* True to use OPFLAG_SEEKRESULT */
drhf8ffb272013-11-01 17:08:56 +00001243 sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur,
drh788d55a2018-04-13 01:15:09 +00001244 regIns, 0, ipkColumn>=0, onError, endOfLoop, &isReplace, 0, pUpsert
drh04adf412008-01-08 18:57:50 +00001245 );
dan8ff2d952013-09-05 18:40:29 +00001246 sqlite3FkCheck(pParse, pTab, 0, regIns, 0, 0);
dan3b908d42016-11-08 19:22:32 +00001247
1248 /* Set the OPFLAG_USESEEKRESULT flag if either (a) there are no REPLACE
1249 ** constraints or (b) there are no triggers and this table is not a
1250 ** parent table in a foreign key constraint. It is safe to set the
1251 ** flag in the second case as if any REPLACE constraint is hit, an
1252 ** OP_Delete or OP_IdxDelete instruction will be executed on each
1253 ** cursor that is disturbed. And these instructions both clear the
1254 ** VdbeCursor.seekResult variable, disabling the OPFLAG_USESEEKRESULT
1255 ** functionality. */
drh06baba52019-10-24 19:35:26 +00001256 bUseSeek = (isReplace==0 || !sqlite3VdbeHasSubProgram(v));
drh26198bb2013-10-31 11:15:09 +00001257 sqlite3CompleteInsertion(pParse, pTab, iDataCur, iIdxCur,
dan3b908d42016-11-08 19:22:32 +00001258 regIns, aRegIdx, 0, appendFlag, bUseSeek
1259 );
drh4cbdda92006-06-14 19:00:20 +00001260 }
drh5cf590c2003-04-24 01:45:04 +00001261 }
danielk1977c3f9bad2002-05-15 08:30:12 +00001262
drh5cf590c2003-04-24 01:45:04 +00001263 /* Update the count of rows that are inserted
1264 */
drh79636912018-04-19 23:52:39 +00001265 if( regRowCount ){
drh6a288a32008-01-07 19:20:24 +00001266 sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1);
drh5974a302000-06-07 14:42:26 +00001267 }
drh1ccde152000-06-17 13:12:39 +00001268
danielk19772f886d12009-02-28 10:47:41 +00001269 if( pTrigger ){
danielk1977c3f9bad2002-05-15 08:30:12 +00001270 /* Code AFTER triggers */
dan165921a2009-08-28 18:53:45 +00001271 sqlite3CodeRowTrigger(pParse, pTrigger, TK_INSERT, 0, TRIGGER_AFTER,
dan94d7f502009-09-24 09:05:49 +00001272 pTab, regData-2-pTab->nCol, onError, endOfLoop);
drh1bee3d72001-10-15 00:44:35 +00001273 }
1274
drhe00ee6e2008-06-20 15:24:01 +00001275 /* The bottom of the main insertion loop, if the data source
1276 ** is a SELECT statement.
danielk1977f29ce552002-05-19 23:43:12 +00001277 */
danielk19774adee202004-05-08 08:23:19 +00001278 sqlite3VdbeResolveLabel(v, endOfLoop);
drh142e30d2002-08-28 03:00:58 +00001279 if( useTempTable ){
drh688852a2014-02-17 22:40:43 +00001280 sqlite3VdbeAddOp2(v, OP_Next, srcTab, addrCont); VdbeCoverage(v);
drhe00ee6e2008-06-20 15:24:01 +00001281 sqlite3VdbeJumpHere(v, addrInsTop);
drh2eb95372008-06-06 15:04:36 +00001282 sqlite3VdbeAddOp1(v, OP_Close, srcTab);
drh142e30d2002-08-28 03:00:58 +00001283 }else if( pSelect ){
drh076e85f2015-09-03 13:46:12 +00001284 sqlite3VdbeGoto(v, addrCont);
drhd9670ab2019-12-28 01:52:46 +00001285#ifdef SQLITE_DEBUG
1286 /* If we are jumping back to an OP_Yield that is preceded by an
1287 ** OP_ReleaseReg, set the p5 flag on the OP_Goto so that the
1288 ** OP_ReleaseReg will be included in the loop. */
1289 if( sqlite3VdbeGetOp(v, addrCont-1)->opcode==OP_ReleaseReg ){
1290 assert( sqlite3VdbeGetOp(v, addrCont)->opcode==OP_Yield );
1291 sqlite3VdbeChangeP5(v, 1);
1292 }
1293#endif
drhe00ee6e2008-06-20 15:24:01 +00001294 sqlite3VdbeJumpHere(v, addrInsTop);
drh6b563442001-11-07 16:48:26 +00001295 }
danielk1977c3f9bad2002-05-15 08:30:12 +00001296
drh0b9f50d2009-06-23 20:28:53 +00001297insert_end:
drhf3388142004-11-13 03:48:06 +00001298 /* Update the sqlite_sequence table by storing the content of the
drh0b9f50d2009-06-23 20:28:53 +00001299 ** maximum rowid counter values recorded while inserting into
1300 ** autoincrement tables.
drh2958a4e2004-11-12 03:56:15 +00001301 */
dan165921a2009-08-28 18:53:45 +00001302 if( pParse->nested==0 && pParse->pTriggerTab==0 ){
drh0b9f50d2009-06-23 20:28:53 +00001303 sqlite3AutoincrementEnd(pParse);
1304 }
drh2958a4e2004-11-12 03:56:15 +00001305
drh1bee3d72001-10-15 00:44:35 +00001306 /*
danielk1977e7de6f22004-11-05 06:02:06 +00001307 ** Return the number of rows inserted. If this routine is
1308 ** generating code because of a call to sqlite3NestedParse(), do not
1309 ** invoke the callback function.
drh1bee3d72001-10-15 00:44:35 +00001310 */
drh79636912018-04-19 23:52:39 +00001311 if( regRowCount ){
drh6a288a32008-01-07 19:20:24 +00001312 sqlite3VdbeAddOp2(v, OP_ResultRow, regRowCount, 1);
danielk197722322fd2004-05-25 23:35:17 +00001313 sqlite3VdbeSetNumCols(v, 1);
danielk197710fb7492008-10-31 10:53:22 +00001314 sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows inserted", SQLITE_STATIC);
drh1bee3d72001-10-15 00:44:35 +00001315 }
drhcce7d172000-05-31 15:34:51 +00001316
1317insert_cleanup:
drh633e6d52008-07-28 19:34:53 +00001318 sqlite3SrcListDelete(db, pTabList);
1319 sqlite3ExprListDelete(db, pList);
drh46d2e5c2018-04-12 13:15:43 +00001320 sqlite3UpsertDelete(db, pUpsert);
drh633e6d52008-07-28 19:34:53 +00001321 sqlite3SelectDelete(db, pSelect);
1322 sqlite3IdListDelete(db, pColumn);
1323 sqlite3DbFree(db, aRegIdx);
drhcce7d172000-05-31 15:34:51 +00001324}
drh9cfcf5d2002-01-29 18:41:24 +00001325
dan75cbd982009-09-21 16:06:03 +00001326/* Make sure "isView" and other macros defined above are undefined. Otherwise
peter.d.reid60ec9142014-09-06 16:39:46 +00001327** they may interfere with compilation of other functions in this file
dan75cbd982009-09-21 16:06:03 +00001328** (or in another file, if this file becomes part of the amalgamation). */
1329#ifdef isView
1330 #undef isView
1331#endif
1332#ifdef pTrigger
1333 #undef pTrigger
1334#endif
1335#ifdef tmask
1336 #undef tmask
1337#endif
1338
drh9cfcf5d2002-01-29 18:41:24 +00001339/*
drhe9816d82018-09-15 21:38:48 +00001340** Meanings of bits in of pWalker->eCode for
1341** sqlite3ExprReferencesUpdatedColumn()
drh98bfa162016-02-10 18:24:05 +00001342*/
1343#define CKCNSTRNT_COLUMN 0x01 /* CHECK constraint uses a changing column */
1344#define CKCNSTRNT_ROWID 0x02 /* CHECK constraint references the ROWID */
1345
drhe9816d82018-09-15 21:38:48 +00001346/* This is the Walker callback from sqlite3ExprReferencesUpdatedColumn().
1347* Set bit 0x01 of pWalker->eCode if pWalker->eCode to 0 and if this
1348** expression node references any of the
drh2a0b5272016-02-10 16:52:24 +00001349** columns that are being modifed by an UPDATE statement.
1350*/
1351static int checkConstraintExprNode(Walker *pWalker, Expr *pExpr){
drh98bfa162016-02-10 18:24:05 +00001352 if( pExpr->op==TK_COLUMN ){
1353 assert( pExpr->iColumn>=0 || pExpr->iColumn==-1 );
1354 if( pExpr->iColumn>=0 ){
1355 if( pWalker->u.aiCol[pExpr->iColumn]>=0 ){
1356 pWalker->eCode |= CKCNSTRNT_COLUMN;
1357 }
1358 }else{
1359 pWalker->eCode |= CKCNSTRNT_ROWID;
1360 }
drh2a0b5272016-02-10 16:52:24 +00001361 }
1362 return WRC_Continue;
1363}
1364
1365/*
1366** pExpr is a CHECK constraint on a row that is being UPDATE-ed. The
1367** only columns that are modified by the UPDATE are those for which
drh98bfa162016-02-10 18:24:05 +00001368** aiChng[i]>=0, and also the ROWID is modified if chngRowid is true.
1369**
drhe9816d82018-09-15 21:38:48 +00001370** Return true if CHECK constraint pExpr uses any of the
drh98bfa162016-02-10 18:24:05 +00001371** changing columns (or the rowid if it is changing). In other words,
drhe9816d82018-09-15 21:38:48 +00001372** return true if this CHECK constraint must be validated for
drh98bfa162016-02-10 18:24:05 +00001373** the new row in the UPDATE statement.
drhe9816d82018-09-15 21:38:48 +00001374**
1375** 2018-09-15: pExpr might also be an expression for an index-on-expressions.
1376** The operation of this routine is the same - return true if an only if
1377** the expression uses one or more of columns identified by the second and
1378** third arguments.
drh2a0b5272016-02-10 16:52:24 +00001379*/
drhe9816d82018-09-15 21:38:48 +00001380int sqlite3ExprReferencesUpdatedColumn(
1381 Expr *pExpr, /* The expression to be checked */
1382 int *aiChng, /* aiChng[x]>=0 if column x changed by the UPDATE */
1383 int chngRowid /* True if UPDATE changes the rowid */
1384){
drh2a0b5272016-02-10 16:52:24 +00001385 Walker w;
1386 memset(&w, 0, sizeof(w));
drh98bfa162016-02-10 18:24:05 +00001387 w.eCode = 0;
drh2a0b5272016-02-10 16:52:24 +00001388 w.xExprCallback = checkConstraintExprNode;
1389 w.u.aiCol = aiChng;
1390 sqlite3WalkExpr(&w, pExpr);
drh05723a92016-02-10 19:10:50 +00001391 if( !chngRowid ){
1392 testcase( (w.eCode & CKCNSTRNT_ROWID)!=0 );
1393 w.eCode &= ~CKCNSTRNT_ROWID;
1394 }
1395 testcase( w.eCode==0 );
1396 testcase( w.eCode==CKCNSTRNT_COLUMN );
1397 testcase( w.eCode==CKCNSTRNT_ROWID );
1398 testcase( w.eCode==(CKCNSTRNT_ROWID|CKCNSTRNT_COLUMN) );
drhe9816d82018-09-15 21:38:48 +00001399 return w.eCode!=0;
drh2a0b5272016-02-10 16:52:24 +00001400}
1401
drh11e85272013-10-26 15:40:48 +00001402/*
drh6934fc72013-10-31 15:37:49 +00001403** Generate code to do constraint checks prior to an INSERT or an UPDATE
1404** on table pTab.
drh9cfcf5d2002-01-29 18:41:24 +00001405**
drh6934fc72013-10-31 15:37:49 +00001406** The regNewData parameter is the first register in a range that contains
1407** the data to be inserted or the data after the update. There will be
1408** pTab->nCol+1 registers in this range. The first register (the one
1409** that regNewData points to) will contain the new rowid, or NULL in the
1410** case of a WITHOUT ROWID table. The second register in the range will
1411** contain the content of the first table column. The third register will
1412** contain the content of the second table column. And so forth.
drh0ca3e242002-01-29 23:07:02 +00001413**
drhf8ffb272013-11-01 17:08:56 +00001414** The regOldData parameter is similar to regNewData except that it contains
1415** the data prior to an UPDATE rather than afterwards. regOldData is zero
1416** for an INSERT. This routine can distinguish between UPDATE and INSERT by
1417** checking regOldData for zero.
drh0ca3e242002-01-29 23:07:02 +00001418**
drhf8ffb272013-11-01 17:08:56 +00001419** For an UPDATE, the pkChng boolean is true if the true primary key (the
1420** rowid for a normal table or the PRIMARY KEY for a WITHOUT ROWID table)
1421** might be modified by the UPDATE. If pkChng is false, then the key of
1422** the iDataCur content table is guaranteed to be unchanged by the UPDATE.
drh0ca3e242002-01-29 23:07:02 +00001423**
drhf8ffb272013-11-01 17:08:56 +00001424** For an INSERT, the pkChng boolean indicates whether or not the rowid
1425** was explicitly specified as part of the INSERT statement. If pkChng
1426** is zero, it means that the either rowid is computed automatically or
1427** that the table is a WITHOUT ROWID table and has no rowid. On an INSERT,
1428** pkChng will only be true if the INSERT statement provides an integer
1429** value for either the rowid column or its INTEGER PRIMARY KEY alias.
drh0ca3e242002-01-29 23:07:02 +00001430**
drh6934fc72013-10-31 15:37:49 +00001431** The code generated by this routine will store new index entries into
drhaa9b8962008-01-08 02:57:55 +00001432** registers identified by aRegIdx[]. No index entry is created for
1433** indices where aRegIdx[i]==0. The order of indices in aRegIdx[] is
1434** the same as the order of indices on the linked list of indices
drh6934fc72013-10-31 15:37:49 +00001435** at pTab->pIndex.
1436**
drha7c3b932019-05-07 19:13:42 +00001437** (2019-05-07) The generated code also creates a new record for the
1438** main table, if pTab is a rowid table, and stores that record in the
1439** register identified by aRegIdx[nIdx] - in other words in the first
1440** entry of aRegIdx[] past the last index. It is important that the
1441** record be generated during constraint checks to avoid affinity changes
1442** to the register content that occur after constraint checks but before
1443** the new record is inserted.
1444**
drh6934fc72013-10-31 15:37:49 +00001445** The caller must have already opened writeable cursors on the main
1446** table and all applicable indices (that is to say, all indices for which
1447** aRegIdx[] is not zero). iDataCur is the cursor for the main table when
1448** inserting or updating a rowid table, or the cursor for the PRIMARY KEY
1449** index when operating on a WITHOUT ROWID table. iIdxCur is the cursor
1450** for the first index in the pTab->pIndex list. Cursors for other indices
1451** are at iIdxCur+N for the N-th element of the pTab->pIndex list.
drh9cfcf5d2002-01-29 18:41:24 +00001452**
1453** This routine also generates code to check constraints. NOT NULL,
1454** CHECK, and UNIQUE constraints are all checked. If a constraint fails,
drh1c928532002-01-31 15:54:21 +00001455** then the appropriate action is performed. There are five possible
1456** actions: ROLLBACK, ABORT, FAIL, REPLACE, and IGNORE.
drh9cfcf5d2002-01-29 18:41:24 +00001457**
1458** Constraint type Action What Happens
1459** --------------- ---------- ----------------------------------------
drh1c928532002-01-31 15:54:21 +00001460** any ROLLBACK The current transaction is rolled back and
drh6934fc72013-10-31 15:37:49 +00001461** sqlite3_step() returns immediately with a
drh9cfcf5d2002-01-29 18:41:24 +00001462** return code of SQLITE_CONSTRAINT.
1463**
drh1c928532002-01-31 15:54:21 +00001464** any ABORT Back out changes from the current command
1465** only (do not do a complete rollback) then
drh6934fc72013-10-31 15:37:49 +00001466** cause sqlite3_step() to return immediately
drh1c928532002-01-31 15:54:21 +00001467** with SQLITE_CONSTRAINT.
1468**
drh6934fc72013-10-31 15:37:49 +00001469** any FAIL Sqlite3_step() returns immediately with a
drh1c928532002-01-31 15:54:21 +00001470** return code of SQLITE_CONSTRAINT. The
1471** transaction is not rolled back and any
drh6934fc72013-10-31 15:37:49 +00001472** changes to prior rows are retained.
drh1c928532002-01-31 15:54:21 +00001473**
drh6934fc72013-10-31 15:37:49 +00001474** any IGNORE The attempt in insert or update the current
1475** row is skipped, without throwing an error.
1476** Processing continues with the next row.
1477** (There is an immediate jump to ignoreDest.)
drh9cfcf5d2002-01-29 18:41:24 +00001478**
1479** NOT NULL REPLACE The NULL value is replace by the default
1480** value for that column. If the default value
1481** is NULL, the action is the same as ABORT.
1482**
1483** UNIQUE REPLACE The other row that conflicts with the row
1484** being inserted is removed.
1485**
1486** CHECK REPLACE Illegal. The results in an exception.
1487**
drh1c928532002-01-31 15:54:21 +00001488** Which action to take is determined by the overrideError parameter.
1489** Or if overrideError==OE_Default, then the pParse->onError parameter
1490** is used. Or if pParse->onError==OE_Default then the onError value
1491** for the constraint is used.
drh9cfcf5d2002-01-29 18:41:24 +00001492*/
danielk19774adee202004-05-08 08:23:19 +00001493void sqlite3GenerateConstraintChecks(
drh6934fc72013-10-31 15:37:49 +00001494 Parse *pParse, /* The parser context */
1495 Table *pTab, /* The table being inserted or updated */
drhf8ffb272013-11-01 17:08:56 +00001496 int *aRegIdx, /* Use register aRegIdx[i] for index i. 0 for unused */
drh6934fc72013-10-31 15:37:49 +00001497 int iDataCur, /* Canonical data cursor (main table or PK index) */
1498 int iIdxCur, /* First index cursor */
1499 int regNewData, /* First register in a range holding values to insert */
drhf8ffb272013-11-01 17:08:56 +00001500 int regOldData, /* Previous content. 0 for INSERTs */
1501 u8 pkChng, /* Non-zero if the rowid or PRIMARY KEY changed */
1502 u8 overrideError, /* Override onError to this if not OE_Default */
drh6934fc72013-10-31 15:37:49 +00001503 int ignoreDest, /* Jump to this label on an OE_Ignore resolution */
drhbdb00222016-02-10 16:03:20 +00001504 int *pbMayReplace, /* OUT: Set to true if constraint may cause a replace */
drh788d55a2018-04-13 01:15:09 +00001505 int *aiChng, /* column i is unchanged if aiChng[i]<0 */
1506 Upsert *pUpsert /* ON CONFLICT clauses, if any. NULL otherwise */
drh9cfcf5d2002-01-29 18:41:24 +00001507){
drh6934fc72013-10-31 15:37:49 +00001508 Vdbe *v; /* VDBE under constrution */
drh1b7ecbb2009-05-03 01:00:59 +00001509 Index *pIdx; /* Pointer to one of the indices */
drh11e85272013-10-26 15:40:48 +00001510 Index *pPk = 0; /* The PRIMARY KEY index */
drh2938f922012-03-07 19:13:29 +00001511 sqlite3 *db; /* Database connection */
drhf8ffb272013-11-01 17:08:56 +00001512 int i; /* loop counter */
1513 int ix; /* Index loop counter */
1514 int nCol; /* Number of columns */
1515 int onError; /* Conflict resolution strategy */
drh1b7ecbb2009-05-03 01:00:59 +00001516 int seenReplace = 0; /* True if REPLACE is used to resolve INT PK conflict */
drh6fbe41a2013-10-30 20:22:55 +00001517 int nPkField; /* Number of fields in PRIMARY KEY. 1 for ROWID tables */
drh096fd472018-04-14 20:24:36 +00001518 Index *pUpIdx = 0; /* Index to which to apply the upsert */
drh8d1b82e2013-11-05 19:41:32 +00001519 u8 isUpdate; /* True if this is an UPDATE operation */
drh57bf4a82014-02-17 14:59:22 +00001520 u8 bAffinityDone = 0; /* True if the OP_Affinity operation has been run */
drh096fd472018-04-14 20:24:36 +00001521 int upsertBypass = 0; /* Address of Goto to bypass upsert subroutine */
drh84304502018-08-14 15:12:52 +00001522 int upsertJump = 0; /* Address of Goto that jumps into upsert subroutine */
1523 int ipkTop = 0; /* Top of the IPK uniqueness check */
1524 int ipkBottom = 0; /* OP_Goto at the end of the IPK uniqueness check */
drha407ecc2019-10-26 00:04:21 +00001525 /* Variables associated with retesting uniqueness constraints after
1526 ** replace triggers fire have run */
1527 int regTrigCnt; /* Register used to count replace trigger invocations */
1528 int addrRecheck = 0; /* Jump here to recheck all uniqueness constraints */
1529 int lblRecheckOk = 0; /* Each recheck jumps to this label if it passes */
1530 Trigger *pTrigger; /* List of DELETE triggers on the table pTab */
1531 int nReplaceTrig = 0; /* Number of replace triggers coded */
drh9cfcf5d2002-01-29 18:41:24 +00001532
drhf8ffb272013-11-01 17:08:56 +00001533 isUpdate = regOldData!=0;
drh2938f922012-03-07 19:13:29 +00001534 db = pParse->db;
drhf0b41742020-08-15 21:55:14 +00001535 v = pParse->pVdbe;
drh9cfcf5d2002-01-29 18:41:24 +00001536 assert( v!=0 );
drh417be792002-03-03 18:59:40 +00001537 assert( pTab->pSelect==0 ); /* This table is not a VIEW */
drh9cfcf5d2002-01-29 18:41:24 +00001538 nCol = pTab->nCol;
drh6934fc72013-10-31 15:37:49 +00001539
1540 /* pPk is the PRIMARY KEY index for WITHOUT ROWID tables and NULL for
1541 ** normal rowid tables. nPkField is the number of key fields in the
1542 ** pPk index or 1 for a rowid table. In other words, nPkField is the
1543 ** number of fields in the true primary key of the table. */
drh26198bb2013-10-31 11:15:09 +00001544 if( HasRowid(pTab) ){
1545 pPk = 0;
1546 nPkField = 1;
1547 }else{
1548 pPk = sqlite3PrimaryKeyIndex(pTab);
1549 nPkField = pPk->nKeyCol;
1550 }
drh6fbe41a2013-10-30 20:22:55 +00001551
1552 /* Record that this module has started */
1553 VdbeModuleComment((v, "BEGIN: GenCnstCks(%d,%d,%d,%d,%d)",
drh6934fc72013-10-31 15:37:49 +00001554 iDataCur, iIdxCur, regNewData, regOldData, pkChng));
drh9cfcf5d2002-01-29 18:41:24 +00001555
1556 /* Test all NOT NULL constraints.
1557 */
drhcbda9c72019-10-26 17:08:06 +00001558 if( pTab->tabFlags & TF_HasNotNull ){
drhad5f1572019-12-28 00:36:51 +00001559 int b2ndPass = 0; /* True if currently running 2nd pass */
1560 int nSeenReplace = 0; /* Number of ON CONFLICT REPLACE operations */
1561 int nGenerated = 0; /* Number of generated columns with NOT NULL */
1562 while(1){ /* Make 2 passes over columns. Exit loop via "break" */
1563 for(i=0; i<nCol; i++){
1564 int iReg; /* Register holding column value */
1565 Column *pCol = &pTab->aCol[i]; /* The column to check for NOT NULL */
1566 int isGenerated; /* non-zero if column is generated */
1567 onError = pCol->notNull;
1568 if( onError==OE_None ) continue; /* No NOT NULL on this column */
1569 if( i==pTab->iPKey ){
1570 continue; /* ROWID is never NULL */
1571 }
1572 isGenerated = pCol->colFlags & COLFLAG_GENERATED;
1573 if( isGenerated && !b2ndPass ){
1574 nGenerated++;
1575 continue; /* Generated columns processed on 2nd pass */
1576 }
1577 if( aiChng && aiChng[i]<0 && !isGenerated ){
1578 /* Do not check NOT NULL on columns that do not change */
1579 continue;
1580 }
1581 if( overrideError!=OE_Default ){
1582 onError = overrideError;
1583 }else if( onError==OE_Default ){
drhcbda9c72019-10-26 17:08:06 +00001584 onError = OE_Abort;
drhcbda9c72019-10-26 17:08:06 +00001585 }
drhad5f1572019-12-28 00:36:51 +00001586 if( onError==OE_Replace ){
1587 if( b2ndPass /* REPLACE becomes ABORT on the 2nd pass */
1588 || pCol->pDflt==0 /* REPLACE is ABORT if no DEFAULT value */
1589 ){
1590 testcase( pCol->colFlags & COLFLAG_VIRTUAL );
1591 testcase( pCol->colFlags & COLFLAG_STORED );
1592 testcase( pCol->colFlags & COLFLAG_GENERATED );
1593 onError = OE_Abort;
1594 }else{
1595 assert( !isGenerated );
1596 }
1597 }else if( b2ndPass && !isGenerated ){
1598 continue;
drhcbda9c72019-10-26 17:08:06 +00001599 }
drhad5f1572019-12-28 00:36:51 +00001600 assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail
1601 || onError==OE_Ignore || onError==OE_Replace );
1602 testcase( i!=sqlite3TableColumnToStorage(pTab, i) );
1603 iReg = sqlite3TableColumnToStorage(pTab, i) + regNewData + 1;
1604 switch( onError ){
1605 case OE_Replace: {
1606 int addr1 = sqlite3VdbeAddOp1(v, OP_NotNull, iReg);
1607 VdbeCoverage(v);
1608 assert( (pCol->colFlags & COLFLAG_GENERATED)==0 );
1609 nSeenReplace++;
drh5cf1b612020-03-10 18:55:41 +00001610 sqlite3ExprCodeCopy(pParse, pCol->pDflt, iReg);
drhad5f1572019-12-28 00:36:51 +00001611 sqlite3VdbeJumpHere(v, addr1);
1612 break;
1613 }
1614 case OE_Abort:
1615 sqlite3MayAbort(pParse);
drh08b92082020-08-10 14:18:00 +00001616 /* no break */ deliberate_fall_through
drhad5f1572019-12-28 00:36:51 +00001617 case OE_Rollback:
1618 case OE_Fail: {
1619 char *zMsg = sqlite3MPrintf(db, "%s.%s", pTab->zName,
1620 pCol->zName);
1621 sqlite3VdbeAddOp3(v, OP_HaltIfNull, SQLITE_CONSTRAINT_NOTNULL,
1622 onError, iReg);
1623 sqlite3VdbeAppendP4(v, zMsg, P4_DYNAMIC);
1624 sqlite3VdbeChangeP5(v, P5_ConstraintNotNull);
1625 VdbeCoverage(v);
1626 break;
1627 }
1628 default: {
1629 assert( onError==OE_Ignore );
1630 sqlite3VdbeAddOp2(v, OP_IsNull, iReg, ignoreDest);
1631 VdbeCoverage(v);
1632 break;
1633 }
1634 } /* end switch(onError) */
1635 } /* end loop i over columns */
1636 if( nGenerated==0 && nSeenReplace==0 ){
1637 /* If there are no generated columns with NOT NULL constraints
1638 ** and no NOT NULL ON CONFLICT REPLACE constraints, then a single
1639 ** pass is sufficient */
1640 break;
drh9cfcf5d2002-01-29 18:41:24 +00001641 }
drhad5f1572019-12-28 00:36:51 +00001642 if( b2ndPass ) break; /* Never need more than 2 passes */
1643 b2ndPass = 1;
drhef9f7192020-01-17 19:14:08 +00001644#ifndef SQLITE_OMIT_GENERATED_COLUMNS
drhad5f1572019-12-28 00:36:51 +00001645 if( nSeenReplace>0 && (pTab->tabFlags & TF_HasGenerated)!=0 ){
1646 /* If any NOT NULL ON CONFLICT REPLACE constraints fired on the
1647 ** first pass, recomputed values for all generated columns, as
1648 ** those values might depend on columns affected by the REPLACE.
1649 */
1650 sqlite3ComputeGeneratedColumns(pParse, regNewData+1, pTab);
1651 }
drhef9f7192020-01-17 19:14:08 +00001652#endif
drhad5f1572019-12-28 00:36:51 +00001653 } /* end of 2-pass loop */
1654 } /* end if( has-not-null-constraints ) */
drh9cfcf5d2002-01-29 18:41:24 +00001655
1656 /* Test all CHECK constraints
1657 */
drhffe07b22005-11-03 00:41:17 +00001658#ifndef SQLITE_OMIT_CHECK
drh2938f922012-03-07 19:13:29 +00001659 if( pTab->pCheck && (db->flags & SQLITE_IgnoreChecks)==0 ){
1660 ExprList *pCheck = pTab->pCheck;
drh6e97f8e2017-07-20 13:17:08 +00001661 pParse->iSelfTab = -(regNewData+1);
drhaa01c7e2006-03-15 16:26:10 +00001662 onError = overrideError!=OE_Default ? overrideError : OE_Abort;
drh2938f922012-03-07 19:13:29 +00001663 for(i=0; i<pCheck->nExpr; i++){
drh05723a92016-02-10 19:10:50 +00001664 int allOk;
drh5cf1b612020-03-10 18:55:41 +00001665 Expr *pCopy;
drh2a0b5272016-02-10 16:52:24 +00001666 Expr *pExpr = pCheck->a[i].pExpr;
drhe9816d82018-09-15 21:38:48 +00001667 if( aiChng
1668 && !sqlite3ExprReferencesUpdatedColumn(pExpr, aiChng, pkChng)
1669 ){
1670 /* The check constraints do not reference any of the columns being
1671 ** updated so there is no point it verifying the check constraint */
1672 continue;
1673 }
drh9dce0ef2020-02-01 21:03:27 +00001674 if( bAffinityDone==0 ){
1675 sqlite3TableAffinity(v, pTab, regNewData+1);
1676 bAffinityDone = 1;
1677 }
drhec4ccdb2018-12-29 02:26:59 +00001678 allOk = sqlite3VdbeMakeLabel(pParse);
drh4031baf2018-05-28 17:31:20 +00001679 sqlite3VdbeVerifyAbortable(v, onError);
drh5cf1b612020-03-10 18:55:41 +00001680 pCopy = sqlite3ExprDup(db, pExpr, 0);
1681 if( !db->mallocFailed ){
1682 sqlite3ExprIfTrue(pParse, pCopy, allOk, SQLITE_JUMPIFNULL);
1683 }
1684 sqlite3ExprDelete(db, pCopy);
drh2d8e9202012-12-08 04:10:44 +00001685 if( onError==OE_Ignore ){
drh076e85f2015-09-03 13:46:12 +00001686 sqlite3VdbeGoto(v, ignoreDest);
drh2d8e9202012-12-08 04:10:44 +00001687 }else{
drh41cee662019-12-12 20:22:34 +00001688 char *zName = pCheck->a[i].zEName;
drhe2678b92020-08-28 12:58:21 +00001689 assert( zName!=0 || pParse->db->mallocFailed );
drh0ce974d2019-06-12 22:46:04 +00001690 if( onError==OE_Replace ) onError = OE_Abort; /* IMP: R-26383-51744 */
drhd91c1a12013-02-09 13:58:25 +00001691 sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_CHECK,
drhf9c8ce32013-11-05 13:33:55 +00001692 onError, zName, P4_TRANSIENT,
1693 P5_ConstraintCheck);
drh2938f922012-03-07 19:13:29 +00001694 }
drh2d8e9202012-12-08 04:10:44 +00001695 sqlite3VdbeResolveLabel(v, allOk);
drhaa01c7e2006-03-15 16:26:10 +00001696 }
drh6e97f8e2017-07-20 13:17:08 +00001697 pParse->iSelfTab = 0;
drhffe07b22005-11-03 00:41:17 +00001698 }
1699#endif /* !defined(SQLITE_OMIT_CHECK) */
drh9cfcf5d2002-01-29 18:41:24 +00001700
drh096fd472018-04-14 20:24:36 +00001701 /* UNIQUE and PRIMARY KEY constraints should be handled in the following
1702 ** order:
1703 **
drh84304502018-08-14 15:12:52 +00001704 ** (1) OE_Update
1705 ** (2) OE_Abort, OE_Fail, OE_Rollback, OE_Ignore
drh096fd472018-04-14 20:24:36 +00001706 ** (3) OE_Replace
1707 **
1708 ** OE_Fail and OE_Ignore must happen before any changes are made.
1709 ** OE_Update guarantees that only a single row will change, so it
1710 ** must happen before OE_Replace. Technically, OE_Abort and OE_Rollback
1711 ** could happen in any order, but they are grouped up front for
1712 ** convenience.
1713 **
drh84304502018-08-14 15:12:52 +00001714 ** 2018-08-14: Ticket https://www.sqlite.org/src/info/908f001483982c43
1715 ** The order of constraints used to have OE_Update as (2) and OE_Abort
1716 ** and so forth as (1). But apparently PostgreSQL checks the OE_Update
1717 ** constraint before any others, so it had to be moved.
1718 **
drh096fd472018-04-14 20:24:36 +00001719 ** Constraint checking code is generated in this order:
1720 ** (A) The rowid constraint
1721 ** (B) Unique index constraints that do not have OE_Replace as their
1722 ** default conflict resolution strategy
1723 ** (C) Unique index that do use OE_Replace by default.
1724 **
1725 ** The ordering of (2) and (3) is accomplished by making sure the linked
1726 ** list of indexes attached to a table puts all OE_Replace indexes last
1727 ** in the list. See sqlite3CreateIndex() for where that happens.
1728 */
1729
drh096fd472018-04-14 20:24:36 +00001730 if( pUpsert ){
1731 if( pUpsert->pUpsertTarget==0 ){
drhdedbc502018-04-17 18:18:05 +00001732 /* An ON CONFLICT DO NOTHING clause, without a constraint-target.
1733 ** Make all unique constraint resolution be OE_Ignore */
1734 assert( pUpsert->pUpsertSet==0 );
1735 overrideError = OE_Ignore;
1736 pUpsert = 0;
drh096fd472018-04-14 20:24:36 +00001737 }else if( (pUpIdx = pUpsert->pUpsertIdx)!=0 ){
drh84304502018-08-14 15:12:52 +00001738 /* If the constraint-target uniqueness check must be run first.
1739 ** Jump to that uniqueness check now */
1740 upsertJump = sqlite3VdbeAddOp0(v, OP_Goto);
1741 VdbeComment((v, "UPSERT constraint goes first"));
drh096fd472018-04-14 20:24:36 +00001742 }
1743 }
1744
drha407ecc2019-10-26 00:04:21 +00001745 /* Determine if it is possible that triggers (either explicitly coded
1746 ** triggers or FK resolution actions) might run as a result of deletes
1747 ** that happen when OE_Replace conflict resolution occurs. (Call these
1748 ** "replace triggers".) If any replace triggers run, we will need to
1749 ** recheck all of the uniqueness constraints after they have all run.
1750 ** But on the recheck, the resolution is OE_Abort instead of OE_Replace.
1751 **
1752 ** If replace triggers are a possibility, then
1753 **
1754 ** (1) Allocate register regTrigCnt and initialize it to zero.
1755 ** That register will count the number of replace triggers that
drhd3c468b2019-10-26 16:38:49 +00001756 ** fire. Constraint recheck only occurs if the number is positive.
1757 ** (2) Initialize pTrigger to the list of all DELETE triggers on pTab.
drha407ecc2019-10-26 00:04:21 +00001758 ** (3) Initialize addrRecheck and lblRecheckOk
1759 **
1760 ** The uniqueness rechecking code will create a series of tests to run
1761 ** in a second pass. The addrRecheck and lblRecheckOk variables are
1762 ** used to link together these tests which are separated from each other
1763 ** in the generate bytecode.
1764 */
1765 if( (db->flags & (SQLITE_RecTriggers|SQLITE_ForeignKeys))==0 ){
1766 /* There are not DELETE triggers nor FK constraints. No constraint
1767 ** rechecks are needed. */
1768 pTrigger = 0;
1769 regTrigCnt = 0;
1770 }else{
1771 if( db->flags&SQLITE_RecTriggers ){
1772 pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0);
1773 regTrigCnt = pTrigger!=0 || sqlite3FkRequired(pParse, pTab, 0, 0);
1774 }else{
1775 pTrigger = 0;
1776 regTrigCnt = sqlite3FkRequired(pParse, pTab, 0, 0);
1777 }
1778 if( regTrigCnt ){
1779 /* Replace triggers might exist. Allocate the counter and
1780 ** initialize it to zero. */
1781 regTrigCnt = ++pParse->nMem;
1782 sqlite3VdbeAddOp2(v, OP_Integer, 0, regTrigCnt);
1783 VdbeComment((v, "trigger count"));
1784 lblRecheckOk = sqlite3VdbeMakeLabel(pParse);
1785 addrRecheck = lblRecheckOk;
1786 }
1787 }
1788
drhf8ffb272013-11-01 17:08:56 +00001789 /* If rowid is changing, make sure the new rowid does not previously
1790 ** exist in the table.
drh9cfcf5d2002-01-29 18:41:24 +00001791 */
drh6fbe41a2013-10-30 20:22:55 +00001792 if( pkChng && pPk==0 ){
drhec4ccdb2018-12-29 02:26:59 +00001793 int addrRowidOk = sqlite3VdbeMakeLabel(pParse);
drh11e85272013-10-26 15:40:48 +00001794
drhf8ffb272013-11-01 17:08:56 +00001795 /* Figure out what action to take in case of a rowid collision */
drh0ca3e242002-01-29 23:07:02 +00001796 onError = pTab->keyConf;
1797 if( overrideError!=OE_Default ){
1798 onError = overrideError;
drha996e472003-05-16 02:30:27 +00001799 }else if( onError==OE_Default ){
1800 onError = OE_Abort;
drh0ca3e242002-01-29 23:07:02 +00001801 }
drh11e85272013-10-26 15:40:48 +00001802
drhc8a0c902018-04-13 15:14:33 +00001803 /* figure out whether or not upsert applies in this case */
drh096fd472018-04-14 20:24:36 +00001804 if( pUpsert && pUpsert->pUpsertIdx==0 ){
drhc8a0c902018-04-13 15:14:33 +00001805 if( pUpsert->pUpsertSet==0 ){
1806 onError = OE_Ignore; /* DO NOTHING is the same as INSERT OR IGNORE */
1807 }else{
1808 onError = OE_Update; /* DO UPDATE */
1809 }
1810 }
1811
drh8d1b82e2013-11-05 19:41:32 +00001812 /* If the response to a rowid conflict is REPLACE but the response
1813 ** to some other UNIQUE constraint is FAIL or IGNORE, then we need
1814 ** to defer the running of the rowid conflict checking until after
1815 ** the UNIQUE constraints have run.
1816 */
drh84304502018-08-14 15:12:52 +00001817 if( onError==OE_Replace /* IPK rule is REPLACE */
1818 && onError!=overrideError /* Rules for other contraints are different */
1819 && pTab->pIndex /* There exist other constraints */
drh096fd472018-04-14 20:24:36 +00001820 ){
drh84304502018-08-14 15:12:52 +00001821 ipkTop = sqlite3VdbeAddOp0(v, OP_Goto)+1;
1822 VdbeComment((v, "defer IPK REPLACE until last"));
drh8d1b82e2013-11-05 19:41:32 +00001823 }
1824
drhbb6b1ca2018-04-19 13:52:39 +00001825 if( isUpdate ){
1826 /* pkChng!=0 does not mean that the rowid has changed, only that
1827 ** it might have changed. Skip the conflict logic below if the rowid
1828 ** is unchanged. */
1829 sqlite3VdbeAddOp3(v, OP_Eq, regNewData, addrRowidOk, regOldData);
1830 sqlite3VdbeChangeP5(v, SQLITE_NOTNULL);
1831 VdbeCoverage(v);
1832 }
1833
drhf8ffb272013-11-01 17:08:56 +00001834 /* Check to see if the new rowid already exists in the table. Skip
1835 ** the following conflict logic if it does not. */
drh7f5f3062018-04-17 20:06:24 +00001836 VdbeNoopComment((v, "uniqueness check for ROWID"));
drh4031baf2018-05-28 17:31:20 +00001837 sqlite3VdbeVerifyAbortable(v, onError);
drh6934fc72013-10-31 15:37:49 +00001838 sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, addrRowidOk, regNewData);
drh688852a2014-02-17 22:40:43 +00001839 VdbeCoverage(v);
drhf8ffb272013-11-01 17:08:56 +00001840
dane1e2ae22009-09-24 16:52:28 +00001841 switch( onError ){
1842 default: {
1843 onError = OE_Abort;
drh08b92082020-08-10 14:18:00 +00001844 /* no break */ deliberate_fall_through
drh79b0c952002-05-21 12:56:43 +00001845 }
dane1e2ae22009-09-24 16:52:28 +00001846 case OE_Rollback:
1847 case OE_Abort:
1848 case OE_Fail: {
drh99160482018-04-18 01:34:39 +00001849 testcase( onError==OE_Rollback );
1850 testcase( onError==OE_Abort );
1851 testcase( onError==OE_Fail );
drhf9c8ce32013-11-05 13:33:55 +00001852 sqlite3RowidConstraint(pParse, onError, pTab);
dane1e2ae22009-09-24 16:52:28 +00001853 break;
drh0ca3e242002-01-29 23:07:02 +00001854 }
dane1e2ae22009-09-24 16:52:28 +00001855 case OE_Replace: {
1856 /* If there are DELETE triggers on this table and the
1857 ** recursive-triggers flag is set, call GenerateRowDelete() to
mistachkind5578432012-08-25 10:01:29 +00001858 ** remove the conflicting row from the table. This will fire
dane1e2ae22009-09-24 16:52:28 +00001859 ** the triggers and remove both the table and index b-tree entries.
1860 **
1861 ** Otherwise, if there are no triggers or the recursive-triggers
danda730f62010-02-18 08:19:19 +00001862 ** flag is not set, but the table has one or more indexes, call
1863 ** GenerateRowIndexDelete(). This removes the index b-tree entries
1864 ** only. The table b-tree entry will be replaced by the new entry
1865 ** when it is inserted.
1866 **
1867 ** If either GenerateRowDelete() or GenerateRowIndexDelete() is called,
1868 ** also invoke MultiWrite() to indicate that this VDBE may require
1869 ** statement rollback (if the statement is aborted after the delete
1870 ** takes place). Earlier versions called sqlite3MultiWrite() regardless,
1871 ** but being more selective here allows statements like:
1872 **
1873 ** REPLACE INTO t(rowid) VALUES($newrowid)
1874 **
1875 ** to run without a statement journal if there are no indexes on the
1876 ** table.
1877 */
drha407ecc2019-10-26 00:04:21 +00001878 if( regTrigCnt ){
danda730f62010-02-18 08:19:19 +00001879 sqlite3MultiWrite(pParse);
drh26198bb2013-10-31 11:15:09 +00001880 sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,
dan438b8812015-09-15 15:55:15 +00001881 regNewData, 1, 0, OE_Replace, 1, -1);
drha407ecc2019-10-26 00:04:21 +00001882 sqlite3VdbeAddOp2(v, OP_AddImm, regTrigCnt, 1); /* incr trigger cnt */
1883 nReplaceTrig++;
dan46c47d42011-03-01 18:42:07 +00001884 }else{
drh9b1c62d2011-03-30 21:04:43 +00001885#ifdef SQLITE_ENABLE_PREUPDATE_HOOK
drh54f2cd92018-04-13 16:23:22 +00001886 assert( HasRowid(pTab) );
1887 /* This OP_Delete opcode fires the pre-update-hook only. It does
1888 ** not modify the b-tree. It is more efficient to let the coming
1889 ** OP_Insert replace the existing entry than it is to delete the
1890 ** existing entry and then insert a new one. */
1891 sqlite3VdbeAddOp2(v, OP_Delete, iDataCur, OPFLAG_ISNOOP);
1892 sqlite3VdbeAppendP4(v, pTab, P4_TABLE);
drh9b1c62d2011-03-30 21:04:43 +00001893#endif /* SQLITE_ENABLE_PREUPDATE_HOOK */
dan46c47d42011-03-01 18:42:07 +00001894 if( pTab->pIndex ){
1895 sqlite3MultiWrite(pParse);
drhb77ebd82015-09-15 13:42:16 +00001896 sqlite3GenerateRowIndexDelete(pParse, pTab, iDataCur, iIdxCur,0,-1);
dan46c47d42011-03-01 18:42:07 +00001897 }
dane1e2ae22009-09-24 16:52:28 +00001898 }
1899 seenReplace = 1;
1900 break;
drh5383ae52003-06-04 12:23:30 +00001901 }
drh9eddaca2018-04-13 18:59:17 +00001902#ifndef SQLITE_OMIT_UPSERT
1903 case OE_Update: {
dan2cc00422018-04-17 18:16:10 +00001904 sqlite3UpsertDoUpdate(pParse, pUpsert, pTab, 0, iDataCur);
drh08b92082020-08-10 14:18:00 +00001905 /* no break */ deliberate_fall_through
drh9eddaca2018-04-13 18:59:17 +00001906 }
1907#endif
dane1e2ae22009-09-24 16:52:28 +00001908 case OE_Ignore: {
drh99160482018-04-18 01:34:39 +00001909 testcase( onError==OE_Ignore );
drh076e85f2015-09-03 13:46:12 +00001910 sqlite3VdbeGoto(v, ignoreDest);
dane1e2ae22009-09-24 16:52:28 +00001911 break;
1912 }
1913 }
drh11e85272013-10-26 15:40:48 +00001914 sqlite3VdbeResolveLabel(v, addrRowidOk);
drh84304502018-08-14 15:12:52 +00001915 if( ipkTop ){
1916 ipkBottom = sqlite3VdbeAddOp0(v, OP_Goto);
1917 sqlite3VdbeJumpHere(v, ipkTop-1);
drh5383ae52003-06-04 12:23:30 +00001918 }
drh0ca3e242002-01-29 23:07:02 +00001919 }
drh0bd1f4e2002-06-06 18:54:39 +00001920
1921 /* Test all UNIQUE constraints by creating entries for each UNIQUE
1922 ** index and making sure that duplicate entries do not already exist.
drh11e85272013-10-26 15:40:48 +00001923 ** Compute the revised record entries for indices as we go.
drhf8ffb272013-11-01 17:08:56 +00001924 **
1925 ** This loop also handles the case of the PRIMARY KEY index for a
1926 ** WITHOUT ROWID table.
drh0bd1f4e2002-06-06 18:54:39 +00001927 */
drh26198bb2013-10-31 11:15:09 +00001928 for(ix=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, ix++){
drh6934fc72013-10-31 15:37:49 +00001929 int regIdx; /* Range of registers hold conent for pIdx */
1930 int regR; /* Range of registers holding conflicting PK */
1931 int iThisCur; /* Cursor for this UNIQUE index */
1932 int addrUniqueOk; /* Jump here if the UNIQUE constraint is satisfied */
drha407ecc2019-10-26 00:04:21 +00001933 int addrConflictCk; /* First opcode in the conflict check logic */
drh2184fc72011-04-09 03:04:13 +00001934
drh26198bb2013-10-31 11:15:09 +00001935 if( aRegIdx[ix]==0 ) continue; /* Skip indices that do not change */
drh7f5f3062018-04-17 20:06:24 +00001936 if( pUpIdx==pIdx ){
drh84304502018-08-14 15:12:52 +00001937 addrUniqueOk = upsertJump+1;
drh7f5f3062018-04-17 20:06:24 +00001938 upsertBypass = sqlite3VdbeGoto(v, 0);
1939 VdbeComment((v, "Skip upsert subroutine"));
drh84304502018-08-14 15:12:52 +00001940 sqlite3VdbeJumpHere(v, upsertJump);
drh7f5f3062018-04-17 20:06:24 +00001941 }else{
drhec4ccdb2018-12-29 02:26:59 +00001942 addrUniqueOk = sqlite3VdbeMakeLabel(pParse);
drh7f5f3062018-04-17 20:06:24 +00001943 }
drh84304502018-08-14 15:12:52 +00001944 if( bAffinityDone==0 && (pUpIdx==0 || pUpIdx==pIdx) ){
1945 sqlite3TableAffinity(v, pTab, regNewData+1);
1946 bAffinityDone = 1;
1947 }
drh8e50d652020-05-23 17:56:49 +00001948 VdbeNoopComment((v, "prep index %s", pIdx->zName));
drh6934fc72013-10-31 15:37:49 +00001949 iThisCur = iIdxCur+ix;
drh7f5f3062018-04-17 20:06:24 +00001950
drhb2fe7d82003-04-20 17:29:23 +00001951
drhf8ffb272013-11-01 17:08:56 +00001952 /* Skip partial indices for which the WHERE clause is not true */
drhb2b9d3d2013-08-01 01:14:43 +00001953 if( pIdx->pPartIdxWhere ){
drh26198bb2013-10-31 11:15:09 +00001954 sqlite3VdbeAddOp2(v, OP_Null, 0, aRegIdx[ix]);
drh6e97f8e2017-07-20 13:17:08 +00001955 pParse->iSelfTab = -(regNewData+1);
drh72bc8202015-06-11 13:58:35 +00001956 sqlite3ExprIfFalseDup(pParse, pIdx->pPartIdxWhere, addrUniqueOk,
1957 SQLITE_JUMPIFNULL);
drh6e97f8e2017-07-20 13:17:08 +00001958 pParse->iSelfTab = 0;
drhb2b9d3d2013-08-01 01:14:43 +00001959 }
1960
drh6934fc72013-10-31 15:37:49 +00001961 /* Create a record for this index entry as it should appear after
drhf8ffb272013-11-01 17:08:56 +00001962 ** the insert or update. Store that record in the aRegIdx[ix] register
1963 */
drhbf2f5732016-11-10 16:07:43 +00001964 regIdx = aRegIdx[ix]+1;
drh9cfcf5d2002-01-29 18:41:24 +00001965 for(i=0; i<pIdx->nColumn; i++){
drh6934fc72013-10-31 15:37:49 +00001966 int iField = pIdx->aiColumn[i];
drhf82b9af2013-11-01 14:03:20 +00001967 int x;
drh4b92f982015-09-29 17:20:14 +00001968 if( iField==XN_EXPR ){
drh6e97f8e2017-07-20 13:17:08 +00001969 pParse->iSelfTab = -(regNewData+1);
drh1c75c9d2015-12-21 15:22:13 +00001970 sqlite3ExprCodeCopy(pParse, pIdx->aColExpr->a[i].pExpr, regIdx+i);
drh6e97f8e2017-07-20 13:17:08 +00001971 pParse->iSelfTab = 0;
drh1f9ca2c2015-08-25 16:57:52 +00001972 VdbeComment((v, "%s column %d", pIdx->zName, i));
drh463e76f2019-10-18 10:05:06 +00001973 }else if( iField==XN_ROWID || iField==pTab->iPKey ){
1974 x = regNewData;
1975 sqlite3VdbeAddOp2(v, OP_IntCopy, x, regIdx+i);
1976 VdbeComment((v, "rowid"));
drh9cfcf5d2002-01-29 18:41:24 +00001977 }else{
drhc5f808d2019-10-19 15:01:52 +00001978 testcase( sqlite3TableColumnToStorage(pTab, iField)!=iField );
drhb9bcf7c2019-10-19 13:29:10 +00001979 x = sqlite3TableColumnToStorage(pTab, iField) + regNewData + 1;
drh463e76f2019-10-18 10:05:06 +00001980 sqlite3VdbeAddOp2(v, OP_SCopy, x, regIdx+i);
1981 VdbeComment((v, "%s", pTab->aCol[iField].zName));
drh9cfcf5d2002-01-29 18:41:24 +00001982 }
1983 }
drh26198bb2013-10-31 11:15:09 +00001984 sqlite3VdbeAddOp3(v, OP_MakeRecord, regIdx, pIdx->nColumn, aRegIdx[ix]);
drh26198bb2013-10-31 11:15:09 +00001985 VdbeComment((v, "for %s", pIdx->zName));
drh7e4acf72017-02-14 20:00:16 +00001986#ifdef SQLITE_ENABLE_NULL_TRIM
drh9df385e2019-02-19 13:08:35 +00001987 if( pIdx->idxType==SQLITE_IDXTYPE_PRIMARYKEY ){
1988 sqlite3SetMakeRecordP5(v, pIdx->pTable);
1989 }
drh7e4acf72017-02-14 20:00:16 +00001990#endif
drh3aef2fb2020-01-02 17:46:02 +00001991 sqlite3VdbeReleaseRegisters(pParse, regIdx, pIdx->nColumn, 0, 0);
drhb2fe7d82003-04-20 17:29:23 +00001992
drhf8ffb272013-11-01 17:08:56 +00001993 /* In an UPDATE operation, if this index is the PRIMARY KEY index
1994 ** of a WITHOUT ROWID table and there has been no change the
1995 ** primary key, then no collision is possible. The collision detection
1996 ** logic below can all be skipped. */
drh00012df2013-11-05 01:59:07 +00001997 if( isUpdate && pPk==pIdx && pkChng==0 ){
drhda475b82013-11-04 21:44:54 +00001998 sqlite3VdbeResolveLabel(v, addrUniqueOk);
1999 continue;
2000 }
drhf8ffb272013-11-01 17:08:56 +00002001
drh6934fc72013-10-31 15:37:49 +00002002 /* Find out what action to take in case there is a uniqueness conflict */
drh9cfcf5d2002-01-29 18:41:24 +00002003 onError = pIdx->onError;
danielk1977de630352009-05-04 11:42:29 +00002004 if( onError==OE_None ){
drh11e85272013-10-26 15:40:48 +00002005 sqlite3VdbeResolveLabel(v, addrUniqueOk);
danielk1977de630352009-05-04 11:42:29 +00002006 continue; /* pIdx is not a UNIQUE index */
2007 }
drh9cfcf5d2002-01-29 18:41:24 +00002008 if( overrideError!=OE_Default ){
2009 onError = overrideError;
drha996e472003-05-16 02:30:27 +00002010 }else if( onError==OE_Default ){
2011 onError = OE_Abort;
drh9cfcf5d2002-01-29 18:41:24 +00002012 }
drhc6c9e152016-11-10 17:01:36 +00002013
drhc8a0c902018-04-13 15:14:33 +00002014 /* Figure out if the upsert clause applies to this index */
drh096fd472018-04-14 20:24:36 +00002015 if( pUpIdx==pIdx ){
drhc8a0c902018-04-13 15:14:33 +00002016 if( pUpsert->pUpsertSet==0 ){
2017 onError = OE_Ignore; /* DO NOTHING is the same as INSERT OR IGNORE */
2018 }else{
2019 onError = OE_Update; /* DO UPDATE */
2020 }
2021 }
2022
drh801f55d2017-01-04 22:02:56 +00002023 /* Collision detection may be omitted if all of the following are true:
2024 ** (1) The conflict resolution algorithm is REPLACE
2025 ** (2) The table is a WITHOUT ROWID table
2026 ** (3) There are no secondary indexes on the table
2027 ** (4) No delete triggers need to be fired if there is a conflict
danf9a12a12017-01-05 06:57:42 +00002028 ** (5) No FK constraint counters need to be updated if a conflict occurs.
dan418454c2019-01-07 15:57:35 +00002029 **
2030 ** This is not possible for ENABLE_PREUPDATE_HOOK builds, as the row
2031 ** must be explicitly deleted in order to ensure any pre-update hook
2032 ** is invoked. */
2033#ifndef SQLITE_ENABLE_PREUPDATE_HOOK
drh801f55d2017-01-04 22:02:56 +00002034 if( (ix==0 && pIdx->pNext==0) /* Condition 3 */
2035 && pPk==pIdx /* Condition 2 */
2036 && onError==OE_Replace /* Condition 1 */
2037 && ( 0==(db->flags&SQLITE_RecTriggers) || /* Condition 4 */
2038 0==sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0))
danf9a12a12017-01-05 06:57:42 +00002039 && ( 0==(db->flags&SQLITE_ForeignKeys) || /* Condition 5 */
2040 (0==pTab->pFKey && 0==sqlite3FkReferences(pTab)))
drh801f55d2017-01-04 22:02:56 +00002041 ){
2042 sqlite3VdbeResolveLabel(v, addrUniqueOk);
2043 continue;
drhc6c9e152016-11-10 17:01:36 +00002044 }
dan418454c2019-01-07 15:57:35 +00002045#endif /* ifndef SQLITE_ENABLE_PREUPDATE_HOOK */
drhc6c9e152016-11-10 17:01:36 +00002046
drhb2fe7d82003-04-20 17:29:23 +00002047 /* Check to see if the new index entry will be unique */
drh4031baf2018-05-28 17:31:20 +00002048 sqlite3VdbeVerifyAbortable(v, onError);
drha407ecc2019-10-26 00:04:21 +00002049 addrConflictCk =
2050 sqlite3VdbeAddOp4Int(v, OP_NoConflict, iThisCur, addrUniqueOk,
2051 regIdx, pIdx->nKeyCol); VdbeCoverage(v);
drhf8ffb272013-11-01 17:08:56 +00002052
2053 /* Generate code to handle collisions */
drh392ee212013-11-08 16:54:56 +00002054 regR = (pIdx==pPk) ? regIdx : sqlite3GetTempRange(pParse, nPkField);
drh46d03fc2013-12-19 02:23:45 +00002055 if( isUpdate || onError==OE_Replace ){
2056 if( HasRowid(pTab) ){
2057 sqlite3VdbeAddOp2(v, OP_IdxRowid, iThisCur, regR);
2058 /* Conflict only if the rowid of the existing index entry
2059 ** is different from old-rowid */
2060 if( isUpdate ){
2061 sqlite3VdbeAddOp3(v, OP_Eq, regR, addrUniqueOk, regOldData);
drh3d77dee2014-02-19 14:20:49 +00002062 sqlite3VdbeChangeP5(v, SQLITE_NOTNULL);
drh688852a2014-02-17 22:40:43 +00002063 VdbeCoverage(v);
drhda475b82013-11-04 21:44:54 +00002064 }
drh46d03fc2013-12-19 02:23:45 +00002065 }else{
2066 int x;
2067 /* Extract the PRIMARY KEY from the end of the index entry and
2068 ** store it in registers regR..regR+nPk-1 */
drha021f122013-12-19 14:34:34 +00002069 if( pIdx!=pPk ){
drh46d03fc2013-12-19 02:23:45 +00002070 for(i=0; i<pPk->nKeyCol; i++){
drh4b92f982015-09-29 17:20:14 +00002071 assert( pPk->aiColumn[i]>=0 );
drhb9bcf7c2019-10-19 13:29:10 +00002072 x = sqlite3TableColumnToIndex(pIdx, pPk->aiColumn[i]);
drh46d03fc2013-12-19 02:23:45 +00002073 sqlite3VdbeAddOp3(v, OP_Column, iThisCur, x, regR+i);
2074 VdbeComment((v, "%s.%s", pTab->zName,
2075 pTab->aCol[pPk->aiColumn[i]].zName));
drhda475b82013-11-04 21:44:54 +00002076 }
drh46d03fc2013-12-19 02:23:45 +00002077 }
2078 if( isUpdate ){
2079 /* If currently processing the PRIMARY KEY of a WITHOUT ROWID
2080 ** table, only conflict if the new PRIMARY KEY values are actually
2081 ** different from the old.
2082 **
2083 ** For a UNIQUE index, only conflict if the PRIMARY KEY values
2084 ** of the matched index row are different from the original PRIMARY
2085 ** KEY values of this row before the update. */
2086 int addrJump = sqlite3VdbeCurrentAddr(v)+pPk->nKeyCol;
2087 int op = OP_Ne;
drh48dd1d82014-05-27 18:18:58 +00002088 int regCmp = (IsPrimaryKeyIndex(pIdx) ? regIdx : regR);
drh46d03fc2013-12-19 02:23:45 +00002089
2090 for(i=0; i<pPk->nKeyCol; i++){
2091 char *p4 = (char*)sqlite3LocateCollSeq(pParse, pPk->azColl[i]);
2092 x = pPk->aiColumn[i];
drh4b92f982015-09-29 17:20:14 +00002093 assert( x>=0 );
drh46d03fc2013-12-19 02:23:45 +00002094 if( i==(pPk->nKeyCol-1) ){
2095 addrJump = addrUniqueOk;
2096 op = OP_Eq;
2097 }
drhb6d861e2019-10-29 03:30:26 +00002098 x = sqlite3TableColumnToStorage(pTab, x);
drh46d03fc2013-12-19 02:23:45 +00002099 sqlite3VdbeAddOp4(v, op,
2100 regOldData+1+x, addrJump, regCmp+i, p4, P4_COLLSEQ
2101 );
drh3d77dee2014-02-19 14:20:49 +00002102 sqlite3VdbeChangeP5(v, SQLITE_NOTNULL);
2103 VdbeCoverageIf(v, op==OP_Eq);
2104 VdbeCoverageIf(v, op==OP_Ne);
drh46d03fc2013-12-19 02:23:45 +00002105 }
drh26198bb2013-10-31 11:15:09 +00002106 }
drh26198bb2013-10-31 11:15:09 +00002107 }
drh11e85272013-10-26 15:40:48 +00002108 }
drhb2fe7d82003-04-20 17:29:23 +00002109
2110 /* Generate code that executes if the new index entry is not unique */
danielk1977b84f96f2005-01-20 11:32:23 +00002111 assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail
drh9eddaca2018-04-13 18:59:17 +00002112 || onError==OE_Ignore || onError==OE_Replace || onError==OE_Update );
drh9cfcf5d2002-01-29 18:41:24 +00002113 switch( onError ){
drh1c928532002-01-31 15:54:21 +00002114 case OE_Rollback:
2115 case OE_Abort:
2116 case OE_Fail: {
drh99160482018-04-18 01:34:39 +00002117 testcase( onError==OE_Rollback );
2118 testcase( onError==OE_Abort );
2119 testcase( onError==OE_Fail );
drhf9c8ce32013-11-05 13:33:55 +00002120 sqlite3UniqueConstraint(pParse, onError, pIdx);
drh9cfcf5d2002-01-29 18:41:24 +00002121 break;
2122 }
drh9eddaca2018-04-13 18:59:17 +00002123#ifndef SQLITE_OMIT_UPSERT
2124 case OE_Update: {
dan2cc00422018-04-17 18:16:10 +00002125 sqlite3UpsertDoUpdate(pParse, pUpsert, pTab, pIdx, iIdxCur+ix);
drh08b92082020-08-10 14:18:00 +00002126 /* no break */ deliberate_fall_through
drh9eddaca2018-04-13 18:59:17 +00002127 }
2128#endif
drh9cfcf5d2002-01-29 18:41:24 +00002129 case OE_Ignore: {
drh99160482018-04-18 01:34:39 +00002130 testcase( onError==OE_Ignore );
drh076e85f2015-09-03 13:46:12 +00002131 sqlite3VdbeGoto(v, ignoreDest);
drh9cfcf5d2002-01-29 18:41:24 +00002132 break;
2133 }
drh098d1682009-05-02 15:46:46 +00002134 default: {
drha407ecc2019-10-26 00:04:21 +00002135 int nConflictCk; /* Number of opcodes in conflict check logic */
2136
drh098d1682009-05-02 15:46:46 +00002137 assert( onError==OE_Replace );
drha407ecc2019-10-26 00:04:21 +00002138 nConflictCk = sqlite3VdbeCurrentAddr(v) - addrConflictCk;
drhd3c468b2019-10-26 16:38:49 +00002139 assert( nConflictCk>0 );
2140 testcase( nConflictCk>1 );
drha407ecc2019-10-26 00:04:21 +00002141 if( regTrigCnt ){
danfecfb312018-05-28 18:29:46 +00002142 sqlite3MultiWrite(pParse);
drha407ecc2019-10-26 00:04:21 +00002143 nReplaceTrig++;
danfecfb312018-05-28 18:29:46 +00002144 }
drh7b14b652019-12-29 22:08:20 +00002145 if( pTrigger && isUpdate ){
2146 sqlite3VdbeAddOp1(v, OP_CursorLock, iDataCur);
2147 }
drh26198bb2013-10-31 11:15:09 +00002148 sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,
drhb0264ee2015-09-14 14:45:50 +00002149 regR, nPkField, 0, OE_Replace,
drh68116932017-01-07 14:47:03 +00002150 (pIdx==pPk ? ONEPASS_SINGLE : ONEPASS_OFF), iThisCur);
drh7b14b652019-12-29 22:08:20 +00002151 if( pTrigger && isUpdate ){
2152 sqlite3VdbeAddOp1(v, OP_CursorUnlock, iDataCur);
2153 }
drha407ecc2019-10-26 00:04:21 +00002154 if( regTrigCnt ){
drha407ecc2019-10-26 00:04:21 +00002155 int addrBypass; /* Jump destination to bypass recheck logic */
2156
2157 sqlite3VdbeAddOp2(v, OP_AddImm, regTrigCnt, 1); /* incr trigger cnt */
2158 addrBypass = sqlite3VdbeAddOp0(v, OP_Goto); /* Bypass recheck */
2159 VdbeComment((v, "bypass recheck"));
2160
2161 /* Here we insert code that will be invoked after all constraint
2162 ** checks have run, if and only if one or more replace triggers
2163 ** fired. */
2164 sqlite3VdbeResolveLabel(v, lblRecheckOk);
2165 lblRecheckOk = sqlite3VdbeMakeLabel(pParse);
2166 if( pIdx->pPartIdxWhere ){
2167 /* Bypass the recheck if this partial index is not defined
2168 ** for the current row */
drh06608842019-10-26 01:43:14 +00002169 sqlite3VdbeAddOp2(v, OP_IsNull, regIdx-1, lblRecheckOk);
drha407ecc2019-10-26 00:04:21 +00002170 VdbeCoverage(v);
2171 }
2172 /* Copy the constraint check code from above, except change
2173 ** the constraint-ok jump destination to be the address of
2174 ** the next retest block */
drhd3c468b2019-10-26 16:38:49 +00002175 while( nConflictCk>0 ){
drhd901b162019-10-26 12:27:55 +00002176 VdbeOp x; /* Conflict check opcode to copy */
2177 /* The sqlite3VdbeAddOp4() call might reallocate the opcode array.
2178 ** Hence, make a complete copy of the opcode, rather than using
2179 ** a pointer to the opcode. */
2180 x = *sqlite3VdbeGetOp(v, addrConflictCk);
2181 if( x.opcode!=OP_IdxRowid ){
2182 int p2; /* New P2 value for copied conflict check opcode */
drhb9f2e5f2020-01-28 15:02:23 +00002183 const char *zP4;
drhd901b162019-10-26 12:27:55 +00002184 if( sqlite3OpcodeProperty[x.opcode]&OPFLG_JUMP ){
2185 p2 = lblRecheckOk;
2186 }else{
2187 p2 = x.p2;
2188 }
drhb9f2e5f2020-01-28 15:02:23 +00002189 zP4 = x.p4type==P4_INT32 ? SQLITE_INT_TO_PTR(x.p4.i) : x.p4.z;
2190 sqlite3VdbeAddOp4(v, x.opcode, x.p1, p2, x.p3, zP4, x.p4type);
drhd901b162019-10-26 12:27:55 +00002191 sqlite3VdbeChangeP5(v, x.p5);
2192 VdbeCoverageIf(v, p2!=x.p2);
drha407ecc2019-10-26 00:04:21 +00002193 }
2194 nConflictCk--;
drhd901b162019-10-26 12:27:55 +00002195 addrConflictCk++;
drha407ecc2019-10-26 00:04:21 +00002196 }
2197 /* If the retest fails, issue an abort */
drh2da8d6f2019-10-16 14:56:03 +00002198 sqlite3UniqueConstraint(pParse, OE_Abort, pIdx);
drha407ecc2019-10-26 00:04:21 +00002199
2200 sqlite3VdbeJumpHere(v, addrBypass); /* Terminate the recheck bypass */
drh2da8d6f2019-10-16 14:56:03 +00002201 }
drh0ca3e242002-01-29 23:07:02 +00002202 seenReplace = 1;
drh9cfcf5d2002-01-29 18:41:24 +00002203 break;
2204 }
drh9cfcf5d2002-01-29 18:41:24 +00002205 }
drh7f5f3062018-04-17 20:06:24 +00002206 if( pUpIdx==pIdx ){
drh84304502018-08-14 15:12:52 +00002207 sqlite3VdbeGoto(v, upsertJump+1);
drh7f5f3062018-04-17 20:06:24 +00002208 sqlite3VdbeJumpHere(v, upsertBypass);
2209 }else{
2210 sqlite3VdbeResolveLabel(v, addrUniqueOk);
2211 }
drh392ee212013-11-08 16:54:56 +00002212 if( regR!=regIdx ) sqlite3ReleaseTempRange(pParse, regR, nPkField);
drh9cfcf5d2002-01-29 18:41:24 +00002213 }
drh84304502018-08-14 15:12:52 +00002214
2215 /* If the IPK constraint is a REPLACE, run it last */
2216 if( ipkTop ){
drh6214d932019-01-12 16:19:23 +00002217 sqlite3VdbeGoto(v, ipkTop);
drh84304502018-08-14 15:12:52 +00002218 VdbeComment((v, "Do IPK REPLACE"));
2219 sqlite3VdbeJumpHere(v, ipkBottom);
2220 }
2221
drha407ecc2019-10-26 00:04:21 +00002222 /* Recheck all uniqueness constraints after replace triggers have run */
2223 testcase( regTrigCnt!=0 && nReplaceTrig==0 );
drhd3c468b2019-10-26 16:38:49 +00002224 assert( regTrigCnt!=0 || nReplaceTrig==0 );
drha407ecc2019-10-26 00:04:21 +00002225 if( nReplaceTrig ){
2226 sqlite3VdbeAddOp2(v, OP_IfNot, regTrigCnt, lblRecheckOk);VdbeCoverage(v);
2227 if( !pPk ){
2228 if( isUpdate ){
2229 sqlite3VdbeAddOp3(v, OP_Eq, regNewData, addrRecheck, regOldData);
2230 sqlite3VdbeChangeP5(v, SQLITE_NOTNULL);
2231 VdbeCoverage(v);
2232 }
2233 sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, addrRecheck, regNewData);
2234 VdbeCoverage(v);
2235 sqlite3RowidConstraint(pParse, OE_Abort, pTab);
2236 }else{
2237 sqlite3VdbeGoto(v, addrRecheck);
2238 }
2239 sqlite3VdbeResolveLabel(v, lblRecheckOk);
2240 }
2241
drha7c3b932019-05-07 19:13:42 +00002242 /* Generate the table record */
2243 if( HasRowid(pTab) ){
2244 int regRec = aRegIdx[ix];
drh0b0b3a92019-10-17 18:35:57 +00002245 sqlite3VdbeAddOp3(v, OP_MakeRecord, regNewData+1, pTab->nNVCol, regRec);
drha7c3b932019-05-07 19:13:42 +00002246 sqlite3SetMakeRecordP5(v, pTab);
2247 if( !bAffinityDone ){
2248 sqlite3TableAffinity(v, pTab, 0);
2249 }
2250 }
2251
drh4308e342013-11-11 16:55:52 +00002252 *pbMayReplace = seenReplace;
drhce60aa42013-11-08 01:09:15 +00002253 VdbeModuleComment((v, "END: GenCnstCks(%d)", seenReplace));
drh9cfcf5d2002-01-29 18:41:24 +00002254}
drh0ca3e242002-01-29 23:07:02 +00002255
drhd447dce2017-01-25 20:55:11 +00002256#ifdef SQLITE_ENABLE_NULL_TRIM
drh0ca3e242002-01-29 23:07:02 +00002257/*
drh585ce192017-01-25 14:58:27 +00002258** Change the P5 operand on the last opcode (which should be an OP_MakeRecord)
2259** to be the number of columns in table pTab that must not be NULL-trimmed.
2260**
2261** Or if no columns of pTab may be NULL-trimmed, leave P5 at zero.
2262*/
2263void sqlite3SetMakeRecordP5(Vdbe *v, Table *pTab){
2264 u16 i;
2265
2266 /* Records with omitted columns are only allowed for schema format
2267 ** version 2 and later (SQLite version 3.1.4, 2005-02-20). */
2268 if( pTab->pSchema->file_format<2 ) return;
2269
drh7e4acf72017-02-14 20:00:16 +00002270 for(i=pTab->nCol-1; i>0; i--){
2271 if( pTab->aCol[i].pDflt!=0 ) break;
2272 if( pTab->aCol[i].colFlags & COLFLAG_PRIMKEY ) break;
2273 }
2274 sqlite3VdbeChangeP5(v, i+1);
drh585ce192017-01-25 14:58:27 +00002275}
drhd447dce2017-01-25 20:55:11 +00002276#endif
drh585ce192017-01-25 14:58:27 +00002277
drh0ca3e242002-01-29 23:07:02 +00002278/*
2279** This routine generates code to finish the INSERT or UPDATE operation
danielk19774adee202004-05-08 08:23:19 +00002280** that was started by a prior call to sqlite3GenerateConstraintChecks.
drh6934fc72013-10-31 15:37:49 +00002281** A consecutive range of registers starting at regNewData contains the
drh04adf412008-01-08 18:57:50 +00002282** rowid and the content to be inserted.
drh0ca3e242002-01-29 23:07:02 +00002283**
drhb419a922002-01-30 16:17:23 +00002284** The arguments to this routine should be the same as the first six
danielk19774adee202004-05-08 08:23:19 +00002285** arguments to sqlite3GenerateConstraintChecks.
drh0ca3e242002-01-29 23:07:02 +00002286*/
danielk19774adee202004-05-08 08:23:19 +00002287void sqlite3CompleteInsertion(
drh0ca3e242002-01-29 23:07:02 +00002288 Parse *pParse, /* The parser context */
2289 Table *pTab, /* the table into which we are inserting */
drh26198bb2013-10-31 11:15:09 +00002290 int iDataCur, /* Cursor of the canonical data source */
2291 int iIdxCur, /* First index cursor */
drh6934fc72013-10-31 15:37:49 +00002292 int regNewData, /* Range of content */
drhaa9b8962008-01-08 02:57:55 +00002293 int *aRegIdx, /* Register used by each index. 0 for unused indices */
danf91c1312017-01-10 20:04:38 +00002294 int update_flags, /* True for UPDATE, False for INSERT */
danielk1977de630352009-05-04 11:42:29 +00002295 int appendBias, /* True if this is likely to be an append */
2296 int useSeekResult /* True to set the USESEEKRESULT flag on OP_[Idx]Insert */
drh0ca3e242002-01-29 23:07:02 +00002297){
drh6934fc72013-10-31 15:37:49 +00002298 Vdbe *v; /* Prepared statements under construction */
2299 Index *pIdx; /* An index being inserted or updated */
2300 u8 pik_flags; /* flag values passed to the btree insert */
drh6934fc72013-10-31 15:37:49 +00002301 int i; /* Loop counter */
drh0ca3e242002-01-29 23:07:02 +00002302
danf91c1312017-01-10 20:04:38 +00002303 assert( update_flags==0
2304 || update_flags==OPFLAG_ISUPDATE
2305 || update_flags==(OPFLAG_ISUPDATE|OPFLAG_SAVEPOSITION)
2306 );
2307
drhf0b41742020-08-15 21:55:14 +00002308 v = pParse->pVdbe;
drh0ca3e242002-01-29 23:07:02 +00002309 assert( v!=0 );
drh417be792002-03-03 18:59:40 +00002310 assert( pTab->pSelect==0 ); /* This table is not a VIEW */
drhb2b9d3d2013-08-01 01:14:43 +00002311 for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
drhd35bdd62019-12-15 02:49:32 +00002312 /* All REPLACE indexes are at the end of the list */
2313 assert( pIdx->onError!=OE_Replace
2314 || pIdx->pNext==0
2315 || pIdx->pNext->onError==OE_Replace );
drhaa9b8962008-01-08 02:57:55 +00002316 if( aRegIdx[i]==0 ) continue;
drhb2b9d3d2013-08-01 01:14:43 +00002317 if( pIdx->pPartIdxWhere ){
2318 sqlite3VdbeAddOp2(v, OP_IsNull, aRegIdx[i], sqlite3VdbeCurrentAddr(v)+2);
drh688852a2014-02-17 22:40:43 +00002319 VdbeCoverage(v);
drhb2b9d3d2013-08-01 01:14:43 +00002320 }
dancb9a3642017-01-30 19:44:53 +00002321 pik_flags = (useSeekResult ? OPFLAG_USESEEKRESULT : 0);
drh48dd1d82014-05-27 18:18:58 +00002322 if( IsPrimaryKeyIndex(pIdx) && !HasRowid(pTab) ){
drh4308e342013-11-11 16:55:52 +00002323 assert( pParse->nested==0 );
drh6546af12013-11-04 15:23:25 +00002324 pik_flags |= OPFLAG_NCHANGE;
danf91c1312017-01-10 20:04:38 +00002325 pik_flags |= (update_flags & OPFLAG_SAVEPOSITION);
dancb9a3642017-01-30 19:44:53 +00002326#ifdef SQLITE_ENABLE_PREUPDATE_HOOK
2327 if( update_flags==0 ){
drh50ef6712019-02-22 23:29:56 +00002328 int r = sqlite3GetTempReg(pParse);
2329 sqlite3VdbeAddOp2(v, OP_Integer, 0, r);
2330 sqlite3VdbeAddOp4(v, OP_Insert,
2331 iIdxCur+i, aRegIdx[i], r, (char*)pTab, P4_TABLE
dancb9a3642017-01-30 19:44:53 +00002332 );
2333 sqlite3VdbeChangeP5(v, OPFLAG_ISNOOP);
drh50ef6712019-02-22 23:29:56 +00002334 sqlite3ReleaseTempReg(pParse, r);
dancb9a3642017-01-30 19:44:53 +00002335 }
2336#endif
danielk1977de630352009-05-04 11:42:29 +00002337 }
dancb9a3642017-01-30 19:44:53 +00002338 sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iIdxCur+i, aRegIdx[i],
2339 aRegIdx[i]+1,
2340 pIdx->uniqNotNull ? pIdx->nKeyCol: pIdx->nColumn);
drh9b34abe2016-01-16 15:12:35 +00002341 sqlite3VdbeChangeP5(v, pik_flags);
drh0ca3e242002-01-29 23:07:02 +00002342 }
drhec95c442013-10-23 01:57:32 +00002343 if( !HasRowid(pTab) ) return;
drh4794f732004-11-05 17:17:50 +00002344 if( pParse->nested ){
2345 pik_flags = 0;
2346 }else{
danielk197794eb6a12005-12-15 15:22:08 +00002347 pik_flags = OPFLAG_NCHANGE;
danf91c1312017-01-10 20:04:38 +00002348 pik_flags |= (update_flags?update_flags:OPFLAG_LASTROWID);
drh4794f732004-11-05 17:17:50 +00002349 }
drhe4d90812007-03-29 05:51:49 +00002350 if( appendBias ){
2351 pik_flags |= OPFLAG_APPEND;
2352 }
danielk1977de630352009-05-04 11:42:29 +00002353 if( useSeekResult ){
2354 pik_flags |= OPFLAG_USESEEKRESULT;
2355 }
drha7c3b932019-05-07 19:13:42 +00002356 sqlite3VdbeAddOp3(v, OP_Insert, iDataCur, aRegIdx[i], regNewData);
danielk197794eb6a12005-12-15 15:22:08 +00002357 if( !pParse->nested ){
drhf14b7fb2016-12-07 21:35:55 +00002358 sqlite3VdbeAppendP4(v, pTab, P4_TABLE);
danielk197794eb6a12005-12-15 15:22:08 +00002359 }
drhb7654112008-01-12 12:48:07 +00002360 sqlite3VdbeChangeP5(v, pik_flags);
drh0ca3e242002-01-29 23:07:02 +00002361}
drhcd446902004-02-24 01:05:31 +00002362
2363/*
drh26198bb2013-10-31 11:15:09 +00002364** Allocate cursors for the pTab table and all its indices and generate
2365** code to open and initialized those cursors.
drhaa9b8962008-01-08 02:57:55 +00002366**
drh26198bb2013-10-31 11:15:09 +00002367** The cursor for the object that contains the complete data (normally
2368** the table itself, but the PRIMARY KEY index in the case of a WITHOUT
2369** ROWID table) is returned in *piDataCur. The first index cursor is
2370** returned in *piIdxCur. The number of indices is returned.
2371**
2372** Use iBase as the first cursor (either the *piDataCur for rowid tables
2373** or the first index for WITHOUT ROWID tables) if it is non-negative.
2374** If iBase is negative, then allocate the next available cursor.
2375**
2376** For a rowid table, *piDataCur will be exactly one less than *piIdxCur.
2377** For a WITHOUT ROWID table, *piDataCur will be somewhere in the range
2378** of *piIdxCurs, depending on where the PRIMARY KEY index appears on the
2379** pTab->pIndex list.
drhb6b4b792014-08-21 14:10:23 +00002380**
2381** If pTab is a virtual table, then this routine is a no-op and the
2382** *piDataCur and *piIdxCur values are left uninitialized.
drhcd446902004-02-24 01:05:31 +00002383*/
drhaa9b8962008-01-08 02:57:55 +00002384int sqlite3OpenTableAndIndices(
drh290c1942004-08-21 17:54:45 +00002385 Parse *pParse, /* Parsing context */
2386 Table *pTab, /* Table to be opened */
drh26198bb2013-10-31 11:15:09 +00002387 int op, /* OP_OpenRead or OP_OpenWrite */
drhb89aeb62016-01-27 15:49:32 +00002388 u8 p5, /* P5 value for OP_Open* opcodes (except on WITHOUT ROWID) */
drh26198bb2013-10-31 11:15:09 +00002389 int iBase, /* Use this for the table cursor, if there is one */
drh6a534992013-11-16 20:13:39 +00002390 u8 *aToOpen, /* If not NULL: boolean for each table and index */
drh26198bb2013-10-31 11:15:09 +00002391 int *piDataCur, /* Write the database source cursor number here */
2392 int *piIdxCur /* Write the first index cursor number here */
drh290c1942004-08-21 17:54:45 +00002393){
drhcd446902004-02-24 01:05:31 +00002394 int i;
drh4cbdda92006-06-14 19:00:20 +00002395 int iDb;
drh6a534992013-11-16 20:13:39 +00002396 int iDataCur;
drhcd446902004-02-24 01:05:31 +00002397 Index *pIdx;
drh4cbdda92006-06-14 19:00:20 +00002398 Vdbe *v;
2399
drh26198bb2013-10-31 11:15:09 +00002400 assert( op==OP_OpenRead || op==OP_OpenWrite );
danfd261ec2015-10-22 20:54:33 +00002401 assert( op==OP_OpenWrite || p5==0 );
drh26198bb2013-10-31 11:15:09 +00002402 if( IsVirtual(pTab) ){
drhb6b4b792014-08-21 14:10:23 +00002403 /* This routine is a no-op for virtual tables. Leave the output
2404 ** variables *piDataCur and *piIdxCur uninitialized so that valgrind
2405 ** can detect if they are used by mistake in the caller. */
drh26198bb2013-10-31 11:15:09 +00002406 return 0;
2407 }
drh4cbdda92006-06-14 19:00:20 +00002408 iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema);
drhf0b41742020-08-15 21:55:14 +00002409 v = pParse->pVdbe;
drhcd446902004-02-24 01:05:31 +00002410 assert( v!=0 );
drh26198bb2013-10-31 11:15:09 +00002411 if( iBase<0 ) iBase = pParse->nTab;
drh6a534992013-11-16 20:13:39 +00002412 iDataCur = iBase++;
2413 if( piDataCur ) *piDataCur = iDataCur;
2414 if( HasRowid(pTab) && (aToOpen==0 || aToOpen[0]) ){
2415 sqlite3OpenTable(pParse, iDataCur, iDb, pTab, op);
drh6fbe41a2013-10-30 20:22:55 +00002416 }else{
drh26198bb2013-10-31 11:15:09 +00002417 sqlite3TableLock(pParse, iDb, pTab->tnum, op==OP_OpenWrite, pTab->zName);
drh6fbe41a2013-10-30 20:22:55 +00002418 }
drh6a534992013-11-16 20:13:39 +00002419 if( piIdxCur ) *piIdxCur = iBase;
drh26198bb2013-10-31 11:15:09 +00002420 for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
drh26198bb2013-10-31 11:15:09 +00002421 int iIdxCur = iBase++;
danielk1977da184232006-01-05 11:34:32 +00002422 assert( pIdx->pSchema==pTab->pSchema );
dan61441c32016-08-26 12:00:50 +00002423 if( IsPrimaryKeyIndex(pIdx) && !HasRowid(pTab) ){
2424 if( piDataCur ) *piDataCur = iIdxCur;
2425 p5 = 0;
2426 }
drh6a534992013-11-16 20:13:39 +00002427 if( aToOpen==0 || aToOpen[i+1] ){
2428 sqlite3VdbeAddOp3(v, op, iIdxCur, pIdx->tnum, iDb);
2429 sqlite3VdbeSetP4KeyInfo(pParse, pIdx);
drhb89aeb62016-01-27 15:49:32 +00002430 sqlite3VdbeChangeP5(v, p5);
dan61441c32016-08-26 12:00:50 +00002431 VdbeComment((v, "%s", pIdx->zName));
drhb89aeb62016-01-27 15:49:32 +00002432 }
drhcd446902004-02-24 01:05:31 +00002433 }
drh26198bb2013-10-31 11:15:09 +00002434 if( iBase>pParse->nTab ) pParse->nTab = iBase;
2435 return i;
drhcd446902004-02-24 01:05:31 +00002436}
drh9d9cf222007-02-13 15:01:11 +00002437
drh91c58e22007-03-27 12:04:04 +00002438
2439#ifdef SQLITE_TEST
2440/*
2441** The following global variable is incremented whenever the
2442** transfer optimization is used. This is used for testing
2443** purposes only - to make sure the transfer optimization really
peter.d.reid60ec9142014-09-06 16:39:46 +00002444** is happening when it is supposed to.
drh91c58e22007-03-27 12:04:04 +00002445*/
2446int sqlite3_xferopt_count;
2447#endif /* SQLITE_TEST */
2448
2449
drh9d9cf222007-02-13 15:01:11 +00002450#ifndef SQLITE_OMIT_XFER_OPT
2451/*
drh9d9cf222007-02-13 15:01:11 +00002452** Check to see if index pSrc is compatible as a source of data
2453** for index pDest in an insert transfer optimization. The rules
2454** for a compatible index:
2455**
2456** * The index is over the same set of columns
2457** * The same DESC and ASC markings occurs on all columns
2458** * The same onError processing (OE_Abort, OE_Ignore, etc)
2459** * The same collating sequence on each column
drhb2b9d3d2013-08-01 01:14:43 +00002460** * The index has the exact same WHERE clause
drh9d9cf222007-02-13 15:01:11 +00002461*/
2462static int xferCompatibleIndex(Index *pDest, Index *pSrc){
2463 int i;
2464 assert( pDest && pSrc );
2465 assert( pDest->pTable!=pSrc->pTable );
drh1e7c00e2019-11-07 14:51:24 +00002466 if( pDest->nKeyCol!=pSrc->nKeyCol || pDest->nColumn!=pSrc->nColumn ){
drh9d9cf222007-02-13 15:01:11 +00002467 return 0; /* Different number of columns */
2468 }
2469 if( pDest->onError!=pSrc->onError ){
2470 return 0; /* Different conflict resolution strategies */
2471 }
drhbbbdc832013-10-22 18:01:40 +00002472 for(i=0; i<pSrc->nKeyCol; i++){
drh9d9cf222007-02-13 15:01:11 +00002473 if( pSrc->aiColumn[i]!=pDest->aiColumn[i] ){
2474 return 0; /* Different columns indexed */
2475 }
drh4b92f982015-09-29 17:20:14 +00002476 if( pSrc->aiColumn[i]==XN_EXPR ){
drh1f9ca2c2015-08-25 16:57:52 +00002477 assert( pSrc->aColExpr!=0 && pDest->aColExpr!=0 );
dan5aa550c2017-06-24 18:10:29 +00002478 if( sqlite3ExprCompare(0, pSrc->aColExpr->a[i].pExpr,
drh1f9ca2c2015-08-25 16:57:52 +00002479 pDest->aColExpr->a[i].pExpr, -1)!=0 ){
2480 return 0; /* Different expressions in the index */
2481 }
2482 }
drh9d9cf222007-02-13 15:01:11 +00002483 if( pSrc->aSortOrder[i]!=pDest->aSortOrder[i] ){
2484 return 0; /* Different sort orders */
2485 }
drh0472af92015-12-30 15:18:16 +00002486 if( sqlite3_stricmp(pSrc->azColl[i],pDest->azColl[i])!=0 ){
drh60a713c2008-01-21 16:22:45 +00002487 return 0; /* Different collating sequences */
drh9d9cf222007-02-13 15:01:11 +00002488 }
2489 }
dan5aa550c2017-06-24 18:10:29 +00002490 if( sqlite3ExprCompare(0, pSrc->pPartIdxWhere, pDest->pPartIdxWhere, -1) ){
drhb2b9d3d2013-08-01 01:14:43 +00002491 return 0; /* Different WHERE clauses */
2492 }
drh9d9cf222007-02-13 15:01:11 +00002493
2494 /* If no test above fails then the indices must be compatible */
2495 return 1;
2496}
2497
2498/*
2499** Attempt the transfer optimization on INSERTs of the form
2500**
2501** INSERT INTO tab1 SELECT * FROM tab2;
2502**
drhccdf1ba2011-11-04 14:36:02 +00002503** The xfer optimization transfers raw records from tab2 over to tab1.
peter.d.reid60ec9142014-09-06 16:39:46 +00002504** Columns are not decoded and reassembled, which greatly improves
drhccdf1ba2011-11-04 14:36:02 +00002505** performance. Raw index records are transferred in the same way.
drh9d9cf222007-02-13 15:01:11 +00002506**
drhccdf1ba2011-11-04 14:36:02 +00002507** The xfer optimization is only attempted if tab1 and tab2 are compatible.
2508** There are lots of rules for determining compatibility - see comments
2509** embedded in the code for details.
drh9d9cf222007-02-13 15:01:11 +00002510**
drhccdf1ba2011-11-04 14:36:02 +00002511** This routine returns TRUE if the optimization is guaranteed to be used.
2512** Sometimes the xfer optimization will only work if the destination table
2513** is empty - a factor that can only be determined at run-time. In that
2514** case, this routine generates code for the xfer optimization but also
2515** does a test to see if the destination table is empty and jumps over the
2516** xfer optimization code if the test fails. In that case, this routine
2517** returns FALSE so that the caller will know to go ahead and generate
2518** an unoptimized transfer. This routine also returns FALSE if there
2519** is no chance that the xfer optimization can be applied.
drh9d9cf222007-02-13 15:01:11 +00002520**
drhccdf1ba2011-11-04 14:36:02 +00002521** This optimization is particularly useful at making VACUUM run faster.
drh9d9cf222007-02-13 15:01:11 +00002522*/
2523static int xferOptimization(
2524 Parse *pParse, /* Parser context */
2525 Table *pDest, /* The table we are inserting into */
2526 Select *pSelect, /* A SELECT statement to use as the data source */
2527 int onError, /* How to handle constraint errors */
2528 int iDbDest /* The database of pDest */
2529){
dane34162b2015-04-01 18:20:25 +00002530 sqlite3 *db = pParse->db;
drh9d9cf222007-02-13 15:01:11 +00002531 ExprList *pEList; /* The result set of the SELECT */
2532 Table *pSrc; /* The table in the FROM clause of SELECT */
2533 Index *pSrcIdx, *pDestIdx; /* Source and destination indices */
2534 struct SrcList_item *pItem; /* An element of pSelect->pSrc */
2535 int i; /* Loop counter */
2536 int iDbSrc; /* The database of pSrc */
2537 int iSrc, iDest; /* Cursors from source and destination */
2538 int addr1, addr2; /* Loop addresses */
drhda475b82013-11-04 21:44:54 +00002539 int emptyDestTest = 0; /* Address of test for empty pDest */
2540 int emptySrcTest = 0; /* Address of test for empty pSrc */
drh9d9cf222007-02-13 15:01:11 +00002541 Vdbe *v; /* The VDBE we are building */
drh6a288a32008-01-07 19:20:24 +00002542 int regAutoinc; /* Memory register used by AUTOINC */
drhf33c9fa2007-04-10 18:17:55 +00002543 int destHasUniqueIdx = 0; /* True if pDest has a UNIQUE index */
drhb7654112008-01-12 12:48:07 +00002544 int regData, regRowid; /* Registers holding data and rowid */
drh9d9cf222007-02-13 15:01:11 +00002545
2546 if( pSelect==0 ){
2547 return 0; /* Must be of the form INSERT INTO ... SELECT ... */
2548 }
danebbf08a2014-01-18 08:27:02 +00002549 if( pParse->pWith || pSelect->pWith ){
2550 /* Do not attempt to process this query if there are an WITH clauses
2551 ** attached to it. Proceeding may generate a false "no such table: xxx"
2552 ** error if pSelect reads from a CTE named "xxx". */
2553 return 0;
2554 }
danielk19772f886d12009-02-28 10:47:41 +00002555 if( sqlite3TriggerList(pParse, pDest) ){
drh9d9cf222007-02-13 15:01:11 +00002556 return 0; /* tab1 must not have triggers */
2557 }
2558#ifndef SQLITE_OMIT_VIRTUALTABLE
drh44266ec2017-02-16 14:48:08 +00002559 if( IsVirtual(pDest) ){
drh9d9cf222007-02-13 15:01:11 +00002560 return 0; /* tab1 must not be a virtual table */
2561 }
2562#endif
2563 if( onError==OE_Default ){
drhe7224a02011-11-04 00:23:53 +00002564 if( pDest->iPKey>=0 ) onError = pDest->keyConf;
2565 if( onError==OE_Default ) onError = OE_Abort;
drh9d9cf222007-02-13 15:01:11 +00002566 }
danielk19775ce240a2007-09-03 17:30:06 +00002567 assert(pSelect->pSrc); /* allocated even if there is no FROM clause */
drh9d9cf222007-02-13 15:01:11 +00002568 if( pSelect->pSrc->nSrc!=1 ){
2569 return 0; /* FROM clause must have exactly one term */
2570 }
2571 if( pSelect->pSrc->a[0].pSelect ){
2572 return 0; /* FROM clause cannot contain a subquery */
2573 }
2574 if( pSelect->pWhere ){
2575 return 0; /* SELECT may not have a WHERE clause */
2576 }
2577 if( pSelect->pOrderBy ){
2578 return 0; /* SELECT may not have an ORDER BY clause */
2579 }
drh8103b7d2007-02-24 13:23:51 +00002580 /* Do not need to test for a HAVING clause. If HAVING is present but
2581 ** there is no ORDER BY, we will get an error. */
drh9d9cf222007-02-13 15:01:11 +00002582 if( pSelect->pGroupBy ){
2583 return 0; /* SELECT may not have a GROUP BY clause */
2584 }
2585 if( pSelect->pLimit ){
2586 return 0; /* SELECT may not have a LIMIT clause */
2587 }
drh9d9cf222007-02-13 15:01:11 +00002588 if( pSelect->pPrior ){
2589 return 0; /* SELECT may not be a compound query */
2590 }
drh7d10d5a2008-08-20 16:35:10 +00002591 if( pSelect->selFlags & SF_Distinct ){
drh9d9cf222007-02-13 15:01:11 +00002592 return 0; /* SELECT may not be DISTINCT */
2593 }
2594 pEList = pSelect->pEList;
2595 assert( pEList!=0 );
2596 if( pEList->nExpr!=1 ){
2597 return 0; /* The result set must have exactly one column */
2598 }
2599 assert( pEList->a[0].pExpr );
drh1a1d3cd2015-11-19 16:33:31 +00002600 if( pEList->a[0].pExpr->op!=TK_ASTERISK ){
drh9d9cf222007-02-13 15:01:11 +00002601 return 0; /* The result set must be the special operator "*" */
2602 }
2603
2604 /* At this point we have established that the statement is of the
2605 ** correct syntactic form to participate in this optimization. Now
2606 ** we have to check the semantics.
2607 */
2608 pItem = pSelect->pSrc->a;
dan41fb5cd2012-10-04 19:33:00 +00002609 pSrc = sqlite3LocateTableItem(pParse, 0, pItem);
drh9d9cf222007-02-13 15:01:11 +00002610 if( pSrc==0 ){
2611 return 0; /* FROM clause does not contain a real table */
2612 }
drh21908b22019-01-17 20:19:35 +00002613 if( pSrc->tnum==pDest->tnum && pSrc->pSchema==pDest->pSchema ){
drh1e32bed2020-06-19 13:33:53 +00002614 testcase( pSrc!=pDest ); /* Possible due to bad sqlite_schema.rootpage */
drh9d9cf222007-02-13 15:01:11 +00002615 return 0; /* tab1 and tab2 may not be the same table */
2616 }
drh55548272013-10-23 16:03:07 +00002617 if( HasRowid(pDest)!=HasRowid(pSrc) ){
2618 return 0; /* source and destination must both be WITHOUT ROWID or not */
2619 }
drh9d9cf222007-02-13 15:01:11 +00002620#ifndef SQLITE_OMIT_VIRTUALTABLE
drh44266ec2017-02-16 14:48:08 +00002621 if( IsVirtual(pSrc) ){
drh9d9cf222007-02-13 15:01:11 +00002622 return 0; /* tab2 must not be a virtual table */
2623 }
2624#endif
2625 if( pSrc->pSelect ){
2626 return 0; /* tab2 may not be a view */
2627 }
2628 if( pDest->nCol!=pSrc->nCol ){
2629 return 0; /* Number of columns must be the same in tab1 and tab2 */
2630 }
2631 if( pDest->iPKey!=pSrc->iPKey ){
2632 return 0; /* Both tables must have the same INTEGER PRIMARY KEY */
2633 }
2634 for(i=0; i<pDest->nCol; i++){
dan9940e2a2014-04-26 14:07:57 +00002635 Column *pDestCol = &pDest->aCol[i];
2636 Column *pSrcCol = &pSrc->aCol[i];
danba68f8f2015-11-19 16:46:46 +00002637#ifdef SQLITE_ENABLE_HIDDEN_COLUMNS
drh8257aa82017-07-26 19:59:13 +00002638 if( (db->mDbFlags & DBFLAG_Vacuum)==0
danaaea3142015-11-19 18:09:05 +00002639 && (pDestCol->colFlags | pSrcCol->colFlags) & COLFLAG_HIDDEN
2640 ){
danba68f8f2015-11-19 16:46:46 +00002641 return 0; /* Neither table may have __hidden__ columns */
2642 }
2643#endif
drh6ab61d72019-10-23 15:47:33 +00002644#ifndef SQLITE_OMIT_GENERATED_COLUMNS
2645 /* Even if tables t1 and t2 have identical schemas, if they contain
2646 ** generated columns, then this statement is semantically incorrect:
2647 **
2648 ** INSERT INTO t2 SELECT * FROM t1;
2649 **
2650 ** The reason is that generated column values are returned by the
2651 ** the SELECT statement on the right but the INSERT statement on the
2652 ** left wants them to be omitted.
2653 **
2654 ** Nevertheless, this is a useful notational shorthand to tell SQLite
2655 ** to do a bulk transfer all of the content from t1 over to t2.
2656 **
2657 ** We could, in theory, disable this (except for internal use by the
2658 ** VACUUM command where it is actually needed). But why do that? It
2659 ** seems harmless enough, and provides a useful service.
2660 */
drhae3977a2019-10-17 16:16:34 +00002661 if( (pDestCol->colFlags & COLFLAG_GENERATED) !=
2662 (pSrcCol->colFlags & COLFLAG_GENERATED) ){
drh6ab61d72019-10-23 15:47:33 +00002663 return 0; /* Both columns have the same generated-column type */
drhae3977a2019-10-17 16:16:34 +00002664 }
drh6ab61d72019-10-23 15:47:33 +00002665 /* But the transfer is only allowed if both the source and destination
2666 ** tables have the exact same expressions for generated columns.
2667 ** This requirement could be relaxed for VIRTUAL columns, I suppose.
2668 */
2669 if( (pDestCol->colFlags & COLFLAG_GENERATED)!=0 ){
2670 if( sqlite3ExprCompare(0, pSrcCol->pDflt, pDestCol->pDflt, -1)!=0 ){
2671 testcase( pDestCol->colFlags & COLFLAG_VIRTUAL );
2672 testcase( pDestCol->colFlags & COLFLAG_STORED );
2673 return 0; /* Different generator expressions */
2674 }
2675 }
2676#endif
dan9940e2a2014-04-26 14:07:57 +00002677 if( pDestCol->affinity!=pSrcCol->affinity ){
drh9d9cf222007-02-13 15:01:11 +00002678 return 0; /* Affinity must be the same on all columns */
2679 }
drh0472af92015-12-30 15:18:16 +00002680 if( sqlite3_stricmp(pDestCol->zColl, pSrcCol->zColl)!=0 ){
drh9d9cf222007-02-13 15:01:11 +00002681 return 0; /* Collating sequence must be the same on all columns */
2682 }
dan9940e2a2014-04-26 14:07:57 +00002683 if( pDestCol->notNull && !pSrcCol->notNull ){
drh9d9cf222007-02-13 15:01:11 +00002684 return 0; /* tab2 must be NOT NULL if tab1 is */
2685 }
drh453e0262014-04-26 17:52:08 +00002686 /* Default values for second and subsequent columns need to match. */
drhae3977a2019-10-17 16:16:34 +00002687 if( (pDestCol->colFlags & COLFLAG_GENERATED)==0 && i>0 ){
drh94fa9c42016-02-27 21:16:04 +00002688 assert( pDestCol->pDflt==0 || pDestCol->pDflt->op==TK_SPAN );
2689 assert( pSrcCol->pDflt==0 || pSrcCol->pDflt->op==TK_SPAN );
2690 if( (pDestCol->pDflt==0)!=(pSrcCol->pDflt==0)
2691 || (pDestCol->pDflt && strcmp(pDestCol->pDflt->u.zToken,
2692 pSrcCol->pDflt->u.zToken)!=0)
2693 ){
2694 return 0; /* Default values must be the same for all columns */
2695 }
dan9940e2a2014-04-26 14:07:57 +00002696 }
drh9d9cf222007-02-13 15:01:11 +00002697 }
2698 for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){
drh5f1d1d92014-07-31 22:59:04 +00002699 if( IsUniqueIndex(pDestIdx) ){
drhf33c9fa2007-04-10 18:17:55 +00002700 destHasUniqueIdx = 1;
2701 }
drh9d9cf222007-02-13 15:01:11 +00002702 for(pSrcIdx=pSrc->pIndex; pSrcIdx; pSrcIdx=pSrcIdx->pNext){
2703 if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break;
2704 }
2705 if( pSrcIdx==0 ){
2706 return 0; /* pDestIdx has no corresponding index in pSrc */
2707 }
drhe3bd2322019-04-05 16:38:12 +00002708 if( pSrcIdx->tnum==pDestIdx->tnum && pSrc->pSchema==pDest->pSchema
2709 && sqlite3FaultSim(411)==SQLITE_OK ){
2710 /* The sqlite3FaultSim() call allows this corruption test to be
2711 ** bypassed during testing, in order to exercise other corruption tests
2712 ** further downstream. */
drh86223e82019-04-05 15:44:55 +00002713 return 0; /* Corrupt schema - two indexes on the same btree */
2714 }
drh9d9cf222007-02-13 15:01:11 +00002715 }
drh7fc2f412007-03-29 00:08:24 +00002716#ifndef SQLITE_OMIT_CHECK
drh619a1302013-08-01 13:04:46 +00002717 if( pDest->pCheck && sqlite3ExprListCompare(pSrc->pCheck,pDest->pCheck,-1) ){
drh8103b7d2007-02-24 13:23:51 +00002718 return 0; /* Tables have different CHECK constraints. Ticket #2252 */
2719 }
drh7fc2f412007-03-29 00:08:24 +00002720#endif
drh713de342011-04-24 22:56:07 +00002721#ifndef SQLITE_OMIT_FOREIGN_KEY
2722 /* Disallow the transfer optimization if the destination table constains
2723 ** any foreign key constraints. This is more restrictive than necessary.
2724 ** But the main beneficiary of the transfer optimization is the VACUUM
2725 ** command, and the VACUUM command disables foreign key constraints. So
2726 ** the extra complication to make this rule less restrictive is probably
2727 ** not worth the effort. Ticket [6284df89debdfa61db8073e062908af0c9b6118e]
2728 */
dane34162b2015-04-01 18:20:25 +00002729 if( (db->flags & SQLITE_ForeignKeys)!=0 && pDest->pFKey!=0 ){
drh713de342011-04-24 22:56:07 +00002730 return 0;
2731 }
2732#endif
dane34162b2015-04-01 18:20:25 +00002733 if( (db->flags & SQLITE_CountRows)!=0 ){
drhccdf1ba2011-11-04 14:36:02 +00002734 return 0; /* xfer opt does not play well with PRAGMA count_changes */
dan16961242011-09-30 12:01:01 +00002735 }
drh9d9cf222007-02-13 15:01:11 +00002736
drhccdf1ba2011-11-04 14:36:02 +00002737 /* If we get this far, it means that the xfer optimization is at
2738 ** least a possibility, though it might only work if the destination
2739 ** table (tab1) is initially empty.
drh9d9cf222007-02-13 15:01:11 +00002740 */
drhdd735212007-02-24 13:53:05 +00002741#ifdef SQLITE_TEST
2742 sqlite3_xferopt_count++;
2743#endif
dane34162b2015-04-01 18:20:25 +00002744 iDbSrc = sqlite3SchemaToIndex(db, pSrc->pSchema);
drh9d9cf222007-02-13 15:01:11 +00002745 v = sqlite3GetVdbe(pParse);
drhf53e9b52007-08-29 13:45:58 +00002746 sqlite3CodeVerifySchema(pParse, iDbSrc);
drh9d9cf222007-02-13 15:01:11 +00002747 iSrc = pParse->nTab++;
2748 iDest = pParse->nTab++;
drh6a288a32008-01-07 19:20:24 +00002749 regAutoinc = autoIncBegin(pParse, iDbDest, pDest);
drhb7654112008-01-12 12:48:07 +00002750 regData = sqlite3GetTempReg(pParse);
dan7aae7352020-12-10 18:06:24 +00002751 sqlite3VdbeAddOp2(v, OP_Null, 0, regData);
drhb7654112008-01-12 12:48:07 +00002752 regRowid = sqlite3GetTempReg(pParse);
drh9d9cf222007-02-13 15:01:11 +00002753 sqlite3OpenTable(pParse, iDest, iDbDest, pDest, OP_OpenWrite);
dan427ebba2013-11-05 16:39:31 +00002754 assert( HasRowid(pDest) || destHasUniqueIdx );
drh8257aa82017-07-26 19:59:13 +00002755 if( (db->mDbFlags & DBFLAG_Vacuum)==0 && (
dane34162b2015-04-01 18:20:25 +00002756 (pDest->iPKey<0 && pDest->pIndex!=0) /* (1) */
drh9d9cf222007-02-13 15:01:11 +00002757 || destHasUniqueIdx /* (2) */
2758 || (onError!=OE_Abort && onError!=OE_Rollback) /* (3) */
dane34162b2015-04-01 18:20:25 +00002759 )){
drh9d9cf222007-02-13 15:01:11 +00002760 /* In some circumstances, we are able to run the xfer optimization
dane34162b2015-04-01 18:20:25 +00002761 ** only if the destination table is initially empty. Unless the
drh8257aa82017-07-26 19:59:13 +00002762 ** DBFLAG_Vacuum flag is set, this block generates code to make
2763 ** that determination. If DBFLAG_Vacuum is set, then the destination
dane34162b2015-04-01 18:20:25 +00002764 ** table is always empty.
2765 **
2766 ** Conditions under which the destination must be empty:
drhbd36ba62007-03-31 13:00:26 +00002767 **
drh9d9cf222007-02-13 15:01:11 +00002768 ** (1) There is no INTEGER PRIMARY KEY but there are indices.
2769 ** (If the destination is not initially empty, the rowid fields
2770 ** of index entries might need to change.)
2771 **
2772 ** (2) The destination has a unique index. (The xfer optimization
drhbd36ba62007-03-31 13:00:26 +00002773 ** is unable to test uniqueness.)
2774 **
2775 ** (3) onError is something other than OE_Abort and OE_Rollback.
drhf33c9fa2007-04-10 18:17:55 +00002776 */
drh688852a2014-02-17 22:40:43 +00002777 addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iDest, 0); VdbeCoverage(v);
drh2991ba02015-09-02 18:19:00 +00002778 emptyDestTest = sqlite3VdbeAddOp0(v, OP_Goto);
drh66a51672008-01-03 00:01:23 +00002779 sqlite3VdbeJumpHere(v, addr1);
drh9d9cf222007-02-13 15:01:11 +00002780 }
drh55548272013-10-23 16:03:07 +00002781 if( HasRowid(pSrc) ){
drhc9b9dea2016-11-15 02:46:39 +00002782 u8 insFlags;
drh55548272013-10-23 16:03:07 +00002783 sqlite3OpenTable(pParse, iSrc, iDbSrc, pSrc, OP_OpenRead);
drh688852a2014-02-17 22:40:43 +00002784 emptySrcTest = sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0); VdbeCoverage(v);
drh55548272013-10-23 16:03:07 +00002785 if( pDest->iPKey>=0 ){
2786 addr1 = sqlite3VdbeAddOp2(v, OP_Rowid, iSrc, regRowid);
dan036e0672020-12-08 20:19:07 +00002787 if( (db->mDbFlags & DBFLAG_Vacuum)==0 ){
2788 sqlite3VdbeVerifyAbortable(v, onError);
2789 addr2 = sqlite3VdbeAddOp3(v, OP_NotExists, iDest, 0, regRowid);
2790 VdbeCoverage(v);
2791 sqlite3RowidConstraint(pParse, onError, pDest);
2792 sqlite3VdbeJumpHere(v, addr2);
2793 }
drh55548272013-10-23 16:03:07 +00002794 autoIncStep(pParse, regAutoinc, regRowid);
drh4e61e882019-04-04 14:00:23 +00002795 }else if( pDest->pIndex==0 && !(db->mDbFlags & DBFLAG_VacuumInto) ){
drh55548272013-10-23 16:03:07 +00002796 addr1 = sqlite3VdbeAddOp2(v, OP_NewRowid, iDest, regRowid);
2797 }else{
2798 addr1 = sqlite3VdbeAddOp2(v, OP_Rowid, iSrc, regRowid);
2799 assert( (pDest->tabFlags & TF_Autoincrement)==0 );
2800 }
dan7aae7352020-12-10 18:06:24 +00002801
drh8257aa82017-07-26 19:59:13 +00002802 if( db->mDbFlags & DBFLAG_Vacuum ){
drh86b40df2017-08-01 19:53:43 +00002803 sqlite3VdbeAddOp1(v, OP_SeekEnd, iDest);
dan7aae7352020-12-10 18:06:24 +00002804 insFlags = OPFLAG_APPEND|OPFLAG_USESEEKRESULT|OPFLAG_PREFORMAT;
drhc9b9dea2016-11-15 02:46:39 +00002805 }else{
dan7aae7352020-12-10 18:06:24 +00002806 insFlags = OPFLAG_NCHANGE|OPFLAG_LASTROWID|OPFLAG_APPEND|OPFLAG_PREFORMAT;
drhc9b9dea2016-11-15 02:46:39 +00002807 }
dan7aae7352020-12-10 18:06:24 +00002808#ifdef SQLITE_ENABLE_PREUPDATE_HOOK
2809 if( db->xPreUpdateCallback ){
2810 sqlite3VdbeAddOp3(v, OP_RowData, iSrc, regData, 1);
2811 insFlags &= ~OPFLAG_PREFORMAT;
2812 }else
2813#endif
2814 {
2815 sqlite3VdbeAddOp3(v, OP_RowCell, iDest, iSrc, regRowid);
2816 }
2817 sqlite3VdbeAddOp4(v, OP_Insert, iDest, regData, regRowid,
2818 (char*)pDest, P4_TABLE);
2819 sqlite3VdbeChangeP5(v, insFlags);
2820
drh688852a2014-02-17 22:40:43 +00002821 sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1); VdbeCoverage(v);
drh55548272013-10-23 16:03:07 +00002822 sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0);
2823 sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);
drh95bad4c2007-03-28 18:04:10 +00002824 }else{
drhda475b82013-11-04 21:44:54 +00002825 sqlite3TableLock(pParse, iDbDest, pDest->tnum, 1, pDest->zName);
2826 sqlite3TableLock(pParse, iDbSrc, pSrc->tnum, 0, pSrc->zName);
drh95bad4c2007-03-28 18:04:10 +00002827 }
drh9d9cf222007-02-13 15:01:11 +00002828 for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){
drh41b9ca22015-07-28 18:53:37 +00002829 u8 idxInsFlags = 0;
drh1b7ecbb2009-05-03 01:00:59 +00002830 for(pSrcIdx=pSrc->pIndex; ALWAYS(pSrcIdx); pSrcIdx=pSrcIdx->pNext){
drh9d9cf222007-02-13 15:01:11 +00002831 if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break;
2832 }
2833 assert( pSrcIdx );
drh2ec2fb22013-11-06 19:59:23 +00002834 sqlite3VdbeAddOp3(v, OP_OpenRead, iSrc, pSrcIdx->tnum, iDbSrc);
2835 sqlite3VdbeSetP4KeyInfo(pParse, pSrcIdx);
drhd4e70eb2008-01-02 00:34:36 +00002836 VdbeComment((v, "%s", pSrcIdx->zName));
drh2ec2fb22013-11-06 19:59:23 +00002837 sqlite3VdbeAddOp3(v, OP_OpenWrite, iDest, pDestIdx->tnum, iDbDest);
2838 sqlite3VdbeSetP4KeyInfo(pParse, pDestIdx);
dan59885722013-10-04 18:17:18 +00002839 sqlite3VdbeChangeP5(v, OPFLAG_BULKCSR);
danielk1977207872a2008-01-03 07:54:23 +00002840 VdbeComment((v, "%s", pDestIdx->zName));
drh688852a2014-02-17 22:40:43 +00002841 addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0); VdbeCoverage(v);
drh8257aa82017-07-26 19:59:13 +00002842 if( db->mDbFlags & DBFLAG_Vacuum ){
dane34162b2015-04-01 18:20:25 +00002843 /* This INSERT command is part of a VACUUM operation, which guarantees
2844 ** that the destination table is empty. If all indexed columns use
2845 ** collation sequence BINARY, then it can also be assumed that the
2846 ** index will be populated by inserting keys in strictly sorted
2847 ** order. In this case, instead of seeking within the b-tree as part
drh86b40df2017-08-01 19:53:43 +00002848 ** of every OP_IdxInsert opcode, an OP_SeekEnd is added before the
dane34162b2015-04-01 18:20:25 +00002849 ** OP_IdxInsert to seek to the point within the b-tree where each key
2850 ** should be inserted. This is faster.
2851 **
2852 ** If any of the indexed columns use a collation sequence other than
2853 ** BINARY, this optimization is disabled. This is because the user
2854 ** might change the definition of a collation sequence and then run
2855 ** a VACUUM command. In that case keys may not be written in strictly
2856 ** sorted order. */
dane34162b2015-04-01 18:20:25 +00002857 for(i=0; i<pSrcIdx->nColumn; i++){
drhf19aa5f2015-12-30 16:51:20 +00002858 const char *zColl = pSrcIdx->azColl[i];
drhf19aa5f2015-12-30 16:51:20 +00002859 if( sqlite3_stricmp(sqlite3StrBINARY, zColl) ) break;
dane34162b2015-04-01 18:20:25 +00002860 }
2861 if( i==pSrcIdx->nColumn ){
dan7aae7352020-12-10 18:06:24 +00002862 idxInsFlags = OPFLAG_USESEEKRESULT|OPFLAG_PREFORMAT;
drh86b40df2017-08-01 19:53:43 +00002863 sqlite3VdbeAddOp1(v, OP_SeekEnd, iDest);
dan7aae7352020-12-10 18:06:24 +00002864 sqlite3VdbeAddOp3(v, OP_RowCell, iDest, iSrc, regData);
dane34162b2015-04-01 18:20:25 +00002865 }
drhc84ad312020-02-06 20:46:08 +00002866 }else if( !HasRowid(pSrc) && pDestIdx->idxType==SQLITE_IDXTYPE_PRIMARYKEY ){
drh41b9ca22015-07-28 18:53:37 +00002867 idxInsFlags |= OPFLAG_NCHANGE;
2868 }
dan7aae7352020-12-10 18:06:24 +00002869 if( idxInsFlags!=(OPFLAG_USESEEKRESULT|OPFLAG_PREFORMAT) ){
dancd1b2d02020-12-09 20:33:51 +00002870 sqlite3VdbeAddOp3(v, OP_RowData, iSrc, regData, 1);
dancd1b2d02020-12-09 20:33:51 +00002871 }
dan7aae7352020-12-10 18:06:24 +00002872 sqlite3VdbeAddOp2(v, OP_IdxInsert, iDest, regData);
2873 sqlite3VdbeChangeP5(v, idxInsFlags|OPFLAG_APPEND);
drh688852a2014-02-17 22:40:43 +00002874 sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1+1); VdbeCoverage(v);
drh9d9cf222007-02-13 15:01:11 +00002875 sqlite3VdbeJumpHere(v, addr1);
drh55548272013-10-23 16:03:07 +00002876 sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0);
2877 sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);
drh9d9cf222007-02-13 15:01:11 +00002878 }
drhaceb31b2014-02-08 01:40:27 +00002879 if( emptySrcTest ) sqlite3VdbeJumpHere(v, emptySrcTest);
drhb7654112008-01-12 12:48:07 +00002880 sqlite3ReleaseTempReg(pParse, regRowid);
2881 sqlite3ReleaseTempReg(pParse, regData);
drh9d9cf222007-02-13 15:01:11 +00002882 if( emptyDestTest ){
drh1dd518c2016-10-03 02:59:33 +00002883 sqlite3AutoincrementEnd(pParse);
drh66a51672008-01-03 00:01:23 +00002884 sqlite3VdbeAddOp2(v, OP_Halt, SQLITE_OK, 0);
drh9d9cf222007-02-13 15:01:11 +00002885 sqlite3VdbeJumpHere(v, emptyDestTest);
drh66a51672008-01-03 00:01:23 +00002886 sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);
drh9d9cf222007-02-13 15:01:11 +00002887 return 0;
2888 }else{
2889 return 1;
2890 }
2891}
2892#endif /* SQLITE_OMIT_XFER_OPT */