SQLite

# 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.11 2009/01/11 00:18:38 drh Exp $

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


#----------------------------------------------------------------------
# The following tests - savepoint-1.* - test that the SAVEPOINT, RELEASE

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


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

    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 {

  sqlite3_get_autocommit db
} {1}
do_test savepoint-12.4 {
  execsql { SAVEPOINT one }
} {}

finish_test