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