os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/laststmtchanges.test
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/laststmtchanges.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,281 @@
1.4 +# The author disclaims copyright to this source code. In place of
1.5 +# a legal notice, here is a blessing:
1.6 +#
1.7 +# May you do good and not evil.
1.8 +# May you find forgiveness for yourself and forgive others.
1.9 +# May you share freely, never taking more than you give.
1.10 +#
1.11 +#***********************************************************************
1.12 +#
1.13 +# Tests to make sure that values returned by changes() and total_changes()
1.14 +# are updated properly, especially inside triggers
1.15 +#
1.16 +# Note 1: changes() remains constant within a statement and only updates
1.17 +# once the statement is finished (triggers count as part of
1.18 +# statement).
1.19 +# Note 2: changes() is changed within the context of a trigger much like
1.20 +# last_insert_rowid() (see lastinsert.test), but is restored once
1.21 +# the trigger exits.
1.22 +# Note 3: changes() is not changed by a change to a view (since everything
1.23 +# is done within instead of trigger context).
1.24 +#
1.25 +
1.26 +set testdir [file dirname $argv0]
1.27 +source $testdir/tester.tcl
1.28 +
1.29 +# ----------------------------------------------------------------------------
1.30 +# 1.x - basic tests (no triggers)
1.31 +
1.32 +# changes() set properly after insert
1.33 +do_test laststmtchanges-1.1 {
1.34 + catchsql {
1.35 + create table t0 (x);
1.36 + insert into t0 values (1);
1.37 + insert into t0 values (1);
1.38 + insert into t0 values (2);
1.39 + insert into t0 values (2);
1.40 + insert into t0 values (1);
1.41 + insert into t0 values (1);
1.42 + insert into t0 values (1);
1.43 + insert into t0 values (2);
1.44 + select changes(), total_changes();
1.45 + }
1.46 +} {0 {1 8}}
1.47 +
1.48 +# changes() set properly after update
1.49 +do_test laststmtchanges-1.2 {
1.50 + catchsql {
1.51 + update t0 set x=3 where x=1;
1.52 + select changes(), total_changes();
1.53 + }
1.54 +} {0 {5 13}}
1.55 +
1.56 +# There was some goofy change-counting logic in sqlite3_exec() that
1.57 +# appears to have been left over from SQLite version 2. This test
1.58 +# makes sure it has been removed.
1.59 +#
1.60 +do_test laststmtchanges-1.2.1 {
1.61 + db cache flush
1.62 + sqlite3_exec_printf db {update t0 set x=4 where x=3; select 1;} {}
1.63 + execsql {select changes()}
1.64 +} {5}
1.65 +
1.66 +# changes() unchanged within an update statement
1.67 +do_test laststmtchanges-1.3 {
1.68 + execsql {update t0 set x=3 where x=4}
1.69 + catchsql {
1.70 + update t0 set x=x+changes() where x=3;
1.71 + select count() from t0 where x=8;
1.72 + }
1.73 +} {0 5}
1.74 +
1.75 +# changes() set properly after update on table where no rows changed
1.76 +do_test laststmtchanges-1.4 {
1.77 + catchsql {
1.78 + update t0 set x=77 where x=88;
1.79 + select changes();
1.80 + }
1.81 +} {0 0}
1.82 +
1.83 +# changes() set properly after delete from table
1.84 +do_test laststmtchanges-1.5 {
1.85 + catchsql {
1.86 + delete from t0 where x=2;
1.87 + select changes();
1.88 + }
1.89 +} {0 3}
1.90 +
1.91 +# All remaining tests involve triggers. Skip them if triggers are not
1.92 +# supported in this build.
1.93 +#
1.94 +ifcapable {!trigger} {
1.95 + finish_test
1.96 + return
1.97 +}
1.98 +
1.99 +
1.100 +# ----------------------------------------------------------------------------
1.101 +# 2.x - tests with after insert trigger
1.102 +
1.103 +# changes() changed properly after insert into table containing after trigger
1.104 +do_test laststmtchanges-2.1 {
1.105 + set ::tc [db total_changes]
1.106 + catchsql {
1.107 + create table t1 (k integer primary key);
1.108 + create table t2 (k integer primary key, v1, v2);
1.109 + create trigger r1 after insert on t1 for each row begin
1.110 + insert into t2 values (NULL, changes(), NULL);
1.111 + update t0 set x=x;
1.112 + update t2 set v2=changes();
1.113 + end;
1.114 + insert into t1 values (77);
1.115 + select changes();
1.116 + }
1.117 +} {0 1}
1.118 +
1.119 +# changes() unchanged upon entry into after insert trigger
1.120 +do_test laststmtchanges-2.2 {
1.121 + catchsql {
1.122 + select v1 from t2;
1.123 + }
1.124 +} {0 3}
1.125 +
1.126 +# changes() changed properly by update within context of after insert trigger
1.127 +do_test laststmtchanges-2.3 {
1.128 + catchsql {
1.129 + select v2 from t2;
1.130 + }
1.131 +} {0 5}
1.132 +
1.133 +# Total changes caused by firing the trigger above:
1.134 +#
1.135 +# 1 from "insert into t1 values(77)" +
1.136 +# 1 from "insert into t2 values (NULL, changes(), NULL);" +
1.137 +# 5 from "update t0 set x=x;" +
1.138 +# 1 from "update t2 set v2=changes();"
1.139 +#
1.140 +do_test laststmtchanges-2.4 {
1.141 + expr [db total_changes] - $::tc
1.142 +} {8}
1.143 +
1.144 +# ----------------------------------------------------------------------------
1.145 +# 3.x - tests with after update trigger
1.146 +
1.147 +# changes() changed properly after update into table containing after trigger
1.148 +do_test laststmtchanges-3.1 {
1.149 + catchsql {
1.150 + drop trigger r1;
1.151 + delete from t2; delete from t2;
1.152 + create trigger r1 after update on t1 for each row begin
1.153 + insert into t2 values (NULL, changes(), NULL);
1.154 + delete from t0 where oid=1 or oid=2;
1.155 + update t2 set v2=changes();
1.156 + end;
1.157 + update t1 set k=k;
1.158 + select changes();
1.159 + }
1.160 +} {0 1}
1.161 +
1.162 +# changes() unchanged upon entry into after update trigger
1.163 +do_test laststmtchanges-3.2 {
1.164 + catchsql {
1.165 + select v1 from t2;
1.166 + }
1.167 +} {0 0}
1.168 +
1.169 +# changes() changed properly by delete within context of after update trigger
1.170 +do_test laststmtchanges-3.3 {
1.171 + catchsql {
1.172 + select v2 from t2;
1.173 + }
1.174 +} {0 2}
1.175 +
1.176 +# ----------------------------------------------------------------------------
1.177 +# 4.x - tests with before delete trigger
1.178 +
1.179 +# changes() changed properly on delete from table containing before trigger
1.180 +do_test laststmtchanges-4.1 {
1.181 + catchsql {
1.182 + drop trigger r1;
1.183 + delete from t2; delete from t2;
1.184 + create trigger r1 before delete on t1 for each row begin
1.185 + insert into t2 values (NULL, changes(), NULL);
1.186 + insert into t0 values (5);
1.187 + update t2 set v2=changes();
1.188 + end;
1.189 + delete from t1;
1.190 + select changes();
1.191 + }
1.192 +} {0 1}
1.193 +
1.194 +# changes() unchanged upon entry into before delete trigger
1.195 +do_test laststmtchanges-4.2 {
1.196 + catchsql {
1.197 + select v1 from t2;
1.198 + }
1.199 +} {0 0}
1.200 +
1.201 +# changes() changed properly by insert within context of before delete trigger
1.202 +do_test laststmtchanges-4.3 {
1.203 + catchsql {
1.204 + select v2 from t2;
1.205 + }
1.206 +} {0 1}
1.207 +
1.208 +# ----------------------------------------------------------------------------
1.209 +# 5.x - complex tests with temporary tables and nested instead of triggers
1.210 +# These tests cannot run if the library does not have view support enabled.
1.211 +
1.212 +ifcapable view&&tempdb {
1.213 +
1.214 +do_test laststmtchanges-5.1 {
1.215 + catchsql {
1.216 + drop table t0; drop table t1; drop table t2;
1.217 + create temp table t0(x);
1.218 + create temp table t1 (k integer primary key);
1.219 + create temp table t2 (k integer primary key);
1.220 + create temp view v1 as select * from t1;
1.221 + create temp view v2 as select * from t2;
1.222 + create temp table n1 (k integer primary key, n);
1.223 + create temp table n2 (k integer primary key, n);
1.224 + insert into t0 values (1);
1.225 + insert into t0 values (2);
1.226 + insert into t0 values (1);
1.227 + insert into t0 values (1);
1.228 + insert into t0 values (1);
1.229 + insert into t0 values (2);
1.230 + insert into t0 values (2);
1.231 + insert into t0 values (1);
1.232 + create temp trigger r1 instead of insert on v1 for each row begin
1.233 + insert into n1 values (NULL, changes());
1.234 + update t0 set x=x*10 where x=1;
1.235 + insert into n1 values (NULL, changes());
1.236 + insert into t1 values (NEW.k);
1.237 + insert into n1 values (NULL, changes());
1.238 + update t0 set x=x*10 where x=0;
1.239 + insert into v2 values (100+NEW.k);
1.240 + insert into n1 values (NULL, changes());
1.241 + end;
1.242 + create temp trigger r2 instead of insert on v2 for each row begin
1.243 + insert into n2 values (NULL, changes());
1.244 + insert into t2 values (1000+NEW.k);
1.245 + insert into n2 values (NULL, changes());
1.246 + update t0 set x=x*100 where x=0;
1.247 + insert into n2 values (NULL, changes());
1.248 + delete from t0 where x=2;
1.249 + insert into n2 values (NULL, changes());
1.250 + end;
1.251 + insert into t1 values (77);
1.252 + select changes();
1.253 + }
1.254 +} {0 1}
1.255 +
1.256 +do_test laststmtchanges-5.2 {
1.257 + catchsql {
1.258 + delete from t1 where k=88;
1.259 + select changes();
1.260 + }
1.261 +} {0 0}
1.262 +
1.263 +do_test laststmtchanges-5.3 {
1.264 + catchsql {
1.265 + insert into v1 values (5);
1.266 + select changes();
1.267 + }
1.268 +} {0 0}
1.269 +
1.270 +do_test laststmtchanges-5.4 {
1.271 + catchsql {
1.272 + select n from n1;
1.273 + }
1.274 +} {0 {0 5 1 0}}
1.275 +
1.276 +do_test laststmtchanges-5.5 {
1.277 + catchsql {
1.278 + select n from n2;
1.279 + }
1.280 +} {0 {0 1 0 3}}
1.281 +
1.282 +} ;# ifcapable view
1.283 +
1.284 +finish_test