SQLite

  } -shutdown {
    unregister_demovfs
  } -include {
    insert.test   insert2.test  insert3.test rollback.test 
    select1.test  select2.test  select3.test
  }
}

run_tests "wal" -description {
  Run tests with journal_mode=WAL
} -include {
  savepoint.test
  savepoint2.test
}

# End of tests
#############################################################################

if {$::perm::testmode eq "targets"} { puts "" ; exit }

# Restore the [sqlite3] command.

#***********************************************************************
#
# $Id: savepoint.test,v 1.13 2009/07/18 08:30:45 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.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.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 {

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

    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 {

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

            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');

        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;

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

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

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

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

} {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 13 14 15 16}
}

finish_test

#
#***********************************************************************
#
# $Id: savepoint2.test,v 1.5 2009/06/05 17:09:12 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl


# Tests in this file are quite similar to those run by trans.test and
# avtrans.test.
#

proc signature {} {
  return [db eval {SELECT count(*), md5sum(x) FROM t3}]
}

do_test savepoint2-1 {
  wal_set_journal_mode
  execsql {
    PRAGMA cache_size=10;
    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}
wal_check_journal_mode savepoint2-1.1

unset -nocomplain ::sig
unset -nocomplain SQL

set iterations 20

set SQL(1) {

  #
  do_test savepoint2-$ii.6 {
    execsql $SQL(4)
    execsql COMMIT
    sqlite3_get_autocommit db
  } {1}
  integrity_check savepoint2-$ii.6.1

  # Check that the connection is still running in WAL mode.
  wal_check_journal_mode savepoint2-$ii.7
}

unset -nocomplain ::sig
unset -nocomplain SQL

finish_test

      $db eval "DROP $type $t"
    }
  }
  ifcapable trigger&&foreignkey {
    $db eval "PRAGMA foreign_keys = $pk"
  }
}

#-------------------------------------------------------------------------
# If a test script is executed with global variable 
# $::permutations_test_prefix set to "wal", then the tests are run
# in WAL mode. Otherwise, they should be run in rollback mode. The 
# following Tcl procs are used to make this less intrusive:
#
#   wal_set_journal_mode ?DB?
#
#     If running a WAL test, execute "PRAGMA journal_mode = wal" using
#     connection handle DB. Otherwise, this command is a no-op.
#
#   wal_check_journal_mode TESTNAME ?DB?
#
#     If running a WAL test, execute a tests case that fails if the main
#     database for connection handle DB is not currently a WAL database.
#     Otherwise (if not running a WAL permutation) this is a no-op.
#
#   wal_is_wal_mode
#   
#     Returns true if this test should be run in WAL mode. False otherwise.
# 
proc wal_is_wal_mode {} {
  expr { [catch {set ::permutations_test_prefix} v]==0 && $v == "wal" }
}
proc wal_set_journal_mode {{db db}} {
  if { [wal_is_wal_mode] } {
    $db eval "PRAGMA journal_mode = WAL"
  }
}
proc wal_check_journal_mode {testname {db db}} {
  if { [wal_is_wal_mode] } {
    $db eval { SELECT * FROM sqlite_master }
    do_test $testname [list $db eval "PRAGMA main.journal_mode"] {wal}
  }
}


# If the library is compiled with the SQLITE_DEFAULT_AUTOVACUUM macro set
# to non-zero, then set the global variable $AUTOVACUUM to 1.
set AUTOVACUUM $sqlite_options(default_autovacuum)

source $testdir/thread_common.tcl