SQLite

#***********************************************************************
#

set testdir [file join [file dirname $argv0] .. .. test]
source $testdir/tester.tcl
set ::testprefix ota2

forcedelete test.db-oal 
forcedelete test.db-oal test.db-bak

do_execsql_test 1.0 {
  CREATE TABLE t1(a, b);
  INSERT INTO t1 VALUES(1, 2);
} {}
do_test 1.1 { glob test.db* } {test.db}

# 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 pragma.
# Test some properties of the pager_ota_mode and ota_mode pragmas.
#

set testdir [file join [file dirname $argv0] .. .. 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 { 
do_execsql_test 1.1.1 { 
  PRAGMA journal_mode = wal;
  SELECT * FROM sqlite_master;
} {wal}
do_catchsql_test 1.2 { 
do_catchsql_test 1.1.2 { 
  PRAGMA pager_ota_mode = 1 
} {1 {cannot set pager_ota_mode in wal mode}}


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


do_execsql_test 3.1 {
do_execsql_test 1.3.1 {
  PRAGMA journal_mode = wal;
  CREATE TABLE t1(a, b);
  INSERT INTO t1 VALUES(1, 2);
} {wal}
do_test 3.2 {
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 4.1 {
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 5.1 {
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 5.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 5.4 {
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

  assert( pTab->pSelect==0 );  /* This table is not a VIEW */
  for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
    if( aRegIdx[i]==0 ) continue;

    /* If the "ota_mode" flag is set, ignore all indexes except the PK 
    ** index of WITHOUT ROWID tables.  */
    if( (pParse->db->flags & SQLITE_OtaMode) 
     && (pTab->iPKey>=0 || pIdx->idxType!=SQLITE_IDXTYPE_PRIMARYKEY) 
     && (HasRowid(pTab) || pIdx->idxType!=SQLITE_IDXTYPE_PRIMARYKEY) 
    ){
      continue;
    }

    bAffinityDone = 1;
    if( pIdx->pPartIdxWhere ){
      sqlite3VdbeAddOp2(v, OP_IsNull, aRegIdx[i], sqlite3VdbeCurrentAddr(v)+2);

** triggers on pTab in the TEMP schema.  This routine prepends all
** TEMP triggers on pTab to the beginning of the pTab->pTrigger list
** and returns the combined list.
**
** To state it another way:  This routine returns a list of all triggers
** that fire off of pTab.  The list will include any TEMP triggers on
** pTab as well as the triggers lised in pTab->pTrigger.
**
** If the SQLITE_OtaMode flag is set, do not include any non-temporary
** triggers in the returned list.
*/
Trigger *sqlite3TriggerList(Parse *pParse, Table *pTab){
  Schema * const pTmpSchema = pParse->db->aDb[1].pSchema;
  Trigger *pList = 0;                  /* List of triggers to return */
  int bOta = !!(pParse->db->flags & SQLITE_OtaMode);

  if( pParse->disableTriggers ){
    return 0;
  }

  if( pTmpSchema!=pTab->pSchema ){
    HashElem *p;
    assert( sqlite3SchemaMutexHeld(pParse->db, 0, pTmpSchema) );
    for(p=sqliteHashFirst(&pTmpSchema->trigHash); p; p=sqliteHashNext(p)){
      Trigger *pTrig = (Trigger *)sqliteHashData(p);
      if( pTrig->pTabSchema==pTab->pSchema
       && 0==sqlite3StrICmp(pTrig->table, pTab->zName) 
      ){
        pTrig->pNext = (pList ? pList : pTab->pTrigger);
        pTrig->pNext = ((pList || bOta) ? pList : pTab->pTrigger);
        pList = pTrig;
      }
    }
  }

  return (pList ? pList : pTab->pTrigger);
  return ((pList || bOta) ? pList : pTab->pTrigger);
}

/*
** This is called by the parser when it sees a CREATE TRIGGER statement
** up to the point of the BEGIN before the trigger actions.  A Trigger
** structure is generated based on the information available and stored
** in pParse->pNewTrigger.  After the trigger actions have been parsed, the