summaryrefslogtreecommitdiffstats
path: root/test/savepoint.test
diff options
context:
space:
mode:
Diffstat (limited to 'test/savepoint.test')
-rw-r--r--test/savepoint.test1058
1 files changed, 1058 insertions, 0 deletions
diff --git a/test/savepoint.test b/test/savepoint.test
new file mode 100644
index 0000000..3952981
--- /dev/null
+++ b/test/savepoint.test
@@ -0,0 +1,1058 @@
+# 2008 December 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.
+#
+#***********************************************************************
+#
+# $Id: savepoint.test,v 1.13 2009/07/18 08:30:45 danielk1977 Exp $
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+source $testdir/lock_common.tcl
+source $testdir/malloc_common.tcl
+
+forcedelete test2.db
+
+#----------------------------------------------------------------------
+# The following tests - savepoint-1.* - test that the SAVEPOINT, RELEASE
+# and ROLLBACK TO comands are correctly parsed, and that the auto-commit
+# flag is correctly set and unset as a result.
+#
+do_test savepoint-1.1 {
+ wal_set_journal_mode
+ execsql {
+ SAVEPOINT sp1;
+ RELEASE sp1;
+ }
+} {}
+do_test savepoint-1.2 {
+ execsql {
+ SAVEPOINT sp1;
+ ROLLBACK TO sp1;
+ }
+} {}
+do_test savepoint-1.3 {
+ execsql { SAVEPOINT sp1 }
+ db close
+} {}
+sqlite3 db test.db
+do_test savepoint-1.4.1 {
+ execsql {
+ SAVEPOINT sp1;
+ SAVEPOINT sp2;
+ RELEASE sp1;
+ }
+ sqlite3_get_autocommit db
+} {1}
+do_test savepoint-1.4.2 {
+ execsql {
+ SAVEPOINT sp1;
+ SAVEPOINT sp2;
+ RELEASE sp2;
+ }
+ sqlite3_get_autocommit db
+} {0}
+do_test savepoint-1.4.3 {
+ execsql { RELEASE sp1 }
+ sqlite3_get_autocommit db
+} {1}
+do_test savepoint-1.4.4 {
+ execsql {
+ SAVEPOINT sp1;
+ SAVEPOINT sp2;
+ ROLLBACK TO sp1;
+ }
+ sqlite3_get_autocommit db
+} {0}
+do_test savepoint-1.4.5 {
+ execsql { RELEASE SAVEPOINT sp1 }
+ sqlite3_get_autocommit db
+} {1}
+do_test savepoint-1.4.6 {
+ execsql {
+ SAVEPOINT sp1;
+ SAVEPOINT sp2;
+ SAVEPOINT sp3;
+ ROLLBACK TO SAVEPOINT sp3;
+ ROLLBACK TRANSACTION TO sp2;
+ ROLLBACK TRANSACTION TO SAVEPOINT sp1;
+ }
+ sqlite3_get_autocommit db
+} {0}
+do_test savepoint-1.4.7 {
+ execsql { RELEASE SAVEPOINT SP1 }
+ sqlite3_get_autocommit db
+} {1}
+do_test savepoint-1.5 {
+ execsql {
+ SAVEPOINT sp1;
+ ROLLBACK TO sp1;
+ }
+} {}
+do_test savepoint-1.6 {
+ execsql COMMIT
+} {}
+wal_check_journal_mode savepoint-1.7
+
+#------------------------------------------------------------------------
+# These tests - savepoint-2.* - test rollbacks and releases of savepoints
+# with a very simple data set.
+#
+
+do_test savepoint-2.1 {
+ execsql {
+ CREATE TABLE t1(a, b, c);
+ BEGIN;
+ INSERT INTO t1 VALUES(1, 2, 3);
+ SAVEPOINT one;
+ UPDATE t1 SET a = 2, b = 3, c = 4;
+ }
+ execsql { SELECT * FROM t1 }
+} {2 3 4}
+do_test savepoint-2.2 {
+ execsql {
+ ROLLBACK TO one;
+ }
+ execsql { SELECT * FROM t1 }
+} {1 2 3}
+do_test savepoint-2.3 {
+ execsql {
+ INSERT INTO t1 VALUES(4, 5, 6);
+ }
+ execsql { SELECT * FROM t1 }
+} {1 2 3 4 5 6}
+do_test savepoint-2.4 {
+ execsql {
+ ROLLBACK TO one;
+ }
+ execsql { SELECT * FROM t1 }
+} {1 2 3}
+
+
+do_test savepoint-2.5 {
+ execsql {
+ INSERT INTO t1 VALUES(7, 8, 9);
+ SAVEPOINT two;
+ INSERT INTO t1 VALUES(10, 11, 12);
+ }
+ execsql { SELECT * FROM t1 }
+} {1 2 3 7 8 9 10 11 12}
+do_test savepoint-2.6 {
+ execsql {
+ ROLLBACK TO two;
+ }
+ execsql { SELECT * FROM t1 }
+} {1 2 3 7 8 9}
+do_test savepoint-2.7 {
+ execsql {
+ INSERT INTO t1 VALUES(10, 11, 12);
+ }
+ execsql { SELECT * FROM t1 }
+} {1 2 3 7 8 9 10 11 12}
+do_test savepoint-2.8 {
+ execsql {
+ ROLLBACK TO one;
+ }
+ execsql { SELECT * FROM t1 }
+} {1 2 3}
+do_test savepoint-2.9 {
+ execsql {
+ INSERT INTO t1 VALUES('a', 'b', 'c');
+ SAVEPOINT two;
+ INSERT INTO t1 VALUES('d', 'e', 'f');
+ }
+ execsql { SELECT * FROM t1 }
+} {1 2 3 a b c d e f}
+do_test savepoint-2.10 {
+ execsql {
+ RELEASE two;
+ }
+ execsql { SELECT * FROM t1 }
+} {1 2 3 a b c d e f}
+do_test savepoint-2.11 {
+ execsql {
+ ROLLBACK;
+ }
+ execsql { SELECT * FROM t1 }
+} {}
+wal_check_journal_mode savepoint-2.12
+
+#------------------------------------------------------------------------
+# This block of tests - savepoint-3.* - test that when a transaction
+# savepoint is rolled back, locks are not released from database files.
+# And that when a transaction savepoint is released, they are released.
+#
+# These tests do not work in WAL mode. WAL mode does not take RESERVED
+# locks on the database file.
+#
+if {[wal_is_wal_mode]==0} {
+ do_test savepoint-3.1 {
+ execsql { SAVEPOINT "transaction" }
+ execsql { PRAGMA lock_status }
+ } {main unlocked temp closed}
+
+ do_test savepoint-3.2 {
+ execsql { INSERT INTO t1 VALUES(1, 2, 3) }
+ execsql { PRAGMA lock_status }
+ } {main reserved temp closed}
+
+ do_test savepoint-3.3 {
+ execsql { ROLLBACK TO "transaction" }
+ execsql { PRAGMA lock_status }
+ } {main reserved temp closed}
+
+ do_test savepoint-3.4 {
+ execsql { INSERT INTO t1 VALUES(1, 2, 3) }
+ execsql { PRAGMA lock_status }
+ } {main reserved temp closed}
+
+ do_test savepoint-3.5 {
+ execsql { RELEASE "transaction" }
+ execsql { PRAGMA lock_status }
+ } {main unlocked temp closed}
+}
+
+#------------------------------------------------------------------------
+# Test that savepoints that include schema modifications are handled
+# correctly. Test cases savepoint-4.*.
+#
+do_test savepoint-4.1 {
+ execsql {
+ CREATE TABLE t2(d, e, f);
+ SELECT sql FROM sqlite_master;
+ }
+} {{CREATE TABLE t1(a, b, c)} {CREATE TABLE t2(d, e, f)}}
+do_test savepoint-4.2 {
+ execsql {
+ BEGIN;
+ CREATE TABLE t3(g,h);
+ INSERT INTO t3 VALUES('I', 'II');
+ SAVEPOINT one;
+ DROP TABLE t3;
+ }
+} {}
+do_test savepoint-4.3 {
+ execsql {
+ CREATE TABLE t3(g, h, i);
+ INSERT INTO t3 VALUES('III', 'IV', 'V');
+ }
+ execsql {SELECT * FROM t3}
+} {III IV V}
+do_test savepoint-4.4 {
+ execsql { ROLLBACK TO one; }
+ execsql {SELECT * FROM t3}
+} {I II}
+do_test savepoint-4.5 {
+ execsql {
+ ROLLBACK;
+ SELECT sql FROM sqlite_master;
+ }
+} {{CREATE TABLE t1(a, b, c)} {CREATE TABLE t2(d, e, f)}}
+
+do_test savepoint-4.6 {
+ execsql {
+ BEGIN;
+ INSERT INTO t1 VALUES('o', 't', 't');
+ SAVEPOINT sp1;
+ CREATE TABLE t3(a, b, c);
+ INSERT INTO t3 VALUES('z', 'y', 'x');
+ }
+ execsql {SELECT * FROM t3}
+} {z y x}
+do_test savepoint-4.7 {
+ execsql {
+ ROLLBACK TO sp1;
+ CREATE TABLE t3(a);
+ INSERT INTO t3 VALUES('value');
+ }
+ execsql {SELECT * FROM t3}
+} {value}
+do_test savepoint-4.8 {
+ execsql COMMIT
+} {}
+wal_check_journal_mode savepoint-4.9
+
+#------------------------------------------------------------------------
+# Test some logic errors to do with the savepoint feature.
+#
+
+ifcapable incrblob {
+ do_test savepoint-5.1.1 {
+ execsql {
+ CREATE TABLE blobs(x);
+ INSERT INTO blobs VALUES('a twentyeight character blob');
+ }
+ set fd [db incrblob blobs x 1]
+ puts -nonewline $fd "hello"
+ catchsql {SAVEPOINT abc}
+ } {1 {cannot open savepoint - SQL statements in progress}}
+ do_test savepoint-5.1.2 {
+ close $fd
+ catchsql {SAVEPOINT abc}
+ } {0 {}}
+
+ do_test savepoint-5.2 {
+ execsql {RELEASE abc}
+ catchsql {RELEASE abc}
+ } {1 {no such savepoint: abc}}
+
+ do_test savepoint-5.3.1 {
+ execsql {SAVEPOINT abc}
+ catchsql {ROLLBACK TO def}
+ } {1 {no such savepoint: def}}
+ do_test savepoint-5.3.2.1 {
+ execsql {SAVEPOINT def}
+ set fd [db incrblob -readonly blobs x 1]
+ set rc [catch {seek $fd 0;read $fd} res]
+ lappend rc $res
+ } {0 {hellontyeight character blob}}
+ do_test savepoint-5.3.2.2 {
+ catchsql {ROLLBACK TO def}
+ } {0 {}}
+ do_test savepoint-5.3.2.3 {
+ set rc [catch {seek $fd 0; read $fd} res]
+ set rc
+ } {0}
+ do_test savepoint-5.3.3 {
+ catchsql {RELEASE def}
+ } {0 {}}
+ do_test savepoint-5.3.4 {
+ close $fd
+ execsql {savepoint def}
+ set fd [db incrblob blobs x 1]
+ catchsql {release def}
+ } {1 {cannot release savepoint - SQL statements in progress}}
+ do_test savepoint-5.3.5 {
+ close $fd
+ execsql {release abc}
+ } {}
+
+ # Rollback mode:
+ #
+ # Open a savepoint transaction and insert a row into the database. Then,
+ # using a second database handle, open a read-only transaction on the
+ # database file. Check that the savepoint transaction cannot be committed
+ # until after the read-only transaction has been closed.
+ #
+ # WAL mode:
+ #
+ # As above, except that the savepoint transaction can be successfully
+ # committed before the read-only transaction has been closed.
+ #
+ do_test savepoint-5.4.1 {
+ execsql {
+ SAVEPOINT main;
+ INSERT INTO blobs VALUES('another blob');
+ }
+ } {}
+ do_test savepoint-5.4.2 {
+ sqlite3 db2 test.db
+ execsql { BEGIN ; SELECT count(*) FROM blobs } db2
+ } {1}
+ if {[wal_is_wal_mode]} {
+ do_test savepoint-5.4.3 { catchsql "RELEASE main" } {0 {}}
+ do_test savepoint-5.4.4 { db2 close } {}
+ } else {
+ do_test savepoint-5.4.3 {
+ catchsql { RELEASE main }
+ } {1 {database is locked}}
+ do_test savepoint-5.4.4 {
+ db2 close
+ catchsql { RELEASE main }
+ } {0 {}}
+ }
+ do_test savepoint-5.4.5 {
+ execsql { SELECT x FROM blobs WHERE rowid = 2 }
+ } {{another blob}}
+ do_test savepoint-5.4.6 {
+ execsql { SELECT count(*) FROM blobs }
+ } {2}
+}
+wal_check_journal_mode savepoint-5.5
+
+#-------------------------------------------------------------------------
+# The following tests, savepoint-6.*, test an incr-vacuum inside of a
+# couple of nested savepoints.
+#
+ifcapable {autovacuum && pragma} {
+ db close
+ forcedelete test.db
+ sqlite3 db test.db
+
+ do_test savepoint-6.1 {
+ execsql { PRAGMA auto_vacuum = incremental }
+ wal_set_journal_mode
+ execsql {
+ CREATE TABLE t1(a, b, c);
+ CREATE INDEX i1 ON t1(a, b);
+ BEGIN;
+ INSERT INTO t1 VALUES(randstr(10,400),randstr(10,400),randstr(10,400));
+ }
+ set r "randstr(10,400)"
+ for {set ii 0} {$ii < 10} {incr ii} {
+ execsql "INSERT INTO t1 SELECT $r, $r, $r FROM t1"
+ }
+ execsql { COMMIT }
+ } {}
+
+ integrity_check savepoint-6.2
+
+ do_test savepoint-6.3 {
+ execsql {
+ PRAGMA cache_size = 10;
+ BEGIN;
+ UPDATE t1 SET a = randstr(10,10) WHERE (rowid%4)==0;
+ SAVEPOINT one;
+ DELETE FROM t1 WHERE rowid%2;
+ PRAGMA incr_vacuum;
+ SAVEPOINT two;
+ INSERT INTO t1 SELECT randstr(10,400), randstr(10,400), c FROM t1;
+ DELETE FROM t1 WHERE rowid%2;
+ PRAGMA incr_vacuum;
+ ROLLBACK TO one;
+ COMMIT;
+ }
+ } {}
+
+ integrity_check savepoint-6.4
+
+ wal_check_journal_mode savepoint-6.5
+}
+
+#-------------------------------------------------------------------------
+# The following tests, savepoint-7.*, attempt to break the logic
+# surrounding savepoints by growing and shrinking the database file.
+#
+db close
+forcedelete test.db
+sqlite3 db test.db
+
+do_test savepoint-7.1 {
+ execsql { PRAGMA auto_vacuum = incremental }
+ wal_set_journal_mode
+ execsql {
+ PRAGMA cache_size = 10;
+ BEGIN;
+ CREATE TABLE t1(a PRIMARY KEY, b);
+ INSERT INTO t1(a) VALUES('alligator');
+ INSERT INTO t1(a) VALUES('angelfish');
+ INSERT INTO t1(a) VALUES('ant');
+ INSERT INTO t1(a) VALUES('antelope');
+ INSERT INTO t1(a) VALUES('ape');
+ INSERT INTO t1(a) VALUES('baboon');
+ INSERT INTO t1(a) VALUES('badger');
+ INSERT INTO t1(a) VALUES('bear');
+ INSERT INTO t1(a) VALUES('beetle');
+ INSERT INTO t1(a) VALUES('bird');
+ INSERT INTO t1(a) VALUES('bison');
+ UPDATE t1 SET b = randstr(1000,1000);
+ UPDATE t1 SET b = b||randstr(1000,1000);
+ UPDATE t1 SET b = b||randstr(1000,1000);
+ UPDATE t1 SET b = b||randstr(10,1000);
+ COMMIT;
+ }
+ expr ([execsql { PRAGMA page_count }] > 20)
+} {1}
+do_test savepoint-7.2.1 {
+ execsql {
+ BEGIN;
+ SAVEPOINT one;
+ CREATE TABLE t2(a, b);
+ INSERT INTO t2 SELECT a, b FROM t1;
+ ROLLBACK TO one;
+ }
+ execsql {
+ PRAGMA integrity_check;
+ }
+} {ok}
+do_test savepoint-7.2.2 {
+ execsql {
+ COMMIT;
+ PRAGMA integrity_check;
+ }
+} {ok}
+
+do_test savepoint-7.3.1 {
+ execsql {
+ CREATE TABLE t2(a, b);
+ INSERT INTO t2 SELECT a, b FROM t1;
+ }
+} {}
+do_test savepoint-7.3.2 {
+ execsql {
+ BEGIN;
+ SAVEPOINT one;
+ DELETE FROM t2;
+ PRAGMA incremental_vacuum;
+ SAVEPOINT two;
+ INSERT INTO t2 SELECT a, b FROM t1;
+ ROLLBACK TO two;
+ COMMIT;
+ }
+ execsql { PRAGMA integrity_check }
+} {ok}
+wal_check_journal_mode savepoint-7.3.3
+
+do_test savepoint-7.4.1 {
+ db close
+ forcedelete test.db
+ sqlite3 db test.db
+ execsql { PRAGMA auto_vacuum = incremental }
+ wal_set_journal_mode
+ execsql {
+ CREATE TABLE t1(a, b, PRIMARY KEY(a, b));
+ INSERT INTO t1 VALUES(randstr(1000,1000), randstr(1000,1000));
+ BEGIN;
+ DELETE FROM t1;
+ SAVEPOINT one;
+ PRAGMA incremental_vacuum;
+ ROLLBACK TO one;
+ COMMIT;
+ }
+
+ execsql { PRAGMA integrity_check }
+} {ok}
+
+do_test savepoint-7.5.1 {
+ execsql {
+ PRAGMA incremental_vacuum;
+ CREATE TABLE t5(x, y);
+ INSERT INTO t5 VALUES(1, randstr(1000,1000));
+ INSERT INTO t5 VALUES(2, randstr(1000,1000));
+ INSERT INTO t5 VALUES(3, randstr(1000,1000));
+
+ BEGIN;
+ INSERT INTO t5 VALUES(4, randstr(1000,1000));
+ INSERT INTO t5 VALUES(5, randstr(1000,1000));
+ DELETE FROM t5 WHERE x=1 OR x=2;
+ SAVEPOINT one;
+ PRAGMA incremental_vacuum;
+ SAVEPOINT two;
+ INSERT INTO t5 VALUES(1, randstr(1000,1000));
+ INSERT INTO t5 VALUES(2, randstr(1000,1000));
+ ROLLBACK TO two;
+ ROLLBACK TO one;
+ COMMIT;
+ PRAGMA integrity_check;
+ }
+} {ok}
+do_test savepoint-7.5.2 {
+ execsql {
+ DROP TABLE t5;
+ }
+} {}
+wal_check_journal_mode savepoint-7.5.3
+
+# Test oddly named and quoted savepoints.
+#
+do_test savepoint-8-1 {
+ execsql { SAVEPOINT "save1" }
+ execsql { RELEASE save1 }
+} {}
+do_test savepoint-8-2 {
+ execsql { SAVEPOINT "Including whitespace " }
+ execsql { RELEASE "including Whitespace " }
+} {}
+
+# Test that the authorization callback works.
+#
+ifcapable auth {
+ proc auth {args} {
+ eval lappend ::authdata [lrange $args 0 4]
+ return SQLITE_OK
+ }
+ db auth auth
+
+ do_test savepoint-9.1 {
+ set ::authdata [list]
+ execsql { SAVEPOINT sp1 }
+ set ::authdata
+ } {SQLITE_SAVEPOINT BEGIN sp1 {} {}}
+ do_test savepoint-9.2 {
+ set ::authdata [list]
+ execsql { ROLLBACK TO sp1 }
+ set ::authdata
+ } {SQLITE_SAVEPOINT ROLLBACK sp1 {} {}}
+ do_test savepoint-9.3 {
+ set ::authdata [list]
+ execsql { RELEASE sp1 }
+ set ::authdata
+ } {SQLITE_SAVEPOINT RELEASE sp1 {} {}}
+
+ proc auth {args} {
+ eval lappend ::authdata [lrange $args 0 4]
+ return SQLITE_DENY
+ }
+ db auth auth
+
+ do_test savepoint-9.4 {
+ set ::authdata [list]
+ set res [catchsql { SAVEPOINT sp1 }]
+ concat $::authdata $res
+ } {SQLITE_SAVEPOINT BEGIN sp1 {} {} 1 {not authorized}}
+ do_test savepoint-9.5 {
+ set ::authdata [list]
+ set res [catchsql { ROLLBACK TO sp1 }]
+ concat $::authdata $res
+ } {SQLITE_SAVEPOINT ROLLBACK sp1 {} {} 1 {not authorized}}
+ do_test savepoint-9.6 {
+ set ::authdata [list]
+ set res [catchsql { RELEASE sp1 }]
+ concat $::authdata $res
+ } {SQLITE_SAVEPOINT RELEASE sp1 {} {} 1 {not authorized}}
+
+ catch { db eval ROLLBACK }
+ db auth ""
+}
+
+#-------------------------------------------------------------------------
+# The following tests - savepoint-10.* - test the interaction of
+# savepoints and ATTACH statements.
+#
+
+# First make sure it is not possible to attach or detach a database while
+# a savepoint is open (it is not possible if any transaction is open).
+#
+# UPDATE 2017-07-26: It is not possible to ATTACH and DETACH within a
+# a transaction.
+#
+do_test savepoint-10.1.1 {
+ catchsql {
+ SAVEPOINT one;
+ ATTACH 'test2.db' AS aux;
+ DETACH aux;
+ }
+} {0 {}}
+do_test savepoint-10.1.2 {
+ execsql {
+ RELEASE one;
+ ATTACH 'test2.db' AS aux;
+ }
+ catchsql {
+ SAVEPOINT one;
+ DETACH aux;
+ ATTACH 'test2.db' AS aux;
+ }
+} {0 {}}
+do_test savepoint-10.1.3 {
+ execsql {
+ RELEASE one;
+ DETACH aux;
+ }
+} {}
+
+# The lock state of the TEMP database can vary if SQLITE_TEMP_STORE=3
+# And the following set of tests is only really interested in the status
+# of the aux1 and aux2 locks. So record the current lock status of
+# TEMP for use in the answers.
+set templockstate [lindex [db eval {PRAGMA lock_status}] 3]
+
+
+if {[wal_is_wal_mode]==0} {
+ do_test savepoint-10.2.1 {
+ forcedelete test3.db
+ forcedelete test2.db
+ execsql {
+ ATTACH 'test2.db' AS aux1;
+ ATTACH 'test3.db' AS aux2;
+ DROP TABLE t1;
+ CREATE TABLE main.t1(x, y);
+ CREATE TABLE aux1.t2(x, y);
+ CREATE TABLE aux2.t3(x, y);
+ SELECT name FROM sqlite_master;
+ SELECT name FROM aux1.sqlite_master;
+ SELECT name FROM aux2.sqlite_master;
+ }
+ } {t1 t2 t3}
+ do_test savepoint-10.2.2 {
+ execsql { PRAGMA lock_status }
+ } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked]
+
+ do_test savepoint-10.2.3 {
+ execsql {
+ SAVEPOINT one;
+ INSERT INTO t1 VALUES(1, 2);
+ PRAGMA lock_status;
+ }
+ } [list main reserved temp $templockstate aux1 unlocked aux2 unlocked]
+ do_test savepoint-10.2.4 {
+ execsql {
+ INSERT INTO t3 VALUES(3, 4);
+ PRAGMA lock_status;
+ }
+ } [list main reserved temp $templockstate aux1 unlocked aux2 reserved]
+ do_test savepoint-10.2.5 {
+ execsql {
+ SAVEPOINT two;
+ INSERT INTO t2 VALUES(5, 6);
+ PRAGMA lock_status;
+ }
+ } [list main reserved temp $templockstate aux1 reserved aux2 reserved]
+ do_test savepoint-10.2.6 {
+ execsql { SELECT * FROM t2 }
+ } {5 6}
+ do_test savepoint-10.2.7 {
+ execsql { ROLLBACK TO two }
+ execsql { SELECT * FROM t2 }
+ } {}
+ do_test savepoint-10.2.8 {
+ execsql { PRAGMA lock_status }
+ } [list main reserved temp $templockstate aux1 reserved aux2 reserved]
+ do_test savepoint-10.2.9 {
+ execsql { SELECT 'a', * FROM t1 ; SELECT 'b', * FROM t3 }
+ } {a 1 2 b 3 4}
+ do_test savepoint-10.2.9 {
+ execsql {
+ INSERT INTO t2 VALUES(5, 6);
+ RELEASE one;
+ }
+ execsql {
+ SELECT * FROM t1;
+ SELECT * FROM t2;
+ SELECT * FROM t3;
+ }
+ } {1 2 5 6 3 4}
+ do_test savepoint-10.2.9 {
+ execsql { PRAGMA lock_status }
+ } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked]
+
+ do_test savepoint-10.2.10 {
+ execsql {
+ SAVEPOINT one;
+ INSERT INTO t1 VALUES('a', 'b');
+ SAVEPOINT two;
+ INSERT INTO t2 VALUES('c', 'd');
+ SAVEPOINT three;
+ INSERT INTO t3 VALUES('e', 'f');
+ }
+ execsql {
+ SELECT * FROM t1;
+ SELECT * FROM t2;
+ SELECT * FROM t3;
+ }
+ } {1 2 a b 5 6 c d 3 4 e f}
+ do_test savepoint-10.2.11 {
+ execsql { ROLLBACK TO two }
+ execsql {
+ SELECT * FROM t1;
+ SELECT * FROM t2;
+ SELECT * FROM t3;
+ }
+ } {1 2 a b 5 6 3 4}
+ do_test savepoint-10.2.12 {
+ execsql {
+ INSERT INTO t3 VALUES('g', 'h');
+ ROLLBACK TO two;
+ }
+ execsql {
+ SELECT * FROM t1;
+ SELECT * FROM t2;
+ SELECT * FROM t3;
+ }
+ } {1 2 a b 5 6 3 4}
+ do_test savepoint-10.2.13 {
+ execsql { ROLLBACK }
+ execsql {
+ SELECT * FROM t1;
+ SELECT * FROM t2;
+ SELECT * FROM t3;
+ }
+ } {1 2 5 6 3 4}
+ do_test savepoint-10.2.14 {
+ execsql { PRAGMA lock_status }
+ } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked]
+}
+
+#-------------------------------------------------------------------------
+# The following tests - savepoint-11.* - test the interaction of
+# savepoints and creating or dropping tables and indexes in
+# auto-vacuum mode.
+#
+do_test savepoint-11.1 {
+ db close
+ forcedelete test.db
+ sqlite3 db test.db
+ execsql { PRAGMA auto_vacuum = full; }
+ wal_set_journal_mode
+ execsql {
+ CREATE TABLE t1(a, b, UNIQUE(a, b));
+ INSERT INTO t1 VALUES(1, randstr(1000,1000));
+ INSERT INTO t1 VALUES(2, randstr(1000,1000));
+ }
+} {}
+do_test savepoint-11.2 {
+ execsql {
+ SAVEPOINT one;
+ CREATE TABLE t2(a, b, UNIQUE(a, b));
+ SAVEPOINT two;
+ CREATE TABLE t3(a, b, UNIQUE(a, b));
+ }
+} {}
+integrity_check savepoint-11.3
+do_test savepoint-11.4 {
+ execsql { ROLLBACK TO two }
+} {}
+integrity_check savepoint-11.5
+do_test savepoint-11.6 {
+ execsql {
+ CREATE TABLE t3(a, b, UNIQUE(a, b));
+ ROLLBACK TO one;
+ }
+} {}
+integrity_check savepoint-11.7
+do_test savepoint-11.8 {
+ execsql { ROLLBACK }
+ execsql { PRAGMA wal_checkpoint }
+ file size test.db
+} {8192}
+
+do_test savepoint-11.9 {
+ execsql {
+ DROP TABLE IF EXISTS t1;
+ DROP TABLE IF EXISTS t2;
+ DROP TABLE IF EXISTS t3;
+ }
+} {}
+do_test savepoint-11.10 {
+ execsql {
+ BEGIN;
+ CREATE TABLE t1(a, b);
+ CREATE TABLE t2(x, y);
+ INSERT INTO t2 VALUES(1, 2);
+ SAVEPOINT one;
+ INSERT INTO t2 VALUES(3, 4);
+ SAVEPOINT two;
+ DROP TABLE t1;
+ ROLLBACK TO two;
+ }
+ execsql {SELECT * FROM t2}
+} {1 2 3 4}
+do_test savepoint-11.11 {
+ execsql COMMIT
+} {}
+do_test savepoint-11.12 {
+ execsql {SELECT * FROM t2}
+} {1 2 3 4}
+wal_check_journal_mode savepoint-11.13
+
+#-------------------------------------------------------------------------
+# The following tests - savepoint-12.* - test the interaction of
+# savepoints and "ON CONFLICT ROLLBACK" clauses.
+#
+do_test savepoint-12.1 {
+ execsql {
+ CREATE TABLE t4(a PRIMARY KEY, b);
+ INSERT INTO t4 VALUES(1, 'one');
+ }
+} {}
+do_test savepoint-12.2 {
+ # The final statement of the following SQL hits a constraint when the
+ # conflict handling mode is "OR ROLLBACK" and there are a couple of
+ # open savepoints. At one point this would fail to clear the internal
+ # record of the open savepoints, resulting in an assert() failure
+ # later on.
+ #
+ catchsql {
+ BEGIN;
+ INSERT INTO t4 VALUES(2, 'two');
+ SAVEPOINT sp1;
+ INSERT INTO t4 VALUES(3, 'three');
+ SAVEPOINT sp2;
+ INSERT OR ROLLBACK INTO t4 VALUES(1, 'one');
+ }
+} {1 {UNIQUE constraint failed: t4.a}}
+do_test savepoint-12.3 {
+ sqlite3_get_autocommit db
+} {1}
+do_test savepoint-12.4 {
+ execsql { SAVEPOINT one }
+} {}
+wal_check_journal_mode savepoint-12.5
+
+#-------------------------------------------------------------------------
+# The following tests - savepoint-13.* - test the interaction of
+# savepoints and "journal_mode = off".
+#
+if {[wal_is_wal_mode]==0} {
+ do_test savepoint-13.1 {
+ db close
+ catch {forcedelete test.db}
+ sqlite3 db test.db
+ execsql {
+ BEGIN;
+ CREATE TABLE t1(a PRIMARY KEY, b);
+ INSERT INTO t1 VALUES(1, 2);
+ COMMIT;
+ PRAGMA journal_mode = off;
+ }
+ } {off}
+ do_test savepoint-13.2 {
+ execsql {
+ BEGIN;
+ INSERT INTO t1 VALUES(3, 4);
+ INSERT INTO t1 SELECT a+4,b+4 FROM t1;
+ COMMIT;
+ }
+ } {}
+ do_test savepoint-13.3 {
+ execsql {
+ BEGIN;
+ INSERT INTO t1 VALUES(9, 10);
+ SAVEPOINT s1;
+ INSERT INTO t1 VALUES(11, 12);
+ COMMIT;
+ }
+ } {}
+ do_test savepoint-13.4 {
+ execsql {
+ BEGIN;
+ INSERT INTO t1 VALUES(13, 14);
+ SAVEPOINT s1;
+ INSERT INTO t1 VALUES(15, 16);
+ ROLLBACK TO s1;
+ ROLLBACK;
+ SELECT * FROM t1;
+ }
+ } {1 2 3 4 5 6 7 8 9 10 11 12}
+}
+
+db close
+delete_file test.db
+do_multiclient_test tn {
+ do_test savepoint-14.$tn.1 {
+ sql1 {
+ CREATE TABLE foo(x);
+ INSERT INTO foo VALUES(1);
+ INSERT INTO foo VALUES(2);
+ }
+ sql2 {
+ BEGIN;
+ SELECT * FROM foo;
+ }
+ } {1 2}
+ do_test savepoint-14.$tn.2 {
+ sql1 {
+ SAVEPOINT one;
+ INSERT INTO foo VALUES(1);
+ }
+ csql1 { RELEASE one }
+ } {1 {database is locked}}
+ do_test savepoint-14.$tn.3 {
+ sql1 { ROLLBACK TO one }
+ sql2 { COMMIT }
+ sql1 { RELEASE one }
+ } {}
+
+ do_test savepoint-14.$tn.4 {
+ sql2 {
+ BEGIN;
+ SELECT * FROM foo;
+ }
+ } {1 2}
+ do_test savepoint-14.$tn.5 {
+ sql1 {
+ SAVEPOINT one;
+ INSERT INTO foo VALUES(1);
+ }
+ csql1 { RELEASE one }
+ } {1 {database is locked}}
+ do_test savepoint-14.$tn.6 {
+ sql2 { COMMIT }
+ sql1 {
+ ROLLBACK TO one;
+ INSERT INTO foo VALUES(3);
+ INSERT INTO foo VALUES(4);
+ INSERT INTO foo VALUES(5);
+ RELEASE one;
+ }
+ } {}
+ do_test savepoint-14.$tn.7 {
+ sql2 { CREATE INDEX fooidx ON foo(x); }
+ sql3 { PRAGMA integrity_check }
+ } {ok}
+}
+
+do_multiclient_test tn {
+ do_test savepoint-15.$tn.1 {
+ sql1 {
+ CREATE TABLE foo(x);
+ INSERT INTO foo VALUES(1);
+ INSERT INTO foo VALUES(2);
+ }
+ sql2 { BEGIN; SELECT * FROM foo; }
+ } {1 2}
+ do_test savepoint-15.$tn.2 {
+ sql1 {
+ PRAGMA locking_mode = EXCLUSIVE;
+ BEGIN;
+ INSERT INTO foo VALUES(3);
+ }
+ csql1 { COMMIT }
+ } {1 {database is locked}}
+ do_test savepoint-15.$tn.3 {
+ sql1 { ROLLBACK }
+ sql2 { COMMIT }
+ sql1 {
+ INSERT INTO foo VALUES(3);
+ PRAGMA locking_mode = NORMAL;
+ INSERT INTO foo VALUES(4);
+ }
+ sql2 { CREATE INDEX fooidx ON foo(x); }
+ sql3 { PRAGMA integrity_check }
+ } {ok}
+}
+
+do_multiclient_test tn {
+ do_test savepoint-16.$tn.1 {
+ sql1 {
+ CREATE TABLE foo(x);
+ INSERT INTO foo VALUES(1);
+ INSERT INTO foo VALUES(2);
+ }
+ } {}
+ do_test savepoint-16.$tn.2 {
+
+ db eval {SELECT * FROM foo} {
+ sql1 { INSERT INTO foo VALUES(3) }
+ sql2 { SELECT * FROM foo }
+ sql1 { INSERT INTO foo VALUES(4) }
+ break
+ }
+
+ sql2 { CREATE INDEX fooidx ON foo(x); }
+ sql3 { PRAGMA integrity_check }
+ } {ok}
+ do_test savepoint-16.$tn.3 {
+ sql1 { SELECT * FROM foo }
+ } {1 2 3 4}
+}
+
+#-------------------------------------------------------------------------
+# This next block of tests verifies that a problem reported on the mailing
+# list has been resolved. At one point the second "CREATE TABLE t6" would
+# fail as table t6 still existed in the internal cache of the db schema
+# (even though it had been removed from the database by the ROLLBACK
+# command).
+#
+sqlite3 db test.db
+do_execsql_test savepoint-17.1 {
+ BEGIN;
+ CREATE TABLE t6(a, b);
+ INSERT INTO t6 VALUES(1, 2);
+ SAVEPOINT one;
+ INSERT INTO t6 VALUES(3, 4);
+ ROLLBACK TO one;
+ SELECT * FROM t6;
+ ROLLBACK;
+} {1 2}
+
+do_execsql_test savepoint-17.2 {
+ CREATE TABLE t6(a, b);
+} {}
+
+finish_test
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-05 17:23:07 +0000