1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/trans.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,944 @@
1.4 +# 2001 September 15
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 SQLite library. The
1.15 +# focus of this script is database locks.
1.16 +#
1.17 +# $Id: trans.test,v 1.38 2008/04/19 20:34:19 drh Exp $
1.18 +
1.19 +
1.20 +set testdir [file dirname $argv0]
1.21 +source $testdir/tester.tcl
1.22 +
1.23 +
1.24 +# Create several tables to work with.
1.25 +#
1.26 +do_test trans-1.0 {
1.27 + execsql {
1.28 + CREATE TABLE one(a int PRIMARY KEY, b text);
1.29 + INSERT INTO one VALUES(1,'one');
1.30 + INSERT INTO one VALUES(2,'two');
1.31 + INSERT INTO one VALUES(3,'three');
1.32 + SELECT b FROM one ORDER BY a;
1.33 + }
1.34 +} {one two three}
1.35 +integrity_check trans-1.0.1
1.36 +do_test trans-1.1 {
1.37 + execsql {
1.38 + CREATE TABLE two(a int PRIMARY KEY, b text);
1.39 + INSERT INTO two VALUES(1,'I');
1.40 + INSERT INTO two VALUES(5,'V');
1.41 + INSERT INTO two VALUES(10,'X');
1.42 + SELECT b FROM two ORDER BY a;
1.43 + }
1.44 +} {I V X}
1.45 +do_test trans-1.9 {
1.46 + sqlite3 altdb test.db
1.47 + execsql {SELECT b FROM one ORDER BY a} altdb
1.48 +} {one two three}
1.49 +do_test trans-1.10 {
1.50 + execsql {SELECT b FROM two ORDER BY a} altdb
1.51 +} {I V X}
1.52 +integrity_check trans-1.11
1.53 +
1.54 +# Basic transactions
1.55 +#
1.56 +do_test trans-2.1 {
1.57 + set v [catch {execsql {BEGIN}} msg]
1.58 + lappend v $msg
1.59 +} {0 {}}
1.60 +do_test trans-2.2 {
1.61 + set v [catch {execsql {END}} msg]
1.62 + lappend v $msg
1.63 +} {0 {}}
1.64 +do_test trans-2.3 {
1.65 + set v [catch {execsql {BEGIN TRANSACTION}} msg]
1.66 + lappend v $msg
1.67 +} {0 {}}
1.68 +do_test trans-2.4 {
1.69 + set v [catch {execsql {COMMIT TRANSACTION}} msg]
1.70 + lappend v $msg
1.71 +} {0 {}}
1.72 +do_test trans-2.5 {
1.73 + set v [catch {execsql {BEGIN TRANSACTION 'foo'}} msg]
1.74 + lappend v $msg
1.75 +} {0 {}}
1.76 +do_test trans-2.6 {
1.77 + set v [catch {execsql {ROLLBACK TRANSACTION 'foo'}} msg]
1.78 + lappend v $msg
1.79 +} {0 {}}
1.80 +do_test trans-2.10 {
1.81 + execsql {
1.82 + BEGIN;
1.83 + SELECT a FROM one ORDER BY a;
1.84 + SELECT a FROM two ORDER BY a;
1.85 + END;
1.86 + }
1.87 +} {1 2 3 1 5 10}
1.88 +integrity_check trans-2.11
1.89 +
1.90 +# Check the locking behavior
1.91 +#
1.92 +do_test trans-3.1 {
1.93 + execsql {
1.94 + BEGIN;
1.95 + UPDATE one SET a = 0 WHERE 0;
1.96 + SELECT a FROM one ORDER BY a;
1.97 + }
1.98 +} {1 2 3}
1.99 +do_test trans-3.2 {
1.100 + catchsql {
1.101 + SELECT a FROM two ORDER BY a;
1.102 + } altdb
1.103 +} {0 {1 5 10}}
1.104 +
1.105 +do_test trans-3.3 {
1.106 + catchsql {
1.107 + SELECT a FROM one ORDER BY a;
1.108 + } altdb
1.109 +} {0 {1 2 3}}
1.110 +do_test trans-3.4 {
1.111 + catchsql {
1.112 + INSERT INTO one VALUES(4,'four');
1.113 + }
1.114 +} {0 {}}
1.115 +do_test trans-3.5 {
1.116 + catchsql {
1.117 + SELECT a FROM two ORDER BY a;
1.118 + } altdb
1.119 +} {0 {1 5 10}}
1.120 +do_test trans-3.6 {
1.121 + catchsql {
1.122 + SELECT a FROM one ORDER BY a;
1.123 + } altdb
1.124 +} {0 {1 2 3}}
1.125 +do_test trans-3.7 {
1.126 + catchsql {
1.127 + INSERT INTO two VALUES(4,'IV');
1.128 + }
1.129 +} {0 {}}
1.130 +do_test trans-3.8 {
1.131 + catchsql {
1.132 + SELECT a FROM two ORDER BY a;
1.133 + } altdb
1.134 +} {0 {1 5 10}}
1.135 +do_test trans-3.9 {
1.136 + catchsql {
1.137 + SELECT a FROM one ORDER BY a;
1.138 + } altdb
1.139 +} {0 {1 2 3}}
1.140 +do_test trans-3.10 {
1.141 + execsql {END TRANSACTION}
1.142 +} {}
1.143 +
1.144 +do_test trans-3.11 {
1.145 + set v [catch {execsql {
1.146 + SELECT a FROM two ORDER BY a;
1.147 + } altdb} msg]
1.148 + lappend v $msg
1.149 +} {0 {1 4 5 10}}
1.150 +do_test trans-3.12 {
1.151 + set v [catch {execsql {
1.152 + SELECT a FROM one ORDER BY a;
1.153 + } altdb} msg]
1.154 + lappend v $msg
1.155 +} {0 {1 2 3 4}}
1.156 +do_test trans-3.13 {
1.157 + set v [catch {execsql {
1.158 + SELECT a FROM two ORDER BY a;
1.159 + } db} msg]
1.160 + lappend v $msg
1.161 +} {0 {1 4 5 10}}
1.162 +do_test trans-3.14 {
1.163 + set v [catch {execsql {
1.164 + SELECT a FROM one ORDER BY a;
1.165 + } db} msg]
1.166 + lappend v $msg
1.167 +} {0 {1 2 3 4}}
1.168 +integrity_check trans-3.15
1.169 +
1.170 +do_test trans-4.1 {
1.171 + set v [catch {execsql {
1.172 + COMMIT;
1.173 + } db} msg]
1.174 + lappend v $msg
1.175 +} {1 {cannot commit - no transaction is active}}
1.176 +do_test trans-4.2 {
1.177 + set v [catch {execsql {
1.178 + ROLLBACK;
1.179 + } db} msg]
1.180 + lappend v $msg
1.181 +} {1 {cannot rollback - no transaction is active}}
1.182 +do_test trans-4.3 {
1.183 + catchsql {
1.184 + BEGIN TRANSACTION;
1.185 + UPDATE two SET a = 0 WHERE 0;
1.186 + SELECT a FROM two ORDER BY a;
1.187 + } db
1.188 +} {0 {1 4 5 10}}
1.189 +do_test trans-4.4 {
1.190 + catchsql {
1.191 + SELECT a FROM two ORDER BY a;
1.192 + } altdb
1.193 +} {0 {1 4 5 10}}
1.194 +do_test trans-4.5 {
1.195 + catchsql {
1.196 + SELECT a FROM one ORDER BY a;
1.197 + } altdb
1.198 +} {0 {1 2 3 4}}
1.199 +do_test trans-4.6 {
1.200 + catchsql {
1.201 + BEGIN TRANSACTION;
1.202 + SELECT a FROM one ORDER BY a;
1.203 + } db
1.204 +} {1 {cannot start a transaction within a transaction}}
1.205 +do_test trans-4.7 {
1.206 + catchsql {
1.207 + SELECT a FROM two ORDER BY a;
1.208 + } altdb
1.209 +} {0 {1 4 5 10}}
1.210 +do_test trans-4.8 {
1.211 + catchsql {
1.212 + SELECT a FROM one ORDER BY a;
1.213 + } altdb
1.214 +} {0 {1 2 3 4}}
1.215 +do_test trans-4.9 {
1.216 + set v [catch {execsql {
1.217 + END TRANSACTION;
1.218 + SELECT a FROM two ORDER BY a;
1.219 + } db} msg]
1.220 + lappend v $msg
1.221 +} {0 {1 4 5 10}}
1.222 +do_test trans-4.10 {
1.223 + set v [catch {execsql {
1.224 + SELECT a FROM two ORDER BY a;
1.225 + } altdb} msg]
1.226 + lappend v $msg
1.227 +} {0 {1 4 5 10}}
1.228 +do_test trans-4.11 {
1.229 + set v [catch {execsql {
1.230 + SELECT a FROM one ORDER BY a;
1.231 + } altdb} msg]
1.232 + lappend v $msg
1.233 +} {0 {1 2 3 4}}
1.234 +integrity_check trans-4.12
1.235 +do_test trans-4.98 {
1.236 + altdb close
1.237 + execsql {
1.238 + DROP TABLE one;
1.239 + DROP TABLE two;
1.240 + }
1.241 +} {}
1.242 +integrity_check trans-4.99
1.243 +
1.244 +# Check out the commit/rollback behavior of the database
1.245 +#
1.246 +do_test trans-5.1 {
1.247 + execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
1.248 +} {}
1.249 +do_test trans-5.2 {
1.250 + execsql {BEGIN TRANSACTION}
1.251 + execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
1.252 +} {}
1.253 +do_test trans-5.3 {
1.254 + execsql {CREATE TABLE one(a text, b int)}
1.255 + execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
1.256 +} {one}
1.257 +do_test trans-5.4 {
1.258 + execsql {SELECT a,b FROM one ORDER BY b}
1.259 +} {}
1.260 +do_test trans-5.5 {
1.261 + execsql {INSERT INTO one(a,b) VALUES('hello', 1)}
1.262 + execsql {SELECT a,b FROM one ORDER BY b}
1.263 +} {hello 1}
1.264 +do_test trans-5.6 {
1.265 + execsql {ROLLBACK}
1.266 + execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
1.267 +} {}
1.268 +do_test trans-5.7 {
1.269 + set v [catch {
1.270 + execsql {SELECT a,b FROM one ORDER BY b}
1.271 + } msg]
1.272 + lappend v $msg
1.273 +} {1 {no such table: one}}
1.274 +
1.275 +# Test commits and rollbacks of table CREATE TABLEs, CREATE INDEXs
1.276 +# DROP TABLEs and DROP INDEXs
1.277 +#
1.278 +do_test trans-5.8 {
1.279 + execsql {
1.280 + SELECT name fROM sqlite_master
1.281 + WHERE type='table' OR type='index'
1.282 + ORDER BY name
1.283 + }
1.284 +} {}
1.285 +do_test trans-5.9 {
1.286 + execsql {
1.287 + BEGIN TRANSACTION;
1.288 + CREATE TABLE t1(a int, b int, c int);
1.289 + SELECT name fROM sqlite_master
1.290 + WHERE type='table' OR type='index'
1.291 + ORDER BY name;
1.292 + }
1.293 +} {t1}
1.294 +do_test trans-5.10 {
1.295 + execsql {
1.296 + CREATE INDEX i1 ON t1(a);
1.297 + SELECT name fROM sqlite_master
1.298 + WHERE type='table' OR type='index'
1.299 + ORDER BY name;
1.300 + }
1.301 +} {i1 t1}
1.302 +do_test trans-5.11 {
1.303 + execsql {
1.304 + COMMIT;
1.305 + SELECT name fROM sqlite_master
1.306 + WHERE type='table' OR type='index'
1.307 + ORDER BY name;
1.308 + }
1.309 +} {i1 t1}
1.310 +do_test trans-5.12 {
1.311 + execsql {
1.312 + BEGIN TRANSACTION;
1.313 + CREATE TABLE t2(a int, b int, c int);
1.314 + CREATE INDEX i2a ON t2(a);
1.315 + CREATE INDEX i2b ON t2(b);
1.316 + DROP TABLE t1;
1.317 + SELECT name fROM sqlite_master
1.318 + WHERE type='table' OR type='index'
1.319 + ORDER BY name;
1.320 + }
1.321 +} {i2a i2b t2}
1.322 +do_test trans-5.13 {
1.323 + execsql {
1.324 + ROLLBACK;
1.325 + SELECT name fROM sqlite_master
1.326 + WHERE type='table' OR type='index'
1.327 + ORDER BY name;
1.328 + }
1.329 +} {i1 t1}
1.330 +do_test trans-5.14 {
1.331 + execsql {
1.332 + BEGIN TRANSACTION;
1.333 + DROP INDEX i1;
1.334 + SELECT name fROM sqlite_master
1.335 + WHERE type='table' OR type='index'
1.336 + ORDER BY name;
1.337 + }
1.338 +} {t1}
1.339 +do_test trans-5.15 {
1.340 + execsql {
1.341 + ROLLBACK;
1.342 + SELECT name fROM sqlite_master
1.343 + WHERE type='table' OR type='index'
1.344 + ORDER BY name;
1.345 + }
1.346 +} {i1 t1}
1.347 +do_test trans-5.16 {
1.348 + execsql {
1.349 + BEGIN TRANSACTION;
1.350 + DROP INDEX i1;
1.351 + CREATE TABLE t2(x int, y int, z int);
1.352 + CREATE INDEX i2x ON t2(x);
1.353 + CREATE INDEX i2y ON t2(y);
1.354 + INSERT INTO t2 VALUES(1,2,3);
1.355 + SELECT name fROM sqlite_master
1.356 + WHERE type='table' OR type='index'
1.357 + ORDER BY name;
1.358 + }
1.359 +} {i2x i2y t1 t2}
1.360 +do_test trans-5.17 {
1.361 + execsql {
1.362 + COMMIT;
1.363 + SELECT name fROM sqlite_master
1.364 + WHERE type='table' OR type='index'
1.365 + ORDER BY name;
1.366 + }
1.367 +} {i2x i2y t1 t2}
1.368 +do_test trans-5.18 {
1.369 + execsql {
1.370 + SELECT * FROM t2;
1.371 + }
1.372 +} {1 2 3}
1.373 +do_test trans-5.19 {
1.374 + execsql {
1.375 + SELECT x FROM t2 WHERE y=2;
1.376 + }
1.377 +} {1}
1.378 +do_test trans-5.20 {
1.379 + execsql {
1.380 + BEGIN TRANSACTION;
1.381 + DROP TABLE t1;
1.382 + DROP TABLE t2;
1.383 + SELECT name fROM sqlite_master
1.384 + WHERE type='table' OR type='index'
1.385 + ORDER BY name;
1.386 + }
1.387 +} {}
1.388 +do_test trans-5.21 {
1.389 + set r [catch {execsql {
1.390 + SELECT * FROM t2
1.391 + }} msg]
1.392 + lappend r $msg
1.393 +} {1 {no such table: t2}}
1.394 +do_test trans-5.22 {
1.395 + execsql {
1.396 + ROLLBACK;
1.397 + SELECT name fROM sqlite_master
1.398 + WHERE type='table' OR type='index'
1.399 + ORDER BY name;
1.400 + }
1.401 +} {i2x i2y t1 t2}
1.402 +do_test trans-5.23 {
1.403 + execsql {
1.404 + SELECT * FROM t2;
1.405 + }
1.406 +} {1 2 3}
1.407 +integrity_check trans-5.23
1.408 +
1.409 +
1.410 +# Try to DROP and CREATE tables and indices with the same name
1.411 +# within a transaction. Make sure ROLLBACK works.
1.412 +#
1.413 +do_test trans-6.1 {
1.414 + execsql2 {
1.415 + INSERT INTO t1 VALUES(1,2,3);
1.416 + BEGIN TRANSACTION;
1.417 + DROP TABLE t1;
1.418 + CREATE TABLE t1(p,q,r);
1.419 + ROLLBACK;
1.420 + SELECT * FROM t1;
1.421 + }
1.422 +} {a 1 b 2 c 3}
1.423 +do_test trans-6.2 {
1.424 + execsql2 {
1.425 + INSERT INTO t1 VALUES(1,2,3);
1.426 + BEGIN TRANSACTION;
1.427 + DROP TABLE t1;
1.428 + CREATE TABLE t1(p,q,r);
1.429 + COMMIT;
1.430 + SELECT * FROM t1;
1.431 + }
1.432 +} {}
1.433 +do_test trans-6.3 {
1.434 + execsql2 {
1.435 + INSERT INTO t1 VALUES(1,2,3);
1.436 + SELECT * FROM t1;
1.437 + }
1.438 +} {p 1 q 2 r 3}
1.439 +do_test trans-6.4 {
1.440 + execsql2 {
1.441 + BEGIN TRANSACTION;
1.442 + DROP TABLE t1;
1.443 + CREATE TABLE t1(a,b,c);
1.444 + INSERT INTO t1 VALUES(4,5,6);
1.445 + SELECT * FROM t1;
1.446 + DROP TABLE t1;
1.447 + }
1.448 +} {a 4 b 5 c 6}
1.449 +do_test trans-6.5 {
1.450 + execsql2 {
1.451 + ROLLBACK;
1.452 + SELECT * FROM t1;
1.453 + }
1.454 +} {p 1 q 2 r 3}
1.455 +do_test trans-6.6 {
1.456 + execsql2 {
1.457 + BEGIN TRANSACTION;
1.458 + DROP TABLE t1;
1.459 + CREATE TABLE t1(a,b,c);
1.460 + INSERT INTO t1 VALUES(4,5,6);
1.461 + SELECT * FROM t1;
1.462 + DROP TABLE t1;
1.463 + }
1.464 +} {a 4 b 5 c 6}
1.465 +do_test trans-6.7 {
1.466 + catchsql {
1.467 + COMMIT;
1.468 + SELECT * FROM t1;
1.469 + }
1.470 +} {1 {no such table: t1}}
1.471 +
1.472 +# Repeat on a table with an automatically generated index.
1.473 +#
1.474 +do_test trans-6.10 {
1.475 + execsql2 {
1.476 + CREATE TABLE t1(a unique,b,c);
1.477 + INSERT INTO t1 VALUES(1,2,3);
1.478 + BEGIN TRANSACTION;
1.479 + DROP TABLE t1;
1.480 + CREATE TABLE t1(p unique,q,r);
1.481 + ROLLBACK;
1.482 + SELECT * FROM t1;
1.483 + }
1.484 +} {a 1 b 2 c 3}
1.485 +do_test trans-6.11 {
1.486 + execsql2 {
1.487 + BEGIN TRANSACTION;
1.488 + DROP TABLE t1;
1.489 + CREATE TABLE t1(p unique,q,r);
1.490 + COMMIT;
1.491 + SELECT * FROM t1;
1.492 + }
1.493 +} {}
1.494 +do_test trans-6.12 {
1.495 + execsql2 {
1.496 + INSERT INTO t1 VALUES(1,2,3);
1.497 + SELECT * FROM t1;
1.498 + }
1.499 +} {p 1 q 2 r 3}
1.500 +do_test trans-6.13 {
1.501 + execsql2 {
1.502 + BEGIN TRANSACTION;
1.503 + DROP TABLE t1;
1.504 + CREATE TABLE t1(a unique,b,c);
1.505 + INSERT INTO t1 VALUES(4,5,6);
1.506 + SELECT * FROM t1;
1.507 + DROP TABLE t1;
1.508 + }
1.509 +} {a 4 b 5 c 6}
1.510 +do_test trans-6.14 {
1.511 + execsql2 {
1.512 + ROLLBACK;
1.513 + SELECT * FROM t1;
1.514 + }
1.515 +} {p 1 q 2 r 3}
1.516 +do_test trans-6.15 {
1.517 + execsql2 {
1.518 + BEGIN TRANSACTION;
1.519 + DROP TABLE t1;
1.520 + CREATE TABLE t1(a unique,b,c);
1.521 + INSERT INTO t1 VALUES(4,5,6);
1.522 + SELECT * FROM t1;
1.523 + DROP TABLE t1;
1.524 + }
1.525 +} {a 4 b 5 c 6}
1.526 +do_test trans-6.16 {
1.527 + catchsql {
1.528 + COMMIT;
1.529 + SELECT * FROM t1;
1.530 + }
1.531 +} {1 {no such table: t1}}
1.532 +
1.533 +do_test trans-6.20 {
1.534 + execsql {
1.535 + CREATE TABLE t1(a integer primary key,b,c);
1.536 + INSERT INTO t1 VALUES(1,-2,-3);
1.537 + INSERT INTO t1 VALUES(4,-5,-6);
1.538 + SELECT * FROM t1;
1.539 + }
1.540 +} {1 -2 -3 4 -5 -6}
1.541 +do_test trans-6.21 {
1.542 + execsql {
1.543 + CREATE INDEX i1 ON t1(b);
1.544 + SELECT * FROM t1 WHERE b<1;
1.545 + }
1.546 +} {4 -5 -6 1 -2 -3}
1.547 +do_test trans-6.22 {
1.548 + execsql {
1.549 + BEGIN TRANSACTION;
1.550 + DROP INDEX i1;
1.551 + SELECT * FROM t1 WHERE b<1;
1.552 + ROLLBACK;
1.553 + }
1.554 +} {1 -2 -3 4 -5 -6}
1.555 +do_test trans-6.23 {
1.556 + execsql {
1.557 + SELECT * FROM t1 WHERE b<1;
1.558 + }
1.559 +} {4 -5 -6 1 -2 -3}
1.560 +do_test trans-6.24 {
1.561 + execsql {
1.562 + BEGIN TRANSACTION;
1.563 + DROP TABLE t1;
1.564 + ROLLBACK;
1.565 + SELECT * FROM t1 WHERE b<1;
1.566 + }
1.567 +} {4 -5 -6 1 -2 -3}
1.568 +
1.569 +do_test trans-6.25 {
1.570 + execsql {
1.571 + BEGIN TRANSACTION;
1.572 + DROP INDEX i1;
1.573 + CREATE INDEX i1 ON t1(c);
1.574 + SELECT * FROM t1 WHERE b<1;
1.575 + }
1.576 +} {1 -2 -3 4 -5 -6}
1.577 +do_test trans-6.26 {
1.578 + execsql {
1.579 + SELECT * FROM t1 WHERE c<1;
1.580 + }
1.581 +} {4 -5 -6 1 -2 -3}
1.582 +do_test trans-6.27 {
1.583 + execsql {
1.584 + ROLLBACK;
1.585 + SELECT * FROM t1 WHERE b<1;
1.586 + }
1.587 +} {4 -5 -6 1 -2 -3}
1.588 +do_test trans-6.28 {
1.589 + execsql {
1.590 + SELECT * FROM t1 WHERE c<1;
1.591 + }
1.592 +} {1 -2 -3 4 -5 -6}
1.593 +
1.594 +# The following repeats steps 6.20 through 6.28, but puts a "unique"
1.595 +# constraint the first field of the table in order to generate an
1.596 +# automatic index.
1.597 +#
1.598 +do_test trans-6.30 {
1.599 + execsql {
1.600 + BEGIN TRANSACTION;
1.601 + DROP TABLE t1;
1.602 + CREATE TABLE t1(a int unique,b,c);
1.603 + COMMIT;
1.604 + INSERT INTO t1 VALUES(1,-2,-3);
1.605 + INSERT INTO t1 VALUES(4,-5,-6);
1.606 + SELECT * FROM t1 ORDER BY a;
1.607 + }
1.608 +} {1 -2 -3 4 -5 -6}
1.609 +do_test trans-6.31 {
1.610 + execsql {
1.611 + CREATE INDEX i1 ON t1(b);
1.612 + SELECT * FROM t1 WHERE b<1;
1.613 + }
1.614 +} {4 -5 -6 1 -2 -3}
1.615 +do_test trans-6.32 {
1.616 + execsql {
1.617 + BEGIN TRANSACTION;
1.618 + DROP INDEX i1;
1.619 + SELECT * FROM t1 WHERE b<1;
1.620 + ROLLBACK;
1.621 + }
1.622 +} {1 -2 -3 4 -5 -6}
1.623 +do_test trans-6.33 {
1.624 + execsql {
1.625 + SELECT * FROM t1 WHERE b<1;
1.626 + }
1.627 +} {4 -5 -6 1 -2 -3}
1.628 +do_test trans-6.34 {
1.629 + execsql {
1.630 + BEGIN TRANSACTION;
1.631 + DROP TABLE t1;
1.632 + ROLLBACK;
1.633 + SELECT * FROM t1 WHERE b<1;
1.634 + }
1.635 +} {4 -5 -6 1 -2 -3}
1.636 +
1.637 +do_test trans-6.35 {
1.638 + execsql {
1.639 + BEGIN TRANSACTION;
1.640 + DROP INDEX i1;
1.641 + CREATE INDEX i1 ON t1(c);
1.642 + SELECT * FROM t1 WHERE b<1;
1.643 + }
1.644 +} {1 -2 -3 4 -5 -6}
1.645 +do_test trans-6.36 {
1.646 + execsql {
1.647 + SELECT * FROM t1 WHERE c<1;
1.648 + }
1.649 +} {4 -5 -6 1 -2 -3}
1.650 +do_test trans-6.37 {
1.651 + execsql {
1.652 + DROP INDEX i1;
1.653 + SELECT * FROM t1 WHERE c<1;
1.654 + }
1.655 +} {1 -2 -3 4 -5 -6}
1.656 +do_test trans-6.38 {
1.657 + execsql {
1.658 + ROLLBACK;
1.659 + SELECT * FROM t1 WHERE b<1;
1.660 + }
1.661 +} {4 -5 -6 1 -2 -3}
1.662 +do_test trans-6.39 {
1.663 + execsql {
1.664 + SELECT * FROM t1 WHERE c<1;
1.665 + }
1.666 +} {1 -2 -3 4 -5 -6}
1.667 +integrity_check trans-6.40
1.668 +
1.669 +# Test to make sure rollback restores the database back to its original
1.670 +# state.
1.671 +#
1.672 +do_test trans-7.1 {
1.673 + execsql {BEGIN}
1.674 + for {set i 0} {$i<1000} {incr i} {
1.675 + set r1 [expr {rand()}]
1.676 + set r2 [expr {rand()}]
1.677 + set r3 [expr {rand()}]
1.678 + execsql "INSERT INTO t2 VALUES($r1,$r2,$r3)"
1.679 + }
1.680 + execsql {COMMIT}
1.681 + set ::checksum [execsql {SELECT md5sum(x,y,z) FROM t2}]
1.682 + set ::checksum2 [
1.683 + execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
1.684 + ]
1.685 + execsql {SELECT count(*) FROM t2}
1.686 +} {1001}
1.687 +do_test trans-7.2 {
1.688 + execsql {SELECT md5sum(x,y,z) FROM t2}
1.689 +} $checksum
1.690 +do_test trans-7.2.1 {
1.691 + execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
1.692 +} $checksum2
1.693 +do_test trans-7.3 {
1.694 + execsql {
1.695 + BEGIN;
1.696 + DELETE FROM t2;
1.697 + ROLLBACK;
1.698 + SELECT md5sum(x,y,z) FROM t2;
1.699 + }
1.700 +} $checksum
1.701 +do_test trans-7.4 {
1.702 + execsql {
1.703 + BEGIN;
1.704 + INSERT INTO t2 SELECT * FROM t2;
1.705 + ROLLBACK;
1.706 + SELECT md5sum(x,y,z) FROM t2;
1.707 + }
1.708 +} $checksum
1.709 +do_test trans-7.5 {
1.710 + execsql {
1.711 + BEGIN;
1.712 + DELETE FROM t2;
1.713 + ROLLBACK;
1.714 + SELECT md5sum(x,y,z) FROM t2;
1.715 + }
1.716 +} $checksum
1.717 +do_test trans-7.6 {
1.718 + execsql {
1.719 + BEGIN;
1.720 + INSERT INTO t2 SELECT * FROM t2;
1.721 + ROLLBACK;
1.722 + SELECT md5sum(x,y,z) FROM t2;
1.723 + }
1.724 +} $checksum
1.725 +do_test trans-7.7 {
1.726 + execsql {
1.727 + BEGIN;
1.728 + CREATE TABLE t3 AS SELECT * FROM t2;
1.729 + INSERT INTO t2 SELECT * FROM t3;
1.730 + ROLLBACK;
1.731 + SELECT md5sum(x,y,z) FROM t2;
1.732 + }
1.733 +} $checksum
1.734 +do_test trans-7.8 {
1.735 + execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
1.736 +} $checksum2
1.737 +ifcapable tempdb {
1.738 + do_test trans-7.9 {
1.739 + execsql {
1.740 + BEGIN;
1.741 + CREATE TEMP TABLE t3 AS SELECT * FROM t2;
1.742 + INSERT INTO t2 SELECT * FROM t3;
1.743 + ROLLBACK;
1.744 + SELECT md5sum(x,y,z) FROM t2;
1.745 + }
1.746 + } $checksum
1.747 +}
1.748 +do_test trans-7.10 {
1.749 + execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
1.750 +} $checksum2
1.751 +ifcapable tempdb {
1.752 + do_test trans-7.11 {
1.753 + execsql {
1.754 + BEGIN;
1.755 + CREATE TEMP TABLE t3 AS SELECT * FROM t2;
1.756 + INSERT INTO t2 SELECT * FROM t3;
1.757 + DROP INDEX i2x;
1.758 + DROP INDEX i2y;
1.759 + CREATE INDEX i3a ON t3(x);
1.760 + ROLLBACK;
1.761 + SELECT md5sum(x,y,z) FROM t2;
1.762 + }
1.763 + } $checksum
1.764 +}
1.765 +do_test trans-7.12 {
1.766 + execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
1.767 +} $checksum2
1.768 +ifcapable tempdb {
1.769 + do_test trans-7.13 {
1.770 + execsql {
1.771 + BEGIN;
1.772 + DROP TABLE t2;
1.773 + ROLLBACK;
1.774 + SELECT md5sum(x,y,z) FROM t2;
1.775 + }
1.776 + } $checksum
1.777 +}
1.778 +do_test trans-7.14 {
1.779 + execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
1.780 +} $checksum2
1.781 +integrity_check trans-7.15
1.782 +
1.783 +# Arrange for another process to begin modifying the database but abort
1.784 +# and die in the middle of the modification. Then have this process read
1.785 +# the database. This process should detect the journal file and roll it
1.786 +# back. Verify that this happens correctly.
1.787 +#
1.788 +print_text "TclSqlite3-2" "begin"
1.789 +set fd [open test.tcl w]
1.790 +puts $fd {
1.791 + sqlite3 db test.db
1.792 + db eval {
1.793 + PRAGMA default_cache_size=20;
1.794 + BEGIN;
1.795 + CREATE TABLE t3 AS SELECT * FROM t2;
1.796 + DELETE FROM t2;
1.797 + }
1.798 + sqlite_abort
1.799 +}
1.800 +close $fd
1.801 +print_text "TclSqlite3-2" "end"
1.802 +
1.803 +do_test trans-8.1 {
1.804 + catch {exec [info nameofexec] test.tcl}
1.805 + execsql {SELECT md5sum(x,y,z) FROM t2}
1.806 +} $checksum
1.807 +do_test trans-8.2 {
1.808 + execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
1.809 +} $checksum2
1.810 +integrity_check trans-8.3
1.811 +
1.812 +print_text "TclSqlite3-2" "begin"
1.813 +set fd [open test.tcl w]
1.814 +puts $fd {
1.815 + sqlite3 db test.db
1.816 + db eval {
1.817 + PRAGMA journal_mode=persist;
1.818 + PRAGMA default_cache_size=20;
1.819 + BEGIN;
1.820 + CREATE TABLE t3 AS SELECT * FROM t2;
1.821 + DELETE FROM t2;
1.822 + }
1.823 + sqlite_abort
1.824 +}
1.825 +close $fd
1.826 +print_text "TclSqlite3-2" "end"
1.827 +
1.828 +do_test trans-8.4 {
1.829 + catch {exec [info nameofexec] test.tcl}
1.830 + execsql {SELECT md5sum(x,y,z) FROM t2}
1.831 +} $checksum
1.832 +do_test trans-8.5 {
1.833 + execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
1.834 +} $checksum2
1.835 +integrity_check trans-8.6
1.836 +
1.837 +# In the following sequence of tests, compute the MD5 sum of the content
1.838 +# of a table, make lots of modifications to that table, then do a rollback.
1.839 +# Verify that after the rollback, the MD5 checksum is unchanged.
1.840 +#
1.841 +do_test trans-9.1 {
1.842 + execsql {
1.843 + PRAGMA default_cache_size=10;
1.844 + }
1.845 + db close
1.846 + sqlite3 db test.db
1.847 + execsql {
1.848 + BEGIN;
1.849 + CREATE TABLE t3(x TEXT);
1.850 + INSERT INTO t3 VALUES(randstr(10,400));
1.851 + INSERT INTO t3 VALUES(randstr(10,400));
1.852 + INSERT INTO t3 SELECT randstr(10,400) FROM t3;
1.853 + INSERT INTO t3 SELECT randstr(10,400) FROM t3;
1.854 + INSERT INTO t3 SELECT randstr(10,400) FROM t3;
1.855 + INSERT INTO t3 SELECT randstr(10,400) FROM t3;
1.856 + INSERT INTO t3 SELECT randstr(10,400) FROM t3;
1.857 + INSERT INTO t3 SELECT randstr(10,400) FROM t3;
1.858 + INSERT INTO t3 SELECT randstr(10,400) FROM t3;
1.859 + INSERT INTO t3 SELECT randstr(10,400) FROM t3;
1.860 + INSERT INTO t3 SELECT randstr(10,400) FROM t3;
1.861 + COMMIT;
1.862 + SELECT count(*) FROM t3;
1.863 + }
1.864 +} {1024}
1.865 +
1.866 +# The following procedure computes a "signature" for table "t3". If
1.867 +# T3 changes in any way, the signature should change.
1.868 +#
1.869 +# This is used to test ROLLBACK. We gather a signature for t3, then
1.870 +# make lots of changes to t3, then rollback and take another signature.
1.871 +# The two signatures should be the same.
1.872 +#
1.873 +proc signature {} {
1.874 + return [db eval {SELECT count(*), md5sum(x) FROM t3}]
1.875 +}
1.876 +
1.877 +# Repeat the following group of tests 20 times for quick testing and
1.878 +# 40 times for full testing. Each iteration of the test makes table
1.879 +# t3 a little larger, and thus takes a little longer, so doing 40 tests
1.880 +# is more than 2.0 times slower than doing 20 tests. Considerably more.
1.881 +#
1.882 +if {[info exists ISQUICK]} {
1.883 + set limit 20
1.884 +} elseif {[info exists SOAKTEST]} {
1.885 + set limit 100
1.886 +} else {
1.887 + set limit 40
1.888 +}
1.889 +
1.890 +# Do rollbacks. Make sure the signature does not change.
1.891 +#
1.892 +for {set i 2} {$i<=$limit} {incr i} {
1.893 + set ::sig [signature]
1.894 + set cnt [lindex $::sig 0]
1.895 + if {$i%2==0} {
1.896 + execsql {PRAGMA fullfsync=ON}
1.897 + } else {
1.898 + execsql {PRAGMA fullfsync=OFF}
1.899 + }
1.900 + set sqlite_sync_count 0
1.901 + set sqlite_fullsync_count 0
1.902 + do_test trans-9.$i.1-$cnt {
1.903 + execsql {
1.904 + BEGIN;
1.905 + DELETE FROM t3 WHERE random()%10!=0;
1.906 + INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
1.907 + INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
1.908 + ROLLBACK;
1.909 + }
1.910 + signature
1.911 + } $sig
1.912 + do_test trans-9.$i.2-$cnt {
1.913 + execsql {
1.914 + BEGIN;
1.915 + DELETE FROM t3 WHERE random()%10!=0;
1.916 + INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
1.917 + DELETE FROM t3 WHERE random()%10!=0;
1.918 + INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
1.919 + ROLLBACK;
1.920 + }
1.921 + signature
1.922 + } $sig
1.923 + if {$i<$limit} {
1.924 + do_test trans-9.$i.3-$cnt {
1.925 + execsql {
1.926 + INSERT INTO t3 SELECT randstr(10,400) FROM t3 WHERE random()%10==0;
1.927 + }
1.928 + } {}
1.929 + if {$tcl_platform(platform)=="unix"} {
1.930 + do_test trans-9.$i.4-$cnt {
1.931 + expr {$sqlite_sync_count>0}
1.932 + } 1
1.933 + ifcapable pager_pragmas {
1.934 + do_test trans-9.$i.5-$cnt {
1.935 + expr {$sqlite_fullsync_count>0}
1.936 + } [expr {$i%2==0}]
1.937 + } else {
1.938 + do_test trans-9.$i.5-$cnt {
1.939 + expr {$sqlite_fullsync_count==0}
1.940 + } {1}
1.941 + }
1.942 + }
1.943 + }
1.944 + set ::pager_old_format 0
1.945 +}
1.946 +
1.947 +finish_test