gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/laststmtchanges.test (about) 1 # 2 # The author disclaims copyright to this source code. In place of 3 # a legal notice, here is a blessing: 4 # 5 # May you do good and not evil. 6 # May you find forgiveness for yourself and forgive others. 7 # May you share freely, never taking more than you give. 8 # 9 #*********************************************************************** 10 # 11 # Tests to make sure that values returned by changes() and total_changes() 12 # are updated properly, especially inside triggers 13 # 14 # Note 1: changes() remains constant within a statement and only updates 15 # once the statement is finished (triggers count as part of 16 # statement). 17 # Note 2: changes() is changed within the context of a trigger much like 18 # last_insert_rowid() (see lastinsert.test), but is restored once 19 # the trigger exits. 20 # Note 3: changes() is not changed by a change to a view (since everything 21 # is done within instead of trigger context). 22 # 23 # $Id: laststmtchanges.test,v 1.7 2008/10/27 13:59:34 danielk1977 Exp $ 24 25 set testdir [file dirname $argv0] 26 source $testdir/tester.tcl 27 28 # ---------------------------------------------------------------------------- 29 # 1.x - basic tests (no triggers) 30 31 # changes() set properly after insert 32 do_test laststmtchanges-1.1 { 33 catchsql { 34 create table t0 (x); 35 insert into t0 values (1); 36 insert into t0 values (1); 37 insert into t0 values (2); 38 insert into t0 values (2); 39 insert into t0 values (1); 40 insert into t0 values (1); 41 insert into t0 values (1); 42 insert into t0 values (2); 43 select changes(), total_changes(); 44 } 45 } {0 {1 8}} 46 47 # changes() set properly after update 48 do_test laststmtchanges-1.2 { 49 catchsql { 50 update t0 set x=3 where x=1; 51 select changes(), total_changes(); 52 } 53 } {0 {5 13}} 54 55 # There was some goofy change-counting logic in sqlite3_exec() that 56 # appears to have been left over from SQLite version 2. This test 57 # makes sure it has been removed. 58 # 59 do_test laststmtchanges-1.2.1 { 60 db cache flush 61 sqlite3_exec_printf db {update t0 set x=4 where x=3; select 1;} {} 62 execsql {select changes()} 63 } {5} 64 65 # changes() unchanged within an update statement 66 do_test laststmtchanges-1.3 { 67 execsql {update t0 set x=3 where x=4} 68 catchsql { 69 update t0 set x=x+changes() where x=3; 70 select count() from t0 where x=8; 71 } 72 } {0 5} 73 74 # changes() set properly after update on table where no rows changed 75 do_test laststmtchanges-1.4 { 76 catchsql { 77 update t0 set x=77 where x=88; 78 select changes(); 79 } 80 } {0 0} 81 82 # changes() set properly after delete from table 83 do_test laststmtchanges-1.5 { 84 catchsql { 85 delete from t0 where x=2; 86 select changes(); 87 } 88 } {0 3} 89 90 # All remaining tests involve triggers. Skip them if triggers are not 91 # supported in this build. 92 # 93 ifcapable {!trigger} { 94 finish_test 95 return 96 } 97 98 99 # ---------------------------------------------------------------------------- 100 # 2.x - tests with after insert trigger 101 102 # changes() changed properly after insert into table containing after trigger 103 do_test laststmtchanges-2.1 { 104 set ::tc [db total_changes] 105 catchsql { 106 create table t1 (k integer primary key); 107 create table t2 (k integer primary key, v1, v2); 108 create trigger r1 after insert on t1 for each row begin 109 insert into t2 values (NULL, changes(), NULL); 110 update t0 set x=x; 111 update t2 set v2=changes(); 112 end; 113 insert into t1 values (77); 114 select changes(); 115 } 116 } {0 1} 117 118 # changes() unchanged upon entry into after insert trigger 119 do_test laststmtchanges-2.2 { 120 catchsql { 121 select v1 from t2; 122 } 123 } {0 3} 124 125 # changes() changed properly by update within context of after insert trigger 126 do_test laststmtchanges-2.3 { 127 catchsql { 128 select v2 from t2; 129 } 130 } {0 5} 131 132 # Total changes caused by firing the trigger above: 133 # 134 # 1 from "insert into t1 values(77)" + 135 # 1 from "insert into t2 values (NULL, changes(), NULL);" + 136 # 5 from "update t0 set x=x;" + 137 # 1 from "update t2 set v2=changes();" 138 # 139 do_test laststmtchanges-2.4 { 140 expr [db total_changes] - $::tc 141 } {8} 142 143 # ---------------------------------------------------------------------------- 144 # 3.x - tests with after update trigger 145 146 # changes() changed properly after update into table containing after trigger 147 do_test laststmtchanges-3.1 { 148 catchsql { 149 drop trigger r1; 150 delete from t2; delete from t2; 151 create trigger r1 after update on t1 for each row begin 152 insert into t2 values (NULL, changes(), NULL); 153 delete from t0 where oid=1 or oid=2; 154 update t2 set v2=changes(); 155 end; 156 update t1 set k=k; 157 select changes(); 158 } 159 } {0 1} 160 161 # changes() unchanged upon entry into after update trigger 162 do_test laststmtchanges-3.2 { 163 catchsql { 164 select v1 from t2; 165 } 166 } {0 0} 167 168 # changes() changed properly by delete within context of after update trigger 169 do_test laststmtchanges-3.3 { 170 catchsql { 171 select v2 from t2; 172 } 173 } {0 2} 174 175 # ---------------------------------------------------------------------------- 176 # 4.x - tests with before delete trigger 177 178 # changes() changed properly on delete from table containing before trigger 179 do_test laststmtchanges-4.1 { 180 catchsql { 181 drop trigger r1; 182 delete from t2; delete from t2; 183 create trigger r1 before delete on t1 for each row begin 184 insert into t2 values (NULL, changes(), NULL); 185 insert into t0 values (5); 186 update t2 set v2=changes(); 187 end; 188 delete from t1; 189 select changes(); 190 } 191 } {0 1} 192 193 # changes() unchanged upon entry into before delete trigger 194 do_test laststmtchanges-4.2 { 195 catchsql { 196 select v1 from t2; 197 } 198 } {0 0} 199 200 # changes() changed properly by insert within context of before delete trigger 201 do_test laststmtchanges-4.3 { 202 catchsql { 203 select v2 from t2; 204 } 205 } {0 1} 206 207 # ---------------------------------------------------------------------------- 208 # 5.x - complex tests with temporary tables and nested instead of triggers 209 # These tests cannot run if the library does not have view support enabled. 210 211 ifcapable view&&tempdb { 212 213 do_test laststmtchanges-5.1 { 214 catchsql { 215 drop table t0; drop table t1; drop table t2; 216 create temp table t0(x); 217 create temp table t1 (k integer primary key); 218 create temp table t2 (k integer primary key); 219 create temp view v1 as select * from t1; 220 create temp view v2 as select * from t2; 221 create temp table n1 (k integer primary key, n); 222 create temp table n2 (k integer primary key, n); 223 insert into t0 values (1); 224 insert into t0 values (2); 225 insert into t0 values (1); 226 insert into t0 values (1); 227 insert into t0 values (1); 228 insert into t0 values (2); 229 insert into t0 values (2); 230 insert into t0 values (1); 231 create temp trigger r1 instead of insert on v1 for each row begin 232 insert into n1 values (NULL, changes()); 233 update t0 set x=x*10 where x=1; 234 insert into n1 values (NULL, changes()); 235 insert into t1 values (NEW.k); 236 insert into n1 values (NULL, changes()); 237 update t0 set x=x*10 where x=0; 238 insert into v2 values (100+NEW.k); 239 insert into n1 values (NULL, changes()); 240 end; 241 create temp trigger r2 instead of insert on v2 for each row begin 242 insert into n2 values (NULL, changes()); 243 insert into t2 values (1000+NEW.k); 244 insert into n2 values (NULL, changes()); 245 update t0 set x=x*100 where x=0; 246 insert into n2 values (NULL, changes()); 247 delete from t0 where x=2; 248 insert into n2 values (NULL, changes()); 249 end; 250 insert into t1 values (77); 251 select changes(); 252 } 253 } {0 1} 254 255 do_test laststmtchanges-5.2 { 256 catchsql { 257 delete from t1 where k=88; 258 select changes(); 259 } 260 } {0 0} 261 262 do_test laststmtchanges-5.3 { 263 catchsql { 264 insert into v1 values (5); 265 select changes(); 266 } 267 } {0 0} 268 269 do_test laststmtchanges-5.4 { 270 catchsql { 271 select n from n1; 272 } 273 } {0 {0 5 1 0}} 274 275 do_test laststmtchanges-5.5 { 276 catchsql { 277 select n from n2; 278 } 279 } {0 {0 1 0 3}} 280 281 } ;# ifcapable view 282 283 284 # ---------------------------------------------------------------------------- 285 # 6.x - Test "DELETE FROM <table>" in the absence of triggers 286 # 287 do_test laststmtchanges-6.1 { 288 execsql { 289 CREATE TABLE t3(a, b, c); 290 INSERT INTO t3 VALUES(1, 2, 3); 291 INSERT INTO t3 VALUES(4, 5, 6); 292 } 293 } {} 294 do_test laststmtchanges-6.2 { 295 execsql { 296 BEGIN; 297 DELETE FROM t3; 298 SELECT changes(); 299 } 300 } {2} 301 do_test laststmtchanges-6.3 { 302 execsql { 303 ROLLBACK; 304 BEGIN; 305 DELETE FROM t3 WHERE a IS NOT NULL; 306 SELECT changes(); 307 } 308 } {2} 309 do_test laststmtchanges-6.4 { 310 execsql { 311 ROLLBACK; 312 CREATE INDEX t3_i1 ON t3(a); 313 BEGIN; 314 DELETE FROM t3; 315 SELECT changes(); 316 } 317 } {2} 318 do_test laststmtchanges-6.5 { 319 execsql { ROLLBACK } 320 set nTotalChange [execsql {SELECT total_changes()}] 321 expr 0 322 } {0} 323 do_test laststmtchanges-6.6 { 324 execsql { 325 SELECT total_changes(); 326 DELETE FROM t3; 327 SELECT total_changes(); 328 } 329 } [list $nTotalChange [expr $nTotalChange+2]] 330 331 finish_test