4 ** The author disclaims copyright to this source code. In place of
5 ** a legal notice, here is a blessing:
7 ** May you do good and not evil.
8 ** May you find forgiveness for yourself and forgive others.
9 ** May you share freely, never taking more than you give.
11 *************************************************************************
12 ** This file contains C code routines that are called by the parser
13 ** to handle INSERT statements in SQLite.
15 #include "sqliteInt.h"
18 ** Generate code that will
20 ** (1) acquire a lock for table pTab then
21 ** (2) open pTab as cursor iCur.
23 ** If pTab is a WITHOUT ROWID table, then it is the PRIMARY KEY index
24 ** for that table that is actually opened.
26 void sqlite3OpenTable(
27 Parse
*pParse
, /* Generate code into this VDBE */
28 int iCur
, /* The cursor number of the table */
29 int iDb
, /* The database index in sqlite3.aDb[] */
30 Table
*pTab
, /* The table to be opened */
31 int opcode
/* OP_OpenRead or OP_OpenWrite */
34 assert( !IsVirtual(pTab
) );
35 v
= sqlite3GetVdbe(pParse
);
36 assert( opcode
==OP_OpenWrite
|| opcode
==OP_OpenRead
);
37 sqlite3TableLock(pParse
, iDb
, pTab
->tnum
,
38 (opcode
==OP_OpenWrite
)?1:0, pTab
->zName
);
40 sqlite3VdbeAddOp4Int(v
, opcode
, iCur
, pTab
->tnum
, iDb
, pTab
->nCol
);
41 VdbeComment((v
, "%s", pTab
->zName
));
43 Index
*pPk
= sqlite3PrimaryKeyIndex(pTab
);
45 assert( pPk
->tnum
==pTab
->tnum
);
46 sqlite3VdbeAddOp3(v
, opcode
, iCur
, pPk
->tnum
, iDb
);
47 sqlite3VdbeSetP4KeyInfo(pParse
, pPk
);
48 VdbeComment((v
, "%s", pTab
->zName
));
53 ** Return a pointer to the column affinity string associated with index
54 ** pIdx. A column affinity string has one character for each column in
55 ** the table, according to the affinity of the column:
57 ** Character Column affinity
58 ** ------------------------------
65 ** An extra 'D' is appended to the end of the string to cover the
66 ** rowid that appears as the last column in every index.
68 ** Memory for the buffer containing the column index affinity string
69 ** is managed along with the rest of the Index structure. It will be
70 ** released when sqlite3DeleteIndex() is called.
72 const char *sqlite3IndexAffinityStr(sqlite3
*db
, Index
*pIdx
){
74 /* The first time a column affinity string for a particular index is
75 ** required, it is allocated and populated here. It is then stored as
76 ** a member of the Index structure for subsequent use.
78 ** The column affinity string will eventually be deleted by
79 ** sqliteDeleteIndex() when the Index structure itself is cleaned
83 Table
*pTab
= pIdx
->pTable
;
84 pIdx
->zColAff
= (char *)sqlite3DbMallocRaw(0, pIdx
->nColumn
+1);
89 for(n
=0; n
<pIdx
->nColumn
; n
++){
90 i16 x
= pIdx
->aiColumn
[n
];
92 pIdx
->zColAff
[n
] = pTab
->aCol
[x
].affinity
;
93 }else if( x
==XN_ROWID
){
94 pIdx
->zColAff
[n
] = SQLITE_AFF_INTEGER
;
98 assert( pIdx
->aColExpr
!=0 );
99 aff
= sqlite3ExprAffinity(pIdx
->aColExpr
->a
[n
].pExpr
);
100 if( aff
==0 ) aff
= SQLITE_AFF_BLOB
;
101 pIdx
->zColAff
[n
] = aff
;
104 pIdx
->zColAff
[n
] = 0;
107 return pIdx
->zColAff
;
111 ** Compute the affinity string for table pTab, if it has not already been
112 ** computed. As an optimization, omit trailing SQLITE_AFF_BLOB affinities.
114 ** If the affinity exists (if it is no entirely SQLITE_AFF_BLOB values) and
115 ** if iReg>0 then code an OP_Affinity opcode that will set the affinities
116 ** for register iReg and following. Or if affinities exists and iReg==0,
117 ** then just set the P4 operand of the previous opcode (which should be
118 ** an OP_MakeRecord) to the affinity string.
120 ** A column affinity string has one character per column:
122 ** Character Column affinity
123 ** ------------------------------
130 void sqlite3TableAffinity(Vdbe
*v
, Table
*pTab
, int iReg
){
132 char *zColAff
= pTab
->zColAff
;
134 sqlite3
*db
= sqlite3VdbeDb(v
);
135 zColAff
= (char *)sqlite3DbMallocRaw(0, pTab
->nCol
+1);
141 for(i
=0; i
<pTab
->nCol
; i
++){
142 zColAff
[i
] = pTab
->aCol
[i
].affinity
;
146 }while( i
>=0 && zColAff
[i
]==SQLITE_AFF_BLOB
);
147 pTab
->zColAff
= zColAff
;
149 i
= sqlite3Strlen30(zColAff
);
152 sqlite3VdbeAddOp4(v
, OP_Affinity
, iReg
, i
, 0, zColAff
, i
);
154 sqlite3VdbeChangeP4(v
, -1, zColAff
, i
);
160 ** Return non-zero if the table pTab in database iDb or any of its indices
161 ** have been opened at any point in the VDBE program. This is used to see if
162 ** a statement of the form "INSERT INTO <iDb, pTab> SELECT ..." can
163 ** run without using a temporary table for the results of the SELECT.
165 static int readsTable(Parse
*p
, int iDb
, Table
*pTab
){
166 Vdbe
*v
= sqlite3GetVdbe(p
);
168 int iEnd
= sqlite3VdbeCurrentAddr(v
);
169 #ifndef SQLITE_OMIT_VIRTUALTABLE
170 VTable
*pVTab
= IsVirtual(pTab
) ? sqlite3GetVTable(p
->db
, pTab
) : 0;
173 for(i
=1; i
<iEnd
; i
++){
174 VdbeOp
*pOp
= sqlite3VdbeGetOp(v
, i
);
176 if( pOp
->opcode
==OP_OpenRead
&& pOp
->p3
==iDb
){
179 if( tnum
==pTab
->tnum
){
182 for(pIndex
=pTab
->pIndex
; pIndex
; pIndex
=pIndex
->pNext
){
183 if( tnum
==pIndex
->tnum
){
188 #ifndef SQLITE_OMIT_VIRTUALTABLE
189 if( pOp
->opcode
==OP_VOpen
&& pOp
->p4
.pVtab
==pVTab
){
190 assert( pOp
->p4
.pVtab
!=0 );
191 assert( pOp
->p4type
==P4_VTAB
);
199 #ifndef SQLITE_OMIT_AUTOINCREMENT
201 ** Locate or create an AutoincInfo structure associated with table pTab
202 ** which is in database iDb. Return the register number for the register
203 ** that holds the maximum rowid.
205 ** There is at most one AutoincInfo structure per table even if the
206 ** same table is autoincremented multiple times due to inserts within
207 ** triggers. A new AutoincInfo structure is created if this is the
208 ** first use of table pTab. On 2nd and subsequent uses, the original
209 ** AutoincInfo structure is used.
211 ** Three memory locations are allocated:
213 ** (1) Register to hold the name of the pTab table.
214 ** (2) Register to hold the maximum ROWID of pTab.
215 ** (3) Register to hold the rowid in sqlite_sequence of pTab
217 ** The 2nd register is the one that is returned. That is all the
218 ** insert routine needs to know about.
220 static int autoIncBegin(
221 Parse
*pParse
, /* Parsing context */
222 int iDb
, /* Index of the database holding pTab */
223 Table
*pTab
/* The table we are writing to */
225 int memId
= 0; /* Register holding maximum rowid */
226 if( pTab
->tabFlags
& TF_Autoincrement
){
227 Parse
*pToplevel
= sqlite3ParseToplevel(pParse
);
230 pInfo
= pToplevel
->pAinc
;
231 while( pInfo
&& pInfo
->pTab
!=pTab
){ pInfo
= pInfo
->pNext
; }
233 pInfo
= sqlite3DbMallocRawNN(pParse
->db
, sizeof(*pInfo
));
234 if( pInfo
==0 ) return 0;
235 pInfo
->pNext
= pToplevel
->pAinc
;
236 pToplevel
->pAinc
= pInfo
;
239 pToplevel
->nMem
++; /* Register to hold name of table */
240 pInfo
->regCtr
= ++pToplevel
->nMem
; /* Max rowid register */
241 pToplevel
->nMem
++; /* Rowid in sqlite_sequence */
243 memId
= pInfo
->regCtr
;
249 ** This routine generates code that will initialize all of the
250 ** register used by the autoincrement tracker.
252 void sqlite3AutoincrementBegin(Parse
*pParse
){
253 AutoincInfo
*p
; /* Information about an AUTOINCREMENT */
254 sqlite3
*db
= pParse
->db
; /* The database connection */
255 Db
*pDb
; /* Database only autoinc table */
256 int memId
; /* Register holding max rowid */
257 Vdbe
*v
= pParse
->pVdbe
; /* VDBE under construction */
259 /* This routine is never called during trigger-generation. It is
260 ** only called from the top-level */
261 assert( pParse
->pTriggerTab
==0 );
262 assert( sqlite3IsToplevel(pParse
) );
264 assert( v
); /* We failed long ago if this is not so */
265 for(p
= pParse
->pAinc
; p
; p
= p
->pNext
){
266 static const int iLn
= VDBE_OFFSET_LINENO(2);
267 static const VdbeOpList autoInc
[] = {
268 /* 0 */ {OP_Null
, 0, 0, 0},
269 /* 1 */ {OP_Rewind
, 0, 9, 0},
270 /* 2 */ {OP_Column
, 0, 0, 0},
271 /* 3 */ {OP_Ne
, 0, 7, 0},
272 /* 4 */ {OP_Rowid
, 0, 0, 0},
273 /* 5 */ {OP_Column
, 0, 1, 0},
274 /* 6 */ {OP_Goto
, 0, 9, 0},
275 /* 7 */ {OP_Next
, 0, 2, 0},
276 /* 8 */ {OP_Integer
, 0, 0, 0},
277 /* 9 */ {OP_Close
, 0, 0, 0}
280 pDb
= &db
->aDb
[p
->iDb
];
282 assert( sqlite3SchemaMutexHeld(db
, 0, pDb
->pSchema
) );
283 sqlite3OpenTable(pParse
, 0, p
->iDb
, pDb
->pSchema
->pSeqTab
, OP_OpenRead
);
284 sqlite3VdbeLoadString(v
, memId
-1, p
->pTab
->zName
);
285 aOp
= sqlite3VdbeAddOpList(v
, ArraySize(autoInc
), autoInc
, iLn
);
292 aOp
[3].p5
= SQLITE_JUMPIFNULL
;
300 ** Update the maximum rowid for an autoincrement calculation.
302 ** This routine should be called when the regRowid register holds a
303 ** new rowid that is about to be inserted. If that new rowid is
304 ** larger than the maximum rowid in the memId memory cell, then the
305 ** memory cell is updated.
307 static void autoIncStep(Parse
*pParse
, int memId
, int regRowid
){
309 sqlite3VdbeAddOp2(pParse
->pVdbe
, OP_MemMax
, memId
, regRowid
);
314 ** This routine generates the code needed to write autoincrement
315 ** maximum rowid values back into the sqlite_sequence register.
316 ** Every statement that might do an INSERT into an autoincrement
317 ** table (either directly or through triggers) needs to call this
318 ** routine just before the "exit" code.
320 static SQLITE_NOINLINE
void autoIncrementEnd(Parse
*pParse
){
322 Vdbe
*v
= pParse
->pVdbe
;
323 sqlite3
*db
= pParse
->db
;
326 for(p
= pParse
->pAinc
; p
; p
= p
->pNext
){
327 static const int iLn
= VDBE_OFFSET_LINENO(2);
328 static const VdbeOpList autoIncEnd
[] = {
329 /* 0 */ {OP_NotNull
, 0, 2, 0},
330 /* 1 */ {OP_NewRowid
, 0, 0, 0},
331 /* 2 */ {OP_MakeRecord
, 0, 2, 0},
332 /* 3 */ {OP_Insert
, 0, 0, 0},
333 /* 4 */ {OP_Close
, 0, 0, 0}
336 Db
*pDb
= &db
->aDb
[p
->iDb
];
338 int memId
= p
->regCtr
;
340 iRec
= sqlite3GetTempReg(pParse
);
341 assert( sqlite3SchemaMutexHeld(db
, 0, pDb
->pSchema
) );
342 sqlite3OpenTable(pParse
, 0, p
->iDb
, pDb
->pSchema
->pSeqTab
, OP_OpenWrite
);
343 aOp
= sqlite3VdbeAddOpList(v
, ArraySize(autoIncEnd
), autoIncEnd
, iLn
);
351 aOp
[3].p5
= OPFLAG_APPEND
;
352 sqlite3ReleaseTempReg(pParse
, iRec
);
355 void sqlite3AutoincrementEnd(Parse
*pParse
){
356 if( pParse
->pAinc
) autoIncrementEnd(pParse
);
360 ** If SQLITE_OMIT_AUTOINCREMENT is defined, then the three routines
361 ** above are all no-ops
363 # define autoIncBegin(A,B,C) (0)
364 # define autoIncStep(A,B,C)
365 #endif /* SQLITE_OMIT_AUTOINCREMENT */
368 /* Forward declaration */
369 static int xferOptimization(
370 Parse
*pParse
, /* Parser context */
371 Table
*pDest
, /* The table we are inserting into */
372 Select
*pSelect
, /* A SELECT statement to use as the data source */
373 int onError
, /* How to handle constraint errors */
374 int iDbDest
/* The database of pDest */
378 ** This routine is called to handle SQL of the following forms:
380 ** insert into TABLE (IDLIST) values(EXPRLIST),(EXPRLIST),...
381 ** insert into TABLE (IDLIST) select
382 ** insert into TABLE (IDLIST) default values
384 ** The IDLIST following the table name is always optional. If omitted,
385 ** then a list of all (non-hidden) columns for the table is substituted.
386 ** The IDLIST appears in the pColumn parameter. pColumn is NULL if IDLIST
389 ** For the pSelect parameter holds the values to be inserted for the
390 ** first two forms shown above. A VALUES clause is really just short-hand
391 ** for a SELECT statement that omits the FROM clause and everything else
392 ** that follows. If the pSelect parameter is NULL, that means that the
393 ** DEFAULT VALUES form of the INSERT statement is intended.
395 ** The code generated follows one of four templates. For a simple
396 ** insert with data coming from a single-row VALUES clause, the code executes
397 ** once straight down through. Pseudo-code follows (we call this
398 ** the "1st template"):
400 ** open write cursor to <table> and its indices
401 ** put VALUES clause expressions into registers
402 ** write the resulting record into <table>
405 ** The three remaining templates assume the statement is of the form
407 ** INSERT INTO <table> SELECT ...
409 ** If the SELECT clause is of the restricted form "SELECT * FROM <table2>" -
410 ** in other words if the SELECT pulls all columns from a single table
411 ** and there is no WHERE or LIMIT or GROUP BY or ORDER BY clauses, and
412 ** if <table2> and <table1> are distinct tables but have identical
413 ** schemas, including all the same indices, then a special optimization
414 ** is invoked that copies raw records from <table2> over to <table1>.
415 ** See the xferOptimization() function for the implementation of this
416 ** template. This is the 2nd template.
418 ** open a write cursor to <table>
419 ** open read cursor on <table2>
420 ** transfer all records in <table2> over to <table>
422 ** foreach index on <table>
423 ** open a write cursor on the <table> index
424 ** open a read cursor on the corresponding <table2> index
425 ** transfer all records from the read to the write cursors
429 ** The 3rd template is for when the second template does not apply
430 ** and the SELECT clause does not read from <table> at any time.
431 ** The generated code follows this template:
435 ** A: setup for the SELECT
436 ** loop over the rows in the SELECT
437 ** load values into registers R..R+n
440 ** cleanup after the SELECT
442 ** B: open write cursor to <table> and its indices
443 ** C: yield X, at EOF goto D
444 ** insert the select result into <table> from R..R+n
448 ** The 4th template is used if the insert statement takes its
449 ** values from a SELECT but the data is being inserted into a table
450 ** that is also read as part of the SELECT. In the third form,
451 ** we have to use an intermediate table to store the results of
452 ** the select. The template is like this:
456 ** A: setup for the SELECT
457 ** loop over the tables in the SELECT
458 ** load value into register R..R+n
461 ** cleanup after the SELECT
463 ** B: open temp table
464 ** L: yield X, at EOF goto M
465 ** insert row from R..R+n into temp table
467 ** M: open write cursor to <table> and its indices
469 ** C: loop over rows of intermediate table
470 ** transfer values form intermediate table into <table>
475 Parse
*pParse
, /* Parser context */
476 SrcList
*pTabList
, /* Name of table into which we are inserting */
477 Select
*pSelect
, /* A SELECT statement to use as the data source */
478 IdList
*pColumn
, /* Column names corresponding to IDLIST. */
479 int onError
/* How to handle constraint errors */
481 sqlite3
*db
; /* The main database structure */
482 Table
*pTab
; /* The table to insert into. aka TABLE */
483 char *zTab
; /* Name of the table into which we are inserting */
484 const char *zDb
; /* Name of the database holding this table */
485 int i
, j
, idx
; /* Loop counters */
486 Vdbe
*v
; /* Generate code into this virtual machine */
487 Index
*pIdx
; /* For looping over indices of the table */
488 int nColumn
; /* Number of columns in the data */
489 int nHidden
= 0; /* Number of hidden columns if TABLE is virtual */
490 int iDataCur
= 0; /* VDBE cursor that is the main data repository */
491 int iIdxCur
= 0; /* First index cursor */
492 int ipkColumn
= -1; /* Column that is the INTEGER PRIMARY KEY */
493 int endOfLoop
; /* Label for the end of the insertion loop */
494 int srcTab
= 0; /* Data comes from this temporary cursor if >=0 */
495 int addrInsTop
= 0; /* Jump to label "D" */
496 int addrCont
= 0; /* Top of insert loop. Label "C" in templates 3 and 4 */
497 SelectDest dest
; /* Destination for SELECT on rhs of INSERT */
498 int iDb
; /* Index of database holding TABLE */
499 Db
*pDb
; /* The database containing table being inserted into */
500 u8 useTempTable
= 0; /* Store SELECT results in intermediate table */
501 u8 appendFlag
= 0; /* True if the insert is likely to be an append */
502 u8 withoutRowid
; /* 0 for normal table. 1 for WITHOUT ROWID table */
503 u8 bIdListInOrder
; /* True if IDLIST is in table order */
504 ExprList
*pList
= 0; /* List of VALUES() to be inserted */
506 /* Register allocations */
507 int regFromSelect
= 0;/* Base register for data coming from SELECT */
508 int regAutoinc
= 0; /* Register holding the AUTOINCREMENT counter */
509 int regRowCount
= 0; /* Memory cell used for the row counter */
510 int regIns
; /* Block of regs holding rowid+data being inserted */
511 int regRowid
; /* registers holding insert rowid */
512 int regData
; /* register holding first column to insert */
513 int *aRegIdx
= 0; /* One register allocated to each index */
515 #ifndef SQLITE_OMIT_TRIGGER
516 int isView
; /* True if attempting to insert into a view */
517 Trigger
*pTrigger
; /* List of triggers on pTab, if required */
518 int tmask
; /* Mask of trigger times */
522 memset(&dest
, 0, sizeof(dest
));
523 if( pParse
->nErr
|| db
->mallocFailed
){
527 /* If the Select object is really just a simple VALUES() list with a
528 ** single row (the common case) then keep that one row of values
529 ** and discard the other (unused) parts of the pSelect object
531 if( pSelect
&& (pSelect
->selFlags
& SF_Values
)!=0 && pSelect
->pPrior
==0 ){
532 pList
= pSelect
->pEList
;
534 sqlite3SelectDelete(db
, pSelect
);
538 /* Locate the table into which we will be inserting new information.
540 assert( pTabList
->nSrc
==1 );
541 zTab
= pTabList
->a
[0].zName
;
542 if( NEVER(zTab
==0) ) goto insert_cleanup
;
543 pTab
= sqlite3SrcListLookup(pParse
, pTabList
);
547 iDb
= sqlite3SchemaToIndex(db
, pTab
->pSchema
);
548 assert( iDb
<db
->nDb
);
551 if( sqlite3AuthCheck(pParse
, SQLITE_INSERT
, pTab
->zName
, 0, zDb
) ){
554 withoutRowid
= !HasRowid(pTab
);
556 /* Figure out if we have any triggers and if the table being
557 ** inserted into is a view
559 #ifndef SQLITE_OMIT_TRIGGER
560 pTrigger
= sqlite3TriggersExist(pParse
, pTab
, TK_INSERT
, 0, &tmask
);
561 isView
= pTab
->pSelect
!=0;
567 #ifdef SQLITE_OMIT_VIEW
571 assert( (pTrigger
&& tmask
) || (pTrigger
==0 && tmask
==0) );
573 /* If pTab is really a view, make sure it has been initialized.
574 ** ViewGetColumnNames() is a no-op if pTab is not a view.
576 if( sqlite3ViewGetColumnNames(pParse
, pTab
) ){
580 /* Cannot insert into a read-only table.
582 if( sqlite3IsReadOnly(pParse
, pTab
, tmask
) ){
588 v
= sqlite3GetVdbe(pParse
);
589 if( v
==0 ) goto insert_cleanup
;
590 if( pParse
->nested
==0 ) sqlite3VdbeCountChanges(v
);
591 sqlite3BeginWriteOperation(pParse
, pSelect
|| pTrigger
, iDb
);
593 #ifndef SQLITE_OMIT_XFER_OPT
594 /* If the statement is of the form
596 ** INSERT INTO <table1> SELECT * FROM <table2>;
598 ** Then special optimizations can be applied that make the transfer
599 ** very fast and which reduce fragmentation of indices.
601 ** This is the 2nd template.
603 if( pColumn
==0 && xferOptimization(pParse
, pTab
, pSelect
, onError
, iDb
) ){
608 #endif /* SQLITE_OMIT_XFER_OPT */
610 /* If this is an AUTOINCREMENT table, look up the sequence number in the
611 ** sqlite_sequence table and store it in memory cell regAutoinc.
613 regAutoinc
= autoIncBegin(pParse
, iDb
, pTab
);
615 /* Allocate registers for holding the rowid of the new row,
616 ** the content of the new row, and the assembled row record.
618 regRowid
= regIns
= pParse
->nMem
+1;
619 pParse
->nMem
+= pTab
->nCol
+ 1;
620 if( IsVirtual(pTab
) ){
624 regData
= regRowid
+1;
626 /* If the INSERT statement included an IDLIST term, then make sure
627 ** all elements of the IDLIST really are columns of the table and
628 ** remember the column indices.
630 ** If the table has an INTEGER PRIMARY KEY column and that column
631 ** is named in the IDLIST, then record in the ipkColumn variable
632 ** the index into IDLIST of the primary key column. ipkColumn is
633 ** the index of the primary key as it appears in IDLIST, not as
634 ** is appears in the original table. (The index of the INTEGER
635 ** PRIMARY KEY in the original table is pTab->iPKey.)
637 bIdListInOrder
= (pTab
->tabFlags
& TF_OOOHidden
)==0;
639 for(i
=0; i
<pColumn
->nId
; i
++){
640 pColumn
->a
[i
].idx
= -1;
642 for(i
=0; i
<pColumn
->nId
; i
++){
643 for(j
=0; j
<pTab
->nCol
; j
++){
644 if( sqlite3StrICmp(pColumn
->a
[i
].zName
, pTab
->aCol
[j
].zName
)==0 ){
645 pColumn
->a
[i
].idx
= j
;
646 if( i
!=j
) bIdListInOrder
= 0;
647 if( j
==pTab
->iPKey
){
648 ipkColumn
= i
; assert( !withoutRowid
);
654 if( sqlite3IsRowid(pColumn
->a
[i
].zName
) && !withoutRowid
){
658 sqlite3ErrorMsg(pParse
, "table %S has no column named %s",
659 pTabList
, 0, pColumn
->a
[i
].zName
);
660 pParse
->checkSchema
= 1;
667 /* Figure out how many columns of data are supplied. If the data
668 ** is coming from a SELECT statement, then generate a co-routine that
669 ** produces a single row of the SELECT on each invocation. The
670 ** co-routine is the common header to the 3rd and 4th templates.
673 /* Data is coming from a SELECT or from a multi-row VALUES clause.
674 ** Generate a co-routine to run the SELECT. */
675 int regYield
; /* Register holding co-routine entry-point */
676 int addrTop
; /* Top of the co-routine */
677 int rc
; /* Result code */
679 regYield
= ++pParse
->nMem
;
680 addrTop
= sqlite3VdbeCurrentAddr(v
) + 1;
681 sqlite3VdbeAddOp3(v
, OP_InitCoroutine
, regYield
, 0, addrTop
);
682 sqlite3SelectDestInit(&dest
, SRT_Coroutine
, regYield
);
683 dest
.iSdst
= bIdListInOrder
? regData
: 0;
684 dest
.nSdst
= pTab
->nCol
;
685 rc
= sqlite3Select(pParse
, pSelect
, &dest
);
686 regFromSelect
= dest
.iSdst
;
687 if( rc
|| db
->mallocFailed
|| pParse
->nErr
) goto insert_cleanup
;
688 sqlite3VdbeEndCoroutine(v
, regYield
);
689 sqlite3VdbeJumpHere(v
, addrTop
- 1); /* label B: */
690 assert( pSelect
->pEList
);
691 nColumn
= pSelect
->pEList
->nExpr
;
693 /* Set useTempTable to TRUE if the result of the SELECT statement
694 ** should be written into a temporary table (template 4). Set to
695 ** FALSE if each output row of the SELECT can be written directly into
696 ** the destination table (template 3).
698 ** A temp table must be used if the table being updated is also one
699 ** of the tables being read by the SELECT statement. Also use a
700 ** temp table in the case of row triggers.
702 if( pTrigger
|| readsTable(pParse
, iDb
, pTab
) ){
707 /* Invoke the coroutine to extract information from the SELECT
708 ** and add it to a transient table srcTab. The code generated
709 ** here is from the 4th template:
711 ** B: open temp table
712 ** L: yield X, goto M at EOF
713 ** insert row from R..R+n into temp table
717 int regRec
; /* Register to hold packed record */
718 int regTempRowid
; /* Register to hold temp table ROWID */
719 int addrL
; /* Label "L" */
721 srcTab
= pParse
->nTab
++;
722 regRec
= sqlite3GetTempReg(pParse
);
723 regTempRowid
= sqlite3GetTempReg(pParse
);
724 sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, srcTab
, nColumn
);
725 addrL
= sqlite3VdbeAddOp1(v
, OP_Yield
, dest
.iSDParm
); VdbeCoverage(v
);
726 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regFromSelect
, nColumn
, regRec
);
727 sqlite3VdbeAddOp2(v
, OP_NewRowid
, srcTab
, regTempRowid
);
728 sqlite3VdbeAddOp3(v
, OP_Insert
, srcTab
, regRec
, regTempRowid
);
729 sqlite3VdbeGoto(v
, addrL
);
730 sqlite3VdbeJumpHere(v
, addrL
);
731 sqlite3ReleaseTempReg(pParse
, regRec
);
732 sqlite3ReleaseTempReg(pParse
, regTempRowid
);
735 /* This is the case if the data for the INSERT is coming from a
736 ** single-row VALUES clause
739 memset(&sNC
, 0, sizeof(sNC
));
742 assert( useTempTable
==0 );
744 nColumn
= pList
->nExpr
;
745 if( sqlite3ResolveExprListNames(&sNC
, pList
) ){
753 /* If there is no IDLIST term but the table has an integer primary
754 ** key, the set the ipkColumn variable to the integer primary key
755 ** column index in the original table definition.
757 if( pColumn
==0 && nColumn
>0 ){
758 ipkColumn
= pTab
->iPKey
;
761 /* Make sure the number of columns in the source data matches the number
762 ** of columns to be inserted into the table.
764 for(i
=0; i
<pTab
->nCol
; i
++){
765 nHidden
+= (IsHiddenColumn(&pTab
->aCol
[i
]) ? 1 : 0);
767 if( pColumn
==0 && nColumn
&& nColumn
!=(pTab
->nCol
-nHidden
) ){
768 sqlite3ErrorMsg(pParse
,
769 "table %S has %d columns but %d values were supplied",
770 pTabList
, 0, pTab
->nCol
-nHidden
, nColumn
);
773 if( pColumn
!=0 && nColumn
!=pColumn
->nId
){
774 sqlite3ErrorMsg(pParse
, "%d values for %d columns", nColumn
, pColumn
->nId
);
778 /* Initialize the count of rows to be inserted
780 if( db
->flags
& SQLITE_CountRows
){
781 regRowCount
= ++pParse
->nMem
;
782 sqlite3VdbeAddOp2(v
, OP_Integer
, 0, regRowCount
);
785 /* If this is not a view, open the table and and all indices */
788 nIdx
= sqlite3OpenTableAndIndices(pParse
, pTab
, OP_OpenWrite
, 0, -1, 0,
789 &iDataCur
, &iIdxCur
);
790 aRegIdx
= sqlite3DbMallocRawNN(db
, sizeof(int)*(nIdx
+1));
794 for(i
=0; i
<nIdx
; i
++){
795 aRegIdx
[i
] = ++pParse
->nMem
;
799 /* This is the top of the main insertion loop */
801 /* This block codes the top of loop only. The complete loop is the
802 ** following pseudocode (template 4):
804 ** rewind temp table, if empty goto D
805 ** C: loop over rows of intermediate table
806 ** transfer values form intermediate table into <table>
810 addrInsTop
= sqlite3VdbeAddOp1(v
, OP_Rewind
, srcTab
); VdbeCoverage(v
);
811 addrCont
= sqlite3VdbeCurrentAddr(v
);
813 /* This block codes the top of loop only. The complete loop is the
814 ** following pseudocode (template 3):
816 ** C: yield X, at EOF goto D
817 ** insert the select result into <table> from R..R+n
821 addrInsTop
= addrCont
= sqlite3VdbeAddOp1(v
, OP_Yield
, dest
.iSDParm
);
825 /* Run the BEFORE and INSTEAD OF triggers, if there are any
827 endOfLoop
= sqlite3VdbeMakeLabel(v
);
828 if( tmask
& TRIGGER_BEFORE
){
829 int regCols
= sqlite3GetTempRange(pParse
, pTab
->nCol
+1);
831 /* build the NEW.* reference row. Note that if there is an INTEGER
832 ** PRIMARY KEY into which a NULL is being inserted, that NULL will be
833 ** translated into a unique ID for the row. But on a BEFORE trigger,
834 ** we do not know what the unique ID will be (because the insert has
835 ** not happened yet) so we substitute a rowid of -1
838 sqlite3VdbeAddOp2(v
, OP_Integer
, -1, regCols
);
841 assert( !withoutRowid
);
843 sqlite3VdbeAddOp3(v
, OP_Column
, srcTab
, ipkColumn
, regCols
);
845 assert( pSelect
==0 ); /* Otherwise useTempTable is true */
846 sqlite3ExprCode(pParse
, pList
->a
[ipkColumn
].pExpr
, regCols
);
848 addr1
= sqlite3VdbeAddOp1(v
, OP_NotNull
, regCols
); VdbeCoverage(v
);
849 sqlite3VdbeAddOp2(v
, OP_Integer
, -1, regCols
);
850 sqlite3VdbeJumpHere(v
, addr1
);
851 sqlite3VdbeAddOp1(v
, OP_MustBeInt
, regCols
); VdbeCoverage(v
);
854 /* Cannot have triggers on a virtual table. If it were possible,
855 ** this block would have to account for hidden column.
857 assert( !IsVirtual(pTab
) );
859 /* Create the new column data
861 for(i
=j
=0; i
<pTab
->nCol
; i
++){
863 for(j
=0; j
<pColumn
->nId
; j
++){
864 if( pColumn
->a
[j
].idx
==i
) break;
867 if( (!useTempTable
&& !pList
) || (pColumn
&& j
>=pColumn
->nId
)
868 || (pColumn
==0 && IsOrdinaryHiddenColumn(&pTab
->aCol
[i
])) ){
869 sqlite3ExprCode(pParse
, pTab
->aCol
[i
].pDflt
, regCols
+i
+1);
870 }else if( useTempTable
){
871 sqlite3VdbeAddOp3(v
, OP_Column
, srcTab
, j
, regCols
+i
+1);
873 assert( pSelect
==0 ); /* Otherwise useTempTable is true */
874 sqlite3ExprCodeAndCache(pParse
, pList
->a
[j
].pExpr
, regCols
+i
+1);
876 if( pColumn
==0 && !IsOrdinaryHiddenColumn(&pTab
->aCol
[i
]) ) j
++;
879 /* If this is an INSERT on a view with an INSTEAD OF INSERT trigger,
880 ** do not attempt any conversions before assembling the record.
881 ** If this is a real table, attempt conversions as required by the
882 ** table column affinities.
885 sqlite3TableAffinity(v
, pTab
, regCols
+1);
888 /* Fire BEFORE or INSTEAD OF triggers */
889 sqlite3CodeRowTrigger(pParse
, pTrigger
, TK_INSERT
, 0, TRIGGER_BEFORE
,
890 pTab
, regCols
-pTab
->nCol
-1, onError
, endOfLoop
);
892 sqlite3ReleaseTempRange(pParse
, regCols
, pTab
->nCol
+1);
895 /* Compute the content of the next row to insert into a range of
896 ** registers beginning at regIns.
899 if( IsVirtual(pTab
) ){
900 /* The row that the VUpdate opcode will delete: none */
901 sqlite3VdbeAddOp2(v
, OP_Null
, 0, regIns
);
905 sqlite3VdbeAddOp3(v
, OP_Column
, srcTab
, ipkColumn
, regRowid
);
907 sqlite3VdbeAddOp2(v
, OP_Copy
, regFromSelect
+ipkColumn
, regRowid
);
910 sqlite3ExprCode(pParse
, pList
->a
[ipkColumn
].pExpr
, regRowid
);
911 pOp
= sqlite3VdbeGetOp(v
, -1);
912 if( ALWAYS(pOp
) && pOp
->opcode
==OP_Null
&& !IsVirtual(pTab
) ){
914 pOp
->opcode
= OP_NewRowid
;
917 pOp
->p3
= regAutoinc
;
920 /* If the PRIMARY KEY expression is NULL, then use OP_NewRowid
921 ** to generate a unique primary key value.
925 if( !IsVirtual(pTab
) ){
926 addr1
= sqlite3VdbeAddOp1(v
, OP_NotNull
, regRowid
); VdbeCoverage(v
);
927 sqlite3VdbeAddOp3(v
, OP_NewRowid
, iDataCur
, regRowid
, regAutoinc
);
928 sqlite3VdbeJumpHere(v
, addr1
);
930 addr1
= sqlite3VdbeCurrentAddr(v
);
931 sqlite3VdbeAddOp2(v
, OP_IsNull
, regRowid
, addr1
+2); VdbeCoverage(v
);
933 sqlite3VdbeAddOp1(v
, OP_MustBeInt
, regRowid
); VdbeCoverage(v
);
935 }else if( IsVirtual(pTab
) || withoutRowid
){
936 sqlite3VdbeAddOp2(v
, OP_Null
, 0, regRowid
);
938 sqlite3VdbeAddOp3(v
, OP_NewRowid
, iDataCur
, regRowid
, regAutoinc
);
941 autoIncStep(pParse
, regAutoinc
, regRowid
);
943 /* Compute data for all columns of the new entry, beginning
944 ** with the first column.
947 for(i
=0; i
<pTab
->nCol
; i
++){
948 int iRegStore
= regRowid
+1+i
;
949 if( i
==pTab
->iPKey
){
950 /* The value of the INTEGER PRIMARY KEY column is always a NULL.
951 ** Whenever this column is read, the rowid will be substituted
952 ** in its place. Hence, fill this column with a NULL to avoid
953 ** taking up data space with information that will never be used.
954 ** As there may be shallow copies of this value, make it a soft-NULL */
955 sqlite3VdbeAddOp1(v
, OP_SoftNull
, iRegStore
);
959 if( IsHiddenColumn(&pTab
->aCol
[i
]) ){
966 for(j
=0; j
<pColumn
->nId
; j
++){
967 if( pColumn
->a
[j
].idx
==i
) break;
970 if( j
<0 || nColumn
==0 || (pColumn
&& j
>=pColumn
->nId
) ){
971 sqlite3ExprCodeFactorable(pParse
, pTab
->aCol
[i
].pDflt
, iRegStore
);
972 }else if( useTempTable
){
973 sqlite3VdbeAddOp3(v
, OP_Column
, srcTab
, j
, iRegStore
);
975 if( regFromSelect
!=regData
){
976 sqlite3VdbeAddOp2(v
, OP_SCopy
, regFromSelect
+j
, iRegStore
);
979 sqlite3ExprCode(pParse
, pList
->a
[j
].pExpr
, iRegStore
);
983 /* Generate code to check constraints and generate index keys and
986 #ifndef SQLITE_OMIT_VIRTUALTABLE
987 if( IsVirtual(pTab
) ){
988 const char *pVTab
= (const char *)sqlite3GetVTable(db
, pTab
);
989 sqlite3VtabMakeWritable(pParse
, pTab
);
990 sqlite3VdbeAddOp4(v
, OP_VUpdate
, 1, pTab
->nCol
+2, regIns
, pVTab
, P4_VTAB
);
991 sqlite3VdbeChangeP5(v
, onError
==OE_Default
? OE_Abort
: onError
);
992 sqlite3MayAbort(pParse
);
996 int isReplace
; /* Set to true if constraints may cause a replace */
997 sqlite3GenerateConstraintChecks(pParse
, pTab
, aRegIdx
, iDataCur
, iIdxCur
,
998 regIns
, 0, ipkColumn
>=0, onError
, endOfLoop
, &isReplace
, 0
1000 sqlite3FkCheck(pParse
, pTab
, 0, regIns
, 0, 0);
1001 sqlite3CompleteInsertion(pParse
, pTab
, iDataCur
, iIdxCur
,
1002 regIns
, aRegIdx
, 0, appendFlag
, isReplace
==0);
1006 /* Update the count of rows that are inserted
1008 if( (db
->flags
& SQLITE_CountRows
)!=0 ){
1009 sqlite3VdbeAddOp2(v
, OP_AddImm
, regRowCount
, 1);
1013 /* Code AFTER triggers */
1014 sqlite3CodeRowTrigger(pParse
, pTrigger
, TK_INSERT
, 0, TRIGGER_AFTER
,
1015 pTab
, regData
-2-pTab
->nCol
, onError
, endOfLoop
);
1018 /* The bottom of the main insertion loop, if the data source
1019 ** is a SELECT statement.
1021 sqlite3VdbeResolveLabel(v
, endOfLoop
);
1023 sqlite3VdbeAddOp2(v
, OP_Next
, srcTab
, addrCont
); VdbeCoverage(v
);
1024 sqlite3VdbeJumpHere(v
, addrInsTop
);
1025 sqlite3VdbeAddOp1(v
, OP_Close
, srcTab
);
1026 }else if( pSelect
){
1027 sqlite3VdbeGoto(v
, addrCont
);
1028 sqlite3VdbeJumpHere(v
, addrInsTop
);
1031 if( !IsVirtual(pTab
) && !isView
){
1032 /* Close all tables opened */
1033 if( iDataCur
<iIdxCur
) sqlite3VdbeAddOp1(v
, OP_Close
, iDataCur
);
1034 for(idx
=0, pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
, idx
++){
1035 sqlite3VdbeAddOp1(v
, OP_Close
, idx
+iIdxCur
);
1040 /* Update the sqlite_sequence table by storing the content of the
1041 ** maximum rowid counter values recorded while inserting into
1042 ** autoincrement tables.
1044 if( pParse
->nested
==0 && pParse
->pTriggerTab
==0 ){
1045 sqlite3AutoincrementEnd(pParse
);
1049 ** Return the number of rows inserted. If this routine is
1050 ** generating code because of a call to sqlite3NestedParse(), do not
1051 ** invoke the callback function.
1053 if( (db
->flags
&SQLITE_CountRows
) && !pParse
->nested
&& !pParse
->pTriggerTab
){
1054 sqlite3VdbeAddOp2(v
, OP_ResultRow
, regRowCount
, 1);
1055 sqlite3VdbeSetNumCols(v
, 1);
1056 sqlite3VdbeSetColName(v
, 0, COLNAME_NAME
, "rows inserted", SQLITE_STATIC
);
1060 sqlite3SrcListDelete(db
, pTabList
);
1061 sqlite3ExprListDelete(db
, pList
);
1062 sqlite3SelectDelete(db
, pSelect
);
1063 sqlite3IdListDelete(db
, pColumn
);
1064 sqlite3DbFree(db
, aRegIdx
);
1067 /* Make sure "isView" and other macros defined above are undefined. Otherwise
1068 ** they may interfere with compilation of other functions in this file
1069 ** (or in another file, if this file becomes part of the amalgamation). */
1081 ** Meanings of bits in of pWalker->eCode for checkConstraintUnchanged()
1083 #define CKCNSTRNT_COLUMN 0x01 /* CHECK constraint uses a changing column */
1084 #define CKCNSTRNT_ROWID 0x02 /* CHECK constraint references the ROWID */
1086 /* This is the Walker callback from checkConstraintUnchanged(). Set
1087 ** bit 0x01 of pWalker->eCode if
1088 ** pWalker->eCode to 0 if this expression node references any of the
1089 ** columns that are being modifed by an UPDATE statement.
1091 static int checkConstraintExprNode(Walker
*pWalker
, Expr
*pExpr
){
1092 if( pExpr
->op
==TK_COLUMN
){
1093 assert( pExpr
->iColumn
>=0 || pExpr
->iColumn
==-1 );
1094 if( pExpr
->iColumn
>=0 ){
1095 if( pWalker
->u
.aiCol
[pExpr
->iColumn
]>=0 ){
1096 pWalker
->eCode
|= CKCNSTRNT_COLUMN
;
1099 pWalker
->eCode
|= CKCNSTRNT_ROWID
;
1102 return WRC_Continue
;
1106 ** pExpr is a CHECK constraint on a row that is being UPDATE-ed. The
1107 ** only columns that are modified by the UPDATE are those for which
1108 ** aiChng[i]>=0, and also the ROWID is modified if chngRowid is true.
1110 ** Return true if CHECK constraint pExpr does not use any of the
1111 ** changing columns (or the rowid if it is changing). In other words,
1112 ** return true if this CHECK constraint can be skipped when validating
1113 ** the new row in the UPDATE statement.
1115 static int checkConstraintUnchanged(Expr
*pExpr
, int *aiChng
, int chngRowid
){
1117 memset(&w
, 0, sizeof(w
));
1119 w
.xExprCallback
= checkConstraintExprNode
;
1121 sqlite3WalkExpr(&w
, pExpr
);
1123 testcase( (w
.eCode
& CKCNSTRNT_ROWID
)!=0 );
1124 w
.eCode
&= ~CKCNSTRNT_ROWID
;
1126 testcase( w
.eCode
==0 );
1127 testcase( w
.eCode
==CKCNSTRNT_COLUMN
);
1128 testcase( w
.eCode
==CKCNSTRNT_ROWID
);
1129 testcase( w
.eCode
==(CKCNSTRNT_ROWID
|CKCNSTRNT_COLUMN
) );
1134 ** Generate code to do constraint checks prior to an INSERT or an UPDATE
1137 ** The regNewData parameter is the first register in a range that contains
1138 ** the data to be inserted or the data after the update. There will be
1139 ** pTab->nCol+1 registers in this range. The first register (the one
1140 ** that regNewData points to) will contain the new rowid, or NULL in the
1141 ** case of a WITHOUT ROWID table. The second register in the range will
1142 ** contain the content of the first table column. The third register will
1143 ** contain the content of the second table column. And so forth.
1145 ** The regOldData parameter is similar to regNewData except that it contains
1146 ** the data prior to an UPDATE rather than afterwards. regOldData is zero
1147 ** for an INSERT. This routine can distinguish between UPDATE and INSERT by
1148 ** checking regOldData for zero.
1150 ** For an UPDATE, the pkChng boolean is true if the true primary key (the
1151 ** rowid for a normal table or the PRIMARY KEY for a WITHOUT ROWID table)
1152 ** might be modified by the UPDATE. If pkChng is false, then the key of
1153 ** the iDataCur content table is guaranteed to be unchanged by the UPDATE.
1155 ** For an INSERT, the pkChng boolean indicates whether or not the rowid
1156 ** was explicitly specified as part of the INSERT statement. If pkChng
1157 ** is zero, it means that the either rowid is computed automatically or
1158 ** that the table is a WITHOUT ROWID table and has no rowid. On an INSERT,
1159 ** pkChng will only be true if the INSERT statement provides an integer
1160 ** value for either the rowid column or its INTEGER PRIMARY KEY alias.
1162 ** The code generated by this routine will store new index entries into
1163 ** registers identified by aRegIdx[]. No index entry is created for
1164 ** indices where aRegIdx[i]==0. The order of indices in aRegIdx[] is
1165 ** the same as the order of indices on the linked list of indices
1168 ** The caller must have already opened writeable cursors on the main
1169 ** table and all applicable indices (that is to say, all indices for which
1170 ** aRegIdx[] is not zero). iDataCur is the cursor for the main table when
1171 ** inserting or updating a rowid table, or the cursor for the PRIMARY KEY
1172 ** index when operating on a WITHOUT ROWID table. iIdxCur is the cursor
1173 ** for the first index in the pTab->pIndex list. Cursors for other indices
1174 ** are at iIdxCur+N for the N-th element of the pTab->pIndex list.
1176 ** This routine also generates code to check constraints. NOT NULL,
1177 ** CHECK, and UNIQUE constraints are all checked. If a constraint fails,
1178 ** then the appropriate action is performed. There are five possible
1179 ** actions: ROLLBACK, ABORT, FAIL, REPLACE, and IGNORE.
1181 ** Constraint type Action What Happens
1182 ** --------------- ---------- ----------------------------------------
1183 ** any ROLLBACK The current transaction is rolled back and
1184 ** sqlite3_step() returns immediately with a
1185 ** return code of SQLITE_CONSTRAINT.
1187 ** any ABORT Back out changes from the current command
1188 ** only (do not do a complete rollback) then
1189 ** cause sqlite3_step() to return immediately
1190 ** with SQLITE_CONSTRAINT.
1192 ** any FAIL Sqlite3_step() returns immediately with a
1193 ** return code of SQLITE_CONSTRAINT. The
1194 ** transaction is not rolled back and any
1195 ** changes to prior rows are retained.
1197 ** any IGNORE The attempt in insert or update the current
1198 ** row is skipped, without throwing an error.
1199 ** Processing continues with the next row.
1200 ** (There is an immediate jump to ignoreDest.)
1202 ** NOT NULL REPLACE The NULL value is replace by the default
1203 ** value for that column. If the default value
1204 ** is NULL, the action is the same as ABORT.
1206 ** UNIQUE REPLACE The other row that conflicts with the row
1207 ** being inserted is removed.
1209 ** CHECK REPLACE Illegal. The results in an exception.
1211 ** Which action to take is determined by the overrideError parameter.
1212 ** Or if overrideError==OE_Default, then the pParse->onError parameter
1213 ** is used. Or if pParse->onError==OE_Default then the onError value
1214 ** for the constraint is used.
1216 void sqlite3GenerateConstraintChecks(
1217 Parse
*pParse
, /* The parser context */
1218 Table
*pTab
, /* The table being inserted or updated */
1219 int *aRegIdx
, /* Use register aRegIdx[i] for index i. 0 for unused */
1220 int iDataCur
, /* Canonical data cursor (main table or PK index) */
1221 int iIdxCur
, /* First index cursor */
1222 int regNewData
, /* First register in a range holding values to insert */
1223 int regOldData
, /* Previous content. 0 for INSERTs */
1224 u8 pkChng
, /* Non-zero if the rowid or PRIMARY KEY changed */
1225 u8 overrideError
, /* Override onError to this if not OE_Default */
1226 int ignoreDest
, /* Jump to this label on an OE_Ignore resolution */
1227 int *pbMayReplace
, /* OUT: Set to true if constraint may cause a replace */
1228 int *aiChng
/* column i is unchanged if aiChng[i]<0 */
1230 Vdbe
*v
; /* VDBE under constrution */
1231 Index
*pIdx
; /* Pointer to one of the indices */
1232 Index
*pPk
= 0; /* The PRIMARY KEY index */
1233 sqlite3
*db
; /* Database connection */
1234 int i
; /* loop counter */
1235 int ix
; /* Index loop counter */
1236 int nCol
; /* Number of columns */
1237 int onError
; /* Conflict resolution strategy */
1238 int addr1
; /* Address of jump instruction */
1239 int seenReplace
= 0; /* True if REPLACE is used to resolve INT PK conflict */
1240 int nPkField
; /* Number of fields in PRIMARY KEY. 1 for ROWID tables */
1241 int ipkTop
= 0; /* Top of the rowid change constraint check */
1242 int ipkBottom
= 0; /* Bottom of the rowid change constraint check */
1243 u8 isUpdate
; /* True if this is an UPDATE operation */
1244 u8 bAffinityDone
= 0; /* True if the OP_Affinity operation has been run */
1245 int regRowid
= -1; /* Register holding ROWID value */
1247 isUpdate
= regOldData
!=0;
1249 v
= sqlite3GetVdbe(pParse
);
1251 assert( pTab
->pSelect
==0 ); /* This table is not a VIEW */
1254 /* pPk is the PRIMARY KEY index for WITHOUT ROWID tables and NULL for
1255 ** normal rowid tables. nPkField is the number of key fields in the
1256 ** pPk index or 1 for a rowid table. In other words, nPkField is the
1257 ** number of fields in the true primary key of the table. */
1258 if( HasRowid(pTab
) ){
1262 pPk
= sqlite3PrimaryKeyIndex(pTab
);
1263 nPkField
= pPk
->nKeyCol
;
1266 /* Record that this module has started */
1267 VdbeModuleComment((v
, "BEGIN: GenCnstCks(%d,%d,%d,%d,%d)",
1268 iDataCur
, iIdxCur
, regNewData
, regOldData
, pkChng
));
1270 /* Test all NOT NULL constraints.
1272 for(i
=0; i
<nCol
; i
++){
1273 if( i
==pTab
->iPKey
){
1274 continue; /* ROWID is never NULL */
1276 if( aiChng
&& aiChng
[i
]<0 ){
1277 /* Don't bother checking for NOT NULL on columns that do not change */
1280 onError
= pTab
->aCol
[i
].notNull
;
1281 if( onError
==OE_None
) continue; /* This column is allowed to be NULL */
1282 if( overrideError
!=OE_Default
){
1283 onError
= overrideError
;
1284 }else if( onError
==OE_Default
){
1287 if( onError
==OE_Replace
&& pTab
->aCol
[i
].pDflt
==0 ){
1290 assert( onError
==OE_Rollback
|| onError
==OE_Abort
|| onError
==OE_Fail
1291 || onError
==OE_Ignore
|| onError
==OE_Replace
);
1294 sqlite3MayAbort(pParse
);
1298 char *zMsg
= sqlite3MPrintf(db
, "%s.%s", pTab
->zName
,
1299 pTab
->aCol
[i
].zName
);
1300 sqlite3VdbeAddOp4(v
, OP_HaltIfNull
, SQLITE_CONSTRAINT_NOTNULL
, onError
,
1301 regNewData
+1+i
, zMsg
, P4_DYNAMIC
);
1302 sqlite3VdbeChangeP5(v
, P5_ConstraintNotNull
);
1307 sqlite3VdbeAddOp2(v
, OP_IsNull
, regNewData
+1+i
, ignoreDest
);
1312 assert( onError
==OE_Replace
);
1313 addr1
= sqlite3VdbeAddOp1(v
, OP_NotNull
, regNewData
+1+i
);
1315 sqlite3ExprCode(pParse
, pTab
->aCol
[i
].pDflt
, regNewData
+1+i
);
1316 sqlite3VdbeJumpHere(v
, addr1
);
1322 /* Test all CHECK constraints
1324 #ifndef SQLITE_OMIT_CHECK
1325 if( pTab
->pCheck
&& (db
->flags
& SQLITE_IgnoreChecks
)==0 ){
1326 ExprList
*pCheck
= pTab
->pCheck
;
1327 pParse
->ckBase
= regNewData
+1;
1328 onError
= overrideError
!=OE_Default
? overrideError
: OE_Abort
;
1329 for(i
=0; i
<pCheck
->nExpr
; i
++){
1331 Expr
*pExpr
= pCheck
->a
[i
].pExpr
;
1332 if( aiChng
&& checkConstraintUnchanged(pExpr
, aiChng
, pkChng
) ) continue;
1333 allOk
= sqlite3VdbeMakeLabel(v
);
1334 sqlite3ExprIfTrue(pParse
, pExpr
, allOk
, SQLITE_JUMPIFNULL
);
1335 if( onError
==OE_Ignore
){
1336 sqlite3VdbeGoto(v
, ignoreDest
);
1338 char *zName
= pCheck
->a
[i
].zName
;
1339 if( zName
==0 ) zName
= pTab
->zName
;
1340 if( onError
==OE_Replace
) onError
= OE_Abort
; /* IMP: R-15569-63625 */
1341 sqlite3HaltConstraint(pParse
, SQLITE_CONSTRAINT_CHECK
,
1342 onError
, zName
, P4_TRANSIENT
,
1343 P5_ConstraintCheck
);
1345 sqlite3VdbeResolveLabel(v
, allOk
);
1348 #endif /* !defined(SQLITE_OMIT_CHECK) */
1350 /* If rowid is changing, make sure the new rowid does not previously
1351 ** exist in the table.
1353 if( pkChng
&& pPk
==0 ){
1354 int addrRowidOk
= sqlite3VdbeMakeLabel(v
);
1356 /* Figure out what action to take in case of a rowid collision */
1357 onError
= pTab
->keyConf
;
1358 if( overrideError
!=OE_Default
){
1359 onError
= overrideError
;
1360 }else if( onError
==OE_Default
){
1365 /* pkChng!=0 does not mean that the rowid has change, only that
1366 ** it might have changed. Skip the conflict logic below if the rowid
1368 sqlite3VdbeAddOp3(v
, OP_Eq
, regNewData
, addrRowidOk
, regOldData
);
1369 sqlite3VdbeChangeP5(v
, SQLITE_NOTNULL
);
1373 /* If the response to a rowid conflict is REPLACE but the response
1374 ** to some other UNIQUE constraint is FAIL or IGNORE, then we need
1375 ** to defer the running of the rowid conflict checking until after
1376 ** the UNIQUE constraints have run.
1378 if( onError
==OE_Replace
&& overrideError
!=OE_Replace
){
1379 for(pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
){
1380 if( pIdx
->onError
==OE_Ignore
|| pIdx
->onError
==OE_Fail
){
1381 ipkTop
= sqlite3VdbeAddOp0(v
, OP_Goto
);
1387 /* Check to see if the new rowid already exists in the table. Skip
1388 ** the following conflict logic if it does not. */
1389 sqlite3VdbeAddOp3(v
, OP_NotExists
, iDataCur
, addrRowidOk
, regNewData
);
1392 /* Generate code that deals with a rowid collision */
1396 /* Fall thru into the next case */
1401 sqlite3RowidConstraint(pParse
, onError
, pTab
);
1405 /* If there are DELETE triggers on this table and the
1406 ** recursive-triggers flag is set, call GenerateRowDelete() to
1407 ** remove the conflicting row from the table. This will fire
1408 ** the triggers and remove both the table and index b-tree entries.
1410 ** Otherwise, if there are no triggers or the recursive-triggers
1411 ** flag is not set, but the table has one or more indexes, call
1412 ** GenerateRowIndexDelete(). This removes the index b-tree entries
1413 ** only. The table b-tree entry will be replaced by the new entry
1414 ** when it is inserted.
1416 ** If either GenerateRowDelete() or GenerateRowIndexDelete() is called,
1417 ** also invoke MultiWrite() to indicate that this VDBE may require
1418 ** statement rollback (if the statement is aborted after the delete
1419 ** takes place). Earlier versions called sqlite3MultiWrite() regardless,
1420 ** but being more selective here allows statements like:
1422 ** REPLACE INTO t(rowid) VALUES($newrowid)
1424 ** to run without a statement journal if there are no indexes on the
1427 Trigger
*pTrigger
= 0;
1428 if( db
->flags
&SQLITE_RecTriggers
){
1429 pTrigger
= sqlite3TriggersExist(pParse
, pTab
, TK_DELETE
, 0, 0);
1431 if( pTrigger
|| sqlite3FkRequired(pParse
, pTab
, 0, 0) ){
1432 sqlite3MultiWrite(pParse
);
1433 sqlite3GenerateRowDelete(pParse
, pTab
, pTrigger
, iDataCur
, iIdxCur
,
1434 regNewData
, 1, 0, OE_Replace
, 1, -1);
1436 #ifdef SQLITE_ENABLE_PREUPDATE_HOOK
1437 if( HasRowid(pTab
) ){
1438 /* This OP_Delete opcode fires the pre-update-hook only. It does
1439 ** not modify the b-tree. It is more efficient to let the coming
1440 ** OP_Insert replace the existing entry than it is to delete the
1441 ** existing entry and then insert a new one. */
1442 sqlite3VdbeAddOp2(v
, OP_Delete
, iDataCur
, OPFLAG_ISNOOP
);
1443 sqlite3VdbeChangeP4(v
, -1, (char *)pTab
, P4_TABLE
);
1445 #endif /* SQLITE_ENABLE_PREUPDATE_HOOK */
1447 sqlite3MultiWrite(pParse
);
1448 sqlite3GenerateRowIndexDelete(pParse
, pTab
, iDataCur
, iIdxCur
,0,-1);
1455 /*assert( seenReplace==0 );*/
1456 sqlite3VdbeGoto(v
, ignoreDest
);
1460 sqlite3VdbeResolveLabel(v
, addrRowidOk
);
1462 ipkBottom
= sqlite3VdbeAddOp0(v
, OP_Goto
);
1463 sqlite3VdbeJumpHere(v
, ipkTop
);
1467 /* Test all UNIQUE constraints by creating entries for each UNIQUE
1468 ** index and making sure that duplicate entries do not already exist.
1469 ** Compute the revised record entries for indices as we go.
1471 ** This loop also handles the case of the PRIMARY KEY index for a
1472 ** WITHOUT ROWID table.
1474 for(ix
=0, pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
, ix
++){
1475 int regIdx
; /* Range of registers hold conent for pIdx */
1476 int regR
; /* Range of registers holding conflicting PK */
1477 int iThisCur
; /* Cursor for this UNIQUE index */
1478 int addrUniqueOk
; /* Jump here if the UNIQUE constraint is satisfied */
1480 if( aRegIdx
[ix
]==0 ) continue; /* Skip indices that do not change */
1481 if( bAffinityDone
==0 ){
1482 sqlite3TableAffinity(v
, pTab
, regNewData
+1);
1485 iThisCur
= iIdxCur
+ix
;
1486 addrUniqueOk
= sqlite3VdbeMakeLabel(v
);
1488 /* Skip partial indices for which the WHERE clause is not true */
1489 if( pIdx
->pPartIdxWhere
){
1490 sqlite3VdbeAddOp2(v
, OP_Null
, 0, aRegIdx
[ix
]);
1491 pParse
->ckBase
= regNewData
+1;
1492 sqlite3ExprIfFalseDup(pParse
, pIdx
->pPartIdxWhere
, addrUniqueOk
,
1497 /* Create a record for this index entry as it should appear after
1498 ** the insert or update. Store that record in the aRegIdx[ix] register
1500 regIdx
= sqlite3GetTempRange(pParse
, pIdx
->nColumn
);
1501 for(i
=0; i
<pIdx
->nColumn
; i
++){
1502 int iField
= pIdx
->aiColumn
[i
];
1504 if( iField
==XN_EXPR
){
1505 pParse
->ckBase
= regNewData
+1;
1506 sqlite3ExprCodeCopy(pParse
, pIdx
->aColExpr
->a
[i
].pExpr
, regIdx
+i
);
1508 VdbeComment((v
, "%s column %d", pIdx
->zName
, i
));
1510 if( iField
==XN_ROWID
|| iField
==pTab
->iPKey
){
1511 if( regRowid
==regIdx
+i
) continue; /* ROWID already in regIdx+i */
1513 regRowid
= pIdx
->pPartIdxWhere
? -1 : regIdx
+i
;
1515 x
= iField
+ regNewData
+ 1;
1517 sqlite3VdbeAddOp2(v
, iField
<0 ? OP_IntCopy
: OP_SCopy
, x
, regIdx
+i
);
1518 VdbeComment((v
, "%s", iField
<0 ? "rowid" : pTab
->aCol
[iField
].zName
));
1521 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regIdx
, pIdx
->nColumn
, aRegIdx
[ix
]);
1522 VdbeComment((v
, "for %s", pIdx
->zName
));
1523 sqlite3ExprCacheAffinityChange(pParse
, regIdx
, pIdx
->nColumn
);
1525 /* In an UPDATE operation, if this index is the PRIMARY KEY index
1526 ** of a WITHOUT ROWID table and there has been no change the
1527 ** primary key, then no collision is possible. The collision detection
1528 ** logic below can all be skipped. */
1529 if( isUpdate
&& pPk
==pIdx
&& pkChng
==0 ){
1530 sqlite3VdbeResolveLabel(v
, addrUniqueOk
);
1534 /* Find out what action to take in case there is a uniqueness conflict */
1535 onError
= pIdx
->onError
;
1536 if( onError
==OE_None
){
1537 sqlite3ReleaseTempRange(pParse
, regIdx
, pIdx
->nColumn
);
1538 sqlite3VdbeResolveLabel(v
, addrUniqueOk
);
1539 continue; /* pIdx is not a UNIQUE index */
1541 if( overrideError
!=OE_Default
){
1542 onError
= overrideError
;
1543 }else if( onError
==OE_Default
){
1547 /* Check to see if the new index entry will be unique */
1548 sqlite3VdbeAddOp4Int(v
, OP_NoConflict
, iThisCur
, addrUniqueOk
,
1549 regIdx
, pIdx
->nKeyCol
); VdbeCoverage(v
);
1551 /* Generate code to handle collisions */
1552 regR
= (pIdx
==pPk
) ? regIdx
: sqlite3GetTempRange(pParse
, nPkField
);
1553 if( isUpdate
|| onError
==OE_Replace
){
1554 if( HasRowid(pTab
) ){
1555 sqlite3VdbeAddOp2(v
, OP_IdxRowid
, iThisCur
, regR
);
1556 /* Conflict only if the rowid of the existing index entry
1557 ** is different from old-rowid */
1559 sqlite3VdbeAddOp3(v
, OP_Eq
, regR
, addrUniqueOk
, regOldData
);
1560 sqlite3VdbeChangeP5(v
, SQLITE_NOTNULL
);
1565 /* Extract the PRIMARY KEY from the end of the index entry and
1566 ** store it in registers regR..regR+nPk-1 */
1568 for(i
=0; i
<pPk
->nKeyCol
; i
++){
1569 assert( pPk
->aiColumn
[i
]>=0 );
1570 x
= sqlite3ColumnOfIndex(pIdx
, pPk
->aiColumn
[i
]);
1571 sqlite3VdbeAddOp3(v
, OP_Column
, iThisCur
, x
, regR
+i
);
1572 VdbeComment((v
, "%s.%s", pTab
->zName
,
1573 pTab
->aCol
[pPk
->aiColumn
[i
]].zName
));
1577 /* If currently processing the PRIMARY KEY of a WITHOUT ROWID
1578 ** table, only conflict if the new PRIMARY KEY values are actually
1579 ** different from the old.
1581 ** For a UNIQUE index, only conflict if the PRIMARY KEY values
1582 ** of the matched index row are different from the original PRIMARY
1583 ** KEY values of this row before the update. */
1584 int addrJump
= sqlite3VdbeCurrentAddr(v
)+pPk
->nKeyCol
;
1586 int regCmp
= (IsPrimaryKeyIndex(pIdx
) ? regIdx
: regR
);
1588 for(i
=0; i
<pPk
->nKeyCol
; i
++){
1589 char *p4
= (char*)sqlite3LocateCollSeq(pParse
, pPk
->azColl
[i
]);
1590 x
= pPk
->aiColumn
[i
];
1592 if( i
==(pPk
->nKeyCol
-1) ){
1593 addrJump
= addrUniqueOk
;
1596 sqlite3VdbeAddOp4(v
, op
,
1597 regOldData
+1+x
, addrJump
, regCmp
+i
, p4
, P4_COLLSEQ
1599 sqlite3VdbeChangeP5(v
, SQLITE_NOTNULL
);
1600 VdbeCoverageIf(v
, op
==OP_Eq
);
1601 VdbeCoverageIf(v
, op
==OP_Ne
);
1607 /* Generate code that executes if the new index entry is not unique */
1608 assert( onError
==OE_Rollback
|| onError
==OE_Abort
|| onError
==OE_Fail
1609 || onError
==OE_Ignore
|| onError
==OE_Replace
);
1614 sqlite3UniqueConstraint(pParse
, onError
, pIdx
);
1618 sqlite3VdbeGoto(v
, ignoreDest
);
1622 Trigger
*pTrigger
= 0;
1623 assert( onError
==OE_Replace
);
1624 sqlite3MultiWrite(pParse
);
1625 if( db
->flags
&SQLITE_RecTriggers
){
1626 pTrigger
= sqlite3TriggersExist(pParse
, pTab
, TK_DELETE
, 0, 0);
1628 sqlite3GenerateRowDelete(pParse
, pTab
, pTrigger
, iDataCur
, iIdxCur
,
1629 regR
, nPkField
, 0, OE_Replace
,
1630 (pIdx
==pPk
? ONEPASS_SINGLE
: ONEPASS_OFF
), -1);
1635 sqlite3VdbeResolveLabel(v
, addrUniqueOk
);
1636 sqlite3ReleaseTempRange(pParse
, regIdx
, pIdx
->nColumn
);
1637 if( regR
!=regIdx
) sqlite3ReleaseTempRange(pParse
, regR
, nPkField
);
1640 sqlite3VdbeGoto(v
, ipkTop
+1);
1641 sqlite3VdbeJumpHere(v
, ipkBottom
);
1644 *pbMayReplace
= seenReplace
;
1645 VdbeModuleComment((v
, "END: GenCnstCks(%d)", seenReplace
));
1649 ** This routine generates code to finish the INSERT or UPDATE operation
1650 ** that was started by a prior call to sqlite3GenerateConstraintChecks.
1651 ** A consecutive range of registers starting at regNewData contains the
1652 ** rowid and the content to be inserted.
1654 ** The arguments to this routine should be the same as the first six
1655 ** arguments to sqlite3GenerateConstraintChecks.
1657 void sqlite3CompleteInsertion(
1658 Parse
*pParse
, /* The parser context */
1659 Table
*pTab
, /* the table into which we are inserting */
1660 int iDataCur
, /* Cursor of the canonical data source */
1661 int iIdxCur
, /* First index cursor */
1662 int regNewData
, /* Range of content */
1663 int *aRegIdx
, /* Register used by each index. 0 for unused indices */
1664 int isUpdate
, /* True for UPDATE, False for INSERT */
1665 int appendBias
, /* True if this is likely to be an append */
1666 int useSeekResult
/* True to set the USESEEKRESULT flag on OP_[Idx]Insert */
1668 Vdbe
*v
; /* Prepared statements under construction */
1669 Index
*pIdx
; /* An index being inserted or updated */
1670 u8 pik_flags
; /* flag values passed to the btree insert */
1671 int regData
; /* Content registers (after the rowid) */
1672 int regRec
; /* Register holding assembled record for the table */
1673 int i
; /* Loop counter */
1674 u8 bAffinityDone
= 0; /* True if OP_Affinity has been run already */
1676 v
= sqlite3GetVdbe(pParse
);
1678 assert( pTab
->pSelect
==0 ); /* This table is not a VIEW */
1679 for(i
=0, pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
, i
++){
1680 if( aRegIdx
[i
]==0 ) continue;
1682 if( pIdx
->pPartIdxWhere
){
1683 sqlite3VdbeAddOp2(v
, OP_IsNull
, aRegIdx
[i
], sqlite3VdbeCurrentAddr(v
)+2);
1686 sqlite3VdbeAddOp2(v
, OP_IdxInsert
, iIdxCur
+i
, aRegIdx
[i
]);
1688 if( useSeekResult
) pik_flags
= OPFLAG_USESEEKRESULT
;
1689 if( IsPrimaryKeyIndex(pIdx
) && !HasRowid(pTab
) ){
1690 assert( pParse
->nested
==0 );
1691 pik_flags
|= OPFLAG_NCHANGE
;
1693 sqlite3VdbeChangeP5(v
, pik_flags
);
1695 if( !HasRowid(pTab
) ) return;
1696 regData
= regNewData
+ 1;
1697 regRec
= sqlite3GetTempReg(pParse
);
1698 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regData
, pTab
->nCol
, regRec
);
1699 if( !bAffinityDone
) sqlite3TableAffinity(v
, pTab
, 0);
1700 sqlite3ExprCacheAffinityChange(pParse
, regData
, pTab
->nCol
);
1701 if( pParse
->nested
){
1704 pik_flags
= OPFLAG_NCHANGE
;
1705 pik_flags
|= (isUpdate
?OPFLAG_ISUPDATE
:OPFLAG_LASTROWID
);
1708 pik_flags
|= OPFLAG_APPEND
;
1710 if( useSeekResult
){
1711 pik_flags
|= OPFLAG_USESEEKRESULT
;
1713 sqlite3VdbeAddOp3(v
, OP_Insert
, iDataCur
, regRec
, regNewData
);
1714 if( !pParse
->nested
){
1715 sqlite3VdbeChangeP4(v
, -1, (char *)pTab
, P4_TABLE
);
1717 sqlite3VdbeChangeP5(v
, pik_flags
);
1721 ** Allocate cursors for the pTab table and all its indices and generate
1722 ** code to open and initialized those cursors.
1724 ** The cursor for the object that contains the complete data (normally
1725 ** the table itself, but the PRIMARY KEY index in the case of a WITHOUT
1726 ** ROWID table) is returned in *piDataCur. The first index cursor is
1727 ** returned in *piIdxCur. The number of indices is returned.
1729 ** Use iBase as the first cursor (either the *piDataCur for rowid tables
1730 ** or the first index for WITHOUT ROWID tables) if it is non-negative.
1731 ** If iBase is negative, then allocate the next available cursor.
1733 ** For a rowid table, *piDataCur will be exactly one less than *piIdxCur.
1734 ** For a WITHOUT ROWID table, *piDataCur will be somewhere in the range
1735 ** of *piIdxCurs, depending on where the PRIMARY KEY index appears on the
1736 ** pTab->pIndex list.
1738 ** If pTab is a virtual table, then this routine is a no-op and the
1739 ** *piDataCur and *piIdxCur values are left uninitialized.
1741 int sqlite3OpenTableAndIndices(
1742 Parse
*pParse
, /* Parsing context */
1743 Table
*pTab
, /* Table to be opened */
1744 int op
, /* OP_OpenRead or OP_OpenWrite */
1745 u8 p5
, /* P5 value for OP_Open* opcodes (except on WITHOUT ROWID) */
1746 int iBase
, /* Use this for the table cursor, if there is one */
1747 u8
*aToOpen
, /* If not NULL: boolean for each table and index */
1748 int *piDataCur
, /* Write the database source cursor number here */
1749 int *piIdxCur
/* Write the first index cursor number here */
1757 assert( op
==OP_OpenRead
|| op
==OP_OpenWrite
);
1758 assert( op
==OP_OpenWrite
|| p5
==0 );
1759 if( IsVirtual(pTab
) ){
1760 /* This routine is a no-op for virtual tables. Leave the output
1761 ** variables *piDataCur and *piIdxCur uninitialized so that valgrind
1762 ** can detect if they are used by mistake in the caller. */
1765 iDb
= sqlite3SchemaToIndex(pParse
->db
, pTab
->pSchema
);
1766 v
= sqlite3GetVdbe(pParse
);
1768 if( iBase
<0 ) iBase
= pParse
->nTab
;
1770 if( piDataCur
) *piDataCur
= iDataCur
;
1771 if( HasRowid(pTab
) && (aToOpen
==0 || aToOpen
[0]) ){
1772 sqlite3OpenTable(pParse
, iDataCur
, iDb
, pTab
, op
);
1774 sqlite3TableLock(pParse
, iDb
, pTab
->tnum
, op
==OP_OpenWrite
, pTab
->zName
);
1776 if( piIdxCur
) *piIdxCur
= iBase
;
1777 for(i
=0, pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
, i
++){
1778 int iIdxCur
= iBase
++;
1779 assert( pIdx
->pSchema
==pTab
->pSchema
);
1780 if( aToOpen
==0 || aToOpen
[i
+1] ){
1781 sqlite3VdbeAddOp3(v
, op
, iIdxCur
, pIdx
->tnum
, iDb
);
1782 sqlite3VdbeSetP4KeyInfo(pParse
, pIdx
);
1783 VdbeComment((v
, "%s", pIdx
->zName
));
1785 if( IsPrimaryKeyIndex(pIdx
) && !HasRowid(pTab
) ){
1786 if( piDataCur
) *piDataCur
= iIdxCur
;
1788 sqlite3VdbeChangeP5(v
, p5
);
1791 if( iBase
>pParse
->nTab
) pParse
->nTab
= iBase
;
1798 ** The following global variable is incremented whenever the
1799 ** transfer optimization is used. This is used for testing
1800 ** purposes only - to make sure the transfer optimization really
1801 ** is happening when it is supposed to.
1803 int sqlite3_xferopt_count
;
1804 #endif /* SQLITE_TEST */
1807 #ifndef SQLITE_OMIT_XFER_OPT
1809 ** Check to see if index pSrc is compatible as a source of data
1810 ** for index pDest in an insert transfer optimization. The rules
1811 ** for a compatible index:
1813 ** * The index is over the same set of columns
1814 ** * The same DESC and ASC markings occurs on all columns
1815 ** * The same onError processing (OE_Abort, OE_Ignore, etc)
1816 ** * The same collating sequence on each column
1817 ** * The index has the exact same WHERE clause
1819 static int xferCompatibleIndex(Index
*pDest
, Index
*pSrc
){
1821 assert( pDest
&& pSrc
);
1822 assert( pDest
->pTable
!=pSrc
->pTable
);
1823 if( pDest
->nKeyCol
!=pSrc
->nKeyCol
){
1824 return 0; /* Different number of columns */
1826 if( pDest
->onError
!=pSrc
->onError
){
1827 return 0; /* Different conflict resolution strategies */
1829 for(i
=0; i
<pSrc
->nKeyCol
; i
++){
1830 if( pSrc
->aiColumn
[i
]!=pDest
->aiColumn
[i
] ){
1831 return 0; /* Different columns indexed */
1833 if( pSrc
->aiColumn
[i
]==XN_EXPR
){
1834 assert( pSrc
->aColExpr
!=0 && pDest
->aColExpr
!=0 );
1835 if( sqlite3ExprCompare(pSrc
->aColExpr
->a
[i
].pExpr
,
1836 pDest
->aColExpr
->a
[i
].pExpr
, -1)!=0 ){
1837 return 0; /* Different expressions in the index */
1840 if( pSrc
->aSortOrder
[i
]!=pDest
->aSortOrder
[i
] ){
1841 return 0; /* Different sort orders */
1843 if( sqlite3_stricmp(pSrc
->azColl
[i
],pDest
->azColl
[i
])!=0 ){
1844 return 0; /* Different collating sequences */
1847 if( sqlite3ExprCompare(pSrc
->pPartIdxWhere
, pDest
->pPartIdxWhere
, -1) ){
1848 return 0; /* Different WHERE clauses */
1851 /* If no test above fails then the indices must be compatible */
1856 ** Attempt the transfer optimization on INSERTs of the form
1858 ** INSERT INTO tab1 SELECT * FROM tab2;
1860 ** The xfer optimization transfers raw records from tab2 over to tab1.
1861 ** Columns are not decoded and reassembled, which greatly improves
1862 ** performance. Raw index records are transferred in the same way.
1864 ** The xfer optimization is only attempted if tab1 and tab2 are compatible.
1865 ** There are lots of rules for determining compatibility - see comments
1866 ** embedded in the code for details.
1868 ** This routine returns TRUE if the optimization is guaranteed to be used.
1869 ** Sometimes the xfer optimization will only work if the destination table
1870 ** is empty - a factor that can only be determined at run-time. In that
1871 ** case, this routine generates code for the xfer optimization but also
1872 ** does a test to see if the destination table is empty and jumps over the
1873 ** xfer optimization code if the test fails. In that case, this routine
1874 ** returns FALSE so that the caller will know to go ahead and generate
1875 ** an unoptimized transfer. This routine also returns FALSE if there
1876 ** is no chance that the xfer optimization can be applied.
1878 ** This optimization is particularly useful at making VACUUM run faster.
1880 static int xferOptimization(
1881 Parse
*pParse
, /* Parser context */
1882 Table
*pDest
, /* The table we are inserting into */
1883 Select
*pSelect
, /* A SELECT statement to use as the data source */
1884 int onError
, /* How to handle constraint errors */
1885 int iDbDest
/* The database of pDest */
1887 sqlite3
*db
= pParse
->db
;
1888 ExprList
*pEList
; /* The result set of the SELECT */
1889 Table
*pSrc
; /* The table in the FROM clause of SELECT */
1890 Index
*pSrcIdx
, *pDestIdx
; /* Source and destination indices */
1891 struct SrcList_item
*pItem
; /* An element of pSelect->pSrc */
1892 int i
; /* Loop counter */
1893 int iDbSrc
; /* The database of pSrc */
1894 int iSrc
, iDest
; /* Cursors from source and destination */
1895 int addr1
, addr2
; /* Loop addresses */
1896 int emptyDestTest
= 0; /* Address of test for empty pDest */
1897 int emptySrcTest
= 0; /* Address of test for empty pSrc */
1898 Vdbe
*v
; /* The VDBE we are building */
1899 int regAutoinc
; /* Memory register used by AUTOINC */
1900 int destHasUniqueIdx
= 0; /* True if pDest has a UNIQUE index */
1901 int regData
, regRowid
; /* Registers holding data and rowid */
1904 return 0; /* Must be of the form INSERT INTO ... SELECT ... */
1906 if( pParse
->pWith
|| pSelect
->pWith
){
1907 /* Do not attempt to process this query if there are an WITH clauses
1908 ** attached to it. Proceeding may generate a false "no such table: xxx"
1909 ** error if pSelect reads from a CTE named "xxx". */
1912 if( sqlite3TriggerList(pParse
, pDest
) ){
1913 return 0; /* tab1 must not have triggers */
1915 #ifndef SQLITE_OMIT_VIRTUALTABLE
1916 if( pDest
->tabFlags
& TF_Virtual
){
1917 return 0; /* tab1 must not be a virtual table */
1920 if( onError
==OE_Default
){
1921 if( pDest
->iPKey
>=0 ) onError
= pDest
->keyConf
;
1922 if( onError
==OE_Default
) onError
= OE_Abort
;
1924 assert(pSelect
->pSrc
); /* allocated even if there is no FROM clause */
1925 if( pSelect
->pSrc
->nSrc
!=1 ){
1926 return 0; /* FROM clause must have exactly one term */
1928 if( pSelect
->pSrc
->a
[0].pSelect
){
1929 return 0; /* FROM clause cannot contain a subquery */
1931 if( pSelect
->pWhere
){
1932 return 0; /* SELECT may not have a WHERE clause */
1934 if( pSelect
->pOrderBy
){
1935 return 0; /* SELECT may not have an ORDER BY clause */
1937 /* Do not need to test for a HAVING clause. If HAVING is present but
1938 ** there is no ORDER BY, we will get an error. */
1939 if( pSelect
->pGroupBy
){
1940 return 0; /* SELECT may not have a GROUP BY clause */
1942 if( pSelect
->pLimit
){
1943 return 0; /* SELECT may not have a LIMIT clause */
1945 assert( pSelect
->pOffset
==0 ); /* Must be so if pLimit==0 */
1946 if( pSelect
->pPrior
){
1947 return 0; /* SELECT may not be a compound query */
1949 if( pSelect
->selFlags
& SF_Distinct
){
1950 return 0; /* SELECT may not be DISTINCT */
1952 pEList
= pSelect
->pEList
;
1953 assert( pEList
!=0 );
1954 if( pEList
->nExpr
!=1 ){
1955 return 0; /* The result set must have exactly one column */
1957 assert( pEList
->a
[0].pExpr
);
1958 if( pEList
->a
[0].pExpr
->op
!=TK_ASTERISK
){
1959 return 0; /* The result set must be the special operator "*" */
1962 /* At this point we have established that the statement is of the
1963 ** correct syntactic form to participate in this optimization. Now
1964 ** we have to check the semantics.
1966 pItem
= pSelect
->pSrc
->a
;
1967 pSrc
= sqlite3LocateTableItem(pParse
, 0, pItem
);
1969 return 0; /* FROM clause does not contain a real table */
1972 return 0; /* tab1 and tab2 may not be the same table */
1974 if( HasRowid(pDest
)!=HasRowid(pSrc
) ){
1975 return 0; /* source and destination must both be WITHOUT ROWID or not */
1977 #ifndef SQLITE_OMIT_VIRTUALTABLE
1978 if( pSrc
->tabFlags
& TF_Virtual
){
1979 return 0; /* tab2 must not be a virtual table */
1982 if( pSrc
->pSelect
){
1983 return 0; /* tab2 may not be a view */
1985 if( pDest
->nCol
!=pSrc
->nCol
){
1986 return 0; /* Number of columns must be the same in tab1 and tab2 */
1988 if( pDest
->iPKey
!=pSrc
->iPKey
){
1989 return 0; /* Both tables must have the same INTEGER PRIMARY KEY */
1991 for(i
=0; i
<pDest
->nCol
; i
++){
1992 Column
*pDestCol
= &pDest
->aCol
[i
];
1993 Column
*pSrcCol
= &pSrc
->aCol
[i
];
1994 #ifdef SQLITE_ENABLE_HIDDEN_COLUMNS
1995 if( (db
->flags
& SQLITE_Vacuum
)==0
1996 && (pDestCol
->colFlags
| pSrcCol
->colFlags
) & COLFLAG_HIDDEN
1998 return 0; /* Neither table may have __hidden__ columns */
2001 if( pDestCol
->affinity
!=pSrcCol
->affinity
){
2002 return 0; /* Affinity must be the same on all columns */
2004 if( sqlite3_stricmp(pDestCol
->zColl
, pSrcCol
->zColl
)!=0 ){
2005 return 0; /* Collating sequence must be the same on all columns */
2007 if( pDestCol
->notNull
&& !pSrcCol
->notNull
){
2008 return 0; /* tab2 must be NOT NULL if tab1 is */
2010 /* Default values for second and subsequent columns need to match. */
2012 assert( pDestCol
->pDflt
==0 || pDestCol
->pDflt
->op
==TK_SPAN
);
2013 assert( pSrcCol
->pDflt
==0 || pSrcCol
->pDflt
->op
==TK_SPAN
);
2014 if( (pDestCol
->pDflt
==0)!=(pSrcCol
->pDflt
==0)
2015 || (pDestCol
->pDflt
&& strcmp(pDestCol
->pDflt
->u
.zToken
,
2016 pSrcCol
->pDflt
->u
.zToken
)!=0)
2018 return 0; /* Default values must be the same for all columns */
2022 for(pDestIdx
=pDest
->pIndex
; pDestIdx
; pDestIdx
=pDestIdx
->pNext
){
2023 if( IsUniqueIndex(pDestIdx
) ){
2024 destHasUniqueIdx
= 1;
2026 for(pSrcIdx
=pSrc
->pIndex
; pSrcIdx
; pSrcIdx
=pSrcIdx
->pNext
){
2027 if( xferCompatibleIndex(pDestIdx
, pSrcIdx
) ) break;
2030 return 0; /* pDestIdx has no corresponding index in pSrc */
2033 #ifndef SQLITE_OMIT_CHECK
2034 if( pDest
->pCheck
&& sqlite3ExprListCompare(pSrc
->pCheck
,pDest
->pCheck
,-1) ){
2035 return 0; /* Tables have different CHECK constraints. Ticket #2252 */
2038 #ifndef SQLITE_OMIT_FOREIGN_KEY
2039 /* Disallow the transfer optimization if the destination table constains
2040 ** any foreign key constraints. This is more restrictive than necessary.
2041 ** But the main beneficiary of the transfer optimization is the VACUUM
2042 ** command, and the VACUUM command disables foreign key constraints. So
2043 ** the extra complication to make this rule less restrictive is probably
2044 ** not worth the effort. Ticket [6284df89debdfa61db8073e062908af0c9b6118e]
2046 if( (db
->flags
& SQLITE_ForeignKeys
)!=0 && pDest
->pFKey
!=0 ){
2050 if( (db
->flags
& SQLITE_CountRows
)!=0 ){
2051 return 0; /* xfer opt does not play well with PRAGMA count_changes */
2054 /* If we get this far, it means that the xfer optimization is at
2055 ** least a possibility, though it might only work if the destination
2056 ** table (tab1) is initially empty.
2059 sqlite3_xferopt_count
++;
2061 iDbSrc
= sqlite3SchemaToIndex(db
, pSrc
->pSchema
);
2062 v
= sqlite3GetVdbe(pParse
);
2063 sqlite3CodeVerifySchema(pParse
, iDbSrc
);
2064 iSrc
= pParse
->nTab
++;
2065 iDest
= pParse
->nTab
++;
2066 regAutoinc
= autoIncBegin(pParse
, iDbDest
, pDest
);
2067 regData
= sqlite3GetTempReg(pParse
);
2068 regRowid
= sqlite3GetTempReg(pParse
);
2069 sqlite3OpenTable(pParse
, iDest
, iDbDest
, pDest
, OP_OpenWrite
);
2070 assert( HasRowid(pDest
) || destHasUniqueIdx
);
2071 if( (db
->flags
& SQLITE_Vacuum
)==0 && (
2072 (pDest
->iPKey
<0 && pDest
->pIndex
!=0) /* (1) */
2073 || destHasUniqueIdx
/* (2) */
2074 || (onError
!=OE_Abort
&& onError
!=OE_Rollback
) /* (3) */
2076 /* In some circumstances, we are able to run the xfer optimization
2077 ** only if the destination table is initially empty. Unless the
2078 ** SQLITE_Vacuum flag is set, this block generates code to make
2079 ** that determination. If SQLITE_Vacuum is set, then the destination
2080 ** table is always empty.
2082 ** Conditions under which the destination must be empty:
2084 ** (1) There is no INTEGER PRIMARY KEY but there are indices.
2085 ** (If the destination is not initially empty, the rowid fields
2086 ** of index entries might need to change.)
2088 ** (2) The destination has a unique index. (The xfer optimization
2089 ** is unable to test uniqueness.)
2091 ** (3) onError is something other than OE_Abort and OE_Rollback.
2093 addr1
= sqlite3VdbeAddOp2(v
, OP_Rewind
, iDest
, 0); VdbeCoverage(v
);
2094 emptyDestTest
= sqlite3VdbeAddOp0(v
, OP_Goto
);
2095 sqlite3VdbeJumpHere(v
, addr1
);
2097 if( HasRowid(pSrc
) ){
2098 sqlite3OpenTable(pParse
, iSrc
, iDbSrc
, pSrc
, OP_OpenRead
);
2099 emptySrcTest
= sqlite3VdbeAddOp2(v
, OP_Rewind
, iSrc
, 0); VdbeCoverage(v
);
2100 if( pDest
->iPKey
>=0 ){
2101 addr1
= sqlite3VdbeAddOp2(v
, OP_Rowid
, iSrc
, regRowid
);
2102 addr2
= sqlite3VdbeAddOp3(v
, OP_NotExists
, iDest
, 0, regRowid
);
2104 sqlite3RowidConstraint(pParse
, onError
, pDest
);
2105 sqlite3VdbeJumpHere(v
, addr2
);
2106 autoIncStep(pParse
, regAutoinc
, regRowid
);
2107 }else if( pDest
->pIndex
==0 ){
2108 addr1
= sqlite3VdbeAddOp2(v
, OP_NewRowid
, iDest
, regRowid
);
2110 addr1
= sqlite3VdbeAddOp2(v
, OP_Rowid
, iSrc
, regRowid
);
2111 assert( (pDest
->tabFlags
& TF_Autoincrement
)==0 );
2113 sqlite3VdbeAddOp2(v
, OP_RowData
, iSrc
, regData
);
2114 sqlite3VdbeAddOp4(v
, OP_Insert
, iDest
, regData
, regRowid
,
2115 (char*)pDest
, P4_TABLE
);
2116 sqlite3VdbeChangeP5(v
, OPFLAG_NCHANGE
|OPFLAG_LASTROWID
|OPFLAG_APPEND
);
2117 sqlite3VdbeAddOp2(v
, OP_Next
, iSrc
, addr1
); VdbeCoverage(v
);
2118 sqlite3VdbeAddOp2(v
, OP_Close
, iSrc
, 0);
2119 sqlite3VdbeAddOp2(v
, OP_Close
, iDest
, 0);
2121 sqlite3TableLock(pParse
, iDbDest
, pDest
->tnum
, 1, pDest
->zName
);
2122 sqlite3TableLock(pParse
, iDbSrc
, pSrc
->tnum
, 0, pSrc
->zName
);
2124 for(pDestIdx
=pDest
->pIndex
; pDestIdx
; pDestIdx
=pDestIdx
->pNext
){
2126 for(pSrcIdx
=pSrc
->pIndex
; ALWAYS(pSrcIdx
); pSrcIdx
=pSrcIdx
->pNext
){
2127 if( xferCompatibleIndex(pDestIdx
, pSrcIdx
) ) break;
2130 sqlite3VdbeAddOp3(v
, OP_OpenRead
, iSrc
, pSrcIdx
->tnum
, iDbSrc
);
2131 sqlite3VdbeSetP4KeyInfo(pParse
, pSrcIdx
);
2132 VdbeComment((v
, "%s", pSrcIdx
->zName
));
2133 sqlite3VdbeAddOp3(v
, OP_OpenWrite
, iDest
, pDestIdx
->tnum
, iDbDest
);
2134 sqlite3VdbeSetP4KeyInfo(pParse
, pDestIdx
);
2135 sqlite3VdbeChangeP5(v
, OPFLAG_BULKCSR
);
2136 VdbeComment((v
, "%s", pDestIdx
->zName
));
2137 addr1
= sqlite3VdbeAddOp2(v
, OP_Rewind
, iSrc
, 0); VdbeCoverage(v
);
2138 sqlite3VdbeAddOp2(v
, OP_RowKey
, iSrc
, regData
);
2139 if( db
->flags
& SQLITE_Vacuum
){
2140 /* This INSERT command is part of a VACUUM operation, which guarantees
2141 ** that the destination table is empty. If all indexed columns use
2142 ** collation sequence BINARY, then it can also be assumed that the
2143 ** index will be populated by inserting keys in strictly sorted
2144 ** order. In this case, instead of seeking within the b-tree as part
2145 ** of every OP_IdxInsert opcode, an OP_Last is added before the
2146 ** OP_IdxInsert to seek to the point within the b-tree where each key
2147 ** should be inserted. This is faster.
2149 ** If any of the indexed columns use a collation sequence other than
2150 ** BINARY, this optimization is disabled. This is because the user
2151 ** might change the definition of a collation sequence and then run
2152 ** a VACUUM command. In that case keys may not be written in strictly
2154 for(i
=0; i
<pSrcIdx
->nColumn
; i
++){
2155 const char *zColl
= pSrcIdx
->azColl
[i
];
2156 assert( sqlite3_stricmp(sqlite3StrBINARY
, zColl
)!=0
2157 || sqlite3StrBINARY
==zColl
);
2158 if( sqlite3_stricmp(sqlite3StrBINARY
, zColl
) ) break;
2160 if( i
==pSrcIdx
->nColumn
){
2161 idxInsFlags
= OPFLAG_USESEEKRESULT
;
2162 sqlite3VdbeAddOp3(v
, OP_Last
, iDest
, 0, -1);
2165 if( !HasRowid(pSrc
) && pDestIdx
->idxType
==2 ){
2166 idxInsFlags
|= OPFLAG_NCHANGE
;
2168 sqlite3VdbeAddOp3(v
, OP_IdxInsert
, iDest
, regData
, 1);
2169 sqlite3VdbeChangeP5(v
, idxInsFlags
);
2170 sqlite3VdbeAddOp2(v
, OP_Next
, iSrc
, addr1
+1); VdbeCoverage(v
);
2171 sqlite3VdbeJumpHere(v
, addr1
);
2172 sqlite3VdbeAddOp2(v
, OP_Close
, iSrc
, 0);
2173 sqlite3VdbeAddOp2(v
, OP_Close
, iDest
, 0);
2175 if( emptySrcTest
) sqlite3VdbeJumpHere(v
, emptySrcTest
);
2176 sqlite3ReleaseTempReg(pParse
, regRowid
);
2177 sqlite3ReleaseTempReg(pParse
, regData
);
2178 if( emptyDestTest
){
2179 sqlite3VdbeAddOp2(v
, OP_Halt
, SQLITE_OK
, 0);
2180 sqlite3VdbeJumpHere(v
, emptyDestTest
);
2181 sqlite3VdbeAddOp2(v
, OP_Close
, iDest
, 0);
2187 #endif /* SQLITE_OMIT_XFER_OPT */