Get `gn gen` to succeed on Windows

third_party/sqlite/src/test/savepoint.test

+# 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
+
+#----------------------------------------------------------------------
+# 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 {
+    execsql  {SAVEPOINT def}
+    set fd [db incrblob -readonly blobs x 1]
+    catchsql {ROLLBACK TO def}
+  } {1 {cannot rollback savepoint - SQL statements in progress}}
+  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
+  file delete -force 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
+file delete -force 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
+  file delete -force 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 $args
+    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 $args
+    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).
+#
+do_test savepoint-10.1.1 {
+  catchsql {
+    SAVEPOINT one;
+    ATTACH 'test2.db' AS aux;
+  }
+} {1 {cannot ATTACH database within transaction}}
+do_test savepoint-10.1.2 {
+  execsql {
+    RELEASE one;
+    ATTACH 'test2.db' AS aux;
+  }
+  catchsql {
+    SAVEPOINT one;
+    DETACH aux;
+  }
+} {1 {cannot DETACH database within transaction}}
+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 {
+    file delete -force test3.db
+    file delete -force 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 
+        UNION ALL
+      SELECT name FROM aux1.sqlite_master 
+        UNION ALL
+      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 UNION ALL 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
+  file delete -force 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 {column a is not unique}}
+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 {file delete -force 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
+file delete 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