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);
913 if( pOp
->opcode
==OP_Null
&& !IsVirtual(pTab
) ){
915 pOp
->opcode
= OP_NewRowid
;
918 pOp
->p3
= regAutoinc
;
921 /* If the PRIMARY KEY expression is NULL, then use OP_NewRowid
922 ** to generate a unique primary key value.
926 if( !IsVirtual(pTab
) ){
927 addr1
= sqlite3VdbeAddOp1(v
, OP_NotNull
, regRowid
); VdbeCoverage(v
);
928 sqlite3VdbeAddOp3(v
, OP_NewRowid
, iDataCur
, regRowid
, regAutoinc
);
929 sqlite3VdbeJumpHere(v
, addr1
);
931 addr1
= sqlite3VdbeCurrentAddr(v
);
932 sqlite3VdbeAddOp2(v
, OP_IsNull
, regRowid
, addr1
+2); VdbeCoverage(v
);
934 sqlite3VdbeAddOp1(v
, OP_MustBeInt
, regRowid
); VdbeCoverage(v
);
936 }else if( IsVirtual(pTab
) || withoutRowid
){
937 sqlite3VdbeAddOp2(v
, OP_Null
, 0, regRowid
);
939 sqlite3VdbeAddOp3(v
, OP_NewRowid
, iDataCur
, regRowid
, regAutoinc
);
942 autoIncStep(pParse
, regAutoinc
, regRowid
);
944 /* Compute data for all columns of the new entry, beginning
945 ** with the first column.
948 for(i
=0; i
<pTab
->nCol
; i
++){
949 int iRegStore
= regRowid
+1+i
;
950 if( i
==pTab
->iPKey
){
951 /* The value of the INTEGER PRIMARY KEY column is always a NULL.
952 ** Whenever this column is read, the rowid will be substituted
953 ** in its place. Hence, fill this column with a NULL to avoid
954 ** taking up data space with information that will never be used.
955 ** As there may be shallow copies of this value, make it a soft-NULL */
956 sqlite3VdbeAddOp1(v
, OP_SoftNull
, iRegStore
);
960 if( IsHiddenColumn(&pTab
->aCol
[i
]) ){
967 for(j
=0; j
<pColumn
->nId
; j
++){
968 if( pColumn
->a
[j
].idx
==i
) break;
971 if( j
<0 || nColumn
==0 || (pColumn
&& j
>=pColumn
->nId
) ){
972 sqlite3ExprCodeFactorable(pParse
, pTab
->aCol
[i
].pDflt
, iRegStore
);
973 }else if( useTempTable
){
974 sqlite3VdbeAddOp3(v
, OP_Column
, srcTab
, j
, iRegStore
);
976 if( regFromSelect
!=regData
){
977 sqlite3VdbeAddOp2(v
, OP_SCopy
, regFromSelect
+j
, iRegStore
);
980 sqlite3ExprCode(pParse
, pList
->a
[j
].pExpr
, iRegStore
);
984 /* Generate code to check constraints and generate index keys and
987 #ifndef SQLITE_OMIT_VIRTUALTABLE
988 if( IsVirtual(pTab
) ){
989 const char *pVTab
= (const char *)sqlite3GetVTable(db
, pTab
);
990 sqlite3VtabMakeWritable(pParse
, pTab
);
991 sqlite3VdbeAddOp4(v
, OP_VUpdate
, 1, pTab
->nCol
+2, regIns
, pVTab
, P4_VTAB
);
992 sqlite3VdbeChangeP5(v
, onError
==OE_Default
? OE_Abort
: onError
);
993 sqlite3MayAbort(pParse
);
997 int isReplace
; /* Set to true if constraints may cause a replace */
998 int bUseSeek
; /* True to use OPFLAG_SEEKRESULT */
999 sqlite3GenerateConstraintChecks(pParse
, pTab
, aRegIdx
, iDataCur
, iIdxCur
,
1000 regIns
, 0, ipkColumn
>=0, onError
, endOfLoop
, &isReplace
, 0
1002 sqlite3FkCheck(pParse
, pTab
, 0, regIns
, 0, 0);
1004 /* Set the OPFLAG_USESEEKRESULT flag if either (a) there are no REPLACE
1005 ** constraints or (b) there are no triggers and this table is not a
1006 ** parent table in a foreign key constraint. It is safe to set the
1007 ** flag in the second case as if any REPLACE constraint is hit, an
1008 ** OP_Delete or OP_IdxDelete instruction will be executed on each
1009 ** cursor that is disturbed. And these instructions both clear the
1010 ** VdbeCursor.seekResult variable, disabling the OPFLAG_USESEEKRESULT
1011 ** functionality. */
1012 bUseSeek
= (isReplace
==0 || (pTrigger
==0 &&
1013 ((db
->flags
& SQLITE_ForeignKeys
)==0 || sqlite3FkReferences(pTab
)==0)
1015 sqlite3CompleteInsertion(pParse
, pTab
, iDataCur
, iIdxCur
,
1016 regIns
, aRegIdx
, 0, appendFlag
, bUseSeek
1021 /* Update the count of rows that are inserted
1023 if( (db
->flags
& SQLITE_CountRows
)!=0 ){
1024 sqlite3VdbeAddOp2(v
, OP_AddImm
, regRowCount
, 1);
1028 /* Code AFTER triggers */
1029 sqlite3CodeRowTrigger(pParse
, pTrigger
, TK_INSERT
, 0, TRIGGER_AFTER
,
1030 pTab
, regData
-2-pTab
->nCol
, onError
, endOfLoop
);
1033 /* The bottom of the main insertion loop, if the data source
1034 ** is a SELECT statement.
1036 sqlite3VdbeResolveLabel(v
, endOfLoop
);
1038 sqlite3VdbeAddOp2(v
, OP_Next
, srcTab
, addrCont
); VdbeCoverage(v
);
1039 sqlite3VdbeJumpHere(v
, addrInsTop
);
1040 sqlite3VdbeAddOp1(v
, OP_Close
, srcTab
);
1041 }else if( pSelect
){
1042 sqlite3VdbeGoto(v
, addrCont
);
1043 sqlite3VdbeJumpHere(v
, addrInsTop
);
1047 /* Update the sqlite_sequence table by storing the content of the
1048 ** maximum rowid counter values recorded while inserting into
1049 ** autoincrement tables.
1051 if( pParse
->nested
==0 && pParse
->pTriggerTab
==0 ){
1052 sqlite3AutoincrementEnd(pParse
);
1056 ** Return the number of rows inserted. If this routine is
1057 ** generating code because of a call to sqlite3NestedParse(), do not
1058 ** invoke the callback function.
1060 if( (db
->flags
&SQLITE_CountRows
) && !pParse
->nested
&& !pParse
->pTriggerTab
){
1061 sqlite3VdbeAddOp2(v
, OP_ResultRow
, regRowCount
, 1);
1062 sqlite3VdbeSetNumCols(v
, 1);
1063 sqlite3VdbeSetColName(v
, 0, COLNAME_NAME
, "rows inserted", SQLITE_STATIC
);
1067 sqlite3SrcListDelete(db
, pTabList
);
1068 sqlite3ExprListDelete(db
, pList
);
1069 sqlite3SelectDelete(db
, pSelect
);
1070 sqlite3IdListDelete(db
, pColumn
);
1071 sqlite3DbFree(db
, aRegIdx
);
1074 /* Make sure "isView" and other macros defined above are undefined. Otherwise
1075 ** they may interfere with compilation of other functions in this file
1076 ** (or in another file, if this file becomes part of the amalgamation). */
1088 ** Meanings of bits in of pWalker->eCode for checkConstraintUnchanged()
1090 #define CKCNSTRNT_COLUMN 0x01 /* CHECK constraint uses a changing column */
1091 #define CKCNSTRNT_ROWID 0x02 /* CHECK constraint references the ROWID */
1093 /* This is the Walker callback from checkConstraintUnchanged(). Set
1094 ** bit 0x01 of pWalker->eCode if
1095 ** pWalker->eCode to 0 if this expression node references any of the
1096 ** columns that are being modifed by an UPDATE statement.
1098 static int checkConstraintExprNode(Walker
*pWalker
, Expr
*pExpr
){
1099 if( pExpr
->op
==TK_COLUMN
){
1100 assert( pExpr
->iColumn
>=0 || pExpr
->iColumn
==-1 );
1101 if( pExpr
->iColumn
>=0 ){
1102 if( pWalker
->u
.aiCol
[pExpr
->iColumn
]>=0 ){
1103 pWalker
->eCode
|= CKCNSTRNT_COLUMN
;
1106 pWalker
->eCode
|= CKCNSTRNT_ROWID
;
1109 return WRC_Continue
;
1113 ** pExpr is a CHECK constraint on a row that is being UPDATE-ed. The
1114 ** only columns that are modified by the UPDATE are those for which
1115 ** aiChng[i]>=0, and also the ROWID is modified if chngRowid is true.
1117 ** Return true if CHECK constraint pExpr does not use any of the
1118 ** changing columns (or the rowid if it is changing). In other words,
1119 ** return true if this CHECK constraint can be skipped when validating
1120 ** the new row in the UPDATE statement.
1122 static int checkConstraintUnchanged(Expr
*pExpr
, int *aiChng
, int chngRowid
){
1124 memset(&w
, 0, sizeof(w
));
1126 w
.xExprCallback
= checkConstraintExprNode
;
1128 sqlite3WalkExpr(&w
, pExpr
);
1130 testcase( (w
.eCode
& CKCNSTRNT_ROWID
)!=0 );
1131 w
.eCode
&= ~CKCNSTRNT_ROWID
;
1133 testcase( w
.eCode
==0 );
1134 testcase( w
.eCode
==CKCNSTRNT_COLUMN
);
1135 testcase( w
.eCode
==CKCNSTRNT_ROWID
);
1136 testcase( w
.eCode
==(CKCNSTRNT_ROWID
|CKCNSTRNT_COLUMN
) );
1141 ** Generate code to do constraint checks prior to an INSERT or an UPDATE
1144 ** The regNewData parameter is the first register in a range that contains
1145 ** the data to be inserted or the data after the update. There will be
1146 ** pTab->nCol+1 registers in this range. The first register (the one
1147 ** that regNewData points to) will contain the new rowid, or NULL in the
1148 ** case of a WITHOUT ROWID table. The second register in the range will
1149 ** contain the content of the first table column. The third register will
1150 ** contain the content of the second table column. And so forth.
1152 ** The regOldData parameter is similar to regNewData except that it contains
1153 ** the data prior to an UPDATE rather than afterwards. regOldData is zero
1154 ** for an INSERT. This routine can distinguish between UPDATE and INSERT by
1155 ** checking regOldData for zero.
1157 ** For an UPDATE, the pkChng boolean is true if the true primary key (the
1158 ** rowid for a normal table or the PRIMARY KEY for a WITHOUT ROWID table)
1159 ** might be modified by the UPDATE. If pkChng is false, then the key of
1160 ** the iDataCur content table is guaranteed to be unchanged by the UPDATE.
1162 ** For an INSERT, the pkChng boolean indicates whether or not the rowid
1163 ** was explicitly specified as part of the INSERT statement. If pkChng
1164 ** is zero, it means that the either rowid is computed automatically or
1165 ** that the table is a WITHOUT ROWID table and has no rowid. On an INSERT,
1166 ** pkChng will only be true if the INSERT statement provides an integer
1167 ** value for either the rowid column or its INTEGER PRIMARY KEY alias.
1169 ** The code generated by this routine will store new index entries into
1170 ** registers identified by aRegIdx[]. No index entry is created for
1171 ** indices where aRegIdx[i]==0. The order of indices in aRegIdx[] is
1172 ** the same as the order of indices on the linked list of indices
1175 ** The caller must have already opened writeable cursors on the main
1176 ** table and all applicable indices (that is to say, all indices for which
1177 ** aRegIdx[] is not zero). iDataCur is the cursor for the main table when
1178 ** inserting or updating a rowid table, or the cursor for the PRIMARY KEY
1179 ** index when operating on a WITHOUT ROWID table. iIdxCur is the cursor
1180 ** for the first index in the pTab->pIndex list. Cursors for other indices
1181 ** are at iIdxCur+N for the N-th element of the pTab->pIndex list.
1183 ** This routine also generates code to check constraints. NOT NULL,
1184 ** CHECK, and UNIQUE constraints are all checked. If a constraint fails,
1185 ** then the appropriate action is performed. There are five possible
1186 ** actions: ROLLBACK, ABORT, FAIL, REPLACE, and IGNORE.
1188 ** Constraint type Action What Happens
1189 ** --------------- ---------- ----------------------------------------
1190 ** any ROLLBACK The current transaction is rolled back and
1191 ** sqlite3_step() returns immediately with a
1192 ** return code of SQLITE_CONSTRAINT.
1194 ** any ABORT Back out changes from the current command
1195 ** only (do not do a complete rollback) then
1196 ** cause sqlite3_step() to return immediately
1197 ** with SQLITE_CONSTRAINT.
1199 ** any FAIL Sqlite3_step() returns immediately with a
1200 ** return code of SQLITE_CONSTRAINT. The
1201 ** transaction is not rolled back and any
1202 ** changes to prior rows are retained.
1204 ** any IGNORE The attempt in insert or update the current
1205 ** row is skipped, without throwing an error.
1206 ** Processing continues with the next row.
1207 ** (There is an immediate jump to ignoreDest.)
1209 ** NOT NULL REPLACE The NULL value is replace by the default
1210 ** value for that column. If the default value
1211 ** is NULL, the action is the same as ABORT.
1213 ** UNIQUE REPLACE The other row that conflicts with the row
1214 ** being inserted is removed.
1216 ** CHECK REPLACE Illegal. The results in an exception.
1218 ** Which action to take is determined by the overrideError parameter.
1219 ** Or if overrideError==OE_Default, then the pParse->onError parameter
1220 ** is used. Or if pParse->onError==OE_Default then the onError value
1221 ** for the constraint is used.
1223 void sqlite3GenerateConstraintChecks(
1224 Parse
*pParse
, /* The parser context */
1225 Table
*pTab
, /* The table being inserted or updated */
1226 int *aRegIdx
, /* Use register aRegIdx[i] for index i. 0 for unused */
1227 int iDataCur
, /* Canonical data cursor (main table or PK index) */
1228 int iIdxCur
, /* First index cursor */
1229 int regNewData
, /* First register in a range holding values to insert */
1230 int regOldData
, /* Previous content. 0 for INSERTs */
1231 u8 pkChng
, /* Non-zero if the rowid or PRIMARY KEY changed */
1232 u8 overrideError
, /* Override onError to this if not OE_Default */
1233 int ignoreDest
, /* Jump to this label on an OE_Ignore resolution */
1234 int *pbMayReplace
, /* OUT: Set to true if constraint may cause a replace */
1235 int *aiChng
/* column i is unchanged if aiChng[i]<0 */
1237 Vdbe
*v
; /* VDBE under constrution */
1238 Index
*pIdx
; /* Pointer to one of the indices */
1239 Index
*pPk
= 0; /* The PRIMARY KEY index */
1240 sqlite3
*db
; /* Database connection */
1241 int i
; /* loop counter */
1242 int ix
; /* Index loop counter */
1243 int nCol
; /* Number of columns */
1244 int onError
; /* Conflict resolution strategy */
1245 int addr1
; /* Address of jump instruction */
1246 int seenReplace
= 0; /* True if REPLACE is used to resolve INT PK conflict */
1247 int nPkField
; /* Number of fields in PRIMARY KEY. 1 for ROWID tables */
1248 int ipkTop
= 0; /* Top of the rowid change constraint check */
1249 int ipkBottom
= 0; /* Bottom of the rowid change constraint check */
1250 u8 isUpdate
; /* True if this is an UPDATE operation */
1251 u8 bAffinityDone
= 0; /* True if the OP_Affinity operation has been run */
1253 isUpdate
= regOldData
!=0;
1255 v
= sqlite3GetVdbe(pParse
);
1257 assert( pTab
->pSelect
==0 ); /* This table is not a VIEW */
1260 /* pPk is the PRIMARY KEY index for WITHOUT ROWID tables and NULL for
1261 ** normal rowid tables. nPkField is the number of key fields in the
1262 ** pPk index or 1 for a rowid table. In other words, nPkField is the
1263 ** number of fields in the true primary key of the table. */
1264 if( HasRowid(pTab
) ){
1268 pPk
= sqlite3PrimaryKeyIndex(pTab
);
1269 nPkField
= pPk
->nKeyCol
;
1272 /* Record that this module has started */
1273 VdbeModuleComment((v
, "BEGIN: GenCnstCks(%d,%d,%d,%d,%d)",
1274 iDataCur
, iIdxCur
, regNewData
, regOldData
, pkChng
));
1276 /* Test all NOT NULL constraints.
1278 for(i
=0; i
<nCol
; i
++){
1279 if( i
==pTab
->iPKey
){
1280 continue; /* ROWID is never NULL */
1282 if( aiChng
&& aiChng
[i
]<0 ){
1283 /* Don't bother checking for NOT NULL on columns that do not change */
1286 onError
= pTab
->aCol
[i
].notNull
;
1287 if( onError
==OE_None
) continue; /* This column is allowed to be NULL */
1288 if( overrideError
!=OE_Default
){
1289 onError
= overrideError
;
1290 }else if( onError
==OE_Default
){
1293 if( onError
==OE_Replace
&& pTab
->aCol
[i
].pDflt
==0 ){
1296 assert( onError
==OE_Rollback
|| onError
==OE_Abort
|| onError
==OE_Fail
1297 || onError
==OE_Ignore
|| onError
==OE_Replace
);
1300 sqlite3MayAbort(pParse
);
1304 char *zMsg
= sqlite3MPrintf(db
, "%s.%s", pTab
->zName
,
1305 pTab
->aCol
[i
].zName
);
1306 sqlite3VdbeAddOp3(v
, OP_HaltIfNull
, SQLITE_CONSTRAINT_NOTNULL
, onError
,
1308 sqlite3VdbeAppendP4(v
, zMsg
, P4_DYNAMIC
);
1309 sqlite3VdbeChangeP5(v
, P5_ConstraintNotNull
);
1314 sqlite3VdbeAddOp2(v
, OP_IsNull
, regNewData
+1+i
, ignoreDest
);
1319 assert( onError
==OE_Replace
);
1320 addr1
= sqlite3VdbeAddOp1(v
, OP_NotNull
, regNewData
+1+i
);
1322 sqlite3ExprCode(pParse
, pTab
->aCol
[i
].pDflt
, regNewData
+1+i
);
1323 sqlite3VdbeJumpHere(v
, addr1
);
1329 /* Test all CHECK constraints
1331 #ifndef SQLITE_OMIT_CHECK
1332 if( pTab
->pCheck
&& (db
->flags
& SQLITE_IgnoreChecks
)==0 ){
1333 ExprList
*pCheck
= pTab
->pCheck
;
1334 pParse
->iSelfTab
= -(regNewData
+1);
1335 onError
= overrideError
!=OE_Default
? overrideError
: OE_Abort
;
1336 for(i
=0; i
<pCheck
->nExpr
; i
++){
1338 Expr
*pExpr
= pCheck
->a
[i
].pExpr
;
1339 if( aiChng
&& checkConstraintUnchanged(pExpr
, aiChng
, pkChng
) ) continue;
1340 allOk
= sqlite3VdbeMakeLabel(v
);
1341 sqlite3ExprIfTrue(pParse
, pExpr
, allOk
, SQLITE_JUMPIFNULL
);
1342 if( onError
==OE_Ignore
){
1343 sqlite3VdbeGoto(v
, ignoreDest
);
1345 char *zName
= pCheck
->a
[i
].zName
;
1346 if( zName
==0 ) zName
= pTab
->zName
;
1347 if( onError
==OE_Replace
) onError
= OE_Abort
; /* IMP: R-15569-63625 */
1348 sqlite3HaltConstraint(pParse
, SQLITE_CONSTRAINT_CHECK
,
1349 onError
, zName
, P4_TRANSIENT
,
1350 P5_ConstraintCheck
);
1352 sqlite3VdbeResolveLabel(v
, allOk
);
1354 pParse
->iSelfTab
= 0;
1356 #endif /* !defined(SQLITE_OMIT_CHECK) */
1358 /* If rowid is changing, make sure the new rowid does not previously
1359 ** exist in the table.
1361 if( pkChng
&& pPk
==0 ){
1362 int addrRowidOk
= sqlite3VdbeMakeLabel(v
);
1364 /* Figure out what action to take in case of a rowid collision */
1365 onError
= pTab
->keyConf
;
1366 if( overrideError
!=OE_Default
){
1367 onError
= overrideError
;
1368 }else if( onError
==OE_Default
){
1373 /* pkChng!=0 does not mean that the rowid has changed, only that
1374 ** it might have changed. Skip the conflict logic below if the rowid
1376 sqlite3VdbeAddOp3(v
, OP_Eq
, regNewData
, addrRowidOk
, regOldData
);
1377 sqlite3VdbeChangeP5(v
, SQLITE_NOTNULL
);
1381 /* If the response to a rowid conflict is REPLACE but the response
1382 ** to some other UNIQUE constraint is FAIL or IGNORE, then we need
1383 ** to defer the running of the rowid conflict checking until after
1384 ** the UNIQUE constraints have run.
1386 if( onError
==OE_Replace
&& overrideError
!=OE_Replace
){
1387 for(pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
){
1388 if( pIdx
->onError
==OE_Ignore
|| pIdx
->onError
==OE_Fail
){
1389 ipkTop
= sqlite3VdbeAddOp0(v
, OP_Goto
);
1395 /* Check to see if the new rowid already exists in the table. Skip
1396 ** the following conflict logic if it does not. */
1397 sqlite3VdbeAddOp3(v
, OP_NotExists
, iDataCur
, addrRowidOk
, regNewData
);
1400 /* Generate code that deals with a rowid collision */
1404 /* Fall thru into the next case */
1409 sqlite3RowidConstraint(pParse
, onError
, pTab
);
1413 /* If there are DELETE triggers on this table and the
1414 ** recursive-triggers flag is set, call GenerateRowDelete() to
1415 ** remove the conflicting row from the table. This will fire
1416 ** the triggers and remove both the table and index b-tree entries.
1418 ** Otherwise, if there are no triggers or the recursive-triggers
1419 ** flag is not set, but the table has one or more indexes, call
1420 ** GenerateRowIndexDelete(). This removes the index b-tree entries
1421 ** only. The table b-tree entry will be replaced by the new entry
1422 ** when it is inserted.
1424 ** If either GenerateRowDelete() or GenerateRowIndexDelete() is called,
1425 ** also invoke MultiWrite() to indicate that this VDBE may require
1426 ** statement rollback (if the statement is aborted after the delete
1427 ** takes place). Earlier versions called sqlite3MultiWrite() regardless,
1428 ** but being more selective here allows statements like:
1430 ** REPLACE INTO t(rowid) VALUES($newrowid)
1432 ** to run without a statement journal if there are no indexes on the
1435 Trigger
*pTrigger
= 0;
1436 if( db
->flags
&SQLITE_RecTriggers
){
1437 pTrigger
= sqlite3TriggersExist(pParse
, pTab
, TK_DELETE
, 0, 0);
1439 if( pTrigger
|| sqlite3FkRequired(pParse
, pTab
, 0, 0) ){
1440 sqlite3MultiWrite(pParse
);
1441 sqlite3GenerateRowDelete(pParse
, pTab
, pTrigger
, iDataCur
, iIdxCur
,
1442 regNewData
, 1, 0, OE_Replace
, 1, -1);
1444 #ifdef SQLITE_ENABLE_PREUPDATE_HOOK
1445 if( HasRowid(pTab
) ){
1446 /* This OP_Delete opcode fires the pre-update-hook only. It does
1447 ** not modify the b-tree. It is more efficient to let the coming
1448 ** OP_Insert replace the existing entry than it is to delete the
1449 ** existing entry and then insert a new one. */
1450 sqlite3VdbeAddOp2(v
, OP_Delete
, iDataCur
, OPFLAG_ISNOOP
);
1451 sqlite3VdbeAppendP4(v
, pTab
, P4_TABLE
);
1453 #endif /* SQLITE_ENABLE_PREUPDATE_HOOK */
1455 sqlite3MultiWrite(pParse
);
1456 sqlite3GenerateRowIndexDelete(pParse
, pTab
, iDataCur
, iIdxCur
,0,-1);
1463 /*assert( seenReplace==0 );*/
1464 sqlite3VdbeGoto(v
, ignoreDest
);
1468 sqlite3VdbeResolveLabel(v
, addrRowidOk
);
1470 ipkBottom
= sqlite3VdbeAddOp0(v
, OP_Goto
);
1471 sqlite3VdbeJumpHere(v
, ipkTop
);
1475 /* Test all UNIQUE constraints by creating entries for each UNIQUE
1476 ** index and making sure that duplicate entries do not already exist.
1477 ** Compute the revised record entries for indices as we go.
1479 ** This loop also handles the case of the PRIMARY KEY index for a
1480 ** WITHOUT ROWID table.
1482 for(ix
=0, pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
, ix
++){
1483 int regIdx
; /* Range of registers hold conent for pIdx */
1484 int regR
; /* Range of registers holding conflicting PK */
1485 int iThisCur
; /* Cursor for this UNIQUE index */
1486 int addrUniqueOk
; /* Jump here if the UNIQUE constraint is satisfied */
1488 if( aRegIdx
[ix
]==0 ) continue; /* Skip indices that do not change */
1489 if( bAffinityDone
==0 ){
1490 sqlite3TableAffinity(v
, pTab
, regNewData
+1);
1493 iThisCur
= iIdxCur
+ix
;
1494 addrUniqueOk
= sqlite3VdbeMakeLabel(v
);
1496 /* Skip partial indices for which the WHERE clause is not true */
1497 if( pIdx
->pPartIdxWhere
){
1498 sqlite3VdbeAddOp2(v
, OP_Null
, 0, aRegIdx
[ix
]);
1499 pParse
->iSelfTab
= -(regNewData
+1);
1500 sqlite3ExprIfFalseDup(pParse
, pIdx
->pPartIdxWhere
, addrUniqueOk
,
1502 pParse
->iSelfTab
= 0;
1505 /* Create a record for this index entry as it should appear after
1506 ** the insert or update. Store that record in the aRegIdx[ix] register
1508 regIdx
= aRegIdx
[ix
]+1;
1509 for(i
=0; i
<pIdx
->nColumn
; i
++){
1510 int iField
= pIdx
->aiColumn
[i
];
1512 if( iField
==XN_EXPR
){
1513 pParse
->iSelfTab
= -(regNewData
+1);
1514 sqlite3ExprCodeCopy(pParse
, pIdx
->aColExpr
->a
[i
].pExpr
, regIdx
+i
);
1515 pParse
->iSelfTab
= 0;
1516 VdbeComment((v
, "%s column %d", pIdx
->zName
, i
));
1518 if( iField
==XN_ROWID
|| iField
==pTab
->iPKey
){
1521 x
= iField
+ regNewData
+ 1;
1523 sqlite3VdbeAddOp2(v
, iField
<0 ? OP_IntCopy
: OP_SCopy
, x
, regIdx
+i
);
1524 VdbeComment((v
, "%s", iField
<0 ? "rowid" : pTab
->aCol
[iField
].zName
));
1527 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regIdx
, pIdx
->nColumn
, aRegIdx
[ix
]);
1528 VdbeComment((v
, "for %s", pIdx
->zName
));
1529 #ifdef SQLITE_ENABLE_NULL_TRIM
1530 if( pIdx
->idxType
==2 ) sqlite3SetMakeRecordP5(v
, pIdx
->pTable
);
1533 /* In an UPDATE operation, if this index is the PRIMARY KEY index
1534 ** of a WITHOUT ROWID table and there has been no change the
1535 ** primary key, then no collision is possible. The collision detection
1536 ** logic below can all be skipped. */
1537 if( isUpdate
&& pPk
==pIdx
&& pkChng
==0 ){
1538 sqlite3VdbeResolveLabel(v
, addrUniqueOk
);
1542 /* Find out what action to take in case there is a uniqueness conflict */
1543 onError
= pIdx
->onError
;
1544 if( onError
==OE_None
){
1545 sqlite3VdbeResolveLabel(v
, addrUniqueOk
);
1546 continue; /* pIdx is not a UNIQUE index */
1548 if( overrideError
!=OE_Default
){
1549 onError
= overrideError
;
1550 }else if( onError
==OE_Default
){
1554 /* Collision detection may be omitted if all of the following are true:
1555 ** (1) The conflict resolution algorithm is REPLACE
1556 ** (2) The table is a WITHOUT ROWID table
1557 ** (3) There are no secondary indexes on the table
1558 ** (4) No delete triggers need to be fired if there is a conflict
1559 ** (5) No FK constraint counters need to be updated if a conflict occurs.
1561 if( (ix
==0 && pIdx
->pNext
==0) /* Condition 3 */
1562 && pPk
==pIdx
/* Condition 2 */
1563 && onError
==OE_Replace
/* Condition 1 */
1564 && ( 0==(db
->flags
&SQLITE_RecTriggers
) || /* Condition 4 */
1565 0==sqlite3TriggersExist(pParse
, pTab
, TK_DELETE
, 0, 0))
1566 && ( 0==(db
->flags
&SQLITE_ForeignKeys
) || /* Condition 5 */
1567 (0==pTab
->pFKey
&& 0==sqlite3FkReferences(pTab
)))
1569 sqlite3VdbeResolveLabel(v
, addrUniqueOk
);
1573 /* Check to see if the new index entry will be unique */
1574 sqlite3ExprCachePush(pParse
);
1575 sqlite3VdbeAddOp4Int(v
, OP_NoConflict
, iThisCur
, addrUniqueOk
,
1576 regIdx
, pIdx
->nKeyCol
); VdbeCoverage(v
);
1578 /* Generate code to handle collisions */
1579 regR
= (pIdx
==pPk
) ? regIdx
: sqlite3GetTempRange(pParse
, nPkField
);
1580 if( isUpdate
|| onError
==OE_Replace
){
1581 if( HasRowid(pTab
) ){
1582 sqlite3VdbeAddOp2(v
, OP_IdxRowid
, iThisCur
, regR
);
1583 /* Conflict only if the rowid of the existing index entry
1584 ** is different from old-rowid */
1586 sqlite3VdbeAddOp3(v
, OP_Eq
, regR
, addrUniqueOk
, regOldData
);
1587 sqlite3VdbeChangeP5(v
, SQLITE_NOTNULL
);
1592 /* Extract the PRIMARY KEY from the end of the index entry and
1593 ** store it in registers regR..regR+nPk-1 */
1595 for(i
=0; i
<pPk
->nKeyCol
; i
++){
1596 assert( pPk
->aiColumn
[i
]>=0 );
1597 x
= sqlite3ColumnOfIndex(pIdx
, pPk
->aiColumn
[i
]);
1598 sqlite3VdbeAddOp3(v
, OP_Column
, iThisCur
, x
, regR
+i
);
1599 VdbeComment((v
, "%s.%s", pTab
->zName
,
1600 pTab
->aCol
[pPk
->aiColumn
[i
]].zName
));
1604 /* If currently processing the PRIMARY KEY of a WITHOUT ROWID
1605 ** table, only conflict if the new PRIMARY KEY values are actually
1606 ** different from the old.
1608 ** For a UNIQUE index, only conflict if the PRIMARY KEY values
1609 ** of the matched index row are different from the original PRIMARY
1610 ** KEY values of this row before the update. */
1611 int addrJump
= sqlite3VdbeCurrentAddr(v
)+pPk
->nKeyCol
;
1613 int regCmp
= (IsPrimaryKeyIndex(pIdx
) ? regIdx
: regR
);
1615 for(i
=0; i
<pPk
->nKeyCol
; i
++){
1616 char *p4
= (char*)sqlite3LocateCollSeq(pParse
, pPk
->azColl
[i
]);
1617 x
= pPk
->aiColumn
[i
];
1619 if( i
==(pPk
->nKeyCol
-1) ){
1620 addrJump
= addrUniqueOk
;
1623 sqlite3VdbeAddOp4(v
, op
,
1624 regOldData
+1+x
, addrJump
, regCmp
+i
, p4
, P4_COLLSEQ
1626 sqlite3VdbeChangeP5(v
, SQLITE_NOTNULL
);
1627 VdbeCoverageIf(v
, op
==OP_Eq
);
1628 VdbeCoverageIf(v
, op
==OP_Ne
);
1634 /* Generate code that executes if the new index entry is not unique */
1635 assert( onError
==OE_Rollback
|| onError
==OE_Abort
|| onError
==OE_Fail
1636 || onError
==OE_Ignore
|| onError
==OE_Replace
);
1641 sqlite3UniqueConstraint(pParse
, onError
, pIdx
);
1645 sqlite3VdbeGoto(v
, ignoreDest
);
1649 Trigger
*pTrigger
= 0;
1650 assert( onError
==OE_Replace
);
1651 sqlite3MultiWrite(pParse
);
1652 if( db
->flags
&SQLITE_RecTriggers
){
1653 pTrigger
= sqlite3TriggersExist(pParse
, pTab
, TK_DELETE
, 0, 0);
1655 sqlite3GenerateRowDelete(pParse
, pTab
, pTrigger
, iDataCur
, iIdxCur
,
1656 regR
, nPkField
, 0, OE_Replace
,
1657 (pIdx
==pPk
? ONEPASS_SINGLE
: ONEPASS_OFF
), iThisCur
);
1662 sqlite3VdbeResolveLabel(v
, addrUniqueOk
);
1663 sqlite3ExprCachePop(pParse
);
1664 if( regR
!=regIdx
) sqlite3ReleaseTempRange(pParse
, regR
, nPkField
);
1667 sqlite3VdbeGoto(v
, ipkTop
+1);
1668 sqlite3VdbeJumpHere(v
, ipkBottom
);
1671 *pbMayReplace
= seenReplace
;
1672 VdbeModuleComment((v
, "END: GenCnstCks(%d)", seenReplace
));
1675 #ifdef SQLITE_ENABLE_NULL_TRIM
1677 ** Change the P5 operand on the last opcode (which should be an OP_MakeRecord)
1678 ** to be the number of columns in table pTab that must not be NULL-trimmed.
1680 ** Or if no columns of pTab may be NULL-trimmed, leave P5 at zero.
1682 void sqlite3SetMakeRecordP5(Vdbe
*v
, Table
*pTab
){
1685 /* Records with omitted columns are only allowed for schema format
1686 ** version 2 and later (SQLite version 3.1.4, 2005-02-20). */
1687 if( pTab
->pSchema
->file_format
<2 ) return;
1689 for(i
=pTab
->nCol
-1; i
>0; i
--){
1690 if( pTab
->aCol
[i
].pDflt
!=0 ) break;
1691 if( pTab
->aCol
[i
].colFlags
& COLFLAG_PRIMKEY
) break;
1693 sqlite3VdbeChangeP5(v
, i
+1);
1698 ** This routine generates code to finish the INSERT or UPDATE operation
1699 ** that was started by a prior call to sqlite3GenerateConstraintChecks.
1700 ** A consecutive range of registers starting at regNewData contains the
1701 ** rowid and the content to be inserted.
1703 ** The arguments to this routine should be the same as the first six
1704 ** arguments to sqlite3GenerateConstraintChecks.
1706 void sqlite3CompleteInsertion(
1707 Parse
*pParse
, /* The parser context */
1708 Table
*pTab
, /* the table into which we are inserting */
1709 int iDataCur
, /* Cursor of the canonical data source */
1710 int iIdxCur
, /* First index cursor */
1711 int regNewData
, /* Range of content */
1712 int *aRegIdx
, /* Register used by each index. 0 for unused indices */
1713 int update_flags
, /* True for UPDATE, False for INSERT */
1714 int appendBias
, /* True if this is likely to be an append */
1715 int useSeekResult
/* True to set the USESEEKRESULT flag on OP_[Idx]Insert */
1717 Vdbe
*v
; /* Prepared statements under construction */
1718 Index
*pIdx
; /* An index being inserted or updated */
1719 u8 pik_flags
; /* flag values passed to the btree insert */
1720 int regData
; /* Content registers (after the rowid) */
1721 int regRec
; /* Register holding assembled record for the table */
1722 int i
; /* Loop counter */
1723 u8 bAffinityDone
= 0; /* True if OP_Affinity has been run already */
1725 assert( update_flags
==0
1726 || update_flags
==OPFLAG_ISUPDATE
1727 || update_flags
==(OPFLAG_ISUPDATE
|OPFLAG_SAVEPOSITION
)
1730 v
= sqlite3GetVdbe(pParse
);
1732 assert( pTab
->pSelect
==0 ); /* This table is not a VIEW */
1733 for(i
=0, pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
, i
++){
1734 if( aRegIdx
[i
]==0 ) continue;
1736 if( pIdx
->pPartIdxWhere
){
1737 sqlite3VdbeAddOp2(v
, OP_IsNull
, aRegIdx
[i
], sqlite3VdbeCurrentAddr(v
)+2);
1740 pik_flags
= (useSeekResult
? OPFLAG_USESEEKRESULT
: 0);
1741 if( IsPrimaryKeyIndex(pIdx
) && !HasRowid(pTab
) ){
1742 assert( pParse
->nested
==0 );
1743 pik_flags
|= OPFLAG_NCHANGE
;
1744 pik_flags
|= (update_flags
& OPFLAG_SAVEPOSITION
);
1745 #ifdef SQLITE_ENABLE_PREUPDATE_HOOK
1746 if( update_flags
==0 ){
1747 sqlite3VdbeAddOp4(v
, OP_InsertInt
,
1748 iIdxCur
+i
, aRegIdx
[i
], 0, (char*)pTab
, P4_TABLE
1750 sqlite3VdbeChangeP5(v
, OPFLAG_ISNOOP
);
1754 sqlite3VdbeAddOp4Int(v
, OP_IdxInsert
, iIdxCur
+i
, aRegIdx
[i
],
1756 pIdx
->uniqNotNull
? pIdx
->nKeyCol
: pIdx
->nColumn
);
1757 sqlite3VdbeChangeP5(v
, pik_flags
);
1759 if( !HasRowid(pTab
) ) return;
1760 regData
= regNewData
+ 1;
1761 regRec
= sqlite3GetTempReg(pParse
);
1762 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regData
, pTab
->nCol
, regRec
);
1763 sqlite3SetMakeRecordP5(v
, pTab
);
1764 if( !bAffinityDone
){
1765 sqlite3TableAffinity(v
, pTab
, 0);
1766 sqlite3ExprCacheAffinityChange(pParse
, regData
, pTab
->nCol
);
1768 if( pParse
->nested
){
1771 pik_flags
= OPFLAG_NCHANGE
;
1772 pik_flags
|= (update_flags
?update_flags
:OPFLAG_LASTROWID
);
1775 pik_flags
|= OPFLAG_APPEND
;
1777 if( useSeekResult
){
1778 pik_flags
|= OPFLAG_USESEEKRESULT
;
1780 sqlite3VdbeAddOp3(v
, OP_Insert
, iDataCur
, regRec
, regNewData
);
1781 if( !pParse
->nested
){
1782 sqlite3VdbeAppendP4(v
, pTab
, P4_TABLE
);
1784 sqlite3VdbeChangeP5(v
, pik_flags
);
1788 ** Allocate cursors for the pTab table and all its indices and generate
1789 ** code to open and initialized those cursors.
1791 ** The cursor for the object that contains the complete data (normally
1792 ** the table itself, but the PRIMARY KEY index in the case of a WITHOUT
1793 ** ROWID table) is returned in *piDataCur. The first index cursor is
1794 ** returned in *piIdxCur. The number of indices is returned.
1796 ** Use iBase as the first cursor (either the *piDataCur for rowid tables
1797 ** or the first index for WITHOUT ROWID tables) if it is non-negative.
1798 ** If iBase is negative, then allocate the next available cursor.
1800 ** For a rowid table, *piDataCur will be exactly one less than *piIdxCur.
1801 ** For a WITHOUT ROWID table, *piDataCur will be somewhere in the range
1802 ** of *piIdxCurs, depending on where the PRIMARY KEY index appears on the
1803 ** pTab->pIndex list.
1805 ** If pTab is a virtual table, then this routine is a no-op and the
1806 ** *piDataCur and *piIdxCur values are left uninitialized.
1808 int sqlite3OpenTableAndIndices(
1809 Parse
*pParse
, /* Parsing context */
1810 Table
*pTab
, /* Table to be opened */
1811 int op
, /* OP_OpenRead or OP_OpenWrite */
1812 u8 p5
, /* P5 value for OP_Open* opcodes (except on WITHOUT ROWID) */
1813 int iBase
, /* Use this for the table cursor, if there is one */
1814 u8
*aToOpen
, /* If not NULL: boolean for each table and index */
1815 int *piDataCur
, /* Write the database source cursor number here */
1816 int *piIdxCur
/* Write the first index cursor number here */
1824 assert( op
==OP_OpenRead
|| op
==OP_OpenWrite
);
1825 assert( op
==OP_OpenWrite
|| p5
==0 );
1826 if( IsVirtual(pTab
) ){
1827 /* This routine is a no-op for virtual tables. Leave the output
1828 ** variables *piDataCur and *piIdxCur uninitialized so that valgrind
1829 ** can detect if they are used by mistake in the caller. */
1832 iDb
= sqlite3SchemaToIndex(pParse
->db
, pTab
->pSchema
);
1833 v
= sqlite3GetVdbe(pParse
);
1835 if( iBase
<0 ) iBase
= pParse
->nTab
;
1837 if( piDataCur
) *piDataCur
= iDataCur
;
1838 if( HasRowid(pTab
) && (aToOpen
==0 || aToOpen
[0]) ){
1839 sqlite3OpenTable(pParse
, iDataCur
, iDb
, pTab
, op
);
1841 sqlite3TableLock(pParse
, iDb
, pTab
->tnum
, op
==OP_OpenWrite
, pTab
->zName
);
1843 if( piIdxCur
) *piIdxCur
= iBase
;
1844 for(i
=0, pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
, i
++){
1845 int iIdxCur
= iBase
++;
1846 assert( pIdx
->pSchema
==pTab
->pSchema
);
1847 if( IsPrimaryKeyIndex(pIdx
) && !HasRowid(pTab
) ){
1848 if( piDataCur
) *piDataCur
= iIdxCur
;
1851 if( aToOpen
==0 || aToOpen
[i
+1] ){
1852 sqlite3VdbeAddOp3(v
, op
, iIdxCur
, pIdx
->tnum
, iDb
);
1853 sqlite3VdbeSetP4KeyInfo(pParse
, pIdx
);
1854 sqlite3VdbeChangeP5(v
, p5
);
1855 VdbeComment((v
, "%s", pIdx
->zName
));
1858 if( iBase
>pParse
->nTab
) pParse
->nTab
= iBase
;
1865 ** The following global variable is incremented whenever the
1866 ** transfer optimization is used. This is used for testing
1867 ** purposes only - to make sure the transfer optimization really
1868 ** is happening when it is supposed to.
1870 int sqlite3_xferopt_count
;
1871 #endif /* SQLITE_TEST */
1874 #ifndef SQLITE_OMIT_XFER_OPT
1876 ** Check to see if index pSrc is compatible as a source of data
1877 ** for index pDest in an insert transfer optimization. The rules
1878 ** for a compatible index:
1880 ** * The index is over the same set of columns
1881 ** * The same DESC and ASC markings occurs on all columns
1882 ** * The same onError processing (OE_Abort, OE_Ignore, etc)
1883 ** * The same collating sequence on each column
1884 ** * The index has the exact same WHERE clause
1886 static int xferCompatibleIndex(Index
*pDest
, Index
*pSrc
){
1888 assert( pDest
&& pSrc
);
1889 assert( pDest
->pTable
!=pSrc
->pTable
);
1890 if( pDest
->nKeyCol
!=pSrc
->nKeyCol
){
1891 return 0; /* Different number of columns */
1893 if( pDest
->onError
!=pSrc
->onError
){
1894 return 0; /* Different conflict resolution strategies */
1896 for(i
=0; i
<pSrc
->nKeyCol
; i
++){
1897 if( pSrc
->aiColumn
[i
]!=pDest
->aiColumn
[i
] ){
1898 return 0; /* Different columns indexed */
1900 if( pSrc
->aiColumn
[i
]==XN_EXPR
){
1901 assert( pSrc
->aColExpr
!=0 && pDest
->aColExpr
!=0 );
1902 if( sqlite3ExprCompare(0, pSrc
->aColExpr
->a
[i
].pExpr
,
1903 pDest
->aColExpr
->a
[i
].pExpr
, -1)!=0 ){
1904 return 0; /* Different expressions in the index */
1907 if( pSrc
->aSortOrder
[i
]!=pDest
->aSortOrder
[i
] ){
1908 return 0; /* Different sort orders */
1910 if( sqlite3_stricmp(pSrc
->azColl
[i
],pDest
->azColl
[i
])!=0 ){
1911 return 0; /* Different collating sequences */
1914 if( sqlite3ExprCompare(0, pSrc
->pPartIdxWhere
, pDest
->pPartIdxWhere
, -1) ){
1915 return 0; /* Different WHERE clauses */
1918 /* If no test above fails then the indices must be compatible */
1923 ** Attempt the transfer optimization on INSERTs of the form
1925 ** INSERT INTO tab1 SELECT * FROM tab2;
1927 ** The xfer optimization transfers raw records from tab2 over to tab1.
1928 ** Columns are not decoded and reassembled, which greatly improves
1929 ** performance. Raw index records are transferred in the same way.
1931 ** The xfer optimization is only attempted if tab1 and tab2 are compatible.
1932 ** There are lots of rules for determining compatibility - see comments
1933 ** embedded in the code for details.
1935 ** This routine returns TRUE if the optimization is guaranteed to be used.
1936 ** Sometimes the xfer optimization will only work if the destination table
1937 ** is empty - a factor that can only be determined at run-time. In that
1938 ** case, this routine generates code for the xfer optimization but also
1939 ** does a test to see if the destination table is empty and jumps over the
1940 ** xfer optimization code if the test fails. In that case, this routine
1941 ** returns FALSE so that the caller will know to go ahead and generate
1942 ** an unoptimized transfer. This routine also returns FALSE if there
1943 ** is no chance that the xfer optimization can be applied.
1945 ** This optimization is particularly useful at making VACUUM run faster.
1947 static int xferOptimization(
1948 Parse
*pParse
, /* Parser context */
1949 Table
*pDest
, /* The table we are inserting into */
1950 Select
*pSelect
, /* A SELECT statement to use as the data source */
1951 int onError
, /* How to handle constraint errors */
1952 int iDbDest
/* The database of pDest */
1954 sqlite3
*db
= pParse
->db
;
1955 ExprList
*pEList
; /* The result set of the SELECT */
1956 Table
*pSrc
; /* The table in the FROM clause of SELECT */
1957 Index
*pSrcIdx
, *pDestIdx
; /* Source and destination indices */
1958 struct SrcList_item
*pItem
; /* An element of pSelect->pSrc */
1959 int i
; /* Loop counter */
1960 int iDbSrc
; /* The database of pSrc */
1961 int iSrc
, iDest
; /* Cursors from source and destination */
1962 int addr1
, addr2
; /* Loop addresses */
1963 int emptyDestTest
= 0; /* Address of test for empty pDest */
1964 int emptySrcTest
= 0; /* Address of test for empty pSrc */
1965 Vdbe
*v
; /* The VDBE we are building */
1966 int regAutoinc
; /* Memory register used by AUTOINC */
1967 int destHasUniqueIdx
= 0; /* True if pDest has a UNIQUE index */
1968 int regData
, regRowid
; /* Registers holding data and rowid */
1971 return 0; /* Must be of the form INSERT INTO ... SELECT ... */
1973 if( pParse
->pWith
|| pSelect
->pWith
){
1974 /* Do not attempt to process this query if there are an WITH clauses
1975 ** attached to it. Proceeding may generate a false "no such table: xxx"
1976 ** error if pSelect reads from a CTE named "xxx". */
1979 if( sqlite3TriggerList(pParse
, pDest
) ){
1980 return 0; /* tab1 must not have triggers */
1982 #ifndef SQLITE_OMIT_VIRTUALTABLE
1983 if( IsVirtual(pDest
) ){
1984 return 0; /* tab1 must not be a virtual table */
1987 if( onError
==OE_Default
){
1988 if( pDest
->iPKey
>=0 ) onError
= pDest
->keyConf
;
1989 if( onError
==OE_Default
) onError
= OE_Abort
;
1991 assert(pSelect
->pSrc
); /* allocated even if there is no FROM clause */
1992 if( pSelect
->pSrc
->nSrc
!=1 ){
1993 return 0; /* FROM clause must have exactly one term */
1995 if( pSelect
->pSrc
->a
[0].pSelect
){
1996 return 0; /* FROM clause cannot contain a subquery */
1998 if( pSelect
->pWhere
){
1999 return 0; /* SELECT may not have a WHERE clause */
2001 if( pSelect
->pOrderBy
){
2002 return 0; /* SELECT may not have an ORDER BY clause */
2004 /* Do not need to test for a HAVING clause. If HAVING is present but
2005 ** there is no ORDER BY, we will get an error. */
2006 if( pSelect
->pGroupBy
){
2007 return 0; /* SELECT may not have a GROUP BY clause */
2009 if( pSelect
->pLimit
){
2010 return 0; /* SELECT may not have a LIMIT clause */
2012 if( pSelect
->pPrior
){
2013 return 0; /* SELECT may not be a compound query */
2015 if( pSelect
->selFlags
& SF_Distinct
){
2016 return 0; /* SELECT may not be DISTINCT */
2018 pEList
= pSelect
->pEList
;
2019 assert( pEList
!=0 );
2020 if( pEList
->nExpr
!=1 ){
2021 return 0; /* The result set must have exactly one column */
2023 assert( pEList
->a
[0].pExpr
);
2024 if( pEList
->a
[0].pExpr
->op
!=TK_ASTERISK
){
2025 return 0; /* The result set must be the special operator "*" */
2028 /* At this point we have established that the statement is of the
2029 ** correct syntactic form to participate in this optimization. Now
2030 ** we have to check the semantics.
2032 pItem
= pSelect
->pSrc
->a
;
2033 pSrc
= sqlite3LocateTableItem(pParse
, 0, pItem
);
2035 return 0; /* FROM clause does not contain a real table */
2038 return 0; /* tab1 and tab2 may not be the same table */
2040 if( HasRowid(pDest
)!=HasRowid(pSrc
) ){
2041 return 0; /* source and destination must both be WITHOUT ROWID or not */
2043 #ifndef SQLITE_OMIT_VIRTUALTABLE
2044 if( IsVirtual(pSrc
) ){
2045 return 0; /* tab2 must not be a virtual table */
2048 if( pSrc
->pSelect
){
2049 return 0; /* tab2 may not be a view */
2051 if( pDest
->nCol
!=pSrc
->nCol
){
2052 return 0; /* Number of columns must be the same in tab1 and tab2 */
2054 if( pDest
->iPKey
!=pSrc
->iPKey
){
2055 return 0; /* Both tables must have the same INTEGER PRIMARY KEY */
2057 for(i
=0; i
<pDest
->nCol
; i
++){
2058 Column
*pDestCol
= &pDest
->aCol
[i
];
2059 Column
*pSrcCol
= &pSrc
->aCol
[i
];
2060 #ifdef SQLITE_ENABLE_HIDDEN_COLUMNS
2061 if( (db
->mDbFlags
& DBFLAG_Vacuum
)==0
2062 && (pDestCol
->colFlags
| pSrcCol
->colFlags
) & COLFLAG_HIDDEN
2064 return 0; /* Neither table may have __hidden__ columns */
2067 if( pDestCol
->affinity
!=pSrcCol
->affinity
){
2068 return 0; /* Affinity must be the same on all columns */
2070 if( sqlite3_stricmp(pDestCol
->zColl
, pSrcCol
->zColl
)!=0 ){
2071 return 0; /* Collating sequence must be the same on all columns */
2073 if( pDestCol
->notNull
&& !pSrcCol
->notNull
){
2074 return 0; /* tab2 must be NOT NULL if tab1 is */
2076 /* Default values for second and subsequent columns need to match. */
2078 assert( pDestCol
->pDflt
==0 || pDestCol
->pDflt
->op
==TK_SPAN
);
2079 assert( pSrcCol
->pDflt
==0 || pSrcCol
->pDflt
->op
==TK_SPAN
);
2080 if( (pDestCol
->pDflt
==0)!=(pSrcCol
->pDflt
==0)
2081 || (pDestCol
->pDflt
&& strcmp(pDestCol
->pDflt
->u
.zToken
,
2082 pSrcCol
->pDflt
->u
.zToken
)!=0)
2084 return 0; /* Default values must be the same for all columns */
2088 for(pDestIdx
=pDest
->pIndex
; pDestIdx
; pDestIdx
=pDestIdx
->pNext
){
2089 if( IsUniqueIndex(pDestIdx
) ){
2090 destHasUniqueIdx
= 1;
2092 for(pSrcIdx
=pSrc
->pIndex
; pSrcIdx
; pSrcIdx
=pSrcIdx
->pNext
){
2093 if( xferCompatibleIndex(pDestIdx
, pSrcIdx
) ) break;
2096 return 0; /* pDestIdx has no corresponding index in pSrc */
2099 #ifndef SQLITE_OMIT_CHECK
2100 if( pDest
->pCheck
&& sqlite3ExprListCompare(pSrc
->pCheck
,pDest
->pCheck
,-1) ){
2101 return 0; /* Tables have different CHECK constraints. Ticket #2252 */
2104 #ifndef SQLITE_OMIT_FOREIGN_KEY
2105 /* Disallow the transfer optimization if the destination table constains
2106 ** any foreign key constraints. This is more restrictive than necessary.
2107 ** But the main beneficiary of the transfer optimization is the VACUUM
2108 ** command, and the VACUUM command disables foreign key constraints. So
2109 ** the extra complication to make this rule less restrictive is probably
2110 ** not worth the effort. Ticket [6284df89debdfa61db8073e062908af0c9b6118e]
2112 if( (db
->flags
& SQLITE_ForeignKeys
)!=0 && pDest
->pFKey
!=0 ){
2116 if( (db
->flags
& SQLITE_CountRows
)!=0 ){
2117 return 0; /* xfer opt does not play well with PRAGMA count_changes */
2120 /* If we get this far, it means that the xfer optimization is at
2121 ** least a possibility, though it might only work if the destination
2122 ** table (tab1) is initially empty.
2125 sqlite3_xferopt_count
++;
2127 iDbSrc
= sqlite3SchemaToIndex(db
, pSrc
->pSchema
);
2128 v
= sqlite3GetVdbe(pParse
);
2129 sqlite3CodeVerifySchema(pParse
, iDbSrc
);
2130 iSrc
= pParse
->nTab
++;
2131 iDest
= pParse
->nTab
++;
2132 regAutoinc
= autoIncBegin(pParse
, iDbDest
, pDest
);
2133 regData
= sqlite3GetTempReg(pParse
);
2134 regRowid
= sqlite3GetTempReg(pParse
);
2135 sqlite3OpenTable(pParse
, iDest
, iDbDest
, pDest
, OP_OpenWrite
);
2136 assert( HasRowid(pDest
) || destHasUniqueIdx
);
2137 if( (db
->mDbFlags
& DBFLAG_Vacuum
)==0 && (
2138 (pDest
->iPKey
<0 && pDest
->pIndex
!=0) /* (1) */
2139 || destHasUniqueIdx
/* (2) */
2140 || (onError
!=OE_Abort
&& onError
!=OE_Rollback
) /* (3) */
2142 /* In some circumstances, we are able to run the xfer optimization
2143 ** only if the destination table is initially empty. Unless the
2144 ** DBFLAG_Vacuum flag is set, this block generates code to make
2145 ** that determination. If DBFLAG_Vacuum is set, then the destination
2146 ** table is always empty.
2148 ** Conditions under which the destination must be empty:
2150 ** (1) There is no INTEGER PRIMARY KEY but there are indices.
2151 ** (If the destination is not initially empty, the rowid fields
2152 ** of index entries might need to change.)
2154 ** (2) The destination has a unique index. (The xfer optimization
2155 ** is unable to test uniqueness.)
2157 ** (3) onError is something other than OE_Abort and OE_Rollback.
2159 addr1
= sqlite3VdbeAddOp2(v
, OP_Rewind
, iDest
, 0); VdbeCoverage(v
);
2160 emptyDestTest
= sqlite3VdbeAddOp0(v
, OP_Goto
);
2161 sqlite3VdbeJumpHere(v
, addr1
);
2163 if( HasRowid(pSrc
) ){
2165 sqlite3OpenTable(pParse
, iSrc
, iDbSrc
, pSrc
, OP_OpenRead
);
2166 emptySrcTest
= sqlite3VdbeAddOp2(v
, OP_Rewind
, iSrc
, 0); VdbeCoverage(v
);
2167 if( pDest
->iPKey
>=0 ){
2168 addr1
= sqlite3VdbeAddOp2(v
, OP_Rowid
, iSrc
, regRowid
);
2169 addr2
= sqlite3VdbeAddOp3(v
, OP_NotExists
, iDest
, 0, regRowid
);
2171 sqlite3RowidConstraint(pParse
, onError
, pDest
);
2172 sqlite3VdbeJumpHere(v
, addr2
);
2173 autoIncStep(pParse
, regAutoinc
, regRowid
);
2174 }else if( pDest
->pIndex
==0 ){
2175 addr1
= sqlite3VdbeAddOp2(v
, OP_NewRowid
, iDest
, regRowid
);
2177 addr1
= sqlite3VdbeAddOp2(v
, OP_Rowid
, iSrc
, regRowid
);
2178 assert( (pDest
->tabFlags
& TF_Autoincrement
)==0 );
2180 sqlite3VdbeAddOp3(v
, OP_RowData
, iSrc
, regData
, 1);
2181 if( db
->mDbFlags
& DBFLAG_Vacuum
){
2182 sqlite3VdbeAddOp1(v
, OP_SeekEnd
, iDest
);
2183 insFlags
= OPFLAG_NCHANGE
|OPFLAG_LASTROWID
|
2184 OPFLAG_APPEND
|OPFLAG_USESEEKRESULT
;
2186 insFlags
= OPFLAG_NCHANGE
|OPFLAG_LASTROWID
|OPFLAG_APPEND
;
2188 sqlite3VdbeAddOp4(v
, OP_Insert
, iDest
, regData
, regRowid
,
2189 (char*)pDest
, P4_TABLE
);
2190 sqlite3VdbeChangeP5(v
, insFlags
);
2191 sqlite3VdbeAddOp2(v
, OP_Next
, iSrc
, addr1
); VdbeCoverage(v
);
2192 sqlite3VdbeAddOp2(v
, OP_Close
, iSrc
, 0);
2193 sqlite3VdbeAddOp2(v
, OP_Close
, iDest
, 0);
2195 sqlite3TableLock(pParse
, iDbDest
, pDest
->tnum
, 1, pDest
->zName
);
2196 sqlite3TableLock(pParse
, iDbSrc
, pSrc
->tnum
, 0, pSrc
->zName
);
2198 for(pDestIdx
=pDest
->pIndex
; pDestIdx
; pDestIdx
=pDestIdx
->pNext
){
2200 for(pSrcIdx
=pSrc
->pIndex
; ALWAYS(pSrcIdx
); pSrcIdx
=pSrcIdx
->pNext
){
2201 if( xferCompatibleIndex(pDestIdx
, pSrcIdx
) ) break;
2204 sqlite3VdbeAddOp3(v
, OP_OpenRead
, iSrc
, pSrcIdx
->tnum
, iDbSrc
);
2205 sqlite3VdbeSetP4KeyInfo(pParse
, pSrcIdx
);
2206 VdbeComment((v
, "%s", pSrcIdx
->zName
));
2207 sqlite3VdbeAddOp3(v
, OP_OpenWrite
, iDest
, pDestIdx
->tnum
, iDbDest
);
2208 sqlite3VdbeSetP4KeyInfo(pParse
, pDestIdx
);
2209 sqlite3VdbeChangeP5(v
, OPFLAG_BULKCSR
);
2210 VdbeComment((v
, "%s", pDestIdx
->zName
));
2211 addr1
= sqlite3VdbeAddOp2(v
, OP_Rewind
, iSrc
, 0); VdbeCoverage(v
);
2212 sqlite3VdbeAddOp3(v
, OP_RowData
, iSrc
, regData
, 1);
2213 if( db
->mDbFlags
& DBFLAG_Vacuum
){
2214 /* This INSERT command is part of a VACUUM operation, which guarantees
2215 ** that the destination table is empty. If all indexed columns use
2216 ** collation sequence BINARY, then it can also be assumed that the
2217 ** index will be populated by inserting keys in strictly sorted
2218 ** order. In this case, instead of seeking within the b-tree as part
2219 ** of every OP_IdxInsert opcode, an OP_SeekEnd is added before the
2220 ** OP_IdxInsert to seek to the point within the b-tree where each key
2221 ** should be inserted. This is faster.
2223 ** If any of the indexed columns use a collation sequence other than
2224 ** BINARY, this optimization is disabled. This is because the user
2225 ** might change the definition of a collation sequence and then run
2226 ** a VACUUM command. In that case keys may not be written in strictly
2228 for(i
=0; i
<pSrcIdx
->nColumn
; i
++){
2229 const char *zColl
= pSrcIdx
->azColl
[i
];
2230 if( sqlite3_stricmp(sqlite3StrBINARY
, zColl
) ) break;
2232 if( i
==pSrcIdx
->nColumn
){
2233 idxInsFlags
= OPFLAG_USESEEKRESULT
;
2234 sqlite3VdbeAddOp1(v
, OP_SeekEnd
, iDest
);
2237 if( !HasRowid(pSrc
) && pDestIdx
->idxType
==2 ){
2238 idxInsFlags
|= OPFLAG_NCHANGE
;
2240 sqlite3VdbeAddOp2(v
, OP_IdxInsert
, iDest
, regData
);
2241 sqlite3VdbeChangeP5(v
, idxInsFlags
|OPFLAG_APPEND
);
2242 sqlite3VdbeAddOp2(v
, OP_Next
, iSrc
, addr1
+1); VdbeCoverage(v
);
2243 sqlite3VdbeJumpHere(v
, addr1
);
2244 sqlite3VdbeAddOp2(v
, OP_Close
, iSrc
, 0);
2245 sqlite3VdbeAddOp2(v
, OP_Close
, iDest
, 0);
2247 if( emptySrcTest
) sqlite3VdbeJumpHere(v
, emptySrcTest
);
2248 sqlite3ReleaseTempReg(pParse
, regRowid
);
2249 sqlite3ReleaseTempReg(pParse
, regData
);
2250 if( emptyDestTest
){
2251 sqlite3AutoincrementEnd(pParse
);
2252 sqlite3VdbeAddOp2(v
, OP_Halt
, SQLITE_OK
, 0);
2253 sqlite3VdbeJumpHere(v
, emptyDestTest
);
2254 sqlite3VdbeAddOp2(v
, OP_Close
, iDest
, 0);
2260 #endif /* SQLITE_OMIT_XFER_OPT */