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 ** Three memory locations are allocated:
215 ** (1) Register to hold the name of the pTab table.
216 ** (2) Register to hold the maximum ROWID of pTab.
217 ** (3) Register to hold the rowid in sqlite_sequence of pTab
219 ** The 2nd register is the one that is returned. That is all the
220 ** insert routine needs to know about.
222 static int autoIncBegin(
223 Parse
*pParse
, /* Parsing context */
224 int iDb
, /* Index of the database holding pTab */
225 Table
*pTab
/* The table we are writing to */
227 int memId
= 0; /* Register holding maximum rowid */
228 if( (pTab
->tabFlags
& TF_Autoincrement
)!=0
229 && (pParse
->db
->mDbFlags
& DBFLAG_Vacuum
)==0
231 Parse
*pToplevel
= sqlite3ParseToplevel(pParse
);
234 pInfo
= pToplevel
->pAinc
;
235 while( pInfo
&& pInfo
->pTab
!=pTab
){ pInfo
= pInfo
->pNext
; }
237 pInfo
= sqlite3DbMallocRawNN(pParse
->db
, sizeof(*pInfo
));
238 if( pInfo
==0 ) return 0;
239 pInfo
->pNext
= pToplevel
->pAinc
;
240 pToplevel
->pAinc
= pInfo
;
243 pToplevel
->nMem
++; /* Register to hold name of table */
244 pInfo
->regCtr
= ++pToplevel
->nMem
; /* Max rowid register */
245 pToplevel
->nMem
++; /* Rowid in sqlite_sequence */
247 memId
= pInfo
->regCtr
;
253 ** This routine generates code that will initialize all of the
254 ** register used by the autoincrement tracker.
256 void sqlite3AutoincrementBegin(Parse
*pParse
){
257 AutoincInfo
*p
; /* Information about an AUTOINCREMENT */
258 sqlite3
*db
= pParse
->db
; /* The database connection */
259 Db
*pDb
; /* Database only autoinc table */
260 int memId
; /* Register holding max rowid */
261 Vdbe
*v
= pParse
->pVdbe
; /* VDBE under construction */
263 /* This routine is never called during trigger-generation. It is
264 ** only called from the top-level */
265 assert( pParse
->pTriggerTab
==0 );
266 assert( sqlite3IsToplevel(pParse
) );
268 assert( v
); /* We failed long ago if this is not so */
269 for(p
= pParse
->pAinc
; p
; p
= p
->pNext
){
270 static const int iLn
= VDBE_OFFSET_LINENO(2);
271 static const VdbeOpList autoInc
[] = {
272 /* 0 */ {OP_Null
, 0, 0, 0},
273 /* 1 */ {OP_Rewind
, 0, 9, 0},
274 /* 2 */ {OP_Column
, 0, 0, 0},
275 /* 3 */ {OP_Ne
, 0, 7, 0},
276 /* 4 */ {OP_Rowid
, 0, 0, 0},
277 /* 5 */ {OP_Column
, 0, 1, 0},
278 /* 6 */ {OP_Goto
, 0, 9, 0},
279 /* 7 */ {OP_Next
, 0, 2, 0},
280 /* 8 */ {OP_Integer
, 0, 0, 0},
281 /* 9 */ {OP_Close
, 0, 0, 0}
284 pDb
= &db
->aDb
[p
->iDb
];
286 assert( sqlite3SchemaMutexHeld(db
, 0, pDb
->pSchema
) );
287 sqlite3OpenTable(pParse
, 0, p
->iDb
, pDb
->pSchema
->pSeqTab
, OP_OpenRead
);
288 sqlite3VdbeLoadString(v
, memId
-1, p
->pTab
->zName
);
289 aOp
= sqlite3VdbeAddOpList(v
, ArraySize(autoInc
), autoInc
, iLn
);
296 aOp
[3].p5
= SQLITE_JUMPIFNULL
;
304 ** Update the maximum rowid for an autoincrement calculation.
306 ** This routine should be called when the regRowid register holds a
307 ** new rowid that is about to be inserted. If that new rowid is
308 ** larger than the maximum rowid in the memId memory cell, then the
309 ** memory cell is updated.
311 static void autoIncStep(Parse
*pParse
, int memId
, int regRowid
){
313 sqlite3VdbeAddOp2(pParse
->pVdbe
, OP_MemMax
, memId
, regRowid
);
318 ** This routine generates the code needed to write autoincrement
319 ** maximum rowid values back into the sqlite_sequence register.
320 ** Every statement that might do an INSERT into an autoincrement
321 ** table (either directly or through triggers) needs to call this
322 ** routine just before the "exit" code.
324 static SQLITE_NOINLINE
void autoIncrementEnd(Parse
*pParse
){
326 Vdbe
*v
= pParse
->pVdbe
;
327 sqlite3
*db
= pParse
->db
;
330 for(p
= pParse
->pAinc
; p
; p
= p
->pNext
){
331 static const int iLn
= VDBE_OFFSET_LINENO(2);
332 static const VdbeOpList autoIncEnd
[] = {
333 /* 0 */ {OP_NotNull
, 0, 2, 0},
334 /* 1 */ {OP_NewRowid
, 0, 0, 0},
335 /* 2 */ {OP_MakeRecord
, 0, 2, 0},
336 /* 3 */ {OP_Insert
, 0, 0, 0},
337 /* 4 */ {OP_Close
, 0, 0, 0}
340 Db
*pDb
= &db
->aDb
[p
->iDb
];
342 int memId
= p
->regCtr
;
344 iRec
= sqlite3GetTempReg(pParse
);
345 assert( sqlite3SchemaMutexHeld(db
, 0, pDb
->pSchema
) );
346 sqlite3OpenTable(pParse
, 0, p
->iDb
, pDb
->pSchema
->pSeqTab
, OP_OpenWrite
);
347 aOp
= sqlite3VdbeAddOpList(v
, ArraySize(autoIncEnd
), autoIncEnd
, iLn
);
355 aOp
[3].p5
= OPFLAG_APPEND
;
356 sqlite3ReleaseTempReg(pParse
, iRec
);
359 void sqlite3AutoincrementEnd(Parse
*pParse
){
360 if( pParse
->pAinc
) autoIncrementEnd(pParse
);
364 ** If SQLITE_OMIT_AUTOINCREMENT is defined, then the three routines
365 ** above are all no-ops
367 # define autoIncBegin(A,B,C) (0)
368 # define autoIncStep(A,B,C)
369 #endif /* SQLITE_OMIT_AUTOINCREMENT */
372 /* Forward declaration */
373 static int xferOptimization(
374 Parse
*pParse
, /* Parser context */
375 Table
*pDest
, /* The table we are inserting into */
376 Select
*pSelect
, /* A SELECT statement to use as the data source */
377 int onError
, /* How to handle constraint errors */
378 int iDbDest
/* The database of pDest */
382 ** This routine is called to handle SQL of the following forms:
384 ** insert into TABLE (IDLIST) values(EXPRLIST),(EXPRLIST),...
385 ** insert into TABLE (IDLIST) select
386 ** insert into TABLE (IDLIST) default values
388 ** The IDLIST following the table name is always optional. If omitted,
389 ** then a list of all (non-hidden) columns for the table is substituted.
390 ** The IDLIST appears in the pColumn parameter. pColumn is NULL if IDLIST
393 ** For the pSelect parameter holds the values to be inserted for the
394 ** first two forms shown above. A VALUES clause is really just short-hand
395 ** for a SELECT statement that omits the FROM clause and everything else
396 ** that follows. If the pSelect parameter is NULL, that means that the
397 ** DEFAULT VALUES form of the INSERT statement is intended.
399 ** The code generated follows one of four templates. For a simple
400 ** insert with data coming from a single-row VALUES clause, the code executes
401 ** once straight down through. Pseudo-code follows (we call this
402 ** the "1st template"):
404 ** open write cursor to <table> and its indices
405 ** put VALUES clause expressions into registers
406 ** write the resulting record into <table>
409 ** The three remaining templates assume the statement is of the form
411 ** INSERT INTO <table> SELECT ...
413 ** If the SELECT clause is of the restricted form "SELECT * FROM <table2>" -
414 ** in other words if the SELECT pulls all columns from a single table
415 ** and there is no WHERE or LIMIT or GROUP BY or ORDER BY clauses, and
416 ** if <table2> and <table1> are distinct tables but have identical
417 ** schemas, including all the same indices, then a special optimization
418 ** is invoked that copies raw records from <table2> over to <table1>.
419 ** See the xferOptimization() function for the implementation of this
420 ** template. This is the 2nd template.
422 ** open a write cursor to <table>
423 ** open read cursor on <table2>
424 ** transfer all records in <table2> over to <table>
426 ** foreach index on <table>
427 ** open a write cursor on the <table> index
428 ** open a read cursor on the corresponding <table2> index
429 ** transfer all records from the read to the write cursors
433 ** The 3rd template is for when the second template does not apply
434 ** and the SELECT clause does not read from <table> at any time.
435 ** The generated code follows this template:
439 ** A: setup for the SELECT
440 ** loop over the rows in the SELECT
441 ** load values into registers R..R+n
444 ** cleanup after the SELECT
446 ** B: open write cursor to <table> and its indices
447 ** C: yield X, at EOF goto D
448 ** insert the select result into <table> from R..R+n
452 ** The 4th template is used if the insert statement takes its
453 ** values from a SELECT but the data is being inserted into a table
454 ** that is also read as part of the SELECT. In the third form,
455 ** we have to use an intermediate table to store the results of
456 ** the select. The template is like this:
460 ** A: setup for the SELECT
461 ** loop over the tables in the SELECT
462 ** load value into register R..R+n
465 ** cleanup after the SELECT
467 ** B: open temp table
468 ** L: yield X, at EOF goto M
469 ** insert row from R..R+n into temp table
471 ** M: open write cursor to <table> and its indices
473 ** C: loop over rows of intermediate table
474 ** transfer values form intermediate table into <table>
479 Parse
*pParse
, /* Parser context */
480 SrcList
*pTabList
, /* Name of table into which we are inserting */
481 Select
*pSelect
, /* A SELECT statement to use as the data source */
482 IdList
*pColumn
, /* Column names corresponding to IDLIST. */
483 int onError
/* How to handle constraint errors */
485 sqlite3
*db
; /* The main database structure */
486 Table
*pTab
; /* The table to insert into. aka TABLE */
487 int i
, j
; /* Loop counters */
488 Vdbe
*v
; /* Generate code into this virtual machine */
489 Index
*pIdx
; /* For looping over indices of the table */
490 int nColumn
; /* Number of columns in the data */
491 int nHidden
= 0; /* Number of hidden columns if TABLE is virtual */
492 int iDataCur
= 0; /* VDBE cursor that is the main data repository */
493 int iIdxCur
= 0; /* First index cursor */
494 int ipkColumn
= -1; /* Column that is the INTEGER PRIMARY KEY */
495 int endOfLoop
; /* Label for the end of the insertion loop */
496 int srcTab
= 0; /* Data comes from this temporary cursor if >=0 */
497 int addrInsTop
= 0; /* Jump to label "D" */
498 int addrCont
= 0; /* Top of insert loop. Label "C" in templates 3 and 4 */
499 SelectDest dest
; /* Destination for SELECT on rhs of INSERT */
500 int iDb
; /* Index of database holding TABLE */
501 u8 useTempTable
= 0; /* Store SELECT results in intermediate table */
502 u8 appendFlag
= 0; /* True if the insert is likely to be an append */
503 u8 withoutRowid
; /* 0 for normal table. 1 for WITHOUT ROWID table */
504 u8 bIdListInOrder
; /* True if IDLIST is in table order */
505 ExprList
*pList
= 0; /* List of VALUES() to be inserted */
507 /* Register allocations */
508 int regFromSelect
= 0;/* Base register for data coming from SELECT */
509 int regAutoinc
= 0; /* Register holding the AUTOINCREMENT counter */
510 int regRowCount
= 0; /* Memory cell used for the row counter */
511 int regIns
; /* Block of regs holding rowid+data being inserted */
512 int regRowid
; /* registers holding insert rowid */
513 int regData
; /* register holding first column to insert */
514 int *aRegIdx
= 0; /* One register allocated to each index */
516 #ifndef SQLITE_OMIT_TRIGGER
517 int isView
; /* True if attempting to insert into a view */
518 Trigger
*pTrigger
; /* List of triggers on pTab, if required */
519 int tmask
; /* Mask of trigger times */
523 if( pParse
->nErr
|| db
->mallocFailed
){
526 dest
.iSDParm
= 0; /* Suppress a harmless compiler warning */
528 /* If the Select object is really just a simple VALUES() list with a
529 ** single row (the common case) then keep that one row of values
530 ** and discard the other (unused) parts of the pSelect object
532 if( pSelect
&& (pSelect
->selFlags
& SF_Values
)!=0 && pSelect
->pPrior
==0 ){
533 pList
= pSelect
->pEList
;
535 sqlite3SelectDelete(db
, pSelect
);
539 /* Locate the table into which we will be inserting new information.
541 assert( pTabList
->nSrc
==1 );
542 pTab
= sqlite3SrcListLookup(pParse
, pTabList
);
546 iDb
= sqlite3SchemaToIndex(db
, pTab
->pSchema
);
547 assert( iDb
<db
->nDb
);
548 if( sqlite3AuthCheck(pParse
, SQLITE_INSERT
, pTab
->zName
, 0,
549 db
->aDb
[iDb
].zDbSName
) ){
552 withoutRowid
= !HasRowid(pTab
);
554 /* Figure out if we have any triggers and if the table being
555 ** inserted into is a view
557 #ifndef SQLITE_OMIT_TRIGGER
558 pTrigger
= sqlite3TriggersExist(pParse
, pTab
, TK_INSERT
, 0, &tmask
);
559 isView
= pTab
->pSelect
!=0;
565 #ifdef SQLITE_OMIT_VIEW
569 assert( (pTrigger
&& tmask
) || (pTrigger
==0 && tmask
==0) );
571 /* If pTab is really a view, make sure it has been initialized.
572 ** ViewGetColumnNames() is a no-op if pTab is not a view.
574 if( sqlite3ViewGetColumnNames(pParse
, pTab
) ){
578 /* Cannot insert into a read-only table.
580 if( sqlite3IsReadOnly(pParse
, pTab
, tmask
) ){
586 v
= sqlite3GetVdbe(pParse
);
587 if( v
==0 ) goto insert_cleanup
;
588 if( pParse
->nested
==0 ) sqlite3VdbeCountChanges(v
);
589 sqlite3BeginWriteOperation(pParse
, pSelect
|| pTrigger
, iDb
);
591 #ifndef SQLITE_OMIT_XFER_OPT
592 /* If the statement is of the form
594 ** INSERT INTO <table1> SELECT * FROM <table2>;
596 ** Then special optimizations can be applied that make the transfer
597 ** very fast and which reduce fragmentation of indices.
599 ** This is the 2nd template.
601 if( pColumn
==0 && xferOptimization(pParse
, pTab
, pSelect
, onError
, iDb
) ){
606 #endif /* SQLITE_OMIT_XFER_OPT */
608 /* If this is an AUTOINCREMENT table, look up the sequence number in the
609 ** sqlite_sequence table and store it in memory cell regAutoinc.
611 regAutoinc
= autoIncBegin(pParse
, iDb
, pTab
);
613 /* Allocate registers for holding the rowid of the new row,
614 ** the content of the new row, and the assembled row record.
616 regRowid
= regIns
= pParse
->nMem
+1;
617 pParse
->nMem
+= pTab
->nCol
+ 1;
618 if( IsVirtual(pTab
) ){
622 regData
= regRowid
+1;
624 /* If the INSERT statement included an IDLIST term, then make sure
625 ** all elements of the IDLIST really are columns of the table and
626 ** remember the column indices.
628 ** If the table has an INTEGER PRIMARY KEY column and that column
629 ** is named in the IDLIST, then record in the ipkColumn variable
630 ** the index into IDLIST of the primary key column. ipkColumn is
631 ** the index of the primary key as it appears in IDLIST, not as
632 ** is appears in the original table. (The index of the INTEGER
633 ** PRIMARY KEY in the original table is pTab->iPKey.)
635 bIdListInOrder
= (pTab
->tabFlags
& TF_OOOHidden
)==0;
637 for(i
=0; i
<pColumn
->nId
; i
++){
638 pColumn
->a
[i
].idx
= -1;
640 for(i
=0; i
<pColumn
->nId
; i
++){
641 for(j
=0; j
<pTab
->nCol
; j
++){
642 if( sqlite3StrICmp(pColumn
->a
[i
].zName
, pTab
->aCol
[j
].zName
)==0 ){
643 pColumn
->a
[i
].idx
= j
;
644 if( i
!=j
) bIdListInOrder
= 0;
645 if( j
==pTab
->iPKey
){
646 ipkColumn
= i
; assert( !withoutRowid
);
652 if( sqlite3IsRowid(pColumn
->a
[i
].zName
) && !withoutRowid
){
656 sqlite3ErrorMsg(pParse
, "table %S has no column named %s",
657 pTabList
, 0, pColumn
->a
[i
].zName
);
658 pParse
->checkSchema
= 1;
665 /* Figure out how many columns of data are supplied. If the data
666 ** is coming from a SELECT statement, then generate a co-routine that
667 ** produces a single row of the SELECT on each invocation. The
668 ** co-routine is the common header to the 3rd and 4th templates.
671 /* Data is coming from a SELECT or from a multi-row VALUES clause.
672 ** Generate a co-routine to run the SELECT. */
673 int regYield
; /* Register holding co-routine entry-point */
674 int addrTop
; /* Top of the co-routine */
675 int rc
; /* Result code */
677 regYield
= ++pParse
->nMem
;
678 addrTop
= sqlite3VdbeCurrentAddr(v
) + 1;
679 sqlite3VdbeAddOp3(v
, OP_InitCoroutine
, regYield
, 0, addrTop
);
680 sqlite3SelectDestInit(&dest
, SRT_Coroutine
, regYield
);
681 dest
.iSdst
= bIdListInOrder
? regData
: 0;
682 dest
.nSdst
= pTab
->nCol
;
683 rc
= sqlite3Select(pParse
, pSelect
, &dest
);
684 regFromSelect
= dest
.iSdst
;
685 if( rc
|| db
->mallocFailed
|| pParse
->nErr
) goto insert_cleanup
;
686 sqlite3VdbeEndCoroutine(v
, regYield
);
687 sqlite3VdbeJumpHere(v
, addrTop
- 1); /* label B: */
688 assert( pSelect
->pEList
);
689 nColumn
= pSelect
->pEList
->nExpr
;
691 /* Set useTempTable to TRUE if the result of the SELECT statement
692 ** should be written into a temporary table (template 4). Set to
693 ** FALSE if each output row of the SELECT can be written directly into
694 ** the destination table (template 3).
696 ** A temp table must be used if the table being updated is also one
697 ** of the tables being read by the SELECT statement. Also use a
698 ** temp table in the case of row triggers.
700 if( pTrigger
|| readsTable(pParse
, iDb
, pTab
) ){
705 /* Invoke the coroutine to extract information from the SELECT
706 ** and add it to a transient table srcTab. The code generated
707 ** here is from the 4th template:
709 ** B: open temp table
710 ** L: yield X, goto M at EOF
711 ** insert row from R..R+n into temp table
715 int regRec
; /* Register to hold packed record */
716 int regTempRowid
; /* Register to hold temp table ROWID */
717 int addrL
; /* Label "L" */
719 srcTab
= pParse
->nTab
++;
720 regRec
= sqlite3GetTempReg(pParse
);
721 regTempRowid
= sqlite3GetTempReg(pParse
);
722 sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, srcTab
, nColumn
);
723 addrL
= sqlite3VdbeAddOp1(v
, OP_Yield
, dest
.iSDParm
); VdbeCoverage(v
);
724 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regFromSelect
, nColumn
, regRec
);
725 sqlite3VdbeAddOp2(v
, OP_NewRowid
, srcTab
, regTempRowid
);
726 sqlite3VdbeAddOp3(v
, OP_Insert
, srcTab
, regRec
, regTempRowid
);
727 sqlite3VdbeGoto(v
, addrL
);
728 sqlite3VdbeJumpHere(v
, addrL
);
729 sqlite3ReleaseTempReg(pParse
, regRec
);
730 sqlite3ReleaseTempReg(pParse
, regTempRowid
);
733 /* This is the case if the data for the INSERT is coming from a
734 ** single-row VALUES clause
737 memset(&sNC
, 0, sizeof(sNC
));
740 assert( useTempTable
==0 );
742 nColumn
= pList
->nExpr
;
743 if( sqlite3ResolveExprListNames(&sNC
, pList
) ){
751 /* If there is no IDLIST term but the table has an integer primary
752 ** key, the set the ipkColumn variable to the integer primary key
753 ** column index in the original table definition.
755 if( pColumn
==0 && nColumn
>0 ){
756 ipkColumn
= pTab
->iPKey
;
759 /* Make sure the number of columns in the source data matches the number
760 ** of columns to be inserted into the table.
762 for(i
=0; i
<pTab
->nCol
; i
++){
763 nHidden
+= (IsHiddenColumn(&pTab
->aCol
[i
]) ? 1 : 0);
765 if( pColumn
==0 && nColumn
&& nColumn
!=(pTab
->nCol
-nHidden
) ){
766 sqlite3ErrorMsg(pParse
,
767 "table %S has %d columns but %d values were supplied",
768 pTabList
, 0, pTab
->nCol
-nHidden
, nColumn
);
771 if( pColumn
!=0 && nColumn
!=pColumn
->nId
){
772 sqlite3ErrorMsg(pParse
, "%d values for %d columns", nColumn
, pColumn
->nId
);
776 /* Initialize the count of rows to be inserted
778 if( db
->flags
& SQLITE_CountRows
){
779 regRowCount
= ++pParse
->nMem
;
780 sqlite3VdbeAddOp2(v
, OP_Integer
, 0, regRowCount
);
783 /* If this is not a view, open the table and and all indices */
786 nIdx
= sqlite3OpenTableAndIndices(pParse
, pTab
, OP_OpenWrite
, 0, -1, 0,
787 &iDataCur
, &iIdxCur
);
788 aRegIdx
= sqlite3DbMallocRawNN(db
, sizeof(int)*(nIdx
+1));
792 for(i
=0, pIdx
=pTab
->pIndex
; i
<nIdx
; pIdx
=pIdx
->pNext
, i
++){
794 aRegIdx
[i
] = ++pParse
->nMem
;
795 pParse
->nMem
+= pIdx
->nColumn
;
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 int bUseSeek
; /* True to use OPFLAG_SEEKRESULT */
998 sqlite3GenerateConstraintChecks(pParse
, pTab
, aRegIdx
, iDataCur
, iIdxCur
,
999 regIns
, 0, ipkColumn
>=0, onError
, endOfLoop
, &isReplace
, 0
1001 sqlite3FkCheck(pParse
, pTab
, 0, regIns
, 0, 0);
1003 /* Set the OPFLAG_USESEEKRESULT flag if either (a) there are no REPLACE
1004 ** constraints or (b) there are no triggers and this table is not a
1005 ** parent table in a foreign key constraint. It is safe to set the
1006 ** flag in the second case as if any REPLACE constraint is hit, an
1007 ** OP_Delete or OP_IdxDelete instruction will be executed on each
1008 ** cursor that is disturbed. And these instructions both clear the
1009 ** VdbeCursor.seekResult variable, disabling the OPFLAG_USESEEKRESULT
1010 ** functionality. */
1011 bUseSeek
= (isReplace
==0 || (pTrigger
==0 &&
1012 ((db
->flags
& SQLITE_ForeignKeys
)==0 || sqlite3FkReferences(pTab
)==0)
1014 sqlite3CompleteInsertion(pParse
, pTab
, iDataCur
, iIdxCur
,
1015 regIns
, aRegIdx
, 0, appendFlag
, bUseSeek
1020 /* Update the count of rows that are inserted
1022 if( (db
->flags
& SQLITE_CountRows
)!=0 ){
1023 sqlite3VdbeAddOp2(v
, OP_AddImm
, regRowCount
, 1);
1027 /* Code AFTER triggers */
1028 sqlite3CodeRowTrigger(pParse
, pTrigger
, TK_INSERT
, 0, TRIGGER_AFTER
,
1029 pTab
, regData
-2-pTab
->nCol
, onError
, endOfLoop
);
1032 /* The bottom of the main insertion loop, if the data source
1033 ** is a SELECT statement.
1035 sqlite3VdbeResolveLabel(v
, endOfLoop
);
1037 sqlite3VdbeAddOp2(v
, OP_Next
, srcTab
, addrCont
); VdbeCoverage(v
);
1038 sqlite3VdbeJumpHere(v
, addrInsTop
);
1039 sqlite3VdbeAddOp1(v
, OP_Close
, srcTab
);
1040 }else if( pSelect
){
1041 sqlite3VdbeGoto(v
, addrCont
);
1042 sqlite3VdbeJumpHere(v
, addrInsTop
);
1046 /* Update the sqlite_sequence table by storing the content of the
1047 ** maximum rowid counter values recorded while inserting into
1048 ** autoincrement tables.
1050 if( pParse
->nested
==0 && pParse
->pTriggerTab
==0 ){
1051 sqlite3AutoincrementEnd(pParse
);
1055 ** Return the number of rows inserted. If this routine is
1056 ** generating code because of a call to sqlite3NestedParse(), do not
1057 ** invoke the callback function.
1059 if( (db
->flags
&SQLITE_CountRows
) && !pParse
->nested
&& !pParse
->pTriggerTab
){
1060 sqlite3VdbeAddOp2(v
, OP_ResultRow
, regRowCount
, 1);
1061 sqlite3VdbeSetNumCols(v
, 1);
1062 sqlite3VdbeSetColName(v
, 0, COLNAME_NAME
, "rows inserted", SQLITE_STATIC
);
1066 sqlite3SrcListDelete(db
, pTabList
);
1067 sqlite3ExprListDelete(db
, pList
);
1068 sqlite3SelectDelete(db
, pSelect
);
1069 sqlite3IdListDelete(db
, pColumn
);
1070 sqlite3DbFree(db
, aRegIdx
);
1073 /* Make sure "isView" and other macros defined above are undefined. Otherwise
1074 ** they may interfere with compilation of other functions in this file
1075 ** (or in another file, if this file becomes part of the amalgamation). */
1087 ** Meanings of bits in of pWalker->eCode for checkConstraintUnchanged()
1089 #define CKCNSTRNT_COLUMN 0x01 /* CHECK constraint uses a changing column */
1090 #define CKCNSTRNT_ROWID 0x02 /* CHECK constraint references the ROWID */
1092 /* This is the Walker callback from checkConstraintUnchanged(). Set
1093 ** bit 0x01 of pWalker->eCode if
1094 ** pWalker->eCode to 0 if this expression node references any of the
1095 ** columns that are being modifed by an UPDATE statement.
1097 static int checkConstraintExprNode(Walker
*pWalker
, Expr
*pExpr
){
1098 if( pExpr
->op
==TK_COLUMN
){
1099 assert( pExpr
->iColumn
>=0 || pExpr
->iColumn
==-1 );
1100 if( pExpr
->iColumn
>=0 ){
1101 if( pWalker
->u
.aiCol
[pExpr
->iColumn
]>=0 ){
1102 pWalker
->eCode
|= CKCNSTRNT_COLUMN
;
1105 pWalker
->eCode
|= CKCNSTRNT_ROWID
;
1108 return WRC_Continue
;
1112 ** pExpr is a CHECK constraint on a row that is being UPDATE-ed. The
1113 ** only columns that are modified by the UPDATE are those for which
1114 ** aiChng[i]>=0, and also the ROWID is modified if chngRowid is true.
1116 ** Return true if CHECK constraint pExpr does not use any of the
1117 ** changing columns (or the rowid if it is changing). In other words,
1118 ** return true if this CHECK constraint can be skipped when validating
1119 ** the new row in the UPDATE statement.
1121 static int checkConstraintUnchanged(Expr
*pExpr
, int *aiChng
, int chngRowid
){
1123 memset(&w
, 0, sizeof(w
));
1125 w
.xExprCallback
= checkConstraintExprNode
;
1127 sqlite3WalkExpr(&w
, pExpr
);
1129 testcase( (w
.eCode
& CKCNSTRNT_ROWID
)!=0 );
1130 w
.eCode
&= ~CKCNSTRNT_ROWID
;
1132 testcase( w
.eCode
==0 );
1133 testcase( w
.eCode
==CKCNSTRNT_COLUMN
);
1134 testcase( w
.eCode
==CKCNSTRNT_ROWID
);
1135 testcase( w
.eCode
==(CKCNSTRNT_ROWID
|CKCNSTRNT_COLUMN
) );
1140 ** Generate code to do constraint checks prior to an INSERT or an UPDATE
1143 ** The regNewData parameter is the first register in a range that contains
1144 ** the data to be inserted or the data after the update. There will be
1145 ** pTab->nCol+1 registers in this range. The first register (the one
1146 ** that regNewData points to) will contain the new rowid, or NULL in the
1147 ** case of a WITHOUT ROWID table. The second register in the range will
1148 ** contain the content of the first table column. The third register will
1149 ** contain the content of the second table column. And so forth.
1151 ** The regOldData parameter is similar to regNewData except that it contains
1152 ** the data prior to an UPDATE rather than afterwards. regOldData is zero
1153 ** for an INSERT. This routine can distinguish between UPDATE and INSERT by
1154 ** checking regOldData for zero.
1156 ** For an UPDATE, the pkChng boolean is true if the true primary key (the
1157 ** rowid for a normal table or the PRIMARY KEY for a WITHOUT ROWID table)
1158 ** might be modified by the UPDATE. If pkChng is false, then the key of
1159 ** the iDataCur content table is guaranteed to be unchanged by the UPDATE.
1161 ** For an INSERT, the pkChng boolean indicates whether or not the rowid
1162 ** was explicitly specified as part of the INSERT statement. If pkChng
1163 ** is zero, it means that the either rowid is computed automatically or
1164 ** that the table is a WITHOUT ROWID table and has no rowid. On an INSERT,
1165 ** pkChng will only be true if the INSERT statement provides an integer
1166 ** value for either the rowid column or its INTEGER PRIMARY KEY alias.
1168 ** The code generated by this routine will store new index entries into
1169 ** registers identified by aRegIdx[]. No index entry is created for
1170 ** indices where aRegIdx[i]==0. The order of indices in aRegIdx[] is
1171 ** the same as the order of indices on the linked list of indices
1174 ** The caller must have already opened writeable cursors on the main
1175 ** table and all applicable indices (that is to say, all indices for which
1176 ** aRegIdx[] is not zero). iDataCur is the cursor for the main table when
1177 ** inserting or updating a rowid table, or the cursor for the PRIMARY KEY
1178 ** index when operating on a WITHOUT ROWID table. iIdxCur is the cursor
1179 ** for the first index in the pTab->pIndex list. Cursors for other indices
1180 ** are at iIdxCur+N for the N-th element of the pTab->pIndex list.
1182 ** This routine also generates code to check constraints. NOT NULL,
1183 ** CHECK, and UNIQUE constraints are all checked. If a constraint fails,
1184 ** then the appropriate action is performed. There are five possible
1185 ** actions: ROLLBACK, ABORT, FAIL, REPLACE, and IGNORE.
1187 ** Constraint type Action What Happens
1188 ** --------------- ---------- ----------------------------------------
1189 ** any ROLLBACK The current transaction is rolled back and
1190 ** sqlite3_step() returns immediately with a
1191 ** return code of SQLITE_CONSTRAINT.
1193 ** any ABORT Back out changes from the current command
1194 ** only (do not do a complete rollback) then
1195 ** cause sqlite3_step() to return immediately
1196 ** with SQLITE_CONSTRAINT.
1198 ** any FAIL Sqlite3_step() returns immediately with a
1199 ** return code of SQLITE_CONSTRAINT. The
1200 ** transaction is not rolled back and any
1201 ** changes to prior rows are retained.
1203 ** any IGNORE The attempt in insert or update the current
1204 ** row is skipped, without throwing an error.
1205 ** Processing continues with the next row.
1206 ** (There is an immediate jump to ignoreDest.)
1208 ** NOT NULL REPLACE The NULL value is replace by the default
1209 ** value for that column. If the default value
1210 ** is NULL, the action is the same as ABORT.
1212 ** UNIQUE REPLACE The other row that conflicts with the row
1213 ** being inserted is removed.
1215 ** CHECK REPLACE Illegal. The results in an exception.
1217 ** Which action to take is determined by the overrideError parameter.
1218 ** Or if overrideError==OE_Default, then the pParse->onError parameter
1219 ** is used. Or if pParse->onError==OE_Default then the onError value
1220 ** for the constraint is used.
1222 void sqlite3GenerateConstraintChecks(
1223 Parse
*pParse
, /* The parser context */
1224 Table
*pTab
, /* The table being inserted or updated */
1225 int *aRegIdx
, /* Use register aRegIdx[i] for index i. 0 for unused */
1226 int iDataCur
, /* Canonical data cursor (main table or PK index) */
1227 int iIdxCur
, /* First index cursor */
1228 int regNewData
, /* First register in a range holding values to insert */
1229 int regOldData
, /* Previous content. 0 for INSERTs */
1230 u8 pkChng
, /* Non-zero if the rowid or PRIMARY KEY changed */
1231 u8 overrideError
, /* Override onError to this if not OE_Default */
1232 int ignoreDest
, /* Jump to this label on an OE_Ignore resolution */
1233 int *pbMayReplace
, /* OUT: Set to true if constraint may cause a replace */
1234 int *aiChng
/* column i is unchanged if aiChng[i]<0 */
1236 Vdbe
*v
; /* VDBE under constrution */
1237 Index
*pIdx
; /* Pointer to one of the indices */
1238 Index
*pPk
= 0; /* The PRIMARY KEY index */
1239 sqlite3
*db
; /* Database connection */
1240 int i
; /* loop counter */
1241 int ix
; /* Index loop counter */
1242 int nCol
; /* Number of columns */
1243 int onError
; /* Conflict resolution strategy */
1244 int addr1
; /* Address of jump instruction */
1245 int seenReplace
= 0; /* True if REPLACE is used to resolve INT PK conflict */
1246 int nPkField
; /* Number of fields in PRIMARY KEY. 1 for ROWID tables */
1247 int ipkTop
= 0; /* Top of the rowid change constraint check */
1248 int ipkBottom
= 0; /* Bottom of the rowid change constraint check */
1249 u8 isUpdate
; /* True if this is an UPDATE operation */
1250 u8 bAffinityDone
= 0; /* True if the OP_Affinity operation has been run */
1252 isUpdate
= regOldData
!=0;
1254 v
= sqlite3GetVdbe(pParse
);
1256 assert( pTab
->pSelect
==0 ); /* This table is not a VIEW */
1259 /* pPk is the PRIMARY KEY index for WITHOUT ROWID tables and NULL for
1260 ** normal rowid tables. nPkField is the number of key fields in the
1261 ** pPk index or 1 for a rowid table. In other words, nPkField is the
1262 ** number of fields in the true primary key of the table. */
1263 if( HasRowid(pTab
) ){
1267 pPk
= sqlite3PrimaryKeyIndex(pTab
);
1268 nPkField
= pPk
->nKeyCol
;
1271 /* Record that this module has started */
1272 VdbeModuleComment((v
, "BEGIN: GenCnstCks(%d,%d,%d,%d,%d)",
1273 iDataCur
, iIdxCur
, regNewData
, regOldData
, pkChng
));
1275 /* Test all NOT NULL constraints.
1277 for(i
=0; i
<nCol
; i
++){
1278 if( i
==pTab
->iPKey
){
1279 continue; /* ROWID is never NULL */
1281 if( aiChng
&& aiChng
[i
]<0 ){
1282 /* Don't bother checking for NOT NULL on columns that do not change */
1285 onError
= pTab
->aCol
[i
].notNull
;
1286 if( onError
==OE_None
) continue; /* This column is allowed to be NULL */
1287 if( overrideError
!=OE_Default
){
1288 onError
= overrideError
;
1289 }else if( onError
==OE_Default
){
1292 if( onError
==OE_Replace
&& pTab
->aCol
[i
].pDflt
==0 ){
1295 assert( onError
==OE_Rollback
|| onError
==OE_Abort
|| onError
==OE_Fail
1296 || onError
==OE_Ignore
|| onError
==OE_Replace
);
1299 sqlite3MayAbort(pParse
);
1303 char *zMsg
= sqlite3MPrintf(db
, "%s.%s", pTab
->zName
,
1304 pTab
->aCol
[i
].zName
);
1305 sqlite3VdbeAddOp3(v
, OP_HaltIfNull
, SQLITE_CONSTRAINT_NOTNULL
, onError
,
1307 sqlite3VdbeAppendP4(v
, zMsg
, P4_DYNAMIC
);
1308 sqlite3VdbeChangeP5(v
, P5_ConstraintNotNull
);
1313 sqlite3VdbeAddOp2(v
, OP_IsNull
, regNewData
+1+i
, ignoreDest
);
1318 assert( onError
==OE_Replace
);
1319 addr1
= sqlite3VdbeAddOp1(v
, OP_NotNull
, regNewData
+1+i
);
1321 sqlite3ExprCode(pParse
, pTab
->aCol
[i
].pDflt
, regNewData
+1+i
);
1322 sqlite3VdbeJumpHere(v
, addr1
);
1328 /* Test all CHECK constraints
1330 #ifndef SQLITE_OMIT_CHECK
1331 if( pTab
->pCheck
&& (db
->flags
& SQLITE_IgnoreChecks
)==0 ){
1332 ExprList
*pCheck
= pTab
->pCheck
;
1333 pParse
->iSelfTab
= -(regNewData
+1);
1334 onError
= overrideError
!=OE_Default
? overrideError
: OE_Abort
;
1335 for(i
=0; i
<pCheck
->nExpr
; i
++){
1337 Expr
*pExpr
= pCheck
->a
[i
].pExpr
;
1338 if( aiChng
&& checkConstraintUnchanged(pExpr
, aiChng
, pkChng
) ) continue;
1339 allOk
= sqlite3VdbeMakeLabel(v
);
1340 sqlite3ExprIfTrue(pParse
, pExpr
, allOk
, SQLITE_JUMPIFNULL
);
1341 if( onError
==OE_Ignore
){
1342 sqlite3VdbeGoto(v
, ignoreDest
);
1344 char *zName
= pCheck
->a
[i
].zName
;
1345 if( zName
==0 ) zName
= pTab
->zName
;
1346 if( onError
==OE_Replace
) onError
= OE_Abort
; /* IMP: R-15569-63625 */
1347 sqlite3HaltConstraint(pParse
, SQLITE_CONSTRAINT_CHECK
,
1348 onError
, zName
, P4_TRANSIENT
,
1349 P5_ConstraintCheck
);
1351 sqlite3VdbeResolveLabel(v
, allOk
);
1353 pParse
->iSelfTab
= 0;
1355 #endif /* !defined(SQLITE_OMIT_CHECK) */
1357 /* If rowid is changing, make sure the new rowid does not previously
1358 ** exist in the table.
1360 if( pkChng
&& pPk
==0 ){
1361 int addrRowidOk
= sqlite3VdbeMakeLabel(v
);
1363 /* Figure out what action to take in case of a rowid collision */
1364 onError
= pTab
->keyConf
;
1365 if( overrideError
!=OE_Default
){
1366 onError
= overrideError
;
1367 }else if( onError
==OE_Default
){
1372 /* pkChng!=0 does not mean that the rowid has changed, only that
1373 ** it might have changed. Skip the conflict logic below if the rowid
1375 sqlite3VdbeAddOp3(v
, OP_Eq
, regNewData
, addrRowidOk
, regOldData
);
1376 sqlite3VdbeChangeP5(v
, SQLITE_NOTNULL
);
1380 /* If the response to a rowid conflict is REPLACE but the response
1381 ** to some other UNIQUE constraint is FAIL or IGNORE, then we need
1382 ** to defer the running of the rowid conflict checking until after
1383 ** the UNIQUE constraints have run.
1385 if( onError
==OE_Replace
&& overrideError
!=OE_Replace
){
1386 for(pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
){
1387 if( pIdx
->onError
==OE_Ignore
|| pIdx
->onError
==OE_Fail
){
1388 ipkTop
= sqlite3VdbeAddOp0(v
, OP_Goto
);
1394 /* Check to see if the new rowid already exists in the table. Skip
1395 ** the following conflict logic if it does not. */
1396 sqlite3VdbeAddOp3(v
, OP_NotExists
, iDataCur
, addrRowidOk
, regNewData
);
1399 /* Generate code that deals with a rowid collision */
1403 /* Fall thru into the next case */
1408 sqlite3RowidConstraint(pParse
, onError
, pTab
);
1412 /* If there are DELETE triggers on this table and the
1413 ** recursive-triggers flag is set, call GenerateRowDelete() to
1414 ** remove the conflicting row from the table. This will fire
1415 ** the triggers and remove both the table and index b-tree entries.
1417 ** Otherwise, if there are no triggers or the recursive-triggers
1418 ** flag is not set, but the table has one or more indexes, call
1419 ** GenerateRowIndexDelete(). This removes the index b-tree entries
1420 ** only. The table b-tree entry will be replaced by the new entry
1421 ** when it is inserted.
1423 ** If either GenerateRowDelete() or GenerateRowIndexDelete() is called,
1424 ** also invoke MultiWrite() to indicate that this VDBE may require
1425 ** statement rollback (if the statement is aborted after the delete
1426 ** takes place). Earlier versions called sqlite3MultiWrite() regardless,
1427 ** but being more selective here allows statements like:
1429 ** REPLACE INTO t(rowid) VALUES($newrowid)
1431 ** to run without a statement journal if there are no indexes on the
1434 Trigger
*pTrigger
= 0;
1435 if( db
->flags
&SQLITE_RecTriggers
){
1436 pTrigger
= sqlite3TriggersExist(pParse
, pTab
, TK_DELETE
, 0, 0);
1438 if( pTrigger
|| sqlite3FkRequired(pParse
, pTab
, 0, 0) ){
1439 sqlite3MultiWrite(pParse
);
1440 sqlite3GenerateRowDelete(pParse
, pTab
, pTrigger
, iDataCur
, iIdxCur
,
1441 regNewData
, 1, 0, OE_Replace
, 1, -1);
1443 #ifdef SQLITE_ENABLE_PREUPDATE_HOOK
1444 if( HasRowid(pTab
) ){
1445 /* This OP_Delete opcode fires the pre-update-hook only. It does
1446 ** not modify the b-tree. It is more efficient to let the coming
1447 ** OP_Insert replace the existing entry than it is to delete the
1448 ** existing entry and then insert a new one. */
1449 sqlite3VdbeAddOp2(v
, OP_Delete
, iDataCur
, OPFLAG_ISNOOP
);
1450 sqlite3VdbeAppendP4(v
, pTab
, P4_TABLE
);
1452 #endif /* SQLITE_ENABLE_PREUPDATE_HOOK */
1454 sqlite3MultiWrite(pParse
);
1455 sqlite3GenerateRowIndexDelete(pParse
, pTab
, iDataCur
, iIdxCur
,0,-1);
1462 /*assert( seenReplace==0 );*/
1463 sqlite3VdbeGoto(v
, ignoreDest
);
1467 sqlite3VdbeResolveLabel(v
, addrRowidOk
);
1469 ipkBottom
= sqlite3VdbeAddOp0(v
, OP_Goto
);
1470 sqlite3VdbeJumpHere(v
, ipkTop
);
1474 /* Test all UNIQUE constraints by creating entries for each UNIQUE
1475 ** index and making sure that duplicate entries do not already exist.
1476 ** Compute the revised record entries for indices as we go.
1478 ** This loop also handles the case of the PRIMARY KEY index for a
1479 ** WITHOUT ROWID table.
1481 for(ix
=0, pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
, ix
++){
1482 int regIdx
; /* Range of registers hold conent for pIdx */
1483 int regR
; /* Range of registers holding conflicting PK */
1484 int iThisCur
; /* Cursor for this UNIQUE index */
1485 int addrUniqueOk
; /* Jump here if the UNIQUE constraint is satisfied */
1487 if( aRegIdx
[ix
]==0 ) continue; /* Skip indices that do not change */
1488 if( bAffinityDone
==0 ){
1489 sqlite3TableAffinity(v
, pTab
, regNewData
+1);
1492 iThisCur
= iIdxCur
+ix
;
1493 addrUniqueOk
= sqlite3VdbeMakeLabel(v
);
1495 /* Skip partial indices for which the WHERE clause is not true */
1496 if( pIdx
->pPartIdxWhere
){
1497 sqlite3VdbeAddOp2(v
, OP_Null
, 0, aRegIdx
[ix
]);
1498 pParse
->iSelfTab
= -(regNewData
+1);
1499 sqlite3ExprIfFalseDup(pParse
, pIdx
->pPartIdxWhere
, addrUniqueOk
,
1501 pParse
->iSelfTab
= 0;
1504 /* Create a record for this index entry as it should appear after
1505 ** the insert or update. Store that record in the aRegIdx[ix] register
1507 regIdx
= aRegIdx
[ix
]+1;
1508 for(i
=0; i
<pIdx
->nColumn
; i
++){
1509 int iField
= pIdx
->aiColumn
[i
];
1511 if( iField
==XN_EXPR
){
1512 pParse
->iSelfTab
= -(regNewData
+1);
1513 sqlite3ExprCodeCopy(pParse
, pIdx
->aColExpr
->a
[i
].pExpr
, regIdx
+i
);
1514 pParse
->iSelfTab
= 0;
1515 VdbeComment((v
, "%s column %d", pIdx
->zName
, i
));
1517 if( iField
==XN_ROWID
|| iField
==pTab
->iPKey
){
1520 x
= iField
+ regNewData
+ 1;
1522 sqlite3VdbeAddOp2(v
, iField
<0 ? OP_IntCopy
: OP_SCopy
, x
, regIdx
+i
);
1523 VdbeComment((v
, "%s", iField
<0 ? "rowid" : pTab
->aCol
[iField
].zName
));
1526 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regIdx
, pIdx
->nColumn
, aRegIdx
[ix
]);
1527 VdbeComment((v
, "for %s", pIdx
->zName
));
1528 #ifdef SQLITE_ENABLE_NULL_TRIM
1529 if( pIdx
->idxType
==2 ) sqlite3SetMakeRecordP5(v
, pIdx
->pTable
);
1532 /* In an UPDATE operation, if this index is the PRIMARY KEY index
1533 ** of a WITHOUT ROWID table and there has been no change the
1534 ** primary key, then no collision is possible. The collision detection
1535 ** logic below can all be skipped. */
1536 if( isUpdate
&& pPk
==pIdx
&& pkChng
==0 ){
1537 sqlite3VdbeResolveLabel(v
, addrUniqueOk
);
1541 /* Find out what action to take in case there is a uniqueness conflict */
1542 onError
= pIdx
->onError
;
1543 if( onError
==OE_None
){
1544 sqlite3VdbeResolveLabel(v
, addrUniqueOk
);
1545 continue; /* pIdx is not a UNIQUE index */
1547 if( overrideError
!=OE_Default
){
1548 onError
= overrideError
;
1549 }else if( onError
==OE_Default
){
1553 /* Collision detection may be omitted if all of the following are true:
1554 ** (1) The conflict resolution algorithm is REPLACE
1555 ** (2) The table is a WITHOUT ROWID table
1556 ** (3) There are no secondary indexes on the table
1557 ** (4) No delete triggers need to be fired if there is a conflict
1558 ** (5) No FK constraint counters need to be updated if a conflict occurs.
1560 if( (ix
==0 && pIdx
->pNext
==0) /* Condition 3 */
1561 && pPk
==pIdx
/* Condition 2 */
1562 && onError
==OE_Replace
/* Condition 1 */
1563 && ( 0==(db
->flags
&SQLITE_RecTriggers
) || /* Condition 4 */
1564 0==sqlite3TriggersExist(pParse
, pTab
, TK_DELETE
, 0, 0))
1565 && ( 0==(db
->flags
&SQLITE_ForeignKeys
) || /* Condition 5 */
1566 (0==pTab
->pFKey
&& 0==sqlite3FkReferences(pTab
)))
1568 sqlite3VdbeResolveLabel(v
, addrUniqueOk
);
1572 /* Check to see if the new index entry will be unique */
1573 sqlite3VdbeAddOp4Int(v
, OP_NoConflict
, iThisCur
, addrUniqueOk
,
1574 regIdx
, pIdx
->nKeyCol
); VdbeCoverage(v
);
1576 /* Generate code to handle collisions */
1577 regR
= (pIdx
==pPk
) ? regIdx
: sqlite3GetTempRange(pParse
, nPkField
);
1578 if( isUpdate
|| onError
==OE_Replace
){
1579 if( HasRowid(pTab
) ){
1580 sqlite3VdbeAddOp2(v
, OP_IdxRowid
, iThisCur
, regR
);
1581 /* Conflict only if the rowid of the existing index entry
1582 ** is different from old-rowid */
1584 sqlite3VdbeAddOp3(v
, OP_Eq
, regR
, addrUniqueOk
, regOldData
);
1585 sqlite3VdbeChangeP5(v
, SQLITE_NOTNULL
);
1590 /* Extract the PRIMARY KEY from the end of the index entry and
1591 ** store it in registers regR..regR+nPk-1 */
1593 for(i
=0; i
<pPk
->nKeyCol
; i
++){
1594 assert( pPk
->aiColumn
[i
]>=0 );
1595 x
= sqlite3ColumnOfIndex(pIdx
, pPk
->aiColumn
[i
]);
1596 sqlite3VdbeAddOp3(v
, OP_Column
, iThisCur
, x
, regR
+i
);
1597 VdbeComment((v
, "%s.%s", pTab
->zName
,
1598 pTab
->aCol
[pPk
->aiColumn
[i
]].zName
));
1602 /* If currently processing the PRIMARY KEY of a WITHOUT ROWID
1603 ** table, only conflict if the new PRIMARY KEY values are actually
1604 ** different from the old.
1606 ** For a UNIQUE index, only conflict if the PRIMARY KEY values
1607 ** of the matched index row are different from the original PRIMARY
1608 ** KEY values of this row before the update. */
1609 int addrJump
= sqlite3VdbeCurrentAddr(v
)+pPk
->nKeyCol
;
1611 int regCmp
= (IsPrimaryKeyIndex(pIdx
) ? regIdx
: regR
);
1613 for(i
=0; i
<pPk
->nKeyCol
; i
++){
1614 char *p4
= (char*)sqlite3LocateCollSeq(pParse
, pPk
->azColl
[i
]);
1615 x
= pPk
->aiColumn
[i
];
1617 if( i
==(pPk
->nKeyCol
-1) ){
1618 addrJump
= addrUniqueOk
;
1621 sqlite3VdbeAddOp4(v
, op
,
1622 regOldData
+1+x
, addrJump
, regCmp
+i
, p4
, P4_COLLSEQ
1624 sqlite3VdbeChangeP5(v
, SQLITE_NOTNULL
);
1625 VdbeCoverageIf(v
, op
==OP_Eq
);
1626 VdbeCoverageIf(v
, op
==OP_Ne
);
1632 /* Generate code that executes if the new index entry is not unique */
1633 assert( onError
==OE_Rollback
|| onError
==OE_Abort
|| onError
==OE_Fail
1634 || onError
==OE_Ignore
|| onError
==OE_Replace
);
1639 sqlite3UniqueConstraint(pParse
, onError
, pIdx
);
1643 sqlite3VdbeGoto(v
, ignoreDest
);
1647 Trigger
*pTrigger
= 0;
1648 assert( onError
==OE_Replace
);
1649 sqlite3MultiWrite(pParse
);
1650 if( db
->flags
&SQLITE_RecTriggers
){
1651 pTrigger
= sqlite3TriggersExist(pParse
, pTab
, TK_DELETE
, 0, 0);
1653 sqlite3GenerateRowDelete(pParse
, pTab
, pTrigger
, iDataCur
, iIdxCur
,
1654 regR
, nPkField
, 0, OE_Replace
,
1655 (pIdx
==pPk
? ONEPASS_SINGLE
: ONEPASS_OFF
), iThisCur
);
1660 sqlite3VdbeResolveLabel(v
, addrUniqueOk
);
1661 if( regR
!=regIdx
) sqlite3ReleaseTempRange(pParse
, regR
, nPkField
);
1664 sqlite3VdbeGoto(v
, ipkTop
+1);
1665 sqlite3VdbeJumpHere(v
, ipkBottom
);
1668 *pbMayReplace
= seenReplace
;
1669 VdbeModuleComment((v
, "END: GenCnstCks(%d)", seenReplace
));
1672 #ifdef SQLITE_ENABLE_NULL_TRIM
1674 ** Change the P5 operand on the last opcode (which should be an OP_MakeRecord)
1675 ** to be the number of columns in table pTab that must not be NULL-trimmed.
1677 ** Or if no columns of pTab may be NULL-trimmed, leave P5 at zero.
1679 void sqlite3SetMakeRecordP5(Vdbe
*v
, Table
*pTab
){
1682 /* Records with omitted columns are only allowed for schema format
1683 ** version 2 and later (SQLite version 3.1.4, 2005-02-20). */
1684 if( pTab
->pSchema
->file_format
<2 ) return;
1686 for(i
=pTab
->nCol
-1; i
>0; i
--){
1687 if( pTab
->aCol
[i
].pDflt
!=0 ) break;
1688 if( pTab
->aCol
[i
].colFlags
& COLFLAG_PRIMKEY
) break;
1690 sqlite3VdbeChangeP5(v
, i
+1);
1695 ** This routine generates code to finish the INSERT or UPDATE operation
1696 ** that was started by a prior call to sqlite3GenerateConstraintChecks.
1697 ** A consecutive range of registers starting at regNewData contains the
1698 ** rowid and the content to be inserted.
1700 ** The arguments to this routine should be the same as the first six
1701 ** arguments to sqlite3GenerateConstraintChecks.
1703 void sqlite3CompleteInsertion(
1704 Parse
*pParse
, /* The parser context */
1705 Table
*pTab
, /* the table into which we are inserting */
1706 int iDataCur
, /* Cursor of the canonical data source */
1707 int iIdxCur
, /* First index cursor */
1708 int regNewData
, /* Range of content */
1709 int *aRegIdx
, /* Register used by each index. 0 for unused indices */
1710 int update_flags
, /* True for UPDATE, False for INSERT */
1711 int appendBias
, /* True if this is likely to be an append */
1712 int useSeekResult
/* True to set the USESEEKRESULT flag on OP_[Idx]Insert */
1714 Vdbe
*v
; /* Prepared statements under construction */
1715 Index
*pIdx
; /* An index being inserted or updated */
1716 u8 pik_flags
; /* flag values passed to the btree insert */
1717 int regData
; /* Content registers (after the rowid) */
1718 int regRec
; /* Register holding assembled record for the table */
1719 int i
; /* Loop counter */
1720 u8 bAffinityDone
= 0; /* True if OP_Affinity has been run already */
1722 assert( update_flags
==0
1723 || update_flags
==OPFLAG_ISUPDATE
1724 || update_flags
==(OPFLAG_ISUPDATE
|OPFLAG_SAVEPOSITION
)
1727 v
= sqlite3GetVdbe(pParse
);
1729 assert( pTab
->pSelect
==0 ); /* This table is not a VIEW */
1730 for(i
=0, pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
, i
++){
1731 if( aRegIdx
[i
]==0 ) continue;
1733 if( pIdx
->pPartIdxWhere
){
1734 sqlite3VdbeAddOp2(v
, OP_IsNull
, aRegIdx
[i
], sqlite3VdbeCurrentAddr(v
)+2);
1737 pik_flags
= (useSeekResult
? OPFLAG_USESEEKRESULT
: 0);
1738 if( IsPrimaryKeyIndex(pIdx
) && !HasRowid(pTab
) ){
1739 assert( pParse
->nested
==0 );
1740 pik_flags
|= OPFLAG_NCHANGE
;
1741 pik_flags
|= (update_flags
& OPFLAG_SAVEPOSITION
);
1742 #ifdef SQLITE_ENABLE_PREUPDATE_HOOK
1743 if( update_flags
==0 ){
1744 sqlite3VdbeAddOp4(v
, OP_InsertInt
,
1745 iIdxCur
+i
, aRegIdx
[i
], 0, (char*)pTab
, P4_TABLE
1747 sqlite3VdbeChangeP5(v
, OPFLAG_ISNOOP
);
1751 sqlite3VdbeAddOp4Int(v
, OP_IdxInsert
, iIdxCur
+i
, aRegIdx
[i
],
1753 pIdx
->uniqNotNull
? pIdx
->nKeyCol
: pIdx
->nColumn
);
1754 sqlite3VdbeChangeP5(v
, pik_flags
);
1756 if( !HasRowid(pTab
) ) return;
1757 regData
= regNewData
+ 1;
1758 regRec
= sqlite3GetTempReg(pParse
);
1759 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regData
, pTab
->nCol
, regRec
);
1760 sqlite3SetMakeRecordP5(v
, pTab
);
1761 if( !bAffinityDone
){
1762 sqlite3TableAffinity(v
, pTab
, 0);
1763 sqlite3ExprCacheAffinityChange(pParse
, regData
, pTab
->nCol
);
1765 if( pParse
->nested
){
1768 pik_flags
= OPFLAG_NCHANGE
;
1769 pik_flags
|= (update_flags
?update_flags
:OPFLAG_LASTROWID
);
1772 pik_flags
|= OPFLAG_APPEND
;
1774 if( useSeekResult
){
1775 pik_flags
|= OPFLAG_USESEEKRESULT
;
1777 sqlite3VdbeAddOp3(v
, OP_Insert
, iDataCur
, regRec
, regNewData
);
1778 if( !pParse
->nested
){
1779 sqlite3VdbeAppendP4(v
, pTab
, P4_TABLE
);
1781 sqlite3VdbeChangeP5(v
, pik_flags
);
1785 ** Allocate cursors for the pTab table and all its indices and generate
1786 ** code to open and initialized those cursors.
1788 ** The cursor for the object that contains the complete data (normally
1789 ** the table itself, but the PRIMARY KEY index in the case of a WITHOUT
1790 ** ROWID table) is returned in *piDataCur. The first index cursor is
1791 ** returned in *piIdxCur. The number of indices is returned.
1793 ** Use iBase as the first cursor (either the *piDataCur for rowid tables
1794 ** or the first index for WITHOUT ROWID tables) if it is non-negative.
1795 ** If iBase is negative, then allocate the next available cursor.
1797 ** For a rowid table, *piDataCur will be exactly one less than *piIdxCur.
1798 ** For a WITHOUT ROWID table, *piDataCur will be somewhere in the range
1799 ** of *piIdxCurs, depending on where the PRIMARY KEY index appears on the
1800 ** pTab->pIndex list.
1802 ** If pTab is a virtual table, then this routine is a no-op and the
1803 ** *piDataCur and *piIdxCur values are left uninitialized.
1805 int sqlite3OpenTableAndIndices(
1806 Parse
*pParse
, /* Parsing context */
1807 Table
*pTab
, /* Table to be opened */
1808 int op
, /* OP_OpenRead or OP_OpenWrite */
1809 u8 p5
, /* P5 value for OP_Open* opcodes (except on WITHOUT ROWID) */
1810 int iBase
, /* Use this for the table cursor, if there is one */
1811 u8
*aToOpen
, /* If not NULL: boolean for each table and index */
1812 int *piDataCur
, /* Write the database source cursor number here */
1813 int *piIdxCur
/* Write the first index cursor number here */
1821 assert( op
==OP_OpenRead
|| op
==OP_OpenWrite
);
1822 assert( op
==OP_OpenWrite
|| p5
==0 );
1823 if( IsVirtual(pTab
) ){
1824 /* This routine is a no-op for virtual tables. Leave the output
1825 ** variables *piDataCur and *piIdxCur uninitialized so that valgrind
1826 ** can detect if they are used by mistake in the caller. */
1829 iDb
= sqlite3SchemaToIndex(pParse
->db
, pTab
->pSchema
);
1830 v
= sqlite3GetVdbe(pParse
);
1832 if( iBase
<0 ) iBase
= pParse
->nTab
;
1834 if( piDataCur
) *piDataCur
= iDataCur
;
1835 if( HasRowid(pTab
) && (aToOpen
==0 || aToOpen
[0]) ){
1836 sqlite3OpenTable(pParse
, iDataCur
, iDb
, pTab
, op
);
1838 sqlite3TableLock(pParse
, iDb
, pTab
->tnum
, op
==OP_OpenWrite
, pTab
->zName
);
1840 if( piIdxCur
) *piIdxCur
= iBase
;
1841 for(i
=0, pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
, i
++){
1842 int iIdxCur
= iBase
++;
1843 assert( pIdx
->pSchema
==pTab
->pSchema
);
1844 if( IsPrimaryKeyIndex(pIdx
) && !HasRowid(pTab
) ){
1845 if( piDataCur
) *piDataCur
= iIdxCur
;
1848 if( aToOpen
==0 || aToOpen
[i
+1] ){
1849 sqlite3VdbeAddOp3(v
, op
, iIdxCur
, pIdx
->tnum
, iDb
);
1850 sqlite3VdbeSetP4KeyInfo(pParse
, pIdx
);
1851 sqlite3VdbeChangeP5(v
, p5
);
1852 VdbeComment((v
, "%s", pIdx
->zName
));
1855 if( iBase
>pParse
->nTab
) pParse
->nTab
= iBase
;
1862 ** The following global variable is incremented whenever the
1863 ** transfer optimization is used. This is used for testing
1864 ** purposes only - to make sure the transfer optimization really
1865 ** is happening when it is supposed to.
1867 int sqlite3_xferopt_count
;
1868 #endif /* SQLITE_TEST */
1871 #ifndef SQLITE_OMIT_XFER_OPT
1873 ** Check to see if index pSrc is compatible as a source of data
1874 ** for index pDest in an insert transfer optimization. The rules
1875 ** for a compatible index:
1877 ** * The index is over the same set of columns
1878 ** * The same DESC and ASC markings occurs on all columns
1879 ** * The same onError processing (OE_Abort, OE_Ignore, etc)
1880 ** * The same collating sequence on each column
1881 ** * The index has the exact same WHERE clause
1883 static int xferCompatibleIndex(Index
*pDest
, Index
*pSrc
){
1885 assert( pDest
&& pSrc
);
1886 assert( pDest
->pTable
!=pSrc
->pTable
);
1887 if( pDest
->nKeyCol
!=pSrc
->nKeyCol
){
1888 return 0; /* Different number of columns */
1890 if( pDest
->onError
!=pSrc
->onError
){
1891 return 0; /* Different conflict resolution strategies */
1893 for(i
=0; i
<pSrc
->nKeyCol
; i
++){
1894 if( pSrc
->aiColumn
[i
]!=pDest
->aiColumn
[i
] ){
1895 return 0; /* Different columns indexed */
1897 if( pSrc
->aiColumn
[i
]==XN_EXPR
){
1898 assert( pSrc
->aColExpr
!=0 && pDest
->aColExpr
!=0 );
1899 if( sqlite3ExprCompare(0, pSrc
->aColExpr
->a
[i
].pExpr
,
1900 pDest
->aColExpr
->a
[i
].pExpr
, -1)!=0 ){
1901 return 0; /* Different expressions in the index */
1904 if( pSrc
->aSortOrder
[i
]!=pDest
->aSortOrder
[i
] ){
1905 return 0; /* Different sort orders */
1907 if( sqlite3_stricmp(pSrc
->azColl
[i
],pDest
->azColl
[i
])!=0 ){
1908 return 0; /* Different collating sequences */
1911 if( sqlite3ExprCompare(0, pSrc
->pPartIdxWhere
, pDest
->pPartIdxWhere
, -1) ){
1912 return 0; /* Different WHERE clauses */
1915 /* If no test above fails then the indices must be compatible */
1920 ** Attempt the transfer optimization on INSERTs of the form
1922 ** INSERT INTO tab1 SELECT * FROM tab2;
1924 ** The xfer optimization transfers raw records from tab2 over to tab1.
1925 ** Columns are not decoded and reassembled, which greatly improves
1926 ** performance. Raw index records are transferred in the same way.
1928 ** The xfer optimization is only attempted if tab1 and tab2 are compatible.
1929 ** There are lots of rules for determining compatibility - see comments
1930 ** embedded in the code for details.
1932 ** This routine returns TRUE if the optimization is guaranteed to be used.
1933 ** Sometimes the xfer optimization will only work if the destination table
1934 ** is empty - a factor that can only be determined at run-time. In that
1935 ** case, this routine generates code for the xfer optimization but also
1936 ** does a test to see if the destination table is empty and jumps over the
1937 ** xfer optimization code if the test fails. In that case, this routine
1938 ** returns FALSE so that the caller will know to go ahead and generate
1939 ** an unoptimized transfer. This routine also returns FALSE if there
1940 ** is no chance that the xfer optimization can be applied.
1942 ** This optimization is particularly useful at making VACUUM run faster.
1944 static int xferOptimization(
1945 Parse
*pParse
, /* Parser context */
1946 Table
*pDest
, /* The table we are inserting into */
1947 Select
*pSelect
, /* A SELECT statement to use as the data source */
1948 int onError
, /* How to handle constraint errors */
1949 int iDbDest
/* The database of pDest */
1951 sqlite3
*db
= pParse
->db
;
1952 ExprList
*pEList
; /* The result set of the SELECT */
1953 Table
*pSrc
; /* The table in the FROM clause of SELECT */
1954 Index
*pSrcIdx
, *pDestIdx
; /* Source and destination indices */
1955 struct SrcList_item
*pItem
; /* An element of pSelect->pSrc */
1956 int i
; /* Loop counter */
1957 int iDbSrc
; /* The database of pSrc */
1958 int iSrc
, iDest
; /* Cursors from source and destination */
1959 int addr1
, addr2
; /* Loop addresses */
1960 int emptyDestTest
= 0; /* Address of test for empty pDest */
1961 int emptySrcTest
= 0; /* Address of test for empty pSrc */
1962 Vdbe
*v
; /* The VDBE we are building */
1963 int regAutoinc
; /* Memory register used by AUTOINC */
1964 int destHasUniqueIdx
= 0; /* True if pDest has a UNIQUE index */
1965 int regData
, regRowid
; /* Registers holding data and rowid */
1968 return 0; /* Must be of the form INSERT INTO ... SELECT ... */
1970 if( pParse
->pWith
|| pSelect
->pWith
){
1971 /* Do not attempt to process this query if there are an WITH clauses
1972 ** attached to it. Proceeding may generate a false "no such table: xxx"
1973 ** error if pSelect reads from a CTE named "xxx". */
1976 if( sqlite3TriggerList(pParse
, pDest
) ){
1977 return 0; /* tab1 must not have triggers */
1979 #ifndef SQLITE_OMIT_VIRTUALTABLE
1980 if( IsVirtual(pDest
) ){
1981 return 0; /* tab1 must not be a virtual table */
1984 if( onError
==OE_Default
){
1985 if( pDest
->iPKey
>=0 ) onError
= pDest
->keyConf
;
1986 if( onError
==OE_Default
) onError
= OE_Abort
;
1988 assert(pSelect
->pSrc
); /* allocated even if there is no FROM clause */
1989 if( pSelect
->pSrc
->nSrc
!=1 ){
1990 return 0; /* FROM clause must have exactly one term */
1992 if( pSelect
->pSrc
->a
[0].pSelect
){
1993 return 0; /* FROM clause cannot contain a subquery */
1995 if( pSelect
->pWhere
){
1996 return 0; /* SELECT may not have a WHERE clause */
1998 if( pSelect
->pOrderBy
){
1999 return 0; /* SELECT may not have an ORDER BY clause */
2001 /* Do not need to test for a HAVING clause. If HAVING is present but
2002 ** there is no ORDER BY, we will get an error. */
2003 if( pSelect
->pGroupBy
){
2004 return 0; /* SELECT may not have a GROUP BY clause */
2006 if( pSelect
->pLimit
){
2007 return 0; /* SELECT may not have a LIMIT clause */
2009 assert( pSelect
->pOffset
==0 ); /* Must be so if pLimit==0 */
2010 if( pSelect
->pPrior
){
2011 return 0; /* SELECT may not be a compound query */
2013 if( pSelect
->selFlags
& SF_Distinct
){
2014 return 0; /* SELECT may not be DISTINCT */
2016 pEList
= pSelect
->pEList
;
2017 assert( pEList
!=0 );
2018 if( pEList
->nExpr
!=1 ){
2019 return 0; /* The result set must have exactly one column */
2021 assert( pEList
->a
[0].pExpr
);
2022 if( pEList
->a
[0].pExpr
->op
!=TK_ASTERISK
){
2023 return 0; /* The result set must be the special operator "*" */
2026 /* At this point we have established that the statement is of the
2027 ** correct syntactic form to participate in this optimization. Now
2028 ** we have to check the semantics.
2030 pItem
= pSelect
->pSrc
->a
;
2031 pSrc
= sqlite3LocateTableItem(pParse
, 0, pItem
);
2033 return 0; /* FROM clause does not contain a real table */
2036 return 0; /* tab1 and tab2 may not be the same table */
2038 if( HasRowid(pDest
)!=HasRowid(pSrc
) ){
2039 return 0; /* source and destination must both be WITHOUT ROWID or not */
2041 #ifndef SQLITE_OMIT_VIRTUALTABLE
2042 if( IsVirtual(pSrc
) ){
2043 return 0; /* tab2 must not be a virtual table */
2046 if( pSrc
->pSelect
){
2047 return 0; /* tab2 may not be a view */
2049 if( pDest
->nCol
!=pSrc
->nCol
){
2050 return 0; /* Number of columns must be the same in tab1 and tab2 */
2052 if( pDest
->iPKey
!=pSrc
->iPKey
){
2053 return 0; /* Both tables must have the same INTEGER PRIMARY KEY */
2055 for(i
=0; i
<pDest
->nCol
; i
++){
2056 Column
*pDestCol
= &pDest
->aCol
[i
];
2057 Column
*pSrcCol
= &pSrc
->aCol
[i
];
2058 #ifdef SQLITE_ENABLE_HIDDEN_COLUMNS
2059 if( (db
->mDbFlags
& DBFLAG_Vacuum
)==0
2060 && (pDestCol
->colFlags
| pSrcCol
->colFlags
) & COLFLAG_HIDDEN
2062 return 0; /* Neither table may have __hidden__ columns */
2065 if( pDestCol
->affinity
!=pSrcCol
->affinity
){
2066 return 0; /* Affinity must be the same on all columns */
2068 if( sqlite3_stricmp(pDestCol
->zColl
, pSrcCol
->zColl
)!=0 ){
2069 return 0; /* Collating sequence must be the same on all columns */
2071 if( pDestCol
->notNull
&& !pSrcCol
->notNull
){
2072 return 0; /* tab2 must be NOT NULL if tab1 is */
2074 /* Default values for second and subsequent columns need to match. */
2076 assert( pDestCol
->pDflt
==0 || pDestCol
->pDflt
->op
==TK_SPAN
);
2077 assert( pSrcCol
->pDflt
==0 || pSrcCol
->pDflt
->op
==TK_SPAN
);
2078 if( (pDestCol
->pDflt
==0)!=(pSrcCol
->pDflt
==0)
2079 || (pDestCol
->pDflt
&& strcmp(pDestCol
->pDflt
->u
.zToken
,
2080 pSrcCol
->pDflt
->u
.zToken
)!=0)
2082 return 0; /* Default values must be the same for all columns */
2086 for(pDestIdx
=pDest
->pIndex
; pDestIdx
; pDestIdx
=pDestIdx
->pNext
){
2087 if( IsUniqueIndex(pDestIdx
) ){
2088 destHasUniqueIdx
= 1;
2090 for(pSrcIdx
=pSrc
->pIndex
; pSrcIdx
; pSrcIdx
=pSrcIdx
->pNext
){
2091 if( xferCompatibleIndex(pDestIdx
, pSrcIdx
) ) break;
2094 return 0; /* pDestIdx has no corresponding index in pSrc */
2097 #ifndef SQLITE_OMIT_CHECK
2098 if( pDest
->pCheck
&& sqlite3ExprListCompare(pSrc
->pCheck
,pDest
->pCheck
,-1) ){
2099 return 0; /* Tables have different CHECK constraints. Ticket #2252 */
2102 #ifndef SQLITE_OMIT_FOREIGN_KEY
2103 /* Disallow the transfer optimization if the destination table constains
2104 ** any foreign key constraints. This is more restrictive than necessary.
2105 ** But the main beneficiary of the transfer optimization is the VACUUM
2106 ** command, and the VACUUM command disables foreign key constraints. So
2107 ** the extra complication to make this rule less restrictive is probably
2108 ** not worth the effort. Ticket [6284df89debdfa61db8073e062908af0c9b6118e]
2110 if( (db
->flags
& SQLITE_ForeignKeys
)!=0 && pDest
->pFKey
!=0 ){
2114 if( (db
->flags
& SQLITE_CountRows
)!=0 ){
2115 return 0; /* xfer opt does not play well with PRAGMA count_changes */
2118 /* If we get this far, it means that the xfer optimization is at
2119 ** least a possibility, though it might only work if the destination
2120 ** table (tab1) is initially empty.
2123 sqlite3_xferopt_count
++;
2125 iDbSrc
= sqlite3SchemaToIndex(db
, pSrc
->pSchema
);
2126 v
= sqlite3GetVdbe(pParse
);
2127 sqlite3CodeVerifySchema(pParse
, iDbSrc
);
2128 iSrc
= pParse
->nTab
++;
2129 iDest
= pParse
->nTab
++;
2130 regAutoinc
= autoIncBegin(pParse
, iDbDest
, pDest
);
2131 regData
= sqlite3GetTempReg(pParse
);
2132 regRowid
= sqlite3GetTempReg(pParse
);
2133 sqlite3OpenTable(pParse
, iDest
, iDbDest
, pDest
, OP_OpenWrite
);
2134 assert( HasRowid(pDest
) || destHasUniqueIdx
);
2135 if( (db
->mDbFlags
& DBFLAG_Vacuum
)==0 && (
2136 (pDest
->iPKey
<0 && pDest
->pIndex
!=0) /* (1) */
2137 || destHasUniqueIdx
/* (2) */
2138 || (onError
!=OE_Abort
&& onError
!=OE_Rollback
) /* (3) */
2140 /* In some circumstances, we are able to run the xfer optimization
2141 ** only if the destination table is initially empty. Unless the
2142 ** DBFLAG_Vacuum flag is set, this block generates code to make
2143 ** that determination. If DBFLAG_Vacuum is set, then the destination
2144 ** table is always empty.
2146 ** Conditions under which the destination must be empty:
2148 ** (1) There is no INTEGER PRIMARY KEY but there are indices.
2149 ** (If the destination is not initially empty, the rowid fields
2150 ** of index entries might need to change.)
2152 ** (2) The destination has a unique index. (The xfer optimization
2153 ** is unable to test uniqueness.)
2155 ** (3) onError is something other than OE_Abort and OE_Rollback.
2157 addr1
= sqlite3VdbeAddOp2(v
, OP_Rewind
, iDest
, 0); VdbeCoverage(v
);
2158 emptyDestTest
= sqlite3VdbeAddOp0(v
, OP_Goto
);
2159 sqlite3VdbeJumpHere(v
, addr1
);
2161 if( HasRowid(pSrc
) ){
2163 sqlite3OpenTable(pParse
, iSrc
, iDbSrc
, pSrc
, OP_OpenRead
);
2164 emptySrcTest
= sqlite3VdbeAddOp2(v
, OP_Rewind
, iSrc
, 0); VdbeCoverage(v
);
2165 if( pDest
->iPKey
>=0 ){
2166 addr1
= sqlite3VdbeAddOp2(v
, OP_Rowid
, iSrc
, regRowid
);
2167 addr2
= sqlite3VdbeAddOp3(v
, OP_NotExists
, iDest
, 0, regRowid
);
2169 sqlite3RowidConstraint(pParse
, onError
, pDest
);
2170 sqlite3VdbeJumpHere(v
, addr2
);
2171 autoIncStep(pParse
, regAutoinc
, regRowid
);
2172 }else if( pDest
->pIndex
==0 ){
2173 addr1
= sqlite3VdbeAddOp2(v
, OP_NewRowid
, iDest
, regRowid
);
2175 addr1
= sqlite3VdbeAddOp2(v
, OP_Rowid
, iSrc
, regRowid
);
2176 assert( (pDest
->tabFlags
& TF_Autoincrement
)==0 );
2178 sqlite3VdbeAddOp3(v
, OP_RowData
, iSrc
, regData
, 1);
2179 if( db
->mDbFlags
& DBFLAG_Vacuum
){
2180 sqlite3VdbeAddOp1(v
, OP_SeekEnd
, iDest
);
2181 insFlags
= OPFLAG_NCHANGE
|OPFLAG_LASTROWID
|
2182 OPFLAG_APPEND
|OPFLAG_USESEEKRESULT
;
2184 insFlags
= OPFLAG_NCHANGE
|OPFLAG_LASTROWID
|OPFLAG_APPEND
;
2186 sqlite3VdbeAddOp4(v
, OP_Insert
, iDest
, regData
, regRowid
,
2187 (char*)pDest
, P4_TABLE
);
2188 sqlite3VdbeChangeP5(v
, insFlags
);
2189 sqlite3VdbeAddOp2(v
, OP_Next
, iSrc
, addr1
); VdbeCoverage(v
);
2190 sqlite3VdbeAddOp2(v
, OP_Close
, iSrc
, 0);
2191 sqlite3VdbeAddOp2(v
, OP_Close
, iDest
, 0);
2193 sqlite3TableLock(pParse
, iDbDest
, pDest
->tnum
, 1, pDest
->zName
);
2194 sqlite3TableLock(pParse
, iDbSrc
, pSrc
->tnum
, 0, pSrc
->zName
);
2196 for(pDestIdx
=pDest
->pIndex
; pDestIdx
; pDestIdx
=pDestIdx
->pNext
){
2198 for(pSrcIdx
=pSrc
->pIndex
; ALWAYS(pSrcIdx
); pSrcIdx
=pSrcIdx
->pNext
){
2199 if( xferCompatibleIndex(pDestIdx
, pSrcIdx
) ) break;
2202 sqlite3VdbeAddOp3(v
, OP_OpenRead
, iSrc
, pSrcIdx
->tnum
, iDbSrc
);
2203 sqlite3VdbeSetP4KeyInfo(pParse
, pSrcIdx
);
2204 VdbeComment((v
, "%s", pSrcIdx
->zName
));
2205 sqlite3VdbeAddOp3(v
, OP_OpenWrite
, iDest
, pDestIdx
->tnum
, iDbDest
);
2206 sqlite3VdbeSetP4KeyInfo(pParse
, pDestIdx
);
2207 sqlite3VdbeChangeP5(v
, OPFLAG_BULKCSR
);
2208 VdbeComment((v
, "%s", pDestIdx
->zName
));
2209 addr1
= sqlite3VdbeAddOp2(v
, OP_Rewind
, iSrc
, 0); VdbeCoverage(v
);
2210 sqlite3VdbeAddOp3(v
, OP_RowData
, iSrc
, regData
, 1);
2211 if( db
->mDbFlags
& DBFLAG_Vacuum
){
2212 /* This INSERT command is part of a VACUUM operation, which guarantees
2213 ** that the destination table is empty. If all indexed columns use
2214 ** collation sequence BINARY, then it can also be assumed that the
2215 ** index will be populated by inserting keys in strictly sorted
2216 ** order. In this case, instead of seeking within the b-tree as part
2217 ** of every OP_IdxInsert opcode, an OP_SeekEnd is added before the
2218 ** OP_IdxInsert to seek to the point within the b-tree where each key
2219 ** should be inserted. This is faster.
2221 ** If any of the indexed columns use a collation sequence other than
2222 ** BINARY, this optimization is disabled. This is because the user
2223 ** might change the definition of a collation sequence and then run
2224 ** a VACUUM command. In that case keys may not be written in strictly
2226 for(i
=0; i
<pSrcIdx
->nColumn
; i
++){
2227 const char *zColl
= pSrcIdx
->azColl
[i
];
2228 if( sqlite3_stricmp(sqlite3StrBINARY
, zColl
) ) break;
2230 if( i
==pSrcIdx
->nColumn
){
2231 idxInsFlags
= OPFLAG_USESEEKRESULT
;
2232 sqlite3VdbeAddOp1(v
, OP_SeekEnd
, iDest
);
2235 if( !HasRowid(pSrc
) && pDestIdx
->idxType
==2 ){
2236 idxInsFlags
|= OPFLAG_NCHANGE
;
2238 sqlite3VdbeAddOp2(v
, OP_IdxInsert
, iDest
, regData
);
2239 sqlite3VdbeChangeP5(v
, idxInsFlags
|OPFLAG_APPEND
);
2240 sqlite3VdbeAddOp2(v
, OP_Next
, iSrc
, addr1
+1); VdbeCoverage(v
);
2241 sqlite3VdbeJumpHere(v
, addr1
);
2242 sqlite3VdbeAddOp2(v
, OP_Close
, iSrc
, 0);
2243 sqlite3VdbeAddOp2(v
, OP_Close
, iDest
, 0);
2245 if( emptySrcTest
) sqlite3VdbeJumpHere(v
, emptySrcTest
);
2246 sqlite3ReleaseTempReg(pParse
, regRowid
);
2247 sqlite3ReleaseTempReg(pParse
, regData
);
2248 if( emptyDestTest
){
2249 sqlite3AutoincrementEnd(pParse
);
2250 sqlite3VdbeAddOp2(v
, OP_Halt
, SQLITE_OK
, 0);
2251 sqlite3VdbeJumpHere(v
, emptyDestTest
);
2252 sqlite3VdbeAddOp2(v
, OP_Close
, iDest
, 0);
2258 #endif /* SQLITE_OMIT_XFER_OPT */