SQLite

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


# Create several tables to work with.
#
do_test avtrans-1.0 {
  execsql { PRAGMA auto_vacuum=ON }
  wal_set_journal_mode
  execsql { 

    CREATE TABLE one(a int PRIMARY KEY, b text);
    INSERT INTO one VALUES(1,'one');
    INSERT INTO one VALUES(2,'two');
    INSERT INTO one VALUES(3,'three');
    SELECT b FROM one ORDER BY a;
  }
} {one two three}

  sqlite3 altdb test.db
  execsql {SELECT b FROM one ORDER BY a} altdb
} {one two three}
do_test avtrans-1.10 {
  execsql {SELECT b FROM two ORDER BY a} altdb
} {I V X}
integrity_check avtrans-1.11
wal_check_journal_mode avtrans-1.12

# Basic transactions
#
do_test avtrans-2.1 {
  set v [catch {execsql {BEGIN}} msg]
  lappend v $msg
} {0 {}}

    BEGIN;
    SELECT a FROM one ORDER BY a;
    SELECT a FROM two ORDER BY a;
    END;
  }
} {1 2 3 1 5 10}
integrity_check avtrans-2.11
wal_check_journal_mode avtrans-2.12

# Check the locking behavior
#
sqlite3_soft_heap_limit 0
do_test avtrans-3.1 {
  execsql {
    BEGIN;

        } [expr {$i%2==0}]
      } else {
        do_test avtrans-9.$i.5-$cnt {
           expr {$sqlite_fullsync_count==0}
        } {1}
      }
    }
    wal_check_journal_mode avtrans-9.$i-6.$cnt
  }
  set ::pager_old_format 0
}
integrity_check avtrans-10.1
wal_check_journal_mode avtrans-10.2
   
finish_test

run_tests "wal" -description {
  Run tests with journal_mode=WAL
} -initialize {
  set ::savepoint6_iterations 100
} -shutdown {
  unset -nocomplain ::savepoint6_iterations
} -include {
  savepoint.test     savepoint2.test     savepoint6.test
  trans.test         avtrans.test

}

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

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

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

# Create several tables to work with.
#
wal_set_journal_mode
do_test trans-1.0 {
  execsql {
    CREATE TABLE one(a int PRIMARY KEY, b text);
    INSERT INTO one VALUES(1,'one');
    INSERT INTO one VALUES(2,'two');
    INSERT INTO one VALUES(3,'three');
    SELECT b FROM one ORDER BY a;

  sqlite3 altdb test.db
  execsql {SELECT b FROM one ORDER BY a} altdb
} {one two three}
do_test trans-1.10 {
  execsql {SELECT b FROM two ORDER BY a} altdb
} {I V X}
integrity_check trans-1.11
wal_check_journal_mode trans-1.12

# Basic transactions
#
do_test trans-2.1 {
  set v [catch {execsql {BEGIN}} msg]
  lappend v $msg
} {0 {}}

    BEGIN;
    SELECT a FROM one ORDER BY a;
    SELECT a FROM two ORDER BY a;
    END;
  }
} {1 2 3 1 5 10}
integrity_check trans-2.11
wal_check_journal_mode trans-2.12

# Check the locking behavior
#
do_test trans-3.1 {
  execsql {
    BEGIN;
    UPDATE one SET a = 0 WHERE 0;

do_test trans-3.14 {
  set v [catch {execsql {
    SELECT a FROM one ORDER BY a;
  } db} msg]
  lappend v $msg
} {0 {1 2 3 4}}
integrity_check trans-3.15
wal_check_journal_mode trans-3.16

do_test trans-4.1 {
  set v [catch {execsql {
    COMMIT;
  } db} msg]
  lappend v $msg
} {1 {cannot commit - no transaction is active}}

do_test trans-4.11 {
  set v [catch {execsql {
    SELECT a FROM one ORDER BY a;
  } altdb} msg]
  lappend v $msg
} {0 {1 2 3 4}}
integrity_check trans-4.12
wal_check_journal_mode trans-4.13
wal_check_journal_mode trans-4.14 altdb
do_test trans-4.98 {
  altdb close
  execsql {
    DROP TABLE one;
    DROP TABLE two;
  }
} {}

    }
  } $checksum
}
do_test trans-7.14 {
  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
} $checksum2
integrity_check trans-7.15
wal_check_journal_mode trans-7.16

# Arrange for another process to begin modifying the database but abort
# and die in the middle of the modification.  Then have this process read
# the database.  This process should detect the journal file and roll it
# back.  Verify that this happens correctly.
#
set fd [open test.tcl w]

  catch {exec [info nameofexec] test.tcl}
  execsql {SELECT md5sum(x,y,z) FROM t2}
} $checksum
do_test trans-8.5 {
  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
} $checksum2
integrity_check trans-8.6
wal_check_journal_mode trans-8.7

# In the following sequence of tests, compute the MD5 sum of the content
# of a table, make lots of modifications to that table, then do a rollback.
# Verify that after the rollback, the MD5 checksum is unchanged.
#
do_test trans-9.1 {
  execsql {

    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 trans-9.1.1

# The following procedure computes a "signature" for table "t3".  If
# T3 changes in any way, the signature should change.  
#
# This is used to test ROLLBACK.  We gather a signature for t3, then
# make lots of changes to t3, then rollback and take another signature.
# The two signatures should be the same.

      } else {
        do_test trans-9.$i.5-$cnt {
          expr {$sqlite_fullsync_count==0}
        } {1}
      }
    }
  }

  wal_check_journal_mode trans-9.$i.6-$cnt
  set ::pager_old_format 0
}
   
finish_test