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-38914-26427 The total_changes() function returns the
000036 # number of row changes caused by INSERT, UPDATE or DELETE statements
000037 # since the current database connection was opened.
000038 #
000039 # 1.1.*: different types of I/U/D statements,
000040 # 1.2.*: trigger programs.
000041 #
000042 do_tc_test 1.1.1 {
000043 INSERT INTO t1 VALUES(1, 2);
000044 INSERT INTO t1 VALUES(3, 4);
000045 UPDATE t1 SET a = a+1;
000046 DELETE FROM t1;
000047 } {6}
000048 do_tc_test 1.1.2 {
000049 DELETE FROM t1
000050 } {6}
000051
000052 do_tc_test 1.1.3 {
000053 WITH data(a,b) AS (
000054 SELECT 0, 0 UNION ALL SELECT a+1, b+1 FROM data WHERE a<99
000055 )
000056 INSERT INTO t1 SELECT * FROM data;
000057 } {106}
000058
000059 do_tc_test 1.1.4 {
000060 INSERT INTO t2 SELECT * FROM t1 WHERE a<50;
000061 UPDATE t2 SET y=y+1;
000062 } {206}
000063
000064 do_tc_test 1.1.5 {
000065 DELETE FROM t2 WHERE y<=25
000066 } {231}
000067
000068 do_execsql_test 1.2.1 {
000069 DELETE FROM t1;
000070 DELETE FROM t2;
000071 }
000072 sqlite3 db test.db ; # To reset total_changes
000073 do_tc_test 1.2.2 {
000074 CREATE TABLE log(detail);
000075 CREATE TRIGGER t1_after_insert AFTER INSERT ON t1 BEGIN
000076 INSERT INTO log VALUES('inserted into t1');
000077 END;
000078
000079 CREATE TRIGGER t1_before_delete BEFORE DELETE ON t1 BEGIN
000080 INSERT INTO log VALUES('deleting from t1');
000081 INSERT INTO log VALUES('here we go!');
000082 END;
000083
000084 CREATE TRIGGER t1_after_update AFTER UPDATE ON t1 BEGIN
000085 INSERT INTO log VALUES('update');
000086 DELETE FROM log;
000087 END;
000088
000089 INSERT INTO t1 VALUES('a', 'b'); -- 1 + 1
000090 UPDATE t1 SET b='c'; -- 1 + 1 + 2
000091 DELETE FROM t1; -- 1 + 1 + 1
000092 } {9}
000093
000094 #--------------------------------------------------------------------------
000095 # EVIDENCE-OF: R-61766-15253 Executing any other type of SQL statement
000096 # does not affect the value returned by sqlite3_total_changes().
000097 ifcapable altertable {
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 #--------------------------------------------------------------------------
000120 # EVIDENCE-OF: R-36043-10590 Changes made as part of foreign key
000121 # actions are included in the count, but those made as part of REPLACE
000122 # constraint resolution are not.
000123 #
000124 # 3.1.*: foreign key actions
000125 # 3.2.*: REPLACE constraints.
000126 #
000127 sqlite3 db test.db ; # To reset total_changes
000128 do_tc_test 3.1.1 {
000129 CREATE TABLE p1(c PRIMARY KEY, d);
000130 CREATE TABLE c1(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE SET NULL);
000131 CREATE TABLE c2(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE CASCADE);
000132 CREATE TABLE c3(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE SET DEFAULT);
000133
000134 INSERT INTO p1 VALUES(1, 'one');
000135 INSERT INTO p1 VALUES(2, 'two');
000136 INSERT INTO p1 VALUES(3, 'three');
000137 INSERT INTO p1 VALUES(4, 'four');
000138
000139 INSERT INTO c1 VALUES(1, 'i');
000140 INSERT INTO c2 VALUES(2, 'ii');
000141 INSERT INTO c3 VALUES(3, 'iii');
000142 PRAGMA foreign_keys = ON;
000143 } {7}
000144
000145 do_tc_test 3.1.2 { DELETE FROM p1 WHERE c=1; } {9}
000146 do_tc_test 3.1.3 { DELETE FROM p1 WHERE c=2; } {11}
000147 do_tc_test 3.1.4 { DELETE FROM p1 WHERE c=3; } {13}
000148 do_tc_test 3.1.5 { DELETE FROM p1 WHERE c=4; } {14} ; # only 1 this time.
000149
000150 sqlite3 db test.db ; # To reset total_changes
000151 do_tc_test 3.1.6 {
000152 DROP TABLE c1;
000153 DROP TABLE c2;
000154 DROP TABLE c3;
000155 CREATE TABLE c1(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE SET NULL);
000156 CREATE TABLE c2(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE CASCADE);
000157 CREATE TABLE c3(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE SET DEFAULT);
000158
000159 INSERT INTO p1 VALUES(1, 'one');
000160 INSERT INTO p1 VALUES(2, 'two');
000161 INSERT INTO p1 VALUES(3, 'three');
000162 INSERT INTO p1 VALUES(4, 'four');
000163
000164 INSERT INTO c1 VALUES(1, 'i');
000165 INSERT INTO c2 VALUES(2, 'ii');
000166 INSERT INTO c3 VALUES(3, 'iii');
000167 PRAGMA foreign_keys = ON;
000168 } {7}
000169
000170 do_tc_test 3.1.7 { UPDATE p1 SET c=c+4 WHERE c=1; } {9}
000171 do_tc_test 3.1.8 { UPDATE p1 SET c=c+4 WHERE c=2; } {11}
000172 do_tc_test 3.1.9 { UPDATE p1 SET c=c+4 WHERE c=3; } {13}
000173 do_tc_test 3.1.10 { UPDATE p1 SET c=c+4 WHERE c=4; } {14} ; # only 1 this time.
000174
000175 sqlite3 db test.db ; # To reset total_changes
000176 do_tc_test 3.2.1 {
000177 CREATE TABLE t3(a UNIQUE, b UNIQUE);
000178 INSERT INTO t3 VALUES('one', 'one');
000179 INSERT INTO t3 VALUES('two', 'two');
000180 INSERT OR REPLACE INTO t3 VALUES('one', 'two');
000181 } {3}
000182
000183 do_tc_test 3.2.2 {
000184 INSERT INTO t3 VALUES('three', 'one');
000185 UPDATE OR REPLACE t3 SET b='two' WHERE b='one';
000186 SELECT * FROM t3;
000187 } {three two 5}
000188
000189 #--------------------------------------------------------------------------
000190 # EVIDENCE-OF: R-54872-08741 Changes to a view that are intercepted by
000191 # INSTEAD OF triggers are not counted.
000192 #
000193 sqlite3 db test.db ; # To reset total_changes
000194 do_tc_test 4.1 {
000195 CREATE TABLE t6(x);
000196 CREATE VIEW v1 AS SELECT * FROM t6;
000197 CREATE TRIGGER v1_tr1 INSTEAD OF INSERT ON v1 BEGIN
000198 SELECT 'no-op';
000199 END;
000200
000201 INSERT INTO v1 VALUES('a');
000202 INSERT INTO v1 VALUES('b');
000203 } {0}
000204 do_tc_test 4.2 {
000205 CREATE TRIGGER v1_tr2 INSTEAD OF INSERT ON v1 BEGIN
000206 INSERT INTO t6 VALUES(new.x);
000207 END;
000208
000209 INSERT INTO v1 VALUES('c');
000210 INSERT INTO v1 VALUES('d');
000211 } {2}
000212
000213
000214 finish_test