000001 /* 000002 ** 2001 September 15 000003 ** 000004 ** The author disclaims copyright to this source code. In place of 000005 ** a legal notice, here is a blessing: 000006 ** 000007 ** May you do good and not evil. 000008 ** May you find forgiveness for yourself and forgive others. 000009 ** May you share freely, never taking more than you give. 000010 ** 000011 ************************************************************************* 000012 ** This file contains C code routines that are called by the parser 000013 ** to handle INSERT statements in SQLite. 000014 */ 000015 #include "sqliteInt.h" 000016 000017 /* 000018 ** Generate code that will 000019 ** 000020 ** (1) acquire a lock for table pTab then 000021 ** (2) open pTab as cursor iCur. 000022 ** 000023 ** If pTab is a WITHOUT ROWID table, then it is the PRIMARY KEY index 000024 ** for that table that is actually opened. 000025 */ 000026 void sqlite3OpenTable( 000027 Parse *pParse, /* Generate code into this VDBE */ 000028 int iCur, /* The cursor number of the table */ 000029 int iDb, /* The database index in sqlite3.aDb[] */ 000030 Table *pTab, /* The table to be opened */ 000031 int opcode /* OP_OpenRead or OP_OpenWrite */ 000032 ){ 000033 Vdbe *v; 000034 assert( !IsVirtual(pTab) ); 000035 v = sqlite3GetVdbe(pParse); 000036 assert( opcode==OP_OpenWrite || opcode==OP_OpenRead ); 000037 sqlite3TableLock(pParse, iDb, pTab->tnum, 000038 (opcode==OP_OpenWrite)?1:0, pTab->zName); 000039 if( HasRowid(pTab) ){ 000040 sqlite3VdbeAddOp4Int(v, opcode, iCur, pTab->tnum, iDb, pTab->nNVCol); 000041 VdbeComment((v, "%s", pTab->zName)); 000042 }else{ 000043 Index *pPk = sqlite3PrimaryKeyIndex(pTab); 000044 assert( pPk!=0 ); 000045 assert( pPk->tnum==pTab->tnum ); 000046 sqlite3VdbeAddOp3(v, opcode, iCur, pPk->tnum, iDb); 000047 sqlite3VdbeSetP4KeyInfo(pParse, pPk); 000048 VdbeComment((v, "%s", pTab->zName)); 000049 } 000050 } 000051 000052 /* 000053 ** Return a pointer to the column affinity string associated with index 000054 ** pIdx. A column affinity string has one character for each column in 000055 ** the table, according to the affinity of the column: 000056 ** 000057 ** Character Column affinity 000058 ** ------------------------------ 000059 ** 'A' BLOB 000060 ** 'B' TEXT 000061 ** 'C' NUMERIC 000062 ** 'D' INTEGER 000063 ** 'F' REAL 000064 ** 000065 ** An extra 'D' is appended to the end of the string to cover the 000066 ** rowid that appears as the last column in every index. 000067 ** 000068 ** Memory for the buffer containing the column index affinity string 000069 ** is managed along with the rest of the Index structure. It will be 000070 ** released when sqlite3DeleteIndex() is called. 000071 */ 000072 const char *sqlite3IndexAffinityStr(sqlite3 *db, Index *pIdx){ 000073 if( !pIdx->zColAff ){ 000074 /* The first time a column affinity string for a particular index is 000075 ** required, it is allocated and populated here. It is then stored as 000076 ** a member of the Index structure for subsequent use. 000077 ** 000078 ** The column affinity string will eventually be deleted by 000079 ** sqliteDeleteIndex() when the Index structure itself is cleaned 000080 ** up. 000081 */ 000082 int n; 000083 Table *pTab = pIdx->pTable; 000084 pIdx->zColAff = (char *)sqlite3DbMallocRaw(0, pIdx->nColumn+1); 000085 if( !pIdx->zColAff ){ 000086 sqlite3OomFault(db); 000087 return 0; 000088 } 000089 for(n=0; n<pIdx->nColumn; n++){ 000090 i16 x = pIdx->aiColumn[n]; 000091 char aff; 000092 if( x>=0 ){ 000093 aff = pTab->aCol[x].affinity; 000094 }else if( x==XN_ROWID ){ 000095 aff = SQLITE_AFF_INTEGER; 000096 }else{ 000097 assert( x==XN_EXPR ); 000098 assert( pIdx->aColExpr!=0 ); 000099 aff = sqlite3ExprAffinity(pIdx->aColExpr->a[n].pExpr); 000100 } 000101 if( aff<SQLITE_AFF_BLOB ) aff = SQLITE_AFF_BLOB; 000102 if( aff>SQLITE_AFF_NUMERIC) aff = SQLITE_AFF_NUMERIC; 000103 pIdx->zColAff[n] = aff; 000104 } 000105 pIdx->zColAff[n] = 0; 000106 } 000107 000108 return pIdx->zColAff; 000109 } 000110 000111 /* 000112 ** Compute the affinity string for table pTab, if it has not already been 000113 ** computed. As an optimization, omit trailing SQLITE_AFF_BLOB affinities. 000114 ** 000115 ** If the affinity exists (if it is no entirely SQLITE_AFF_BLOB values) and 000116 ** if iReg>0 then code an OP_Affinity opcode that will set the affinities 000117 ** for register iReg and following. Or if affinities exists and iReg==0, 000118 ** then just set the P4 operand of the previous opcode (which should be 000119 ** an OP_MakeRecord) to the affinity string. 000120 ** 000121 ** A column affinity string has one character per column: 000122 ** 000123 ** Character Column affinity 000124 ** ------------------------------ 000125 ** 'A' BLOB 000126 ** 'B' TEXT 000127 ** 'C' NUMERIC 000128 ** 'D' INTEGER 000129 ** 'E' REAL 000130 */ 000131 void sqlite3TableAffinity(Vdbe *v, Table *pTab, int iReg){ 000132 int i, j; 000133 char *zColAff = pTab->zColAff; 000134 if( zColAff==0 ){ 000135 sqlite3 *db = sqlite3VdbeDb(v); 000136 zColAff = (char *)sqlite3DbMallocRaw(0, pTab->nCol+1); 000137 if( !zColAff ){ 000138 sqlite3OomFault(db); 000139 return; 000140 } 000141 000142 for(i=j=0; i<pTab->nCol; i++){ 000143 assert( pTab->aCol[i].affinity!=0 ); 000144 if( (pTab->aCol[i].colFlags & COLFLAG_VIRTUAL)==0 ){ 000145 zColAff[j++] = pTab->aCol[i].affinity; 000146 } 000147 } 000148 do{ 000149 zColAff[j--] = 0; 000150 }while( j>=0 && zColAff[j]<=SQLITE_AFF_BLOB ); 000151 pTab->zColAff = zColAff; 000152 } 000153 assert( zColAff!=0 ); 000154 i = sqlite3Strlen30NN(zColAff); 000155 if( i ){ 000156 if( iReg ){ 000157 sqlite3VdbeAddOp4(v, OP_Affinity, iReg, i, 0, zColAff, i); 000158 }else{ 000159 sqlite3VdbeChangeP4(v, -1, zColAff, i); 000160 } 000161 } 000162 } 000163 000164 /* 000165 ** Return non-zero if the table pTab in database iDb or any of its indices 000166 ** have been opened at any point in the VDBE program. This is used to see if 000167 ** a statement of the form "INSERT INTO <iDb, pTab> SELECT ..." can 000168 ** run without using a temporary table for the results of the SELECT. 000169 */ 000170 static int readsTable(Parse *p, int iDb, Table *pTab){ 000171 Vdbe *v = sqlite3GetVdbe(p); 000172 int i; 000173 int iEnd = sqlite3VdbeCurrentAddr(v); 000174 #ifndef SQLITE_OMIT_VIRTUALTABLE 000175 VTable *pVTab = IsVirtual(pTab) ? sqlite3GetVTable(p->db, pTab) : 0; 000176 #endif 000177 000178 for(i=1; i<iEnd; i++){ 000179 VdbeOp *pOp = sqlite3VdbeGetOp(v, i); 000180 assert( pOp!=0 ); 000181 if( pOp->opcode==OP_OpenRead && pOp->p3==iDb ){ 000182 Index *pIndex; 000183 int tnum = pOp->p2; 000184 if( tnum==pTab->tnum ){ 000185 return 1; 000186 } 000187 for(pIndex=pTab->pIndex; pIndex; pIndex=pIndex->pNext){ 000188 if( tnum==pIndex->tnum ){ 000189 return 1; 000190 } 000191 } 000192 } 000193 #ifndef SQLITE_OMIT_VIRTUALTABLE 000194 if( pOp->opcode==OP_VOpen && pOp->p4.pVtab==pVTab ){ 000195 assert( pOp->p4.pVtab!=0 ); 000196 assert( pOp->p4type==P4_VTAB ); 000197 return 1; 000198 } 000199 #endif 000200 } 000201 return 0; 000202 } 000203 000204 /* This walker callback will compute the union of colFlags flags for all 000205 ** referenced columns in a CHECK constraint or generated column expression. 000206 */ 000207 static int exprColumnFlagUnion(Walker *pWalker, Expr *pExpr){ 000208 if( pExpr->op==TK_COLUMN && pExpr->iColumn>=0 ){ 000209 assert( pExpr->iColumn < pWalker->u.pTab->nCol ); 000210 pWalker->eCode |= pWalker->u.pTab->aCol[pExpr->iColumn].colFlags; 000211 } 000212 return WRC_Continue; 000213 } 000214 000215 #ifndef SQLITE_OMIT_GENERATED_COLUMNS 000216 /* 000217 ** All regular columns for table pTab have been puts into registers 000218 ** starting with iRegStore. The registers that correspond to STORED 000219 ** or VIRTUAL columns have not yet been initialized. This routine goes 000220 ** back and computes the values for those columns based on the previously 000221 ** computed normal columns. 000222 */ 000223 void sqlite3ComputeGeneratedColumns( 000224 Parse *pParse, /* Parsing context */ 000225 int iRegStore, /* Register holding the first column */ 000226 Table *pTab /* The table */ 000227 ){ 000228 int i; 000229 Walker w; 000230 Column *pRedo; 000231 int eProgress; 000232 VdbeOp *pOp; 000233 000234 assert( pTab->tabFlags & TF_HasGenerated ); 000235 testcase( pTab->tabFlags & TF_HasVirtual ); 000236 testcase( pTab->tabFlags & TF_HasStored ); 000237 000238 /* Before computing generated columns, first go through and make sure 000239 ** that appropriate affinity has been applied to the regular columns 000240 */ 000241 sqlite3TableAffinity(pParse->pVdbe, pTab, iRegStore); 000242 if( (pTab->tabFlags & TF_HasStored)!=0 000243 && (pOp = sqlite3VdbeGetOp(pParse->pVdbe,-1))->opcode==OP_Affinity 000244 ){ 000245 /* Change the OP_Affinity argument to '@' (NONE) for all stored 000246 ** columns. '@' is the no-op affinity and those columns have not 000247 ** yet been computed. */ 000248 int ii, jj; 000249 char *zP4 = pOp->p4.z; 000250 assert( zP4!=0 ); 000251 assert( pOp->p4type==P4_DYNAMIC ); 000252 for(ii=jj=0; zP4[jj]; ii++){ 000253 if( pTab->aCol[ii].colFlags & COLFLAG_VIRTUAL ){ 000254 continue; 000255 } 000256 if( pTab->aCol[ii].colFlags & COLFLAG_STORED ){ 000257 zP4[jj] = SQLITE_AFF_NONE; 000258 } 000259 jj++; 000260 } 000261 } 000262 000263 /* Because there can be multiple generated columns that refer to one another, 000264 ** this is a two-pass algorithm. On the first pass, mark all generated 000265 ** columns as "not available". 000266 */ 000267 for(i=0; i<pTab->nCol; i++){ 000268 if( pTab->aCol[i].colFlags & COLFLAG_GENERATED ){ 000269 testcase( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL ); 000270 testcase( pTab->aCol[i].colFlags & COLFLAG_STORED ); 000271 pTab->aCol[i].colFlags |= COLFLAG_NOTAVAIL; 000272 } 000273 } 000274 000275 w.u.pTab = pTab; 000276 w.xExprCallback = exprColumnFlagUnion; 000277 w.xSelectCallback = 0; 000278 w.xSelectCallback2 = 0; 000279 000280 /* On the second pass, compute the value of each NOT-AVAILABLE column. 000281 ** Companion code in the TK_COLUMN case of sqlite3ExprCodeTarget() will 000282 ** compute dependencies and mark remove the COLSPAN_NOTAVAIL mark, as 000283 ** they are needed. 000284 */ 000285 pParse->iSelfTab = -iRegStore; 000286 do{ 000287 eProgress = 0; 000288 pRedo = 0; 000289 for(i=0; i<pTab->nCol; i++){ 000290 Column *pCol = pTab->aCol + i; 000291 if( (pCol->colFlags & COLFLAG_NOTAVAIL)!=0 ){ 000292 int x; 000293 pCol->colFlags |= COLFLAG_BUSY; 000294 w.eCode = 0; 000295 sqlite3WalkExpr(&w, pCol->pDflt); 000296 pCol->colFlags &= ~COLFLAG_BUSY; 000297 if( w.eCode & COLFLAG_NOTAVAIL ){ 000298 pRedo = pCol; 000299 continue; 000300 } 000301 eProgress = 1; 000302 assert( pCol->colFlags & COLFLAG_GENERATED ); 000303 x = sqlite3TableColumnToStorage(pTab, i) + iRegStore; 000304 sqlite3ExprCodeGeneratedColumn(pParse, pCol, x); 000305 pCol->colFlags &= ~COLFLAG_NOTAVAIL; 000306 } 000307 } 000308 }while( pRedo && eProgress ); 000309 if( pRedo ){ 000310 sqlite3ErrorMsg(pParse, "generated column loop on \"%s\"", pRedo->zName); 000311 } 000312 pParse->iSelfTab = 0; 000313 } 000314 #endif /* SQLITE_OMIT_GENERATED_COLUMNS */ 000315 000316 000317 #ifndef SQLITE_OMIT_AUTOINCREMENT 000318 /* 000319 ** Locate or create an AutoincInfo structure associated with table pTab 000320 ** which is in database iDb. Return the register number for the register 000321 ** that holds the maximum rowid. Return zero if pTab is not an AUTOINCREMENT 000322 ** table. (Also return zero when doing a VACUUM since we do not want to 000323 ** update the AUTOINCREMENT counters during a VACUUM.) 000324 ** 000325 ** There is at most one AutoincInfo structure per table even if the 000326 ** same table is autoincremented multiple times due to inserts within 000327 ** triggers. A new AutoincInfo structure is created if this is the 000328 ** first use of table pTab. On 2nd and subsequent uses, the original 000329 ** AutoincInfo structure is used. 000330 ** 000331 ** Four consecutive registers are allocated: 000332 ** 000333 ** (1) The name of the pTab table. 000334 ** (2) The maximum ROWID of pTab. 000335 ** (3) The rowid in sqlite_sequence of pTab 000336 ** (4) The original value of the max ROWID in pTab, or NULL if none 000337 ** 000338 ** The 2nd register is the one that is returned. That is all the 000339 ** insert routine needs to know about. 000340 */ 000341 static int autoIncBegin( 000342 Parse *pParse, /* Parsing context */ 000343 int iDb, /* Index of the database holding pTab */ 000344 Table *pTab /* The table we are writing to */ 000345 ){ 000346 int memId = 0; /* Register holding maximum rowid */ 000347 assert( pParse->db->aDb[iDb].pSchema!=0 ); 000348 if( (pTab->tabFlags & TF_Autoincrement)!=0 000349 && (pParse->db->mDbFlags & DBFLAG_Vacuum)==0 000350 ){ 000351 Parse *pToplevel = sqlite3ParseToplevel(pParse); 000352 AutoincInfo *pInfo; 000353 Table *pSeqTab = pParse->db->aDb[iDb].pSchema->pSeqTab; 000354 000355 /* Verify that the sqlite_sequence table exists and is an ordinary 000356 ** rowid table with exactly two columns. 000357 ** Ticket d8dc2b3a58cd5dc2918a1d4acb 2018-05-23 */ 000358 if( pSeqTab==0 000359 || !HasRowid(pSeqTab) 000360 || IsVirtual(pSeqTab) 000361 || pSeqTab->nCol!=2 000362 ){ 000363 pParse->nErr++; 000364 pParse->rc = SQLITE_CORRUPT_SEQUENCE; 000365 return 0; 000366 } 000367 000368 pInfo = pToplevel->pAinc; 000369 while( pInfo && pInfo->pTab!=pTab ){ pInfo = pInfo->pNext; } 000370 if( pInfo==0 ){ 000371 pInfo = sqlite3DbMallocRawNN(pParse->db, sizeof(*pInfo)); 000372 if( pInfo==0 ) return 0; 000373 pInfo->pNext = pToplevel->pAinc; 000374 pToplevel->pAinc = pInfo; 000375 pInfo->pTab = pTab; 000376 pInfo->iDb = iDb; 000377 pToplevel->nMem++; /* Register to hold name of table */ 000378 pInfo->regCtr = ++pToplevel->nMem; /* Max rowid register */ 000379 pToplevel->nMem +=2; /* Rowid in sqlite_sequence + orig max val */ 000380 } 000381 memId = pInfo->regCtr; 000382 } 000383 return memId; 000384 } 000385 000386 /* 000387 ** This routine generates code that will initialize all of the 000388 ** register used by the autoincrement tracker. 000389 */ 000390 void sqlite3AutoincrementBegin(Parse *pParse){ 000391 AutoincInfo *p; /* Information about an AUTOINCREMENT */ 000392 sqlite3 *db = pParse->db; /* The database connection */ 000393 Db *pDb; /* Database only autoinc table */ 000394 int memId; /* Register holding max rowid */ 000395 Vdbe *v = pParse->pVdbe; /* VDBE under construction */ 000396 000397 /* This routine is never called during trigger-generation. It is 000398 ** only called from the top-level */ 000399 assert( pParse->pTriggerTab==0 ); 000400 assert( sqlite3IsToplevel(pParse) ); 000401 000402 assert( v ); /* We failed long ago if this is not so */ 000403 for(p = pParse->pAinc; p; p = p->pNext){ 000404 static const int iLn = VDBE_OFFSET_LINENO(2); 000405 static const VdbeOpList autoInc[] = { 000406 /* 0 */ {OP_Null, 0, 0, 0}, 000407 /* 1 */ {OP_Rewind, 0, 10, 0}, 000408 /* 2 */ {OP_Column, 0, 0, 0}, 000409 /* 3 */ {OP_Ne, 0, 9, 0}, 000410 /* 4 */ {OP_Rowid, 0, 0, 0}, 000411 /* 5 */ {OP_Column, 0, 1, 0}, 000412 /* 6 */ {OP_AddImm, 0, 0, 0}, 000413 /* 7 */ {OP_Copy, 0, 0, 0}, 000414 /* 8 */ {OP_Goto, 0, 11, 0}, 000415 /* 9 */ {OP_Next, 0, 2, 0}, 000416 /* 10 */ {OP_Integer, 0, 0, 0}, 000417 /* 11 */ {OP_Close, 0, 0, 0} 000418 }; 000419 VdbeOp *aOp; 000420 pDb = &db->aDb[p->iDb]; 000421 memId = p->regCtr; 000422 assert( sqlite3SchemaMutexHeld(db, 0, pDb->pSchema) ); 000423 sqlite3OpenTable(pParse, 0, p->iDb, pDb->pSchema->pSeqTab, OP_OpenRead); 000424 sqlite3VdbeLoadString(v, memId-1, p->pTab->zName); 000425 aOp = sqlite3VdbeAddOpList(v, ArraySize(autoInc), autoInc, iLn); 000426 if( aOp==0 ) break; 000427 aOp[0].p2 = memId; 000428 aOp[0].p3 = memId+2; 000429 aOp[2].p3 = memId; 000430 aOp[3].p1 = memId-1; 000431 aOp[3].p3 = memId; 000432 aOp[3].p5 = SQLITE_JUMPIFNULL; 000433 aOp[4].p2 = memId+1; 000434 aOp[5].p3 = memId; 000435 aOp[6].p1 = memId; 000436 aOp[7].p2 = memId+2; 000437 aOp[7].p1 = memId; 000438 aOp[10].p2 = memId; 000439 if( pParse->nTab==0 ) pParse->nTab = 1; 000440 } 000441 } 000442 000443 /* 000444 ** Update the maximum rowid for an autoincrement calculation. 000445 ** 000446 ** This routine should be called when the regRowid register holds a 000447 ** new rowid that is about to be inserted. If that new rowid is 000448 ** larger than the maximum rowid in the memId memory cell, then the 000449 ** memory cell is updated. 000450 */ 000451 static void autoIncStep(Parse *pParse, int memId, int regRowid){ 000452 if( memId>0 ){ 000453 sqlite3VdbeAddOp2(pParse->pVdbe, OP_MemMax, memId, regRowid); 000454 } 000455 } 000456 000457 /* 000458 ** This routine generates the code needed to write autoincrement 000459 ** maximum rowid values back into the sqlite_sequence register. 000460 ** Every statement that might do an INSERT into an autoincrement 000461 ** table (either directly or through triggers) needs to call this 000462 ** routine just before the "exit" code. 000463 */ 000464 static SQLITE_NOINLINE void autoIncrementEnd(Parse *pParse){ 000465 AutoincInfo *p; 000466 Vdbe *v = pParse->pVdbe; 000467 sqlite3 *db = pParse->db; 000468 000469 assert( v ); 000470 for(p = pParse->pAinc; p; p = p->pNext){ 000471 static const int iLn = VDBE_OFFSET_LINENO(2); 000472 static const VdbeOpList autoIncEnd[] = { 000473 /* 0 */ {OP_NotNull, 0, 2, 0}, 000474 /* 1 */ {OP_NewRowid, 0, 0, 0}, 000475 /* 2 */ {OP_MakeRecord, 0, 2, 0}, 000476 /* 3 */ {OP_Insert, 0, 0, 0}, 000477 /* 4 */ {OP_Close, 0, 0, 0} 000478 }; 000479 VdbeOp *aOp; 000480 Db *pDb = &db->aDb[p->iDb]; 000481 int iRec; 000482 int memId = p->regCtr; 000483 000484 iRec = sqlite3GetTempReg(pParse); 000485 assert( sqlite3SchemaMutexHeld(db, 0, pDb->pSchema) ); 000486 sqlite3VdbeAddOp3(v, OP_Le, memId+2, sqlite3VdbeCurrentAddr(v)+7, memId); 000487 VdbeCoverage(v); 000488 sqlite3OpenTable(pParse, 0, p->iDb, pDb->pSchema->pSeqTab, OP_OpenWrite); 000489 aOp = sqlite3VdbeAddOpList(v, ArraySize(autoIncEnd), autoIncEnd, iLn); 000490 if( aOp==0 ) break; 000491 aOp[0].p1 = memId+1; 000492 aOp[1].p2 = memId+1; 000493 aOp[2].p1 = memId-1; 000494 aOp[2].p3 = iRec; 000495 aOp[3].p2 = iRec; 000496 aOp[3].p3 = memId+1; 000497 aOp[3].p5 = OPFLAG_APPEND; 000498 sqlite3ReleaseTempReg(pParse, iRec); 000499 } 000500 } 000501 void sqlite3AutoincrementEnd(Parse *pParse){ 000502 if( pParse->pAinc ) autoIncrementEnd(pParse); 000503 } 000504 #else 000505 /* 000506 ** If SQLITE_OMIT_AUTOINCREMENT is defined, then the three routines 000507 ** above are all no-ops 000508 */ 000509 # define autoIncBegin(A,B,C) (0) 000510 # define autoIncStep(A,B,C) 000511 #endif /* SQLITE_OMIT_AUTOINCREMENT */ 000512 000513 000514 /* Forward declaration */ 000515 static int xferOptimization( 000516 Parse *pParse, /* Parser context */ 000517 Table *pDest, /* The table we are inserting into */ 000518 Select *pSelect, /* A SELECT statement to use as the data source */ 000519 int onError, /* How to handle constraint errors */ 000520 int iDbDest /* The database of pDest */ 000521 ); 000522 000523 /* 000524 ** This routine is called to handle SQL of the following forms: 000525 ** 000526 ** insert into TABLE (IDLIST) values(EXPRLIST),(EXPRLIST),... 000527 ** insert into TABLE (IDLIST) select 000528 ** insert into TABLE (IDLIST) default values 000529 ** 000530 ** The IDLIST following the table name is always optional. If omitted, 000531 ** then a list of all (non-hidden) columns for the table is substituted. 000532 ** The IDLIST appears in the pColumn parameter. pColumn is NULL if IDLIST 000533 ** is omitted. 000534 ** 000535 ** For the pSelect parameter holds the values to be inserted for the 000536 ** first two forms shown above. A VALUES clause is really just short-hand 000537 ** for a SELECT statement that omits the FROM clause and everything else 000538 ** that follows. If the pSelect parameter is NULL, that means that the 000539 ** DEFAULT VALUES form of the INSERT statement is intended. 000540 ** 000541 ** The code generated follows one of four templates. For a simple 000542 ** insert with data coming from a single-row VALUES clause, the code executes 000543 ** once straight down through. Pseudo-code follows (we call this 000544 ** the "1st template"): 000545 ** 000546 ** open write cursor to <table> and its indices 000547 ** put VALUES clause expressions into registers 000548 ** write the resulting record into <table> 000549 ** cleanup 000550 ** 000551 ** The three remaining templates assume the statement is of the form 000552 ** 000553 ** INSERT INTO <table> SELECT ... 000554 ** 000555 ** If the SELECT clause is of the restricted form "SELECT * FROM <table2>" - 000556 ** in other words if the SELECT pulls all columns from a single table 000557 ** and there is no WHERE or LIMIT or GROUP BY or ORDER BY clauses, and 000558 ** if <table2> and <table1> are distinct tables but have identical 000559 ** schemas, including all the same indices, then a special optimization 000560 ** is invoked that copies raw records from <table2> over to <table1>. 000561 ** See the xferOptimization() function for the implementation of this 000562 ** template. This is the 2nd template. 000563 ** 000564 ** open a write cursor to <table> 000565 ** open read cursor on <table2> 000566 ** transfer all records in <table2> over to <table> 000567 ** close cursors 000568 ** foreach index on <table> 000569 ** open a write cursor on the <table> index 000570 ** open a read cursor on the corresponding <table2> index 000571 ** transfer all records from the read to the write cursors 000572 ** close cursors 000573 ** end foreach 000574 ** 000575 ** The 3rd template is for when the second template does not apply 000576 ** and the SELECT clause does not read from <table> at any time. 000577 ** The generated code follows this template: 000578 ** 000579 ** X <- A 000580 ** goto B 000581 ** A: setup for the SELECT 000582 ** loop over the rows in the SELECT 000583 ** load values into registers R..R+n 000584 ** yield X 000585 ** end loop 000586 ** cleanup after the SELECT 000587 ** end-coroutine X 000588 ** B: open write cursor to <table> and its indices 000589 ** C: yield X, at EOF goto D 000590 ** insert the select result into <table> from R..R+n 000591 ** goto C 000592 ** D: cleanup 000593 ** 000594 ** The 4th template is used if the insert statement takes its 000595 ** values from a SELECT but the data is being inserted into a table 000596 ** that is also read as part of the SELECT. In the third form, 000597 ** we have to use an intermediate table to store the results of 000598 ** the select. The template is like this: 000599 ** 000600 ** X <- A 000601 ** goto B 000602 ** A: setup for the SELECT 000603 ** loop over the tables in the SELECT 000604 ** load value into register R..R+n 000605 ** yield X 000606 ** end loop 000607 ** cleanup after the SELECT 000608 ** end co-routine R 000609 ** B: open temp table 000610 ** L: yield X, at EOF goto M 000611 ** insert row from R..R+n into temp table 000612 ** goto L 000613 ** M: open write cursor to <table> and its indices 000614 ** rewind temp table 000615 ** C: loop over rows of intermediate table 000616 ** transfer values form intermediate table into <table> 000617 ** end loop 000618 ** D: cleanup 000619 */ 000620 void sqlite3Insert( 000621 Parse *pParse, /* Parser context */ 000622 SrcList *pTabList, /* Name of table into which we are inserting */ 000623 Select *pSelect, /* A SELECT statement to use as the data source */ 000624 IdList *pColumn, /* Column names corresponding to IDLIST, or NULL. */ 000625 int onError, /* How to handle constraint errors */ 000626 Upsert *pUpsert /* ON CONFLICT clauses for upsert, or NULL */ 000627 ){ 000628 sqlite3 *db; /* The main database structure */ 000629 Table *pTab; /* The table to insert into. aka TABLE */ 000630 int i, j; /* Loop counters */ 000631 Vdbe *v; /* Generate code into this virtual machine */ 000632 Index *pIdx; /* For looping over indices of the table */ 000633 int nColumn; /* Number of columns in the data */ 000634 int nHidden = 0; /* Number of hidden columns if TABLE is virtual */ 000635 int iDataCur = 0; /* VDBE cursor that is the main data repository */ 000636 int iIdxCur = 0; /* First index cursor */ 000637 int ipkColumn = -1; /* Column that is the INTEGER PRIMARY KEY */ 000638 int endOfLoop; /* Label for the end of the insertion loop */ 000639 int srcTab = 0; /* Data comes from this temporary cursor if >=0 */ 000640 int addrInsTop = 0; /* Jump to label "D" */ 000641 int addrCont = 0; /* Top of insert loop. Label "C" in templates 3 and 4 */ 000642 SelectDest dest; /* Destination for SELECT on rhs of INSERT */ 000643 int iDb; /* Index of database holding TABLE */ 000644 u8 useTempTable = 0; /* Store SELECT results in intermediate table */ 000645 u8 appendFlag = 0; /* True if the insert is likely to be an append */ 000646 u8 withoutRowid; /* 0 for normal table. 1 for WITHOUT ROWID table */ 000647 u8 bIdListInOrder; /* True if IDLIST is in table order */ 000648 ExprList *pList = 0; /* List of VALUES() to be inserted */ 000649 int iRegStore; /* Register in which to store next column */ 000650 000651 /* Register allocations */ 000652 int regFromSelect = 0;/* Base register for data coming from SELECT */ 000653 int regAutoinc = 0; /* Register holding the AUTOINCREMENT counter */ 000654 int regRowCount = 0; /* Memory cell used for the row counter */ 000655 int regIns; /* Block of regs holding rowid+data being inserted */ 000656 int regRowid; /* registers holding insert rowid */ 000657 int regData; /* register holding first column to insert */ 000658 int *aRegIdx = 0; /* One register allocated to each index */ 000659 000660 #ifndef SQLITE_OMIT_TRIGGER 000661 int isView; /* True if attempting to insert into a view */ 000662 Trigger *pTrigger; /* List of triggers on pTab, if required */ 000663 int tmask; /* Mask of trigger times */ 000664 #endif 000665 000666 db = pParse->db; 000667 if( pParse->nErr || db->mallocFailed ){ 000668 goto insert_cleanup; 000669 } 000670 dest.iSDParm = 0; /* Suppress a harmless compiler warning */ 000671 000672 /* If the Select object is really just a simple VALUES() list with a 000673 ** single row (the common case) then keep that one row of values 000674 ** and discard the other (unused) parts of the pSelect object 000675 */ 000676 if( pSelect && (pSelect->selFlags & SF_Values)!=0 && pSelect->pPrior==0 ){ 000677 pList = pSelect->pEList; 000678 pSelect->pEList = 0; 000679 sqlite3SelectDelete(db, pSelect); 000680 pSelect = 0; 000681 } 000682 000683 /* Locate the table into which we will be inserting new information. 000684 */ 000685 assert( pTabList->nSrc==1 ); 000686 pTab = sqlite3SrcListLookup(pParse, pTabList); 000687 if( pTab==0 ){ 000688 goto insert_cleanup; 000689 } 000690 iDb = sqlite3SchemaToIndex(db, pTab->pSchema); 000691 assert( iDb<db->nDb ); 000692 if( sqlite3AuthCheck(pParse, SQLITE_INSERT, pTab->zName, 0, 000693 db->aDb[iDb].zDbSName) ){ 000694 goto insert_cleanup; 000695 } 000696 withoutRowid = !HasRowid(pTab); 000697 000698 /* Figure out if we have any triggers and if the table being 000699 ** inserted into is a view 000700 */ 000701 #ifndef SQLITE_OMIT_TRIGGER 000702 pTrigger = sqlite3TriggersExist(pParse, pTab, TK_INSERT, 0, &tmask); 000703 isView = pTab->pSelect!=0; 000704 #else 000705 # define pTrigger 0 000706 # define tmask 0 000707 # define isView 0 000708 #endif 000709 #ifdef SQLITE_OMIT_VIEW 000710 # undef isView 000711 # define isView 0 000712 #endif 000713 assert( (pTrigger && tmask) || (pTrigger==0 && tmask==0) ); 000714 000715 /* If pTab is really a view, make sure it has been initialized. 000716 ** ViewGetColumnNames() is a no-op if pTab is not a view. 000717 */ 000718 if( sqlite3ViewGetColumnNames(pParse, pTab) ){ 000719 goto insert_cleanup; 000720 } 000721 000722 /* Cannot insert into a read-only table. 000723 */ 000724 if( sqlite3IsReadOnly(pParse, pTab, tmask) ){ 000725 goto insert_cleanup; 000726 } 000727 000728 /* Allocate a VDBE 000729 */ 000730 v = sqlite3GetVdbe(pParse); 000731 if( v==0 ) goto insert_cleanup; 000732 if( pParse->nested==0 ) sqlite3VdbeCountChanges(v); 000733 sqlite3BeginWriteOperation(pParse, pSelect || pTrigger, iDb); 000734 000735 #ifndef SQLITE_OMIT_XFER_OPT 000736 /* If the statement is of the form 000737 ** 000738 ** INSERT INTO <table1> SELECT * FROM <table2>; 000739 ** 000740 ** Then special optimizations can be applied that make the transfer 000741 ** very fast and which reduce fragmentation of indices. 000742 ** 000743 ** This is the 2nd template. 000744 */ 000745 if( pColumn==0 && xferOptimization(pParse, pTab, pSelect, onError, iDb) ){ 000746 assert( !pTrigger ); 000747 assert( pList==0 ); 000748 goto insert_end; 000749 } 000750 #endif /* SQLITE_OMIT_XFER_OPT */ 000751 000752 /* If this is an AUTOINCREMENT table, look up the sequence number in the 000753 ** sqlite_sequence table and store it in memory cell regAutoinc. 000754 */ 000755 regAutoinc = autoIncBegin(pParse, iDb, pTab); 000756 000757 /* Allocate a block registers to hold the rowid and the values 000758 ** for all columns of the new row. 000759 */ 000760 regRowid = regIns = pParse->nMem+1; 000761 pParse->nMem += pTab->nCol + 1; 000762 if( IsVirtual(pTab) ){ 000763 regRowid++; 000764 pParse->nMem++; 000765 } 000766 regData = regRowid+1; 000767 000768 /* If the INSERT statement included an IDLIST term, then make sure 000769 ** all elements of the IDLIST really are columns of the table and 000770 ** remember the column indices. 000771 ** 000772 ** If the table has an INTEGER PRIMARY KEY column and that column 000773 ** is named in the IDLIST, then record in the ipkColumn variable 000774 ** the index into IDLIST of the primary key column. ipkColumn is 000775 ** the index of the primary key as it appears in IDLIST, not as 000776 ** is appears in the original table. (The index of the INTEGER 000777 ** PRIMARY KEY in the original table is pTab->iPKey.) After this 000778 ** loop, if ipkColumn==(-1), that means that integer primary key 000779 ** is unspecified, and hence the table is either WITHOUT ROWID or 000780 ** it will automatically generated an integer primary key. 000781 ** 000782 ** bIdListInOrder is true if the columns in IDLIST are in storage 000783 ** order. This enables an optimization that avoids shuffling the 000784 ** columns into storage order. False negatives are harmless, 000785 ** but false positives will cause database corruption. 000786 */ 000787 bIdListInOrder = (pTab->tabFlags & (TF_OOOHidden|TF_HasStored))==0; 000788 if( pColumn ){ 000789 for(i=0; i<pColumn->nId; i++){ 000790 pColumn->a[i].idx = -1; 000791 } 000792 for(i=0; i<pColumn->nId; i++){ 000793 for(j=0; j<pTab->nCol; j++){ 000794 if( sqlite3StrICmp(pColumn->a[i].zName, pTab->aCol[j].zName)==0 ){ 000795 pColumn->a[i].idx = j; 000796 if( i!=j ) bIdListInOrder = 0; 000797 if( j==pTab->iPKey ){ 000798 ipkColumn = i; assert( !withoutRowid ); 000799 } 000800 #ifndef SQLITE_OMIT_GENERATED_COLUMNS 000801 if( pTab->aCol[j].colFlags & (COLFLAG_STORED|COLFLAG_VIRTUAL) ){ 000802 sqlite3ErrorMsg(pParse, 000803 "cannot INSERT into generated column \"%s\"", 000804 pTab->aCol[j].zName); 000805 goto insert_cleanup; 000806 } 000807 #endif 000808 break; 000809 } 000810 } 000811 if( j>=pTab->nCol ){ 000812 if( sqlite3IsRowid(pColumn->a[i].zName) && !withoutRowid ){ 000813 ipkColumn = i; 000814 bIdListInOrder = 0; 000815 }else{ 000816 sqlite3ErrorMsg(pParse, "table %S has no column named %s", 000817 pTabList, 0, pColumn->a[i].zName); 000818 pParse->checkSchema = 1; 000819 goto insert_cleanup; 000820 } 000821 } 000822 } 000823 } 000824 000825 /* Figure out how many columns of data are supplied. If the data 000826 ** is coming from a SELECT statement, then generate a co-routine that 000827 ** produces a single row of the SELECT on each invocation. The 000828 ** co-routine is the common header to the 3rd and 4th templates. 000829 */ 000830 if( pSelect ){ 000831 /* Data is coming from a SELECT or from a multi-row VALUES clause. 000832 ** Generate a co-routine to run the SELECT. */ 000833 int regYield; /* Register holding co-routine entry-point */ 000834 int addrTop; /* Top of the co-routine */ 000835 int rc; /* Result code */ 000836 000837 regYield = ++pParse->nMem; 000838 addrTop = sqlite3VdbeCurrentAddr(v) + 1; 000839 sqlite3VdbeAddOp3(v, OP_InitCoroutine, regYield, 0, addrTop); 000840 sqlite3SelectDestInit(&dest, SRT_Coroutine, regYield); 000841 dest.iSdst = bIdListInOrder ? regData : 0; 000842 dest.nSdst = pTab->nCol; 000843 rc = sqlite3Select(pParse, pSelect, &dest); 000844 regFromSelect = dest.iSdst; 000845 if( rc || db->mallocFailed || pParse->nErr ) goto insert_cleanup; 000846 sqlite3VdbeEndCoroutine(v, regYield); 000847 sqlite3VdbeJumpHere(v, addrTop - 1); /* label B: */ 000848 assert( pSelect->pEList ); 000849 nColumn = pSelect->pEList->nExpr; 000850 000851 /* Set useTempTable to TRUE if the result of the SELECT statement 000852 ** should be written into a temporary table (template 4). Set to 000853 ** FALSE if each output row of the SELECT can be written directly into 000854 ** the destination table (template 3). 000855 ** 000856 ** A temp table must be used if the table being updated is also one 000857 ** of the tables being read by the SELECT statement. Also use a 000858 ** temp table in the case of row triggers. 000859 */ 000860 if( pTrigger || readsTable(pParse, iDb, pTab) ){ 000861 useTempTable = 1; 000862 } 000863 000864 if( useTempTable ){ 000865 /* Invoke the coroutine to extract information from the SELECT 000866 ** and add it to a transient table srcTab. The code generated 000867 ** here is from the 4th template: 000868 ** 000869 ** B: open temp table 000870 ** L: yield X, goto M at EOF 000871 ** insert row from R..R+n into temp table 000872 ** goto L 000873 ** M: ... 000874 */ 000875 int regRec; /* Register to hold packed record */ 000876 int regTempRowid; /* Register to hold temp table ROWID */ 000877 int addrL; /* Label "L" */ 000878 000879 srcTab = pParse->nTab++; 000880 regRec = sqlite3GetTempReg(pParse); 000881 regTempRowid = sqlite3GetTempReg(pParse); 000882 sqlite3VdbeAddOp2(v, OP_OpenEphemeral, srcTab, nColumn); 000883 addrL = sqlite3VdbeAddOp1(v, OP_Yield, dest.iSDParm); VdbeCoverage(v); 000884 sqlite3VdbeAddOp3(v, OP_MakeRecord, regFromSelect, nColumn, regRec); 000885 sqlite3VdbeAddOp2(v, OP_NewRowid, srcTab, regTempRowid); 000886 sqlite3VdbeAddOp3(v, OP_Insert, srcTab, regRec, regTempRowid); 000887 sqlite3VdbeGoto(v, addrL); 000888 sqlite3VdbeJumpHere(v, addrL); 000889 sqlite3ReleaseTempReg(pParse, regRec); 000890 sqlite3ReleaseTempReg(pParse, regTempRowid); 000891 } 000892 }else{ 000893 /* This is the case if the data for the INSERT is coming from a 000894 ** single-row VALUES clause 000895 */ 000896 NameContext sNC; 000897 memset(&sNC, 0, sizeof(sNC)); 000898 sNC.pParse = pParse; 000899 srcTab = -1; 000900 assert( useTempTable==0 ); 000901 if( pList ){ 000902 nColumn = pList->nExpr; 000903 if( sqlite3ResolveExprListNames(&sNC, pList) ){ 000904 goto insert_cleanup; 000905 } 000906 }else{ 000907 nColumn = 0; 000908 } 000909 } 000910 000911 /* If there is no IDLIST term but the table has an integer primary 000912 ** key, the set the ipkColumn variable to the integer primary key 000913 ** column index in the original table definition. 000914 */ 000915 if( pColumn==0 && nColumn>0 ){ 000916 ipkColumn = pTab->iPKey; 000917 #ifndef SQLITE_OMIT_GENERATED_COLUMNS 000918 if( ipkColumn>=0 && (pTab->tabFlags & TF_HasGenerated)!=0 ){ 000919 testcase( pTab->tabFlags & TF_HasVirtual ); 000920 testcase( pTab->tabFlags & TF_HasStored ); 000921 for(i=ipkColumn-1; i>=0; i--){ 000922 if( pTab->aCol[i].colFlags & COLFLAG_GENERATED ){ 000923 testcase( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL ); 000924 testcase( pTab->aCol[i].colFlags & COLFLAG_STORED ); 000925 ipkColumn--; 000926 } 000927 } 000928 } 000929 #endif 000930 } 000931 000932 /* Make sure the number of columns in the source data matches the number 000933 ** of columns to be inserted into the table. 000934 */ 000935 for(i=0; i<pTab->nCol; i++){ 000936 if( pTab->aCol[i].colFlags & COLFLAG_NOINSERT ) nHidden++; 000937 } 000938 if( pColumn==0 && nColumn && nColumn!=(pTab->nCol-nHidden) ){ 000939 sqlite3ErrorMsg(pParse, 000940 "table %S has %d columns but %d values were supplied", 000941 pTabList, 0, pTab->nCol-nHidden, nColumn); 000942 goto insert_cleanup; 000943 } 000944 if( pColumn!=0 && nColumn!=pColumn->nId ){ 000945 sqlite3ErrorMsg(pParse, "%d values for %d columns", nColumn, pColumn->nId); 000946 goto insert_cleanup; 000947 } 000948 000949 /* Initialize the count of rows to be inserted 000950 */ 000951 if( (db->flags & SQLITE_CountRows)!=0 000952 && !pParse->nested 000953 && !pParse->pTriggerTab 000954 ){ 000955 regRowCount = ++pParse->nMem; 000956 sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount); 000957 } 000958 000959 /* If this is not a view, open the table and and all indices */ 000960 if( !isView ){ 000961 int nIdx; 000962 nIdx = sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, 0, -1, 0, 000963 &iDataCur, &iIdxCur); 000964 aRegIdx = sqlite3DbMallocRawNN(db, sizeof(int)*(nIdx+2)); 000965 if( aRegIdx==0 ){ 000966 goto insert_cleanup; 000967 } 000968 for(i=0, pIdx=pTab->pIndex; i<nIdx; pIdx=pIdx->pNext, i++){ 000969 assert( pIdx ); 000970 aRegIdx[i] = ++pParse->nMem; 000971 pParse->nMem += pIdx->nColumn; 000972 } 000973 aRegIdx[i] = ++pParse->nMem; /* Register to store the table record */ 000974 } 000975 #ifndef SQLITE_OMIT_UPSERT 000976 if( pUpsert ){ 000977 if( IsVirtual(pTab) ){ 000978 sqlite3ErrorMsg(pParse, "UPSERT not implemented for virtual table \"%s\"", 000979 pTab->zName); 000980 goto insert_cleanup; 000981 } 000982 if( pTab->pSelect ){ 000983 sqlite3ErrorMsg(pParse, "cannot UPSERT a view"); 000984 goto insert_cleanup; 000985 } 000986 if( sqlite3HasExplicitNulls(pParse, pUpsert->pUpsertTarget) ){ 000987 goto insert_cleanup; 000988 } 000989 pTabList->a[0].iCursor = iDataCur; 000990 pUpsert->pUpsertSrc = pTabList; 000991 pUpsert->regData = regData; 000992 pUpsert->iDataCur = iDataCur; 000993 pUpsert->iIdxCur = iIdxCur; 000994 if( pUpsert->pUpsertTarget ){ 000995 sqlite3UpsertAnalyzeTarget(pParse, pTabList, pUpsert); 000996 } 000997 } 000998 #endif 000999 001000 001001 /* This is the top of the main insertion loop */ 001002 if( useTempTable ){ 001003 /* This block codes the top of loop only. The complete loop is the 001004 ** following pseudocode (template 4): 001005 ** 001006 ** rewind temp table, if empty goto D 001007 ** C: loop over rows of intermediate table 001008 ** transfer values form intermediate table into <table> 001009 ** end loop 001010 ** D: ... 001011 */ 001012 addrInsTop = sqlite3VdbeAddOp1(v, OP_Rewind, srcTab); VdbeCoverage(v); 001013 addrCont = sqlite3VdbeCurrentAddr(v); 001014 }else if( pSelect ){ 001015 /* This block codes the top of loop only. The complete loop is the 001016 ** following pseudocode (template 3): 001017 ** 001018 ** C: yield X, at EOF goto D 001019 ** insert the select result into <table> from R..R+n 001020 ** goto C 001021 ** D: ... 001022 */ 001023 sqlite3VdbeReleaseRegisters(pParse, regData, pTab->nCol, 0); 001024 addrInsTop = addrCont = sqlite3VdbeAddOp1(v, OP_Yield, dest.iSDParm); 001025 VdbeCoverage(v); 001026 if( ipkColumn>=0 ){ 001027 /* tag-20191021-001: If the INTEGER PRIMARY KEY is being generated by the 001028 ** SELECT, go ahead and copy the value into the rowid slot now, so that 001029 ** the value does not get overwritten by a NULL at tag-20191021-002. */ 001030 sqlite3VdbeAddOp2(v, OP_Copy, regFromSelect+ipkColumn, regRowid); 001031 } 001032 } 001033 001034 /* Compute data for ordinary columns of the new entry. Values 001035 ** are written in storage order into registers starting with regData. 001036 ** Only ordinary columns are computed in this loop. The rowid 001037 ** (if there is one) is computed later and generated columns are 001038 ** computed after the rowid since they might depend on the value 001039 ** of the rowid. 001040 */ 001041 nHidden = 0; 001042 iRegStore = regData; assert( regData==regRowid+1 ); 001043 for(i=0; i<pTab->nCol; i++, iRegStore++){ 001044 int k; 001045 u32 colFlags; 001046 assert( i>=nHidden ); 001047 if( i==pTab->iPKey ){ 001048 /* tag-20191021-002: References to the INTEGER PRIMARY KEY are filled 001049 ** using the rowid. So put a NULL in the IPK slot of the record to avoid 001050 ** using excess space. The file format definition requires this extra 001051 ** NULL - we cannot optimize further by skipping the column completely */ 001052 sqlite3VdbeAddOp1(v, OP_SoftNull, iRegStore); 001053 continue; 001054 } 001055 if( ((colFlags = pTab->aCol[i].colFlags) & COLFLAG_NOINSERT)!=0 ){ 001056 nHidden++; 001057 if( (colFlags & COLFLAG_VIRTUAL)!=0 ){ 001058 /* Virtual columns do not participate in OP_MakeRecord. So back up 001059 ** iRegStore by one slot to compensate for the iRegStore++ in the 001060 ** outer for() loop */ 001061 iRegStore--; 001062 continue; 001063 }else if( (colFlags & COLFLAG_STORED)!=0 ){ 001064 /* Stored columns are computed later. But if there are BEFORE 001065 ** triggers, the slots used for stored columns will be OP_Copy-ed 001066 ** to a second block of registers, so the register needs to be 001067 ** initialized to NULL to avoid an uninitialized register read */ 001068 if( tmask & TRIGGER_BEFORE ){ 001069 sqlite3VdbeAddOp1(v, OP_SoftNull, iRegStore); 001070 } 001071 continue; 001072 }else if( pColumn==0 ){ 001073 /* Hidden columns that are not explicitly named in the INSERT 001074 ** get there default value */ 001075 sqlite3ExprCodeFactorable(pParse, pTab->aCol[i].pDflt, iRegStore); 001076 continue; 001077 } 001078 } 001079 if( pColumn ){ 001080 for(j=0; j<pColumn->nId && pColumn->a[j].idx!=i; j++){} 001081 if( j>=pColumn->nId ){ 001082 /* A column not named in the insert column list gets its 001083 ** default value */ 001084 sqlite3ExprCodeFactorable(pParse, pTab->aCol[i].pDflt, iRegStore); 001085 continue; 001086 } 001087 k = j; 001088 }else if( nColumn==0 ){ 001089 /* This is INSERT INTO ... DEFAULT VALUES. Load the default value. */ 001090 sqlite3ExprCodeFactorable(pParse, pTab->aCol[i].pDflt, iRegStore); 001091 continue; 001092 }else{ 001093 k = i - nHidden; 001094 } 001095 001096 if( useTempTable ){ 001097 sqlite3VdbeAddOp3(v, OP_Column, srcTab, k, iRegStore); 001098 }else if( pSelect ){ 001099 if( regFromSelect!=regData ){ 001100 sqlite3VdbeAddOp2(v, OP_SCopy, regFromSelect+k, iRegStore); 001101 } 001102 }else{ 001103 sqlite3ExprCode(pParse, pList->a[k].pExpr, iRegStore); 001104 } 001105 } 001106 001107 001108 /* Run the BEFORE and INSTEAD OF triggers, if there are any 001109 */ 001110 endOfLoop = sqlite3VdbeMakeLabel(pParse); 001111 if( tmask & TRIGGER_BEFORE ){ 001112 int regCols = sqlite3GetTempRange(pParse, pTab->nCol+1); 001113 001114 /* build the NEW.* reference row. Note that if there is an INTEGER 001115 ** PRIMARY KEY into which a NULL is being inserted, that NULL will be 001116 ** translated into a unique ID for the row. But on a BEFORE trigger, 001117 ** we do not know what the unique ID will be (because the insert has 001118 ** not happened yet) so we substitute a rowid of -1 001119 */ 001120 if( ipkColumn<0 ){ 001121 sqlite3VdbeAddOp2(v, OP_Integer, -1, regCols); 001122 }else{ 001123 int addr1; 001124 assert( !withoutRowid ); 001125 if( useTempTable ){ 001126 sqlite3VdbeAddOp3(v, OP_Column, srcTab, ipkColumn, regCols); 001127 }else{ 001128 assert( pSelect==0 ); /* Otherwise useTempTable is true */ 001129 sqlite3ExprCode(pParse, pList->a[ipkColumn].pExpr, regCols); 001130 } 001131 addr1 = sqlite3VdbeAddOp1(v, OP_NotNull, regCols); VdbeCoverage(v); 001132 sqlite3VdbeAddOp2(v, OP_Integer, -1, regCols); 001133 sqlite3VdbeJumpHere(v, addr1); 001134 sqlite3VdbeAddOp1(v, OP_MustBeInt, regCols); VdbeCoverage(v); 001135 } 001136 001137 /* Cannot have triggers on a virtual table. If it were possible, 001138 ** this block would have to account for hidden column. 001139 */ 001140 assert( !IsVirtual(pTab) ); 001141 001142 /* Copy the new data already generated. */ 001143 assert( pTab->nNVCol>0 ); 001144 sqlite3VdbeAddOp3(v, OP_Copy, regRowid+1, regCols+1, pTab->nNVCol-1); 001145 001146 #ifndef SQLITE_OMIT_GENERATED_COLUMNS 001147 /* Compute the new value for generated columns after all other 001148 ** columns have already been computed. This must be done after 001149 ** computing the ROWID in case one of the generated columns 001150 ** refers to the ROWID. */ 001151 if( pTab->tabFlags & TF_HasGenerated ){ 001152 testcase( pTab->tabFlags & TF_HasVirtual ); 001153 testcase( pTab->tabFlags & TF_HasStored ); 001154 sqlite3ComputeGeneratedColumns(pParse, regCols+1, pTab); 001155 } 001156 #endif 001157 001158 /* If this is an INSERT on a view with an INSTEAD OF INSERT trigger, 001159 ** do not attempt any conversions before assembling the record. 001160 ** If this is a real table, attempt conversions as required by the 001161 ** table column affinities. 001162 */ 001163 if( !isView ){ 001164 sqlite3TableAffinity(v, pTab, regCols+1); 001165 } 001166 001167 /* Fire BEFORE or INSTEAD OF triggers */ 001168 sqlite3CodeRowTrigger(pParse, pTrigger, TK_INSERT, 0, TRIGGER_BEFORE, 001169 pTab, regCols-pTab->nCol-1, onError, endOfLoop); 001170 001171 sqlite3ReleaseTempRange(pParse, regCols, pTab->nCol+1); 001172 } 001173 001174 if( !isView ){ 001175 if( IsVirtual(pTab) ){ 001176 /* The row that the VUpdate opcode will delete: none */ 001177 sqlite3VdbeAddOp2(v, OP_Null, 0, regIns); 001178 } 001179 if( ipkColumn>=0 ){ 001180 /* Compute the new rowid */ 001181 if( useTempTable ){ 001182 sqlite3VdbeAddOp3(v, OP_Column, srcTab, ipkColumn, regRowid); 001183 }else if( pSelect ){ 001184 /* Rowid already initialized at tag-20191021-001 */ 001185 }else{ 001186 Expr *pIpk = pList->a[ipkColumn].pExpr; 001187 if( pIpk->op==TK_NULL && !IsVirtual(pTab) ){ 001188 sqlite3VdbeAddOp3(v, OP_NewRowid, iDataCur, regRowid, regAutoinc); 001189 appendFlag = 1; 001190 }else{ 001191 sqlite3ExprCode(pParse, pList->a[ipkColumn].pExpr, regRowid); 001192 } 001193 } 001194 /* If the PRIMARY KEY expression is NULL, then use OP_NewRowid 001195 ** to generate a unique primary key value. 001196 */ 001197 if( !appendFlag ){ 001198 int addr1; 001199 if( !IsVirtual(pTab) ){ 001200 addr1 = sqlite3VdbeAddOp1(v, OP_NotNull, regRowid); VdbeCoverage(v); 001201 sqlite3VdbeAddOp3(v, OP_NewRowid, iDataCur, regRowid, regAutoinc); 001202 sqlite3VdbeJumpHere(v, addr1); 001203 }else{ 001204 addr1 = sqlite3VdbeCurrentAddr(v); 001205 sqlite3VdbeAddOp2(v, OP_IsNull, regRowid, addr1+2); VdbeCoverage(v); 001206 } 001207 sqlite3VdbeAddOp1(v, OP_MustBeInt, regRowid); VdbeCoverage(v); 001208 } 001209 }else if( IsVirtual(pTab) || withoutRowid ){ 001210 sqlite3VdbeAddOp2(v, OP_Null, 0, regRowid); 001211 }else{ 001212 sqlite3VdbeAddOp3(v, OP_NewRowid, iDataCur, regRowid, regAutoinc); 001213 appendFlag = 1; 001214 } 001215 autoIncStep(pParse, regAutoinc, regRowid); 001216 001217 #ifndef SQLITE_OMIT_GENERATED_COLUMNS 001218 /* Compute the new value for generated columns after all other 001219 ** columns have already been computed. This must be done after 001220 ** computing the ROWID in case one of the generated columns 001221 ** is derived from the INTEGER PRIMARY KEY. */ 001222 if( pTab->tabFlags & TF_HasGenerated ){ 001223 sqlite3ComputeGeneratedColumns(pParse, regRowid+1, pTab); 001224 } 001225 #endif 001226 001227 /* Generate code to check constraints and generate index keys and 001228 ** do the insertion. 001229 */ 001230 #ifndef SQLITE_OMIT_VIRTUALTABLE 001231 if( IsVirtual(pTab) ){ 001232 const char *pVTab = (const char *)sqlite3GetVTable(db, pTab); 001233 sqlite3VtabMakeWritable(pParse, pTab); 001234 sqlite3VdbeAddOp4(v, OP_VUpdate, 1, pTab->nCol+2, regIns, pVTab, P4_VTAB); 001235 sqlite3VdbeChangeP5(v, onError==OE_Default ? OE_Abort : onError); 001236 sqlite3MayAbort(pParse); 001237 }else 001238 #endif 001239 { 001240 int isReplace; /* Set to true if constraints may cause a replace */ 001241 int bUseSeek; /* True to use OPFLAG_SEEKRESULT */ 001242 sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur, 001243 regIns, 0, ipkColumn>=0, onError, endOfLoop, &isReplace, 0, pUpsert 001244 ); 001245 sqlite3FkCheck(pParse, pTab, 0, regIns, 0, 0); 001246 001247 /* Set the OPFLAG_USESEEKRESULT flag if either (a) there are no REPLACE 001248 ** constraints or (b) there are no triggers and this table is not a 001249 ** parent table in a foreign key constraint. It is safe to set the 001250 ** flag in the second case as if any REPLACE constraint is hit, an 001251 ** OP_Delete or OP_IdxDelete instruction will be executed on each 001252 ** cursor that is disturbed. And these instructions both clear the 001253 ** VdbeCursor.seekResult variable, disabling the OPFLAG_USESEEKRESULT 001254 ** functionality. */ 001255 bUseSeek = (isReplace==0 || !sqlite3VdbeHasSubProgram(v)); 001256 sqlite3CompleteInsertion(pParse, pTab, iDataCur, iIdxCur, 001257 regIns, aRegIdx, 0, appendFlag, bUseSeek 001258 ); 001259 } 001260 } 001261 001262 /* Update the count of rows that are inserted 001263 */ 001264 if( regRowCount ){ 001265 sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1); 001266 } 001267 001268 if( pTrigger ){ 001269 /* Code AFTER triggers */ 001270 sqlite3CodeRowTrigger(pParse, pTrigger, TK_INSERT, 0, TRIGGER_AFTER, 001271 pTab, regData-2-pTab->nCol, onError, endOfLoop); 001272 } 001273 001274 /* The bottom of the main insertion loop, if the data source 001275 ** is a SELECT statement. 001276 */ 001277 sqlite3VdbeResolveLabel(v, endOfLoop); 001278 if( useTempTable ){ 001279 sqlite3VdbeAddOp2(v, OP_Next, srcTab, addrCont); VdbeCoverage(v); 001280 sqlite3VdbeJumpHere(v, addrInsTop); 001281 sqlite3VdbeAddOp1(v, OP_Close, srcTab); 001282 }else if( pSelect ){ 001283 sqlite3VdbeGoto(v, addrCont); 001284 #ifdef SQLITE_DEBUG 001285 /* If we are jumping back to an OP_Yield that is preceded by an 001286 ** OP_ReleaseReg, set the p5 flag on the OP_Goto so that the 001287 ** OP_ReleaseReg will be included in the loop. */ 001288 if( sqlite3VdbeGetOp(v, addrCont-1)->opcode==OP_ReleaseReg ){ 001289 assert( sqlite3VdbeGetOp(v, addrCont)->opcode==OP_Yield ); 001290 sqlite3VdbeChangeP5(v, 1); 001291 } 001292 #endif 001293 sqlite3VdbeJumpHere(v, addrInsTop); 001294 } 001295 001296 insert_end: 001297 /* Update the sqlite_sequence table by storing the content of the 001298 ** maximum rowid counter values recorded while inserting into 001299 ** autoincrement tables. 001300 */ 001301 if( pParse->nested==0 && pParse->pTriggerTab==0 ){ 001302 sqlite3AutoincrementEnd(pParse); 001303 } 001304 001305 /* 001306 ** Return the number of rows inserted. If this routine is 001307 ** generating code because of a call to sqlite3NestedParse(), do not 001308 ** invoke the callback function. 001309 */ 001310 if( regRowCount ){ 001311 sqlite3VdbeAddOp2(v, OP_ResultRow, regRowCount, 1); 001312 sqlite3VdbeSetNumCols(v, 1); 001313 sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows inserted", SQLITE_STATIC); 001314 } 001315 001316 insert_cleanup: 001317 sqlite3SrcListDelete(db, pTabList); 001318 sqlite3ExprListDelete(db, pList); 001319 sqlite3UpsertDelete(db, pUpsert); 001320 sqlite3SelectDelete(db, pSelect); 001321 sqlite3IdListDelete(db, pColumn); 001322 sqlite3DbFree(db, aRegIdx); 001323 } 001324 001325 /* Make sure "isView" and other macros defined above are undefined. Otherwise 001326 ** they may interfere with compilation of other functions in this file 001327 ** (or in another file, if this file becomes part of the amalgamation). */ 001328 #ifdef isView 001329 #undef isView 001330 #endif 001331 #ifdef pTrigger 001332 #undef pTrigger 001333 #endif 001334 #ifdef tmask 001335 #undef tmask 001336 #endif 001337 001338 /* 001339 ** Meanings of bits in of pWalker->eCode for 001340 ** sqlite3ExprReferencesUpdatedColumn() 001341 */ 001342 #define CKCNSTRNT_COLUMN 0x01 /* CHECK constraint uses a changing column */ 001343 #define CKCNSTRNT_ROWID 0x02 /* CHECK constraint references the ROWID */ 001344 001345 /* This is the Walker callback from sqlite3ExprReferencesUpdatedColumn(). 001346 * Set bit 0x01 of pWalker->eCode if pWalker->eCode to 0 and if this 001347 ** expression node references any of the 001348 ** columns that are being modifed by an UPDATE statement. 001349 */ 001350 static int checkConstraintExprNode(Walker *pWalker, Expr *pExpr){ 001351 if( pExpr->op==TK_COLUMN ){ 001352 assert( pExpr->iColumn>=0 || pExpr->iColumn==-1 ); 001353 if( pExpr->iColumn>=0 ){ 001354 if( pWalker->u.aiCol[pExpr->iColumn]>=0 ){ 001355 pWalker->eCode |= CKCNSTRNT_COLUMN; 001356 } 001357 }else{ 001358 pWalker->eCode |= CKCNSTRNT_ROWID; 001359 } 001360 } 001361 return WRC_Continue; 001362 } 001363 001364 /* 001365 ** pExpr is a CHECK constraint on a row that is being UPDATE-ed. The 001366 ** only columns that are modified by the UPDATE are those for which 001367 ** aiChng[i]>=0, and also the ROWID is modified if chngRowid is true. 001368 ** 001369 ** Return true if CHECK constraint pExpr uses any of the 001370 ** changing columns (or the rowid if it is changing). In other words, 001371 ** return true if this CHECK constraint must be validated for 001372 ** the new row in the UPDATE statement. 001373 ** 001374 ** 2018-09-15: pExpr might also be an expression for an index-on-expressions. 001375 ** The operation of this routine is the same - return true if an only if 001376 ** the expression uses one or more of columns identified by the second and 001377 ** third arguments. 001378 */ 001379 int sqlite3ExprReferencesUpdatedColumn( 001380 Expr *pExpr, /* The expression to be checked */ 001381 int *aiChng, /* aiChng[x]>=0 if column x changed by the UPDATE */ 001382 int chngRowid /* True if UPDATE changes the rowid */ 001383 ){ 001384 Walker w; 001385 memset(&w, 0, sizeof(w)); 001386 w.eCode = 0; 001387 w.xExprCallback = checkConstraintExprNode; 001388 w.u.aiCol = aiChng; 001389 sqlite3WalkExpr(&w, pExpr); 001390 if( !chngRowid ){ 001391 testcase( (w.eCode & CKCNSTRNT_ROWID)!=0 ); 001392 w.eCode &= ~CKCNSTRNT_ROWID; 001393 } 001394 testcase( w.eCode==0 ); 001395 testcase( w.eCode==CKCNSTRNT_COLUMN ); 001396 testcase( w.eCode==CKCNSTRNT_ROWID ); 001397 testcase( w.eCode==(CKCNSTRNT_ROWID|CKCNSTRNT_COLUMN) ); 001398 return w.eCode!=0; 001399 } 001400 001401 /* 001402 ** Generate code to do constraint checks prior to an INSERT or an UPDATE 001403 ** on table pTab. 001404 ** 001405 ** The regNewData parameter is the first register in a range that contains 001406 ** the data to be inserted or the data after the update. There will be 001407 ** pTab->nCol+1 registers in this range. The first register (the one 001408 ** that regNewData points to) will contain the new rowid, or NULL in the 001409 ** case of a WITHOUT ROWID table. The second register in the range will 001410 ** contain the content of the first table column. The third register will 001411 ** contain the content of the second table column. And so forth. 001412 ** 001413 ** The regOldData parameter is similar to regNewData except that it contains 001414 ** the data prior to an UPDATE rather than afterwards. regOldData is zero 001415 ** for an INSERT. This routine can distinguish between UPDATE and INSERT by 001416 ** checking regOldData for zero. 001417 ** 001418 ** For an UPDATE, the pkChng boolean is true if the true primary key (the 001419 ** rowid for a normal table or the PRIMARY KEY for a WITHOUT ROWID table) 001420 ** might be modified by the UPDATE. If pkChng is false, then the key of 001421 ** the iDataCur content table is guaranteed to be unchanged by the UPDATE. 001422 ** 001423 ** For an INSERT, the pkChng boolean indicates whether or not the rowid 001424 ** was explicitly specified as part of the INSERT statement. If pkChng 001425 ** is zero, it means that the either rowid is computed automatically or 001426 ** that the table is a WITHOUT ROWID table and has no rowid. On an INSERT, 001427 ** pkChng will only be true if the INSERT statement provides an integer 001428 ** value for either the rowid column or its INTEGER PRIMARY KEY alias. 001429 ** 001430 ** The code generated by this routine will store new index entries into 001431 ** registers identified by aRegIdx[]. No index entry is created for 001432 ** indices where aRegIdx[i]==0. The order of indices in aRegIdx[] is 001433 ** the same as the order of indices on the linked list of indices 001434 ** at pTab->pIndex. 001435 ** 001436 ** (2019-05-07) The generated code also creates a new record for the 001437 ** main table, if pTab is a rowid table, and stores that record in the 001438 ** register identified by aRegIdx[nIdx] - in other words in the first 001439 ** entry of aRegIdx[] past the last index. It is important that the 001440 ** record be generated during constraint checks to avoid affinity changes 001441 ** to the register content that occur after constraint checks but before 001442 ** the new record is inserted. 001443 ** 001444 ** The caller must have already opened writeable cursors on the main 001445 ** table and all applicable indices (that is to say, all indices for which 001446 ** aRegIdx[] is not zero). iDataCur is the cursor for the main table when 001447 ** inserting or updating a rowid table, or the cursor for the PRIMARY KEY 001448 ** index when operating on a WITHOUT ROWID table. iIdxCur is the cursor 001449 ** for the first index in the pTab->pIndex list. Cursors for other indices 001450 ** are at iIdxCur+N for the N-th element of the pTab->pIndex list. 001451 ** 001452 ** This routine also generates code to check constraints. NOT NULL, 001453 ** CHECK, and UNIQUE constraints are all checked. If a constraint fails, 001454 ** then the appropriate action is performed. There are five possible 001455 ** actions: ROLLBACK, ABORT, FAIL, REPLACE, and IGNORE. 001456 ** 001457 ** Constraint type Action What Happens 001458 ** --------------- ---------- ---------------------------------------- 001459 ** any ROLLBACK The current transaction is rolled back and 001460 ** sqlite3_step() returns immediately with a 001461 ** return code of SQLITE_CONSTRAINT. 001462 ** 001463 ** any ABORT Back out changes from the current command 001464 ** only (do not do a complete rollback) then 001465 ** cause sqlite3_step() to return immediately 001466 ** with SQLITE_CONSTRAINT. 001467 ** 001468 ** any FAIL Sqlite3_step() returns immediately with a 001469 ** return code of SQLITE_CONSTRAINT. The 001470 ** transaction is not rolled back and any 001471 ** changes to prior rows are retained. 001472 ** 001473 ** any IGNORE The attempt in insert or update the current 001474 ** row is skipped, without throwing an error. 001475 ** Processing continues with the next row. 001476 ** (There is an immediate jump to ignoreDest.) 001477 ** 001478 ** NOT NULL REPLACE The NULL value is replace by the default 001479 ** value for that column. If the default value 001480 ** is NULL, the action is the same as ABORT. 001481 ** 001482 ** UNIQUE REPLACE The other row that conflicts with the row 001483 ** being inserted is removed. 001484 ** 001485 ** CHECK REPLACE Illegal. The results in an exception. 001486 ** 001487 ** Which action to take is determined by the overrideError parameter. 001488 ** Or if overrideError==OE_Default, then the pParse->onError parameter 001489 ** is used. Or if pParse->onError==OE_Default then the onError value 001490 ** for the constraint is used. 001491 */ 001492 void sqlite3GenerateConstraintChecks( 001493 Parse *pParse, /* The parser context */ 001494 Table *pTab, /* The table being inserted or updated */ 001495 int *aRegIdx, /* Use register aRegIdx[i] for index i. 0 for unused */ 001496 int iDataCur, /* Canonical data cursor (main table or PK index) */ 001497 int iIdxCur, /* First index cursor */ 001498 int regNewData, /* First register in a range holding values to insert */ 001499 int regOldData, /* Previous content. 0 for INSERTs */ 001500 u8 pkChng, /* Non-zero if the rowid or PRIMARY KEY changed */ 001501 u8 overrideError, /* Override onError to this if not OE_Default */ 001502 int ignoreDest, /* Jump to this label on an OE_Ignore resolution */ 001503 int *pbMayReplace, /* OUT: Set to true if constraint may cause a replace */ 001504 int *aiChng, /* column i is unchanged if aiChng[i]<0 */ 001505 Upsert *pUpsert /* ON CONFLICT clauses, if any. NULL otherwise */ 001506 ){ 001507 Vdbe *v; /* VDBE under constrution */ 001508 Index *pIdx; /* Pointer to one of the indices */ 001509 Index *pPk = 0; /* The PRIMARY KEY index */ 001510 sqlite3 *db; /* Database connection */ 001511 int i; /* loop counter */ 001512 int ix; /* Index loop counter */ 001513 int nCol; /* Number of columns */ 001514 int onError; /* Conflict resolution strategy */ 001515 int seenReplace = 0; /* True if REPLACE is used to resolve INT PK conflict */ 001516 int nPkField; /* Number of fields in PRIMARY KEY. 1 for ROWID tables */ 001517 Index *pUpIdx = 0; /* Index to which to apply the upsert */ 001518 u8 isUpdate; /* True if this is an UPDATE operation */ 001519 u8 bAffinityDone = 0; /* True if the OP_Affinity operation has been run */ 001520 int upsertBypass = 0; /* Address of Goto to bypass upsert subroutine */ 001521 int upsertJump = 0; /* Address of Goto that jumps into upsert subroutine */ 001522 int ipkTop = 0; /* Top of the IPK uniqueness check */ 001523 int ipkBottom = 0; /* OP_Goto at the end of the IPK uniqueness check */ 001524 /* Variables associated with retesting uniqueness constraints after 001525 ** replace triggers fire have run */ 001526 int regTrigCnt; /* Register used to count replace trigger invocations */ 001527 int addrRecheck = 0; /* Jump here to recheck all uniqueness constraints */ 001528 int lblRecheckOk = 0; /* Each recheck jumps to this label if it passes */ 001529 Trigger *pTrigger; /* List of DELETE triggers on the table pTab */ 001530 int nReplaceTrig = 0; /* Number of replace triggers coded */ 001531 001532 isUpdate = regOldData!=0; 001533 db = pParse->db; 001534 v = sqlite3GetVdbe(pParse); 001535 assert( v!=0 ); 001536 assert( pTab->pSelect==0 ); /* This table is not a VIEW */ 001537 nCol = pTab->nCol; 001538 001539 /* pPk is the PRIMARY KEY index for WITHOUT ROWID tables and NULL for 001540 ** normal rowid tables. nPkField is the number of key fields in the 001541 ** pPk index or 1 for a rowid table. In other words, nPkField is the 001542 ** number of fields in the true primary key of the table. */ 001543 if( HasRowid(pTab) ){ 001544 pPk = 0; 001545 nPkField = 1; 001546 }else{ 001547 pPk = sqlite3PrimaryKeyIndex(pTab); 001548 nPkField = pPk->nKeyCol; 001549 } 001550 001551 /* Record that this module has started */ 001552 VdbeModuleComment((v, "BEGIN: GenCnstCks(%d,%d,%d,%d,%d)", 001553 iDataCur, iIdxCur, regNewData, regOldData, pkChng)); 001554 001555 /* Test all NOT NULL constraints. 001556 */ 001557 if( pTab->tabFlags & TF_HasNotNull ){ 001558 int b2ndPass = 0; /* True if currently running 2nd pass */ 001559 int nSeenReplace = 0; /* Number of ON CONFLICT REPLACE operations */ 001560 int nGenerated = 0; /* Number of generated columns with NOT NULL */ 001561 while(1){ /* Make 2 passes over columns. Exit loop via "break" */ 001562 for(i=0; i<nCol; i++){ 001563 int iReg; /* Register holding column value */ 001564 Column *pCol = &pTab->aCol[i]; /* The column to check for NOT NULL */ 001565 int isGenerated; /* non-zero if column is generated */ 001566 onError = pCol->notNull; 001567 if( onError==OE_None ) continue; /* No NOT NULL on this column */ 001568 if( i==pTab->iPKey ){ 001569 continue; /* ROWID is never NULL */ 001570 } 001571 isGenerated = pCol->colFlags & COLFLAG_GENERATED; 001572 if( isGenerated && !b2ndPass ){ 001573 nGenerated++; 001574 continue; /* Generated columns processed on 2nd pass */ 001575 } 001576 if( aiChng && aiChng[i]<0 && !isGenerated ){ 001577 /* Do not check NOT NULL on columns that do not change */ 001578 continue; 001579 } 001580 if( overrideError!=OE_Default ){ 001581 onError = overrideError; 001582 }else if( onError==OE_Default ){ 001583 onError = OE_Abort; 001584 } 001585 if( onError==OE_Replace ){ 001586 if( b2ndPass /* REPLACE becomes ABORT on the 2nd pass */ 001587 || pCol->pDflt==0 /* REPLACE is ABORT if no DEFAULT value */ 001588 ){ 001589 testcase( pCol->colFlags & COLFLAG_VIRTUAL ); 001590 testcase( pCol->colFlags & COLFLAG_STORED ); 001591 testcase( pCol->colFlags & COLFLAG_GENERATED ); 001592 onError = OE_Abort; 001593 }else{ 001594 assert( !isGenerated ); 001595 } 001596 }else if( b2ndPass && !isGenerated ){ 001597 continue; 001598 } 001599 assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail 001600 || onError==OE_Ignore || onError==OE_Replace ); 001601 testcase( i!=sqlite3TableColumnToStorage(pTab, i) ); 001602 iReg = sqlite3TableColumnToStorage(pTab, i) + regNewData + 1; 001603 switch( onError ){ 001604 case OE_Replace: { 001605 int addr1 = sqlite3VdbeAddOp1(v, OP_NotNull, iReg); 001606 VdbeCoverage(v); 001607 assert( (pCol->colFlags & COLFLAG_GENERATED)==0 ); 001608 nSeenReplace++; 001609 sqlite3ExprCode(pParse, pCol->pDflt, iReg); 001610 sqlite3VdbeJumpHere(v, addr1); 001611 break; 001612 } 001613 case OE_Abort: 001614 sqlite3MayAbort(pParse); 001615 /* Fall through */ 001616 case OE_Rollback: 001617 case OE_Fail: { 001618 char *zMsg = sqlite3MPrintf(db, "%s.%s", pTab->zName, 001619 pCol->zName); 001620 sqlite3VdbeAddOp3(v, OP_HaltIfNull, SQLITE_CONSTRAINT_NOTNULL, 001621 onError, iReg); 001622 sqlite3VdbeAppendP4(v, zMsg, P4_DYNAMIC); 001623 sqlite3VdbeChangeP5(v, P5_ConstraintNotNull); 001624 VdbeCoverage(v); 001625 break; 001626 } 001627 default: { 001628 assert( onError==OE_Ignore ); 001629 sqlite3VdbeAddOp2(v, OP_IsNull, iReg, ignoreDest); 001630 VdbeCoverage(v); 001631 break; 001632 } 001633 } /* end switch(onError) */ 001634 } /* end loop i over columns */ 001635 if( nGenerated==0 && nSeenReplace==0 ){ 001636 /* If there are no generated columns with NOT NULL constraints 001637 ** and no NOT NULL ON CONFLICT REPLACE constraints, then a single 001638 ** pass is sufficient */ 001639 break; 001640 } 001641 if( b2ndPass ) break; /* Never need more than 2 passes */ 001642 b2ndPass = 1; 001643 if( nSeenReplace>0 && (pTab->tabFlags & TF_HasGenerated)!=0 ){ 001644 /* If any NOT NULL ON CONFLICT REPLACE constraints fired on the 001645 ** first pass, recomputed values for all generated columns, as 001646 ** those values might depend on columns affected by the REPLACE. 001647 */ 001648 sqlite3ComputeGeneratedColumns(pParse, regNewData+1, pTab); 001649 } 001650 } /* end of 2-pass loop */ 001651 } /* end if( has-not-null-constraints ) */ 001652 001653 /* Test all CHECK constraints 001654 */ 001655 #ifndef SQLITE_OMIT_CHECK 001656 if( pTab->pCheck && (db->flags & SQLITE_IgnoreChecks)==0 ){ 001657 ExprList *pCheck = pTab->pCheck; 001658 pParse->iSelfTab = -(regNewData+1); 001659 onError = overrideError!=OE_Default ? overrideError : OE_Abort; 001660 for(i=0; i<pCheck->nExpr; i++){ 001661 int allOk; 001662 Expr *pExpr = pCheck->a[i].pExpr; 001663 if( aiChng 001664 && !sqlite3ExprReferencesUpdatedColumn(pExpr, aiChng, pkChng) 001665 ){ 001666 /* The check constraints do not reference any of the columns being 001667 ** updated so there is no point it verifying the check constraint */ 001668 continue; 001669 } 001670 allOk = sqlite3VdbeMakeLabel(pParse); 001671 sqlite3VdbeVerifyAbortable(v, onError); 001672 sqlite3ExprIfTrue(pParse, pExpr, allOk, SQLITE_JUMPIFNULL); 001673 if( onError==OE_Ignore ){ 001674 sqlite3VdbeGoto(v, ignoreDest); 001675 }else{ 001676 char *zName = pCheck->a[i].zName; 001677 if( zName==0 ) zName = pTab->zName; 001678 if( onError==OE_Replace ) onError = OE_Abort; /* IMP: R-26383-51744 */ 001679 sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_CHECK, 001680 onError, zName, P4_TRANSIENT, 001681 P5_ConstraintCheck); 001682 } 001683 sqlite3VdbeResolveLabel(v, allOk); 001684 } 001685 pParse->iSelfTab = 0; 001686 } 001687 #endif /* !defined(SQLITE_OMIT_CHECK) */ 001688 001689 /* UNIQUE and PRIMARY KEY constraints should be handled in the following 001690 ** order: 001691 ** 001692 ** (1) OE_Update 001693 ** (2) OE_Abort, OE_Fail, OE_Rollback, OE_Ignore 001694 ** (3) OE_Replace 001695 ** 001696 ** OE_Fail and OE_Ignore must happen before any changes are made. 001697 ** OE_Update guarantees that only a single row will change, so it 001698 ** must happen before OE_Replace. Technically, OE_Abort and OE_Rollback 001699 ** could happen in any order, but they are grouped up front for 001700 ** convenience. 001701 ** 001702 ** 2018-08-14: Ticket https://www.sqlite.org/src/info/908f001483982c43 001703 ** The order of constraints used to have OE_Update as (2) and OE_Abort 001704 ** and so forth as (1). But apparently PostgreSQL checks the OE_Update 001705 ** constraint before any others, so it had to be moved. 001706 ** 001707 ** Constraint checking code is generated in this order: 001708 ** (A) The rowid constraint 001709 ** (B) Unique index constraints that do not have OE_Replace as their 001710 ** default conflict resolution strategy 001711 ** (C) Unique index that do use OE_Replace by default. 001712 ** 001713 ** The ordering of (2) and (3) is accomplished by making sure the linked 001714 ** list of indexes attached to a table puts all OE_Replace indexes last 001715 ** in the list. See sqlite3CreateIndex() for where that happens. 001716 */ 001717 001718 if( pUpsert ){ 001719 if( pUpsert->pUpsertTarget==0 ){ 001720 /* An ON CONFLICT DO NOTHING clause, without a constraint-target. 001721 ** Make all unique constraint resolution be OE_Ignore */ 001722 assert( pUpsert->pUpsertSet==0 ); 001723 overrideError = OE_Ignore; 001724 pUpsert = 0; 001725 }else if( (pUpIdx = pUpsert->pUpsertIdx)!=0 ){ 001726 /* If the constraint-target uniqueness check must be run first. 001727 ** Jump to that uniqueness check now */ 001728 upsertJump = sqlite3VdbeAddOp0(v, OP_Goto); 001729 VdbeComment((v, "UPSERT constraint goes first")); 001730 } 001731 } 001732 001733 /* Determine if it is possible that triggers (either explicitly coded 001734 ** triggers or FK resolution actions) might run as a result of deletes 001735 ** that happen when OE_Replace conflict resolution occurs. (Call these 001736 ** "replace triggers".) If any replace triggers run, we will need to 001737 ** recheck all of the uniqueness constraints after they have all run. 001738 ** But on the recheck, the resolution is OE_Abort instead of OE_Replace. 001739 ** 001740 ** If replace triggers are a possibility, then 001741 ** 001742 ** (1) Allocate register regTrigCnt and initialize it to zero. 001743 ** That register will count the number of replace triggers that 001744 ** fire. Constraint recheck only occurs if the number is positive. 001745 ** (2) Initialize pTrigger to the list of all DELETE triggers on pTab. 001746 ** (3) Initialize addrRecheck and lblRecheckOk 001747 ** 001748 ** The uniqueness rechecking code will create a series of tests to run 001749 ** in a second pass. The addrRecheck and lblRecheckOk variables are 001750 ** used to link together these tests which are separated from each other 001751 ** in the generate bytecode. 001752 */ 001753 if( (db->flags & (SQLITE_RecTriggers|SQLITE_ForeignKeys))==0 ){ 001754 /* There are not DELETE triggers nor FK constraints. No constraint 001755 ** rechecks are needed. */ 001756 pTrigger = 0; 001757 regTrigCnt = 0; 001758 }else{ 001759 if( db->flags&SQLITE_RecTriggers ){ 001760 pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0); 001761 regTrigCnt = pTrigger!=0 || sqlite3FkRequired(pParse, pTab, 0, 0); 001762 }else{ 001763 pTrigger = 0; 001764 regTrigCnt = sqlite3FkRequired(pParse, pTab, 0, 0); 001765 } 001766 if( regTrigCnt ){ 001767 /* Replace triggers might exist. Allocate the counter and 001768 ** initialize it to zero. */ 001769 regTrigCnt = ++pParse->nMem; 001770 sqlite3VdbeAddOp2(v, OP_Integer, 0, regTrigCnt); 001771 VdbeComment((v, "trigger count")); 001772 lblRecheckOk = sqlite3VdbeMakeLabel(pParse); 001773 addrRecheck = lblRecheckOk; 001774 } 001775 } 001776 001777 /* If rowid is changing, make sure the new rowid does not previously 001778 ** exist in the table. 001779 */ 001780 if( pkChng && pPk==0 ){ 001781 int addrRowidOk = sqlite3VdbeMakeLabel(pParse); 001782 001783 /* Figure out what action to take in case of a rowid collision */ 001784 onError = pTab->keyConf; 001785 if( overrideError!=OE_Default ){ 001786 onError = overrideError; 001787 }else if( onError==OE_Default ){ 001788 onError = OE_Abort; 001789 } 001790 001791 /* figure out whether or not upsert applies in this case */ 001792 if( pUpsert && pUpsert->pUpsertIdx==0 ){ 001793 if( pUpsert->pUpsertSet==0 ){ 001794 onError = OE_Ignore; /* DO NOTHING is the same as INSERT OR IGNORE */ 001795 }else{ 001796 onError = OE_Update; /* DO UPDATE */ 001797 } 001798 } 001799 001800 /* If the response to a rowid conflict is REPLACE but the response 001801 ** to some other UNIQUE constraint is FAIL or IGNORE, then we need 001802 ** to defer the running of the rowid conflict checking until after 001803 ** the UNIQUE constraints have run. 001804 */ 001805 if( onError==OE_Replace /* IPK rule is REPLACE */ 001806 && onError!=overrideError /* Rules for other contraints are different */ 001807 && pTab->pIndex /* There exist other constraints */ 001808 ){ 001809 ipkTop = sqlite3VdbeAddOp0(v, OP_Goto)+1; 001810 VdbeComment((v, "defer IPK REPLACE until last")); 001811 } 001812 001813 if( isUpdate ){ 001814 /* pkChng!=0 does not mean that the rowid has changed, only that 001815 ** it might have changed. Skip the conflict logic below if the rowid 001816 ** is unchanged. */ 001817 sqlite3VdbeAddOp3(v, OP_Eq, regNewData, addrRowidOk, regOldData); 001818 sqlite3VdbeChangeP5(v, SQLITE_NOTNULL); 001819 VdbeCoverage(v); 001820 } 001821 001822 /* Check to see if the new rowid already exists in the table. Skip 001823 ** the following conflict logic if it does not. */ 001824 VdbeNoopComment((v, "uniqueness check for ROWID")); 001825 sqlite3VdbeVerifyAbortable(v, onError); 001826 sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, addrRowidOk, regNewData); 001827 VdbeCoverage(v); 001828 001829 switch( onError ){ 001830 default: { 001831 onError = OE_Abort; 001832 /* Fall thru into the next case */ 001833 } 001834 case OE_Rollback: 001835 case OE_Abort: 001836 case OE_Fail: { 001837 testcase( onError==OE_Rollback ); 001838 testcase( onError==OE_Abort ); 001839 testcase( onError==OE_Fail ); 001840 sqlite3RowidConstraint(pParse, onError, pTab); 001841 break; 001842 } 001843 case OE_Replace: { 001844 /* If there are DELETE triggers on this table and the 001845 ** recursive-triggers flag is set, call GenerateRowDelete() to 001846 ** remove the conflicting row from the table. This will fire 001847 ** the triggers and remove both the table and index b-tree entries. 001848 ** 001849 ** Otherwise, if there are no triggers or the recursive-triggers 001850 ** flag is not set, but the table has one or more indexes, call 001851 ** GenerateRowIndexDelete(). This removes the index b-tree entries 001852 ** only. The table b-tree entry will be replaced by the new entry 001853 ** when it is inserted. 001854 ** 001855 ** If either GenerateRowDelete() or GenerateRowIndexDelete() is called, 001856 ** also invoke MultiWrite() to indicate that this VDBE may require 001857 ** statement rollback (if the statement is aborted after the delete 001858 ** takes place). Earlier versions called sqlite3MultiWrite() regardless, 001859 ** but being more selective here allows statements like: 001860 ** 001861 ** REPLACE INTO t(rowid) VALUES($newrowid) 001862 ** 001863 ** to run without a statement journal if there are no indexes on the 001864 ** table. 001865 */ 001866 if( regTrigCnt ){ 001867 sqlite3MultiWrite(pParse); 001868 sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur, 001869 regNewData, 1, 0, OE_Replace, 1, -1); 001870 sqlite3VdbeAddOp2(v, OP_AddImm, regTrigCnt, 1); /* incr trigger cnt */ 001871 nReplaceTrig++; 001872 }else{ 001873 #ifdef SQLITE_ENABLE_PREUPDATE_HOOK 001874 assert( HasRowid(pTab) ); 001875 /* This OP_Delete opcode fires the pre-update-hook only. It does 001876 ** not modify the b-tree. It is more efficient to let the coming 001877 ** OP_Insert replace the existing entry than it is to delete the 001878 ** existing entry and then insert a new one. */ 001879 sqlite3VdbeAddOp2(v, OP_Delete, iDataCur, OPFLAG_ISNOOP); 001880 sqlite3VdbeAppendP4(v, pTab, P4_TABLE); 001881 #endif /* SQLITE_ENABLE_PREUPDATE_HOOK */ 001882 if( pTab->pIndex ){ 001883 sqlite3MultiWrite(pParse); 001884 sqlite3GenerateRowIndexDelete(pParse, pTab, iDataCur, iIdxCur,0,-1); 001885 } 001886 } 001887 seenReplace = 1; 001888 break; 001889 } 001890 #ifndef SQLITE_OMIT_UPSERT 001891 case OE_Update: { 001892 sqlite3UpsertDoUpdate(pParse, pUpsert, pTab, 0, iDataCur); 001893 /* Fall through */ 001894 } 001895 #endif 001896 case OE_Ignore: { 001897 testcase( onError==OE_Ignore ); 001898 sqlite3VdbeGoto(v, ignoreDest); 001899 break; 001900 } 001901 } 001902 sqlite3VdbeResolveLabel(v, addrRowidOk); 001903 if( ipkTop ){ 001904 ipkBottom = sqlite3VdbeAddOp0(v, OP_Goto); 001905 sqlite3VdbeJumpHere(v, ipkTop-1); 001906 } 001907 } 001908 001909 /* Test all UNIQUE constraints by creating entries for each UNIQUE 001910 ** index and making sure that duplicate entries do not already exist. 001911 ** Compute the revised record entries for indices as we go. 001912 ** 001913 ** This loop also handles the case of the PRIMARY KEY index for a 001914 ** WITHOUT ROWID table. 001915 */ 001916 for(ix=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, ix++){ 001917 int regIdx; /* Range of registers hold conent for pIdx */ 001918 int regR; /* Range of registers holding conflicting PK */ 001919 int iThisCur; /* Cursor for this UNIQUE index */ 001920 int addrUniqueOk; /* Jump here if the UNIQUE constraint is satisfied */ 001921 int addrConflictCk; /* First opcode in the conflict check logic */ 001922 001923 if( aRegIdx[ix]==0 ) continue; /* Skip indices that do not change */ 001924 if( pUpIdx==pIdx ){ 001925 addrUniqueOk = upsertJump+1; 001926 upsertBypass = sqlite3VdbeGoto(v, 0); 001927 VdbeComment((v, "Skip upsert subroutine")); 001928 sqlite3VdbeJumpHere(v, upsertJump); 001929 }else{ 001930 addrUniqueOk = sqlite3VdbeMakeLabel(pParse); 001931 } 001932 if( bAffinityDone==0 && (pUpIdx==0 || pUpIdx==pIdx) ){ 001933 sqlite3TableAffinity(v, pTab, regNewData+1); 001934 bAffinityDone = 1; 001935 } 001936 VdbeNoopComment((v, "uniqueness check for %s", pIdx->zName)); 001937 iThisCur = iIdxCur+ix; 001938 001939 001940 /* Skip partial indices for which the WHERE clause is not true */ 001941 if( pIdx->pPartIdxWhere ){ 001942 sqlite3VdbeAddOp2(v, OP_Null, 0, aRegIdx[ix]); 001943 pParse->iSelfTab = -(regNewData+1); 001944 sqlite3ExprIfFalseDup(pParse, pIdx->pPartIdxWhere, addrUniqueOk, 001945 SQLITE_JUMPIFNULL); 001946 pParse->iSelfTab = 0; 001947 } 001948 001949 /* Create a record for this index entry as it should appear after 001950 ** the insert or update. Store that record in the aRegIdx[ix] register 001951 */ 001952 regIdx = aRegIdx[ix]+1; 001953 for(i=0; i<pIdx->nColumn; i++){ 001954 int iField = pIdx->aiColumn[i]; 001955 int x; 001956 if( iField==XN_EXPR ){ 001957 pParse->iSelfTab = -(regNewData+1); 001958 sqlite3ExprCodeCopy(pParse, pIdx->aColExpr->a[i].pExpr, regIdx+i); 001959 pParse->iSelfTab = 0; 001960 VdbeComment((v, "%s column %d", pIdx->zName, i)); 001961 }else if( iField==XN_ROWID || iField==pTab->iPKey ){ 001962 x = regNewData; 001963 sqlite3VdbeAddOp2(v, OP_IntCopy, x, regIdx+i); 001964 VdbeComment((v, "rowid")); 001965 }else{ 001966 testcase( sqlite3TableColumnToStorage(pTab, iField)!=iField ); 001967 x = sqlite3TableColumnToStorage(pTab, iField) + regNewData + 1; 001968 sqlite3VdbeAddOp2(v, OP_SCopy, x, regIdx+i); 001969 VdbeComment((v, "%s", pTab->aCol[iField].zName)); 001970 } 001971 } 001972 sqlite3VdbeAddOp3(v, OP_MakeRecord, regIdx, pIdx->nColumn, aRegIdx[ix]); 001973 VdbeComment((v, "for %s", pIdx->zName)); 001974 #ifdef SQLITE_ENABLE_NULL_TRIM 001975 if( pIdx->idxType==SQLITE_IDXTYPE_PRIMARYKEY ){ 001976 sqlite3SetMakeRecordP5(v, pIdx->pTable); 001977 } 001978 #endif 001979 001980 /* In an UPDATE operation, if this index is the PRIMARY KEY index 001981 ** of a WITHOUT ROWID table and there has been no change the 001982 ** primary key, then no collision is possible. The collision detection 001983 ** logic below can all be skipped. */ 001984 if( isUpdate && pPk==pIdx && pkChng==0 ){ 001985 sqlite3VdbeResolveLabel(v, addrUniqueOk); 001986 continue; 001987 } 001988 001989 /* Find out what action to take in case there is a uniqueness conflict */ 001990 onError = pIdx->onError; 001991 if( onError==OE_None ){ 001992 sqlite3VdbeResolveLabel(v, addrUniqueOk); 001993 continue; /* pIdx is not a UNIQUE index */ 001994 } 001995 if( overrideError!=OE_Default ){ 001996 onError = overrideError; 001997 }else if( onError==OE_Default ){ 001998 onError = OE_Abort; 001999 } 002000 002001 /* Figure out if the upsert clause applies to this index */ 002002 if( pUpIdx==pIdx ){ 002003 if( pUpsert->pUpsertSet==0 ){ 002004 onError = OE_Ignore; /* DO NOTHING is the same as INSERT OR IGNORE */ 002005 }else{ 002006 onError = OE_Update; /* DO UPDATE */ 002007 } 002008 } 002009 002010 /* Collision detection may be omitted if all of the following are true: 002011 ** (1) The conflict resolution algorithm is REPLACE 002012 ** (2) The table is a WITHOUT ROWID table 002013 ** (3) There are no secondary indexes on the table 002014 ** (4) No delete triggers need to be fired if there is a conflict 002015 ** (5) No FK constraint counters need to be updated if a conflict occurs. 002016 ** 002017 ** This is not possible for ENABLE_PREUPDATE_HOOK builds, as the row 002018 ** must be explicitly deleted in order to ensure any pre-update hook 002019 ** is invoked. */ 002020 #ifndef SQLITE_ENABLE_PREUPDATE_HOOK 002021 if( (ix==0 && pIdx->pNext==0) /* Condition 3 */ 002022 && pPk==pIdx /* Condition 2 */ 002023 && onError==OE_Replace /* Condition 1 */ 002024 && ( 0==(db->flags&SQLITE_RecTriggers) || /* Condition 4 */ 002025 0==sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0)) 002026 && ( 0==(db->flags&SQLITE_ForeignKeys) || /* Condition 5 */ 002027 (0==pTab->pFKey && 0==sqlite3FkReferences(pTab))) 002028 ){ 002029 sqlite3VdbeResolveLabel(v, addrUniqueOk); 002030 continue; 002031 } 002032 #endif /* ifndef SQLITE_ENABLE_PREUPDATE_HOOK */ 002033 002034 /* Check to see if the new index entry will be unique */ 002035 sqlite3VdbeVerifyAbortable(v, onError); 002036 addrConflictCk = 002037 sqlite3VdbeAddOp4Int(v, OP_NoConflict, iThisCur, addrUniqueOk, 002038 regIdx, pIdx->nKeyCol); VdbeCoverage(v); 002039 002040 /* Generate code to handle collisions */ 002041 regR = (pIdx==pPk) ? regIdx : sqlite3GetTempRange(pParse, nPkField); 002042 if( isUpdate || onError==OE_Replace ){ 002043 if( HasRowid(pTab) ){ 002044 sqlite3VdbeAddOp2(v, OP_IdxRowid, iThisCur, regR); 002045 /* Conflict only if the rowid of the existing index entry 002046 ** is different from old-rowid */ 002047 if( isUpdate ){ 002048 sqlite3VdbeAddOp3(v, OP_Eq, regR, addrUniqueOk, regOldData); 002049 sqlite3VdbeChangeP5(v, SQLITE_NOTNULL); 002050 VdbeCoverage(v); 002051 } 002052 }else{ 002053 int x; 002054 /* Extract the PRIMARY KEY from the end of the index entry and 002055 ** store it in registers regR..regR+nPk-1 */ 002056 if( pIdx!=pPk ){ 002057 for(i=0; i<pPk->nKeyCol; i++){ 002058 assert( pPk->aiColumn[i]>=0 ); 002059 x = sqlite3TableColumnToIndex(pIdx, pPk->aiColumn[i]); 002060 sqlite3VdbeAddOp3(v, OP_Column, iThisCur, x, regR+i); 002061 VdbeComment((v, "%s.%s", pTab->zName, 002062 pTab->aCol[pPk->aiColumn[i]].zName)); 002063 } 002064 } 002065 if( isUpdate ){ 002066 /* If currently processing the PRIMARY KEY of a WITHOUT ROWID 002067 ** table, only conflict if the new PRIMARY KEY values are actually 002068 ** different from the old. 002069 ** 002070 ** For a UNIQUE index, only conflict if the PRIMARY KEY values 002071 ** of the matched index row are different from the original PRIMARY 002072 ** KEY values of this row before the update. */ 002073 int addrJump = sqlite3VdbeCurrentAddr(v)+pPk->nKeyCol; 002074 int op = OP_Ne; 002075 int regCmp = (IsPrimaryKeyIndex(pIdx) ? regIdx : regR); 002076 002077 for(i=0; i<pPk->nKeyCol; i++){ 002078 char *p4 = (char*)sqlite3LocateCollSeq(pParse, pPk->azColl[i]); 002079 x = pPk->aiColumn[i]; 002080 assert( x>=0 ); 002081 if( i==(pPk->nKeyCol-1) ){ 002082 addrJump = addrUniqueOk; 002083 op = OP_Eq; 002084 } 002085 x = sqlite3TableColumnToStorage(pTab, x); 002086 sqlite3VdbeAddOp4(v, op, 002087 regOldData+1+x, addrJump, regCmp+i, p4, P4_COLLSEQ 002088 ); 002089 sqlite3VdbeChangeP5(v, SQLITE_NOTNULL); 002090 VdbeCoverageIf(v, op==OP_Eq); 002091 VdbeCoverageIf(v, op==OP_Ne); 002092 } 002093 } 002094 } 002095 } 002096 002097 /* Generate code that executes if the new index entry is not unique */ 002098 assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail 002099 || onError==OE_Ignore || onError==OE_Replace || onError==OE_Update ); 002100 switch( onError ){ 002101 case OE_Rollback: 002102 case OE_Abort: 002103 case OE_Fail: { 002104 testcase( onError==OE_Rollback ); 002105 testcase( onError==OE_Abort ); 002106 testcase( onError==OE_Fail ); 002107 sqlite3UniqueConstraint(pParse, onError, pIdx); 002108 break; 002109 } 002110 #ifndef SQLITE_OMIT_UPSERT 002111 case OE_Update: { 002112 sqlite3UpsertDoUpdate(pParse, pUpsert, pTab, pIdx, iIdxCur+ix); 002113 /* Fall through */ 002114 } 002115 #endif 002116 case OE_Ignore: { 002117 testcase( onError==OE_Ignore ); 002118 sqlite3VdbeGoto(v, ignoreDest); 002119 break; 002120 } 002121 default: { 002122 int nConflictCk; /* Number of opcodes in conflict check logic */ 002123 002124 assert( onError==OE_Replace ); 002125 nConflictCk = sqlite3VdbeCurrentAddr(v) - addrConflictCk; 002126 assert( nConflictCk>0 ); 002127 testcase( nConflictCk>1 ); 002128 if( regTrigCnt ){ 002129 sqlite3MultiWrite(pParse); 002130 nReplaceTrig++; 002131 } 002132 sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur, 002133 regR, nPkField, 0, OE_Replace, 002134 (pIdx==pPk ? ONEPASS_SINGLE : ONEPASS_OFF), iThisCur); 002135 if( regTrigCnt ){ 002136 int addrBypass; /* Jump destination to bypass recheck logic */ 002137 002138 sqlite3VdbeAddOp2(v, OP_AddImm, regTrigCnt, 1); /* incr trigger cnt */ 002139 addrBypass = sqlite3VdbeAddOp0(v, OP_Goto); /* Bypass recheck */ 002140 VdbeComment((v, "bypass recheck")); 002141 002142 /* Here we insert code that will be invoked after all constraint 002143 ** checks have run, if and only if one or more replace triggers 002144 ** fired. */ 002145 sqlite3VdbeResolveLabel(v, lblRecheckOk); 002146 lblRecheckOk = sqlite3VdbeMakeLabel(pParse); 002147 if( pIdx->pPartIdxWhere ){ 002148 /* Bypass the recheck if this partial index is not defined 002149 ** for the current row */ 002150 sqlite3VdbeAddOp2(v, OP_IsNull, regIdx-1, lblRecheckOk); 002151 VdbeCoverage(v); 002152 } 002153 /* Copy the constraint check code from above, except change 002154 ** the constraint-ok jump destination to be the address of 002155 ** the next retest block */ 002156 while( nConflictCk>0 ){ 002157 VdbeOp x; /* Conflict check opcode to copy */ 002158 /* The sqlite3VdbeAddOp4() call might reallocate the opcode array. 002159 ** Hence, make a complete copy of the opcode, rather than using 002160 ** a pointer to the opcode. */ 002161 x = *sqlite3VdbeGetOp(v, addrConflictCk); 002162 if( x.opcode!=OP_IdxRowid ){ 002163 int p2; /* New P2 value for copied conflict check opcode */ 002164 if( sqlite3OpcodeProperty[x.opcode]&OPFLG_JUMP ){ 002165 p2 = lblRecheckOk; 002166 }else{ 002167 p2 = x.p2; 002168 } 002169 sqlite3VdbeAddOp4(v, x.opcode, x.p1, p2, x.p3, x.p4.z, x.p4type); 002170 sqlite3VdbeChangeP5(v, x.p5); 002171 VdbeCoverageIf(v, p2!=x.p2); 002172 } 002173 nConflictCk--; 002174 addrConflictCk++; 002175 } 002176 /* If the retest fails, issue an abort */ 002177 sqlite3UniqueConstraint(pParse, OE_Abort, pIdx); 002178 002179 sqlite3VdbeJumpHere(v, addrBypass); /* Terminate the recheck bypass */ 002180 } 002181 seenReplace = 1; 002182 break; 002183 } 002184 } 002185 if( pUpIdx==pIdx ){ 002186 sqlite3VdbeGoto(v, upsertJump+1); 002187 sqlite3VdbeJumpHere(v, upsertBypass); 002188 }else{ 002189 sqlite3VdbeResolveLabel(v, addrUniqueOk); 002190 } 002191 if( regR!=regIdx ) sqlite3ReleaseTempRange(pParse, regR, nPkField); 002192 } 002193 002194 /* If the IPK constraint is a REPLACE, run it last */ 002195 if( ipkTop ){ 002196 sqlite3VdbeGoto(v, ipkTop); 002197 VdbeComment((v, "Do IPK REPLACE")); 002198 sqlite3VdbeJumpHere(v, ipkBottom); 002199 } 002200 002201 /* Recheck all uniqueness constraints after replace triggers have run */ 002202 testcase( regTrigCnt!=0 && nReplaceTrig==0 ); 002203 assert( regTrigCnt!=0 || nReplaceTrig==0 ); 002204 if( nReplaceTrig ){ 002205 sqlite3VdbeAddOp2(v, OP_IfNot, regTrigCnt, lblRecheckOk);VdbeCoverage(v); 002206 if( !pPk ){ 002207 if( isUpdate ){ 002208 sqlite3VdbeAddOp3(v, OP_Eq, regNewData, addrRecheck, regOldData); 002209 sqlite3VdbeChangeP5(v, SQLITE_NOTNULL); 002210 VdbeCoverage(v); 002211 } 002212 sqlite3VdbeAddOp3(v, OP_NotExists, iDataCur, addrRecheck, regNewData); 002213 VdbeCoverage(v); 002214 sqlite3RowidConstraint(pParse, OE_Abort, pTab); 002215 }else{ 002216 sqlite3VdbeGoto(v, addrRecheck); 002217 } 002218 sqlite3VdbeResolveLabel(v, lblRecheckOk); 002219 } 002220 002221 /* Generate the table record */ 002222 if( HasRowid(pTab) ){ 002223 int regRec = aRegIdx[ix]; 002224 sqlite3VdbeAddOp3(v, OP_MakeRecord, regNewData+1, pTab->nNVCol, regRec); 002225 sqlite3SetMakeRecordP5(v, pTab); 002226 if( !bAffinityDone ){ 002227 sqlite3TableAffinity(v, pTab, 0); 002228 } 002229 } 002230 002231 *pbMayReplace = seenReplace; 002232 VdbeModuleComment((v, "END: GenCnstCks(%d)", seenReplace)); 002233 } 002234 002235 #ifdef SQLITE_ENABLE_NULL_TRIM 002236 /* 002237 ** Change the P5 operand on the last opcode (which should be an OP_MakeRecord) 002238 ** to be the number of columns in table pTab that must not be NULL-trimmed. 002239 ** 002240 ** Or if no columns of pTab may be NULL-trimmed, leave P5 at zero. 002241 */ 002242 void sqlite3SetMakeRecordP5(Vdbe *v, Table *pTab){ 002243 u16 i; 002244 002245 /* Records with omitted columns are only allowed for schema format 002246 ** version 2 and later (SQLite version 3.1.4, 2005-02-20). */ 002247 if( pTab->pSchema->file_format<2 ) return; 002248 002249 for(i=pTab->nCol-1; i>0; i--){ 002250 if( pTab->aCol[i].pDflt!=0 ) break; 002251 if( pTab->aCol[i].colFlags & COLFLAG_PRIMKEY ) break; 002252 } 002253 sqlite3VdbeChangeP5(v, i+1); 002254 } 002255 #endif 002256 002257 /* 002258 ** This routine generates code to finish the INSERT or UPDATE operation 002259 ** that was started by a prior call to sqlite3GenerateConstraintChecks. 002260 ** A consecutive range of registers starting at regNewData contains the 002261 ** rowid and the content to be inserted. 002262 ** 002263 ** The arguments to this routine should be the same as the first six 002264 ** arguments to sqlite3GenerateConstraintChecks. 002265 */ 002266 void sqlite3CompleteInsertion( 002267 Parse *pParse, /* The parser context */ 002268 Table *pTab, /* the table into which we are inserting */ 002269 int iDataCur, /* Cursor of the canonical data source */ 002270 int iIdxCur, /* First index cursor */ 002271 int regNewData, /* Range of content */ 002272 int *aRegIdx, /* Register used by each index. 0 for unused indices */ 002273 int update_flags, /* True for UPDATE, False for INSERT */ 002274 int appendBias, /* True if this is likely to be an append */ 002275 int useSeekResult /* True to set the USESEEKRESULT flag on OP_[Idx]Insert */ 002276 ){ 002277 Vdbe *v; /* Prepared statements under construction */ 002278 Index *pIdx; /* An index being inserted or updated */ 002279 u8 pik_flags; /* flag values passed to the btree insert */ 002280 int i; /* Loop counter */ 002281 002282 assert( update_flags==0 002283 || update_flags==OPFLAG_ISUPDATE 002284 || update_flags==(OPFLAG_ISUPDATE|OPFLAG_SAVEPOSITION) 002285 ); 002286 002287 v = sqlite3GetVdbe(pParse); 002288 assert( v!=0 ); 002289 assert( pTab->pSelect==0 ); /* This table is not a VIEW */ 002290 for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){ 002291 /* All REPLACE indexes are at the end of the list */ 002292 assert( pIdx->onError!=OE_Replace 002293 || pIdx->pNext==0 002294 || pIdx->pNext->onError==OE_Replace ); 002295 if( aRegIdx[i]==0 ) continue; 002296 if( pIdx->pPartIdxWhere ){ 002297 sqlite3VdbeAddOp2(v, OP_IsNull, aRegIdx[i], sqlite3VdbeCurrentAddr(v)+2); 002298 VdbeCoverage(v); 002299 } 002300 pik_flags = (useSeekResult ? OPFLAG_USESEEKRESULT : 0); 002301 if( IsPrimaryKeyIndex(pIdx) && !HasRowid(pTab) ){ 002302 assert( pParse->nested==0 ); 002303 pik_flags |= OPFLAG_NCHANGE; 002304 pik_flags |= (update_flags & OPFLAG_SAVEPOSITION); 002305 #ifdef SQLITE_ENABLE_PREUPDATE_HOOK 002306 if( update_flags==0 ){ 002307 int r = sqlite3GetTempReg(pParse); 002308 sqlite3VdbeAddOp2(v, OP_Integer, 0, r); 002309 sqlite3VdbeAddOp4(v, OP_Insert, 002310 iIdxCur+i, aRegIdx[i], r, (char*)pTab, P4_TABLE 002311 ); 002312 sqlite3VdbeChangeP5(v, OPFLAG_ISNOOP); 002313 sqlite3ReleaseTempReg(pParse, r); 002314 } 002315 #endif 002316 } 002317 sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iIdxCur+i, aRegIdx[i], 002318 aRegIdx[i]+1, 002319 pIdx->uniqNotNull ? pIdx->nKeyCol: pIdx->nColumn); 002320 sqlite3VdbeChangeP5(v, pik_flags); 002321 } 002322 if( !HasRowid(pTab) ) return; 002323 if( pParse->nested ){ 002324 pik_flags = 0; 002325 }else{ 002326 pik_flags = OPFLAG_NCHANGE; 002327 pik_flags |= (update_flags?update_flags:OPFLAG_LASTROWID); 002328 } 002329 if( appendBias ){ 002330 pik_flags |= OPFLAG_APPEND; 002331 } 002332 if( useSeekResult ){ 002333 pik_flags |= OPFLAG_USESEEKRESULT; 002334 } 002335 sqlite3VdbeAddOp3(v, OP_Insert, iDataCur, aRegIdx[i], regNewData); 002336 if( !pParse->nested ){ 002337 sqlite3VdbeAppendP4(v, pTab, P4_TABLE); 002338 } 002339 sqlite3VdbeChangeP5(v, pik_flags); 002340 } 002341 002342 /* 002343 ** Allocate cursors for the pTab table and all its indices and generate 002344 ** code to open and initialized those cursors. 002345 ** 002346 ** The cursor for the object that contains the complete data (normally 002347 ** the table itself, but the PRIMARY KEY index in the case of a WITHOUT 002348 ** ROWID table) is returned in *piDataCur. The first index cursor is 002349 ** returned in *piIdxCur. The number of indices is returned. 002350 ** 002351 ** Use iBase as the first cursor (either the *piDataCur for rowid tables 002352 ** or the first index for WITHOUT ROWID tables) if it is non-negative. 002353 ** If iBase is negative, then allocate the next available cursor. 002354 ** 002355 ** For a rowid table, *piDataCur will be exactly one less than *piIdxCur. 002356 ** For a WITHOUT ROWID table, *piDataCur will be somewhere in the range 002357 ** of *piIdxCurs, depending on where the PRIMARY KEY index appears on the 002358 ** pTab->pIndex list. 002359 ** 002360 ** If pTab is a virtual table, then this routine is a no-op and the 002361 ** *piDataCur and *piIdxCur values are left uninitialized. 002362 */ 002363 int sqlite3OpenTableAndIndices( 002364 Parse *pParse, /* Parsing context */ 002365 Table *pTab, /* Table to be opened */ 002366 int op, /* OP_OpenRead or OP_OpenWrite */ 002367 u8 p5, /* P5 value for OP_Open* opcodes (except on WITHOUT ROWID) */ 002368 int iBase, /* Use this for the table cursor, if there is one */ 002369 u8 *aToOpen, /* If not NULL: boolean for each table and index */ 002370 int *piDataCur, /* Write the database source cursor number here */ 002371 int *piIdxCur /* Write the first index cursor number here */ 002372 ){ 002373 int i; 002374 int iDb; 002375 int iDataCur; 002376 Index *pIdx; 002377 Vdbe *v; 002378 002379 assert( op==OP_OpenRead || op==OP_OpenWrite ); 002380 assert( op==OP_OpenWrite || p5==0 ); 002381 if( IsVirtual(pTab) ){ 002382 /* This routine is a no-op for virtual tables. Leave the output 002383 ** variables *piDataCur and *piIdxCur uninitialized so that valgrind 002384 ** can detect if they are used by mistake in the caller. */ 002385 return 0; 002386 } 002387 iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema); 002388 v = sqlite3GetVdbe(pParse); 002389 assert( v!=0 ); 002390 if( iBase<0 ) iBase = pParse->nTab; 002391 iDataCur = iBase++; 002392 if( piDataCur ) *piDataCur = iDataCur; 002393 if( HasRowid(pTab) && (aToOpen==0 || aToOpen[0]) ){ 002394 sqlite3OpenTable(pParse, iDataCur, iDb, pTab, op); 002395 }else{ 002396 sqlite3TableLock(pParse, iDb, pTab->tnum, op==OP_OpenWrite, pTab->zName); 002397 } 002398 if( piIdxCur ) *piIdxCur = iBase; 002399 for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){ 002400 int iIdxCur = iBase++; 002401 assert( pIdx->pSchema==pTab->pSchema ); 002402 if( IsPrimaryKeyIndex(pIdx) && !HasRowid(pTab) ){ 002403 if( piDataCur ) *piDataCur = iIdxCur; 002404 p5 = 0; 002405 } 002406 if( aToOpen==0 || aToOpen[i+1] ){ 002407 sqlite3VdbeAddOp3(v, op, iIdxCur, pIdx->tnum, iDb); 002408 sqlite3VdbeSetP4KeyInfo(pParse, pIdx); 002409 sqlite3VdbeChangeP5(v, p5); 002410 VdbeComment((v, "%s", pIdx->zName)); 002411 } 002412 } 002413 if( iBase>pParse->nTab ) pParse->nTab = iBase; 002414 return i; 002415 } 002416 002417 002418 #ifdef SQLITE_TEST 002419 /* 002420 ** The following global variable is incremented whenever the 002421 ** transfer optimization is used. This is used for testing 002422 ** purposes only - to make sure the transfer optimization really 002423 ** is happening when it is supposed to. 002424 */ 002425 int sqlite3_xferopt_count; 002426 #endif /* SQLITE_TEST */ 002427 002428 002429 #ifndef SQLITE_OMIT_XFER_OPT 002430 /* 002431 ** Check to see if index pSrc is compatible as a source of data 002432 ** for index pDest in an insert transfer optimization. The rules 002433 ** for a compatible index: 002434 ** 002435 ** * The index is over the same set of columns 002436 ** * The same DESC and ASC markings occurs on all columns 002437 ** * The same onError processing (OE_Abort, OE_Ignore, etc) 002438 ** * The same collating sequence on each column 002439 ** * The index has the exact same WHERE clause 002440 */ 002441 static int xferCompatibleIndex(Index *pDest, Index *pSrc){ 002442 int i; 002443 assert( pDest && pSrc ); 002444 assert( pDest->pTable!=pSrc->pTable ); 002445 if( pDest->nKeyCol!=pSrc->nKeyCol || pDest->nColumn!=pSrc->nColumn ){ 002446 return 0; /* Different number of columns */ 002447 } 002448 if( pDest->onError!=pSrc->onError ){ 002449 return 0; /* Different conflict resolution strategies */ 002450 } 002451 for(i=0; i<pSrc->nKeyCol; i++){ 002452 if( pSrc->aiColumn[i]!=pDest->aiColumn[i] ){ 002453 return 0; /* Different columns indexed */ 002454 } 002455 if( pSrc->aiColumn[i]==XN_EXPR ){ 002456 assert( pSrc->aColExpr!=0 && pDest->aColExpr!=0 ); 002457 if( sqlite3ExprCompare(0, pSrc->aColExpr->a[i].pExpr, 002458 pDest->aColExpr->a[i].pExpr, -1)!=0 ){ 002459 return 0; /* Different expressions in the index */ 002460 } 002461 } 002462 if( pSrc->aSortOrder[i]!=pDest->aSortOrder[i] ){ 002463 return 0; /* Different sort orders */ 002464 } 002465 if( sqlite3_stricmp(pSrc->azColl[i],pDest->azColl[i])!=0 ){ 002466 return 0; /* Different collating sequences */ 002467 } 002468 } 002469 if( sqlite3ExprCompare(0, pSrc->pPartIdxWhere, pDest->pPartIdxWhere, -1) ){ 002470 return 0; /* Different WHERE clauses */ 002471 } 002472 002473 /* If no test above fails then the indices must be compatible */ 002474 return 1; 002475 } 002476 002477 /* 002478 ** Attempt the transfer optimization on INSERTs of the form 002479 ** 002480 ** INSERT INTO tab1 SELECT * FROM tab2; 002481 ** 002482 ** The xfer optimization transfers raw records from tab2 over to tab1. 002483 ** Columns are not decoded and reassembled, which greatly improves 002484 ** performance. Raw index records are transferred in the same way. 002485 ** 002486 ** The xfer optimization is only attempted if tab1 and tab2 are compatible. 002487 ** There are lots of rules for determining compatibility - see comments 002488 ** embedded in the code for details. 002489 ** 002490 ** This routine returns TRUE if the optimization is guaranteed to be used. 002491 ** Sometimes the xfer optimization will only work if the destination table 002492 ** is empty - a factor that can only be determined at run-time. In that 002493 ** case, this routine generates code for the xfer optimization but also 002494 ** does a test to see if the destination table is empty and jumps over the 002495 ** xfer optimization code if the test fails. In that case, this routine 002496 ** returns FALSE so that the caller will know to go ahead and generate 002497 ** an unoptimized transfer. This routine also returns FALSE if there 002498 ** is no chance that the xfer optimization can be applied. 002499 ** 002500 ** This optimization is particularly useful at making VACUUM run faster. 002501 */ 002502 static int xferOptimization( 002503 Parse *pParse, /* Parser context */ 002504 Table *pDest, /* The table we are inserting into */ 002505 Select *pSelect, /* A SELECT statement to use as the data source */ 002506 int onError, /* How to handle constraint errors */ 002507 int iDbDest /* The database of pDest */ 002508 ){ 002509 sqlite3 *db = pParse->db; 002510 ExprList *pEList; /* The result set of the SELECT */ 002511 Table *pSrc; /* The table in the FROM clause of SELECT */ 002512 Index *pSrcIdx, *pDestIdx; /* Source and destination indices */ 002513 struct SrcList_item *pItem; /* An element of pSelect->pSrc */ 002514 int i; /* Loop counter */ 002515 int iDbSrc; /* The database of pSrc */ 002516 int iSrc, iDest; /* Cursors from source and destination */ 002517 int addr1, addr2; /* Loop addresses */ 002518 int emptyDestTest = 0; /* Address of test for empty pDest */ 002519 int emptySrcTest = 0; /* Address of test for empty pSrc */ 002520 Vdbe *v; /* The VDBE we are building */ 002521 int regAutoinc; /* Memory register used by AUTOINC */ 002522 int destHasUniqueIdx = 0; /* True if pDest has a UNIQUE index */ 002523 int regData, regRowid; /* Registers holding data and rowid */ 002524 002525 if( pSelect==0 ){ 002526 return 0; /* Must be of the form INSERT INTO ... SELECT ... */ 002527 } 002528 if( pParse->pWith || pSelect->pWith ){ 002529 /* Do not attempt to process this query if there are an WITH clauses 002530 ** attached to it. Proceeding may generate a false "no such table: xxx" 002531 ** error if pSelect reads from a CTE named "xxx". */ 002532 return 0; 002533 } 002534 if( sqlite3TriggerList(pParse, pDest) ){ 002535 return 0; /* tab1 must not have triggers */ 002536 } 002537 #ifndef SQLITE_OMIT_VIRTUALTABLE 002538 if( IsVirtual(pDest) ){ 002539 return 0; /* tab1 must not be a virtual table */ 002540 } 002541 #endif 002542 if( onError==OE_Default ){ 002543 if( pDest->iPKey>=0 ) onError = pDest->keyConf; 002544 if( onError==OE_Default ) onError = OE_Abort; 002545 } 002546 assert(pSelect->pSrc); /* allocated even if there is no FROM clause */ 002547 if( pSelect->pSrc->nSrc!=1 ){ 002548 return 0; /* FROM clause must have exactly one term */ 002549 } 002550 if( pSelect->pSrc->a[0].pSelect ){ 002551 return 0; /* FROM clause cannot contain a subquery */ 002552 } 002553 if( pSelect->pWhere ){ 002554 return 0; /* SELECT may not have a WHERE clause */ 002555 } 002556 if( pSelect->pOrderBy ){ 002557 return 0; /* SELECT may not have an ORDER BY clause */ 002558 } 002559 /* Do not need to test for a HAVING clause. If HAVING is present but 002560 ** there is no ORDER BY, we will get an error. */ 002561 if( pSelect->pGroupBy ){ 002562 return 0; /* SELECT may not have a GROUP BY clause */ 002563 } 002564 if( pSelect->pLimit ){ 002565 return 0; /* SELECT may not have a LIMIT clause */ 002566 } 002567 if( pSelect->pPrior ){ 002568 return 0; /* SELECT may not be a compound query */ 002569 } 002570 if( pSelect->selFlags & SF_Distinct ){ 002571 return 0; /* SELECT may not be DISTINCT */ 002572 } 002573 pEList = pSelect->pEList; 002574 assert( pEList!=0 ); 002575 if( pEList->nExpr!=1 ){ 002576 return 0; /* The result set must have exactly one column */ 002577 } 002578 assert( pEList->a[0].pExpr ); 002579 if( pEList->a[0].pExpr->op!=TK_ASTERISK ){ 002580 return 0; /* The result set must be the special operator "*" */ 002581 } 002582 002583 /* At this point we have established that the statement is of the 002584 ** correct syntactic form to participate in this optimization. Now 002585 ** we have to check the semantics. 002586 */ 002587 pItem = pSelect->pSrc->a; 002588 pSrc = sqlite3LocateTableItem(pParse, 0, pItem); 002589 if( pSrc==0 ){ 002590 return 0; /* FROM clause does not contain a real table */ 002591 } 002592 if( pSrc->tnum==pDest->tnum && pSrc->pSchema==pDest->pSchema ){ 002593 testcase( pSrc!=pDest ); /* Possible due to bad sqlite_master.rootpage */ 002594 return 0; /* tab1 and tab2 may not be the same table */ 002595 } 002596 if( HasRowid(pDest)!=HasRowid(pSrc) ){ 002597 return 0; /* source and destination must both be WITHOUT ROWID or not */ 002598 } 002599 #ifndef SQLITE_OMIT_VIRTUALTABLE 002600 if( IsVirtual(pSrc) ){ 002601 return 0; /* tab2 must not be a virtual table */ 002602 } 002603 #endif 002604 if( pSrc->pSelect ){ 002605 return 0; /* tab2 may not be a view */ 002606 } 002607 if( pDest->nCol!=pSrc->nCol ){ 002608 return 0; /* Number of columns must be the same in tab1 and tab2 */ 002609 } 002610 if( pDest->iPKey!=pSrc->iPKey ){ 002611 return 0; /* Both tables must have the same INTEGER PRIMARY KEY */ 002612 } 002613 for(i=0; i<pDest->nCol; i++){ 002614 Column *pDestCol = &pDest->aCol[i]; 002615 Column *pSrcCol = &pSrc->aCol[i]; 002616 #ifdef SQLITE_ENABLE_HIDDEN_COLUMNS 002617 if( (db->mDbFlags & DBFLAG_Vacuum)==0 002618 && (pDestCol->colFlags | pSrcCol->colFlags) & COLFLAG_HIDDEN 002619 ){ 002620 return 0; /* Neither table may have __hidden__ columns */ 002621 } 002622 #endif 002623 #ifndef SQLITE_OMIT_GENERATED_COLUMNS 002624 /* Even if tables t1 and t2 have identical schemas, if they contain 002625 ** generated columns, then this statement is semantically incorrect: 002626 ** 002627 ** INSERT INTO t2 SELECT * FROM t1; 002628 ** 002629 ** The reason is that generated column values are returned by the 002630 ** the SELECT statement on the right but the INSERT statement on the 002631 ** left wants them to be omitted. 002632 ** 002633 ** Nevertheless, this is a useful notational shorthand to tell SQLite 002634 ** to do a bulk transfer all of the content from t1 over to t2. 002635 ** 002636 ** We could, in theory, disable this (except for internal use by the 002637 ** VACUUM command where it is actually needed). But why do that? It 002638 ** seems harmless enough, and provides a useful service. 002639 */ 002640 if( (pDestCol->colFlags & COLFLAG_GENERATED) != 002641 (pSrcCol->colFlags & COLFLAG_GENERATED) ){ 002642 return 0; /* Both columns have the same generated-column type */ 002643 } 002644 /* But the transfer is only allowed if both the source and destination 002645 ** tables have the exact same expressions for generated columns. 002646 ** This requirement could be relaxed for VIRTUAL columns, I suppose. 002647 */ 002648 if( (pDestCol->colFlags & COLFLAG_GENERATED)!=0 ){ 002649 if( sqlite3ExprCompare(0, pSrcCol->pDflt, pDestCol->pDflt, -1)!=0 ){ 002650 testcase( pDestCol->colFlags & COLFLAG_VIRTUAL ); 002651 testcase( pDestCol->colFlags & COLFLAG_STORED ); 002652 return 0; /* Different generator expressions */ 002653 } 002654 } 002655 #endif 002656 if( pDestCol->affinity!=pSrcCol->affinity ){ 002657 return 0; /* Affinity must be the same on all columns */ 002658 } 002659 if( sqlite3_stricmp(pDestCol->zColl, pSrcCol->zColl)!=0 ){ 002660 return 0; /* Collating sequence must be the same on all columns */ 002661 } 002662 if( pDestCol->notNull && !pSrcCol->notNull ){ 002663 return 0; /* tab2 must be NOT NULL if tab1 is */ 002664 } 002665 /* Default values for second and subsequent columns need to match. */ 002666 if( (pDestCol->colFlags & COLFLAG_GENERATED)==0 && i>0 ){ 002667 assert( pDestCol->pDflt==0 || pDestCol->pDflt->op==TK_SPAN ); 002668 assert( pSrcCol->pDflt==0 || pSrcCol->pDflt->op==TK_SPAN ); 002669 if( (pDestCol->pDflt==0)!=(pSrcCol->pDflt==0) 002670 || (pDestCol->pDflt && strcmp(pDestCol->pDflt->u.zToken, 002671 pSrcCol->pDflt->u.zToken)!=0) 002672 ){ 002673 return 0; /* Default values must be the same for all columns */ 002674 } 002675 } 002676 } 002677 for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){ 002678 if( IsUniqueIndex(pDestIdx) ){ 002679 destHasUniqueIdx = 1; 002680 } 002681 for(pSrcIdx=pSrc->pIndex; pSrcIdx; pSrcIdx=pSrcIdx->pNext){ 002682 if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break; 002683 } 002684 if( pSrcIdx==0 ){ 002685 return 0; /* pDestIdx has no corresponding index in pSrc */ 002686 } 002687 if( pSrcIdx->tnum==pDestIdx->tnum && pSrc->pSchema==pDest->pSchema 002688 && sqlite3FaultSim(411)==SQLITE_OK ){ 002689 /* The sqlite3FaultSim() call allows this corruption test to be 002690 ** bypassed during testing, in order to exercise other corruption tests 002691 ** further downstream. */ 002692 return 0; /* Corrupt schema - two indexes on the same btree */ 002693 } 002694 } 002695 #ifndef SQLITE_OMIT_CHECK 002696 if( pDest->pCheck && sqlite3ExprListCompare(pSrc->pCheck,pDest->pCheck,-1) ){ 002697 return 0; /* Tables have different CHECK constraints. Ticket #2252 */ 002698 } 002699 #endif 002700 #ifndef SQLITE_OMIT_FOREIGN_KEY 002701 /* Disallow the transfer optimization if the destination table constains 002702 ** any foreign key constraints. This is more restrictive than necessary. 002703 ** But the main beneficiary of the transfer optimization is the VACUUM 002704 ** command, and the VACUUM command disables foreign key constraints. So 002705 ** the extra complication to make this rule less restrictive is probably 002706 ** not worth the effort. Ticket [6284df89debdfa61db8073e062908af0c9b6118e] 002707 */ 002708 if( (db->flags & SQLITE_ForeignKeys)!=0 && pDest->pFKey!=0 ){ 002709 return 0; 002710 } 002711 #endif 002712 if( (db->flags & SQLITE_CountRows)!=0 ){ 002713 return 0; /* xfer opt does not play well with PRAGMA count_changes */ 002714 } 002715 002716 /* If we get this far, it means that the xfer optimization is at 002717 ** least a possibility, though it might only work if the destination 002718 ** table (tab1) is initially empty. 002719 */ 002720 #ifdef SQLITE_TEST 002721 sqlite3_xferopt_count++; 002722 #endif 002723 iDbSrc = sqlite3SchemaToIndex(db, pSrc->pSchema); 002724 v = sqlite3GetVdbe(pParse); 002725 sqlite3CodeVerifySchema(pParse, iDbSrc); 002726 iSrc = pParse->nTab++; 002727 iDest = pParse->nTab++; 002728 regAutoinc = autoIncBegin(pParse, iDbDest, pDest); 002729 regData = sqlite3GetTempReg(pParse); 002730 regRowid = sqlite3GetTempReg(pParse); 002731 sqlite3OpenTable(pParse, iDest, iDbDest, pDest, OP_OpenWrite); 002732 assert( HasRowid(pDest) || destHasUniqueIdx ); 002733 if( (db->mDbFlags & DBFLAG_Vacuum)==0 && ( 002734 (pDest->iPKey<0 && pDest->pIndex!=0) /* (1) */ 002735 || destHasUniqueIdx /* (2) */ 002736 || (onError!=OE_Abort && onError!=OE_Rollback) /* (3) */ 002737 )){ 002738 /* In some circumstances, we are able to run the xfer optimization 002739 ** only if the destination table is initially empty. Unless the 002740 ** DBFLAG_Vacuum flag is set, this block generates code to make 002741 ** that determination. If DBFLAG_Vacuum is set, then the destination 002742 ** table is always empty. 002743 ** 002744 ** Conditions under which the destination must be empty: 002745 ** 002746 ** (1) There is no INTEGER PRIMARY KEY but there are indices. 002747 ** (If the destination is not initially empty, the rowid fields 002748 ** of index entries might need to change.) 002749 ** 002750 ** (2) The destination has a unique index. (The xfer optimization 002751 ** is unable to test uniqueness.) 002752 ** 002753 ** (3) onError is something other than OE_Abort and OE_Rollback. 002754 */ 002755 addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iDest, 0); VdbeCoverage(v); 002756 emptyDestTest = sqlite3VdbeAddOp0(v, OP_Goto); 002757 sqlite3VdbeJumpHere(v, addr1); 002758 } 002759 if( HasRowid(pSrc) ){ 002760 u8 insFlags; 002761 sqlite3OpenTable(pParse, iSrc, iDbSrc, pSrc, OP_OpenRead); 002762 emptySrcTest = sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0); VdbeCoverage(v); 002763 if( pDest->iPKey>=0 ){ 002764 addr1 = sqlite3VdbeAddOp2(v, OP_Rowid, iSrc, regRowid); 002765 sqlite3VdbeVerifyAbortable(v, onError); 002766 addr2 = sqlite3VdbeAddOp3(v, OP_NotExists, iDest, 0, regRowid); 002767 VdbeCoverage(v); 002768 sqlite3RowidConstraint(pParse, onError, pDest); 002769 sqlite3VdbeJumpHere(v, addr2); 002770 autoIncStep(pParse, regAutoinc, regRowid); 002771 }else if( pDest->pIndex==0 && !(db->mDbFlags & DBFLAG_VacuumInto) ){ 002772 addr1 = sqlite3VdbeAddOp2(v, OP_NewRowid, iDest, regRowid); 002773 }else{ 002774 addr1 = sqlite3VdbeAddOp2(v, OP_Rowid, iSrc, regRowid); 002775 assert( (pDest->tabFlags & TF_Autoincrement)==0 ); 002776 } 002777 sqlite3VdbeAddOp3(v, OP_RowData, iSrc, regData, 1); 002778 if( db->mDbFlags & DBFLAG_Vacuum ){ 002779 sqlite3VdbeAddOp1(v, OP_SeekEnd, iDest); 002780 insFlags = OPFLAG_NCHANGE|OPFLAG_LASTROWID| 002781 OPFLAG_APPEND|OPFLAG_USESEEKRESULT; 002782 }else{ 002783 insFlags = OPFLAG_NCHANGE|OPFLAG_LASTROWID|OPFLAG_APPEND; 002784 } 002785 sqlite3VdbeAddOp4(v, OP_Insert, iDest, regData, regRowid, 002786 (char*)pDest, P4_TABLE); 002787 sqlite3VdbeChangeP5(v, insFlags); 002788 sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1); VdbeCoverage(v); 002789 sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0); 002790 sqlite3VdbeAddOp2(v, OP_Close, iDest, 0); 002791 }else{ 002792 sqlite3TableLock(pParse, iDbDest, pDest->tnum, 1, pDest->zName); 002793 sqlite3TableLock(pParse, iDbSrc, pSrc->tnum, 0, pSrc->zName); 002794 } 002795 for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){ 002796 u8 idxInsFlags = 0; 002797 for(pSrcIdx=pSrc->pIndex; ALWAYS(pSrcIdx); pSrcIdx=pSrcIdx->pNext){ 002798 if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break; 002799 } 002800 assert( pSrcIdx ); 002801 sqlite3VdbeAddOp3(v, OP_OpenRead, iSrc, pSrcIdx->tnum, iDbSrc); 002802 sqlite3VdbeSetP4KeyInfo(pParse, pSrcIdx); 002803 VdbeComment((v, "%s", pSrcIdx->zName)); 002804 sqlite3VdbeAddOp3(v, OP_OpenWrite, iDest, pDestIdx->tnum, iDbDest); 002805 sqlite3VdbeSetP4KeyInfo(pParse, pDestIdx); 002806 sqlite3VdbeChangeP5(v, OPFLAG_BULKCSR); 002807 VdbeComment((v, "%s", pDestIdx->zName)); 002808 addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0); VdbeCoverage(v); 002809 sqlite3VdbeAddOp3(v, OP_RowData, iSrc, regData, 1); 002810 if( db->mDbFlags & DBFLAG_Vacuum ){ 002811 /* This INSERT command is part of a VACUUM operation, which guarantees 002812 ** that the destination table is empty. If all indexed columns use 002813 ** collation sequence BINARY, then it can also be assumed that the 002814 ** index will be populated by inserting keys in strictly sorted 002815 ** order. In this case, instead of seeking within the b-tree as part 002816 ** of every OP_IdxInsert opcode, an OP_SeekEnd is added before the 002817 ** OP_IdxInsert to seek to the point within the b-tree where each key 002818 ** should be inserted. This is faster. 002819 ** 002820 ** If any of the indexed columns use a collation sequence other than 002821 ** BINARY, this optimization is disabled. This is because the user 002822 ** might change the definition of a collation sequence and then run 002823 ** a VACUUM command. In that case keys may not be written in strictly 002824 ** sorted order. */ 002825 for(i=0; i<pSrcIdx->nColumn; i++){ 002826 const char *zColl = pSrcIdx->azColl[i]; 002827 if( sqlite3_stricmp(sqlite3StrBINARY, zColl) ) break; 002828 } 002829 if( i==pSrcIdx->nColumn ){ 002830 idxInsFlags = OPFLAG_USESEEKRESULT; 002831 sqlite3VdbeAddOp1(v, OP_SeekEnd, iDest); 002832 } 002833 } 002834 if( !HasRowid(pSrc) && pDestIdx->idxType==SQLITE_IDXTYPE_PRIMARYKEY ){ 002835 idxInsFlags |= OPFLAG_NCHANGE; 002836 } 002837 sqlite3VdbeAddOp2(v, OP_IdxInsert, iDest, regData); 002838 sqlite3VdbeChangeP5(v, idxInsFlags|OPFLAG_APPEND); 002839 sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1+1); VdbeCoverage(v); 002840 sqlite3VdbeJumpHere(v, addr1); 002841 sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0); 002842 sqlite3VdbeAddOp2(v, OP_Close, iDest, 0); 002843 } 002844 if( emptySrcTest ) sqlite3VdbeJumpHere(v, emptySrcTest); 002845 sqlite3ReleaseTempReg(pParse, regRowid); 002846 sqlite3ReleaseTempReg(pParse, regData); 002847 if( emptyDestTest ){ 002848 sqlite3AutoincrementEnd(pParse); 002849 sqlite3VdbeAddOp2(v, OP_Halt, SQLITE_OK, 0); 002850 sqlite3VdbeJumpHere(v, emptyDestTest); 002851 sqlite3VdbeAddOp2(v, OP_Close, iDest, 0); 002852 return 0; 002853 }else{ 002854 return 1; 002855 } 002856 } 002857 #endif /* SQLITE_OMIT_XFER_OPT */