Fix for bugs #5892/6182/8751/8758/10994 (based on Antony's patch)

  "Triggers have the wrong namespace"
  "Triggers: duplicate names allowed"
  "Triggers: CREATE TRIGGER does not accept fully qualified names"
  "SHOW TRIGGERS"


mysql-test/r/information_schema.result:
  Added tests for new INFORMATION_SCHEMA.TRIGGERS view and SHOW TRIGGERS command.
mysql-test/r/information_schema_db.result:
  INFORMATION_SCHEMA.TRIGGERS view was added.
mysql-test/r/rpl_sp.result:
  Now DROP TRIGGER interprets first part of trigger identifier as database
  name and not as table name. Adjusted tests properly.
mysql-test/r/trigger.result:
  Now DROP TRIGGER interprets first part of trigger identifier as database
  name and not as table name. Adjusted tests properly.
  Added test checking that triggers have database wide namespace.
  Added test for bug #8791 "Triggers: Allowed to create triggers on a subject
  table in a different DB".
mysql-test/r/view.result:
  Now DROP TRIGGER interprets first part of trigger identifier as database
  name and not as table name. Adjusted tests properly.
mysql-test/t/information_schema.test:
  Added tests for new INFORMATION_SCHEMA.TRIGGERS view and SHOW TRIGGERS command.
mysql-test/t/rpl_sp.test:
  Now DROP TRIGGER interprets first part of trigger identifier as database
  name and not as table name. Adjusted tests properly.
mysql-test/t/trigger.test:
  Now DROP TRIGGER interprets first part of trigger identifier as database
  name and not as table name. Adjusted tests properly.
  Added test checking that triggers have database wide namespace.
  Added test for bug #8791 "Triggers: Allowed to create triggers on a subject
  table in a different DB".
mysql-test/t/view.test:
  Now DROP TRIGGER interprets first part of trigger identifier as database
  name and not as table name. Adjusted tests properly.
sql/handler.cc:
  Added .TRN tho the list of known file extensions assoicated with tables.
sql/item.h:
  trg_action_time_type/trg_event_type enums:
    Added TRG_ACTION_MAX/TRG_EVENT_MAX elements which should be used instead of
    magical values in various loops where we iterate through all types of trigger
    action times or/and trigger event types.
sql/lex.h:
  Added new symbol "TRIGGERS".
sql/mysql_priv.h:
  Added declaration of constant holding extension for trigger name (.TRN) files.
sql/mysqld.cc:
  Added statistical variable for SHOW TRIGGERS command.
sql/share/errmsg.txt:
  Added error message saying that one attempts to create trigger in wrong schema.
sql/sp.cc:
  Replaced magical values with TRG_EVENT_MAX/TRG_ACTION_MAX constants.
sql/sql_base.cc:
  open_unireg_entry():
    Now Table_triggers_list::check_n_load() has one more argument which
    controls whether we should prepare Table_triggers_list with fully functional
    triggers or load only their names.
sql/sql_lex.h:
  Added element for new SHOW TRIGGERS command to enum_sql_command enum.
sql/sql_parse.cc:
  prepare_schema_table():
    Added support for SHOW TRIGGERS statement.
sql/sql_show.cc:
  Added new INFORMATION_SCHEMA.TRIGGERS view and SHOW TRIGGERS command.
sql/sql_table.cc:
  mysql_rm_table_part2():
    Replaced simple deletion of .TRG file with call to
    Table_triggers_list::drop_all_triggers which will also delete .TRN files
    for all triggers associated with table.
sql/sql_trigger.cc:
  Now triggers have database wide namespace. To support it we create special .TRN
  file with same name as trigger for each trigger. This file contains name of
  trigger's table so one does not need to specify it explicitly in DROP TRIGGER.
  Moreover DROP TRIGGER treats first part of trigger identifier as database name
  now. Updated mysql_create_or_drop_trigger() routine and
  Table_triggers_list::create_trigger()/drop_trigger()/check_n_load() methods
  accordingly. Added add_table_for_trigger() routine and
  Table_triggers_list::drop_all_triggers() method.
  
  Added Table_triggers_list::get_trigger_info() for obtaining trigger metadata.
sql/sql_trigger.h:
  Table_triggers_list:
    Use TRG_EVENT_MAX, TRG_ACTION_MAX instead of magic values.
    Added get_trigger_info() method for obtaining trigger's meta-data.
    Added drop_all_triggers() method which drops all triggers for table.
    Added declarations of trg_action_time_type_names/trg_event_type_names
    arrays which hold names of triggers action time types  and event types.
sql/sql_yacc.yy:
  Changed grammar for CREATE/DROP TRIGGER to support database wide trigger
  namespace. Added new SHOW TRIGGERS statement.
sql/table.h:
  enum enum_schema_tables:
    Added constant for new INFORMATION_SCHEMA.TRIGGERS view.

1 files changed, 39 insertions, 20 deletions

diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test
index 8a27636ed84..b36140f2ada 100644
--- a/mysql-test/t/trigger.test
+++ b/mysql-test/t/trigger.test
@@ -17,13 +17,13 @@ set @a:=0;
 select @a;
 insert into t1 values (1);
 select @a;
-drop trigger t1.trg;
+drop trigger trg;
 
 # let us test simple trigger reading some values 
 create trigger trg before insert on t1 for each row set @a:=new.i;
 insert into t1 values (123);
 select @a;
-drop trigger t1.trg;
+drop trigger trg;
 
 drop table t1;
 
@@ -40,7 +40,7 @@ end|
 insert into t1 (i) values (1)|
 insert into t1 (i,j) values (2, 3)|
 select * from t1|
-drop trigger t1.trg|
+drop trigger trg|
 drop table t1|
 delimiter ;|
 
@@ -52,7 +52,7 @@ create trigger trg after insert on t1 for each row
 set @a:="";
 insert into t1 values (2),(3),(4),(5);
 select @a;
-drop trigger t1.trg;
+drop trigger trg;
 drop table t1;
 
 # PS doesn't work with multi-row statements
@@ -75,7 +75,7 @@ set @update_failed:=""|
 update t1 set balance=1500|
 select @update_failed;
 select * from t1|
-drop trigger t1.trg|
+drop trigger trg|
 drop table t1|
 delimiter ;|
 --enable_ps_protocol
@@ -88,7 +88,7 @@ create trigger trg after update on t1 for each row
 set @total_change:=0;
 update t1 set i=3;
 select @total_change;
-drop trigger t1.trg;
+drop trigger trg;
 drop table t1;
 
 # Before delete trigger
@@ -100,7 +100,7 @@ create trigger trg before delete on t1 for each row
 set @del_sum:= 0;
 delete from t1 where i <= 3;
 select @del_sum;
-drop trigger t1.trg;
+drop trigger trg;
 drop table t1;
 
 # After delete trigger. 
@@ -111,7 +111,7 @@ create trigger trg after delete on t1 for each row set @del:= 1;
 set @del:= 0;
 delete from t1 where i <> 0;
 select @del;
-drop trigger t1.trg;
+drop trigger trg;
 drop table t1;
 
 # Several triggers on one table
@@ -145,9 +145,9 @@ update t1 set j= 20;
 select @fired;
 select * from t1;
 
-drop trigger t1.trg1;
-drop trigger t1.trg2;
-drop trigger t1.trg3;
+drop trigger trg1;
+drop trigger trg2;
+drop trigger trg3;
 drop table t1;
 
 
@@ -212,7 +212,7 @@ create trigger t1_ai after insert on t1 for each row
 insert into t1 (id, data) values (1, "one"), (2, "two");
 select * from t1;
 select * from t2;
-drop trigger t1.t1_ai;
+drop trigger t1_ai;
 # Trigger which uses couple of tables (and partially emulates FK constraint)
 delimiter |;
 create trigger t1_bi before insert on t1 for each row
@@ -282,6 +282,7 @@ drop table t1, t2;
 # Test of wrong column specifiers in triggers
 #
 create table t1 (i int);
+create table t3 (i int);
 
 --error 1363
 create trigger trg before insert on t1 for each row set @a:= old.i;
@@ -301,7 +302,7 @@ create trigger trg before update on t1 for each row set @a:=old.j;
 
 #
 # Let us test various trigger creation errors
-#
+# Also quickly test table namespace (bug#5892/6182)
 #
 --error 1146
 create trigger trg before insert on t2 for each row set @a:=1;
@@ -311,10 +312,14 @@ create trigger trg before insert on t1 for each row set @a:=1;
 create trigger trg after insert on t1 for each row set @a:=1;
 --error 1359
 create trigger trg2 before insert on t1 for each row set @a:=1;
-drop trigger t1.trg;
+--error 1359
+create trigger trg before insert on t3 for each row set @a:=1;
+create trigger trg2 before insert on t3 for each row set @a:=1;
+drop trigger trg2;
+drop trigger trg;
 
 --error 1360
-drop trigger t1.trg;
+drop trigger trg;
 
 create view v1 as select * from t1;
 --error 1347
@@ -322,6 +327,7 @@ create trigger trg before insert on v1 for each row set @a:=1;
 drop view v1;
 
 drop table t1;
+drop table t3;
 
 create temporary table t1 (i int);
 --error 1361
@@ -339,7 +345,7 @@ drop table t1;
 create table t1 (x1col char);  
 create trigger tx1 before insert on t1 for each row set new.x1col = 'x';
 insert into t1 values ('y');
-drop trigger t1.tx1;
+drop trigger tx1;
 drop table t1;
 
 #
@@ -355,8 +361,8 @@ create trigger trg2 after delete on t1 for each row set @del_after:= @del_after
 set @del_before:=0, @del_after:= 0;
 delete from t1;
 select @del_before, @del_after;
-drop trigger t1.trg1;
-drop trigger t1.trg2;
+drop trigger trg1;
+drop trigger trg2;
 drop table t1;
 
 # Test for bug #5859 "DROP TABLE does not drop triggers". Trigger should not
@@ -378,6 +384,19 @@ create trigger trg1 before insert on t1 for each row set @a:= 1;
 drop database mysqltest;
 use test;
 
+# Test for bug #8791
+# "Triggers: Allowed to create triggers on a subject table in a different DB". 
+create database mysqltest;
+create table mysqltest.t1 (i int);
+--error 1429
+create trigger trg1 before insert on mysqltest.t1 for each row set @a:= 1;
+use mysqltest;
+--error 1429
+create trigger test.trg1 before insert on t1 for each row set @a:= 1;
+drop database mysqltest;
+use test;
+
+
 # Test for bug #5860 "Multi-table UPDATE does not activate update triggers"
 # We will also test how delete triggers wor for multi-table DELETE.
 create table t1 (i int, j int default 10, k int not null, key (k));
@@ -559,7 +578,7 @@ select * from t1;
 # To test properly code-paths different from those that are used
 # in ordinary INSERT we need to drop "before insert" trigger.
 alter table t1 add primary key (i);
-drop trigger t1.bi;
+drop trigger bi;
 --error 1054
 insert into t1 values (2, 4) on duplicate key update k= k + 10;
 select * from t1;
@@ -589,5 +608,5 @@ drop function bug5893;
 --error 1305
 update t1 set col2 = 4;
 # This should not crash server too.
-drop trigger t1.t1_bu;
+drop trigger t1_bu;
 drop table t1;