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 261 : ){
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 261 : 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 261 : 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 261 : int newIdx = -1; /* Cursor for the NEW table */
118 :
119 261 : if( pParse->nErr || sqlite_malloc_failed ) goto insert_cleanup;
120 261 : db = pParse->db;
121 :
122 : /* Locate the table into which we will be inserting new information.
123 : */
124 : assert( pTabList->nSrc==1 );
125 261 : zTab = pTabList->a[0].zName;
126 261 : if( zTab==0 ) goto insert_cleanup;
127 261 : pTab = sqliteSrcListLookup(pParse, pTabList);
128 261 : if( pTab==0 ){
129 0 : goto insert_cleanup;
130 : }
131 : assert( pTab->iDb<db->nDb );
132 261 : zDb = db->aDb[pTab->iDb].zName;
133 261 : if( sqliteAuthCheck(pParse, SQLITE_INSERT, pTab->zName, 0, zDb) ){
134 0 : 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 261 : before_triggers = sqliteTriggersExist(pParse, pTab->pTrigger, TK_INSERT,
142 : TK_BEFORE, TK_ROW, 0);
143 261 : after_triggers = sqliteTriggersExist(pParse, pTab->pTrigger, TK_INSERT,
144 : TK_AFTER, TK_ROW, 0);
145 261 : row_triggers_exist = before_triggers || after_triggers;
146 261 : isView = pTab->pSelect!=0;
147 261 : if( sqliteIsReadOnly(pParse, pTab, before_triggers) ){
148 0 : goto insert_cleanup;
149 : }
150 261 : if( pTab==0 ) goto insert_cleanup;
151 :
152 : /* If pTab is really a view, make sure it has been initialized.
153 : */
154 261 : if( isView && sqliteViewGetColumnNames(pParse, pTab) ){
155 0 : goto insert_cleanup;
156 : }
157 :
158 : /* Allocate a VDBE
159 : */
160 261 : v = sqliteGetVdbe(pParse);
161 261 : if( v==0 ) goto insert_cleanup;
162 261 : sqliteBeginWriteOperation(pParse, pSelect || row_triggers_exist, pTab->iDb);
163 :
164 : /* if there are row triggers, allocate a temp table for new.* references. */
165 261 : if( row_triggers_exist ){
166 0 : 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 261 : if( pSelect ){
178 : /* Data is coming from a SELECT. Generate code to implement that SELECT
179 : */
180 : int rc, iInitCode;
181 0 : iInitCode = sqliteVdbeAddOp(v, OP_Goto, 0, 0);
182 0 : iSelectLoop = sqliteVdbeCurrentAddr(v);
183 0 : iInsertBlock = sqliteVdbeMakeLabel(v);
184 0 : rc = sqliteSelect(pParse, pSelect, SRT_Subroutine, iInsertBlock, 0,0,0);
185 0 : if( rc || pParse->nErr || sqlite_malloc_failed ) goto insert_cleanup;
186 0 : iCleanup = sqliteVdbeMakeLabel(v);
187 0 : sqliteVdbeAddOp(v, OP_Goto, 0, iCleanup);
188 : assert( pSelect->pEList );
189 0 : 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 0 : if( row_triggers_exist ){
200 0 : useTempTable = 1;
201 : }else{
202 0 : int addr = sqliteVdbeFindOp(v, OP_OpenRead, pTab->tnum);
203 0 : useTempTable = 0;
204 0 : if( addr>0 ){
205 0 : VdbeOp *pOp = sqliteVdbeGetOp(v, addr-2);
206 0 : if( pOp->opcode==OP_Integer && pOp->p1==pTab->iDb ){
207 0 : useTempTable = 1;
208 : }
209 : }
210 : }
211 :
212 0 : 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 0 : srcTab = pParse->nTab++;
217 0 : sqliteVdbeResolveLabel(v, iInsertBlock);
218 0 : sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0);
219 0 : sqliteVdbeAddOp(v, OP_NewRecno, srcTab, 0);
220 0 : sqliteVdbeAddOp(v, OP_Pull, 1, 0);
221 0 : sqliteVdbeAddOp(v, OP_PutIntKey, srcTab, 0);
222 0 : 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 0 : sqliteVdbeChangeP2(v, iInitCode, sqliteVdbeCurrentAddr(v));
229 0 : sqliteVdbeAddOp(v, OP_OpenTemp, srcTab, 0);
230 0 : sqliteVdbeAddOp(v, OP_Goto, 0, iSelectLoop);
231 0 : sqliteVdbeResolveLabel(v, iCleanup);
232 : }else{
233 0 : 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 261 : srcTab = -1;
242 261 : useTempTable = 0;
243 : assert( pList );
244 261 : nColumn = pList->nExpr;
245 261 : dummy.nSrc = 0;
246 791 : for(i=0; i<nColumn; i++){
247 530 : if( sqliteExprResolveIds(pParse, &dummy, 0, pList->a[i].pExpr) ){
248 0 : goto insert_cleanup;
249 : }
250 530 : if( sqliteExprCheck(pParse, pList->a[i].pExpr, 0, 0) ){
251 0 : 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 261 : if( pColumn==0 && nColumn!=pTab->nCol ){
260 0 : sqliteErrorMsg(pParse,
261 : "table %S has %d columns but %d values were supplied",
262 : pTabList, 0, pTab->nCol, nColumn);
263 0 : goto insert_cleanup;
264 : }
265 261 : if( pColumn!=0 && nColumn!=pColumn->nId ){
266 0 : sqliteErrorMsg(pParse, "%d values for %d columns", nColumn, pColumn->nId);
267 0 : 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 261 : if( pColumn ){
282 119 : for(i=0; i<pColumn->nId; i++){
283 74 : pColumn->a[i].idx = -1;
284 : }
285 119 : for(i=0; i<pColumn->nId; i++){
286 136 : for(j=0; j<pTab->nCol; j++){
287 136 : if( sqliteStrICmp(pColumn->a[i].zName, pTab->aCol[j].zName)==0 ){
288 74 : pColumn->a[i].idx = j;
289 74 : if( j==pTab->iPKey ){
290 3 : keyColumn = i;
291 : }
292 74 : break;
293 : }
294 : }
295 74 : if( j>=pTab->nCol ){
296 0 : if( sqliteIsRowid(pColumn->a[i].zName) ){
297 0 : keyColumn = i;
298 : }else{
299 0 : sqliteErrorMsg(pParse, "table %S has no column named %s",
300 : pTabList, 0, pColumn->a[i].zName);
301 0 : pParse->nErr++;
302 0 : 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 261 : if( pColumn==0 ){
313 216 : keyColumn = pTab->iPKey;
314 : }
315 :
316 : /* Open the temp table for FOR EACH ROW triggers
317 : */
318 261 : if( row_triggers_exist ){
319 0 : sqliteVdbeAddOp(v, OP_OpenPseudo, newIdx, 0);
320 : }
321 :
322 : /* Initialize the count of rows to be inserted
323 : */
324 261 : if( db->flags & SQLITE_CountRows ){
325 0 : iCntMem = pParse->nMem++;
326 0 : sqliteVdbeAddOp(v, OP_Integer, 0, 0);
327 0 : sqliteVdbeAddOp(v, OP_MemStore, iCntMem, 1);
328 : }
329 :
330 : /* Open tables and indices if there are no row triggers */
331 261 : if( !row_triggers_exist ){
332 261 : base = pParse->nTab;
333 261 : idx = sqliteOpenTableAndIndices(pParse, pTab, base);
334 261 : 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 261 : if( useTempTable ){
343 0 : iBreak = sqliteVdbeMakeLabel(v);
344 0 : sqliteVdbeAddOp(v, OP_Rewind, srcTab, iBreak);
345 0 : iCont = sqliteVdbeCurrentAddr(v);
346 261 : }else if( pSelect ){
347 0 : sqliteVdbeAddOp(v, OP_Goto, 0, iSelectLoop);
348 0 : sqliteVdbeResolveLabel(v, iInsertBlock);
349 : }
350 :
351 : /* Run the BEFORE and INSTEAD OF triggers, if there are any
352 : */
353 261 : endOfLoop = sqliteVdbeMakeLabel(v);
354 261 : 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 0 : if( keyColumn<0 ){
363 0 : sqliteVdbeAddOp(v, OP_Integer, -1, 0);
364 0 : }else if( useTempTable ){
365 0 : sqliteVdbeAddOp(v, OP_Column, srcTab, keyColumn);
366 0 : }else if( pSelect ){
367 0 : sqliteVdbeAddOp(v, OP_Dup, nColumn - keyColumn - 1, 1);
368 : }else{
369 0 : sqliteExprCode(pParse, pList->a[keyColumn].pExpr);
370 0 : sqliteVdbeAddOp(v, OP_NotNull, -1, sqliteVdbeCurrentAddr(v)+3);
371 0 : sqliteVdbeAddOp(v, OP_Pop, 1, 0);
372 0 : sqliteVdbeAddOp(v, OP_Integer, -1, 0);
373 0 : sqliteVdbeAddOp(v, OP_MustBeInt, 0, 0);
374 : }
375 :
376 : /* Create the new column data
377 : */
378 0 : for(i=0; i<pTab->nCol; i++){
379 0 : if( pColumn==0 ){
380 0 : j = i;
381 : }else{
382 0 : for(j=0; j<pColumn->nId; j++){
383 0 : if( pColumn->a[j].idx==i ) break;
384 : }
385 : }
386 0 : if( pColumn && j>=pColumn->nId ){
387 0 : sqliteVdbeOp3(v, OP_String, 0, 0, pTab->aCol[i].zDflt, P3_STATIC);
388 0 : }else if( useTempTable ){
389 0 : sqliteVdbeAddOp(v, OP_Column, srcTab, j);
390 0 : }else if( pSelect ){
391 0 : sqliteVdbeAddOp(v, OP_Dup, nColumn-j-1, 1);
392 : }else{
393 0 : sqliteExprCode(pParse, pList->a[j].pExpr);
394 : }
395 : }
396 0 : sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
397 0 : sqliteVdbeAddOp(v, OP_PutIntKey, newIdx, 0);
398 :
399 : /* Fire BEFORE or INSTEAD OF triggers */
400 0 : if( sqliteCodeRowTrigger(pParse, TK_INSERT, 0, TK_BEFORE, pTab,
401 : newIdx, -1, onError, endOfLoop) ){
402 0 : goto insert_cleanup;
403 : }
404 : }
405 :
406 : /* If any triggers exists, the opening of tables and indices is deferred
407 : ** until now.
408 : */
409 261 : if( row_triggers_exist && !isView ){
410 0 : base = pParse->nTab;
411 0 : idx = sqliteOpenTableAndIndices(pParse, pTab, base);
412 0 : 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 261 : if( !isView ){
421 261 : if( keyColumn>=0 ){
422 3 : if( useTempTable ){
423 0 : sqliteVdbeAddOp(v, OP_Column, srcTab, keyColumn);
424 3 : }else if( pSelect ){
425 0 : sqliteVdbeAddOp(v, OP_Dup, nColumn - keyColumn - 1, 1);
426 : }else{
427 3 : 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 3 : sqliteVdbeAddOp(v, OP_NotNull, -1, sqliteVdbeCurrentAddr(v)+3);
433 3 : sqliteVdbeAddOp(v, OP_Pop, 1, 0);
434 3 : sqliteVdbeAddOp(v, OP_NewRecno, base, 0);
435 3 : sqliteVdbeAddOp(v, OP_MustBeInt, 0, 0);
436 : }else{
437 258 : 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 813 : for(i=0; i<pTab->nCol; i++){
444 552 : 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 25 : sqliteVdbeAddOp(v, OP_String, 0, 0);
450 25 : continue;
451 : }
452 527 : if( pColumn==0 ){
453 456 : j = i;
454 : }else{
455 111 : for(j=0; j<pColumn->nId; j++){
456 111 : if( pColumn->a[j].idx==i ) break;
457 : }
458 : }
459 527 : if( pColumn && j>=pColumn->nId ){
460 0 : sqliteVdbeOp3(v, OP_String, 0, 0, pTab->aCol[i].zDflt, P3_STATIC);
461 527 : }else if( useTempTable ){
462 0 : sqliteVdbeAddOp(v, OP_Column, srcTab, j);
463 527 : }else if( pSelect ){
464 0 : sqliteVdbeAddOp(v, OP_Dup, i+nColumn-j, 1);
465 : }else{
466 527 : 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 261 : sqliteGenerateConstraintChecks(pParse, pTab, base, 0, keyColumn>=0,
474 : 0, onError, endOfLoop);
475 261 : 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 261 : if( (db->flags & SQLITE_CountRows)!=0 ){
482 0 : sqliteVdbeAddOp(v, OP_MemIncr, iCntMem, 0);
483 : }
484 :
485 261 : if( row_triggers_exist ){
486 : /* Close all tables opened */
487 0 : if( !isView ){
488 0 : sqliteVdbeAddOp(v, OP_Close, base, 0);
489 0 : for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
490 0 : sqliteVdbeAddOp(v, OP_Close, idx+base, 0);
491 : }
492 : }
493 :
494 : /* Code AFTER triggers */
495 0 : if( sqliteCodeRowTrigger(pParse, TK_INSERT, 0, TK_AFTER, pTab, newIdx, -1,
496 : onError, endOfLoop) ){
497 0 : goto insert_cleanup;
498 : }
499 : }
500 :
501 : /* The bottom of the loop, if the data source is a SELECT statement
502 : */
503 261 : sqliteVdbeResolveLabel(v, endOfLoop);
504 261 : if( useTempTable ){
505 0 : sqliteVdbeAddOp(v, OP_Next, srcTab, iCont);
506 0 : sqliteVdbeResolveLabel(v, iBreak);
507 0 : sqliteVdbeAddOp(v, OP_Close, srcTab, 0);
508 261 : }else if( pSelect ){
509 0 : sqliteVdbeAddOp(v, OP_Pop, nColumn, 0);
510 0 : sqliteVdbeAddOp(v, OP_Return, 0, 0);
511 0 : sqliteVdbeResolveLabel(v, iCleanup);
512 : }
513 :
514 261 : if( !row_triggers_exist ){
515 : /* Close all tables opened */
516 261 : sqliteVdbeAddOp(v, OP_Close, base, 0);
517 406 : for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
518 145 : sqliteVdbeAddOp(v, OP_Close, idx+base, 0);
519 : }
520 : }
521 :
522 261 : sqliteVdbeAddOp(v, OP_SetCounts, 0, 0);
523 261 : sqliteEndWriteOperation(pParse);
524 :
525 : /*
526 : ** Return the number of rows inserted.
527 : */
528 261 : if( db->flags & SQLITE_CountRows ){
529 0 : sqliteVdbeOp3(v, OP_ColumnName, 0, 1, "rows inserted", P3_STATIC);
530 0 : sqliteVdbeAddOp(v, OP_MemLoad, iCntMem, 0);
531 0 : sqliteVdbeAddOp(v, OP_Callback, 1, 0);
532 : }
533 :
534 261 : insert_cleanup:
535 261 : sqliteSrcListDelete(pTabList);
536 261 : if( pList ) sqliteExprListDelete(pList);
537 261 : if( pSelect ) sqliteSelectDelete(pSelect);
538 261 : sqliteIdListDelete(pColumn);
539 261 : }
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 262 : ){
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 262 : int seenReplace = 0;
639 : int jumpInst1, jumpInst2;
640 : int contAddr;
641 262 : int hasTwoRecnos = (isUpdate && recnoChng);
642 :
643 262 : v = sqliteGetVdbe(pParse);
644 : assert( v!=0 );
645 : assert( pTab->pSelect==0 ); /* This table is not a VIEW */
646 262 : nCol = pTab->nCol;
647 :
648 : /* Test all NOT NULL constraints.
649 : */
650 815 : for(i=0; i<nCol; i++){
651 553 : if( i==pTab->iPKey ){
652 25 : continue;
653 : }
654 528 : onError = pTab->aCol[i].notNull;
655 528 : if( onError==OE_None ) continue;
656 122 : if( overrideError!=OE_Default ){
657 0 : onError = overrideError;
658 122 : }else if( pParse->db->onError!=OE_Default ){
659 0 : onError = pParse->db->onError;
660 122 : }else if( onError==OE_Default ){
661 122 : onError = OE_Abort;
662 : }
663 122 : if( onError==OE_Replace && pTab->aCol[i].zDflt==0 ){
664 0 : onError = OE_Abort;
665 : }
666 122 : sqliteVdbeAddOp(v, OP_Dup, nCol-1-i, 1);
667 122 : addr = sqliteVdbeAddOp(v, OP_NotNull, 1, 0);
668 122 : switch( onError ){
669 : case OE_Rollback:
670 : case OE_Abort:
671 : case OE_Fail: {
672 122 : char *zMsg = 0;
673 122 : sqliteVdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, onError);
674 122 : sqliteSetString(&zMsg, pTab->zName, ".", pTab->aCol[i].zName,
675 : " may not be NULL", (char*)0);
676 122 : sqliteVdbeChangeP3(v, -1, zMsg, P3_DYNAMIC);
677 122 : break;
678 : }
679 : case OE_Ignore: {
680 0 : sqliteVdbeAddOp(v, OP_Pop, nCol+1+hasTwoRecnos, 0);
681 0 : sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
682 0 : break;
683 : }
684 : case OE_Replace: {
685 0 : sqliteVdbeOp3(v, OP_String, 0, 0, pTab->aCol[i].zDflt, P3_STATIC);
686 0 : sqliteVdbeAddOp(v, OP_Push, nCol-i, 0);
687 : break;
688 : }
689 : default: assert(0);
690 : }
691 122 : 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 262 : if( recnoChng ){
703 3 : onError = pTab->keyConf;
704 3 : if( overrideError!=OE_Default ){
705 0 : onError = overrideError;
706 3 : }else if( pParse->db->onError!=OE_Default ){
707 0 : onError = pParse->db->onError;
708 3 : }else if( onError==OE_Default ){
709 3 : onError = OE_Abort;
710 : }
711 :
712 3 : if( isUpdate ){
713 0 : sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
714 0 : sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
715 0 : jumpInst1 = sqliteVdbeAddOp(v, OP_Eq, 0, 0);
716 : }
717 3 : sqliteVdbeAddOp(v, OP_Dup, nCol, 1);
718 3 : jumpInst2 = sqliteVdbeAddOp(v, OP_NotExists, base, 0);
719 3 : switch( onError ){
720 : default: {
721 0 : onError = OE_Abort;
722 : /* Fall thru into the next case */
723 : }
724 : case OE_Rollback:
725 : case OE_Abort:
726 : case OE_Fail: {
727 3 : sqliteVdbeOp3(v, OP_Halt, SQLITE_CONSTRAINT, onError,
728 : "PRIMARY KEY must be unique", P3_STATIC);
729 3 : break;
730 : }
731 : case OE_Replace: {
732 0 : sqliteGenerateRowIndexDelete(pParse->db, v, pTab, base, 0);
733 0 : if( isUpdate ){
734 0 : sqliteVdbeAddOp(v, OP_Dup, nCol+hasTwoRecnos, 1);
735 0 : sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
736 : }
737 0 : seenReplace = 1;
738 0 : break;
739 : }
740 : case OE_Ignore: {
741 : assert( seenReplace==0 );
742 0 : sqliteVdbeAddOp(v, OP_Pop, nCol+1+hasTwoRecnos, 0);
743 0 : sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
744 : break;
745 : }
746 : }
747 3 : contAddr = sqliteVdbeCurrentAddr(v);
748 3 : sqliteVdbeChangeP2(v, jumpInst2, contAddr);
749 3 : if( isUpdate ){
750 0 : sqliteVdbeChangeP2(v, jumpInst1, contAddr);
751 0 : sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
752 0 : 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 262 : extra = -1;
761 407 : for(iCur=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, iCur++){
762 145 : if( aIdxUsed && aIdxUsed[iCur]==0 ) continue; /* Skip unused indices */
763 145 : extra++;
764 :
765 : /* Create a key for accessing the index entry */
766 145 : sqliteVdbeAddOp(v, OP_Dup, nCol+extra, 1);
767 290 : for(i=0; i<pIdx->nColumn; i++){
768 145 : int idx = pIdx->aiColumn[i];
769 145 : if( idx==pTab->iPKey ){
770 0 : sqliteVdbeAddOp(v, OP_Dup, i+extra+nCol+1, 1);
771 : }else{
772 145 : sqliteVdbeAddOp(v, OP_Dup, i+extra+nCol-idx, 1);
773 : }
774 : }
775 145 : jumpInst1 = sqliteVdbeAddOp(v, OP_MakeIdxKey, pIdx->nColumn, 0);
776 145 : 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 145 : onError = pIdx->onError;
780 145 : if( onError==OE_None ) continue; /* pIdx is not a UNIQUE index */
781 145 : if( overrideError!=OE_Default ){
782 0 : onError = overrideError;
783 145 : }else if( pParse->db->onError!=OE_Default ){
784 0 : onError = pParse->db->onError;
785 145 : }else if( onError==OE_Default ){
786 145 : onError = OE_Abort;
787 : }
788 145 : if( seenReplace ){
789 0 : if( onError==OE_Ignore ) onError = OE_Replace;
790 0 : else if( onError==OE_Fail ) onError = OE_Abort;
791 : }
792 :
793 :
794 : /* Check to see if the new index entry will be unique */
795 145 : sqliteVdbeAddOp(v, OP_Dup, extra+nCol+1+hasTwoRecnos, 1);
796 145 : jumpInst2 = sqliteVdbeAddOp(v, OP_IsUnique, base+iCur+1, 0);
797 :
798 : /* Generate code that executes if the new index entry is not unique */
799 145 : switch( onError ){
800 : case OE_Rollback:
801 : case OE_Abort:
802 : case OE_Fail: {
803 : int j, n1, n2;
804 : char zErrMsg[200];
805 145 : strcpy(zErrMsg, pIdx->nColumn>1 ? "columns " : "column ");
806 145 : n1 = strlen(zErrMsg);
807 290 : for(j=0; j<pIdx->nColumn && n1<sizeof(zErrMsg)-30; j++){
808 145 : char *zCol = pTab->aCol[pIdx->aiColumn[j]].zName;
809 145 : n2 = strlen(zCol);
810 145 : if( j>0 ){
811 0 : strcpy(&zErrMsg[n1], ", ");
812 0 : n1 += 2;
813 : }
814 145 : if( n1+n2>sizeof(zErrMsg)-30 ){
815 0 : strcpy(&zErrMsg[n1], "...");
816 0 : n1 += 3;
817 0 : break;
818 : }else{
819 145 : strcpy(&zErrMsg[n1], zCol);
820 145 : n1 += n2;
821 : }
822 : }
823 145 : strcpy(&zErrMsg[n1],
824 : pIdx->nColumn>1 ? " are not unique" : " is not unique");
825 145 : sqliteVdbeOp3(v, OP_Halt, SQLITE_CONSTRAINT, onError, zErrMsg, 0);
826 145 : break;
827 : }
828 : case OE_Ignore: {
829 : assert( seenReplace==0 );
830 0 : sqliteVdbeAddOp(v, OP_Pop, nCol+extra+3+hasTwoRecnos, 0);
831 0 : sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
832 0 : break;
833 : }
834 : case OE_Replace: {
835 0 : sqliteGenerateRowDelete(pParse->db, v, pTab, base, 0);
836 0 : if( isUpdate ){
837 0 : sqliteVdbeAddOp(v, OP_Dup, nCol+extra+1+hasTwoRecnos, 1);
838 0 : sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
839 : }
840 0 : seenReplace = 1;
841 : break;
842 : }
843 : default: assert(0);
844 : }
845 145 : contAddr = sqliteVdbeCurrentAddr(v);
846 : #if NULL_DISTINCT_FOR_UNIQUE
847 145 : sqliteVdbeChangeP2(v, jumpInst1, contAddr);
848 : #endif
849 145 : sqliteVdbeChangeP2(v, jumpInst2, contAddr);
850 : }
851 262 : }
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 262 : ){
872 : int i;
873 : Vdbe *v;
874 : int nIdx;
875 : Index *pIdx;
876 :
877 262 : v = sqliteGetVdbe(pParse);
878 : assert( v!=0 );
879 : assert( pTab->pSelect==0 ); /* This table is not a VIEW */
880 262 : for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){}
881 407 : for(i=nIdx-1; i>=0; i--){
882 145 : if( aIdxUsed && aIdxUsed[i]==0 ) continue;
883 145 : sqliteVdbeAddOp(v, OP_IdxPut, base+i+1, 0);
884 : }
885 262 : sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
886 262 : if( newIdx>=0 ){
887 0 : sqliteVdbeAddOp(v, OP_Dup, 1, 0);
888 0 : sqliteVdbeAddOp(v, OP_Dup, 1, 0);
889 0 : sqliteVdbeAddOp(v, OP_PutIntKey, newIdx, 0);
890 : }
891 262 : sqliteVdbeAddOp(v, OP_PutIntKey, base,
892 : (pParse->trigStack?0:OPFLAG_NCHANGE) |
893 : (isUpdate?0:OPFLAG_LASTROWID) | OPFLAG_CSCHANGE);
894 262 : if( isUpdate && recnoChng ){
895 0 : sqliteVdbeAddOp(v, OP_Pop, 1, 0);
896 : }
897 262 : }
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 262 : int sqliteOpenTableAndIndices(Parse *pParse, Table *pTab, int base){
908 : int i;
909 : Index *pIdx;
910 262 : Vdbe *v = sqliteGetVdbe(pParse);
911 : assert( v!=0 );
912 262 : sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
913 262 : sqliteVdbeOp3(v, OP_OpenWrite, base, pTab->tnum, pTab->zName, P3_STATIC);
914 407 : for(i=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
915 145 : sqliteVdbeAddOp(v, OP_Integer, pIdx->iDb, 0);
916 145 : sqliteVdbeOp3(v, OP_OpenWrite, i+base, pIdx->tnum, pIdx->zName, P3_STATIC);
917 : }
918 262 : return i;
919 : }
|
