Back to index

php5  5.3.10
update.c
Go to the documentation of this file.
00001 /*
00002 ** 2001 September 15
00003 **
00004 ** The author disclaims copyright to this source code.  In place of
00005 ** a legal notice, here is a blessing:
00006 **
00007 **    May you do good and not evil.
00008 **    May you find forgiveness for yourself and forgive others.
00009 **    May you share freely, never taking more than you give.
00010 **
00011 *************************************************************************
00012 ** This file contains C code routines that are called by the parser
00013 ** to handle UPDATE statements.
00014 **
00015 ** $Id: update.c 195361 2005-09-07 15:11:33Z iliaa $
00016 */
00017 #include "sqliteInt.h"
00018 
00019 /*
00020 ** Process an UPDATE statement.
00021 **
00022 **   UPDATE OR IGNORE table_wxyz SET a=b, c=d WHERE e<5 AND f NOT NULL;
00023 **          \_______/ \________/     \______/       \________________/
00024 *            onError   pTabList      pChanges             pWhere
00025 */
00026 void sqliteUpdate(
00027   Parse *pParse,         /* The parser context */
00028   SrcList *pTabList,     /* The table in which we should change things */
00029   ExprList *pChanges,    /* Things to be changed */
00030   Expr *pWhere,          /* The WHERE clause.  May be null */
00031   int onError            /* How to handle constraint errors */
00032 ){
00033   int i, j;              /* Loop counters */
00034   Table *pTab;           /* The table to be updated */
00035   int loopStart;         /* VDBE instruction address of the start of the loop */
00036   int jumpInst;          /* Addr of VDBE instruction to jump out of loop */
00037   WhereInfo *pWInfo;     /* Information about the WHERE clause */
00038   Vdbe *v;               /* The virtual database engine */
00039   Index *pIdx;           /* For looping over indices */
00040   int nIdx;              /* Number of indices that need updating */
00041   int nIdxTotal;         /* Total number of indices */
00042   int iCur;              /* VDBE Cursor number of pTab */
00043   sqlite *db;            /* The database structure */
00044   Index **apIdx = 0;     /* An array of indices that need updating too */
00045   char *aIdxUsed = 0;    /* aIdxUsed[i]==1 if the i-th index is used */
00046   int *aXRef = 0;        /* aXRef[i] is the index in pChanges->a[] of the
00047                          ** an expression for the i-th column of the table.
00048                          ** aXRef[i]==-1 if the i-th column is not changed. */
00049   int chngRecno;         /* True if the record number is being changed */
00050   Expr *pRecnoExpr;      /* Expression defining the new record number */
00051   int openAll;           /* True if all indices need to be opened */
00052   int isView;            /* Trying to update a view */
00053   int iStackDepth;       /* Index of memory cell holding stack depth */
00054   AuthContext sContext;  /* The authorization context */
00055 
00056   int before_triggers;         /* True if there are any BEFORE triggers */
00057   int after_triggers;          /* True if there are any AFTER triggers */
00058   int row_triggers_exist = 0;  /* True if any row triggers exist */
00059 
00060   int newIdx      = -1;  /* index of trigger "new" temp table       */
00061   int oldIdx      = -1;  /* index of trigger "old" temp table       */
00062 
00063   sContext.pParse = 0;
00064   if( pParse->nErr || sqlite_malloc_failed ) goto update_cleanup;
00065   db = pParse->db;
00066   assert( pTabList->nSrc==1 );
00067   iStackDepth = pParse->nMem++;
00068 
00069   /* Locate the table which we want to update. 
00070   */
00071   pTab = sqliteSrcListLookup(pParse, pTabList);
00072   if( pTab==0 ) goto update_cleanup;
00073   before_triggers = sqliteTriggersExist(pParse, pTab->pTrigger, 
00074             TK_UPDATE, TK_BEFORE, TK_ROW, pChanges);
00075   after_triggers = sqliteTriggersExist(pParse, pTab->pTrigger, 
00076             TK_UPDATE, TK_AFTER, TK_ROW, pChanges);
00077   row_triggers_exist = before_triggers || after_triggers;
00078   isView = pTab->pSelect!=0;
00079   if( sqliteIsReadOnly(pParse, pTab, before_triggers) ){
00080     goto update_cleanup;
00081   }
00082   if( isView ){
00083     if( sqliteViewGetColumnNames(pParse, pTab) ){
00084       goto update_cleanup;
00085     }
00086   }
00087   aXRef = sqliteMalloc( sizeof(int) * pTab->nCol );
00088   if( aXRef==0 ) goto update_cleanup;
00089   for(i=0; i<pTab->nCol; i++) aXRef[i] = -1;
00090 
00091   /* If there are FOR EACH ROW triggers, allocate cursors for the
00092   ** special OLD and NEW tables
00093   */
00094   if( row_triggers_exist ){
00095     newIdx = pParse->nTab++;
00096     oldIdx = pParse->nTab++;
00097   }
00098 
00099   /* Allocate a cursors for the main database table and for all indices.
00100   ** The index cursors might not be used, but if they are used they
00101   ** need to occur right after the database cursor.  So go ahead and
00102   ** allocate enough space, just in case.
00103   */
00104   pTabList->a[0].iCursor = iCur = pParse->nTab++;
00105   for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
00106     pParse->nTab++;
00107   }
00108 
00109   /* Resolve the column names in all the expressions of the
00110   ** of the UPDATE statement.  Also find the column index
00111   ** for each column to be updated in the pChanges array.  For each
00112   ** column to be updated, make sure we have authorization to change
00113   ** that column.
00114   */
00115   chngRecno = 0;
00116   for(i=0; i<pChanges->nExpr; i++){
00117     if( sqliteExprResolveIds(pParse, pTabList, 0, pChanges->a[i].pExpr) ){
00118       goto update_cleanup;
00119     }
00120     if( sqliteExprCheck(pParse, pChanges->a[i].pExpr, 0, 0) ){
00121       goto update_cleanup;
00122     }
00123     for(j=0; j<pTab->nCol; j++){
00124       if( sqliteStrICmp(pTab->aCol[j].zName, pChanges->a[i].zName)==0 ){
00125         if( j==pTab->iPKey ){
00126           chngRecno = 1;
00127           pRecnoExpr = pChanges->a[i].pExpr;
00128         }
00129         aXRef[j] = i;
00130         break;
00131       }
00132     }
00133     if( j>=pTab->nCol ){
00134       if( sqliteIsRowid(pChanges->a[i].zName) ){
00135         chngRecno = 1;
00136         pRecnoExpr = pChanges->a[i].pExpr;
00137       }else{
00138         sqliteErrorMsg(pParse, "no such column: %s", pChanges->a[i].zName);
00139         goto update_cleanup;
00140       }
00141     }
00142 #ifndef SQLITE_OMIT_AUTHORIZATION
00143     {
00144       int rc;
00145       rc = sqliteAuthCheck(pParse, SQLITE_UPDATE, pTab->zName,
00146                            pTab->aCol[j].zName, db->aDb[pTab->iDb].zName);
00147       if( rc==SQLITE_DENY ){
00148         goto update_cleanup;
00149       }else if( rc==SQLITE_IGNORE ){
00150         aXRef[j] = -1;
00151       }
00152     }
00153 #endif
00154   }
00155 
00156   /* Allocate memory for the array apIdx[] and fill it with pointers to every
00157   ** index that needs to be updated.  Indices only need updating if their
00158   ** key includes one of the columns named in pChanges or if the record
00159   ** number of the original table entry is changing.
00160   */
00161   for(nIdx=nIdxTotal=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdxTotal++){
00162     if( chngRecno ){
00163       i = 0;
00164     }else {
00165       for(i=0; i<pIdx->nColumn; i++){
00166         if( aXRef[pIdx->aiColumn[i]]>=0 ) break;
00167       }
00168     }
00169     if( i<pIdx->nColumn ) nIdx++;
00170   }
00171   if( nIdxTotal>0 ){
00172     apIdx = sqliteMalloc( sizeof(Index*) * nIdx + nIdxTotal );
00173     if( apIdx==0 ) goto update_cleanup;
00174     aIdxUsed = (char*)&apIdx[nIdx];
00175   }
00176   for(nIdx=j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){
00177     if( chngRecno ){
00178       i = 0;
00179     }else{
00180       for(i=0; i<pIdx->nColumn; i++){
00181         if( aXRef[pIdx->aiColumn[i]]>=0 ) break;
00182       }
00183     }
00184     if( i<pIdx->nColumn ){
00185       apIdx[nIdx++] = pIdx;
00186       aIdxUsed[j] = 1;
00187     }else{
00188       aIdxUsed[j] = 0;
00189     }
00190   }
00191 
00192   /* Resolve the column names in all the expressions in the
00193   ** WHERE clause.
00194   */
00195   if( pWhere ){
00196     if( sqliteExprResolveIds(pParse, pTabList, 0, pWhere) ){
00197       goto update_cleanup;
00198     }
00199     if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
00200       goto update_cleanup;
00201     }
00202   }
00203 
00204   /* Start the view context
00205   */
00206   if( isView ){
00207     sqliteAuthContextPush(pParse, &sContext, pTab->zName);
00208   }
00209 
00210   /* Begin generating code.
00211   */
00212   v = sqliteGetVdbe(pParse);
00213   if( v==0 ) goto update_cleanup;
00214   sqliteBeginWriteOperation(pParse, 1, pTab->iDb);
00215 
00216   /* If we are trying to update a view, construct that view into
00217   ** a temporary table.
00218   */
00219   if( isView ){
00220     Select *pView;
00221     pView = sqliteSelectDup(pTab->pSelect);
00222     sqliteSelect(pParse, pView, SRT_TempTable, iCur, 0, 0, 0);
00223     sqliteSelectDelete(pView);
00224   }
00225 
00226   /* Begin the database scan
00227   */
00228   pWInfo = sqliteWhereBegin(pParse, pTabList, pWhere, 1, 0);
00229   if( pWInfo==0 ) goto update_cleanup;
00230 
00231   /* Remember the index of every item to be updated.
00232   */
00233   sqliteVdbeAddOp(v, OP_ListWrite, 0, 0);
00234 
00235   /* End the database scan loop.
00236   */
00237   sqliteWhereEnd(pWInfo);
00238 
00239   /* Initialize the count of updated rows
00240   */
00241   if( db->flags & SQLITE_CountRows && !pParse->trigStack ){
00242     sqliteVdbeAddOp(v, OP_Integer, 0, 0);
00243   }
00244 
00245   if( row_triggers_exist ){
00246     /* Create pseudo-tables for NEW and OLD
00247     */
00248     sqliteVdbeAddOp(v, OP_OpenPseudo, oldIdx, 0);
00249     sqliteVdbeAddOp(v, OP_OpenPseudo, newIdx, 0);
00250 
00251     /* The top of the update loop for when there are triggers.
00252     */
00253     sqliteVdbeAddOp(v, OP_ListRewind, 0, 0);
00254     sqliteVdbeAddOp(v, OP_StackDepth, 0, 0);
00255     sqliteVdbeAddOp(v, OP_MemStore, iStackDepth, 1);
00256     loopStart = sqliteVdbeAddOp(v, OP_MemLoad, iStackDepth, 0);
00257     sqliteVdbeAddOp(v, OP_StackReset, 0, 0);
00258     jumpInst = sqliteVdbeAddOp(v, OP_ListRead, 0, 0);
00259     sqliteVdbeAddOp(v, OP_Dup, 0, 0);
00260 
00261     /* Open a cursor and make it point to the record that is
00262     ** being updated.
00263     */
00264     sqliteVdbeAddOp(v, OP_Dup, 0, 0);
00265     if( !isView ){
00266       sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
00267       sqliteVdbeAddOp(v, OP_OpenRead, iCur, pTab->tnum);
00268     }
00269     sqliteVdbeAddOp(v, OP_MoveTo, iCur, 0);
00270 
00271     /* Generate the OLD table
00272     */
00273     sqliteVdbeAddOp(v, OP_Recno, iCur, 0);
00274     sqliteVdbeAddOp(v, OP_RowData, iCur, 0);
00275     sqliteVdbeAddOp(v, OP_PutIntKey, oldIdx, 0);
00276 
00277     /* Generate the NEW table
00278     */
00279     if( chngRecno ){
00280       sqliteExprCode(pParse, pRecnoExpr);
00281     }else{
00282       sqliteVdbeAddOp(v, OP_Recno, iCur, 0);
00283     }
00284     for(i=0; i<pTab->nCol; i++){
00285       if( i==pTab->iPKey ){
00286         sqliteVdbeAddOp(v, OP_String, 0, 0);
00287         continue;
00288       }
00289       j = aXRef[i];
00290       if( j<0 ){
00291         sqliteVdbeAddOp(v, OP_Column, iCur, i);
00292       }else{
00293         sqliteExprCode(pParse, pChanges->a[j].pExpr);
00294       }
00295     }
00296     sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
00297     sqliteVdbeAddOp(v, OP_PutIntKey, newIdx, 0);
00298     if( !isView ){
00299       sqliteVdbeAddOp(v, OP_Close, iCur, 0);
00300     }
00301 
00302     /* Fire the BEFORE and INSTEAD OF triggers
00303     */
00304     if( sqliteCodeRowTrigger(pParse, TK_UPDATE, pChanges, TK_BEFORE, pTab, 
00305           newIdx, oldIdx, onError, loopStart) ){
00306       goto update_cleanup;
00307     }
00308   }
00309 
00310   if( !isView ){
00311     /* 
00312     ** Open every index that needs updating.  Note that if any
00313     ** index could potentially invoke a REPLACE conflict resolution 
00314     ** action, then we need to open all indices because we might need
00315     ** to be deleting some records.
00316     */
00317     sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
00318     sqliteVdbeAddOp(v, OP_OpenWrite, iCur, pTab->tnum);
00319     if( onError==OE_Replace ){
00320       openAll = 1;
00321     }else{
00322       openAll = 0;
00323       for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
00324         if( pIdx->onError==OE_Replace ){
00325           openAll = 1;
00326           break;
00327         }
00328       }
00329     }
00330     for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
00331       if( openAll || aIdxUsed[i] ){
00332         sqliteVdbeAddOp(v, OP_Integer, pIdx->iDb, 0);
00333         sqliteVdbeAddOp(v, OP_OpenWrite, iCur+i+1, pIdx->tnum);
00334         assert( pParse->nTab>iCur+i+1 );
00335       }
00336     }
00337 
00338     /* Loop over every record that needs updating.  We have to load
00339     ** the old data for each record to be updated because some columns
00340     ** might not change and we will need to copy the old value.
00341     ** Also, the old data is needed to delete the old index entires.
00342     ** So make the cursor point at the old record.
00343     */
00344     if( !row_triggers_exist ){
00345       sqliteVdbeAddOp(v, OP_ListRewind, 0, 0);
00346       jumpInst = loopStart = sqliteVdbeAddOp(v, OP_ListRead, 0, 0);
00347       sqliteVdbeAddOp(v, OP_Dup, 0, 0);
00348     }
00349     sqliteVdbeAddOp(v, OP_NotExists, iCur, loopStart);
00350 
00351     /* If the record number will change, push the record number as it
00352     ** will be after the update. (The old record number is currently
00353     ** on top of the stack.)
00354     */
00355     if( chngRecno ){
00356       sqliteExprCode(pParse, pRecnoExpr);
00357       sqliteVdbeAddOp(v, OP_MustBeInt, 0, 0);
00358     }
00359 
00360     /* Compute new data for this record.  
00361     */
00362     for(i=0; i<pTab->nCol; i++){
00363       if( i==pTab->iPKey ){
00364         sqliteVdbeAddOp(v, OP_String, 0, 0);
00365         continue;
00366       }
00367       j = aXRef[i];
00368       if( j<0 ){
00369         sqliteVdbeAddOp(v, OP_Column, iCur, i);
00370       }else{
00371         sqliteExprCode(pParse, pChanges->a[j].pExpr);
00372       }
00373     }
00374 
00375     /* Do constraint checks
00376     */
00377     sqliteGenerateConstraintChecks(pParse, pTab, iCur, aIdxUsed, chngRecno, 1,
00378                                    onError, loopStart);
00379 
00380     /* Delete the old indices for the current record.
00381     */
00382     sqliteGenerateRowIndexDelete(db, v, pTab, iCur, aIdxUsed);
00383 
00384     /* If changing the record number, delete the old record.
00385     */
00386     if( chngRecno ){
00387       sqliteVdbeAddOp(v, OP_Delete, iCur, 0);
00388     }
00389 
00390     /* Create the new index entries and the new record.
00391     */
00392     sqliteCompleteInsertion(pParse, pTab, iCur, aIdxUsed, chngRecno, 1, -1);
00393   }
00394 
00395   /* Increment the row counter 
00396   */
00397   if( db->flags & SQLITE_CountRows && !pParse->trigStack){
00398     sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
00399   }
00400 
00401   /* If there are triggers, close all the cursors after each iteration
00402   ** through the loop.  The fire the after triggers.
00403   */
00404   if( row_triggers_exist ){
00405     if( !isView ){
00406       for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
00407         if( openAll || aIdxUsed[i] )
00408           sqliteVdbeAddOp(v, OP_Close, iCur+i+1, 0);
00409       }
00410       sqliteVdbeAddOp(v, OP_Close, iCur, 0);
00411       pParse->nTab = iCur;
00412     }
00413     if( sqliteCodeRowTrigger(pParse, TK_UPDATE, pChanges, TK_AFTER, pTab, 
00414           newIdx, oldIdx, onError, loopStart) ){
00415       goto update_cleanup;
00416     }
00417   }
00418 
00419   /* Repeat the above with the next record to be updated, until
00420   ** all record selected by the WHERE clause have been updated.
00421   */
00422   sqliteVdbeAddOp(v, OP_Goto, 0, loopStart);
00423   sqliteVdbeChangeP2(v, jumpInst, sqliteVdbeCurrentAddr(v));
00424   sqliteVdbeAddOp(v, OP_ListReset, 0, 0);
00425 
00426   /* Close all tables if there were no FOR EACH ROW triggers */
00427   if( !row_triggers_exist ){
00428     for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
00429       if( openAll || aIdxUsed[i] ){
00430         sqliteVdbeAddOp(v, OP_Close, iCur+i+1, 0);
00431       }
00432     }
00433     sqliteVdbeAddOp(v, OP_Close, iCur, 0);
00434     pParse->nTab = iCur;
00435   }else{
00436     sqliteVdbeAddOp(v, OP_Close, newIdx, 0);
00437     sqliteVdbeAddOp(v, OP_Close, oldIdx, 0);
00438   }
00439 
00440   sqliteVdbeAddOp(v, OP_SetCounts, 0, 0);
00441   sqliteEndWriteOperation(pParse);
00442 
00443   /*
00444   ** Return the number of rows that were changed.
00445   */
00446   if( db->flags & SQLITE_CountRows && !pParse->trigStack ){
00447     sqliteVdbeOp3(v, OP_ColumnName, 0, 1, "rows updated", P3_STATIC);
00448     sqliteVdbeAddOp(v, OP_Callback, 1, 0);
00449   }
00450 
00451 update_cleanup:
00452   sqliteAuthContextPop(&sContext);
00453   sqliteFree(apIdx);
00454   sqliteFree(aXRef);
00455   sqliteSrcListDelete(pTabList);
00456   sqliteExprListDelete(pChanges);
00457   sqliteExprDelete(pWhere);
00458   return;
00459 }