1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/lastinsert.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,366 @@
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 value returned by last_insert_rowid() (LIRID)
1.14 +# is updated properly, especially inside triggers
1.15 +#
1.16 +# Note 1: insert into table is now the only statement which changes LIRID
1.17 +# Note 2: upon entry into before or instead of triggers,
1.18 +# LIRID is unchanged (rather than -1)
1.19 +# Note 3: LIRID is changed within the context of a trigger,
1.20 +# but is restored once the trigger exits
1.21 +# Note 4: LIRID is not changed by an insert into a view (since everything
1.22 +# is done within instead of trigger context)
1.23 +#
1.24 +
1.25 +set testdir [file dirname $argv0]
1.26 +source $testdir/tester.tcl
1.27 +
1.28 +# ----------------------------------------------------------------------------
1.29 +# 1.x - basic tests (no triggers)
1.30 +
1.31 +# LIRID changed properly after an insert into a table
1.32 +do_test lastinsert-1.1 {
1.33 + catchsql {
1.34 + create table t1 (k integer primary key);
1.35 + insert into t1 values (1);
1.36 + insert into t1 values (NULL);
1.37 + insert into t1 values (NULL);
1.38 + select last_insert_rowid();
1.39 + }
1.40 +} {0 3}
1.41 +
1.42 +# LIRID unchanged after an update on a table
1.43 +do_test lastinsert-1.2 {
1.44 + catchsql {
1.45 + update t1 set k=4 where k=2;
1.46 + select last_insert_rowid();
1.47 + }
1.48 +} {0 3}
1.49 +
1.50 +# LIRID unchanged after a delete from a table
1.51 +do_test lastinsert-1.3 {
1.52 + catchsql {
1.53 + delete from t1 where k=4;
1.54 + select last_insert_rowid();
1.55 + }
1.56 +} {0 3}
1.57 +
1.58 +# LIRID unchanged after create table/view statements
1.59 +do_test lastinsert-1.4.1 {
1.60 + catchsql {
1.61 + create table t2 (k integer primary key, val1, val2, val3);
1.62 + select last_insert_rowid();
1.63 + }
1.64 +} {0 3}
1.65 +ifcapable view {
1.66 +do_test lastinsert-1.4.2 {
1.67 + catchsql {
1.68 + create view v as select * from t1;
1.69 + select last_insert_rowid();
1.70 + }
1.71 +} {0 3}
1.72 +} ;# ifcapable view
1.73 +
1.74 +# All remaining tests involve triggers. Skip them if triggers are not
1.75 +# supported in this build.
1.76 +#
1.77 +ifcapable {!trigger} {
1.78 + finish_test
1.79 + return
1.80 +}
1.81 +
1.82 +# ----------------------------------------------------------------------------
1.83 +# 2.x - tests with after insert trigger
1.84 +
1.85 +# LIRID changed properly after an insert into table containing an after trigger
1.86 +do_test lastinsert-2.1 {
1.87 + catchsql {
1.88 + delete from t2;
1.89 + create trigger r1 after insert on t1 for each row begin
1.90 + insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);
1.91 + update t2 set k=k+10, val2=100+last_insert_rowid();
1.92 + update t2 set val3=1000+last_insert_rowid();
1.93 + end;
1.94 + insert into t1 values (13);
1.95 + select last_insert_rowid();
1.96 + }
1.97 +} {0 13}
1.98 +
1.99 +# LIRID equals NEW.k upon entry into after insert trigger
1.100 +do_test lastinsert-2.2 {
1.101 + catchsql {
1.102 + select val1 from t2;
1.103 + }
1.104 +} {0 13}
1.105 +
1.106 +# LIRID changed properly by insert within context of after insert trigger
1.107 +do_test lastinsert-2.3 {
1.108 + catchsql {
1.109 + select val2 from t2;
1.110 + }
1.111 +} {0 126}
1.112 +
1.113 +# LIRID unchanged by update within context of after insert trigger
1.114 +do_test lastinsert-2.4 {
1.115 + catchsql {
1.116 + select val3 from t2;
1.117 + }
1.118 +} {0 1026}
1.119 +
1.120 +# ----------------------------------------------------------------------------
1.121 +# 3.x - tests with after update trigger
1.122 +
1.123 +# LIRID not changed after an update onto a table containing an after trigger
1.124 +do_test lastinsert-3.1 {
1.125 + catchsql {
1.126 + delete from t2;
1.127 + drop trigger r1;
1.128 + create trigger r1 after update on t1 for each row begin
1.129 + insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);
1.130 + update t2 set k=k+10, val2=100+last_insert_rowid();
1.131 + update t2 set val3=1000+last_insert_rowid();
1.132 + end;
1.133 + update t1 set k=14 where k=3;
1.134 + select last_insert_rowid();
1.135 + }
1.136 +} {0 13}
1.137 +
1.138 +# LIRID unchanged upon entry into after update trigger
1.139 +do_test lastinsert-3.2 {
1.140 + catchsql {
1.141 + select val1 from t2;
1.142 + }
1.143 +} {0 13}
1.144 +
1.145 +# LIRID changed properly by insert within context of after update trigger
1.146 +do_test lastinsert-3.3 {
1.147 + catchsql {
1.148 + select val2 from t2;
1.149 + }
1.150 +} {0 128}
1.151 +
1.152 +# LIRID unchanged by update within context of after update trigger
1.153 +do_test lastinsert-3.4 {
1.154 + catchsql {
1.155 + select val3 from t2;
1.156 + }
1.157 +} {0 1028}
1.158 +
1.159 +# ----------------------------------------------------------------------------
1.160 +# 4.x - tests with instead of insert trigger
1.161 +# These may not be run if either views or triggers were disabled at
1.162 +# compile-time
1.163 +
1.164 +ifcapable {view && trigger} {
1.165 +# LIRID not changed after an insert into view containing an instead of trigger
1.166 +do_test lastinsert-4.1 {
1.167 + catchsql {
1.168 + delete from t2;
1.169 + drop trigger r1;
1.170 + create trigger r1 instead of insert on v for each row begin
1.171 + insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);
1.172 + update t2 set k=k+10, val2=100+last_insert_rowid();
1.173 + update t2 set val3=1000+last_insert_rowid();
1.174 + end;
1.175 + insert into v values (15);
1.176 + select last_insert_rowid();
1.177 + }
1.178 +} {0 13}
1.179 +
1.180 +# LIRID unchanged upon entry into instead of trigger
1.181 +do_test lastinsert-4.2 {
1.182 + catchsql {
1.183 + select val1 from t2;
1.184 + }
1.185 +} {0 13}
1.186 +
1.187 +# LIRID changed properly by insert within context of instead of trigger
1.188 +do_test lastinsert-4.3 {
1.189 + catchsql {
1.190 + select val2 from t2;
1.191 + }
1.192 +} {0 130}
1.193 +
1.194 +# LIRID unchanged by update within context of instead of trigger
1.195 +do_test lastinsert-4.4 {
1.196 + catchsql {
1.197 + select val3 from t2;
1.198 + }
1.199 +} {0 1030}
1.200 +} ;# ifcapable (view && trigger)
1.201 +
1.202 +# ----------------------------------------------------------------------------
1.203 +# 5.x - tests with before delete trigger
1.204 +
1.205 +# LIRID not changed after a delete on a table containing a before trigger
1.206 +do_test lastinsert-5.1 {
1.207 + catchsql {
1.208 + drop trigger r1; -- This was not created if views are disabled.
1.209 + }
1.210 + catchsql {
1.211 + delete from t2;
1.212 + create trigger r1 before delete on t1 for each row begin
1.213 + insert into t2 values (77, last_insert_rowid(), NULL, NULL);
1.214 + update t2 set k=k+10, val2=100+last_insert_rowid();
1.215 + update t2 set val3=1000+last_insert_rowid();
1.216 + end;
1.217 + delete from t1 where k=1;
1.218 + select last_insert_rowid();
1.219 + }
1.220 +} {0 13}
1.221 +
1.222 +# LIRID unchanged upon entry into delete trigger
1.223 +do_test lastinsert-5.2 {
1.224 + catchsql {
1.225 + select val1 from t2;
1.226 + }
1.227 +} {0 13}
1.228 +
1.229 +# LIRID changed properly by insert within context of delete trigger
1.230 +do_test lastinsert-5.3 {
1.231 + catchsql {
1.232 + select val2 from t2;
1.233 + }
1.234 +} {0 177}
1.235 +
1.236 +# LIRID unchanged by update within context of delete trigger
1.237 +do_test lastinsert-5.4 {
1.238 + catchsql {
1.239 + select val3 from t2;
1.240 + }
1.241 +} {0 1077}
1.242 +
1.243 +# ----------------------------------------------------------------------------
1.244 +# 6.x - tests with instead of update trigger
1.245 +# These tests may not run if either views or triggers are disabled.
1.246 +
1.247 +ifcapable {view && trigger} {
1.248 +# LIRID not changed after an update on a view containing an instead of trigger
1.249 +do_test lastinsert-6.1 {
1.250 + catchsql {
1.251 + delete from t2;
1.252 + drop trigger r1;
1.253 + create trigger r1 instead of update on v for each row begin
1.254 + insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);
1.255 + update t2 set k=k+10, val2=100+last_insert_rowid();
1.256 + update t2 set val3=1000+last_insert_rowid();
1.257 + end;
1.258 + update v set k=16 where k=14;
1.259 + select last_insert_rowid();
1.260 + }
1.261 +} {0 13}
1.262 +
1.263 +# LIRID unchanged upon entry into instead of trigger
1.264 +do_test lastinsert-6.2 {
1.265 + catchsql {
1.266 + select val1 from t2;
1.267 + }
1.268 +} {0 13}
1.269 +
1.270 +# LIRID changed properly by insert within context of instead of trigger
1.271 +do_test lastinsert-6.3 {
1.272 + catchsql {
1.273 + select val2 from t2;
1.274 + }
1.275 +} {0 132}
1.276 +
1.277 +# LIRID unchanged by update within context of instead of trigger
1.278 +do_test lastinsert-6.4 {
1.279 + catchsql {
1.280 + select val3 from t2;
1.281 + }
1.282 +} {0 1032}
1.283 +} ;# ifcapable (view && trigger)
1.284 +
1.285 +# ----------------------------------------------------------------------------
1.286 +# 7.x - complex tests with temporary tables and nested instead of triggers
1.287 +# These do not run if views or triggers are disabled.
1.288 +
1.289 +ifcapable {trigger && view && tempdb} {
1.290 +do_test lastinsert-7.1 {
1.291 + catchsql {
1.292 + drop table t1; drop table t2; drop trigger r1;
1.293 + create temp table t1 (k integer primary key);
1.294 + create temp table t2 (k integer primary key);
1.295 + create temp view v1 as select * from t1;
1.296 + create temp view v2 as select * from t2;
1.297 + create temp table rid (k integer primary key, rin, rout);
1.298 + insert into rid values (1, NULL, NULL);
1.299 + insert into rid values (2, NULL, NULL);
1.300 + create temp trigger r1 instead of insert on v1 for each row begin
1.301 + update rid set rin=last_insert_rowid() where k=1;
1.302 + insert into t1 values (100+NEW.k);
1.303 + insert into v2 values (100+last_insert_rowid());
1.304 + update rid set rout=last_insert_rowid() where k=1;
1.305 + end;
1.306 + create temp trigger r2 instead of insert on v2 for each row begin
1.307 + update rid set rin=last_insert_rowid() where k=2;
1.308 + insert into t2 values (1000+NEW.k);
1.309 + update rid set rout=last_insert_rowid() where k=2;
1.310 + end;
1.311 + insert into t1 values (77);
1.312 + select last_insert_rowid();
1.313 + }
1.314 +} {0 77}
1.315 +
1.316 +do_test lastinsert-7.2 {
1.317 + catchsql {
1.318 + insert into v1 values (5);
1.319 + select last_insert_rowid();
1.320 + }
1.321 +} {0 77}
1.322 +
1.323 +do_test lastinsert-7.3 {
1.324 + catchsql {
1.325 + select rin from rid where k=1;
1.326 + }
1.327 +} {0 77}
1.328 +
1.329 +do_test lastinsert-7.4 {
1.330 + catchsql {
1.331 + select rout from rid where k=1;
1.332 + }
1.333 +} {0 105}
1.334 +
1.335 +do_test lastinsert-7.5 {
1.336 + catchsql {
1.337 + select rin from rid where k=2;
1.338 + }
1.339 +} {0 105}
1.340 +
1.341 +do_test lastinsert-7.6 {
1.342 + catchsql {
1.343 + select rout from rid where k=2;
1.344 + }
1.345 +} {0 1205}
1.346 +
1.347 +do_test lastinsert-8.1 {
1.348 + db close
1.349 + sqlite3 db test.db
1.350 + execsql {
1.351 + CREATE TABLE t2(x INTEGER PRIMARY KEY, y);
1.352 + CREATE TABLE t3(a, b);
1.353 + CREATE TRIGGER after_t2 AFTER INSERT ON t2 BEGIN
1.354 + INSERT INTO t3 VALUES(new.x, new.y);
1.355 + END;
1.356 + INSERT INTO t2 VALUES(5000000000, 1);
1.357 + SELECT last_insert_rowid();
1.358 + }
1.359 +} 5000000000
1.360 +
1.361 +do_test lastinsert-9.1 {
1.362 + db eval {INSERT INTO t2 VALUES(123456789012345,0)}
1.363 + db last_insert_rowid
1.364 +} {123456789012345}
1.365 +
1.366 +
1.367 +} ;# ifcapable (view && trigger)
1.368 +
1.369 +finish_test