# 2010 November 29
#
# 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 tests to verify that the "testable statements" in 
# the lang_droptrigger.html document are correct.
#

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

ifcapable !trigger { finish_test ; return }

proc do_droptable_tests {nm args} {
  uplevel do_select_tests [list e_createtable-$nm] $args
}

proc list_all_triggers {{db db}} {
  set res [list]
  $db eval { PRAGMA database_list } {
    if {$name == "temp"} {
      set tbl sqlite_temp_master
    } else {
      set tbl "$name.sqlite_master"
    }
    lappend res {*}[
      db eval "SELECT '$name.' || name FROM $tbl WHERE type = 'trigger'"
    ]
  }
  set res
}


proc droptrigger_reopen_db {{event INSERT}} {
  db close
  forcedelete test.db test.db2
  sqlite3 db test.db

  set ::triggers_fired [list]
  proc r {x} { lappend ::triggers_fired $x }
  db func r r

  db eval "
    ATTACH 'test.db2' AS aux;

    CREATE TEMP TABLE t1(a, b);
    INSERT INTO t1 VALUES('a', 'b');
    CREATE TRIGGER tr1 AFTER $event ON t1 BEGIN SELECT r('temp.tr1') ; END;

    CREATE TABLE t2(a, b);
    INSERT INTO t2 VALUES('a', 'b');
    CREATE TRIGGER tr1 BEFORE $event ON t2 BEGIN SELECT r('main.tr1') ; END;
    CREATE TRIGGER tr2 AFTER  $event ON t2 BEGIN SELECT r('main.tr2') ; END;

    CREATE TABLE aux.t3(a, b);
    INSERT INTO t3 VALUES('a', 'b');
    CREATE TRIGGER aux.tr1 BEFORE $event ON t3 BEGIN SELECT r('aux.tr1') ; END;
    CREATE TRIGGER aux.tr2 AFTER  $event ON t3 BEGIN SELECT r('aux.tr2') ; END;
    CREATE TRIGGER aux.tr3 AFTER  $event ON t3 BEGIN SELECT r('aux.tr3') ; END;
  "
}


# EVIDENCE-OF: R-52650-16855 -- syntax diagram drop-trigger-stmt
#
do_droptable_tests 1.1 -repair {
  droptrigger_reopen_db
} -tclquery {
  list_all_triggers 
} {
  1   "DROP TRIGGER main.tr1"            
      {main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3}
  2   "DROP TRIGGER IF EXISTS main.tr1"  
      {main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3}
  3   "DROP TRIGGER tr1"                 
      {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3}
  4   "DROP TRIGGER IF EXISTS tr1"       
      {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3}

  5   "DROP TRIGGER aux.tr1"             
      {main.tr1 main.tr2 temp.tr1 aux.tr2 aux.tr3}
  6   "DROP TRIGGER IF EXISTS aux.tr1"   
      {main.tr1 main.tr2 temp.tr1 aux.tr2 aux.tr3}

  7   "DROP TRIGGER IF EXISTS aux.xxx"   
      {main.tr1 main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3}
  8   "DROP TRIGGER IF EXISTS aux.xxx"   
      {main.tr1 main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3}
}

# EVIDENCE-OF: R-61172-15671 The DROP TRIGGER statement removes a
# trigger created by the CREATE TRIGGER statement.
#
foreach {tn tbl droptrigger before after} {
  1   t1  "DROP TRIGGER tr1" {temp.tr1}                {}
  2   t2  "DROP TRIGGER tr1" {main.tr1 main.tr2}       {main.tr1 main.tr2}
  3   t3  "DROP TRIGGER tr1" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2}

  4   t1  "DROP TRIGGER tr2" {temp.tr1}                {temp.tr1}
  5   t2  "DROP TRIGGER tr2" {main.tr1 main.tr2}       {main.tr1}
  6   t3  "DROP TRIGGER tr2" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2}

  7   t1  "DROP TRIGGER tr3" {temp.tr1}                {temp.tr1}
  8   t2  "DROP TRIGGER tr3" {main.tr1 main.tr2}       {main.tr1 main.tr2}
  9   t3  "DROP TRIGGER tr3" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr2}
} {

  do_test 2.$tn.1 {
    droptrigger_reopen_db
    execsql " INSERT INTO $tbl VALUES('1', '2') "
    set ::triggers_fired
  } $before

  do_test 2.$tn.2 {
    droptrigger_reopen_db
    execsql $droptrigger
    execsql " INSERT INTO $tbl VALUES('1', '2') "
    set ::triggers_fired
  } $after
}

# EVIDENCE-OF: R-50239-29811 Once removed, the trigger definition is no
# longer present in the sqlite_master (or sqlite_temp_master) table and
# is not fired by any subsequent INSERT, UPDATE or DELETE statements.
#
#   Test cases e_droptrigger-1.* test the first part of this statement
#   (that dropped triggers do not appear in the schema table), and tests
#   droptrigger-2.* test that dropped triggers are not fired by INSERT
#   statements. The following tests verify that they are not fired by
#   UPDATE or DELETE statements.
#
foreach {tn tbl droptrigger before after} {
  1   t1  "DROP TRIGGER tr1" {temp.tr1}                {}
  2   t2  "DROP TRIGGER tr1" {main.tr1 main.tr2}       {main.tr1 main.tr2}
  3   t3  "DROP TRIGGER tr1" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2}

  4   t1  "DROP TRIGGER tr2" {temp.tr1}                {temp.tr1}
  5   t2  "DROP TRIGGER tr2" {main.tr1 main.tr2}       {main.tr1}
  6   t3  "DROP TRIGGER tr2" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2}

  7   t1  "DROP TRIGGER tr3" {temp.tr1}                {temp.tr1}
  8   t2  "DROP TRIGGER tr3" {main.tr1 main.tr2}       {main.tr1 main.tr2}
  9   t3  "DROP TRIGGER tr3" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr2}
} {

  do_test 3.1.$tn.1 {
    droptrigger_reopen_db UPDATE
    execsql "UPDATE $tbl SET a = 'abc'"
    set ::triggers_fired
  } $before

  do_test 3.1.$tn.2 {
    droptrigger_reopen_db UPDATE
    execsql $droptrigger
    execsql "UPDATE $tbl SET a = 'abc'"
    set ::triggers_fired
  } $after
}
foreach {tn tbl droptrigger before after} {
  1   t1  "DROP TRIGGER tr1" {temp.tr1}                {}
  2   t2  "DROP TRIGGER tr1" {main.tr1 main.tr2}       {main.tr1 main.tr2}
  3   t3  "DROP TRIGGER tr1" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2}

  4   t1  "DROP TRIGGER tr2" {temp.tr1}                {temp.tr1}
  5   t2  "DROP TRIGGER tr2" {main.tr1 main.tr2}       {main.tr1}
  6   t3  "DROP TRIGGER tr2" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr3 aux.tr2}

  7   t1  "DROP TRIGGER tr3" {temp.tr1}                {temp.tr1}
  8   t2  "DROP TRIGGER tr3" {main.tr1 main.tr2}       {main.tr1 main.tr2}
  9   t3  "DROP TRIGGER tr3" {aux.tr1 aux.tr3 aux.tr2} {aux.tr1 aux.tr2}
} {

  do_test 3.2.$tn.1 {
    droptrigger_reopen_db DELETE
    execsql "DELETE FROM $tbl"
    set ::triggers_fired
  } $before

  do_test 3.2.$tn.2 {
    droptrigger_reopen_db DELETE
    execsql $droptrigger
    execsql "DELETE FROM $tbl"
    set ::triggers_fired
  } $after
}

# EVIDENCE-OF: R-37808-62273 Note that triggers are automatically
# dropped when the associated table is dropped.
#
do_test 4.1 {
  droptrigger_reopen_db
  list_all_triggers
} {main.tr1 main.tr2 temp.tr1 aux.tr1 aux.tr2 aux.tr3}
do_test 4.2 {
  droptrigger_reopen_db
  execsql "DROP TABLE t1"
  list_all_triggers
} {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3}
do_test 4.3 {
  droptrigger_reopen_db
  execsql "DROP TABLE t1"
  list_all_triggers
} {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3}
do_test 4.4 {
  droptrigger_reopen_db
  execsql "DROP TABLE t1"
  list_all_triggers
} {main.tr1 main.tr2 aux.tr1 aux.tr2 aux.tr3}

finish_test

} {{four five}}
do_test fts3aa-6.2 {
  execsql {INSERT INTO t1(rowid, content) VALUES(-1, 'three four')}
} {}
do_test fts3aa-6.3 {
  execsql {SELECT content FROM t1 WHERE rowid = -1}
} {{three four}}
breakpoint
do_test fts3aa-6.4 {
  execsql {SELECT rowid FROM t1 WHERE t1 MATCH 'four'}
} {-1 0 8 9 10 11 12 13 14 15 24 25 26 27 28 29 30 31}
























finish_test

} {{four five}}
do_test fts3aa-6.2 {
  execsql {INSERT INTO t1(rowid, content) VALUES(-1, 'three four')}
} {}
do_test fts3aa-6.3 {
  execsql {SELECT content FROM t1 WHERE rowid = -1}
} {{three four}}

do_test fts3aa-6.4 {
  execsql {SELECT rowid FROM t1 WHERE t1 MATCH 'four'}
} {-1 0 8 9 10 11 12 13 14 15 24 25 26 27 28 29 30 31}

# Test creation of FTS3 and FTS4 tables with columns that contain
# an "=" character.
#
do_execsql_test fts3aa-7.1 {
  CREATE VIRTUAL TABLE t2 USING fts3(xyz=abc);
  SELECT xyz FROM t2;
} {}
do_catchsql_test fts3aa-7.2 {
  CREATE VIRTUAL TABLE t3 USING fts4(xyz=abc);
} {1 {unrecognized parameter: xyz=abc}}
do_catchsql_test fts3aa-7.3 {
  CREATE VIRTUAL TABLE t3 USING fts4(xyz = abc);
} {1 {unrecognized parameter: xyz = abc}}

do_execsql_test fts3aa-7.4 {
  CREATE VIRTUAL TABLE t3 USING fts3(tokenize=simple, tokenize=simple);
  SELECT tokenize FROM t3;
} {}
do_catchsql_test fts3aa-7.5 {
  CREATE VIRTUAL TABLE t4 USING fts4(tokenize=simple, tokenize=simple);
} {1 {unrecognized parameter: tokenize=simple}}


finish_test

  }

  set testspec [list -prep $O(-prep) -body $O(-body) -test $O(-test)]
  foreach f [lsort -unique $faultlist] {
    eval do_one_faultsim_test "$name-$f" $FAULTSIM($f) $testspec
  }
}


#-------------------------------------------------------------------------
# Procedures to save and restore the current file-system state:
#
#   faultsim_save
#   faultsim_restore
#   faultsim_save_and_close
#   faultsim_restore_and_reopen
#   faultsim_delete_and_reopen
#
proc faultsim_save {} {
  foreach f [glob -nocomplain sv_test.db*] { forcedelete $f }
  foreach f [glob -nocomplain test.db*] {
    set f2 "sv_$f"
    file copy -force $f $f2
  }
}
proc faultsim_save_and_close {} {
  faultsim_save
  catch { db close }
  return ""
}
proc faultsim_restore {} {
  foreach f [glob -nocomplain test.db*] { forcedelete $f }
  foreach f2 [glob -nocomplain sv_test.db*] {
    set f [string range $f2 3 end]
    file copy -force $f2 $f
  }
}
proc faultsim_restore_and_reopen {{dbfile test.db}} {
  catch { db close }
  faultsim_restore
  sqlite3 db $dbfile
  sqlite3_extended_result_codes db 1
  sqlite3_db_config_lookaside db 0 0 0
}

proc faultsim_integrity_check {{db db}} {
  set ic [$db eval { PRAGMA integrity_check }]
  if {$ic != "ok"} { error "Integrity check: $ic" }
}

proc faultsim_delete_and_reopen {{file test.db}} {
  catch { db close }
  foreach f [glob -nocomplain test.db*] { file delete -force $f }
  sqlite3 db $file
}


# The following procs are used as [do_one_faultsim_test] callbacks when 
# injecting OOM faults into test cases.
#
proc oom_injectstart {nRepeat iFail} {
  sqlite3_memdebug_fail [expr $iFail-1] -repeat $nRepeat
}

  }

  set testspec [list -prep $O(-prep) -body $O(-body) -test $O(-test)]
  foreach f [lsort -unique $faultlist] {
    eval do_one_faultsim_test "$name-$f" $FAULTSIM($f) $testspec
  }
}


#-------------------------------------------------------------------------
# Procedures to save and restore the current file-system state:
#
#   faultsim_save
#   faultsim_restore
#   faultsim_save_and_close
#   faultsim_restore_and_reopen
#   faultsim_delete_and_reopen
#
proc faultsim_save {args} { uplevel db_save $args }
proc faultsim_save_and_close {args} { uplevel db_save_and_close $args }
proc faultsim_restore {args} { uplevel db_restore $args }
proc faultsim_restore_and_reopen {args} { 
  uplevel db_restore_and_reopen $args 
  sqlite3_extended_result_codes db 1
  sqlite3_db_config_lookaside db 0 0 0
}
proc faultsim_delete_and_reopen {args} {
  uplevel db_delete_and_reopen $args 













  sqlite3_extended_result_codes db 1
  sqlite3_db_config_lookaside db 0 0 0
}

proc faultsim_integrity_check {{db db}} {
  set ic [$db eval { PRAGMA integrity_check }]
  if {$ic != "ok"} { error "Integrity check: $ic" }
}








# The following procs are used as [do_one_faultsim_test] callbacks when 
# injecting OOM faults into test cases.
#
proc oom_injectstart {nRepeat iFail} {
  sqlite3_memdebug_fail [expr $iFail-1] -repeat $nRepeat
}

  catch { db36231 func a_string a_string }
  execsql $sql db36231
  db36231 close
  hexio_write test.db 28 $A
  hexio_write test.db 92 $B
  return ""
}































# If the library is compiled with the SQLITE_DEFAULT_AUTOVACUUM macro set
# to non-zero, then set the global variable $AUTOVACUUM to 1.
set AUTOVACUUM $sqlite_options(default_autovacuum)

source $testdir/thread_common.tcl
source $testdir/malloc_common.tcl

  catch { db36231 func a_string a_string }
  execsql $sql db36231
  db36231 close
  hexio_write test.db 28 $A
  hexio_write test.db 92 $B
  return ""
}

proc db_save {} {
  foreach f [glob -nocomplain sv_test.db*] { forcedelete $f }
  foreach f [glob -nocomplain test.db*] {
    set f2 "sv_$f"
    file copy -force $f $f2
  }
}
proc db_save_and_close {} {
  db_save
  catch { db close }
  return ""
}
proc db_restore {} {
  foreach f [glob -nocomplain test.db*] { forcedelete $f }
  foreach f2 [glob -nocomplain sv_test.db*] {
    set f [string range $f2 3 end]
    file copy -force $f2 $f
  }
}
proc db_restore_and_reopen {{dbfile test.db}} {
  catch { db close }
  db_restore
  sqlite3 db $dbfile
}
proc db_delete_and_reopen {{file test.db}} {
  catch { db close }
  foreach f [glob -nocomplain test.db*] { file delete -force $f }
  sqlite3 db $file
}

# If the library is compiled with the SQLITE_DEFAULT_AUTOVACUUM macro set
# to non-zero, then set the global variable $AUTOVACUUM to 1.
set AUTOVACUUM $sqlite_options(default_autovacuum)

source $testdir/thread_common.tcl
source $testdir/malloc_common.tcl