1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/hook.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,317 @@
1.4 +# 2004 Jan 14
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for TCL interface to the
1.15 +# SQLite library.
1.16 +#
1.17 +# The focus of the tests in this file is the following interface:
1.18 +#
1.19 +# sqlite_commit_hook (tests hook-1..hook-3 inclusive)
1.20 +# sqlite_update_hook (tests hook-4-*)
1.21 +# sqlite_rollback_hook (tests hook-5.*)
1.22 +#
1.23 +# $Id: hook.test,v 1.13 2008/01/19 20:11:26 drh Exp $
1.24 +
1.25 +set testdir [file dirname $argv0]
1.26 +source $testdir/tester.tcl
1.27 +
1.28 +do_test hook-1.2 {
1.29 + db commit_hook
1.30 +} {}
1.31 +
1.32 +
1.33 +do_test hook-3.1 {
1.34 + set commit_cnt 0
1.35 + proc commit_hook {} {
1.36 + incr ::commit_cnt
1.37 + return 0
1.38 + }
1.39 + db commit_hook ::commit_hook
1.40 + db commit_hook
1.41 +} {::commit_hook}
1.42 +do_test hook-3.2 {
1.43 + set commit_cnt
1.44 +} {0}
1.45 +do_test hook-3.3 {
1.46 + execsql {
1.47 + CREATE TABLE t2(a,b);
1.48 + }
1.49 + set commit_cnt
1.50 +} {1}
1.51 +do_test hook-3.4 {
1.52 + execsql {
1.53 + INSERT INTO t2 VALUES(1,2);
1.54 + INSERT INTO t2 SELECT a+1, b+1 FROM t2;
1.55 + INSERT INTO t2 SELECT a+2, b+2 FROM t2;
1.56 + }
1.57 + set commit_cnt
1.58 +} {4}
1.59 +do_test hook-3.5 {
1.60 + set commit_cnt {}
1.61 + proc commit_hook {} {
1.62 + set ::commit_cnt [execsql {SELECT * FROM t2}]
1.63 + return 0
1.64 + }
1.65 + execsql {
1.66 + INSERT INTO t2 VALUES(5,6);
1.67 + }
1.68 + set commit_cnt
1.69 +} {1 2 2 3 3 4 4 5 5 6}
1.70 +do_test hook-3.6 {
1.71 + set commit_cnt {}
1.72 + proc commit_hook {} {
1.73 + set ::commit_cnt [execsql {SELECT * FROM t2}]
1.74 + return 1
1.75 + }
1.76 + catchsql {
1.77 + INSERT INTO t2 VALUES(6,7);
1.78 + }
1.79 +} {1 {constraint failed}}
1.80 +do_test hook-3.7 {
1.81 + set ::commit_cnt
1.82 +} {1 2 2 3 3 4 4 5 5 6 6 7}
1.83 +do_test hook-3.8 {
1.84 + execsql {SELECT * FROM t2}
1.85 +} {1 2 2 3 3 4 4 5 5 6}
1.86 +
1.87 +# Test turnning off the commit hook
1.88 +#
1.89 +do_test hook-3.9 {
1.90 + db commit_hook {}
1.91 + set ::commit_cnt {}
1.92 + execsql {
1.93 + INSERT INTO t2 VALUES(7,8);
1.94 + }
1.95 + set ::commit_cnt
1.96 +} {}
1.97 +
1.98 +#----------------------------------------------------------------------------
1.99 +# Tests for the update-hook.
1.100 +#
1.101 +# 4.1.* - Very simple tests. Test that the update hook is invoked correctly
1.102 +# for INSERT, DELETE and UPDATE statements, including DELETE
1.103 +# statements with no WHERE clause.
1.104 +# 4.2.* - Check that the update-hook is invoked for rows modified by trigger
1.105 +# bodies. Also that the database name is correctly reported when
1.106 +# an attached database is modified.
1.107 +# 4.3.* - Do some sorting, grouping, compound queries, population and
1.108 +# depopulation of indices, to make sure the update-hook is not
1.109 +# invoked incorrectly.
1.110 +#
1.111 +
1.112 +# Simple tests
1.113 +do_test hook-4.1.1 {
1.114 + catchsql {
1.115 + DROP TABLE t1;
1.116 + }
1.117 + execsql {
1.118 + CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
1.119 + INSERT INTO t1 VALUES(1, 'one');
1.120 + INSERT INTO t1 VALUES(2, 'two');
1.121 + INSERT INTO t1 VALUES(3, 'three');
1.122 + }
1.123 + db update_hook [list lappend ::update_hook]
1.124 +} {}
1.125 +do_test hook-4.1.2 {
1.126 + execsql {
1.127 + INSERT INTO t1 VALUES(4, 'four');
1.128 + DELETE FROM t1 WHERE b = 'two';
1.129 + UPDATE t1 SET b = '' WHERE a = 1 OR a = 3;
1.130 + DELETE FROM t1 WHERE 1; -- Avoid the truncate optimization (for now)
1.131 + }
1.132 + set ::update_hook
1.133 +} [list \
1.134 + INSERT main t1 4 \
1.135 + DELETE main t1 2 \
1.136 + UPDATE main t1 1 \
1.137 + UPDATE main t1 3 \
1.138 + DELETE main t1 1 \
1.139 + DELETE main t1 3 \
1.140 + DELETE main t1 4 \
1.141 +]
1.142 +
1.143 +# Update hook is not invoked for changes to sqlite_master
1.144 +#
1.145 +do_test hook-4.1.3 {
1.146 + set ::update_hook {}
1.147 + execsql {
1.148 + CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN SELECT RAISE(IGNORE); END;
1.149 + }
1.150 + set ::update_hook
1.151 +} {}
1.152 +do_test hook-4.1.4 {
1.153 + set ::update_hook {}
1.154 + execsql {
1.155 + DROP TRIGGER r1;
1.156 + }
1.157 + set ::update_hook
1.158 +} {}
1.159 +
1.160 +
1.161 +set ::update_hook {}
1.162 +ifcapable trigger {
1.163 + do_test hook-4.2.1 {
1.164 + catchsql {
1.165 + DROP TABLE t2;
1.166 + }
1.167 + execsql {
1.168 + CREATE TABLE t2(c INTEGER PRIMARY KEY, d);
1.169 + CREATE TRIGGER t1_trigger AFTER INSERT ON t1 BEGIN
1.170 + INSERT INTO t2 VALUES(new.a, new.b);
1.171 + UPDATE t2 SET d = d || ' via trigger' WHERE new.a = c;
1.172 + DELETE FROM t2 WHERE new.a = c;
1.173 + END;
1.174 + }
1.175 + } {}
1.176 + do_test hook-4.2.2 {
1.177 + execsql {
1.178 + INSERT INTO t1 VALUES(1, 'one');
1.179 + INSERT INTO t1 VALUES(2, 'two');
1.180 + }
1.181 + set ::update_hook
1.182 + } [list \
1.183 + INSERT main t1 1 \
1.184 + INSERT main t2 1 \
1.185 + UPDATE main t2 1 \
1.186 + DELETE main t2 1 \
1.187 + INSERT main t1 2 \
1.188 + INSERT main t2 2 \
1.189 + UPDATE main t2 2 \
1.190 + DELETE main t2 2 \
1.191 + ]
1.192 +} else {
1.193 + execsql {
1.194 + INSERT INTO t1 VALUES(1, 'one');
1.195 + INSERT INTO t1 VALUES(2, 'two');
1.196 + }
1.197 +}
1.198 +
1.199 +# Update-hook + ATTACH
1.200 +set ::update_hook {}
1.201 +ifcapable attach {
1.202 + do_test hook-4.2.3 {
1.203 + file delete -force test2.db
1.204 + execsql {
1.205 + ATTACH 'test2.db' AS aux;
1.206 + CREATE TABLE aux.t3(a INTEGER PRIMARY KEY, b);
1.207 + INSERT INTO aux.t3 SELECT * FROM t1;
1.208 + UPDATE t3 SET b = 'two or so' WHERE a = 2;
1.209 + DELETE FROM t3 WHERE 1; -- Avoid the truncate optimization (for now)
1.210 + }
1.211 + set ::update_hook
1.212 + } [list \
1.213 + INSERT aux t3 1 \
1.214 + INSERT aux t3 2 \
1.215 + UPDATE aux t3 2 \
1.216 + DELETE aux t3 1 \
1.217 + DELETE aux t3 2 \
1.218 + ]
1.219 +}
1.220 +
1.221 +ifcapable trigger {
1.222 + execsql {
1.223 + DROP TRIGGER t1_trigger;
1.224 + }
1.225 +}
1.226 +
1.227 +# Test that other vdbe operations involving btree structures do not
1.228 +# incorrectly invoke the update-hook.
1.229 +set ::update_hook {}
1.230 +do_test hook-4.3.1 {
1.231 + execsql {
1.232 + CREATE INDEX t1_i ON t1(b);
1.233 + INSERT INTO t1 VALUES(3, 'three');
1.234 + UPDATE t1 SET b = '';
1.235 + DELETE FROM t1 WHERE a > 1;
1.236 + }
1.237 + set ::update_hook
1.238 +} [list \
1.239 + INSERT main t1 3 \
1.240 + UPDATE main t1 1 \
1.241 + UPDATE main t1 2 \
1.242 + UPDATE main t1 3 \
1.243 + DELETE main t1 2 \
1.244 + DELETE main t1 3 \
1.245 +]
1.246 +set ::update_hook {}
1.247 +ifcapable compound&&attach {
1.248 + do_test hook-4.3.2 {
1.249 + execsql {
1.250 + SELECT * FROM t1 UNION SELECT * FROM t3;
1.251 + SELECT * FROM t1 UNION ALL SELECT * FROM t3;
1.252 + SELECT * FROM t1 INTERSECT SELECT * FROM t3;
1.253 + SELECT * FROM t1 EXCEPT SELECT * FROM t3;
1.254 + SELECT * FROM t1 ORDER BY b;
1.255 + SELECT * FROM t1 GROUP BY b;
1.256 + }
1.257 + set ::update_hook
1.258 + } [list]
1.259 +}
1.260 +db update_hook {}
1.261 +#
1.262 +#----------------------------------------------------------------------------
1.263 +
1.264 +#----------------------------------------------------------------------------
1.265 +# Test the rollback-hook. The rollback-hook is a bit more complicated than
1.266 +# either the commit or update hooks because a rollback can happen
1.267 +# explicitly (an sql ROLLBACK statement) or implicitly (a constraint or
1.268 +# error condition).
1.269 +#
1.270 +# hook-5.1.* - Test explicit rollbacks.
1.271 +# hook-5.2.* - Test implicit rollbacks caused by constraint failure.
1.272 +#
1.273 +# hook-5.3.* - Test implicit rollbacks caused by IO errors.
1.274 +# hook-5.4.* - Test implicit rollbacks caused by malloc() failure.
1.275 +# hook-5.5.* - Test hot-journal rollbacks. Or should the rollback hook
1.276 +# not be called for these?
1.277 +#
1.278 +
1.279 +do_test hook-5.0 {
1.280 + # Configure the rollback hook to increment global variable
1.281 + # $::rollback_hook each time it is invoked.
1.282 + set ::rollback_hook 0
1.283 + db rollback_hook [list incr ::rollback_hook]
1.284 +} {}
1.285 +
1.286 +# Test explicit rollbacks. Not much can really go wrong here.
1.287 +#
1.288 +do_test hook-5.1.1 {
1.289 + set ::rollback_hook 0
1.290 + execsql {
1.291 + BEGIN;
1.292 + ROLLBACK;
1.293 + }
1.294 + set ::rollback_hook
1.295 +} {1}
1.296 +
1.297 +# Test implicit rollbacks caused by constraints.
1.298 +#
1.299 +do_test hook-5.2.1 {
1.300 + set ::rollback_hook 0
1.301 + catchsql {
1.302 + DROP TABLE t1;
1.303 + CREATE TABLE t1(a PRIMARY KEY, b);
1.304 + INSERT INTO t1 VALUES('one', 'I');
1.305 + INSERT INTO t1 VALUES('one', 'I');
1.306 + }
1.307 + set ::rollback_hook
1.308 +} {1}
1.309 +do_test hook-5.2.2 {
1.310 + # Check that the INSERT transaction above really was rolled back.
1.311 + execsql {
1.312 + SELECT count(*) FROM t1;
1.313 + }
1.314 +} {1}
1.315 +
1.316 +#
1.317 +# End rollback-hook testing.
1.318 +#----------------------------------------------------------------------------
1.319 +
1.320 +finish_test