xref: /illumos-gate/usr/src/lib/libsqlite/src/insert.c (revision 55fea89dcaa64928bed4327112404dcb3e07b79f)
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.94 2004/02/24 01:05:33 drh Exp $
16 */
17 #include "sqliteInt.h"
18 
19 /*
20 ** This routine is call to handle SQL of the following forms:
21 **
22 **    insert into TABLE (IDLIST) values(EXPRLIST)
23 **    insert into TABLE (IDLIST) select
24 **
25 ** The IDLIST following the table name is always optional.  If omitted,
26 ** then a list of all columns for the table is substituted.  The IDLIST
27 ** appears in the pColumn parameter.  pColumn is NULL if IDLIST is omitted.
28 **
29 ** The pList parameter holds EXPRLIST in the first form of the INSERT
30 ** statement above, and pSelect is NULL.  For the second form, pList is
31 ** NULL and pSelect is a pointer to the select statement used to generate
32 ** data for the insert.
33 **
34 ** The code generated follows one of three templates.  For a simple
35 ** select with data coming from a VALUES clause, the code executes
36 ** once straight down through.  The template looks like this:
37 **
38 **         open write cursor to <table> and its indices
39 **         puts VALUES clause expressions onto the stack
40 **         write the resulting record into <table>
41 **         cleanup
42 **
43 ** If the statement is of the form
44 **
45 **   INSERT INTO <table> SELECT ...
46 **
47 ** And the SELECT clause does not read from <table> at any time, then
48 ** the generated code follows this template:
49 **
50 **         goto B
51 **      A: setup for the SELECT
52 **         loop over the tables in the SELECT
53 **           gosub C
54 **         end loop
55 **         cleanup after the SELECT
56 **         goto D
57 **      B: open write cursor to <table> and its indices
58 **         goto A
59 **      C: insert the select result into <table>
60 **         return
61 **      D: cleanup
62 **
63 ** The third template is used if the insert statement takes its
64 ** values from a SELECT but the data is being inserted into a table
65 ** that is also read as part of the SELECT.  In the third form,
66 ** we have to use a intermediate table to store the results of
67 ** the select.  The template is like this:
68 **
69 **         goto B
70 **      A: setup for the SELECT
71 **         loop over the tables in the SELECT
72 **           gosub C
73 **         end loop
74 **         cleanup after the SELECT
75 **         goto D
76 **      C: insert the select result into the intermediate table
77 **         return
78 **      B: open a cursor to an intermediate table
79 **         goto A
80 **      D: open write cursor to <table> and its indices
81 **         loop over the intermediate table
82 **           transfer values form intermediate table into <table>
83 **         end the loop
84 **         cleanup
85 */
sqliteInsert(Parse * pParse,SrcList * pTabList,ExprList * pList,Select * pSelect,IdList * pColumn,int onError)86 void sqliteInsert(
87   Parse *pParse,        /* Parser context */
88   SrcList *pTabList,    /* Name of table into which we are inserting */
89   ExprList *pList,      /* List of values to be inserted */
90   Select *pSelect,      /* A SELECT statement to use as the data source */
91   IdList *pColumn,      /* Column names corresponding to IDLIST. */
92   int onError           /* How to handle constraint errors */
93 ){
94   Table *pTab;          /* The table to insert into */
95   char *zTab;           /* Name of the table into which we are inserting */
96   const char *zDb;      /* Name of the database holding this table */
97   int i, j, idx;        /* Loop counters */
98   Vdbe *v;              /* Generate code into this virtual machine */
99   Index *pIdx;          /* For looping over indices of the table */
100   int nColumn;          /* Number of columns in the data */
101   int base;             /* VDBE Cursor number for pTab */
102   int iCont, iBreak;    /* Beginning and end of the loop over srcTab */
103   sqlite *db;           /* The main database structure */
104   int keyColumn = -1;   /* Column that is the INTEGER PRIMARY KEY */
105   int endOfLoop;        /* Label for the end of the insertion loop */
106   int useTempTable;     /* Store SELECT results in intermediate table */
107   int srcTab;           /* Data comes from this temporary cursor if >=0 */
108   int iSelectLoop;      /* Address of code that implements the SELECT */
109   int iCleanup;         /* Address of the cleanup code */
110   int iInsertBlock;     /* Address of the subroutine used to insert data */
111   int iCntMem;          /* Memory cell used for the row counter */
112   int isView;           /* True if attempting to insert into a view */
113 
114   int row_triggers_exist = 0; /* True if there are FOR EACH ROW triggers */
115   int before_triggers;        /* True if there are BEFORE triggers */
116   int after_triggers;         /* True if there are AFTER triggers */
117   int newIdx = -1;            /* Cursor for the NEW table */
118 
119   if( pParse->nErr || sqlite_malloc_failed ) goto insert_cleanup;
120   db = pParse->db;
121 
122   /* Locate the table into which we will be inserting new information.
123   */
124   assert( pTabList->nSrc==1 );
125   zTab = pTabList->a[0].zName;
126   if( zTab==0 ) goto insert_cleanup;
127   pTab = sqliteSrcListLookup(pParse, pTabList);
128   if( pTab==0 ){
129     goto insert_cleanup;
130   }
131   assert( pTab->iDb<db->nDb );
132   zDb = db->aDb[pTab->iDb].zName;
133   if( sqliteAuthCheck(pParse, SQLITE_INSERT, pTab->zName, 0, zDb) ){
134     goto insert_cleanup;
135   }
136 
137   /* Ensure that:
138   *  (a) the table is not read-only,
139   *  (b) that if it is a view then ON INSERT triggers exist
140   */
141   before_triggers = sqliteTriggersExist(pParse, pTab->pTrigger, TK_INSERT,
142                                        TK_BEFORE, TK_ROW, 0);
143   after_triggers = sqliteTriggersExist(pParse, pTab->pTrigger, TK_INSERT,
144                                        TK_AFTER, TK_ROW, 0);
145   row_triggers_exist = before_triggers || after_triggers;
146   isView = pTab->pSelect!=0;
147   if( sqliteIsReadOnly(pParse, pTab, before_triggers) ){
148     goto insert_cleanup;
149   }
150   if( pTab==0 ) goto insert_cleanup;
151 
152   /* If pTab is really a view, make sure it has been initialized.
153   */
154   if( isView && sqliteViewGetColumnNames(pParse, pTab) ){
155     goto insert_cleanup;
156   }
157 
158   /* Allocate a VDBE
159   */
160   v = sqliteGetVdbe(pParse);
161   if( v==0 ) goto insert_cleanup;
162   sqliteBeginWriteOperation(pParse, pSelect || row_triggers_exist, pTab->iDb);
163 
164   /* if there are row triggers, allocate a temp table for new.* references. */
165   if( row_triggers_exist ){
166     newIdx = pParse->nTab++;
167   }
168 
169   /* Figure out how many columns of data are supplied.  If the data
170   ** is coming from a SELECT statement, then this step also generates
171   ** all the code to implement the SELECT statement and invoke a subroutine
172   ** to process each row of the result. (Template 2.) If the SELECT
173   ** statement uses the the table that is being inserted into, then the
174   ** subroutine is also coded here.  That subroutine stores the SELECT
175   ** results in a temporary table. (Template 3.)
176   */
177   if( pSelect ){
178     /* Data is coming from a SELECT.  Generate code to implement that SELECT
179     */
180     int rc, iInitCode;
181     iInitCode = sqliteVdbeAddOp(v, OP_Goto, 0, 0);
182     iSelectLoop = sqliteVdbeCurrentAddr(v);
183     iInsertBlock = sqliteVdbeMakeLabel(v);
184     rc = sqliteSelect(pParse, pSelect, SRT_Subroutine, iInsertBlock, 0,0,0);
185     if( rc || pParse->nErr || sqlite_malloc_failed ) goto insert_cleanup;
186     iCleanup = sqliteVdbeMakeLabel(v);
187     sqliteVdbeAddOp(v, OP_Goto, 0, iCleanup);
188     assert( pSelect->pEList );
189     nColumn = pSelect->pEList->nExpr;
190 
191     /* Set useTempTable to TRUE if the result of the SELECT statement
192     ** should be written into a temporary table.  Set to FALSE if each
193     ** row of the SELECT can be written directly into the result table.
194     **
195     ** A temp table must be used if the table being updated is also one
196     ** of the tables being read by the SELECT statement.  Also use a
197     ** temp table in the case of row triggers.
198     */
199     if( row_triggers_exist ){
200       useTempTable = 1;
201     }else{
202       int addr = sqliteVdbeFindOp(v, OP_OpenRead, pTab->tnum);
203       useTempTable = 0;
204       if( addr>0 ){
205         VdbeOp *pOp = sqliteVdbeGetOp(v, addr-2);
206         if( pOp->opcode==OP_Integer && pOp->p1==pTab->iDb ){
207           useTempTable = 1;
208         }
209       }
210     }
211 
212     if( useTempTable ){
213       /* Generate the subroutine that SELECT calls to process each row of
214       ** the result.  Store the result in a temporary table
215       */
216       srcTab = pParse->nTab++;
217       sqliteVdbeResolveLabel(v, iInsertBlock);
218       sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0);
219       sqliteVdbeAddOp(v, OP_NewRecno, srcTab, 0);
220       sqliteVdbeAddOp(v, OP_Pull, 1, 0);
221       sqliteVdbeAddOp(v, OP_PutIntKey, srcTab, 0);
222       sqliteVdbeAddOp(v, OP_Return, 0, 0);
223 
224       /* The following code runs first because the GOTO at the very top
225       ** of the program jumps to it.  Create the temporary table, then jump
226       ** back up and execute the SELECT code above.
227       */
228       sqliteVdbeChangeP2(v, iInitCode, sqliteVdbeCurrentAddr(v));
229       sqliteVdbeAddOp(v, OP_OpenTemp, srcTab, 0);
230       sqliteVdbeAddOp(v, OP_Goto, 0, iSelectLoop);
231       sqliteVdbeResolveLabel(v, iCleanup);
232     }else{
233       sqliteVdbeChangeP2(v, iInitCode, sqliteVdbeCurrentAddr(v));
234     }
235   }else{
236     /* This is the case if the data for the INSERT is coming from a VALUES
237     ** clause
238     */
239     SrcList dummy;
240     assert( pList!=0 );
241     srcTab = -1;
242     useTempTable = 0;
243     assert( pList );
244     nColumn = pList->nExpr;
245     dummy.nSrc = 0;
246     for(i=0; i<nColumn; i++){
247       if( sqliteExprResolveIds(pParse, &dummy, 0, pList->a[i].pExpr) ){
248         goto insert_cleanup;
249       }
250       if( sqliteExprCheck(pParse, pList->a[i].pExpr, 0, 0) ){
251         goto insert_cleanup;
252       }
253     }
254   }
255 
256   /* Make sure the number of columns in the source data matches the number
257   ** of columns to be inserted into the table.
258   */
259   if( pColumn==0 && nColumn!=pTab->nCol ){
260     sqliteErrorMsg(pParse,
261        "table %S has %d columns but %d values were supplied",
262        pTabList, 0, pTab->nCol, nColumn);
263     goto insert_cleanup;
264   }
265   if( pColumn!=0 && nColumn!=pColumn->nId ){
266     sqliteErrorMsg(pParse, "%d values for %d columns", nColumn, pColumn->nId);
267     goto insert_cleanup;
268   }
269 
270   /* If the INSERT statement included an IDLIST term, then make sure
271   ** all elements of the IDLIST really are columns of the table and
272   ** remember the column indices.
273   **
274   ** If the table has an INTEGER PRIMARY KEY column and that column
275   ** is named in the IDLIST, then record in the keyColumn variable
276   ** the index into IDLIST of the primary key column.  keyColumn is
277   ** the index of the primary key as it appears in IDLIST, not as
278   ** is appears in the original table.  (The index of the primary
279   ** key in the original table is pTab->iPKey.)
280   */
281   if( pColumn ){
282     for(i=0; i<pColumn->nId; i++){
283       pColumn->a[i].idx = -1;
284     }
285     for(i=0; i<pColumn->nId; i++){
286       for(j=0; j<pTab->nCol; j++){
287         if( sqliteStrICmp(pColumn->a[i].zName, pTab->aCol[j].zName)==0 ){
288           pColumn->a[i].idx = j;
289           if( j==pTab->iPKey ){
290             keyColumn = i;
291           }
292           break;
293         }
294       }
295       if( j>=pTab->nCol ){
296         if( sqliteIsRowid(pColumn->a[i].zName) ){
297           keyColumn = i;
298         }else{
299           sqliteErrorMsg(pParse, "table %S has no column named %s",
300               pTabList, 0, pColumn->a[i].zName);
301           pParse->nErr++;
302           goto insert_cleanup;
303         }
304       }
305     }
306   }
307 
308   /* If there is no IDLIST term but the table has an integer primary
309   ** key, the set the keyColumn variable to the primary key column index
310   ** in the original table definition.
311   */
312   if( pColumn==0 ){
313     keyColumn = pTab->iPKey;
314   }
315 
316   /* Open the temp table for FOR EACH ROW triggers
317   */
318   if( row_triggers_exist ){
319     sqliteVdbeAddOp(v, OP_OpenPseudo, newIdx, 0);
320   }
321 
322   /* Initialize the count of rows to be inserted
323   */
324   if( db->flags & SQLITE_CountRows ){
325     iCntMem = pParse->nMem++;
326     sqliteVdbeAddOp(v, OP_Integer, 0, 0);
327     sqliteVdbeAddOp(v, OP_MemStore, iCntMem, 1);
328   }
329 
330   /* Open tables and indices if there are no row triggers */
331   if( !row_triggers_exist ){
332     base = pParse->nTab;
333     idx = sqliteOpenTableAndIndices(pParse, pTab, base);
334     pParse->nTab += idx;
335   }
336 
337   /* If the data source is a temporary table, then we have to create
338   ** a loop because there might be multiple rows of data.  If the data
339   ** source is a subroutine call from the SELECT statement, then we need
340   ** to launch the SELECT statement processing.
341   */
342   if( useTempTable ){
343     iBreak = sqliteVdbeMakeLabel(v);
344     sqliteVdbeAddOp(v, OP_Rewind, srcTab, iBreak);
345     iCont = sqliteVdbeCurrentAddr(v);
346   }else if( pSelect ){
347     sqliteVdbeAddOp(v, OP_Goto, 0, iSelectLoop);
348     sqliteVdbeResolveLabel(v, iInsertBlock);
349   }
350 
351   /* Run the BEFORE and INSTEAD OF triggers, if there are any
352   */
353   endOfLoop = sqliteVdbeMakeLabel(v);
354   if( before_triggers ){
355 
356     /* build the NEW.* reference row.  Note that if there is an INTEGER
357     ** PRIMARY KEY into which a NULL is being inserted, that NULL will be
358     ** translated into a unique ID for the row.  But on a BEFORE trigger,
359     ** we do not know what the unique ID will be (because the insert has
360     ** not happened yet) so we substitute a rowid of -1
361     */
362     if( keyColumn<0 ){
363       sqliteVdbeAddOp(v, OP_Integer, -1, 0);
364     }else if( useTempTable ){
365       sqliteVdbeAddOp(v, OP_Column, srcTab, keyColumn);
366     }else if( pSelect ){
367       sqliteVdbeAddOp(v, OP_Dup, nColumn - keyColumn - 1, 1);
368     }else{
369       sqliteExprCode(pParse, pList->a[keyColumn].pExpr);
370       sqliteVdbeAddOp(v, OP_NotNull, -1, sqliteVdbeCurrentAddr(v)+3);
371       sqliteVdbeAddOp(v, OP_Pop, 1, 0);
372       sqliteVdbeAddOp(v, OP_Integer, -1, 0);
373       sqliteVdbeAddOp(v, OP_MustBeInt, 0, 0);
374     }
375 
376     /* Create the new column data
377     */
378     for(i=0; i<pTab->nCol; i++){
379       if( pColumn==0 ){
380         j = i;
381       }else{
382         for(j=0; j<pColumn->nId; j++){
383           if( pColumn->a[j].idx==i ) break;
384         }
385       }
386       if( pColumn && j>=pColumn->nId ){
387         sqliteVdbeOp3(v, OP_String, 0, 0, pTab->aCol[i].zDflt, P3_STATIC);
388       }else if( useTempTable ){
389         sqliteVdbeAddOp(v, OP_Column, srcTab, j);
390       }else if( pSelect ){
391         sqliteVdbeAddOp(v, OP_Dup, nColumn-j-1, 1);
392       }else{
393         sqliteExprCode(pParse, pList->a[j].pExpr);
394       }
395     }
396     sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
397     sqliteVdbeAddOp(v, OP_PutIntKey, newIdx, 0);
398 
399     /* Fire BEFORE or INSTEAD OF triggers */
400     if( sqliteCodeRowTrigger(pParse, TK_INSERT, 0, TK_BEFORE, pTab,
401         newIdx, -1, onError, endOfLoop) ){
402       goto insert_cleanup;
403     }
404   }
405 
406   /* If any triggers exists, the opening of tables and indices is deferred
407   ** until now.
408   */
409   if( row_triggers_exist && !isView ){
410     base = pParse->nTab;
411     idx = sqliteOpenTableAndIndices(pParse, pTab, base);
412     pParse->nTab += idx;
413   }
414 
415   /* Push the record number for the new entry onto the stack.  The
416   ** record number is a randomly generate integer created by NewRecno
417   ** except when the table has an INTEGER PRIMARY KEY column, in which
418   ** case the record number is the same as that column.
419   */
420   if( !isView ){
421     if( keyColumn>=0 ){
422       if( useTempTable ){
423         sqliteVdbeAddOp(v, OP_Column, srcTab, keyColumn);
424       }else if( pSelect ){
425         sqliteVdbeAddOp(v, OP_Dup, nColumn - keyColumn - 1, 1);
426       }else{
427         sqliteExprCode(pParse, pList->a[keyColumn].pExpr);
428       }
429       /* If the PRIMARY KEY expression is NULL, then use OP_NewRecno
430       ** to generate a unique primary key value.
431       */
432       sqliteVdbeAddOp(v, OP_NotNull, -1, sqliteVdbeCurrentAddr(v)+3);
433       sqliteVdbeAddOp(v, OP_Pop, 1, 0);
434       sqliteVdbeAddOp(v, OP_NewRecno, base, 0);
435       sqliteVdbeAddOp(v, OP_MustBeInt, 0, 0);
436     }else{
437       sqliteVdbeAddOp(v, OP_NewRecno, base, 0);
438     }
439 
440     /* Push onto the stack, data for all columns of the new entry, beginning
441     ** with the first column.
442     */
443     for(i=0; i<pTab->nCol; i++){
444       if( i==pTab->iPKey ){
445         /* The value of the INTEGER PRIMARY KEY column is always a NULL.
446         ** Whenever this column is read, the record number will be substituted
447         ** in its place.  So will fill this column with a NULL to avoid
448         ** taking up data space with information that will never be used. */
449         sqliteVdbeAddOp(v, OP_String, 0, 0);
450         continue;
451       }
452       if( pColumn==0 ){
453         j = i;
454       }else{
455         for(j=0; j<pColumn->nId; j++){
456           if( pColumn->a[j].idx==i ) break;
457         }
458       }
459       if( pColumn && j>=pColumn->nId ){
460         sqliteVdbeOp3(v, OP_String, 0, 0, pTab->aCol[i].zDflt, P3_STATIC);
461       }else if( useTempTable ){
462         sqliteVdbeAddOp(v, OP_Column, srcTab, j);
463       }else if( pSelect ){
464         sqliteVdbeAddOp(v, OP_Dup, i+nColumn-j, 1);
465       }else{
466         sqliteExprCode(pParse, pList->a[j].pExpr);
467       }
468     }
469 
470     /* Generate code to check constraints and generate index keys and
471     ** do the insertion.
472     */
473     sqliteGenerateConstraintChecks(pParse, pTab, base, 0, keyColumn>=0,
474                                    0, onError, endOfLoop);
475     sqliteCompleteInsertion(pParse, pTab, base, 0,0,0,
476                             after_triggers ? newIdx : -1);
477   }
478 
479   /* Update the count of rows that are inserted
480   */
481   if( (db->flags & SQLITE_CountRows)!=0 ){
482     sqliteVdbeAddOp(v, OP_MemIncr, iCntMem, 0);
483   }
484 
485   if( row_triggers_exist ){
486     /* Close all tables opened */
487     if( !isView ){
488       sqliteVdbeAddOp(v, OP_Close, base, 0);
489       for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
490         sqliteVdbeAddOp(v, OP_Close, idx+base, 0);
491       }
492     }
493 
494     /* Code AFTER triggers */
495     if( sqliteCodeRowTrigger(pParse, TK_INSERT, 0, TK_AFTER, pTab, newIdx, -1,
496           onError, endOfLoop) ){
497       goto insert_cleanup;
498     }
499   }
500 
501   /* The bottom of the loop, if the data source is a SELECT statement
502   */
503   sqliteVdbeResolveLabel(v, endOfLoop);
504   if( useTempTable ){
505     sqliteVdbeAddOp(v, OP_Next, srcTab, iCont);
506     sqliteVdbeResolveLabel(v, iBreak);
507     sqliteVdbeAddOp(v, OP_Close, srcTab, 0);
508   }else if( pSelect ){
509     sqliteVdbeAddOp(v, OP_Pop, nColumn, 0);
510     sqliteVdbeAddOp(v, OP_Return, 0, 0);
511     sqliteVdbeResolveLabel(v, iCleanup);
512   }
513 
514   if( !row_triggers_exist ){
515     /* Close all tables opened */
516     sqliteVdbeAddOp(v, OP_Close, base, 0);
517     for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
518       sqliteVdbeAddOp(v, OP_Close, idx+base, 0);
519     }
520   }
521 
522   sqliteVdbeAddOp(v, OP_SetCounts, 0, 0);
523   sqliteEndWriteOperation(pParse);
524 
525   /*
526   ** Return the number of rows inserted.
527   */
528   if( db->flags & SQLITE_CountRows ){
529     sqliteVdbeOp3(v, OP_ColumnName, 0, 1, "rows inserted", P3_STATIC);
530     sqliteVdbeAddOp(v, OP_MemLoad, iCntMem, 0);
531     sqliteVdbeAddOp(v, OP_Callback, 1, 0);
532   }
533 
534 insert_cleanup:
535   sqliteSrcListDelete(pTabList);
536   if( pList ) sqliteExprListDelete(pList);
537   if( pSelect ) sqliteSelectDelete(pSelect);
538   sqliteIdListDelete(pColumn);
539 }
540 
541 /*
542 ** Generate code to do a constraint check prior to an INSERT or an UPDATE.
543 **
544 ** When this routine is called, the stack contains (from bottom to top)
545 ** the following values:
546 **
547 **    1.  The recno of the row to be updated before the update.  This
548 **        value is omitted unless we are doing an UPDATE that involves a
549 **        change to the record number.
550 **
551 **    2.  The recno of the row after the update.
552 **
553 **    3.  The data in the first column of the entry after the update.
554 **
555 **    i.  Data from middle columns...
556 **
557 **    N.  The data in the last column of the entry after the update.
558 **
559 ** The old recno shown as entry (1) above is omitted unless both isUpdate
560 ** and recnoChng are 1.  isUpdate is true for UPDATEs and false for
561 ** INSERTs and recnoChng is true if the record number is being changed.
562 **
563 ** The code generated by this routine pushes additional entries onto
564 ** the stack which are the keys for new index entries for the new record.
565 ** The order of index keys is the same as the order of the indices on
566 ** the pTable->pIndex list.  A key is only created for index i if
567 ** aIdxUsed!=0 and aIdxUsed[i]!=0.
568 **
569 ** This routine also generates code to check constraints.  NOT NULL,
570 ** CHECK, and UNIQUE constraints are all checked.  If a constraint fails,
571 ** then the appropriate action is performed.  There are five possible
572 ** actions: ROLLBACK, ABORT, FAIL, REPLACE, and IGNORE.
573 **
574 **  Constraint type  Action       What Happens
575 **  ---------------  ----------   ----------------------------------------
576 **  any              ROLLBACK     The current transaction is rolled back and
577 **                                sqlite_exec() returns immediately with a
578 **                                return code of SQLITE_CONSTRAINT.
579 **
580 **  any              ABORT        Back out changes from the current command
581 **                                only (do not do a complete rollback) then
582 **                                cause sqlite_exec() to return immediately
583 **                                with SQLITE_CONSTRAINT.
584 **
585 **  any              FAIL         Sqlite_exec() returns immediately with a
586 **                                return code of SQLITE_CONSTRAINT.  The
587 **                                transaction is not rolled back and any
588 **                                prior changes are retained.
589 **
590 **  any              IGNORE       The record number and data is popped from
591 **                                the stack and there is an immediate jump
592 **                                to label ignoreDest.
593 **
594 **  NOT NULL         REPLACE      The NULL value is replace by the default
595 **                                value for that column.  If the default value
596 **                                is NULL, the action is the same as ABORT.
597 **
598 **  UNIQUE           REPLACE      The other row that conflicts with the row
599 **                                being inserted is removed.
600 **
601 **  CHECK            REPLACE      Illegal.  The results in an exception.
602 **
603 ** Which action to take is determined by the overrideError parameter.
604 ** Or if overrideError==OE_Default, then the pParse->onError parameter
605 ** is used.  Or if pParse->onError==OE_Default then the onError value
606 ** for the constraint is used.
607 **
608 ** The calling routine must open a read/write cursor for pTab with
609 ** cursor number "base".  All indices of pTab must also have open
610 ** read/write cursors with cursor number base+i for the i-th cursor.
611 ** Except, if there is no possibility of a REPLACE action then
612 ** cursors do not need to be open for indices where aIdxUsed[i]==0.
613 **
614 ** If the isUpdate flag is true, it means that the "base" cursor is
615 ** initially pointing to an entry that is being updated.  The isUpdate
616 ** flag causes extra code to be generated so that the "base" cursor
617 ** is still pointing at the same entry after the routine returns.
618 ** Without the isUpdate flag, the "base" cursor might be moved.
619 */
sqliteGenerateConstraintChecks(Parse * pParse,Table * pTab,int base,char * aIdxUsed,int recnoChng,int isUpdate,int overrideError,int ignoreDest)620 void sqliteGenerateConstraintChecks(
621   Parse *pParse,      /* The parser context */
622   Table *pTab,        /* the table into which we are inserting */
623   int base,           /* Index of a read/write cursor pointing at pTab */
624   char *aIdxUsed,     /* Which indices are used.  NULL means all are used */
625   int recnoChng,      /* True if the record number will change */
626   int isUpdate,       /* True for UPDATE, False for INSERT */
627   int overrideError,  /* Override onError to this if not OE_Default */
628   int ignoreDest      /* Jump to this label on an OE_Ignore resolution */
629 ){
630   int i;
631   Vdbe *v;
632   int nCol;
633   int onError;
634   int addr;
635   int extra;
636   int iCur;
637   Index *pIdx;
638   int seenReplace = 0;
639   int jumpInst1, jumpInst2;
640   int contAddr;
641   int hasTwoRecnos = (isUpdate && recnoChng);
642 
643   v = sqliteGetVdbe(pParse);
644   assert( v!=0 );
645   assert( pTab->pSelect==0 );  /* This table is not a VIEW */
646   nCol = pTab->nCol;
647 
648   /* Test all NOT NULL constraints.
649   */
650   for(i=0; i<nCol; i++){
651     if( i==pTab->iPKey ){
652       continue;
653     }
654     onError = pTab->aCol[i].notNull;
655     if( onError==OE_None ) continue;
656     if( overrideError!=OE_Default ){
657       onError = overrideError;
658     }else if( pParse->db->onError!=OE_Default ){
659       onError = pParse->db->onError;
660     }else if( onError==OE_Default ){
661       onError = OE_Abort;
662     }
663     if( onError==OE_Replace && pTab->aCol[i].zDflt==0 ){
664       onError = OE_Abort;
665     }
666     sqliteVdbeAddOp(v, OP_Dup, nCol-1-i, 1);
667     addr = sqliteVdbeAddOp(v, OP_NotNull, 1, 0);
668     switch( onError ){
669       case OE_Rollback:
670       case OE_Abort:
671       case OE_Fail: {
672         char *zMsg = 0;
673         sqliteVdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, onError);
674         sqliteSetString(&zMsg, pTab->zName, ".", pTab->aCol[i].zName,
675                         " may not be NULL", (char*)0);
676         sqliteVdbeChangeP3(v, -1, zMsg, P3_DYNAMIC);
677         break;
678       }
679       case OE_Ignore: {
680         sqliteVdbeAddOp(v, OP_Pop, nCol+1+hasTwoRecnos, 0);
681         sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
682         break;
683       }
684       case OE_Replace: {
685         sqliteVdbeOp3(v, OP_String, 0, 0, pTab->aCol[i].zDflt, P3_STATIC);
686         sqliteVdbeAddOp(v, OP_Push, nCol-i, 0);
687         break;
688       }
689       default: assert(0);
690     }
691     sqliteVdbeChangeP2(v, addr, sqliteVdbeCurrentAddr(v));
692   }
693 
694   /* Test all CHECK constraints
695   */
696   /**** TBD ****/
697 
698   /* If we have an INTEGER PRIMARY KEY, make sure the primary key
699   ** of the new record does not previously exist.  Except, if this
700   ** is an UPDATE and the primary key is not changing, that is OK.
701   */
702   if( recnoChng ){
703     onError = pTab->keyConf;
704     if( overrideError!=OE_Default ){
705       onError = overrideError;
706     }else if( pParse->db->onError!=OE_Default ){
707       onError = pParse->db->onError;
708     }else if( onError==OE_Default ){
709       onError = OE_Abort;
710     }
711 
712     if( isUpdate ){
713       sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
714       sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
715       jumpInst1 = sqliteVdbeAddOp(v, OP_Eq, 0, 0);
716     }
717     sqliteVdbeAddOp(v, OP_Dup, nCol, 1);
718     jumpInst2 = sqliteVdbeAddOp(v, OP_NotExists, base, 0);
719     switch( onError ){
720       default: {
721         onError = OE_Abort;
722       }
723       /* FALLTHROUGH */
724       case OE_Rollback:
725       case OE_Abort:
726       case OE_Fail: {
727         sqliteVdbeOp3(v, OP_Halt, SQLITE_CONSTRAINT, onError,
728                          "PRIMARY KEY must be unique", P3_STATIC);
729         break;
730       }
731       case OE_Replace: {
732         sqliteGenerateRowIndexDelete(pParse->db, v, pTab, base, 0);
733         if( isUpdate ){
734           sqliteVdbeAddOp(v, OP_Dup, nCol+hasTwoRecnos, 1);
735           sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
736         }
737         seenReplace = 1;
738         break;
739       }
740       case OE_Ignore: {
741         assert( seenReplace==0 );
742         sqliteVdbeAddOp(v, OP_Pop, nCol+1+hasTwoRecnos, 0);
743         sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
744         break;
745       }
746     }
747     contAddr = sqliteVdbeCurrentAddr(v);
748     sqliteVdbeChangeP2(v, jumpInst2, contAddr);
749     if( isUpdate ){
750       sqliteVdbeChangeP2(v, jumpInst1, contAddr);
751       sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
752       sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
753     }
754   }
755 
756   /* Test all UNIQUE constraints by creating entries for each UNIQUE
757   ** index and making sure that duplicate entries do not already exist.
758   ** Add the new records to the indices as we go.
759   */
760   extra = -1;
761   for(iCur=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, iCur++){
762     if( aIdxUsed && aIdxUsed[iCur]==0 ) continue;  /* Skip unused indices */
763     extra++;
764 
765     /* Create a key for accessing the index entry */
766     sqliteVdbeAddOp(v, OP_Dup, nCol+extra, 1);
767     for(i=0; i<pIdx->nColumn; i++){
768       int idx = pIdx->aiColumn[i];
769       if( idx==pTab->iPKey ){
770         sqliteVdbeAddOp(v, OP_Dup, i+extra+nCol+1, 1);
771       }else{
772         sqliteVdbeAddOp(v, OP_Dup, i+extra+nCol-idx, 1);
773       }
774     }
775     jumpInst1 = sqliteVdbeAddOp(v, OP_MakeIdxKey, pIdx->nColumn, 0);
776     if( pParse->db->file_format>=4 ) sqliteAddIdxKeyType(v, pIdx);
777 
778     /* Find out what action to take in case there is an indexing conflict */
779     onError = pIdx->onError;
780     if( onError==OE_None ) continue;  /* pIdx is not a UNIQUE index */
781     if( overrideError!=OE_Default ){
782       onError = overrideError;
783     }else if( pParse->db->onError!=OE_Default ){
784       onError = pParse->db->onError;
785     }else if( onError==OE_Default ){
786       onError = OE_Abort;
787     }
788     if( seenReplace ){
789       if( onError==OE_Ignore ) onError = OE_Replace;
790       else if( onError==OE_Fail ) onError = OE_Abort;
791     }
792 
793 
794     /* Check to see if the new index entry will be unique */
795     sqliteVdbeAddOp(v, OP_Dup, extra+nCol+1+hasTwoRecnos, 1);
796     jumpInst2 = sqliteVdbeAddOp(v, OP_IsUnique, base+iCur+1, 0);
797 
798     /* Generate code that executes if the new index entry is not unique */
799     switch( onError ){
800       case OE_Rollback:
801       case OE_Abort:
802       case OE_Fail: {
803         int j, n1, n2;
804         char zErrMsg[200];
805         strcpy(zErrMsg, pIdx->nColumn>1 ? "columns " : "column ");
806         n1 = strlen(zErrMsg);
807         for(j=0; j<pIdx->nColumn && n1<sizeof(zErrMsg)-30; j++){
808           char *zCol = pTab->aCol[pIdx->aiColumn[j]].zName;
809           n2 = strlen(zCol);
810           if( j>0 ){
811             strcpy(&zErrMsg[n1], ", ");
812             n1 += 2;
813           }
814           if( n1+n2>sizeof(zErrMsg)-30 ){
815             strcpy(&zErrMsg[n1], "...");
816             n1 += 3;
817             break;
818           }else{
819             strcpy(&zErrMsg[n1], zCol);
820             n1 += n2;
821           }
822         }
823         strcpy(&zErrMsg[n1],
824             pIdx->nColumn>1 ? " are not unique" : " is not unique");
825         sqliteVdbeOp3(v, OP_Halt, SQLITE_CONSTRAINT, onError, zErrMsg, 0);
826         break;
827       }
828       case OE_Ignore: {
829         assert( seenReplace==0 );
830         sqliteVdbeAddOp(v, OP_Pop, nCol+extra+3+hasTwoRecnos, 0);
831         sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
832         break;
833       }
834       case OE_Replace: {
835         sqliteGenerateRowDelete(pParse->db, v, pTab, base, 0);
836         if( isUpdate ){
837           sqliteVdbeAddOp(v, OP_Dup, nCol+extra+1+hasTwoRecnos, 1);
838           sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
839         }
840         seenReplace = 1;
841         break;
842       }
843       default: assert(0);
844     }
845     contAddr = sqliteVdbeCurrentAddr(v);
846 #if NULL_DISTINCT_FOR_UNIQUE
847     sqliteVdbeChangeP2(v, jumpInst1, contAddr);
848 #endif
849     sqliteVdbeChangeP2(v, jumpInst2, contAddr);
850   }
851 }
852 
853 /*
854 ** This routine generates code to finish the INSERT or UPDATE operation
855 ** that was started by a prior call to sqliteGenerateConstraintChecks.
856 ** The stack must contain keys for all active indices followed by data
857 ** and the recno for the new entry.  This routine creates the new
858 ** entries in all indices and in the main table.
859 **
860 ** The arguments to this routine should be the same as the first six
861 ** arguments to sqliteGenerateConstraintChecks.
862 */
sqliteCompleteInsertion(Parse * pParse,Table * pTab,int base,char * aIdxUsed,int recnoChng,int isUpdate,int newIdx)863 void sqliteCompleteInsertion(
864   Parse *pParse,      /* The parser context */
865   Table *pTab,        /* the table into which we are inserting */
866   int base,           /* Index of a read/write cursor pointing at pTab */
867   char *aIdxUsed,     /* Which indices are used.  NULL means all are used */
868   int recnoChng,      /* True if the record number will change */
869   int isUpdate,       /* True for UPDATE, False for INSERT */
870   int newIdx          /* Index of NEW table for triggers.  -1 if none */
871 ){
872   int i;
873   Vdbe *v;
874   int nIdx;
875   Index *pIdx;
876 
877   v = sqliteGetVdbe(pParse);
878   assert( v!=0 );
879   assert( pTab->pSelect==0 );  /* This table is not a VIEW */
880   for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){}
881   for(i=nIdx-1; i>=0; i--){
882     if( aIdxUsed && aIdxUsed[i]==0 ) continue;
883     sqliteVdbeAddOp(v, OP_IdxPut, base+i+1, 0);
884   }
885   sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
886   if( newIdx>=0 ){
887     sqliteVdbeAddOp(v, OP_Dup, 1, 0);
888     sqliteVdbeAddOp(v, OP_Dup, 1, 0);
889     sqliteVdbeAddOp(v, OP_PutIntKey, newIdx, 0);
890   }
891   sqliteVdbeAddOp(v, OP_PutIntKey, base,
892     (pParse->trigStack?0:OPFLAG_NCHANGE) |
893     (isUpdate?0:OPFLAG_LASTROWID) | OPFLAG_CSCHANGE);
894   if( isUpdate && recnoChng ){
895     sqliteVdbeAddOp(v, OP_Pop, 1, 0);
896   }
897 }
898 
899 /*
900 ** Generate code that will open write cursors for a table and for all
901 ** indices of that table.  The "base" parameter is the cursor number used
902 ** for the table.  Indices are opened on subsequent cursors.
903 **
904 ** Return the total number of cursors opened.  This is always at least
905 ** 1 (for the main table) plus more for each cursor.
906 */
sqliteOpenTableAndIndices(Parse * pParse,Table * pTab,int base)907 int sqliteOpenTableAndIndices(Parse *pParse, Table *pTab, int base){
908   int i;
909   Index *pIdx;
910   Vdbe *v = sqliteGetVdbe(pParse);
911   assert( v!=0 );
912   sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
913   sqliteVdbeOp3(v, OP_OpenWrite, base, pTab->tnum, pTab->zName, P3_STATIC);
914   for(i=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
915     sqliteVdbeAddOp(v, OP_Integer, pIdx->iDb, 0);
916     sqliteVdbeOp3(v, OP_OpenWrite, i+base, pIdx->tnum, pIdx->zName, P3_STATIC);
917   }
918   return i;
919 }
920