4 ** The author disclaims copyright to this source code. In place of
5 ** a legal notice, here is a blessing:
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.
11 *************************************************************************
12 ** This file contains C code routines that are called by the parser
13 ** to handle UPDATE statements.
15 #include "sqliteInt.h"
17 #ifndef SQLITE_OMIT_VIRTUALTABLE
18 /* Forward declaration */
19 static void updateVirtualTable(
20 Parse
*pParse
, /* The parsing context */
21 SrcList
*pSrc
, /* The virtual table to be modified */
22 Table
*pTab
, /* The virtual table */
23 ExprList
*pChanges
, /* The columns to change in the UPDATE statement */
24 Expr
*pRowidExpr
, /* Expression used to recompute the rowid */
25 int *aXRef
, /* Mapping from columns of pTab to entries in pChanges */
26 Expr
*pWhere
, /* WHERE clause of the UPDATE statement */
27 int onError
/* ON CONFLICT strategy */
29 #endif /* SQLITE_OMIT_VIRTUALTABLE */
32 ** The most recently coded instruction was an OP_Column to retrieve the
33 ** i-th column of table pTab. This routine sets the P4 parameter of the
34 ** OP_Column to the default value, if any.
36 ** The default value of a column is specified by a DEFAULT clause in the
37 ** column definition. This was either supplied by the user when the table
38 ** was created, or added later to the table definition by an ALTER TABLE
39 ** command. If the latter, then the row-records in the table btree on disk
40 ** may not contain a value for the column and the default value, taken
41 ** from the P4 parameter of the OP_Column instruction, is returned instead.
42 ** If the former, then all row-records are guaranteed to include a value
43 ** for the column and the P4 value is not required.
45 ** Column definitions created by an ALTER TABLE command may only have
46 ** literal default values specified: a number, null or a string. (If a more
47 ** complicated default expression value was provided, it is evaluated
48 ** when the ALTER TABLE is executed and one of the literal values written
49 ** into the sqlite_master table.)
51 ** Therefore, the P4 parameter is only required if the default value for
52 ** the column is a literal number, string or null. The sqlite3ValueFromExpr()
53 ** function is capable of transforming these types of expressions into
54 ** sqlite3_value objects.
56 ** If parameter iReg is not negative, code an OP_RealAffinity instruction
57 ** on register iReg. This is used when an equivalent integer value is
58 ** stored in place of an 8-byte floating point value in order to save
61 void sqlite3ColumnDefault(Vdbe
*v
, Table
*pTab
, int i
, int iReg
){
64 sqlite3_value
*pValue
= 0;
65 u8 enc
= ENC(sqlite3VdbeDb(v
));
66 Column
*pCol
= &pTab
->aCol
[i
];
67 VdbeComment((v
, "%s.%s", pTab
->zName
, pCol
->zName
));
68 assert( i
<pTab
->nCol
);
69 sqlite3ValueFromExpr(sqlite3VdbeDb(v
), pCol
->pDflt
, enc
,
70 pCol
->affinity
, &pValue
);
72 sqlite3VdbeAppendP4(v
, pValue
, P4_MEM
);
75 #ifndef SQLITE_OMIT_FLOATING_POINT
76 if( pTab
->aCol
[i
].affinity
==SQLITE_AFF_REAL
){
77 sqlite3VdbeAddOp1(v
, OP_RealAffinity
, iReg
);
83 ** Process an UPDATE statement.
85 ** UPDATE OR IGNORE table_wxyz SET a=b, c=d WHERE e<5 AND f NOT NULL;
86 ** \_______/ \________/ \______/ \________________/
87 * onError pTabList pChanges pWhere
90 Parse
*pParse
, /* The parser context */
91 SrcList
*pTabList
, /* The table in which we should change things */
92 ExprList
*pChanges
, /* Things to be changed */
93 Expr
*pWhere
, /* The WHERE clause. May be null */
94 int onError
, /* How to handle constraint errors */
95 ExprList
*pOrderBy
, /* ORDER BY clause. May be null */
96 Expr
*pLimit
, /* LIMIT clause. May be null */
97 Upsert
*pUpsert
/* ON CONFLICT clause, or null */
99 int i
, j
; /* Loop counters */
100 Table
*pTab
; /* The table to be updated */
101 int addrTop
= 0; /* VDBE instruction address of the start of the loop */
102 WhereInfo
*pWInfo
; /* Information about the WHERE clause */
103 Vdbe
*v
; /* The virtual database engine */
104 Index
*pIdx
; /* For looping over indices */
105 Index
*pPk
; /* The PRIMARY KEY index for WITHOUT ROWID tables */
106 int nIdx
; /* Number of indices that need updating */
107 int iBaseCur
; /* Base cursor number */
108 int iDataCur
; /* Cursor for the canonical data btree */
109 int iIdxCur
; /* Cursor for the first index */
110 sqlite3
*db
; /* The database structure */
111 int *aRegIdx
= 0; /* First register in array assigned to each index */
112 int *aXRef
= 0; /* aXRef[i] is the index in pChanges->a[] of the
113 ** an expression for the i-th column of the table.
114 ** aXRef[i]==-1 if the i-th column is not changed. */
115 u8
*aToOpen
; /* 1 for tables and indices to be opened */
116 u8 chngPk
; /* PRIMARY KEY changed in a WITHOUT ROWID table */
117 u8 chngRowid
; /* Rowid changed in a normal table */
118 u8 chngKey
; /* Either chngPk or chngRowid */
119 Expr
*pRowidExpr
= 0; /* Expression defining the new record number */
120 AuthContext sContext
; /* The authorization context */
121 NameContext sNC
; /* The name-context to resolve expressions in */
122 int iDb
; /* Database containing the table being updated */
123 int eOnePass
; /* ONEPASS_XXX value from where.c */
124 int hasFK
; /* True if foreign key processing is required */
125 int labelBreak
; /* Jump here to break out of UPDATE loop */
126 int labelContinue
; /* Jump here to continue next step of UPDATE loop */
127 int flags
; /* Flags for sqlite3WhereBegin() */
129 #ifndef SQLITE_OMIT_TRIGGER
130 int isView
; /* True when updating a view (INSTEAD OF trigger) */
131 Trigger
*pTrigger
; /* List of triggers on pTab, if required */
132 int tmask
; /* Mask of TRIGGER_BEFORE|TRIGGER_AFTER */
134 int newmask
; /* Mask of NEW.* columns accessed by BEFORE triggers */
135 int iEph
= 0; /* Ephemeral table holding all primary key values */
136 int nKey
= 0; /* Number of elements in regKey for WITHOUT ROWID */
137 int aiCurOnePass
[2]; /* The write cursors opened by WHERE_ONEPASS */
138 int addrOpen
= 0; /* Address of OP_OpenEphemeral */
139 int iPk
= 0; /* First of nPk cells holding PRIMARY KEY value */
140 i16 nPk
= 0; /* Number of components of the PRIMARY KEY */
141 int bReplace
= 0; /* True if REPLACE conflict resolution might happen */
143 /* Register Allocations */
144 int regRowCount
= 0; /* A count of rows changed */
145 int regOldRowid
= 0; /* The old rowid */
146 int regNewRowid
= 0; /* The new rowid */
147 int regNew
= 0; /* Content of the NEW.* table in triggers */
148 int regOld
= 0; /* Content of OLD.* table in triggers */
149 int regRowSet
= 0; /* Rowset of rows to be updated */
150 int regKey
= 0; /* composite PRIMARY KEY value */
152 memset(&sContext
, 0, sizeof(sContext
));
154 if( pParse
->nErr
|| db
->mallocFailed
){
157 assert( pTabList
->nSrc
==1 );
159 /* Locate the table which we want to update.
161 pTab
= sqlite3SrcListLookup(pParse
, pTabList
);
162 if( pTab
==0 ) goto update_cleanup
;
163 iDb
= sqlite3SchemaToIndex(pParse
->db
, pTab
->pSchema
);
165 /* Figure out if we have any triggers and if the table being
166 ** updated is a view.
168 #ifndef SQLITE_OMIT_TRIGGER
169 pTrigger
= sqlite3TriggersExist(pParse
, pTab
, TK_UPDATE
, pChanges
, &tmask
);
170 isView
= pTab
->pSelect
!=0;
171 assert( pTrigger
|| tmask
==0 );
177 #ifdef SQLITE_OMIT_VIEW
182 #ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
184 pWhere
= sqlite3LimitWhere(
185 pParse
, pTabList
, pWhere
, pOrderBy
, pLimit
, "UPDATE"
192 if( sqlite3ViewGetColumnNames(pParse
, pTab
) ){
195 if( sqlite3IsReadOnly(pParse
, pTab
, tmask
) ){
199 /* Allocate a cursors for the main database table and for all indices.
200 ** The index cursors might not be used, but if they are used they
201 ** need to occur right after the database cursor. So go ahead and
202 ** allocate enough space, just in case.
204 iBaseCur
= iDataCur
= pParse
->nTab
++;
205 iIdxCur
= iDataCur
+1;
206 pPk
= HasRowid(pTab
) ? 0 : sqlite3PrimaryKeyIndex(pTab
);
207 testcase( pPk
!=0 && pPk
!=pTab
->pIndex
);
208 for(nIdx
=0, pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
, nIdx
++){
210 iDataCur
= pParse
->nTab
;
215 /* On an UPSERT, reuse the same cursors already opened by INSERT */
216 iDataCur
= pUpsert
->iDataCur
;
217 iIdxCur
= pUpsert
->iIdxCur
;
218 pParse
->nTab
= iBaseCur
;
220 pTabList
->a
[0].iCursor
= iDataCur
;
222 /* Allocate space for aXRef[], aRegIdx[], and aToOpen[].
223 ** Initialize aXRef[] and aToOpen[] to their default values.
225 aXRef
= sqlite3DbMallocRawNN(db
, sizeof(int) * (pTab
->nCol
+nIdx
) + nIdx
+2 );
226 if( aXRef
==0 ) goto update_cleanup
;
227 aRegIdx
= aXRef
+pTab
->nCol
;
228 aToOpen
= (u8
*)(aRegIdx
+nIdx
);
229 memset(aToOpen
, 1, nIdx
+1);
231 for(i
=0; i
<pTab
->nCol
; i
++) aXRef
[i
] = -1;
233 /* Initialize the name-context */
234 memset(&sNC
, 0, sizeof(sNC
));
236 sNC
.pSrcList
= pTabList
;
237 sNC
.uNC
.pUpsert
= pUpsert
;
238 sNC
.ncFlags
= NC_UUpsert
;
240 /* Resolve the column names in all the expressions of the
241 ** of the UPDATE statement. Also find the column index
242 ** for each column to be updated in the pChanges array. For each
243 ** column to be updated, make sure we have authorization to change
246 chngRowid
= chngPk
= 0;
247 for(i
=0; i
<pChanges
->nExpr
; i
++){
248 if( sqlite3ResolveExprNames(&sNC
, pChanges
->a
[i
].pExpr
) ){
251 for(j
=0; j
<pTab
->nCol
; j
++){
252 if( sqlite3StrICmp(pTab
->aCol
[j
].zName
, pChanges
->a
[i
].zName
)==0 ){
253 if( j
==pTab
->iPKey
){
255 pRowidExpr
= pChanges
->a
[i
].pExpr
;
256 }else if( pPk
&& (pTab
->aCol
[j
].colFlags
& COLFLAG_PRIMKEY
)!=0 ){
264 if( pPk
==0 && sqlite3IsRowid(pChanges
->a
[i
].zName
) ){
267 pRowidExpr
= pChanges
->a
[i
].pExpr
;
269 sqlite3ErrorMsg(pParse
, "no such column: %s", pChanges
->a
[i
].zName
);
270 pParse
->checkSchema
= 1;
274 #ifndef SQLITE_OMIT_AUTHORIZATION
277 rc
= sqlite3AuthCheck(pParse
, SQLITE_UPDATE
, pTab
->zName
,
278 j
<0 ? "ROWID" : pTab
->aCol
[j
].zName
,
279 db
->aDb
[iDb
].zDbSName
);
280 if( rc
==SQLITE_DENY
){
282 }else if( rc
==SQLITE_IGNORE
){
288 assert( (chngRowid
& chngPk
)==0 );
289 assert( chngRowid
==0 || chngRowid
==1 );
290 assert( chngPk
==0 || chngPk
==1 );
291 chngKey
= chngRowid
+ chngPk
;
293 /* The SET expressions are not actually used inside the WHERE loop.
294 ** So reset the colUsed mask. Unless this is a virtual table. In that
295 ** case, set all bits of the colUsed mask (to ensure that the virtual
296 ** table implementation makes all columns available).
298 pTabList
->a
[0].colUsed
= IsVirtual(pTab
) ? ALLBITS
: 0;
300 hasFK
= sqlite3FkRequired(pParse
, pTab
, aXRef
, chngKey
);
302 /* There is one entry in the aRegIdx[] array for each index on the table
303 ** being updated. Fill in aRegIdx[] with a register number that will hold
304 ** the key for accessing each index.
306 ** FIXME: Be smarter about omitting indexes that use expressions.
308 for(j
=0, pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
, j
++){
310 if( chngKey
|| hasFK
>1 || pIdx
->pPartIdxWhere
|| pIdx
==pPk
){
311 reg
= ++pParse
->nMem
;
312 pParse
->nMem
+= pIdx
->nColumn
;
315 for(i
=0; i
<pIdx
->nKeyCol
; i
++){
316 i16 iIdxCol
= pIdx
->aiColumn
[i
];
317 if( iIdxCol
<0 || aXRef
[iIdxCol
]>=0 ){
318 reg
= ++pParse
->nMem
;
319 pParse
->nMem
+= pIdx
->nColumn
;
320 if( (onError
==OE_Replace
)
321 || (onError
==OE_Default
&& pIdx
->onError
==OE_Replace
)
329 if( reg
==0 ) aToOpen
[j
+1] = 0;
333 /* If REPLACE conflict resolution might be invoked, open cursors on all
334 ** indexes in case they are needed to delete records. */
335 memset(aToOpen
, 1, nIdx
+1);
338 /* Begin generating code. */
339 v
= sqlite3GetVdbe(pParse
);
340 if( v
==0 ) goto update_cleanup
;
341 if( pParse
->nested
==0 ) sqlite3VdbeCountChanges(v
);
342 sqlite3BeginWriteOperation(pParse
, pTrigger
|| hasFK
, iDb
);
344 /* Allocate required registers. */
345 if( !IsVirtual(pTab
) ){
346 regRowSet
= ++pParse
->nMem
;
347 regOldRowid
= regNewRowid
= ++pParse
->nMem
;
348 if( chngPk
|| pTrigger
|| hasFK
){
349 regOld
= pParse
->nMem
+ 1;
350 pParse
->nMem
+= pTab
->nCol
;
352 if( chngKey
|| pTrigger
|| hasFK
){
353 regNewRowid
= ++pParse
->nMem
;
355 regNew
= pParse
->nMem
+ 1;
356 pParse
->nMem
+= pTab
->nCol
;
359 /* Start the view context. */
361 sqlite3AuthContextPush(pParse
, &sContext
, pTab
->zName
);
364 /* If we are trying to update a view, realize that view into
365 ** an ephemeral table.
367 #if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER)
369 sqlite3MaterializeView(pParse
, pTab
,
370 pWhere
, pOrderBy
, pLimit
, iDataCur
377 /* Resolve the column names in all the expressions in the
380 if( sqlite3ResolveExprNames(&sNC
, pWhere
) ){
384 #ifndef SQLITE_OMIT_VIRTUALTABLE
385 /* Virtual tables must be handled separately */
386 if( IsVirtual(pTab
) ){
387 updateVirtualTable(pParse
, pTabList
, pTab
, pChanges
, pRowidExpr
, aXRef
,
393 /* Jump to labelBreak to abandon further processing of this UPDATE */
394 labelContinue
= labelBreak
= sqlite3VdbeMakeLabel(v
);
396 /* Not an UPSERT. Normal processing. Begin by
397 ** initialize the count of updated rows */
398 if( (db
->flags
&SQLITE_CountRows
)!=0
399 && !pParse
->pTriggerTab
403 regRowCount
= ++pParse
->nMem
;
404 sqlite3VdbeAddOp2(v
, OP_Integer
, 0, regRowCount
);
407 if( HasRowid(pTab
) ){
408 sqlite3VdbeAddOp3(v
, OP_Null
, 0, regRowSet
, regOldRowid
);
412 iPk
= pParse
->nMem
+1;
414 regKey
= ++pParse
->nMem
;
416 iEph
= pParse
->nTab
++;
417 sqlite3VdbeAddOp3(v
, OP_Null
, 0, iPk
, iPk
+nPk
-1);
418 addrOpen
= sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, iEph
, nPk
);
419 sqlite3VdbeSetP4KeyInfo(pParse
, pPk
);
424 /* If this is an UPSERT, then all cursors have already been opened by
425 ** the outer INSERT and the data cursor should be pointing at the row
426 ** that is to be updated. So bypass the code that searches for the
427 ** row(s) to be updated.
430 eOnePass
= ONEPASS_SINGLE
;
431 sqlite3ExprIfFalse(pParse
, pWhere
, labelBreak
, SQLITE_JUMPIFNULL
);
433 /* Begin the database scan.
435 ** Do not consider a single-pass strategy for a multi-row update if
436 ** there are any triggers or foreign keys to process, or rows may
437 ** be deleted as a result of REPLACE conflict handling. Any of these
438 ** things might disturb a cursor being used to scan through the table
439 ** or index, causing a single-pass approach to malfunction. */
440 flags
= WHERE_ONEPASS_DESIRED
|WHERE_SEEK_UNIQ_TABLE
;
441 if( !pParse
->nested
&& !pTrigger
&& !hasFK
&& !chngKey
&& !bReplace
){
442 flags
|= WHERE_ONEPASS_MULTIROW
;
444 pWInfo
= sqlite3WhereBegin(pParse
, pTabList
, pWhere
, 0, 0, flags
, iIdxCur
);
445 if( pWInfo
==0 ) goto update_cleanup
;
447 /* A one-pass strategy that might update more than one row may not
448 ** be used if any column of the index used for the scan is being
449 ** updated. Otherwise, if there is an index on "b", statements like
450 ** the following could create an infinite loop:
452 ** UPDATE t1 SET b=b+1 WHERE b>?
454 ** Fall back to ONEPASS_OFF if where.c has selected a ONEPASS_MULTI
455 ** strategy that uses an index for which one or more columns are being
457 eOnePass
= sqlite3WhereOkOnePass(pWInfo
, aiCurOnePass
);
458 if( eOnePass
!=ONEPASS_SINGLE
){
459 sqlite3MultiWrite(pParse
);
460 if( eOnePass
==ONEPASS_MULTI
){
461 int iCur
= aiCurOnePass
[1];
462 if( iCur
>=0 && iCur
!=iDataCur
&& aToOpen
[iCur
-iBaseCur
] ){
463 eOnePass
= ONEPASS_OFF
;
465 assert( iCur
!=iDataCur
|| !HasRowid(pTab
) );
470 if( HasRowid(pTab
) ){
471 /* Read the rowid of the current row of the WHERE scan. In ONEPASS_OFF
472 ** mode, write the rowid into the FIFO. In either of the one-pass modes,
473 ** leave it in register regOldRowid. */
474 sqlite3VdbeAddOp2(v
, OP_Rowid
, iDataCur
, regOldRowid
);
475 if( eOnePass
==ONEPASS_OFF
){
476 sqlite3VdbeAddOp2(v
, OP_RowSetAdd
, regRowSet
, regOldRowid
);
479 /* Read the PK of the current row into an array of registers. In
480 ** ONEPASS_OFF mode, serialize the array into a record and store it in
481 ** the ephemeral table. Or, in ONEPASS_SINGLE or MULTI mode, change
482 ** the OP_OpenEphemeral instruction to a Noop (the ephemeral table
483 ** is not required) and leave the PK fields in the array of registers. */
484 for(i
=0; i
<nPk
; i
++){
485 assert( pPk
->aiColumn
[i
]>=0 );
486 sqlite3ExprCodeGetColumnOfTable(v
, pTab
, iDataCur
,pPk
->aiColumn
[i
],iPk
+i
);
489 if( addrOpen
) sqlite3VdbeChangeToNoop(v
, addrOpen
);
493 sqlite3VdbeAddOp4(v
, OP_MakeRecord
, iPk
, nPk
, regKey
,
494 sqlite3IndexAffinityStr(db
, pPk
), nPk
);
495 sqlite3VdbeAddOp4Int(v
, OP_IdxInsert
, iEph
, regKey
, iPk
, nPk
);
500 if( eOnePass
!=ONEPASS_MULTI
){
501 sqlite3WhereEnd(pWInfo
);
507 /* Open every index that needs updating. */
508 if( eOnePass
!=ONEPASS_OFF
){
509 if( aiCurOnePass
[0]>=0 ) aToOpen
[aiCurOnePass
[0]-iBaseCur
] = 0;
510 if( aiCurOnePass
[1]>=0 ) aToOpen
[aiCurOnePass
[1]-iBaseCur
] = 0;
513 if( eOnePass
==ONEPASS_MULTI
&& (nIdx
-(aiCurOnePass
[1]>=0))>0 ){
514 addrOnce
= sqlite3VdbeAddOp0(v
, OP_Once
); VdbeCoverage(v
);
516 sqlite3OpenTableAndIndices(pParse
, pTab
, OP_OpenWrite
, 0, iBaseCur
,
518 if( addrOnce
) sqlite3VdbeJumpHere(v
, addrOnce
);
521 /* Top of the update loop */
522 if( eOnePass
!=ONEPASS_OFF
){
523 if( !isView
&& aiCurOnePass
[0]!=iDataCur
&& aiCurOnePass
[1]!=iDataCur
){
525 sqlite3VdbeAddOp4Int(v
, OP_NotFound
, iDataCur
, labelBreak
, regKey
,nKey
);
526 VdbeCoverageNeverTaken(v
);
528 if( eOnePass
!=ONEPASS_SINGLE
){
529 labelContinue
= sqlite3VdbeMakeLabel(v
);
531 sqlite3VdbeAddOp2(v
, OP_IsNull
, pPk
? regKey
: regOldRowid
, labelBreak
);
532 VdbeCoverageIf(v
, pPk
==0);
533 VdbeCoverageIf(v
, pPk
!=0);
535 labelContinue
= sqlite3VdbeMakeLabel(v
);
536 sqlite3VdbeAddOp2(v
, OP_Rewind
, iEph
, labelBreak
); VdbeCoverage(v
);
537 addrTop
= sqlite3VdbeAddOp2(v
, OP_RowData
, iEph
, regKey
);
538 sqlite3VdbeAddOp4Int(v
, OP_NotFound
, iDataCur
, labelContinue
, regKey
, 0);
541 labelContinue
= sqlite3VdbeAddOp3(v
, OP_RowSetRead
, regRowSet
,labelBreak
,
544 sqlite3VdbeAddOp3(v
, OP_NotExists
, iDataCur
, labelContinue
, regOldRowid
);
549 /* If the rowid value will change, set register regNewRowid to
550 ** contain the new value. If the rowid is not being modified,
551 ** then regNewRowid is the same register as regOldRowid, which is
552 ** already populated. */
553 assert( chngKey
|| pTrigger
|| hasFK
|| regOldRowid
==regNewRowid
);
555 sqlite3ExprCode(pParse
, pRowidExpr
, regNewRowid
);
556 sqlite3VdbeAddOp1(v
, OP_MustBeInt
, regNewRowid
); VdbeCoverage(v
);
559 /* Compute the old pre-UPDATE content of the row being changed, if that
560 ** information is needed */
561 if( chngPk
|| hasFK
|| pTrigger
){
562 u32 oldmask
= (hasFK
? sqlite3FkOldmask(pParse
, pTab
) : 0);
563 oldmask
|= sqlite3TriggerColmask(pParse
,
564 pTrigger
, pChanges
, 0, TRIGGER_BEFORE
|TRIGGER_AFTER
, pTab
, onError
566 for(i
=0; i
<pTab
->nCol
; i
++){
567 if( oldmask
==0xffffffff
568 || (i
<32 && (oldmask
& MASKBIT32(i
))!=0)
569 || (pTab
->aCol
[i
].colFlags
& COLFLAG_PRIMKEY
)!=0
571 testcase( oldmask
!=0xffffffff && i
==31 );
572 sqlite3ExprCodeGetColumnOfTable(v
, pTab
, iDataCur
, i
, regOld
+i
);
574 sqlite3VdbeAddOp2(v
, OP_Null
, 0, regOld
+i
);
577 if( chngRowid
==0 && pPk
==0 ){
578 sqlite3VdbeAddOp2(v
, OP_Copy
, regOldRowid
, regNewRowid
);
582 /* Populate the array of registers beginning at regNew with the new
583 ** row data. This array is used to check constants, create the new
584 ** table and index records, and as the values for any new.* references
587 ** If there are one or more BEFORE triggers, then do not populate the
588 ** registers associated with columns that are (a) not modified by
589 ** this UPDATE statement and (b) not accessed by new.* references. The
590 ** values for registers not modified by the UPDATE must be reloaded from
591 ** the database after the BEFORE triggers are fired anyway (as the trigger
592 ** may have modified them). So not loading those that are not going to
593 ** be used eliminates some redundant opcodes.
595 newmask
= sqlite3TriggerColmask(
596 pParse
, pTrigger
, pChanges
, 1, TRIGGER_BEFORE
, pTab
, onError
598 for(i
=0; i
<pTab
->nCol
; i
++){
599 if( i
==pTab
->iPKey
){
600 sqlite3VdbeAddOp2(v
, OP_Null
, 0, regNew
+i
);
604 sqlite3ExprCode(pParse
, pChanges
->a
[j
].pExpr
, regNew
+i
);
605 }else if( 0==(tmask
&TRIGGER_BEFORE
) || i
>31 || (newmask
& MASKBIT32(i
)) ){
606 /* This branch loads the value of a column that will not be changed
607 ** into a register. This is done if there are no BEFORE triggers, or
608 ** if there are one or more BEFORE triggers that use this value via
609 ** a new.* reference in a trigger program.
613 sqlite3ExprCodeGetColumnToReg(pParse
, pTab
, i
, iDataCur
, regNew
+i
);
614 if( tmask
& TRIGGER_BEFORE
){
615 /* This value will be recomputed in After-BEFORE-trigger-reload-loop
616 ** below, so make sure that it is not cached and reused.
617 ** Ticket d85fffd6ffe856092ed8daefa811b1e399706b28. */
618 sqlite3ExprCacheRemove(pParse
, regNew
+i
, 1);
621 sqlite3VdbeAddOp2(v
, OP_Null
, 0, regNew
+i
);
626 /* Fire any BEFORE UPDATE triggers. This happens before constraints are
627 ** verified. One could argue that this is wrong.
629 if( tmask
&TRIGGER_BEFORE
){
630 sqlite3TableAffinity(v
, pTab
, regNew
);
631 sqlite3CodeRowTrigger(pParse
, pTrigger
, TK_UPDATE
, pChanges
,
632 TRIGGER_BEFORE
, pTab
, regOldRowid
, onError
, labelContinue
);
634 /* The row-trigger may have deleted the row being updated. In this
635 ** case, jump to the next row. No updates or AFTER triggers are
636 ** required. This behavior - what happens when the row being updated
637 ** is deleted or renamed by a BEFORE trigger - is left undefined in the
641 sqlite3VdbeAddOp4Int(v
, OP_NotFound
, iDataCur
, labelContinue
,regKey
,nKey
);
644 sqlite3VdbeAddOp3(v
, OP_NotExists
, iDataCur
, labelContinue
, regOldRowid
);
648 /* After-BEFORE-trigger-reload-loop:
649 ** If it did not delete it, the BEFORE trigger may still have modified
650 ** some of the columns of the row being updated. Load the values for
651 ** all columns not modified by the update statement into their registers
652 ** in case this has happened. Only unmodified columns are reloaded.
653 ** The values computed for modified columns use the values before the
654 ** BEFORE trigger runs. See test case trigger1-18.0 (added 2018-04-26)
657 for(i
=0; i
<pTab
->nCol
; i
++){
658 if( aXRef
[i
]<0 && i
!=pTab
->iPKey
){
659 sqlite3ExprCodeGetColumnOfTable(v
, pTab
, iDataCur
, i
, regNew
+i
);
665 int addr1
= 0; /* Address of jump instruction */
667 /* Do constraint checks. */
668 assert( regOldRowid
>0 );
669 sqlite3GenerateConstraintChecks(pParse
, pTab
, aRegIdx
, iDataCur
, iIdxCur
,
670 regNewRowid
, regOldRowid
, chngKey
, onError
, labelContinue
, &bReplace
,
673 /* Do FK constraint checks. */
675 sqlite3FkCheck(pParse
, pTab
, regOldRowid
, 0, aXRef
, chngKey
);
678 /* Delete the index entries associated with the current record. */
679 if( bReplace
|| chngKey
){
681 addr1
= sqlite3VdbeAddOp4Int(v
, OP_NotFound
, iDataCur
, 0, regKey
, nKey
);
683 addr1
= sqlite3VdbeAddOp3(v
, OP_NotExists
, iDataCur
, 0, regOldRowid
);
685 VdbeCoverageNeverTaken(v
);
687 sqlite3GenerateRowIndexDelete(pParse
, pTab
, iDataCur
, iIdxCur
, aRegIdx
, -1);
689 /* If changing the rowid value, or if there are foreign key constraints
690 ** to process, delete the old record. Otherwise, add a noop OP_Delete
691 ** to invoke the pre-update hook.
693 ** That (regNew==regnewRowid+1) is true is also important for the
694 ** pre-update hook. If the caller invokes preupdate_new(), the returned
695 ** value is copied from memory cell (regNewRowid+1+iCol), where iCol
696 ** is the column index supplied by the user.
698 assert( regNew
==regNewRowid
+1 );
699 #ifdef SQLITE_ENABLE_PREUPDATE_HOOK
700 sqlite3VdbeAddOp3(v
, OP_Delete
, iDataCur
,
701 OPFLAG_ISUPDATE
| ((hasFK
>1 || chngKey
) ? 0 : OPFLAG_ISNOOP
),
704 if( eOnePass
==ONEPASS_MULTI
){
705 assert( hasFK
==0 && chngKey
==0 );
706 sqlite3VdbeChangeP5(v
, OPFLAG_SAVEPOSITION
);
708 if( !pParse
->nested
){
709 sqlite3VdbeAppendP4(v
, pTab
, P4_TABLE
);
712 if( hasFK
>1 || chngKey
){
713 sqlite3VdbeAddOp2(v
, OP_Delete
, iDataCur
, 0);
716 if( bReplace
|| chngKey
){
717 sqlite3VdbeJumpHere(v
, addr1
);
721 sqlite3FkCheck(pParse
, pTab
, 0, regNewRowid
, aXRef
, chngKey
);
724 /* Insert the new index entries and the new record. */
725 sqlite3CompleteInsertion(
726 pParse
, pTab
, iDataCur
, iIdxCur
, regNewRowid
, aRegIdx
,
727 OPFLAG_ISUPDATE
| (eOnePass
==ONEPASS_MULTI
? OPFLAG_SAVEPOSITION
: 0),
731 /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to
732 ** handle rows (possibly in other tables) that refer via a foreign key
733 ** to the row just updated. */
735 sqlite3FkActions(pParse
, pTab
, pChanges
, regOldRowid
, aXRef
, chngKey
);
739 /* Increment the row counter
742 sqlite3VdbeAddOp2(v
, OP_AddImm
, regRowCount
, 1);
745 sqlite3CodeRowTrigger(pParse
, pTrigger
, TK_UPDATE
, pChanges
,
746 TRIGGER_AFTER
, pTab
, regOldRowid
, onError
, labelContinue
);
748 /* Repeat the above with the next record to be updated, until
749 ** all record selected by the WHERE clause have been updated.
751 if( eOnePass
==ONEPASS_SINGLE
){
752 /* Nothing to do at end-of-loop for a single-pass */
753 }else if( eOnePass
==ONEPASS_MULTI
){
754 sqlite3VdbeResolveLabel(v
, labelContinue
);
755 sqlite3WhereEnd(pWInfo
);
757 sqlite3VdbeResolveLabel(v
, labelContinue
);
758 sqlite3VdbeAddOp2(v
, OP_Next
, iEph
, addrTop
); VdbeCoverage(v
);
760 sqlite3VdbeGoto(v
, labelContinue
);
762 sqlite3VdbeResolveLabel(v
, labelBreak
);
764 /* Update the sqlite_sequence table by storing the content of the
765 ** maximum rowid counter values recorded while inserting into
766 ** autoincrement tables.
768 if( pParse
->nested
==0 && pParse
->pTriggerTab
==0 && pUpsert
==0 ){
769 sqlite3AutoincrementEnd(pParse
);
773 ** Return the number of rows that were changed, if we are tracking
777 sqlite3VdbeAddOp2(v
, OP_ResultRow
, regRowCount
, 1);
778 sqlite3VdbeSetNumCols(v
, 1);
779 sqlite3VdbeSetColName(v
, 0, COLNAME_NAME
, "rows updated", SQLITE_STATIC
);
783 sqlite3AuthContextPop(&sContext
);
784 sqlite3DbFree(db
, aXRef
); /* Also frees aRegIdx[] and aToOpen[] */
785 sqlite3SrcListDelete(db
, pTabList
);
786 sqlite3ExprListDelete(db
, pChanges
);
787 sqlite3ExprDelete(db
, pWhere
);
788 #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT)
789 sqlite3ExprListDelete(db
, pOrderBy
);
790 sqlite3ExprDelete(db
, pLimit
);
794 /* Make sure "isView" and other macros defined above are undefined. Otherwise
795 ** they may interfere with compilation of other functions in this file
796 ** (or in another file, if this file becomes part of the amalgamation). */
804 #ifndef SQLITE_OMIT_VIRTUALTABLE
806 ** Generate code for an UPDATE of a virtual table.
808 ** There are two possible strategies - the default and the special
809 ** "onepass" strategy. Onepass is only used if the virtual table
810 ** implementation indicates that pWhere may match at most one row.
812 ** The default strategy is to create an ephemeral table that contains
813 ** for each row to be changed:
815 ** (A) The original rowid of that row.
816 ** (B) The revised rowid for the row.
817 ** (C) The content of every column in the row.
819 ** Then loop through the contents of this ephemeral table executing a
820 ** VUpdate for each row. When finished, drop the ephemeral table.
822 ** The "onepass" strategy does not use an ephemeral table. Instead, it
823 ** stores the same values (A, B and C above) in a register array and
824 ** makes a single invocation of VUpdate.
826 static void updateVirtualTable(
827 Parse
*pParse
, /* The parsing context */
828 SrcList
*pSrc
, /* The virtual table to be modified */
829 Table
*pTab
, /* The virtual table */
830 ExprList
*pChanges
, /* The columns to change in the UPDATE statement */
831 Expr
*pRowid
, /* Expression used to recompute the rowid */
832 int *aXRef
, /* Mapping from columns of pTab to entries in pChanges */
833 Expr
*pWhere
, /* WHERE clause of the UPDATE statement */
834 int onError
/* ON CONFLICT strategy */
836 Vdbe
*v
= pParse
->pVdbe
; /* Virtual machine under construction */
837 int ephemTab
; /* Table holding the result of the SELECT */
838 int i
; /* Loop counter */
839 sqlite3
*db
= pParse
->db
; /* Database connection */
840 const char *pVTab
= (const char*)sqlite3GetVTable(db
, pTab
);
842 int nArg
= 2 + pTab
->nCol
; /* Number of arguments to VUpdate */
843 int regArg
; /* First register in VUpdate arg array */
844 int regRec
; /* Register in which to assemble record */
845 int regRowid
; /* Register for ephem table rowid */
846 int iCsr
= pSrc
->a
[0].iCursor
; /* Cursor used for virtual table scan */
847 int aDummy
[2]; /* Unused arg for sqlite3WhereOkOnePass() */
848 int eOnePass
; /* True to use onepass strategy */
849 int addr
; /* Address of OP_OpenEphemeral */
851 /* Allocate nArg registers in which to gather the arguments for VUpdate. Then
852 ** create and open the ephemeral table in which the records created from
853 ** these arguments will be temporarily stored. */
855 ephemTab
= pParse
->nTab
++;
856 addr
= sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, ephemTab
, nArg
);
857 regArg
= pParse
->nMem
+ 1;
858 pParse
->nMem
+= nArg
;
859 regRec
= ++pParse
->nMem
;
860 regRowid
= ++pParse
->nMem
;
862 /* Start scanning the virtual table */
863 pWInfo
= sqlite3WhereBegin(pParse
, pSrc
, pWhere
, 0,0,WHERE_ONEPASS_DESIRED
,0);
864 if( pWInfo
==0 ) return;
866 /* Populate the argument registers. */
867 for(i
=0; i
<pTab
->nCol
; i
++){
869 sqlite3ExprCode(pParse
, pChanges
->a
[aXRef
[i
]].pExpr
, regArg
+2+i
);
871 sqlite3VdbeAddOp3(v
, OP_VColumn
, iCsr
, i
, regArg
+2+i
);
872 sqlite3VdbeChangeP5(v
, 1); /* Enable sqlite3_vtab_nochange() */
875 if( HasRowid(pTab
) ){
876 sqlite3VdbeAddOp2(v
, OP_Rowid
, iCsr
, regArg
);
878 sqlite3ExprCode(pParse
, pRowid
, regArg
+1);
880 sqlite3VdbeAddOp2(v
, OP_Rowid
, iCsr
, regArg
+1);
883 Index
*pPk
; /* PRIMARY KEY index */
884 i16 iPk
; /* PRIMARY KEY column */
885 pPk
= sqlite3PrimaryKeyIndex(pTab
);
887 assert( pPk
->nKeyCol
==1 );
888 iPk
= pPk
->aiColumn
[0];
889 sqlite3VdbeAddOp3(v
, OP_VColumn
, iCsr
, iPk
, regArg
);
890 sqlite3VdbeAddOp2(v
, OP_SCopy
, regArg
+2+iPk
, regArg
+1);
893 eOnePass
= sqlite3WhereOkOnePass(pWInfo
, aDummy
);
895 /* There is no ONEPASS_MULTI on virtual tables */
896 assert( eOnePass
==ONEPASS_OFF
|| eOnePass
==ONEPASS_SINGLE
);
899 /* If using the onepass strategy, no-op out the OP_OpenEphemeral coded
901 sqlite3VdbeChangeToNoop(v
, addr
);
902 sqlite3VdbeAddOp1(v
, OP_Close
, iCsr
);
904 /* Create a record from the argument register contents and insert it into
905 ** the ephemeral table. */
906 sqlite3MultiWrite(pParse
);
907 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regArg
, nArg
, regRec
);
909 /* Signal an assert() within OP_MakeRecord that it is allowed to
910 ** accept no-change records with serial_type 10 */
911 sqlite3VdbeChangeP5(v
, OPFLAG_NOCHNG_MAGIC
);
913 sqlite3VdbeAddOp2(v
, OP_NewRowid
, ephemTab
, regRowid
);
914 sqlite3VdbeAddOp3(v
, OP_Insert
, ephemTab
, regRec
, regRowid
);
918 if( eOnePass
==ONEPASS_OFF
){
919 /* End the virtual table scan */
920 sqlite3WhereEnd(pWInfo
);
922 /* Begin scannning through the ephemeral table. */
923 addr
= sqlite3VdbeAddOp1(v
, OP_Rewind
, ephemTab
); VdbeCoverage(v
);
925 /* Extract arguments from the current row of the ephemeral table and
926 ** invoke the VUpdate method. */
927 for(i
=0; i
<nArg
; i
++){
928 sqlite3VdbeAddOp3(v
, OP_Column
, ephemTab
, i
, regArg
+i
);
931 sqlite3VtabMakeWritable(pParse
, pTab
);
932 sqlite3VdbeAddOp4(v
, OP_VUpdate
, 0, nArg
, regArg
, pVTab
, P4_VTAB
);
933 sqlite3VdbeChangeP5(v
, onError
==OE_Default
? OE_Abort
: onError
);
934 sqlite3MayAbort(pParse
);
936 /* End of the ephemeral table scan. Or, if using the onepass strategy,
937 ** jump to here if the scan visited zero rows. */
938 if( eOnePass
==ONEPASS_OFF
){
939 sqlite3VdbeAddOp2(v
, OP_Next
, ephemTab
, addr
+1); VdbeCoverage(v
);
940 sqlite3VdbeJumpHere(v
, addr
);
941 sqlite3VdbeAddOp2(v
, OP_Close
, ephemTab
, 0);
943 sqlite3WhereEnd(pWInfo
);
946 #endif /* SQLITE_OMIT_VIRTUALTABLE */