Annotation of embedaddon/php/ext/sqlite/libsqlite/src/insert.c, revision 1.1.1.1
1.1 misho 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 195361 2005-09-07 15:11:33Z iliaa $
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: */
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: */
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: /* Fall thru into the next case */
723: }
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: */
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: */
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: }
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to insert.c CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / php / ext / sqlite / libsqlite / src