AryaWu/sqlite

1# 2011 May 062#3# The author disclaims copyright to this source code.  In place of4# 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 13set testdir [file dirname $argv0]14source $testdir/tester.tcl15set testprefix e_totalchanges16 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#20proc do_tc_test {tn sql res} {21  uplevel [list \22    do_test $tn "concat \[execsql {$sql}\] \[db total_changes\]" $res23  ]24}25 26do_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-38914-26427 The total_changes() function returns the36# number of row changes caused by INSERT, UPDATE or DELETE statements37# since the current database connection was opened.38#39#   1.1.*: different types of I/U/D statements,40#   1.2.*: trigger programs.41#42do_tc_test 1.1.1 {43  INSERT INTO t1 VALUES(1, 2);44  INSERT INTO t1 VALUES(3, 4);45  UPDATE t1 SET a = a+1;46  DELETE FROM t1;47} {6}48do_tc_test 1.1.2 {49  DELETE FROM t150} {6}51 52do_tc_test 1.1.3 {53  WITH data(a,b) AS (54      SELECT 0, 0 UNION ALL SELECT a+1, b+1 FROM data WHERE a<9955  )56  INSERT INTO t1 SELECT * FROM data;57} {106}58 59do_tc_test 1.1.4 {60  INSERT INTO t2 SELECT * FROM t1 WHERE a<50;61  UPDATE t2 SET y=y+1;62} {206}63 64do_tc_test 1.1.5 {65  DELETE FROM t2 WHERE y<=2566} {231}67 68do_execsql_test 1.2.1 {69  DELETE FROM t1;70  DELETE FROM t2;71}72sqlite3 db test.db     ; # To reset total_changes73do_tc_test 1.2.2 {74  CREATE TABLE log(detail);75  CREATE TRIGGER t1_after_insert AFTER INSERT ON t1 BEGIN 76    INSERT INTO log VALUES('inserted into t1');77  END;78 79  CREATE TRIGGER t1_before_delete BEFORE DELETE ON t1 BEGIN 80    INSERT INTO log VALUES('deleting from t1');81    INSERT INTO log VALUES('here we go!');82  END;83 84  CREATE TRIGGER t1_after_update AFTER UPDATE ON t1 BEGIN 85    INSERT INTO log VALUES('update');86    DELETE FROM log;87  END;88 89  INSERT INTO t1 VALUES('a', 'b');   -- 1 + 190  UPDATE t1 SET b='c';               -- 1 + 1 + 291  DELETE FROM t1;                    -- 1 + 1 + 192} {9}93 94#--------------------------------------------------------------------------95# EVIDENCE-OF: R-61766-15253 Executing any other type of SQL statement96# does not affect the value returned by sqlite3_total_changes().97ifcapable altertable {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#--------------------------------------------------------------------------120# EVIDENCE-OF: R-36043-10590 Changes made as part of foreign key121# actions are included in the count, but those made as part of REPLACE122# constraint resolution are not.123#124#   3.1.*: foreign key actions125#   3.2.*: REPLACE constraints.126#127sqlite3 db test.db     ; # To reset total_changes128do_tc_test 3.1.1 {129  CREATE TABLE p1(c PRIMARY KEY, d);130  CREATE TABLE c1(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE SET NULL);131  CREATE TABLE c2(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE CASCADE);132  CREATE TABLE c3(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE SET DEFAULT);133 134  INSERT INTO p1 VALUES(1, 'one');135  INSERT INTO p1 VALUES(2, 'two');136  INSERT INTO p1 VALUES(3, 'three');137  INSERT INTO p1 VALUES(4, 'four');138 139  INSERT INTO c1 VALUES(1, 'i');140  INSERT INTO c2 VALUES(2, 'ii');141  INSERT INTO c3 VALUES(3, 'iii');142  PRAGMA foreign_keys = ON;143} {7}144 145do_tc_test 3.1.2 { DELETE FROM p1 WHERE c=1; } {9}146do_tc_test 3.1.3 { DELETE FROM p1 WHERE c=2; } {11}147do_tc_test 3.1.4 { DELETE FROM p1 WHERE c=3; } {13}148do_tc_test 3.1.5 { DELETE FROM p1 WHERE c=4; } {14}  ; # only 1 this time.149 150sqlite3 db test.db     ; # To reset total_changes151do_tc_test 3.1.6 {152  DROP TABLE c1;153  DROP TABLE c2;154  DROP TABLE c3;155  CREATE TABLE c1(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE SET NULL);156  CREATE TABLE c2(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE CASCADE);157  CREATE TABLE c3(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE SET DEFAULT);158 159  INSERT INTO p1 VALUES(1, 'one');160  INSERT INTO p1 VALUES(2, 'two');161  INSERT INTO p1 VALUES(3, 'three');162  INSERT INTO p1 VALUES(4, 'four');163 164  INSERT INTO c1 VALUES(1, 'i');165  INSERT INTO c2 VALUES(2, 'ii');166  INSERT INTO c3 VALUES(3, 'iii');167  PRAGMA foreign_keys = ON;168} {7}169 170do_tc_test 3.1.7  { UPDATE p1 SET c=c+4 WHERE c=1; } {9}171do_tc_test 3.1.8  { UPDATE p1 SET c=c+4 WHERE c=2; } {11}172do_tc_test 3.1.9  { UPDATE p1 SET c=c+4 WHERE c=3; } {13}173do_tc_test 3.1.10 { UPDATE p1 SET c=c+4 WHERE c=4; } {14}  ; # only 1 this time.174 175sqlite3 db test.db     ; # To reset total_changes176do_tc_test 3.2.1 {177  CREATE TABLE t3(a UNIQUE, b UNIQUE);178  INSERT INTO t3 VALUES('one', 'one');179  INSERT INTO t3 VALUES('two', 'two');180  INSERT OR REPLACE INTO t3 VALUES('one', 'two');181} {3}182 183do_tc_test 3.2.2 {184  INSERT INTO t3 VALUES('three', 'one');185  UPDATE OR REPLACE t3 SET b='two' WHERE b='one';186  SELECT * FROM t3;187} {three two 5}188 189#--------------------------------------------------------------------------190# EVIDENCE-OF: R-54872-08741 Changes to a view that are intercepted by191# INSTEAD OF triggers are not counted.192#193sqlite3 db test.db     ; # To reset total_changes194do_tc_test 4.1 {195  CREATE TABLE t6(x);196  CREATE VIEW v1 AS SELECT * FROM t6;197  CREATE TRIGGER v1_tr1 INSTEAD OF INSERT ON v1 BEGIN198    SELECT 'no-op';199  END;200 201  INSERT INTO v1 VALUES('a');202  INSERT INTO v1 VALUES('b');203} {0}204do_tc_test 4.2 {205  CREATE TRIGGER v1_tr2 INSTEAD OF INSERT ON v1 BEGIN206    INSERT INTO t6 VALUES(new.x);207  END;208 209  INSERT INTO v1 VALUES('c');210  INSERT INTO v1 VALUES('d');211} {2}212 213 214finish_test215