xref: /illumos-gate/usr/src/lib/libsqlite/src/update.c (revision c5c4113d)
1 
2 #pragma ident	"%Z%%M%	%I%	%E% SMI"
3 
4 /*
5 ** 2001 September 15
6 **
7 ** The author disclaims copyright to this source code.  In place of
8 ** a legal notice, here is a blessing:
9 **
10 **    May you do good and not evil.
11 **    May you find forgiveness for yourself and forgive others.
12 **    May you share freely, never taking more than you give.
13 **
14 *************************************************************************
15 ** This file contains C code routines that are called by the parser
16 ** to handle UPDATE statements.
17 **
18 ** $Id: update.c,v 1.70.2.1 2004/04/29 16:16:29 drh Exp $
19 */
20 #include "sqliteInt.h"
21 
22 /*
23 ** Process an UPDATE statement.
24 **
25 **   UPDATE OR IGNORE table_wxyz SET a=b, c=d WHERE e<5 AND f NOT NULL;
26 **          \_______/ \________/     \______/       \________________/
27 *            onError   pTabList      pChanges             pWhere
28 */
sqliteUpdate(Parse * pParse,SrcList * pTabList,ExprList * pChanges,Expr * pWhere,int onError)29 void sqliteUpdate(
30   Parse *pParse,         /* The parser context */
31   SrcList *pTabList,     /* The table in which we should change things */
32   ExprList *pChanges,    /* Things to be changed */
33   Expr *pWhere,          /* The WHERE clause.  May be null */
34   int onError            /* How to handle constraint errors */
35 ){
36   int i, j;              /* Loop counters */
37   Table *pTab;           /* The table to be updated */
38   int loopStart;         /* VDBE instruction address of the start of the loop */
39   int jumpInst;          /* Addr of VDBE instruction to jump out of loop */
40   WhereInfo *pWInfo;     /* Information about the WHERE clause */
41   Vdbe *v;               /* The virtual database engine */
42   Index *pIdx;           /* For looping over indices */
43   int nIdx;              /* Number of indices that need updating */
44   int nIdxTotal;         /* Total number of indices */
45   int iCur;              /* VDBE Cursor number of pTab */
46   sqlite *db;            /* The database structure */
47   Index **apIdx = 0;     /* An array of indices that need updating too */
48   char *aIdxUsed = 0;    /* aIdxUsed[i]==1 if the i-th index is used */
49   int *aXRef = 0;        /* aXRef[i] is the index in pChanges->a[] of the
50                          ** an expression for the i-th column of the table.
51                          ** aXRef[i]==-1 if the i-th column is not changed. */
52   int chngRecno;         /* True if the record number is being changed */
53   Expr *pRecnoExpr;      /* Expression defining the new record number */
54   int openAll;           /* True if all indices need to be opened */
55   int isView;            /* Trying to update a view */
56   int iStackDepth;       /* Index of memory cell holding stack depth */
57   AuthContext sContext;  /* The authorization context */
58 
59   int before_triggers;         /* True if there are any BEFORE triggers */
60   int after_triggers;          /* True if there are any AFTER triggers */
61   int row_triggers_exist = 0;  /* True if any row triggers exist */
62 
63   int newIdx      = -1;  /* index of trigger "new" temp table       */
64   int oldIdx      = -1;  /* index of trigger "old" temp table       */
65 
66   sContext.pParse = 0;
67   if( pParse->nErr || sqlite_malloc_failed ) goto update_cleanup;
68   db = pParse->db;
69   assert( pTabList->nSrc==1 );
70   iStackDepth = pParse->nMem++;
71 
72   /* Locate the table which we want to update.
73   */
74   pTab = sqliteSrcListLookup(pParse, pTabList);
75   if( pTab==0 ) goto update_cleanup;
76   before_triggers = sqliteTriggersExist(pParse, pTab->pTrigger,
77             TK_UPDATE, TK_BEFORE, TK_ROW, pChanges);
78   after_triggers = sqliteTriggersExist(pParse, pTab->pTrigger,
79             TK_UPDATE, TK_AFTER, TK_ROW, pChanges);
80   row_triggers_exist = before_triggers || after_triggers;
81   isView = pTab->pSelect!=0;
82   if( sqliteIsReadOnly(pParse, pTab, before_triggers) ){
83     goto update_cleanup;
84   }
85   if( isView ){
86     if( sqliteViewGetColumnNames(pParse, pTab) ){
87       goto update_cleanup;
88     }
89   }
90   aXRef = sqliteMalloc( sizeof(int) * pTab->nCol );
91   if( aXRef==0 ) goto update_cleanup;
92   for(i=0; i<pTab->nCol; i++) aXRef[i] = -1;
93 
94   /* If there are FOR EACH ROW triggers, allocate cursors for the
95   ** special OLD and NEW tables
96   */
97   if( row_triggers_exist ){
98     newIdx = pParse->nTab++;
99     oldIdx = pParse->nTab++;
100   }
101 
102   /* Allocate a cursors for the main database table and for all indices.
103   ** The index cursors might not be used, but if they are used they
104   ** need to occur right after the database cursor.  So go ahead and
105   ** allocate enough space, just in case.
106   */
107   pTabList->a[0].iCursor = iCur = pParse->nTab++;
108   for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
109     pParse->nTab++;
110   }
111 
112   /* Resolve the column names in all the expressions of the
113   ** of the UPDATE statement.  Also find the column index
114   ** for each column to be updated in the pChanges array.  For each
115   ** column to be updated, make sure we have authorization to change
116   ** that column.
117   */
118   chngRecno = 0;
119   for(i=0; i<pChanges->nExpr; i++){
120     if( sqliteExprResolveIds(pParse, pTabList, 0, pChanges->a[i].pExpr) ){
121       goto update_cleanup;
122     }
123     if( sqliteExprCheck(pParse, pChanges->a[i].pExpr, 0, 0) ){
124       goto update_cleanup;
125     }
126     for(j=0; j<pTab->nCol; j++){
127       if( sqliteStrICmp(pTab->aCol[j].zName, pChanges->a[i].zName)==0 ){
128         if( j==pTab->iPKey ){
129           chngRecno = 1;
130           pRecnoExpr = pChanges->a[i].pExpr;
131         }
132         aXRef[j] = i;
133         break;
134       }
135     }
136     if( j>=pTab->nCol ){
137       if( sqliteIsRowid(pChanges->a[i].zName) ){
138         chngRecno = 1;
139         pRecnoExpr = pChanges->a[i].pExpr;
140       }else{
141         sqliteErrorMsg(pParse, "no such column: %s", pChanges->a[i].zName);
142         goto update_cleanup;
143       }
144     }
145 #ifndef SQLITE_OMIT_AUTHORIZATION
146     {
147       int rc;
148       rc = sqliteAuthCheck(pParse, SQLITE_UPDATE, pTab->zName,
149                            pTab->aCol[j].zName, db->aDb[pTab->iDb].zName);
150       if( rc==SQLITE_DENY ){
151         goto update_cleanup;
152       }else if( rc==SQLITE_IGNORE ){
153         aXRef[j] = -1;
154       }
155     }
156 #endif
157   }
158 
159   /* Allocate memory for the array apIdx[] and fill it with pointers to every
160   ** index that needs to be updated.  Indices only need updating if their
161   ** key includes one of the columns named in pChanges or if the record
162   ** number of the original table entry is changing.
163   */
164   for(nIdx=nIdxTotal=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdxTotal++){
165     if( chngRecno ){
166       i = 0;
167     }else {
168       for(i=0; i<pIdx->nColumn; i++){
169         if( aXRef[pIdx->aiColumn[i]]>=0 ) break;
170       }
171     }
172     if( i<pIdx->nColumn ) nIdx++;
173   }
174   if( nIdxTotal>0 ){
175     apIdx = sqliteMalloc( sizeof(Index*) * nIdx + nIdxTotal );
176     if( apIdx==0 ) goto update_cleanup;
177     aIdxUsed = (char*)&apIdx[nIdx];
178   }
179   for(nIdx=j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){
180     if( chngRecno ){
181       i = 0;
182     }else{
183       for(i=0; i<pIdx->nColumn; i++){
184         if( aXRef[pIdx->aiColumn[i]]>=0 ) break;
185       }
186     }
187     if( i<pIdx->nColumn ){
188       apIdx[nIdx++] = pIdx;
189       aIdxUsed[j] = 1;
190     }else{
191       aIdxUsed[j] = 0;
192     }
193   }
194 
195   /* Resolve the column names in all the expressions in the
196   ** WHERE clause.
197   */
198   if( pWhere ){
199     if( sqliteExprResolveIds(pParse, pTabList, 0, pWhere) ){
200       goto update_cleanup;
201     }
202     if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
203       goto update_cleanup;
204     }
205   }
206 
207   /* Start the view context
208   */
209   if( isView ){
210     sqliteAuthContextPush(pParse, &sContext, pTab->zName);
211   }
212 
213   /* Begin generating code.
214   */
215   v = sqliteGetVdbe(pParse);
216   if( v==0 ) goto update_cleanup;
217   sqliteBeginWriteOperation(pParse, 1, pTab->iDb);
218 
219   /* If we are trying to update a view, construct that view into
220   ** a temporary table.
221   */
222   if( isView ){
223     Select *pView;
224     pView = sqliteSelectDup(pTab->pSelect);
225     sqliteSelect(pParse, pView, SRT_TempTable, iCur, 0, 0, 0);
226     sqliteSelectDelete(pView);
227   }
228 
229   /* Begin the database scan
230   */
231   pWInfo = sqliteWhereBegin(pParse, pTabList, pWhere, 1, 0);
232   if( pWInfo==0 ) goto update_cleanup;
233 
234   /* Remember the index of every item to be updated.
235   */
236   sqliteVdbeAddOp(v, OP_ListWrite, 0, 0);
237 
238   /* End the database scan loop.
239   */
240   sqliteWhereEnd(pWInfo);
241 
242   /* Initialize the count of updated rows
243   */
244   if( db->flags & SQLITE_CountRows && !pParse->trigStack ){
245     sqliteVdbeAddOp(v, OP_Integer, 0, 0);
246   }
247 
248   if( row_triggers_exist ){
249     /* Create pseudo-tables for NEW and OLD
250     */
251     sqliteVdbeAddOp(v, OP_OpenPseudo, oldIdx, 0);
252     sqliteVdbeAddOp(v, OP_OpenPseudo, newIdx, 0);
253 
254     /* The top of the update loop for when there are triggers.
255     */
256     sqliteVdbeAddOp(v, OP_ListRewind, 0, 0);
257     sqliteVdbeAddOp(v, OP_StackDepth, 0, 0);
258     sqliteVdbeAddOp(v, OP_MemStore, iStackDepth, 1);
259     loopStart = sqliteVdbeAddOp(v, OP_MemLoad, iStackDepth, 0);
260     sqliteVdbeAddOp(v, OP_StackReset, 0, 0);
261     jumpInst = sqliteVdbeAddOp(v, OP_ListRead, 0, 0);
262     sqliteVdbeAddOp(v, OP_Dup, 0, 0);
263 
264     /* Open a cursor and make it point to the record that is
265     ** being updated.
266     */
267     sqliteVdbeAddOp(v, OP_Dup, 0, 0);
268     if( !isView ){
269       sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
270       sqliteVdbeAddOp(v, OP_OpenRead, iCur, pTab->tnum);
271     }
272     sqliteVdbeAddOp(v, OP_MoveTo, iCur, 0);
273 
274     /* Generate the OLD table
275     */
276     sqliteVdbeAddOp(v, OP_Recno, iCur, 0);
277     sqliteVdbeAddOp(v, OP_RowData, iCur, 0);
278     sqliteVdbeAddOp(v, OP_PutIntKey, oldIdx, 0);
279 
280     /* Generate the NEW table
281     */
282     if( chngRecno ){
283       sqliteExprCode(pParse, pRecnoExpr);
284     }else{
285       sqliteVdbeAddOp(v, OP_Recno, iCur, 0);
286     }
287     for(i=0; i<pTab->nCol; i++){
288       if( i==pTab->iPKey ){
289         sqliteVdbeAddOp(v, OP_String, 0, 0);
290         continue;
291       }
292       j = aXRef[i];
293       if( j<0 ){
294         sqliteVdbeAddOp(v, OP_Column, iCur, i);
295       }else{
296         sqliteExprCode(pParse, pChanges->a[j].pExpr);
297       }
298     }
299     sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
300     sqliteVdbeAddOp(v, OP_PutIntKey, newIdx, 0);
301     if( !isView ){
302       sqliteVdbeAddOp(v, OP_Close, iCur, 0);
303     }
304 
305     /* Fire the BEFORE and INSTEAD OF triggers
306     */
307     if( sqliteCodeRowTrigger(pParse, TK_UPDATE, pChanges, TK_BEFORE, pTab,
308           newIdx, oldIdx, onError, loopStart) ){
309       goto update_cleanup;
310     }
311   }
312 
313   if( !isView ){
314     /*
315     ** Open every index that needs updating.  Note that if any
316     ** index could potentially invoke a REPLACE conflict resolution
317     ** action, then we need to open all indices because we might need
318     ** to be deleting some records.
319     */
320     sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
321     sqliteVdbeAddOp(v, OP_OpenWrite, iCur, pTab->tnum);
322     if( onError==OE_Replace ){
323       openAll = 1;
324     }else{
325       openAll = 0;
326       for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
327         if( pIdx->onError==OE_Replace ){
328           openAll = 1;
329           break;
330         }
331       }
332     }
333     for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
334       if( openAll || aIdxUsed[i] ){
335         sqliteVdbeAddOp(v, OP_Integer, pIdx->iDb, 0);
336         sqliteVdbeAddOp(v, OP_OpenWrite, iCur+i+1, pIdx->tnum);
337         assert( pParse->nTab>iCur+i+1 );
338       }
339     }
340 
341     /* Loop over every record that needs updating.  We have to load
342     ** the old data for each record to be updated because some columns
343     ** might not change and we will need to copy the old value.
344     ** Also, the old data is needed to delete the old index entires.
345     ** So make the cursor point at the old record.
346     */
347     if( !row_triggers_exist ){
348       sqliteVdbeAddOp(v, OP_ListRewind, 0, 0);
349       jumpInst = loopStart = sqliteVdbeAddOp(v, OP_ListRead, 0, 0);
350       sqliteVdbeAddOp(v, OP_Dup, 0, 0);
351     }
352     sqliteVdbeAddOp(v, OP_NotExists, iCur, loopStart);
353 
354     /* If the record number will change, push the record number as it
355     ** will be after the update. (The old record number is currently
356     ** on top of the stack.)
357     */
358     if( chngRecno ){
359       sqliteExprCode(pParse, pRecnoExpr);
360       sqliteVdbeAddOp(v, OP_MustBeInt, 0, 0);
361     }
362 
363     /* Compute new data for this record.
364     */
365     for(i=0; i<pTab->nCol; i++){
366       if( i==pTab->iPKey ){
367         sqliteVdbeAddOp(v, OP_String, 0, 0);
368         continue;
369       }
370       j = aXRef[i];
371       if( j<0 ){
372         sqliteVdbeAddOp(v, OP_Column, iCur, i);
373       }else{
374         sqliteExprCode(pParse, pChanges->a[j].pExpr);
375       }
376     }
377 
378     /* Do constraint checks
379     */
380     sqliteGenerateConstraintChecks(pParse, pTab, iCur, aIdxUsed, chngRecno, 1,
381                                    onError, loopStart);
382 
383     /* Delete the old indices for the current record.
384     */
385     sqliteGenerateRowIndexDelete(db, v, pTab, iCur, aIdxUsed);
386 
387     /* If changing the record number, delete the old record.
388     */
389     if( chngRecno ){
390       sqliteVdbeAddOp(v, OP_Delete, iCur, 0);
391     }
392 
393     /* Create the new index entries and the new record.
394     */
395     sqliteCompleteInsertion(pParse, pTab, iCur, aIdxUsed, chngRecno, 1, -1);
396   }
397 
398   /* Increment the row counter
399   */
400   if( db->flags & SQLITE_CountRows && !pParse->trigStack){
401     sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
402   }
403 
404   /* If there are triggers, close all the cursors after each iteration
405   ** through the loop.  The fire the after triggers.
406   */
407   if( row_triggers_exist ){
408     if( !isView ){
409       for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
410         if( openAll || aIdxUsed[i] )
411           sqliteVdbeAddOp(v, OP_Close, iCur+i+1, 0);
412       }
413       sqliteVdbeAddOp(v, OP_Close, iCur, 0);
414       pParse->nTab = iCur;
415     }
416     if( sqliteCodeRowTrigger(pParse, TK_UPDATE, pChanges, TK_AFTER, pTab,
417           newIdx, oldIdx, onError, loopStart) ){
418       goto update_cleanup;
419     }
420   }
421 
422   /* Repeat the above with the next record to be updated, until
423   ** all record selected by the WHERE clause have been updated.
424   */
425   sqliteVdbeAddOp(v, OP_Goto, 0, loopStart);
426   sqliteVdbeChangeP2(v, jumpInst, sqliteVdbeCurrentAddr(v));
427   sqliteVdbeAddOp(v, OP_ListReset, 0, 0);
428 
429   /* Close all tables if there were no FOR EACH ROW triggers */
430   if( !row_triggers_exist ){
431     for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
432       if( openAll || aIdxUsed[i] ){
433         sqliteVdbeAddOp(v, OP_Close, iCur+i+1, 0);
434       }
435     }
436     sqliteVdbeAddOp(v, OP_Close, iCur, 0);
437     pParse->nTab = iCur;
438   }else{
439     sqliteVdbeAddOp(v, OP_Close, newIdx, 0);
440     sqliteVdbeAddOp(v, OP_Close, oldIdx, 0);
441   }
442 
443   sqliteVdbeAddOp(v, OP_SetCounts, 0, 0);
444   sqliteEndWriteOperation(pParse);
445 
446   /*
447   ** Return the number of rows that were changed.
448   */
449   if( db->flags & SQLITE_CountRows && !pParse->trigStack ){
450     sqliteVdbeOp3(v, OP_ColumnName, 0, 1, "rows updated", P3_STATIC);
451     sqliteVdbeAddOp(v, OP_Callback, 1, 0);
452   }
453 
454 update_cleanup:
455   sqliteAuthContextPop(&sContext);
456   sqliteFree(apIdx);
457   sqliteFree(aXRef);
458   sqliteSrcListDelete(pTabList);
459   sqliteExprListDelete(pChanges);
460   sqliteExprDelete(pWhere);
461   return;
462 }
463