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. Return zero if pTab is not an AUTOINCREMENT
204 ** table. (Also return zero when doing a VACUUM since we do not want to
205 ** update the AUTOINCREMENT counters during a VACUUM.)
207 ** There is at most one AutoincInfo structure per table even if the
208 ** same table is autoincremented multiple times due to inserts within
209 ** triggers. A new AutoincInfo structure is created if this is the
210 ** first use of table pTab. On 2nd and subsequent uses, the original
211 ** AutoincInfo structure is used.
213 ** Four consecutive registers are allocated:
215 ** (1) The name of the pTab table.
216 ** (2) The maximum ROWID of pTab.
217 ** (3) The rowid in sqlite_sequence of pTab
218 ** (4) The original value of the max ROWID in pTab, or NULL if none
220 ** The 2nd register is the one that is returned. That is all the
221 ** insert routine needs to know about.
223 static int autoIncBegin(
224 Parse
*pParse
, /* Parsing context */
225 int iDb
, /* Index of the database holding pTab */
226 Table
*pTab
/* The table we are writing to */
228 int memId
= 0; /* Register holding maximum rowid */
229 if( (pTab
->tabFlags
& TF_Autoincrement
)!=0
230 && (pParse
->db
->mDbFlags
& DBFLAG_Vacuum
)==0
232 Parse
*pToplevel
= sqlite3ParseToplevel(pParse
);
235 pInfo
= pToplevel
->pAinc
;
236 while( pInfo
&& pInfo
->pTab
!=pTab
){ pInfo
= pInfo
->pNext
; }
238 pInfo
= sqlite3DbMallocRawNN(pParse
->db
, sizeof(*pInfo
));
239 if( pInfo
==0 ) return 0;
240 pInfo
->pNext
= pToplevel
->pAinc
;
241 pToplevel
->pAinc
= pInfo
;
244 pToplevel
->nMem
++; /* Register to hold name of table */
245 pInfo
->regCtr
= ++pToplevel
->nMem
; /* Max rowid register */
246 pToplevel
->nMem
+=2; /* Rowid in sqlite_sequence + orig max val */
248 memId
= pInfo
->regCtr
;
254 ** This routine generates code that will initialize all of the
255 ** register used by the autoincrement tracker.
257 void sqlite3AutoincrementBegin(Parse
*pParse
){
258 AutoincInfo
*p
; /* Information about an AUTOINCREMENT */
259 sqlite3
*db
= pParse
->db
; /* The database connection */
260 Db
*pDb
; /* Database only autoinc table */
261 int memId
; /* Register holding max rowid */
262 Vdbe
*v
= pParse
->pVdbe
; /* VDBE under construction */
264 /* This routine is never called during trigger-generation. It is
265 ** only called from the top-level */
266 assert( pParse
->pTriggerTab
==0 );
267 assert( sqlite3IsToplevel(pParse
) );
269 assert( v
); /* We failed long ago if this is not so */
270 for(p
= pParse
->pAinc
; p
; p
= p
->pNext
){
271 static const int iLn
= VDBE_OFFSET_LINENO(2);
272 static const VdbeOpList autoInc
[] = {
273 /* 0 */ {OP_Null
, 0, 0, 0},
274 /* 1 */ {OP_Rewind
, 0, 10, 0},
275 /* 2 */ {OP_Column
, 0, 0, 0},
276 /* 3 */ {OP_Ne
, 0, 9, 0},
277 /* 4 */ {OP_Rowid
, 0, 0, 0},
278 /* 5 */ {OP_Column
, 0, 1, 0},
279 /* 6 */ {OP_AddImm
, 0, 0, 0},
280 /* 7 */ {OP_Copy
, 0, 0, 0},
281 /* 8 */ {OP_Goto
, 0, 11, 0},
282 /* 9 */ {OP_Next
, 0, 2, 0},
283 /* 10 */ {OP_Integer
, 0, 0, 0},
284 /* 11 */ {OP_Close
, 0, 0, 0}
287 pDb
= &db
->aDb
[p
->iDb
];
289 assert( sqlite3SchemaMutexHeld(db
, 0, pDb
->pSchema
) );
290 sqlite3OpenTable(pParse
, 0, p
->iDb
, pDb
->pSchema
->pSeqTab
, OP_OpenRead
);
291 sqlite3VdbeLoadString(v
, memId
-1, p
->pTab
->zName
);
292 aOp
= sqlite3VdbeAddOpList(v
, ArraySize(autoInc
), autoInc
, iLn
);
299 aOp
[3].p5
= SQLITE_JUMPIFNULL
;
310 ** Update the maximum rowid for an autoincrement calculation.
312 ** This routine should be called when the regRowid register holds a
313 ** new rowid that is about to be inserted. If that new rowid is
314 ** larger than the maximum rowid in the memId memory cell, then the
315 ** memory cell is updated.
317 static void autoIncStep(Parse
*pParse
, int memId
, int regRowid
){
319 sqlite3VdbeAddOp2(pParse
->pVdbe
, OP_MemMax
, memId
, regRowid
);
324 ** This routine generates the code needed to write autoincrement
325 ** maximum rowid values back into the sqlite_sequence register.
326 ** Every statement that might do an INSERT into an autoincrement
327 ** table (either directly or through triggers) needs to call this
328 ** routine just before the "exit" code.
330 static SQLITE_NOINLINE
void autoIncrementEnd(Parse
*pParse
){
332 Vdbe
*v
= pParse
->pVdbe
;
333 sqlite3
*db
= pParse
->db
;
336 for(p
= pParse
->pAinc
; p
; p
= p
->pNext
){
337 static const int iLn
= VDBE_OFFSET_LINENO(2);
338 static const VdbeOpList autoIncEnd
[] = {
339 /* 0 */ {OP_NotNull
, 0, 2, 0},
340 /* 1 */ {OP_NewRowid
, 0, 0, 0},
341 /* 2 */ {OP_MakeRecord
, 0, 2, 0},
342 /* 3 */ {OP_Insert
, 0, 0, 0},
343 /* 4 */ {OP_Close
, 0, 0, 0}
346 Db
*pDb
= &db
->aDb
[p
->iDb
];
348 int memId
= p
->regCtr
;
350 iRec
= sqlite3GetTempReg(pParse
);
351 assert( sqlite3SchemaMutexHeld(db
, 0, pDb
->pSchema
) );
352 sqlite3VdbeAddOp3(v
, OP_Le
, memId
+2, sqlite3VdbeCurrentAddr(v
)+7, memId
);
354 sqlite3OpenTable(pParse
, 0, p
->iDb
, pDb
->pSchema
->pSeqTab
, OP_OpenWrite
);
355 aOp
= sqlite3VdbeAddOpList(v
, ArraySize(autoIncEnd
), autoIncEnd
, iLn
);
363 aOp
[3].p5
= OPFLAG_APPEND
;
364 sqlite3ReleaseTempReg(pParse
, iRec
);
367 void sqlite3AutoincrementEnd(Parse
*pParse
){
368 if( pParse
->pAinc
) autoIncrementEnd(pParse
);
372 ** If SQLITE_OMIT_AUTOINCREMENT is defined, then the three routines
373 ** above are all no-ops
375 # define autoIncBegin(A,B,C) (0)
376 # define autoIncStep(A,B,C)
377 #endif /* SQLITE_OMIT_AUTOINCREMENT */
380 /* Forward declaration */
381 static int xferOptimization(
382 Parse
*pParse
, /* Parser context */
383 Table
*pDest
, /* The table we are inserting into */
384 Select
*pSelect
, /* A SELECT statement to use as the data source */
385 int onError
, /* How to handle constraint errors */
386 int iDbDest
/* The database of pDest */
390 ** This routine is called to handle SQL of the following forms:
392 ** insert into TABLE (IDLIST) values(EXPRLIST),(EXPRLIST),...
393 ** insert into TABLE (IDLIST) select
394 ** insert into TABLE (IDLIST) default values
396 ** The IDLIST following the table name is always optional. If omitted,
397 ** then a list of all (non-hidden) columns for the table is substituted.
398 ** The IDLIST appears in the pColumn parameter. pColumn is NULL if IDLIST
401 ** For the pSelect parameter holds the values to be inserted for the
402 ** first two forms shown above. A VALUES clause is really just short-hand
403 ** for a SELECT statement that omits the FROM clause and everything else
404 ** that follows. If the pSelect parameter is NULL, that means that the
405 ** DEFAULT VALUES form of the INSERT statement is intended.
407 ** The code generated follows one of four templates. For a simple
408 ** insert with data coming from a single-row VALUES clause, the code executes
409 ** once straight down through. Pseudo-code follows (we call this
410 ** the "1st template"):
412 ** open write cursor to <table> and its indices
413 ** put VALUES clause expressions into registers
414 ** write the resulting record into <table>
417 ** The three remaining templates assume the statement is of the form
419 ** INSERT INTO <table> SELECT ...
421 ** If the SELECT clause is of the restricted form "SELECT * FROM <table2>" -
422 ** in other words if the SELECT pulls all columns from a single table
423 ** and there is no WHERE or LIMIT or GROUP BY or ORDER BY clauses, and
424 ** if <table2> and <table1> are distinct tables but have identical
425 ** schemas, including all the same indices, then a special optimization
426 ** is invoked that copies raw records from <table2> over to <table1>.
427 ** See the xferOptimization() function for the implementation of this
428 ** template. This is the 2nd template.
430 ** open a write cursor to <table>
431 ** open read cursor on <table2>
432 ** transfer all records in <table2> over to <table>
434 ** foreach index on <table>
435 ** open a write cursor on the <table> index
436 ** open a read cursor on the corresponding <table2> index
437 ** transfer all records from the read to the write cursors
441 ** The 3rd template is for when the second template does not apply
442 ** and the SELECT clause does not read from <table> at any time.
443 ** The generated code follows this template:
447 ** A: setup for the SELECT
448 ** loop over the rows in the SELECT
449 ** load values into registers R..R+n
452 ** cleanup after the SELECT
454 ** B: open write cursor to <table> and its indices
455 ** C: yield X, at EOF goto D
456 ** insert the select result into <table> from R..R+n
460 ** The 4th template is used if the insert statement takes its
461 ** values from a SELECT but the data is being inserted into a table
462 ** that is also read as part of the SELECT. In the third form,
463 ** we have to use an intermediate table to store the results of
464 ** the select. The template is like this:
468 ** A: setup for the SELECT
469 ** loop over the tables in the SELECT
470 ** load value into register R..R+n
473 ** cleanup after the SELECT
475 ** B: open temp table
476 ** L: yield X, at EOF goto M
477 ** insert row from R..R+n into temp table
479 ** M: open write cursor to <table> and its indices
481 ** C: loop over rows of intermediate table
482 ** transfer values form intermediate table into <table>
487 Parse
*pParse
, /* Parser context */
488 SrcList
*pTabList
, /* Name of table into which we are inserting */
489 Select
*pSelect
, /* A SELECT statement to use as the data source */
490 IdList
*pColumn
, /* Column names corresponding to IDLIST. */
491 int onError
, /* How to handle constraint errors */
492 Upsert
*pUpsert
/* ON CONFLICT clauses for upsert, or NULL */
494 sqlite3
*db
; /* The main database structure */
495 Table
*pTab
; /* The table to insert into. aka TABLE */
496 int i
, j
; /* Loop counters */
497 Vdbe
*v
; /* Generate code into this virtual machine */
498 Index
*pIdx
; /* For looping over indices of the table */
499 int nColumn
; /* Number of columns in the data */
500 int nHidden
= 0; /* Number of hidden columns if TABLE is virtual */
501 int iDataCur
= 0; /* VDBE cursor that is the main data repository */
502 int iIdxCur
= 0; /* First index cursor */
503 int ipkColumn
= -1; /* Column that is the INTEGER PRIMARY KEY */
504 int endOfLoop
; /* Label for the end of the insertion loop */
505 int srcTab
= 0; /* Data comes from this temporary cursor if >=0 */
506 int addrInsTop
= 0; /* Jump to label "D" */
507 int addrCont
= 0; /* Top of insert loop. Label "C" in templates 3 and 4 */
508 SelectDest dest
; /* Destination for SELECT on rhs of INSERT */
509 int iDb
; /* Index of database holding TABLE */
510 u8 useTempTable
= 0; /* Store SELECT results in intermediate table */
511 u8 appendFlag
= 0; /* True if the insert is likely to be an append */
512 u8 withoutRowid
; /* 0 for normal table. 1 for WITHOUT ROWID table */
513 u8 bIdListInOrder
; /* True if IDLIST is in table order */
514 ExprList
*pList
= 0; /* List of VALUES() to be inserted */
516 /* Register allocations */
517 int regFromSelect
= 0;/* Base register for data coming from SELECT */
518 int regAutoinc
= 0; /* Register holding the AUTOINCREMENT counter */
519 int regRowCount
= 0; /* Memory cell used for the row counter */
520 int regIns
; /* Block of regs holding rowid+data being inserted */
521 int regRowid
; /* registers holding insert rowid */
522 int regData
; /* register holding first column to insert */
523 int *aRegIdx
= 0; /* One register allocated to each index */
525 #ifndef SQLITE_OMIT_TRIGGER
526 int isView
; /* True if attempting to insert into a view */
527 Trigger
*pTrigger
; /* List of triggers on pTab, if required */
528 int tmask
; /* Mask of trigger times */
532 if( pParse
->nErr
|| db
->mallocFailed
){
535 dest
.iSDParm
= 0; /* Suppress a harmless compiler warning */
537 /* If the Select object is really just a simple VALUES() list with a
538 ** single row (the common case) then keep that one row of values
539 ** and discard the other (unused) parts of the pSelect object
541 if( pSelect
&& (pSelect
->selFlags
& SF_Values
)!=0 && pSelect
->pPrior
==0 ){
542 pList
= pSelect
->pEList
;
544 sqlite3SelectDelete(db
, pSelect
);
548 /* Locate the table into which we will be inserting new information.
550 assert( pTabList
->nSrc
==1 );
551 pTab
= sqlite3SrcListLookup(pParse
, pTabList
);
555 iDb
= sqlite3SchemaToIndex(db
, pTab
->pSchema
);
556 assert( iDb
<db
->nDb
);
557 if( sqlite3AuthCheck(pParse
, SQLITE_INSERT
, pTab
->zName
, 0,
558 db
->aDb
[iDb
].zDbSName
) ){
561 withoutRowid
= !HasRowid(pTab
);
563 /* Figure out if we have any triggers and if the table being
564 ** inserted into is a view
566 #ifndef SQLITE_OMIT_TRIGGER
567 pTrigger
= sqlite3TriggersExist(pParse
, pTab
, TK_INSERT
, 0, &tmask
);
568 isView
= pTab
->pSelect
!=0;
574 #ifdef SQLITE_OMIT_VIEW
578 assert( (pTrigger
&& tmask
) || (pTrigger
==0 && tmask
==0) );
580 /* If pTab is really a view, make sure it has been initialized.
581 ** ViewGetColumnNames() is a no-op if pTab is not a view.
583 if( sqlite3ViewGetColumnNames(pParse
, pTab
) ){
587 /* Cannot insert into a read-only table.
589 if( sqlite3IsReadOnly(pParse
, pTab
, tmask
) ){
595 v
= sqlite3GetVdbe(pParse
);
596 if( v
==0 ) goto insert_cleanup
;
597 if( pParse
->nested
==0 ) sqlite3VdbeCountChanges(v
);
598 sqlite3BeginWriteOperation(pParse
, pSelect
|| pTrigger
, iDb
);
600 #ifndef SQLITE_OMIT_XFER_OPT
601 /* If the statement is of the form
603 ** INSERT INTO <table1> SELECT * FROM <table2>;
605 ** Then special optimizations can be applied that make the transfer
606 ** very fast and which reduce fragmentation of indices.
608 ** This is the 2nd template.
610 if( pColumn
==0 && xferOptimization(pParse
, pTab
, pSelect
, onError
, iDb
) ){
615 #endif /* SQLITE_OMIT_XFER_OPT */
617 /* If this is an AUTOINCREMENT table, look up the sequence number in the
618 ** sqlite_sequence table and store it in memory cell regAutoinc.
620 regAutoinc
= autoIncBegin(pParse
, iDb
, pTab
);
622 /* Allocate registers for holding the rowid of the new row,
623 ** the content of the new row, and the assembled row record.
625 regRowid
= regIns
= pParse
->nMem
+1;
626 pParse
->nMem
+= pTab
->nCol
+ 1;
627 if( IsVirtual(pTab
) ){
631 regData
= regRowid
+1;
633 /* If the INSERT statement included an IDLIST term, then make sure
634 ** all elements of the IDLIST really are columns of the table and
635 ** remember the column indices.
637 ** If the table has an INTEGER PRIMARY KEY column and that column
638 ** is named in the IDLIST, then record in the ipkColumn variable
639 ** the index into IDLIST of the primary key column. ipkColumn is
640 ** the index of the primary key as it appears in IDLIST, not as
641 ** is appears in the original table. (The index of the INTEGER
642 ** PRIMARY KEY in the original table is pTab->iPKey.)
644 bIdListInOrder
= (pTab
->tabFlags
& TF_OOOHidden
)==0;
646 for(i
=0; i
<pColumn
->nId
; i
++){
647 pColumn
->a
[i
].idx
= -1;
649 for(i
=0; i
<pColumn
->nId
; i
++){
650 for(j
=0; j
<pTab
->nCol
; j
++){
651 if( sqlite3StrICmp(pColumn
->a
[i
].zName
, pTab
->aCol
[j
].zName
)==0 ){
652 pColumn
->a
[i
].idx
= j
;
653 if( i
!=j
) bIdListInOrder
= 0;
654 if( j
==pTab
->iPKey
){
655 ipkColumn
= i
; assert( !withoutRowid
);
661 if( sqlite3IsRowid(pColumn
->a
[i
].zName
) && !withoutRowid
){
665 sqlite3ErrorMsg(pParse
, "table %S has no column named %s",
666 pTabList
, 0, pColumn
->a
[i
].zName
);
667 pParse
->checkSchema
= 1;
674 /* Figure out how many columns of data are supplied. If the data
675 ** is coming from a SELECT statement, then generate a co-routine that
676 ** produces a single row of the SELECT on each invocation. The
677 ** co-routine is the common header to the 3rd and 4th templates.
680 /* Data is coming from a SELECT or from a multi-row VALUES clause.
681 ** Generate a co-routine to run the SELECT. */
682 int regYield
; /* Register holding co-routine entry-point */
683 int addrTop
; /* Top of the co-routine */
684 int rc
; /* Result code */
686 regYield
= ++pParse
->nMem
;
687 addrTop
= sqlite3VdbeCurrentAddr(v
) + 1;
688 sqlite3VdbeAddOp3(v
, OP_InitCoroutine
, regYield
, 0, addrTop
);
689 sqlite3SelectDestInit(&dest
, SRT_Coroutine
, regYield
);
690 dest
.iSdst
= bIdListInOrder
? regData
: 0;
691 dest
.nSdst
= pTab
->nCol
;
692 rc
= sqlite3Select(pParse
, pSelect
, &dest
);
693 regFromSelect
= dest
.iSdst
;
694 if( rc
|| db
->mallocFailed
|| pParse
->nErr
) goto insert_cleanup
;
695 sqlite3VdbeEndCoroutine(v
, regYield
);
696 sqlite3VdbeJumpHere(v
, addrTop
- 1); /* label B: */
697 assert( pSelect
->pEList
);
698 nColumn
= pSelect
->pEList
->nExpr
;
700 /* Set useTempTable to TRUE if the result of the SELECT statement
701 ** should be written into a temporary table (template 4). Set to
702 ** FALSE if each output row of the SELECT can be written directly into
703 ** the destination table (template 3).
705 ** A temp table must be used if the table being updated is also one
706 ** of the tables being read by the SELECT statement. Also use a
707 ** temp table in the case of row triggers.
709 if( pTrigger
|| readsTable(pParse
, iDb
, pTab
) ){
714 /* Invoke the coroutine to extract information from the SELECT
715 ** and add it to a transient table srcTab. The code generated
716 ** here is from the 4th template:
718 ** B: open temp table
719 ** L: yield X, goto M at EOF
720 ** insert row from R..R+n into temp table
724 int regRec
; /* Register to hold packed record */
725 int regTempRowid
; /* Register to hold temp table ROWID */
726 int addrL
; /* Label "L" */
728 srcTab
= pParse
->nTab
++;
729 regRec
= sqlite3GetTempReg(pParse
);
730 regTempRowid
= sqlite3GetTempReg(pParse
);
731 sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, srcTab
, nColumn
);
732 addrL
= sqlite3VdbeAddOp1(v
, OP_Yield
, dest
.iSDParm
); VdbeCoverage(v
);
733 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regFromSelect
, nColumn
, regRec
);
734 sqlite3VdbeAddOp2(v
, OP_NewRowid
, srcTab
, regTempRowid
);
735 sqlite3VdbeAddOp3(v
, OP_Insert
, srcTab
, regRec
, regTempRowid
);
736 sqlite3VdbeGoto(v
, addrL
);
737 sqlite3VdbeJumpHere(v
, addrL
);
738 sqlite3ReleaseTempReg(pParse
, regRec
);
739 sqlite3ReleaseTempReg(pParse
, regTempRowid
);
742 /* This is the case if the data for the INSERT is coming from a
743 ** single-row VALUES clause
746 memset(&sNC
, 0, sizeof(sNC
));
749 assert( useTempTable
==0 );
751 nColumn
= pList
->nExpr
;
752 if( sqlite3ResolveExprListNames(&sNC
, pList
) ){
760 /* If there is no IDLIST term but the table has an integer primary
761 ** key, the set the ipkColumn variable to the integer primary key
762 ** column index in the original table definition.
764 if( pColumn
==0 && nColumn
>0 ){
765 ipkColumn
= pTab
->iPKey
;
768 /* Make sure the number of columns in the source data matches the number
769 ** of columns to be inserted into the table.
771 for(i
=0; i
<pTab
->nCol
; i
++){
772 nHidden
+= (IsHiddenColumn(&pTab
->aCol
[i
]) ? 1 : 0);
774 if( pColumn
==0 && nColumn
&& nColumn
!=(pTab
->nCol
-nHidden
) ){
775 sqlite3ErrorMsg(pParse
,
776 "table %S has %d columns but %d values were supplied",
777 pTabList
, 0, pTab
->nCol
-nHidden
, nColumn
);
780 if( pColumn
!=0 && nColumn
!=pColumn
->nId
){
781 sqlite3ErrorMsg(pParse
, "%d values for %d columns", nColumn
, pColumn
->nId
);
785 /* Initialize the count of rows to be inserted
787 if( db
->flags
& SQLITE_CountRows
){
788 regRowCount
= ++pParse
->nMem
;
789 sqlite3VdbeAddOp2(v
, OP_Integer
, 0, regRowCount
);
792 /* If this is not a view, open the table and and all indices */
795 nIdx
= sqlite3OpenTableAndIndices(pParse
, pTab
, OP_OpenWrite
, 0, -1, 0,
796 &iDataCur
, &iIdxCur
);
797 aRegIdx
= sqlite3DbMallocRawNN(db
, sizeof(int)*(nIdx
+1));
801 for(i
=0, pIdx
=pTab
->pIndex
; i
<nIdx
; pIdx
=pIdx
->pNext
, i
++){
803 aRegIdx
[i
] = ++pParse
->nMem
;
804 pParse
->nMem
+= pIdx
->nColumn
;
807 #ifndef SQLITE_OMIT_UPSERT
809 pTabList
->a
[0].iCursor
= iDataCur
;
810 pUpsert
->pUpsertSrc
= pTabList
;
811 pUpsert
->regData
= regData
;
812 if( pUpsert
->pUpsertTarget
){
813 sqlite3UpsertAnalyzeTarget(pParse
, pTabList
, pUpsert
);
819 /* This is the top of the main insertion loop */
821 /* This block codes the top of loop only. The complete loop is the
822 ** following pseudocode (template 4):
824 ** rewind temp table, if empty goto D
825 ** C: loop over rows of intermediate table
826 ** transfer values form intermediate table into <table>
830 addrInsTop
= sqlite3VdbeAddOp1(v
, OP_Rewind
, srcTab
); VdbeCoverage(v
);
831 addrCont
= sqlite3VdbeCurrentAddr(v
);
833 /* This block codes the top of loop only. The complete loop is the
834 ** following pseudocode (template 3):
836 ** C: yield X, at EOF goto D
837 ** insert the select result into <table> from R..R+n
841 addrInsTop
= addrCont
= sqlite3VdbeAddOp1(v
, OP_Yield
, dest
.iSDParm
);
845 /* Run the BEFORE and INSTEAD OF triggers, if there are any
847 endOfLoop
= sqlite3VdbeMakeLabel(v
);
848 if( tmask
& TRIGGER_BEFORE
){
849 int regCols
= sqlite3GetTempRange(pParse
, pTab
->nCol
+1);
851 /* build the NEW.* reference row. Note that if there is an INTEGER
852 ** PRIMARY KEY into which a NULL is being inserted, that NULL will be
853 ** translated into a unique ID for the row. But on a BEFORE trigger,
854 ** we do not know what the unique ID will be (because the insert has
855 ** not happened yet) so we substitute a rowid of -1
858 sqlite3VdbeAddOp2(v
, OP_Integer
, -1, regCols
);
861 assert( !withoutRowid
);
863 sqlite3VdbeAddOp3(v
, OP_Column
, srcTab
, ipkColumn
, regCols
);
865 assert( pSelect
==0 ); /* Otherwise useTempTable is true */
866 sqlite3ExprCode(pParse
, pList
->a
[ipkColumn
].pExpr
, regCols
);
868 addr1
= sqlite3VdbeAddOp1(v
, OP_NotNull
, regCols
); VdbeCoverage(v
);
869 sqlite3VdbeAddOp2(v
, OP_Integer
, -1, regCols
);
870 sqlite3VdbeJumpHere(v
, addr1
);
871 sqlite3VdbeAddOp1(v
, OP_MustBeInt
, regCols
); VdbeCoverage(v
);
874 /* Cannot have triggers on a virtual table. If it were possible,
875 ** this block would have to account for hidden column.
877 assert( !IsVirtual(pTab
) );
879 /* Create the new column data
881 for(i
=j
=0; i
<pTab
->nCol
; i
++){
883 for(j
=0; j
<pColumn
->nId
; j
++){
884 if( pColumn
->a
[j
].idx
==i
) break;
887 if( (!useTempTable
&& !pList
) || (pColumn
&& j
>=pColumn
->nId
)
888 || (pColumn
==0 && IsOrdinaryHiddenColumn(&pTab
->aCol
[i
])) ){
889 sqlite3ExprCode(pParse
, pTab
->aCol
[i
].pDflt
, regCols
+i
+1);
890 }else if( useTempTable
){
891 sqlite3VdbeAddOp3(v
, OP_Column
, srcTab
, j
, regCols
+i
+1);
893 assert( pSelect
==0 ); /* Otherwise useTempTable is true */
894 sqlite3ExprCodeAndCache(pParse
, pList
->a
[j
].pExpr
, regCols
+i
+1);
896 if( pColumn
==0 && !IsOrdinaryHiddenColumn(&pTab
->aCol
[i
]) ) j
++;
899 /* If this is an INSERT on a view with an INSTEAD OF INSERT trigger,
900 ** do not attempt any conversions before assembling the record.
901 ** If this is a real table, attempt conversions as required by the
902 ** table column affinities.
905 sqlite3TableAffinity(v
, pTab
, regCols
+1);
908 /* Fire BEFORE or INSTEAD OF triggers */
909 sqlite3CodeRowTrigger(pParse
, pTrigger
, TK_INSERT
, 0, TRIGGER_BEFORE
,
910 pTab
, regCols
-pTab
->nCol
-1, onError
, endOfLoop
);
912 sqlite3ReleaseTempRange(pParse
, regCols
, pTab
->nCol
+1);
915 /* Compute the content of the next row to insert into a range of
916 ** registers beginning at regIns.
919 if( IsVirtual(pTab
) ){
920 /* The row that the VUpdate opcode will delete: none */
921 sqlite3VdbeAddOp2(v
, OP_Null
, 0, regIns
);
925 sqlite3VdbeAddOp3(v
, OP_Column
, srcTab
, ipkColumn
, regRowid
);
927 sqlite3VdbeAddOp2(v
, OP_Copy
, regFromSelect
+ipkColumn
, regRowid
);
930 sqlite3ExprCode(pParse
, pList
->a
[ipkColumn
].pExpr
, regRowid
);
931 pOp
= sqlite3VdbeGetOp(v
, -1);
933 if( pOp
->opcode
==OP_Null
&& !IsVirtual(pTab
) ){
935 pOp
->opcode
= OP_NewRowid
;
938 pOp
->p3
= regAutoinc
;
941 /* If the PRIMARY KEY expression is NULL, then use OP_NewRowid
942 ** to generate a unique primary key value.
946 if( !IsVirtual(pTab
) ){
947 addr1
= sqlite3VdbeAddOp1(v
, OP_NotNull
, regRowid
); VdbeCoverage(v
);
948 sqlite3VdbeAddOp3(v
, OP_NewRowid
, iDataCur
, regRowid
, regAutoinc
);
949 sqlite3VdbeJumpHere(v
, addr1
);
951 addr1
= sqlite3VdbeCurrentAddr(v
);
952 sqlite3VdbeAddOp2(v
, OP_IsNull
, regRowid
, addr1
+2); VdbeCoverage(v
);
954 sqlite3VdbeAddOp1(v
, OP_MustBeInt
, regRowid
); VdbeCoverage(v
);
956 }else if( IsVirtual(pTab
) || withoutRowid
){
957 sqlite3VdbeAddOp2(v
, OP_Null
, 0, regRowid
);
959 sqlite3VdbeAddOp3(v
, OP_NewRowid
, iDataCur
, regRowid
, regAutoinc
);
962 autoIncStep(pParse
, regAutoinc
, regRowid
);
964 /* Compute data for all columns of the new entry, beginning
965 ** with the first column.
968 for(i
=0; i
<pTab
->nCol
; i
++){
969 int iRegStore
= regRowid
+1+i
;
970 if( i
==pTab
->iPKey
){
971 /* The value of the INTEGER PRIMARY KEY column is always a NULL.
972 ** Whenever this column is read, the rowid will be substituted
973 ** in its place. Hence, fill this column with a NULL to avoid
974 ** taking up data space with information that will never be used.
975 ** As there may be shallow copies of this value, make it a soft-NULL */
976 sqlite3VdbeAddOp1(v
, OP_SoftNull
, iRegStore
);
980 if( IsHiddenColumn(&pTab
->aCol
[i
]) ){
987 for(j
=0; j
<pColumn
->nId
; j
++){
988 if( pColumn
->a
[j
].idx
==i
) break;
991 if( j
<0 || nColumn
==0 || (pColumn
&& j
>=pColumn
->nId
) ){
992 sqlite3ExprCodeFactorable(pParse
, pTab
->aCol
[i
].pDflt
, iRegStore
);
993 }else if( useTempTable
){
994 sqlite3VdbeAddOp3(v
, OP_Column
, srcTab
, j
, iRegStore
);
996 if( regFromSelect
!=regData
){
997 sqlite3VdbeAddOp2(v
, OP_SCopy
, regFromSelect
+j
, iRegStore
);
1000 sqlite3ExprCode(pParse
, pList
->a
[j
].pExpr
, iRegStore
);
1004 /* Generate code to check constraints and generate index keys and
1005 ** do the insertion.
1007 #ifndef SQLITE_OMIT_VIRTUALTABLE
1008 if( IsVirtual(pTab
) ){
1009 const char *pVTab
= (const char *)sqlite3GetVTable(db
, pTab
);
1010 sqlite3VtabMakeWritable(pParse
, pTab
);
1011 sqlite3VdbeAddOp4(v
, OP_VUpdate
, 1, pTab
->nCol
+2, regIns
, pVTab
, P4_VTAB
);
1012 sqlite3VdbeChangeP5(v
, onError
==OE_Default
? OE_Abort
: onError
);
1013 sqlite3MayAbort(pParse
);
1017 int isReplace
; /* Set to true if constraints may cause a replace */
1018 int bUseSeek
; /* True to use OPFLAG_SEEKRESULT */
1019 sqlite3GenerateConstraintChecks(pParse
, pTab
, aRegIdx
, iDataCur
, iIdxCur
,
1020 regIns
, 0, ipkColumn
>=0, onError
, endOfLoop
, &isReplace
, 0, pUpsert
1022 sqlite3FkCheck(pParse
, pTab
, 0, regIns
, 0, 0);
1024 /* Set the OPFLAG_USESEEKRESULT flag if either (a) there are no REPLACE
1025 ** constraints or (b) there are no triggers and this table is not a
1026 ** parent table in a foreign key constraint. It is safe to set the
1027 ** flag in the second case as if any REPLACE constraint is hit, an
1028 ** OP_Delete or OP_IdxDelete instruction will be executed on each
1029 ** cursor that is disturbed. And these instructions both clear the
1030 ** VdbeCursor.seekResult variable, disabling the OPFLAG_USESEEKRESULT
1031 ** functionality. */
1032 bUseSeek
= (isReplace
==0 || (pTrigger
==0 &&
1033 ((db
->flags
& SQLITE_ForeignKeys
)==0 || sqlite3FkReferences(pTab
)==0)
1035 sqlite3CompleteInsertion(pParse
, pTab
, iDataCur
, iIdxCur
,
1036 regIns
, aRegIdx
, 0, appendFlag
, bUseSeek
1041 /* Update the count of rows that are inserted
1043 if( (db
->flags
& SQLITE_CountRows
)!=0 ){
1044 sqlite3VdbeAddOp2(v
, OP_AddImm
, regRowCount
, 1);
1048 /* Code AFTER triggers */
1049 sqlite3CodeRowTrigger(pParse
, pTrigger
, TK_INSERT
, 0, TRIGGER_AFTER
,
1050 pTab
, regData
-2-pTab
->nCol
, onError
, endOfLoop
);
1053 /* The bottom of the main insertion loop, if the data source
1054 ** is a SELECT statement.
1056 sqlite3VdbeResolveLabel(v
, endOfLoop
);
1058 sqlite3VdbeAddOp2(v
, OP_Next
, srcTab
, addrCont
); VdbeCoverage(v
);
1059 sqlite3VdbeJumpHere(v
, addrInsTop
);
1060 sqlite3VdbeAddOp1(v
, OP_Close
, srcTab
);
1061 }else if( pSelect
){
1062 sqlite3VdbeGoto(v
, addrCont
);
1063 sqlite3VdbeJumpHere(v
, addrInsTop
);
1067 /* Update the sqlite_sequence table by storing the content of the
1068 ** maximum rowid counter values recorded while inserting into
1069 ** autoincrement tables.
1071 if( pParse
->nested
==0 && pParse
->pTriggerTab
==0 ){
1072 sqlite3AutoincrementEnd(pParse
);
1076 ** Return the number of rows inserted. If this routine is
1077 ** generating code because of a call to sqlite3NestedParse(), do not
1078 ** invoke the callback function.
1080 if( (db
->flags
&SQLITE_CountRows
) && !pParse
->nested
&& !pParse
->pTriggerTab
){
1081 sqlite3VdbeAddOp2(v
, OP_ResultRow
, regRowCount
, 1);
1082 sqlite3VdbeSetNumCols(v
, 1);
1083 sqlite3VdbeSetColName(v
, 0, COLNAME_NAME
, "rows inserted", SQLITE_STATIC
);
1087 sqlite3SrcListDelete(db
, pTabList
);
1088 sqlite3ExprListDelete(db
, pList
);
1089 sqlite3UpsertDelete(db
, pUpsert
);
1090 sqlite3SelectDelete(db
, pSelect
);
1091 sqlite3IdListDelete(db
, pColumn
);
1092 sqlite3DbFree(db
, aRegIdx
);
1095 /* Make sure "isView" and other macros defined above are undefined. Otherwise
1096 ** they may interfere with compilation of other functions in this file
1097 ** (or in another file, if this file becomes part of the amalgamation). */
1109 ** Meanings of bits in of pWalker->eCode for checkConstraintUnchanged()
1111 #define CKCNSTRNT_COLUMN 0x01 /* CHECK constraint uses a changing column */
1112 #define CKCNSTRNT_ROWID 0x02 /* CHECK constraint references the ROWID */
1114 /* This is the Walker callback from checkConstraintUnchanged(). Set
1115 ** bit 0x01 of pWalker->eCode if
1116 ** pWalker->eCode to 0 if this expression node references any of the
1117 ** columns that are being modifed by an UPDATE statement.
1119 static int checkConstraintExprNode(Walker
*pWalker
, Expr
*pExpr
){
1120 if( pExpr
->op
==TK_COLUMN
){
1121 assert( pExpr
->iColumn
>=0 || pExpr
->iColumn
==-1 );
1122 if( pExpr
->iColumn
>=0 ){
1123 if( pWalker
->u
.aiCol
[pExpr
->iColumn
]>=0 ){
1124 pWalker
->eCode
|= CKCNSTRNT_COLUMN
;
1127 pWalker
->eCode
|= CKCNSTRNT_ROWID
;
1130 return WRC_Continue
;
1134 ** pExpr is a CHECK constraint on a row that is being UPDATE-ed. The
1135 ** only columns that are modified by the UPDATE are those for which
1136 ** aiChng[i]>=0, and also the ROWID is modified if chngRowid is true.
1138 ** Return true if CHECK constraint pExpr does not use any of the
1139 ** changing columns (or the rowid if it is changing). In other words,
1140 ** return true if this CHECK constraint can be skipped when validating
1141 ** the new row in the UPDATE statement.
1143 static int checkConstraintUnchanged(Expr
*pExpr
, int *aiChng
, int chngRowid
){
1145 memset(&w
, 0, sizeof(w
));
1147 w
.xExprCallback
= checkConstraintExprNode
;
1149 sqlite3WalkExpr(&w
, pExpr
);
1151 testcase( (w
.eCode
& CKCNSTRNT_ROWID
)!=0 );
1152 w
.eCode
&= ~CKCNSTRNT_ROWID
;
1154 testcase( w
.eCode
==0 );
1155 testcase( w
.eCode
==CKCNSTRNT_COLUMN
);
1156 testcase( w
.eCode
==CKCNSTRNT_ROWID
);
1157 testcase( w
.eCode
==(CKCNSTRNT_ROWID
|CKCNSTRNT_COLUMN
) );
1162 ** An instance of the ConstraintAddr object remembers the byte-code addresses
1163 ** for sections of the constraint checks that deal with uniqueness constraints
1164 ** on the rowid and on the upsert constraint.
1166 ** This information is passed into checkReorderConstraintChecks() to insert
1167 ** some OP_Goto operations so that the rowid and upsert constraints occur
1168 ** in the correct order relative to other constraints.
1170 typedef struct ConstraintAddr ConstraintAddr
;
1171 struct ConstraintAddr
{
1172 int ipkTop
; /* Subroutine for rowid constraint check */
1173 int upsertTop
; /* Label for upsert constraint check subroutine */
1174 int ipkBtm
; /* Return opcode rowid constraint check */
1175 int upsertBtm
; /* upsert constraint returns to this label */
1179 ** Generate any OP_Goto operations needed to cause constraints to be
1180 ** run that haven't already been run.
1182 static void reorderConstraintChecks(Vdbe
*v
, ConstraintAddr
*p
){
1184 sqlite3VdbeGoto(v
, p
->upsertTop
);
1185 VdbeComment((v
, "call upsert subroutine"));
1186 sqlite3VdbeResolveLabel(v
, p
->upsertBtm
);
1190 sqlite3VdbeGoto(v
, p
->ipkTop
);
1191 VdbeComment((v
, "call rowid constraint-check subroutine"));
1192 sqlite3VdbeJumpHere(v
, p
->ipkBtm
);
1198 ** Generate code to do constraint checks prior to an INSERT or an UPDATE
1201 ** The regNewData parameter is the first register in a range that contains
1202 ** the data to be inserted or the data after the update. There will be
1203 ** pTab->nCol+1 registers in this range. The first register (the one
1204 ** that regNewData points to) will contain the new rowid, or NULL in the
1205 ** case of a WITHOUT ROWID table. The second register in the range will
1206 ** contain the content of the first table column. The third register will
1207 ** contain the content of the second table column. And so forth.
1209 ** The regOldData parameter is similar to regNewData except that it contains
1210 ** the data prior to an UPDATE rather than afterwards. regOldData is zero
1211 ** for an INSERT. This routine can distinguish between UPDATE and INSERT by
1212 ** checking regOldData for zero.
1214 ** For an UPDATE, the pkChng boolean is true if the true primary key (the
1215 ** rowid for a normal table or the PRIMARY KEY for a WITHOUT ROWID table)
1216 ** might be modified by the UPDATE. If pkChng is false, then the key of
1217 ** the iDataCur content table is guaranteed to be unchanged by the UPDATE.
1219 ** For an INSERT, the pkChng boolean indicates whether or not the rowid
1220 ** was explicitly specified as part of the INSERT statement. If pkChng
1221 ** is zero, it means that the either rowid is computed automatically or
1222 ** that the table is a WITHOUT ROWID table and has no rowid. On an INSERT,
1223 ** pkChng will only be true if the INSERT statement provides an integer
1224 ** value for either the rowid column or its INTEGER PRIMARY KEY alias.
1226 ** The code generated by this routine will store new index entries into
1227 ** registers identified by aRegIdx[]. No index entry is created for
1228 ** indices where aRegIdx[i]==0. The order of indices in aRegIdx[] is
1229 ** the same as the order of indices on the linked list of indices
1232 ** The caller must have already opened writeable cursors on the main
1233 ** table and all applicable indices (that is to say, all indices for which
1234 ** aRegIdx[] is not zero). iDataCur is the cursor for the main table when
1235 ** inserting or updating a rowid table, or the cursor for the PRIMARY KEY
1236 ** index when operating on a WITHOUT ROWID table. iIdxCur is the cursor
1237 ** for the first index in the pTab->pIndex list. Cursors for other indices
1238 ** are at iIdxCur+N for the N-th element of the pTab->pIndex list.
1240 ** This routine also generates code to check constraints. NOT NULL,
1241 ** CHECK, and UNIQUE constraints are all checked. If a constraint fails,
1242 ** then the appropriate action is performed. There are five possible
1243 ** actions: ROLLBACK, ABORT, FAIL, REPLACE, and IGNORE.
1245 ** Constraint type Action What Happens
1246 ** --------------- ---------- ----------------------------------------
1247 ** any ROLLBACK The current transaction is rolled back and
1248 ** sqlite3_step() returns immediately with a
1249 ** return code of SQLITE_CONSTRAINT.
1251 ** any ABORT Back out changes from the current command
1252 ** only (do not do a complete rollback) then
1253 ** cause sqlite3_step() to return immediately
1254 ** with SQLITE_CONSTRAINT.
1256 ** any FAIL Sqlite3_step() returns immediately with a
1257 ** return code of SQLITE_CONSTRAINT. The
1258 ** transaction is not rolled back and any
1259 ** changes to prior rows are retained.
1261 ** any IGNORE The attempt in insert or update the current
1262 ** row is skipped, without throwing an error.
1263 ** Processing continues with the next row.
1264 ** (There is an immediate jump to ignoreDest.)
1266 ** NOT NULL REPLACE The NULL value is replace by the default
1267 ** value for that column. If the default value
1268 ** is NULL, the action is the same as ABORT.
1270 ** UNIQUE REPLACE The other row that conflicts with the row
1271 ** being inserted is removed.
1273 ** CHECK REPLACE Illegal. The results in an exception.
1275 ** Which action to take is determined by the overrideError parameter.
1276 ** Or if overrideError==OE_Default, then the pParse->onError parameter
1277 ** is used. Or if pParse->onError==OE_Default then the onError value
1278 ** for the constraint is used.
1280 void sqlite3GenerateConstraintChecks(
1281 Parse
*pParse
, /* The parser context */
1282 Table
*pTab
, /* The table being inserted or updated */
1283 int *aRegIdx
, /* Use register aRegIdx[i] for index i. 0 for unused */
1284 int iDataCur
, /* Canonical data cursor (main table or PK index) */
1285 int iIdxCur
, /* First index cursor */
1286 int regNewData
, /* First register in a range holding values to insert */
1287 int regOldData
, /* Previous content. 0 for INSERTs */
1288 u8 pkChng
, /* Non-zero if the rowid or PRIMARY KEY changed */
1289 u8 overrideError
, /* Override onError to this if not OE_Default */
1290 int ignoreDest
, /* Jump to this label on an OE_Ignore resolution */
1291 int *pbMayReplace
, /* OUT: Set to true if constraint may cause a replace */
1292 int *aiChng
, /* column i is unchanged if aiChng[i]<0 */
1293 Upsert
*pUpsert
/* ON CONFLICT clauses, if any. NULL otherwise */
1295 Vdbe
*v
; /* VDBE under constrution */
1296 Index
*pIdx
; /* Pointer to one of the indices */
1297 Index
*pPk
= 0; /* The PRIMARY KEY index */
1298 sqlite3
*db
; /* Database connection */
1299 int i
; /* loop counter */
1300 int ix
; /* Index loop counter */
1301 int nCol
; /* Number of columns */
1302 int onError
; /* Conflict resolution strategy */
1303 int addr1
; /* Address of jump instruction */
1304 int seenReplace
= 0; /* True if REPLACE is used to resolve INT PK conflict */
1305 int nPkField
; /* Number of fields in PRIMARY KEY. 1 for ROWID tables */
1306 ConstraintAddr sAddr
;/* Address information for constraint reordering */
1307 Index
*pUpIdx
= 0; /* Index to which to apply the upsert */
1308 u8 isUpdate
; /* True if this is an UPDATE operation */
1309 u8 bAffinityDone
= 0; /* True if the OP_Affinity operation has been run */
1310 int upsertBypass
= 0; /* Address of Goto to bypass upsert subroutine */
1312 isUpdate
= regOldData
!=0;
1314 v
= sqlite3GetVdbe(pParse
);
1316 assert( pTab
->pSelect
==0 ); /* This table is not a VIEW */
1319 sAddr
.upsertTop
= 0;
1321 /* pPk is the PRIMARY KEY index for WITHOUT ROWID tables and NULL for
1322 ** normal rowid tables. nPkField is the number of key fields in the
1323 ** pPk index or 1 for a rowid table. In other words, nPkField is the
1324 ** number of fields in the true primary key of the table. */
1325 if( HasRowid(pTab
) ){
1329 pPk
= sqlite3PrimaryKeyIndex(pTab
);
1330 nPkField
= pPk
->nKeyCol
;
1333 /* Record that this module has started */
1334 VdbeModuleComment((v
, "BEGIN: GenCnstCks(%d,%d,%d,%d,%d)",
1335 iDataCur
, iIdxCur
, regNewData
, regOldData
, pkChng
));
1337 /* Test all NOT NULL constraints.
1339 for(i
=0; i
<nCol
; i
++){
1340 if( i
==pTab
->iPKey
){
1341 continue; /* ROWID is never NULL */
1343 if( aiChng
&& aiChng
[i
]<0 ){
1344 /* Don't bother checking for NOT NULL on columns that do not change */
1347 onError
= pTab
->aCol
[i
].notNull
;
1348 if( onError
==OE_None
) continue; /* This column is allowed to be NULL */
1349 if( overrideError
!=OE_Default
){
1350 onError
= overrideError
;
1351 }else if( onError
==OE_Default
){
1354 if( onError
==OE_Replace
&& pTab
->aCol
[i
].pDflt
==0 ){
1357 assert( onError
==OE_Rollback
|| onError
==OE_Abort
|| onError
==OE_Fail
1358 || onError
==OE_Ignore
|| onError
==OE_Replace
);
1361 sqlite3MayAbort(pParse
);
1365 char *zMsg
= sqlite3MPrintf(db
, "%s.%s", pTab
->zName
,
1366 pTab
->aCol
[i
].zName
);
1367 sqlite3VdbeAddOp3(v
, OP_HaltIfNull
, SQLITE_CONSTRAINT_NOTNULL
, onError
,
1369 sqlite3VdbeAppendP4(v
, zMsg
, P4_DYNAMIC
);
1370 sqlite3VdbeChangeP5(v
, P5_ConstraintNotNull
);
1375 sqlite3VdbeAddOp2(v
, OP_IsNull
, regNewData
+1+i
, ignoreDest
);
1380 assert( onError
==OE_Replace
);
1381 addr1
= sqlite3VdbeAddOp1(v
, OP_NotNull
, regNewData
+1+i
);
1383 sqlite3ExprCode(pParse
, pTab
->aCol
[i
].pDflt
, regNewData
+1+i
);
1384 sqlite3VdbeJumpHere(v
, addr1
);
1390 /* Test all CHECK constraints
1392 #ifndef SQLITE_OMIT_CHECK
1393 if( pTab
->pCheck
&& (db
->flags
& SQLITE_IgnoreChecks
)==0 ){
1394 ExprList
*pCheck
= pTab
->pCheck
;
1395 pParse
->iSelfTab
= -(regNewData
+1);
1396 onError
= overrideError
!=OE_Default
? overrideError
: OE_Abort
;
1397 for(i
=0; i
<pCheck
->nExpr
; i
++){
1399 Expr
*pExpr
= pCheck
->a
[i
].pExpr
;
1400 if( aiChng
&& checkConstraintUnchanged(pExpr
, aiChng
, pkChng
) ) continue;
1401 allOk
= sqlite3VdbeMakeLabel(v
);
1402 sqlite3ExprIfTrue(pParse
, pExpr
, allOk
, SQLITE_JUMPIFNULL
);
1403 if( onError
==OE_Ignore
){
1404 sqlite3VdbeGoto(v
, ignoreDest
);
1406 char *zName
= pCheck
->a
[i
].zName
;
1407 if( zName
==0 ) zName
= pTab
->zName
;
1408 if( onError
==OE_Replace
) onError
= OE_Abort
; /* IMP: R-15569-63625 */
1409 sqlite3HaltConstraint(pParse
, SQLITE_CONSTRAINT_CHECK
,
1410 onError
, zName
, P4_TRANSIENT
,
1411 P5_ConstraintCheck
);
1413 sqlite3VdbeResolveLabel(v
, allOk
);
1415 pParse
->iSelfTab
= 0;
1417 #endif /* !defined(SQLITE_OMIT_CHECK) */
1419 /* UNIQUE and PRIMARY KEY constraints should be handled in the following
1422 ** (1) OE_Abort, OE_Fail, OE_Rollback, OE_Ignore
1426 ** OE_Fail and OE_Ignore must happen before any changes are made.
1427 ** OE_Update guarantees that only a single row will change, so it
1428 ** must happen before OE_Replace. Technically, OE_Abort and OE_Rollback
1429 ** could happen in any order, but they are grouped up front for
1432 ** Constraint checking code is generated in this order:
1433 ** (A) The rowid constraint
1434 ** (B) Unique index constraints that do not have OE_Replace as their
1435 ** default conflict resolution strategy
1436 ** (C) Unique index that do use OE_Replace by default.
1438 ** The ordering of (2) and (3) is accomplished by making sure the linked
1439 ** list of indexes attached to a table puts all OE_Replace indexes last
1440 ** in the list. See sqlite3CreateIndex() for where that happens.
1443 /* If there is an ON CONFLICT clause without a constraint-target
1444 ** (In other words, one of "ON CONFLICT DO NOTHING" or
1445 ** "ON DUPLICATION KEY UPDATE") then change the overrideError to
1446 ** whichever is appropriate.
1449 if( pUpsert
->pUpsertTarget
==0 ){
1450 if( pUpsert
->pUpsertSet
==0 ){
1451 /* An ON CONFLICT DO NOTHING clause, without a constraint-target.
1452 ** Make all unique constraint resolution be OE_Ignore */
1453 overrideError
= OE_Ignore
;
1456 /* An ON DUPLICATE KEY UPDATE clause. All unique constraints
1457 ** do upsert processing */
1458 overrideError
= OE_Update
;
1460 }else if( (pUpIdx
= pUpsert
->pUpsertIdx
)!=0 ){
1461 sAddr
.upsertTop
= sqlite3VdbeMakeLabel(v
);
1462 sAddr
.upsertBtm
= sqlite3VdbeMakeLabel(v
);
1466 /* If rowid is changing, make sure the new rowid does not previously
1467 ** exist in the table.
1469 if( pkChng
&& pPk
==0 ){
1470 int addrRowidOk
= sqlite3VdbeMakeLabel(v
);
1472 /* Figure out what action to take in case of a rowid collision */
1473 onError
= pTab
->keyConf
;
1474 if( overrideError
!=OE_Default
){
1475 onError
= overrideError
;
1476 }else if( onError
==OE_Default
){
1481 /* pkChng!=0 does not mean that the rowid has changed, only that
1482 ** it might have changed. Skip the conflict logic below if the rowid
1484 sqlite3VdbeAddOp3(v
, OP_Eq
, regNewData
, addrRowidOk
, regOldData
);
1485 sqlite3VdbeChangeP5(v
, SQLITE_NOTNULL
);
1489 /* figure out whether or not upsert applies in this case */
1490 if( pUpsert
&& pUpsert
->pUpsertIdx
==0 ){
1491 if( pUpsert
->pUpsertSet
==0 ){
1492 onError
= OE_Ignore
; /* DO NOTHING is the same as INSERT OR IGNORE */
1494 onError
= OE_Update
; /* DO UPDATE */
1498 /* If the response to a rowid conflict is REPLACE but the response
1499 ** to some other UNIQUE constraint is FAIL or IGNORE, then we need
1500 ** to defer the running of the rowid conflict checking until after
1501 ** the UNIQUE constraints have run.
1503 assert( OE_Update
>OE_Replace
);
1504 assert( OE_Ignore
<OE_Replace
);
1505 assert( OE_Fail
<OE_Replace
);
1506 assert( OE_Abort
<OE_Replace
);
1507 assert( OE_Rollback
<OE_Replace
);
1508 if( onError
>=OE_Replace
1509 && onError
!=overrideError
1512 sAddr
.ipkTop
= sqlite3VdbeAddOp0(v
, OP_Goto
)+1;
1515 /* Check to see if the new rowid already exists in the table. Skip
1516 ** the following conflict logic if it does not. */
1517 VdbeNoopComment((v
, "constraint checks for ROWID"));
1518 sqlite3VdbeAddOp3(v
, OP_NotExists
, iDataCur
, addrRowidOk
, regNewData
);
1524 /* Fall thru into the next case */
1529 sqlite3RowidConstraint(pParse
, onError
, pTab
);
1533 /* If there are DELETE triggers on this table and the
1534 ** recursive-triggers flag is set, call GenerateRowDelete() to
1535 ** remove the conflicting row from the table. This will fire
1536 ** the triggers and remove both the table and index b-tree entries.
1538 ** Otherwise, if there are no triggers or the recursive-triggers
1539 ** flag is not set, but the table has one or more indexes, call
1540 ** GenerateRowIndexDelete(). This removes the index b-tree entries
1541 ** only. The table b-tree entry will be replaced by the new entry
1542 ** when it is inserted.
1544 ** If either GenerateRowDelete() or GenerateRowIndexDelete() is called,
1545 ** also invoke MultiWrite() to indicate that this VDBE may require
1546 ** statement rollback (if the statement is aborted after the delete
1547 ** takes place). Earlier versions called sqlite3MultiWrite() regardless,
1548 ** but being more selective here allows statements like:
1550 ** REPLACE INTO t(rowid) VALUES($newrowid)
1552 ** to run without a statement journal if there are no indexes on the
1555 Trigger
*pTrigger
= 0;
1556 if( db
->flags
&SQLITE_RecTriggers
){
1557 pTrigger
= sqlite3TriggersExist(pParse
, pTab
, TK_DELETE
, 0, 0);
1559 if( pTrigger
|| sqlite3FkRequired(pParse
, pTab
, 0, 0) ){
1560 sqlite3MultiWrite(pParse
);
1561 sqlite3GenerateRowDelete(pParse
, pTab
, pTrigger
, iDataCur
, iIdxCur
,
1562 regNewData
, 1, 0, OE_Replace
, 1, -1);
1564 #ifdef SQLITE_ENABLE_PREUPDATE_HOOK
1565 assert( HasRowid(pTab
) );
1566 /* This OP_Delete opcode fires the pre-update-hook only. It does
1567 ** not modify the b-tree. It is more efficient to let the coming
1568 ** OP_Insert replace the existing entry than it is to delete the
1569 ** existing entry and then insert a new one. */
1570 sqlite3VdbeAddOp2(v
, OP_Delete
, iDataCur
, OPFLAG_ISNOOP
);
1571 sqlite3VdbeAppendP4(v
, pTab
, P4_TABLE
);
1572 #endif /* SQLITE_ENABLE_PREUPDATE_HOOK */
1574 sqlite3MultiWrite(pParse
);
1575 sqlite3GenerateRowIndexDelete(pParse
, pTab
, iDataCur
, iIdxCur
,0,-1);
1581 #ifndef SQLITE_OMIT_UPSERT
1583 sqlite3UpsertDoUpdate(pParse
, pUpsert
, pTab
, 0, iDataCur
, 0);
1588 sqlite3VdbeGoto(v
, ignoreDest
);
1592 sqlite3VdbeResolveLabel(v
, addrRowidOk
);
1594 sAddr
.ipkBtm
= sqlite3VdbeAddOp0(v
, OP_Goto
);
1595 sqlite3VdbeJumpHere(v
, sAddr
.ipkTop
-1);
1599 /* Test all UNIQUE constraints by creating entries for each UNIQUE
1600 ** index and making sure that duplicate entries do not already exist.
1601 ** Compute the revised record entries for indices as we go.
1603 ** This loop also handles the case of the PRIMARY KEY index for a
1604 ** WITHOUT ROWID table.
1606 for(ix
=0, pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
, ix
++){
1607 int regIdx
; /* Range of registers hold conent for pIdx */
1608 int regR
; /* Range of registers holding conflicting PK */
1609 int iThisCur
; /* Cursor for this UNIQUE index */
1610 int addrUniqueOk
; /* Jump here if the UNIQUE constraint is satisfied */
1612 if( aRegIdx
[ix
]==0 ) continue; /* Skip indices that do not change */
1613 VdbeNoopComment((v
, "constraint checks for %s", pIdx
->zName
));
1614 if( bAffinityDone
==0 ){
1615 sqlite3TableAffinity(v
, pTab
, regNewData
+1);
1618 iThisCur
= iIdxCur
+ix
;
1620 addrUniqueOk
= sAddr
.upsertBtm
;
1622 addrUniqueOk
= sqlite3VdbeMakeLabel(v
);
1625 /* Skip partial indices for which the WHERE clause is not true */
1626 if( pIdx
->pPartIdxWhere
){
1627 sqlite3VdbeAddOp2(v
, OP_Null
, 0, aRegIdx
[ix
]);
1628 pParse
->iSelfTab
= -(regNewData
+1);
1629 sqlite3ExprIfFalseDup(pParse
, pIdx
->pPartIdxWhere
, addrUniqueOk
,
1631 pParse
->iSelfTab
= 0;
1634 /* Create a record for this index entry as it should appear after
1635 ** the insert or update. Store that record in the aRegIdx[ix] register
1637 regIdx
= aRegIdx
[ix
]+1;
1638 for(i
=0; i
<pIdx
->nColumn
; i
++){
1639 int iField
= pIdx
->aiColumn
[i
];
1641 if( iField
==XN_EXPR
){
1642 pParse
->iSelfTab
= -(regNewData
+1);
1643 sqlite3ExprCodeCopy(pParse
, pIdx
->aColExpr
->a
[i
].pExpr
, regIdx
+i
);
1644 pParse
->iSelfTab
= 0;
1645 VdbeComment((v
, "%s column %d", pIdx
->zName
, i
));
1647 if( iField
==XN_ROWID
|| iField
==pTab
->iPKey
){
1650 x
= iField
+ regNewData
+ 1;
1652 sqlite3VdbeAddOp2(v
, iField
<0 ? OP_IntCopy
: OP_SCopy
, x
, regIdx
+i
);
1653 VdbeComment((v
, "%s", iField
<0 ? "rowid" : pTab
->aCol
[iField
].zName
));
1656 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regIdx
, pIdx
->nColumn
, aRegIdx
[ix
]);
1657 VdbeComment((v
, "for %s", pIdx
->zName
));
1658 #ifdef SQLITE_ENABLE_NULL_TRIM
1659 if( pIdx
->idxType
==2 ) sqlite3SetMakeRecordP5(v
, pIdx
->pTable
);
1662 /* In an UPDATE operation, if this index is the PRIMARY KEY index
1663 ** of a WITHOUT ROWID table and there has been no change the
1664 ** primary key, then no collision is possible. The collision detection
1665 ** logic below can all be skipped. */
1666 if( isUpdate
&& pPk
==pIdx
&& pkChng
==0 ){
1667 sqlite3VdbeResolveLabel(v
, addrUniqueOk
);
1671 /* Find out what action to take in case there is a uniqueness conflict */
1672 onError
= pIdx
->onError
;
1673 if( onError
==OE_None
){
1674 sqlite3VdbeResolveLabel(v
, addrUniqueOk
);
1675 continue; /* pIdx is not a UNIQUE index */
1677 if( overrideError
!=OE_Default
){
1678 onError
= overrideError
;
1679 }else if( onError
==OE_Default
){
1682 if( onError
==OE_Replace
){
1683 reorderConstraintChecks(v
, &sAddr
);
1686 /* Figure out if the upsert clause applies to this index */
1688 if( pUpsert
->pUpsertSet
==0 ){
1689 onError
= OE_Ignore
; /* DO NOTHING is the same as INSERT OR IGNORE */
1691 onError
= OE_Update
; /* DO UPDATE */
1693 upsertBypass
= sqlite3VdbeGoto(v
, 0);
1694 VdbeComment((v
, "Upsert bypass"));
1695 sqlite3VdbeResolveLabel(v
, sAddr
.upsertTop
);
1698 /* Collision detection may be omitted if all of the following are true:
1699 ** (1) The conflict resolution algorithm is REPLACE
1700 ** (2) The table is a WITHOUT ROWID table
1701 ** (3) There are no secondary indexes on the table
1702 ** (4) No delete triggers need to be fired if there is a conflict
1703 ** (5) No FK constraint counters need to be updated if a conflict occurs.
1705 if( (ix
==0 && pIdx
->pNext
==0) /* Condition 3 */
1706 && pPk
==pIdx
/* Condition 2 */
1707 && onError
==OE_Replace
/* Condition 1 */
1708 && ( 0==(db
->flags
&SQLITE_RecTriggers
) || /* Condition 4 */
1709 0==sqlite3TriggersExist(pParse
, pTab
, TK_DELETE
, 0, 0))
1710 && ( 0==(db
->flags
&SQLITE_ForeignKeys
) || /* Condition 5 */
1711 (0==pTab
->pFKey
&& 0==sqlite3FkReferences(pTab
)))
1713 sqlite3VdbeResolveLabel(v
, addrUniqueOk
);
1717 /* Check to see if the new index entry will be unique */
1718 sqlite3ExprCachePush(pParse
);
1719 sqlite3VdbeAddOp4Int(v
, OP_NoConflict
, iThisCur
, addrUniqueOk
,
1720 regIdx
, pIdx
->nKeyCol
); VdbeCoverage(v
);
1722 /* Generate code to handle collisions */
1723 regR
= (pIdx
==pPk
) ? regIdx
: sqlite3GetTempRange(pParse
, nPkField
);
1724 if( isUpdate
|| onError
==OE_Replace
){
1725 if( HasRowid(pTab
) ){
1726 sqlite3VdbeAddOp2(v
, OP_IdxRowid
, iThisCur
, regR
);
1727 /* Conflict only if the rowid of the existing index entry
1728 ** is different from old-rowid */
1730 sqlite3VdbeAddOp3(v
, OP_Eq
, regR
, addrUniqueOk
, regOldData
);
1731 sqlite3VdbeChangeP5(v
, SQLITE_NOTNULL
);
1736 /* Extract the PRIMARY KEY from the end of the index entry and
1737 ** store it in registers regR..regR+nPk-1 */
1739 for(i
=0; i
<pPk
->nKeyCol
; i
++){
1740 assert( pPk
->aiColumn
[i
]>=0 );
1741 x
= sqlite3ColumnOfIndex(pIdx
, pPk
->aiColumn
[i
]);
1742 sqlite3VdbeAddOp3(v
, OP_Column
, iThisCur
, x
, regR
+i
);
1743 VdbeComment((v
, "%s.%s", pTab
->zName
,
1744 pTab
->aCol
[pPk
->aiColumn
[i
]].zName
));
1748 /* If currently processing the PRIMARY KEY of a WITHOUT ROWID
1749 ** table, only conflict if the new PRIMARY KEY values are actually
1750 ** different from the old.
1752 ** For a UNIQUE index, only conflict if the PRIMARY KEY values
1753 ** of the matched index row are different from the original PRIMARY
1754 ** KEY values of this row before the update. */
1755 int addrJump
= sqlite3VdbeCurrentAddr(v
)+pPk
->nKeyCol
;
1757 int regCmp
= (IsPrimaryKeyIndex(pIdx
) ? regIdx
: regR
);
1759 for(i
=0; i
<pPk
->nKeyCol
; i
++){
1760 char *p4
= (char*)sqlite3LocateCollSeq(pParse
, pPk
->azColl
[i
]);
1761 x
= pPk
->aiColumn
[i
];
1763 if( i
==(pPk
->nKeyCol
-1) ){
1764 addrJump
= addrUniqueOk
;
1767 sqlite3VdbeAddOp4(v
, op
,
1768 regOldData
+1+x
, addrJump
, regCmp
+i
, p4
, P4_COLLSEQ
1770 sqlite3VdbeChangeP5(v
, SQLITE_NOTNULL
);
1771 VdbeCoverageIf(v
, op
==OP_Eq
);
1772 VdbeCoverageIf(v
, op
==OP_Ne
);
1778 /* Generate code that executes if the new index entry is not unique */
1779 assert( onError
==OE_Rollback
|| onError
==OE_Abort
|| onError
==OE_Fail
1780 || onError
==OE_Ignore
|| onError
==OE_Replace
|| onError
==OE_Update
);
1785 sqlite3UniqueConstraint(pParse
, onError
, pIdx
);
1788 #ifndef SQLITE_OMIT_UPSERT
1790 sqlite3UpsertDoUpdate(pParse
, pUpsert
, pTab
, pIdx
, iDataCur
, iIdxCur
);
1795 sqlite3VdbeGoto(v
, ignoreDest
);
1799 Trigger
*pTrigger
= 0;
1800 assert( onError
==OE_Replace
);
1801 sqlite3MultiWrite(pParse
);
1802 if( db
->flags
&SQLITE_RecTriggers
){
1803 pTrigger
= sqlite3TriggersExist(pParse
, pTab
, TK_DELETE
, 0, 0);
1805 sqlite3GenerateRowDelete(pParse
, pTab
, pTrigger
, iDataCur
, iIdxCur
,
1806 regR
, nPkField
, 0, OE_Replace
,
1807 (pIdx
==pPk
? ONEPASS_SINGLE
: ONEPASS_OFF
), iThisCur
);
1812 sqlite3VdbeResolveLabel(v
, addrUniqueOk
);
1813 sqlite3ExprCachePop(pParse
);
1814 if( regR
!=regIdx
) sqlite3ReleaseTempRange(pParse
, regR
, nPkField
);
1815 if( pUpIdx
==pIdx
) sqlite3VdbeJumpHere(v
, upsertBypass
);
1818 reorderConstraintChecks(v
, &sAddr
);
1820 *pbMayReplace
= seenReplace
;
1821 VdbeModuleComment((v
, "END: GenCnstCks(%d)", seenReplace
));
1824 #ifdef SQLITE_ENABLE_NULL_TRIM
1826 ** Change the P5 operand on the last opcode (which should be an OP_MakeRecord)
1827 ** to be the number of columns in table pTab that must not be NULL-trimmed.
1829 ** Or if no columns of pTab may be NULL-trimmed, leave P5 at zero.
1831 void sqlite3SetMakeRecordP5(Vdbe
*v
, Table
*pTab
){
1834 /* Records with omitted columns are only allowed for schema format
1835 ** version 2 and later (SQLite version 3.1.4, 2005-02-20). */
1836 if( pTab
->pSchema
->file_format
<2 ) return;
1838 for(i
=pTab
->nCol
-1; i
>0; i
--){
1839 if( pTab
->aCol
[i
].pDflt
!=0 ) break;
1840 if( pTab
->aCol
[i
].colFlags
& COLFLAG_PRIMKEY
) break;
1842 sqlite3VdbeChangeP5(v
, i
+1);
1847 ** This routine generates code to finish the INSERT or UPDATE operation
1848 ** that was started by a prior call to sqlite3GenerateConstraintChecks.
1849 ** A consecutive range of registers starting at regNewData contains the
1850 ** rowid and the content to be inserted.
1852 ** The arguments to this routine should be the same as the first six
1853 ** arguments to sqlite3GenerateConstraintChecks.
1855 void sqlite3CompleteInsertion(
1856 Parse
*pParse
, /* The parser context */
1857 Table
*pTab
, /* the table into which we are inserting */
1858 int iDataCur
, /* Cursor of the canonical data source */
1859 int iIdxCur
, /* First index cursor */
1860 int regNewData
, /* Range of content */
1861 int *aRegIdx
, /* Register used by each index. 0 for unused indices */
1862 int update_flags
, /* True for UPDATE, False for INSERT */
1863 int appendBias
, /* True if this is likely to be an append */
1864 int useSeekResult
/* True to set the USESEEKRESULT flag on OP_[Idx]Insert */
1866 Vdbe
*v
; /* Prepared statements under construction */
1867 Index
*pIdx
; /* An index being inserted or updated */
1868 u8 pik_flags
; /* flag values passed to the btree insert */
1869 int regData
; /* Content registers (after the rowid) */
1870 int regRec
; /* Register holding assembled record for the table */
1871 int i
; /* Loop counter */
1872 u8 bAffinityDone
= 0; /* True if OP_Affinity has been run already */
1874 assert( update_flags
==0
1875 || update_flags
==OPFLAG_ISUPDATE
1876 || update_flags
==(OPFLAG_ISUPDATE
|OPFLAG_SAVEPOSITION
)
1879 v
= sqlite3GetVdbe(pParse
);
1881 assert( pTab
->pSelect
==0 ); /* This table is not a VIEW */
1882 for(i
=0, pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
, i
++){
1883 if( aRegIdx
[i
]==0 ) continue;
1885 if( pIdx
->pPartIdxWhere
){
1886 sqlite3VdbeAddOp2(v
, OP_IsNull
, aRegIdx
[i
], sqlite3VdbeCurrentAddr(v
)+2);
1889 pik_flags
= (useSeekResult
? OPFLAG_USESEEKRESULT
: 0);
1890 if( IsPrimaryKeyIndex(pIdx
) && !HasRowid(pTab
) ){
1891 assert( pParse
->nested
==0 );
1892 pik_flags
|= OPFLAG_NCHANGE
;
1893 pik_flags
|= (update_flags
& OPFLAG_SAVEPOSITION
);
1894 #ifdef SQLITE_ENABLE_PREUPDATE_HOOK
1895 if( update_flags
==0 ){
1896 sqlite3VdbeAddOp4(v
, OP_InsertInt
,
1897 iIdxCur
+i
, aRegIdx
[i
], 0, (char*)pTab
, P4_TABLE
1899 sqlite3VdbeChangeP5(v
, OPFLAG_ISNOOP
);
1903 sqlite3VdbeAddOp4Int(v
, OP_IdxInsert
, iIdxCur
+i
, aRegIdx
[i
],
1905 pIdx
->uniqNotNull
? pIdx
->nKeyCol
: pIdx
->nColumn
);
1906 sqlite3VdbeChangeP5(v
, pik_flags
);
1908 if( !HasRowid(pTab
) ) return;
1909 regData
= regNewData
+ 1;
1910 regRec
= sqlite3GetTempReg(pParse
);
1911 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regData
, pTab
->nCol
, regRec
);
1912 sqlite3SetMakeRecordP5(v
, pTab
);
1913 if( !bAffinityDone
){
1914 sqlite3TableAffinity(v
, pTab
, 0);
1915 sqlite3ExprCacheAffinityChange(pParse
, regData
, pTab
->nCol
);
1917 if( pParse
->nested
){
1920 pik_flags
= OPFLAG_NCHANGE
;
1921 pik_flags
|= (update_flags
?update_flags
:OPFLAG_LASTROWID
);
1924 pik_flags
|= OPFLAG_APPEND
;
1926 if( useSeekResult
){
1927 pik_flags
|= OPFLAG_USESEEKRESULT
;
1929 sqlite3VdbeAddOp3(v
, OP_Insert
, iDataCur
, regRec
, regNewData
);
1930 if( !pParse
->nested
){
1931 sqlite3VdbeAppendP4(v
, pTab
, P4_TABLE
);
1933 sqlite3VdbeChangeP5(v
, pik_flags
);
1937 ** Allocate cursors for the pTab table and all its indices and generate
1938 ** code to open and initialized those cursors.
1940 ** The cursor for the object that contains the complete data (normally
1941 ** the table itself, but the PRIMARY KEY index in the case of a WITHOUT
1942 ** ROWID table) is returned in *piDataCur. The first index cursor is
1943 ** returned in *piIdxCur. The number of indices is returned.
1945 ** Use iBase as the first cursor (either the *piDataCur for rowid tables
1946 ** or the first index for WITHOUT ROWID tables) if it is non-negative.
1947 ** If iBase is negative, then allocate the next available cursor.
1949 ** For a rowid table, *piDataCur will be exactly one less than *piIdxCur.
1950 ** For a WITHOUT ROWID table, *piDataCur will be somewhere in the range
1951 ** of *piIdxCurs, depending on where the PRIMARY KEY index appears on the
1952 ** pTab->pIndex list.
1954 ** If pTab is a virtual table, then this routine is a no-op and the
1955 ** *piDataCur and *piIdxCur values are left uninitialized.
1957 int sqlite3OpenTableAndIndices(
1958 Parse
*pParse
, /* Parsing context */
1959 Table
*pTab
, /* Table to be opened */
1960 int op
, /* OP_OpenRead or OP_OpenWrite */
1961 u8 p5
, /* P5 value for OP_Open* opcodes (except on WITHOUT ROWID) */
1962 int iBase
, /* Use this for the table cursor, if there is one */
1963 u8
*aToOpen
, /* If not NULL: boolean for each table and index */
1964 int *piDataCur
, /* Write the database source cursor number here */
1965 int *piIdxCur
/* Write the first index cursor number here */
1973 assert( op
==OP_OpenRead
|| op
==OP_OpenWrite
);
1974 assert( op
==OP_OpenWrite
|| p5
==0 );
1975 if( IsVirtual(pTab
) ){
1976 /* This routine is a no-op for virtual tables. Leave the output
1977 ** variables *piDataCur and *piIdxCur uninitialized so that valgrind
1978 ** can detect if they are used by mistake in the caller. */
1981 iDb
= sqlite3SchemaToIndex(pParse
->db
, pTab
->pSchema
);
1982 v
= sqlite3GetVdbe(pParse
);
1984 if( iBase
<0 ) iBase
= pParse
->nTab
;
1986 if( piDataCur
) *piDataCur
= iDataCur
;
1987 if( HasRowid(pTab
) && (aToOpen
==0 || aToOpen
[0]) ){
1988 sqlite3OpenTable(pParse
, iDataCur
, iDb
, pTab
, op
);
1990 sqlite3TableLock(pParse
, iDb
, pTab
->tnum
, op
==OP_OpenWrite
, pTab
->zName
);
1992 if( piIdxCur
) *piIdxCur
= iBase
;
1993 for(i
=0, pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
, i
++){
1994 int iIdxCur
= iBase
++;
1995 assert( pIdx
->pSchema
==pTab
->pSchema
);
1996 if( IsPrimaryKeyIndex(pIdx
) && !HasRowid(pTab
) ){
1997 if( piDataCur
) *piDataCur
= iIdxCur
;
2000 if( aToOpen
==0 || aToOpen
[i
+1] ){
2001 sqlite3VdbeAddOp3(v
, op
, iIdxCur
, pIdx
->tnum
, iDb
);
2002 sqlite3VdbeSetP4KeyInfo(pParse
, pIdx
);
2003 sqlite3VdbeChangeP5(v
, p5
);
2004 VdbeComment((v
, "%s", pIdx
->zName
));
2007 if( iBase
>pParse
->nTab
) pParse
->nTab
= iBase
;
2014 ** The following global variable is incremented whenever the
2015 ** transfer optimization is used. This is used for testing
2016 ** purposes only - to make sure the transfer optimization really
2017 ** is happening when it is supposed to.
2019 int sqlite3_xferopt_count
;
2020 #endif /* SQLITE_TEST */
2023 #ifndef SQLITE_OMIT_XFER_OPT
2025 ** Check to see if index pSrc is compatible as a source of data
2026 ** for index pDest in an insert transfer optimization. The rules
2027 ** for a compatible index:
2029 ** * The index is over the same set of columns
2030 ** * The same DESC and ASC markings occurs on all columns
2031 ** * The same onError processing (OE_Abort, OE_Ignore, etc)
2032 ** * The same collating sequence on each column
2033 ** * The index has the exact same WHERE clause
2035 static int xferCompatibleIndex(Index
*pDest
, Index
*pSrc
){
2037 assert( pDest
&& pSrc
);
2038 assert( pDest
->pTable
!=pSrc
->pTable
);
2039 if( pDest
->nKeyCol
!=pSrc
->nKeyCol
){
2040 return 0; /* Different number of columns */
2042 if( pDest
->onError
!=pSrc
->onError
){
2043 return 0; /* Different conflict resolution strategies */
2045 for(i
=0; i
<pSrc
->nKeyCol
; i
++){
2046 if( pSrc
->aiColumn
[i
]!=pDest
->aiColumn
[i
] ){
2047 return 0; /* Different columns indexed */
2049 if( pSrc
->aiColumn
[i
]==XN_EXPR
){
2050 assert( pSrc
->aColExpr
!=0 && pDest
->aColExpr
!=0 );
2051 if( sqlite3ExprCompare(0, pSrc
->aColExpr
->a
[i
].pExpr
,
2052 pDest
->aColExpr
->a
[i
].pExpr
, -1)!=0 ){
2053 return 0; /* Different expressions in the index */
2056 if( pSrc
->aSortOrder
[i
]!=pDest
->aSortOrder
[i
] ){
2057 return 0; /* Different sort orders */
2059 if( sqlite3_stricmp(pSrc
->azColl
[i
],pDest
->azColl
[i
])!=0 ){
2060 return 0; /* Different collating sequences */
2063 if( sqlite3ExprCompare(0, pSrc
->pPartIdxWhere
, pDest
->pPartIdxWhere
, -1) ){
2064 return 0; /* Different WHERE clauses */
2067 /* If no test above fails then the indices must be compatible */
2072 ** Attempt the transfer optimization on INSERTs of the form
2074 ** INSERT INTO tab1 SELECT * FROM tab2;
2076 ** The xfer optimization transfers raw records from tab2 over to tab1.
2077 ** Columns are not decoded and reassembled, which greatly improves
2078 ** performance. Raw index records are transferred in the same way.
2080 ** The xfer optimization is only attempted if tab1 and tab2 are compatible.
2081 ** There are lots of rules for determining compatibility - see comments
2082 ** embedded in the code for details.
2084 ** This routine returns TRUE if the optimization is guaranteed to be used.
2085 ** Sometimes the xfer optimization will only work if the destination table
2086 ** is empty - a factor that can only be determined at run-time. In that
2087 ** case, this routine generates code for the xfer optimization but also
2088 ** does a test to see if the destination table is empty and jumps over the
2089 ** xfer optimization code if the test fails. In that case, this routine
2090 ** returns FALSE so that the caller will know to go ahead and generate
2091 ** an unoptimized transfer. This routine also returns FALSE if there
2092 ** is no chance that the xfer optimization can be applied.
2094 ** This optimization is particularly useful at making VACUUM run faster.
2096 static int xferOptimization(
2097 Parse
*pParse
, /* Parser context */
2098 Table
*pDest
, /* The table we are inserting into */
2099 Select
*pSelect
, /* A SELECT statement to use as the data source */
2100 int onError
, /* How to handle constraint errors */
2101 int iDbDest
/* The database of pDest */
2103 sqlite3
*db
= pParse
->db
;
2104 ExprList
*pEList
; /* The result set of the SELECT */
2105 Table
*pSrc
; /* The table in the FROM clause of SELECT */
2106 Index
*pSrcIdx
, *pDestIdx
; /* Source and destination indices */
2107 struct SrcList_item
*pItem
; /* An element of pSelect->pSrc */
2108 int i
; /* Loop counter */
2109 int iDbSrc
; /* The database of pSrc */
2110 int iSrc
, iDest
; /* Cursors from source and destination */
2111 int addr1
, addr2
; /* Loop addresses */
2112 int emptyDestTest
= 0; /* Address of test for empty pDest */
2113 int emptySrcTest
= 0; /* Address of test for empty pSrc */
2114 Vdbe
*v
; /* The VDBE we are building */
2115 int regAutoinc
; /* Memory register used by AUTOINC */
2116 int destHasUniqueIdx
= 0; /* True if pDest has a UNIQUE index */
2117 int regData
, regRowid
; /* Registers holding data and rowid */
2120 return 0; /* Must be of the form INSERT INTO ... SELECT ... */
2122 if( pParse
->pWith
|| pSelect
->pWith
){
2123 /* Do not attempt to process this query if there are an WITH clauses
2124 ** attached to it. Proceeding may generate a false "no such table: xxx"
2125 ** error if pSelect reads from a CTE named "xxx". */
2128 if( sqlite3TriggerList(pParse
, pDest
) ){
2129 return 0; /* tab1 must not have triggers */
2131 #ifndef SQLITE_OMIT_VIRTUALTABLE
2132 if( IsVirtual(pDest
) ){
2133 return 0; /* tab1 must not be a virtual table */
2136 if( onError
==OE_Default
){
2137 if( pDest
->iPKey
>=0 ) onError
= pDest
->keyConf
;
2138 if( onError
==OE_Default
) onError
= OE_Abort
;
2140 assert(pSelect
->pSrc
); /* allocated even if there is no FROM clause */
2141 if( pSelect
->pSrc
->nSrc
!=1 ){
2142 return 0; /* FROM clause must have exactly one term */
2144 if( pSelect
->pSrc
->a
[0].pSelect
){
2145 return 0; /* FROM clause cannot contain a subquery */
2147 if( pSelect
->pWhere
){
2148 return 0; /* SELECT may not have a WHERE clause */
2150 if( pSelect
->pOrderBy
){
2151 return 0; /* SELECT may not have an ORDER BY clause */
2153 /* Do not need to test for a HAVING clause. If HAVING is present but
2154 ** there is no ORDER BY, we will get an error. */
2155 if( pSelect
->pGroupBy
){
2156 return 0; /* SELECT may not have a GROUP BY clause */
2158 if( pSelect
->pLimit
){
2159 return 0; /* SELECT may not have a LIMIT clause */
2161 if( pSelect
->pPrior
){
2162 return 0; /* SELECT may not be a compound query */
2164 if( pSelect
->selFlags
& SF_Distinct
){
2165 return 0; /* SELECT may not be DISTINCT */
2167 pEList
= pSelect
->pEList
;
2168 assert( pEList
!=0 );
2169 if( pEList
->nExpr
!=1 ){
2170 return 0; /* The result set must have exactly one column */
2172 assert( pEList
->a
[0].pExpr
);
2173 if( pEList
->a
[0].pExpr
->op
!=TK_ASTERISK
){
2174 return 0; /* The result set must be the special operator "*" */
2177 /* At this point we have established that the statement is of the
2178 ** correct syntactic form to participate in this optimization. Now
2179 ** we have to check the semantics.
2181 pItem
= pSelect
->pSrc
->a
;
2182 pSrc
= sqlite3LocateTableItem(pParse
, 0, pItem
);
2184 return 0; /* FROM clause does not contain a real table */
2187 return 0; /* tab1 and tab2 may not be the same table */
2189 if( HasRowid(pDest
)!=HasRowid(pSrc
) ){
2190 return 0; /* source and destination must both be WITHOUT ROWID or not */
2192 #ifndef SQLITE_OMIT_VIRTUALTABLE
2193 if( IsVirtual(pSrc
) ){
2194 return 0; /* tab2 must not be a virtual table */
2197 if( pSrc
->pSelect
){
2198 return 0; /* tab2 may not be a view */
2200 if( pDest
->nCol
!=pSrc
->nCol
){
2201 return 0; /* Number of columns must be the same in tab1 and tab2 */
2203 if( pDest
->iPKey
!=pSrc
->iPKey
){
2204 return 0; /* Both tables must have the same INTEGER PRIMARY KEY */
2206 for(i
=0; i
<pDest
->nCol
; i
++){
2207 Column
*pDestCol
= &pDest
->aCol
[i
];
2208 Column
*pSrcCol
= &pSrc
->aCol
[i
];
2209 #ifdef SQLITE_ENABLE_HIDDEN_COLUMNS
2210 if( (db
->mDbFlags
& DBFLAG_Vacuum
)==0
2211 && (pDestCol
->colFlags
| pSrcCol
->colFlags
) & COLFLAG_HIDDEN
2213 return 0; /* Neither table may have __hidden__ columns */
2216 if( pDestCol
->affinity
!=pSrcCol
->affinity
){
2217 return 0; /* Affinity must be the same on all columns */
2219 if( sqlite3_stricmp(pDestCol
->zColl
, pSrcCol
->zColl
)!=0 ){
2220 return 0; /* Collating sequence must be the same on all columns */
2222 if( pDestCol
->notNull
&& !pSrcCol
->notNull
){
2223 return 0; /* tab2 must be NOT NULL if tab1 is */
2225 /* Default values for second and subsequent columns need to match. */
2227 assert( pDestCol
->pDflt
==0 || pDestCol
->pDflt
->op
==TK_SPAN
);
2228 assert( pSrcCol
->pDflt
==0 || pSrcCol
->pDflt
->op
==TK_SPAN
);
2229 if( (pDestCol
->pDflt
==0)!=(pSrcCol
->pDflt
==0)
2230 || (pDestCol
->pDflt
&& strcmp(pDestCol
->pDflt
->u
.zToken
,
2231 pSrcCol
->pDflt
->u
.zToken
)!=0)
2233 return 0; /* Default values must be the same for all columns */
2237 for(pDestIdx
=pDest
->pIndex
; pDestIdx
; pDestIdx
=pDestIdx
->pNext
){
2238 if( IsUniqueIndex(pDestIdx
) ){
2239 destHasUniqueIdx
= 1;
2241 for(pSrcIdx
=pSrc
->pIndex
; pSrcIdx
; pSrcIdx
=pSrcIdx
->pNext
){
2242 if( xferCompatibleIndex(pDestIdx
, pSrcIdx
) ) break;
2245 return 0; /* pDestIdx has no corresponding index in pSrc */
2248 #ifndef SQLITE_OMIT_CHECK
2249 if( pDest
->pCheck
&& sqlite3ExprListCompare(pSrc
->pCheck
,pDest
->pCheck
,-1) ){
2250 return 0; /* Tables have different CHECK constraints. Ticket #2252 */
2253 #ifndef SQLITE_OMIT_FOREIGN_KEY
2254 /* Disallow the transfer optimization if the destination table constains
2255 ** any foreign key constraints. This is more restrictive than necessary.
2256 ** But the main beneficiary of the transfer optimization is the VACUUM
2257 ** command, and the VACUUM command disables foreign key constraints. So
2258 ** the extra complication to make this rule less restrictive is probably
2259 ** not worth the effort. Ticket [6284df89debdfa61db8073e062908af0c9b6118e]
2261 if( (db
->flags
& SQLITE_ForeignKeys
)!=0 && pDest
->pFKey
!=0 ){
2265 if( (db
->flags
& SQLITE_CountRows
)!=0 ){
2266 return 0; /* xfer opt does not play well with PRAGMA count_changes */
2269 /* If we get this far, it means that the xfer optimization is at
2270 ** least a possibility, though it might only work if the destination
2271 ** table (tab1) is initially empty.
2274 sqlite3_xferopt_count
++;
2276 iDbSrc
= sqlite3SchemaToIndex(db
, pSrc
->pSchema
);
2277 v
= sqlite3GetVdbe(pParse
);
2278 sqlite3CodeVerifySchema(pParse
, iDbSrc
);
2279 iSrc
= pParse
->nTab
++;
2280 iDest
= pParse
->nTab
++;
2281 regAutoinc
= autoIncBegin(pParse
, iDbDest
, pDest
);
2282 regData
= sqlite3GetTempReg(pParse
);
2283 regRowid
= sqlite3GetTempReg(pParse
);
2284 sqlite3OpenTable(pParse
, iDest
, iDbDest
, pDest
, OP_OpenWrite
);
2285 assert( HasRowid(pDest
) || destHasUniqueIdx
);
2286 if( (db
->mDbFlags
& DBFLAG_Vacuum
)==0 && (
2287 (pDest
->iPKey
<0 && pDest
->pIndex
!=0) /* (1) */
2288 || destHasUniqueIdx
/* (2) */
2289 || (onError
!=OE_Abort
&& onError
!=OE_Rollback
) /* (3) */
2291 /* In some circumstances, we are able to run the xfer optimization
2292 ** only if the destination table is initially empty. Unless the
2293 ** DBFLAG_Vacuum flag is set, this block generates code to make
2294 ** that determination. If DBFLAG_Vacuum is set, then the destination
2295 ** table is always empty.
2297 ** Conditions under which the destination must be empty:
2299 ** (1) There is no INTEGER PRIMARY KEY but there are indices.
2300 ** (If the destination is not initially empty, the rowid fields
2301 ** of index entries might need to change.)
2303 ** (2) The destination has a unique index. (The xfer optimization
2304 ** is unable to test uniqueness.)
2306 ** (3) onError is something other than OE_Abort and OE_Rollback.
2308 addr1
= sqlite3VdbeAddOp2(v
, OP_Rewind
, iDest
, 0); VdbeCoverage(v
);
2309 emptyDestTest
= sqlite3VdbeAddOp0(v
, OP_Goto
);
2310 sqlite3VdbeJumpHere(v
, addr1
);
2312 if( HasRowid(pSrc
) ){
2314 sqlite3OpenTable(pParse
, iSrc
, iDbSrc
, pSrc
, OP_OpenRead
);
2315 emptySrcTest
= sqlite3VdbeAddOp2(v
, OP_Rewind
, iSrc
, 0); VdbeCoverage(v
);
2316 if( pDest
->iPKey
>=0 ){
2317 addr1
= sqlite3VdbeAddOp2(v
, OP_Rowid
, iSrc
, regRowid
);
2318 addr2
= sqlite3VdbeAddOp3(v
, OP_NotExists
, iDest
, 0, regRowid
);
2320 sqlite3RowidConstraint(pParse
, onError
, pDest
);
2321 sqlite3VdbeJumpHere(v
, addr2
);
2322 autoIncStep(pParse
, regAutoinc
, regRowid
);
2323 }else if( pDest
->pIndex
==0 ){
2324 addr1
= sqlite3VdbeAddOp2(v
, OP_NewRowid
, iDest
, regRowid
);
2326 addr1
= sqlite3VdbeAddOp2(v
, OP_Rowid
, iSrc
, regRowid
);
2327 assert( (pDest
->tabFlags
& TF_Autoincrement
)==0 );
2329 sqlite3VdbeAddOp3(v
, OP_RowData
, iSrc
, regData
, 1);
2330 if( db
->mDbFlags
& DBFLAG_Vacuum
){
2331 sqlite3VdbeAddOp1(v
, OP_SeekEnd
, iDest
);
2332 insFlags
= OPFLAG_NCHANGE
|OPFLAG_LASTROWID
|
2333 OPFLAG_APPEND
|OPFLAG_USESEEKRESULT
;
2335 insFlags
= OPFLAG_NCHANGE
|OPFLAG_LASTROWID
|OPFLAG_APPEND
;
2337 sqlite3VdbeAddOp4(v
, OP_Insert
, iDest
, regData
, regRowid
,
2338 (char*)pDest
, P4_TABLE
);
2339 sqlite3VdbeChangeP5(v
, insFlags
);
2340 sqlite3VdbeAddOp2(v
, OP_Next
, iSrc
, addr1
); VdbeCoverage(v
);
2341 sqlite3VdbeAddOp2(v
, OP_Close
, iSrc
, 0);
2342 sqlite3VdbeAddOp2(v
, OP_Close
, iDest
, 0);
2344 sqlite3TableLock(pParse
, iDbDest
, pDest
->tnum
, 1, pDest
->zName
);
2345 sqlite3TableLock(pParse
, iDbSrc
, pSrc
->tnum
, 0, pSrc
->zName
);
2347 for(pDestIdx
=pDest
->pIndex
; pDestIdx
; pDestIdx
=pDestIdx
->pNext
){
2349 for(pSrcIdx
=pSrc
->pIndex
; ALWAYS(pSrcIdx
); pSrcIdx
=pSrcIdx
->pNext
){
2350 if( xferCompatibleIndex(pDestIdx
, pSrcIdx
) ) break;
2353 sqlite3VdbeAddOp3(v
, OP_OpenRead
, iSrc
, pSrcIdx
->tnum
, iDbSrc
);
2354 sqlite3VdbeSetP4KeyInfo(pParse
, pSrcIdx
);
2355 VdbeComment((v
, "%s", pSrcIdx
->zName
));
2356 sqlite3VdbeAddOp3(v
, OP_OpenWrite
, iDest
, pDestIdx
->tnum
, iDbDest
);
2357 sqlite3VdbeSetP4KeyInfo(pParse
, pDestIdx
);
2358 sqlite3VdbeChangeP5(v
, OPFLAG_BULKCSR
);
2359 VdbeComment((v
, "%s", pDestIdx
->zName
));
2360 addr1
= sqlite3VdbeAddOp2(v
, OP_Rewind
, iSrc
, 0); VdbeCoverage(v
);
2361 sqlite3VdbeAddOp3(v
, OP_RowData
, iSrc
, regData
, 1);
2362 if( db
->mDbFlags
& DBFLAG_Vacuum
){
2363 /* This INSERT command is part of a VACUUM operation, which guarantees
2364 ** that the destination table is empty. If all indexed columns use
2365 ** collation sequence BINARY, then it can also be assumed that the
2366 ** index will be populated by inserting keys in strictly sorted
2367 ** order. In this case, instead of seeking within the b-tree as part
2368 ** of every OP_IdxInsert opcode, an OP_SeekEnd is added before the
2369 ** OP_IdxInsert to seek to the point within the b-tree where each key
2370 ** should be inserted. This is faster.
2372 ** If any of the indexed columns use a collation sequence other than
2373 ** BINARY, this optimization is disabled. This is because the user
2374 ** might change the definition of a collation sequence and then run
2375 ** a VACUUM command. In that case keys may not be written in strictly
2377 for(i
=0; i
<pSrcIdx
->nColumn
; i
++){
2378 const char *zColl
= pSrcIdx
->azColl
[i
];
2379 if( sqlite3_stricmp(sqlite3StrBINARY
, zColl
) ) break;
2381 if( i
==pSrcIdx
->nColumn
){
2382 idxInsFlags
= OPFLAG_USESEEKRESULT
;
2383 sqlite3VdbeAddOp1(v
, OP_SeekEnd
, iDest
);
2386 if( !HasRowid(pSrc
) && pDestIdx
->idxType
==2 ){
2387 idxInsFlags
|= OPFLAG_NCHANGE
;
2389 sqlite3VdbeAddOp2(v
, OP_IdxInsert
, iDest
, regData
);
2390 sqlite3VdbeChangeP5(v
, idxInsFlags
|OPFLAG_APPEND
);
2391 sqlite3VdbeAddOp2(v
, OP_Next
, iSrc
, addr1
+1); VdbeCoverage(v
);
2392 sqlite3VdbeJumpHere(v
, addr1
);
2393 sqlite3VdbeAddOp2(v
, OP_Close
, iSrc
, 0);
2394 sqlite3VdbeAddOp2(v
, OP_Close
, iDest
, 0);
2396 if( emptySrcTest
) sqlite3VdbeJumpHere(v
, emptySrcTest
);
2397 sqlite3ReleaseTempReg(pParse
, regRowid
);
2398 sqlite3ReleaseTempReg(pParse
, regData
);
2399 if( emptyDestTest
){
2400 sqlite3AutoincrementEnd(pParse
);
2401 sqlite3VdbeAddOp2(v
, OP_Halt
, SQLITE_OK
, 0);
2402 sqlite3VdbeJumpHere(v
, emptyDestTest
);
2403 sqlite3VdbeAddOp2(v
, OP_Close
, iDest
, 0);
2409 #endif /* SQLITE_OMIT_XFER_OPT */