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 */