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