/ Check-in [41cdd0c4]
Login

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

Overview
Comment:Modify the documentation for sqlite3_changes() to make it more testable. Add tests and minor fixes for the same.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 41cdd0c422d61533a94870cb5ad094682956d472
User & Date: dan 2014-10-28 18:24:16
Context
2014-10-28
20:35
Update the documentation on the sqlite3_randomness() interface to conform to enhancements associated with the SQLITE_ENABLE_API_ARMOR change. check-in: 96e9917c user: drh tags: trunk
18:24
Modify the documentation for sqlite3_changes() to make it more testable. Add tests and minor fixes for the same. check-in: 41cdd0c4 user: dan tags: trunk
16:50
Fix a crash that could occur if the WHERE clause of an UPDATE statement on a view that does not feature a column named "rowid" contains a term such as "rowid=?". check-in: 8523670d user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/sqlite.h.in.

  1870   1870   ** last insert [rowid].
  1871   1871   */
  1872   1872   sqlite3_int64 sqlite3_last_insert_rowid(sqlite3*);
  1873   1873   
  1874   1874   /*
  1875   1875   ** CAPI3REF: Count The Number Of Rows Modified
  1876   1876   **
  1877         -** ^This function returns the number of database rows that were changed
  1878         -** or inserted or deleted by the most recently completed SQL statement
  1879         -** on the [database connection] specified by the first parameter.
  1880         -** ^(Only changes that are directly specified by the [INSERT], [UPDATE],
  1881         -** or [DELETE] statement are counted.  Auxiliary changes caused by
  1882         -** triggers or [foreign key actions] are not counted.)^ Use the
  1883         -** [sqlite3_total_changes()] function to find the total number of changes
  1884         -** including changes caused by triggers and foreign key actions.
  1885         -**
  1886         -** ^Changes to a view that are simulated by an [INSTEAD OF trigger]
  1887         -** are not counted.  Only real table changes are counted.
  1888         -**
  1889         -** ^(A "row change" is a change to a single row of a single table
  1890         -** caused by an INSERT, DELETE, or UPDATE statement.  Rows that
  1891         -** are changed as side effects of [REPLACE] constraint resolution,
  1892         -** rollback, ABORT processing, [DROP TABLE], or by any other
  1893         -** mechanisms do not count as direct row changes.)^
  1894         -**
  1895         -** A "trigger context" is a scope of execution that begins and
  1896         -** ends with the script of a [CREATE TRIGGER | trigger]. 
  1897         -** Most SQL statements are
  1898         -** evaluated outside of any trigger.  This is the "top level"
  1899         -** trigger context.  If a trigger fires from the top level, a
  1900         -** new trigger context is entered for the duration of that one
  1901         -** trigger.  Subtriggers create subcontexts for their duration.
  1902         -**
  1903         -** ^Calling [sqlite3_exec()] or [sqlite3_step()] recursively does
  1904         -** not create a new trigger context.
  1905         -**
  1906         -** ^This function returns the number of direct row changes in the
  1907         -** most recent INSERT, UPDATE, or DELETE statement within the same
  1908         -** trigger context.
  1909         -**
  1910         -** ^Thus, when called from the top level, this function returns the
  1911         -** number of changes in the most recent INSERT, UPDATE, or DELETE
  1912         -** that also occurred at the top level.  ^(Within the body of a trigger,
  1913         -** the sqlite3_changes() interface can be called to find the number of
  1914         -** changes in the most recently completed INSERT, UPDATE, or DELETE
  1915         -** statement within the body of the same trigger.
  1916         -** However, the number returned does not include changes
  1917         -** caused by subtriggers since those have their own context.)^
         1877  +** ^This function returns the number of rows modified, inserted or
         1878  +** deleted by the most recently completed INSERT, UPDATE or DELETE
         1879  +** statement on the database connection specified by the only parameter.
         1880  +** ^Executing any other type of SQL statement does not modify the value
         1881  +** returned by this function.
         1882  +**
         1883  +** ^Only changes made directly by the INSERT, UPDATE or DELETE statement are
         1884  +** considered - auxiliary changes caused by [CREATE TRIGGER | triggers], 
         1885  +** [foreign key actions] or [REPLACE] constraint resolution are not counted.
         1886  +** 
         1887  +** Changes to a view that are intercepted by 
         1888  +** [INSTEAD OF trigger | INSTEAD OF triggers] are not counted. ^The value 
         1889  +** returned by sqlite3_changes() immediately after an INSERT, UPDATE or 
         1890  +** DELETE statement run on a view is always zero. Only changes made to real 
         1891  +** tables are counted.
         1892  +**
         1893  +** Things are more complicated if the sqlite3_changes() function is
         1894  +** executed while a trigger program is running. This may happen if the
         1895  +** program uses the [changes() SQL function], or if some other callback
         1896  +** function invokes sqlite3_changes() directly. Essentially:
         1897  +** 
         1898  +** <ul>
         1899  +**   <li> ^(Before entering a trigger program the value returned by
         1900  +**        sqlite3_changes() function is saved. After the trigger program 
         1901  +**        has finished, the original value is restored.)^
         1902  +** 
         1903  +**   <li> ^(Within a trigger program each INSERT, UPDATE and DELETE 
         1904  +**        statement sets the value returned by sqlite3_changes() 
         1905  +**        upon completion as normal. Of course, this value will not include 
         1906  +**        any changes performed by sub-triggers, as the sqlite3_changes() 
         1907  +**        value will be saved and restored after each sub-trigger has run.)^
         1908  +** </ul>
         1909  +** 
         1910  +** ^This means that if the changes() SQL function (or similar) is used
         1911  +** by the first INSERT, UPDATE or DELETE statement within a trigger, it 
         1912  +** returns the value as set when the calling statement began executing.
         1913  +** ^If it is used by the second or subsequent such statement within a trigger 
         1914  +** program, the value returned reflects the number of rows modified by the 
         1915  +** previous INSERT, UPDATE or DELETE statement within the same trigger.
  1918   1916   **
  1919   1917   ** See also the [sqlite3_total_changes()] interface, the
  1920   1918   ** [count_changes pragma], and the [changes() SQL function].
  1921   1919   **
  1922   1920   ** If a separate thread makes changes on the same database connection
  1923   1921   ** while [sqlite3_changes()] is running then the value returned
  1924   1922   ** is unpredictable and not meaningful.
................................................................................
  1930   1928   **
  1931   1929   ** ^This function returns the number of row changes caused by [INSERT],
  1932   1930   ** [UPDATE] or [DELETE] statements since the [database connection] was opened.
  1933   1931   ** ^(The count returned by sqlite3_total_changes() includes all changes
  1934   1932   ** from all [CREATE TRIGGER | trigger] contexts and changes made by
  1935   1933   ** [foreign key actions]. However,
  1936   1934   ** the count does not include changes used to implement [REPLACE] constraints,
  1937         -** do rollbacks or ABORT processing, or [DROP TABLE] processing.  The
         1935  +** rollbacks or [DROP TABLE] commands.  The
  1938   1936   ** count does not include rows of views that fire an [INSTEAD OF trigger],
  1939   1937   ** though if the INSTEAD OF trigger makes changes of its own, those changes 
  1940   1938   ** are counted.)^
  1941   1939   ** ^The sqlite3_total_changes() function counts the changes as soon as
  1942   1940   ** the statement that makes them is completed (when the statement handle
  1943   1941   ** is passed to [sqlite3_reset()] or [sqlite3_finalize()]).
  1944   1942   **

Changes to src/vdbe.c.

  5419   5419       assert( pc==pFrame->pc );
  5420   5420     }
  5421   5421   
  5422   5422     p->nFrame++;
  5423   5423     pFrame->pParent = p->pFrame;
  5424   5424     pFrame->lastRowid = lastRowid;
  5425   5425     pFrame->nChange = p->nChange;
         5426  +  pFrame->nDbChange = p->db->nChange;
  5426   5427     p->nChange = 0;
  5427   5428     p->pFrame = pFrame;
  5428   5429     p->aMem = aMem = &VdbeFrameMem(pFrame)[-1];
  5429   5430     p->nMem = pFrame->nChildMem;
  5430   5431     p->nCursor = (u16)pFrame->nChildCsr;
  5431   5432     p->apCsr = (VdbeCursor **)&aMem[p->nMem+1];
  5432   5433     p->aOp = aOp = pProgram->aOp;

Changes to src/vdbeInt.h.

   140    140     int nCursor;            /* Number of entries in apCsr */
   141    141     int pc;                 /* Program Counter in parent (calling) frame */
   142    142     int nOp;                /* Size of aOp array */
   143    143     int nMem;               /* Number of entries in aMem */
   144    144     int nOnceFlag;          /* Number of entries in aOnceFlag */
   145    145     int nChildMem;          /* Number of memory cells for child frame */
   146    146     int nChildCsr;          /* Number of cursors for child frame */
   147         -  int nChange;            /* Statement changes (Vdbe.nChanges)     */
          147  +  int nChange;            /* Statement changes (Vdbe.nChange)     */
          148  +  int nDbChange;          /* Value of db->nChange */
   148    149   };
   149    150   
   150    151   #define VdbeFrameMem(p) ((Mem *)&((u8 *)p)[ROUND8(sizeof(VdbeFrame))])
   151    152   
   152    153   /*
   153    154   ** A value for VdbeCursor.cacheValid that means the cache is always invalid.
   154    155   */

Changes to src/vdbeaux.c.

  1768   1768     v->nOp = pFrame->nOp;
  1769   1769     v->aMem = pFrame->aMem;
  1770   1770     v->nMem = pFrame->nMem;
  1771   1771     v->apCsr = pFrame->apCsr;
  1772   1772     v->nCursor = pFrame->nCursor;
  1773   1773     v->db->lastRowid = pFrame->lastRowid;
  1774   1774     v->nChange = pFrame->nChange;
         1775  +  v->db->nChange = pFrame->nDbChange;
  1775   1776     return pFrame->pc;
  1776   1777   }
  1777   1778   
  1778   1779   /*
  1779   1780   ** Close all cursors.
  1780   1781   **
  1781   1782   ** Also release any dynamic memory held by the VM in the Vdbe.aMem memory 
................................................................................
  2335   2336           }else{
  2336   2337             /* We are forced to roll back the active transaction. Before doing
  2337   2338             ** so, abort any other statements this handle currently has active.
  2338   2339             */
  2339   2340             sqlite3RollbackAll(db, SQLITE_ABORT_ROLLBACK);
  2340   2341             sqlite3CloseSavepoints(db);
  2341   2342             db->autoCommit = 1;
         2343  +          p->nChange = 0;
  2342   2344           }
  2343   2345         }
  2344   2346       }
  2345   2347   
  2346   2348       /* Check for immediate foreign key violations. */
  2347   2349       if( p->rc==SQLITE_OK ){
  2348   2350         sqlite3VdbeCheckFk(p, 0);
................................................................................
  2375   2377           }
  2376   2378           if( rc==SQLITE_BUSY && p->readOnly ){
  2377   2379             sqlite3VdbeLeave(p);
  2378   2380             return SQLITE_BUSY;
  2379   2381           }else if( rc!=SQLITE_OK ){
  2380   2382             p->rc = rc;
  2381   2383             sqlite3RollbackAll(db, SQLITE_OK);
         2384  +          p->nChange = 0;
  2382   2385           }else{
  2383   2386             db->nDeferredCons = 0;
  2384   2387             db->nDeferredImmCons = 0;
  2385   2388             db->flags &= ~SQLITE_DeferFKs;
  2386   2389             sqlite3CommitInternalChanges(db);
  2387   2390           }
  2388   2391         }else{
  2389   2392           sqlite3RollbackAll(db, SQLITE_OK);
         2393  +        p->nChange = 0;
  2390   2394         }
  2391   2395         db->nStatement = 0;
  2392   2396       }else if( eStatementOp==0 ){
  2393   2397         if( p->rc==SQLITE_OK || p->errorAction==OE_Fail ){
  2394   2398           eStatementOp = SAVEPOINT_RELEASE;
  2395   2399         }else if( p->errorAction==OE_Abort ){
  2396   2400           eStatementOp = SAVEPOINT_ROLLBACK;
  2397   2401         }else{
  2398   2402           sqlite3RollbackAll(db, SQLITE_ABORT_ROLLBACK);
  2399   2403           sqlite3CloseSavepoints(db);
  2400   2404           db->autoCommit = 1;
         2405  +        p->nChange = 0;
  2401   2406         }
  2402   2407       }
  2403   2408     
  2404   2409       /* If eStatementOp is non-zero, then a statement transaction needs to
  2405   2410       ** be committed or rolled back. Call sqlite3VdbeCloseStatement() to
  2406   2411       ** do so. If this operation returns an error, and the current statement
  2407   2412       ** error code is SQLITE_OK or SQLITE_CONSTRAINT, then promote the
................................................................................
  2414   2419             p->rc = rc;
  2415   2420             sqlite3DbFree(db, p->zErrMsg);
  2416   2421             p->zErrMsg = 0;
  2417   2422           }
  2418   2423           sqlite3RollbackAll(db, SQLITE_ABORT_ROLLBACK);
  2419   2424           sqlite3CloseSavepoints(db);
  2420   2425           db->autoCommit = 1;
         2426  +        p->nChange = 0;
  2421   2427         }
  2422   2428       }
  2423   2429     
  2424   2430       /* If this was an INSERT, UPDATE or DELETE and no statement transaction
  2425   2431       ** has been rolled back, update the database connection change-counter. 
  2426   2432       */
  2427   2433       if( p->changeCntOn ){

Added test/e_changes.test.

            1  +# 2011 October 28
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +
           13  +set testdir [file dirname $argv0]
           14  +source $testdir/tester.tcl
           15  +set testprefix e_changes
           16  +
           17  +# Like [do_execsql_test], except it appends the value returned by 
           18  +# [db changes] to the result of executing the SQL script.
           19  +#
           20  +proc do_changes_test {tn sql res} {
           21  +  uplevel [list \
           22  +    do_test $tn "concat \[execsql {$sql}\] \[db changes\]" $res
           23  +  ]
           24  +}
           25  +
           26  +
           27  +#--------------------------------------------------------------------------
           28  +# EVIDENCE-OF: R-15996-49369 This function returns the number of rows
           29  +# modified, inserted or deleted by the most recently completed INSERT,
           30  +# UPDATE or DELETE statement on the database connection specified by the
           31  +# only parameter.
           32  +#
           33  +do_execsql_test 1.0 {
           34  +  CREATE TABLE t1(a, b);
           35  +  CREATE TABLE t2(x, y, PRIMARY KEY(x, y)) WITHOUT ROWID;
           36  +  CREATE INDEX i1 ON t1(a);
           37  +  CREATE INDEX i2 ON t2(y);
           38  +}
           39  +foreach {tn schema} {
           40  +  1 { 
           41  +      CREATE TABLE t1(a, b);
           42  +      CREATE INDEX i1 ON t1(b);
           43  +  }
           44  +  2 { 
           45  +      CREATE TABLE t1(a, b, PRIMARY KEY(a, b)) WITHOUT ROWID;
           46  +      CREATE INDEX i1 ON t1(b);
           47  +  }
           48  +} {
           49  +  reset_db
           50  +  execsql $schema
           51  +
           52  +  # Insert 1 row.
           53  +  do_changes_test 1.$tn.1 { INSERT INTO t1 VALUES(0, 0) } 1
           54  +
           55  +  # Insert 10 rows.
           56  +  do_changes_test 1.$tn.2 {
           57  +    WITH rows(i, j) AS (
           58  +        SELECT 1, 1 UNION ALL SELECT i+1, j+i FROM rows WHERE i<10
           59  +    )
           60  +    INSERT INTO t1 SELECT * FROM rows
           61  +  } 10
           62  +
           63  +  # Modify 5 rows.
           64  +  do_changes_test 1.$tn.3 {
           65  +    UPDATE t1 SET b=b+1 WHERE a<5;
           66  +  } 5
           67  +
           68  +  # Delete 4 rows
           69  +  do_changes_test 1.$tn.4 {
           70  +    DELETE FROM t1 WHERE a>6
           71  +  } 4
           72  +
           73  +  # Check the "on the database connecton specified" part of hte
           74  +  # requirement - changes made by other connections do not show up in
           75  +  # the return value of sqlite3_changes().
           76  +  do_test 1.$tn.5 {
           77  +    sqlite3 db2 test.db
           78  +    execsql { INSERT INTO t1 VALUES(-1, -1) } db2
           79  +    db2 changes
           80  +  } 1
           81  +  do_test 1.$tn.6 {
           82  +    db changes
           83  +  } 4
           84  +  db2 close
           85  +
           86  +  # Test that statements that modify no rows because they hit UNIQUE
           87  +  # constraints set the sqlite3_changes() value to 0. Regardless of
           88  +  # whether or not they are executed inside an explicit transaction.
           89  +  #
           90  +  #   1.$tn.8-9: outside of a transaction
           91  +  #   1.$tn.10-12: inside a transaction
           92  +  #
           93  +  do_changes_test 1.$tn.7 {
           94  +    CREATE UNIQUE INDEX i2 ON t1(a);
           95  +  } 4
           96  +  do_catchsql_test 1.$tn.8 {
           97  +    INSERT INTO t1 VALUES('a', 0), ('b', 0), ('c', 0), (0, 11);
           98  +  } {1 {UNIQUE constraint failed: t1.a}}
           99  +  do_test 1.$tn.9 { db changes } 0
          100  +  do_catchsql_test 1.$tn.10 {
          101  +    BEGIN;
          102  +      INSERT INTO t1 VALUES('a', 0), ('b', 0), ('c', 0), (0, 11);
          103  +  } {1 {UNIQUE constraint failed: t1.a}}
          104  +  do_test 1.$tn.11 { db changes } 0
          105  +  do_changes_test 1.$tn.12 COMMIT 0
          106  +
          107  +}
          108  +
          109  +
          110  +#--------------------------------------------------------------------------
          111  +# EVIDENCE-OF: R-44877-05564 Executing any other type of SQL statement
          112  +# does not modify the value returned by this function.
          113  +#
          114  +reset_db
          115  +do_changes_test 2.1 { CREATE TABLE t1(x)          } 0
          116  +do_changes_test 2.2 { 
          117  +  WITH d(y) AS (SELECT 1 UNION ALL SELECT y+1 FROM d WHERE y<47)
          118  +  INSERT INTO t1 SELECT y FROM d;
          119  +} 47
          120  +
          121  +# The statement above set changes() to 47. Check that none of the following
          122  +# modify this.
          123  +do_changes_test 2.3 { SELECT count(x) FROM t1 } {47 47}
          124  +do_changes_test 2.4 { DROP TABLE t1               } 47
          125  +do_changes_test 2.5 { CREATE TABLE t1(x)          } 47
          126  +do_changes_test 2.6 { ALTER TABLE t1 ADD COLUMN b } 47
          127  +
          128  +
          129  +#--------------------------------------------------------------------------
          130  +# EVIDENCE-OF: R-53938-27527 Only changes made directly by the INSERT,
          131  +# UPDATE or DELETE statement are considered - auxiliary changes caused
          132  +# by triggers, foreign key actions or REPLACE constraint resolution are
          133  +# not counted.
          134  +#
          135  +#   3.1.*: triggers
          136  +#   3.2.*: foreign key actions
          137  +#   3.3.*: replace constraints
          138  +#
          139  +reset_db
          140  +do_execsql_test 3.1.0 {
          141  +  CREATE TABLE log(x);
          142  +  CREATE TABLE p1(one PRIMARY KEY, two);
          143  +
          144  +  CREATE TRIGGER tr_ai AFTER INSERT ON p1 BEGIN
          145  +    INSERT INTO log VALUES('insert');
          146  +  END;
          147  +  CREATE TRIGGER tr_bd BEFORE DELETE ON p1 BEGIN
          148  +    INSERT INTO log VALUES('delete');
          149  +  END;
          150  +  CREATE TRIGGER tr_au AFTER UPDATE ON p1 BEGIN
          151  +    INSERT INTO log VALUES('update');
          152  +  END;
          153  +
          154  +}
          155  +
          156  +do_changes_test 3.1.1 {
          157  +  INSERT INTO p1 VALUES('a', 'A'), ('b', 'B'), ('c', 'C');
          158  +} 3
          159  +do_changes_test 3.1.2 {
          160  +  UPDATE p1 SET two = two||two;
          161  +} 3
          162  +do_changes_test 3.1.3 {
          163  +  DELETE FROM p1 WHERE one IN ('a', 'c');
          164  +} 2
          165  +do_execsql_test 3.1.4 {
          166  +  -- None of the inserts on table log were counted.
          167  +  SELECT count(*) FROM log
          168  +} 8
          169  +
          170  +do_execsql_test 3.2.0 {
          171  +  DELETE FROM p1;
          172  +  INSERT INTO p1 VALUES('a', 'A'), ('b', 'B'), ('c', 'C');
          173  +
          174  +  CREATE TABLE c1(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE SET NULL);
          175  +  CREATE TABLE c2(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE SET DEFAULT);
          176  +  CREATE TABLE c3(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE CASCADE);
          177  +  INSERT INTO c1 VALUES('a', 'aaa');
          178  +  INSERT INTO c2 VALUES('b', 'bbb');
          179  +  INSERT INTO c3 VALUES('c', 'ccc');
          180  +
          181  +  INSERT INTO p1 VALUES('d', 'D'), ('e', 'E'), ('f', 'F');
          182  +  CREATE TABLE c4(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE SET NULL);
          183  +  CREATE TABLE c5(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE SET DEFAULT);
          184  +  CREATE TABLE c6(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE CASCADE);
          185  +  INSERT INTO c4 VALUES('d', 'aaa');
          186  +  INSERT INTO c5 VALUES('e', 'bbb');
          187  +  INSERT INTO c6 VALUES('f', 'ccc');
          188  +
          189  +  PRAGMA foreign_keys = ON;
          190  +}
          191  +
          192  +do_changes_test 3.2.1 { DELETE FROM p1 WHERE one = 'a' } 1
          193  +do_changes_test 3.2.2 { DELETE FROM p1 WHERE one = 'b' } 1
          194  +do_changes_test 3.2.3 { DELETE FROM p1 WHERE one = 'c' } 1
          195  +do_execsql_test 3.2.4 { 
          196  +  SELECT * FROM c1;
          197  +  SELECT * FROM c2;
          198  +  SELECT * FROM c3;
          199  +} {{} aaa {} bbb}
          200  +
          201  +do_changes_test 3.2.5 { UPDATE p1 SET one = 'g' WHERE one = 'd' } 1
          202  +do_changes_test 3.2.6 { UPDATE p1 SET one = 'h' WHERE one = 'e' } 1
          203  +do_changes_test 3.2.7 { UPDATE p1 SET one = 'i' WHERE one = 'f' } 1
          204  +do_execsql_test 3.2.8 { 
          205  +  SELECT * FROM c4;
          206  +  SELECT * FROM c5;
          207  +  SELECT * FROM c6;
          208  +} {{} aaa {} bbb i ccc}
          209  +
          210  +do_execsql_test 3.3.0 {
          211  +  CREATE TABLE r1(a UNIQUE, b UNIQUE);
          212  +  INSERT INTO r1 VALUES('i', 'i');
          213  +  INSERT INTO r1 VALUES('ii', 'ii');
          214  +  INSERT INTO r1 VALUES('iii', 'iii');
          215  +  INSERT INTO r1 VALUES('iv', 'iv');
          216  +  INSERT INTO r1 VALUES('v', 'v');
          217  +  INSERT INTO r1 VALUES('vi', 'vi');
          218  +  INSERT INTO r1 VALUES('vii', 'vii');
          219  +}
          220  +
          221  +do_changes_test 3.3.1 { INSERT OR REPLACE INTO r1 VALUES('i', 1)    }   1
          222  +do_changes_test 3.3.2 { INSERT OR REPLACE INTO r1 VALUES('iv', 'v') }   1
          223  +do_changes_test 3.3.3 { UPDATE OR REPLACE r1 SET b='v' WHERE a='iii' }  1
          224  +do_changes_test 3.3.4 { UPDATE OR REPLACE r1 SET b='vi',a='vii' WHERE a='ii' } 1
          225  +do_execsql_test 3.3.5 { 
          226  +  SELECT * FROM r1 ORDER BY a;
          227  +} {i 1   iii v   vii vi}
          228  +
          229  +
          230  +#--------------------------------------------------------------------------
          231  +# EVIDENCE-OF: R-09813-48563 The value returned by sqlite3_changes()
          232  +# immediately after an INSERT, UPDATE or DELETE statement run on a view
          233  +# is always zero.
          234  +#
          235  +reset_db
          236  +do_execsql_test 4.1 {
          237  +  CREATE TABLE log(log);
          238  +  CREATE TABLE t1(x, y);
          239  +  INSERT INTO t1 VALUES(1, 2);
          240  +  INSERT INTO t1 VALUES(3, 4);
          241  +  INSERT INTO t1 VALUES(5, 6);
          242  +
          243  +  CREATE VIEW v1 AS SELECT * FROM t1;
          244  +  CREATE TRIGGER v1_i INSTEAD OF INSERT ON v1 BEGIN
          245  +    INSERT INTO log VALUES('insert');
          246  +  END;
          247  +  CREATE TRIGGER v1_u INSTEAD OF UPDATE ON v1 BEGIN
          248  +    INSERT INTO log VALUES('update'), ('update');
          249  +  END;
          250  +  CREATE TRIGGER v1_d INSTEAD OF DELETE ON v1 BEGIN
          251  +    INSERT INTO log VALUES('delete'), ('delete'), ('delete');
          252  +  END;
          253  +}
          254  +
          255  +do_changes_test 4.2.1 { INSERT INTO t1 SELECT * FROM t1 }  3
          256  +do_changes_test 4.2.2 { INSERT INTO v1 VALUES(1, 2) }      0
          257  +
          258  +do_changes_test 4.3.1 { INSERT INTO t1 SELECT * FROM t1 }  6
          259  +do_changes_test 4.3.2 { UPDATE v1 SET y='xyz' WHERE x=1 }  0
          260  +
          261  +do_changes_test 4.4.1 { INSERT INTO t1 SELECT * FROM t1 } 12
          262  +do_changes_test 4.4.2 { DELETE FROM v1 WHERE x=5 }         0
          263  +
          264  +
          265  +#--------------------------------------------------------------------------
          266  +# EVIDENCE-OF: R-32918-61474 Before entering a trigger program the value
          267  +# returned by sqlite3_changes() function is saved. After the trigger
          268  +# program has finished, the original value is restored.
          269  +#
          270  +reset_db
          271  +db func my_changes my_changes
          272  +set ::changes [list]
          273  +proc my_changes {x} {
          274  +  set res [db changes]
          275  +  lappend ::changes $x $res
          276  +  return $res
          277  +}
          278  +
          279  +do_execsql_test 5.1.0 {
          280  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
          281  +  CREATE TABLE t2(x);
          282  +  INSERT INTO t1 VALUES(1, NULL);
          283  +  INSERT INTO t1 VALUES(2, NULL);
          284  +  INSERT INTO t1 VALUES(3, NULL);
          285  +  CREATE TRIGGER AFTER UPDATE ON t1 BEGIN
          286  +    INSERT INTO t2 VALUES('a'), ('b'), ('c');
          287  +    SELECT my_changes('trigger');
          288  +  END;
          289  +}
          290  +
          291  +do_execsql_test 5.1.1 {
          292  +  INSERT INTO t2 VALUES('a'), ('b');
          293  +  UPDATE t1 SET b = my_changes('update');
          294  +  SELECT * FROM t1;
          295  +} {1 2 2 2 3 2}
          296  +
          297  +# Value is being restored to "2" when the trigger program exits.
          298  +do_test 5.1.2 {
          299  +  set ::changes
          300  +} {update 2 trigger 3 update 2 trigger 3 update 2 trigger 3}
          301  +
          302  +
          303  +reset_db
          304  +do_execsql_test 5.2.0 {
          305  +  CREATE TABLE t1(a, b);
          306  +  CREATE TABLE log(x);
          307  +  INSERT INTO t1 VALUES(1, 0);
          308  +  INSERT INTO t1 VALUES(2, 0);
          309  +  INSERT INTO t1 VALUES(3, 0);
          310  +  CREATE TRIGGER t1_a_u AFTER UPDATE ON t1 BEGIN
          311  +    INSERT INTO log VALUES(old.b || ' -> ' || new.b || ' c = ' || changes() );
          312  +  END;
          313  +  CREATE TABLE t2(a);
          314  +  INSERT INTO t2 VALUES(1), (2), (3);
          315  +  UPDATE t1 SET b = changes();
          316  +}
          317  +do_execsql_test 5.2.1 {
          318  +  SELECT * FROM t1;
          319  +} {1 3 2 3 3 3}
          320  +do_execsql_test 5.2.2 {
          321  +  SELECT * FROM log;
          322  +} {{0 -> 3 c = 3} {0 -> 3 c = 3} {0 -> 3 c = 3}}
          323  +
          324  +
          325  +#--------------------------------------------------------------------------
          326  +# EVIDENCE-OF: R-17146-37073 Within a trigger program each INSERT,
          327  +# UPDATE and DELETE statement sets the value returned by
          328  +# sqlite3_changes() upon completion as normal. Of course, this value
          329  +# will not include any changes performed by sub-triggers, as the
          330  +# sqlite3_changes() value will be saved and restored after each
          331  +# sub-trigger has run.
          332  +reset_db
          333  +do_execsql_test 6.0 {
          334  +
          335  +  CREATE TABLE t1(a, b);
          336  +  CREATE TABLE t2(a, b);
          337  +  CREATE TABLE t3(a, b);
          338  +  CREATE TABLE log(x);
          339  +
          340  +  CREATE TRIGGER t1_i BEFORE INSERT ON t1 BEGIN
          341  +    INSERT INTO t2 VALUES(new.a, new.b), (new.a, new.b);
          342  +    INSERT INTO log VALUES('t2->' || changes());
          343  +  END;
          344  +
          345  +  CREATE TRIGGER t2_i AFTER INSERT ON t2 BEGIN
          346  +    INSERT INTO t3 VALUES(new.a, new.b), (new.a, new.b), (new.a, new.b);
          347  +    INSERT INTO log VALUES('t3->' || changes());
          348  +  END;
          349  +
          350  +  CREATE TRIGGER t1_u AFTER UPDATE ON t1 BEGIN
          351  +    UPDATE t2 SET b=new.b WHERE a=old.a;
          352  +    INSERT INTO log VALUES('t2->' || changes());
          353  +  END;
          354  +
          355  +  CREATE TRIGGER t2_u BEFORE UPDATE ON t2 BEGIN
          356  +    UPDATE t3 SET b=new.b WHERE a=old.a;
          357  +    INSERT INTO log VALUES('t3->' || changes());
          358  +  END;
          359  +
          360  +  CREATE TRIGGER t1_d AFTER DELETE ON t1 BEGIN
          361  +    DELETE FROM t2 WHERE a=old.a AND b=old.b;
          362  +    INSERT INTO log VALUES('t2->' || changes());
          363  +  END;
          364  +
          365  +  CREATE TRIGGER t2_d BEFORE DELETE ON t2 BEGIN
          366  +    DELETE FROM t3 WHERE a=old.a AND b=old.b;
          367  +    INSERT INTO log VALUES('t3->' || changes());
          368  +  END;
          369  +}
          370  +
          371  +do_changes_test 6.1 {
          372  +  INSERT INTO t1 VALUES('+', 'o');
          373  +  SELECT * FROM log;
          374  +} {t3->3 t3->3 t2->2 1}
          375  +
          376  +do_changes_test 6.2 {
          377  +  DELETE FROM log;
          378  +  UPDATE t1 SET b='*';
          379  +  SELECT * FROM log;
          380  +} {t3->6 t3->6 t2->2 1}
          381  +
          382  +do_changes_test 6.3 {
          383  +  DELETE FROM log;
          384  +  DELETE FROM t1;
          385  +  SELECT * FROM log;
          386  +} {t3->6 t3->0 t2->2 1}
          387  +
          388  +
          389  +#--------------------------------------------------------------------------
          390  +# EVIDENCE-OF: R-43399-09409 This means that if the changes() SQL
          391  +# function (or similar) is used by the first INSERT, UPDATE or DELETE
          392  +# statement within a trigger, it returns the value as set when the
          393  +# calling statement began executing.
          394  +#
          395  +# EVIDENCE-OF: R-53215-27584 If it is used by the second or subsequent
          396  +# such statement within a trigger program, the value returned reflects
          397  +# the number of rows modified by the previous INSERT, UPDATE or DELETE
          398  +# statement within the same trigger.
          399  +#
          400  +reset_db
          401  +do_execsql_test 7.1 {
          402  +  CREATE TABLE q1(t);
          403  +  CREATE TABLE q2(u, v);
          404  +  CREATE TABLE q3(w);
          405  +
          406  +  CREATE TRIGGER q2_insert BEFORE INSERT ON q2 BEGIN
          407  +
          408  +    /* changes() returns value from previous I/U/D in callers context */
          409  +    INSERT INTO q1 VALUES('1:' || changes());
          410  +
          411  +    /* changes() returns value of previous I/U/D in this context */
          412  +    INSERT INTO q3 VALUES(changes()), (2), (3);
          413  +    INSERT INTO q1 VALUES('2:' || changes());
          414  +    INSERT INTO q3 VALUES(changes() + 3), (changes()+4);
          415  +    SELECT 'this does not affect things!';
          416  +    INSERT INTO q1 VALUES('3:' || changes());
          417  +    UPDATE q3 SET w = w+10 WHERE w%2;
          418  +    INSERT INTO q1 VALUES('4:' || changes());
          419  +    DELETE FROM q3;
          420  +    INSERT INTO q1 VALUES('5:' || changes());
          421  +  END;
          422  +}
          423  +
          424  +do_execsql_test 7.2 {
          425  +  INSERT INTO q2 VALUES('x', 'y');
          426  +  SELECT * FROM q1;
          427  +} {
          428  +  1:0   2:3   3:2   4:3   5:5
          429  +}
          430  +
          431  +do_execsql_test 7.3 {
          432  +  DELETE FROM q1;
          433  +  INSERT INTO q2 VALUES('x', 'y');
          434  +  SELECT * FROM q1;
          435  +} {
          436  +  1:5   2:3   3:2   4:3   5:5
          437  +}
          438  +
          439  +
          440  +
          441  +finish_test