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