Import SQLite 3.7.6.3.

third_party/sqlite/src/test/e_droptrigger.test

+# 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_droptrigger
+
+ifcapable !trigger { finish_test ; return }
+
+proc do_droptrigger_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_droptrigger_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