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*/
29void 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
454update_cleanup:
455  sqliteAuthContextPop(&sContext);
456  sqliteFree(apIdx);
457  sqliteFree(aXRef);
458  sqliteSrcListDelete(pTabList);
459  sqliteExprListDelete(pChanges);
460  sqliteExprDelete(pWhere);
461  return;
462}
463