Artifact 60f897f329a6782ef2f24862640acf3c52e48077:

• File ext/ota/ota4.test — part of check-in [95ffdaa5] at 2014-09-19 18:08:39 on branch ota-update — Add further tests to ota5.test. Add "ota.test", for running all ota tests. (user: dan size: 5806)

# 2014 August 30
#
# 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.
#
#***********************************************************************
#
# Test some properties of the pager_ota_mode and ota_mode pragmas.
#

if {![info exists testdir]} {
  set testdir [file join [file dirname [info script]] .. .. test]
}
source $testdir/tester.tcl
set ::testprefix ota4

#-------------------------------------------------------------------------
# The following tests aim to verify some properties of the pager_ota_mode
# pragma:
#
# 1. Cannot set the pager_ota_mode flag on a WAL mode database.
#
# 2. Or if there is an open read transaction.
#
# 3. Cannot start a transaction with pager_ota_mode set if there
#    is a WAL file in the file-system.
# 
# 4. Or if the wal-mode flag is set in the database file header.
# 
# 5. Cannot open a transaction with pager_ota_mode set if the database
#    file has been modified by a rollback mode client since the *-oal
#    file was started.
#

do_execsql_test 1.1.1 { 
  PRAGMA journal_mode = wal;
  SELECT * FROM sqlite_master;
} {wal}
do_catchsql_test 1.1.2 { 
  PRAGMA pager_ota_mode = 1 
} {1 {cannot set pager_ota_mode in wal mode}}


do_execsql_test 1.2.1 { 
  PRAGMA journal_mode = delete;
  BEGIN;
    SELECT * FROM sqlite_master;
} {delete}
do_catchsql_test 1.2.2 { 
  PRAGMA pager_ota_mode = 1 
} {1 {cannot set pager_ota_mode with open transaction}}
do_execsql_test 1.2.3 { 
  COMMIT;
} {}


do_execsql_test 1.3.1 {
  PRAGMA journal_mode = wal;
  CREATE TABLE t1(a, b);
  INSERT INTO t1 VALUES(1, 2);
} {wal}
do_test 1.3.2 {
  forcecopy test.db-wal test.db-bak
  execsql { 
    PRAGMA journal_mode = delete;
    PRAGMA pager_ota_mode = 1;
  }
  forcecopy test.db-bak test.db-wal
  catchsql {
    SELECT * FROM sqlite_master
  }
} {1 {unable to open database file}}

do_test 1.4.1 {
  db close
  forcedelete test.db-wal test.db-oal
  sqlite3 db test.db
  execsql { 
    PRAGMA journal_mode = wal;
    PRAGMA pager_ota_mode = 1;
  }
  catchsql {
    SELECT * FROM sqlite_master;
  }
} {1 {unable to open database file}}

do_test 1.5.1 {
  forcedelete test.db-oal
  reset_db
  execsql {
    PRAGMA journal_mode = delete;
    CREATE TABLE t1(a, b);
    INSERT INTO t1 VALUES(1, 2);
  }
  execsql {
    PRAGMA pager_ota_mode = 1;
    INSERT INTO t1 VALUES(3, 4);
  }
  db close
  sqlite3 db test.db
  execsql {
    SELECT * FROM t1;
  }
} {1 2}
do_execsql_test 1.5.2 {
  PRAGMA pager_ota_mode = 1;
  SELECT * FROM t1;
  INSERT INTO t1 VALUES(5, 6);
} {1 2 3 4}
do_test 5.3 {
  db close
  sqlite3 db test.db
  execsql {
    INSERT INTO t1 VALUES(7, 8);
    SELECT * FROM t1;
  }
} {1 2 7 8}
do_catchsql_test 1.5.4 {
  PRAGMA pager_ota_mode = 1;
  SELECT * FROM t1;
} {1 {database is locked}}

#-------------------------------------------------------------------------
# These tests - ota4-2.* - aim to verify some properties of the ota_mode
# pragma.
#
#   1. Check that UNIQUE constraints are not tested in ota_mode.
#   2. Except for (real) PRIMARY KEY constraints.
#   3. Check that all non-temporary triggers are ignored.
#
reset_db
do_execsql_test 2.1.1 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
  CREATE UNIQUE INDEX i1 ON t1(b);
  INSERT INTO t1 VALUES(1, 2, 3);
  INSERT INTO t1 VALUES(2, 4, 6);
}

do_execsql_test 2.1.2 {
  PRAGMA ota_mode = 1;
  INSERT INTO t1 VALUES(3, 2, 6);
  UPDATE t1 SET b=2 WHERE a=2;
  SELECT * FROM t1;
} {
  1 2 3
  2 2 6
  3 2 6
}

reset_db
do_execsql_test 2.2.1 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
  CREATE TABLE t2(x, y, z, PRIMARY KEY(y, z)) WITHOUT ROWID;

  INSERT INTO t1 VALUES(1, 2, 3);
  INSERT INTO t2 VALUES(4, 5, 6);
  PRAGMA ota_mode = 1;
}
do_catchsql_test 2.2.2 {
  INSERT INTO t1 VALUES(1, 'two', 'three');
} {1 {UNIQUE constraint failed: t1.a}}
do_catchsql_test 2.2.3 {
  INSERT INTO t2 VALUES('four', 5, 6);
} {1 {UNIQUE constraint failed: t2.y, t2.z}}

reset_db
do_execsql_test 2.3.1 {
  CREATE TABLE t1(a, b, c);
  CREATE TABLE log(x);
  INSERT INTO t1 VALUES(1, 2, 3);

  CREATE TRIGGER tr1 BEFORE INSERT ON t1 BEGIN
    INSERT INTO log VALUES('permanent');
  END;
  CREATE TRIGGER tr2 AFTER INSERT ON t1 BEGIN
    INSERT INTO log VALUES('permanent');
  END;
  CREATE TRIGGER tr3 BEFORE DELETE ON t1 BEGIN
    INSERT INTO log VALUES('permanent');
  END;
  CREATE TRIGGER tr4 AFTER DELETE ON t1 BEGIN
    INSERT INTO log VALUES('permanent');
  END;
  CREATE TRIGGER tr5 BEFORE UPDATE ON t1 BEGIN
    INSERT INTO log VALUES('permanent');
  END;
  CREATE TRIGGER tr6 AFTER UPDATE ON t1 BEGIN
    INSERT INTO log VALUES('permanent');
  END;

  CREATE TEMP TRIGGER ttr1 BEFORE INSERT ON t1 BEGIN
    INSERT INTO log VALUES('temp');
  END;
  CREATE TEMP TRIGGER ttr2 AFTER INSERT ON t1 BEGIN
    INSERT INTO log VALUES('temp');
  END;
  CREATE TEMP TRIGGER ttr3 BEFORE DELETE ON t1 BEGIN
    INSERT INTO log VALUES('temp');
  END;
  CREATE TEMP TRIGGER ttr4 AFTER DELETE ON t1 BEGIN
    INSERT INTO log VALUES('temp');
  END;
  CREATE TEMP TRIGGER ttr5 BEFORE UPDATE ON t1 BEGIN
    INSERT INTO log VALUES('temp');
  END;
  CREATE TEMP TRIGGER ttr6 AFTER UPDATE ON t1 BEGIN
    INSERT INTO log VALUES('temp');
  END;
}
do_execsql_test 2.3.2 {
  INSERT INTO t1 VALUES(4, 5, 6);
  DELETE FROM t1 WHERE a = 4;
  UPDATE t1 SET c = 6;
  SELECT x FROM log;
} {
  temp permanent temp permanent temp permanent 
  temp permanent temp permanent temp permanent
}
do_execsql_test 2.3.3 {
  DELETE FROM log;
  PRAGMA ota_mode = 1;
  INSERT INTO t1 VALUES(4, 5, 6);
  DELETE FROM t1 WHERE a = 4;
  UPDATE t1 SET c = 6;
  SELECT x FROM log;
} {temp temp temp temp temp temp}

finish_test