aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/libraries/sqlite/win32/insert.c
diff options
context:
space:
mode:
Diffstat (limited to 'libraries/sqlite/win32/insert.c')
-rwxr-xr-xlibraries/sqlite/win32/insert.c1605
1 files changed, 1605 insertions, 0 deletions
diff --git a/libraries/sqlite/win32/insert.c b/libraries/sqlite/win32/insert.c
new file mode 100755
index 0000000..9d68c95
--- /dev/null
+++ b/libraries/sqlite/win32/insert.c
@@ -0,0 +1,1605 @@
1/*
2** 2001 September 15
3**
4** The author disclaims copyright to this source code. In place of
5** a legal notice, here is a blessing:
6**
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.
10**
11*************************************************************************
12** This file contains C code routines that are called by the parser
13** to handle INSERT statements in SQLite.
14**
15** $Id: insert.c,v 1.192 2007/09/03 17:30:07 danielk1977 Exp $
16*/
17#include "sqliteInt.h"
18
19/*
20** Set P3 of the most recently inserted opcode to a column affinity
21** string for index pIdx. A column affinity string has one character
22** for each column in the table, according to the affinity of the column:
23**
24** Character Column affinity
25** ------------------------------
26** 'a' TEXT
27** 'b' NONE
28** 'c' NUMERIC
29** 'd' INTEGER
30** 'e' REAL
31*/
32void sqlite3IndexAffinityStr(Vdbe *v, Index *pIdx){
33 if( !pIdx->zColAff ){
34 /* The first time a column affinity string for a particular index is
35 ** required, it is allocated and populated here. It is then stored as
36 ** a member of the Index structure for subsequent use.
37 **
38 ** The column affinity string will eventually be deleted by
39 ** sqliteDeleteIndex() when the Index structure itself is cleaned
40 ** up.
41 */
42 int n;
43 Table *pTab = pIdx->pTable;
44 sqlite3 *db = sqlite3VdbeDb(v);
45 pIdx->zColAff = (char *)sqlite3DbMallocZero(db, pIdx->nColumn+1);
46 if( !pIdx->zColAff ){
47 return;
48 }
49 for(n=0; n<pIdx->nColumn; n++){
50 pIdx->zColAff[n] = pTab->aCol[pIdx->aiColumn[n]].affinity;
51 }
52 pIdx->zColAff[pIdx->nColumn] = '\0';
53 }
54
55 sqlite3VdbeChangeP3(v, -1, pIdx->zColAff, 0);
56}
57
58/*
59** Set P3 of the most recently inserted opcode to a column affinity
60** string for table pTab. A column affinity string has one character
61** for each column indexed by the index, according to the affinity of the
62** column:
63**
64** Character Column affinity
65** ------------------------------
66** 'a' TEXT
67** 'b' NONE
68** 'c' NUMERIC
69** 'd' INTEGER
70** 'e' REAL
71*/
72void sqlite3TableAffinityStr(Vdbe *v, Table *pTab){
73 /* The first time a column affinity string for a particular table
74 ** is required, it is allocated and populated here. It is then
75 ** stored as a member of the Table structure for subsequent use.
76 **
77 ** The column affinity string will eventually be deleted by
78 ** sqlite3DeleteTable() when the Table structure itself is cleaned up.
79 */
80 if( !pTab->zColAff ){
81 char *zColAff;
82 int i;
83 sqlite3 *db = sqlite3VdbeDb(v);
84
85 zColAff = (char *)sqlite3DbMallocZero(db, pTab->nCol+1);
86 if( !zColAff ){
87 return;
88 }
89
90 for(i=0; i<pTab->nCol; i++){
91 zColAff[i] = pTab->aCol[i].affinity;
92 }
93 zColAff[pTab->nCol] = '\0';
94
95 pTab->zColAff = zColAff;
96 }
97
98 sqlite3VdbeChangeP3(v, -1, pTab->zColAff, 0);
99}
100
101/*
102** Return non-zero if SELECT statement p opens the table with rootpage
103** iTab in database iDb. This is used to see if a statement of the form
104** "INSERT INTO <iDb, iTab> SELECT ..." can run without using temporary
105** table for the results of the SELECT.
106**
107** No checking is done for sub-selects that are part of expressions.
108*/
109static int selectReadsTable(Select *p, Schema *pSchema, int iTab){
110 int i;
111 struct SrcList_item *pItem;
112 if( p->pSrc==0 ) return 0;
113 for(i=0, pItem=p->pSrc->a; i<p->pSrc->nSrc; i++, pItem++){
114 if( pItem->pSelect ){
115 if( selectReadsTable(pItem->pSelect, pSchema, iTab) ) return 1;
116 }else{
117 if( pItem->pTab->pSchema==pSchema && pItem->pTab->tnum==iTab ) return 1;
118 }
119 }
120 return 0;
121}
122
123#ifndef SQLITE_OMIT_AUTOINCREMENT
124/*
125** Write out code to initialize the autoincrement logic. This code
126** looks up the current autoincrement value in the sqlite_sequence
127** table and stores that value in a memory cell. Code generated by
128** autoIncStep() will keep that memory cell holding the largest
129** rowid value. Code generated by autoIncEnd() will write the new
130** largest value of the counter back into the sqlite_sequence table.
131**
132** This routine returns the index of the mem[] cell that contains
133** the maximum rowid counter.
134**
135** Two memory cells are allocated. The next memory cell after the
136** one returned holds the rowid in sqlite_sequence where we will
137** write back the revised maximum rowid.
138*/
139static int autoIncBegin(
140 Parse *pParse, /* Parsing context */
141 int iDb, /* Index of the database holding pTab */
142 Table *pTab /* The table we are writing to */
143){
144 int memId = 0;
145 if( pTab->autoInc ){
146 Vdbe *v = pParse->pVdbe;
147 Db *pDb = &pParse->db->aDb[iDb];
148 int iCur = pParse->nTab;
149 int addr;
150 assert( v );
151 addr = sqlite3VdbeCurrentAddr(v);
152 memId = pParse->nMem+1;
153 pParse->nMem += 2;
154 sqlite3OpenTable(pParse, iCur, iDb, pDb->pSchema->pSeqTab, OP_OpenRead);
155 sqlite3VdbeAddOp(v, OP_Rewind, iCur, addr+13);
156 sqlite3VdbeAddOp(v, OP_Column, iCur, 0);
157 sqlite3VdbeOp3(v, OP_String8, 0, 0, pTab->zName, 0);
158 sqlite3VdbeAddOp(v, OP_Ne, 0x100, addr+12);
159 sqlite3VdbeAddOp(v, OP_Rowid, iCur, 0);
160 sqlite3VdbeAddOp(v, OP_MemStore, memId-1, 1);
161 sqlite3VdbeAddOp(v, OP_Column, iCur, 1);
162 sqlite3VdbeAddOp(v, OP_MemStore, memId, 1);
163 sqlite3VdbeAddOp(v, OP_Goto, 0, addr+13);
164 sqlite3VdbeAddOp(v, OP_Next, iCur, addr+4);
165 sqlite3VdbeAddOp(v, OP_Close, iCur, 0);
166 }
167 return memId;
168}
169
170/*
171** Update the maximum rowid for an autoincrement calculation.
172**
173** This routine should be called when the top of the stack holds a
174** new rowid that is about to be inserted. If that new rowid is
175** larger than the maximum rowid in the memId memory cell, then the
176** memory cell is updated. The stack is unchanged.
177*/
178static void autoIncStep(Parse *pParse, int memId){
179 if( memId>0 ){
180 sqlite3VdbeAddOp(pParse->pVdbe, OP_MemMax, memId, 0);
181 }
182}
183
184/*
185** After doing one or more inserts, the maximum rowid is stored
186** in mem[memId]. Generate code to write this value back into the
187** the sqlite_sequence table.
188*/
189static void autoIncEnd(
190 Parse *pParse, /* The parsing context */
191 int iDb, /* Index of the database holding pTab */
192 Table *pTab, /* Table we are inserting into */
193 int memId /* Memory cell holding the maximum rowid */
194){
195 if( pTab->autoInc ){
196 int iCur = pParse->nTab;
197 Vdbe *v = pParse->pVdbe;
198 Db *pDb = &pParse->db->aDb[iDb];
199 int addr;
200 assert( v );
201 addr = sqlite3VdbeCurrentAddr(v);
202 sqlite3OpenTable(pParse, iCur, iDb, pDb->pSchema->pSeqTab, OP_OpenWrite);
203 sqlite3VdbeAddOp(v, OP_MemLoad, memId-1, 0);
204 sqlite3VdbeAddOp(v, OP_NotNull, -1, addr+7);
205 sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
206 sqlite3VdbeAddOp(v, OP_NewRowid, iCur, 0);
207 sqlite3VdbeOp3(v, OP_String8, 0, 0, pTab->zName, 0);
208 sqlite3VdbeAddOp(v, OP_MemLoad, memId, 0);
209 sqlite3VdbeAddOp(v, OP_MakeRecord, 2, 0);
210 sqlite3VdbeAddOp(v, OP_Insert, iCur, OPFLAG_APPEND);
211 sqlite3VdbeAddOp(v, OP_Close, iCur, 0);
212 }
213}
214#else
215/*
216** If SQLITE_OMIT_AUTOINCREMENT is defined, then the three routines
217** above are all no-ops
218*/
219# define autoIncBegin(A,B,C) (0)
220# define autoIncStep(A,B)
221# define autoIncEnd(A,B,C,D)
222#endif /* SQLITE_OMIT_AUTOINCREMENT */
223
224
225/* Forward declaration */
226static int xferOptimization(
227 Parse *pParse, /* Parser context */
228 Table *pDest, /* The table we are inserting into */
229 Select *pSelect, /* A SELECT statement to use as the data source */
230 int onError, /* How to handle constraint errors */
231 int iDbDest /* The database of pDest */
232);
233
234/*
235** This routine is call to handle SQL of the following forms:
236**
237** insert into TABLE (IDLIST) values(EXPRLIST)
238** insert into TABLE (IDLIST) select
239**
240** The IDLIST following the table name is always optional. If omitted,
241** then a list of all columns for the table is substituted. The IDLIST
242** appears in the pColumn parameter. pColumn is NULL if IDLIST is omitted.
243**
244** The pList parameter holds EXPRLIST in the first form of the INSERT
245** statement above, and pSelect is NULL. For the second form, pList is
246** NULL and pSelect is a pointer to the select statement used to generate
247** data for the insert.
248**
249** The code generated follows one of four templates. For a simple
250** select with data coming from a VALUES clause, the code executes
251** once straight down through. The template looks like this:
252**
253** open write cursor to <table> and its indices
254** puts VALUES clause expressions onto the stack
255** write the resulting record into <table>
256** cleanup
257**
258** The three remaining templates assume the statement is of the form
259**
260** INSERT INTO <table> SELECT ...
261**
262** If the SELECT clause is of the restricted form "SELECT * FROM <table2>" -
263** in other words if the SELECT pulls all columns from a single table
264** and there is no WHERE or LIMIT or GROUP BY or ORDER BY clauses, and
265** if <table2> and <table1> are distinct tables but have identical
266** schemas, including all the same indices, then a special optimization
267** is invoked that copies raw records from <table2> over to <table1>.
268** See the xferOptimization() function for the implementation of this
269** template. This is the second template.
270**
271** open a write cursor to <table>
272** open read cursor on <table2>
273** transfer all records in <table2> over to <table>
274** close cursors
275** foreach index on <table>
276** open a write cursor on the <table> index
277** open a read cursor on the corresponding <table2> index
278** transfer all records from the read to the write cursors
279** close cursors
280** end foreach
281**
282** The third template is for when the second template does not apply
283** and the SELECT clause does not read from <table> at any time.
284** The generated code follows this template:
285**
286** goto B
287** A: setup for the SELECT
288** loop over the rows in the SELECT
289** gosub C
290** end loop
291** cleanup after the SELECT
292** goto D
293** B: open write cursor to <table> and its indices
294** goto A
295** C: insert the select result into <table>
296** return
297** D: cleanup
298**
299** The fourth template is used if the insert statement takes its
300** values from a SELECT but the data is being inserted into a table
301** that is also read as part of the SELECT. In the third form,
302** we have to use a intermediate table to store the results of
303** the select. The template is like this:
304**
305** goto B
306** A: setup for the SELECT
307** loop over the tables in the SELECT
308** gosub C
309** end loop
310** cleanup after the SELECT
311** goto D
312** C: insert the select result into the intermediate table
313** return
314** B: open a cursor to an intermediate table
315** goto A
316** D: open write cursor to <table> and its indices
317** loop over the intermediate table
318** transfer values form intermediate table into <table>
319** end the loop
320** cleanup
321*/
322void sqlite3Insert(
323 Parse *pParse, /* Parser context */
324 SrcList *pTabList, /* Name of table into which we are inserting */
325 ExprList *pList, /* List of values to be inserted */
326 Select *pSelect, /* A SELECT statement to use as the data source */
327 IdList *pColumn, /* Column names corresponding to IDLIST. */
328 int onError /* How to handle constraint errors */
329){
330 Table *pTab; /* The table to insert into */
331 char *zTab; /* Name of the table into which we are inserting */
332 const char *zDb; /* Name of the database holding this table */
333 int i, j, idx; /* Loop counters */
334 Vdbe *v; /* Generate code into this virtual machine */
335 Index *pIdx; /* For looping over indices of the table */
336 int nColumn; /* Number of columns in the data */
337 int base = 0; /* VDBE Cursor number for pTab */
338 int iCont=0,iBreak=0; /* Beginning and end of the loop over srcTab */
339 sqlite3 *db; /* The main database structure */
340 int keyColumn = -1; /* Column that is the INTEGER PRIMARY KEY */
341 int endOfLoop; /* Label for the end of the insertion loop */
342 int useTempTable = 0; /* Store SELECT results in intermediate table */
343 int srcTab = 0; /* Data comes from this temporary cursor if >=0 */
344 int iSelectLoop = 0; /* Address of code that implements the SELECT */
345 int iCleanup = 0; /* Address of the cleanup code */
346 int iInsertBlock = 0; /* Address of the subroutine used to insert data */
347 int iCntMem = 0; /* Memory cell used for the row counter */
348 int newIdx = -1; /* Cursor for the NEW table */
349 Db *pDb; /* The database containing table being inserted into */
350 int counterMem = 0; /* Memory cell holding AUTOINCREMENT counter */
351 int appendFlag = 0; /* True if the insert is likely to be an append */
352 int iDb;
353
354 int nHidden = 0;
355
356#ifndef SQLITE_OMIT_TRIGGER
357 int isView; /* True if attempting to insert into a view */
358 int triggers_exist = 0; /* True if there are FOR EACH ROW triggers */
359#endif
360
361 db = pParse->db;
362 if( pParse->nErr || db->mallocFailed ){
363 goto insert_cleanup;
364 }
365
366 /* Locate the table into which we will be inserting new information.
367 */
368 assert( pTabList->nSrc==1 );
369 zTab = pTabList->a[0].zName;
370 if( zTab==0 ) goto insert_cleanup;
371 pTab = sqlite3SrcListLookup(pParse, pTabList);
372 if( pTab==0 ){
373 goto insert_cleanup;
374 }
375 iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
376 assert( iDb<db->nDb );
377 pDb = &db->aDb[iDb];
378 zDb = pDb->zName;
379 if( sqlite3AuthCheck(pParse, SQLITE_INSERT, pTab->zName, 0, zDb) ){
380 goto insert_cleanup;
381 }
382
383 /* Figure out if we have any triggers and if the table being
384 ** inserted into is a view
385 */
386#ifndef SQLITE_OMIT_TRIGGER
387 triggers_exist = sqlite3TriggersExist(pParse, pTab, TK_INSERT, 0);
388 isView = pTab->pSelect!=0;
389#else
390# define triggers_exist 0
391# define isView 0
392#endif
393#ifdef SQLITE_OMIT_VIEW
394# undef isView
395# define isView 0
396#endif
397
398 /* Ensure that:
399 * (a) the table is not read-only,
400 * (b) that if it is a view then ON INSERT triggers exist
401 */
402 if( sqlite3IsReadOnly(pParse, pTab, triggers_exist) ){
403 goto insert_cleanup;
404 }
405 assert( pTab!=0 );
406
407 /* If pTab is really a view, make sure it has been initialized.
408 ** ViewGetColumnNames() is a no-op if pTab is not a view (or virtual
409 ** module table).
410 */
411 if( sqlite3ViewGetColumnNames(pParse, pTab) ){
412 goto insert_cleanup;
413 }
414
415 /* Allocate a VDBE
416 */
417 v = sqlite3GetVdbe(pParse);
418 if( v==0 ) goto insert_cleanup;
419 if( pParse->nested==0 ) sqlite3VdbeCountChanges(v);
420 sqlite3BeginWriteOperation(pParse, pSelect || triggers_exist, iDb);
421
422 /* if there are row triggers, allocate a temp table for new.* references. */
423 if( triggers_exist ){
424 newIdx = pParse->nTab++;
425 }
426
427#ifndef SQLITE_OMIT_XFER_OPT
428 /* If the statement is of the form
429 **
430 ** INSERT INTO <table1> SELECT * FROM <table2>;
431 **
432 ** Then special optimizations can be applied that make the transfer
433 ** very fast and which reduce fragmentation of indices.
434 */
435 if( pColumn==0 && xferOptimization(pParse, pTab, pSelect, onError, iDb) ){
436 assert( !triggers_exist );
437 assert( pList==0 );
438 goto insert_cleanup;
439 }
440#endif /* SQLITE_OMIT_XFER_OPT */
441
442 /* If this is an AUTOINCREMENT table, look up the sequence number in the
443 ** sqlite_sequence table and store it in memory cell counterMem. Also
444 ** remember the rowid of the sqlite_sequence table entry in memory cell
445 ** counterRowid.
446 */
447 counterMem = autoIncBegin(pParse, iDb, pTab);
448
449 /* Figure out how many columns of data are supplied. If the data
450 ** is coming from a SELECT statement, then this step also generates
451 ** all the code to implement the SELECT statement and invoke a subroutine
452 ** to process each row of the result. (Template 2.) If the SELECT
453 ** statement uses the the table that is being inserted into, then the
454 ** subroutine is also coded here. That subroutine stores the SELECT
455 ** results in a temporary table. (Template 3.)
456 */
457 if( pSelect ){
458 /* Data is coming from a SELECT. Generate code to implement that SELECT
459 */
460 int rc, iInitCode;
461 iInitCode = sqlite3VdbeAddOp(v, OP_Goto, 0, 0);
462 iSelectLoop = sqlite3VdbeCurrentAddr(v);
463 iInsertBlock = sqlite3VdbeMakeLabel(v);
464
465 /* Resolve the expressions in the SELECT statement and execute it. */
466 rc = sqlite3Select(pParse, pSelect, SRT_Subroutine, iInsertBlock,0,0,0,0);
467 if( rc || pParse->nErr || db->mallocFailed ){
468 goto insert_cleanup;
469 }
470
471 iCleanup = sqlite3VdbeMakeLabel(v);
472 sqlite3VdbeAddOp(v, OP_Goto, 0, iCleanup);
473 assert( pSelect->pEList );
474 nColumn = pSelect->pEList->nExpr;
475
476 /* Set useTempTable to TRUE if the result of the SELECT statement
477 ** should be written into a temporary table. Set to FALSE if each
478 ** row of the SELECT can be written directly into the result table.
479 **
480 ** A temp table must be used if the table being updated is also one
481 ** of the tables being read by the SELECT statement. Also use a
482 ** temp table in the case of row triggers.
483 */
484 if( triggers_exist || selectReadsTable(pSelect,pTab->pSchema,pTab->tnum) ){
485 useTempTable = 1;
486 }
487
488 if( useTempTable ){
489 /* Generate the subroutine that SELECT calls to process each row of
490 ** the result. Store the result in a temporary table
491 */
492 srcTab = pParse->nTab++;
493 sqlite3VdbeResolveLabel(v, iInsertBlock);
494 sqlite3VdbeAddOp(v, OP_MakeRecord, nColumn, 0);
495 sqlite3VdbeAddOp(v, OP_NewRowid, srcTab, 0);
496 sqlite3VdbeAddOp(v, OP_Pull, 1, 0);
497 sqlite3VdbeAddOp(v, OP_Insert, srcTab, OPFLAG_APPEND);
498 sqlite3VdbeAddOp(v, OP_Return, 0, 0);
499
500 /* The following code runs first because the GOTO at the very top
501 ** of the program jumps to it. Create the temporary table, then jump
502 ** back up and execute the SELECT code above.
503 */
504 sqlite3VdbeJumpHere(v, iInitCode);
505 sqlite3VdbeAddOp(v, OP_OpenEphemeral, srcTab, 0);
506 sqlite3VdbeAddOp(v, OP_SetNumColumns, srcTab, nColumn);
507 sqlite3VdbeAddOp(v, OP_Goto, 0, iSelectLoop);
508 sqlite3VdbeResolveLabel(v, iCleanup);
509 }else{
510 sqlite3VdbeJumpHere(v, iInitCode);
511 }
512 }else{
513 /* This is the case if the data for the INSERT is coming from a VALUES
514 ** clause
515 */
516 NameContext sNC;
517 memset(&sNC, 0, sizeof(sNC));
518 sNC.pParse = pParse;
519 srcTab = -1;
520 useTempTable = 0;
521 nColumn = pList ? pList->nExpr : 0;
522 for(i=0; i<nColumn; i++){
523 if( sqlite3ExprResolveNames(&sNC, pList->a[i].pExpr) ){
524 goto insert_cleanup;
525 }
526 }
527 }
528
529 /* Make sure the number of columns in the source data matches the number
530 ** of columns to be inserted into the table.
531 */
532 if( IsVirtual(pTab) ){
533 for(i=0; i<pTab->nCol; i++){
534 nHidden += (IsHiddenColumn(&pTab->aCol[i]) ? 1 : 0);
535 }
536 }
537 if( pColumn==0 && nColumn && nColumn!=(pTab->nCol-nHidden) ){
538 sqlite3ErrorMsg(pParse,
539 "table %S has %d columns but %d values were supplied",
540 pTabList, 0, pTab->nCol, nColumn);
541 goto insert_cleanup;
542 }
543 if( pColumn!=0 && nColumn!=pColumn->nId ){
544 sqlite3ErrorMsg(pParse, "%d values for %d columns", nColumn, pColumn->nId);
545 goto insert_cleanup;
546 }
547
548 /* If the INSERT statement included an IDLIST term, then make sure
549 ** all elements of the IDLIST really are columns of the table and
550 ** remember the column indices.
551 **
552 ** If the table has an INTEGER PRIMARY KEY column and that column
553 ** is named in the IDLIST, then record in the keyColumn variable
554 ** the index into IDLIST of the primary key column. keyColumn is
555 ** the index of the primary key as it appears in IDLIST, not as
556 ** is appears in the original table. (The index of the primary
557 ** key in the original table is pTab->iPKey.)
558 */
559 if( pColumn ){
560 for(i=0; i<pColumn->nId; i++){
561 pColumn->a[i].idx = -1;
562 }
563 for(i=0; i<pColumn->nId; i++){
564 for(j=0; j<pTab->nCol; j++){
565 if( sqlite3StrICmp(pColumn->a[i].zName, pTab->aCol[j].zName)==0 ){
566 pColumn->a[i].idx = j;
567 if( j==pTab->iPKey ){
568 keyColumn = i;
569 }
570 break;
571 }
572 }
573 if( j>=pTab->nCol ){
574 if( sqlite3IsRowid(pColumn->a[i].zName) ){
575 keyColumn = i;
576 }else{
577 sqlite3ErrorMsg(pParse, "table %S has no column named %s",
578 pTabList, 0, pColumn->a[i].zName);
579 pParse->nErr++;
580 goto insert_cleanup;
581 }
582 }
583 }
584 }
585
586 /* If there is no IDLIST term but the table has an integer primary
587 ** key, the set the keyColumn variable to the primary key column index
588 ** in the original table definition.
589 */
590 if( pColumn==0 && nColumn>0 ){
591 keyColumn = pTab->iPKey;
592 }
593
594 /* Open the temp table for FOR EACH ROW triggers
595 */
596 if( triggers_exist ){
597 sqlite3VdbeAddOp(v, OP_OpenPseudo, newIdx, 0);
598 sqlite3VdbeAddOp(v, OP_SetNumColumns, newIdx, pTab->nCol);
599 }
600
601 /* Initialize the count of rows to be inserted
602 */
603 if( db->flags & SQLITE_CountRows ){
604 iCntMem = pParse->nMem++;
605 sqlite3VdbeAddOp(v, OP_MemInt, 0, iCntMem);
606 }
607
608 /* Open tables and indices if there are no row triggers */
609 if( !triggers_exist ){
610 base = pParse->nTab;
611 sqlite3OpenTableAndIndices(pParse, pTab, base, OP_OpenWrite);
612 }
613
614 /* If the data source is a temporary table, then we have to create
615 ** a loop because there might be multiple rows of data. If the data
616 ** source is a subroutine call from the SELECT statement, then we need
617 ** to launch the SELECT statement processing.
618 */
619 if( useTempTable ){
620 iBreak = sqlite3VdbeMakeLabel(v);
621 sqlite3VdbeAddOp(v, OP_Rewind, srcTab, iBreak);
622 iCont = sqlite3VdbeCurrentAddr(v);
623 }else if( pSelect ){
624 sqlite3VdbeAddOp(v, OP_Goto, 0, iSelectLoop);
625 sqlite3VdbeResolveLabel(v, iInsertBlock);
626 }
627
628 /* Run the BEFORE and INSTEAD OF triggers, if there are any
629 */
630 endOfLoop = sqlite3VdbeMakeLabel(v);
631 if( triggers_exist & TRIGGER_BEFORE ){
632
633 /* build the NEW.* reference row. Note that if there is an INTEGER
634 ** PRIMARY KEY into which a NULL is being inserted, that NULL will be
635 ** translated into a unique ID for the row. But on a BEFORE trigger,
636 ** we do not know what the unique ID will be (because the insert has
637 ** not happened yet) so we substitute a rowid of -1
638 */
639 if( keyColumn<0 ){
640 sqlite3VdbeAddOp(v, OP_Integer, -1, 0);
641 }else if( useTempTable ){
642 sqlite3VdbeAddOp(v, OP_Column, srcTab, keyColumn);
643 }else{
644 assert( pSelect==0 ); /* Otherwise useTempTable is true */
645 sqlite3ExprCode(pParse, pList->a[keyColumn].pExpr);
646 sqlite3VdbeAddOp(v, OP_NotNull, -1, sqlite3VdbeCurrentAddr(v)+3);
647 sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
648 sqlite3VdbeAddOp(v, OP_Integer, -1, 0);
649 sqlite3VdbeAddOp(v, OP_MustBeInt, 0, 0);
650 }
651
652 /* Cannot have triggers on a virtual table. If it were possible,
653 ** this block would have to account for hidden column.
654 */
655 assert(!IsVirtual(pTab));
656
657 /* Create the new column data
658 */
659 for(i=0; i<pTab->nCol; i++){
660 if( pColumn==0 ){
661 j = i;
662 }else{
663 for(j=0; j<pColumn->nId; j++){
664 if( pColumn->a[j].idx==i ) break;
665 }
666 }
667 if( pColumn && j>=pColumn->nId ){
668 sqlite3ExprCode(pParse, pTab->aCol[i].pDflt);
669 }else if( useTempTable ){
670 sqlite3VdbeAddOp(v, OP_Column, srcTab, j);
671 }else{
672 assert( pSelect==0 ); /* Otherwise useTempTable is true */
673 sqlite3ExprCodeAndCache(pParse, pList->a[j].pExpr);
674 }
675 }
676 sqlite3VdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
677
678 /* If this is an INSERT on a view with an INSTEAD OF INSERT trigger,
679 ** do not attempt any conversions before assembling the record.
680 ** If this is a real table, attempt conversions as required by the
681 ** table column affinities.
682 */
683 if( !isView ){
684 sqlite3TableAffinityStr(v, pTab);
685 }
686 sqlite3VdbeAddOp(v, OP_Insert, newIdx, 0);
687
688 /* Fire BEFORE or INSTEAD OF triggers */
689 if( sqlite3CodeRowTrigger(pParse, TK_INSERT, 0, TRIGGER_BEFORE, pTab,
690 newIdx, -1, onError, endOfLoop) ){
691 goto insert_cleanup;
692 }
693 }
694
695 /* If any triggers exists, the opening of tables and indices is deferred
696 ** until now.
697 */
698 if( triggers_exist && !isView ){
699 base = pParse->nTab;
700 sqlite3OpenTableAndIndices(pParse, pTab, base, OP_OpenWrite);
701 }
702
703 /* Push the record number for the new entry onto the stack. The
704 ** record number is a randomly generate integer created by NewRowid
705 ** except when the table has an INTEGER PRIMARY KEY column, in which
706 ** case the record number is the same as that column.
707 */
708 if( !isView ){
709 if( IsVirtual(pTab) ){
710 /* The row that the VUpdate opcode will delete: none */
711 sqlite3VdbeAddOp(v, OP_Null, 0, 0);
712 }
713 if( keyColumn>=0 ){
714 if( useTempTable ){
715 sqlite3VdbeAddOp(v, OP_Column, srcTab, keyColumn);
716 }else if( pSelect ){
717 sqlite3VdbeAddOp(v, OP_Dup, nColumn - keyColumn - 1, 1);
718 }else{
719 VdbeOp *pOp;
720 sqlite3ExprCode(pParse, pList->a[keyColumn].pExpr);
721 pOp = sqlite3VdbeGetOp(v, sqlite3VdbeCurrentAddr(v) - 1);
722 if( pOp && pOp->opcode==OP_Null ){
723 appendFlag = 1;
724 pOp->opcode = OP_NewRowid;
725 pOp->p1 = base;
726 pOp->p2 = counterMem;
727 }
728 }
729 /* If the PRIMARY KEY expression is NULL, then use OP_NewRowid
730 ** to generate a unique primary key value.
731 */
732 if( !appendFlag ){
733 sqlite3VdbeAddOp(v, OP_NotNull, -1, sqlite3VdbeCurrentAddr(v)+3);
734 sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
735 sqlite3VdbeAddOp(v, OP_NewRowid, base, counterMem);
736 sqlite3VdbeAddOp(v, OP_MustBeInt, 0, 0);
737 }
738 }else if( IsVirtual(pTab) ){
739 sqlite3VdbeAddOp(v, OP_Null, 0, 0);
740 }else{
741 sqlite3VdbeAddOp(v, OP_NewRowid, base, counterMem);
742 appendFlag = 1;
743 }
744 autoIncStep(pParse, counterMem);
745
746 /* Push onto the stack, data for all columns of the new entry, beginning
747 ** with the first column.
748 */
749 nHidden = 0;
750 for(i=0; i<pTab->nCol; i++){
751 if( i==pTab->iPKey ){
752 /* The value of the INTEGER PRIMARY KEY column is always a NULL.
753 ** Whenever this column is read, the record number will be substituted
754 ** in its place. So will fill this column with a NULL to avoid
755 ** taking up data space with information that will never be used. */
756 sqlite3VdbeAddOp(v, OP_Null, 0, 0);
757 continue;
758 }
759 if( pColumn==0 ){
760 if( IsHiddenColumn(&pTab->aCol[i]) ){
761 assert( IsVirtual(pTab) );
762 j = -1;
763 nHidden++;
764 }else{
765 j = i - nHidden;
766 }
767 }else{
768 for(j=0; j<pColumn->nId; j++){
769 if( pColumn->a[j].idx==i ) break;
770 }
771 }
772 if( j<0 || nColumn==0 || (pColumn && j>=pColumn->nId) ){
773 sqlite3ExprCode(pParse, pTab->aCol[i].pDflt);
774 }else if( useTempTable ){
775 sqlite3VdbeAddOp(v, OP_Column, srcTab, j);
776 }else if( pSelect ){
777 sqlite3VdbeAddOp(v, OP_Dup, i+nColumn-j+IsVirtual(pTab), 1);
778 }else{
779 sqlite3ExprCode(pParse, pList->a[j].pExpr);
780 }
781 }
782
783 /* Generate code to check constraints and generate index keys and
784 ** do the insertion.
785 */
786#ifndef SQLITE_OMIT_VIRTUALTABLE
787 if( IsVirtual(pTab) ){
788 pParse->pVirtualLock = pTab;
789 sqlite3VdbeOp3(v, OP_VUpdate, 1, pTab->nCol+2,
790 (const char*)pTab->pVtab, P3_VTAB);
791 }else
792#endif
793 {
794 sqlite3GenerateConstraintChecks(pParse, pTab, base, 0, keyColumn>=0,
795 0, onError, endOfLoop);
796 sqlite3CompleteInsertion(pParse, pTab, base, 0,0,0,
797 (triggers_exist & TRIGGER_AFTER)!=0 ? newIdx : -1,
798 appendFlag);
799 }
800 }
801
802 /* Update the count of rows that are inserted
803 */
804 if( (db->flags & SQLITE_CountRows)!=0 ){
805 sqlite3VdbeAddOp(v, OP_MemIncr, 1, iCntMem);
806 }
807
808 if( triggers_exist ){
809 /* Close all tables opened */
810 if( !isView ){
811 sqlite3VdbeAddOp(v, OP_Close, base, 0);
812 for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
813 sqlite3VdbeAddOp(v, OP_Close, idx+base, 0);
814 }
815 }
816
817 /* Code AFTER triggers */
818 if( sqlite3CodeRowTrigger(pParse, TK_INSERT, 0, TRIGGER_AFTER, pTab,
819 newIdx, -1, onError, endOfLoop) ){
820 goto insert_cleanup;
821 }
822 }
823
824 /* The bottom of the loop, if the data source is a SELECT statement
825 */
826 sqlite3VdbeResolveLabel(v, endOfLoop);
827 if( useTempTable ){
828 sqlite3VdbeAddOp(v, OP_Next, srcTab, iCont);
829 sqlite3VdbeResolveLabel(v, iBreak);
830 sqlite3VdbeAddOp(v, OP_Close, srcTab, 0);
831 }else if( pSelect ){
832 sqlite3VdbeAddOp(v, OP_Pop, nColumn, 0);
833 sqlite3VdbeAddOp(v, OP_Return, 0, 0);
834 sqlite3VdbeResolveLabel(v, iCleanup);
835 }
836
837 if( !triggers_exist && !IsVirtual(pTab) ){
838 /* Close all tables opened */
839 sqlite3VdbeAddOp(v, OP_Close, base, 0);
840 for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
841 sqlite3VdbeAddOp(v, OP_Close, idx+base, 0);
842 }
843 }
844
845 /* Update the sqlite_sequence table by storing the content of the
846 ** counter value in memory counterMem back into the sqlite_sequence
847 ** table.
848 */
849 autoIncEnd(pParse, iDb, pTab, counterMem);
850
851 /*
852 ** Return the number of rows inserted. If this routine is
853 ** generating code because of a call to sqlite3NestedParse(), do not
854 ** invoke the callback function.
855 */
856 if( db->flags & SQLITE_CountRows && pParse->nested==0 && !pParse->trigStack ){
857 sqlite3VdbeAddOp(v, OP_MemLoad, iCntMem, 0);
858 sqlite3VdbeAddOp(v, OP_Callback, 1, 0);
859 sqlite3VdbeSetNumCols(v, 1);
860 sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows inserted", P3_STATIC);
861 }
862
863insert_cleanup:
864 sqlite3SrcListDelete(pTabList);
865 sqlite3ExprListDelete(pList);
866 sqlite3SelectDelete(pSelect);
867 sqlite3IdListDelete(pColumn);
868}
869
870/*
871** Generate code to do a constraint check prior to an INSERT or an UPDATE.
872**
873** When this routine is called, the stack contains (from bottom to top)
874** the following values:
875**
876** 1. The rowid of the row to be updated before the update. This
877** value is omitted unless we are doing an UPDATE that involves a
878** change to the record number.
879**
880** 2. The rowid of the row after the update.
881**
882** 3. The data in the first column of the entry after the update.
883**
884** i. Data from middle columns...
885**
886** N. The data in the last column of the entry after the update.
887**
888** The old rowid shown as entry (1) above is omitted unless both isUpdate
889** and rowidChng are 1. isUpdate is true for UPDATEs and false for
890** INSERTs and rowidChng is true if the record number is being changed.
891**
892** The code generated by this routine pushes additional entries onto
893** the stack which are the keys for new index entries for the new record.
894** The order of index keys is the same as the order of the indices on
895** the pTable->pIndex list. A key is only created for index i if
896** aIdxUsed!=0 and aIdxUsed[i]!=0.
897**
898** This routine also generates code to check constraints. NOT NULL,
899** CHECK, and UNIQUE constraints are all checked. If a constraint fails,
900** then the appropriate action is performed. There are five possible
901** actions: ROLLBACK, ABORT, FAIL, REPLACE, and IGNORE.
902**
903** Constraint type Action What Happens
904** --------------- ---------- ----------------------------------------
905** any ROLLBACK The current transaction is rolled back and
906** sqlite3_exec() returns immediately with a
907** return code of SQLITE_CONSTRAINT.
908**
909** any ABORT Back out changes from the current command
910** only (do not do a complete rollback) then
911** cause sqlite3_exec() to return immediately
912** with SQLITE_CONSTRAINT.
913**
914** any FAIL Sqlite_exec() returns immediately with a
915** return code of SQLITE_CONSTRAINT. The
916** transaction is not rolled back and any
917** prior changes are retained.
918**
919** any IGNORE The record number and data is popped from
920** the stack and there is an immediate jump
921** to label ignoreDest.
922**
923** NOT NULL REPLACE The NULL value is replace by the default
924** value for that column. If the default value
925** is NULL, the action is the same as ABORT.
926**
927** UNIQUE REPLACE The other row that conflicts with the row
928** being inserted is removed.
929**
930** CHECK REPLACE Illegal. The results in an exception.
931**
932** Which action to take is determined by the overrideError parameter.
933** Or if overrideError==OE_Default, then the pParse->onError parameter
934** is used. Or if pParse->onError==OE_Default then the onError value
935** for the constraint is used.
936**
937** The calling routine must open a read/write cursor for pTab with
938** cursor number "base". All indices of pTab must also have open
939** read/write cursors with cursor number base+i for the i-th cursor.
940** Except, if there is no possibility of a REPLACE action then
941** cursors do not need to be open for indices where aIdxUsed[i]==0.
942**
943** If the isUpdate flag is true, it means that the "base" cursor is
944** initially pointing to an entry that is being updated. The isUpdate
945** flag causes extra code to be generated so that the "base" cursor
946** is still pointing at the same entry after the routine returns.
947** Without the isUpdate flag, the "base" cursor might be moved.
948*/
949void sqlite3GenerateConstraintChecks(
950 Parse *pParse, /* The parser context */
951 Table *pTab, /* the table into which we are inserting */
952 int base, /* Index of a read/write cursor pointing at pTab */
953 char *aIdxUsed, /* Which indices are used. NULL means all are used */
954 int rowidChng, /* True if the record number will change */
955 int isUpdate, /* True for UPDATE, False for INSERT */
956 int overrideError, /* Override onError to this if not OE_Default */
957 int ignoreDest /* Jump to this label on an OE_Ignore resolution */
958){
959 int i;
960 Vdbe *v;
961 int nCol;
962 int onError;
963 int addr;
964 int extra;
965 int iCur;
966 Index *pIdx;
967 int seenReplace = 0;
968 int jumpInst1=0, jumpInst2;
969 int hasTwoRowids = (isUpdate && rowidChng);
970
971 v = sqlite3GetVdbe(pParse);
972 assert( v!=0 );
973 assert( pTab->pSelect==0 ); /* This table is not a VIEW */
974 nCol = pTab->nCol;
975
976 /* Test all NOT NULL constraints.
977 */
978 for(i=0; i<nCol; i++){
979 if( i==pTab->iPKey ){
980 continue;
981 }
982 onError = pTab->aCol[i].notNull;
983 if( onError==OE_None ) continue;
984 if( overrideError!=OE_Default ){
985 onError = overrideError;
986 }else if( onError==OE_Default ){
987 onError = OE_Abort;
988 }
989 if( onError==OE_Replace && pTab->aCol[i].pDflt==0 ){
990 onError = OE_Abort;
991 }
992 sqlite3VdbeAddOp(v, OP_Dup, nCol-1-i, 1);
993 addr = sqlite3VdbeAddOp(v, OP_NotNull, 1, 0);
994 assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail
995 || onError==OE_Ignore || onError==OE_Replace );
996 switch( onError ){
997 case OE_Rollback:
998 case OE_Abort:
999 case OE_Fail: {
1000 char *zMsg = 0;
1001 sqlite3VdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, onError);
1002 sqlite3SetString(&zMsg, pTab->zName, ".", pTab->aCol[i].zName,
1003 " may not be NULL", (char*)0);
1004 sqlite3VdbeChangeP3(v, -1, zMsg, P3_DYNAMIC);
1005 break;
1006 }
1007 case OE_Ignore: {
1008 sqlite3VdbeAddOp(v, OP_Pop, nCol+1+hasTwoRowids, 0);
1009 sqlite3VdbeAddOp(v, OP_Goto, 0, ignoreDest);
1010 break;
1011 }
1012 case OE_Replace: {
1013 sqlite3ExprCode(pParse, pTab->aCol[i].pDflt);
1014 sqlite3VdbeAddOp(v, OP_Push, nCol-i, 0);
1015 break;
1016 }
1017 }
1018 sqlite3VdbeJumpHere(v, addr);
1019 }
1020
1021 /* Test all CHECK constraints
1022 */
1023#ifndef SQLITE_OMIT_CHECK
1024 if( pTab->pCheck && (pParse->db->flags & SQLITE_IgnoreChecks)==0 ){
1025 int allOk = sqlite3VdbeMakeLabel(v);
1026 assert( pParse->ckOffset==0 );
1027 pParse->ckOffset = nCol;
1028 sqlite3ExprIfTrue(pParse, pTab->pCheck, allOk, 1);
1029 assert( pParse->ckOffset==nCol );
1030 pParse->ckOffset = 0;
1031 onError = overrideError!=OE_Default ? overrideError : OE_Abort;
1032 if( onError==OE_Ignore ){
1033 sqlite3VdbeAddOp(v, OP_Pop, nCol+1+hasTwoRowids, 0);
1034 sqlite3VdbeAddOp(v, OP_Goto, 0, ignoreDest);
1035 }else{
1036 sqlite3VdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, onError);
1037 }
1038 sqlite3VdbeResolveLabel(v, allOk);
1039 }
1040#endif /* !defined(SQLITE_OMIT_CHECK) */
1041
1042 /* If we have an INTEGER PRIMARY KEY, make sure the primary key
1043 ** of the new record does not previously exist. Except, if this
1044 ** is an UPDATE and the primary key is not changing, that is OK.
1045 */
1046 if( rowidChng ){
1047 onError = pTab->keyConf;
1048 if( overrideError!=OE_Default ){
1049 onError = overrideError;
1050 }else if( onError==OE_Default ){
1051 onError = OE_Abort;
1052 }
1053
1054 if( isUpdate ){
1055 sqlite3VdbeAddOp(v, OP_Dup, nCol+1, 1);
1056 sqlite3VdbeAddOp(v, OP_Dup, nCol+1, 1);
1057 jumpInst1 = sqlite3VdbeAddOp(v, OP_Eq, 0, 0);
1058 }
1059 sqlite3VdbeAddOp(v, OP_Dup, nCol, 1);
1060 jumpInst2 = sqlite3VdbeAddOp(v, OP_NotExists, base, 0);
1061 switch( onError ){
1062 default: {
1063 onError = OE_Abort;
1064 /* Fall thru into the next case */
1065 }
1066 case OE_Rollback:
1067 case OE_Abort:
1068 case OE_Fail: {
1069 sqlite3VdbeOp3(v, OP_Halt, SQLITE_CONSTRAINT, onError,
1070 "PRIMARY KEY must be unique", P3_STATIC);
1071 break;
1072 }
1073 case OE_Replace: {
1074 sqlite3GenerateRowIndexDelete(v, pTab, base, 0);
1075 if( isUpdate ){
1076 sqlite3VdbeAddOp(v, OP_Dup, nCol+hasTwoRowids, 1);
1077 sqlite3VdbeAddOp(v, OP_MoveGe, base, 0);
1078 }
1079 seenReplace = 1;
1080 break;
1081 }
1082 case OE_Ignore: {
1083 assert( seenReplace==0 );
1084 sqlite3VdbeAddOp(v, OP_Pop, nCol+1+hasTwoRowids, 0);
1085 sqlite3VdbeAddOp(v, OP_Goto, 0, ignoreDest);
1086 break;
1087 }
1088 }
1089 sqlite3VdbeJumpHere(v, jumpInst2);
1090 if( isUpdate ){
1091 sqlite3VdbeJumpHere(v, jumpInst1);
1092 sqlite3VdbeAddOp(v, OP_Dup, nCol+1, 1);
1093 sqlite3VdbeAddOp(v, OP_MoveGe, base, 0);
1094 }
1095 }
1096
1097 /* Test all UNIQUE constraints by creating entries for each UNIQUE
1098 ** index and making sure that duplicate entries do not already exist.
1099 ** Add the new records to the indices as we go.
1100 */
1101 extra = -1;
1102 for(iCur=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, iCur++){
1103 if( aIdxUsed && aIdxUsed[iCur]==0 ) continue; /* Skip unused indices */
1104 extra++;
1105
1106 /* Create a key for accessing the index entry */
1107 sqlite3VdbeAddOp(v, OP_Dup, nCol+extra, 1);
1108 for(i=0; i<pIdx->nColumn; i++){
1109 int idx = pIdx->aiColumn[i];
1110 if( idx==pTab->iPKey ){
1111 sqlite3VdbeAddOp(v, OP_Dup, i+extra+nCol+1, 1);
1112 }else{
1113 sqlite3VdbeAddOp(v, OP_Dup, i+extra+nCol-idx, 1);
1114 }
1115 }
1116 jumpInst1 = sqlite3VdbeAddOp(v, OP_MakeIdxRec, pIdx->nColumn, 0);
1117 sqlite3IndexAffinityStr(v, pIdx);
1118
1119 /* Find out what action to take in case there is an indexing conflict */
1120 onError = pIdx->onError;
1121 if( onError==OE_None ) continue; /* pIdx is not a UNIQUE index */
1122 if( overrideError!=OE_Default ){
1123 onError = overrideError;
1124 }else if( onError==OE_Default ){
1125 onError = OE_Abort;
1126 }
1127 if( seenReplace ){
1128 if( onError==OE_Ignore ) onError = OE_Replace;
1129 else if( onError==OE_Fail ) onError = OE_Abort;
1130 }
1131
1132
1133 /* Check to see if the new index entry will be unique */
1134 sqlite3VdbeAddOp(v, OP_Dup, extra+nCol+1+hasTwoRowids, 1);
1135 jumpInst2 = sqlite3VdbeAddOp(v, OP_IsUnique, base+iCur+1, 0);
1136
1137 /* Generate code that executes if the new index entry is not unique */
1138 assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail
1139 || onError==OE_Ignore || onError==OE_Replace );
1140 switch( onError ){
1141 case OE_Rollback:
1142 case OE_Abort:
1143 case OE_Fail: {
1144 int j, n1, n2;
1145 char zErrMsg[200];
1146 sqlite3_snprintf(sizeof(zErrMsg), zErrMsg,
1147 pIdx->nColumn>1 ? "columns " : "column ");
1148 n1 = strlen(zErrMsg);
1149 for(j=0; j<pIdx->nColumn && n1<sizeof(zErrMsg)-30; j++){
1150 char *zCol = pTab->aCol[pIdx->aiColumn[j]].zName;
1151 n2 = strlen(zCol);
1152 if( j>0 ){
1153 sqlite3_snprintf(sizeof(zErrMsg)-n1, &zErrMsg[n1], ", ");
1154 n1 += 2;
1155 }
1156 if( n1+n2>sizeof(zErrMsg)-30 ){
1157 sqlite3_snprintf(sizeof(zErrMsg)-n1, &zErrMsg[n1], "...");
1158 n1 += 3;
1159 break;
1160 }else{
1161 sqlite3_snprintf(sizeof(zErrMsg)-n1, &zErrMsg[n1], "%s", zCol);
1162 n1 += n2;
1163 }
1164 }
1165 sqlite3_snprintf(sizeof(zErrMsg)-n1, &zErrMsg[n1],
1166 pIdx->nColumn>1 ? " are not unique" : " is not unique");
1167 sqlite3VdbeOp3(v, OP_Halt, SQLITE_CONSTRAINT, onError, zErrMsg, 0);
1168 break;
1169 }
1170 case OE_Ignore: {
1171 assert( seenReplace==0 );
1172 sqlite3VdbeAddOp(v, OP_Pop, nCol+extra+3+hasTwoRowids, 0);
1173 sqlite3VdbeAddOp(v, OP_Goto, 0, ignoreDest);
1174 break;
1175 }
1176 case OE_Replace: {
1177 sqlite3GenerateRowDelete(pParse->db, v, pTab, base, 0);
1178 if( isUpdate ){
1179 sqlite3VdbeAddOp(v, OP_Dup, nCol+extra+1+hasTwoRowids, 1);
1180 sqlite3VdbeAddOp(v, OP_MoveGe, base, 0);
1181 }
1182 seenReplace = 1;
1183 break;
1184 }
1185 }
1186#if NULL_DISTINCT_FOR_UNIQUE
1187 sqlite3VdbeJumpHere(v, jumpInst1);
1188#endif
1189 sqlite3VdbeJumpHere(v, jumpInst2);
1190 }
1191}
1192
1193/*
1194** This routine generates code to finish the INSERT or UPDATE operation
1195** that was started by a prior call to sqlite3GenerateConstraintChecks.
1196** The stack must contain keys for all active indices followed by data
1197** and the rowid for the new entry. This routine creates the new
1198** entries in all indices and in the main table.
1199**
1200** The arguments to this routine should be the same as the first six
1201** arguments to sqlite3GenerateConstraintChecks.
1202*/
1203void sqlite3CompleteInsertion(
1204 Parse *pParse, /* The parser context */
1205 Table *pTab, /* the table into which we are inserting */
1206 int base, /* Index of a read/write cursor pointing at pTab */
1207 char *aIdxUsed, /* Which indices are used. NULL means all are used */
1208 int rowidChng, /* True if the record number will change */
1209 int isUpdate, /* True for UPDATE, False for INSERT */
1210 int newIdx, /* Index of NEW table for triggers. -1 if none */
1211 int appendBias /* True if this is likely to be an append */
1212){
1213 int i;
1214 Vdbe *v;
1215 int nIdx;
1216 Index *pIdx;
1217 int pik_flags;
1218
1219 v = sqlite3GetVdbe(pParse);
1220 assert( v!=0 );
1221 assert( pTab->pSelect==0 ); /* This table is not a VIEW */
1222 for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){}
1223 for(i=nIdx-1; i>=0; i--){
1224 if( aIdxUsed && aIdxUsed[i]==0 ) continue;
1225 sqlite3VdbeAddOp(v, OP_IdxInsert, base+i+1, 0);
1226 }
1227 sqlite3VdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
1228 sqlite3TableAffinityStr(v, pTab);
1229#ifndef SQLITE_OMIT_TRIGGER
1230 if( newIdx>=0 ){
1231 sqlite3VdbeAddOp(v, OP_Dup, 1, 0);
1232 sqlite3VdbeAddOp(v, OP_Dup, 1, 0);
1233 sqlite3VdbeAddOp(v, OP_Insert, newIdx, 0);
1234 }
1235#endif
1236 if( pParse->nested ){
1237 pik_flags = 0;
1238 }else{
1239 pik_flags = OPFLAG_NCHANGE;
1240 pik_flags |= (isUpdate?OPFLAG_ISUPDATE:OPFLAG_LASTROWID);
1241 }
1242 if( appendBias ){
1243 pik_flags |= OPFLAG_APPEND;
1244 }
1245 sqlite3VdbeAddOp(v, OP_Insert, base, pik_flags);
1246 if( !pParse->nested ){
1247 sqlite3VdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
1248 }
1249
1250 if( isUpdate && rowidChng ){
1251 sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
1252 }
1253}
1254
1255/*
1256** Generate code that will open cursors for a table and for all
1257** indices of that table. The "base" parameter is the cursor number used
1258** for the table. Indices are opened on subsequent cursors.
1259*/
1260void sqlite3OpenTableAndIndices(
1261 Parse *pParse, /* Parsing context */
1262 Table *pTab, /* Table to be opened */
1263 int base, /* Cursor number assigned to the table */
1264 int op /* OP_OpenRead or OP_OpenWrite */
1265){
1266 int i;
1267 int iDb;
1268 Index *pIdx;
1269 Vdbe *v;
1270
1271 if( IsVirtual(pTab) ) return;
1272 iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema);
1273 v = sqlite3GetVdbe(pParse);
1274 assert( v!=0 );
1275 sqlite3OpenTable(pParse, base, iDb, pTab, op);
1276 for(i=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
1277 KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx);
1278 assert( pIdx->pSchema==pTab->pSchema );
1279 sqlite3VdbeAddOp(v, OP_Integer, iDb, 0);
1280 VdbeComment((v, "# %s", pIdx->zName));
1281 sqlite3VdbeOp3(v, op, i+base, pIdx->tnum, (char*)pKey, P3_KEYINFO_HANDOFF);
1282 }
1283 if( pParse->nTab<=base+i ){
1284 pParse->nTab = base+i;
1285 }
1286}
1287
1288
1289#ifdef SQLITE_TEST
1290/*
1291** The following global variable is incremented whenever the
1292** transfer optimization is used. This is used for testing
1293** purposes only - to make sure the transfer optimization really
1294** is happening when it is suppose to.
1295*/
1296int sqlite3_xferopt_count;
1297#endif /* SQLITE_TEST */
1298
1299
1300#ifndef SQLITE_OMIT_XFER_OPT
1301/*
1302** Check to collation names to see if they are compatible.
1303*/
1304static int xferCompatibleCollation(const char *z1, const char *z2){
1305 if( z1==0 ){
1306 return z2==0;
1307 }
1308 if( z2==0 ){
1309 return 0;
1310 }
1311 return sqlite3StrICmp(z1, z2)==0;
1312}
1313
1314
1315/*
1316** Check to see if index pSrc is compatible as a source of data
1317** for index pDest in an insert transfer optimization. The rules
1318** for a compatible index:
1319**
1320** * The index is over the same set of columns
1321** * The same DESC and ASC markings occurs on all columns
1322** * The same onError processing (OE_Abort, OE_Ignore, etc)
1323** * The same collating sequence on each column
1324*/
1325static int xferCompatibleIndex(Index *pDest, Index *pSrc){
1326 int i;
1327 assert( pDest && pSrc );
1328 assert( pDest->pTable!=pSrc->pTable );
1329 if( pDest->nColumn!=pSrc->nColumn ){
1330 return 0; /* Different number of columns */
1331 }
1332 if( pDest->onError!=pSrc->onError ){
1333 return 0; /* Different conflict resolution strategies */
1334 }
1335 for(i=0; i<pSrc->nColumn; i++){
1336 if( pSrc->aiColumn[i]!=pDest->aiColumn[i] ){
1337 return 0; /* Different columns indexed */
1338 }
1339 if( pSrc->aSortOrder[i]!=pDest->aSortOrder[i] ){
1340 return 0; /* Different sort orders */
1341 }
1342 if( pSrc->azColl[i]!=pDest->azColl[i] ){
1343 return 0; /* Different sort orders */
1344 }
1345 }
1346
1347 /* If no test above fails then the indices must be compatible */
1348 return 1;
1349}
1350
1351/*
1352** Attempt the transfer optimization on INSERTs of the form
1353**
1354** INSERT INTO tab1 SELECT * FROM tab2;
1355**
1356** This optimization is only attempted if
1357**
1358** (1) tab1 and tab2 have identical schemas including all the
1359** same indices and constraints
1360**
1361** (2) tab1 and tab2 are different tables
1362**
1363** (3) There must be no triggers on tab1
1364**
1365** (4) The result set of the SELECT statement is "*"
1366**
1367** (5) The SELECT statement has no WHERE, HAVING, ORDER BY, GROUP BY,
1368** or LIMIT clause.
1369**
1370** (6) The SELECT statement is a simple (not a compound) select that
1371** contains only tab2 in its FROM clause
1372**
1373** This method for implementing the INSERT transfers raw records from
1374** tab2 over to tab1. The columns are not decoded. Raw records from
1375** the indices of tab2 are transfered to tab1 as well. In so doing,
1376** the resulting tab1 has much less fragmentation.
1377**
1378** This routine returns TRUE if the optimization is attempted. If any
1379** of the conditions above fail so that the optimization should not
1380** be attempted, then this routine returns FALSE.
1381*/
1382static int xferOptimization(
1383 Parse *pParse, /* Parser context */
1384 Table *pDest, /* The table we are inserting into */
1385 Select *pSelect, /* A SELECT statement to use as the data source */
1386 int onError, /* How to handle constraint errors */
1387 int iDbDest /* The database of pDest */
1388){
1389 ExprList *pEList; /* The result set of the SELECT */
1390 Table *pSrc; /* The table in the FROM clause of SELECT */
1391 Index *pSrcIdx, *pDestIdx; /* Source and destination indices */
1392 struct SrcList_item *pItem; /* An element of pSelect->pSrc */
1393 int i; /* Loop counter */
1394 int iDbSrc; /* The database of pSrc */
1395 int iSrc, iDest; /* Cursors from source and destination */
1396 int addr1, addr2; /* Loop addresses */
1397 int emptyDestTest; /* Address of test for empty pDest */
1398 int emptySrcTest; /* Address of test for empty pSrc */
1399 Vdbe *v; /* The VDBE we are building */
1400 KeyInfo *pKey; /* Key information for an index */
1401 int counterMem; /* Memory register used by AUTOINC */
1402 int destHasUniqueIdx = 0; /* True if pDest has a UNIQUE index */
1403
1404 if( pSelect==0 ){
1405 return 0; /* Must be of the form INSERT INTO ... SELECT ... */
1406 }
1407 if( pDest->pTrigger ){
1408 return 0; /* tab1 must not have triggers */
1409 }
1410#ifndef SQLITE_OMIT_VIRTUALTABLE
1411 if( pDest->isVirtual ){
1412 return 0; /* tab1 must not be a virtual table */
1413 }
1414#endif
1415 if( onError==OE_Default ){
1416 onError = OE_Abort;
1417 }
1418 if( onError!=OE_Abort && onError!=OE_Rollback ){
1419 return 0; /* Cannot do OR REPLACE or OR IGNORE or OR FAIL */
1420 }
1421 assert(pSelect->pSrc); /* allocated even if there is no FROM clause */
1422 if( pSelect->pSrc->nSrc!=1 ){
1423 return 0; /* FROM clause must have exactly one term */
1424 }
1425 if( pSelect->pSrc->a[0].pSelect ){
1426 return 0; /* FROM clause cannot contain a subquery */
1427 }
1428 if( pSelect->pWhere ){
1429 return 0; /* SELECT may not have a WHERE clause */
1430 }
1431 if( pSelect->pOrderBy ){
1432 return 0; /* SELECT may not have an ORDER BY clause */
1433 }
1434 /* Do not need to test for a HAVING clause. If HAVING is present but
1435 ** there is no ORDER BY, we will get an error. */
1436 if( pSelect->pGroupBy ){
1437 return 0; /* SELECT may not have a GROUP BY clause */
1438 }
1439 if( pSelect->pLimit ){
1440 return 0; /* SELECT may not have a LIMIT clause */
1441 }
1442 assert( pSelect->pOffset==0 ); /* Must be so if pLimit==0 */
1443 if( pSelect->pPrior ){
1444 return 0; /* SELECT may not be a compound query */
1445 }
1446 if( pSelect->isDistinct ){
1447 return 0; /* SELECT may not be DISTINCT */
1448 }
1449 pEList = pSelect->pEList;
1450 assert( pEList!=0 );
1451 if( pEList->nExpr!=1 ){
1452 return 0; /* The result set must have exactly one column */
1453 }
1454 assert( pEList->a[0].pExpr );
1455 if( pEList->a[0].pExpr->op!=TK_ALL ){
1456 return 0; /* The result set must be the special operator "*" */
1457 }
1458
1459 /* At this point we have established that the statement is of the
1460 ** correct syntactic form to participate in this optimization. Now
1461 ** we have to check the semantics.
1462 */
1463 pItem = pSelect->pSrc->a;
1464 pSrc = sqlite3LocateTable(pParse, pItem->zName, pItem->zDatabase);
1465 if( pSrc==0 ){
1466 return 0; /* FROM clause does not contain a real table */
1467 }
1468 if( pSrc==pDest ){
1469 return 0; /* tab1 and tab2 may not be the same table */
1470 }
1471#ifndef SQLITE_OMIT_VIRTUALTABLE
1472 if( pSrc->isVirtual ){
1473 return 0; /* tab2 must not be a virtual table */
1474 }
1475#endif
1476 if( pSrc->pSelect ){
1477 return 0; /* tab2 may not be a view */
1478 }
1479 if( pDest->nCol!=pSrc->nCol ){
1480 return 0; /* Number of columns must be the same in tab1 and tab2 */
1481 }
1482 if( pDest->iPKey!=pSrc->iPKey ){
1483 return 0; /* Both tables must have the same INTEGER PRIMARY KEY */
1484 }
1485 for(i=0; i<pDest->nCol; i++){
1486 if( pDest->aCol[i].affinity!=pSrc->aCol[i].affinity ){
1487 return 0; /* Affinity must be the same on all columns */
1488 }
1489 if( !xferCompatibleCollation(pDest->aCol[i].zColl, pSrc->aCol[i].zColl) ){
1490 return 0; /* Collating sequence must be the same on all columns */
1491 }
1492 if( pDest->aCol[i].notNull && !pSrc->aCol[i].notNull ){
1493 return 0; /* tab2 must be NOT NULL if tab1 is */
1494 }
1495 }
1496 for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){
1497 if( pDestIdx->onError!=OE_None ){
1498 destHasUniqueIdx = 1;
1499 }
1500 for(pSrcIdx=pSrc->pIndex; pSrcIdx; pSrcIdx=pSrcIdx->pNext){
1501 if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break;
1502 }
1503 if( pSrcIdx==0 ){
1504 return 0; /* pDestIdx has no corresponding index in pSrc */
1505 }
1506 }
1507#ifndef SQLITE_OMIT_CHECK
1508 if( pDest->pCheck && !sqlite3ExprCompare(pSrc->pCheck, pDest->pCheck) ){
1509 return 0; /* Tables have different CHECK constraints. Ticket #2252 */
1510 }
1511#endif
1512
1513 /* If we get this far, it means either:
1514 **
1515 ** * We can always do the transfer if the table contains an
1516 ** an integer primary key
1517 **
1518 ** * We can conditionally do the transfer if the destination
1519 ** table is empty.
1520 */
1521#ifdef SQLITE_TEST
1522 sqlite3_xferopt_count++;
1523#endif
1524 iDbSrc = sqlite3SchemaToIndex(pParse->db, pSrc->pSchema);
1525 v = sqlite3GetVdbe(pParse);
1526 sqlite3CodeVerifySchema(pParse, iDbSrc);
1527 iSrc = pParse->nTab++;
1528 iDest = pParse->nTab++;
1529 counterMem = autoIncBegin(pParse, iDbDest, pDest);
1530 sqlite3OpenTable(pParse, iDest, iDbDest, pDest, OP_OpenWrite);
1531 if( (pDest->iPKey<0 && pDest->pIndex!=0) || destHasUniqueIdx ){
1532 /* If tables do not have an INTEGER PRIMARY KEY and there
1533 ** are indices to be copied and the destination is not empty,
1534 ** we have to disallow the transfer optimization because the
1535 ** the rowids might change which will mess up indexing.
1536 **
1537 ** Or if the destination has a UNIQUE index and is not empty,
1538 ** we also disallow the transfer optimization because we cannot
1539 ** insure that all entries in the union of DEST and SRC will be
1540 ** unique.
1541 */
1542 addr1 = sqlite3VdbeAddOp(v, OP_Rewind, iDest, 0);
1543 emptyDestTest = sqlite3VdbeAddOp(v, OP_Goto, 0, 0);
1544 sqlite3VdbeJumpHere(v, addr1);
1545 }else{
1546 emptyDestTest = 0;
1547 }
1548 sqlite3OpenTable(pParse, iSrc, iDbSrc, pSrc, OP_OpenRead);
1549 emptySrcTest = sqlite3VdbeAddOp(v, OP_Rewind, iSrc, 0);
1550 if( pDest->iPKey>=0 ){
1551 addr1 = sqlite3VdbeAddOp(v, OP_Rowid, iSrc, 0);
1552 sqlite3VdbeAddOp(v, OP_Dup, 0, 0);
1553 addr2 = sqlite3VdbeAddOp(v, OP_NotExists, iDest, 0);
1554 sqlite3VdbeOp3(v, OP_Halt, SQLITE_CONSTRAINT, onError,
1555 "PRIMARY KEY must be unique", P3_STATIC);
1556 sqlite3VdbeJumpHere(v, addr2);
1557 autoIncStep(pParse, counterMem);
1558 }else if( pDest->pIndex==0 ){
1559 addr1 = sqlite3VdbeAddOp(v, OP_NewRowid, iDest, 0);
1560 }else{
1561 addr1 = sqlite3VdbeAddOp(v, OP_Rowid, iSrc, 0);
1562 assert( pDest->autoInc==0 );
1563 }
1564 sqlite3VdbeAddOp(v, OP_RowData, iSrc, 0);
1565 sqlite3VdbeOp3(v, OP_Insert, iDest,
1566 OPFLAG_NCHANGE|OPFLAG_LASTROWID|OPFLAG_APPEND,
1567 pDest->zName, 0);
1568 sqlite3VdbeAddOp(v, OP_Next, iSrc, addr1);
1569 autoIncEnd(pParse, iDbDest, pDest, counterMem);
1570 for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){
1571 for(pSrcIdx=pSrc->pIndex; pSrcIdx; pSrcIdx=pSrcIdx->pNext){
1572 if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break;
1573 }
1574 assert( pSrcIdx );
1575 sqlite3VdbeAddOp(v, OP_Close, iSrc, 0);
1576 sqlite3VdbeAddOp(v, OP_Close, iDest, 0);
1577 sqlite3VdbeAddOp(v, OP_Integer, iDbSrc, 0);
1578 pKey = sqlite3IndexKeyinfo(pParse, pSrcIdx);
1579 VdbeComment((v, "# %s", pSrcIdx->zName));
1580 sqlite3VdbeOp3(v, OP_OpenRead, iSrc, pSrcIdx->tnum,
1581 (char*)pKey, P3_KEYINFO_HANDOFF);
1582 sqlite3VdbeAddOp(v, OP_Integer, iDbDest, 0);
1583 pKey = sqlite3IndexKeyinfo(pParse, pDestIdx);
1584 VdbeComment((v, "# %s", pDestIdx->zName));
1585 sqlite3VdbeOp3(v, OP_OpenWrite, iDest, pDestIdx->tnum,
1586 (char*)pKey, P3_KEYINFO_HANDOFF);
1587 addr1 = sqlite3VdbeAddOp(v, OP_Rewind, iSrc, 0);
1588 sqlite3VdbeAddOp(v, OP_RowKey, iSrc, 0);
1589 sqlite3VdbeAddOp(v, OP_IdxInsert, iDest, 1);
1590 sqlite3VdbeAddOp(v, OP_Next, iSrc, addr1+1);
1591 sqlite3VdbeJumpHere(v, addr1);
1592 }
1593 sqlite3VdbeJumpHere(v, emptySrcTest);
1594 sqlite3VdbeAddOp(v, OP_Close, iSrc, 0);
1595 sqlite3VdbeAddOp(v, OP_Close, iDest, 0);
1596 if( emptyDestTest ){
1597 sqlite3VdbeAddOp(v, OP_Halt, SQLITE_OK, 0);
1598 sqlite3VdbeJumpHere(v, emptyDestTest);
1599 sqlite3VdbeAddOp(v, OP_Close, iDest, 0);
1600 return 0;
1601 }else{
1602 return 1;
1603 }
1604}
1605#endif /* SQLITE_OMIT_XFER_OPT */
generated by cgit v1.1 at 2024-12-23 16:55:19 +0000