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: update.c,v 1.70.2.1 2004/04/29 16:16:29 drh Exp $
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