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