[sql] Import reference version of SQLite 3.17..

third_party/sqlite/sqlite-src-3170000/test/snapshot.test

+# 2015 December 7
+#
+# 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 file is the sqlite3_snapshot_xxx() APIs.
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+ifcapable !snapshot {finish_test; return}
+set testprefix snapshot
+
+# This test does not work with the inmemory_journal permutation. The reason
+# is that each connection opened as part of this permutation executes
+# "PRAGMA journal_mode=memory", which fails if the database is in wal mode
+# and there are one or more existing connections.
+if {[permutation]=="inmemory_journal"} {
+  finish_test
+  return
+}
+
+foreach {tn tcl} {
+  1 {
+    proc snapshot_get {DB DBNAME} {
+      uplevel [list sqlite3_snapshot_get $DB $DBNAME]
+    }
+    proc snapshot_open {DB DBNAME SNAPSHOT} {
+      uplevel [list sqlite3_snapshot_open $DB $DBNAME $SNAPSHOT]
+    }
+    proc snapshot_free {SNAPSHOT} {
+      uplevel [list sqlite3_snapshot_free $SNAPSHOT]
+    }
+    proc snapshot_cmp {SNAPSHOT1 SNAPSHOT2} {
+      uplevel [list sqlite3_snapshot_cmp $SNAPSHOT1 $SNAPSHOT2]
+    }
+  }
+
+  2 {
+    proc snapshot_get {DB DBNAME} {
+      uplevel [list sqlite3_snapshot_get_blob $DB $DBNAME]
+    }
+    proc snapshot_open {DB DBNAME SNAPSHOT} {
+      uplevel [list sqlite3_snapshot_open_blob $DB $DBNAME $SNAPSHOT]
+    }
+    proc snapshot_free {SNAPSHOT} {
+    }
+    proc snapshot_cmp {SNAPSHOT1 SNAPSHOT2} {
+      uplevel [list sqlite3_snapshot_cmp_blob $SNAPSHOT1 $SNAPSHOT2]
+    }
+  }
+} {
+
+  reset_db
+  eval $tcl
+
+  #-------------------------------------------------------------------------
+  # Check some error conditions in snapshot_get(). It is an error if:
+  #
+  #  1) snapshot_get() is called on a non-WAL database, or
+  #  2) there is an open write transaction on the database.
+  #  3) the database handle is in auto-commit mode
+  #
+  do_execsql_test $tn.1.0 {
+    CREATE TABLE t1(a, b);
+    INSERT INTO t1 VALUES(1, 2);
+    INSERT INTO t1 VALUES(3, 4);
+  }
+
+  do_test $tn.1.1.1 {
+    execsql { BEGIN; SELECT * FROM t1; }
+    list [catch { snapshot_get db main } msg] $msg
+  } {1 SQLITE_ERROR}
+  do_execsql_test $tn.1.1.2 COMMIT
+
+  do_test $tn.1.2.1 {
+    execsql {
+      PRAGMA journal_mode = WAL;
+      BEGIN;
+        INSERT INTO t1 VALUES(5, 6);
+        INSERT INTO t1 VALUES(7, 8);
+    }
+    list [catch { snapshot_get db main } msg] $msg
+  } {1 SQLITE_ERROR}
+  do_execsql_test $tn.1.2.2 COMMIT
+
+  do_test $tn.1.3.1 {
+    list [catch { snapshot_get db main } msg] $msg
+  } {1 SQLITE_ERROR}
+  do_test $tn.1.3.2 {
+    db trans { set snap [snapshot_get db main] }
+    snapshot_free $snap
+  } {}
+
+  #-------------------------------------------------------------------------
+  # Check that a simple case works. Reuse the database created by the
+  # block of tests above.
+  #
+  do_execsql_test $tn.2.1.0 {
+    BEGIN;
+      SELECT * FROM t1;
+  } {1 2 3 4 5 6 7 8}
+
+  do_test $tn.2.1.1 {
+    set snapshot [snapshot_get db main]
+    execsql {
+      COMMIT;
+      INSERT INTO t1 VALUES(9, 10);
+      SELECT * FROM t1;
+    }
+  } {1 2 3 4 5 6 7 8 9 10}
+
+  do_test $tn.2.1.2 {
+    execsql BEGIN
+    snapshot_open db main $snapshot
+    execsql {
+      SELECT * FROM t1;
+    }
+  } {1 2 3 4 5 6 7 8}
+
+  do_test $tn.2.1.3 {
+    snapshot_free $snapshot
+    execsql COMMIT
+  } {}
+
+  do_test $tn.2.2.0 {
+    sqlite3 db2 test.db
+    execsql {
+      BEGIN;
+        SELECT * FROM t1;
+    } db2
+  } {1 2 3 4 5 6 7 8 9 10}
+
+  do_test $tn.2.2.1 {
+    set snapshot [snapshot_get db2 main]
+    execsql {
+      INSERT INTO t1 VALUES(11, 12);
+      SELECT * FROM t1;
+    }
+  } {1 2 3 4 5 6 7 8 9 10 11 12}
+
+  do_test $tn.2.2.2 {
+    execsql BEGIN
+    snapshot_open db main $snapshot
+    execsql {
+      SELECT * FROM t1;
+    }
+  } {1 2 3 4 5 6 7 8 9 10}
+
+  do_test $tn.2.2.3 {
+    snapshot_free $snapshot
+    execsql COMMIT
+    execsql COMMIT db2
+    db2 close
+  } {}
+
+  do_test $tn.2.3.1 {
+    execsql { DELETE FROM t1 WHERE a>6 }
+    db trans { set snapshot [snapshot_get db main] }
+    execsql {
+      INSERT INTO t1 VALUES('a', 'b');
+      INSERT INTO t1 VALUES('c', 'd');
+      SELECT * FROM t1;
+    }
+  } {1 2 3 4 5 6 a b c d}
+  do_test $tn.2.3.2 {
+    execsql BEGIN
+    snapshot_open db main $snapshot
+    execsql { SELECT * FROM t1 }
+  } {1 2 3 4 5 6}
+
+  do_test $tn.2.3.3 {
+    catchsql {
+      INSERT INTO t1 VALUES('x','y')
+    }
+  } {1 {database is locked}}
+  do_test $tn.2.3.4 {
+    execsql COMMIT
+    snapshot_free $snapshot
+  } {}
+
+  #-------------------------------------------------------------------------
+  # Check some errors in snapshot_open(). It is an error if:
+  #
+  #   1) the db is in auto-commit mode,
+  #   2) the db has an open (read or write) transaction,
+  #   3) the db is not a wal database,
+  #
+  # Reuse the database created by earlier tests.
+  #
+  do_execsql_test $tn.3.0.0 {
+    CREATE TABLE t2(x, y);
+    INSERT INTO t2 VALUES('a', 'b');
+    INSERT INTO t2 VALUES('c', 'd');
+    BEGIN;
+      SELECT * FROM t2;
+  } {a b c d}
+  do_test $tn.3.0.1 {
+    set snapshot [snapshot_get db main]
+    execsql { COMMIT }
+    execsql { INSERT INTO t2 VALUES('e', 'f'); }
+  } {}
+
+  do_test $tn.3.1 {
+    list [catch {snapshot_open db main $snapshot } msg] $msg
+  } {1 SQLITE_ERROR}
+
+  do_test $tn.3.2.1 {
+    execsql {
+      BEGIN;
+        SELECT * FROM t2;
+    }
+  } {a b c d e f}
+  do_test $tn.3.2.2 {
+    list [catch {snapshot_open db main $snapshot } msg] $msg
+  } {1 SQLITE_ERROR}
+
+  do_test $tn.3.2.3 {
+    execsql {
+      COMMIT;
+      BEGIN;
+        INSERT INTO t2 VALUES('g', 'h');
+    }
+    list [catch {snapshot_open db main $snapshot } msg] $msg
+  } {1 SQLITE_ERROR}
+  do_execsql_test $tn.3.2.4 COMMIT
+
+  do_test $tn.3.3.1 {
+    execsql { PRAGMA journal_mode = DELETE }
+    execsql { BEGIN }
+    list [catch {snapshot_open db main $snapshot } msg] $msg
+  } {1 SQLITE_ERROR}
+
+  do_test $tn.$tn.3.3.2 {
+    snapshot_free $snapshot
+    execsql COMMIT
+  } {}
+
+  #-------------------------------------------------------------------------
+  # Check that SQLITE_BUSY_SNAPSHOT is returned if the specified snapshot
+  # no longer exists because the wal file has been checkpointed.
+  #
+  #   1. Reading a snapshot from the middle of a wal file is not possible
+  #      after the wal file has been checkpointed.
+  #
+  #   2. That a snapshot from the end of a wal file can not be read once
+  #      the wal file has been wrapped.
+  #
+  do_execsql_test $tn.4.1.0 {
+    PRAGMA journal_mode = wal;
+    CREATE TABLE t3(i, j);
+    INSERT INTO t3 VALUES('o', 't');
+    INSERT INTO t3 VALUES('t', 'f');
+    BEGIN;
+      SELECT * FROM t3;
+  } {wal o t t f}
+
+  do_test $tn.4.1.1 {
+    set snapshot [snapshot_get db main]
+    execsql COMMIT
+  } {}
+  do_test $tn.4.1.2 {
+    execsql { 
+      INSERT INTO t3 VALUES('f', 's'); 
+      BEGIN;
+    }
+    snapshot_open db main $snapshot
+    execsql { SELECT * FROM t3 }
+  } {o t t f}
+
+  do_test $tn.4.1.3 {
+    execsql { 
+      COMMIT;
+      PRAGMA wal_checkpoint;
+      BEGIN;
+    }
+    list [catch {snapshot_open db main $snapshot} msg] $msg
+  } {1 SQLITE_BUSY_SNAPSHOT}
+  do_test $tn.4.1.4 {
+    snapshot_free $snapshot
+    execsql COMMIT
+  } {}
+
+  do_test $tn.4.2.1 {
+    execsql {
+      INSERT INTO t3 VALUES('s', 'e');
+      INSERT INTO t3 VALUES('n', 't');
+      BEGIN;
+        SELECT * FROM t3;
+    }
+  } {o t t f f s s e n t}
+  do_test $tn.4.2.2 {
+    set snapshot [snapshot_get db main]
+    execsql {
+      COMMIT;
+      PRAGMA wal_checkpoint;
+      BEGIN;
+    }
+    snapshot_open db main $snapshot
+    execsql { SELECT * FROM t3 }
+  } {o t t f f s s e n t}
+  do_test $tn.4.2.3 {
+    execsql {
+      COMMIT;
+      INSERT INTO t3 VALUES('e', 't');
+      BEGIN;
+    }
+    list [catch {snapshot_open db main $snapshot} msg] $msg
+  } {1 SQLITE_BUSY_SNAPSHOT}
+  do_test $tn.4.2.4 {
+    snapshot_free $snapshot
+  } {}
+
+  #-------------------------------------------------------------------------
+  # Check that SQLITE_BUSY is returned if a checkpoint is running when
+  # sqlite3_snapshot_open() is called.
+  #
+  reset_db
+  db close
+  testvfs tvfs
+  sqlite3 db test.db -vfs tvfs
+
+  do_execsql_test $tn.5.1 {
+    PRAGMA journal_mode = wal;
+    CREATE TABLE x1(x, xx, xxx);
+    INSERT INTO x1 VALUES('z', 'zz', 'zzz');
+    BEGIN;
+      SELECT * FROM x1;
+  } {wal z zz zzz}
+
+  do_test $tn.5.2 {
+    set ::snapshot [snapshot_get db main]
+    sqlite3 db2 test.db -vfs tvfs
+    execsql {
+      INSERT INTO x1 VALUES('a', 'aa', 'aaa');
+      COMMIT;
+    }
+  } {}
+
+  set t53 0
+  proc write_callback {args} {
+    do_test $tn.5.3.[incr ::t53] {
+      execsql BEGIN
+      list [catch { snapshot_open db main $::snapshot } msg] $msg
+    } {1 SQLITE_BUSY}
+    catchsql COMMIT
+  }
+
+  tvfs filter xWrite
+  tvfs script write_callback
+  db2 eval { PRAGMA wal_checkpoint }
+  db close
+  db2 close
+  tvfs delete
+  snapshot_free $snapshot
+
+  #-------------------------------------------------------------------------
+  # Test that sqlite3_snapshot_get() may be called immediately after
+  # "BEGIN; PRAGMA user_version;". And that sqlite3_snapshot_open() may
+  # be called after opening the db handle and running the script
+  # "PRAGMA user_version; BEGIN".
+  reset_db
+  do_execsql_test $tn.6.1 {
+    PRAGMA journal_mode = wal;
+    CREATE TABLE x1(x, xx, xxx);
+    INSERT INTO x1 VALUES('z', 'zz', 'zzz');
+    BEGIN;
+      PRAGMA user_version;
+  } {wal 0}
+  do_test $tn.6.2 {
+    set ::snapshot [snapshot_get db main]
+    execsql {
+      INSERT INTO x1 VALUES('a', 'aa', 'aaa');
+      COMMIT;
+    }
+  } {}
+  do_test $tn.6.3 {
+    sqlite3 db2 test.db 
+    db2 eval "PRAGMA user_version ; BEGIN"
+    snapshot_open db2 main $::snapshot
+    db2 eval { SELECT * FROM x1 }
+  } {z zz zzz}
+  do_test $tn.6.4 {
+    db2 close
+    sqlite3 db2 test.db 
+    db2 eval "PRAGMA application_id"
+    db2 eval "BEGIN"
+    snapshot_open db2 main $::snapshot
+    db2 eval { SELECT * FROM x1 }
+  } {z zz zzz}
+
+  do_test $tn.6.5 {
+    db2 close
+    sqlite3 db2 test.db 
+    db2 eval "BEGIN"
+    list [catch {snapshot_open db2 main $::snapshot} msg] $msg
+  } {1 SQLITE_ERROR}
+
+  snapshot_free $snapshot
+
+  #-------------------------------------------------------------------------
+  # The following tests investigate the sqlite3_snapshot_cmp() API.
+  #
+
+  # Compare snapshots $p1 and $p2, checking that the result is $r.
+  #
+  proc do_snapshot_cmp_test {tn p1 p2 r} {
+    uplevel [list do_test $tn.1 [list snapshot_cmp $p1 $p2] $r]
+    uplevel [list do_test $tn.2 [list snapshot_cmp $p2 $p1] [expr $r*-1]]
+    uplevel [list do_test $tn.3 [list snapshot_cmp $p1 $p1] 0]
+    uplevel [list do_test $tn.4 [list snapshot_cmp $p2 $p2] 0]
+  }
+
+  catch { db2 close }
+  reset_db
+
+  do_execsql_test $tn.7.1 {
+    PRAGMA journal_mode = wal;
+    CREATE TABLE t1(x);
+  } wal
+
+  do_test $tn.7.1.2 {
+    execsql { BEGIN ; PRAGMA application_id }
+    set p1 [snapshot_get db main]
+    execsql {
+      INSERT INTO t1 VALUES(10);
+      COMMIT;
+    }
+    execsql { BEGIN ; PRAGMA application_id }
+    set p2 [snapshot_get db main]
+    execsql COMMIT
+  } {}
+
+  do_snapshot_cmp_test $tn.7.1.3 $p1 $p2 -1
+  snapshot_free $p1
+  snapshot_free $p2
+
+  do_execsql_test $tn.7.2.1 {
+    INSERT INTO t1 VALUES(11);
+    INSERT INTO t1 VALUES(12);
+    INSERT INTO t1 VALUES(13);
+    BEGIN; 
+      PRAGMA application_id;
+  } {0}
+  do_test $tn.7.2.2 {
+    set p1 [snapshot_get db main]
+    execsql {
+      COMMIT;
+      INSERT INTO t1 VALUES(14);
+      PRAGMA wal_checkpoint;
+      BEGIN;
+        PRAGMA application_id;
+    }
+    set p2 [snapshot_get db main]
+    execsql COMMIT
+  } {}
+
+  do_snapshot_cmp_test $tn.7.2.3 $p1 $p2 -1
+  snapshot_free $p2
+
+  do_test $tn.7.3.1 {
+    execsql {
+      INSERT INTO t1 VALUES(14);
+      BEGIN;
+        PRAGMA application_id;
+    }
+    set p2 [snapshot_get db main]
+    execsql COMMIT
+  } {}
+
+  do_snapshot_cmp_test $tn.7.3.2 $p1 $p2 -1
+  snapshot_free $p1
+  snapshot_free $p2
+}
+
+finish_test