# 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.38 2008/04/19 20:34:19 drh Exp $

 set testdir [file dirname $argv0]

 source $testdir/tester.tcl

 # Create several tables to work with.

 #

 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

 # 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

 # 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

 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

 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

 # 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.

 #

 print_text "TclSqlite3-2" "begin"

 set fd [open test.tcl w]

 puts $fd {

   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

 print_text "TclSqlite3-2" "end"

 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

 print_text "TclSqlite3-2" "begin"

 set fd [open test.tcl w]

 puts $fd {

   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

 print_text "TclSqlite3-2" "end"

 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

 # 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}

 # 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.

 #

 if {[info exists ISQUICK]} {

   set limit 20

 } elseif {[info exists SOAKTEST]} {

   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;

        }

     } {}

     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}

       }

     }

   }

   set ::pager_old_format 0

 }

 finish_test