test/server2.test

# 2017 April 25
#
# 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 script is testing the server mode of SQLite.
#


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

source $testdir/server_common.tcl
return_if_no_server
db close

foreach {tn vfs} {1 unix-excl 2 unix} {
  server_set_vfs $vfs

  foreach f [glob -nocomplain test.db*] {
    forcedelete $f
  }

  #-------------------------------------------------------------------------
  # Check that the *-journal* files are deleted correctly.
  #
  server_reset_db
  do_execsql_test 1.0 {
    CREATE TABLE t1(a, b);
  } {}
  
  do_test $tn.1.1 {
    lsort [glob -nocomplain test.db-journal/*-journal]
  } {test.db-journal/0-journal}
  
  do_test $tn.1.2 {
    db close
    lsort [glob -nocomplain test.db-journal/*-journal]
  } {}
  
  server_sqlite3 db test.db
  do_execsql_test $tn.1.3 {
    CREATE TABLE t2(a, b);
  } {}
  
  server_sqlite3 db2 test.db
  do_test $tn.1.4 {
    db eval {
      BEGIN;
        INSERT INTO t1 VALUES(1, 2);
    }
    db2 eval {
      BEGIN;
        INSERT INTO t2 VALUES(3, 4);
    }
  } {}
  
  do_test $tn.1.5 {
    db2 eval COMMIT
    db eval COMMIT
    lsort [glob -nocomplain test.db-journal/*-journal]
  } {test.db-journal/0-journal test.db-journal/1-journal}
  
  do_test $tn.1.6 {
    db close
    lsort [glob -nocomplain test.db-journal/*-journal]
  } {test.db-journal/0-journal test.db-journal/1-journal}
  
  do_test $tn.1.7 {
    db2 close
    lsort [glob -nocomplain test.db-journal/*-journal]
  } {}
  
  #-------------------------------------------------------------------------
  #
  server_reset_db
  server_sqlite3 db2 test.db
  
  do_execsql_test $tn.2.0 {
    CREATE TABLE t1(a, b);
    CREATE TABLE t2(c, d);
  }
  
  # Two concurrent transactions committed.
  #
  do_test $tn.2.1 {
    db eval {
      BEGIN;
        INSERT INTO t1 VALUES(1, 2);
    }
    db2 eval {
      BEGIN;
        INSERT INTO t2 VALUES(3, 4);
    }
  } {}
  do_test $tn.2.2 {
    lsort [glob -nocomplain test.db-journal/*-journal]
  } {test.db-journal/0-journal test.db-journal/1-journal}
  do_test $tn.2.3.1 { db eval COMMIT  } {}
  do_test $tn.2.3.2 { db2 eval COMMIT } {}
  do_execsql_test 2.4 {SELECT * FROM t1, t2} {1 2 3 4}
  do_test $tn.2.5 {
    lsort [glob -nocomplain test.db-journal/*-journal]
  } {test.db-journal/0-journal test.db-journal/1-journal}
  
  do_test $tn.2.6 {
    execsql {BEGIN}
    execsql {INSERT INTO t1 VALUES(5, 6)}
  
    execsql {BEGIN} db2
    catchsql {INSERT INTO t1 VALUES(7, 8)} db2
  } {1 {database is locked}}
  do_test $tn.2.7 {
    # Transaction is automatically rolled back in this case.
    sqlite3_get_autocommit db2
  } {1}
  do_test $tn.2.8 {
    execsql COMMIT
    execsql { SELECT * FROM t1 } db2
  } {1 2 5 6}
  db2 close
  
  #-------------------------------------------------------------------------
  #
  server_reset_db
  do_execsql_test $tn.3.0 {
    CREATE TABLE t1(a, b);
  }
  
  do_test $tn.3.1 {
    lsort [glob -nocomplain test.db-journal/*-journal]
  } {test.db-journal/0-journal}
  
  do_test $tn.3.2 {
    db close
    lsort [glob -nocomplain test.db-journal/*-journal]
  } {}

  #-----------------------------------------------------------------------
  # Test that write-locks are downgraded when a transaction is ended,
  # even if the connection holds an open read statement.
  #
  do_test $tn.4.1 {
    server_sqlite3 db test.db
    server_sqlite3 db2 test.db
    db eval {
      CREATE TABLE t2(a);
      INSERT INTO t2 VALUES('one');
      INSERT INTO t2 VALUES('two');
      INSERT INTO t2 VALUES('three');
      CREATE TABLE t3(k INTEGER PRIMARY KEY, val);
    }

    set res [list]
    db eval { SELECT a FROM t2 ORDER BY rowid } {
      db eval { REPLACE INTO t3 VALUES(1, $a) }
      lappend res [db2 one { SELECT val FROM t3 }]
    }

    set res
  } {one two three}

  catch { db close }
  catch { db2 close }
}

finish_test