ds@1952: /* ds@1952: ** 2001 September 15 ds@1952: ** ds@1952: ** The author disclaims copyright to this source code. In place of ds@1952: ** a legal notice, here is a blessing: ds@1952: ** ds@1952: ** May you do good and not evil. ds@1952: ** May you find forgiveness for yourself and forgive others. ds@1952: ** May you share freely, never taking more than you give. ds@1952: ** ds@1952: ************************************************************************* ds@1952: ** This file contains C code routines that are called by the parser ds@1952: ** to handle INSERT statements in SQLite. ds@1952: ** ds@1952: ** $Id$ ds@1952: */ ds@1952: #include "sqliteInt.h" ds@1952: ds@1952: /* ds@1952: ** Set P3 of the most recently inserted opcode to a column affinity ds@1952: ** string for index pIdx. A column affinity string has one character ds@1952: ** for each column in the table, according to the affinity of the column: ds@1952: ** ds@1952: ** Character Column affinity ds@1952: ** ------------------------------ ds@1952: ** 'a' TEXT ds@1952: ** 'b' NONE ds@1952: ** 'c' NUMERIC ds@1952: ** 'd' INTEGER ds@1952: ** 'e' REAL ds@1952: */ ds@1952: void sqlite3IndexAffinityStr(Vdbe *v, Index *pIdx){ ds@1952: if( !pIdx->zColAff ){ ds@1952: /* The first time a column affinity string for a particular index is ds@1952: ** required, it is allocated and populated here. It is then stored as ds@1952: ** a member of the Index structure for subsequent use. ds@1952: ** ds@1952: ** The column affinity string will eventually be deleted by ds@1952: ** sqliteDeleteIndex() when the Index structure itself is cleaned ds@1952: ** up. ds@1952: */ ds@1952: int n; ds@1952: Table *pTab = pIdx->pTable; ds@1952: sqlite3 *db = sqlite3VdbeDb(v); ds@1952: pIdx->zColAff = (char *)sqlite3DbMallocZero(db, pIdx->nColumn+1); ds@1952: if( !pIdx->zColAff ){ ds@1952: return; ds@1952: } ds@1952: for(n=0; nnColumn; n++){ ds@1952: pIdx->zColAff[n] = pTab->aCol[pIdx->aiColumn[n]].affinity; ds@1952: } ds@1952: pIdx->zColAff[pIdx->nColumn] = '\0'; ds@1952: } ds@1952: ds@1952: sqlite3VdbeChangeP3(v, -1, pIdx->zColAff, 0); ds@1952: } ds@1952: ds@1952: /* ds@1952: ** Set P3 of the most recently inserted opcode to a column affinity ds@1952: ** string for table pTab. A column affinity string has one character ds@1952: ** for each column indexed by the index, according to the affinity of the ds@1952: ** column: ds@1952: ** ds@1952: ** Character Column affinity ds@1952: ** ------------------------------ ds@1952: ** 'a' TEXT ds@1952: ** 'b' NONE ds@1952: ** 'c' NUMERIC ds@1952: ** 'd' INTEGER ds@1952: ** 'e' REAL ds@1952: */ ds@1952: void sqlite3TableAffinityStr(Vdbe *v, Table *pTab){ ds@1952: /* The first time a column affinity string for a particular table ds@1952: ** is required, it is allocated and populated here. It is then ds@1952: ** stored as a member of the Table structure for subsequent use. ds@1952: ** ds@1952: ** The column affinity string will eventually be deleted by ds@1952: ** sqlite3DeleteTable() when the Table structure itself is cleaned up. ds@1952: */ ds@1952: if( !pTab->zColAff ){ ds@1952: char *zColAff; ds@1952: int i; ds@1952: sqlite3 *db = sqlite3VdbeDb(v); ds@1952: ds@1952: zColAff = (char *)sqlite3DbMallocZero(db, pTab->nCol+1); ds@1952: if( !zColAff ){ ds@1952: return; ds@1952: } ds@1952: ds@1952: for(i=0; inCol; i++){ ds@1952: zColAff[i] = pTab->aCol[i].affinity; ds@1952: } ds@1952: zColAff[pTab->nCol] = '\0'; ds@1952: ds@1952: pTab->zColAff = zColAff; ds@1952: } ds@1952: ds@1952: sqlite3VdbeChangeP3(v, -1, pTab->zColAff, 0); ds@1952: } ds@1952: ds@1952: /* ds@1952: ** Return non-zero if SELECT statement p opens the table with rootpage ds@1952: ** iTab in database iDb. This is used to see if a statement of the form ds@1952: ** "INSERT INTO SELECT ..." can run without using temporary ds@1952: ** table for the results of the SELECT. ds@1952: ** ds@1952: ** No checking is done for sub-selects that are part of expressions. ds@1952: */ ds@1952: static int selectReadsTable(Select *p, Schema *pSchema, int iTab){ ds@1952: int i; ds@1952: struct SrcList_item *pItem; ds@1952: if( p->pSrc==0 ) return 0; ds@1952: for(i=0, pItem=p->pSrc->a; ipSrc->nSrc; i++, pItem++){ ds@1952: if( pItem->pSelect ){ ds@1952: if( selectReadsTable(pItem->pSelect, pSchema, iTab) ) return 1; ds@1952: }else{ ds@1952: if( pItem->pTab->pSchema==pSchema && pItem->pTab->tnum==iTab ) return 1; ds@1952: } ds@1952: } ds@1952: return 0; ds@1952: } ds@1952: ds@1952: #ifndef SQLITE_OMIT_AUTOINCREMENT ds@1952: /* ds@1952: ** Write out code to initialize the autoincrement logic. This code ds@1952: ** looks up the current autoincrement value in the sqlite_sequence ds@1952: ** table and stores that value in a memory cell. Code generated by ds@1952: ** autoIncStep() will keep that memory cell holding the largest ds@1952: ** rowid value. Code generated by autoIncEnd() will write the new ds@1952: ** largest value of the counter back into the sqlite_sequence table. ds@1952: ** ds@1952: ** This routine returns the index of the mem[] cell that contains ds@1952: ** the maximum rowid counter. ds@1952: ** ds@1952: ** Two memory cells are allocated. The next memory cell after the ds@1952: ** one returned holds the rowid in sqlite_sequence where we will ds@1952: ** write back the revised maximum rowid. ds@1952: */ ds@1952: static int autoIncBegin( ds@1952: Parse *pParse, /* Parsing context */ ds@1952: int iDb, /* Index of the database holding pTab */ ds@1952: Table *pTab /* The table we are writing to */ ds@1952: ){ ds@1952: int memId = 0; ds@1952: if( pTab->autoInc ){ ds@1952: Vdbe *v = pParse->pVdbe; ds@1952: Db *pDb = &pParse->db->aDb[iDb]; ds@1952: int iCur = pParse->nTab; ds@1952: int addr; ds@1952: assert( v ); ds@1952: addr = sqlite3VdbeCurrentAddr(v); ds@1952: memId = pParse->nMem+1; ds@1952: pParse->nMem += 2; ds@1952: sqlite3OpenTable(pParse, iCur, iDb, pDb->pSchema->pSeqTab, OP_OpenRead); ds@1952: sqlite3VdbeAddOp(v, OP_Rewind, iCur, addr+13); ds@1952: sqlite3VdbeAddOp(v, OP_Column, iCur, 0); ds@1952: sqlite3VdbeOp3(v, OP_String8, 0, 0, pTab->zName, 0); ds@1952: sqlite3VdbeAddOp(v, OP_Ne, 0x100, addr+12); ds@1952: sqlite3VdbeAddOp(v, OP_Rowid, iCur, 0); ds@1952: sqlite3VdbeAddOp(v, OP_MemStore, memId-1, 1); ds@1952: sqlite3VdbeAddOp(v, OP_Column, iCur, 1); ds@1952: sqlite3VdbeAddOp(v, OP_MemStore, memId, 1); ds@1952: sqlite3VdbeAddOp(v, OP_Goto, 0, addr+13); ds@1952: sqlite3VdbeAddOp(v, OP_Next, iCur, addr+4); ds@1952: sqlite3VdbeAddOp(v, OP_Close, iCur, 0); ds@1952: } ds@1952: return memId; ds@1952: } ds@1952: ds@1952: /* ds@1952: ** Update the maximum rowid for an autoincrement calculation. ds@1952: ** ds@1952: ** This routine should be called when the top of the stack holds a ds@1952: ** new rowid that is about to be inserted. If that new rowid is ds@1952: ** larger than the maximum rowid in the memId memory cell, then the ds@1952: ** memory cell is updated. The stack is unchanged. ds@1952: */ ds@1952: static void autoIncStep(Parse *pParse, int memId){ ds@1952: if( memId>0 ){ ds@1952: sqlite3VdbeAddOp(pParse->pVdbe, OP_MemMax, memId, 0); ds@1952: } ds@1952: } ds@1952: ds@1952: /* ds@1952: ** After doing one or more inserts, the maximum rowid is stored ds@1952: ** in mem[memId]. Generate code to write this value back into the ds@1952: ** the sqlite_sequence table. ds@1952: */ ds@1952: static void autoIncEnd( ds@1952: Parse *pParse, /* The parsing context */ ds@1952: int iDb, /* Index of the database holding pTab */ ds@1952: Table *pTab, /* Table we are inserting into */ ds@1952: int memId /* Memory cell holding the maximum rowid */ ds@1952: ){ ds@1952: if( pTab->autoInc ){ ds@1952: int iCur = pParse->nTab; ds@1952: Vdbe *v = pParse->pVdbe; ds@1952: Db *pDb = &pParse->db->aDb[iDb]; ds@1952: int addr; ds@1952: assert( v ); ds@1952: addr = sqlite3VdbeCurrentAddr(v); ds@1952: sqlite3OpenTable(pParse, iCur, iDb, pDb->pSchema->pSeqTab, OP_OpenWrite); ds@1952: sqlite3VdbeAddOp(v, OP_MemLoad, memId-1, 0); ds@1952: sqlite3VdbeAddOp(v, OP_NotNull, -1, addr+7); ds@1952: sqlite3VdbeAddOp(v, OP_Pop, 1, 0); ds@1952: sqlite3VdbeAddOp(v, OP_NewRowid, iCur, 0); ds@1952: sqlite3VdbeOp3(v, OP_String8, 0, 0, pTab->zName, 0); ds@1952: sqlite3VdbeAddOp(v, OP_MemLoad, memId, 0); ds@1952: sqlite3VdbeAddOp(v, OP_MakeRecord, 2, 0); ds@1952: sqlite3VdbeAddOp(v, OP_Insert, iCur, OPFLAG_APPEND); ds@1952: sqlite3VdbeAddOp(v, OP_Close, iCur, 0); ds@1952: } ds@1952: } ds@1952: #else ds@1952: /* ds@1952: ** If SQLITE_OMIT_AUTOINCREMENT is defined, then the three routines ds@1952: ** above are all no-ops ds@1952: */ ds@1952: # define autoIncBegin(A,B,C) (0) ds@1952: # define autoIncStep(A,B) ds@1952: # define autoIncEnd(A,B,C,D) ds@1952: #endif /* SQLITE_OMIT_AUTOINCREMENT */ ds@1952: ds@1952: ds@1952: /* Forward declaration */ ds@1952: static int xferOptimization( ds@1952: Parse *pParse, /* Parser context */ ds@1952: Table *pDest, /* The table we are inserting into */ ds@1952: Select *pSelect, /* A SELECT statement to use as the data source */ ds@1952: int onError, /* How to handle constraint errors */ ds@1952: int iDbDest /* The database of pDest */ ds@1952: ); ds@1952: ds@1952: /* ds@1952: ** This routine is call to handle SQL of the following forms: ds@1952: ** ds@1952: ** insert into TABLE (IDLIST) values(EXPRLIST) ds@1952: ** insert into TABLE (IDLIST) select ds@1952: ** ds@1952: ** The IDLIST following the table name is always optional. If omitted, ds@1952: ** then a list of all columns for the table is substituted. The IDLIST ds@1952: ** appears in the pColumn parameter. pColumn is NULL if IDLIST is omitted. ds@1952: ** ds@1952: ** The pList parameter holds EXPRLIST in the first form of the INSERT ds@1952: ** statement above, and pSelect is NULL. For the second form, pList is ds@1952: ** NULL and pSelect is a pointer to the select statement used to generate ds@1952: ** data for the insert. ds@1952: ** ds@1952: ** The code generated follows one of four templates. For a simple ds@1952: ** select with data coming from a VALUES clause, the code executes ds@1952: ** once straight down through. The template looks like this: ds@1952: ** ds@1952: ** open write cursor to and its indices ds@1952: ** puts VALUES clause expressions onto the stack ds@1952: ** write the resulting record into
ds@1952: ** cleanup ds@1952: ** ds@1952: ** The three remaining templates assume the statement is of the form ds@1952: ** ds@1952: ** INSERT INTO
SELECT ... ds@1952: ** ds@1952: ** If the SELECT clause is of the restricted form "SELECT * FROM " - ds@1952: ** in other words if the SELECT pulls all columns from a single table ds@1952: ** and there is no WHERE or LIMIT or GROUP BY or ORDER BY clauses, and ds@1952: ** if and are distinct tables but have identical ds@1952: ** schemas, including all the same indices, then a special optimization ds@1952: ** is invoked that copies raw records from over to . ds@1952: ** See the xferOptimization() function for the implementation of this ds@1952: ** template. This is the second template. ds@1952: ** ds@1952: ** open a write cursor to
ds@1952: ** open read cursor on ds@1952: ** transfer all records in over to
ds@1952: ** close cursors ds@1952: ** foreach index on
ds@1952: ** open a write cursor on the
index ds@1952: ** open a read cursor on the corresponding index ds@1952: ** transfer all records from the read to the write cursors ds@1952: ** close cursors ds@1952: ** end foreach ds@1952: ** ds@1952: ** The third template is for when the second template does not apply ds@1952: ** and the SELECT clause does not read from
at any time. ds@1952: ** The generated code follows this template: ds@1952: ** ds@1952: ** goto B ds@1952: ** A: setup for the SELECT ds@1952: ** loop over the rows in the SELECT ds@1952: ** gosub C ds@1952: ** end loop ds@1952: ** cleanup after the SELECT ds@1952: ** goto D ds@1952: ** B: open write cursor to
and its indices ds@1952: ** goto A ds@1952: ** C: insert the select result into
ds@1952: ** return ds@1952: ** D: cleanup ds@1952: ** ds@1952: ** The fourth template is used if the insert statement takes its ds@1952: ** values from a SELECT but the data is being inserted into a table ds@1952: ** that is also read as part of the SELECT. In the third form, ds@1952: ** we have to use a intermediate table to store the results of ds@1952: ** the select. The template is like this: ds@1952: ** ds@1952: ** goto B ds@1952: ** A: setup for the SELECT ds@1952: ** loop over the tables in the SELECT ds@1952: ** gosub C ds@1952: ** end loop ds@1952: ** cleanup after the SELECT ds@1952: ** goto D ds@1952: ** C: insert the select result into the intermediate table ds@1952: ** return ds@1952: ** B: open a cursor to an intermediate table ds@1952: ** goto A ds@1952: ** D: open write cursor to
and its indices ds@1952: ** loop over the intermediate table ds@1952: ** transfer values form intermediate table into
ds@1952: ** end the loop ds@1952: ** cleanup ds@1952: */ ds@1952: void sqlite3Insert( ds@1952: Parse *pParse, /* Parser context */ ds@1952: SrcList *pTabList, /* Name of table into which we are inserting */ ds@1952: ExprList *pList, /* List of values to be inserted */ ds@1952: Select *pSelect, /* A SELECT statement to use as the data source */ ds@1952: IdList *pColumn, /* Column names corresponding to IDLIST. */ ds@1952: int onError /* How to handle constraint errors */ ds@1952: ){ ds@1952: Table *pTab; /* The table to insert into */ ds@1952: char *zTab; /* Name of the table into which we are inserting */ ds@1952: const char *zDb; /* Name of the database holding this table */ ds@1952: int i, j, idx; /* Loop counters */ ds@1952: Vdbe *v; /* Generate code into this virtual machine */ ds@1952: Index *pIdx; /* For looping over indices of the table */ ds@1952: int nColumn; /* Number of columns in the data */ ds@1952: int base = 0; /* VDBE Cursor number for pTab */ ds@1952: int iCont=0,iBreak=0; /* Beginning and end of the loop over srcTab */ ds@1952: sqlite3 *db; /* The main database structure */ ds@1952: int keyColumn = -1; /* Column that is the INTEGER PRIMARY KEY */ ds@1952: int endOfLoop; /* Label for the end of the insertion loop */ ds@1952: int useTempTable = 0; /* Store SELECT results in intermediate table */ ds@1952: int srcTab = 0; /* Data comes from this temporary cursor if >=0 */ ds@1952: int iSelectLoop = 0; /* Address of code that implements the SELECT */ ds@1952: int iCleanup = 0; /* Address of the cleanup code */ ds@1952: int iInsertBlock = 0; /* Address of the subroutine used to insert data */ ds@1952: int iCntMem = 0; /* Memory cell used for the row counter */ ds@1952: int newIdx = -1; /* Cursor for the NEW table */ ds@1952: Db *pDb; /* The database containing table being inserted into */ ds@1952: int counterMem = 0; /* Memory cell holding AUTOINCREMENT counter */ ds@1952: int appendFlag = 0; /* True if the insert is likely to be an append */ ds@1952: int iDb; ds@1952: ds@1952: int nHidden = 0; ds@1952: ds@1952: #ifndef SQLITE_OMIT_TRIGGER ds@1952: int isView; /* True if attempting to insert into a view */ ds@1952: int triggers_exist = 0; /* True if there are FOR EACH ROW triggers */ ds@1952: #endif ds@1952: ds@1952: db = pParse->db; ds@1952: if( pParse->nErr || db->mallocFailed ){ ds@1952: goto insert_cleanup; ds@1952: } ds@1952: ds@1952: /* Locate the table into which we will be inserting new information. ds@1952: */ ds@1952: assert( pTabList->nSrc==1 ); ds@1952: zTab = pTabList->a[0].zName; ds@1952: if( zTab==0 ) goto insert_cleanup; ds@1952: pTab = sqlite3SrcListLookup(pParse, pTabList); ds@1952: if( pTab==0 ){ ds@1952: goto insert_cleanup; ds@1952: } ds@1952: iDb = sqlite3SchemaToIndex(db, pTab->pSchema); ds@1952: assert( iDbnDb ); ds@1952: pDb = &db->aDb[iDb]; ds@1952: zDb = pDb->zName; ds@1952: if( sqlite3AuthCheck(pParse, SQLITE_INSERT, pTab->zName, 0, zDb) ){ ds@1952: goto insert_cleanup; ds@1952: } ds@1952: ds@1952: /* Figure out if we have any triggers and if the table being ds@1952: ** inserted into is a view ds@1952: */ ds@1952: #ifndef SQLITE_OMIT_TRIGGER ds@1952: triggers_exist = sqlite3TriggersExist(pParse, pTab, TK_INSERT, 0); ds@1952: isView = pTab->pSelect!=0; ds@1952: #else ds@1952: # define triggers_exist 0 ds@1952: # define isView 0 ds@1952: #endif ds@1952: #ifdef SQLITE_OMIT_VIEW ds@1952: # undef isView ds@1952: # define isView 0 ds@1952: #endif ds@1952: ds@1952: /* Ensure that: ds@1952: * (a) the table is not read-only, ds@1952: * (b) that if it is a view then ON INSERT triggers exist ds@1952: */ ds@1952: if( sqlite3IsReadOnly(pParse, pTab, triggers_exist) ){ ds@1952: goto insert_cleanup; ds@1952: } ds@1952: assert( pTab!=0 ); ds@1952: ds@1952: /* If pTab is really a view, make sure it has been initialized. ds@1952: ** ViewGetColumnNames() is a no-op if pTab is not a view (or virtual ds@1952: ** module table). ds@1952: */ ds@1952: if( sqlite3ViewGetColumnNames(pParse, pTab) ){ ds@1952: goto insert_cleanup; ds@1952: } ds@1952: ds@1952: /* Allocate a VDBE ds@1952: */ ds@1952: v = sqlite3GetVdbe(pParse); ds@1952: if( v==0 ) goto insert_cleanup; ds@1952: if( pParse->nested==0 ) sqlite3VdbeCountChanges(v); ds@1952: sqlite3BeginWriteOperation(pParse, pSelect || triggers_exist, iDb); ds@1952: ds@1952: /* if there are row triggers, allocate a temp table for new.* references. */ ds@1952: if( triggers_exist ){ ds@1952: newIdx = pParse->nTab++; ds@1952: } ds@1952: ds@1952: #ifndef SQLITE_OMIT_XFER_OPT ds@1952: /* If the statement is of the form ds@1952: ** ds@1952: ** INSERT INTO SELECT * FROM ; ds@1952: ** ds@1952: ** Then special optimizations can be applied that make the transfer ds@1952: ** very fast and which reduce fragmentation of indices. ds@1952: */ ds@1952: if( pColumn==0 && xferOptimization(pParse, pTab, pSelect, onError, iDb) ){ ds@1952: assert( !triggers_exist ); ds@1952: assert( pList==0 ); ds@1952: goto insert_cleanup; ds@1952: } ds@1952: #endif /* SQLITE_OMIT_XFER_OPT */ ds@1952: ds@1952: /* If this is an AUTOINCREMENT table, look up the sequence number in the ds@1952: ** sqlite_sequence table and store it in memory cell counterMem. Also ds@1952: ** remember the rowid of the sqlite_sequence table entry in memory cell ds@1952: ** counterRowid. ds@1952: */ ds@1952: counterMem = autoIncBegin(pParse, iDb, pTab); ds@1952: ds@1952: /* Figure out how many columns of data are supplied. If the data ds@1952: ** is coming from a SELECT statement, then this step also generates ds@1952: ** all the code to implement the SELECT statement and invoke a subroutine ds@1952: ** to process each row of the result. (Template 2.) If the SELECT ds@1952: ** statement uses the the table that is being inserted into, then the ds@1952: ** subroutine is also coded here. That subroutine stores the SELECT ds@1952: ** results in a temporary table. (Template 3.) ds@1952: */ ds@1952: if( pSelect ){ ds@1952: /* Data is coming from a SELECT. Generate code to implement that SELECT ds@1952: */ ds@1952: int rc, iInitCode; ds@1952: iInitCode = sqlite3VdbeAddOp(v, OP_Goto, 0, 0); ds@1952: iSelectLoop = sqlite3VdbeCurrentAddr(v); ds@1952: iInsertBlock = sqlite3VdbeMakeLabel(v); ds@1952: ds@1952: /* Resolve the expressions in the SELECT statement and execute it. */ ds@1952: rc = sqlite3Select(pParse, pSelect, SRT_Subroutine, iInsertBlock,0,0,0,0); ds@1952: if( rc || pParse->nErr || db->mallocFailed ){ ds@1952: goto insert_cleanup; ds@1952: } ds@1952: ds@1952: iCleanup = sqlite3VdbeMakeLabel(v); ds@1952: sqlite3VdbeAddOp(v, OP_Goto, 0, iCleanup); ds@1952: assert( pSelect->pEList ); ds@1952: nColumn = pSelect->pEList->nExpr; ds@1952: ds@1952: /* Set useTempTable to TRUE if the result of the SELECT statement ds@1952: ** should be written into a temporary table. Set to FALSE if each ds@1952: ** row of the SELECT can be written directly into the result table. ds@1952: ** ds@1952: ** A temp table must be used if the table being updated is also one ds@1952: ** of the tables being read by the SELECT statement. Also use a ds@1952: ** temp table in the case of row triggers. ds@1952: */ ds@1952: if( triggers_exist || selectReadsTable(pSelect,pTab->pSchema,pTab->tnum) ){ ds@1952: useTempTable = 1; ds@1952: } ds@1952: ds@1952: if( useTempTable ){ ds@1952: /* Generate the subroutine that SELECT calls to process each row of ds@1952: ** the result. Store the result in a temporary table ds@1952: */ ds@1952: srcTab = pParse->nTab++; ds@1952: sqlite3VdbeResolveLabel(v, iInsertBlock); ds@1952: sqlite3VdbeAddOp(v, OP_MakeRecord, nColumn, 0); ds@1952: sqlite3VdbeAddOp(v, OP_NewRowid, srcTab, 0); ds@1952: sqlite3VdbeAddOp(v, OP_Pull, 1, 0); ds@1952: sqlite3VdbeAddOp(v, OP_Insert, srcTab, OPFLAG_APPEND); ds@1952: sqlite3VdbeAddOp(v, OP_Return, 0, 0); ds@1952: ds@1952: /* The following code runs first because the GOTO at the very top ds@1952: ** of the program jumps to it. Create the temporary table, then jump ds@1952: ** back up and execute the SELECT code above. ds@1952: */ ds@1952: sqlite3VdbeJumpHere(v, iInitCode); ds@1952: sqlite3VdbeAddOp(v, OP_OpenEphemeral, srcTab, 0); ds@1952: sqlite3VdbeAddOp(v, OP_SetNumColumns, srcTab, nColumn); ds@1952: sqlite3VdbeAddOp(v, OP_Goto, 0, iSelectLoop); ds@1952: sqlite3VdbeResolveLabel(v, iCleanup); ds@1952: }else{ ds@1952: sqlite3VdbeJumpHere(v, iInitCode); ds@1952: } ds@1952: }else{ ds@1952: /* This is the case if the data for the INSERT is coming from a VALUES ds@1952: ** clause ds@1952: */ ds@1952: NameContext sNC; ds@1952: memset(&sNC, 0, sizeof(sNC)); ds@1952: sNC.pParse = pParse; ds@1952: srcTab = -1; ds@1952: useTempTable = 0; ds@1952: nColumn = pList ? pList->nExpr : 0; ds@1952: for(i=0; ia[i].pExpr) ){ ds@1952: goto insert_cleanup; ds@1952: } ds@1952: } ds@1952: } ds@1952: ds@1952: /* Make sure the number of columns in the source data matches the number ds@1952: ** of columns to be inserted into the table. ds@1952: */ ds@1952: if( IsVirtual(pTab) ){ ds@1952: for(i=0; inCol; i++){ ds@1952: nHidden += (IsHiddenColumn(&pTab->aCol[i]) ? 1 : 0); ds@1952: } ds@1952: } ds@1952: if( pColumn==0 && nColumn && nColumn!=(pTab->nCol-nHidden) ){ ds@1952: sqlite3ErrorMsg(pParse, ds@1952: "table %S has %d columns but %d values were supplied", ds@1952: pTabList, 0, pTab->nCol, nColumn); ds@1952: goto insert_cleanup; ds@1952: } ds@1952: if( pColumn!=0 && nColumn!=pColumn->nId ){ ds@1952: sqlite3ErrorMsg(pParse, "%d values for %d columns", nColumn, pColumn->nId); ds@1952: goto insert_cleanup; ds@1952: } ds@1952: ds@1952: /* If the INSERT statement included an IDLIST term, then make sure ds@1952: ** all elements of the IDLIST really are columns of the table and ds@1952: ** remember the column indices. ds@1952: ** ds@1952: ** If the table has an INTEGER PRIMARY KEY column and that column ds@1952: ** is named in the IDLIST, then record in the keyColumn variable ds@1952: ** the index into IDLIST of the primary key column. keyColumn is ds@1952: ** the index of the primary key as it appears in IDLIST, not as ds@1952: ** is appears in the original table. (The index of the primary ds@1952: ** key in the original table is pTab->iPKey.) ds@1952: */ ds@1952: if( pColumn ){ ds@1952: for(i=0; inId; i++){ ds@1952: pColumn->a[i].idx = -1; ds@1952: } ds@1952: for(i=0; inId; i++){ ds@1952: for(j=0; jnCol; j++){ ds@1952: if( sqlite3StrICmp(pColumn->a[i].zName, pTab->aCol[j].zName)==0 ){ ds@1952: pColumn->a[i].idx = j; ds@1952: if( j==pTab->iPKey ){ ds@1952: keyColumn = i; ds@1952: } ds@1952: break; ds@1952: } ds@1952: } ds@1952: if( j>=pTab->nCol ){ ds@1952: if( sqlite3IsRowid(pColumn->a[i].zName) ){ ds@1952: keyColumn = i; ds@1952: }else{ ds@1952: sqlite3ErrorMsg(pParse, "table %S has no column named %s", ds@1952: pTabList, 0, pColumn->a[i].zName); ds@1952: pParse->nErr++; ds@1952: goto insert_cleanup; ds@1952: } ds@1952: } ds@1952: } ds@1952: } ds@1952: ds@1952: /* If there is no IDLIST term but the table has an integer primary ds@1952: ** key, the set the keyColumn variable to the primary key column index ds@1952: ** in the original table definition. ds@1952: */ ds@1952: if( pColumn==0 && nColumn>0 ){ ds@1952: keyColumn = pTab->iPKey; ds@1952: } ds@1952: ds@1952: /* Open the temp table for FOR EACH ROW triggers ds@1952: */ ds@1952: if( triggers_exist ){ ds@1952: sqlite3VdbeAddOp(v, OP_OpenPseudo, newIdx, 0); ds@1952: sqlite3VdbeAddOp(v, OP_SetNumColumns, newIdx, pTab->nCol); ds@1952: } ds@1952: ds@1952: /* Initialize the count of rows to be inserted ds@1952: */ ds@1952: if( db->flags & SQLITE_CountRows ){ ds@1952: iCntMem = pParse->nMem++; ds@1952: sqlite3VdbeAddOp(v, OP_MemInt, 0, iCntMem); ds@1952: } ds@1952: ds@1952: /* Open tables and indices if there are no row triggers */ ds@1952: if( !triggers_exist ){ ds@1952: base = pParse->nTab; ds@1952: sqlite3OpenTableAndIndices(pParse, pTab, base, OP_OpenWrite); ds@1952: } ds@1952: ds@1952: /* If the data source is a temporary table, then we have to create ds@1952: ** a loop because there might be multiple rows of data. If the data ds@1952: ** source is a subroutine call from the SELECT statement, then we need ds@1952: ** to launch the SELECT statement processing. ds@1952: */ ds@1952: if( useTempTable ){ ds@1952: iBreak = sqlite3VdbeMakeLabel(v); ds@1952: sqlite3VdbeAddOp(v, OP_Rewind, srcTab, iBreak); ds@1952: iCont = sqlite3VdbeCurrentAddr(v); ds@1952: }else if( pSelect ){ ds@1952: sqlite3VdbeAddOp(v, OP_Goto, 0, iSelectLoop); ds@1952: sqlite3VdbeResolveLabel(v, iInsertBlock); ds@1952: } ds@1952: ds@1952: /* Run the BEFORE and INSTEAD OF triggers, if there are any ds@1952: */ ds@1952: endOfLoop = sqlite3VdbeMakeLabel(v); ds@1952: if( triggers_exist & TRIGGER_BEFORE ){ ds@1952: ds@1952: /* build the NEW.* reference row. Note that if there is an INTEGER ds@1952: ** PRIMARY KEY into which a NULL is being inserted, that NULL will be ds@1952: ** translated into a unique ID for the row. But on a BEFORE trigger, ds@1952: ** we do not know what the unique ID will be (because the insert has ds@1952: ** not happened yet) so we substitute a rowid of -1 ds@1952: */ ds@1952: if( keyColumn<0 ){ ds@1952: sqlite3VdbeAddOp(v, OP_Integer, -1, 0); ds@1952: }else if( useTempTable ){ ds@1952: sqlite3VdbeAddOp(v, OP_Column, srcTab, keyColumn); ds@1952: }else{ ds@1952: assert( pSelect==0 ); /* Otherwise useTempTable is true */ ds@1952: sqlite3ExprCode(pParse, pList->a[keyColumn].pExpr); ds@1952: sqlite3VdbeAddOp(v, OP_NotNull, -1, sqlite3VdbeCurrentAddr(v)+3); ds@1952: sqlite3VdbeAddOp(v, OP_Pop, 1, 0); ds@1952: sqlite3VdbeAddOp(v, OP_Integer, -1, 0); ds@1952: sqlite3VdbeAddOp(v, OP_MustBeInt, 0, 0); ds@1952: } ds@1952: ds@1952: /* Cannot have triggers on a virtual table. If it were possible, ds@1952: ** this block would have to account for hidden column. ds@1952: */ ds@1952: assert(!IsVirtual(pTab)); ds@1952: ds@1952: /* Create the new column data ds@1952: */ ds@1952: for(i=0; inCol; i++){ ds@1952: if( pColumn==0 ){ ds@1952: j = i; ds@1952: }else{ ds@1952: for(j=0; jnId; j++){ ds@1952: if( pColumn->a[j].idx==i ) break; ds@1952: } ds@1952: } ds@1952: if( pColumn && j>=pColumn->nId ){ ds@1952: sqlite3ExprCode(pParse, pTab->aCol[i].pDflt); ds@1952: }else if( useTempTable ){ ds@1952: sqlite3VdbeAddOp(v, OP_Column, srcTab, j); ds@1952: }else{ ds@1952: assert( pSelect==0 ); /* Otherwise useTempTable is true */ ds@1952: sqlite3ExprCodeAndCache(pParse, pList->a[j].pExpr); ds@1952: } ds@1952: } ds@1952: sqlite3VdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0); ds@1952: ds@1952: /* If this is an INSERT on a view with an INSTEAD OF INSERT trigger, ds@1952: ** do not attempt any conversions before assembling the record. ds@1952: ** If this is a real table, attempt conversions as required by the ds@1952: ** table column affinities. ds@1952: */ ds@1952: if( !isView ){ ds@1952: sqlite3TableAffinityStr(v, pTab); ds@1952: } ds@1952: sqlite3VdbeAddOp(v, OP_Insert, newIdx, 0); ds@1952: ds@1952: /* Fire BEFORE or INSTEAD OF triggers */ ds@1952: if( sqlite3CodeRowTrigger(pParse, TK_INSERT, 0, TRIGGER_BEFORE, pTab, ds@1952: newIdx, -1, onError, endOfLoop) ){ ds@1952: goto insert_cleanup; ds@1952: } ds@1952: } ds@1952: ds@1952: /* If any triggers exists, the opening of tables and indices is deferred ds@1952: ** until now. ds@1952: */ ds@1952: if( triggers_exist && !isView ){ ds@1952: base = pParse->nTab; ds@1952: sqlite3OpenTableAndIndices(pParse, pTab, base, OP_OpenWrite); ds@1952: } ds@1952: ds@1952: /* Push the record number for the new entry onto the stack. The ds@1952: ** record number is a randomly generate integer created by NewRowid ds@1952: ** except when the table has an INTEGER PRIMARY KEY column, in which ds@1952: ** case the record number is the same as that column. ds@1952: */ ds@1952: if( !isView ){ ds@1952: if( IsVirtual(pTab) ){ ds@1952: /* The row that the VUpdate opcode will delete: none */ ds@1952: sqlite3VdbeAddOp(v, OP_Null, 0, 0); ds@1952: } ds@1952: if( keyColumn>=0 ){ ds@1952: if( useTempTable ){ ds@1952: sqlite3VdbeAddOp(v, OP_Column, srcTab, keyColumn); ds@1952: }else if( pSelect ){ ds@1952: sqlite3VdbeAddOp(v, OP_Dup, nColumn - keyColumn - 1, 1); ds@1952: }else{ ds@1952: VdbeOp *pOp; ds@1952: sqlite3ExprCode(pParse, pList->a[keyColumn].pExpr); ds@1952: pOp = sqlite3VdbeGetOp(v, sqlite3VdbeCurrentAddr(v) - 1); ds@1952: if( pOp && pOp->opcode==OP_Null ){ ds@1952: appendFlag = 1; ds@1952: pOp->opcode = OP_NewRowid; ds@1952: pOp->p1 = base; ds@1952: pOp->p2 = counterMem; ds@1952: } ds@1952: } ds@1952: /* If the PRIMARY KEY expression is NULL, then use OP_NewRowid ds@1952: ** to generate a unique primary key value. ds@1952: */ ds@1952: if( !appendFlag ){ ds@1952: sqlite3VdbeAddOp(v, OP_NotNull, -1, sqlite3VdbeCurrentAddr(v)+3); ds@1952: sqlite3VdbeAddOp(v, OP_Pop, 1, 0); ds@1952: sqlite3VdbeAddOp(v, OP_NewRowid, base, counterMem); ds@1952: sqlite3VdbeAddOp(v, OP_MustBeInt, 0, 0); ds@1952: } ds@1952: }else if( IsVirtual(pTab) ){ ds@1952: sqlite3VdbeAddOp(v, OP_Null, 0, 0); ds@1952: }else{ ds@1952: sqlite3VdbeAddOp(v, OP_NewRowid, base, counterMem); ds@1952: appendFlag = 1; ds@1952: } ds@1952: autoIncStep(pParse, counterMem); ds@1952: ds@1952: /* Push onto the stack, data for all columns of the new entry, beginning ds@1952: ** with the first column. ds@1952: */ ds@1952: nHidden = 0; ds@1952: for(i=0; inCol; i++){ ds@1952: if( i==pTab->iPKey ){ ds@1952: /* The value of the INTEGER PRIMARY KEY column is always a NULL. ds@1952: ** Whenever this column is read, the record number will be substituted ds@1952: ** in its place. So will fill this column with a NULL to avoid ds@1952: ** taking up data space with information that will never be used. */ ds@1952: sqlite3VdbeAddOp(v, OP_Null, 0, 0); ds@1952: continue; ds@1952: } ds@1952: if( pColumn==0 ){ ds@1952: if( IsHiddenColumn(&pTab->aCol[i]) ){ ds@1952: assert( IsVirtual(pTab) ); ds@1952: j = -1; ds@1952: nHidden++; ds@1952: }else{ ds@1952: j = i - nHidden; ds@1952: } ds@1952: }else{ ds@1952: for(j=0; jnId; j++){ ds@1952: if( pColumn->a[j].idx==i ) break; ds@1952: } ds@1952: } ds@1952: if( j<0 || nColumn==0 || (pColumn && j>=pColumn->nId) ){ ds@1952: sqlite3ExprCode(pParse, pTab->aCol[i].pDflt); ds@1952: }else if( useTempTable ){ ds@1952: sqlite3VdbeAddOp(v, OP_Column, srcTab, j); ds@1952: }else if( pSelect ){ ds@1952: sqlite3VdbeAddOp(v, OP_Dup, i+nColumn-j+IsVirtual(pTab), 1); ds@1952: }else{ ds@1952: sqlite3ExprCode(pParse, pList->a[j].pExpr); ds@1952: } ds@1952: } ds@1952: ds@1952: /* Generate code to check constraints and generate index keys and ds@1952: ** do the insertion. ds@1952: */ ds@1952: #ifndef SQLITE_OMIT_VIRTUALTABLE ds@1952: if( IsVirtual(pTab) ){ ds@1952: pParse->pVirtualLock = pTab; ds@1952: sqlite3VdbeOp3(v, OP_VUpdate, 1, pTab->nCol+2, ds@1952: (const char*)pTab->pVtab, P3_VTAB); ds@1952: }else ds@1952: #endif ds@1952: { ds@1952: sqlite3GenerateConstraintChecks(pParse, pTab, base, 0, keyColumn>=0, ds@1952: 0, onError, endOfLoop); ds@1952: sqlite3CompleteInsertion(pParse, pTab, base, 0,0,0, ds@1952: (triggers_exist & TRIGGER_AFTER)!=0 ? newIdx : -1, ds@1952: appendFlag); ds@1952: } ds@1952: } ds@1952: ds@1952: /* Update the count of rows that are inserted ds@1952: */ ds@1952: if( (db->flags & SQLITE_CountRows)!=0 ){ ds@1952: sqlite3VdbeAddOp(v, OP_MemIncr, 1, iCntMem); ds@1952: } ds@1952: ds@1952: if( triggers_exist ){ ds@1952: /* Close all tables opened */ ds@1952: if( !isView ){ ds@1952: sqlite3VdbeAddOp(v, OP_Close, base, 0); ds@1952: for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){ ds@1952: sqlite3VdbeAddOp(v, OP_Close, idx+base, 0); ds@1952: } ds@1952: } ds@1952: ds@1952: /* Code AFTER triggers */ ds@1952: if( sqlite3CodeRowTrigger(pParse, TK_INSERT, 0, TRIGGER_AFTER, pTab, ds@1952: newIdx, -1, onError, endOfLoop) ){ ds@1952: goto insert_cleanup; ds@1952: } ds@1952: } ds@1952: ds@1952: /* The bottom of the loop, if the data source is a SELECT statement ds@1952: */ ds@1952: sqlite3VdbeResolveLabel(v, endOfLoop); ds@1952: if( useTempTable ){ ds@1952: sqlite3VdbeAddOp(v, OP_Next, srcTab, iCont); ds@1952: sqlite3VdbeResolveLabel(v, iBreak); ds@1952: sqlite3VdbeAddOp(v, OP_Close, srcTab, 0); ds@1952: }else if( pSelect ){ ds@1952: sqlite3VdbeAddOp(v, OP_Pop, nColumn, 0); ds@1952: sqlite3VdbeAddOp(v, OP_Return, 0, 0); ds@1952: sqlite3VdbeResolveLabel(v, iCleanup); ds@1952: } ds@1952: ds@1952: if( !triggers_exist && !IsVirtual(pTab) ){ ds@1952: /* Close all tables opened */ ds@1952: sqlite3VdbeAddOp(v, OP_Close, base, 0); ds@1952: for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){ ds@1952: sqlite3VdbeAddOp(v, OP_Close, idx+base, 0); ds@1952: } ds@1952: } ds@1952: ds@1952: /* Update the sqlite_sequence table by storing the content of the ds@1952: ** counter value in memory counterMem back into the sqlite_sequence ds@1952: ** table. ds@1952: */ ds@1952: autoIncEnd(pParse, iDb, pTab, counterMem); ds@1952: ds@1952: /* ds@1952: ** Return the number of rows inserted. If this routine is ds@1952: ** generating code because of a call to sqlite3NestedParse(), do not ds@1952: ** invoke the callback function. ds@1952: */ ds@1952: if( db->flags & SQLITE_CountRows && pParse->nested==0 && !pParse->trigStack ){ ds@1952: sqlite3VdbeAddOp(v, OP_MemLoad, iCntMem, 0); ds@1952: sqlite3VdbeAddOp(v, OP_Callback, 1, 0); ds@1952: sqlite3VdbeSetNumCols(v, 1); ds@1952: sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows inserted", P3_STATIC); ds@1952: } ds@1952: ds@1952: insert_cleanup: ds@1952: sqlite3SrcListDelete(pTabList); ds@1952: sqlite3ExprListDelete(pList); ds@1952: sqlite3SelectDelete(pSelect); ds@1952: sqlite3IdListDelete(pColumn); ds@1952: } ds@1952: ds@1952: /* ds@1952: ** Generate code to do a constraint check prior to an INSERT or an UPDATE. ds@1952: ** ds@1952: ** When this routine is called, the stack contains (from bottom to top) ds@1952: ** the following values: ds@1952: ** ds@1952: ** 1. The rowid of the row to be updated before the update. This ds@1952: ** value is omitted unless we are doing an UPDATE that involves a ds@1952: ** change to the record number. ds@1952: ** ds@1952: ** 2. The rowid of the row after the update. ds@1952: ** ds@1952: ** 3. The data in the first column of the entry after the update. ds@1952: ** ds@1952: ** i. Data from middle columns... ds@1952: ** ds@1952: ** N. The data in the last column of the entry after the update. ds@1952: ** ds@1952: ** The old rowid shown as entry (1) above is omitted unless both isUpdate ds@1952: ** and rowidChng are 1. isUpdate is true for UPDATEs and false for ds@1952: ** INSERTs and rowidChng is true if the record number is being changed. ds@1952: ** ds@1952: ** The code generated by this routine pushes additional entries onto ds@1952: ** the stack which are the keys for new index entries for the new record. ds@1952: ** The order of index keys is the same as the order of the indices on ds@1952: ** the pTable->pIndex list. A key is only created for index i if ds@1952: ** aIdxUsed!=0 and aIdxUsed[i]!=0. ds@1952: ** ds@1952: ** This routine also generates code to check constraints. NOT NULL, ds@1952: ** CHECK, and UNIQUE constraints are all checked. If a constraint fails, ds@1952: ** then the appropriate action is performed. There are five possible ds@1952: ** actions: ROLLBACK, ABORT, FAIL, REPLACE, and IGNORE. ds@1952: ** ds@1952: ** Constraint type Action What Happens ds@1952: ** --------------- ---------- ---------------------------------------- ds@1952: ** any ROLLBACK The current transaction is rolled back and ds@1952: ** sqlite3_exec() returns immediately with a ds@1952: ** return code of SQLITE_CONSTRAINT. ds@1952: ** ds@1952: ** any ABORT Back out changes from the current command ds@1952: ** only (do not do a complete rollback) then ds@1952: ** cause sqlite3_exec() to return immediately ds@1952: ** with SQLITE_CONSTRAINT. ds@1952: ** ds@1952: ** any FAIL Sqlite_exec() returns immediately with a ds@1952: ** return code of SQLITE_CONSTRAINT. The ds@1952: ** transaction is not rolled back and any ds@1952: ** prior changes are retained. ds@1952: ** ds@1952: ** any IGNORE The record number and data is popped from ds@1952: ** the stack and there is an immediate jump ds@1952: ** to label ignoreDest. ds@1952: ** ds@1952: ** NOT NULL REPLACE The NULL value is replace by the default ds@1952: ** value for that column. If the default value ds@1952: ** is NULL, the action is the same as ABORT. ds@1952: ** ds@1952: ** UNIQUE REPLACE The other row that conflicts with the row ds@1952: ** being inserted is removed. ds@1952: ** ds@1952: ** CHECK REPLACE Illegal. The results in an exception. ds@1952: ** ds@1952: ** Which action to take is determined by the overrideError parameter. ds@1952: ** Or if overrideError==OE_Default, then the pParse->onError parameter ds@1952: ** is used. Or if pParse->onError==OE_Default then the onError value ds@1952: ** for the constraint is used. ds@1952: ** ds@1952: ** The calling routine must open a read/write cursor for pTab with ds@1952: ** cursor number "base". All indices of pTab must also have open ds@1952: ** read/write cursors with cursor number base+i for the i-th cursor. ds@1952: ** Except, if there is no possibility of a REPLACE action then ds@1952: ** cursors do not need to be open for indices where aIdxUsed[i]==0. ds@1952: ** ds@1952: ** If the isUpdate flag is true, it means that the "base" cursor is ds@1952: ** initially pointing to an entry that is being updated. The isUpdate ds@1952: ** flag causes extra code to be generated so that the "base" cursor ds@1952: ** is still pointing at the same entry after the routine returns. ds@1952: ** Without the isUpdate flag, the "base" cursor might be moved. ds@1952: */ ds@1952: void sqlite3GenerateConstraintChecks( ds@1952: Parse *pParse, /* The parser context */ ds@1952: Table *pTab, /* the table into which we are inserting */ ds@1952: int base, /* Index of a read/write cursor pointing at pTab */ ds@1952: char *aIdxUsed, /* Which indices are used. NULL means all are used */ ds@1952: int rowidChng, /* True if the record number will change */ ds@1952: int isUpdate, /* True for UPDATE, False for INSERT */ ds@1952: int overrideError, /* Override onError to this if not OE_Default */ ds@1952: int ignoreDest /* Jump to this label on an OE_Ignore resolution */ ds@1952: ){ ds@1952: int i; ds@1952: Vdbe *v; ds@1952: int nCol; ds@1952: int onError; ds@1952: int addr; ds@1952: int extra; ds@1952: int iCur; ds@1952: Index *pIdx; ds@1952: int seenReplace = 0; ds@1952: int jumpInst1=0, jumpInst2; ds@1952: int hasTwoRowids = (isUpdate && rowidChng); ds@1952: ds@1952: v = sqlite3GetVdbe(pParse); ds@1952: assert( v!=0 ); ds@1952: assert( pTab->pSelect==0 ); /* This table is not a VIEW */ ds@1952: nCol = pTab->nCol; ds@1952: ds@1952: /* Test all NOT NULL constraints. ds@1952: */ ds@1952: for(i=0; iiPKey ){ ds@1952: continue; ds@1952: } ds@1952: onError = pTab->aCol[i].notNull; ds@1952: if( onError==OE_None ) continue; ds@1952: if( overrideError!=OE_Default ){ ds@1952: onError = overrideError; ds@1952: }else if( onError==OE_Default ){ ds@1952: onError = OE_Abort; ds@1952: } ds@1952: if( onError==OE_Replace && pTab->aCol[i].pDflt==0 ){ ds@1952: onError = OE_Abort; ds@1952: } ds@1952: sqlite3VdbeAddOp(v, OP_Dup, nCol-1-i, 1); ds@1952: addr = sqlite3VdbeAddOp(v, OP_NotNull, 1, 0); ds@1952: assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail ds@1952: || onError==OE_Ignore || onError==OE_Replace ); ds@1952: switch( onError ){ ds@1952: case OE_Rollback: ds@1952: case OE_Abort: ds@1952: case OE_Fail: { ds@1952: char *zMsg = 0; ds@1952: sqlite3VdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, onError); ds@1952: sqlite3SetString(&zMsg, pTab->zName, ".", pTab->aCol[i].zName, ds@1952: " may not be NULL", (char*)0); ds@1952: sqlite3VdbeChangeP3(v, -1, zMsg, P3_DYNAMIC); ds@1952: break; ds@1952: } ds@1952: case OE_Ignore: { ds@1952: sqlite3VdbeAddOp(v, OP_Pop, nCol+1+hasTwoRowids, 0); ds@1952: sqlite3VdbeAddOp(v, OP_Goto, 0, ignoreDest); ds@1952: break; ds@1952: } ds@1952: case OE_Replace: { ds@1952: sqlite3ExprCode(pParse, pTab->aCol[i].pDflt); ds@1952: sqlite3VdbeAddOp(v, OP_Push, nCol-i, 0); ds@1952: break; ds@1952: } ds@1952: } ds@1952: sqlite3VdbeJumpHere(v, addr); ds@1952: } ds@1952: ds@1952: /* Test all CHECK constraints ds@1952: */ ds@1952: #ifndef SQLITE_OMIT_CHECK ds@1952: if( pTab->pCheck && (pParse->db->flags & SQLITE_IgnoreChecks)==0 ){ ds@1952: int allOk = sqlite3VdbeMakeLabel(v); ds@1952: assert( pParse->ckOffset==0 ); ds@1952: pParse->ckOffset = nCol; ds@1952: sqlite3ExprIfTrue(pParse, pTab->pCheck, allOk, 1); ds@1952: assert( pParse->ckOffset==nCol ); ds@1952: pParse->ckOffset = 0; ds@1952: onError = overrideError!=OE_Default ? overrideError : OE_Abort; ds@1952: if( onError==OE_Ignore ){ ds@1952: sqlite3VdbeAddOp(v, OP_Pop, nCol+1+hasTwoRowids, 0); ds@1952: sqlite3VdbeAddOp(v, OP_Goto, 0, ignoreDest); ds@1952: }else{ ds@1952: sqlite3VdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, onError); ds@1952: } ds@1952: sqlite3VdbeResolveLabel(v, allOk); ds@1952: } ds@1952: #endif /* !defined(SQLITE_OMIT_CHECK) */ ds@1952: ds@1952: /* If we have an INTEGER PRIMARY KEY, make sure the primary key ds@1952: ** of the new record does not previously exist. Except, if this ds@1952: ** is an UPDATE and the primary key is not changing, that is OK. ds@1952: */ ds@1952: if( rowidChng ){ ds@1952: onError = pTab->keyConf; ds@1952: if( overrideError!=OE_Default ){ ds@1952: onError = overrideError; ds@1952: }else if( onError==OE_Default ){ ds@1952: onError = OE_Abort; ds@1952: } ds@1952: ds@1952: if( isUpdate ){ ds@1952: sqlite3VdbeAddOp(v, OP_Dup, nCol+1, 1); ds@1952: sqlite3VdbeAddOp(v, OP_Dup, nCol+1, 1); ds@1952: jumpInst1 = sqlite3VdbeAddOp(v, OP_Eq, 0, 0); ds@1952: } ds@1952: sqlite3VdbeAddOp(v, OP_Dup, nCol, 1); ds@1952: jumpInst2 = sqlite3VdbeAddOp(v, OP_NotExists, base, 0); ds@1952: switch( onError ){ ds@1952: default: { ds@1952: onError = OE_Abort; ds@1952: /* Fall thru into the next case */ ds@1952: } ds@1952: case OE_Rollback: ds@1952: case OE_Abort: ds@1952: case OE_Fail: { ds@1952: sqlite3VdbeOp3(v, OP_Halt, SQLITE_CONSTRAINT, onError, ds@1952: "PRIMARY KEY must be unique", P3_STATIC); ds@1952: break; ds@1952: } ds@1952: case OE_Replace: { ds@1952: sqlite3GenerateRowIndexDelete(v, pTab, base, 0); ds@1952: if( isUpdate ){ ds@1952: sqlite3VdbeAddOp(v, OP_Dup, nCol+hasTwoRowids, 1); ds@1952: sqlite3VdbeAddOp(v, OP_MoveGe, base, 0); ds@1952: } ds@1952: seenReplace = 1; ds@1952: break; ds@1952: } ds@1952: case OE_Ignore: { ds@1952: assert( seenReplace==0 ); ds@1952: sqlite3VdbeAddOp(v, OP_Pop, nCol+1+hasTwoRowids, 0); ds@1952: sqlite3VdbeAddOp(v, OP_Goto, 0, ignoreDest); ds@1952: break; ds@1952: } ds@1952: } ds@1952: sqlite3VdbeJumpHere(v, jumpInst2); ds@1952: if( isUpdate ){ ds@1952: sqlite3VdbeJumpHere(v, jumpInst1); ds@1952: sqlite3VdbeAddOp(v, OP_Dup, nCol+1, 1); ds@1952: sqlite3VdbeAddOp(v, OP_MoveGe, base, 0); ds@1952: } ds@1952: } ds@1952: ds@1952: /* Test all UNIQUE constraints by creating entries for each UNIQUE ds@1952: ** index and making sure that duplicate entries do not already exist. ds@1952: ** Add the new records to the indices as we go. ds@1952: */ ds@1952: extra = -1; ds@1952: for(iCur=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, iCur++){ ds@1952: if( aIdxUsed && aIdxUsed[iCur]==0 ) continue; /* Skip unused indices */ ds@1952: extra++; ds@1952: ds@1952: /* Create a key for accessing the index entry */ ds@1952: sqlite3VdbeAddOp(v, OP_Dup, nCol+extra, 1); ds@1952: for(i=0; inColumn; i++){ ds@1952: int idx = pIdx->aiColumn[i]; ds@1952: if( idx==pTab->iPKey ){ ds@1952: sqlite3VdbeAddOp(v, OP_Dup, i+extra+nCol+1, 1); ds@1952: }else{ ds@1952: sqlite3VdbeAddOp(v, OP_Dup, i+extra+nCol-idx, 1); ds@1952: } ds@1952: } ds@1952: jumpInst1 = sqlite3VdbeAddOp(v, OP_MakeIdxRec, pIdx->nColumn, 0); ds@1952: sqlite3IndexAffinityStr(v, pIdx); ds@1952: ds@1952: /* Find out what action to take in case there is an indexing conflict */ ds@1952: onError = pIdx->onError; ds@1952: if( onError==OE_None ) continue; /* pIdx is not a UNIQUE index */ ds@1952: if( overrideError!=OE_Default ){ ds@1952: onError = overrideError; ds@1952: }else if( onError==OE_Default ){ ds@1952: onError = OE_Abort; ds@1952: } ds@1952: if( seenReplace ){ ds@1952: if( onError==OE_Ignore ) onError = OE_Replace; ds@1952: else if( onError==OE_Fail ) onError = OE_Abort; ds@1952: } ds@1952: ds@1952: ds@1952: /* Check to see if the new index entry will be unique */ ds@1952: sqlite3VdbeAddOp(v, OP_Dup, extra+nCol+1+hasTwoRowids, 1); ds@1952: jumpInst2 = sqlite3VdbeAddOp(v, OP_IsUnique, base+iCur+1, 0); ds@1952: ds@1952: /* Generate code that executes if the new index entry is not unique */ ds@1952: assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail ds@1952: || onError==OE_Ignore || onError==OE_Replace ); ds@1952: switch( onError ){ ds@1952: case OE_Rollback: ds@1952: case OE_Abort: ds@1952: case OE_Fail: { ds@1952: int j, n1, n2; ds@1952: char zErrMsg[200]; ds@1952: sqlite3_snprintf(sizeof(zErrMsg), zErrMsg, ds@1952: pIdx->nColumn>1 ? "columns " : "column "); ds@1952: n1 = strlen(zErrMsg); ds@1952: for(j=0; jnColumn && n1aCol[pIdx->aiColumn[j]].zName; ds@1952: n2 = strlen(zCol); ds@1952: if( j>0 ){ ds@1952: sqlite3_snprintf(sizeof(zErrMsg)-n1, &zErrMsg[n1], ", "); ds@1952: n1 += 2; ds@1952: } ds@1952: if( n1+n2>sizeof(zErrMsg)-30 ){ ds@1952: sqlite3_snprintf(sizeof(zErrMsg)-n1, &zErrMsg[n1], "..."); ds@1952: n1 += 3; ds@1952: break; ds@1952: }else{ ds@1952: sqlite3_snprintf(sizeof(zErrMsg)-n1, &zErrMsg[n1], "%s", zCol); ds@1952: n1 += n2; ds@1952: } ds@1952: } ds@1952: sqlite3_snprintf(sizeof(zErrMsg)-n1, &zErrMsg[n1], ds@1952: pIdx->nColumn>1 ? " are not unique" : " is not unique"); ds@1952: sqlite3VdbeOp3(v, OP_Halt, SQLITE_CONSTRAINT, onError, zErrMsg, 0); ds@1952: break; ds@1952: } ds@1952: case OE_Ignore: { ds@1952: assert( seenReplace==0 ); ds@1952: sqlite3VdbeAddOp(v, OP_Pop, nCol+extra+3+hasTwoRowids, 0); ds@1952: sqlite3VdbeAddOp(v, OP_Goto, 0, ignoreDest); ds@1952: break; ds@1952: } ds@1952: case OE_Replace: { ds@1952: sqlite3GenerateRowDelete(pParse->db, v, pTab, base, 0); ds@1952: if( isUpdate ){ ds@1952: sqlite3VdbeAddOp(v, OP_Dup, nCol+extra+1+hasTwoRowids, 1); ds@1952: sqlite3VdbeAddOp(v, OP_MoveGe, base, 0); ds@1952: } ds@1952: seenReplace = 1; ds@1952: break; ds@1952: } ds@1952: } ds@1952: #if NULL_DISTINCT_FOR_UNIQUE ds@1952: sqlite3VdbeJumpHere(v, jumpInst1); ds@1952: #endif ds@1952: sqlite3VdbeJumpHere(v, jumpInst2); ds@1952: } ds@1952: } ds@1952: ds@1952: /* ds@1952: ** This routine generates code to finish the INSERT or UPDATE operation ds@1952: ** that was started by a prior call to sqlite3GenerateConstraintChecks. ds@1952: ** The stack must contain keys for all active indices followed by data ds@1952: ** and the rowid for the new entry. This routine creates the new ds@1952: ** entries in all indices and in the main table. ds@1952: ** ds@1952: ** The arguments to this routine should be the same as the first six ds@1952: ** arguments to sqlite3GenerateConstraintChecks. ds@1952: */ ds@1952: void sqlite3CompleteInsertion( ds@1952: Parse *pParse, /* The parser context */ ds@1952: Table *pTab, /* the table into which we are inserting */ ds@1952: int base, /* Index of a read/write cursor pointing at pTab */ ds@1952: char *aIdxUsed, /* Which indices are used. NULL means all are used */ ds@1952: int rowidChng, /* True if the record number will change */ ds@1952: int isUpdate, /* True for UPDATE, False for INSERT */ ds@1952: int newIdx, /* Index of NEW table for triggers. -1 if none */ ds@1952: int appendBias /* True if this is likely to be an append */ ds@1952: ){ ds@1952: int i; ds@1952: Vdbe *v; ds@1952: int nIdx; ds@1952: Index *pIdx; ds@1952: int pik_flags; ds@1952: ds@1952: v = sqlite3GetVdbe(pParse); ds@1952: assert( v!=0 ); ds@1952: assert( pTab->pSelect==0 ); /* This table is not a VIEW */ ds@1952: for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){} ds@1952: for(i=nIdx-1; i>=0; i--){ ds@1952: if( aIdxUsed && aIdxUsed[i]==0 ) continue; ds@1952: sqlite3VdbeAddOp(v, OP_IdxInsert, base+i+1, 0); ds@1952: } ds@1952: sqlite3VdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0); ds@1952: sqlite3TableAffinityStr(v, pTab); ds@1952: #ifndef SQLITE_OMIT_TRIGGER ds@1952: if( newIdx>=0 ){ ds@1952: sqlite3VdbeAddOp(v, OP_Dup, 1, 0); ds@1952: sqlite3VdbeAddOp(v, OP_Dup, 1, 0); ds@1952: sqlite3VdbeAddOp(v, OP_Insert, newIdx, 0); ds@1952: } ds@1952: #endif ds@1952: if( pParse->nested ){ ds@1952: pik_flags = 0; ds@1952: }else{ ds@1952: pik_flags = OPFLAG_NCHANGE; ds@1952: pik_flags |= (isUpdate?OPFLAG_ISUPDATE:OPFLAG_LASTROWID); ds@1952: } ds@1952: if( appendBias ){ ds@1952: pik_flags |= OPFLAG_APPEND; ds@1952: } ds@1952: sqlite3VdbeAddOp(v, OP_Insert, base, pik_flags); ds@1952: if( !pParse->nested ){ ds@1952: sqlite3VdbeChangeP3(v, -1, pTab->zName, P3_STATIC); ds@1952: } ds@1952: ds@1952: if( isUpdate && rowidChng ){ ds@1952: sqlite3VdbeAddOp(v, OP_Pop, 1, 0); ds@1952: } ds@1952: } ds@1952: ds@1952: /* ds@1952: ** Generate code that will open cursors for a table and for all ds@1952: ** indices of that table. The "base" parameter is the cursor number used ds@1952: ** for the table. Indices are opened on subsequent cursors. ds@1952: */ ds@1952: void sqlite3OpenTableAndIndices( ds@1952: Parse *pParse, /* Parsing context */ ds@1952: Table *pTab, /* Table to be opened */ ds@1952: int base, /* Cursor number assigned to the table */ ds@1952: int op /* OP_OpenRead or OP_OpenWrite */ ds@1952: ){ ds@1952: int i; ds@1952: int iDb; ds@1952: Index *pIdx; ds@1952: Vdbe *v; ds@1952: ds@1952: if( IsVirtual(pTab) ) return; ds@1952: iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema); ds@1952: v = sqlite3GetVdbe(pParse); ds@1952: assert( v!=0 ); ds@1952: sqlite3OpenTable(pParse, base, iDb, pTab, op); ds@1952: for(i=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){ ds@1952: KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx); ds@1952: assert( pIdx->pSchema==pTab->pSchema ); ds@1952: sqlite3VdbeAddOp(v, OP_Integer, iDb, 0); ds@1952: VdbeComment((v, "# %s", pIdx->zName)); ds@1952: sqlite3VdbeOp3(v, op, i+base, pIdx->tnum, (char*)pKey, P3_KEYINFO_HANDOFF); ds@1952: } ds@1952: if( pParse->nTab<=base+i ){ ds@1952: pParse->nTab = base+i; ds@1952: } ds@1952: } ds@1952: ds@1952: ds@1952: #ifdef SQLITE_TEST ds@1952: /* ds@1952: ** The following global variable is incremented whenever the ds@1952: ** transfer optimization is used. This is used for testing ds@1952: ** purposes only - to make sure the transfer optimization really ds@1952: ** is happening when it is suppose to. ds@1952: */ ds@1952: int sqlite3_xferopt_count; ds@1952: #endif /* SQLITE_TEST */ ds@1952: ds@1952: ds@1952: #ifndef SQLITE_OMIT_XFER_OPT ds@1952: /* ds@1952: ** Check to collation names to see if they are compatible. ds@1952: */ ds@1952: static int xferCompatibleCollation(const char *z1, const char *z2){ ds@1952: if( z1==0 ){ ds@1952: return z2==0; ds@1952: } ds@1952: if( z2==0 ){ ds@1952: return 0; ds@1952: } ds@1952: return sqlite3StrICmp(z1, z2)==0; ds@1952: } ds@1952: ds@1952: ds@1952: /* ds@1952: ** Check to see if index pSrc is compatible as a source of data ds@1952: ** for index pDest in an insert transfer optimization. The rules ds@1952: ** for a compatible index: ds@1952: ** ds@1952: ** * The index is over the same set of columns ds@1952: ** * The same DESC and ASC markings occurs on all columns ds@1952: ** * The same onError processing (OE_Abort, OE_Ignore, etc) ds@1952: ** * The same collating sequence on each column ds@1952: */ ds@1952: static int xferCompatibleIndex(Index *pDest, Index *pSrc){ ds@1952: int i; ds@1952: assert( pDest && pSrc ); ds@1952: assert( pDest->pTable!=pSrc->pTable ); ds@1952: if( pDest->nColumn!=pSrc->nColumn ){ ds@1952: return 0; /* Different number of columns */ ds@1952: } ds@1952: if( pDest->onError!=pSrc->onError ){ ds@1952: return 0; /* Different conflict resolution strategies */ ds@1952: } ds@1952: for(i=0; inColumn; i++){ ds@1952: if( pSrc->aiColumn[i]!=pDest->aiColumn[i] ){ ds@1952: return 0; /* Different columns indexed */ ds@1952: } ds@1952: if( pSrc->aSortOrder[i]!=pDest->aSortOrder[i] ){ ds@1952: return 0; /* Different sort orders */ ds@1952: } ds@1952: if( pSrc->azColl[i]!=pDest->azColl[i] ){ ds@1952: return 0; /* Different sort orders */ ds@1952: } ds@1952: } ds@1952: ds@1952: /* If no test above fails then the indices must be compatible */ ds@1952: return 1; ds@1952: } ds@1952: ds@1952: /* ds@1952: ** Attempt the transfer optimization on INSERTs of the form ds@1952: ** ds@1952: ** INSERT INTO tab1 SELECT * FROM tab2; ds@1952: ** ds@1952: ** This optimization is only attempted if ds@1952: ** ds@1952: ** (1) tab1 and tab2 have identical schemas including all the ds@1952: ** same indices and constraints ds@1952: ** ds@1952: ** (2) tab1 and tab2 are different tables ds@1952: ** ds@1952: ** (3) There must be no triggers on tab1 ds@1952: ** ds@1952: ** (4) The result set of the SELECT statement is "*" ds@1952: ** ds@1952: ** (5) The SELECT statement has no WHERE, HAVING, ORDER BY, GROUP BY, ds@1952: ** or LIMIT clause. ds@1952: ** ds@1952: ** (6) The SELECT statement is a simple (not a compound) select that ds@1952: ** contains only tab2 in its FROM clause ds@1952: ** ds@1952: ** This method for implementing the INSERT transfers raw records from ds@1952: ** tab2 over to tab1. The columns are not decoded. Raw records from ds@1952: ** the indices of tab2 are transfered to tab1 as well. In so doing, ds@1952: ** the resulting tab1 has much less fragmentation. ds@1952: ** ds@1952: ** This routine returns TRUE if the optimization is attempted. If any ds@1952: ** of the conditions above fail so that the optimization should not ds@1952: ** be attempted, then this routine returns FALSE. ds@1952: */ ds@1952: static int xferOptimization( ds@1952: Parse *pParse, /* Parser context */ ds@1952: Table *pDest, /* The table we are inserting into */ ds@1952: Select *pSelect, /* A SELECT statement to use as the data source */ ds@1952: int onError, /* How to handle constraint errors */ ds@1952: int iDbDest /* The database of pDest */ ds@1952: ){ ds@1952: ExprList *pEList; /* The result set of the SELECT */ ds@1952: Table *pSrc; /* The table in the FROM clause of SELECT */ ds@1952: Index *pSrcIdx, *pDestIdx; /* Source and destination indices */ ds@1952: struct SrcList_item *pItem; /* An element of pSelect->pSrc */ ds@1952: int i; /* Loop counter */ ds@1952: int iDbSrc; /* The database of pSrc */ ds@1952: int iSrc, iDest; /* Cursors from source and destination */ ds@1952: int addr1, addr2; /* Loop addresses */ ds@1952: int emptyDestTest; /* Address of test for empty pDest */ ds@1952: int emptySrcTest; /* Address of test for empty pSrc */ ds@1952: Vdbe *v; /* The VDBE we are building */ ds@1952: KeyInfo *pKey; /* Key information for an index */ ds@1952: int counterMem; /* Memory register used by AUTOINC */ ds@1952: int destHasUniqueIdx = 0; /* True if pDest has a UNIQUE index */ ds@1952: ds@1952: if( pSelect==0 ){ ds@1952: return 0; /* Must be of the form INSERT INTO ... SELECT ... */ ds@1952: } ds@1952: if( pDest->pTrigger ){ ds@1952: return 0; /* tab1 must not have triggers */ ds@1952: } ds@1952: #ifndef SQLITE_OMIT_VIRTUALTABLE ds@1952: if( pDest->isVirtual ){ ds@1952: return 0; /* tab1 must not be a virtual table */ ds@1952: } ds@1952: #endif ds@1952: if( onError==OE_Default ){ ds@1952: onError = OE_Abort; ds@1952: } ds@1952: if( onError!=OE_Abort && onError!=OE_Rollback ){ ds@1952: return 0; /* Cannot do OR REPLACE or OR IGNORE or OR FAIL */ ds@1952: } ds@1952: assert(pSelect->pSrc); /* allocated even if there is no FROM clause */ ds@1952: if( pSelect->pSrc->nSrc!=1 ){ ds@1952: return 0; /* FROM clause must have exactly one term */ ds@1952: } ds@1952: if( pSelect->pSrc->a[0].pSelect ){ ds@1952: return 0; /* FROM clause cannot contain a subquery */ ds@1952: } ds@1952: if( pSelect->pWhere ){ ds@1952: return 0; /* SELECT may not have a WHERE clause */ ds@1952: } ds@1952: if( pSelect->pOrderBy ){ ds@1952: return 0; /* SELECT may not have an ORDER BY clause */ ds@1952: } ds@1952: /* Do not need to test for a HAVING clause. If HAVING is present but ds@1952: ** there is no ORDER BY, we will get an error. */ ds@1952: if( pSelect->pGroupBy ){ ds@1952: return 0; /* SELECT may not have a GROUP BY clause */ ds@1952: } ds@1952: if( pSelect->pLimit ){ ds@1952: return 0; /* SELECT may not have a LIMIT clause */ ds@1952: } ds@1952: assert( pSelect->pOffset==0 ); /* Must be so if pLimit==0 */ ds@1952: if( pSelect->pPrior ){ ds@1952: return 0; /* SELECT may not be a compound query */ ds@1952: } ds@1952: if( pSelect->isDistinct ){ ds@1952: return 0; /* SELECT may not be DISTINCT */ ds@1952: } ds@1952: pEList = pSelect->pEList; ds@1952: assert( pEList!=0 ); ds@1952: if( pEList->nExpr!=1 ){ ds@1952: return 0; /* The result set must have exactly one column */ ds@1952: } ds@1952: assert( pEList->a[0].pExpr ); ds@1952: if( pEList->a[0].pExpr->op!=TK_ALL ){ ds@1952: return 0; /* The result set must be the special operator "*" */ ds@1952: } ds@1952: ds@1952: /* At this point we have established that the statement is of the ds@1952: ** correct syntactic form to participate in this optimization. Now ds@1952: ** we have to check the semantics. ds@1952: */ ds@1952: pItem = pSelect->pSrc->a; ds@1952: pSrc = sqlite3LocateTable(pParse, pItem->zName, pItem->zDatabase); ds@1952: if( pSrc==0 ){ ds@1952: return 0; /* FROM clause does not contain a real table */ ds@1952: } ds@1952: if( pSrc==pDest ){ ds@1952: return 0; /* tab1 and tab2 may not be the same table */ ds@1952: } ds@1952: #ifndef SQLITE_OMIT_VIRTUALTABLE ds@1952: if( pSrc->isVirtual ){ ds@1952: return 0; /* tab2 must not be a virtual table */ ds@1952: } ds@1952: #endif ds@1952: if( pSrc->pSelect ){ ds@1952: return 0; /* tab2 may not be a view */ ds@1952: } ds@1952: if( pDest->nCol!=pSrc->nCol ){ ds@1952: return 0; /* Number of columns must be the same in tab1 and tab2 */ ds@1952: } ds@1952: if( pDest->iPKey!=pSrc->iPKey ){ ds@1952: return 0; /* Both tables must have the same INTEGER PRIMARY KEY */ ds@1952: } ds@1952: for(i=0; inCol; i++){ ds@1952: if( pDest->aCol[i].affinity!=pSrc->aCol[i].affinity ){ ds@1952: return 0; /* Affinity must be the same on all columns */ ds@1952: } ds@1952: if( !xferCompatibleCollation(pDest->aCol[i].zColl, pSrc->aCol[i].zColl) ){ ds@1952: return 0; /* Collating sequence must be the same on all columns */ ds@1952: } ds@1952: if( pDest->aCol[i].notNull && !pSrc->aCol[i].notNull ){ ds@1952: return 0; /* tab2 must be NOT NULL if tab1 is */ ds@1952: } ds@1952: } ds@1952: for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){ ds@1952: if( pDestIdx->onError!=OE_None ){ ds@1952: destHasUniqueIdx = 1; ds@1952: } ds@1952: for(pSrcIdx=pSrc->pIndex; pSrcIdx; pSrcIdx=pSrcIdx->pNext){ ds@1952: if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break; ds@1952: } ds@1952: if( pSrcIdx==0 ){ ds@1952: return 0; /* pDestIdx has no corresponding index in pSrc */ ds@1952: } ds@1952: } ds@1952: #ifndef SQLITE_OMIT_CHECK ds@1952: if( pDest->pCheck && !sqlite3ExprCompare(pSrc->pCheck, pDest->pCheck) ){ ds@1952: return 0; /* Tables have different CHECK constraints. Ticket #2252 */ ds@1952: } ds@1952: #endif ds@1952: ds@1952: /* If we get this far, it means either: ds@1952: ** ds@1952: ** * We can always do the transfer if the table contains an ds@1952: ** an integer primary key ds@1952: ** ds@1952: ** * We can conditionally do the transfer if the destination ds@1952: ** table is empty. ds@1952: */ ds@1952: #ifdef SQLITE_TEST ds@1952: sqlite3_xferopt_count++; ds@1952: #endif ds@1952: iDbSrc = sqlite3SchemaToIndex(pParse->db, pSrc->pSchema); ds@1952: v = sqlite3GetVdbe(pParse); ds@1952: sqlite3CodeVerifySchema(pParse, iDbSrc); ds@1952: iSrc = pParse->nTab++; ds@1952: iDest = pParse->nTab++; ds@1952: counterMem = autoIncBegin(pParse, iDbDest, pDest); ds@1952: sqlite3OpenTable(pParse, iDest, iDbDest, pDest, OP_OpenWrite); ds@1952: if( (pDest->iPKey<0 && pDest->pIndex!=0) || destHasUniqueIdx ){ ds@1952: /* If tables do not have an INTEGER PRIMARY KEY and there ds@1952: ** are indices to be copied and the destination is not empty, ds@1952: ** we have to disallow the transfer optimization because the ds@1952: ** the rowids might change which will mess up indexing. ds@1952: ** ds@1952: ** Or if the destination has a UNIQUE index and is not empty, ds@1952: ** we also disallow the transfer optimization because we cannot ds@1952: ** insure that all entries in the union of DEST and SRC will be ds@1952: ** unique. ds@1952: */ ds@1952: addr1 = sqlite3VdbeAddOp(v, OP_Rewind, iDest, 0); ds@1952: emptyDestTest = sqlite3VdbeAddOp(v, OP_Goto, 0, 0); ds@1952: sqlite3VdbeJumpHere(v, addr1); ds@1952: }else{ ds@1952: emptyDestTest = 0; ds@1952: } ds@1952: sqlite3OpenTable(pParse, iSrc, iDbSrc, pSrc, OP_OpenRead); ds@1952: emptySrcTest = sqlite3VdbeAddOp(v, OP_Rewind, iSrc, 0); ds@1952: if( pDest->iPKey>=0 ){ ds@1952: addr1 = sqlite3VdbeAddOp(v, OP_Rowid, iSrc, 0); ds@1952: sqlite3VdbeAddOp(v, OP_Dup, 0, 0); ds@1952: addr2 = sqlite3VdbeAddOp(v, OP_NotExists, iDest, 0); ds@1952: sqlite3VdbeOp3(v, OP_Halt, SQLITE_CONSTRAINT, onError, ds@1952: "PRIMARY KEY must be unique", P3_STATIC); ds@1952: sqlite3VdbeJumpHere(v, addr2); ds@1952: autoIncStep(pParse, counterMem); ds@1952: }else if( pDest->pIndex==0 ){ ds@1952: addr1 = sqlite3VdbeAddOp(v, OP_NewRowid, iDest, 0); ds@1952: }else{ ds@1952: addr1 = sqlite3VdbeAddOp(v, OP_Rowid, iSrc, 0); ds@1952: assert( pDest->autoInc==0 ); ds@1952: } ds@1952: sqlite3VdbeAddOp(v, OP_RowData, iSrc, 0); ds@1952: sqlite3VdbeOp3(v, OP_Insert, iDest, ds@1952: OPFLAG_NCHANGE|OPFLAG_LASTROWID|OPFLAG_APPEND, ds@1952: pDest->zName, 0); ds@1952: sqlite3VdbeAddOp(v, OP_Next, iSrc, addr1); ds@1952: autoIncEnd(pParse, iDbDest, pDest, counterMem); ds@1952: for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){ ds@1952: for(pSrcIdx=pSrc->pIndex; pSrcIdx; pSrcIdx=pSrcIdx->pNext){ ds@1952: if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break; ds@1952: } ds@1952: assert( pSrcIdx ); ds@1952: sqlite3VdbeAddOp(v, OP_Close, iSrc, 0); ds@1952: sqlite3VdbeAddOp(v, OP_Close, iDest, 0); ds@1952: sqlite3VdbeAddOp(v, OP_Integer, iDbSrc, 0); ds@1952: pKey = sqlite3IndexKeyinfo(pParse, pSrcIdx); ds@1952: VdbeComment((v, "# %s", pSrcIdx->zName)); ds@1952: sqlite3VdbeOp3(v, OP_OpenRead, iSrc, pSrcIdx->tnum, ds@1952: (char*)pKey, P3_KEYINFO_HANDOFF); ds@1952: sqlite3VdbeAddOp(v, OP_Integer, iDbDest, 0); ds@1952: pKey = sqlite3IndexKeyinfo(pParse, pDestIdx); ds@1952: VdbeComment((v, "# %s", pDestIdx->zName)); ds@1952: sqlite3VdbeOp3(v, OP_OpenWrite, iDest, pDestIdx->tnum, ds@1952: (char*)pKey, P3_KEYINFO_HANDOFF); ds@1952: addr1 = sqlite3VdbeAddOp(v, OP_Rewind, iSrc, 0); ds@1952: sqlite3VdbeAddOp(v, OP_RowKey, iSrc, 0); ds@1952: sqlite3VdbeAddOp(v, OP_IdxInsert, iDest, 1); ds@1952: sqlite3VdbeAddOp(v, OP_Next, iSrc, addr1+1); ds@1952: sqlite3VdbeJumpHere(v, addr1); ds@1952: } ds@1952: sqlite3VdbeJumpHere(v, emptySrcTest); ds@1952: sqlite3VdbeAddOp(v, OP_Close, iSrc, 0); ds@1952: sqlite3VdbeAddOp(v, OP_Close, iDest, 0); ds@1952: if( emptyDestTest ){ ds@1952: sqlite3VdbeAddOp(v, OP_Halt, SQLITE_OK, 0); ds@1952: sqlite3VdbeJumpHere(v, emptyDestTest); ds@1952: sqlite3VdbeAddOp(v, OP_Close, iDest, 0); ds@1952: return 0; ds@1952: }else{ ds@1952: return 1; ds@1952: } ds@1952: } ds@1952: #endif /* SQLITE_OMIT_XFER_OPT */