1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sql/SQLite/insert.c Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,1739 @@
1.4 +/*
1.5 +** 2001 September 15
1.6 +**
1.7 +** The author disclaims copyright to this source code. In place of
1.8 +** a legal notice, here is a blessing:
1.9 +**
1.10 +** May you do good and not evil.
1.11 +** May you find forgiveness for yourself and forgive others.
1.12 +** May you share freely, never taking more than you give.
1.13 +**
1.14 +*************************************************************************
1.15 +** This file contains C code routines that are called by the parser
1.16 +** to handle INSERT statements in SQLite.
1.17 +**
1.18 +** $Id: insert.c,v 1.248 2008/07/28 19:34:53 drh Exp $
1.19 +*/
1.20 +#include "sqliteInt.h"
1.21 +
1.22 +/*
1.23 +** Set P4 of the most recently inserted opcode to a column affinity
1.24 +** string for index pIdx. A column affinity string has one character
1.25 +** for each column in the table, according to the affinity of the column:
1.26 +**
1.27 +** Character Column affinity
1.28 +** ------------------------------
1.29 +** 'a' TEXT
1.30 +** 'b' NONE
1.31 +** 'c' NUMERIC
1.32 +** 'd' INTEGER
1.33 +** 'e' REAL
1.34 +**
1.35 +** An extra 'b' is appended to the end of the string to cover the
1.36 +** rowid that appears as the last column in every index.
1.37 +*/
1.38 +void sqlite3IndexAffinityStr(Vdbe *v, Index *pIdx){
1.39 + if( !pIdx->zColAff ){
1.40 + /* The first time a column affinity string for a particular index is
1.41 + ** required, it is allocated and populated here. It is then stored as
1.42 + ** a member of the Index structure for subsequent use.
1.43 + **
1.44 + ** The column affinity string will eventually be deleted by
1.45 + ** sqliteDeleteIndex() when the Index structure itself is cleaned
1.46 + ** up.
1.47 + */
1.48 + int n;
1.49 + Table *pTab = pIdx->pTable;
1.50 + sqlite3 *db = sqlite3VdbeDb(v);
1.51 + pIdx->zColAff = (char *)sqlite3Malloc(pIdx->nColumn+2);
1.52 + if( !pIdx->zColAff ){
1.53 + db->mallocFailed = 1;
1.54 + return;
1.55 + }
1.56 + for(n=0; n<pIdx->nColumn; n++){
1.57 + pIdx->zColAff[n] = pTab->aCol[pIdx->aiColumn[n]].affinity;
1.58 + }
1.59 + pIdx->zColAff[n++] = SQLITE_AFF_NONE;
1.60 + pIdx->zColAff[n] = 0;
1.61 + }
1.62 +
1.63 + sqlite3VdbeChangeP4(v, -1, pIdx->zColAff, 0);
1.64 +}
1.65 +
1.66 +/*
1.67 +** Set P4 of the most recently inserted opcode to a column affinity
1.68 +** string for table pTab. A column affinity string has one character
1.69 +** for each column indexed by the index, according to the affinity of the
1.70 +** column:
1.71 +**
1.72 +** Character Column affinity
1.73 +** ------------------------------
1.74 +** 'a' TEXT
1.75 +** 'b' NONE
1.76 +** 'c' NUMERIC
1.77 +** 'd' INTEGER
1.78 +** 'e' REAL
1.79 +*/
1.80 +void sqlite3TableAffinityStr(Vdbe *v, Table *pTab){
1.81 + /* The first time a column affinity string for a particular table
1.82 + ** is required, it is allocated and populated here. It is then
1.83 + ** stored as a member of the Table structure for subsequent use.
1.84 + **
1.85 + ** The column affinity string will eventually be deleted by
1.86 + ** sqlite3DeleteTable() when the Table structure itself is cleaned up.
1.87 + */
1.88 + if( !pTab->zColAff ){
1.89 + char *zColAff;
1.90 + int i;
1.91 + sqlite3 *db = sqlite3VdbeDb(v);
1.92 +
1.93 + zColAff = (char *)sqlite3Malloc(pTab->nCol+1);
1.94 + if( !zColAff ){
1.95 + db->mallocFailed = 1;
1.96 + return;
1.97 + }
1.98 +
1.99 + for(i=0; i<pTab->nCol; i++){
1.100 + zColAff[i] = pTab->aCol[i].affinity;
1.101 + }
1.102 + zColAff[pTab->nCol] = '\0';
1.103 +
1.104 + pTab->zColAff = zColAff;
1.105 + }
1.106 +
1.107 + sqlite3VdbeChangeP4(v, -1, pTab->zColAff, 0);
1.108 +}
1.109 +
1.110 +/*
1.111 +** Return non-zero if the table pTab in database iDb or any of its indices
1.112 +** have been opened at any point in the VDBE program beginning at location
1.113 +** iStartAddr throught the end of the program. This is used to see if
1.114 +** a statement of the form "INSERT INTO <iDb, pTab> SELECT ..." can
1.115 +** run without using temporary table for the results of the SELECT.
1.116 +*/
1.117 +static int readsTable(Vdbe *v, int iStartAddr, int iDb, Table *pTab){
1.118 + int i;
1.119 + int iEnd = sqlite3VdbeCurrentAddr(v);
1.120 + for(i=iStartAddr; i<iEnd; i++){
1.121 + VdbeOp *pOp = sqlite3VdbeGetOp(v, i);
1.122 + assert( pOp!=0 );
1.123 + if( pOp->opcode==OP_OpenRead && pOp->p3==iDb ){
1.124 + Index *pIndex;
1.125 + int tnum = pOp->p2;
1.126 + if( tnum==pTab->tnum ){
1.127 + return 1;
1.128 + }
1.129 + for(pIndex=pTab->pIndex; pIndex; pIndex=pIndex->pNext){
1.130 + if( tnum==pIndex->tnum ){
1.131 + return 1;
1.132 + }
1.133 + }
1.134 + }
1.135 +#ifndef SQLITE_OMIT_VIRTUALTABLE
1.136 + if( pOp->opcode==OP_VOpen && pOp->p4.pVtab==pTab->pVtab ){
1.137 + assert( pOp->p4.pVtab!=0 );
1.138 + assert( pOp->p4type==P4_VTAB );
1.139 + return 1;
1.140 + }
1.141 +#endif
1.142 + }
1.143 + return 0;
1.144 +}
1.145 +
1.146 +#ifndef SQLITE_OMIT_AUTOINCREMENT
1.147 +/*
1.148 +** Write out code to initialize the autoincrement logic. This code
1.149 +** looks up the current autoincrement value in the sqlite_sequence
1.150 +** table and stores that value in a register. Code generated by
1.151 +** autoIncStep() will keep that register holding the largest
1.152 +** rowid value. Code generated by autoIncEnd() will write the new
1.153 +** largest value of the counter back into the sqlite_sequence table.
1.154 +**
1.155 +** This routine returns the index of the mem[] cell that contains
1.156 +** the maximum rowid counter.
1.157 +**
1.158 +** Three consecutive registers are allocated by this routine. The
1.159 +** first two hold the name of the target table and the maximum rowid
1.160 +** inserted into the target table, respectively.
1.161 +** The third holds the rowid in sqlite_sequence where we will
1.162 +** write back the revised maximum rowid. This routine returns the
1.163 +** index of the second of these three registers.
1.164 +*/
1.165 +static int autoIncBegin(
1.166 + Parse *pParse, /* Parsing context */
1.167 + int iDb, /* Index of the database holding pTab */
1.168 + Table *pTab /* The table we are writing to */
1.169 +){
1.170 + int memId = 0; /* Register holding maximum rowid */
1.171 + if( pTab->autoInc ){
1.172 + Vdbe *v = pParse->pVdbe;
1.173 + Db *pDb = &pParse->db->aDb[iDb];
1.174 + int iCur = pParse->nTab;
1.175 + int addr; /* Address of the top of the loop */
1.176 + assert( v );
1.177 + pParse->nMem++; /* Holds name of table */
1.178 + memId = ++pParse->nMem;
1.179 + pParse->nMem++;
1.180 + sqlite3OpenTable(pParse, iCur, iDb, pDb->pSchema->pSeqTab, OP_OpenRead);
1.181 + addr = sqlite3VdbeCurrentAddr(v);
1.182 + sqlite3VdbeAddOp4(v, OP_String8, 0, memId-1, 0, pTab->zName, 0);
1.183 + sqlite3VdbeAddOp2(v, OP_Rewind, iCur, addr+9);
1.184 + sqlite3VdbeAddOp3(v, OP_Column, iCur, 0, memId);
1.185 + sqlite3VdbeAddOp3(v, OP_Ne, memId-1, addr+7, memId);
1.186 + sqlite3VdbeChangeP5(v, SQLITE_JUMPIFNULL);
1.187 + sqlite3VdbeAddOp2(v, OP_Rowid, iCur, memId+1);
1.188 + sqlite3VdbeAddOp3(v, OP_Column, iCur, 1, memId);
1.189 + sqlite3VdbeAddOp2(v, OP_Goto, 0, addr+9);
1.190 + sqlite3VdbeAddOp2(v, OP_Next, iCur, addr+2);
1.191 + sqlite3VdbeAddOp2(v, OP_Integer, 0, memId);
1.192 + sqlite3VdbeAddOp2(v, OP_Close, iCur, 0);
1.193 + }
1.194 + return memId;
1.195 +}
1.196 +
1.197 +/*
1.198 +** Update the maximum rowid for an autoincrement calculation.
1.199 +**
1.200 +** This routine should be called when the top of the stack holds a
1.201 +** new rowid that is about to be inserted. If that new rowid is
1.202 +** larger than the maximum rowid in the memId memory cell, then the
1.203 +** memory cell is updated. The stack is unchanged.
1.204 +*/
1.205 +static void autoIncStep(Parse *pParse, int memId, int regRowid){
1.206 + if( memId>0 ){
1.207 + sqlite3VdbeAddOp2(pParse->pVdbe, OP_MemMax, memId, regRowid);
1.208 + }
1.209 +}
1.210 +
1.211 +/*
1.212 +** After doing one or more inserts, the maximum rowid is stored
1.213 +** in reg[memId]. Generate code to write this value back into the
1.214 +** the sqlite_sequence table.
1.215 +*/
1.216 +static void autoIncEnd(
1.217 + Parse *pParse, /* The parsing context */
1.218 + int iDb, /* Index of the database holding pTab */
1.219 + Table *pTab, /* Table we are inserting into */
1.220 + int memId /* Memory cell holding the maximum rowid */
1.221 +){
1.222 + if( pTab->autoInc ){
1.223 + int iCur = pParse->nTab;
1.224 + Vdbe *v = pParse->pVdbe;
1.225 + Db *pDb = &pParse->db->aDb[iDb];
1.226 + int j1;
1.227 + int iRec = ++pParse->nMem; /* Memory cell used for record */
1.228 +
1.229 + assert( v );
1.230 + sqlite3OpenTable(pParse, iCur, iDb, pDb->pSchema->pSeqTab, OP_OpenWrite);
1.231 + j1 = sqlite3VdbeAddOp1(v, OP_NotNull, memId+1);
1.232 + sqlite3VdbeAddOp2(v, OP_NewRowid, iCur, memId+1);
1.233 + sqlite3VdbeJumpHere(v, j1);
1.234 + sqlite3VdbeAddOp3(v, OP_MakeRecord, memId-1, 2, iRec);
1.235 + sqlite3VdbeAddOp3(v, OP_Insert, iCur, iRec, memId+1);
1.236 + sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
1.237 + sqlite3VdbeAddOp1(v, OP_Close, iCur);
1.238 + }
1.239 +}
1.240 +#else
1.241 +/*
1.242 +** If SQLITE_OMIT_AUTOINCREMENT is defined, then the three routines
1.243 +** above are all no-ops
1.244 +*/
1.245 +# define autoIncBegin(A,B,C) (0)
1.246 +# define autoIncStep(A,B,C)
1.247 +# define autoIncEnd(A,B,C,D)
1.248 +#endif /* SQLITE_OMIT_AUTOINCREMENT */
1.249 +
1.250 +
1.251 +/* Forward declaration */
1.252 +static int xferOptimization(
1.253 + Parse *pParse, /* Parser context */
1.254 + Table *pDest, /* The table we are inserting into */
1.255 + Select *pSelect, /* A SELECT statement to use as the data source */
1.256 + int onError, /* How to handle constraint errors */
1.257 + int iDbDest /* The database of pDest */
1.258 +);
1.259 +
1.260 +/*
1.261 +** This routine is call to handle SQL of the following forms:
1.262 +**
1.263 +** insert into TABLE (IDLIST) values(EXPRLIST)
1.264 +** insert into TABLE (IDLIST) select
1.265 +**
1.266 +** The IDLIST following the table name is always optional. If omitted,
1.267 +** then a list of all columns for the table is substituted. The IDLIST
1.268 +** appears in the pColumn parameter. pColumn is NULL if IDLIST is omitted.
1.269 +**
1.270 +** The pList parameter holds EXPRLIST in the first form of the INSERT
1.271 +** statement above, and pSelect is NULL. For the second form, pList is
1.272 +** NULL and pSelect is a pointer to the select statement used to generate
1.273 +** data for the insert.
1.274 +**
1.275 +** The code generated follows one of four templates. For a simple
1.276 +** select with data coming from a VALUES clause, the code executes
1.277 +** once straight down through. Pseudo-code follows (we call this
1.278 +** the "1st template"):
1.279 +**
1.280 +** open write cursor to <table> and its indices
1.281 +** puts VALUES clause expressions onto the stack
1.282 +** write the resulting record into <table>
1.283 +** cleanup
1.284 +**
1.285 +** The three remaining templates assume the statement is of the form
1.286 +**
1.287 +** INSERT INTO <table> SELECT ...
1.288 +**
1.289 +** If the SELECT clause is of the restricted form "SELECT * FROM <table2>" -
1.290 +** in other words if the SELECT pulls all columns from a single table
1.291 +** and there is no WHERE or LIMIT or GROUP BY or ORDER BY clauses, and
1.292 +** if <table2> and <table1> are distinct tables but have identical
1.293 +** schemas, including all the same indices, then a special optimization
1.294 +** is invoked that copies raw records from <table2> over to <table1>.
1.295 +** See the xferOptimization() function for the implementation of this
1.296 +** template. This is the 2nd template.
1.297 +**
1.298 +** open a write cursor to <table>
1.299 +** open read cursor on <table2>
1.300 +** transfer all records in <table2> over to <table>
1.301 +** close cursors
1.302 +** foreach index on <table>
1.303 +** open a write cursor on the <table> index
1.304 +** open a read cursor on the corresponding <table2> index
1.305 +** transfer all records from the read to the write cursors
1.306 +** close cursors
1.307 +** end foreach
1.308 +**
1.309 +** The 3rd template is for when the second template does not apply
1.310 +** and the SELECT clause does not read from <table> at any time.
1.311 +** The generated code follows this template:
1.312 +**
1.313 +** EOF <- 0
1.314 +** X <- A
1.315 +** goto B
1.316 +** A: setup for the SELECT
1.317 +** loop over the rows in the SELECT
1.318 +** load values into registers R..R+n
1.319 +** yield X
1.320 +** end loop
1.321 +** cleanup after the SELECT
1.322 +** EOF <- 1
1.323 +** yield X
1.324 +** goto A
1.325 +** B: open write cursor to <table> and its indices
1.326 +** C: yield X
1.327 +** if EOF goto D
1.328 +** insert the select result into <table> from R..R+n
1.329 +** goto C
1.330 +** D: cleanup
1.331 +**
1.332 +** The 4th template is used if the insert statement takes its
1.333 +** values from a SELECT but the data is being inserted into a table
1.334 +** that is also read as part of the SELECT. In the third form,
1.335 +** we have to use a intermediate table to store the results of
1.336 +** the select. The template is like this:
1.337 +**
1.338 +** EOF <- 0
1.339 +** X <- A
1.340 +** goto B
1.341 +** A: setup for the SELECT
1.342 +** loop over the tables in the SELECT
1.343 +** load value into register R..R+n
1.344 +** yield X
1.345 +** end loop
1.346 +** cleanup after the SELECT
1.347 +** EOF <- 1
1.348 +** yield X
1.349 +** halt-error
1.350 +** B: open temp table
1.351 +** L: yield X
1.352 +** if EOF goto M
1.353 +** insert row from R..R+n into temp table
1.354 +** goto L
1.355 +** M: open write cursor to <table> and its indices
1.356 +** rewind temp table
1.357 +** C: loop over rows of intermediate table
1.358 +** transfer values form intermediate table into <table>
1.359 +** end loop
1.360 +** D: cleanup
1.361 +*/
1.362 +void sqlite3Insert(
1.363 + Parse *pParse, /* Parser context */
1.364 + SrcList *pTabList, /* Name of table into which we are inserting */
1.365 + ExprList *pList, /* List of values to be inserted */
1.366 + Select *pSelect, /* A SELECT statement to use as the data source */
1.367 + IdList *pColumn, /* Column names corresponding to IDLIST. */
1.368 + int onError /* How to handle constraint errors */
1.369 +){
1.370 + sqlite3 *db; /* The main database structure */
1.371 + Table *pTab; /* The table to insert into. aka TABLE */
1.372 + char *zTab; /* Name of the table into which we are inserting */
1.373 + const char *zDb; /* Name of the database holding this table */
1.374 + int i, j, idx; /* Loop counters */
1.375 + Vdbe *v; /* Generate code into this virtual machine */
1.376 + Index *pIdx; /* For looping over indices of the table */
1.377 + int nColumn; /* Number of columns in the data */
1.378 + int nHidden = 0; /* Number of hidden columns if TABLE is virtual */
1.379 + int baseCur = 0; /* VDBE Cursor number for pTab */
1.380 + int keyColumn = -1; /* Column that is the INTEGER PRIMARY KEY */
1.381 + int endOfLoop; /* Label for the end of the insertion loop */
1.382 + int useTempTable = 0; /* Store SELECT results in intermediate table */
1.383 + int srcTab = 0; /* Data comes from this temporary cursor if >=0 */
1.384 + int addrInsTop = 0; /* Jump to label "D" */
1.385 + int addrCont = 0; /* Top of insert loop. Label "C" in templates 3 and 4 */
1.386 + int addrSelect = 0; /* Address of coroutine that implements the SELECT */
1.387 + SelectDest dest; /* Destination for SELECT on rhs of INSERT */
1.388 + int newIdx = -1; /* Cursor for the NEW pseudo-table */
1.389 + int iDb; /* Index of database holding TABLE */
1.390 + Db *pDb; /* The database containing table being inserted into */
1.391 + int appendFlag = 0; /* True if the insert is likely to be an append */
1.392 +
1.393 + /* Register allocations */
1.394 + int regFromSelect = 0;/* Base register for data coming from SELECT */
1.395 + int regAutoinc = 0; /* Register holding the AUTOINCREMENT counter */
1.396 + int regRowCount = 0; /* Memory cell used for the row counter */
1.397 + int regIns; /* Block of regs holding rowid+data being inserted */
1.398 + int regRowid; /* registers holding insert rowid */
1.399 + int regData; /* register holding first column to insert */
1.400 + int regRecord; /* Holds the assemblied row record */
1.401 + int regEof = 0; /* Register recording end of SELECT data */
1.402 + int *aRegIdx = 0; /* One register allocated to each index */
1.403 +
1.404 +
1.405 +#ifndef SQLITE_OMIT_TRIGGER
1.406 + int isView; /* True if attempting to insert into a view */
1.407 + int triggers_exist = 0; /* True if there are FOR EACH ROW triggers */
1.408 +#endif
1.409 +
1.410 + db = pParse->db;
1.411 + if( pParse->nErr || db->mallocFailed ){
1.412 + goto insert_cleanup;
1.413 + }
1.414 +
1.415 + /* Locate the table into which we will be inserting new information.
1.416 + */
1.417 + assert( pTabList->nSrc==1 );
1.418 + zTab = pTabList->a[0].zName;
1.419 + if( zTab==0 ) goto insert_cleanup;
1.420 + pTab = sqlite3SrcListLookup(pParse, pTabList);
1.421 + if( pTab==0 ){
1.422 + goto insert_cleanup;
1.423 + }
1.424 + iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
1.425 + assert( iDb<db->nDb );
1.426 + pDb = &db->aDb[iDb];
1.427 + zDb = pDb->zName;
1.428 + if( sqlite3AuthCheck(pParse, SQLITE_INSERT, pTab->zName, 0, zDb) ){
1.429 + goto insert_cleanup;
1.430 + }
1.431 +
1.432 + /* Figure out if we have any triggers and if the table being
1.433 + ** inserted into is a view
1.434 + */
1.435 +#ifndef SQLITE_OMIT_TRIGGER
1.436 + triggers_exist = sqlite3TriggersExist(pParse, pTab, TK_INSERT, 0);
1.437 + isView = pTab->pSelect!=0;
1.438 +#else
1.439 +# define triggers_exist 0
1.440 +# define isView 0
1.441 +#endif
1.442 +#ifdef SQLITE_OMIT_VIEW
1.443 +# undef isView
1.444 +# define isView 0
1.445 +#endif
1.446 +
1.447 + /* Ensure that:
1.448 + * (a) the table is not read-only,
1.449 + * (b) that if it is a view then ON INSERT triggers exist
1.450 + */
1.451 + if( sqlite3IsReadOnly(pParse, pTab, triggers_exist) ){
1.452 + goto insert_cleanup;
1.453 + }
1.454 + assert( pTab!=0 );
1.455 +
1.456 + /* If pTab is really a view, make sure it has been initialized.
1.457 + ** ViewGetColumnNames() is a no-op if pTab is not a view (or virtual
1.458 + ** module table).
1.459 + */
1.460 + if( sqlite3ViewGetColumnNames(pParse, pTab) ){
1.461 + goto insert_cleanup;
1.462 + }
1.463 +
1.464 + /* Allocate a VDBE
1.465 + */
1.466 + v = sqlite3GetVdbe(pParse);
1.467 + if( v==0 ) goto insert_cleanup;
1.468 + if( pParse->nested==0 ) sqlite3VdbeCountChanges(v);
1.469 + sqlite3BeginWriteOperation(pParse, pSelect || triggers_exist, iDb);
1.470 +
1.471 + /* if there are row triggers, allocate a temp table for new.* references. */
1.472 + if( triggers_exist ){
1.473 + newIdx = pParse->nTab++;
1.474 + }
1.475 +
1.476 +#ifndef SQLITE_OMIT_XFER_OPT
1.477 + /* If the statement is of the form
1.478 + **
1.479 + ** INSERT INTO <table1> SELECT * FROM <table2>;
1.480 + **
1.481 + ** Then special optimizations can be applied that make the transfer
1.482 + ** very fast and which reduce fragmentation of indices.
1.483 + **
1.484 + ** This is the 2nd template.
1.485 + */
1.486 + if( pColumn==0 && xferOptimization(pParse, pTab, pSelect, onError, iDb) ){
1.487 + assert( !triggers_exist );
1.488 + assert( pList==0 );
1.489 + goto insert_cleanup;
1.490 + }
1.491 +#endif /* SQLITE_OMIT_XFER_OPT */
1.492 +
1.493 + /* If this is an AUTOINCREMENT table, look up the sequence number in the
1.494 + ** sqlite_sequence table and store it in memory cell regAutoinc.
1.495 + */
1.496 + regAutoinc = autoIncBegin(pParse, iDb, pTab);
1.497 +
1.498 + /* Figure out how many columns of data are supplied. If the data
1.499 + ** is coming from a SELECT statement, then generate a co-routine that
1.500 + ** produces a single row of the SELECT on each invocation. The
1.501 + ** co-routine is the common header to the 3rd and 4th templates.
1.502 + */
1.503 + if( pSelect ){
1.504 + /* Data is coming from a SELECT. Generate code to implement that SELECT
1.505 + ** as a co-routine. The code is common to both the 3rd and 4th
1.506 + ** templates:
1.507 + **
1.508 + ** EOF <- 0
1.509 + ** X <- A
1.510 + ** goto B
1.511 + ** A: setup for the SELECT
1.512 + ** loop over the tables in the SELECT
1.513 + ** load value into register R..R+n
1.514 + ** yield X
1.515 + ** end loop
1.516 + ** cleanup after the SELECT
1.517 + ** EOF <- 1
1.518 + ** yield X
1.519 + ** halt-error
1.520 + **
1.521 + ** On each invocation of the co-routine, it puts a single row of the
1.522 + ** SELECT result into registers dest.iMem...dest.iMem+dest.nMem-1.
1.523 + ** (These output registers are allocated by sqlite3Select().) When
1.524 + ** the SELECT completes, it sets the EOF flag stored in regEof.
1.525 + */
1.526 + int rc, j1;
1.527 +
1.528 + regEof = ++pParse->nMem;
1.529 + sqlite3VdbeAddOp2(v, OP_Integer, 0, regEof); /* EOF <- 0 */
1.530 + VdbeComment((v, "SELECT eof flag"));
1.531 + sqlite3SelectDestInit(&dest, SRT_Coroutine, ++pParse->nMem);
1.532 + addrSelect = sqlite3VdbeCurrentAddr(v)+2;
1.533 + sqlite3VdbeAddOp2(v, OP_Integer, addrSelect-1, dest.iParm);
1.534 + j1 = sqlite3VdbeAddOp2(v, OP_Goto, 0, 0);
1.535 + VdbeComment((v, "Jump over SELECT coroutine"));
1.536 +
1.537 + /* Resolve the expressions in the SELECT statement and execute it. */
1.538 + rc = sqlite3Select(pParse, pSelect, &dest, 0, 0, 0);
1.539 + if( rc || pParse->nErr || db->mallocFailed ){
1.540 + goto insert_cleanup;
1.541 + }
1.542 + sqlite3VdbeAddOp2(v, OP_Integer, 1, regEof); /* EOF <- 1 */
1.543 + sqlite3VdbeAddOp1(v, OP_Yield, dest.iParm); /* yield X */
1.544 + sqlite3VdbeAddOp2(v, OP_Halt, SQLITE_INTERNAL, OE_Abort);
1.545 + VdbeComment((v, "End of SELECT coroutine"));
1.546 + sqlite3VdbeJumpHere(v, j1); /* label B: */
1.547 +
1.548 + regFromSelect = dest.iMem;
1.549 + assert( pSelect->pEList );
1.550 + nColumn = pSelect->pEList->nExpr;
1.551 + assert( dest.nMem==nColumn );
1.552 +
1.553 + /* Set useTempTable to TRUE if the result of the SELECT statement
1.554 + ** should be written into a temporary table (template 4). Set to
1.555 + ** FALSE if each* row of the SELECT can be written directly into
1.556 + ** the destination table (template 3).
1.557 + **
1.558 + ** A temp table must be used if the table being updated is also one
1.559 + ** of the tables being read by the SELECT statement. Also use a
1.560 + ** temp table in the case of row triggers.
1.561 + */
1.562 + if( triggers_exist || readsTable(v, addrSelect, iDb, pTab) ){
1.563 + useTempTable = 1;
1.564 + }
1.565 +
1.566 + if( useTempTable ){
1.567 + /* Invoke the coroutine to extract information from the SELECT
1.568 + ** and add it to a transient table srcTab. The code generated
1.569 + ** here is from the 4th template:
1.570 + **
1.571 + ** B: open temp table
1.572 + ** L: yield X
1.573 + ** if EOF goto M
1.574 + ** insert row from R..R+n into temp table
1.575 + ** goto L
1.576 + ** M: ...
1.577 + */
1.578 + int regRec; /* Register to hold packed record */
1.579 + int regRowid; /* Register to hold temp table ROWID */
1.580 + int addrTop; /* Label "L" */
1.581 + int addrIf; /* Address of jump to M */
1.582 +
1.583 + srcTab = pParse->nTab++;
1.584 + regRec = sqlite3GetTempReg(pParse);
1.585 + regRowid = sqlite3GetTempReg(pParse);
1.586 + sqlite3VdbeAddOp2(v, OP_OpenEphemeral, srcTab, nColumn);
1.587 + addrTop = sqlite3VdbeAddOp1(v, OP_Yield, dest.iParm);
1.588 + addrIf = sqlite3VdbeAddOp1(v, OP_If, regEof);
1.589 + sqlite3VdbeAddOp3(v, OP_MakeRecord, regFromSelect, nColumn, regRec);
1.590 + sqlite3VdbeAddOp2(v, OP_NewRowid, srcTab, regRowid);
1.591 + sqlite3VdbeAddOp3(v, OP_Insert, srcTab, regRec, regRowid);
1.592 + sqlite3VdbeAddOp2(v, OP_Goto, 0, addrTop);
1.593 + sqlite3VdbeJumpHere(v, addrIf);
1.594 + sqlite3ReleaseTempReg(pParse, regRec);
1.595 + sqlite3ReleaseTempReg(pParse, regRowid);
1.596 + }
1.597 + }else{
1.598 + /* This is the case if the data for the INSERT is coming from a VALUES
1.599 + ** clause
1.600 + */
1.601 + NameContext sNC;
1.602 + memset(&sNC, 0, sizeof(sNC));
1.603 + sNC.pParse = pParse;
1.604 + srcTab = -1;
1.605 + assert( useTempTable==0 );
1.606 + nColumn = pList ? pList->nExpr : 0;
1.607 + for(i=0; i<nColumn; i++){
1.608 + if( sqlite3ExprResolveNames(&sNC, pList->a[i].pExpr) ){
1.609 + goto insert_cleanup;
1.610 + }
1.611 + }
1.612 + }
1.613 +
1.614 + /* Make sure the number of columns in the source data matches the number
1.615 + ** of columns to be inserted into the table.
1.616 + */
1.617 + if( IsVirtual(pTab) ){
1.618 + for(i=0; i<pTab->nCol; i++){
1.619 + nHidden += (IsHiddenColumn(&pTab->aCol[i]) ? 1 : 0);
1.620 + }
1.621 + }
1.622 + if( pColumn==0 && nColumn && nColumn!=(pTab->nCol-nHidden) ){
1.623 + sqlite3ErrorMsg(pParse,
1.624 + "table %S has %d columns but %d values were supplied",
1.625 + pTabList, 0, pTab->nCol, nColumn);
1.626 + goto insert_cleanup;
1.627 + }
1.628 + if( pColumn!=0 && nColumn!=pColumn->nId ){
1.629 + sqlite3ErrorMsg(pParse, "%d values for %d columns", nColumn, pColumn->nId);
1.630 + goto insert_cleanup;
1.631 + }
1.632 +
1.633 + /* If the INSERT statement included an IDLIST term, then make sure
1.634 + ** all elements of the IDLIST really are columns of the table and
1.635 + ** remember the column indices.
1.636 + **
1.637 + ** If the table has an INTEGER PRIMARY KEY column and that column
1.638 + ** is named in the IDLIST, then record in the keyColumn variable
1.639 + ** the index into IDLIST of the primary key column. keyColumn is
1.640 + ** the index of the primary key as it appears in IDLIST, not as
1.641 + ** is appears in the original table. (The index of the primary
1.642 + ** key in the original table is pTab->iPKey.)
1.643 + */
1.644 + if( pColumn ){
1.645 + for(i=0; i<pColumn->nId; i++){
1.646 + pColumn->a[i].idx = -1;
1.647 + }
1.648 + for(i=0; i<pColumn->nId; i++){
1.649 + for(j=0; j<pTab->nCol; j++){
1.650 + if( sqlite3StrICmp(pColumn->a[i].zName, pTab->aCol[j].zName)==0 ){
1.651 + pColumn->a[i].idx = j;
1.652 + if( j==pTab->iPKey ){
1.653 + keyColumn = i;
1.654 + }
1.655 + break;
1.656 + }
1.657 + }
1.658 + if( j>=pTab->nCol ){
1.659 + if( sqlite3IsRowid(pColumn->a[i].zName) ){
1.660 + keyColumn = i;
1.661 + }else{
1.662 + sqlite3ErrorMsg(pParse, "table %S has no column named %s",
1.663 + pTabList, 0, pColumn->a[i].zName);
1.664 + pParse->nErr++;
1.665 + goto insert_cleanup;
1.666 + }
1.667 + }
1.668 + }
1.669 + }
1.670 +
1.671 + /* If there is no IDLIST term but the table has an integer primary
1.672 + ** key, the set the keyColumn variable to the primary key column index
1.673 + ** in the original table definition.
1.674 + */
1.675 + if( pColumn==0 && nColumn>0 ){
1.676 + keyColumn = pTab->iPKey;
1.677 + }
1.678 +
1.679 + /* Open the temp table for FOR EACH ROW triggers
1.680 + */
1.681 + if( triggers_exist ){
1.682 + sqlite3VdbeAddOp2(v, OP_SetNumColumns, 0, pTab->nCol);
1.683 + sqlite3VdbeAddOp2(v, OP_OpenPseudo, newIdx, 0);
1.684 + }
1.685 +
1.686 + /* Initialize the count of rows to be inserted
1.687 + */
1.688 + if( db->flags & SQLITE_CountRows ){
1.689 + regRowCount = ++pParse->nMem;
1.690 + sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount);
1.691 + }
1.692 +
1.693 + /* If this is not a view, open the table and and all indices */
1.694 + if( !isView ){
1.695 + int nIdx;
1.696 + int i;
1.697 +
1.698 + baseCur = pParse->nTab;
1.699 + nIdx = sqlite3OpenTableAndIndices(pParse, pTab, baseCur, OP_OpenWrite);
1.700 + aRegIdx = sqlite3DbMallocRaw(db, sizeof(int)*(nIdx+1));
1.701 + if( aRegIdx==0 ){
1.702 + goto insert_cleanup;
1.703 + }
1.704 + for(i=0; i<nIdx; i++){
1.705 + aRegIdx[i] = ++pParse->nMem;
1.706 + }
1.707 + }
1.708 +
1.709 + /* This is the top of the main insertion loop */
1.710 + if( useTempTable ){
1.711 + /* This block codes the top of loop only. The complete loop is the
1.712 + ** following pseudocode (template 4):
1.713 + **
1.714 + ** rewind temp table
1.715 + ** C: loop over rows of intermediate table
1.716 + ** transfer values form intermediate table into <table>
1.717 + ** end loop
1.718 + ** D: ...
1.719 + */
1.720 + addrInsTop = sqlite3VdbeAddOp1(v, OP_Rewind, srcTab);
1.721 + addrCont = sqlite3VdbeCurrentAddr(v);
1.722 + }else if( pSelect ){
1.723 + /* This block codes the top of loop only. The complete loop is the
1.724 + ** following pseudocode (template 3):
1.725 + **
1.726 + ** C: yield X
1.727 + ** if EOF goto D
1.728 + ** insert the select result into <table> from R..R+n
1.729 + ** goto C
1.730 + ** D: ...
1.731 + */
1.732 + addrCont = sqlite3VdbeAddOp1(v, OP_Yield, dest.iParm);
1.733 + addrInsTop = sqlite3VdbeAddOp1(v, OP_If, regEof);
1.734 + }
1.735 +
1.736 + /* Allocate registers for holding the rowid of the new row,
1.737 + ** the content of the new row, and the assemblied row record.
1.738 + */
1.739 + regRecord = ++pParse->nMem;
1.740 + regRowid = regIns = pParse->nMem+1;
1.741 + pParse->nMem += pTab->nCol + 1;
1.742 + if( IsVirtual(pTab) ){
1.743 + regRowid++;
1.744 + pParse->nMem++;
1.745 + }
1.746 + regData = regRowid+1;
1.747 +
1.748 + /* Run the BEFORE and INSTEAD OF triggers, if there are any
1.749 + */
1.750 + endOfLoop = sqlite3VdbeMakeLabel(v);
1.751 + if( triggers_exist & TRIGGER_BEFORE ){
1.752 + int regRowid;
1.753 + int regCols;
1.754 + int regRec;
1.755 +
1.756 + /* build the NEW.* reference row. Note that if there is an INTEGER
1.757 + ** PRIMARY KEY into which a NULL is being inserted, that NULL will be
1.758 + ** translated into a unique ID for the row. But on a BEFORE trigger,
1.759 + ** we do not know what the unique ID will be (because the insert has
1.760 + ** not happened yet) so we substitute a rowid of -1
1.761 + */
1.762 + regRowid = sqlite3GetTempReg(pParse);
1.763 + if( keyColumn<0 ){
1.764 + sqlite3VdbeAddOp2(v, OP_Integer, -1, regRowid);
1.765 + }else if( useTempTable ){
1.766 + sqlite3VdbeAddOp3(v, OP_Column, srcTab, keyColumn, regRowid);
1.767 + }else{
1.768 + int j1;
1.769 + assert( pSelect==0 ); /* Otherwise useTempTable is true */
1.770 + sqlite3ExprCode(pParse, pList->a[keyColumn].pExpr, regRowid);
1.771 + j1 = sqlite3VdbeAddOp1(v, OP_NotNull, regRowid);
1.772 + sqlite3VdbeAddOp2(v, OP_Integer, -1, regRowid);
1.773 + sqlite3VdbeJumpHere(v, j1);
1.774 + sqlite3VdbeAddOp1(v, OP_MustBeInt, regRowid);
1.775 + }
1.776 +
1.777 + /* Cannot have triggers on a virtual table. If it were possible,
1.778 + ** this block would have to account for hidden column.
1.779 + */
1.780 + assert(!IsVirtual(pTab));
1.781 +
1.782 + /* Create the new column data
1.783 + */
1.784 + regCols = sqlite3GetTempRange(pParse, pTab->nCol);
1.785 + for(i=0; i<pTab->nCol; i++){
1.786 + if( pColumn==0 ){
1.787 + j = i;
1.788 + }else{
1.789 + for(j=0; j<pColumn->nId; j++){
1.790 + if( pColumn->a[j].idx==i ) break;
1.791 + }
1.792 + }
1.793 + if( pColumn && j>=pColumn->nId ){
1.794 + sqlite3ExprCode(pParse, pTab->aCol[i].pDflt, regCols+i);
1.795 + }else if( useTempTable ){
1.796 + sqlite3VdbeAddOp3(v, OP_Column, srcTab, j, regCols+i);
1.797 + }else{
1.798 + assert( pSelect==0 ); /* Otherwise useTempTable is true */
1.799 + sqlite3ExprCodeAndCache(pParse, pList->a[j].pExpr, regCols+i);
1.800 + }
1.801 + }
1.802 + regRec = sqlite3GetTempReg(pParse);
1.803 + sqlite3VdbeAddOp3(v, OP_MakeRecord, regCols, pTab->nCol, regRec);
1.804 +
1.805 + /* If this is an INSERT on a view with an INSTEAD OF INSERT trigger,
1.806 + ** do not attempt any conversions before assembling the record.
1.807 + ** If this is a real table, attempt conversions as required by the
1.808 + ** table column affinities.
1.809 + */
1.810 + if( !isView ){
1.811 + sqlite3TableAffinityStr(v, pTab);
1.812 + }
1.813 + sqlite3VdbeAddOp3(v, OP_Insert, newIdx, regRec, regRowid);
1.814 + sqlite3ReleaseTempReg(pParse, regRec);
1.815 + sqlite3ReleaseTempReg(pParse, regRowid);
1.816 + sqlite3ReleaseTempRange(pParse, regCols, pTab->nCol);
1.817 +
1.818 + /* Fire BEFORE or INSTEAD OF triggers */
1.819 + if( sqlite3CodeRowTrigger(pParse, TK_INSERT, 0, TRIGGER_BEFORE, pTab,
1.820 + newIdx, -1, onError, endOfLoop, 0, 0) ){
1.821 + goto insert_cleanup;
1.822 + }
1.823 + }
1.824 +
1.825 + /* Push the record number for the new entry onto the stack. The
1.826 + ** record number is a randomly generate integer created by NewRowid
1.827 + ** except when the table has an INTEGER PRIMARY KEY column, in which
1.828 + ** case the record number is the same as that column.
1.829 + */
1.830 + if( !isView ){
1.831 + if( IsVirtual(pTab) ){
1.832 + /* The row that the VUpdate opcode will delete: none */
1.833 + sqlite3VdbeAddOp2(v, OP_Null, 0, regIns);
1.834 + }
1.835 + if( keyColumn>=0 ){
1.836 + if( useTempTable ){
1.837 + sqlite3VdbeAddOp3(v, OP_Column, srcTab, keyColumn, regRowid);
1.838 + }else if( pSelect ){
1.839 + sqlite3VdbeAddOp2(v, OP_SCopy, regFromSelect+keyColumn, regRowid);
1.840 + }else{
1.841 + VdbeOp *pOp;
1.842 + sqlite3ExprCode(pParse, pList->a[keyColumn].pExpr, regRowid);
1.843 + pOp = sqlite3VdbeGetOp(v, sqlite3VdbeCurrentAddr(v) - 1);
1.844 + if( pOp && pOp->opcode==OP_Null && !IsVirtual(pTab) ){
1.845 + appendFlag = 1;
1.846 + pOp->opcode = OP_NewRowid;
1.847 + pOp->p1 = baseCur;
1.848 + pOp->p2 = regRowid;
1.849 + pOp->p3 = regAutoinc;
1.850 + }
1.851 + }
1.852 + /* If the PRIMARY KEY expression is NULL, then use OP_NewRowid
1.853 + ** to generate a unique primary key value.
1.854 + */
1.855 + if( !appendFlag ){
1.856 + int j1;
1.857 + if( !IsVirtual(pTab) ){
1.858 + j1 = sqlite3VdbeAddOp1(v, OP_NotNull, regRowid);
1.859 + sqlite3VdbeAddOp3(v, OP_NewRowid, baseCur, regRowid, regAutoinc);
1.860 + sqlite3VdbeJumpHere(v, j1);
1.861 + }else{
1.862 + j1 = sqlite3VdbeCurrentAddr(v);
1.863 + sqlite3VdbeAddOp2(v, OP_IsNull, regRowid, j1+2);
1.864 + }
1.865 + sqlite3VdbeAddOp1(v, OP_MustBeInt, regRowid);
1.866 + }
1.867 + }else if( IsVirtual(pTab) ){
1.868 + sqlite3VdbeAddOp2(v, OP_Null, 0, regRowid);
1.869 + }else{
1.870 + sqlite3VdbeAddOp3(v, OP_NewRowid, baseCur, regRowid, regAutoinc);
1.871 + appendFlag = 1;
1.872 + }
1.873 + autoIncStep(pParse, regAutoinc, regRowid);
1.874 +
1.875 + /* Push onto the stack, data for all columns of the new entry, beginning
1.876 + ** with the first column.
1.877 + */
1.878 + nHidden = 0;
1.879 + for(i=0; i<pTab->nCol; i++){
1.880 + int iRegStore = regRowid+1+i;
1.881 + if( i==pTab->iPKey ){
1.882 + /* The value of the INTEGER PRIMARY KEY column is always a NULL.
1.883 + ** Whenever this column is read, the record number will be substituted
1.884 + ** in its place. So will fill this column with a NULL to avoid
1.885 + ** taking up data space with information that will never be used. */
1.886 + sqlite3VdbeAddOp2(v, OP_Null, 0, iRegStore);
1.887 + continue;
1.888 + }
1.889 + if( pColumn==0 ){
1.890 + if( IsHiddenColumn(&pTab->aCol[i]) ){
1.891 + assert( IsVirtual(pTab) );
1.892 + j = -1;
1.893 + nHidden++;
1.894 + }else{
1.895 + j = i - nHidden;
1.896 + }
1.897 + }else{
1.898 + for(j=0; j<pColumn->nId; j++){
1.899 + if( pColumn->a[j].idx==i ) break;
1.900 + }
1.901 + }
1.902 + if( j<0 || nColumn==0 || (pColumn && j>=pColumn->nId) ){
1.903 + sqlite3ExprCode(pParse, pTab->aCol[i].pDflt, iRegStore);
1.904 + }else if( useTempTable ){
1.905 + sqlite3VdbeAddOp3(v, OP_Column, srcTab, j, iRegStore);
1.906 + }else if( pSelect ){
1.907 + sqlite3VdbeAddOp2(v, OP_SCopy, regFromSelect+j, iRegStore);
1.908 + }else{
1.909 + sqlite3ExprCode(pParse, pList->a[j].pExpr, iRegStore);
1.910 + }
1.911 + }
1.912 +
1.913 + /* Generate code to check constraints and generate index keys and
1.914 + ** do the insertion.
1.915 + */
1.916 +#ifndef SQLITE_OMIT_VIRTUALTABLE
1.917 + if( IsVirtual(pTab) ){
1.918 + sqlite3VtabMakeWritable(pParse, pTab);
1.919 + sqlite3VdbeAddOp4(v, OP_VUpdate, 1, pTab->nCol+2, regIns,
1.920 + (const char*)pTab->pVtab, P4_VTAB);
1.921 + }else
1.922 +#endif
1.923 + {
1.924 + sqlite3GenerateConstraintChecks(
1.925 + pParse,
1.926 + pTab,
1.927 + baseCur,
1.928 + regIns,
1.929 + aRegIdx,
1.930 + keyColumn>=0,
1.931 + 0,
1.932 + onError,
1.933 + endOfLoop
1.934 + );
1.935 + sqlite3CompleteInsertion(
1.936 + pParse,
1.937 + pTab,
1.938 + baseCur,
1.939 + regIns,
1.940 + aRegIdx,
1.941 + 0,
1.942 + 0,
1.943 + (triggers_exist & TRIGGER_AFTER)!=0 ? newIdx : -1,
1.944 + appendFlag
1.945 + );
1.946 + }
1.947 + }
1.948 +
1.949 + /* Update the count of rows that are inserted
1.950 + */
1.951 + if( (db->flags & SQLITE_CountRows)!=0 ){
1.952 + sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1);
1.953 + }
1.954 +
1.955 + if( triggers_exist ){
1.956 + /* Code AFTER triggers */
1.957 + if( sqlite3CodeRowTrigger(pParse, TK_INSERT, 0, TRIGGER_AFTER, pTab,
1.958 + newIdx, -1, onError, endOfLoop, 0, 0) ){
1.959 + goto insert_cleanup;
1.960 + }
1.961 + }
1.962 +
1.963 + /* The bottom of the main insertion loop, if the data source
1.964 + ** is a SELECT statement.
1.965 + */
1.966 + sqlite3VdbeResolveLabel(v, endOfLoop);
1.967 + if( useTempTable ){
1.968 + sqlite3VdbeAddOp2(v, OP_Next, srcTab, addrCont);
1.969 + sqlite3VdbeJumpHere(v, addrInsTop);
1.970 + sqlite3VdbeAddOp1(v, OP_Close, srcTab);
1.971 + }else if( pSelect ){
1.972 + sqlite3VdbeAddOp2(v, OP_Goto, 0, addrCont);
1.973 + sqlite3VdbeJumpHere(v, addrInsTop);
1.974 + }
1.975 +
1.976 + if( !IsVirtual(pTab) && !isView ){
1.977 + /* Close all tables opened */
1.978 + sqlite3VdbeAddOp1(v, OP_Close, baseCur);
1.979 + for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
1.980 + sqlite3VdbeAddOp1(v, OP_Close, idx+baseCur);
1.981 + }
1.982 + }
1.983 +
1.984 + /* Update the sqlite_sequence table by storing the content of the
1.985 + ** counter value in memory regAutoinc back into the sqlite_sequence
1.986 + ** table.
1.987 + */
1.988 + autoIncEnd(pParse, iDb, pTab, regAutoinc);
1.989 +
1.990 + /*
1.991 + ** Return the number of rows inserted. If this routine is
1.992 + ** generating code because of a call to sqlite3NestedParse(), do not
1.993 + ** invoke the callback function.
1.994 + */
1.995 + if( db->flags & SQLITE_CountRows && pParse->nested==0 && !pParse->trigStack ){
1.996 + sqlite3VdbeAddOp2(v, OP_ResultRow, regRowCount, 1);
1.997 + sqlite3VdbeSetNumCols(v, 1);
1.998 + sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows inserted", P4_STATIC);
1.999 + }
1.1000 +
1.1001 +insert_cleanup:
1.1002 + sqlite3SrcListDelete(db, pTabList);
1.1003 + sqlite3ExprListDelete(db, pList);
1.1004 + sqlite3SelectDelete(db, pSelect);
1.1005 + sqlite3IdListDelete(db, pColumn);
1.1006 + sqlite3DbFree(db, aRegIdx);
1.1007 +}
1.1008 +
1.1009 +/*
1.1010 +** Generate code to do constraint checks prior to an INSERT or an UPDATE.
1.1011 +**
1.1012 +** The input is a range of consecutive registers as follows:
1.1013 +**
1.1014 +** 1. The rowid of the row to be updated before the update. This
1.1015 +** value is omitted unless we are doing an UPDATE that involves a
1.1016 +** change to the record number or writing to a virtual table.
1.1017 +**
1.1018 +** 2. The rowid of the row after the update.
1.1019 +**
1.1020 +** 3. The data in the first column of the entry after the update.
1.1021 +**
1.1022 +** i. Data from middle columns...
1.1023 +**
1.1024 +** N. The data in the last column of the entry after the update.
1.1025 +**
1.1026 +** The regRowid parameter is the index of the register containing (2).
1.1027 +**
1.1028 +** The old rowid shown as entry (1) above is omitted unless both isUpdate
1.1029 +** and rowidChng are 1. isUpdate is true for UPDATEs and false for
1.1030 +** INSERTs. RowidChng means that the new rowid is explicitly specified by
1.1031 +** the update or insert statement. If rowidChng is false, it means that
1.1032 +** the rowid is computed automatically in an insert or that the rowid value
1.1033 +** is not modified by the update.
1.1034 +**
1.1035 +** The code generated by this routine store new index entries into
1.1036 +** registers identified by aRegIdx[]. No index entry is created for
1.1037 +** indices where aRegIdx[i]==0. The order of indices in aRegIdx[] is
1.1038 +** the same as the order of indices on the linked list of indices
1.1039 +** attached to the table.
1.1040 +**
1.1041 +** This routine also generates code to check constraints. NOT NULL,
1.1042 +** CHECK, and UNIQUE constraints are all checked. If a constraint fails,
1.1043 +** then the appropriate action is performed. There are five possible
1.1044 +** actions: ROLLBACK, ABORT, FAIL, REPLACE, and IGNORE.
1.1045 +**
1.1046 +** Constraint type Action What Happens
1.1047 +** --------------- ---------- ----------------------------------------
1.1048 +** any ROLLBACK The current transaction is rolled back and
1.1049 +** sqlite3_exec() returns immediately with a
1.1050 +** return code of SQLITE_CONSTRAINT.
1.1051 +**
1.1052 +** any ABORT Back out changes from the current command
1.1053 +** only (do not do a complete rollback) then
1.1054 +** cause sqlite3_exec() to return immediately
1.1055 +** with SQLITE_CONSTRAINT.
1.1056 +**
1.1057 +** any FAIL Sqlite_exec() returns immediately with a
1.1058 +** return code of SQLITE_CONSTRAINT. The
1.1059 +** transaction is not rolled back and any
1.1060 +** prior changes are retained.
1.1061 +**
1.1062 +** any IGNORE The record number and data is popped from
1.1063 +** the stack and there is an immediate jump
1.1064 +** to label ignoreDest.
1.1065 +**
1.1066 +** NOT NULL REPLACE The NULL value is replace by the default
1.1067 +** value for that column. If the default value
1.1068 +** is NULL, the action is the same as ABORT.
1.1069 +**
1.1070 +** UNIQUE REPLACE The other row that conflicts with the row
1.1071 +** being inserted is removed.
1.1072 +**
1.1073 +** CHECK REPLACE Illegal. The results in an exception.
1.1074 +**
1.1075 +** Which action to take is determined by the overrideError parameter.
1.1076 +** Or if overrideError==OE_Default, then the pParse->onError parameter
1.1077 +** is used. Or if pParse->onError==OE_Default then the onError value
1.1078 +** for the constraint is used.
1.1079 +**
1.1080 +** The calling routine must open a read/write cursor for pTab with
1.1081 +** cursor number "baseCur". All indices of pTab must also have open
1.1082 +** read/write cursors with cursor number baseCur+i for the i-th cursor.
1.1083 +** Except, if there is no possibility of a REPLACE action then
1.1084 +** cursors do not need to be open for indices where aRegIdx[i]==0.
1.1085 +*/
1.1086 +void sqlite3GenerateConstraintChecks(
1.1087 + Parse *pParse, /* The parser context */
1.1088 + Table *pTab, /* the table into which we are inserting */
1.1089 + int baseCur, /* Index of a read/write cursor pointing at pTab */
1.1090 + int regRowid, /* Index of the range of input registers */
1.1091 + int *aRegIdx, /* Register used by each index. 0 for unused indices */
1.1092 + int rowidChng, /* True if the rowid might collide with existing entry */
1.1093 + int isUpdate, /* True for UPDATE, False for INSERT */
1.1094 + int overrideError, /* Override onError to this if not OE_Default */
1.1095 + int ignoreDest /* Jump to this label on an OE_Ignore resolution */
1.1096 +){
1.1097 + int i;
1.1098 + Vdbe *v;
1.1099 + int nCol;
1.1100 + int onError;
1.1101 + int j1, j3; /* Addresses of jump instructions */
1.1102 + int j2 = 0; /* Initialized to placate warning */
1.1103 + int regData; /* Register containing first data column */
1.1104 + int iCur;
1.1105 + Index *pIdx;
1.1106 + int seenReplace = 0;
1.1107 + int hasTwoRowids = (isUpdate && rowidChng);
1.1108 +
1.1109 + v = sqlite3GetVdbe(pParse);
1.1110 + assert( v!=0 );
1.1111 + assert( pTab->pSelect==0 ); /* This table is not a VIEW */
1.1112 + nCol = pTab->nCol;
1.1113 + regData = regRowid + 1;
1.1114 +
1.1115 +
1.1116 + /* Test all NOT NULL constraints.
1.1117 + */
1.1118 + for(i=0; i<nCol; i++){
1.1119 + if( i==pTab->iPKey ){
1.1120 + continue;
1.1121 + }
1.1122 + onError = pTab->aCol[i].notNull;
1.1123 + if( onError==OE_None ) continue;
1.1124 + if( overrideError!=OE_Default ){
1.1125 + onError = overrideError;
1.1126 + }else if( onError==OE_Default ){
1.1127 + onError = OE_Abort;
1.1128 + }
1.1129 + if( onError==OE_Replace && pTab->aCol[i].pDflt==0 ){
1.1130 + onError = OE_Abort;
1.1131 + }
1.1132 + j1 = sqlite3VdbeAddOp1(v, OP_NotNull, regData+i);
1.1133 + assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail
1.1134 + || onError==OE_Ignore || onError==OE_Replace );
1.1135 + switch( onError ){
1.1136 + case OE_Rollback:
1.1137 + case OE_Abort:
1.1138 + case OE_Fail: {
1.1139 + char *zMsg;
1.1140 + sqlite3VdbeAddOp2(v, OP_Halt, SQLITE_CONSTRAINT, onError);
1.1141 + zMsg = sqlite3MPrintf(pParse->db, "%s.%s may not be NULL",
1.1142 + pTab->zName, pTab->aCol[i].zName);
1.1143 + sqlite3VdbeChangeP4(v, -1, zMsg, P4_DYNAMIC);
1.1144 + break;
1.1145 + }
1.1146 + case OE_Ignore: {
1.1147 + sqlite3VdbeAddOp2(v, OP_Goto, 0, ignoreDest);
1.1148 + break;
1.1149 + }
1.1150 + case OE_Replace: {
1.1151 + sqlite3ExprCode(pParse, pTab->aCol[i].pDflt, regData+i);
1.1152 + break;
1.1153 + }
1.1154 + }
1.1155 + sqlite3VdbeJumpHere(v, j1);
1.1156 + }
1.1157 +
1.1158 + /* Test all CHECK constraints
1.1159 + */
1.1160 +#ifndef SQLITE_OMIT_CHECK
1.1161 + if( pTab->pCheck && (pParse->db->flags & SQLITE_IgnoreChecks)==0 ){
1.1162 + int allOk = sqlite3VdbeMakeLabel(v);
1.1163 + pParse->ckBase = regData;
1.1164 + sqlite3ExprIfTrue(pParse, pTab->pCheck, allOk, SQLITE_JUMPIFNULL);
1.1165 + onError = overrideError!=OE_Default ? overrideError : OE_Abort;
1.1166 + if( onError==OE_Ignore ){
1.1167 + sqlite3VdbeAddOp2(v, OP_Goto, 0, ignoreDest);
1.1168 + }else{
1.1169 + sqlite3VdbeAddOp2(v, OP_Halt, SQLITE_CONSTRAINT, onError);
1.1170 + }
1.1171 + sqlite3VdbeResolveLabel(v, allOk);
1.1172 + }
1.1173 +#endif /* !defined(SQLITE_OMIT_CHECK) */
1.1174 +
1.1175 + /* If we have an INTEGER PRIMARY KEY, make sure the primary key
1.1176 + ** of the new record does not previously exist. Except, if this
1.1177 + ** is an UPDATE and the primary key is not changing, that is OK.
1.1178 + */
1.1179 + if( rowidChng ){
1.1180 + onError = pTab->keyConf;
1.1181 + if( overrideError!=OE_Default ){
1.1182 + onError = overrideError;
1.1183 + }else if( onError==OE_Default ){
1.1184 + onError = OE_Abort;
1.1185 + }
1.1186 +
1.1187 + if( onError!=OE_Replace || pTab->pIndex ){
1.1188 + if( isUpdate ){
1.1189 + j2 = sqlite3VdbeAddOp3(v, OP_Eq, regRowid, 0, regRowid-1);
1.1190 + }
1.1191 + j3 = sqlite3VdbeAddOp3(v, OP_NotExists, baseCur, 0, regRowid);
1.1192 + switch( onError ){
1.1193 + default: {
1.1194 + onError = OE_Abort;
1.1195 + /* Fall thru into the next case */
1.1196 + }
1.1197 + case OE_Rollback:
1.1198 + case OE_Abort:
1.1199 + case OE_Fail: {
1.1200 + sqlite3VdbeAddOp4(v, OP_Halt, SQLITE_CONSTRAINT, onError, 0,
1.1201 + "PRIMARY KEY must be unique", P4_STATIC);
1.1202 + break;
1.1203 + }
1.1204 + case OE_Replace: {
1.1205 + sqlite3GenerateRowIndexDelete(pParse, pTab, baseCur, 0);
1.1206 + seenReplace = 1;
1.1207 + break;
1.1208 + }
1.1209 + case OE_Ignore: {
1.1210 + assert( seenReplace==0 );
1.1211 + sqlite3VdbeAddOp2(v, OP_Goto, 0, ignoreDest);
1.1212 + break;
1.1213 + }
1.1214 + }
1.1215 + sqlite3VdbeJumpHere(v, j3);
1.1216 + if( isUpdate ){
1.1217 + sqlite3VdbeJumpHere(v, j2);
1.1218 + }
1.1219 + }
1.1220 + }
1.1221 +
1.1222 + /* Test all UNIQUE constraints by creating entries for each UNIQUE
1.1223 + ** index and making sure that duplicate entries do not already exist.
1.1224 + ** Add the new records to the indices as we go.
1.1225 + */
1.1226 + for(iCur=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, iCur++){
1.1227 + int regIdx;
1.1228 + int regR;
1.1229 +
1.1230 + if( aRegIdx[iCur]==0 ) continue; /* Skip unused indices */
1.1231 +
1.1232 + /* Create a key for accessing the index entry */
1.1233 + regIdx = sqlite3GetTempRange(pParse, pIdx->nColumn+1);
1.1234 + for(i=0; i<pIdx->nColumn; i++){
1.1235 + int idx = pIdx->aiColumn[i];
1.1236 + if( idx==pTab->iPKey ){
1.1237 + sqlite3VdbeAddOp2(v, OP_SCopy, regRowid, regIdx+i);
1.1238 + }else{
1.1239 + sqlite3VdbeAddOp2(v, OP_SCopy, regData+idx, regIdx+i);
1.1240 + }
1.1241 + }
1.1242 + sqlite3VdbeAddOp2(v, OP_SCopy, regRowid, regIdx+i);
1.1243 + sqlite3VdbeAddOp3(v, OP_MakeRecord, regIdx, pIdx->nColumn+1, aRegIdx[iCur]);
1.1244 + sqlite3IndexAffinityStr(v, pIdx);
1.1245 + sqlite3ExprCacheAffinityChange(pParse, regIdx, pIdx->nColumn+1);
1.1246 + sqlite3ReleaseTempRange(pParse, regIdx, pIdx->nColumn+1);
1.1247 +
1.1248 + /* Find out what action to take in case there is an indexing conflict */
1.1249 + onError = pIdx->onError;
1.1250 + if( onError==OE_None ) continue; /* pIdx is not a UNIQUE index */
1.1251 + if( overrideError!=OE_Default ){
1.1252 + onError = overrideError;
1.1253 + }else if( onError==OE_Default ){
1.1254 + onError = OE_Abort;
1.1255 + }
1.1256 + if( seenReplace ){
1.1257 + if( onError==OE_Ignore ) onError = OE_Replace;
1.1258 + else if( onError==OE_Fail ) onError = OE_Abort;
1.1259 + }
1.1260 +
1.1261 +
1.1262 + /* Check to see if the new index entry will be unique */
1.1263 + j2 = sqlite3VdbeAddOp3(v, OP_IsNull, regIdx, 0, pIdx->nColumn);
1.1264 + regR = sqlite3GetTempReg(pParse);
1.1265 + sqlite3VdbeAddOp2(v, OP_SCopy, regRowid-hasTwoRowids, regR);
1.1266 + j3 = sqlite3VdbeAddOp4(v, OP_IsUnique, baseCur+iCur+1, 0,
1.1267 + regR, SQLITE_INT_TO_PTR(aRegIdx[iCur]),
1.1268 + P4_INT32);
1.1269 +
1.1270 + /* Generate code that executes if the new index entry is not unique */
1.1271 + assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail
1.1272 + || onError==OE_Ignore || onError==OE_Replace );
1.1273 + switch( onError ){
1.1274 + case OE_Rollback:
1.1275 + case OE_Abort:
1.1276 + case OE_Fail: {
1.1277 + int j, n1, n2;
1.1278 + char zErrMsg[200];
1.1279 + sqlite3_snprintf(sizeof(zErrMsg), zErrMsg,
1.1280 + pIdx->nColumn>1 ? "columns " : "column ");
1.1281 + n1 = strlen(zErrMsg);
1.1282 + for(j=0; j<pIdx->nColumn && n1<sizeof(zErrMsg)-30; j++){
1.1283 + char *zCol = pTab->aCol[pIdx->aiColumn[j]].zName;
1.1284 + n2 = strlen(zCol);
1.1285 + if( j>0 ){
1.1286 + sqlite3_snprintf(sizeof(zErrMsg)-n1, &zErrMsg[n1], ", ");
1.1287 + n1 += 2;
1.1288 + }
1.1289 + if( n1+n2>sizeof(zErrMsg)-30 ){
1.1290 + sqlite3_snprintf(sizeof(zErrMsg)-n1, &zErrMsg[n1], "...");
1.1291 + n1 += 3;
1.1292 + break;
1.1293 + }else{
1.1294 + sqlite3_snprintf(sizeof(zErrMsg)-n1, &zErrMsg[n1], "%s", zCol);
1.1295 + n1 += n2;
1.1296 + }
1.1297 + }
1.1298 + sqlite3_snprintf(sizeof(zErrMsg)-n1, &zErrMsg[n1],
1.1299 + pIdx->nColumn>1 ? " are not unique" : " is not unique");
1.1300 + sqlite3VdbeAddOp4(v, OP_Halt, SQLITE_CONSTRAINT, onError, 0, zErrMsg,0);
1.1301 + break;
1.1302 + }
1.1303 + case OE_Ignore: {
1.1304 + assert( seenReplace==0 );
1.1305 + sqlite3VdbeAddOp2(v, OP_Goto, 0, ignoreDest);
1.1306 + break;
1.1307 + }
1.1308 + case OE_Replace: {
1.1309 + sqlite3GenerateRowDelete(pParse, pTab, baseCur, regR, 0);
1.1310 + seenReplace = 1;
1.1311 + break;
1.1312 + }
1.1313 + }
1.1314 + sqlite3VdbeJumpHere(v, j2);
1.1315 + sqlite3VdbeJumpHere(v, j3);
1.1316 + sqlite3ReleaseTempReg(pParse, regR);
1.1317 + }
1.1318 +}
1.1319 +
1.1320 +/*
1.1321 +** This routine generates code to finish the INSERT or UPDATE operation
1.1322 +** that was started by a prior call to sqlite3GenerateConstraintChecks.
1.1323 +** A consecutive range of registers starting at regRowid contains the
1.1324 +** rowid and the content to be inserted.
1.1325 +**
1.1326 +** The arguments to this routine should be the same as the first six
1.1327 +** arguments to sqlite3GenerateConstraintChecks.
1.1328 +*/
1.1329 +void sqlite3CompleteInsertion(
1.1330 + Parse *pParse, /* The parser context */
1.1331 + Table *pTab, /* the table into which we are inserting */
1.1332 + int baseCur, /* Index of a read/write cursor pointing at pTab */
1.1333 + int regRowid, /* Range of content */
1.1334 + int *aRegIdx, /* Register used by each index. 0 for unused indices */
1.1335 + int rowidChng, /* True if the record number will change */
1.1336 + int isUpdate, /* True for UPDATE, False for INSERT */
1.1337 + int newIdx, /* Index of NEW table for triggers. -1 if none */
1.1338 + int appendBias /* True if this is likely to be an append */
1.1339 +){
1.1340 + int i;
1.1341 + Vdbe *v;
1.1342 + int nIdx;
1.1343 + Index *pIdx;
1.1344 + int pik_flags;
1.1345 + int regData;
1.1346 + int regRec;
1.1347 +
1.1348 + v = sqlite3GetVdbe(pParse);
1.1349 + assert( v!=0 );
1.1350 + assert( pTab->pSelect==0 ); /* This table is not a VIEW */
1.1351 + for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){}
1.1352 + for(i=nIdx-1; i>=0; i--){
1.1353 + if( aRegIdx[i]==0 ) continue;
1.1354 + sqlite3VdbeAddOp2(v, OP_IdxInsert, baseCur+i+1, aRegIdx[i]);
1.1355 + }
1.1356 + regData = regRowid + 1;
1.1357 + regRec = sqlite3GetTempReg(pParse);
1.1358 + sqlite3VdbeAddOp3(v, OP_MakeRecord, regData, pTab->nCol, regRec);
1.1359 + sqlite3TableAffinityStr(v, pTab);
1.1360 + sqlite3ExprCacheAffinityChange(pParse, regData, pTab->nCol);
1.1361 +#ifndef SQLITE_OMIT_TRIGGER
1.1362 + if( newIdx>=0 ){
1.1363 + sqlite3VdbeAddOp3(v, OP_Insert, newIdx, regRec, regRowid);
1.1364 + }
1.1365 +#endif
1.1366 + if( pParse->nested ){
1.1367 + pik_flags = 0;
1.1368 + }else{
1.1369 + pik_flags = OPFLAG_NCHANGE;
1.1370 + pik_flags |= (isUpdate?OPFLAG_ISUPDATE:OPFLAG_LASTROWID);
1.1371 + }
1.1372 + if( appendBias ){
1.1373 + pik_flags |= OPFLAG_APPEND;
1.1374 + }
1.1375 + sqlite3VdbeAddOp3(v, OP_Insert, baseCur, regRec, regRowid);
1.1376 + if( !pParse->nested ){
1.1377 + sqlite3VdbeChangeP4(v, -1, pTab->zName, P4_STATIC);
1.1378 + }
1.1379 + sqlite3VdbeChangeP5(v, pik_flags);
1.1380 +}
1.1381 +
1.1382 +/*
1.1383 +** Generate code that will open cursors for a table and for all
1.1384 +** indices of that table. The "baseCur" parameter is the cursor number used
1.1385 +** for the table. Indices are opened on subsequent cursors.
1.1386 +**
1.1387 +** Return the number of indices on the table.
1.1388 +*/
1.1389 +int sqlite3OpenTableAndIndices(
1.1390 + Parse *pParse, /* Parsing context */
1.1391 + Table *pTab, /* Table to be opened */
1.1392 + int baseCur, /* Cursor number assigned to the table */
1.1393 + int op /* OP_OpenRead or OP_OpenWrite */
1.1394 +){
1.1395 + int i;
1.1396 + int iDb;
1.1397 + Index *pIdx;
1.1398 + Vdbe *v;
1.1399 +
1.1400 + if( IsVirtual(pTab) ) return 0;
1.1401 + iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema);
1.1402 + v = sqlite3GetVdbe(pParse);
1.1403 + assert( v!=0 );
1.1404 + sqlite3OpenTable(pParse, baseCur, iDb, pTab, op);
1.1405 + for(i=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
1.1406 + KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx);
1.1407 + assert( pIdx->pSchema==pTab->pSchema );
1.1408 + sqlite3VdbeAddOp4(v, op, i+baseCur, pIdx->tnum, iDb,
1.1409 + (char*)pKey, P4_KEYINFO_HANDOFF);
1.1410 + VdbeComment((v, "%s", pIdx->zName));
1.1411 + }
1.1412 + if( pParse->nTab<=baseCur+i ){
1.1413 + pParse->nTab = baseCur+i;
1.1414 + }
1.1415 + return i-1;
1.1416 +}
1.1417 +
1.1418 +
1.1419 +#ifdef SQLITE_TEST
1.1420 +/*
1.1421 +** The following global variable is incremented whenever the
1.1422 +** transfer optimization is used. This is used for testing
1.1423 +** purposes only - to make sure the transfer optimization really
1.1424 +** is happening when it is suppose to.
1.1425 +*/
1.1426 +int sqlite3_xferopt_count;
1.1427 +#endif /* SQLITE_TEST */
1.1428 +
1.1429 +
1.1430 +#ifndef SQLITE_OMIT_XFER_OPT
1.1431 +/*
1.1432 +** Check to collation names to see if they are compatible.
1.1433 +*/
1.1434 +static int xferCompatibleCollation(const char *z1, const char *z2){
1.1435 + if( z1==0 ){
1.1436 + return z2==0;
1.1437 + }
1.1438 + if( z2==0 ){
1.1439 + return 0;
1.1440 + }
1.1441 + return sqlite3StrICmp(z1, z2)==0;
1.1442 +}
1.1443 +
1.1444 +
1.1445 +/*
1.1446 +** Check to see if index pSrc is compatible as a source of data
1.1447 +** for index pDest in an insert transfer optimization. The rules
1.1448 +** for a compatible index:
1.1449 +**
1.1450 +** * The index is over the same set of columns
1.1451 +** * The same DESC and ASC markings occurs on all columns
1.1452 +** * The same onError processing (OE_Abort, OE_Ignore, etc)
1.1453 +** * The same collating sequence on each column
1.1454 +*/
1.1455 +static int xferCompatibleIndex(Index *pDest, Index *pSrc){
1.1456 + int i;
1.1457 + assert( pDest && pSrc );
1.1458 + assert( pDest->pTable!=pSrc->pTable );
1.1459 + if( pDest->nColumn!=pSrc->nColumn ){
1.1460 + return 0; /* Different number of columns */
1.1461 + }
1.1462 + if( pDest->onError!=pSrc->onError ){
1.1463 + return 0; /* Different conflict resolution strategies */
1.1464 + }
1.1465 + for(i=0; i<pSrc->nColumn; i++){
1.1466 + if( pSrc->aiColumn[i]!=pDest->aiColumn[i] ){
1.1467 + return 0; /* Different columns indexed */
1.1468 + }
1.1469 + if( pSrc->aSortOrder[i]!=pDest->aSortOrder[i] ){
1.1470 + return 0; /* Different sort orders */
1.1471 + }
1.1472 + if( pSrc->azColl[i]!=pDest->azColl[i] ){
1.1473 + return 0; /* Different collating sequences */
1.1474 + }
1.1475 + }
1.1476 +
1.1477 + /* If no test above fails then the indices must be compatible */
1.1478 + return 1;
1.1479 +}
1.1480 +
1.1481 +/*
1.1482 +** Attempt the transfer optimization on INSERTs of the form
1.1483 +**
1.1484 +** INSERT INTO tab1 SELECT * FROM tab2;
1.1485 +**
1.1486 +** This optimization is only attempted if
1.1487 +**
1.1488 +** (1) tab1 and tab2 have identical schemas including all the
1.1489 +** same indices and constraints
1.1490 +**
1.1491 +** (2) tab1 and tab2 are different tables
1.1492 +**
1.1493 +** (3) There must be no triggers on tab1
1.1494 +**
1.1495 +** (4) The result set of the SELECT statement is "*"
1.1496 +**
1.1497 +** (5) The SELECT statement has no WHERE, HAVING, ORDER BY, GROUP BY,
1.1498 +** or LIMIT clause.
1.1499 +**
1.1500 +** (6) The SELECT statement is a simple (not a compound) select that
1.1501 +** contains only tab2 in its FROM clause
1.1502 +**
1.1503 +** This method for implementing the INSERT transfers raw records from
1.1504 +** tab2 over to tab1. The columns are not decoded. Raw records from
1.1505 +** the indices of tab2 are transfered to tab1 as well. In so doing,
1.1506 +** the resulting tab1 has much less fragmentation.
1.1507 +**
1.1508 +** This routine returns TRUE if the optimization is attempted. If any
1.1509 +** of the conditions above fail so that the optimization should not
1.1510 +** be attempted, then this routine returns FALSE.
1.1511 +*/
1.1512 +static int xferOptimization(
1.1513 + Parse *pParse, /* Parser context */
1.1514 + Table *pDest, /* The table we are inserting into */
1.1515 + Select *pSelect, /* A SELECT statement to use as the data source */
1.1516 + int onError, /* How to handle constraint errors */
1.1517 + int iDbDest /* The database of pDest */
1.1518 +){
1.1519 + ExprList *pEList; /* The result set of the SELECT */
1.1520 + Table *pSrc; /* The table in the FROM clause of SELECT */
1.1521 + Index *pSrcIdx, *pDestIdx; /* Source and destination indices */
1.1522 + struct SrcList_item *pItem; /* An element of pSelect->pSrc */
1.1523 + int i; /* Loop counter */
1.1524 + int iDbSrc; /* The database of pSrc */
1.1525 + int iSrc, iDest; /* Cursors from source and destination */
1.1526 + int addr1, addr2; /* Loop addresses */
1.1527 + int emptyDestTest; /* Address of test for empty pDest */
1.1528 + int emptySrcTest; /* Address of test for empty pSrc */
1.1529 + Vdbe *v; /* The VDBE we are building */
1.1530 + KeyInfo *pKey; /* Key information for an index */
1.1531 + int regAutoinc; /* Memory register used by AUTOINC */
1.1532 + int destHasUniqueIdx = 0; /* True if pDest has a UNIQUE index */
1.1533 + int regData, regRowid; /* Registers holding data and rowid */
1.1534 +
1.1535 + if( pSelect==0 ){
1.1536 + return 0; /* Must be of the form INSERT INTO ... SELECT ... */
1.1537 + }
1.1538 + if( pDest->pTrigger ){
1.1539 + return 0; /* tab1 must not have triggers */
1.1540 + }
1.1541 +#ifndef SQLITE_OMIT_VIRTUALTABLE
1.1542 + if( pDest->isVirtual ){
1.1543 + return 0; /* tab1 must not be a virtual table */
1.1544 + }
1.1545 +#endif
1.1546 + if( onError==OE_Default ){
1.1547 + onError = OE_Abort;
1.1548 + }
1.1549 + if( onError!=OE_Abort && onError!=OE_Rollback ){
1.1550 + return 0; /* Cannot do OR REPLACE or OR IGNORE or OR FAIL */
1.1551 + }
1.1552 + assert(pSelect->pSrc); /* allocated even if there is no FROM clause */
1.1553 + if( pSelect->pSrc->nSrc!=1 ){
1.1554 + return 0; /* FROM clause must have exactly one term */
1.1555 + }
1.1556 + if( pSelect->pSrc->a[0].pSelect ){
1.1557 + return 0; /* FROM clause cannot contain a subquery */
1.1558 + }
1.1559 + if( pSelect->pWhere ){
1.1560 + return 0; /* SELECT may not have a WHERE clause */
1.1561 + }
1.1562 + if( pSelect->pOrderBy ){
1.1563 + return 0; /* SELECT may not have an ORDER BY clause */
1.1564 + }
1.1565 + /* Do not need to test for a HAVING clause. If HAVING is present but
1.1566 + ** there is no ORDER BY, we will get an error. */
1.1567 + if( pSelect->pGroupBy ){
1.1568 + return 0; /* SELECT may not have a GROUP BY clause */
1.1569 + }
1.1570 + if( pSelect->pLimit ){
1.1571 + return 0; /* SELECT may not have a LIMIT clause */
1.1572 + }
1.1573 + assert( pSelect->pOffset==0 ); /* Must be so if pLimit==0 */
1.1574 + if( pSelect->pPrior ){
1.1575 + return 0; /* SELECT may not be a compound query */
1.1576 + }
1.1577 + if( pSelect->isDistinct ){
1.1578 + return 0; /* SELECT may not be DISTINCT */
1.1579 + }
1.1580 + pEList = pSelect->pEList;
1.1581 + assert( pEList!=0 );
1.1582 + if( pEList->nExpr!=1 ){
1.1583 + return 0; /* The result set must have exactly one column */
1.1584 + }
1.1585 + assert( pEList->a[0].pExpr );
1.1586 + if( pEList->a[0].pExpr->op!=TK_ALL ){
1.1587 + return 0; /* The result set must be the special operator "*" */
1.1588 + }
1.1589 +
1.1590 + /* At this point we have established that the statement is of the
1.1591 + ** correct syntactic form to participate in this optimization. Now
1.1592 + ** we have to check the semantics.
1.1593 + */
1.1594 + pItem = pSelect->pSrc->a;
1.1595 + pSrc = sqlite3LocateTable(pParse, 0, pItem->zName, pItem->zDatabase);
1.1596 + if( pSrc==0 ){
1.1597 + return 0; /* FROM clause does not contain a real table */
1.1598 + }
1.1599 + if( pSrc==pDest ){
1.1600 + return 0; /* tab1 and tab2 may not be the same table */
1.1601 + }
1.1602 +#ifndef SQLITE_OMIT_VIRTUALTABLE
1.1603 + if( pSrc->isVirtual ){
1.1604 + return 0; /* tab2 must not be a virtual table */
1.1605 + }
1.1606 +#endif
1.1607 + if( pSrc->pSelect ){
1.1608 + return 0; /* tab2 may not be a view */
1.1609 + }
1.1610 + if( pDest->nCol!=pSrc->nCol ){
1.1611 + return 0; /* Number of columns must be the same in tab1 and tab2 */
1.1612 + }
1.1613 + if( pDest->iPKey!=pSrc->iPKey ){
1.1614 + return 0; /* Both tables must have the same INTEGER PRIMARY KEY */
1.1615 + }
1.1616 + for(i=0; i<pDest->nCol; i++){
1.1617 + if( pDest->aCol[i].affinity!=pSrc->aCol[i].affinity ){
1.1618 + return 0; /* Affinity must be the same on all columns */
1.1619 + }
1.1620 + if( !xferCompatibleCollation(pDest->aCol[i].zColl, pSrc->aCol[i].zColl) ){
1.1621 + return 0; /* Collating sequence must be the same on all columns */
1.1622 + }
1.1623 + if( pDest->aCol[i].notNull && !pSrc->aCol[i].notNull ){
1.1624 + return 0; /* tab2 must be NOT NULL if tab1 is */
1.1625 + }
1.1626 + }
1.1627 + for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){
1.1628 + if( pDestIdx->onError!=OE_None ){
1.1629 + destHasUniqueIdx = 1;
1.1630 + }
1.1631 + for(pSrcIdx=pSrc->pIndex; pSrcIdx; pSrcIdx=pSrcIdx->pNext){
1.1632 + if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break;
1.1633 + }
1.1634 + if( pSrcIdx==0 ){
1.1635 + return 0; /* pDestIdx has no corresponding index in pSrc */
1.1636 + }
1.1637 + }
1.1638 +#ifndef SQLITE_OMIT_CHECK
1.1639 + if( pDest->pCheck && !sqlite3ExprCompare(pSrc->pCheck, pDest->pCheck) ){
1.1640 + return 0; /* Tables have different CHECK constraints. Ticket #2252 */
1.1641 + }
1.1642 +#endif
1.1643 +
1.1644 + /* If we get this far, it means either:
1.1645 + **
1.1646 + ** * We can always do the transfer if the table contains an
1.1647 + ** an integer primary key
1.1648 + **
1.1649 + ** * We can conditionally do the transfer if the destination
1.1650 + ** table is empty.
1.1651 + */
1.1652 +#ifdef SQLITE_TEST
1.1653 + sqlite3_xferopt_count++;
1.1654 +#endif
1.1655 + iDbSrc = sqlite3SchemaToIndex(pParse->db, pSrc->pSchema);
1.1656 + v = sqlite3GetVdbe(pParse);
1.1657 + sqlite3CodeVerifySchema(pParse, iDbSrc);
1.1658 + iSrc = pParse->nTab++;
1.1659 + iDest = pParse->nTab++;
1.1660 + regAutoinc = autoIncBegin(pParse, iDbDest, pDest);
1.1661 + sqlite3OpenTable(pParse, iDest, iDbDest, pDest, OP_OpenWrite);
1.1662 + if( (pDest->iPKey<0 && pDest->pIndex!=0) || destHasUniqueIdx ){
1.1663 + /* If tables do not have an INTEGER PRIMARY KEY and there
1.1664 + ** are indices to be copied and the destination is not empty,
1.1665 + ** we have to disallow the transfer optimization because the
1.1666 + ** the rowids might change which will mess up indexing.
1.1667 + **
1.1668 + ** Or if the destination has a UNIQUE index and is not empty,
1.1669 + ** we also disallow the transfer optimization because we cannot
1.1670 + ** insure that all entries in the union of DEST and SRC will be
1.1671 + ** unique.
1.1672 + */
1.1673 + addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iDest, 0);
1.1674 + emptyDestTest = sqlite3VdbeAddOp2(v, OP_Goto, 0, 0);
1.1675 + sqlite3VdbeJumpHere(v, addr1);
1.1676 + }else{
1.1677 + emptyDestTest = 0;
1.1678 + }
1.1679 + sqlite3OpenTable(pParse, iSrc, iDbSrc, pSrc, OP_OpenRead);
1.1680 + emptySrcTest = sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0);
1.1681 + regData = sqlite3GetTempReg(pParse);
1.1682 + regRowid = sqlite3GetTempReg(pParse);
1.1683 + if( pDest->iPKey>=0 ){
1.1684 + addr1 = sqlite3VdbeAddOp2(v, OP_Rowid, iSrc, regRowid);
1.1685 + addr2 = sqlite3VdbeAddOp3(v, OP_NotExists, iDest, 0, regRowid);
1.1686 + sqlite3VdbeAddOp4(v, OP_Halt, SQLITE_CONSTRAINT, onError, 0,
1.1687 + "PRIMARY KEY must be unique", P4_STATIC);
1.1688 + sqlite3VdbeJumpHere(v, addr2);
1.1689 + autoIncStep(pParse, regAutoinc, regRowid);
1.1690 + }else if( pDest->pIndex==0 ){
1.1691 + addr1 = sqlite3VdbeAddOp2(v, OP_NewRowid, iDest, regRowid);
1.1692 + }else{
1.1693 + addr1 = sqlite3VdbeAddOp2(v, OP_Rowid, iSrc, regRowid);
1.1694 + assert( pDest->autoInc==0 );
1.1695 + }
1.1696 + sqlite3VdbeAddOp2(v, OP_RowData, iSrc, regData);
1.1697 + sqlite3VdbeAddOp3(v, OP_Insert, iDest, regData, regRowid);
1.1698 + sqlite3VdbeChangeP5(v, OPFLAG_NCHANGE|OPFLAG_LASTROWID|OPFLAG_APPEND);
1.1699 + sqlite3VdbeChangeP4(v, -1, pDest->zName, 0);
1.1700 + sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1);
1.1701 + autoIncEnd(pParse, iDbDest, pDest, regAutoinc);
1.1702 + for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){
1.1703 + for(pSrcIdx=pSrc->pIndex; pSrcIdx; pSrcIdx=pSrcIdx->pNext){
1.1704 + if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break;
1.1705 + }
1.1706 + assert( pSrcIdx );
1.1707 + sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0);
1.1708 + sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);
1.1709 + pKey = sqlite3IndexKeyinfo(pParse, pSrcIdx);
1.1710 + sqlite3VdbeAddOp4(v, OP_OpenRead, iSrc, pSrcIdx->tnum, iDbSrc,
1.1711 + (char*)pKey, P4_KEYINFO_HANDOFF);
1.1712 + VdbeComment((v, "%s", pSrcIdx->zName));
1.1713 + pKey = sqlite3IndexKeyinfo(pParse, pDestIdx);
1.1714 + sqlite3VdbeAddOp4(v, OP_OpenWrite, iDest, pDestIdx->tnum, iDbDest,
1.1715 + (char*)pKey, P4_KEYINFO_HANDOFF);
1.1716 + VdbeComment((v, "%s", pDestIdx->zName));
1.1717 + addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0);
1.1718 + sqlite3VdbeAddOp2(v, OP_RowKey, iSrc, regData);
1.1719 + sqlite3VdbeAddOp3(v, OP_IdxInsert, iDest, regData, 1);
1.1720 + sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1+1);
1.1721 + sqlite3VdbeJumpHere(v, addr1);
1.1722 + }
1.1723 + sqlite3VdbeJumpHere(v, emptySrcTest);
1.1724 + sqlite3ReleaseTempReg(pParse, regRowid);
1.1725 + sqlite3ReleaseTempReg(pParse, regData);
1.1726 + sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0);
1.1727 + sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);
1.1728 + if( emptyDestTest ){
1.1729 + sqlite3VdbeAddOp2(v, OP_Halt, SQLITE_OK, 0);
1.1730 + sqlite3VdbeJumpHere(v, emptyDestTest);
1.1731 + sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);
1.1732 + return 0;
1.1733 + }else{
1.1734 + return 1;
1.1735 + }
1.1736 +}
1.1737 +#endif /* SQLITE_OMIT_XFER_OPT */
1.1738 +
1.1739 +/* Make sure "isView" gets undefined in case this file becomes part of
1.1740 +** the amalgamation - so that subsequent files do not see isView as a
1.1741 +** macro. */
1.1742 +#undef isView