blob: 04d3d580f5d1c246651b7eaf34eedc23c7558a69 [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) );
drh2ec2fb22013-11-06 19:59:23 +000035 v = sqlite3GetVdbe(pParse);
drhbbb5e4e2009-04-30 00:11:09 +000036 assert( opcode==OP_OpenWrite || opcode==OP_OpenRead );
drh2ec2fb22013-11-06 19:59:23 +000037 sqlite3TableLock(pParse, iDb, pTab->tnum,
38 (opcode==OP_OpenWrite)?1:0, pTab->zName);
drhec95c442013-10-23 01:57:32 +000039 if( HasRowid(pTab) ){
drh261c02d2013-10-25 14:46:15 +000040 sqlite3VdbeAddOp4Int(v, opcode, iCur, pTab->tnum, iDb, pTab->nCol);
drhdd9930e2013-10-23 23:37:02 +000041 VdbeComment((v, "%s", pTab->zName));
drh26198bb2013-10-31 11:15:09 +000042 }else{
drhdd9930e2013-10-23 23:37:02 +000043 Index *pPk = sqlite3PrimaryKeyIndex(pTab);
44 assert( pPk!=0 );
drhafe028a2015-05-22 13:09:50 +000045 assert( pPk->tnum==pTab->tnum );
drh2ec2fb22013-11-06 19:59:23 +000046 sqlite3VdbeAddOp3(v, opcode, iCur, pPk->tnum, iDb);
47 sqlite3VdbeSetP4KeyInfo(pParse, pPk);
drhec95c442013-10-23 01:57:32 +000048 VdbeComment((v, "%s", pTab->zName));
49 }
drhbbb5e4e2009-04-30 00:11:09 +000050}
51
52/*
dan69f8bb92009-08-13 19:21:16 +000053** Return a pointer to the column affinity string associated with index
54** pIdx. A column affinity string has one character for each column in
55** the table, according to the affinity of the column:
danielk19773d1bfea2004-05-14 11:00:53 +000056**
57** Character Column affinity
58** ------------------------------
drh05883a32015-06-02 15:32:08 +000059** 'A' BLOB
drh4583c372014-09-19 20:13:25 +000060** 'B' TEXT
61** 'C' NUMERIC
62** 'D' INTEGER
63** 'F' REAL
drh2d401ab2008-01-10 23:50:11 +000064**
drh4583c372014-09-19 20:13:25 +000065** An extra 'D' is appended to the end of the string to cover the
drh2d401ab2008-01-10 23:50:11 +000066** rowid that appears as the last column in every index.
dan69f8bb92009-08-13 19:21:16 +000067**
68** Memory for the buffer containing the column index affinity string
69** is managed along with the rest of the Index structure. It will be
70** released when sqlite3DeleteIndex() is called.
danielk19773d1bfea2004-05-14 11:00:53 +000071*/
drhe9107692015-08-25 19:20:04 +000072const char *sqlite3IndexAffinityStr(sqlite3 *db, Index *pIdx){
danielk1977a37cdde2004-05-16 11:15:36 +000073 if( !pIdx->zColAff ){
danielk1977e014a832004-05-17 10:48:57 +000074 /* The first time a column affinity string for a particular index is
danielk1977a37cdde2004-05-16 11:15:36 +000075 ** required, it is allocated and populated here. It is then stored as
danielk1977e014a832004-05-17 10:48:57 +000076 ** a member of the Index structure for subsequent use.
danielk1977a37cdde2004-05-16 11:15:36 +000077 **
78 ** The column affinity string will eventually be deleted by
danielk1977e014a832004-05-17 10:48:57 +000079 ** sqliteDeleteIndex() when the Index structure itself is cleaned
danielk1977a37cdde2004-05-16 11:15:36 +000080 ** up.
81 */
82 int n;
83 Table *pTab = pIdx->pTable;
drhad124322013-10-23 13:30:58 +000084 pIdx->zColAff = (char *)sqlite3DbMallocRaw(0, pIdx->nColumn+1);
danielk1977a37cdde2004-05-16 11:15:36 +000085 if( !pIdx->zColAff ){
drh4a642b62016-02-05 01:55:27 +000086 sqlite3OomFault(db);
dan69f8bb92009-08-13 19:21:16 +000087 return 0;
danielk1977a37cdde2004-05-16 11:15:36 +000088 }
89 for(n=0; n<pIdx->nColumn; n++){
drhad124322013-10-23 13:30:58 +000090 i16 x = pIdx->aiColumn[n];
drh81506b82019-08-05 19:32:06 +000091 char aff;
drh1f9ca2c2015-08-25 16:57:52 +000092 if( x>=0 ){
drh81506b82019-08-05 19:32:06 +000093 aff = pTab->aCol[x].affinity;
drh4b92f982015-09-29 17:20:14 +000094 }else if( x==XN_ROWID ){
drh81506b82019-08-05 19:32:06 +000095 aff = SQLITE_AFF_INTEGER;
drh1f9ca2c2015-08-25 16:57:52 +000096 }else{
drh4b92f982015-09-29 17:20:14 +000097 assert( x==XN_EXPR );
drh1f9ca2c2015-08-25 16:57:52 +000098 assert( pIdx->aColExpr!=0 );
drh6860e6f2015-08-27 18:24:02 +000099 aff = sqlite3ExprAffinity(pIdx->aColExpr->a[n].pExpr);
drh1f9ca2c2015-08-25 16:57:52 +0000100 }
drh96fb16e2019-08-06 14:37:24 +0000101 if( aff<SQLITE_AFF_BLOB ) aff = SQLITE_AFF_BLOB;
drh81506b82019-08-05 19:32:06 +0000102 pIdx->zColAff[n] = aff;
danielk1977a37cdde2004-05-16 11:15:36 +0000103 }
drh2d401ab2008-01-10 23:50:11 +0000104 pIdx->zColAff[n] = 0;
danielk1977a37cdde2004-05-16 11:15:36 +0000105 }
danielk19773d1bfea2004-05-14 11:00:53 +0000106
dan69f8bb92009-08-13 19:21:16 +0000107 return pIdx->zColAff;
danielk1977a37cdde2004-05-16 11:15:36 +0000108}
109
110/*
drh57bf4a82014-02-17 14:59:22 +0000111** Compute the affinity string for table pTab, if it has not already been
drh05883a32015-06-02 15:32:08 +0000112** computed. As an optimization, omit trailing SQLITE_AFF_BLOB affinities.
drh57bf4a82014-02-17 14:59:22 +0000113**
drh05883a32015-06-02 15:32:08 +0000114** If the affinity exists (if it is no entirely SQLITE_AFF_BLOB values) and
drh57bf4a82014-02-17 14:59:22 +0000115** if iReg>0 then code an OP_Affinity opcode that will set the affinities
116** for register iReg and following. Or if affinities exists and iReg==0,
117** then just set the P4 operand of the previous opcode (which should be
118** an OP_MakeRecord) to the affinity string.
119**
drhb6e8fd12014-03-06 01:56:33 +0000120** A column affinity string has one character per column:
danielk1977a37cdde2004-05-16 11:15:36 +0000121**
122** Character Column affinity
123** ------------------------------
drh05883a32015-06-02 15:32:08 +0000124** 'A' BLOB
drh4583c372014-09-19 20:13:25 +0000125** 'B' TEXT
126** 'C' NUMERIC
127** 'D' INTEGER
128** 'E' REAL
danielk1977a37cdde2004-05-16 11:15:36 +0000129*/
drh57bf4a82014-02-17 14:59:22 +0000130void sqlite3TableAffinity(Vdbe *v, Table *pTab, int iReg){
131 int i;
132 char *zColAff = pTab->zColAff;
133 if( zColAff==0 ){
drhabb6fca2007-08-16 12:24:01 +0000134 sqlite3 *db = sqlite3VdbeDb(v);
drhb9755982010-07-24 16:34:37 +0000135 zColAff = (char *)sqlite3DbMallocRaw(0, pTab->nCol+1);
danielk19773d1bfea2004-05-14 11:00:53 +0000136 if( !zColAff ){
drh4a642b62016-02-05 01:55:27 +0000137 sqlite3OomFault(db);
danielk1977a37cdde2004-05-16 11:15:36 +0000138 return;
danielk19773d1bfea2004-05-14 11:00:53 +0000139 }
140
141 for(i=0; i<pTab->nCol; i++){
drh96fb16e2019-08-06 14:37:24 +0000142 assert( pTab->aCol[i].affinity!=0 );
danielk1977a37cdde2004-05-16 11:15:36 +0000143 zColAff[i] = pTab->aCol[i].affinity;
danielk19773d1bfea2004-05-14 11:00:53 +0000144 }
drh57bf4a82014-02-17 14:59:22 +0000145 do{
146 zColAff[i--] = 0;
drh96fb16e2019-08-06 14:37:24 +0000147 }while( i>=0 && zColAff[i]<=SQLITE_AFF_BLOB );
danielk19773d1bfea2004-05-14 11:00:53 +0000148 pTab->zColAff = zColAff;
149 }
drh7301e772018-10-31 20:52:00 +0000150 assert( zColAff!=0 );
151 i = sqlite3Strlen30NN(zColAff);
drh57bf4a82014-02-17 14:59:22 +0000152 if( i ){
153 if( iReg ){
154 sqlite3VdbeAddOp4(v, OP_Affinity, iReg, i, 0, zColAff, i);
155 }else{
156 sqlite3VdbeChangeP4(v, -1, zColAff, i);
157 }
158 }
danielk19773d1bfea2004-05-14 11:00:53 +0000159}
160
danielk19774d887782005-02-08 08:42:27 +0000161/*
drh48d11782007-11-23 15:02:19 +0000162** Return non-zero if the table pTab in database iDb or any of its indices
drhb6e8fd12014-03-06 01:56:33 +0000163** have been opened at any point in the VDBE program. This is used to see if
drh48d11782007-11-23 15:02:19 +0000164** a statement of the form "INSERT INTO <iDb, pTab> SELECT ..." can
drhb6e8fd12014-03-06 01:56:33 +0000165** run without using a temporary table for the results of the SELECT.
danielk19774d887782005-02-08 08:42:27 +0000166*/
drh05a86c52014-02-16 01:55:49 +0000167static int readsTable(Parse *p, int iDb, Table *pTab){
danielk1977595a5232009-07-24 17:58:53 +0000168 Vdbe *v = sqlite3GetVdbe(p);
danielk19774d887782005-02-08 08:42:27 +0000169 int i;
drh48d11782007-11-23 15:02:19 +0000170 int iEnd = sqlite3VdbeCurrentAddr(v);
danielk1977595a5232009-07-24 17:58:53 +0000171#ifndef SQLITE_OMIT_VIRTUALTABLE
172 VTable *pVTab = IsVirtual(pTab) ? sqlite3GetVTable(p->db, pTab) : 0;
173#endif
174
drh05a86c52014-02-16 01:55:49 +0000175 for(i=1; i<iEnd; i++){
drh48d11782007-11-23 15:02:19 +0000176 VdbeOp *pOp = sqlite3VdbeGetOp(v, i);
drhef0bea92007-12-14 16:11:09 +0000177 assert( pOp!=0 );
danielk1977207872a2008-01-03 07:54:23 +0000178 if( pOp->opcode==OP_OpenRead && pOp->p3==iDb ){
179 Index *pIndex;
drh48d11782007-11-23 15:02:19 +0000180 int tnum = pOp->p2;
danielk1977207872a2008-01-03 07:54:23 +0000181 if( tnum==pTab->tnum ){
182 return 1;
183 }
184 for(pIndex=pTab->pIndex; pIndex; pIndex=pIndex->pNext){
185 if( tnum==pIndex->tnum ){
drh48d11782007-11-23 15:02:19 +0000186 return 1;
187 }
drh48d11782007-11-23 15:02:19 +0000188 }
189 }
drh543165e2007-11-27 14:46:41 +0000190#ifndef SQLITE_OMIT_VIRTUALTABLE
danielk1977595a5232009-07-24 17:58:53 +0000191 if( pOp->opcode==OP_VOpen && pOp->p4.pVtab==pVTab ){
danielk19772dca4ac2008-01-03 11:50:29 +0000192 assert( pOp->p4.pVtab!=0 );
drh66a51672008-01-03 00:01:23 +0000193 assert( pOp->p4type==P4_VTAB );
drh48d11782007-11-23 15:02:19 +0000194 return 1;
danielk19774d887782005-02-08 08:42:27 +0000195 }
drh543165e2007-11-27 14:46:41 +0000196#endif
danielk19774d887782005-02-08 08:42:27 +0000197 }
198 return 0;
199}
danielk19773d1bfea2004-05-14 11:00:53 +0000200
drh9d9cf222007-02-13 15:01:11 +0000201#ifndef SQLITE_OMIT_AUTOINCREMENT
202/*
drh0b9f50d2009-06-23 20:28:53 +0000203** Locate or create an AutoincInfo structure associated with table pTab
204** which is in database iDb. Return the register number for the register
drh9ef5e772016-08-19 14:20:56 +0000205** that holds the maximum rowid. Return zero if pTab is not an AUTOINCREMENT
206** table. (Also return zero when doing a VACUUM since we do not want to
207** update the AUTOINCREMENT counters during a VACUUM.)
drh9d9cf222007-02-13 15:01:11 +0000208**
drh0b9f50d2009-06-23 20:28:53 +0000209** There is at most one AutoincInfo structure per table even if the
210** same table is autoincremented multiple times due to inserts within
211** triggers. A new AutoincInfo structure is created if this is the
212** first use of table pTab. On 2nd and subsequent uses, the original
213** AutoincInfo structure is used.
drh9d9cf222007-02-13 15:01:11 +0000214**
drhc8abbc12018-03-16 18:46:30 +0000215** Four consecutive registers are allocated:
drh0b9f50d2009-06-23 20:28:53 +0000216**
drhc8abbc12018-03-16 18:46:30 +0000217** (1) The name of the pTab table.
218** (2) The maximum ROWID of pTab.
219** (3) The rowid in sqlite_sequence of pTab
220** (4) The original value of the max ROWID in pTab, or NULL if none
drh0b9f50d2009-06-23 20:28:53 +0000221**
222** The 2nd register is the one that is returned. That is all the
223** insert routine needs to know about.
drh9d9cf222007-02-13 15:01:11 +0000224*/
225static int autoIncBegin(
226 Parse *pParse, /* Parsing context */
227 int iDb, /* Index of the database holding pTab */
228 Table *pTab /* The table we are writing to */
229){
drh6a288a32008-01-07 19:20:24 +0000230 int memId = 0; /* Register holding maximum rowid */
drh186ebd42018-05-23 16:50:21 +0000231 assert( pParse->db->aDb[iDb].pSchema!=0 );
drh9ef5e772016-08-19 14:20:56 +0000232 if( (pTab->tabFlags & TF_Autoincrement)!=0
drh8257aa82017-07-26 19:59:13 +0000233 && (pParse->db->mDbFlags & DBFLAG_Vacuum)==0
drh9ef5e772016-08-19 14:20:56 +0000234 ){
dan65a7cd12009-09-01 12:16:01 +0000235 Parse *pToplevel = sqlite3ParseToplevel(pParse);
drh0b9f50d2009-06-23 20:28:53 +0000236 AutoincInfo *pInfo;
drh186ebd42018-05-23 16:50:21 +0000237 Table *pSeqTab = pParse->db->aDb[iDb].pSchema->pSeqTab;
238
239 /* Verify that the sqlite_sequence table exists and is an ordinary
240 ** rowid table with exactly two columns.
241 ** Ticket d8dc2b3a58cd5dc2918a1d4acb 2018-05-23 */
242 if( pSeqTab==0
243 || !HasRowid(pSeqTab)
244 || IsVirtual(pSeqTab)
245 || pSeqTab->nCol!=2
246 ){
247 pParse->nErr++;
248 pParse->rc = SQLITE_CORRUPT_SEQUENCE;
249 return 0;
250 }
drh0b9f50d2009-06-23 20:28:53 +0000251
dan65a7cd12009-09-01 12:16:01 +0000252 pInfo = pToplevel->pAinc;
drh0b9f50d2009-06-23 20:28:53 +0000253 while( pInfo && pInfo->pTab!=pTab ){ pInfo = pInfo->pNext; }
254 if( pInfo==0 ){
drh575fad62016-02-05 13:38:36 +0000255 pInfo = sqlite3DbMallocRawNN(pParse->db, sizeof(*pInfo));
drh0b9f50d2009-06-23 20:28:53 +0000256 if( pInfo==0 ) return 0;
dan65a7cd12009-09-01 12:16:01 +0000257 pInfo->pNext = pToplevel->pAinc;
258 pToplevel->pAinc = pInfo;
drh0b9f50d2009-06-23 20:28:53 +0000259 pInfo->pTab = pTab;
260 pInfo->iDb = iDb;
dan65a7cd12009-09-01 12:16:01 +0000261 pToplevel->nMem++; /* Register to hold name of table */
262 pInfo->regCtr = ++pToplevel->nMem; /* Max rowid register */
drhc8abbc12018-03-16 18:46:30 +0000263 pToplevel->nMem +=2; /* Rowid in sqlite_sequence + orig max val */
drh0b9f50d2009-06-23 20:28:53 +0000264 }
265 memId = pInfo->regCtr;
drh9d9cf222007-02-13 15:01:11 +0000266 }
267 return memId;
268}
269
270/*
drh0b9f50d2009-06-23 20:28:53 +0000271** This routine generates code that will initialize all of the
272** register used by the autoincrement tracker.
273*/
274void sqlite3AutoincrementBegin(Parse *pParse){
275 AutoincInfo *p; /* Information about an AUTOINCREMENT */
276 sqlite3 *db = pParse->db; /* The database connection */
277 Db *pDb; /* Database only autoinc table */
278 int memId; /* Register holding max rowid */
drh0b9f50d2009-06-23 20:28:53 +0000279 Vdbe *v = pParse->pVdbe; /* VDBE under construction */
280
drh345ba7d2009-09-08 13:40:16 +0000281 /* This routine is never called during trigger-generation. It is
282 ** only called from the top-level */
283 assert( pParse->pTriggerTab==0 );
drhc149f182015-09-29 13:25:15 +0000284 assert( sqlite3IsToplevel(pParse) );
dan76d462e2009-08-30 11:42:51 +0000285
drh0b9f50d2009-06-23 20:28:53 +0000286 assert( v ); /* We failed long ago if this is not so */
287 for(p = pParse->pAinc; p; p = p->pNext){
drh1b325542016-02-03 01:55:44 +0000288 static const int iLn = VDBE_OFFSET_LINENO(2);
289 static const VdbeOpList autoInc[] = {
290 /* 0 */ {OP_Null, 0, 0, 0},
drhc8abbc12018-03-16 18:46:30 +0000291 /* 1 */ {OP_Rewind, 0, 10, 0},
drh1b325542016-02-03 01:55:44 +0000292 /* 2 */ {OP_Column, 0, 0, 0},
drhc8abbc12018-03-16 18:46:30 +0000293 /* 3 */ {OP_Ne, 0, 9, 0},
drh1b325542016-02-03 01:55:44 +0000294 /* 4 */ {OP_Rowid, 0, 0, 0},
295 /* 5 */ {OP_Column, 0, 1, 0},
drhc8abbc12018-03-16 18:46:30 +0000296 /* 6 */ {OP_AddImm, 0, 0, 0},
297 /* 7 */ {OP_Copy, 0, 0, 0},
298 /* 8 */ {OP_Goto, 0, 11, 0},
299 /* 9 */ {OP_Next, 0, 2, 0},
300 /* 10 */ {OP_Integer, 0, 0, 0},
301 /* 11 */ {OP_Close, 0, 0, 0}
drh1b325542016-02-03 01:55:44 +0000302 };
303 VdbeOp *aOp;
drh0b9f50d2009-06-23 20:28:53 +0000304 pDb = &db->aDb[p->iDb];
305 memId = p->regCtr;
drh21206082011-04-04 18:22:02 +0000306 assert( sqlite3SchemaMutexHeld(db, 0, pDb->pSchema) );
drh0b9f50d2009-06-23 20:28:53 +0000307 sqlite3OpenTable(pParse, 0, p->iDb, pDb->pSchema->pSeqTab, OP_OpenRead);
drh076e85f2015-09-03 13:46:12 +0000308 sqlite3VdbeLoadString(v, memId-1, p->pTab->zName);
drh1b325542016-02-03 01:55:44 +0000309 aOp = sqlite3VdbeAddOpList(v, ArraySize(autoInc), autoInc, iLn);
310 if( aOp==0 ) break;
311 aOp[0].p2 = memId;
drhc8abbc12018-03-16 18:46:30 +0000312 aOp[0].p3 = memId+2;
drh1b325542016-02-03 01:55:44 +0000313 aOp[2].p3 = memId;
314 aOp[3].p1 = memId-1;
315 aOp[3].p3 = memId;
316 aOp[3].p5 = SQLITE_JUMPIFNULL;
317 aOp[4].p2 = memId+1;
318 aOp[5].p3 = memId;
drhc8abbc12018-03-16 18:46:30 +0000319 aOp[6].p1 = memId;
320 aOp[7].p2 = memId+2;
321 aOp[7].p1 = memId;
322 aOp[10].p2 = memId;
drh04ab5862018-12-01 21:13:41 +0000323 if( pParse->nTab==0 ) pParse->nTab = 1;
drh0b9f50d2009-06-23 20:28:53 +0000324 }
325}
326
327/*
drh9d9cf222007-02-13 15:01:11 +0000328** Update the maximum rowid for an autoincrement calculation.
329**
drh1b325542016-02-03 01:55:44 +0000330** This routine should be called when the regRowid register holds a
drh9d9cf222007-02-13 15:01:11 +0000331** new rowid that is about to be inserted. If that new rowid is
332** larger than the maximum rowid in the memId memory cell, then the
drh1b325542016-02-03 01:55:44 +0000333** memory cell is updated.
drh9d9cf222007-02-13 15:01:11 +0000334*/
drh6a288a32008-01-07 19:20:24 +0000335static void autoIncStep(Parse *pParse, int memId, int regRowid){
drh9d9cf222007-02-13 15:01:11 +0000336 if( memId>0 ){
drh6a288a32008-01-07 19:20:24 +0000337 sqlite3VdbeAddOp2(pParse->pVdbe, OP_MemMax, memId, regRowid);
drh9d9cf222007-02-13 15:01:11 +0000338 }
339}
340
341/*
drh0b9f50d2009-06-23 20:28:53 +0000342** This routine generates the code needed to write autoincrement
343** maximum rowid values back into the sqlite_sequence register.
344** Every statement that might do an INSERT into an autoincrement
345** table (either directly or through triggers) needs to call this
346** routine just before the "exit" code.
drh9d9cf222007-02-13 15:01:11 +0000347*/
drh1b325542016-02-03 01:55:44 +0000348static SQLITE_NOINLINE void autoIncrementEnd(Parse *pParse){
drh0b9f50d2009-06-23 20:28:53 +0000349 AutoincInfo *p;
350 Vdbe *v = pParse->pVdbe;
351 sqlite3 *db = pParse->db;
drh6a288a32008-01-07 19:20:24 +0000352
drh0b9f50d2009-06-23 20:28:53 +0000353 assert( v );
354 for(p = pParse->pAinc; p; p = p->pNext){
drh1b325542016-02-03 01:55:44 +0000355 static const int iLn = VDBE_OFFSET_LINENO(2);
356 static const VdbeOpList autoIncEnd[] = {
357 /* 0 */ {OP_NotNull, 0, 2, 0},
358 /* 1 */ {OP_NewRowid, 0, 0, 0},
359 /* 2 */ {OP_MakeRecord, 0, 2, 0},
360 /* 3 */ {OP_Insert, 0, 0, 0},
361 /* 4 */ {OP_Close, 0, 0, 0}
362 };
363 VdbeOp *aOp;
drh0b9f50d2009-06-23 20:28:53 +0000364 Db *pDb = &db->aDb[p->iDb];
drh0b9f50d2009-06-23 20:28:53 +0000365 int iRec;
366 int memId = p->regCtr;
367
368 iRec = sqlite3GetTempReg(pParse);
drh21206082011-04-04 18:22:02 +0000369 assert( sqlite3SchemaMutexHeld(db, 0, pDb->pSchema) );
drhc8abbc12018-03-16 18:46:30 +0000370 sqlite3VdbeAddOp3(v, OP_Le, memId+2, sqlite3VdbeCurrentAddr(v)+7, memId);
371 VdbeCoverage(v);
drh0b9f50d2009-06-23 20:28:53 +0000372 sqlite3OpenTable(pParse, 0, p->iDb, pDb->pSchema->pSeqTab, OP_OpenWrite);
drh1b325542016-02-03 01:55:44 +0000373 aOp = sqlite3VdbeAddOpList(v, ArraySize(autoIncEnd), autoIncEnd, iLn);
374 if( aOp==0 ) break;
375 aOp[0].p1 = memId+1;
376 aOp[1].p2 = memId+1;
377 aOp[2].p1 = memId-1;
378 aOp[2].p3 = iRec;
379 aOp[3].p2 = iRec;
380 aOp[3].p3 = memId+1;
381 aOp[3].p5 = OPFLAG_APPEND;
drh0b9f50d2009-06-23 20:28:53 +0000382 sqlite3ReleaseTempReg(pParse, iRec);
drh9d9cf222007-02-13 15:01:11 +0000383 }
384}
drh1b325542016-02-03 01:55:44 +0000385void sqlite3AutoincrementEnd(Parse *pParse){
386 if( pParse->pAinc ) autoIncrementEnd(pParse);
387}
drh9d9cf222007-02-13 15:01:11 +0000388#else
389/*
390** If SQLITE_OMIT_AUTOINCREMENT is defined, then the three routines
391** above are all no-ops
392*/
393# define autoIncBegin(A,B,C) (0)
danielk1977287fb612008-01-04 19:10:28 +0000394# define autoIncStep(A,B,C)
drh9d9cf222007-02-13 15:01:11 +0000395#endif /* SQLITE_OMIT_AUTOINCREMENT */
396
397
398/* Forward declaration */
399static int xferOptimization(
400 Parse *pParse, /* Parser context */
401 Table *pDest, /* The table we are inserting into */
402 Select *pSelect, /* A SELECT statement to use as the data source */
403 int onError, /* How to handle constraint errors */
404 int iDbDest /* The database of pDest */
405);
406
danielk19773d1bfea2004-05-14 11:00:53 +0000407/*
drhd82b5022013-10-26 00:58:34 +0000408** This routine is called to handle SQL of the following forms:
drhcce7d172000-05-31 15:34:51 +0000409**
drha21f78b2015-04-19 18:32:43 +0000410** insert into TABLE (IDLIST) values(EXPRLIST),(EXPRLIST),...
drh1ccde152000-06-17 13:12:39 +0000411** insert into TABLE (IDLIST) select
drha21f78b2015-04-19 18:32:43 +0000412** insert into TABLE (IDLIST) default values
drhcce7d172000-05-31 15:34:51 +0000413**
drh1ccde152000-06-17 13:12:39 +0000414** The IDLIST following the table name is always optional. If omitted,
drha21f78b2015-04-19 18:32:43 +0000415** then a list of all (non-hidden) columns for the table is substituted.
416** The IDLIST appears in the pColumn parameter. pColumn is NULL if IDLIST
417** is omitted.
drh1ccde152000-06-17 13:12:39 +0000418**
drha21f78b2015-04-19 18:32:43 +0000419** For the pSelect parameter holds the values to be inserted for the
420** first two forms shown above. A VALUES clause is really just short-hand
421** for a SELECT statement that omits the FROM clause and everything else
422** that follows. If the pSelect parameter is NULL, that means that the
423** DEFAULT VALUES form of the INSERT statement is intended.
drh142e30d2002-08-28 03:00:58 +0000424**
drh9d9cf222007-02-13 15:01:11 +0000425** The code generated follows one of four templates. For a simple
drha21f78b2015-04-19 18:32:43 +0000426** insert with data coming from a single-row VALUES clause, the code executes
drhe00ee6e2008-06-20 15:24:01 +0000427** once straight down through. Pseudo-code follows (we call this
428** the "1st template"):
drh142e30d2002-08-28 03:00:58 +0000429**
430** open write cursor to <table> and its indices
drhec95c442013-10-23 01:57:32 +0000431** put VALUES clause expressions into registers
drh142e30d2002-08-28 03:00:58 +0000432** write the resulting record into <table>
433** cleanup
434**
drh9d9cf222007-02-13 15:01:11 +0000435** The three remaining templates assume the statement is of the form
drh142e30d2002-08-28 03:00:58 +0000436**
437** INSERT INTO <table> SELECT ...
438**
drh9d9cf222007-02-13 15:01:11 +0000439** If the SELECT clause is of the restricted form "SELECT * FROM <table2>" -
440** in other words if the SELECT pulls all columns from a single table
441** and there is no WHERE or LIMIT or GROUP BY or ORDER BY clauses, and
442** if <table2> and <table1> are distinct tables but have identical
443** schemas, including all the same indices, then a special optimization
444** is invoked that copies raw records from <table2> over to <table1>.
445** See the xferOptimization() function for the implementation of this
drhe00ee6e2008-06-20 15:24:01 +0000446** template. This is the 2nd template.
drh9d9cf222007-02-13 15:01:11 +0000447**
448** open a write cursor to <table>
449** open read cursor on <table2>
450** transfer all records in <table2> over to <table>
451** close cursors
452** foreach index on <table>
453** open a write cursor on the <table> index
454** open a read cursor on the corresponding <table2> index
455** transfer all records from the read to the write cursors
456** close cursors
457** end foreach
458**
drhe00ee6e2008-06-20 15:24:01 +0000459** The 3rd template is for when the second template does not apply
drh9d9cf222007-02-13 15:01:11 +0000460** and the SELECT clause does not read from <table> at any time.
461** The generated code follows this template:
drh142e30d2002-08-28 03:00:58 +0000462**
drhe00ee6e2008-06-20 15:24:01 +0000463** X <- A
drh142e30d2002-08-28 03:00:58 +0000464** goto B
465** A: setup for the SELECT
drh9d9cf222007-02-13 15:01:11 +0000466** loop over the rows in the SELECT
drhe00ee6e2008-06-20 15:24:01 +0000467** load values into registers R..R+n
468** yield X
drh142e30d2002-08-28 03:00:58 +0000469** end loop
470** cleanup after the SELECT
drh81cf13e2014-02-07 18:27:53 +0000471** end-coroutine X
drhe00ee6e2008-06-20 15:24:01 +0000472** B: open write cursor to <table> and its indices
drh81cf13e2014-02-07 18:27:53 +0000473** C: yield X, at EOF goto D
drhe00ee6e2008-06-20 15:24:01 +0000474** insert the select result into <table> from R..R+n
475** goto C
drh142e30d2002-08-28 03:00:58 +0000476** D: cleanup
477**
drhe00ee6e2008-06-20 15:24:01 +0000478** The 4th template is used if the insert statement takes its
drh142e30d2002-08-28 03:00:58 +0000479** values from a SELECT but the data is being inserted into a table
480** that is also read as part of the SELECT. In the third form,
peter.d.reid60ec9142014-09-06 16:39:46 +0000481** we have to use an intermediate table to store the results of
drh142e30d2002-08-28 03:00:58 +0000482** the select. The template is like this:
483**
drhe00ee6e2008-06-20 15:24:01 +0000484** X <- A
drh142e30d2002-08-28 03:00:58 +0000485** goto B
486** A: setup for the SELECT
487** loop over the tables in the SELECT
drhe00ee6e2008-06-20 15:24:01 +0000488** load value into register R..R+n
489** yield X
drh142e30d2002-08-28 03:00:58 +0000490** end loop
491** cleanup after the SELECT
drh81cf13e2014-02-07 18:27:53 +0000492** end co-routine R
drhe00ee6e2008-06-20 15:24:01 +0000493** B: open temp table
drh81cf13e2014-02-07 18:27:53 +0000494** L: yield X, at EOF goto M
drhe00ee6e2008-06-20 15:24:01 +0000495** insert row from R..R+n into temp table
496** goto L
497** M: open write cursor to <table> and its indices
498** rewind temp table
499** C: loop over rows of intermediate table
drh142e30d2002-08-28 03:00:58 +0000500** transfer values form intermediate table into <table>
drhe00ee6e2008-06-20 15:24:01 +0000501** end loop
502** D: cleanup
drhcce7d172000-05-31 15:34:51 +0000503*/
danielk19774adee202004-05-08 08:23:19 +0000504void sqlite3Insert(
drhcce7d172000-05-31 15:34:51 +0000505 Parse *pParse, /* Parser context */
drh113088e2003-03-20 01:16:58 +0000506 SrcList *pTabList, /* Name of table into which we are inserting */
drh5974a302000-06-07 14:42:26 +0000507 Select *pSelect, /* A SELECT statement to use as the data source */
drh9cfcf5d2002-01-29 18:41:24 +0000508 IdList *pColumn, /* Column names corresponding to IDLIST. */
drh2c2e8442018-04-07 15:04:05 +0000509 int onError, /* How to handle constraint errors */
drh46d2e5c2018-04-12 13:15:43 +0000510 Upsert *pUpsert /* ON CONFLICT clauses for upsert, or NULL */
drhcce7d172000-05-31 15:34:51 +0000511){
drh6a288a32008-01-07 19:20:24 +0000512 sqlite3 *db; /* The main database structure */
513 Table *pTab; /* The table to insert into. aka TABLE */
drh60ffc802016-11-21 21:33:46 +0000514 int i, j; /* Loop counters */
drh5974a302000-06-07 14:42:26 +0000515 Vdbe *v; /* Generate code into this virtual machine */
516 Index *pIdx; /* For looping over indices of the table */
drh967e8b72000-06-21 13:59:10 +0000517 int nColumn; /* Number of columns in the data */
drh6a288a32008-01-07 19:20:24 +0000518 int nHidden = 0; /* Number of hidden columns if TABLE is virtual */
drh26198bb2013-10-31 11:15:09 +0000519 int iDataCur = 0; /* VDBE cursor that is the main data repository */
520 int iIdxCur = 0; /* First index cursor */
drhd82b5022013-10-26 00:58:34 +0000521 int ipkColumn = -1; /* Column that is the INTEGER PRIMARY KEY */
drh0ca3e242002-01-29 23:07:02 +0000522 int endOfLoop; /* Label for the end of the insertion loop */
danielk1977cfe9a692004-06-16 12:00:29 +0000523 int srcTab = 0; /* Data comes from this temporary cursor if >=0 */
drhe00ee6e2008-06-20 15:24:01 +0000524 int addrInsTop = 0; /* Jump to label "D" */
525 int addrCont = 0; /* Top of insert loop. Label "C" in templates 3 and 4 */
drh2eb95372008-06-06 15:04:36 +0000526 SelectDest dest; /* Destination for SELECT on rhs of INSERT */
drh6a288a32008-01-07 19:20:24 +0000527 int iDb; /* Index of database holding TABLE */
drh05a86c52014-02-16 01:55:49 +0000528 u8 useTempTable = 0; /* Store SELECT results in intermediate table */
529 u8 appendFlag = 0; /* True if the insert is likely to be an append */
530 u8 withoutRowid; /* 0 for normal table. 1 for WITHOUT ROWID table */
drha21f78b2015-04-19 18:32:43 +0000531 u8 bIdListInOrder; /* True if IDLIST is in table order */
drh75593d92014-01-10 20:46:55 +0000532 ExprList *pList = 0; /* List of VALUES() to be inserted */
drhcce7d172000-05-31 15:34:51 +0000533
drh6a288a32008-01-07 19:20:24 +0000534 /* Register allocations */
drh1bd10f82008-12-10 21:19:56 +0000535 int regFromSelect = 0;/* Base register for data coming from SELECT */
drh6a288a32008-01-07 19:20:24 +0000536 int regAutoinc = 0; /* Register holding the AUTOINCREMENT counter */
537 int regRowCount = 0; /* Memory cell used for the row counter */
538 int regIns; /* Block of regs holding rowid+data being inserted */
539 int regRowid; /* registers holding insert rowid */
540 int regData; /* register holding first column to insert */
drhaa9b8962008-01-08 02:57:55 +0000541 int *aRegIdx = 0; /* One register allocated to each index */
drh6a288a32008-01-07 19:20:24 +0000542
drh798da522004-11-04 04:42:28 +0000543#ifndef SQLITE_OMIT_TRIGGER
544 int isView; /* True if attempting to insert into a view */
danielk19772f886d12009-02-28 10:47:41 +0000545 Trigger *pTrigger; /* List of triggers on pTab, if required */
546 int tmask; /* Mask of trigger times */
drh798da522004-11-04 04:42:28 +0000547#endif
danielk1977c3f9bad2002-05-15 08:30:12 +0000548
drh17435752007-08-16 04:30:38 +0000549 db = pParse->db;
550 if( pParse->nErr || db->mallocFailed ){
drh6f7adc82006-01-11 21:41:20 +0000551 goto insert_cleanup;
552 }
drh4c883482017-06-03 20:09:37 +0000553 dest.iSDParm = 0; /* Suppress a harmless compiler warning */
drhdaffd0e2001-04-11 14:28:42 +0000554
drh75593d92014-01-10 20:46:55 +0000555 /* If the Select object is really just a simple VALUES() list with a
drha21f78b2015-04-19 18:32:43 +0000556 ** single row (the common case) then keep that one row of values
557 ** and discard the other (unused) parts of the pSelect object
drh75593d92014-01-10 20:46:55 +0000558 */
559 if( pSelect && (pSelect->selFlags & SF_Values)!=0 && pSelect->pPrior==0 ){
560 pList = pSelect->pEList;
561 pSelect->pEList = 0;
562 sqlite3SelectDelete(db, pSelect);
563 pSelect = 0;
564 }
565
drh1ccde152000-06-17 13:12:39 +0000566 /* Locate the table into which we will be inserting new information.
567 */
drh113088e2003-03-20 01:16:58 +0000568 assert( pTabList->nSrc==1 );
danielk19774adee202004-05-08 08:23:19 +0000569 pTab = sqlite3SrcListLookup(pParse, pTabList);
danielk1977c3f9bad2002-05-15 08:30:12 +0000570 if( pTab==0 ){
danielk1977c3f9bad2002-05-15 08:30:12 +0000571 goto insert_cleanup;
572 }
danielk1977da184232006-01-05 11:34:32 +0000573 iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
574 assert( iDb<db->nDb );
drha0daa752016-09-16 11:53:10 +0000575 if( sqlite3AuthCheck(pParse, SQLITE_INSERT, pTab->zName, 0,
576 db->aDb[iDb].zDbSName) ){
drh1962bda2003-01-12 19:33:52 +0000577 goto insert_cleanup;
578 }
drhec95c442013-10-23 01:57:32 +0000579 withoutRowid = !HasRowid(pTab);
danielk1977c3f9bad2002-05-15 08:30:12 +0000580
drhb7f91642004-10-31 02:22:47 +0000581 /* Figure out if we have any triggers and if the table being
582 ** inserted into is a view
583 */
584#ifndef SQLITE_OMIT_TRIGGER
danielk19772f886d12009-02-28 10:47:41 +0000585 pTrigger = sqlite3TriggersExist(pParse, pTab, TK_INSERT, 0, &tmask);
drhb7f91642004-10-31 02:22:47 +0000586 isView = pTab->pSelect!=0;
587#else
danielk19772f886d12009-02-28 10:47:41 +0000588# define pTrigger 0
589# define tmask 0
drhb7f91642004-10-31 02:22:47 +0000590# define isView 0
591#endif
592#ifdef SQLITE_OMIT_VIEW
593# undef isView
594# define isView 0
595#endif
danielk19772f886d12009-02-28 10:47:41 +0000596 assert( (pTrigger && tmask) || (pTrigger==0 && tmask==0) );
drhb7f91642004-10-31 02:22:47 +0000597
drhf573c992002-07-31 00:32:50 +0000598 /* If pTab is really a view, make sure it has been initialized.
drhd82b5022013-10-26 00:58:34 +0000599 ** ViewGetColumnNames() is a no-op if pTab is not a view.
drhf573c992002-07-31 00:32:50 +0000600 */
danielk1977b3d24bf2006-06-19 03:05:10 +0000601 if( sqlite3ViewGetColumnNames(pParse, pTab) ){
drh5cf590c2003-04-24 01:45:04 +0000602 goto insert_cleanup;
drhf573c992002-07-31 00:32:50 +0000603 }
604
drhd82b5022013-10-26 00:58:34 +0000605 /* Cannot insert into a read-only table.
danielk1977595a5232009-07-24 17:58:53 +0000606 */
607 if( sqlite3IsReadOnly(pParse, pTab, tmask) ){
608 goto insert_cleanup;
609 }
610
drh1ccde152000-06-17 13:12:39 +0000611 /* Allocate a VDBE
612 */
danielk19774adee202004-05-08 08:23:19 +0000613 v = sqlite3GetVdbe(pParse);
drh5974a302000-06-07 14:42:26 +0000614 if( v==0 ) goto insert_cleanup;
drh4794f732004-11-05 17:17:50 +0000615 if( pParse->nested==0 ) sqlite3VdbeCountChanges(v);
danielk19772f886d12009-02-28 10:47:41 +0000616 sqlite3BeginWriteOperation(pParse, pSelect || pTrigger, iDb);
drh1ccde152000-06-17 13:12:39 +0000617
drh9d9cf222007-02-13 15:01:11 +0000618#ifndef SQLITE_OMIT_XFER_OPT
619 /* If the statement is of the form
620 **
621 ** INSERT INTO <table1> SELECT * FROM <table2>;
622 **
623 ** Then special optimizations can be applied that make the transfer
624 ** very fast and which reduce fragmentation of indices.
drhe00ee6e2008-06-20 15:24:01 +0000625 **
626 ** This is the 2nd template.
drh9d9cf222007-02-13 15:01:11 +0000627 */
628 if( pColumn==0 && xferOptimization(pParse, pTab, pSelect, onError, iDb) ){
danielk19772f886d12009-02-28 10:47:41 +0000629 assert( !pTrigger );
drh9d9cf222007-02-13 15:01:11 +0000630 assert( pList==0 );
drh0b9f50d2009-06-23 20:28:53 +0000631 goto insert_end;
drh9d9cf222007-02-13 15:01:11 +0000632 }
633#endif /* SQLITE_OMIT_XFER_OPT */
634
drh2958a4e2004-11-12 03:56:15 +0000635 /* If this is an AUTOINCREMENT table, look up the sequence number in the
drh6a288a32008-01-07 19:20:24 +0000636 ** sqlite_sequence table and store it in memory cell regAutoinc.
drh2958a4e2004-11-12 03:56:15 +0000637 */
drh6a288a32008-01-07 19:20:24 +0000638 regAutoinc = autoIncBegin(pParse, iDb, pTab);
drh2958a4e2004-11-12 03:56:15 +0000639
drh05a86c52014-02-16 01:55:49 +0000640 /* Allocate registers for holding the rowid of the new row,
peter.d.reid60ec9142014-09-06 16:39:46 +0000641 ** the content of the new row, and the assembled row record.
drh1ccde152000-06-17 13:12:39 +0000642 */
drh05a86c52014-02-16 01:55:49 +0000643 regRowid = regIns = pParse->nMem+1;
644 pParse->nMem += pTab->nCol + 1;
danielk1977034ca142007-06-26 10:38:54 +0000645 if( IsVirtual(pTab) ){
drh05a86c52014-02-16 01:55:49 +0000646 regRowid++;
647 pParse->nMem++;
danielk1977034ca142007-06-26 10:38:54 +0000648 }
drh05a86c52014-02-16 01:55:49 +0000649 regData = regRowid+1;
drh1ccde152000-06-17 13:12:39 +0000650
651 /* If the INSERT statement included an IDLIST term, then make sure
652 ** all elements of the IDLIST really are columns of the table and
653 ** remember the column indices.
drhc8392582001-12-31 02:48:51 +0000654 **
655 ** If the table has an INTEGER PRIMARY KEY column and that column
drhd82b5022013-10-26 00:58:34 +0000656 ** is named in the IDLIST, then record in the ipkColumn variable
657 ** the index into IDLIST of the primary key column. ipkColumn is
drhc8392582001-12-31 02:48:51 +0000658 ** the index of the primary key as it appears in IDLIST, not as
drhd82b5022013-10-26 00:58:34 +0000659 ** is appears in the original table. (The index of the INTEGER
660 ** PRIMARY KEY in the original table is pTab->iPKey.)
drh1ccde152000-06-17 13:12:39 +0000661 */
drha21f78b2015-04-19 18:32:43 +0000662 bIdListInOrder = (pTab->tabFlags & TF_OOOHidden)==0;
drh967e8b72000-06-21 13:59:10 +0000663 if( pColumn ){
664 for(i=0; i<pColumn->nId; i++){
665 pColumn->a[i].idx = -1;
drhcce7d172000-05-31 15:34:51 +0000666 }
drh967e8b72000-06-21 13:59:10 +0000667 for(i=0; i<pColumn->nId; i++){
drhcce7d172000-05-31 15:34:51 +0000668 for(j=0; j<pTab->nCol; j++){
danielk19774adee202004-05-08 08:23:19 +0000669 if( sqlite3StrICmp(pColumn->a[i].zName, pTab->aCol[j].zName)==0 ){
drh967e8b72000-06-21 13:59:10 +0000670 pColumn->a[i].idx = j;
drh05a86c52014-02-16 01:55:49 +0000671 if( i!=j ) bIdListInOrder = 0;
drh4a324312001-12-21 14:30:42 +0000672 if( j==pTab->iPKey ){
drhd82b5022013-10-26 00:58:34 +0000673 ipkColumn = i; assert( !withoutRowid );
drh4a324312001-12-21 14:30:42 +0000674 }
drhcce7d172000-05-31 15:34:51 +0000675 break;
676 }
677 }
678 if( j>=pTab->nCol ){
drhec95c442013-10-23 01:57:32 +0000679 if( sqlite3IsRowid(pColumn->a[i].zName) && !withoutRowid ){
drhd82b5022013-10-26 00:58:34 +0000680 ipkColumn = i;
drhe48ae712014-05-23 11:48:57 +0000681 bIdListInOrder = 0;
drha0217ba2003-06-01 01:10:33 +0000682 }else{
danielk19774adee202004-05-08 08:23:19 +0000683 sqlite3ErrorMsg(pParse, "table %S has no column named %s",
drha0217ba2003-06-01 01:10:33 +0000684 pTabList, 0, pColumn->a[i].zName);
dan1db95102010-06-28 10:15:19 +0000685 pParse->checkSchema = 1;
drha0217ba2003-06-01 01:10:33 +0000686 goto insert_cleanup;
687 }
drhcce7d172000-05-31 15:34:51 +0000688 }
689 }
690 }
drh1ccde152000-06-17 13:12:39 +0000691
drhcce7d172000-05-31 15:34:51 +0000692 /* Figure out how many columns of data are supplied. If the data
693 ** is coming from a SELECT statement, then generate a co-routine that
694 ** produces a single row of the SELECT on each invocation. The
695 ** co-routine is the common header to the 3rd and 4th templates.
696 */
drh5f085262012-09-15 13:29:23 +0000697 if( pSelect ){
drha21f78b2015-04-19 18:32:43 +0000698 /* Data is coming from a SELECT or from a multi-row VALUES clause.
699 ** Generate a co-routine to run the SELECT. */
drh05a86c52014-02-16 01:55:49 +0000700 int regYield; /* Register holding co-routine entry-point */
701 int addrTop; /* Top of the co-routine */
702 int rc; /* Result code */
drhcce7d172000-05-31 15:34:51 +0000703
drh05a86c52014-02-16 01:55:49 +0000704 regYield = ++pParse->nMem;
705 addrTop = sqlite3VdbeCurrentAddr(v) + 1;
706 sqlite3VdbeAddOp3(v, OP_InitCoroutine, regYield, 0, addrTop);
707 sqlite3SelectDestInit(&dest, SRT_Coroutine, regYield);
708 dest.iSdst = bIdListInOrder ? regData : 0;
709 dest.nSdst = pTab->nCol;
710 rc = sqlite3Select(pParse, pSelect, &dest);
drh2b596da2012-07-23 21:43:19 +0000711 regFromSelect = dest.iSdst;
drh992590b2015-04-19 22:41:22 +0000712 if( rc || db->mallocFailed || pParse->nErr ) goto insert_cleanup;
drh2fade2f2016-02-09 02:12:20 +0000713 sqlite3VdbeEndCoroutine(v, regYield);
drh05a86c52014-02-16 01:55:49 +0000714 sqlite3VdbeJumpHere(v, addrTop - 1); /* label B: */
drhcce7d172000-05-31 15:34:51 +0000715 assert( pSelect->pEList );
716 nColumn = pSelect->pEList->nExpr;
drhcce7d172000-05-31 15:34:51 +0000717
718 /* Set useTempTable to TRUE if the result of the SELECT statement
719 ** should be written into a temporary table (template 4). Set to
720 ** FALSE if each output row of the SELECT can be written directly into
721 ** the destination table (template 3).
722 **
723 ** A temp table must be used if the table being updated is also one
724 ** of the tables being read by the SELECT statement. Also use a
725 ** temp table in the case of row triggers.
726 */
drh05a86c52014-02-16 01:55:49 +0000727 if( pTrigger || readsTable(pParse, iDb, pTab) ){
drhcce7d172000-05-31 15:34:51 +0000728 useTempTable = 1;
729 }
730
731 if( useTempTable ){
732 /* Invoke the coroutine to extract information from the SELECT
733 ** and add it to a transient table srcTab. The code generated
734 ** here is from the 4th template:
735 **
736 ** B: open temp table
drh81cf13e2014-02-07 18:27:53 +0000737 ** L: yield X, goto M at EOF
drhcce7d172000-05-31 15:34:51 +0000738 ** insert row from R..R+n into temp table
739 ** goto L
740 ** M: ...
741 */
742 int regRec; /* Register to hold packed record */
743 int regTempRowid; /* Register to hold temp table ROWID */
drh06280ee2014-02-20 19:32:38 +0000744 int addrL; /* Label "L" */
drhcce7d172000-05-31 15:34:51 +0000745
746 srcTab = pParse->nTab++;
747 regRec = sqlite3GetTempReg(pParse);
748 regTempRowid = sqlite3GetTempReg(pParse);
749 sqlite3VdbeAddOp2(v, OP_OpenEphemeral, srcTab, nColumn);
drh06280ee2014-02-20 19:32:38 +0000750 addrL = sqlite3VdbeAddOp1(v, OP_Yield, dest.iSDParm); VdbeCoverage(v);
drhcce7d172000-05-31 15:34:51 +0000751 sqlite3VdbeAddOp3(v, OP_MakeRecord, regFromSelect, nColumn, regRec);
752 sqlite3VdbeAddOp2(v, OP_NewRowid, srcTab, regTempRowid);
753 sqlite3VdbeAddOp3(v, OP_Insert, srcTab, regRec, regTempRowid);
drh076e85f2015-09-03 13:46:12 +0000754 sqlite3VdbeGoto(v, addrL);
drh06280ee2014-02-20 19:32:38 +0000755 sqlite3VdbeJumpHere(v, addrL);
drhcce7d172000-05-31 15:34:51 +0000756 sqlite3ReleaseTempReg(pParse, regRec);
757 sqlite3ReleaseTempReg(pParse, regTempRowid);
758 }
759 }else{
drha21f78b2015-04-19 18:32:43 +0000760 /* This is the case if the data for the INSERT is coming from a
761 ** single-row VALUES clause
drhcce7d172000-05-31 15:34:51 +0000762 */
763 NameContext sNC;
764 memset(&sNC, 0, sizeof(sNC));
765 sNC.pParse = pParse;
766 srcTab = -1;
767 assert( useTempTable==0 );
drhfea870b2015-08-24 20:54:06 +0000768 if( pList ){
769 nColumn = pList->nExpr;
770 if( sqlite3ResolveExprListNames(&sNC, pList) ){
drhcce7d172000-05-31 15:34:51 +0000771 goto insert_cleanup;
772 }
drhfea870b2015-08-24 20:54:06 +0000773 }else{
774 nColumn = 0;
drhcce7d172000-05-31 15:34:51 +0000775 }
776 }
777
drhaacc5432002-01-06 17:07:40 +0000778 /* If there is no IDLIST term but the table has an integer primary
drhd82b5022013-10-26 00:58:34 +0000779 ** key, the set the ipkColumn variable to the integer primary key
780 ** column index in the original table definition.
drh4a324312001-12-21 14:30:42 +0000781 */
drh147d0cc2006-08-25 23:42:53 +0000782 if( pColumn==0 && nColumn>0 ){
drhd82b5022013-10-26 00:58:34 +0000783 ipkColumn = pTab->iPKey;
drh4a324312001-12-21 14:30:42 +0000784 }
drh05a86c52014-02-16 01:55:49 +0000785
drhcce7d172000-05-31 15:34:51 +0000786 /* Make sure the number of columns in the source data matches the number
787 ** of columns to be inserted into the table.
788 */
drhf0c91452015-11-18 18:43:15 +0000789 for(i=0; i<pTab->nCol; i++){
790 nHidden += (IsHiddenColumn(&pTab->aCol[i]) ? 1 : 0);
drhcce7d172000-05-31 15:34:51 +0000791 }
792 if( pColumn==0 && nColumn && nColumn!=(pTab->nCol-nHidden) ){
793 sqlite3ErrorMsg(pParse,
794 "table %S has %d columns but %d values were supplied",
795 pTabList, 0, pTab->nCol-nHidden, nColumn);
796 goto insert_cleanup;
797 }
798 if( pColumn!=0 && nColumn!=pColumn->nId ){
799 sqlite3ErrorMsg(pParse, "%d values for %d columns", nColumn, pColumn->nId);
800 goto insert_cleanup;
801 }
danielk1977c3f9bad2002-05-15 08:30:12 +0000802
drhfeeb1392002-04-09 03:28:01 +0000803 /* Initialize the count of rows to be inserted
804 */
drh79636912018-04-19 23:52:39 +0000805 if( (db->flags & SQLITE_CountRows)!=0
806 && !pParse->nested
807 && !pParse->pTriggerTab
808 ){
drh6a288a32008-01-07 19:20:24 +0000809 regRowCount = ++pParse->nMem;
810 sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount);
drhfeeb1392002-04-09 03:28:01 +0000811 }
812
danielk1977e448dc42008-01-02 11:50:51 +0000813 /* If this is not a view, open the table and and all indices */
814 if( !isView ){
drhaa9b8962008-01-08 02:57:55 +0000815 int nIdx;
danfd261ec2015-10-22 20:54:33 +0000816 nIdx = sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, 0, -1, 0,
drh26198bb2013-10-31 11:15:09 +0000817 &iDataCur, &iIdxCur);
drha7c3b932019-05-07 19:13:42 +0000818 aRegIdx = sqlite3DbMallocRawNN(db, sizeof(int)*(nIdx+2));
drhaa9b8962008-01-08 02:57:55 +0000819 if( aRegIdx==0 ){
820 goto insert_cleanup;
821 }
drh2c4dfc32016-11-10 14:24:04 +0000822 for(i=0, pIdx=pTab->pIndex; i<nIdx; pIdx=pIdx->pNext, i++){
823 assert( pIdx );
drhaa9b8962008-01-08 02:57:55 +0000824 aRegIdx[i] = ++pParse->nMem;
drh2c4dfc32016-11-10 14:24:04 +0000825 pParse->nMem += pIdx->nColumn;
drhaa9b8962008-01-08 02:57:55 +0000826 }
drha7c3b932019-05-07 19:13:42 +0000827 aRegIdx[i] = ++pParse->nMem; /* Register to store the table record */
danielk1977c3f9bad2002-05-15 08:30:12 +0000828 }
drh788d55a2018-04-13 01:15:09 +0000829#ifndef SQLITE_OMIT_UPSERT
drh0b30a112018-04-13 21:55:22 +0000830 if( pUpsert ){
drhb042d922019-01-04 23:39:37 +0000831 if( IsVirtual(pTab) ){
832 sqlite3ErrorMsg(pParse, "UPSERT not implemented for virtual table \"%s\"",
833 pTab->zName);
834 goto insert_cleanup;
835 }
dan9105fd52019-08-19 17:26:32 +0000836 if( sqlite3HasExplicitNulls(pParse, pUpsert->pUpsertTarget) ){
837 goto insert_cleanup;
838 }
drh788d55a2018-04-13 01:15:09 +0000839 pTabList->a[0].iCursor = iDataCur;
drh0b30a112018-04-13 21:55:22 +0000840 pUpsert->pUpsertSrc = pTabList;
drheac9fab2018-04-16 13:00:50 +0000841 pUpsert->regData = regData;
drh7fc3aba2018-04-20 13:18:51 +0000842 pUpsert->iDataCur = iDataCur;
843 pUpsert->iIdxCur = iIdxCur;
drh0b30a112018-04-13 21:55:22 +0000844 if( pUpsert->pUpsertTarget ){
845 sqlite3UpsertAnalyzeTarget(pParse, pTabList, pUpsert);
846 }
drh788d55a2018-04-13 01:15:09 +0000847 }
848#endif
849
danielk1977c3f9bad2002-05-15 08:30:12 +0000850
drhe00ee6e2008-06-20 15:24:01 +0000851 /* This is the top of the main insertion loop */
drh142e30d2002-08-28 03:00:58 +0000852 if( useTempTable ){
drhe00ee6e2008-06-20 15:24:01 +0000853 /* This block codes the top of loop only. The complete loop is the
854 ** following pseudocode (template 4):
855 **
drh81cf13e2014-02-07 18:27:53 +0000856 ** rewind temp table, if empty goto D
drhe00ee6e2008-06-20 15:24:01 +0000857 ** C: loop over rows of intermediate table
858 ** transfer values form intermediate table into <table>
859 ** end loop
860 ** D: ...
861 */
drh688852a2014-02-17 22:40:43 +0000862 addrInsTop = sqlite3VdbeAddOp1(v, OP_Rewind, srcTab); VdbeCoverage(v);
drhe00ee6e2008-06-20 15:24:01 +0000863 addrCont = sqlite3VdbeCurrentAddr(v);
drh142e30d2002-08-28 03:00:58 +0000864 }else if( pSelect ){
drhe00ee6e2008-06-20 15:24:01 +0000865 /* This block codes the top of loop only. The complete loop is the
866 ** following pseudocode (template 3):
867 **
drh81cf13e2014-02-07 18:27:53 +0000868 ** C: yield X, at EOF goto D
drhe00ee6e2008-06-20 15:24:01 +0000869 ** insert the select result into <table> from R..R+n
870 ** goto C
871 ** D: ...
872 */
drh81cf13e2014-02-07 18:27:53 +0000873 addrInsTop = addrCont = sqlite3VdbeAddOp1(v, OP_Yield, dest.iSDParm);
drh688852a2014-02-17 22:40:43 +0000874 VdbeCoverage(v);
drh5974a302000-06-07 14:42:26 +0000875 }
drh1ccde152000-06-17 13:12:39 +0000876
drh5cf590c2003-04-24 01:45:04 +0000877 /* Run the BEFORE and INSTEAD OF triggers, if there are any
drh70ce3f02003-04-15 19:22:22 +0000878 */
drhec4ccdb2018-12-29 02:26:59 +0000879 endOfLoop = sqlite3VdbeMakeLabel(pParse);
danielk19772f886d12009-02-28 10:47:41 +0000880 if( tmask & TRIGGER_BEFORE ){
dan76d462e2009-08-30 11:42:51 +0000881 int regCols = sqlite3GetTempRange(pParse, pTab->nCol+1);
danielk1977c3f9bad2002-05-15 08:30:12 +0000882
drh70ce3f02003-04-15 19:22:22 +0000883 /* build the NEW.* reference row. Note that if there is an INTEGER
884 ** PRIMARY KEY into which a NULL is being inserted, that NULL will be
885 ** translated into a unique ID for the row. But on a BEFORE trigger,
886 ** we do not know what the unique ID will be (because the insert has
887 ** not happened yet) so we substitute a rowid of -1
888 */
drhd82b5022013-10-26 00:58:34 +0000889 if( ipkColumn<0 ){
dan76d462e2009-08-30 11:42:51 +0000890 sqlite3VdbeAddOp2(v, OP_Integer, -1, regCols);
drh70ce3f02003-04-15 19:22:22 +0000891 }else{
drh728e0f92015-10-10 14:41:28 +0000892 int addr1;
drhec95c442013-10-23 01:57:32 +0000893 assert( !withoutRowid );
drh7fe45902009-05-01 02:08:04 +0000894 if( useTempTable ){
drhd82b5022013-10-26 00:58:34 +0000895 sqlite3VdbeAddOp3(v, OP_Column, srcTab, ipkColumn, regCols);
drh7fe45902009-05-01 02:08:04 +0000896 }else{
897 assert( pSelect==0 ); /* Otherwise useTempTable is true */
drhd82b5022013-10-26 00:58:34 +0000898 sqlite3ExprCode(pParse, pList->a[ipkColumn].pExpr, regCols);
drh7fe45902009-05-01 02:08:04 +0000899 }
drh728e0f92015-10-10 14:41:28 +0000900 addr1 = sqlite3VdbeAddOp1(v, OP_NotNull, regCols); VdbeCoverage(v);
dan76d462e2009-08-30 11:42:51 +0000901 sqlite3VdbeAddOp2(v, OP_Integer, -1, regCols);
drh728e0f92015-10-10 14:41:28 +0000902 sqlite3VdbeJumpHere(v, addr1);
drh688852a2014-02-17 22:40:43 +0000903 sqlite3VdbeAddOp1(v, OP_MustBeInt, regCols); VdbeCoverage(v);
drh70ce3f02003-04-15 19:22:22 +0000904 }
905
danielk1977034ca142007-06-26 10:38:54 +0000906 /* Cannot have triggers on a virtual table. If it were possible,
907 ** this block would have to account for hidden column.
908 */
dan165921a2009-08-28 18:53:45 +0000909 assert( !IsVirtual(pTab) );
danielk1977034ca142007-06-26 10:38:54 +0000910
drh70ce3f02003-04-15 19:22:22 +0000911 /* Create the new column data
912 */
drhb1daa3f2015-11-18 21:22:02 +0000913 for(i=j=0; i<pTab->nCol; i++){
914 if( pColumn ){
drh9adf9ac2002-05-15 11:44:13 +0000915 for(j=0; j<pColumn->nId; j++){
916 if( pColumn->a[j].idx==i ) break;
917 }
danielk1977c3f9bad2002-05-15 08:30:12 +0000918 }
drhb1daa3f2015-11-18 21:22:02 +0000919 if( (!useTempTable && !pList) || (pColumn && j>=pColumn->nId)
drh03d69a62015-11-19 13:53:57 +0000920 || (pColumn==0 && IsOrdinaryHiddenColumn(&pTab->aCol[i])) ){
dan76d462e2009-08-30 11:42:51 +0000921 sqlite3ExprCode(pParse, pTab->aCol[i].pDflt, regCols+i+1);
drh142e30d2002-08-28 03:00:58 +0000922 }else if( useTempTable ){
dan76d462e2009-08-30 11:42:51 +0000923 sqlite3VdbeAddOp3(v, OP_Column, srcTab, j, regCols+i+1);
danielk1977c3f9bad2002-05-15 08:30:12 +0000924 }else{
drhd6fe9612005-01-14 01:22:00 +0000925 assert( pSelect==0 ); /* Otherwise useTempTable is true */
dan76d462e2009-08-30 11:42:51 +0000926 sqlite3ExprCodeAndCache(pParse, pList->a[j].pExpr, regCols+i+1);
danielk1977c3f9bad2002-05-15 08:30:12 +0000927 }
drh03d69a62015-11-19 13:53:57 +0000928 if( pColumn==0 && !IsOrdinaryHiddenColumn(&pTab->aCol[i]) ) j++;
danielk1977c3f9bad2002-05-15 08:30:12 +0000929 }
danielk1977a37cdde2004-05-16 11:15:36 +0000930
931 /* If this is an INSERT on a view with an INSTEAD OF INSERT trigger,
932 ** do not attempt any conversions before assembling the record.
933 ** If this is a real table, attempt conversions as required by the
934 ** table column affinities.
935 */
936 if( !isView ){
drh57bf4a82014-02-17 14:59:22 +0000937 sqlite3TableAffinity(v, pTab, regCols+1);
danielk1977a37cdde2004-05-16 11:15:36 +0000938 }
danielk1977c3f9bad2002-05-15 08:30:12 +0000939
drh5cf590c2003-04-24 01:45:04 +0000940 /* Fire BEFORE or INSTEAD OF triggers */
dan165921a2009-08-28 18:53:45 +0000941 sqlite3CodeRowTrigger(pParse, pTrigger, TK_INSERT, 0, TRIGGER_BEFORE,
dan94d7f502009-09-24 09:05:49 +0000942 pTab, regCols-pTab->nCol-1, onError, endOfLoop);
dan165921a2009-08-28 18:53:45 +0000943
dan76d462e2009-08-30 11:42:51 +0000944 sqlite3ReleaseTempRange(pParse, regCols, pTab->nCol+1);
drh70ce3f02003-04-15 19:22:22 +0000945 }
danielk1977c3f9bad2002-05-15 08:30:12 +0000946
drhd82b5022013-10-26 00:58:34 +0000947 /* Compute the content of the next row to insert into a range of
948 ** registers beginning at regIns.
drh1ccde152000-06-17 13:12:39 +0000949 */
drh5cf590c2003-04-24 01:45:04 +0000950 if( !isView ){
drh4cbdda92006-06-14 19:00:20 +0000951 if( IsVirtual(pTab) ){
danielk1977287fb612008-01-04 19:10:28 +0000952 /* The row that the VUpdate opcode will delete: none */
drh6a288a32008-01-07 19:20:24 +0000953 sqlite3VdbeAddOp2(v, OP_Null, 0, regIns);
drh4cbdda92006-06-14 19:00:20 +0000954 }
drhd82b5022013-10-26 00:58:34 +0000955 if( ipkColumn>=0 ){
drh142e30d2002-08-28 03:00:58 +0000956 if( useTempTable ){
drhd82b5022013-10-26 00:58:34 +0000957 sqlite3VdbeAddOp3(v, OP_Column, srcTab, ipkColumn, regRowid);
drh142e30d2002-08-28 03:00:58 +0000958 }else if( pSelect ){
drh05a86c52014-02-16 01:55:49 +0000959 sqlite3VdbeAddOp2(v, OP_Copy, regFromSelect+ipkColumn, regRowid);
danielk1977c3f9bad2002-05-15 08:30:12 +0000960 }else{
drh04fcef02019-01-17 04:40:04 +0000961 Expr *pIpk = pList->a[ipkColumn].pExpr;
962 if( pIpk->op==TK_NULL && !IsVirtual(pTab) ){
963 sqlite3VdbeAddOp3(v, OP_NewRowid, iDataCur, regRowid, regAutoinc);
drhe4d90812007-03-29 05:51:49 +0000964 appendFlag = 1;
drh04fcef02019-01-17 04:40:04 +0000965 }else{
966 sqlite3ExprCode(pParse, pList->a[ipkColumn].pExpr, regRowid);
drhe4d90812007-03-29 05:51:49 +0000967 }
danielk1977c3f9bad2002-05-15 08:30:12 +0000968 }
drhf0863fe2005-06-12 21:35:51 +0000969 /* If the PRIMARY KEY expression is NULL, then use OP_NewRowid
drh27a32782002-06-19 20:32:43 +0000970 ** to generate a unique primary key value.
971 */
drhe4d90812007-03-29 05:51:49 +0000972 if( !appendFlag ){
drh728e0f92015-10-10 14:41:28 +0000973 int addr1;
danielk1977bb50e7a2008-07-04 10:56:07 +0000974 if( !IsVirtual(pTab) ){
drh728e0f92015-10-10 14:41:28 +0000975 addr1 = sqlite3VdbeAddOp1(v, OP_NotNull, regRowid); VdbeCoverage(v);
drh26198bb2013-10-31 11:15:09 +0000976 sqlite3VdbeAddOp3(v, OP_NewRowid, iDataCur, regRowid, regAutoinc);
drh728e0f92015-10-10 14:41:28 +0000977 sqlite3VdbeJumpHere(v, addr1);
danielk1977bb50e7a2008-07-04 10:56:07 +0000978 }else{
drh728e0f92015-10-10 14:41:28 +0000979 addr1 = sqlite3VdbeCurrentAddr(v);
980 sqlite3VdbeAddOp2(v, OP_IsNull, regRowid, addr1+2); VdbeCoverage(v);
danielk1977bb50e7a2008-07-04 10:56:07 +0000981 }
drh688852a2014-02-17 22:40:43 +0000982 sqlite3VdbeAddOp1(v, OP_MustBeInt, regRowid); VdbeCoverage(v);
drhe4d90812007-03-29 05:51:49 +0000983 }
drhec95c442013-10-23 01:57:32 +0000984 }else if( IsVirtual(pTab) || withoutRowid ){
drh6a288a32008-01-07 19:20:24 +0000985 sqlite3VdbeAddOp2(v, OP_Null, 0, regRowid);
danielk1977c3f9bad2002-05-15 08:30:12 +0000986 }else{
drh26198bb2013-10-31 11:15:09 +0000987 sqlite3VdbeAddOp3(v, OP_NewRowid, iDataCur, regRowid, regAutoinc);
drhe4d90812007-03-29 05:51:49 +0000988 appendFlag = 1;
drh4a324312001-12-21 14:30:42 +0000989 }
drh6a288a32008-01-07 19:20:24 +0000990 autoIncStep(pParse, regAutoinc, regRowid);
drh4a324312001-12-21 14:30:42 +0000991
drhd82b5022013-10-26 00:58:34 +0000992 /* Compute data for all columns of the new entry, beginning
danielk1977f29ce552002-05-19 23:43:12 +0000993 ** with the first column.
994 */
danielk1977034ca142007-06-26 10:38:54 +0000995 nHidden = 0;
danielk1977c3f9bad2002-05-15 08:30:12 +0000996 for(i=0; i<pTab->nCol; i++){
drh6a288a32008-01-07 19:20:24 +0000997 int iRegStore = regRowid+1+i;
danielk1977c3f9bad2002-05-15 08:30:12 +0000998 if( i==pTab->iPKey ){
drh9adf9ac2002-05-15 11:44:13 +0000999 /* The value of the INTEGER PRIMARY KEY column is always a NULL.
drhd82b5022013-10-26 00:58:34 +00001000 ** Whenever this column is read, the rowid will be substituted
1001 ** in its place. Hence, fill this column with a NULL to avoid
drh05a86c52014-02-16 01:55:49 +00001002 ** taking up data space with information that will never be used.
1003 ** As there may be shallow copies of this value, make it a soft-NULL */
1004 sqlite3VdbeAddOp1(v, OP_SoftNull, iRegStore);
drh9adf9ac2002-05-15 11:44:13 +00001005 continue;
danielk1977c3f9bad2002-05-15 08:30:12 +00001006 }
1007 if( pColumn==0 ){
danielk1977034ca142007-06-26 10:38:54 +00001008 if( IsHiddenColumn(&pTab->aCol[i]) ){
danielk1977034ca142007-06-26 10:38:54 +00001009 j = -1;
1010 nHidden++;
1011 }else{
1012 j = i - nHidden;
1013 }
danielk1977c3f9bad2002-05-15 08:30:12 +00001014 }else{
drh9adf9ac2002-05-15 11:44:13 +00001015 for(j=0; j<pColumn->nId; j++){
1016 if( pColumn->a[j].idx==i ) break;
1017 }
danielk1977c3f9bad2002-05-15 08:30:12 +00001018 }
danielk1977034ca142007-06-26 10:38:54 +00001019 if( j<0 || nColumn==0 || (pColumn && j>=pColumn->nId) ){
drh05a86c52014-02-16 01:55:49 +00001020 sqlite3ExprCodeFactorable(pParse, pTab->aCol[i].pDflt, iRegStore);
drh142e30d2002-08-28 03:00:58 +00001021 }else if( useTempTable ){
danielk1977287fb612008-01-04 19:10:28 +00001022 sqlite3VdbeAddOp3(v, OP_Column, srcTab, j, iRegStore);
drh142e30d2002-08-28 03:00:58 +00001023 }else if( pSelect ){
drh05a86c52014-02-16 01:55:49 +00001024 if( regFromSelect!=regData ){
1025 sqlite3VdbeAddOp2(v, OP_SCopy, regFromSelect+j, iRegStore);
1026 }
danielk1977c3f9bad2002-05-15 08:30:12 +00001027 }else{
danielk1977287fb612008-01-04 19:10:28 +00001028 sqlite3ExprCode(pParse, pList->a[j].pExpr, iRegStore);
drh5974a302000-06-07 14:42:26 +00001029 }
drhbed86902000-06-02 13:27:59 +00001030 }
danielk1977c3f9bad2002-05-15 08:30:12 +00001031
1032 /* Generate code to check constraints and generate index keys and
danielk1977f29ce552002-05-19 23:43:12 +00001033 ** do the insertion.
1034 */
drh4cbdda92006-06-14 19:00:20 +00001035#ifndef SQLITE_OMIT_VIRTUALTABLE
1036 if( IsVirtual(pTab) ){
danielk1977595a5232009-07-24 17:58:53 +00001037 const char *pVTab = (const char *)sqlite3GetVTable(db, pTab);
drh4f3dd152008-04-28 18:46:43 +00001038 sqlite3VtabMakeWritable(pParse, pTab);
danielk1977595a5232009-07-24 17:58:53 +00001039 sqlite3VdbeAddOp4(v, OP_VUpdate, 1, pTab->nCol+2, regIns, pVTab, P4_VTAB);
danb061d052011-04-25 18:49:57 +00001040 sqlite3VdbeChangeP5(v, onError==OE_Default ? OE_Abort : onError);
dane0af83a2009-09-08 19:15:01 +00001041 sqlite3MayAbort(pParse);
drh4cbdda92006-06-14 19:00:20 +00001042 }else
1043#endif
1044 {
danielk1977de630352009-05-04 11:42:29 +00001045 int isReplace; /* Set to true if constraints may cause a replace */
dan3b908d42016-11-08 19:22:32 +00001046 int bUseSeek; /* True to use OPFLAG_SEEKRESULT */
drhf8ffb272013-11-01 17:08:56 +00001047 sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur,
drh788d55a2018-04-13 01:15:09 +00001048 regIns, 0, ipkColumn>=0, onError, endOfLoop, &isReplace, 0, pUpsert
drh04adf412008-01-08 18:57:50 +00001049 );
dan8ff2d952013-09-05 18:40:29 +00001050 sqlite3FkCheck(pParse, pTab, 0, regIns, 0, 0);
dan3b908d42016-11-08 19:22:32 +00001051
1052 /* Set the OPFLAG_USESEEKRESULT flag if either (a) there are no REPLACE
1053 ** constraints or (b) there are no triggers and this table is not a
1054 ** parent table in a foreign key constraint. It is safe to set the
1055 ** flag in the second case as if any REPLACE constraint is hit, an
1056 ** OP_Delete or OP_IdxDelete instruction will be executed on each
1057 ** cursor that is disturbed. And these instructions both clear the
1058 ** VdbeCursor.seekResult variable, disabling the OPFLAG_USESEEKRESULT
1059 ** functionality. */
1060 bUseSeek = (isReplace==0 || (pTrigger==0 &&
1061 ((db->flags & SQLITE_ForeignKeys)==0 || sqlite3FkReferences(pTab)==0)
1062 ));
drh26198bb2013-10-31 11:15:09 +00001063 sqlite3CompleteInsertion(pParse, pTab, iDataCur, iIdxCur,
dan3b908d42016-11-08 19:22:32 +00001064 regIns, aRegIdx, 0, appendFlag, bUseSeek
1065 );
drh4cbdda92006-06-14 19:00:20 +00001066 }
drh5cf590c2003-04-24 01:45:04 +00001067 }
danielk1977c3f9bad2002-05-15 08:30:12 +00001068
drh5cf590c2003-04-24 01:45:04 +00001069 /* Update the count of rows that are inserted
1070 */
drh79636912018-04-19 23:52:39 +00001071 if( regRowCount ){
drh6a288a32008-01-07 19:20:24 +00001072 sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1);
drh5974a302000-06-07 14:42:26 +00001073 }
drh1ccde152000-06-17 13:12:39 +00001074
danielk19772f886d12009-02-28 10:47:41 +00001075 if( pTrigger ){
danielk1977c3f9bad2002-05-15 08:30:12 +00001076 /* Code AFTER triggers */
dan165921a2009-08-28 18:53:45 +00001077 sqlite3CodeRowTrigger(pParse, pTrigger, TK_INSERT, 0, TRIGGER_AFTER,
dan94d7f502009-09-24 09:05:49 +00001078 pTab, regData-2-pTab->nCol, onError, endOfLoop);
drh1bee3d72001-10-15 00:44:35 +00001079 }
1080
drhe00ee6e2008-06-20 15:24:01 +00001081 /* The bottom of the main insertion loop, if the data source
1082 ** is a SELECT statement.
danielk1977f29ce552002-05-19 23:43:12 +00001083 */
danielk19774adee202004-05-08 08:23:19 +00001084 sqlite3VdbeResolveLabel(v, endOfLoop);
drh142e30d2002-08-28 03:00:58 +00001085 if( useTempTable ){
drh688852a2014-02-17 22:40:43 +00001086 sqlite3VdbeAddOp2(v, OP_Next, srcTab, addrCont); VdbeCoverage(v);
drhe00ee6e2008-06-20 15:24:01 +00001087 sqlite3VdbeJumpHere(v, addrInsTop);
drh2eb95372008-06-06 15:04:36 +00001088 sqlite3VdbeAddOp1(v, OP_Close, srcTab);
drh142e30d2002-08-28 03:00:58 +00001089 }else if( pSelect ){
drh076e85f2015-09-03 13:46:12 +00001090 sqlite3VdbeGoto(v, addrCont);
drhe00ee6e2008-06-20 15:24:01 +00001091 sqlite3VdbeJumpHere(v, addrInsTop);
drh6b563442001-11-07 16:48:26 +00001092 }
danielk1977c3f9bad2002-05-15 08:30:12 +00001093
drh0b9f50d2009-06-23 20:28:53 +00001094insert_end:
drhf3388142004-11-13 03:48:06 +00001095 /* Update the sqlite_sequence table by storing the content of the
drh0b9f50d2009-06-23 20:28:53 +00001096 ** maximum rowid counter values recorded while inserting into
1097 ** autoincrement tables.
drh2958a4e2004-11-12 03:56:15 +00001098 */
dan165921a2009-08-28 18:53:45 +00001099 if( pParse->nested==0 && pParse->pTriggerTab==0 ){
drh0b9f50d2009-06-23 20:28:53 +00001100 sqlite3AutoincrementEnd(pParse);
1101 }
drh2958a4e2004-11-12 03:56:15 +00001102
drh1bee3d72001-10-15 00:44:35 +00001103 /*
danielk1977e7de6f22004-11-05 06:02:06 +00001104 ** Return the number of rows inserted. If this routine is
1105 ** generating code because of a call to sqlite3NestedParse(), do not
1106 ** invoke the callback function.
drh1bee3d72001-10-15 00:44:35 +00001107 */
drh79636912018-04-19 23:52:39 +00001108 if( regRowCount ){
drh6a288a32008-01-07 19:20:24 +00001109 sqlite3VdbeAddOp2(v, OP_ResultRow, regRowCount, 1);
danielk197722322fd2004-05-25 23:35:17 +00001110 sqlite3VdbeSetNumCols(v, 1);
danielk197710fb7492008-10-31 10:53:22 +00001111 sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows inserted", SQLITE_STATIC);
drh1bee3d72001-10-15 00:44:35 +00001112 }
drhcce7d172000-05-31 15:34:51 +00001113
1114insert_cleanup:
drh633e6d52008-07-28 19:34:53 +00001115 sqlite3SrcListDelete(db, pTabList);
1116 sqlite3ExprListDelete(db, pList);
drh46d2e5c2018-04-12 13:15:43 +00001117 sqlite3UpsertDelete(db, pUpsert);
drh633e6d52008-07-28 19:34:53 +00001118 sqlite3SelectDelete(db, pSelect);
1119 sqlite3IdListDelete(db, pColumn);
1120 sqlite3DbFree(db, aRegIdx);
drhcce7d172000-05-31 15:34:51 +00001121}
drh9cfcf5d2002-01-29 18:41:24 +00001122
dan75cbd982009-09-21 16:06:03 +00001123/* Make sure "isView" and other macros defined above are undefined. Otherwise
peter.d.reid60ec9142014-09-06 16:39:46 +00001124** they may interfere with compilation of other functions in this file
dan75cbd982009-09-21 16:06:03 +00001125** (or in another file, if this file becomes part of the amalgamation). */
1126#ifdef isView
1127 #undef isView
1128#endif
1129#ifdef pTrigger
1130 #undef pTrigger
1131#endif
1132#ifdef tmask
1133 #undef tmask
1134#endif
1135
drh9cfcf5d2002-01-29 18:41:24 +00001136/*
drhe9816d82018-09-15 21:38:48 +00001137** Meanings of bits in of pWalker->eCode for
1138** sqlite3ExprReferencesUpdatedColumn()
drh98bfa162016-02-10 18:24:05 +00001139*/
1140#define CKCNSTRNT_COLUMN 0x01 /* CHECK constraint uses a changing column */
1141#define CKCNSTRNT_ROWID 0x02 /* CHECK constraint references the ROWID */
1142
drhe9816d82018-09-15 21:38:48 +00001143/* This is the Walker callback from sqlite3ExprReferencesUpdatedColumn().
1144* Set bit 0x01 of pWalker->eCode if pWalker->eCode to 0 and if this
1145** expression node references any of the
drh2a0b5272016-02-10 16:52:24 +00001146** columns that are being modifed by an UPDATE statement.
1147*/
1148static int checkConstraintExprNode(Walker *pWalker, Expr *pExpr){
drh98bfa162016-02-10 18:24:05 +00001149 if( pExpr->op==TK_COLUMN ){
1150 assert( pExpr->iColumn>=0 || pExpr->iColumn==-1 );
1151 if( pExpr->iColumn>=0 ){
1152 if( pWalker->u.aiCol[pExpr->iColumn]>=0 ){
1153 pWalker->eCode |= CKCNSTRNT_COLUMN;
1154 }
1155 }else{
1156 pWalker->eCode |= CKCNSTRNT_ROWID;
1157 }
drh2a0b5272016-02-10 16:52:24 +00001158 }
1159 return WRC_Continue;
1160}
1161
1162/*
1163** pExpr is a CHECK constraint on a row that is being UPDATE-ed. The
1164** only columns that are modified by the UPDATE are those for which
drh98bfa162016-02-10 18:24:05 +00001165** aiChng[i]>=0, and also the ROWID is modified if chngRowid is true.
1166**
drhe9816d82018-09-15 21:38:48 +00001167** Return true if CHECK constraint pExpr uses any of the
drh98bfa162016-02-10 18:24:05 +00001168** changing columns (or the rowid if it is changing). In other words,
drhe9816d82018-09-15 21:38:48 +00001169** return true if this CHECK constraint must be validated for
drh98bfa162016-02-10 18:24:05 +00001170** the new row in the UPDATE statement.
drhe9816d82018-09-15 21:38:48 +00001171**
1172** 2018-09-15: pExpr might also be an expression for an index-on-expressions.
1173** The operation of this routine is the same - return true if an only if
1174** the expression uses one or more of columns identified by the second and
1175** third arguments.
drh2a0b5272016-02-10 16:52:24 +00001176*/
drhe9816d82018-09-15 21:38:48 +00001177int sqlite3ExprReferencesUpdatedColumn(
1178 Expr *pExpr, /* The expression to be checked */
1179 int *aiChng, /* aiChng[x]>=0 if column x changed by the UPDATE */
1180 int chngRowid /* True if UPDATE changes the rowid */
1181){
drh2a0b5272016-02-10 16:52:24 +00001182 Walker w;
1183 memset(&w, 0, sizeof(w));
drh98bfa162016-02-10 18:24:05 +00001184 w.eCode = 0;
drh2a0b5272016-02-10 16:52:24 +00001185 w.xExprCallback = checkConstraintExprNode;
1186 w.u.aiCol = aiChng;
1187 sqlite3WalkExpr(&w, pExpr);
drh05723a92016-02-10 19:10:50 +00001188 if( !chngRowid ){
1189 testcase( (w.eCode & CKCNSTRNT_ROWID)!=0 );
1190 w.eCode &= ~CKCNSTRNT_ROWID;
1191 }
1192 testcase( w.eCode==0 );
1193 testcase( w.eCode==CKCNSTRNT_COLUMN );
1194 testcase( w.eCode==CKCNSTRNT_ROWID );
1195 testcase( w.eCode==(CKCNSTRNT_ROWID|CKCNSTRNT_COLUMN) );
drhe9816d82018-09-15 21:38:48 +00001196 return w.eCode!=0;
drh2a0b5272016-02-10 16:52:24 +00001197}
1198
drh11e85272013-10-26 15:40:48 +00001199/*
drh6934fc72013-10-31 15:37:49 +00001200** Generate code to do constraint checks prior to an INSERT or an UPDATE
1201** on table pTab.
drh9cfcf5d2002-01-29 18:41:24 +00001202**
drh6934fc72013-10-31 15:37:49 +00001203** The regNewData parameter is the first register in a range that contains
1204** the data to be inserted or the data after the update. There will be
1205** pTab->nCol+1 registers in this range. The first register (the one
1206** that regNewData points to) will contain the new rowid, or NULL in the
1207** case of a WITHOUT ROWID table. The second register in the range will
1208** contain the content of the first table column. The third register will
1209** contain the content of the second table column. And so forth.
drh0ca3e242002-01-29 23:07:02 +00001210**
drhf8ffb272013-11-01 17:08:56 +00001211** The regOldData parameter is similar to regNewData except that it contains
1212** the data prior to an UPDATE rather than afterwards. regOldData is zero
1213** for an INSERT. This routine can distinguish between UPDATE and INSERT by
1214** checking regOldData for zero.
drh0ca3e242002-01-29 23:07:02 +00001215**
drhf8ffb272013-11-01 17:08:56 +00001216** For an UPDATE, the pkChng boolean is true if the true primary key (the
1217** rowid for a normal table or the PRIMARY KEY for a WITHOUT ROWID table)
1218** might be modified by the UPDATE. If pkChng is false, then the key of
1219** the iDataCur content table is guaranteed to be unchanged by the UPDATE.
drh0ca3e242002-01-29 23:07:02 +00001220**
drhf8ffb272013-11-01 17:08:56 +00001221** For an INSERT, the pkChng boolean indicates whether or not the rowid
1222** was explicitly specified as part of the INSERT statement. If pkChng
1223** is zero, it means that the either rowid is computed automatically or
1224** that the table is a WITHOUT ROWID table and has no rowid. On an INSERT,
1225** pkChng will only be true if the INSERT statement provides an integer
1226** value for either the rowid column or its INTEGER PRIMARY KEY alias.
drh0ca3e242002-01-29 23:07:02 +00001227**
drh6934fc72013-10-31 15:37:49 +00001228** The code generated by this routine will store new index entries into
drhaa9b8962008-01-08 02:57:55 +00001229** registers identified by aRegIdx[]. No index entry is created for
1230** indices where aRegIdx[i]==0. The order of indices in aRegIdx[] is
1231** the same as the order of indices on the linked list of indices
drh6934fc72013-10-31 15:37:49 +00001232** at pTab->pIndex.
1233**
drha7c3b932019-05-07 19:13:42 +00001234** (2019-05-07) The generated code also creates a new record for the
1235** main table, if pTab is a rowid table, and stores that record in the
1236** register identified by aRegIdx[nIdx] - in other words in the first
1237** entry of aRegIdx[] past the last index. It is important that the
1238** record be generated during constraint checks to avoid affinity changes
1239** to the register content that occur after constraint checks but before
1240** the new record is inserted.
1241**
drh6934fc72013-10-31 15:37:49 +00001242** The caller must have already opened writeable cursors on the main
1243** table and all applicable indices (that is to say, all indices for which
1244** aRegIdx[] is not zero). iDataCur is the cursor for the main table when
1245** inserting or updating a rowid table, or the cursor for the PRIMARY KEY
1246** index when operating on a WITHOUT ROWID table. iIdxCur is the cursor
1247** for the first index in the pTab->pIndex list. Cursors for other indices
1248** are at iIdxCur+N for the N-th element of the pTab->pIndex list.
drh9cfcf5d2002-01-29 18:41:24 +00001249**
1250** This routine also generates code to check constraints. NOT NULL,
1251** CHECK, and UNIQUE constraints are all checked. If a constraint fails,
drh1c928532002-01-31 15:54:21 +00001252** then the appropriate action is performed. There are five possible
1253** actions: ROLLBACK, ABORT, FAIL, REPLACE, and IGNORE.
drh9cfcf5d2002-01-29 18:41:24 +00001254**
1255** Constraint type Action What Happens
1256** --------------- ---------- ----------------------------------------
drh1c928532002-01-31 15:54:21 +00001257** any ROLLBACK The current transaction is rolled back and
drh6934fc72013-10-31 15:37:49 +00001258** sqlite3_step() returns immediately with a
drh9cfcf5d2002-01-29 18:41:24 +00001259** return code of SQLITE_CONSTRAINT.
1260**
drh1c928532002-01-31 15:54:21 +00001261** any ABORT Back out changes from the current command
1262** only (do not do a complete rollback) then
drh6934fc72013-10-31 15:37:49 +00001263** cause sqlite3_step() to return immediately
drh1c928532002-01-31 15:54:21 +00001264** with SQLITE_CONSTRAINT.
1265**
drh6934fc72013-10-31 15:37:49 +00001266** any FAIL Sqlite3_step() returns immediately with a
drh1c928532002-01-31 15:54:21 +00001267** return code of SQLITE_CONSTRAINT. The
1268** transaction is not rolled back and any
drh6934fc72013-10-31 15:37:49 +00001269** changes to prior rows are retained.
drh1c928532002-01-31 15:54:21 +00001270**
drh6934fc72013-10-31 15:37:49 +00001271** any IGNORE The attempt in insert or update the current
1272** row is skipped, without throwing an error.
1273** Processing continues with the next row.
1274** (There is an immediate jump to ignoreDest.)
drh9cfcf5d2002-01-29 18:41:24 +00001275**
1276** NOT NULL REPLACE The NULL value is replace by the default
1277** value for that column. If the default value
1278** is NULL, the action is the same as ABORT.
1279**
1280** UNIQUE REPLACE The other row that conflicts with the row
1281** being inserted is removed.
1282**
1283** CHECK REPLACE Illegal. The results in an exception.
1284**
drh1c928532002-01-31 15:54:21 +00001285** Which action to take is determined by the overrideError parameter.
1286** Or if overrideError==OE_Default, then the pParse->onError parameter
1287** is used. Or if pParse->onError==OE_Default then the onError value
1288** for the constraint is used.
drh9cfcf5d2002-01-29 18:41:24 +00001289*/
danielk19774adee202004-05-08 08:23:19 +00001290void sqlite3GenerateConstraintChecks(
drh6934fc72013-10-31 15:37:49 +00001291 Parse *pParse, /* The parser context */
1292 Table *pTab, /* The table being inserted or updated */
drhf8ffb272013-11-01 17:08:56 +00001293 int *aRegIdx, /* Use register aRegIdx[i] for index i. 0 for unused */
drh6934fc72013-10-31 15:37:49 +00001294 int iDataCur, /* Canonical data cursor (main table or PK index) */
1295 int iIdxCur, /* First index cursor */
1296 int regNewData, /* First register in a range holding values to insert */
drhf8ffb272013-11-01 17:08:56 +00001297 int regOldData, /* Previous content. 0 for INSERTs */
1298 u8 pkChng, /* Non-zero if the rowid or PRIMARY KEY changed */
1299 u8 overrideError, /* Override onError to this if not OE_Default */
drh6934fc72013-10-31 15:37:49 +00001300 int ignoreDest, /* Jump to this label on an OE_Ignore resolution */
drhbdb00222016-02-10 16:03:20 +00001301 int *pbMayReplace, /* OUT: Set to true if constraint may cause a replace */
drh788d55a2018-04-13 01:15:09 +00001302 int *aiChng, /* column i is unchanged if aiChng[i]<0 */
1303 Upsert *pUpsert /* ON CONFLICT clauses, if any. NULL otherwise */
drh9cfcf5d2002-01-29 18:41:24 +00001304){
drh6934fc72013-10-31 15:37:49 +00001305 Vdbe *v; /* VDBE under constrution */
drh1b7ecbb2009-05-03 01:00:59 +00001306 Index *pIdx; /* Pointer to one of the indices */
drh11e85272013-10-26 15:40:48 +00001307 Index *pPk = 0; /* The PRIMARY KEY index */
drh2938f922012-03-07 19:13:29 +00001308 sqlite3 *db; /* Database connection */
drhf8ffb272013-11-01 17:08:56 +00001309 int i; /* loop counter */
1310 int ix; /* Index loop counter */
1311 int nCol; /* Number of columns */
1312 int onError; /* Conflict resolution strategy */
drh728e0f92015-10-10 14:41:28 +00001313 int addr1; /* Address of jump instruction */
drh1b7ecbb2009-05-03 01:00:59 +00001314 int seenReplace = 0; /* True if REPLACE is used to resolve INT PK conflict */
drh6fbe41a2013-10-30 20:22:55 +00001315 int nPkField; /* Number of fields in PRIMARY KEY. 1 for ROWID tables */
drh096fd472018-04-14 20:24:36 +00001316 Index *pUpIdx = 0; /* Index to which to apply the upsert */
drh8d1b82e2013-11-05 19:41:32 +00001317 u8 isUpdate; /* True if this is an UPDATE operation */
drh57bf4a82014-02-17 14:59:22 +00001318 u8 bAffinityDone = 0; /* True if the OP_Affinity operation has been run */
drh096fd472018-04-14 20:24:36 +00001319 int upsertBypass = 0; /* Address of Goto to bypass upsert subroutine */
drh84304502018-08-14 15:12:52 +00001320 int upsertJump = 0; /* Address of Goto that jumps into upsert subroutine */
1321 int ipkTop = 0; /* Top of the IPK uniqueness check */
1322 int ipkBottom = 0; /* OP_Goto at the end of the IPK uniqueness check */
drh9cfcf5d2002-01-29 18:41:24 +00001323
drhf8ffb272013-11-01 17:08:56 +00001324 isUpdate = regOldData!=0;
drh2938f922012-03-07 19:13:29 +00001325 db = pParse->db;
danielk19774adee202004-05-08 08:23:19 +00001326 v = sqlite3GetVdbe(pParse);
drh9cfcf5d2002-01-29 18:41:24 +00001327 assert( v!=0 );
drh417be792002-03-03 18:59:40 +00001328 assert( pTab->pSelect==0 ); /* This table is not a VIEW */
drh9cfcf5d2002-01-29 18:41:24 +00001329 nCol = pTab->nCol;
drh6934fc72013-10-31 15:37:49 +00001330
1331 /* pPk is the PRIMARY KEY index for WITHOUT ROWID tables and NULL for
1332 ** normal rowid tables. nPkField is the number of key fields in the
1333 ** pPk index or 1 for a rowid table. In other words, nPkField is the
1334 ** number of fields in the true primary key of the table. */
drh26198bb2013-10-31 11:15:09 +00001335 if( HasRowid(pTab) ){
1336 pPk = 0;
1337 nPkField = 1;
1338 }else{
1339 pPk = sqlite3PrimaryKeyIndex(pTab);
1340 nPkField = pPk->nKeyCol;
1341 }
drh6fbe41a2013-10-30 20:22:55 +00001342
1343 /* Record that this module has started */
1344 VdbeModuleComment((v, "BEGIN: GenCnstCks(%d,%d,%d,%d,%d)",
drh6934fc72013-10-31 15:37:49 +00001345 iDataCur, iIdxCur, regNewData, regOldData, pkChng));
drh9cfcf5d2002-01-29 18:41:24 +00001346
1347 /* Test all NOT NULL constraints.
1348 */
1349 for(i=0; i<nCol; i++){
drh0ca3e242002-01-29 23:07:02 +00001350 if( i==pTab->iPKey ){
drhbdb00222016-02-10 16:03:20 +00001351 continue; /* ROWID is never NULL */
1352 }
1353 if( aiChng && aiChng[i]<0 ){
1354 /* Don't bother checking for NOT NULL on columns that do not change */
drh0ca3e242002-01-29 23:07:02 +00001355 continue;
1356 }
drh9cfcf5d2002-01-29 18:41:24 +00001357 onError = pTab->aCol[i].notNull;
drhbdb00222016-02-10 16:03:20 +00001358 if( onError==OE_None ) continue; /* This column is allowed to be NULL */
drh9cfcf5d2002-01-29 18:41:24 +00001359 if( overrideError!=OE_Default ){
1360 onError = overrideError;
drha996e472003-05-16 02:30:27 +00001361 }else if( onError==OE_Default ){
1362 onError = OE_Abort;
drh9cfcf5d2002-01-29 18:41:24 +00001363 }
danielk19777977a172004-11-09 12:44:37 +00001364 if( onError==OE_Replace && pTab->aCol[i].pDflt==0 ){
drh9cfcf5d2002-01-29 18:41:24 +00001365 onError = OE_Abort;
1366 }
danielk1977b84f96f2005-01-20 11:32:23 +00001367 assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail
1368 || onError==OE_Ignore || onError==OE_Replace );
drh9bfb0792018-12-22 01:13:25 +00001369 addr1 = 0;
drh9cfcf5d2002-01-29 18:41:24 +00001370 switch( onError ){
drh9bfb0792018-12-22 01:13:25 +00001371 case OE_Replace: {
1372 assert( onError==OE_Replace );
drhec4ccdb2018-12-29 02:26:59 +00001373 addr1 = sqlite3VdbeMakeLabel(pParse);
drh9bfb0792018-12-22 01:13:25 +00001374 sqlite3VdbeAddOp2(v, OP_NotNull, regNewData+1+i, addr1);
1375 VdbeCoverage(v);
1376 sqlite3ExprCode(pParse, pTab->aCol[i].pDflt, regNewData+1+i);
1377 sqlite3VdbeAddOp2(v, OP_NotNull, regNewData+1+i, addr1);
1378 VdbeCoverage(v);
1379 onError = OE_Abort;
1380 /* Fall through into the OE_Abort case to generate code that runs
1381 ** if both the input and the default value are NULL */
1382 }
drh1c928532002-01-31 15:54:21 +00001383 case OE_Abort:
dane0af83a2009-09-08 19:15:01 +00001384 sqlite3MayAbort(pParse);
drh0978d4f2013-10-29 16:14:35 +00001385 /* Fall through */
dane0af83a2009-09-08 19:15:01 +00001386 case OE_Rollback:
drh1c928532002-01-31 15:54:21 +00001387 case OE_Fail: {
drhf9c8ce32013-11-05 13:33:55 +00001388 char *zMsg = sqlite3MPrintf(db, "%s.%s", pTab->zName,
1389 pTab->aCol[i].zName);
drh2700aca2016-12-08 01:38:24 +00001390 sqlite3VdbeAddOp3(v, OP_HaltIfNull, SQLITE_CONSTRAINT_NOTNULL, onError,
1391 regNewData+1+i);
1392 sqlite3VdbeAppendP4(v, zMsg, P4_DYNAMIC);
drhf9c8ce32013-11-05 13:33:55 +00001393 sqlite3VdbeChangeP5(v, P5_ConstraintNotNull);
drh688852a2014-02-17 22:40:43 +00001394 VdbeCoverage(v);
drh9bfb0792018-12-22 01:13:25 +00001395 if( addr1 ) sqlite3VdbeResolveLabel(v, addr1);
drh9cfcf5d2002-01-29 18:41:24 +00001396 break;
1397 }
drh098d1682009-05-02 15:46:46 +00001398 default: {
drh9bfb0792018-12-22 01:13:25 +00001399 assert( onError==OE_Ignore );
1400 sqlite3VdbeAddOp2(v, OP_IsNull, regNewData+1+i, ignoreDest);
1401 VdbeCoverage(v);
drh9cfcf5d2002-01-29 18:41:24 +00001402 break;
1403 }
drh9cfcf5d2002-01-29 18:41:24 +00001404 }
1405 }
1406
1407 /* Test all CHECK constraints
1408 */
drhffe07b22005-11-03 00:41:17 +00001409#ifndef SQLITE_OMIT_CHECK
drh2938f922012-03-07 19:13:29 +00001410 if( pTab->pCheck && (db->flags & SQLITE_IgnoreChecks)==0 ){
1411 ExprList *pCheck = pTab->pCheck;
drh6e97f8e2017-07-20 13:17:08 +00001412 pParse->iSelfTab = -(regNewData+1);
drhaa01c7e2006-03-15 16:26:10 +00001413 onError = overrideError!=OE_Default ? overrideError : OE_Abort;
drh2938f922012-03-07 19:13:29 +00001414 for(i=0; i<pCheck->nExpr; i++){
drh05723a92016-02-10 19:10:50 +00001415 int allOk;
drh2a0b5272016-02-10 16:52:24 +00001416 Expr *pExpr = pCheck->a[i].pExpr;
drhe9816d82018-09-15 21:38:48 +00001417 if( aiChng
1418 && !sqlite3ExprReferencesUpdatedColumn(pExpr, aiChng, pkChng)
1419 ){
1420 /* The check constraints do not reference any of the columns being
1421 ** updated so there is no point it verifying the check constraint */
1422 continue;
1423 }
drhec4ccdb2018-12-29 02:26:59 +00001424 allOk = sqlite3VdbeMakeLabel(pParse);
drh4031baf2018-05-28 17:31:20 +00001425 sqlite3VdbeVerifyAbortable(v, onError);
drh2a0b5272016-02-10 16:52:24 +00001426 sqlite3ExprIfTrue(pParse, pExpr, allOk, SQLITE_JUMPIFNULL);
drh2d8e9202012-12-08 04:10:44 +00001427 if( onError==OE_Ignore ){
drh076e85f2015-09-03 13:46:12 +00001428 sqlite3VdbeGoto(v, ignoreDest);
drh2d8e9202012-12-08 04:10:44 +00001429 }else{
drhf9c8ce32013-11-05 13:33:55 +00001430 char *zName = pCheck->a[i].zName;
1431 if( zName==0 ) zName = pTab->zName;
drh0ce974d2019-06-12 22:46:04 +00001432 if( onError==OE_Replace ) onError = OE_Abort; /* IMP: R-26383-51744 */
drhd91c1a12013-02-09 13:58:25 +00001433 sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_CHECK,
drhf9c8ce32013-11-05 13:33:55 +00001434 onError, zName, P4_TRANSIENT,
1435 P5_ConstraintCheck);
drh2938f922012-03-07 19:13:29 +00001436 }
drh2d8e9202012-12-08 04:10:44 +00001437 sqlite3VdbeResolveLabel(v, allOk);
drhaa01c7e2006-03-15 16:26:10 +00001438 }
drh6e97f8e2017-07-20 13:17:08 +00001439 pParse->iSelfTab = 0;
drhffe07b22005-11-03 00:41:17 +00001440 }
1441#endif /* !defined(SQLITE_OMIT_CHECK) */
drh9cfcf5d2002-01-29 18:41:24 +00001442
drh096fd472018-04-14 20:24:36 +00001443 /* UNIQUE and PRIMARY KEY constraints should be handled in the following
1444 ** order:
1445 **
drh84304502018-08-14 15:12:52 +00001446 ** (1) OE_Update
1447 ** (2) OE_Abort, OE_Fail, OE_Rollback, OE_Ignore
drh096fd472018-04-14 20:24:36 +00001448 ** (3) OE_Replace
1449 **
1450 ** OE_Fail and OE_Ignore must happen before any changes are made.
1451 ** OE_Update guarantees that only a single row will change, so it
1452 ** must happen before OE_Replace. Technically, OE_Abort and OE_Rollback
1453 ** could happen in any order, but they are grouped up front for
1454 ** convenience.
1455 **
drh84304502018-08-14 15:12:52 +00001456 ** 2018-08-14: Ticket https://www.sqlite.org/src/info/908f001483982c43
1457 ** The order of constraints used to have OE_Update as (2) and OE_Abort
1458 ** and so forth as (1). But apparently PostgreSQL checks the OE_Update
1459 ** constraint before any others, so it had to be moved.
1460 **
drh096fd472018-04-14 20:24:36 +00001461 ** Constraint checking code is generated in this order:
1462 ** (A) The rowid constraint
1463 ** (B) Unique index constraints that do not have OE_Replace as their
1464 ** default conflict resolution strategy
1465 ** (C) Unique index that do use OE_Replace by default.
1466 **
1467 ** The ordering of (2) and (3) is accomplished by making sure the linked
1468 ** list of indexes attached to a table puts all OE_Replace indexes last
1469 ** in the list. See sqlite3CreateIndex() for where that happens.
1470 */
1471
drh096fd472018-04-14 20:24:36 +00001472 if( pUpsert ){
1473 if( pUpsert->pUpsertTarget==0 ){
drhdedbc502018-04-17 18:18:05 +00001474 /* An ON CONFLICT DO NOTHING clause, without a constraint-target.
1475 ** Make all unique constraint resolution be OE_Ignore */
1476 assert( pUpsert->pUpsertSet==0 );
1477 overrideError = OE_Ignore;
1478 pUpsert = 0;
drh096fd472018-04-14 20:24:36 +00001479 }else if( (pUpIdx = pUpsert->pUpsertIdx)!=0 ){
drh84304502018-08-14 15:12:52 +00001480 /* If the constraint-target uniqueness check must be run first.
1481 ** Jump to that uniqueness check now */
1482 upsertJump = sqlite3VdbeAddOp0(v, OP_Goto);
1483 VdbeComment((v, "UPSERT constraint goes first"));
drh096fd472018-04-14 20:24:36 +00001484 }
1485 }
1486
drhf8ffb272013-11-01 17:08:56 +00001487 /* If rowid is changing, make sure the new rowid does not previously
1488 ** exist in the table.
drh9cfcf5d2002-01-29 18:41:24 +00001489 */
drh6fbe41a2013-10-30 20:22:55 +00001490 if( pkChng && pPk==0 ){
drhec4ccdb2018-12-29 02:26:59 +00001491 int addrRowidOk = sqlite3VdbeMakeLabel(pParse);
drh11e85272013-10-26 15:40:48 +00001492
drhf8ffb272013-11-01 17:08:56 +00001493 /* Figure out what action to take in case of a rowid collision */
drh0ca3e242002-01-29 23:07:02 +00001494 onError = pTab->keyConf;
1495 if( overrideError!=OE_Default ){
1496 onError = overrideError;
drha996e472003-05-16 02:30:27 +00001497 }else if( onError==OE_Default ){
1498 onError = OE_Abort;
drh0ca3e242002-01-29 23:07:02 +00001499 }
drh11e85272013-10-26 15:40:48 +00001500
drhc8a0c902018-04-13 15:14:33 +00001501 /* figure out whether or not upsert applies in this case */
drh096fd472018-04-14 20:24:36 +00001502 if( pUpsert && pUpsert->pUpsertIdx==0 ){
drhc8a0c902018-04-13 15:14:33 +00001503 if( pUpsert->pUpsertSet==0 ){
1504 onError = OE_Ignore; /* DO NOTHING is the same as INSERT OR IGNORE */
1505 }else{
1506 onError = OE_Update; /* DO UPDATE */
1507 }
1508 }
1509
drh8d1b82e2013-11-05 19:41:32 +00001510 /* If the response to a rowid conflict is REPLACE but the response
1511 ** to some other UNIQUE constraint is FAIL or IGNORE, then we need
1512 ** to defer the running of the rowid conflict checking until after
1513 ** the UNIQUE constraints have run.
1514 */
drh84304502018-08-14 15:12:52 +00001515 if( onError==OE_Replace /* IPK rule is REPLACE */
1516 && onError!=overrideError /* Rules for other contraints are different */
1517 && pTab->pIndex /* There exist other constraints */
drh096fd472018-04-14 20:24:36 +00001518 ){
drh84304502018-08-14 15:12:52 +00001519 ipkTop = sqlite3VdbeAddOp0(v, OP_Goto)+1;
1520 VdbeComment((v, "defer IPK REPLACE until last"));
drh8d1b82e2013-11-05 19:41:32 +00001521 }
1522
drhbb6b1ca2018-04-19 13:52:39 +00001523 if( isUpdate ){
1524 /* pkChng!=0 does not mean that the rowid has changed, only that
1525 ** it might have changed. Skip the conflict logic below if the rowid
1526 ** is unchanged. */
1527 sqlite3VdbeAddOp3(v, OP_Eq, regNewData, addrRowidOk, regOldData);
1528 sqlite3VdbeChangeP5(v, SQLITE_NOTNULL);
1529 VdbeCoverage(v);
1530 }
1531
drhf8ffb272013-11-01 17:08:56 +00001532 /* Check to see if the new rowid already exists in the table. Skip
1533 ** the following conflict logic if it does not. */
drh7f5f3062018-04-17 20:06:24 +00001534 VdbeNoopComment((v, "uniqueness check for ROWID"));
drh4031baf2018-05-28 17:31:20 +00001535 sqlite3VdbeVerifyAbortable(v, onError);
drh6934fc72013-10-31 15:37:49 +00001536 sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, addrRowidOk, regNewData);
drh688852a2014-02-17 22:40:43 +00001537 VdbeCoverage(v);
drhf8ffb272013-11-01 17:08:56 +00001538
dane1e2ae22009-09-24 16:52:28 +00001539 switch( onError ){
1540 default: {
1541 onError = OE_Abort;
1542 /* Fall thru into the next case */
drh79b0c952002-05-21 12:56:43 +00001543 }
dane1e2ae22009-09-24 16:52:28 +00001544 case OE_Rollback:
1545 case OE_Abort:
1546 case OE_Fail: {
drh99160482018-04-18 01:34:39 +00001547 testcase( onError==OE_Rollback );
1548 testcase( onError==OE_Abort );
1549 testcase( onError==OE_Fail );
drhf9c8ce32013-11-05 13:33:55 +00001550 sqlite3RowidConstraint(pParse, onError, pTab);
dane1e2ae22009-09-24 16:52:28 +00001551 break;
drh0ca3e242002-01-29 23:07:02 +00001552 }
dane1e2ae22009-09-24 16:52:28 +00001553 case OE_Replace: {
1554 /* If there are DELETE triggers on this table and the
1555 ** recursive-triggers flag is set, call GenerateRowDelete() to
mistachkind5578432012-08-25 10:01:29 +00001556 ** remove the conflicting row from the table. This will fire
dane1e2ae22009-09-24 16:52:28 +00001557 ** the triggers and remove both the table and index b-tree entries.
1558 **
1559 ** Otherwise, if there are no triggers or the recursive-triggers
danda730f62010-02-18 08:19:19 +00001560 ** flag is not set, but the table has one or more indexes, call
1561 ** GenerateRowIndexDelete(). This removes the index b-tree entries
1562 ** only. The table b-tree entry will be replaced by the new entry
1563 ** when it is inserted.
1564 **
1565 ** If either GenerateRowDelete() or GenerateRowIndexDelete() is called,
1566 ** also invoke MultiWrite() to indicate that this VDBE may require
1567 ** statement rollback (if the statement is aborted after the delete
1568 ** takes place). Earlier versions called sqlite3MultiWrite() regardless,
1569 ** but being more selective here allows statements like:
1570 **
1571 ** REPLACE INTO t(rowid) VALUES($newrowid)
1572 **
1573 ** to run without a statement journal if there are no indexes on the
1574 ** table.
1575 */
dane1e2ae22009-09-24 16:52:28 +00001576 Trigger *pTrigger = 0;
drh2938f922012-03-07 19:13:29 +00001577 if( db->flags&SQLITE_RecTriggers ){
dane1e2ae22009-09-24 16:52:28 +00001578 pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0);
1579 }
dane7a94d82009-10-01 16:09:04 +00001580 if( pTrigger || sqlite3FkRequired(pParse, pTab, 0, 0) ){
danda730f62010-02-18 08:19:19 +00001581 sqlite3MultiWrite(pParse);
drh26198bb2013-10-31 11:15:09 +00001582 sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,
dan438b8812015-09-15 15:55:15 +00001583 regNewData, 1, 0, OE_Replace, 1, -1);
dan46c47d42011-03-01 18:42:07 +00001584 }else{
drh9b1c62d2011-03-30 21:04:43 +00001585#ifdef SQLITE_ENABLE_PREUPDATE_HOOK
drh54f2cd92018-04-13 16:23:22 +00001586 assert( HasRowid(pTab) );
1587 /* This OP_Delete opcode fires the pre-update-hook only. It does
1588 ** not modify the b-tree. It is more efficient to let the coming
1589 ** OP_Insert replace the existing entry than it is to delete the
1590 ** existing entry and then insert a new one. */
1591 sqlite3VdbeAddOp2(v, OP_Delete, iDataCur, OPFLAG_ISNOOP);
1592 sqlite3VdbeAppendP4(v, pTab, P4_TABLE);
drh9b1c62d2011-03-30 21:04:43 +00001593#endif /* SQLITE_ENABLE_PREUPDATE_HOOK */
dan46c47d42011-03-01 18:42:07 +00001594 if( pTab->pIndex ){
1595 sqlite3MultiWrite(pParse);
drhb77ebd82015-09-15 13:42:16 +00001596 sqlite3GenerateRowIndexDelete(pParse, pTab, iDataCur, iIdxCur,0,-1);
dan46c47d42011-03-01 18:42:07 +00001597 }
dane1e2ae22009-09-24 16:52:28 +00001598 }
1599 seenReplace = 1;
1600 break;
drh5383ae52003-06-04 12:23:30 +00001601 }
drh9eddaca2018-04-13 18:59:17 +00001602#ifndef SQLITE_OMIT_UPSERT
1603 case OE_Update: {
dan2cc00422018-04-17 18:16:10 +00001604 sqlite3UpsertDoUpdate(pParse, pUpsert, pTab, 0, iDataCur);
drh9eddaca2018-04-13 18:59:17 +00001605 /* Fall through */
1606 }
1607#endif
dane1e2ae22009-09-24 16:52:28 +00001608 case OE_Ignore: {
drh99160482018-04-18 01:34:39 +00001609 testcase( onError==OE_Ignore );
drh076e85f2015-09-03 13:46:12 +00001610 sqlite3VdbeGoto(v, ignoreDest);
dane1e2ae22009-09-24 16:52:28 +00001611 break;
1612 }
1613 }
drh11e85272013-10-26 15:40:48 +00001614 sqlite3VdbeResolveLabel(v, addrRowidOk);
drh84304502018-08-14 15:12:52 +00001615 if( ipkTop ){
1616 ipkBottom = sqlite3VdbeAddOp0(v, OP_Goto);
1617 sqlite3VdbeJumpHere(v, ipkTop-1);
drh5383ae52003-06-04 12:23:30 +00001618 }
drh0ca3e242002-01-29 23:07:02 +00001619 }
drh0bd1f4e2002-06-06 18:54:39 +00001620
1621 /* Test all UNIQUE constraints by creating entries for each UNIQUE
1622 ** index and making sure that duplicate entries do not already exist.
drh11e85272013-10-26 15:40:48 +00001623 ** Compute the revised record entries for indices as we go.
drhf8ffb272013-11-01 17:08:56 +00001624 **
1625 ** This loop also handles the case of the PRIMARY KEY index for a
1626 ** WITHOUT ROWID table.
drh0bd1f4e2002-06-06 18:54:39 +00001627 */
drh26198bb2013-10-31 11:15:09 +00001628 for(ix=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, ix++){
drh6934fc72013-10-31 15:37:49 +00001629 int regIdx; /* Range of registers hold conent for pIdx */
1630 int regR; /* Range of registers holding conflicting PK */
1631 int iThisCur; /* Cursor for this UNIQUE index */
1632 int addrUniqueOk; /* Jump here if the UNIQUE constraint is satisfied */
drh2184fc72011-04-09 03:04:13 +00001633
drh26198bb2013-10-31 11:15:09 +00001634 if( aRegIdx[ix]==0 ) continue; /* Skip indices that do not change */
drh7f5f3062018-04-17 20:06:24 +00001635 if( pUpIdx==pIdx ){
drh84304502018-08-14 15:12:52 +00001636 addrUniqueOk = upsertJump+1;
drh7f5f3062018-04-17 20:06:24 +00001637 upsertBypass = sqlite3VdbeGoto(v, 0);
1638 VdbeComment((v, "Skip upsert subroutine"));
drh84304502018-08-14 15:12:52 +00001639 sqlite3VdbeJumpHere(v, upsertJump);
drh7f5f3062018-04-17 20:06:24 +00001640 }else{
drhec4ccdb2018-12-29 02:26:59 +00001641 addrUniqueOk = sqlite3VdbeMakeLabel(pParse);
drh7f5f3062018-04-17 20:06:24 +00001642 }
drh84304502018-08-14 15:12:52 +00001643 if( bAffinityDone==0 && (pUpIdx==0 || pUpIdx==pIdx) ){
1644 sqlite3TableAffinity(v, pTab, regNewData+1);
1645 bAffinityDone = 1;
1646 }
drh7f5f3062018-04-17 20:06:24 +00001647 VdbeNoopComment((v, "uniqueness check for %s", pIdx->zName));
drh6934fc72013-10-31 15:37:49 +00001648 iThisCur = iIdxCur+ix;
drh7f5f3062018-04-17 20:06:24 +00001649
drhb2fe7d82003-04-20 17:29:23 +00001650
drhf8ffb272013-11-01 17:08:56 +00001651 /* Skip partial indices for which the WHERE clause is not true */
drhb2b9d3d2013-08-01 01:14:43 +00001652 if( pIdx->pPartIdxWhere ){
drh26198bb2013-10-31 11:15:09 +00001653 sqlite3VdbeAddOp2(v, OP_Null, 0, aRegIdx[ix]);
drh6e97f8e2017-07-20 13:17:08 +00001654 pParse->iSelfTab = -(regNewData+1);
drh72bc8202015-06-11 13:58:35 +00001655 sqlite3ExprIfFalseDup(pParse, pIdx->pPartIdxWhere, addrUniqueOk,
1656 SQLITE_JUMPIFNULL);
drh6e97f8e2017-07-20 13:17:08 +00001657 pParse->iSelfTab = 0;
drhb2b9d3d2013-08-01 01:14:43 +00001658 }
1659
drh6934fc72013-10-31 15:37:49 +00001660 /* Create a record for this index entry as it should appear after
drhf8ffb272013-11-01 17:08:56 +00001661 ** the insert or update. Store that record in the aRegIdx[ix] register
1662 */
drhbf2f5732016-11-10 16:07:43 +00001663 regIdx = aRegIdx[ix]+1;
drh9cfcf5d2002-01-29 18:41:24 +00001664 for(i=0; i<pIdx->nColumn; i++){
drh6934fc72013-10-31 15:37:49 +00001665 int iField = pIdx->aiColumn[i];
drhf82b9af2013-11-01 14:03:20 +00001666 int x;
drh4b92f982015-09-29 17:20:14 +00001667 if( iField==XN_EXPR ){
drh6e97f8e2017-07-20 13:17:08 +00001668 pParse->iSelfTab = -(regNewData+1);
drh1c75c9d2015-12-21 15:22:13 +00001669 sqlite3ExprCodeCopy(pParse, pIdx->aColExpr->a[i].pExpr, regIdx+i);
drh6e97f8e2017-07-20 13:17:08 +00001670 pParse->iSelfTab = 0;
drh1f9ca2c2015-08-25 16:57:52 +00001671 VdbeComment((v, "%s column %d", pIdx->zName, i));
drh9cfcf5d2002-01-29 18:41:24 +00001672 }else{
drh4b92f982015-09-29 17:20:14 +00001673 if( iField==XN_ROWID || iField==pTab->iPKey ){
drh1f9ca2c2015-08-25 16:57:52 +00001674 x = regNewData;
drh1f9ca2c2015-08-25 16:57:52 +00001675 }else{
1676 x = iField + regNewData + 1;
1677 }
drhfed7ac62015-10-15 18:04:59 +00001678 sqlite3VdbeAddOp2(v, iField<0 ? OP_IntCopy : OP_SCopy, x, regIdx+i);
drh1f9ca2c2015-08-25 16:57:52 +00001679 VdbeComment((v, "%s", iField<0 ? "rowid" : pTab->aCol[iField].zName));
drh9cfcf5d2002-01-29 18:41:24 +00001680 }
1681 }
drh26198bb2013-10-31 11:15:09 +00001682 sqlite3VdbeAddOp3(v, OP_MakeRecord, regIdx, pIdx->nColumn, aRegIdx[ix]);
drh26198bb2013-10-31 11:15:09 +00001683 VdbeComment((v, "for %s", pIdx->zName));
drh7e4acf72017-02-14 20:00:16 +00001684#ifdef SQLITE_ENABLE_NULL_TRIM
drh9df385e2019-02-19 13:08:35 +00001685 if( pIdx->idxType==SQLITE_IDXTYPE_PRIMARYKEY ){
1686 sqlite3SetMakeRecordP5(v, pIdx->pTable);
1687 }
drh7e4acf72017-02-14 20:00:16 +00001688#endif
drhb2fe7d82003-04-20 17:29:23 +00001689
drhf8ffb272013-11-01 17:08:56 +00001690 /* In an UPDATE operation, if this index is the PRIMARY KEY index
1691 ** of a WITHOUT ROWID table and there has been no change the
1692 ** primary key, then no collision is possible. The collision detection
1693 ** logic below can all be skipped. */
drh00012df2013-11-05 01:59:07 +00001694 if( isUpdate && pPk==pIdx && pkChng==0 ){
drhda475b82013-11-04 21:44:54 +00001695 sqlite3VdbeResolveLabel(v, addrUniqueOk);
1696 continue;
1697 }
drhf8ffb272013-11-01 17:08:56 +00001698
drh6934fc72013-10-31 15:37:49 +00001699 /* Find out what action to take in case there is a uniqueness conflict */
drh9cfcf5d2002-01-29 18:41:24 +00001700 onError = pIdx->onError;
danielk1977de630352009-05-04 11:42:29 +00001701 if( onError==OE_None ){
drh11e85272013-10-26 15:40:48 +00001702 sqlite3VdbeResolveLabel(v, addrUniqueOk);
danielk1977de630352009-05-04 11:42:29 +00001703 continue; /* pIdx is not a UNIQUE index */
1704 }
drh9cfcf5d2002-01-29 18:41:24 +00001705 if( overrideError!=OE_Default ){
1706 onError = overrideError;
drha996e472003-05-16 02:30:27 +00001707 }else if( onError==OE_Default ){
1708 onError = OE_Abort;
drh9cfcf5d2002-01-29 18:41:24 +00001709 }
drhc6c9e152016-11-10 17:01:36 +00001710
drhc8a0c902018-04-13 15:14:33 +00001711 /* Figure out if the upsert clause applies to this index */
drh096fd472018-04-14 20:24:36 +00001712 if( pUpIdx==pIdx ){
drhc8a0c902018-04-13 15:14:33 +00001713 if( pUpsert->pUpsertSet==0 ){
1714 onError = OE_Ignore; /* DO NOTHING is the same as INSERT OR IGNORE */
1715 }else{
1716 onError = OE_Update; /* DO UPDATE */
1717 }
1718 }
1719
drh801f55d2017-01-04 22:02:56 +00001720 /* Collision detection may be omitted if all of the following are true:
1721 ** (1) The conflict resolution algorithm is REPLACE
1722 ** (2) The table is a WITHOUT ROWID table
1723 ** (3) There are no secondary indexes on the table
1724 ** (4) No delete triggers need to be fired if there is a conflict
danf9a12a12017-01-05 06:57:42 +00001725 ** (5) No FK constraint counters need to be updated if a conflict occurs.
dan418454c2019-01-07 15:57:35 +00001726 **
1727 ** This is not possible for ENABLE_PREUPDATE_HOOK builds, as the row
1728 ** must be explicitly deleted in order to ensure any pre-update hook
1729 ** is invoked. */
1730#ifndef SQLITE_ENABLE_PREUPDATE_HOOK
drh801f55d2017-01-04 22:02:56 +00001731 if( (ix==0 && pIdx->pNext==0) /* Condition 3 */
1732 && pPk==pIdx /* Condition 2 */
1733 && onError==OE_Replace /* Condition 1 */
1734 && ( 0==(db->flags&SQLITE_RecTriggers) || /* Condition 4 */
1735 0==sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0))
danf9a12a12017-01-05 06:57:42 +00001736 && ( 0==(db->flags&SQLITE_ForeignKeys) || /* Condition 5 */
1737 (0==pTab->pFKey && 0==sqlite3FkReferences(pTab)))
drh801f55d2017-01-04 22:02:56 +00001738 ){
1739 sqlite3VdbeResolveLabel(v, addrUniqueOk);
1740 continue;
drhc6c9e152016-11-10 17:01:36 +00001741 }
dan418454c2019-01-07 15:57:35 +00001742#endif /* ifndef SQLITE_ENABLE_PREUPDATE_HOOK */
drhc6c9e152016-11-10 17:01:36 +00001743
drhb2fe7d82003-04-20 17:29:23 +00001744 /* Check to see if the new index entry will be unique */
drh4031baf2018-05-28 17:31:20 +00001745 sqlite3VdbeVerifyAbortable(v, onError);
drh26198bb2013-10-31 11:15:09 +00001746 sqlite3VdbeAddOp4Int(v, OP_NoConflict, iThisCur, addrUniqueOk,
drh688852a2014-02-17 22:40:43 +00001747 regIdx, pIdx->nKeyCol); VdbeCoverage(v);
drhf8ffb272013-11-01 17:08:56 +00001748
1749 /* Generate code to handle collisions */
drh392ee212013-11-08 16:54:56 +00001750 regR = (pIdx==pPk) ? regIdx : sqlite3GetTempRange(pParse, nPkField);
drh46d03fc2013-12-19 02:23:45 +00001751 if( isUpdate || onError==OE_Replace ){
1752 if( HasRowid(pTab) ){
1753 sqlite3VdbeAddOp2(v, OP_IdxRowid, iThisCur, regR);
1754 /* Conflict only if the rowid of the existing index entry
1755 ** is different from old-rowid */
1756 if( isUpdate ){
1757 sqlite3VdbeAddOp3(v, OP_Eq, regR, addrUniqueOk, regOldData);
drh3d77dee2014-02-19 14:20:49 +00001758 sqlite3VdbeChangeP5(v, SQLITE_NOTNULL);
drh688852a2014-02-17 22:40:43 +00001759 VdbeCoverage(v);
drhda475b82013-11-04 21:44:54 +00001760 }
drh46d03fc2013-12-19 02:23:45 +00001761 }else{
1762 int x;
1763 /* Extract the PRIMARY KEY from the end of the index entry and
1764 ** store it in registers regR..regR+nPk-1 */
drha021f122013-12-19 14:34:34 +00001765 if( pIdx!=pPk ){
drh46d03fc2013-12-19 02:23:45 +00001766 for(i=0; i<pPk->nKeyCol; i++){
drh4b92f982015-09-29 17:20:14 +00001767 assert( pPk->aiColumn[i]>=0 );
drh46d03fc2013-12-19 02:23:45 +00001768 x = sqlite3ColumnOfIndex(pIdx, pPk->aiColumn[i]);
1769 sqlite3VdbeAddOp3(v, OP_Column, iThisCur, x, regR+i);
1770 VdbeComment((v, "%s.%s", pTab->zName,
1771 pTab->aCol[pPk->aiColumn[i]].zName));
drhda475b82013-11-04 21:44:54 +00001772 }
drh46d03fc2013-12-19 02:23:45 +00001773 }
1774 if( isUpdate ){
1775 /* If currently processing the PRIMARY KEY of a WITHOUT ROWID
1776 ** table, only conflict if the new PRIMARY KEY values are actually
1777 ** different from the old.
1778 **
1779 ** For a UNIQUE index, only conflict if the PRIMARY KEY values
1780 ** of the matched index row are different from the original PRIMARY
1781 ** KEY values of this row before the update. */
1782 int addrJump = sqlite3VdbeCurrentAddr(v)+pPk->nKeyCol;
1783 int op = OP_Ne;
drh48dd1d82014-05-27 18:18:58 +00001784 int regCmp = (IsPrimaryKeyIndex(pIdx) ? regIdx : regR);
drh46d03fc2013-12-19 02:23:45 +00001785
1786 for(i=0; i<pPk->nKeyCol; i++){
1787 char *p4 = (char*)sqlite3LocateCollSeq(pParse, pPk->azColl[i]);
1788 x = pPk->aiColumn[i];
drh4b92f982015-09-29 17:20:14 +00001789 assert( x>=0 );
drh46d03fc2013-12-19 02:23:45 +00001790 if( i==(pPk->nKeyCol-1) ){
1791 addrJump = addrUniqueOk;
1792 op = OP_Eq;
1793 }
1794 sqlite3VdbeAddOp4(v, op,
1795 regOldData+1+x, addrJump, regCmp+i, p4, P4_COLLSEQ
1796 );
drh3d77dee2014-02-19 14:20:49 +00001797 sqlite3VdbeChangeP5(v, SQLITE_NOTNULL);
1798 VdbeCoverageIf(v, op==OP_Eq);
1799 VdbeCoverageIf(v, op==OP_Ne);
drh46d03fc2013-12-19 02:23:45 +00001800 }
drh26198bb2013-10-31 11:15:09 +00001801 }
drh26198bb2013-10-31 11:15:09 +00001802 }
drh11e85272013-10-26 15:40:48 +00001803 }
drhb2fe7d82003-04-20 17:29:23 +00001804
1805 /* Generate code that executes if the new index entry is not unique */
danielk1977b84f96f2005-01-20 11:32:23 +00001806 assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail
drh9eddaca2018-04-13 18:59:17 +00001807 || onError==OE_Ignore || onError==OE_Replace || onError==OE_Update );
drh9cfcf5d2002-01-29 18:41:24 +00001808 switch( onError ){
drh1c928532002-01-31 15:54:21 +00001809 case OE_Rollback:
1810 case OE_Abort:
1811 case OE_Fail: {
drh99160482018-04-18 01:34:39 +00001812 testcase( onError==OE_Rollback );
1813 testcase( onError==OE_Abort );
1814 testcase( onError==OE_Fail );
drhf9c8ce32013-11-05 13:33:55 +00001815 sqlite3UniqueConstraint(pParse, onError, pIdx);
drh9cfcf5d2002-01-29 18:41:24 +00001816 break;
1817 }
drh9eddaca2018-04-13 18:59:17 +00001818#ifndef SQLITE_OMIT_UPSERT
1819 case OE_Update: {
dan2cc00422018-04-17 18:16:10 +00001820 sqlite3UpsertDoUpdate(pParse, pUpsert, pTab, pIdx, iIdxCur+ix);
drh9eddaca2018-04-13 18:59:17 +00001821 /* Fall through */
1822 }
1823#endif
drh9cfcf5d2002-01-29 18:41:24 +00001824 case OE_Ignore: {
drh99160482018-04-18 01:34:39 +00001825 testcase( onError==OE_Ignore );
drh076e85f2015-09-03 13:46:12 +00001826 sqlite3VdbeGoto(v, ignoreDest);
drh9cfcf5d2002-01-29 18:41:24 +00001827 break;
1828 }
drh098d1682009-05-02 15:46:46 +00001829 default: {
dan2283d462009-09-08 15:55:15 +00001830 Trigger *pTrigger = 0;
drh098d1682009-05-02 15:46:46 +00001831 assert( onError==OE_Replace );
drh2938f922012-03-07 19:13:29 +00001832 if( db->flags&SQLITE_RecTriggers ){
dan2283d462009-09-08 15:55:15 +00001833 pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0);
1834 }
danfecfb312018-05-28 18:29:46 +00001835 if( pTrigger || sqlite3FkRequired(pParse, pTab, 0, 0) ){
1836 sqlite3MultiWrite(pParse);
1837 }
drh26198bb2013-10-31 11:15:09 +00001838 sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,
drhb0264ee2015-09-14 14:45:50 +00001839 regR, nPkField, 0, OE_Replace,
drh68116932017-01-07 14:47:03 +00001840 (pIdx==pPk ? ONEPASS_SINGLE : ONEPASS_OFF), iThisCur);
drh0ca3e242002-01-29 23:07:02 +00001841 seenReplace = 1;
drh9cfcf5d2002-01-29 18:41:24 +00001842 break;
1843 }
drh9cfcf5d2002-01-29 18:41:24 +00001844 }
drh7f5f3062018-04-17 20:06:24 +00001845 if( pUpIdx==pIdx ){
drh84304502018-08-14 15:12:52 +00001846 sqlite3VdbeGoto(v, upsertJump+1);
drh7f5f3062018-04-17 20:06:24 +00001847 sqlite3VdbeJumpHere(v, upsertBypass);
1848 }else{
1849 sqlite3VdbeResolveLabel(v, addrUniqueOk);
1850 }
drh392ee212013-11-08 16:54:56 +00001851 if( regR!=regIdx ) sqlite3ReleaseTempRange(pParse, regR, nPkField);
drh9cfcf5d2002-01-29 18:41:24 +00001852 }
drh84304502018-08-14 15:12:52 +00001853
1854 /* If the IPK constraint is a REPLACE, run it last */
1855 if( ipkTop ){
drh6214d932019-01-12 16:19:23 +00001856 sqlite3VdbeGoto(v, ipkTop);
drh84304502018-08-14 15:12:52 +00001857 VdbeComment((v, "Do IPK REPLACE"));
1858 sqlite3VdbeJumpHere(v, ipkBottom);
1859 }
1860
drha7c3b932019-05-07 19:13:42 +00001861 /* Generate the table record */
1862 if( HasRowid(pTab) ){
1863 int regRec = aRegIdx[ix];
1864 sqlite3VdbeAddOp3(v, OP_MakeRecord, regNewData+1, pTab->nCol, regRec);
1865 sqlite3SetMakeRecordP5(v, pTab);
1866 if( !bAffinityDone ){
1867 sqlite3TableAffinity(v, pTab, 0);
1868 }
1869 }
1870
drh4308e342013-11-11 16:55:52 +00001871 *pbMayReplace = seenReplace;
drhce60aa42013-11-08 01:09:15 +00001872 VdbeModuleComment((v, "END: GenCnstCks(%d)", seenReplace));
drh9cfcf5d2002-01-29 18:41:24 +00001873}
drh0ca3e242002-01-29 23:07:02 +00001874
drhd447dce2017-01-25 20:55:11 +00001875#ifdef SQLITE_ENABLE_NULL_TRIM
drh0ca3e242002-01-29 23:07:02 +00001876/*
drh585ce192017-01-25 14:58:27 +00001877** Change the P5 operand on the last opcode (which should be an OP_MakeRecord)
1878** to be the number of columns in table pTab that must not be NULL-trimmed.
1879**
1880** Or if no columns of pTab may be NULL-trimmed, leave P5 at zero.
1881*/
1882void sqlite3SetMakeRecordP5(Vdbe *v, Table *pTab){
1883 u16 i;
1884
1885 /* Records with omitted columns are only allowed for schema format
1886 ** version 2 and later (SQLite version 3.1.4, 2005-02-20). */
1887 if( pTab->pSchema->file_format<2 ) return;
1888
drh7e4acf72017-02-14 20:00:16 +00001889 for(i=pTab->nCol-1; i>0; i--){
1890 if( pTab->aCol[i].pDflt!=0 ) break;
1891 if( pTab->aCol[i].colFlags & COLFLAG_PRIMKEY ) break;
1892 }
1893 sqlite3VdbeChangeP5(v, i+1);
drh585ce192017-01-25 14:58:27 +00001894}
drhd447dce2017-01-25 20:55:11 +00001895#endif
drh585ce192017-01-25 14:58:27 +00001896
drh0ca3e242002-01-29 23:07:02 +00001897/*
1898** This routine generates code to finish the INSERT or UPDATE operation
danielk19774adee202004-05-08 08:23:19 +00001899** that was started by a prior call to sqlite3GenerateConstraintChecks.
drh6934fc72013-10-31 15:37:49 +00001900** A consecutive range of registers starting at regNewData contains the
drh04adf412008-01-08 18:57:50 +00001901** rowid and the content to be inserted.
drh0ca3e242002-01-29 23:07:02 +00001902**
drhb419a922002-01-30 16:17:23 +00001903** The arguments to this routine should be the same as the first six
danielk19774adee202004-05-08 08:23:19 +00001904** arguments to sqlite3GenerateConstraintChecks.
drh0ca3e242002-01-29 23:07:02 +00001905*/
danielk19774adee202004-05-08 08:23:19 +00001906void sqlite3CompleteInsertion(
drh0ca3e242002-01-29 23:07:02 +00001907 Parse *pParse, /* The parser context */
1908 Table *pTab, /* the table into which we are inserting */
drh26198bb2013-10-31 11:15:09 +00001909 int iDataCur, /* Cursor of the canonical data source */
1910 int iIdxCur, /* First index cursor */
drh6934fc72013-10-31 15:37:49 +00001911 int regNewData, /* Range of content */
drhaa9b8962008-01-08 02:57:55 +00001912 int *aRegIdx, /* Register used by each index. 0 for unused indices */
danf91c1312017-01-10 20:04:38 +00001913 int update_flags, /* True for UPDATE, False for INSERT */
danielk1977de630352009-05-04 11:42:29 +00001914 int appendBias, /* True if this is likely to be an append */
1915 int useSeekResult /* True to set the USESEEKRESULT flag on OP_[Idx]Insert */
drh0ca3e242002-01-29 23:07:02 +00001916){
drh6934fc72013-10-31 15:37:49 +00001917 Vdbe *v; /* Prepared statements under construction */
1918 Index *pIdx; /* An index being inserted or updated */
1919 u8 pik_flags; /* flag values passed to the btree insert */
drh6934fc72013-10-31 15:37:49 +00001920 int i; /* Loop counter */
drh0ca3e242002-01-29 23:07:02 +00001921
danf91c1312017-01-10 20:04:38 +00001922 assert( update_flags==0
1923 || update_flags==OPFLAG_ISUPDATE
1924 || update_flags==(OPFLAG_ISUPDATE|OPFLAG_SAVEPOSITION)
1925 );
1926
danielk19774adee202004-05-08 08:23:19 +00001927 v = sqlite3GetVdbe(pParse);
drh0ca3e242002-01-29 23:07:02 +00001928 assert( v!=0 );
drh417be792002-03-03 18:59:40 +00001929 assert( pTab->pSelect==0 ); /* This table is not a VIEW */
drhb2b9d3d2013-08-01 01:14:43 +00001930 for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
drhaa9b8962008-01-08 02:57:55 +00001931 if( aRegIdx[i]==0 ) continue;
drhb2b9d3d2013-08-01 01:14:43 +00001932 if( pIdx->pPartIdxWhere ){
1933 sqlite3VdbeAddOp2(v, OP_IsNull, aRegIdx[i], sqlite3VdbeCurrentAddr(v)+2);
drh688852a2014-02-17 22:40:43 +00001934 VdbeCoverage(v);
drhb2b9d3d2013-08-01 01:14:43 +00001935 }
dancb9a3642017-01-30 19:44:53 +00001936 pik_flags = (useSeekResult ? OPFLAG_USESEEKRESULT : 0);
drh48dd1d82014-05-27 18:18:58 +00001937 if( IsPrimaryKeyIndex(pIdx) && !HasRowid(pTab) ){
drh4308e342013-11-11 16:55:52 +00001938 assert( pParse->nested==0 );
drh6546af12013-11-04 15:23:25 +00001939 pik_flags |= OPFLAG_NCHANGE;
danf91c1312017-01-10 20:04:38 +00001940 pik_flags |= (update_flags & OPFLAG_SAVEPOSITION);
dancb9a3642017-01-30 19:44:53 +00001941#ifdef SQLITE_ENABLE_PREUPDATE_HOOK
1942 if( update_flags==0 ){
drh50ef6712019-02-22 23:29:56 +00001943 int r = sqlite3GetTempReg(pParse);
1944 sqlite3VdbeAddOp2(v, OP_Integer, 0, r);
1945 sqlite3VdbeAddOp4(v, OP_Insert,
1946 iIdxCur+i, aRegIdx[i], r, (char*)pTab, P4_TABLE
dancb9a3642017-01-30 19:44:53 +00001947 );
1948 sqlite3VdbeChangeP5(v, OPFLAG_ISNOOP);
drh50ef6712019-02-22 23:29:56 +00001949 sqlite3ReleaseTempReg(pParse, r);
dancb9a3642017-01-30 19:44:53 +00001950 }
1951#endif
danielk1977de630352009-05-04 11:42:29 +00001952 }
dancb9a3642017-01-30 19:44:53 +00001953 sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iIdxCur+i, aRegIdx[i],
1954 aRegIdx[i]+1,
1955 pIdx->uniqNotNull ? pIdx->nKeyCol: pIdx->nColumn);
drh9b34abe2016-01-16 15:12:35 +00001956 sqlite3VdbeChangeP5(v, pik_flags);
drh0ca3e242002-01-29 23:07:02 +00001957 }
drhec95c442013-10-23 01:57:32 +00001958 if( !HasRowid(pTab) ) return;
drh4794f732004-11-05 17:17:50 +00001959 if( pParse->nested ){
1960 pik_flags = 0;
1961 }else{
danielk197794eb6a12005-12-15 15:22:08 +00001962 pik_flags = OPFLAG_NCHANGE;
danf91c1312017-01-10 20:04:38 +00001963 pik_flags |= (update_flags?update_flags:OPFLAG_LASTROWID);
drh4794f732004-11-05 17:17:50 +00001964 }
drhe4d90812007-03-29 05:51:49 +00001965 if( appendBias ){
1966 pik_flags |= OPFLAG_APPEND;
1967 }
danielk1977de630352009-05-04 11:42:29 +00001968 if( useSeekResult ){
1969 pik_flags |= OPFLAG_USESEEKRESULT;
1970 }
drha7c3b932019-05-07 19:13:42 +00001971 sqlite3VdbeAddOp3(v, OP_Insert, iDataCur, aRegIdx[i], regNewData);
danielk197794eb6a12005-12-15 15:22:08 +00001972 if( !pParse->nested ){
drhf14b7fb2016-12-07 21:35:55 +00001973 sqlite3VdbeAppendP4(v, pTab, P4_TABLE);
danielk197794eb6a12005-12-15 15:22:08 +00001974 }
drhb7654112008-01-12 12:48:07 +00001975 sqlite3VdbeChangeP5(v, pik_flags);
drh0ca3e242002-01-29 23:07:02 +00001976}
drhcd446902004-02-24 01:05:31 +00001977
1978/*
drh26198bb2013-10-31 11:15:09 +00001979** Allocate cursors for the pTab table and all its indices and generate
1980** code to open and initialized those cursors.
drhaa9b8962008-01-08 02:57:55 +00001981**
drh26198bb2013-10-31 11:15:09 +00001982** The cursor for the object that contains the complete data (normally
1983** the table itself, but the PRIMARY KEY index in the case of a WITHOUT
1984** ROWID table) is returned in *piDataCur. The first index cursor is
1985** returned in *piIdxCur. The number of indices is returned.
1986**
1987** Use iBase as the first cursor (either the *piDataCur for rowid tables
1988** or the first index for WITHOUT ROWID tables) if it is non-negative.
1989** If iBase is negative, then allocate the next available cursor.
1990**
1991** For a rowid table, *piDataCur will be exactly one less than *piIdxCur.
1992** For a WITHOUT ROWID table, *piDataCur will be somewhere in the range
1993** of *piIdxCurs, depending on where the PRIMARY KEY index appears on the
1994** pTab->pIndex list.
drhb6b4b792014-08-21 14:10:23 +00001995**
1996** If pTab is a virtual table, then this routine is a no-op and the
1997** *piDataCur and *piIdxCur values are left uninitialized.
drhcd446902004-02-24 01:05:31 +00001998*/
drhaa9b8962008-01-08 02:57:55 +00001999int sqlite3OpenTableAndIndices(
drh290c1942004-08-21 17:54:45 +00002000 Parse *pParse, /* Parsing context */
2001 Table *pTab, /* Table to be opened */
drh26198bb2013-10-31 11:15:09 +00002002 int op, /* OP_OpenRead or OP_OpenWrite */
drhb89aeb62016-01-27 15:49:32 +00002003 u8 p5, /* P5 value for OP_Open* opcodes (except on WITHOUT ROWID) */
drh26198bb2013-10-31 11:15:09 +00002004 int iBase, /* Use this for the table cursor, if there is one */
drh6a534992013-11-16 20:13:39 +00002005 u8 *aToOpen, /* If not NULL: boolean for each table and index */
drh26198bb2013-10-31 11:15:09 +00002006 int *piDataCur, /* Write the database source cursor number here */
2007 int *piIdxCur /* Write the first index cursor number here */
drh290c1942004-08-21 17:54:45 +00002008){
drhcd446902004-02-24 01:05:31 +00002009 int i;
drh4cbdda92006-06-14 19:00:20 +00002010 int iDb;
drh6a534992013-11-16 20:13:39 +00002011 int iDataCur;
drhcd446902004-02-24 01:05:31 +00002012 Index *pIdx;
drh4cbdda92006-06-14 19:00:20 +00002013 Vdbe *v;
2014
drh26198bb2013-10-31 11:15:09 +00002015 assert( op==OP_OpenRead || op==OP_OpenWrite );
danfd261ec2015-10-22 20:54:33 +00002016 assert( op==OP_OpenWrite || p5==0 );
drh26198bb2013-10-31 11:15:09 +00002017 if( IsVirtual(pTab) ){
drhb6b4b792014-08-21 14:10:23 +00002018 /* This routine is a no-op for virtual tables. Leave the output
2019 ** variables *piDataCur and *piIdxCur uninitialized so that valgrind
2020 ** can detect if they are used by mistake in the caller. */
drh26198bb2013-10-31 11:15:09 +00002021 return 0;
2022 }
drh4cbdda92006-06-14 19:00:20 +00002023 iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema);
2024 v = sqlite3GetVdbe(pParse);
drhcd446902004-02-24 01:05:31 +00002025 assert( v!=0 );
drh26198bb2013-10-31 11:15:09 +00002026 if( iBase<0 ) iBase = pParse->nTab;
drh6a534992013-11-16 20:13:39 +00002027 iDataCur = iBase++;
2028 if( piDataCur ) *piDataCur = iDataCur;
2029 if( HasRowid(pTab) && (aToOpen==0 || aToOpen[0]) ){
2030 sqlite3OpenTable(pParse, iDataCur, iDb, pTab, op);
drh6fbe41a2013-10-30 20:22:55 +00002031 }else{
drh26198bb2013-10-31 11:15:09 +00002032 sqlite3TableLock(pParse, iDb, pTab->tnum, op==OP_OpenWrite, pTab->zName);
drh6fbe41a2013-10-30 20:22:55 +00002033 }
drh6a534992013-11-16 20:13:39 +00002034 if( piIdxCur ) *piIdxCur = iBase;
drh26198bb2013-10-31 11:15:09 +00002035 for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
drh26198bb2013-10-31 11:15:09 +00002036 int iIdxCur = iBase++;
danielk1977da184232006-01-05 11:34:32 +00002037 assert( pIdx->pSchema==pTab->pSchema );
dan61441c32016-08-26 12:00:50 +00002038 if( IsPrimaryKeyIndex(pIdx) && !HasRowid(pTab) ){
2039 if( piDataCur ) *piDataCur = iIdxCur;
2040 p5 = 0;
2041 }
drh6a534992013-11-16 20:13:39 +00002042 if( aToOpen==0 || aToOpen[i+1] ){
2043 sqlite3VdbeAddOp3(v, op, iIdxCur, pIdx->tnum, iDb);
2044 sqlite3VdbeSetP4KeyInfo(pParse, pIdx);
drhb89aeb62016-01-27 15:49:32 +00002045 sqlite3VdbeChangeP5(v, p5);
dan61441c32016-08-26 12:00:50 +00002046 VdbeComment((v, "%s", pIdx->zName));
drhb89aeb62016-01-27 15:49:32 +00002047 }
drhcd446902004-02-24 01:05:31 +00002048 }
drh26198bb2013-10-31 11:15:09 +00002049 if( iBase>pParse->nTab ) pParse->nTab = iBase;
2050 return i;
drhcd446902004-02-24 01:05:31 +00002051}
drh9d9cf222007-02-13 15:01:11 +00002052
drh91c58e22007-03-27 12:04:04 +00002053
2054#ifdef SQLITE_TEST
2055/*
2056** The following global variable is incremented whenever the
2057** transfer optimization is used. This is used for testing
2058** purposes only - to make sure the transfer optimization really
peter.d.reid60ec9142014-09-06 16:39:46 +00002059** is happening when it is supposed to.
drh91c58e22007-03-27 12:04:04 +00002060*/
2061int sqlite3_xferopt_count;
2062#endif /* SQLITE_TEST */
2063
2064
drh9d9cf222007-02-13 15:01:11 +00002065#ifndef SQLITE_OMIT_XFER_OPT
2066/*
drh9d9cf222007-02-13 15:01:11 +00002067** Check to see if index pSrc is compatible as a source of data
2068** for index pDest in an insert transfer optimization. The rules
2069** for a compatible index:
2070**
2071** * The index is over the same set of columns
2072** * The same DESC and ASC markings occurs on all columns
2073** * The same onError processing (OE_Abort, OE_Ignore, etc)
2074** * The same collating sequence on each column
drhb2b9d3d2013-08-01 01:14:43 +00002075** * The index has the exact same WHERE clause
drh9d9cf222007-02-13 15:01:11 +00002076*/
2077static int xferCompatibleIndex(Index *pDest, Index *pSrc){
2078 int i;
2079 assert( pDest && pSrc );
2080 assert( pDest->pTable!=pSrc->pTable );
drhbbbdc832013-10-22 18:01:40 +00002081 if( pDest->nKeyCol!=pSrc->nKeyCol ){
drh9d9cf222007-02-13 15:01:11 +00002082 return 0; /* Different number of columns */
2083 }
2084 if( pDest->onError!=pSrc->onError ){
2085 return 0; /* Different conflict resolution strategies */
2086 }
drhbbbdc832013-10-22 18:01:40 +00002087 for(i=0; i<pSrc->nKeyCol; i++){
drh9d9cf222007-02-13 15:01:11 +00002088 if( pSrc->aiColumn[i]!=pDest->aiColumn[i] ){
2089 return 0; /* Different columns indexed */
2090 }
drh4b92f982015-09-29 17:20:14 +00002091 if( pSrc->aiColumn[i]==XN_EXPR ){
drh1f9ca2c2015-08-25 16:57:52 +00002092 assert( pSrc->aColExpr!=0 && pDest->aColExpr!=0 );
dan5aa550c2017-06-24 18:10:29 +00002093 if( sqlite3ExprCompare(0, pSrc->aColExpr->a[i].pExpr,
drh1f9ca2c2015-08-25 16:57:52 +00002094 pDest->aColExpr->a[i].pExpr, -1)!=0 ){
2095 return 0; /* Different expressions in the index */
2096 }
2097 }
drh9d9cf222007-02-13 15:01:11 +00002098 if( pSrc->aSortOrder[i]!=pDest->aSortOrder[i] ){
2099 return 0; /* Different sort orders */
2100 }
drh0472af92015-12-30 15:18:16 +00002101 if( sqlite3_stricmp(pSrc->azColl[i],pDest->azColl[i])!=0 ){
drh60a713c2008-01-21 16:22:45 +00002102 return 0; /* Different collating sequences */
drh9d9cf222007-02-13 15:01:11 +00002103 }
2104 }
dan5aa550c2017-06-24 18:10:29 +00002105 if( sqlite3ExprCompare(0, pSrc->pPartIdxWhere, pDest->pPartIdxWhere, -1) ){
drhb2b9d3d2013-08-01 01:14:43 +00002106 return 0; /* Different WHERE clauses */
2107 }
drh9d9cf222007-02-13 15:01:11 +00002108
2109 /* If no test above fails then the indices must be compatible */
2110 return 1;
2111}
2112
2113/*
2114** Attempt the transfer optimization on INSERTs of the form
2115**
2116** INSERT INTO tab1 SELECT * FROM tab2;
2117**
drhccdf1ba2011-11-04 14:36:02 +00002118** The xfer optimization transfers raw records from tab2 over to tab1.
peter.d.reid60ec9142014-09-06 16:39:46 +00002119** Columns are not decoded and reassembled, which greatly improves
drhccdf1ba2011-11-04 14:36:02 +00002120** performance. Raw index records are transferred in the same way.
drh9d9cf222007-02-13 15:01:11 +00002121**
drhccdf1ba2011-11-04 14:36:02 +00002122** The xfer optimization is only attempted if tab1 and tab2 are compatible.
2123** There are lots of rules for determining compatibility - see comments
2124** embedded in the code for details.
drh9d9cf222007-02-13 15:01:11 +00002125**
drhccdf1ba2011-11-04 14:36:02 +00002126** This routine returns TRUE if the optimization is guaranteed to be used.
2127** Sometimes the xfer optimization will only work if the destination table
2128** is empty - a factor that can only be determined at run-time. In that
2129** case, this routine generates code for the xfer optimization but also
2130** does a test to see if the destination table is empty and jumps over the
2131** xfer optimization code if the test fails. In that case, this routine
2132** returns FALSE so that the caller will know to go ahead and generate
2133** an unoptimized transfer. This routine also returns FALSE if there
2134** is no chance that the xfer optimization can be applied.
drh9d9cf222007-02-13 15:01:11 +00002135**
drhccdf1ba2011-11-04 14:36:02 +00002136** This optimization is particularly useful at making VACUUM run faster.
drh9d9cf222007-02-13 15:01:11 +00002137*/
2138static int xferOptimization(
2139 Parse *pParse, /* Parser context */
2140 Table *pDest, /* The table we are inserting into */
2141 Select *pSelect, /* A SELECT statement to use as the data source */
2142 int onError, /* How to handle constraint errors */
2143 int iDbDest /* The database of pDest */
2144){
dane34162b2015-04-01 18:20:25 +00002145 sqlite3 *db = pParse->db;
drh9d9cf222007-02-13 15:01:11 +00002146 ExprList *pEList; /* The result set of the SELECT */
2147 Table *pSrc; /* The table in the FROM clause of SELECT */
2148 Index *pSrcIdx, *pDestIdx; /* Source and destination indices */
2149 struct SrcList_item *pItem; /* An element of pSelect->pSrc */
2150 int i; /* Loop counter */
2151 int iDbSrc; /* The database of pSrc */
2152 int iSrc, iDest; /* Cursors from source and destination */
2153 int addr1, addr2; /* Loop addresses */
drhda475b82013-11-04 21:44:54 +00002154 int emptyDestTest = 0; /* Address of test for empty pDest */
2155 int emptySrcTest = 0; /* Address of test for empty pSrc */
drh9d9cf222007-02-13 15:01:11 +00002156 Vdbe *v; /* The VDBE we are building */
drh6a288a32008-01-07 19:20:24 +00002157 int regAutoinc; /* Memory register used by AUTOINC */
drhf33c9fa2007-04-10 18:17:55 +00002158 int destHasUniqueIdx = 0; /* True if pDest has a UNIQUE index */
drhb7654112008-01-12 12:48:07 +00002159 int regData, regRowid; /* Registers holding data and rowid */
drh9d9cf222007-02-13 15:01:11 +00002160
2161 if( pSelect==0 ){
2162 return 0; /* Must be of the form INSERT INTO ... SELECT ... */
2163 }
danebbf08a2014-01-18 08:27:02 +00002164 if( pParse->pWith || pSelect->pWith ){
2165 /* Do not attempt to process this query if there are an WITH clauses
2166 ** attached to it. Proceeding may generate a false "no such table: xxx"
2167 ** error if pSelect reads from a CTE named "xxx". */
2168 return 0;
2169 }
danielk19772f886d12009-02-28 10:47:41 +00002170 if( sqlite3TriggerList(pParse, pDest) ){
drh9d9cf222007-02-13 15:01:11 +00002171 return 0; /* tab1 must not have triggers */
2172 }
2173#ifndef SQLITE_OMIT_VIRTUALTABLE
drh44266ec2017-02-16 14:48:08 +00002174 if( IsVirtual(pDest) ){
drh9d9cf222007-02-13 15:01:11 +00002175 return 0; /* tab1 must not be a virtual table */
2176 }
2177#endif
2178 if( onError==OE_Default ){
drhe7224a02011-11-04 00:23:53 +00002179 if( pDest->iPKey>=0 ) onError = pDest->keyConf;
2180 if( onError==OE_Default ) onError = OE_Abort;
drh9d9cf222007-02-13 15:01:11 +00002181 }
danielk19775ce240a2007-09-03 17:30:06 +00002182 assert(pSelect->pSrc); /* allocated even if there is no FROM clause */
drh9d9cf222007-02-13 15:01:11 +00002183 if( pSelect->pSrc->nSrc!=1 ){
2184 return 0; /* FROM clause must have exactly one term */
2185 }
2186 if( pSelect->pSrc->a[0].pSelect ){
2187 return 0; /* FROM clause cannot contain a subquery */
2188 }
2189 if( pSelect->pWhere ){
2190 return 0; /* SELECT may not have a WHERE clause */
2191 }
2192 if( pSelect->pOrderBy ){
2193 return 0; /* SELECT may not have an ORDER BY clause */
2194 }
drh8103b7d2007-02-24 13:23:51 +00002195 /* Do not need to test for a HAVING clause. If HAVING is present but
2196 ** there is no ORDER BY, we will get an error. */
drh9d9cf222007-02-13 15:01:11 +00002197 if( pSelect->pGroupBy ){
2198 return 0; /* SELECT may not have a GROUP BY clause */
2199 }
2200 if( pSelect->pLimit ){
2201 return 0; /* SELECT may not have a LIMIT clause */
2202 }
drh9d9cf222007-02-13 15:01:11 +00002203 if( pSelect->pPrior ){
2204 return 0; /* SELECT may not be a compound query */
2205 }
drh7d10d5a2008-08-20 16:35:10 +00002206 if( pSelect->selFlags & SF_Distinct ){
drh9d9cf222007-02-13 15:01:11 +00002207 return 0; /* SELECT may not be DISTINCT */
2208 }
2209 pEList = pSelect->pEList;
2210 assert( pEList!=0 );
2211 if( pEList->nExpr!=1 ){
2212 return 0; /* The result set must have exactly one column */
2213 }
2214 assert( pEList->a[0].pExpr );
drh1a1d3cd2015-11-19 16:33:31 +00002215 if( pEList->a[0].pExpr->op!=TK_ASTERISK ){
drh9d9cf222007-02-13 15:01:11 +00002216 return 0; /* The result set must be the special operator "*" */
2217 }
2218
2219 /* At this point we have established that the statement is of the
2220 ** correct syntactic form to participate in this optimization. Now
2221 ** we have to check the semantics.
2222 */
2223 pItem = pSelect->pSrc->a;
dan41fb5cd2012-10-04 19:33:00 +00002224 pSrc = sqlite3LocateTableItem(pParse, 0, pItem);
drh9d9cf222007-02-13 15:01:11 +00002225 if( pSrc==0 ){
2226 return 0; /* FROM clause does not contain a real table */
2227 }
drh21908b22019-01-17 20:19:35 +00002228 if( pSrc->tnum==pDest->tnum && pSrc->pSchema==pDest->pSchema ){
2229 testcase( pSrc!=pDest ); /* Possible due to bad sqlite_master.rootpage */
drh9d9cf222007-02-13 15:01:11 +00002230 return 0; /* tab1 and tab2 may not be the same table */
2231 }
drh55548272013-10-23 16:03:07 +00002232 if( HasRowid(pDest)!=HasRowid(pSrc) ){
2233 return 0; /* source and destination must both be WITHOUT ROWID or not */
2234 }
drh9d9cf222007-02-13 15:01:11 +00002235#ifndef SQLITE_OMIT_VIRTUALTABLE
drh44266ec2017-02-16 14:48:08 +00002236 if( IsVirtual(pSrc) ){
drh9d9cf222007-02-13 15:01:11 +00002237 return 0; /* tab2 must not be a virtual table */
2238 }
2239#endif
2240 if( pSrc->pSelect ){
2241 return 0; /* tab2 may not be a view */
2242 }
2243 if( pDest->nCol!=pSrc->nCol ){
2244 return 0; /* Number of columns must be the same in tab1 and tab2 */
2245 }
2246 if( pDest->iPKey!=pSrc->iPKey ){
2247 return 0; /* Both tables must have the same INTEGER PRIMARY KEY */
2248 }
2249 for(i=0; i<pDest->nCol; i++){
dan9940e2a2014-04-26 14:07:57 +00002250 Column *pDestCol = &pDest->aCol[i];
2251 Column *pSrcCol = &pSrc->aCol[i];
danba68f8f2015-11-19 16:46:46 +00002252#ifdef SQLITE_ENABLE_HIDDEN_COLUMNS
drh8257aa82017-07-26 19:59:13 +00002253 if( (db->mDbFlags & DBFLAG_Vacuum)==0
danaaea3142015-11-19 18:09:05 +00002254 && (pDestCol->colFlags | pSrcCol->colFlags) & COLFLAG_HIDDEN
2255 ){
danba68f8f2015-11-19 16:46:46 +00002256 return 0; /* Neither table may have __hidden__ columns */
2257 }
2258#endif
dan9940e2a2014-04-26 14:07:57 +00002259 if( pDestCol->affinity!=pSrcCol->affinity ){
drh9d9cf222007-02-13 15:01:11 +00002260 return 0; /* Affinity must be the same on all columns */
2261 }
drh0472af92015-12-30 15:18:16 +00002262 if( sqlite3_stricmp(pDestCol->zColl, pSrcCol->zColl)!=0 ){
drh9d9cf222007-02-13 15:01:11 +00002263 return 0; /* Collating sequence must be the same on all columns */
2264 }
dan9940e2a2014-04-26 14:07:57 +00002265 if( pDestCol->notNull && !pSrcCol->notNull ){
drh9d9cf222007-02-13 15:01:11 +00002266 return 0; /* tab2 must be NOT NULL if tab1 is */
2267 }
drh453e0262014-04-26 17:52:08 +00002268 /* Default values for second and subsequent columns need to match. */
drh94fa9c42016-02-27 21:16:04 +00002269 if( i>0 ){
2270 assert( pDestCol->pDflt==0 || pDestCol->pDflt->op==TK_SPAN );
2271 assert( pSrcCol->pDflt==0 || pSrcCol->pDflt->op==TK_SPAN );
2272 if( (pDestCol->pDflt==0)!=(pSrcCol->pDflt==0)
2273 || (pDestCol->pDflt && strcmp(pDestCol->pDflt->u.zToken,
2274 pSrcCol->pDflt->u.zToken)!=0)
2275 ){
2276 return 0; /* Default values must be the same for all columns */
2277 }
dan9940e2a2014-04-26 14:07:57 +00002278 }
drh9d9cf222007-02-13 15:01:11 +00002279 }
2280 for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){
drh5f1d1d92014-07-31 22:59:04 +00002281 if( IsUniqueIndex(pDestIdx) ){
drhf33c9fa2007-04-10 18:17:55 +00002282 destHasUniqueIdx = 1;
2283 }
drh9d9cf222007-02-13 15:01:11 +00002284 for(pSrcIdx=pSrc->pIndex; pSrcIdx; pSrcIdx=pSrcIdx->pNext){
2285 if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break;
2286 }
2287 if( pSrcIdx==0 ){
2288 return 0; /* pDestIdx has no corresponding index in pSrc */
2289 }
drhe3bd2322019-04-05 16:38:12 +00002290 if( pSrcIdx->tnum==pDestIdx->tnum && pSrc->pSchema==pDest->pSchema
2291 && sqlite3FaultSim(411)==SQLITE_OK ){
2292 /* The sqlite3FaultSim() call allows this corruption test to be
2293 ** bypassed during testing, in order to exercise other corruption tests
2294 ** further downstream. */
drh86223e82019-04-05 15:44:55 +00002295 return 0; /* Corrupt schema - two indexes on the same btree */
2296 }
drh9d9cf222007-02-13 15:01:11 +00002297 }
drh7fc2f412007-03-29 00:08:24 +00002298#ifndef SQLITE_OMIT_CHECK
drh619a1302013-08-01 13:04:46 +00002299 if( pDest->pCheck && sqlite3ExprListCompare(pSrc->pCheck,pDest->pCheck,-1) ){
drh8103b7d2007-02-24 13:23:51 +00002300 return 0; /* Tables have different CHECK constraints. Ticket #2252 */
2301 }
drh7fc2f412007-03-29 00:08:24 +00002302#endif
drh713de342011-04-24 22:56:07 +00002303#ifndef SQLITE_OMIT_FOREIGN_KEY
2304 /* Disallow the transfer optimization if the destination table constains
2305 ** any foreign key constraints. This is more restrictive than necessary.
2306 ** But the main beneficiary of the transfer optimization is the VACUUM
2307 ** command, and the VACUUM command disables foreign key constraints. So
2308 ** the extra complication to make this rule less restrictive is probably
2309 ** not worth the effort. Ticket [6284df89debdfa61db8073e062908af0c9b6118e]
2310 */
dane34162b2015-04-01 18:20:25 +00002311 if( (db->flags & SQLITE_ForeignKeys)!=0 && pDest->pFKey!=0 ){
drh713de342011-04-24 22:56:07 +00002312 return 0;
2313 }
2314#endif
dane34162b2015-04-01 18:20:25 +00002315 if( (db->flags & SQLITE_CountRows)!=0 ){
drhccdf1ba2011-11-04 14:36:02 +00002316 return 0; /* xfer opt does not play well with PRAGMA count_changes */
dan16961242011-09-30 12:01:01 +00002317 }
drh9d9cf222007-02-13 15:01:11 +00002318
drhccdf1ba2011-11-04 14:36:02 +00002319 /* If we get this far, it means that the xfer optimization is at
2320 ** least a possibility, though it might only work if the destination
2321 ** table (tab1) is initially empty.
drh9d9cf222007-02-13 15:01:11 +00002322 */
drhdd735212007-02-24 13:53:05 +00002323#ifdef SQLITE_TEST
2324 sqlite3_xferopt_count++;
2325#endif
dane34162b2015-04-01 18:20:25 +00002326 iDbSrc = sqlite3SchemaToIndex(db, pSrc->pSchema);
drh9d9cf222007-02-13 15:01:11 +00002327 v = sqlite3GetVdbe(pParse);
drhf53e9b52007-08-29 13:45:58 +00002328 sqlite3CodeVerifySchema(pParse, iDbSrc);
drh9d9cf222007-02-13 15:01:11 +00002329 iSrc = pParse->nTab++;
2330 iDest = pParse->nTab++;
drh6a288a32008-01-07 19:20:24 +00002331 regAutoinc = autoIncBegin(pParse, iDbDest, pDest);
drhb7654112008-01-12 12:48:07 +00002332 regData = sqlite3GetTempReg(pParse);
2333 regRowid = sqlite3GetTempReg(pParse);
drh9d9cf222007-02-13 15:01:11 +00002334 sqlite3OpenTable(pParse, iDest, iDbDest, pDest, OP_OpenWrite);
dan427ebba2013-11-05 16:39:31 +00002335 assert( HasRowid(pDest) || destHasUniqueIdx );
drh8257aa82017-07-26 19:59:13 +00002336 if( (db->mDbFlags & DBFLAG_Vacuum)==0 && (
dane34162b2015-04-01 18:20:25 +00002337 (pDest->iPKey<0 && pDest->pIndex!=0) /* (1) */
drh9d9cf222007-02-13 15:01:11 +00002338 || destHasUniqueIdx /* (2) */
2339 || (onError!=OE_Abort && onError!=OE_Rollback) /* (3) */
dane34162b2015-04-01 18:20:25 +00002340 )){
drh9d9cf222007-02-13 15:01:11 +00002341 /* In some circumstances, we are able to run the xfer optimization
dane34162b2015-04-01 18:20:25 +00002342 ** only if the destination table is initially empty. Unless the
drh8257aa82017-07-26 19:59:13 +00002343 ** DBFLAG_Vacuum flag is set, this block generates code to make
2344 ** that determination. If DBFLAG_Vacuum is set, then the destination
dane34162b2015-04-01 18:20:25 +00002345 ** table is always empty.
2346 **
2347 ** Conditions under which the destination must be empty:
drhbd36ba62007-03-31 13:00:26 +00002348 **
drh9d9cf222007-02-13 15:01:11 +00002349 ** (1) There is no INTEGER PRIMARY KEY but there are indices.
2350 ** (If the destination is not initially empty, the rowid fields
2351 ** of index entries might need to change.)
2352 **
2353 ** (2) The destination has a unique index. (The xfer optimization
drhbd36ba62007-03-31 13:00:26 +00002354 ** is unable to test uniqueness.)
2355 **
2356 ** (3) onError is something other than OE_Abort and OE_Rollback.
drhf33c9fa2007-04-10 18:17:55 +00002357 */
drh688852a2014-02-17 22:40:43 +00002358 addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iDest, 0); VdbeCoverage(v);
drh2991ba02015-09-02 18:19:00 +00002359 emptyDestTest = sqlite3VdbeAddOp0(v, OP_Goto);
drh66a51672008-01-03 00:01:23 +00002360 sqlite3VdbeJumpHere(v, addr1);
drh9d9cf222007-02-13 15:01:11 +00002361 }
drh55548272013-10-23 16:03:07 +00002362 if( HasRowid(pSrc) ){
drhc9b9dea2016-11-15 02:46:39 +00002363 u8 insFlags;
drh55548272013-10-23 16:03:07 +00002364 sqlite3OpenTable(pParse, iSrc, iDbSrc, pSrc, OP_OpenRead);
drh688852a2014-02-17 22:40:43 +00002365 emptySrcTest = sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0); VdbeCoverage(v);
drh55548272013-10-23 16:03:07 +00002366 if( pDest->iPKey>=0 ){
2367 addr1 = sqlite3VdbeAddOp2(v, OP_Rowid, iSrc, regRowid);
drh4031baf2018-05-28 17:31:20 +00002368 sqlite3VdbeVerifyAbortable(v, onError);
drh55548272013-10-23 16:03:07 +00002369 addr2 = sqlite3VdbeAddOp3(v, OP_NotExists, iDest, 0, regRowid);
drh688852a2014-02-17 22:40:43 +00002370 VdbeCoverage(v);
drhf9c8ce32013-11-05 13:33:55 +00002371 sqlite3RowidConstraint(pParse, onError, pDest);
drh55548272013-10-23 16:03:07 +00002372 sqlite3VdbeJumpHere(v, addr2);
2373 autoIncStep(pParse, regAutoinc, regRowid);
drh4e61e882019-04-04 14:00:23 +00002374 }else if( pDest->pIndex==0 && !(db->mDbFlags & DBFLAG_VacuumInto) ){
drh55548272013-10-23 16:03:07 +00002375 addr1 = sqlite3VdbeAddOp2(v, OP_NewRowid, iDest, regRowid);
2376 }else{
2377 addr1 = sqlite3VdbeAddOp2(v, OP_Rowid, iSrc, regRowid);
2378 assert( (pDest->tabFlags & TF_Autoincrement)==0 );
2379 }
drhe7b554d2017-01-09 15:44:25 +00002380 sqlite3VdbeAddOp3(v, OP_RowData, iSrc, regData, 1);
drh8257aa82017-07-26 19:59:13 +00002381 if( db->mDbFlags & DBFLAG_Vacuum ){
drh86b40df2017-08-01 19:53:43 +00002382 sqlite3VdbeAddOp1(v, OP_SeekEnd, iDest);
drhc9b9dea2016-11-15 02:46:39 +00002383 insFlags = OPFLAG_NCHANGE|OPFLAG_LASTROWID|
2384 OPFLAG_APPEND|OPFLAG_USESEEKRESULT;
2385 }else{
2386 insFlags = OPFLAG_NCHANGE|OPFLAG_LASTROWID|OPFLAG_APPEND;
2387 }
drh9b34abe2016-01-16 15:12:35 +00002388 sqlite3VdbeAddOp4(v, OP_Insert, iDest, regData, regRowid,
drh20f272c2016-01-20 11:33:37 +00002389 (char*)pDest, P4_TABLE);
drhc9b9dea2016-11-15 02:46:39 +00002390 sqlite3VdbeChangeP5(v, insFlags);
drh688852a2014-02-17 22:40:43 +00002391 sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1); VdbeCoverage(v);
drh55548272013-10-23 16:03:07 +00002392 sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0);
2393 sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);
drh95bad4c2007-03-28 18:04:10 +00002394 }else{
drhda475b82013-11-04 21:44:54 +00002395 sqlite3TableLock(pParse, iDbDest, pDest->tnum, 1, pDest->zName);
2396 sqlite3TableLock(pParse, iDbSrc, pSrc->tnum, 0, pSrc->zName);
drh95bad4c2007-03-28 18:04:10 +00002397 }
drh9d9cf222007-02-13 15:01:11 +00002398 for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){
drh41b9ca22015-07-28 18:53:37 +00002399 u8 idxInsFlags = 0;
drh1b7ecbb2009-05-03 01:00:59 +00002400 for(pSrcIdx=pSrc->pIndex; ALWAYS(pSrcIdx); pSrcIdx=pSrcIdx->pNext){
drh9d9cf222007-02-13 15:01:11 +00002401 if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break;
2402 }
2403 assert( pSrcIdx );
drh2ec2fb22013-11-06 19:59:23 +00002404 sqlite3VdbeAddOp3(v, OP_OpenRead, iSrc, pSrcIdx->tnum, iDbSrc);
2405 sqlite3VdbeSetP4KeyInfo(pParse, pSrcIdx);
drhd4e70eb2008-01-02 00:34:36 +00002406 VdbeComment((v, "%s", pSrcIdx->zName));
drh2ec2fb22013-11-06 19:59:23 +00002407 sqlite3VdbeAddOp3(v, OP_OpenWrite, iDest, pDestIdx->tnum, iDbDest);
2408 sqlite3VdbeSetP4KeyInfo(pParse, pDestIdx);
dan59885722013-10-04 18:17:18 +00002409 sqlite3VdbeChangeP5(v, OPFLAG_BULKCSR);
danielk1977207872a2008-01-03 07:54:23 +00002410 VdbeComment((v, "%s", pDestIdx->zName));
drh688852a2014-02-17 22:40:43 +00002411 addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0); VdbeCoverage(v);
drhe7b554d2017-01-09 15:44:25 +00002412 sqlite3VdbeAddOp3(v, OP_RowData, iSrc, regData, 1);
drh8257aa82017-07-26 19:59:13 +00002413 if( db->mDbFlags & DBFLAG_Vacuum ){
dane34162b2015-04-01 18:20:25 +00002414 /* This INSERT command is part of a VACUUM operation, which guarantees
2415 ** that the destination table is empty. If all indexed columns use
2416 ** collation sequence BINARY, then it can also be assumed that the
2417 ** index will be populated by inserting keys in strictly sorted
2418 ** order. In this case, instead of seeking within the b-tree as part
drh86b40df2017-08-01 19:53:43 +00002419 ** of every OP_IdxInsert opcode, an OP_SeekEnd is added before the
dane34162b2015-04-01 18:20:25 +00002420 ** OP_IdxInsert to seek to the point within the b-tree where each key
2421 ** should be inserted. This is faster.
2422 **
2423 ** If any of the indexed columns use a collation sequence other than
2424 ** BINARY, this optimization is disabled. This is because the user
2425 ** might change the definition of a collation sequence and then run
2426 ** a VACUUM command. In that case keys may not be written in strictly
2427 ** sorted order. */
dane34162b2015-04-01 18:20:25 +00002428 for(i=0; i<pSrcIdx->nColumn; i++){
drhf19aa5f2015-12-30 16:51:20 +00002429 const char *zColl = pSrcIdx->azColl[i];
drhf19aa5f2015-12-30 16:51:20 +00002430 if( sqlite3_stricmp(sqlite3StrBINARY, zColl) ) break;
dane34162b2015-04-01 18:20:25 +00002431 }
2432 if( i==pSrcIdx->nColumn ){
drh41b9ca22015-07-28 18:53:37 +00002433 idxInsFlags = OPFLAG_USESEEKRESULT;
drh86b40df2017-08-01 19:53:43 +00002434 sqlite3VdbeAddOp1(v, OP_SeekEnd, iDest);
dane34162b2015-04-01 18:20:25 +00002435 }
2436 }
drh9df385e2019-02-19 13:08:35 +00002437 if( !HasRowid(pSrc) && pDestIdx->idxType==SQLITE_IDXTYPE_PRIMARYKEY ){
drh41b9ca22015-07-28 18:53:37 +00002438 idxInsFlags |= OPFLAG_NCHANGE;
2439 }
drh9b4eaeb2016-11-09 00:10:33 +00002440 sqlite3VdbeAddOp2(v, OP_IdxInsert, iDest, regData);
2441 sqlite3VdbeChangeP5(v, idxInsFlags|OPFLAG_APPEND);
drh688852a2014-02-17 22:40:43 +00002442 sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1+1); VdbeCoverage(v);
drh9d9cf222007-02-13 15:01:11 +00002443 sqlite3VdbeJumpHere(v, addr1);
drh55548272013-10-23 16:03:07 +00002444 sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0);
2445 sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);
drh9d9cf222007-02-13 15:01:11 +00002446 }
drhaceb31b2014-02-08 01:40:27 +00002447 if( emptySrcTest ) sqlite3VdbeJumpHere(v, emptySrcTest);
drhb7654112008-01-12 12:48:07 +00002448 sqlite3ReleaseTempReg(pParse, regRowid);
2449 sqlite3ReleaseTempReg(pParse, regData);
drh9d9cf222007-02-13 15:01:11 +00002450 if( emptyDestTest ){
drh1dd518c2016-10-03 02:59:33 +00002451 sqlite3AutoincrementEnd(pParse);
drh66a51672008-01-03 00:01:23 +00002452 sqlite3VdbeAddOp2(v, OP_Halt, SQLITE_OK, 0);
drh9d9cf222007-02-13 15:01:11 +00002453 sqlite3VdbeJumpHere(v, emptyDestTest);
drh66a51672008-01-03 00:01:23 +00002454 sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);
drh9d9cf222007-02-13 15:01:11 +00002455 return 0;
2456 }else{
2457 return 1;
2458 }
2459}
2460#endif /* SQLITE_OMIT_XFER_OPT */