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 */ 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