SQLite

Check-in [4852186aca]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Only evaluate expressions once for UPDATE and INSERT statements that have BEFORE triggers. Fix for ticket #980. (CVS 2158)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 4852186aca3be6ea40069b6831079197e5fa757a
User & Date: drh 2004-12-07 15:41:49.000
Context
2004-12-09
18:29
Enhance sqlite3_bind_parameter_index so that is does not segfault if you call it incorrectly by passing NULL in place of the parameter name. Ticket #1032. (CVS 2159) (check-in: bf81aabff1 user: drh tags: trunk)
2004-12-07
15:41
Only evaluate expressions once for UPDATE and INSERT statements that have BEFORE triggers. Fix for ticket #980. (CVS 2158) (check-in: 4852186aca user: drh tags: trunk)
14:06
Simplify the trigger logic for DELETE, INSERT, and UPDATE. (CVS 2157) (check-in: 8e164ab277 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/expr.c.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.174 2004/11/23 01:47:30 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.175 2004/12/07 15:41:49 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
1542
1543
1544
1545
1546
1547
1548

























1549
1550
1551
1552
1553
1554
1555
         sqlite3VdbeAddOp(v, OP_Goto, 0, pParse->trigStack->ignoreJump);
         VdbeComment((v, "# raise(IGNORE)"));
      }
    }
    break;
  }
}


























/*
** Generate code that pushes the value of every element of the given
** expression list onto the stack.
**
** Return the number of elements pushed onto the stack.
*/







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
         sqlite3VdbeAddOp(v, OP_Goto, 0, pParse->trigStack->ignoreJump);
         VdbeComment((v, "# raise(IGNORE)"));
      }
    }
    break;
  }
}

/*
** Generate code that evalutes the given expression and leaves the result
** on the stack.  See also sqlite3ExprCode().
**
** This routine might also cache the result and modify the pExpr tree
** so that it will make use of the cached result on subsequent evaluations
** rather than evaluate the whole expression again.  Trivial expressions are
** not cached.  If the expression is cached, its result is stored in a 
** memory location.
*/
void sqlite3ExprCodeAndCache(Parse *pParse, Expr *pExpr){
  Vdbe *v = pParse->pVdbe;
  int iMem;
  int addr1, addr2;
  if( v==0 ) return;
  addr1 = sqlite3VdbeCurrentAddr(v);
  sqlite3ExprCode(pParse, pExpr);
  addr2 = sqlite3VdbeCurrentAddr(v);
  if( addr2>addr1+1 || sqlite3VdbeGetOp(v, addr1)->opcode==OP_Function ){
    iMem = pExpr->iTable = pParse->nMem++;
    sqlite3VdbeAddOp(v, OP_MemStore, iMem, 0);
    pExpr->op = TK_REGISTER;
  }
}

/*
** Generate code that pushes the value of every element of the given
** expression list onto the stack.
**
** Return the number of elements pushed onto the stack.
*/
Changes to src/insert.c.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle INSERT statements in SQLite.
**
** $Id: insert.c,v 1.127 2004/12/07 14:06:13 drh Exp $
*/
#include "sqliteInt.h"

/*
** Set P3 of the most recently inserted opcode to a column affinity
** string for index pIdx. A column affinity string has one character
** for each column in the table, according to the affinity of the column:







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle INSERT statements in SQLite.
**
** $Id: insert.c,v 1.128 2004/12/07 15:41:49 drh Exp $
*/
#include "sqliteInt.h"

/*
** Set P3 of the most recently inserted opcode to a column affinity
** string for index pIdx. A column affinity string has one character
** for each column in the table, according to the affinity of the column:
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
      if( pColumn && j>=pColumn->nId ){
        sqlite3ExprCode(pParse, pTab->aCol[i].pDflt);
      }else if( useTempTable ){
        sqlite3VdbeAddOp(v, OP_Column, srcTab, j); 
      }else if( pSelect ){
        sqlite3VdbeAddOp(v, OP_Dup, nColumn-j-1, 1);
      }else{
        sqlite3ExprCode(pParse, pList->a[j].pExpr);
      }
    }
    sqlite3VdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);

    /* If this is an INSERT on a view with an INSTEAD OF INSERT trigger,
    ** do not attempt any conversions before assembling the record.
    ** If this is a real table, attempt conversions as required by the







|







519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
      if( pColumn && j>=pColumn->nId ){
        sqlite3ExprCode(pParse, pTab->aCol[i].pDflt);
      }else if( useTempTable ){
        sqlite3VdbeAddOp(v, OP_Column, srcTab, j); 
      }else if( pSelect ){
        sqlite3VdbeAddOp(v, OP_Dup, nColumn-j-1, 1);
      }else{
        sqlite3ExprCodeAndCache(pParse, pList->a[j].pExpr);
      }
    }
    sqlite3VdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);

    /* If this is an INSERT on a view with an INSTEAD OF INSERT trigger,
    ** do not attempt any conversions before assembling the record.
    ** If this is a real table, attempt conversions as required by the
Changes to src/sqliteInt.h.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2001 September 15
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.345 2004/12/07 14:06:13 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** These #defines should enable >2GB file support on Posix if the
** underlying operating system supports it.  If the OS lacks













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2001 September 15
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.346 2004/12/07 15:41:49 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** These #defines should enable >2GB file support on Posix if the
** underlying operating system supports it.  If the OS lacks
1350
1351
1352
1353
1354
1355
1356

1357
1358
1359
1360
1361
1362
1363
void sqlite3OpenTableForReading(Vdbe*, int iCur, Table*);
void sqlite3OpenTable(Vdbe*, int iCur, Table*, int);
void sqlite3DeleteFrom(Parse*, SrcList*, Expr*);
void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int);
WhereInfo *sqlite3WhereBegin(Parse*, SrcList*, Expr*, int, ExprList**, Fetch*);
void sqlite3WhereEnd(WhereInfo*);
void sqlite3ExprCode(Parse*, Expr*);

int sqlite3ExprCodeExprList(Parse*, ExprList*);
void sqlite3ExprIfTrue(Parse*, Expr*, int, int);
void sqlite3ExprIfFalse(Parse*, Expr*, int, int);
void sqlite3NextedParse(Parse*, const char*, ...);
Table *sqlite3FindTable(sqlite3*,const char*, const char*);
Table *sqlite3LocateTable(Parse*,const char*, const char*);
Index *sqlite3FindIndex(sqlite3*,const char*, const char*);







>







1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
void sqlite3OpenTableForReading(Vdbe*, int iCur, Table*);
void sqlite3OpenTable(Vdbe*, int iCur, Table*, int);
void sqlite3DeleteFrom(Parse*, SrcList*, Expr*);
void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int);
WhereInfo *sqlite3WhereBegin(Parse*, SrcList*, Expr*, int, ExprList**, Fetch*);
void sqlite3WhereEnd(WhereInfo*);
void sqlite3ExprCode(Parse*, Expr*);
void sqlite3ExprCodeAndCache(Parse*, Expr*);
int sqlite3ExprCodeExprList(Parse*, ExprList*);
void sqlite3ExprIfTrue(Parse*, Expr*, int, int);
void sqlite3ExprIfFalse(Parse*, Expr*, int, int);
void sqlite3NextedParse(Parse*, const char*, ...);
Table *sqlite3FindTable(sqlite3*,const char*, const char*);
Table *sqlite3LocateTable(Parse*,const char*, const char*);
Index *sqlite3FindIndex(sqlite3*,const char*, const char*);
Changes to src/update.c.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle UPDATE statements.
**
** $Id: update.c,v 1.98 2004/12/07 14:06:13 drh Exp $
*/
#include "sqliteInt.h"

/*
** Process an UPDATE statement.
**
**   UPDATE OR IGNORE table_wxyz SET a=b, c=d WHERE e<5 AND f NOT NULL;







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle UPDATE statements.
**
** $Id: update.c,v 1.99 2004/12/07 15:41:49 drh Exp $
*/
#include "sqliteInt.h"

/*
** Process an UPDATE statement.
**
**   UPDATE OR IGNORE table_wxyz SET a=b, c=d WHERE e<5 AND f NOT NULL;
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
    sqlite3VdbeAddOp(v, OP_Recno, iCur, 0);
    sqlite3VdbeAddOp(v, OP_RowData, iCur, 0);
    sqlite3VdbeAddOp(v, OP_PutIntKey, oldIdx, 0);

    /* Generate the NEW table
    */
    if( chngRecno ){
      sqlite3ExprCode(pParse, pRecnoExpr);
    }else{
      sqlite3VdbeAddOp(v, OP_Recno, iCur, 0);
    }
    for(i=0; i<pTab->nCol; i++){ /* TODO: Factor out this loop as common code */
      if( i==pTab->iPKey ){
        sqlite3VdbeAddOp(v, OP_String8, 0, 0);
        continue;
      }
      j = aXRef[i];
      if( j<0 ){
        sqlite3VdbeAddOp(v, OP_Column, iCur, i);
      }else{
        sqlite3ExprCode(pParse, pChanges->a[j].pExpr);
      }
    }
    sqlite3VdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
    if( !isView ){
      sqlite3TableAffinityStr(v, pTab);
    }
    if( pParse->nErr ) goto update_cleanup;







|



|








|







272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
    sqlite3VdbeAddOp(v, OP_Recno, iCur, 0);
    sqlite3VdbeAddOp(v, OP_RowData, iCur, 0);
    sqlite3VdbeAddOp(v, OP_PutIntKey, oldIdx, 0);

    /* Generate the NEW table
    */
    if( chngRecno ){
      sqlite3ExprCodeAndCache(pParse, pRecnoExpr);
    }else{
      sqlite3VdbeAddOp(v, OP_Recno, iCur, 0);
    }
    for(i=0; i<pTab->nCol; i++){
      if( i==pTab->iPKey ){
        sqlite3VdbeAddOp(v, OP_String8, 0, 0);
        continue;
      }
      j = aXRef[i];
      if( j<0 ){
        sqlite3VdbeAddOp(v, OP_Column, iCur, i);
      }else{
        sqlite3ExprCodeAndCache(pParse, pChanges->a[j].pExpr);
      }
    }
    sqlite3VdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
    if( !isView ){
      sqlite3TableAffinityStr(v, pTab);
    }
    if( pParse->nErr ) goto update_cleanup;
Added test/trigger6.test.




































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
# 2004 December 07
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests to make sure expression of an INSERT
# and UPDATE statement are only evaluated once.  See ticket #980.
# If an expression uses a function that has side-effects or which
# is not deterministic (ex: random()) then we want to make sure
# that the same evaluation occurs for the actual INSERT/UPDATE and
# for the NEW.* fields of any triggers that fire.
#
# $Id: trigger6.test,v 1.1 2004/12/07 15:41:50 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl
ifcapable {!trigger} {
  finish_test
  return
}

do_test trigger6-1.1 {
  execsql {
    CREATE TABLE t1(x, y);
    CREATE TABLE log(a, b, c);
    CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
      INSERT INTO log VALUES(1, new.x, new.y);
    END;
    CREATE TRIGGER r2 BEFORE UPDATE ON t1 BEGIN
      INSERT INTO log VALUES(2, new.x, new.y);
    END;
  }
  set ::trigger6_cnt 0
  proc trigger6_counter {args} {
    incr ::trigger6_cnt
    return $::trigger6_cnt
  }
  db function counter trigger6_counter
  execsql {
    INSERT INTO t1 VALUES(1,counter());
    SELECT * FROM t1;
  }
} {1 1}
do_test trigger6-1.2 {
  execsql {
    SELECT * FROM log;
  }
} {1 1 1}
do_test trigger6-1.3 {
  execsql {
    DELETE FROM t1;
    DELETE FROM log;
    INSERT INTO t1 VALUES(2,counter(2,3)+4);
    SELECT * FROM t1;
  }
} {2 6.0}
do_test trigger6-1.4 {
  execsql {
    SELECT * FROM log;
  }
} {1 2 6.0}
do_test trigger6-1.5 {
  execsql {
    DELETE FROM log;
    UPDATE t1 SET y=counter(5);
    SELECT * FROM t1;
  }
} {2 3}
do_test trigger6-1.6 {
  execsql {
    SELECT * FROM log;
  }
} {2 2 3}

finish_test