summaryrefslogtreecommitdiff
path: root/test/trans.test
diff options
context:
space:
mode:
Diffstat (limited to 'test/trans.test')
-rw-r--r--test/trans.test956
1 files changed, 956 insertions, 0 deletions
diff --git a/test/trans.test b/test/trans.test
new file mode 100644
index 0000000..bbaedc5
--- /dev/null
+++ b/test/trans.test
@@ -0,0 +1,956 @@
+# 2001 September 15
+#
+# The author disclaims copyright to this source code. In place of
+# a legal notice, here is a blessing:
+#
+# May you do good and not evil.
+# May you find forgiveness for yourself and forgive others.
+# May you share freely, never taking more than you give.
+#
+#***********************************************************************
+# This file implements regression tests for SQLite library. The
+# focus of this script is database locks.
+#
+# $Id: trans.test,v 1.41 2009/04/28 16:37:59 danielk1977 Exp $
+
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+# Create several tables to work with.
+#
+wal_set_journal_mode
+do_test trans-1.0 {
+ execsql {
+ CREATE TABLE one(a int PRIMARY KEY, b text);
+ INSERT INTO one VALUES(1,'one');
+ INSERT INTO one VALUES(2,'two');
+ INSERT INTO one VALUES(3,'three');
+ SELECT b FROM one ORDER BY a;
+ }
+} {one two three}
+integrity_check trans-1.0.1
+do_test trans-1.1 {
+ execsql {
+ CREATE TABLE two(a int PRIMARY KEY, b text);
+ INSERT INTO two VALUES(1,'I');
+ INSERT INTO two VALUES(5,'V');
+ INSERT INTO two VALUES(10,'X');
+ SELECT b FROM two ORDER BY a;
+ }
+} {I V X}
+do_test trans-1.9 {
+ sqlite3 altdb test.db
+ execsql {SELECT b FROM one ORDER BY a} altdb
+} {one two three}
+do_test trans-1.10 {
+ execsql {SELECT b FROM two ORDER BY a} altdb
+} {I V X}
+integrity_check trans-1.11
+wal_check_journal_mode trans-1.12
+
+# Basic transactions
+#
+do_test trans-2.1 {
+ set v [catch {execsql {BEGIN}} msg]
+ lappend v $msg
+} {0 {}}
+do_test trans-2.2 {
+ set v [catch {execsql {END}} msg]
+ lappend v $msg
+} {0 {}}
+do_test trans-2.3 {
+ set v [catch {execsql {BEGIN TRANSACTION}} msg]
+ lappend v $msg
+} {0 {}}
+do_test trans-2.4 {
+ set v [catch {execsql {COMMIT TRANSACTION}} msg]
+ lappend v $msg
+} {0 {}}
+do_test trans-2.5 {
+ set v [catch {execsql {BEGIN TRANSACTION 'foo'}} msg]
+ lappend v $msg
+} {0 {}}
+do_test trans-2.6 {
+ set v [catch {execsql {ROLLBACK TRANSACTION 'foo'}} msg]
+ lappend v $msg
+} {0 {}}
+do_test trans-2.10 {
+ execsql {
+ BEGIN;
+ SELECT a FROM one ORDER BY a;
+ SELECT a FROM two ORDER BY a;
+ END;
+ }
+} {1 2 3 1 5 10}
+integrity_check trans-2.11
+wal_check_journal_mode trans-2.12
+
+# Check the locking behavior
+#
+do_test trans-3.1 {
+ execsql {
+ BEGIN;
+ UPDATE one SET a = 0 WHERE 0;
+ SELECT a FROM one ORDER BY a;
+ }
+} {1 2 3}
+do_test trans-3.2 {
+ catchsql {
+ SELECT a FROM two ORDER BY a;
+ } altdb
+} {0 {1 5 10}}
+
+do_test trans-3.3 {
+ catchsql {
+ SELECT a FROM one ORDER BY a;
+ } altdb
+} {0 {1 2 3}}
+do_test trans-3.4 {
+ catchsql {
+ INSERT INTO one VALUES(4,'four');
+ }
+} {0 {}}
+do_test trans-3.5 {
+ catchsql {
+ SELECT a FROM two ORDER BY a;
+ } altdb
+} {0 {1 5 10}}
+do_test trans-3.6 {
+ catchsql {
+ SELECT a FROM one ORDER BY a;
+ } altdb
+} {0 {1 2 3}}
+do_test trans-3.7 {
+ catchsql {
+ INSERT INTO two VALUES(4,'IV');
+ }
+} {0 {}}
+do_test trans-3.8 {
+ catchsql {
+ SELECT a FROM two ORDER BY a;
+ } altdb
+} {0 {1 5 10}}
+do_test trans-3.9 {
+ catchsql {
+ SELECT a FROM one ORDER BY a;
+ } altdb
+} {0 {1 2 3}}
+do_test trans-3.10 {
+ execsql {END TRANSACTION}
+} {}
+
+do_test trans-3.11 {
+ set v [catch {execsql {
+ SELECT a FROM two ORDER BY a;
+ } altdb} msg]
+ lappend v $msg
+} {0 {1 4 5 10}}
+do_test trans-3.12 {
+ set v [catch {execsql {
+ SELECT a FROM one ORDER BY a;
+ } altdb} msg]
+ lappend v $msg
+} {0 {1 2 3 4}}
+do_test trans-3.13 {
+ set v [catch {execsql {
+ SELECT a FROM two ORDER BY a;
+ } db} msg]
+ lappend v $msg
+} {0 {1 4 5 10}}
+do_test trans-3.14 {
+ set v [catch {execsql {
+ SELECT a FROM one ORDER BY a;
+ } db} msg]
+ lappend v $msg
+} {0 {1 2 3 4}}
+integrity_check trans-3.15
+wal_check_journal_mode trans-3.16
+
+do_test trans-4.1 {
+ set v [catch {execsql {
+ COMMIT;
+ } db} msg]
+ lappend v $msg
+} {1 {cannot commit - no transaction is active}}
+do_test trans-4.2 {
+ set v [catch {execsql {
+ ROLLBACK;
+ } db} msg]
+ lappend v $msg
+} {1 {cannot rollback - no transaction is active}}
+do_test trans-4.3 {
+ catchsql {
+ BEGIN TRANSACTION;
+ UPDATE two SET a = 0 WHERE 0;
+ SELECT a FROM two ORDER BY a;
+ } db
+} {0 {1 4 5 10}}
+do_test trans-4.4 {
+ catchsql {
+ SELECT a FROM two ORDER BY a;
+ } altdb
+} {0 {1 4 5 10}}
+do_test trans-4.5 {
+ catchsql {
+ SELECT a FROM one ORDER BY a;
+ } altdb
+} {0 {1 2 3 4}}
+do_test trans-4.6 {
+ catchsql {
+ BEGIN TRANSACTION;
+ SELECT a FROM one ORDER BY a;
+ } db
+} {1 {cannot start a transaction within a transaction}}
+do_test trans-4.7 {
+ catchsql {
+ SELECT a FROM two ORDER BY a;
+ } altdb
+} {0 {1 4 5 10}}
+do_test trans-4.8 {
+ catchsql {
+ SELECT a FROM one ORDER BY a;
+ } altdb
+} {0 {1 2 3 4}}
+do_test trans-4.9 {
+ set v [catch {execsql {
+ END TRANSACTION;
+ SELECT a FROM two ORDER BY a;
+ } db} msg]
+ lappend v $msg
+} {0 {1 4 5 10}}
+do_test trans-4.10 {
+ set v [catch {execsql {
+ SELECT a FROM two ORDER BY a;
+ } altdb} msg]
+ lappend v $msg
+} {0 {1 4 5 10}}
+do_test trans-4.11 {
+ set v [catch {execsql {
+ SELECT a FROM one ORDER BY a;
+ } altdb} msg]
+ lappend v $msg
+} {0 {1 2 3 4}}
+integrity_check trans-4.12
+wal_check_journal_mode trans-4.13
+wal_check_journal_mode trans-4.14 altdb
+do_test trans-4.98 {
+ altdb close
+ execsql {
+ DROP TABLE one;
+ DROP TABLE two;
+ }
+} {}
+integrity_check trans-4.99
+
+# Check out the commit/rollback behavior of the database
+#
+do_test trans-5.1 {
+ execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
+} {}
+do_test trans-5.2 {
+ execsql {BEGIN TRANSACTION}
+ execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
+} {}
+do_test trans-5.3 {
+ execsql {CREATE TABLE one(a text, b int)}
+ execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
+} {one}
+do_test trans-5.4 {
+ execsql {SELECT a,b FROM one ORDER BY b}
+} {}
+do_test trans-5.5 {
+ execsql {INSERT INTO one(a,b) VALUES('hello', 1)}
+ execsql {SELECT a,b FROM one ORDER BY b}
+} {hello 1}
+do_test trans-5.6 {
+ execsql {ROLLBACK}
+ execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
+} {}
+do_test trans-5.7 {
+ set v [catch {
+ execsql {SELECT a,b FROM one ORDER BY b}
+ } msg]
+ lappend v $msg
+} {1 {no such table: one}}
+
+# Test commits and rollbacks of table CREATE TABLEs, CREATE INDEXs
+# DROP TABLEs and DROP INDEXs
+#
+do_test trans-5.8 {
+ execsql {
+ SELECT name fROM sqlite_master
+ WHERE type='table' OR type='index'
+ ORDER BY name
+ }
+} {}
+do_test trans-5.9 {
+ execsql {
+ BEGIN TRANSACTION;
+ CREATE TABLE t1(a int, b int, c int);
+ SELECT name fROM sqlite_master
+ WHERE type='table' OR type='index'
+ ORDER BY name;
+ }
+} {t1}
+do_test trans-5.10 {
+ execsql {
+ CREATE INDEX i1 ON t1(a);
+ SELECT name fROM sqlite_master
+ WHERE type='table' OR type='index'
+ ORDER BY name;
+ }
+} {i1 t1}
+do_test trans-5.11 {
+ execsql {
+ COMMIT;
+ SELECT name fROM sqlite_master
+ WHERE type='table' OR type='index'
+ ORDER BY name;
+ }
+} {i1 t1}
+do_test trans-5.12 {
+ execsql {
+ BEGIN TRANSACTION;
+ CREATE TABLE t2(a int, b int, c int);
+ CREATE INDEX i2a ON t2(a);
+ CREATE INDEX i2b ON t2(b);
+ DROP TABLE t1;
+ SELECT name fROM sqlite_master
+ WHERE type='table' OR type='index'
+ ORDER BY name;
+ }
+} {i2a i2b t2}
+do_test trans-5.13 {
+ execsql {
+ ROLLBACK;
+ SELECT name fROM sqlite_master
+ WHERE type='table' OR type='index'
+ ORDER BY name;
+ }
+} {i1 t1}
+do_test trans-5.14 {
+ execsql {
+ BEGIN TRANSACTION;
+ DROP INDEX i1;
+ SELECT name fROM sqlite_master
+ WHERE type='table' OR type='index'
+ ORDER BY name;
+ }
+} {t1}
+do_test trans-5.15 {
+ execsql {
+ ROLLBACK;
+ SELECT name fROM sqlite_master
+ WHERE type='table' OR type='index'
+ ORDER BY name;
+ }
+} {i1 t1}
+do_test trans-5.16 {
+ execsql {
+ BEGIN TRANSACTION;
+ DROP INDEX i1;
+ CREATE TABLE t2(x int, y int, z int);
+ CREATE INDEX i2x ON t2(x);
+ CREATE INDEX i2y ON t2(y);
+ INSERT INTO t2 VALUES(1,2,3);
+ SELECT name fROM sqlite_master
+ WHERE type='table' OR type='index'
+ ORDER BY name;
+ }
+} {i2x i2y t1 t2}
+do_test trans-5.17 {
+ execsql {
+ COMMIT;
+ SELECT name fROM sqlite_master
+ WHERE type='table' OR type='index'
+ ORDER BY name;
+ }
+} {i2x i2y t1 t2}
+do_test trans-5.18 {
+ execsql {
+ SELECT * FROM t2;
+ }
+} {1 2 3}
+do_test trans-5.19 {
+ execsql {
+ SELECT x FROM t2 WHERE y=2;
+ }
+} {1}
+do_test trans-5.20 {
+ execsql {
+ BEGIN TRANSACTION;
+ DROP TABLE t1;
+ DROP TABLE t2;
+ SELECT name fROM sqlite_master
+ WHERE type='table' OR type='index'
+ ORDER BY name;
+ }
+} {}
+do_test trans-5.21 {
+ set r [catch {execsql {
+ SELECT * FROM t2
+ }} msg]
+ lappend r $msg
+} {1 {no such table: t2}}
+do_test trans-5.22 {
+ execsql {
+ ROLLBACK;
+ SELECT name fROM sqlite_master
+ WHERE type='table' OR type='index'
+ ORDER BY name;
+ }
+} {i2x i2y t1 t2}
+do_test trans-5.23 {
+ execsql {
+ SELECT * FROM t2;
+ }
+} {1 2 3}
+integrity_check trans-5.23
+
+
+# Try to DROP and CREATE tables and indices with the same name
+# within a transaction. Make sure ROLLBACK works.
+#
+do_test trans-6.1 {
+ execsql2 {
+ INSERT INTO t1 VALUES(1,2,3);
+ BEGIN TRANSACTION;
+ DROP TABLE t1;
+ CREATE TABLE t1(p,q,r);
+ ROLLBACK;
+ SELECT * FROM t1;
+ }
+} {a 1 b 2 c 3}
+do_test trans-6.2 {
+ execsql2 {
+ INSERT INTO t1 VALUES(1,2,3);
+ BEGIN TRANSACTION;
+ DROP TABLE t1;
+ CREATE TABLE t1(p,q,r);
+ COMMIT;
+ SELECT * FROM t1;
+ }
+} {}
+do_test trans-6.3 {
+ execsql2 {
+ INSERT INTO t1 VALUES(1,2,3);
+ SELECT * FROM t1;
+ }
+} {p 1 q 2 r 3}
+do_test trans-6.4 {
+ execsql2 {
+ BEGIN TRANSACTION;
+ DROP TABLE t1;
+ CREATE TABLE t1(a,b,c);
+ INSERT INTO t1 VALUES(4,5,6);
+ SELECT * FROM t1;
+ DROP TABLE t1;
+ }
+} {a 4 b 5 c 6}
+do_test trans-6.5 {
+ execsql2 {
+ ROLLBACK;
+ SELECT * FROM t1;
+ }
+} {p 1 q 2 r 3}
+do_test trans-6.6 {
+ execsql2 {
+ BEGIN TRANSACTION;
+ DROP TABLE t1;
+ CREATE TABLE t1(a,b,c);
+ INSERT INTO t1 VALUES(4,5,6);
+ SELECT * FROM t1;
+ DROP TABLE t1;
+ }
+} {a 4 b 5 c 6}
+do_test trans-6.7 {
+ catchsql {
+ COMMIT;
+ SELECT * FROM t1;
+ }
+} {1 {no such table: t1}}
+
+# Repeat on a table with an automatically generated index.
+#
+do_test trans-6.10 {
+ execsql2 {
+ CREATE TABLE t1(a unique,b,c);
+ INSERT INTO t1 VALUES(1,2,3);
+ BEGIN TRANSACTION;
+ DROP TABLE t1;
+ CREATE TABLE t1(p unique,q,r);
+ ROLLBACK;
+ SELECT * FROM t1;
+ }
+} {a 1 b 2 c 3}
+do_test trans-6.11 {
+ execsql2 {
+ BEGIN TRANSACTION;
+ DROP TABLE t1;
+ CREATE TABLE t1(p unique,q,r);
+ COMMIT;
+ SELECT * FROM t1;
+ }
+} {}
+do_test trans-6.12 {
+ execsql2 {
+ INSERT INTO t1 VALUES(1,2,3);
+ SELECT * FROM t1;
+ }
+} {p 1 q 2 r 3}
+do_test trans-6.13 {
+ execsql2 {
+ BEGIN TRANSACTION;
+ DROP TABLE t1;
+ CREATE TABLE t1(a unique,b,c);
+ INSERT INTO t1 VALUES(4,5,6);
+ SELECT * FROM t1;
+ DROP TABLE t1;
+ }
+} {a 4 b 5 c 6}
+do_test trans-6.14 {
+ execsql2 {
+ ROLLBACK;
+ SELECT * FROM t1;
+ }
+} {p 1 q 2 r 3}
+do_test trans-6.15 {
+ execsql2 {
+ BEGIN TRANSACTION;
+ DROP TABLE t1;
+ CREATE TABLE t1(a unique,b,c);
+ INSERT INTO t1 VALUES(4,5,6);
+ SELECT * FROM t1;
+ DROP TABLE t1;
+ }
+} {a 4 b 5 c 6}
+do_test trans-6.16 {
+ catchsql {
+ COMMIT;
+ SELECT * FROM t1;
+ }
+} {1 {no such table: t1}}
+
+do_test trans-6.20 {
+ execsql {
+ CREATE TABLE t1(a integer primary key,b,c);
+ INSERT INTO t1 VALUES(1,-2,-3);
+ INSERT INTO t1 VALUES(4,-5,-6);
+ SELECT * FROM t1;
+ }
+} {1 -2 -3 4 -5 -6}
+do_test trans-6.21 {
+ execsql {
+ CREATE INDEX i1 ON t1(b);
+ SELECT * FROM t1 WHERE b<1;
+ }
+} {4 -5 -6 1 -2 -3}
+do_test trans-6.22 {
+ execsql {
+ BEGIN TRANSACTION;
+ DROP INDEX i1;
+ SELECT * FROM t1 WHERE b<1;
+ ROLLBACK;
+ }
+} {1 -2 -3 4 -5 -6}
+do_test trans-6.23 {
+ execsql {
+ SELECT * FROM t1 WHERE b<1;
+ }
+} {4 -5 -6 1 -2 -3}
+do_test trans-6.24 {
+ execsql {
+ BEGIN TRANSACTION;
+ DROP TABLE t1;
+ ROLLBACK;
+ SELECT * FROM t1 WHERE b<1;
+ }
+} {4 -5 -6 1 -2 -3}
+
+do_test trans-6.25 {
+ execsql {
+ BEGIN TRANSACTION;
+ DROP INDEX i1;
+ CREATE INDEX i1 ON t1(c);
+ SELECT * FROM t1 WHERE b<1;
+ }
+} {1 -2 -3 4 -5 -6}
+do_test trans-6.26 {
+ execsql {
+ SELECT * FROM t1 WHERE c<1;
+ }
+} {4 -5 -6 1 -2 -3}
+do_test trans-6.27 {
+ execsql {
+ ROLLBACK;
+ SELECT * FROM t1 WHERE b<1;
+ }
+} {4 -5 -6 1 -2 -3}
+do_test trans-6.28 {
+ execsql {
+ SELECT * FROM t1 WHERE c<1;
+ }
+} {1 -2 -3 4 -5 -6}
+
+# The following repeats steps 6.20 through 6.28, but puts a "unique"
+# constraint the first field of the table in order to generate an
+# automatic index.
+#
+do_test trans-6.30 {
+ execsql {
+ BEGIN TRANSACTION;
+ DROP TABLE t1;
+ CREATE TABLE t1(a int unique,b,c);
+ COMMIT;
+ INSERT INTO t1 VALUES(1,-2,-3);
+ INSERT INTO t1 VALUES(4,-5,-6);
+ SELECT * FROM t1 ORDER BY a;
+ }
+} {1 -2 -3 4 -5 -6}
+do_test trans-6.31 {
+ execsql {
+ CREATE INDEX i1 ON t1(b);
+ SELECT * FROM t1 WHERE b<1;
+ }
+} {4 -5 -6 1 -2 -3}
+do_test trans-6.32 {
+ execsql {
+ BEGIN TRANSACTION;
+ DROP INDEX i1;
+ SELECT * FROM t1 WHERE b<1;
+ ROLLBACK;
+ }
+} {1 -2 -3 4 -5 -6}
+do_test trans-6.33 {
+ execsql {
+ SELECT * FROM t1 WHERE b<1;
+ }
+} {4 -5 -6 1 -2 -3}
+do_test trans-6.34 {
+ execsql {
+ BEGIN TRANSACTION;
+ DROP TABLE t1;
+ ROLLBACK;
+ SELECT * FROM t1 WHERE b<1;
+ }
+} {4 -5 -6 1 -2 -3}
+
+do_test trans-6.35 {
+ execsql {
+ BEGIN TRANSACTION;
+ DROP INDEX i1;
+ CREATE INDEX i1 ON t1(c);
+ SELECT * FROM t1 WHERE b<1;
+ }
+} {1 -2 -3 4 -5 -6}
+do_test trans-6.36 {
+ execsql {
+ SELECT * FROM t1 WHERE c<1;
+ }
+} {4 -5 -6 1 -2 -3}
+do_test trans-6.37 {
+ execsql {
+ DROP INDEX i1;
+ SELECT * FROM t1 WHERE c<1;
+ }
+} {1 -2 -3 4 -5 -6}
+do_test trans-6.38 {
+ execsql {
+ ROLLBACK;
+ SELECT * FROM t1 WHERE b<1;
+ }
+} {4 -5 -6 1 -2 -3}
+do_test trans-6.39 {
+ execsql {
+ SELECT * FROM t1 WHERE c<1;
+ }
+} {1 -2 -3 4 -5 -6}
+integrity_check trans-6.40
+
+# Test to make sure rollback restores the database back to its original
+# state.
+#
+do_test trans-7.1 {
+ execsql {BEGIN}
+ for {set i 0} {$i<1000} {incr i} {
+ set r1 [expr {rand()}]
+ set r2 [expr {rand()}]
+ set r3 [expr {rand()}]
+ execsql "INSERT INTO t2 VALUES($r1,$r2,$r3)"
+ }
+ execsql {COMMIT}
+ set ::checksum [execsql {SELECT md5sum(x,y,z) FROM t2}]
+ set ::checksum2 [
+ execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
+ ]
+ execsql {SELECT count(*) FROM t2}
+} {1001}
+do_test trans-7.2 {
+ execsql {SELECT md5sum(x,y,z) FROM t2}
+} $checksum
+do_test trans-7.2.1 {
+ execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
+} $checksum2
+do_test trans-7.3 {
+ execsql {
+ BEGIN;
+ DELETE FROM t2;
+ ROLLBACK;
+ SELECT md5sum(x,y,z) FROM t2;
+ }
+} $checksum
+do_test trans-7.4 {
+ execsql {
+ BEGIN;
+ INSERT INTO t2 SELECT * FROM t2;
+ ROLLBACK;
+ SELECT md5sum(x,y,z) FROM t2;
+ }
+} $checksum
+do_test trans-7.5 {
+ execsql {
+ BEGIN;
+ DELETE FROM t2;
+ ROLLBACK;
+ SELECT md5sum(x,y,z) FROM t2;
+ }
+} $checksum
+do_test trans-7.6 {
+ execsql {
+ BEGIN;
+ INSERT INTO t2 SELECT * FROM t2;
+ ROLLBACK;
+ SELECT md5sum(x,y,z) FROM t2;
+ }
+} $checksum
+do_test trans-7.7 {
+ execsql {
+ BEGIN;
+ CREATE TABLE t3 AS SELECT * FROM t2;
+ INSERT INTO t2 SELECT * FROM t3;
+ ROLLBACK;
+ SELECT md5sum(x,y,z) FROM t2;
+ }
+} $checksum
+do_test trans-7.8 {
+ execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
+} $checksum2
+ifcapable tempdb {
+ do_test trans-7.9 {
+ execsql {
+ BEGIN;
+ CREATE TEMP TABLE t3 AS SELECT * FROM t2;
+ INSERT INTO t2 SELECT * FROM t3;
+ ROLLBACK;
+ SELECT md5sum(x,y,z) FROM t2;
+ }
+ } $checksum
+}
+do_test trans-7.10 {
+ execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
+} $checksum2
+ifcapable tempdb {
+ do_test trans-7.11 {
+ execsql {
+ BEGIN;
+ CREATE TEMP TABLE t3 AS SELECT * FROM t2;
+ INSERT INTO t2 SELECT * FROM t3;
+ DROP INDEX i2x;
+ DROP INDEX i2y;
+ CREATE INDEX i3a ON t3(x);
+ ROLLBACK;
+ SELECT md5sum(x,y,z) FROM t2;
+ }
+ } $checksum
+}
+do_test trans-7.12 {
+ execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
+} $checksum2
+ifcapable tempdb {
+ do_test trans-7.13 {
+ execsql {
+ BEGIN;
+ DROP TABLE t2;
+ ROLLBACK;
+ SELECT md5sum(x,y,z) FROM t2;
+ }
+ } $checksum
+}
+do_test trans-7.14 {
+ execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
+} $checksum2
+integrity_check trans-7.15
+wal_check_journal_mode trans-7.16
+
+# Arrange for another process to begin modifying the database but abort
+# and die in the middle of the modification. Then have this process read
+# the database. This process should detect the journal file and roll it
+# back. Verify that this happens correctly.
+#
+set fd [open test.tcl w]
+puts $fd {
+ sqlite3_test_control_pending_byte 0x0010000
+ sqlite3 db test.db
+ db eval {
+ PRAGMA default_cache_size=20;
+ BEGIN;
+ CREATE TABLE t3 AS SELECT * FROM t2;
+ DELETE FROM t2;
+ }
+ sqlite_abort
+}
+close $fd
+do_test trans-8.1 {
+ catch {exec [info nameofexec] test.tcl}
+ execsql {SELECT md5sum(x,y,z) FROM t2}
+} $checksum
+do_test trans-8.2 {
+ execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
+} $checksum2
+integrity_check trans-8.3
+set fd [open test.tcl w]
+puts $fd {
+ sqlite3_test_control_pending_byte 0x0010000
+ sqlite3 db test.db
+ db eval {
+ PRAGMA journal_mode=persist;
+ PRAGMA default_cache_size=20;
+ BEGIN;
+ CREATE TABLE t3 AS SELECT * FROM t2;
+ DELETE FROM t2;
+ }
+ sqlite_abort
+}
+close $fd
+do_test trans-8.4 {
+ catch {exec [info nameofexec] test.tcl}
+ execsql {SELECT md5sum(x,y,z) FROM t2}
+} $checksum
+do_test trans-8.5 {
+ execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
+} $checksum2
+integrity_check trans-8.6
+wal_check_journal_mode trans-8.7
+
+# In the following sequence of tests, compute the MD5 sum of the content
+# of a table, make lots of modifications to that table, then do a rollback.
+# Verify that after the rollback, the MD5 checksum is unchanged.
+#
+do_test trans-9.1 {
+ execsql {
+ PRAGMA default_cache_size=10;
+ }
+ db close
+ sqlite3 db test.db
+ execsql {
+ BEGIN;
+ CREATE TABLE t3(x TEXT);
+ INSERT INTO t3 VALUES(randstr(10,400));
+ INSERT INTO t3 VALUES(randstr(10,400));
+ INSERT INTO t3 SELECT randstr(10,400) FROM t3;
+ INSERT INTO t3 SELECT randstr(10,400) FROM t3;
+ INSERT INTO t3 SELECT randstr(10,400) FROM t3;
+ INSERT INTO t3 SELECT randstr(10,400) FROM t3;
+ INSERT INTO t3 SELECT randstr(10,400) FROM t3;
+ INSERT INTO t3 SELECT randstr(10,400) FROM t3;
+ INSERT INTO t3 SELECT randstr(10,400) FROM t3;
+ INSERT INTO t3 SELECT randstr(10,400) FROM t3;
+ INSERT INTO t3 SELECT randstr(10,400) FROM t3;
+ COMMIT;
+ SELECT count(*) FROM t3;
+ }
+} {1024}
+wal_check_journal_mode trans-9.1.1
+
+# The following procedure computes a "signature" for table "t3". If
+# T3 changes in any way, the signature should change.
+#
+# This is used to test ROLLBACK. We gather a signature for t3, then
+# make lots of changes to t3, then rollback and take another signature.
+# The two signatures should be the same.
+#
+proc signature {} {
+ return [db eval {SELECT count(*), md5sum(x) FROM t3}]
+}
+
+# Repeat the following group of tests 20 times for quick testing and
+# 40 times for full testing. Each iteration of the test makes table
+# t3 a little larger, and thus takes a little longer, so doing 40 tests
+# is more than 2.0 times slower than doing 20 tests. Considerably more.
+#
+# Also, if temporary tables are stored in memory and the test pcache
+# is in use, only 20 iterations. Otherwise the test pcache runs out
+# of page slots and SQLite reports "out of memory".
+#
+if {[info exists G(isquick)] || (
+ $TEMP_STORE==3 && [regexp {^pcache[[:digit:]]*$} [permutation]]
+) } {
+ set limit 20
+} elseif {[info exists G(issoak)]} {
+ set limit 100
+} else {
+ set limit 40
+}
+
+# Do rollbacks. Make sure the signature does not change.
+#
+for {set i 2} {$i<=$limit} {incr i} {
+ set ::sig [signature]
+ set cnt [lindex $::sig 0]
+ if {$i%2==0} {
+ execsql {PRAGMA fullfsync=ON}
+ } else {
+ execsql {PRAGMA fullfsync=OFF}
+ }
+ set sqlite_sync_count 0
+ set sqlite_fullsync_count 0
+ do_test trans-9.$i.1-$cnt {
+ execsql {
+ BEGIN;
+ DELETE FROM t3 WHERE random()%10!=0;
+ INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
+ INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
+ ROLLBACK;
+ }
+ signature
+ } $sig
+ do_test trans-9.$i.2-$cnt {
+ execsql {
+ BEGIN;
+ DELETE FROM t3 WHERE random()%10!=0;
+ INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
+ DELETE FROM t3 WHERE random()%10!=0;
+ INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
+ ROLLBACK;
+ }
+ signature
+ } $sig
+ if {$i<$limit} {
+ do_test trans-9.$i.3-$cnt {
+ execsql {
+ INSERT INTO t3 SELECT randstr(10,400) FROM t3 WHERE random()%10==0;
+ }
+ } {}
+ catch flush_async_queue
+ if {$tcl_platform(platform)=="unix"} {
+ do_test trans-9.$i.4-$cnt {
+ expr {$sqlite_sync_count>0}
+ } 1
+ ifcapable pager_pragmas {
+ do_test trans-9.$i.5-$cnt {
+ expr {$sqlite_fullsync_count>0}
+ } [expr {$i%2==0}]
+ } else {
+ do_test trans-9.$i.5-$cnt {
+ expr {$sqlite_fullsync_count==0}
+ } {1}
+ }
+ }
+ }
+
+ wal_check_journal_mode trans-9.$i.6-$cnt
+ set ::pager_old_format 0
+}
+
+finish_test
generated by cgit v1.2.3 (git 2.25.1) at 2024-06-09 03:50:30 +0000