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 195361 2005-09-07 15:11:33Z iliaa $
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 : */
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 2 : ){
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 2 : Index **apIdx = 0; /* An array of indices that need updating too */
45 2 : char *aIdxUsed = 0; /* aIdxUsed[i]==1 if the i-th index is used */
46 2 : 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 2 : int row_triggers_exist = 0; /* True if any row triggers exist */
59 :
60 2 : int newIdx = -1; /* index of trigger "new" temp table */
61 2 : int oldIdx = -1; /* index of trigger "old" temp table */
62 :
63 2 : sContext.pParse = 0;
64 2 : if( pParse->nErr || sqlite_malloc_failed ) goto update_cleanup;
65 2 : db = pParse->db;
66 : assert( pTabList->nSrc==1 );
67 2 : iStackDepth = pParse->nMem++;
68 :
69 : /* Locate the table which we want to update.
70 : */
71 2 : pTab = sqliteSrcListLookup(pParse, pTabList);
72 2 : if( pTab==0 ) goto update_cleanup;
73 1 : before_triggers = sqliteTriggersExist(pParse, pTab->pTrigger,
74 : TK_UPDATE, TK_BEFORE, TK_ROW, pChanges);
75 1 : after_triggers = sqliteTriggersExist(pParse, pTab->pTrigger,
76 : TK_UPDATE, TK_AFTER, TK_ROW, pChanges);
77 1 : row_triggers_exist = before_triggers || after_triggers;
78 1 : isView = pTab->pSelect!=0;
79 1 : if( sqliteIsReadOnly(pParse, pTab, before_triggers) ){
80 0 : goto update_cleanup;
81 : }
82 1 : if( isView ){
83 0 : if( sqliteViewGetColumnNames(pParse, pTab) ){
84 0 : goto update_cleanup;
85 : }
86 : }
87 1 : aXRef = sqliteMalloc( sizeof(int) * pTab->nCol );
88 1 : if( aXRef==0 ) goto update_cleanup;
89 1 : 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 1 : if( row_triggers_exist ){
95 0 : newIdx = pParse->nTab++;
96 0 : 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 1 : pTabList->a[0].iCursor = iCur = pParse->nTab++;
105 1 : for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
106 0 : 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 1 : chngRecno = 0;
116 2 : for(i=0; i<pChanges->nExpr; i++){
117 1 : if( sqliteExprResolveIds(pParse, pTabList, 0, pChanges->a[i].pExpr) ){
118 0 : goto update_cleanup;
119 : }
120 1 : if( sqliteExprCheck(pParse, pChanges->a[i].pExpr, 0, 0) ){
121 0 : goto update_cleanup;
122 : }
123 1 : for(j=0; j<pTab->nCol; j++){
124 1 : if( sqliteStrICmp(pTab->aCol[j].zName, pChanges->a[i].zName)==0 ){
125 1 : if( j==pTab->iPKey ){
126 0 : chngRecno = 1;
127 0 : pRecnoExpr = pChanges->a[i].pExpr;
128 : }
129 1 : aXRef[j] = i;
130 1 : break;
131 : }
132 : }
133 1 : if( j>=pTab->nCol ){
134 0 : if( sqliteIsRowid(pChanges->a[i].zName) ){
135 0 : chngRecno = 1;
136 0 : pRecnoExpr = pChanges->a[i].pExpr;
137 : }else{
138 0 : sqliteErrorMsg(pParse, "no such column: %s", pChanges->a[i].zName);
139 0 : goto update_cleanup;
140 : }
141 : }
142 : #ifndef SQLITE_OMIT_AUTHORIZATION
143 : {
144 : int rc;
145 1 : rc = sqliteAuthCheck(pParse, SQLITE_UPDATE, pTab->zName,
146 : pTab->aCol[j].zName, db->aDb[pTab->iDb].zName);
147 1 : if( rc==SQLITE_DENY ){
148 0 : goto update_cleanup;
149 1 : }else if( rc==SQLITE_IGNORE ){
150 0 : 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 1 : for(nIdx=nIdxTotal=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdxTotal++){
162 0 : if( chngRecno ){
163 0 : i = 0;
164 : }else {
165 0 : for(i=0; i<pIdx->nColumn; i++){
166 0 : if( aXRef[pIdx->aiColumn[i]]>=0 ) break;
167 : }
168 : }
169 0 : if( i<pIdx->nColumn ) nIdx++;
170 : }
171 1 : if( nIdxTotal>0 ){
172 0 : apIdx = sqliteMalloc( sizeof(Index*) * nIdx + nIdxTotal );
173 0 : if( apIdx==0 ) goto update_cleanup;
174 0 : aIdxUsed = (char*)&apIdx[nIdx];
175 : }
176 1 : for(nIdx=j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){
177 0 : if( chngRecno ){
178 0 : i = 0;
179 : }else{
180 0 : for(i=0; i<pIdx->nColumn; i++){
181 0 : if( aXRef[pIdx->aiColumn[i]]>=0 ) break;
182 : }
183 : }
184 0 : if( i<pIdx->nColumn ){
185 0 : apIdx[nIdx++] = pIdx;
186 0 : aIdxUsed[j] = 1;
187 : }else{
188 0 : aIdxUsed[j] = 0;
189 : }
190 : }
191 :
192 : /* Resolve the column names in all the expressions in the
193 : ** WHERE clause.
194 : */
195 1 : if( pWhere ){
196 1 : if( sqliteExprResolveIds(pParse, pTabList, 0, pWhere) ){
197 0 : goto update_cleanup;
198 : }
199 1 : if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
200 0 : goto update_cleanup;
201 : }
202 : }
203 :
204 : /* Start the view context
205 : */
206 1 : if( isView ){
207 0 : sqliteAuthContextPush(pParse, &sContext, pTab->zName);
208 : }
209 :
210 : /* Begin generating code.
211 : */
212 1 : v = sqliteGetVdbe(pParse);
213 1 : if( v==0 ) goto update_cleanup;
214 1 : 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 1 : if( isView ){
220 : Select *pView;
221 0 : pView = sqliteSelectDup(pTab->pSelect);
222 0 : sqliteSelect(pParse, pView, SRT_TempTable, iCur, 0, 0, 0);
223 0 : sqliteSelectDelete(pView);
224 : }
225 :
226 : /* Begin the database scan
227 : */
228 1 : pWInfo = sqliteWhereBegin(pParse, pTabList, pWhere, 1, 0);
229 1 : if( pWInfo==0 ) goto update_cleanup;
230 :
231 : /* Remember the index of every item to be updated.
232 : */
233 1 : sqliteVdbeAddOp(v, OP_ListWrite, 0, 0);
234 :
235 : /* End the database scan loop.
236 : */
237 1 : sqliteWhereEnd(pWInfo);
238 :
239 : /* Initialize the count of updated rows
240 : */
241 1 : if( db->flags & SQLITE_CountRows && !pParse->trigStack ){
242 0 : sqliteVdbeAddOp(v, OP_Integer, 0, 0);
243 : }
244 :
245 1 : if( row_triggers_exist ){
246 : /* Create pseudo-tables for NEW and OLD
247 : */
248 0 : sqliteVdbeAddOp(v, OP_OpenPseudo, oldIdx, 0);
249 0 : sqliteVdbeAddOp(v, OP_OpenPseudo, newIdx, 0);
250 :
251 : /* The top of the update loop for when there are triggers.
252 : */
253 0 : sqliteVdbeAddOp(v, OP_ListRewind, 0, 0);
254 0 : sqliteVdbeAddOp(v, OP_StackDepth, 0, 0);
255 0 : sqliteVdbeAddOp(v, OP_MemStore, iStackDepth, 1);
256 0 : loopStart = sqliteVdbeAddOp(v, OP_MemLoad, iStackDepth, 0);
257 0 : sqliteVdbeAddOp(v, OP_StackReset, 0, 0);
258 0 : jumpInst = sqliteVdbeAddOp(v, OP_ListRead, 0, 0);
259 0 : 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 0 : sqliteVdbeAddOp(v, OP_Dup, 0, 0);
265 0 : if( !isView ){
266 0 : sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
267 0 : sqliteVdbeAddOp(v, OP_OpenRead, iCur, pTab->tnum);
268 : }
269 0 : sqliteVdbeAddOp(v, OP_MoveTo, iCur, 0);
270 :
271 : /* Generate the OLD table
272 : */
273 0 : sqliteVdbeAddOp(v, OP_Recno, iCur, 0);
274 0 : sqliteVdbeAddOp(v, OP_RowData, iCur, 0);
275 0 : sqliteVdbeAddOp(v, OP_PutIntKey, oldIdx, 0);
276 :
277 : /* Generate the NEW table
278 : */
279 0 : if( chngRecno ){
280 0 : sqliteExprCode(pParse, pRecnoExpr);
281 : }else{
282 0 : sqliteVdbeAddOp(v, OP_Recno, iCur, 0);
283 : }
284 0 : for(i=0; i<pTab->nCol; i++){
285 0 : if( i==pTab->iPKey ){
286 0 : sqliteVdbeAddOp(v, OP_String, 0, 0);
287 0 : continue;
288 : }
289 0 : j = aXRef[i];
290 0 : if( j<0 ){
291 0 : sqliteVdbeAddOp(v, OP_Column, iCur, i);
292 : }else{
293 0 : sqliteExprCode(pParse, pChanges->a[j].pExpr);
294 : }
295 : }
296 0 : sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
297 0 : sqliteVdbeAddOp(v, OP_PutIntKey, newIdx, 0);
298 0 : if( !isView ){
299 0 : sqliteVdbeAddOp(v, OP_Close, iCur, 0);
300 : }
301 :
302 : /* Fire the BEFORE and INSTEAD OF triggers
303 : */
304 0 : if( sqliteCodeRowTrigger(pParse, TK_UPDATE, pChanges, TK_BEFORE, pTab,
305 : newIdx, oldIdx, onError, loopStart) ){
306 0 : goto update_cleanup;
307 : }
308 : }
309 :
310 1 : 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 1 : sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
318 1 : sqliteVdbeAddOp(v, OP_OpenWrite, iCur, pTab->tnum);
319 1 : if( onError==OE_Replace ){
320 0 : openAll = 1;
321 : }else{
322 1 : openAll = 0;
323 1 : for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
324 0 : if( pIdx->onError==OE_Replace ){
325 0 : openAll = 1;
326 0 : break;
327 : }
328 : }
329 : }
330 1 : for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
331 0 : if( openAll || aIdxUsed[i] ){
332 0 : sqliteVdbeAddOp(v, OP_Integer, pIdx->iDb, 0);
333 0 : 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 1 : if( !row_triggers_exist ){
345 1 : sqliteVdbeAddOp(v, OP_ListRewind, 0, 0);
346 1 : jumpInst = loopStart = sqliteVdbeAddOp(v, OP_ListRead, 0, 0);
347 1 : sqliteVdbeAddOp(v, OP_Dup, 0, 0);
348 : }
349 1 : 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 1 : if( chngRecno ){
356 0 : sqliteExprCode(pParse, pRecnoExpr);
357 0 : sqliteVdbeAddOp(v, OP_MustBeInt, 0, 0);
358 : }
359 :
360 : /* Compute new data for this record.
361 : */
362 2 : for(i=0; i<pTab->nCol; i++){
363 1 : if( i==pTab->iPKey ){
364 0 : sqliteVdbeAddOp(v, OP_String, 0, 0);
365 0 : continue;
366 : }
367 1 : j = aXRef[i];
368 1 : if( j<0 ){
369 0 : sqliteVdbeAddOp(v, OP_Column, iCur, i);
370 : }else{
371 1 : sqliteExprCode(pParse, pChanges->a[j].pExpr);
372 : }
373 : }
374 :
375 : /* Do constraint checks
376 : */
377 1 : sqliteGenerateConstraintChecks(pParse, pTab, iCur, aIdxUsed, chngRecno, 1,
378 : onError, loopStart);
379 :
380 : /* Delete the old indices for the current record.
381 : */
382 1 : sqliteGenerateRowIndexDelete(db, v, pTab, iCur, aIdxUsed);
383 :
384 : /* If changing the record number, delete the old record.
385 : */
386 1 : if( chngRecno ){
387 0 : sqliteVdbeAddOp(v, OP_Delete, iCur, 0);
388 : }
389 :
390 : /* Create the new index entries and the new record.
391 : */
392 1 : sqliteCompleteInsertion(pParse, pTab, iCur, aIdxUsed, chngRecno, 1, -1);
393 : }
394 :
395 : /* Increment the row counter
396 : */
397 1 : if( db->flags & SQLITE_CountRows && !pParse->trigStack){
398 0 : 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 1 : if( row_triggers_exist ){
405 0 : if( !isView ){
406 0 : for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
407 0 : if( openAll || aIdxUsed[i] )
408 0 : sqliteVdbeAddOp(v, OP_Close, iCur+i+1, 0);
409 : }
410 0 : sqliteVdbeAddOp(v, OP_Close, iCur, 0);
411 0 : pParse->nTab = iCur;
412 : }
413 0 : if( sqliteCodeRowTrigger(pParse, TK_UPDATE, pChanges, TK_AFTER, pTab,
414 : newIdx, oldIdx, onError, loopStart) ){
415 0 : 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 1 : sqliteVdbeAddOp(v, OP_Goto, 0, loopStart);
423 1 : sqliteVdbeChangeP2(v, jumpInst, sqliteVdbeCurrentAddr(v));
424 1 : sqliteVdbeAddOp(v, OP_ListReset, 0, 0);
425 :
426 : /* Close all tables if there were no FOR EACH ROW triggers */
427 1 : if( !row_triggers_exist ){
428 1 : for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
429 0 : if( openAll || aIdxUsed[i] ){
430 0 : sqliteVdbeAddOp(v, OP_Close, iCur+i+1, 0);
431 : }
432 : }
433 1 : sqliteVdbeAddOp(v, OP_Close, iCur, 0);
434 1 : pParse->nTab = iCur;
435 : }else{
436 0 : sqliteVdbeAddOp(v, OP_Close, newIdx, 0);
437 0 : sqliteVdbeAddOp(v, OP_Close, oldIdx, 0);
438 : }
439 :
440 1 : sqliteVdbeAddOp(v, OP_SetCounts, 0, 0);
441 1 : sqliteEndWriteOperation(pParse);
442 :
443 : /*
444 : ** Return the number of rows that were changed.
445 : */
446 1 : if( db->flags & SQLITE_CountRows && !pParse->trigStack ){
447 0 : sqliteVdbeOp3(v, OP_ColumnName, 0, 1, "rows updated", P3_STATIC);
448 0 : sqliteVdbeAddOp(v, OP_Callback, 1, 0);
449 : }
450 :
451 2 : update_cleanup:
452 2 : sqliteAuthContextPop(&sContext);
453 2 : sqliteFree(apIdx);
454 2 : sqliteFree(aXRef);
455 2 : sqliteSrcListDelete(pTabList);
456 2 : sqliteExprListDelete(pChanges);
457 2 : sqliteExprDelete(pWhere);
458 : return;
459 : }
|
