blob: f9f72f8c409b0c511ba788a0b2fe618ea8819637 [file] [log] [blame]
drhcce7d172000-05-31 15:34:51 +00001/*
drhb19a2bc2001-09-16 00:13:26 +00002** 2001 September 15
drhcce7d172000-05-31 15:34:51 +00003**
drhb19a2bc2001-09-16 00:13:26 +00004** The author disclaims copyright to this source code. In place of
5** a legal notice, here is a blessing:
drhcce7d172000-05-31 15:34:51 +00006**
drhb19a2bc2001-09-16 00:13:26 +00007** 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.
drhcce7d172000-05-31 15:34:51 +000010**
11*************************************************************************
12** This file contains C code routines that are called by the parser
drhb19a2bc2001-09-16 00:13:26 +000013** to handle INSERT statements in SQLite.
drhcce7d172000-05-31 15:34:51 +000014**
drh812d7a22003-03-27 13:50:00 +000015** $Id: insert.c,v 1.76 2003/03/27 13:50:00 drh Exp $
drhcce7d172000-05-31 15:34:51 +000016*/
17#include "sqliteInt.h"
18
19/*
drh1ccde152000-06-17 13:12:39 +000020** This routine is call to handle SQL of the following forms:
drhcce7d172000-05-31 15:34:51 +000021**
22** insert into TABLE (IDLIST) values(EXPRLIST)
drh1ccde152000-06-17 13:12:39 +000023** insert into TABLE (IDLIST) select
drhcce7d172000-05-31 15:34:51 +000024**
drh1ccde152000-06-17 13:12:39 +000025** 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
drh967e8b72000-06-21 13:59:10 +000027** appears in the pColumn parameter. pColumn is NULL if IDLIST is omitted.
drh1ccde152000-06-17 13:12:39 +000028**
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.
drh142e30d2002-08-28 03:00:58 +000033**
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
drhcce7d172000-05-31 15:34:51 +000085*/
86void sqliteInsert(
87 Parse *pParse, /* Parser context */
drh113088e2003-03-20 01:16:58 +000088 SrcList *pTabList, /* Name of table into which we are inserting */
drhcce7d172000-05-31 15:34:51 +000089 ExprList *pList, /* List of values to be inserted */
drh5974a302000-06-07 14:42:26 +000090 Select *pSelect, /* A SELECT statement to use as the data source */
drh9cfcf5d2002-01-29 18:41:24 +000091 IdList *pColumn, /* Column names corresponding to IDLIST. */
92 int onError /* How to handle constraint errors */
drhcce7d172000-05-31 15:34:51 +000093){
drh5974a302000-06-07 14:42:26 +000094 Table *pTab; /* The table to insert into */
drh113088e2003-03-20 01:16:58 +000095 char *zTab; /* Name of the table into which we are inserting */
drh5974a302000-06-07 14:42:26 +000096 int i, j, idx; /* Loop counters */
97 Vdbe *v; /* Generate code into this virtual machine */
98 Index *pIdx; /* For looping over indices of the table */
drh967e8b72000-06-21 13:59:10 +000099 int nColumn; /* Number of columns in the data */
drh5974a302000-06-07 14:42:26 +0000100 int base; /* First available cursor */
101 int iCont, iBreak; /* Beginning and end of the loop over srcTab */
drhecdc7532001-09-23 02:35:53 +0000102 sqlite *db; /* The main database structure */
drh4a324312001-12-21 14:30:42 +0000103 int keyColumn = -1; /* Column that is the INTEGER PRIMARY KEY */
drh0ca3e242002-01-29 23:07:02 +0000104 int endOfLoop; /* Label for the end of the insertion loop */
drh142e30d2002-08-28 03:00:58 +0000105 int useTempTable; /* Store SELECT results in intermediate table */
106 int srcTab; /* Data comes from this temporary cursor if >=0 */
107 int iSelectLoop; /* Address of code that implements the SELECT */
108 int iCleanup; /* Address of the cleanup code */
109 int iInsertBlock; /* Address of the subroutine used to insert data */
110 int iCntMem; /* Memory cell used for the row counter */
drhcce7d172000-05-31 15:34:51 +0000111
danielk1977c3f9bad2002-05-15 08:30:12 +0000112 int row_triggers_exist = 0; /* True if there are FOR EACH ROW triggers */
113 int newIdx = -1;
114
drhdaffd0e2001-04-11 14:28:42 +0000115 if( pParse->nErr || sqlite_malloc_failed ) goto insert_cleanup;
drhecdc7532001-09-23 02:35:53 +0000116 db = pParse->db;
drhdaffd0e2001-04-11 14:28:42 +0000117
drh1ccde152000-06-17 13:12:39 +0000118 /* Locate the table into which we will be inserting new information.
119 */
drh113088e2003-03-20 01:16:58 +0000120 assert( pTabList->nSrc==1 );
121 zTab = pTabList->a[0].zName;
drhdaffd0e2001-04-11 14:28:42 +0000122 if( zTab==0 ) goto insert_cleanup;
drh812d7a22003-03-27 13:50:00 +0000123 pTab = sqliteSrcListLookup(pParse, pTabList);
danielk1977c3f9bad2002-05-15 08:30:12 +0000124 if( pTab==0 ){
danielk1977c3f9bad2002-05-15 08:30:12 +0000125 goto insert_cleanup;
126 }
drhe5f9c642003-01-13 23:27:31 +0000127 if( sqliteAuthCheck(pParse, SQLITE_INSERT, pTab->zName, 0) ){
drh1962bda2003-01-12 19:33:52 +0000128 goto insert_cleanup;
129 }
danielk1977c3f9bad2002-05-15 08:30:12 +0000130
131 /* Ensure that:
132 * (a) the table is not read-only,
133 * (b) that if it is a view then ON INSERT triggers exist
134 */
135 row_triggers_exist =
136 sqliteTriggersExist(pParse, pTab->pTrigger, TK_INSERT,
drh9adf9ac2002-05-15 11:44:13 +0000137 TK_BEFORE, TK_ROW, 0) ||
danielk1977c3f9bad2002-05-15 08:30:12 +0000138 sqliteTriggersExist(pParse, pTab->pTrigger, TK_INSERT, TK_AFTER, TK_ROW, 0);
139 if( pTab->readOnly || (pTab->pSelect && !row_triggers_exist) ){
140 sqliteSetString(&pParse->zErrMsg,
141 pTab->pSelect ? "view " : "table ",
142 zTab,
143 " may not be modified", 0);
144 pParse->nErr++;
145 goto insert_cleanup;
146 }
danielk1977c3f9bad2002-05-15 08:30:12 +0000147
drha76b5df2002-02-23 02:32:10 +0000148 if( pTab==0 ) goto insert_cleanup;
drh1ccde152000-06-17 13:12:39 +0000149
drhf573c992002-07-31 00:32:50 +0000150 /* If pTab is really a view, make sure it has been initialized.
151 */
152 if( pTab->pSelect ){
153 if( sqliteViewGetColumnNames(pParse, pTab) ){
154 goto insert_cleanup;
155 }
156 }
157
drh1ccde152000-06-17 13:12:39 +0000158 /* Allocate a VDBE
159 */
drhd8bc7082000-06-07 23:51:50 +0000160 v = sqliteGetVdbe(pParse);
drh5974a302000-06-07 14:42:26 +0000161 if( v==0 ) goto insert_cleanup;
drhcabb0812002-09-14 13:47:32 +0000162 sqliteBeginWriteOperation(pParse, pSelect || row_triggers_exist,
drhd24cc422003-03-27 12:51:24 +0000163 !row_triggers_exist && pTab->iDb==1);
drh1ccde152000-06-17 13:12:39 +0000164
danielk1977c3f9bad2002-05-15 08:30:12 +0000165 /* if there are row triggers, allocate a temp table for new.* references. */
danielk1977f29ce552002-05-19 23:43:12 +0000166 if( row_triggers_exist ){
danielk1977c3f9bad2002-05-15 08:30:12 +0000167 newIdx = pParse->nTab++;
danielk1977f29ce552002-05-19 23:43:12 +0000168 }
danielk1977c3f9bad2002-05-15 08:30:12 +0000169
drh1ccde152000-06-17 13:12:39 +0000170 /* Figure out how many columns of data are supplied. If the data
drh142e30d2002-08-28 03:00:58 +0000171 ** is coming from a SELECT statement, then this step also generates
172 ** all the code to implement the SELECT statement and invoke a subroutine
173 ** to process each row of the result. (Template 2.) If the SELECT
174 ** statement uses the the table that is being inserted into, then the
175 ** subroutine is also coded here. That subroutine stores the SELECT
176 ** results in a temporary table. (Template 3.)
drh1ccde152000-06-17 13:12:39 +0000177 */
drh5974a302000-06-07 14:42:26 +0000178 if( pSelect ){
drh142e30d2002-08-28 03:00:58 +0000179 /* Data is coming from a SELECT. Generate code to implement that SELECT
180 */
181 int rc, iInitCode;
182 int opCode;
183 iInitCode = sqliteVdbeAddOp(v, OP_Goto, 0, 0);
184 iSelectLoop = sqliteVdbeCurrentAddr(v);
185 iInsertBlock = sqliteVdbeMakeLabel(v);
186 rc = sqliteSelect(pParse, pSelect, SRT_Subroutine, iInsertBlock, 0,0,0);
drhdaffd0e2001-04-11 14:28:42 +0000187 if( rc || pParse->nErr || sqlite_malloc_failed ) goto insert_cleanup;
drh142e30d2002-08-28 03:00:58 +0000188 iCleanup = sqliteVdbeMakeLabel(v);
189 sqliteVdbeAddOp(v, OP_Goto, 0, iCleanup);
drh5974a302000-06-07 14:42:26 +0000190 assert( pSelect->pEList );
drh967e8b72000-06-21 13:59:10 +0000191 nColumn = pSelect->pEList->nExpr;
drh142e30d2002-08-28 03:00:58 +0000192
193 /* Set useTempTable to TRUE if the result of the SELECT statement
194 ** should be written into a temporary table. Set to FALSE if each
195 ** row of the SELECT can be written directly into the result table.
196 */
drhd24cc422003-03-27 12:51:24 +0000197 opCode = pTab->iDb==1 ? OP_OpenTemp : OP_OpenRead;
drh142e30d2002-08-28 03:00:58 +0000198 useTempTable = row_triggers_exist || sqliteVdbeFindOp(v,opCode,pTab->tnum);
199
200 if( useTempTable ){
201 /* Generate the subroutine that SELECT calls to process each row of
202 ** the result. Store the result in a temporary table
203 */
204 srcTab = pParse->nTab++;
205 sqliteVdbeResolveLabel(v, iInsertBlock);
206 sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0);
207 sqliteVdbeAddOp(v, OP_NewRecno, srcTab, 0);
208 sqliteVdbeAddOp(v, OP_Pull, 1, 0);
209 sqliteVdbeAddOp(v, OP_PutIntKey, srcTab, 0);
210 sqliteVdbeAddOp(v, OP_Return, 0, 0);
211
212 /* The following code runs first because the GOTO at the very top
213 ** of the program jumps to it. Create the temporary table, then jump
214 ** back up and execute the SELECT code above.
215 */
216 sqliteVdbeChangeP2(v, iInitCode, sqliteVdbeCurrentAddr(v));
217 sqliteVdbeAddOp(v, OP_OpenTemp, srcTab, 0);
218 sqliteVdbeAddOp(v, OP_Goto, 0, iSelectLoop);
219 sqliteVdbeResolveLabel(v, iCleanup);
220 }else{
221 sqliteVdbeChangeP2(v, iInitCode, sqliteVdbeCurrentAddr(v));
222 }
drh5974a302000-06-07 14:42:26 +0000223 }else{
drh142e30d2002-08-28 03:00:58 +0000224 /* This is the case if the data for the INSERT is coming from a VALUES
225 ** clause
226 */
drhad3cab52002-05-24 02:04:32 +0000227 SrcList dummy;
drhdaffd0e2001-04-11 14:28:42 +0000228 assert( pList!=0 );
drh5974a302000-06-07 14:42:26 +0000229 srcTab = -1;
drh142e30d2002-08-28 03:00:58 +0000230 useTempTable = 0;
drh5974a302000-06-07 14:42:26 +0000231 assert( pList );
drh967e8b72000-06-21 13:59:10 +0000232 nColumn = pList->nExpr;
drhad3cab52002-05-24 02:04:32 +0000233 dummy.nSrc = 0;
drhe64e7b22002-02-18 13:56:36 +0000234 for(i=0; i<nColumn; i++){
drh832508b2002-03-02 17:04:07 +0000235 if( sqliteExprResolveIds(pParse, 0, &dummy, 0, pList->a[i].pExpr) ){
drhe64e7b22002-02-18 13:56:36 +0000236 goto insert_cleanup;
237 }
drhb04a5d82002-04-12 03:55:15 +0000238 if( sqliteExprCheck(pParse, pList->a[i].pExpr, 0, 0) ){
239 goto insert_cleanup;
240 }
drhe64e7b22002-02-18 13:56:36 +0000241 }
drh5974a302000-06-07 14:42:26 +0000242 }
drh1ccde152000-06-17 13:12:39 +0000243
244 /* Make sure the number of columns in the source data matches the number
245 ** of columns to be inserted into the table.
246 */
drh967e8b72000-06-21 13:59:10 +0000247 if( pColumn==0 && nColumn!=pTab->nCol ){
drhcce7d172000-05-31 15:34:51 +0000248 char zNum1[30];
249 char zNum2[30];
drh967e8b72000-06-21 13:59:10 +0000250 sprintf(zNum1,"%d", nColumn);
drhcce7d172000-05-31 15:34:51 +0000251 sprintf(zNum2,"%d", pTab->nCol);
252 sqliteSetString(&pParse->zErrMsg, "table ", pTab->zName,
253 " has ", zNum2, " columns but ",
254 zNum1, " values were supplied", 0);
255 pParse->nErr++;
256 goto insert_cleanup;
257 }
drh967e8b72000-06-21 13:59:10 +0000258 if( pColumn!=0 && nColumn!=pColumn->nId ){
drhcce7d172000-05-31 15:34:51 +0000259 char zNum1[30];
260 char zNum2[30];
drh967e8b72000-06-21 13:59:10 +0000261 sprintf(zNum1,"%d", nColumn);
262 sprintf(zNum2,"%d", pColumn->nId);
drhcce7d172000-05-31 15:34:51 +0000263 sqliteSetString(&pParse->zErrMsg, zNum1, " values for ",
264 zNum2, " columns", 0);
265 pParse->nErr++;
266 goto insert_cleanup;
267 }
drh1ccde152000-06-17 13:12:39 +0000268
269 /* If the INSERT statement included an IDLIST term, then make sure
270 ** all elements of the IDLIST really are columns of the table and
271 ** remember the column indices.
drhc8392582001-12-31 02:48:51 +0000272 **
273 ** If the table has an INTEGER PRIMARY KEY column and that column
274 ** is named in the IDLIST, then record in the keyColumn variable
275 ** the index into IDLIST of the primary key column. keyColumn is
276 ** the index of the primary key as it appears in IDLIST, not as
277 ** is appears in the original table. (The index of the primary
278 ** key in the original table is pTab->iPKey.)
drh1ccde152000-06-17 13:12:39 +0000279 */
drh967e8b72000-06-21 13:59:10 +0000280 if( pColumn ){
281 for(i=0; i<pColumn->nId; i++){
282 pColumn->a[i].idx = -1;
drhcce7d172000-05-31 15:34:51 +0000283 }
drh967e8b72000-06-21 13:59:10 +0000284 for(i=0; i<pColumn->nId; i++){
drhcce7d172000-05-31 15:34:51 +0000285 for(j=0; j<pTab->nCol; j++){
drh967e8b72000-06-21 13:59:10 +0000286 if( sqliteStrICmp(pColumn->a[i].zName, pTab->aCol[j].zName)==0 ){
287 pColumn->a[i].idx = j;
drh4a324312001-12-21 14:30:42 +0000288 if( j==pTab->iPKey ){
drh9aa028d2001-12-22 21:48:29 +0000289 keyColumn = i;
drh4a324312001-12-21 14:30:42 +0000290 }
drhcce7d172000-05-31 15:34:51 +0000291 break;
292 }
293 }
294 if( j>=pTab->nCol ){
295 sqliteSetString(&pParse->zErrMsg, "table ", pTab->zName,
drh967e8b72000-06-21 13:59:10 +0000296 " has no column named ", pColumn->a[i].zName, 0);
drhcce7d172000-05-31 15:34:51 +0000297 pParse->nErr++;
298 goto insert_cleanup;
299 }
300 }
301 }
drh1ccde152000-06-17 13:12:39 +0000302
drhaacc5432002-01-06 17:07:40 +0000303 /* If there is no IDLIST term but the table has an integer primary
drhc8392582001-12-31 02:48:51 +0000304 ** key, the set the keyColumn variable to the primary key column index
305 ** in the original table definition.
drh4a324312001-12-21 14:30:42 +0000306 */
307 if( pColumn==0 ){
308 keyColumn = pTab->iPKey;
309 }
310
drh142e30d2002-08-28 03:00:58 +0000311 /* Open the temp table for FOR EACH ROW triggers
312 */
danielk1977f29ce552002-05-19 23:43:12 +0000313 if( row_triggers_exist ){
danielk1977c3f9bad2002-05-15 08:30:12 +0000314 sqliteVdbeAddOp(v, OP_OpenTemp, newIdx, 0);
danielk1977f29ce552002-05-19 23:43:12 +0000315 }
danielk1977c3f9bad2002-05-15 08:30:12 +0000316
drhfeeb1392002-04-09 03:28:01 +0000317 /* Initialize the count of rows to be inserted
318 */
drh142e30d2002-08-28 03:00:58 +0000319 if( db->flags & SQLITE_CountRows ){
320 iCntMem = pParse->nMem++;
321 sqliteVdbeAddOp(v, OP_Integer, 0, 0);
322 sqliteVdbeAddOp(v, OP_MemStore, iCntMem, 1);
drhfeeb1392002-04-09 03:28:01 +0000323 }
324
danielk1977c3f9bad2002-05-15 08:30:12 +0000325 /* Open tables and indices if there are no row triggers */
danielk1977f29ce552002-05-19 23:43:12 +0000326 if( !row_triggers_exist ){
danielk1977c3f9bad2002-05-15 08:30:12 +0000327 base = pParse->nTab;
drhd24cc422003-03-27 12:51:24 +0000328 sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
drh001bbcb2003-03-19 03:14:00 +0000329 sqliteVdbeAddOp(v, OP_OpenWrite, base, pTab->tnum);
danielk1977c3f9bad2002-05-15 08:30:12 +0000330 sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
331 for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
drhd24cc422003-03-27 12:51:24 +0000332 sqliteVdbeAddOp(v, OP_Integer, pIdx->iDb, 0);
drh001bbcb2003-03-19 03:14:00 +0000333 sqliteVdbeAddOp(v, OP_OpenWrite, idx+base, pIdx->tnum);
danielk1977c3f9bad2002-05-15 08:30:12 +0000334 sqliteVdbeChangeP3(v, -1, pIdx->zName, P3_STATIC);
335 }
336 pParse->nTab += idx;
337 }
338
drh142e30d2002-08-28 03:00:58 +0000339 /* If the data source is a temporary table, then we have to create
drh1ccde152000-06-17 13:12:39 +0000340 ** a loop because there might be multiple rows of data. If the data
drh142e30d2002-08-28 03:00:58 +0000341 ** source is a subroutine call from the SELECT statement, then we need
342 ** to launch the SELECT statement processing.
drh1ccde152000-06-17 13:12:39 +0000343 */
drh142e30d2002-08-28 03:00:58 +0000344 if( useTempTable ){
drh5974a302000-06-07 14:42:26 +0000345 iBreak = sqliteVdbeMakeLabel(v);
drh6b563442001-11-07 16:48:26 +0000346 sqliteVdbeAddOp(v, OP_Rewind, srcTab, iBreak);
347 iCont = sqliteVdbeCurrentAddr(v);
drh142e30d2002-08-28 03:00:58 +0000348 }else if( pSelect ){
349 sqliteVdbeAddOp(v, OP_Goto, 0, iSelectLoop);
350 sqliteVdbeResolveLabel(v, iInsertBlock);
drh5974a302000-06-07 14:42:26 +0000351 }
drh1ccde152000-06-17 13:12:39 +0000352
danielk19776f349032002-06-11 02:25:40 +0000353 endOfLoop = sqliteVdbeMakeLabel(v);
danielk1977f29ce552002-05-19 23:43:12 +0000354 if( row_triggers_exist ){
danielk1977c3f9bad2002-05-15 08:30:12 +0000355
356 /* build the new.* reference row */
357 sqliteVdbeAddOp(v, OP_Integer, 13, 0);
358 for(i=0; i<pTab->nCol; i++){
359 if( pColumn==0 ){
drh9adf9ac2002-05-15 11:44:13 +0000360 j = i;
danielk1977c3f9bad2002-05-15 08:30:12 +0000361 }else{
drh9adf9ac2002-05-15 11:44:13 +0000362 for(j=0; j<pColumn->nId; j++){
363 if( pColumn->a[j].idx==i ) break;
364 }
danielk1977c3f9bad2002-05-15 08:30:12 +0000365 }
366 if( pColumn && j>=pColumn->nId ){
drh9adf9ac2002-05-15 11:44:13 +0000367 sqliteVdbeAddOp(v, OP_String, 0, 0);
368 sqliteVdbeChangeP3(v, -1, pTab->aCol[i].zDflt, P3_STATIC);
drh142e30d2002-08-28 03:00:58 +0000369 }else if( useTempTable ){
drh9adf9ac2002-05-15 11:44:13 +0000370 sqliteVdbeAddOp(v, OP_Column, srcTab, j);
drh142e30d2002-08-28 03:00:58 +0000371 }else if( pSelect ){
372 sqliteVdbeAddOp(v, OP_Dup, nColumn-j-1, 1);
danielk1977c3f9bad2002-05-15 08:30:12 +0000373 }else{
drh9adf9ac2002-05-15 11:44:13 +0000374 sqliteExprCode(pParse, pList->a[j].pExpr);
danielk1977c3f9bad2002-05-15 08:30:12 +0000375 }
376 }
377 sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
378 sqliteVdbeAddOp(v, OP_PutIntKey, newIdx, 0);
379 sqliteVdbeAddOp(v, OP_Rewind, newIdx, 0);
380
381 /* Fire BEFORE triggers */
danielk1977f29ce552002-05-19 23:43:12 +0000382 if( sqliteCodeRowTrigger(pParse, TK_INSERT, 0, TK_BEFORE, pTab, newIdx, -1,
danielk19776f349032002-06-11 02:25:40 +0000383 onError, endOfLoop) ){
danielk1977f29ce552002-05-19 23:43:12 +0000384 goto insert_cleanup;
385 }
danielk1977c3f9bad2002-05-15 08:30:12 +0000386
387 /* Open the tables and indices for the INSERT */
danielk1977f29ce552002-05-19 23:43:12 +0000388 if( !pTab->pSelect ){
danielk1977c3f9bad2002-05-15 08:30:12 +0000389 base = pParse->nTab;
drhd24cc422003-03-27 12:51:24 +0000390 sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
drh001bbcb2003-03-19 03:14:00 +0000391 sqliteVdbeAddOp(v, OP_OpenWrite, base, pTab->tnum);
danielk1977c3f9bad2002-05-15 08:30:12 +0000392 sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
393 for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
drhd24cc422003-03-27 12:51:24 +0000394 sqliteVdbeAddOp(v, OP_Integer, pIdx->iDb, 0);
drh001bbcb2003-03-19 03:14:00 +0000395 sqliteVdbeAddOp(v, OP_OpenWrite, idx+base, pIdx->tnum);
drh9adf9ac2002-05-15 11:44:13 +0000396 sqliteVdbeChangeP3(v, -1, pIdx->zName, P3_STATIC);
danielk1977c3f9bad2002-05-15 08:30:12 +0000397 }
398 pParse->nTab += idx;
399 }
400 }
401
drh4a324312001-12-21 14:30:42 +0000402 /* Push the record number for the new entry onto the stack. The
403 ** record number is a randomly generate integer created by NewRecno
404 ** except when the table has an INTEGER PRIMARY KEY column, in which
drhb419a922002-01-30 16:17:23 +0000405 ** case the record number is the same as that column.
drh1ccde152000-06-17 13:12:39 +0000406 */
danielk1977f29ce552002-05-19 23:43:12 +0000407 if( !pTab->pSelect ){
danielk1977c3f9bad2002-05-15 08:30:12 +0000408 if( keyColumn>=0 ){
drh142e30d2002-08-28 03:00:58 +0000409 if( useTempTable ){
drh9adf9ac2002-05-15 11:44:13 +0000410 sqliteVdbeAddOp(v, OP_Column, srcTab, keyColumn);
drh142e30d2002-08-28 03:00:58 +0000411 }else if( pSelect ){
412 sqliteVdbeAddOp(v, OP_Dup, nColumn - keyColumn - 1, 1);
danielk1977c3f9bad2002-05-15 08:30:12 +0000413 }else{
drh9adf9ac2002-05-15 11:44:13 +0000414 sqliteExprCode(pParse, pList->a[keyColumn].pExpr);
danielk1977c3f9bad2002-05-15 08:30:12 +0000415 }
drh27a32782002-06-19 20:32:43 +0000416 /* If the PRIMARY KEY expression is NULL, then use OP_NewRecno
417 ** to generate a unique primary key value.
418 */
419 sqliteVdbeAddOp(v, OP_NotNull, -1, sqliteVdbeCurrentAddr(v)+3);
420 sqliteVdbeAddOp(v, OP_Pop, 1, 0);
421 sqliteVdbeAddOp(v, OP_NewRecno, base, 0);
danielk1977c3f9bad2002-05-15 08:30:12 +0000422 sqliteVdbeAddOp(v, OP_MustBeInt, 0, 0);
423 }else{
drhe1e68f42002-03-31 18:29:03 +0000424 sqliteVdbeAddOp(v, OP_NewRecno, base, 0);
drh4a324312001-12-21 14:30:42 +0000425 }
drh4a324312001-12-21 14:30:42 +0000426
danielk1977c3f9bad2002-05-15 08:30:12 +0000427 /* Push onto the stack, data for all columns of the new entry, beginning
danielk1977f29ce552002-05-19 23:43:12 +0000428 ** with the first column.
429 */
danielk1977c3f9bad2002-05-15 08:30:12 +0000430 for(i=0; i<pTab->nCol; i++){
431 if( i==pTab->iPKey ){
drh9adf9ac2002-05-15 11:44:13 +0000432 /* The value of the INTEGER PRIMARY KEY column is always a NULL.
danielk1977f29ce552002-05-19 23:43:12 +0000433 ** Whenever this column is read, the record number will be substituted
434 ** in its place. So will fill this column with a NULL to avoid
435 ** taking up data space with information that will never be used. */
drh9adf9ac2002-05-15 11:44:13 +0000436 sqliteVdbeAddOp(v, OP_String, 0, 0);
437 continue;
danielk1977c3f9bad2002-05-15 08:30:12 +0000438 }
439 if( pColumn==0 ){
drh9adf9ac2002-05-15 11:44:13 +0000440 j = i;
danielk1977c3f9bad2002-05-15 08:30:12 +0000441 }else{
drh9adf9ac2002-05-15 11:44:13 +0000442 for(j=0; j<pColumn->nId; j++){
443 if( pColumn->a[j].idx==i ) break;
444 }
danielk1977c3f9bad2002-05-15 08:30:12 +0000445 }
446 if( pColumn && j>=pColumn->nId ){
drh9adf9ac2002-05-15 11:44:13 +0000447 sqliteVdbeAddOp(v, OP_String, 0, 0);
448 sqliteVdbeChangeP3(v, -1, pTab->aCol[i].zDflt, P3_STATIC);
drh142e30d2002-08-28 03:00:58 +0000449 }else if( useTempTable ){
drh9adf9ac2002-05-15 11:44:13 +0000450 sqliteVdbeAddOp(v, OP_Column, srcTab, j);
drh142e30d2002-08-28 03:00:58 +0000451 }else if( pSelect ){
452 sqliteVdbeAddOp(v, OP_Dup, i+nColumn-j, 1);
danielk1977c3f9bad2002-05-15 08:30:12 +0000453 }else{
drh9adf9ac2002-05-15 11:44:13 +0000454 sqliteExprCode(pParse, pList->a[j].pExpr);
drh5974a302000-06-07 14:42:26 +0000455 }
drhbed86902000-06-02 13:27:59 +0000456 }
danielk1977c3f9bad2002-05-15 08:30:12 +0000457
458 /* Generate code to check constraints and generate index keys and
danielk1977f29ce552002-05-19 23:43:12 +0000459 ** do the insertion.
460 */
danielk1977c3f9bad2002-05-15 08:30:12 +0000461 sqliteGenerateConstraintChecks(pParse, pTab, base, 0,0,0,onError,endOfLoop);
462 sqliteCompleteInsertion(pParse, pTab, base, 0,0,0);
463
464 /* Update the count of rows that are inserted
danielk1977f29ce552002-05-19 23:43:12 +0000465 */
drh142e30d2002-08-28 03:00:58 +0000466 if( (db->flags & SQLITE_CountRows)!=0 ){
467 sqliteVdbeAddOp(v, OP_MemIncr, iCntMem, 0);
drh5974a302000-06-07 14:42:26 +0000468 }
469 }
drh1ccde152000-06-17 13:12:39 +0000470
danielk1977f29ce552002-05-19 23:43:12 +0000471 if( row_triggers_exist ){
danielk1977c3f9bad2002-05-15 08:30:12 +0000472 /* Close all tables opened */
danielk1977f29ce552002-05-19 23:43:12 +0000473 if( !pTab->pSelect ){
danielk1977c3f9bad2002-05-15 08:30:12 +0000474 sqliteVdbeAddOp(v, OP_Close, base, 0);
475 for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
drh9adf9ac2002-05-15 11:44:13 +0000476 sqliteVdbeAddOp(v, OP_Close, idx+base, 0);
danielk1977c3f9bad2002-05-15 08:30:12 +0000477 }
478 }
drh1bee3d72001-10-15 00:44:35 +0000479
danielk1977c3f9bad2002-05-15 08:30:12 +0000480 /* Code AFTER triggers */
danielk1977f29ce552002-05-19 23:43:12 +0000481 if( sqliteCodeRowTrigger(pParse, TK_INSERT, 0, TK_AFTER, pTab, newIdx, -1,
danielk19776f349032002-06-11 02:25:40 +0000482 onError, endOfLoop) ){
danielk1977f29ce552002-05-19 23:43:12 +0000483 goto insert_cleanup;
484 }
drh1bee3d72001-10-15 00:44:35 +0000485 }
486
drh1ccde152000-06-17 13:12:39 +0000487 /* The bottom of the loop, if the data source is a SELECT statement
danielk1977f29ce552002-05-19 23:43:12 +0000488 */
drh0ca3e242002-01-29 23:07:02 +0000489 sqliteVdbeResolveLabel(v, endOfLoop);
drh142e30d2002-08-28 03:00:58 +0000490 if( useTempTable ){
drh6b563442001-11-07 16:48:26 +0000491 sqliteVdbeAddOp(v, OP_Next, srcTab, iCont);
drh99fcd712001-10-13 01:06:47 +0000492 sqliteVdbeResolveLabel(v, iBreak);
drh6b563442001-11-07 16:48:26 +0000493 sqliteVdbeAddOp(v, OP_Close, srcTab, 0);
drh142e30d2002-08-28 03:00:58 +0000494 }else if( pSelect ){
495 sqliteVdbeAddOp(v, OP_Pop, nColumn, 0);
496 sqliteVdbeAddOp(v, OP_Return, 0, 0);
497 sqliteVdbeResolveLabel(v, iCleanup);
drh6b563442001-11-07 16:48:26 +0000498 }
danielk1977c3f9bad2002-05-15 08:30:12 +0000499
danielk1977f29ce552002-05-19 23:43:12 +0000500 if( !row_triggers_exist ){
danielk1977c3f9bad2002-05-15 08:30:12 +0000501 /* Close all tables opened */
502 sqliteVdbeAddOp(v, OP_Close, base, 0);
503 for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
504 sqliteVdbeAddOp(v, OP_Close, idx+base, 0);
505 }
drhcce7d172000-05-31 15:34:51 +0000506 }
danielk1977c3f9bad2002-05-15 08:30:12 +0000507
drh1c928532002-01-31 15:54:21 +0000508 sqliteEndWriteOperation(pParse);
drh5e00f6c2001-09-13 13:46:56 +0000509
drh1bee3d72001-10-15 00:44:35 +0000510 /*
danielk1977f29ce552002-05-19 23:43:12 +0000511 ** Return the number of rows inserted.
drh1bee3d72001-10-15 00:44:35 +0000512 */
drh142e30d2002-08-28 03:00:58 +0000513 if( db->flags & SQLITE_CountRows ){
drh1bee3d72001-10-15 00:44:35 +0000514 sqliteVdbeAddOp(v, OP_ColumnName, 0, 0);
515 sqliteVdbeChangeP3(v, -1, "rows inserted", P3_STATIC);
drh142e30d2002-08-28 03:00:58 +0000516 sqliteVdbeAddOp(v, OP_MemLoad, iCntMem, 0);
drh1bee3d72001-10-15 00:44:35 +0000517 sqliteVdbeAddOp(v, OP_Callback, 1, 0);
518 }
drhcce7d172000-05-31 15:34:51 +0000519
520insert_cleanup:
drh113088e2003-03-20 01:16:58 +0000521 sqliteSrcListDelete(pTabList);
drh5974a302000-06-07 14:42:26 +0000522 if( pList ) sqliteExprListDelete(pList);
523 if( pSelect ) sqliteSelectDelete(pSelect);
drh967e8b72000-06-21 13:59:10 +0000524 sqliteIdListDelete(pColumn);
drhcce7d172000-05-31 15:34:51 +0000525}
drh9cfcf5d2002-01-29 18:41:24 +0000526
drh9cfcf5d2002-01-29 18:41:24 +0000527/*
528** Generate code to do a constraint check prior to an INSERT or an UPDATE.
529**
530** When this routine is called, the stack contains (from bottom to top)
drh0ca3e242002-01-29 23:07:02 +0000531** the following values:
532**
drhb419a922002-01-30 16:17:23 +0000533** 1. The recno of the row to be updated before it is updated. This
534** value is omitted unless we are doing an UPDATE that involves a
535** change to the record number.
drh0ca3e242002-01-29 23:07:02 +0000536**
drhb419a922002-01-30 16:17:23 +0000537** 2. The recno of the row after the update.
drh0ca3e242002-01-29 23:07:02 +0000538**
539** 3. The data in the first column of the entry after the update.
540**
541** i. Data from middle columns...
542**
543** N. The data in the last column of the entry after the update.
544**
drhb419a922002-01-30 16:17:23 +0000545** The old recno shown as entry (1) above is omitted unless both isUpdate
drh1c928532002-01-31 15:54:21 +0000546** and recnoChng are 1. isUpdate is true for UPDATEs and false for
547** INSERTs and recnoChng is true if the record number is being changed.
drh0ca3e242002-01-29 23:07:02 +0000548**
549** The code generated by this routine pushes additional entries onto
550** the stack which are the keys for new index entries for the new record.
551** The order of index keys is the same as the order of the indices on
552** the pTable->pIndex list. A key is only created for index i if
553** aIdxUsed!=0 and aIdxUsed[i]!=0.
drh9cfcf5d2002-01-29 18:41:24 +0000554**
555** This routine also generates code to check constraints. NOT NULL,
556** CHECK, and UNIQUE constraints are all checked. If a constraint fails,
drh1c928532002-01-31 15:54:21 +0000557** then the appropriate action is performed. There are five possible
558** actions: ROLLBACK, ABORT, FAIL, REPLACE, and IGNORE.
drh9cfcf5d2002-01-29 18:41:24 +0000559**
560** Constraint type Action What Happens
561** --------------- ---------- ----------------------------------------
drh1c928532002-01-31 15:54:21 +0000562** any ROLLBACK The current transaction is rolled back and
drh9cfcf5d2002-01-29 18:41:24 +0000563** sqlite_exec() returns immediately with a
564** return code of SQLITE_CONSTRAINT.
565**
drh1c928532002-01-31 15:54:21 +0000566** any ABORT Back out changes from the current command
567** only (do not do a complete rollback) then
568** cause sqlite_exec() to return immediately
569** with SQLITE_CONSTRAINT.
570**
571** any FAIL Sqlite_exec() returns immediately with a
572** return code of SQLITE_CONSTRAINT. The
573** transaction is not rolled back and any
574** prior changes are retained.
575**
drh9cfcf5d2002-01-29 18:41:24 +0000576** any IGNORE The record number and data is popped from
577** the stack and there is an immediate jump
578** to label ignoreDest.
579**
580** NOT NULL REPLACE The NULL value is replace by the default
581** value for that column. If the default value
582** is NULL, the action is the same as ABORT.
583**
584** UNIQUE REPLACE The other row that conflicts with the row
585** being inserted is removed.
586**
587** CHECK REPLACE Illegal. The results in an exception.
588**
drh1c928532002-01-31 15:54:21 +0000589** Which action to take is determined by the overrideError parameter.
590** Or if overrideError==OE_Default, then the pParse->onError parameter
591** is used. Or if pParse->onError==OE_Default then the onError value
592** for the constraint is used.
drh9cfcf5d2002-01-29 18:41:24 +0000593**
drhaaab5722002-02-19 13:39:21 +0000594** The calling routine must open a read/write cursor for pTab with
drh9cfcf5d2002-01-29 18:41:24 +0000595** cursor number "base". All indices of pTab must also have open
596** read/write cursors with cursor number base+i for the i-th cursor.
597** Except, if there is no possibility of a REPLACE action then
598** cursors do not need to be open for indices where aIdxUsed[i]==0.
599**
600** If the isUpdate flag is true, it means that the "base" cursor is
601** initially pointing to an entry that is being updated. The isUpdate
602** flag causes extra code to be generated so that the "base" cursor
603** is still pointing at the same entry after the routine returns.
604** Without the isUpdate flag, the "base" cursor might be moved.
605*/
606void sqliteGenerateConstraintChecks(
607 Parse *pParse, /* The parser context */
608 Table *pTab, /* the table into which we are inserting */
609 int base, /* Index of a read/write cursor pointing at pTab */
610 char *aIdxUsed, /* Which indices are used. NULL means all are used */
drh0ca3e242002-01-29 23:07:02 +0000611 int recnoChng, /* True if the record number will change */
drhb419a922002-01-30 16:17:23 +0000612 int isUpdate, /* True for UPDATE, False for INSERT */
drh9cfcf5d2002-01-29 18:41:24 +0000613 int overrideError, /* Override onError to this if not OE_Default */
drhb419a922002-01-30 16:17:23 +0000614 int ignoreDest /* Jump to this label on an OE_Ignore resolution */
drh9cfcf5d2002-01-29 18:41:24 +0000615){
616 int i;
617 Vdbe *v;
618 int nCol;
619 int onError;
620 int addr;
621 int extra;
drh0ca3e242002-01-29 23:07:02 +0000622 int iCur;
623 Index *pIdx;
624 int seenReplace = 0;
drhf5905aa2002-05-26 20:54:33 +0000625 int jumpInst1, jumpInst2;
drh0ca3e242002-01-29 23:07:02 +0000626 int contAddr;
drhb419a922002-01-30 16:17:23 +0000627 int hasTwoRecnos = (isUpdate && recnoChng);
drh9cfcf5d2002-01-29 18:41:24 +0000628
629 v = sqliteGetVdbe(pParse);
630 assert( v!=0 );
drh417be792002-03-03 18:59:40 +0000631 assert( pTab->pSelect==0 ); /* This table is not a VIEW */
drh9cfcf5d2002-01-29 18:41:24 +0000632 nCol = pTab->nCol;
633
634 /* Test all NOT NULL constraints.
635 */
636 for(i=0; i<nCol; i++){
drh0ca3e242002-01-29 23:07:02 +0000637 if( i==pTab->iPKey ){
638 /* Fix me: Make sure the INTEGER PRIMARY KEY is not NULL. */
639 continue;
640 }
drh9cfcf5d2002-01-29 18:41:24 +0000641 onError = pTab->aCol[i].notNull;
drh0ca3e242002-01-29 23:07:02 +0000642 if( onError==OE_None ) continue;
drh9cfcf5d2002-01-29 18:41:24 +0000643 if( overrideError!=OE_Default ){
644 onError = overrideError;
drh1c928532002-01-31 15:54:21 +0000645 }else if( onError==OE_Default ){
drh0d65dc02002-02-03 00:56:09 +0000646 onError = pParse->db->onError;
647 if( onError==OE_Default ) onError = OE_Abort;
drh9cfcf5d2002-01-29 18:41:24 +0000648 }
649 if( onError==OE_Replace && pTab->aCol[i].zDflt==0 ){
650 onError = OE_Abort;
651 }
drhef6764a2002-01-30 04:32:00 +0000652 sqliteVdbeAddOp(v, OP_Dup, nCol-1-i, 1);
drhf5905aa2002-05-26 20:54:33 +0000653 addr = sqliteVdbeAddOp(v, OP_NotNull, 1, 0);
drh9cfcf5d2002-01-29 18:41:24 +0000654 switch( onError ){
drh1c928532002-01-31 15:54:21 +0000655 case OE_Rollback:
656 case OE_Abort:
657 case OE_Fail: {
drh483750b2003-01-29 18:46:51 +0000658 char *zMsg = 0;
drh1c928532002-01-31 15:54:21 +0000659 sqliteVdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, onError);
drh483750b2003-01-29 18:46:51 +0000660 sqliteSetString(&zMsg, pTab->zName, ".", pTab->aCol[i].zName,
661 " may not be NULL", 0);
662 sqliteVdbeChangeP3(v, -1, zMsg, P3_DYNAMIC);
drh9cfcf5d2002-01-29 18:41:24 +0000663 break;
664 }
665 case OE_Ignore: {
drhb419a922002-01-30 16:17:23 +0000666 sqliteVdbeAddOp(v, OP_Pop, nCol+1+hasTwoRecnos, 0);
drh0ca3e242002-01-29 23:07:02 +0000667 sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
drh9cfcf5d2002-01-29 18:41:24 +0000668 break;
669 }
670 case OE_Replace: {
671 sqliteVdbeAddOp(v, OP_String, 0, 0);
672 sqliteVdbeChangeP3(v, -1, pTab->aCol[i].zDflt, P3_STATIC);
673 sqliteVdbeAddOp(v, OP_Push, nCol-i, 0);
674 break;
675 }
drh0ca3e242002-01-29 23:07:02 +0000676 default: assert(0);
drh9cfcf5d2002-01-29 18:41:24 +0000677 }
drhef6764a2002-01-30 04:32:00 +0000678 sqliteVdbeChangeP2(v, addr, sqliteVdbeCurrentAddr(v));
drh9cfcf5d2002-01-29 18:41:24 +0000679 }
680
681 /* Test all CHECK constraints
682 */
drh0bd1f4e2002-06-06 18:54:39 +0000683 /**** TBD ****/
drh9cfcf5d2002-01-29 18:41:24 +0000684
drh0bd1f4e2002-06-06 18:54:39 +0000685 /* If we have an INTEGER PRIMARY KEY, make sure the primary key
686 ** of the new record does not previously exist. Except, if this
687 ** is an UPDATE and the primary key is not changing, that is OK.
688 ** Also, if the conflict resolution policy is REPLACE, then we
689 ** can skip this test.
drh9cfcf5d2002-01-29 18:41:24 +0000690 */
drh0d65dc02002-02-03 00:56:09 +0000691 if( (recnoChng || !isUpdate) && pTab->iPKey>=0 ){
drh0ca3e242002-01-29 23:07:02 +0000692 onError = pTab->keyConf;
693 if( overrideError!=OE_Default ){
694 onError = overrideError;
drh1c928532002-01-31 15:54:21 +0000695 }else if( onError==OE_Default ){
drh0d65dc02002-02-03 00:56:09 +0000696 onError = pParse->db->onError;
697 if( onError==OE_Default ) onError = OE_Abort;
drh0ca3e242002-01-29 23:07:02 +0000698 }
drh0d65dc02002-02-03 00:56:09 +0000699 if( onError!=OE_Replace ){
drh79b0c952002-05-21 12:56:43 +0000700 if( isUpdate ){
701 sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
702 sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
drhf5905aa2002-05-26 20:54:33 +0000703 jumpInst1 = sqliteVdbeAddOp(v, OP_Eq, 0, 0);
drh79b0c952002-05-21 12:56:43 +0000704 }
drh0d65dc02002-02-03 00:56:09 +0000705 sqliteVdbeAddOp(v, OP_Dup, nCol, 1);
drhf5905aa2002-05-26 20:54:33 +0000706 jumpInst2 = sqliteVdbeAddOp(v, OP_NotExists, base, 0);
drh0d65dc02002-02-03 00:56:09 +0000707 switch( onError ){
708 case OE_Rollback:
709 case OE_Abort:
710 case OE_Fail: {
711 sqliteVdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, onError);
drh483750b2003-01-29 18:46:51 +0000712 sqliteVdbeChangeP3(v, -1, "PRIMARY KEY must be unique", P3_STATIC);
drh0d65dc02002-02-03 00:56:09 +0000713 break;
714 }
715 case OE_Ignore: {
716 sqliteVdbeAddOp(v, OP_Pop, nCol+1+hasTwoRecnos, 0);
717 sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
718 break;
719 }
720 default: assert(0);
drh0ca3e242002-01-29 23:07:02 +0000721 }
drh0d65dc02002-02-03 00:56:09 +0000722 contAddr = sqliteVdbeCurrentAddr(v);
drhf5905aa2002-05-26 20:54:33 +0000723 sqliteVdbeChangeP2(v, jumpInst2, contAddr);
drh0d65dc02002-02-03 00:56:09 +0000724 if( isUpdate ){
drhf5905aa2002-05-26 20:54:33 +0000725 sqliteVdbeChangeP2(v, jumpInst1, contAddr);
drh0d65dc02002-02-03 00:56:09 +0000726 sqliteVdbeAddOp(v, OP_Dup, nCol+1, 1);
727 sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
drh0ca3e242002-01-29 23:07:02 +0000728 }
drh0ca3e242002-01-29 23:07:02 +0000729 }
730 }
drh0bd1f4e2002-06-06 18:54:39 +0000731
732 /* Test all UNIQUE constraints by creating entries for each UNIQUE
733 ** index and making sure that duplicate entries do not already exist.
734 ** Add the new records to the indices as we go.
735 */
drh9cfcf5d2002-01-29 18:41:24 +0000736 extra = 0;
737 for(extra=(-1), iCur=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, iCur++){
drh9cfcf5d2002-01-29 18:41:24 +0000738 if( aIdxUsed && aIdxUsed[iCur]==0 ) continue;
739 extra++;
740 sqliteVdbeAddOp(v, OP_Dup, nCol+extra, 1);
741 for(i=0; i<pIdx->nColumn; i++){
742 int idx = pIdx->aiColumn[i];
743 if( idx==pTab->iPKey ){
drh0ca3e242002-01-29 23:07:02 +0000744 sqliteVdbeAddOp(v, OP_Dup, i+extra+nCol+1, 1);
drh9cfcf5d2002-01-29 18:41:24 +0000745 }else{
drh0ca3e242002-01-29 23:07:02 +0000746 sqliteVdbeAddOp(v, OP_Dup, i+extra+nCol-idx, 1);
drh9cfcf5d2002-01-29 18:41:24 +0000747 }
748 }
drhf5905aa2002-05-26 20:54:33 +0000749 jumpInst1 = sqliteVdbeAddOp(v, OP_MakeIdxKey, pIdx->nColumn, 0);
drh491791a2002-07-18 00:34:09 +0000750 if( pParse->db->file_format>=4 ) sqliteAddIdxKeyType(v, pIdx);
drh9cfcf5d2002-01-29 18:41:24 +0000751 onError = pIdx->onError;
752 if( onError==OE_None ) continue;
753 if( overrideError!=OE_Default ){
754 onError = overrideError;
drh1c928532002-01-31 15:54:21 +0000755 }else if( onError==OE_Default ){
drh0d65dc02002-02-03 00:56:09 +0000756 onError = pParse->db->onError;
757 if( onError==OE_Default ) onError = OE_Abort;
drh9cfcf5d2002-01-29 18:41:24 +0000758 }
drhb419a922002-01-30 16:17:23 +0000759 sqliteVdbeAddOp(v, OP_Dup, extra+nCol+1+hasTwoRecnos, 1);
drhf5905aa2002-05-26 20:54:33 +0000760 jumpInst2 = sqliteVdbeAddOp(v, OP_IsUnique, base+iCur+1, 0);
drh9cfcf5d2002-01-29 18:41:24 +0000761 switch( onError ){
drh1c928532002-01-31 15:54:21 +0000762 case OE_Rollback:
763 case OE_Abort:
764 case OE_Fail: {
765 sqliteVdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, onError);
drh483750b2003-01-29 18:46:51 +0000766 sqliteVdbeChangeP3(v, -1, "uniqueness constraint failed", P3_STATIC);
drh9cfcf5d2002-01-29 18:41:24 +0000767 break;
768 }
769 case OE_Ignore: {
drh0ca3e242002-01-29 23:07:02 +0000770 assert( seenReplace==0 );
drhfe1a1772002-04-09 03:15:06 +0000771 sqliteVdbeAddOp(v, OP_Pop, nCol+extra+3+hasTwoRecnos, 0);
drh9cfcf5d2002-01-29 18:41:24 +0000772 sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
drh9cfcf5d2002-01-29 18:41:24 +0000773 break;
774 }
775 case OE_Replace: {
drh38640e12002-07-05 21:42:36 +0000776 sqliteGenerateRowDelete(pParse->db, v, pTab, base, 0);
drh9cfcf5d2002-01-29 18:41:24 +0000777 if( isUpdate ){
drhb419a922002-01-30 16:17:23 +0000778 sqliteVdbeAddOp(v, OP_Dup, nCol+extra+1+hasTwoRecnos, 1);
drh0ca3e242002-01-29 23:07:02 +0000779 sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
drh9cfcf5d2002-01-29 18:41:24 +0000780 }
drh0ca3e242002-01-29 23:07:02 +0000781 seenReplace = 1;
drh9cfcf5d2002-01-29 18:41:24 +0000782 break;
783 }
drh0ca3e242002-01-29 23:07:02 +0000784 default: assert(0);
drh9cfcf5d2002-01-29 18:41:24 +0000785 }
786 contAddr = sqliteVdbeCurrentAddr(v);
drh0bd1f4e2002-06-06 18:54:39 +0000787#if NULL_DISTINCT_FOR_UNIQUE
drhf5905aa2002-05-26 20:54:33 +0000788 sqliteVdbeChangeP2(v, jumpInst1, contAddr);
drh0bd1f4e2002-06-06 18:54:39 +0000789#endif
drhf5905aa2002-05-26 20:54:33 +0000790 sqliteVdbeChangeP2(v, jumpInst2, contAddr);
drh9cfcf5d2002-01-29 18:41:24 +0000791 }
792}
drh0ca3e242002-01-29 23:07:02 +0000793
794/*
795** This routine generates code to finish the INSERT or UPDATE operation
796** that was started by a prior call to sqliteGenerateConstraintChecks.
797** The stack must contain keys for all active indices followed by data
798** and the recno for the new entry. This routine creates the new
799** entries in all indices and in the main table.
800**
drhb419a922002-01-30 16:17:23 +0000801** The arguments to this routine should be the same as the first six
drh0ca3e242002-01-29 23:07:02 +0000802** arguments to sqliteGenerateConstraintChecks.
803*/
804void sqliteCompleteInsertion(
805 Parse *pParse, /* The parser context */
806 Table *pTab, /* the table into which we are inserting */
807 int base, /* Index of a read/write cursor pointing at pTab */
808 char *aIdxUsed, /* Which indices are used. NULL means all are used */
drhb419a922002-01-30 16:17:23 +0000809 int recnoChng, /* True if the record number will change */
810 int isUpdate /* True for UPDATE, False for INSERT */
drh0ca3e242002-01-29 23:07:02 +0000811){
812 int i;
813 Vdbe *v;
814 int nIdx;
815 Index *pIdx;
816
817 v = sqliteGetVdbe(pParse);
818 assert( v!=0 );
drh417be792002-03-03 18:59:40 +0000819 assert( pTab->pSelect==0 ); /* This table is not a VIEW */
drh0ca3e242002-01-29 23:07:02 +0000820 for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){}
821 for(i=nIdx-1; i>=0; i--){
822 if( aIdxUsed && aIdxUsed[i]==0 ) continue;
823 sqliteVdbeAddOp(v, OP_IdxPut, base+i+1, 0);
824 }
825 sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
danielk1977c3f9bad2002-05-15 08:30:12 +0000826 sqliteVdbeAddOp(v, OP_PutIntKey, base, pParse->trigStack?0:1);
drhb419a922002-01-30 16:17:23 +0000827 if( isUpdate && recnoChng ){
drh0ca3e242002-01-29 23:07:02 +0000828 sqliteVdbeAddOp(v, OP_Pop, 1, 0);
829 }
830}