AryaWu/sqlite
0
1/*2** 2001 September 153**4** The author disclaims copyright to this source code. In place of5** a legal notice, here is a blessing:6**7** May you do good and not evil.8** May you find forgiveness for yourself and forgive others.9** May you share freely, never taking more than you give.10**11*************************************************************************12** This file contains C code routines that are called by the parser13** to handle INSERT statements in SQLite.14*/15#include "sqliteInt.h"16 17/*18** Generate code that will19**20** (1) acquire a lock for table pTab then21** (2) open pTab as cursor iCur.22**23** If pTab is a WITHOUT ROWID table, then it is the PRIMARY KEY index24** for that table that is actually opened.25*/26void sqlite3OpenTable(27 Parse *pParse, /* Generate code into this VDBE */28 int iCur, /* The cursor number of the table */29 int iDb, /* The database index in sqlite3.aDb[] */30 Table *pTab, /* The table to be opened */31 int opcode /* OP_OpenRead or OP_OpenWrite */32){33 Vdbe *v;34 assert( !IsVirtual(pTab) );35 assert( pParse->pVdbe!=0 );36 v = pParse->pVdbe;37 assert( opcode==OP_OpenWrite || opcode==OP_OpenRead );38 if( !pParse->db->noSharedCache ){39 sqlite3TableLock(pParse, iDb, pTab->tnum,40 (opcode==OP_OpenWrite)?1:0, pTab->zName);41 }42 if( HasRowid(pTab) ){43 sqlite3VdbeAddOp4Int(v, opcode, iCur, pTab->tnum, iDb, pTab->nNVCol);44 VdbeComment((v, "%s", pTab->zName));45 }else{46 Index *pPk = sqlite3PrimaryKeyIndex(pTab);47 assert( pPk!=0 );48 assert( pPk->tnum==pTab->tnum || CORRUPT_DB );49 sqlite3VdbeAddOp3(v, opcode, iCur, pPk->tnum, iDb);50 sqlite3VdbeSetP4KeyInfo(pParse, pPk);51 VdbeComment((v, "%s", pTab->zName));52 }53}54 55/*56** Return a pointer to the column affinity string associated with index57** pIdx. A column affinity string has one character for each column in58** the table, according to the affinity of the column:59**60** Character Column affinity61** ------------------------------62** 'A' BLOB63** 'B' TEXT64** 'C' NUMERIC65** 'D' INTEGER66** 'F' REAL67**68** An extra 'D' is appended to the end of the string to cover the69** rowid that appears as the last column in every index.70**71** Memory for the buffer containing the column index affinity string72** is managed along with the rest of the Index structure. It will be73** released when sqlite3DeleteIndex() is called.74*/75static SQLITE_NOINLINE const char *computeIndexAffStr(sqlite3 *db, Index *pIdx){76 /* The first time a column affinity string for a particular index is77 ** required, it is allocated and populated here. It is then stored as78 ** a member of the Index structure for subsequent use.79 **80 ** The column affinity string will eventually be deleted by81 ** sqliteDeleteIndex() when the Index structure itself is cleaned82 ** up.83 */84 int n;85 Table *pTab = pIdx->pTable;86 pIdx->zColAff = (char *)sqlite3DbMallocRaw(0, pIdx->nColumn+1);87 if( !pIdx->zColAff ){88 sqlite3OomFault(db);89 return 0;90 }91 for(n=0; n<pIdx->nColumn; n++){92 i16 x = pIdx->aiColumn[n];93 char aff;94 if( x>=0 ){95 aff = pTab->aCol[x].affinity;96 }else if( x==XN_ROWID ){97 aff = SQLITE_AFF_INTEGER;98 }else{99 assert( x==XN_EXPR );100 assert( pIdx->bHasExpr );101 assert( pIdx->aColExpr!=0 );102 aff = sqlite3ExprAffinity(pIdx->aColExpr->a[n].pExpr);103 }104 if( aff<SQLITE_AFF_BLOB ) aff = SQLITE_AFF_BLOB;105 if( aff>SQLITE_AFF_NUMERIC) aff = SQLITE_AFF_NUMERIC;106 pIdx->zColAff[n] = aff;107 }108 pIdx->zColAff[n] = 0;109 return pIdx->zColAff;110}111const char *sqlite3IndexAffinityStr(sqlite3 *db, Index *pIdx){112 if( !pIdx->zColAff ) return computeIndexAffStr(db, pIdx);113 return pIdx->zColAff;114}115 116 117/*118** Compute an affinity string for a table. Space is obtained119** from sqlite3DbMalloc(). The caller is responsible for freeing120** the space when done.121*/122char *sqlite3TableAffinityStr(sqlite3 *db, const Table *pTab){123 char *zColAff;124 zColAff = (char *)sqlite3DbMallocRaw(db, pTab->nCol+1);125 if( zColAff ){126 int i, j;127 for(i=j=0; i<pTab->nCol; i++){128 if( (pTab->aCol[i].colFlags & COLFLAG_VIRTUAL)==0 ){129 zColAff[j++] = pTab->aCol[i].affinity;130 }131 }132 do{133 zColAff[j--] = 0;134 }while( j>=0 && zColAff[j]<=SQLITE_AFF_BLOB );135 }136 return zColAff; 137}138 139/*140** Make changes to the evolving bytecode to do affinity transformations141** of values that are about to be gathered into a row for table pTab.142**143** For ordinary (legacy, non-strict) tables:144** -----------------------------------------145**146** Compute the affinity string for table pTab, if it has not already been147** computed. As an optimization, omit trailing SQLITE_AFF_BLOB affinities.148**149** If the affinity string is empty (because it was all SQLITE_AFF_BLOB entries150** which were then optimized out) then this routine becomes a no-op.151**152** Otherwise if iReg>0 then code an OP_Affinity opcode that will set the153** affinities for register iReg and following. Or if iReg==0,154** then just set the P4 operand of the previous opcode (which should be155** an OP_MakeRecord) to the affinity string.156**157** A column affinity string has one character per column:158**159** Character Column affinity160** --------- ---------------161** 'A' BLOB162** 'B' TEXT163** 'C' NUMERIC164** 'D' INTEGER165** 'E' REAL166**167** For STRICT tables:168** ------------------169**170** Generate an appropriate OP_TypeCheck opcode that will verify the171** datatypes against the column definitions in pTab. If iReg==0, that172** means an OP_MakeRecord opcode has already been generated and should be173** the last opcode generated. The new OP_TypeCheck needs to be inserted174** before the OP_MakeRecord. The new OP_TypeCheck should use the same175** register set as the OP_MakeRecord. If iReg>0 then register iReg is176** the first of a series of registers that will form the new record.177** Apply the type checking to that array of registers.178*/179void sqlite3TableAffinity(Vdbe *v, Table *pTab, int iReg){180 int i;181 char *zColAff;182 if( pTab->tabFlags & TF_Strict ){183 if( iReg==0 ){184 /* Move the previous opcode (which should be OP_MakeRecord) forward185 ** by one slot and insert a new OP_TypeCheck where the current186 ** OP_MakeRecord is found */187 VdbeOp *pPrev;188 int p3;189 sqlite3VdbeAppendP4(v, pTab, P4_TABLE);190 pPrev = sqlite3VdbeGetLastOp(v);191 assert( pPrev!=0 );192 assert( pPrev->opcode==OP_MakeRecord || sqlite3VdbeDb(v)->mallocFailed );193 pPrev->opcode = OP_TypeCheck;194 p3 = pPrev->p3;195 pPrev->p3 = 0;196 sqlite3VdbeAddOp3(v, OP_MakeRecord, pPrev->p1, pPrev->p2, p3);197 }else{198 /* Insert an isolated OP_Typecheck */199 sqlite3VdbeAddOp2(v, OP_TypeCheck, iReg, pTab->nNVCol);200 sqlite3VdbeAppendP4(v, pTab, P4_TABLE);201 }202 return;203 }204 zColAff = pTab->zColAff;205 if( zColAff==0 ){206 zColAff = sqlite3TableAffinityStr(0, pTab);207 if( !zColAff ){208 sqlite3OomFault(sqlite3VdbeDb(v));209 return;210 }211 pTab->zColAff = zColAff;212 }213 assert( zColAff!=0 );214 i = sqlite3Strlen30NN(zColAff);215 if( i ){216 if( iReg ){217 sqlite3VdbeAddOp4(v, OP_Affinity, iReg, i, 0, zColAff, i);218 }else{219 assert( sqlite3VdbeGetLastOp(v)->opcode==OP_MakeRecord220 || sqlite3VdbeDb(v)->mallocFailed );221 sqlite3VdbeChangeP4(v, -1, zColAff, i);222 }223 }224}225 226/*227** Return non-zero if the table pTab in database iDb or any of its indices228** have been opened at any point in the VDBE program. This is used to see if229** a statement of the form "INSERT INTO <iDb, pTab> SELECT ..." can230** run without using a temporary table for the results of the SELECT.231*/232static int readsTable(Parse *p, int iDb, Table *pTab){233 Vdbe *v = sqlite3GetVdbe(p);234 int i;235 int iEnd = sqlite3VdbeCurrentAddr(v);236#ifndef SQLITE_OMIT_VIRTUALTABLE237 VTable *pVTab = IsVirtual(pTab) ? sqlite3GetVTable(p->db, pTab) : 0;238#endif239 240 for(i=1; i<iEnd; i++){241 VdbeOp *pOp = sqlite3VdbeGetOp(v, i);242 assert( pOp!=0 );243 if( pOp->opcode==OP_OpenRead && pOp->p3==iDb ){244 Index *pIndex;245 Pgno tnum = pOp->p2;246 if( tnum==pTab->tnum ){247 return 1;248 }249 for(pIndex=pTab->pIndex; pIndex; pIndex=pIndex->pNext){250 if( tnum==pIndex->tnum ){251 return 1;252 }253 }254 }255#ifndef SQLITE_OMIT_VIRTUALTABLE256 if( pOp->opcode==OP_VOpen && pOp->p4.pVtab==pVTab ){257 assert( pOp->p4.pVtab!=0 );258 assert( pOp->p4type==P4_VTAB );259 return 1;260 }261#endif262 }263 return 0;264}265 266/* This walker callback will compute the union of colFlags flags for all267** referenced columns in a CHECK constraint or generated column expression.268*/269static int exprColumnFlagUnion(Walker *pWalker, Expr *pExpr){270 if( pExpr->op==TK_COLUMN && pExpr->iColumn>=0 ){271 assert( pExpr->iColumn < pWalker->u.pTab->nCol );272 pWalker->eCode |= pWalker->u.pTab->aCol[pExpr->iColumn].colFlags;273 }274 return WRC_Continue;275}276 277#ifndef SQLITE_OMIT_GENERATED_COLUMNS278/*279** All regular columns for table pTab have been puts into registers280** starting with iRegStore. The registers that correspond to STORED281** or VIRTUAL columns have not yet been initialized. This routine goes282** back and computes the values for those columns based on the previously283** computed normal columns.284*/285void sqlite3ComputeGeneratedColumns(286 Parse *pParse, /* Parsing context */287 int iRegStore, /* Register holding the first column */288 Table *pTab /* The table */289){290 int i;291 Walker w;292 Column *pRedo;293 int eProgress;294 VdbeOp *pOp;295 296 assert( pTab->tabFlags & TF_HasGenerated );297 testcase( pTab->tabFlags & TF_HasVirtual );298 testcase( pTab->tabFlags & TF_HasStored );299 300 /* Before computing generated columns, first go through and make sure301 ** that appropriate affinity has been applied to the regular columns302 */303 sqlite3TableAffinity(pParse->pVdbe, pTab, iRegStore);304 if( (pTab->tabFlags & TF_HasStored)!=0 ){305 pOp = sqlite3VdbeGetLastOp(pParse->pVdbe);306 if( pOp->opcode==OP_Affinity ){307 /* Change the OP_Affinity argument to '@' (NONE) for all stored308 ** columns. '@' is the no-op affinity and those columns have not309 ** yet been computed. */310 int ii, jj;311 char *zP4 = pOp->p4.z;312 assert( zP4!=0 );313 assert( pOp->p4type==P4_DYNAMIC );314 for(ii=jj=0; zP4[jj]; ii++){315 if( pTab->aCol[ii].colFlags & COLFLAG_VIRTUAL ){316 continue;317 }318 if( pTab->aCol[ii].colFlags & COLFLAG_STORED ){319 zP4[jj] = SQLITE_AFF_NONE;320 }321 jj++;322 }323 }else if( pOp->opcode==OP_TypeCheck ){324 /* If an OP_TypeCheck was generated because the table is STRICT,325 ** then set the P3 operand to indicate that generated columns should326 ** not be checked */327 pOp->p3 = 1;328 }329 }330 331 /* Because there can be multiple generated columns that refer to one another,332 ** this is a two-pass algorithm. On the first pass, mark all generated333 ** columns as "not available".334 */335 for(i=0; i<pTab->nCol; i++){336 if( pTab->aCol[i].colFlags & COLFLAG_GENERATED ){337 testcase( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL );338 testcase( pTab->aCol[i].colFlags & COLFLAG_STORED );339 pTab->aCol[i].colFlags |= COLFLAG_NOTAVAIL;340 }341 }342 343 w.u.pTab = pTab;344 w.xExprCallback = exprColumnFlagUnion;345 w.xSelectCallback = 0;346 w.xSelectCallback2 = 0;347 348 /* On the second pass, compute the value of each NOT-AVAILABLE column.349 ** Companion code in the TK_COLUMN case of sqlite3ExprCodeTarget() will350 ** compute dependencies and mark remove the COLSPAN_NOTAVAIL mark, as351 ** they are needed.352 */353 pParse->iSelfTab = -iRegStore;354 do{355 eProgress = 0;356 pRedo = 0;357 for(i=0; i<pTab->nCol; i++){358 Column *pCol = pTab->aCol + i;359 if( (pCol->colFlags & COLFLAG_NOTAVAIL)!=0 ){360 int x;361 pCol->colFlags |= COLFLAG_BUSY;362 w.eCode = 0;363 sqlite3WalkExpr(&w, sqlite3ColumnExpr(pTab, pCol));364 pCol->colFlags &= ~COLFLAG_BUSY;365 if( w.eCode & COLFLAG_NOTAVAIL ){366 pRedo = pCol;367 continue;368 }369 eProgress = 1;370 assert( pCol->colFlags & COLFLAG_GENERATED );371 x = sqlite3TableColumnToStorage(pTab, i) + iRegStore;372 sqlite3ExprCodeGeneratedColumn(pParse, pTab, pCol, x);373 pCol->colFlags &= ~COLFLAG_NOTAVAIL;374 }375 }376 }while( pRedo && eProgress );377 if( pRedo ){378 sqlite3ErrorMsg(pParse, "generated column loop on \"%s\"", pRedo->zCnName);379 }380 pParse->iSelfTab = 0;381}382#endif /* SQLITE_OMIT_GENERATED_COLUMNS */383 384 385#ifndef SQLITE_OMIT_AUTOINCREMENT386/*387** Locate or create an AutoincInfo structure associated with table pTab388** which is in database iDb. Return the register number for the register389** that holds the maximum rowid. Return zero if pTab is not an AUTOINCREMENT390** table. (Also return zero when doing a VACUUM since we do not want to391** update the AUTOINCREMENT counters during a VACUUM.)392**393** There is at most one AutoincInfo structure per table even if the394** same table is autoincremented multiple times due to inserts within395** triggers. A new AutoincInfo structure is created if this is the396** first use of table pTab. On 2nd and subsequent uses, the original397** AutoincInfo structure is used.398**399** Four consecutive registers are allocated:400**401** (1) The name of the pTab table.402** (2) The maximum ROWID of pTab.403** (3) The rowid in sqlite_sequence of pTab404** (4) The original value of the max ROWID in pTab, or NULL if none405**406** The 2nd register is the one that is returned. That is all the407** insert routine needs to know about.408*/409static int autoIncBegin(410 Parse *pParse, /* Parsing context */411 int iDb, /* Index of the database holding pTab */412 Table *pTab /* The table we are writing to */413){414 int memId = 0; /* Register holding maximum rowid */415 assert( pParse->db->aDb[iDb].pSchema!=0 );416 if( (pTab->tabFlags & TF_Autoincrement)!=0417 && (pParse->db->mDbFlags & DBFLAG_Vacuum)==0418 ){419 Parse *pToplevel = sqlite3ParseToplevel(pParse);420 AutoincInfo *pInfo;421 Table *pSeqTab = pParse->db->aDb[iDb].pSchema->pSeqTab;422 423 /* Verify that the sqlite_sequence table exists and is an ordinary424 ** rowid table with exactly two columns.425 ** Ticket d8dc2b3a58cd5dc2918a1d4acb 2018-05-23 */426 if( pSeqTab==0427 || !HasRowid(pSeqTab)428 || NEVER(IsVirtual(pSeqTab))429 || pSeqTab->nCol!=2430 ){431 pParse->nErr++;432 pParse->rc = SQLITE_CORRUPT_SEQUENCE;433 return 0;434 }435 436 pInfo = pToplevel->pAinc;437 while( pInfo && pInfo->pTab!=pTab ){ pInfo = pInfo->pNext; }438 if( pInfo==0 ){439 pInfo = sqlite3DbMallocRawNN(pParse->db, sizeof(*pInfo));440 sqlite3ParserAddCleanup(pToplevel, sqlite3DbFree, pInfo);441 testcase( pParse->earlyCleanup );442 if( pParse->db->mallocFailed ) return 0;443 pInfo->pNext = pToplevel->pAinc;444 pToplevel->pAinc = pInfo;445 pInfo->pTab = pTab;446 pInfo->iDb = iDb;447 pToplevel->nMem++; /* Register to hold name of table */448 pInfo->regCtr = ++pToplevel->nMem; /* Max rowid register */449 pToplevel->nMem +=2; /* Rowid in sqlite_sequence + orig max val */450 }451 memId = pInfo->regCtr;452 }453 return memId;454}455 456/*457** This routine generates code that will initialize all of the458** register used by the autoincrement tracker. 459*/460void sqlite3AutoincrementBegin(Parse *pParse){461 AutoincInfo *p; /* Information about an AUTOINCREMENT */462 sqlite3 *db = pParse->db; /* The database connection */463 Db *pDb; /* Database only autoinc table */464 int memId; /* Register holding max rowid */465 Vdbe *v = pParse->pVdbe; /* VDBE under construction */466 467 /* This routine is never called during trigger-generation. It is468 ** only called from the top-level */469 assert( pParse->pTriggerTab==0 );470 assert( sqlite3IsToplevel(pParse) );471 472 assert( v ); /* We failed long ago if this is not so */473 for(p = pParse->pAinc; p; p = p->pNext){474 static const int iLn = VDBE_OFFSET_LINENO(2);475 static const VdbeOpList autoInc[] = {476 /* 0 */ {OP_Null, 0, 0, 0},477 /* 1 */ {OP_Rewind, 0, 10, 0},478 /* 2 */ {OP_Column, 0, 0, 0},479 /* 3 */ {OP_Ne, 0, 9, 0},480 /* 4 */ {OP_Rowid, 0, 0, 0},481 /* 5 */ {OP_Column, 0, 1, 0},482 /* 6 */ {OP_AddImm, 0, 0, 0},483 /* 7 */ {OP_Copy, 0, 0, 0},484 /* 8 */ {OP_Goto, 0, 11, 0},485 /* 9 */ {OP_Next, 0, 2, 0},486 /* 10 */ {OP_Integer, 0, 0, 0},487 /* 11 */ {OP_Close, 0, 0, 0}488 };489 VdbeOp *aOp;490 pDb = &db->aDb[p->iDb];491 memId = p->regCtr;492 assert( sqlite3SchemaMutexHeld(db, 0, pDb->pSchema) );493 sqlite3OpenTable(pParse, 0, p->iDb, pDb->pSchema->pSeqTab, OP_OpenRead);494 sqlite3VdbeLoadString(v, memId-1, p->pTab->zName);495 aOp = sqlite3VdbeAddOpList(v, ArraySize(autoInc), autoInc, iLn);496 if( aOp==0 ) break;497 aOp[0].p2 = memId;498 aOp[0].p3 = memId+2;499 aOp[2].p3 = memId;500 aOp[3].p1 = memId-1;501 aOp[3].p3 = memId;502 aOp[3].p5 = SQLITE_JUMPIFNULL;503 aOp[4].p2 = memId+1;504 aOp[5].p3 = memId;505 aOp[6].p1 = memId;506 aOp[7].p2 = memId+2;507 aOp[7].p1 = memId;508 aOp[10].p2 = memId;509 if( pParse->nTab==0 ) pParse->nTab = 1;510 }511}512 513/*514** Update the maximum rowid for an autoincrement calculation.515**516** This routine should be called when the regRowid register holds a517** new rowid that is about to be inserted. If that new rowid is518** larger than the maximum rowid in the memId memory cell, then the519** memory cell is updated.520*/521static void autoIncStep(Parse *pParse, int memId, int regRowid){522 if( memId>0 ){523 sqlite3VdbeAddOp2(pParse->pVdbe, OP_MemMax, memId, regRowid);524 }525}526 527/*528** This routine generates the code needed to write autoincrement529** maximum rowid values back into the sqlite_sequence register.530** Every statement that might do an INSERT into an autoincrement531** table (either directly or through triggers) needs to call this532** routine just before the "exit" code.533*/534static SQLITE_NOINLINE void autoIncrementEnd(Parse *pParse){535 AutoincInfo *p;536 Vdbe *v = pParse->pVdbe;537 sqlite3 *db = pParse->db;538 539 assert( v );540 for(p = pParse->pAinc; p; p = p->pNext){541 static const int iLn = VDBE_OFFSET_LINENO(2);542 static const VdbeOpList autoIncEnd[] = {543 /* 0 */ {OP_NotNull, 0, 2, 0},544 /* 1 */ {OP_NewRowid, 0, 0, 0},545 /* 2 */ {OP_MakeRecord, 0, 2, 0},546 /* 3 */ {OP_Insert, 0, 0, 0},547 /* 4 */ {OP_Close, 0, 0, 0}548 };549 VdbeOp *aOp;550 Db *pDb = &db->aDb[p->iDb];551 int iRec;552 int memId = p->regCtr;553 554 iRec = sqlite3GetTempReg(pParse);555 assert( sqlite3SchemaMutexHeld(db, 0, pDb->pSchema) );556 sqlite3VdbeAddOp3(v, OP_Le, memId+2, sqlite3VdbeCurrentAddr(v)+7, memId);557 VdbeCoverage(v);558 sqlite3OpenTable(pParse, 0, p->iDb, pDb->pSchema->pSeqTab, OP_OpenWrite);559 aOp = sqlite3VdbeAddOpList(v, ArraySize(autoIncEnd), autoIncEnd, iLn);560 if( aOp==0 ) break;561 aOp[0].p1 = memId+1;562 aOp[1].p2 = memId+1;563 aOp[2].p1 = memId-1;564 aOp[2].p3 = iRec;565 aOp[3].p2 = iRec;566 aOp[3].p3 = memId+1;567 aOp[3].p5 = OPFLAG_APPEND;568 sqlite3ReleaseTempReg(pParse, iRec);569 }570}571void sqlite3AutoincrementEnd(Parse *pParse){572 if( pParse->pAinc ) autoIncrementEnd(pParse);573}574#else575/*576** If SQLITE_OMIT_AUTOINCREMENT is defined, then the three routines577** above are all no-ops578*/579# define autoIncBegin(A,B,C) (0)580# define autoIncStep(A,B,C)581#endif /* SQLITE_OMIT_AUTOINCREMENT */582 583/*584** If argument pVal is a Select object returned by an sqlite3MultiValues()585** that was able to use the co-routine optimization, finish coding the586** co-routine.587*/588void sqlite3MultiValuesEnd(Parse *pParse, Select *pVal){589 if( ALWAYS(pVal) && pVal->pSrc->nSrc>0 ){590 SrcItem *pItem = &pVal->pSrc->a[0];591 assert( (pItem->fg.isSubquery && pItem->u4.pSubq!=0) || pParse->nErr );592 if( pItem->fg.isSubquery ){593 sqlite3VdbeEndCoroutine(pParse->pVdbe, pItem->u4.pSubq->regReturn);594 sqlite3VdbeJumpHere(pParse->pVdbe, pItem->u4.pSubq->addrFillSub - 1);595 }596 }597}598 599/*600** Return true if all expressions in the expression-list passed as the601** only argument are constant.602*/603static int exprListIsConstant(Parse *pParse, ExprList *pRow){604 int ii;605 for(ii=0; ii<pRow->nExpr; ii++){606 if( 0==sqlite3ExprIsConstant(pParse, pRow->a[ii].pExpr) ) return 0;607 }608 return 1;609}610 611/*612** Return true if all expressions in the expression-list passed as the613** only argument are both constant and have no affinity.614*/615static int exprListIsNoAffinity(Parse *pParse, ExprList *pRow){616 int ii;617 if( exprListIsConstant(pParse,pRow)==0 ) return 0;618 for(ii=0; ii<pRow->nExpr; ii++){619 Expr *pExpr = pRow->a[ii].pExpr;620 assert( pExpr->op!=TK_RAISE );621 assert( pExpr->affExpr==0 );622 if( 0!=sqlite3ExprAffinity(pExpr) ) return 0;623 }624 return 1;625 626}627 628/*629** This function is called by the parser for the second and subsequent630** rows of a multi-row VALUES clause. Argument pLeft is the part of631** the VALUES clause already parsed, argument pRow is the vector of values632** for the new row. The Select object returned represents the complete633** VALUES clause, including the new row.634**635** There are two ways in which this may be achieved - by incremental 636** coding of a co-routine (the "co-routine" method) or by returning a637** Select object equivalent to the following (the "UNION ALL" method):638**639** "pLeft UNION ALL SELECT pRow"640**641** If the VALUES clause contains a lot of rows, this compound Select642** object may consume a lot of memory.643**644** When the co-routine method is used, each row that will be returned645** by the VALUES clause is coded into part of a co-routine as it is 646** passed to this function. The returned Select object is equivalent to:647**648** SELECT * FROM (649** Select object to read co-routine650** )651**652** The co-routine method is used in most cases. Exceptions are:653**654** a) If the current statement has a WITH clause. This is to avoid655** statements like:656**657** WITH cte AS ( VALUES('x'), ('y') ... )658** SELECT * FROM cte AS a, cte AS b;659**660** This will not work, as the co-routine uses a hard-coded register661** for its OP_Yield instructions, and so it is not possible for two662** cursors to iterate through it concurrently.663**664** b) The schema is currently being parsed (i.e. the VALUES clause is part 665** of a schema item like a VIEW or TRIGGER). In this case there is no VM666** being generated when parsing is taking place, and so generating 667** a co-routine is not possible.668**669** c) There are non-constant expressions in the VALUES clause (e.g.670** the VALUES clause is part of a correlated sub-query).671**672** d) One or more of the values in the first row of the VALUES clause673** has an affinity (i.e. is a CAST expression). This causes problems674** because the complex rules SQLite uses (see function 675** sqlite3SubqueryColumnTypes() in select.c) to determine the effective676** affinity of such a column for all rows require access to all values in677** the column simultaneously. 678*/679Select *sqlite3MultiValues(Parse *pParse, Select *pLeft, ExprList *pRow){680 681 if( pParse->bHasWith /* condition (a) above */682 || pParse->db->init.busy /* condition (b) above */683 || exprListIsConstant(pParse,pRow)==0 /* condition (c) above */684 || (pLeft->pSrc->nSrc==0 &&685 exprListIsNoAffinity(pParse,pLeft->pEList)==0) /* condition (d) above */686 || IN_SPECIAL_PARSE687 ){688 /* The co-routine method cannot be used. Fall back to UNION ALL. */689 Select *pSelect = 0;690 int f = SF_Values | SF_MultiValue;691 if( pLeft->pSrc->nSrc ){692 sqlite3MultiValuesEnd(pParse, pLeft);693 f = SF_Values;694 }else if( pLeft->pPrior ){695 /* In this case set the SF_MultiValue flag only if it was set on pLeft */696 f = (f & pLeft->selFlags);697 }698 pSelect = sqlite3SelectNew(pParse, pRow, 0, 0, 0, 0, 0, f, 0);699 pLeft->selFlags &= ~(u32)SF_MultiValue;700 if( pSelect ){701 pSelect->op = TK_ALL;702 pSelect->pPrior = pLeft;703 pLeft = pSelect;704 }705 }else{706 SrcItem *p = 0; /* SrcItem that reads from co-routine */707 708 if( pLeft->pSrc->nSrc==0 ){709 /* Co-routine has not yet been started and the special Select object710 ** that accesses the co-routine has not yet been created. This block 711 ** does both those things. */712 Vdbe *v = sqlite3GetVdbe(pParse);713 Select *pRet = sqlite3SelectNew(pParse, 0, 0, 0, 0, 0, 0, 0, 0);714 715 /* Ensure the database schema has been read. This is to ensure we have716 ** the correct text encoding. */717 if( (pParse->db->mDbFlags & DBFLAG_SchemaKnownOk)==0 ){718 sqlite3ReadSchema(pParse);719 }720 721 if( pRet ){722 SelectDest dest;723 Subquery *pSubq;724 pRet->pSrc->nSrc = 1;725 pRet->pPrior = pLeft->pPrior;726 pRet->op = pLeft->op;727 if( pRet->pPrior ) pRet->selFlags |= SF_Values;728 pLeft->pPrior = 0;729 pLeft->op = TK_SELECT;730 assert( pLeft->pNext==0 );731 assert( pRet->pNext==0 );732 p = &pRet->pSrc->a[0];733 p->fg.viaCoroutine = 1;734 p->iCursor = -1;735 assert( !p->fg.isIndexedBy && !p->fg.isTabFunc );736 p->u1.nRow = 2;737 if( sqlite3SrcItemAttachSubquery(pParse, p, pLeft, 0) ){738 pSubq = p->u4.pSubq;739 pSubq->addrFillSub = sqlite3VdbeCurrentAddr(v) + 1;740 pSubq->regReturn = ++pParse->nMem;741 sqlite3VdbeAddOp3(v, OP_InitCoroutine,742 pSubq->regReturn, 0, pSubq->addrFillSub);743 sqlite3SelectDestInit(&dest, SRT_Coroutine, pSubq->regReturn);744 745 /* Allocate registers for the output of the co-routine. Do so so746 ** that there are two unused registers immediately before those747 ** used by the co-routine. This allows the code in sqlite3Insert()748 ** to use these registers directly, instead of copying the output749 ** of the co-routine to a separate array for processing. */750 dest.iSdst = pParse->nMem + 3; 751 dest.nSdst = pLeft->pEList->nExpr;752 pParse->nMem += 2 + dest.nSdst;753 754 pLeft->selFlags |= SF_MultiValue;755 sqlite3Select(pParse, pLeft, &dest);756 pSubq->regResult = dest.iSdst;757 assert( pParse->nErr || dest.iSdst>0 );758 }759 pLeft = pRet;760 }761 }else{762 p = &pLeft->pSrc->a[0];763 assert( !p->fg.isTabFunc && !p->fg.isIndexedBy );764 p->u1.nRow++;765 }766 767 if( pParse->nErr==0 ){768 Subquery *pSubq;769 assert( p!=0 );770 assert( p->fg.isSubquery );771 pSubq = p->u4.pSubq;772 assert( pSubq!=0 );773 assert( pSubq->pSelect!=0 );774 assert( pSubq->pSelect->pEList!=0 );775 if( pSubq->pSelect->pEList->nExpr!=pRow->nExpr ){776 sqlite3SelectWrongNumTermsError(pParse, pSubq->pSelect);777 }else{778 sqlite3ExprCodeExprList(pParse, pRow, pSubq->regResult, 0, 0);779 sqlite3VdbeAddOp1(pParse->pVdbe, OP_Yield, pSubq->regReturn);780 }781 }782 sqlite3ExprListDelete(pParse->db, pRow);783 }784 785 return pLeft;786}787 788/* Forward declaration */789static int xferOptimization(790 Parse *pParse, /* Parser context */791 Table *pDest, /* The table we are inserting into */792 Select *pSelect, /* A SELECT statement to use as the data source */793 int onError, /* How to handle constraint errors */794 int iDbDest /* The database of pDest */795);796 797/*798** This routine is called to handle SQL of the following forms:799**800** insert into TABLE (IDLIST) values(EXPRLIST),(EXPRLIST),...801** insert into TABLE (IDLIST) select802** insert into TABLE (IDLIST) default values803**804** The IDLIST following the table name is always optional. If omitted,805** then a list of all (non-hidden) columns for the table is substituted.806** The IDLIST appears in the pColumn parameter. pColumn is NULL if IDLIST807** is omitted.808**809** For the pSelect parameter holds the values to be inserted for the810** first two forms shown above. A VALUES clause is really just short-hand811** for a SELECT statement that omits the FROM clause and everything else812** that follows. If the pSelect parameter is NULL, that means that the813** DEFAULT VALUES form of the INSERT statement is intended.814**815** The code generated follows one of four templates. For a simple816** insert with data coming from a single-row VALUES clause, the code executes817** once straight down through. Pseudo-code follows (we call this818** the "1st template"):819**820** open write cursor to <table> and its indices821** put VALUES clause expressions into registers822** write the resulting record into <table>823** cleanup824**825** The three remaining templates assume the statement is of the form826**827** INSERT INTO <table> SELECT ...828**829** If the SELECT clause is of the restricted form "SELECT * FROM <table2>" -830** in other words if the SELECT pulls all columns from a single table831** and there is no WHERE or LIMIT or GROUP BY or ORDER BY clauses, and832** if <table2> and <table1> are distinct tables but have identical833** schemas, including all the same indices, then a special optimization834** is invoked that copies raw records from <table2> over to <table1>.835** See the xferOptimization() function for the implementation of this836** template. This is the 2nd template.837**838** open a write cursor to <table>839** open read cursor on <table2>840** transfer all records in <table2> over to <table>841** close cursors842** foreach index on <table>843** open a write cursor on the <table> index844** open a read cursor on the corresponding <table2> index845** transfer all records from the read to the write cursors846** close cursors847** end foreach848**849** The 3rd template is for when the second template does not apply850** and the SELECT clause does not read from <table> at any time.851** The generated code follows this template:852**853** X <- A854** goto B855** A: setup for the SELECT856** loop over the rows in the SELECT857** load values into registers R..R+n858** yield X859** end loop860** cleanup after the SELECT861** end-coroutine X862** B: open write cursor to <table> and its indices863** C: yield X, at EOF goto D864** insert the select result into <table> from R..R+n865** goto C866** D: cleanup867**868** The 4th template is used if the insert statement takes its869** values from a SELECT but the data is being inserted into a table870** that is also read as part of the SELECT. In the third form,871** we have to use an intermediate table to store the results of872** the select. The template is like this:873**874** X <- A875** goto B876** A: setup for the SELECT877** loop over the tables in the SELECT878** load value into register R..R+n879** yield X880** end loop881** cleanup after the SELECT882** end co-routine R883** B: open temp table884** L: yield X, at EOF goto M885** insert row from R..R+n into temp table886** goto L887** M: open write cursor to <table> and its indices888** rewind temp table889** C: loop over rows of intermediate table890** transfer values form intermediate table into <table>891** end loop892** D: cleanup893*/894void sqlite3Insert(895 Parse *pParse, /* Parser context */896 SrcList *pTabList, /* Name of table into which we are inserting */897 Select *pSelect, /* A SELECT statement to use as the data source */898 IdList *pColumn, /* Column names corresponding to IDLIST, or NULL. */899 int onError, /* How to handle constraint errors */900 Upsert *pUpsert /* ON CONFLICT clauses for upsert, or NULL */901){902 sqlite3 *db; /* The main database structure */903 Table *pTab; /* The table to insert into. aka TABLE */904 int i, j; /* Loop counters */905 Vdbe *v; /* Generate code into this virtual machine */906 Index *pIdx; /* For looping over indices of the table */907 int nColumn; /* Number of columns in the data */908 int nHidden = 0; /* Number of hidden columns if TABLE is virtual */909 int iDataCur = 0; /* VDBE cursor that is the main data repository */910 int iIdxCur = 0; /* First index cursor */911 int ipkColumn = -1; /* Column that is the INTEGER PRIMARY KEY */912 int endOfLoop; /* Label for the end of the insertion loop */913 int srcTab = 0; /* Data comes from this temporary cursor if >=0 */914 int addrInsTop = 0; /* Jump to label "D" */915 int addrCont = 0; /* Top of insert loop. Label "C" in templates 3 and 4 */916 SelectDest dest; /* Destination for SELECT on rhs of INSERT */917 int iDb; /* Index of database holding TABLE */918 u8 useTempTable = 0; /* Store SELECT results in intermediate table */919 u8 appendFlag = 0; /* True if the insert is likely to be an append */920 u8 withoutRowid; /* 0 for normal table. 1 for WITHOUT ROWID table */921 u8 bIdListInOrder; /* True if IDLIST is in table order */922 ExprList *pList = 0; /* List of VALUES() to be inserted */923 int iRegStore; /* Register in which to store next column */924 925 /* Register allocations */926 int regFromSelect = 0;/* Base register for data coming from SELECT */927 int regAutoinc = 0; /* Register holding the AUTOINCREMENT counter */928 int regRowCount = 0; /* Memory cell used for the row counter */929 int regIns; /* Block of regs holding rowid+data being inserted */930 int regRowid; /* registers holding insert rowid */931 int regData; /* register holding first column to insert */932 int *aRegIdx = 0; /* One register allocated to each index */933 int *aTabColMap = 0; /* Mapping from pTab columns to pCol entries */934 935#ifndef SQLITE_OMIT_TRIGGER936 int isView; /* True if attempting to insert into a view */937 Trigger *pTrigger; /* List of triggers on pTab, if required */938 int tmask; /* Mask of trigger times */939#endif940 941 db = pParse->db;942 assert( db->pParse==pParse );943 if( pParse->nErr ){944 goto insert_cleanup;945 }946 assert( db->mallocFailed==0 );947 dest.iSDParm = 0; /* Suppress a harmless compiler warning */948 949 /* If the Select object is really just a simple VALUES() list with a950 ** single row (the common case) then keep that one row of values951 ** and discard the other (unused) parts of the pSelect object952 */953 if( pSelect && (pSelect->selFlags & SF_Values)!=0 && pSelect->pPrior==0 ){954 pList = pSelect->pEList;955 pSelect->pEList = 0;956 sqlite3SelectDelete(db, pSelect);957 pSelect = 0;958 }959 960 /* Locate the table into which we will be inserting new information.961 */962 assert( pTabList->nSrc==1 );963 pTab = sqlite3SrcListLookup(pParse, pTabList);964 if( pTab==0 ){965 goto insert_cleanup;966 }967 iDb = sqlite3SchemaToIndex(db, pTab->pSchema);968 assert( iDb<db->nDb );969 if( sqlite3AuthCheck(pParse, SQLITE_INSERT, pTab->zName, 0,970 db->aDb[iDb].zDbSName) ){971 goto insert_cleanup;972 }973 withoutRowid = !HasRowid(pTab);974 975 /* Figure out if we have any triggers and if the table being976 ** inserted into is a view977 */978#ifndef SQLITE_OMIT_TRIGGER979 pTrigger = sqlite3TriggersExist(pParse, pTab, TK_INSERT, 0, &tmask);980 isView = IsView(pTab);981#else982# define pTrigger 0983# define tmask 0984# define isView 0985#endif986#ifdef SQLITE_OMIT_VIEW987# undef isView988# define isView 0989#endif990 assert( (pTrigger && tmask) || (pTrigger==0 && tmask==0) );991 992#if TREETRACE_ENABLED993 if( sqlite3TreeTrace & 0x10000 ){994 sqlite3TreeViewLine(0, "In sqlite3Insert() at %s:%d", __FILE__, __LINE__);995 sqlite3TreeViewInsert(pParse->pWith, pTabList, pColumn, pSelect, pList,996 onError, pUpsert, pTrigger);997 }998#endif999 1000 /* If pTab is really a view, make sure it has been initialized.1001 ** ViewGetColumnNames() is a no-op if pTab is not a view.1002 */1003 if( sqlite3ViewGetColumnNames(pParse, pTab) ){1004 goto insert_cleanup;1005 }1006 1007 /* Cannot insert into a read-only table.1008 */1009 if( sqlite3IsReadOnly(pParse, pTab, pTrigger) ){1010 goto insert_cleanup;1011 }1012 1013 /* Allocate a VDBE1014 */1015 v = sqlite3GetVdbe(pParse);1016 if( v==0 ) goto insert_cleanup;1017 if( pParse->nested==0 ) sqlite3VdbeCountChanges(v);1018 sqlite3BeginWriteOperation(pParse, pSelect || pTrigger, iDb);1019 1020#ifndef SQLITE_OMIT_XFER_OPT1021 /* If the statement is of the form1022 **1023 ** INSERT INTO <table1> SELECT * FROM <table2>;1024 **1025 ** Then special optimizations can be applied that make the transfer1026 ** very fast and which reduce fragmentation of indices.1027 **1028 ** This is the 2nd template.1029 */1030 if( pColumn==01031 && pSelect!=01032 && pTrigger==01033 && xferOptimization(pParse, pTab, pSelect, onError, iDb)1034 ){1035 assert( !pTrigger );1036 assert( pList==0 );1037 goto insert_end;1038 }1039#endif /* SQLITE_OMIT_XFER_OPT */1040 1041 /* If this is an AUTOINCREMENT table, look up the sequence number in the1042 ** sqlite_sequence table and store it in memory cell regAutoinc.1043 */1044 regAutoinc = autoIncBegin(pParse, iDb, pTab);1045 1046 /* Allocate a block registers to hold the rowid and the values1047 ** for all columns of the new row.1048 */1049 regRowid = regIns = pParse->nMem+1;1050 pParse->nMem += pTab->nCol + 1;1051 if( IsVirtual(pTab) ){1052 regRowid++;1053 pParse->nMem++;1054 }1055 regData = regRowid+1;1056 1057 /* If the INSERT statement included an IDLIST term, then make sure1058 ** all elements of the IDLIST really are columns of the table and1059 ** remember the column indices.1060 **1061 ** If the table has an INTEGER PRIMARY KEY column and that column1062 ** is named in the IDLIST, then record in the ipkColumn variable1063 ** the index into IDLIST of the primary key column. ipkColumn is1064 ** the index of the primary key as it appears in IDLIST, not as1065 ** is appears in the original table. (The index of the INTEGER1066 ** PRIMARY KEY in the original table is pTab->iPKey.) After this1067 ** loop, if ipkColumn==(-1), that means that integer primary key1068 ** is unspecified, and hence the table is either WITHOUT ROWID or1069 ** it will automatically generated an integer primary key.1070 **1071 ** bIdListInOrder is true if the columns in IDLIST are in storage1072 ** order. This enables an optimization that avoids shuffling the1073 ** columns into storage order. False negatives are harmless,1074 ** but false positives will cause database corruption.1075 */1076 bIdListInOrder = (pTab->tabFlags & (TF_OOOHidden|TF_HasStored))==0;1077 if( pColumn ){1078 aTabColMap = sqlite3DbMallocZero(db, pTab->nCol*sizeof(int));1079 if( aTabColMap==0 ) goto insert_cleanup;1080 for(i=0; i<pColumn->nId; i++){1081 j = sqlite3ColumnIndex(pTab, pColumn->a[i].zName);1082 if( j>=0 ){1083 if( aTabColMap[j]==0 ) aTabColMap[j] = i+1;1084 if( i!=j ) bIdListInOrder = 0;1085 if( j==pTab->iPKey ){1086 ipkColumn = i; assert( !withoutRowid );1087 }1088#ifndef SQLITE_OMIT_GENERATED_COLUMNS1089 if( pTab->aCol[j].colFlags & (COLFLAG_STORED|COLFLAG_VIRTUAL) ){1090 sqlite3ErrorMsg(pParse,1091 "cannot INSERT into generated column \"%s\"",1092 pTab->aCol[j].zCnName);1093 goto insert_cleanup;1094 }1095#endif1096 }else{1097 if( sqlite3IsRowid(pColumn->a[i].zName) && !withoutRowid ){1098 ipkColumn = i;1099 bIdListInOrder = 0;1100 }else{1101 sqlite3ErrorMsg(pParse, "table %S has no column named %s",1102 pTabList->a, pColumn->a[i].zName);1103 pParse->checkSchema = 1;1104 goto insert_cleanup;1105 }1106 }1107 }1108 }1109 1110 /* Figure out how many columns of data are supplied. If the data1111 ** is coming from a SELECT statement, then generate a co-routine that1112 ** produces a single row of the SELECT on each invocation. The1113 ** co-routine is the common header to the 3rd and 4th templates.1114 */1115 if( pSelect ){1116 /* Data is coming from a SELECT or from a multi-row VALUES clause.1117 ** Generate a co-routine to run the SELECT. */1118 int rc; /* Result code */1119 1120 if( pSelect->pSrc->nSrc==1 1121 && pSelect->pSrc->a[0].fg.viaCoroutine 1122 && pSelect->pPrior==01123 ){1124 SrcItem *pItem = &pSelect->pSrc->a[0];1125 Subquery *pSubq;1126 assert( pItem->fg.isSubquery );1127 pSubq = pItem->u4.pSubq;1128 dest.iSDParm = pSubq->regReturn;1129 regFromSelect = pSubq->regResult;1130 assert( pSubq->pSelect!=0 );1131 assert( pSubq->pSelect->pEList!=0 );1132 nColumn = pSubq->pSelect->pEList->nExpr;1133 ExplainQueryPlan((pParse, 0, "SCAN %S", pItem));1134 if( bIdListInOrder && nColumn==pTab->nCol ){1135 regData = regFromSelect;1136 regRowid = regData - 1;1137 regIns = regRowid - (IsVirtual(pTab) ? 1 : 0);1138 }1139 }else{1140 int addrTop; /* Top of the co-routine */1141 int regYield = ++pParse->nMem;1142 addrTop = sqlite3VdbeCurrentAddr(v) + 1;1143 sqlite3VdbeAddOp3(v, OP_InitCoroutine, regYield, 0, addrTop);1144 sqlite3SelectDestInit(&dest, SRT_Coroutine, regYield);1145 dest.iSdst = bIdListInOrder ? regData : 0;1146 dest.nSdst = pTab->nCol;1147 rc = sqlite3Select(pParse, pSelect, &dest);1148 regFromSelect = dest.iSdst;1149 assert( db->pParse==pParse );1150 if( rc || pParse->nErr ) goto insert_cleanup;1151 assert( db->mallocFailed==0 );1152 sqlite3VdbeEndCoroutine(v, regYield);1153 sqlite3VdbeJumpHere(v, addrTop - 1); /* label B: */1154 assert( pSelect->pEList );1155 nColumn = pSelect->pEList->nExpr;1156 }1157 1158 /* Set useTempTable to TRUE if the result of the SELECT statement1159 ** should be written into a temporary table (template 4). Set to1160 ** FALSE if each output row of the SELECT can be written directly into1161 ** the destination table (template 3).1162 **1163 ** A temp table must be used if the table being updated is also one1164 ** of the tables being read by the SELECT statement. Also use a1165 ** temp table in the case of row triggers.1166 */1167 if( pTrigger || readsTable(pParse, iDb, pTab) ){1168 useTempTable = 1;1169 }1170 1171 if( useTempTable ){1172 /* Invoke the coroutine to extract information from the SELECT1173 ** and add it to a transient table srcTab. The code generated1174 ** here is from the 4th template:1175 **1176 ** B: open temp table1177 ** L: yield X, goto M at EOF1178 ** insert row from R..R+n into temp table1179 ** goto L1180 ** M: ...1181 */1182 int regRec; /* Register to hold packed record */1183 int regTempRowid; /* Register to hold temp table ROWID */1184 int addrL; /* Label "L" */1185 1186 srcTab = pParse->nTab++;1187 regRec = sqlite3GetTempReg(pParse);1188 regTempRowid = sqlite3GetTempReg(pParse);1189 sqlite3VdbeAddOp2(v, OP_OpenEphemeral, srcTab, nColumn);1190 addrL = sqlite3VdbeAddOp1(v, OP_Yield, dest.iSDParm); VdbeCoverage(v);1191 sqlite3VdbeAddOp3(v, OP_MakeRecord, regFromSelect, nColumn, regRec);1192 sqlite3VdbeAddOp2(v, OP_NewRowid, srcTab, regTempRowid);1193 sqlite3VdbeAddOp3(v, OP_Insert, srcTab, regRec, regTempRowid);1194 sqlite3VdbeGoto(v, addrL);1195 sqlite3VdbeJumpHere(v, addrL);1196 sqlite3ReleaseTempReg(pParse, regRec);1197 sqlite3ReleaseTempReg(pParse, regTempRowid);1198 }1199 }else{1200 /* This is the case if the data for the INSERT is coming from a