/ Check-in [f84af4ad]
Login

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

Overview
Comment:Add new test file e_totalchanges.test, containing tests of the sqlite3_total_changes() interface.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: f84af4adcc34d7a4c72027bf5b038a1a45a4c307
User & Date: dan 2014-10-28 20:49:59
Context
2014-10-29
00:58
In the OP_Column opcode, when extracting a field that is past the end of a short record (because the row was originally inserted prior to ALTER TABLE ADD COLUMN) then make sure the output register is fully NULL and does not contain leftover flags (such as MEM_Ephem) from its previous use. Fix for ticket [43107840f1c02]. check-in: 24780f8d user: drh tags: trunk
2014-10-28
20:49
Add new test file e_totalchanges.test, containing tests of the sqlite3_total_changes() interface. check-in: f84af4ad user: dan tags: trunk
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
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/sqlite.h.in.

  1922   1922   ** is unpredictable and not meaningful.
  1923   1923   */
  1924   1924   int sqlite3_changes(sqlite3*);
  1925   1925   
  1926   1926   /*
  1927   1927   ** CAPI3REF: Total Number Of Rows Modified
  1928   1928   **
  1929         -** ^This function returns the number of row changes caused by [INSERT],
  1930         -** [UPDATE] or [DELETE] statements since the [database connection] was opened.
  1931         -** ^(The count returned by sqlite3_total_changes() includes all changes
  1932         -** from all [CREATE TRIGGER | trigger] contexts and changes made by
  1933         -** [foreign key actions]. However,
  1934         -** the count does not include changes used to implement [REPLACE] constraints,
  1935         -** rollbacks or [DROP TABLE] commands.  The
  1936         -** count does not include rows of views that fire an [INSTEAD OF trigger],
  1937         -** though if the INSTEAD OF trigger makes changes of its own, those changes 
  1938         -** are counted.)^
  1939         -** ^The sqlite3_total_changes() function counts the changes as soon as
  1940         -** the statement that makes them is completed (when the statement handle
  1941         -** is passed to [sqlite3_reset()] or [sqlite3_finalize()]).
  1942         -**
         1929  +** ^This function returns the total number of rows inserted, modified or
         1930  +** deleted by all [INSERT], [UPDATE] or [DELETE] statements completed
         1931  +** since the database connection was opened, including those executed as
         1932  +** part of trigger programs. ^Executing any other type of SQL statement
         1933  +** does not affect the value returned by sqlite3_total_changes().
         1934  +** 
         1935  +** ^Changes made as part of [foreign key actions] are included in the
         1936  +** count, but those made as part of REPLACE constraint resolution are
         1937  +** not. ^Changes to a view that are intercepted by INSTEAD OF triggers 
         1938  +** are not counted.
         1939  +** 
  1943   1940   ** See also the [sqlite3_changes()] interface, the
  1944   1941   ** [count_changes pragma], and the [total_changes() SQL function].
  1945   1942   **
  1946   1943   ** If a separate thread makes changes on the same database connection
  1947   1944   ** while [sqlite3_total_changes()] is running then the value
  1948   1945   ** returned is unpredictable and not meaningful.
  1949   1946   */

Added test/e_totalchanges.test.

            1  +# 2011 May 06
            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_totalchanges
           16  +
           17  +# Like [do_execsql_test], except it appends the value returned by 
           18  +# [db total_changes] to the result of executing the SQL script.
           19  +#
           20  +proc do_tc_test {tn sql res} {
           21  +  uplevel [list \
           22  +    do_test $tn "concat \[execsql {$sql}\] \[db total_changes\]" $res
           23  +  ]
           24  +}
           25  +
           26  +do_execsql_test 1.0 {
           27  +  CREATE TABLE t1(a, b);
           28  +  CREATE INDEX t1_b ON t1(b);
           29  +  CREATE TABLE t2(x, y, PRIMARY KEY(x, y)) WITHOUT ROWID;
           30  +  CREATE INDEX t2_y ON t2(y);
           31  +}
           32  +
           33  +
           34  +#--------------------------------------------------------------------------
           35  +# EVIDENCE-OF: R-65438-26258 This function returns the total number of
           36  +# rows inserted, modified or deleted by all INSERT, UPDATE or DELETE
           37  +# statements completed since the database connection was opened,
           38  +# including those executed as part of trigger programs.
           39  +#
           40  +#   1.1.*: different types of I/U/D statements,
           41  +#   1.2.*: trigger programs.
           42  +#
           43  +do_tc_test 1.1.1 {
           44  +  INSERT INTO t1 VALUES(1, 2);
           45  +  INSERT INTO t1 VALUES(3, 4);
           46  +  UPDATE t1 SET a = a+1;
           47  +  DELETE FROM t1;
           48  +} {6}
           49  +do_tc_test 1.1.2 {
           50  +  DELETE FROM t1
           51  +} {6}
           52  +
           53  +do_tc_test 1.1.3 {
           54  +  WITH data(a,b) AS (
           55  +      SELECT 0, 0 UNION ALL SELECT a+1, b+1 FROM data WHERE a<99
           56  +  )
           57  +  INSERT INTO t1 SELECT * FROM data;
           58  +} {106}
           59  +
           60  +do_tc_test 1.1.4 {
           61  +  INSERT INTO t2 SELECT * FROM t1 WHERE a<50;
           62  +  UPDATE t2 SET y=y+1;
           63  +} {206}
           64  +
           65  +do_tc_test 1.1.5 {
           66  +  DELETE FROM t2 WHERE y<=25
           67  +} {231}
           68  +
           69  +do_execsql_test 1.2.1 {
           70  +  DELETE FROM t1;
           71  +  DELETE FROM t2;
           72  +}
           73  +sqlite3 db test.db     ; # To reset total_changes
           74  +do_tc_test 1.2.2 {
           75  +  CREATE TABLE log(detail);
           76  +  CREATE TRIGGER t1_after_insert AFTER INSERT ON t1 BEGIN 
           77  +    INSERT INTO log VALUES('inserted into t1');
           78  +  END;
           79  +
           80  +  CREATE TRIGGER t1_before_delete BEFORE DELETE ON t1 BEGIN 
           81  +    INSERT INTO log VALUES('deleting from t1');
           82  +    INSERT INTO log VALUES('here we go!');
           83  +  END;
           84  +
           85  +  CREATE TRIGGER t1_after_update AFTER UPDATE ON t1 BEGIN 
           86  +    INSERT INTO log VALUES('update');
           87  +    DELETE FROM log;
           88  +  END;
           89  +
           90  +  INSERT INTO t1 VALUES('a', 'b');   -- 1 + 1
           91  +  UPDATE t1 SET b='c';               -- 1 + 1 + 2
           92  +  DELETE FROM t1;                    -- 1 + 1 + 1
           93  +} {9}
           94  +
           95  +#--------------------------------------------------------------------------
           96  +# EVIDENCE-OF: R-61766-15253 Executing any other type of SQL statement
           97  +# does not affect the value returned by sqlite3_total_changes().
           98  +do_tc_test 2.1 {
           99  +  INSERT INTO t1 VALUES(1, 2), (3, 4);
          100  +  INSERT INTO t2 VALUES(1, 2), (3, 4);
          101  +} {15}
          102  +do_tc_test 2.2 {
          103  +  SELECT count(*) FROM t1;
          104  +} {2 15}
          105  +do_tc_test 2.3 {
          106  +  CREATE TABLE t4(a, b);
          107  +  ALTER TABLE t4 ADD COLUMN c;
          108  +  CREATE INDEX i4 ON t4(c);
          109  +  ALTER TABLE t4 RENAME TO t5;
          110  +  ANALYZE;
          111  +  BEGIN;
          112  +  DROP TABLE t2;
          113  +  ROLLBACK;
          114  +  VACUUM;
          115  +} {15}
          116  +
          117  +
          118  +#--------------------------------------------------------------------------
          119  +# EVIDENCE-OF: R-36043-10590 Changes made as part of foreign key
          120  +# actions are included in the count, but those made as part of REPLACE
          121  +# constraint resolution are not.
          122  +#
          123  +#   3.1.*: foreign key actions
          124  +#   3.2.*: REPLACE constraints.
          125  +#
          126  +sqlite3 db test.db     ; # To reset total_changes
          127  +do_tc_test 3.1.1 {
          128  +  CREATE TABLE p1(c PRIMARY KEY, d);
          129  +  CREATE TABLE c1(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE SET NULL);
          130  +  CREATE TABLE c2(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE CASCADE);
          131  +  CREATE TABLE c3(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE SET DEFAULT);
          132  +
          133  +  INSERT INTO p1 VALUES(1, 'one');
          134  +  INSERT INTO p1 VALUES(2, 'two');
          135  +  INSERT INTO p1 VALUES(3, 'three');
          136  +  INSERT INTO p1 VALUES(4, 'four');
          137  +
          138  +  INSERT INTO c1 VALUES(1, 'i');
          139  +  INSERT INTO c2 VALUES(2, 'ii');
          140  +  INSERT INTO c3 VALUES(3, 'iii');
          141  +  PRAGMA foreign_keys = ON;
          142  +} {7}
          143  +
          144  +do_tc_test 3.1.2 { DELETE FROM p1 WHERE c=1; } {9}
          145  +do_tc_test 3.1.3 { DELETE FROM p1 WHERE c=2; } {11}
          146  +do_tc_test 3.1.4 { DELETE FROM p1 WHERE c=3; } {13}
          147  +do_tc_test 3.1.5 { DELETE FROM p1 WHERE c=4; } {14}  ; # only 1 this time.
          148  +
          149  +sqlite3 db test.db     ; # To reset total_changes
          150  +do_tc_test 3.1.6 {
          151  +  DROP TABLE c1;
          152  +  DROP TABLE c2;
          153  +  DROP TABLE c3;
          154  +  CREATE TABLE c1(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE SET NULL);
          155  +  CREATE TABLE c2(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE CASCADE);
          156  +  CREATE TABLE c3(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE SET DEFAULT);
          157  +
          158  +  INSERT INTO p1 VALUES(1, 'one');
          159  +  INSERT INTO p1 VALUES(2, 'two');
          160  +  INSERT INTO p1 VALUES(3, 'three');
          161  +  INSERT INTO p1 VALUES(4, 'four');
          162  +
          163  +  INSERT INTO c1 VALUES(1, 'i');
          164  +  INSERT INTO c2 VALUES(2, 'ii');
          165  +  INSERT INTO c3 VALUES(3, 'iii');
          166  +  PRAGMA foreign_keys = ON;
          167  +} {7}
          168  +
          169  +do_tc_test 3.1.7  { UPDATE p1 SET c=c+4 WHERE c=1; } {9}
          170  +do_tc_test 3.1.8  { UPDATE p1 SET c=c+4 WHERE c=2; } {11}
          171  +do_tc_test 3.1.9  { UPDATE p1 SET c=c+4 WHERE c=3; } {13}
          172  +do_tc_test 3.1.10 { UPDATE p1 SET c=c+4 WHERE c=4; } {14}  ; # only 1 this time.
          173  +
          174  +sqlite3 db test.db     ; # To reset total_changes
          175  +do_tc_test 3.2.1 {
          176  +  CREATE TABLE t3(a UNIQUE, b UNIQUE);
          177  +  INSERT INTO t3 VALUES('one', 'one');
          178  +  INSERT INTO t3 VALUES('two', 'two');
          179  +  INSERT OR REPLACE INTO t3 VALUES('one', 'two');
          180  +} {3}
          181  +
          182  +do_tc_test 3.2.2 {
          183  +  INSERT INTO t3 VALUES('three', 'one');
          184  +  UPDATE OR REPLACE t3 SET b='two' WHERE b='one';
          185  +  SELECT * FROM t3;
          186  +} {three two 5}
          187  +
          188  +#--------------------------------------------------------------------------
          189  +# EVIDENCE-OF: R-54872-08741 Changes to a view that are intercepted by
          190  +# INSTEAD OF triggers are not counted.
          191  +#
          192  +sqlite3 db test.db     ; # To reset total_changes
          193  +do_tc_test 4.1 {
          194  +  CREATE TABLE t6(x);
          195  +  CREATE VIEW v1 AS SELECT * FROM t6;
          196  +  CREATE TRIGGER v1_tr1 INSTEAD OF INSERT ON v1 BEGIN
          197  +    SELECT 'no-op';
          198  +  END;
          199  +
          200  +  INSERT INTO v1 VALUES('a');
          201  +  INSERT INTO v1 VALUES('b');
          202  +} {0}
          203  +do_tc_test 4.2 {
          204  +  CREATE TRIGGER v1_tr2 INSTEAD OF INSERT ON v1 BEGIN
          205  +    INSERT INTO t6 VALUES(new.x);
          206  +  END;
          207  +
          208  +  INSERT INTO v1 VALUES('c');
          209  +  INSERT INTO v1 VALUES('d');
          210  +} {2}
          211  +
          212  +
          213  +finish_test