000001 # 2011 May 06
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #***********************************************************************
000011 #
000012
000013 set testdir [file dirname $argv0]
000014 source $testdir/tester.tcl
000015 set testprefix e_totalchanges
000016
000017 # Like [do_execsql_test], except it appends the value returned by
000018 # [db total_changes] to the result of executing the SQL script.
000019 #
000020 proc do_tc_test {tn sql res} {
000021 uplevel [list \
000022 do_test $tn "concat \[execsql {$sql}\] \[db total_changes\]" $res
000023 ]
000024 }
000025
000026 do_execsql_test 1.0 {
000027 CREATE TABLE t1(a, b);
000028 CREATE INDEX t1_b ON t1(b);
000029 CREATE TABLE t2(x, y, PRIMARY KEY(x, y)) WITHOUT ROWID;
000030 CREATE INDEX t2_y ON t2(y);
000031 }
000032
000033
000034 #--------------------------------------------------------------------------
000035 # EVIDENCE-OF: R-65438-26258 This function returns the total number of
000036 # rows inserted, modified or deleted by all INSERT, UPDATE or DELETE
000037 # statements completed since the database connection was opened,
000038 # including those executed as part of trigger programs.
000039 #
000040 # 1.1.*: different types of I/U/D statements,
000041 # 1.2.*: trigger programs.
000042 #
000043 do_tc_test 1.1.1 {
000044 INSERT INTO t1 VALUES(1, 2);
000045 INSERT INTO t1 VALUES(3, 4);
000046 UPDATE t1 SET a = a+1;
000047 DELETE FROM t1;
000048 } {6}
000049 do_tc_test 1.1.2 {
000050 DELETE FROM t1
000051 } {6}
000052
000053 do_tc_test 1.1.3 {
000054 WITH data(a,b) AS (
000055 SELECT 0, 0 UNION ALL SELECT a+1, b+1 FROM data WHERE a<99
000056 )
000057 INSERT INTO t1 SELECT * FROM data;
000058 } {106}
000059
000060 do_tc_test 1.1.4 {
000061 INSERT INTO t2 SELECT * FROM t1 WHERE a<50;
000062 UPDATE t2 SET y=y+1;
000063 } {206}
000064
000065 do_tc_test 1.1.5 {
000066 DELETE FROM t2 WHERE y<=25
000067 } {231}
000068
000069 do_execsql_test 1.2.1 {
000070 DELETE FROM t1;
000071 DELETE FROM t2;
000072 }
000073 sqlite3 db test.db ; # To reset total_changes
000074 do_tc_test 1.2.2 {
000075 CREATE TABLE log(detail);
000076 CREATE TRIGGER t1_after_insert AFTER INSERT ON t1 BEGIN
000077 INSERT INTO log VALUES('inserted into t1');
000078 END;
000079
000080 CREATE TRIGGER t1_before_delete BEFORE DELETE ON t1 BEGIN
000081 INSERT INTO log VALUES('deleting from t1');
000082 INSERT INTO log VALUES('here we go!');
000083 END;
000084
000085 CREATE TRIGGER t1_after_update AFTER UPDATE ON t1 BEGIN
000086 INSERT INTO log VALUES('update');
000087 DELETE FROM log;
000088 END;
000089
000090 INSERT INTO t1 VALUES('a', 'b'); -- 1 + 1
000091 UPDATE t1 SET b='c'; -- 1 + 1 + 2
000092 DELETE FROM t1; -- 1 + 1 + 1
000093 } {9}
000094
000095 #--------------------------------------------------------------------------
000096 # EVIDENCE-OF: R-61766-15253 Executing any other type of SQL statement
000097 # does not affect the value returned by sqlite3_total_changes().
000098 do_tc_test 2.1 {
000099 INSERT INTO t1 VALUES(1, 2), (3, 4);
000100 INSERT INTO t2 VALUES(1, 2), (3, 4);
000101 } {15}
000102 do_tc_test 2.2 {
000103 SELECT count(*) FROM t1;
000104 } {2 15}
000105 do_tc_test 2.3 {
000106 CREATE TABLE t4(a, b);
000107 ALTER TABLE t4 ADD COLUMN c;
000108 CREATE INDEX i4 ON t4(c);
000109 ALTER TABLE t4 RENAME TO t5;
000110 ANALYZE;
000111 BEGIN;
000112 DROP TABLE t2;
000113 ROLLBACK;
000114 VACUUM;
000115 } {15}
000116
000117
000118 #--------------------------------------------------------------------------
000119 # EVIDENCE-OF: R-36043-10590 Changes made as part of foreign key
000120 # actions are included in the count, but those made as part of REPLACE
000121 # constraint resolution are not.
000122 #
000123 # 3.1.*: foreign key actions
000124 # 3.2.*: REPLACE constraints.
000125 #
000126 sqlite3 db test.db ; # To reset total_changes
000127 do_tc_test 3.1.1 {
000128 CREATE TABLE p1(c PRIMARY KEY, d);
000129 CREATE TABLE c1(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE SET NULL);
000130 CREATE TABLE c2(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE CASCADE);
000131 CREATE TABLE c3(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE SET DEFAULT);
000132
000133 INSERT INTO p1 VALUES(1, 'one');
000134 INSERT INTO p1 VALUES(2, 'two');
000135 INSERT INTO p1 VALUES(3, 'three');
000136 INSERT INTO p1 VALUES(4, 'four');
000137
000138 INSERT INTO c1 VALUES(1, 'i');
000139 INSERT INTO c2 VALUES(2, 'ii');
000140 INSERT INTO c3 VALUES(3, 'iii');
000141 PRAGMA foreign_keys = ON;
000142 } {7}
000143
000144 do_tc_test 3.1.2 { DELETE FROM p1 WHERE c=1; } {9}
000145 do_tc_test 3.1.3 { DELETE FROM p1 WHERE c=2; } {11}
000146 do_tc_test 3.1.4 { DELETE FROM p1 WHERE c=3; } {13}
000147 do_tc_test 3.1.5 { DELETE FROM p1 WHERE c=4; } {14} ; # only 1 this time.
000148
000149 sqlite3 db test.db ; # To reset total_changes
000150 do_tc_test 3.1.6 {
000151 DROP TABLE c1;
000152 DROP TABLE c2;
000153 DROP TABLE c3;
000154 CREATE TABLE c1(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE SET NULL);
000155 CREATE TABLE c2(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE CASCADE);
000156 CREATE TABLE c3(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE SET DEFAULT);
000157
000158 INSERT INTO p1 VALUES(1, 'one');
000159 INSERT INTO p1 VALUES(2, 'two');
000160 INSERT INTO p1 VALUES(3, 'three');
000161 INSERT INTO p1 VALUES(4, 'four');
000162
000163 INSERT INTO c1 VALUES(1, 'i');
000164 INSERT INTO c2 VALUES(2, 'ii');
000165 INSERT INTO c3 VALUES(3, 'iii');
000166 PRAGMA foreign_keys = ON;
000167 } {7}
000168
000169 do_tc_test 3.1.7 { UPDATE p1 SET c=c+4 WHERE c=1; } {9}
000170 do_tc_test 3.1.8 { UPDATE p1 SET c=c+4 WHERE c=2; } {11}
000171 do_tc_test 3.1.9 { UPDATE p1 SET c=c+4 WHERE c=3; } {13}
000172 do_tc_test 3.1.10 { UPDATE p1 SET c=c+4 WHERE c=4; } {14} ; # only 1 this time.
000173
000174 sqlite3 db test.db ; # To reset total_changes
000175 do_tc_test 3.2.1 {
000176 CREATE TABLE t3(a UNIQUE, b UNIQUE);
000177 INSERT INTO t3 VALUES('one', 'one');
000178 INSERT INTO t3 VALUES('two', 'two');
000179 INSERT OR REPLACE INTO t3 VALUES('one', 'two');
000180 } {3}
000181
000182 do_tc_test 3.2.2 {
000183 INSERT INTO t3 VALUES('three', 'one');
000184 UPDATE OR REPLACE t3 SET b='two' WHERE b='one';
000185 SELECT * FROM t3;
000186 } {three two 5}
000187
000188 #--------------------------------------------------------------------------
000189 # EVIDENCE-OF: R-54872-08741 Changes to a view that are intercepted by
000190 # INSTEAD OF triggers are not counted.
000191 #
000192 sqlite3 db test.db ; # To reset total_changes
000193 do_tc_test 4.1 {
000194 CREATE TABLE t6(x);
000195 CREATE VIEW v1 AS SELECT * FROM t6;
000196 CREATE TRIGGER v1_tr1 INSTEAD OF INSERT ON v1 BEGIN
000197 SELECT 'no-op';
000198 END;
000199
000200 INSERT INTO v1 VALUES('a');
000201 INSERT INTO v1 VALUES('b');
000202 } {0}
000203 do_tc_test 4.2 {
000204 CREATE TRIGGER v1_tr2 INSTEAD OF INSERT ON v1 BEGIN
000205 INSERT INTO t6 VALUES(new.x);
000206 END;
000207
000208 INSERT INTO v1 VALUES('c');
000209 INSERT INTO v1 VALUES('d');
000210 } {2}
000211
000212
000213 finish_test