| List: | Internals | « Previous MessageNext Message » | |
| From: | antony | Date: | July 19 2005 11:37am |
| Subject: | bk commit into 5.0 tree (acurtis:1.1923) BUG#5892 | ||
| View as plain text | |||
Below is the list of changes that have just been committed into a local 5.0 repository of antony. When antony does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository. For information on how to access the public repository see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html ChangeSet 1.1923 05/07/19 10:37:23 acurtis@stripped +28 -0 Bug#5892/6182/8751/8758/10994 "Triggers have the wrong namespace" "Triggers: duplicate names allowed" "Triggers: CREATE TRIGGER does not accept fully qualified names" "SHOW TRIGGERS" sql/table.h 1.103 05/07/19 10:35:13 acurtis@stripped +1 -1 TRIGGERS information schema view. sql/sql_yacc.yy 1.405 05/07/19 10:35:13 acurtis@stripped +18 -15 fix parser so that trigger identifiers are distinct and seperate from MySQL. sql/sql_view.cc 1.54 05/07/19 10:35:12 acurtis@stripped +1 -1 new arguments for sql_create_definition_file() sql/sql_trigger.h 1.10 05/07/19 10:35:12 acurtis@stripped +26 -14 new consts for trg_action_time_tupe and trb_event_type sql/sql_trigger.cc 1.21 05/07/19 10:35:12 acurtis@stripped +382 -43 new consts for trg_action_time_tupe and trb_event_type sql/sql_table.cc 1.261 05/07/19 10:35:11 acurtis@stripped +10 -10 change how triggers are dropped when table is dropped sql/sql_show.cc 1.254 05/07/19 10:35:11 acurtis@stripped +115 -0 SHOW TRIGGERS / TRIGGERS infomation view sql/sql_parse.cc 1.446 05/07/19 10:35:10 acurtis@stripped +1 -0 TRIGGERS view in information schema sql/sql_lex.h 1.190 05/07/19 10:35:10 acurtis@stripped +1 -0 SHOW TRIGGERS sql/sql_base.cc 1.268 05/07/19 10:35:09 acurtis@stripped +1 -1 fix with extra arg sql/sp.cc 1.83 05/07/19 10:35:09 acurtis@stripped +2 -2 use consts instead of magic values sql/share/errmsg.txt 1.40 05/07/19 10:35:09 acurtis@stripped +6 -0 New error messages for triggers sql/parse_file.h 1.8 05/07/19 10:35:09 acurtis@stripped +5 -2 new arguments for sql_create_definition_file sql/parse_file.cc 1.13 05/07/19 10:35:08 acurtis@stripped +25 -14 new arguments for sql_create_definition_file sql/mysqld.cc 1.479 05/07/19 10:35:08 acurtis@stripped +1 -0 new schema file ext type for trigger names sql/mysql_priv.h 1.327 05/07/19 10:35:08 acurtis@stripped +1 -0 new schema file ext type for trigger names sql/lex.h 1.138 05/07/19 10:35:07 acurtis@stripped +1 -0 New token TRIGGERS sql/item.h 1.152 05/07/19 10:35:07 acurtis@stripped +2 -2 new consts for trg_action_time_tupe and trb_event_type sql/handler.cc 1.179 05/07/19 10:35:07 acurtis@stripped +1 -0 new schema file ext type for trigger names mysql-test/t/view.test 1.85 05/07/19 10:35:07 acurtis@stripped +1 -1 remove table name from trigger identifier mysql-test/t/trigger.test 1.16 05/07/19 10:35:07 acurtis@stripped +38 -19 remove table name from trigger identifier mysql-test/t/rpl_sp.test 1.4 05/07/19 10:35:07 acurtis@stripped +1 -1 remove table name from trigger identifier mysql-test/t/information_schema.test 1.43 05/07/19 10:35:06 acurtis@stripped +37 -0 remove table name from trigger identifier mysql-test/r/view.result 1.90 05/07/19 10:35:06 acurtis@stripped +1 -1 remove table name from trigger identifier mysql-test/r/trigger.result 1.12 05/07/19 10:35:06 acurtis@stripped +33 -18 remove table name from trigger identifier mysql-test/r/rpl_sp.result 1.5 05/07/19 10:35:06 acurtis@stripped +2 -2 remove table name from trigger identifier mysql-test/r/information_schema_db.result 1.3 05/07/19 10:35:06 acurtis@stripped +2 -0 new TRIGGERS view mysql-test/r/information_schema.result 1.61 05/07/19 10:35:06 acurtis@stripped +75 -2 new TRIGGERS view # This is a BitKeeper patch. What follows are the unified diffs for the # set of deltas contained in the patch. The rest of the patch, the part # that BitKeeper cares about, is below these diffs. # User: acurtis # Host: ltantony.xiphis.org # Root: /usr/home/antony/work2/p2-bug5892.3 --- 1.178/sql/handler.cc 2005-07-04 01:50:00 +01:00 +++ 1.179/sql/handler.cc 2005-07-19 10:35:07 +01:00 @@ -2434,6 +2434,7 @@ known_extensions_id= mysys_usage_id; found_exts.push_back((char*) triggers_file_ext); + found_exts.push_back((char*) trigname_file_ext); for (types= sys_table_types; types->type; types++) { if (*types->value == SHOW_OPTION_YES) --- 1.151/sql/item.h 2005-07-13 15:05:50 +01:00 +++ 1.152/sql/item.h 2005-07-19 10:35:07 +01:00 @@ -1776,7 +1776,7 @@ */ enum trg_action_time_type { - TRG_ACTION_BEFORE= 0, TRG_ACTION_AFTER= 1 + TRG_ACTION_BEFORE= 0, TRG_ACTION_AFTER= 1, TRG_ACTION_MAX }; /* @@ -1784,7 +1784,7 @@ */ enum trg_event_type { - TRG_EVENT_INSERT= 0 , TRG_EVENT_UPDATE= 1, TRG_EVENT_DELETE= 2 + TRG_EVENT_INSERT= 0 , TRG_EVENT_UPDATE= 1, TRG_EVENT_DELETE= 2, TRG_EVENT_MAX }; class Table_triggers_list; --- 1.137/sql/lex.h 2005-07-01 13:51:49 +01:00 +++ 1.138/sql/lex.h 2005-07-19 10:35:07 +01:00 @@ -497,6 +497,7 @@ { "TRAILING", SYM(TRAILING)}, { "TRANSACTION", SYM(TRANSACTION_SYM)}, { "TRIGGER", SYM(TRIGGER_SYM)}, + { "TRIGGERS", SYM(TRIGGERS_SYM)}, { "TRUE", SYM(TRUE_SYM)}, { "TRUNCATE", SYM(TRUNCATE_SYM)}, { "TYPE", SYM(TYPE_SYM)}, --- 1.326/sql/mysql_priv.h 2005-07-13 14:50:27 +01:00 +++ 1.327/sql/mysql_priv.h 2005-07-19 10:35:08 +01:00 @@ -1077,6 +1077,7 @@ extern const char *myisam_recover_options_str; extern const char *in_left_expr_name, *in_additional_cond; extern const char * const triggers_file_ext; +extern const char * const trigname_file_ext; extern Eq_creator eq_creator; extern Ne_creator ne_creator; extern Gt_creator gt_creator; --- 1.478/sql/mysqld.cc 2005-07-04 01:44:31 +01:00 +++ 1.479/sql/mysqld.cc 2005-07-19 10:35:08 +01:00 @@ -5722,6 +5722,7 @@ {"Com_show_status", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_STATUS]), SHOW_LONG_STATUS}, {"Com_show_storage_engines", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_STORAGE_ENGINES]), SHOW_LONG_STATUS}, {"Com_show_tables", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TABLES]), SHOW_LONG_STATUS}, + {"Com_show_triggers", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TRIGGERS]), SHOW_LONG_STATUS}, {"Com_show_variables", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_VARIABLES]), SHOW_LONG_STATUS}, {"Com_show_warnings", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_WARNS]), SHOW_LONG_STATUS}, {"Com_slave_start", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SLAVE_START]), SHOW_LONG_STATUS}, --- 1.267/sql/sql_base.cc 2005-07-13 10:57:01 +01:00 +++ 1.268/sql/sql_base.cc 2005-07-19 10:35:09 +01:00 @@ -1712,7 +1712,7 @@ !my_strcasecmp(system_charset_info, name, "proc")) entry->s->system_table= 1; - if (Table_triggers_list::check_n_load(thd, db, name, entry)) + if (Table_triggers_list::check_n_load(thd, db, name, entry, 0)) goto err; /* --- 1.189/sql/sql_lex.h 2005-07-13 14:50:27 +01:00 +++ 1.190/sql/sql_lex.h 2005-07-19 10:35:10 +01:00 @@ -57,6 +57,7 @@ SQLCOM_SHOW_PROCESSLIST, SQLCOM_SHOW_MASTER_STAT, SQLCOM_SHOW_SLAVE_STAT, SQLCOM_SHOW_GRANTS, SQLCOM_SHOW_CREATE, SQLCOM_SHOW_CHARSETS, SQLCOM_SHOW_COLLATIONS, SQLCOM_SHOW_CREATE_DB, SQLCOM_SHOW_TABLE_STATUS, + SQLCOM_SHOW_TRIGGERS, SQLCOM_LOAD,SQLCOM_SET_OPTION,SQLCOM_LOCK_TABLES,SQLCOM_UNLOCK_TABLES, SQLCOM_GRANT, --- 1.445/sql/sql_parse.cc 2005-07-13 15:05:50 +01:00 +++ 1.446/sql/sql_parse.cc 2005-07-19 10:35:10 +01:00 @@ -2104,6 +2104,7 @@ case SCH_TABLE_NAMES: case SCH_TABLES: case SCH_VIEWS: + case SCH_TRIGGERS: #ifdef DONT_ALLOW_SHOW_COMMANDS my_message(ER_NOT_ALLOWED_COMMAND, ER(ER_NOT_ALLOWED_COMMAND), MYF(0)); /* purecov: inspected */ --- 1.253/sql/sql_show.cc 2005-07-07 14:48:40 +01:00 +++ 1.254/sql/sql_show.cc 2005-07-19 10:35:11 +01:00 @@ -21,6 +21,7 @@ #include "sql_select.h" // For select_describe #include "repl_failsafe.h" #include "sp_head.h" +#include "sql_trigger.h" #include <my_dir.h> #ifdef HAVE_BERKELEY_DB @@ -1696,6 +1697,7 @@ break; case SQLCOM_SHOW_TABLES: case SQLCOM_SHOW_TABLE_STATUS: + case SQLCOM_SHOW_TRIGGERS: index_field_values->db_value= lex->current_select->db; index_field_values->table_value= wild; break; @@ -2963,6 +2965,94 @@ } +static const LEX_STRING trg_action_time_type_name[]= +{ + { (char *) STRING_WITH_LEN("BEFORE") }, + { (char *) STRING_WITH_LEN("AFTER") } +}; + +static const LEX_STRING trg_event_type_name[]= +{ + { (char *) STRING_WITH_LEN("INSERT") }, + { (char *) STRING_WITH_LEN("UPDATE") }, + { (char *) STRING_WITH_LEN("DELETE") } +}; + +static bool store_trigger(THD *thd, TABLE *table, const char *db, + const char *tname, LEX_STRING *trigger_name, + enum trg_event_type event, + enum trg_action_time_type timing, + int order, + LEX_STRING *trigger_stmt, + TIME *created) +{ + TIME time; + CHARSET_INFO *cs= system_charset_info; + restore_record(table, s->default_values); + table->field[1]->store(db, strlen(db), cs); + table->field[2]->store(trigger_name->str, trigger_name->length, cs); + table->field[3]->store(trg_event_type_name[event].str, + trg_event_type_name[event].length, cs); + table->field[5]->store(db, strlen(db), cs); + table->field[6]->store(tname, strlen(tname), cs); + table->field[7]->store((longlong)order); + table->field[9]->store(trigger_stmt->str, trigger_stmt->length, cs); + table->field[10]->store("ROW", 3, cs); + table->field[11]->store(trg_action_time_type_name[timing].str, + trg_action_time_type_name[timing].length, cs); + table->field[14]->store("OLD", 3, cs); + table->field[15]->store("NEW", 3, cs); + table->field[16]->store_time(created, MYSQL_TIMESTAMP_DATETIME); + return schema_table_store_record(thd, table); +} + + +static int get_schema_triggers_record(THD *thd, struct st_table_list *tables, + TABLE *table, bool res, + const char *base_name, + const char *file_name) +{ + DBUG_ENTER("get_schema_triggers_record"); + /* + res can be non zero value when processed table is a view + or error happened during opening of processed table + */ + if (res) + { + if (!tables->view) + push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN, + thd->net.last_errno, thd->net.last_error); + thd->clear_error(); + DBUG_RETURN(0); + } + if (!tables->view && tables->table->triggers) + { + Table_triggers_list *triggers= tables->table->triggers; + int event, timing, order; + for (event= TRG_EVENT_INSERT; event < TRG_EVENT_MAX; event++) + { + for (timing= TRG_ACTION_BEFORE; timing < TRG_ACTION_MAX; timing++) + { + LEX_STRING trigger_name; + LEX_STRING trigger_stmt; + TIME created; + if (triggers->get_trigger_info(thd, (enum trg_event_type) event, + (enum trg_action_time_type)timing, + 0, &trigger_name, &trigger_stmt, + &created)) + continue; + if (store_trigger(thd, table, base_name, file_name, &trigger_name, + (enum trg_event_type) event, + (enum trg_action_time_type) timing, 0, + &trigger_stmt, &created)) + DBUG_RETURN(1); + } + } + } + DBUG_RETURN(0); +} + + void store_key_column_usage(TABLE *table, const char*db, const char *tname, const char *key_name, uint key_len, const char *con_type, uint con_len, longlong idx) @@ -3847,6 +3937,29 @@ }; +ST_FIELD_INFO triggers_fields_info[]= +{ + {"TRIGGER_CATALOG", FN_REFLEN, MYSQL_TYPE_STRING, 0, 1, 0}, + {"TRIGGER_SCHEMA",NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0}, + {"TRIGGER_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, "Trigger"}, + {"EVENT_MANIPULATION", 6, MYSQL_TYPE_STRING, 0, 0, "Event"}, + {"EVENT_OBJECT_CATALOG", FN_REFLEN, MYSQL_TYPE_STRING, 0, 1, 0}, + {"EVENT_OBJECT_SCHEMA",NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0}, + {"EVENT_OBJECT_TABLE", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, "Table"}, + {"ACTION_ORDER", 4, MYSQL_TYPE_LONG, 0, 0, 0}, + {"ACTION_CONDITION", 65535, MYSQL_TYPE_STRING, 0, 1, 0}, + {"ACTION_STATEMENT", 65535, MYSQL_TYPE_STRING, 0, 0, "Statement"}, + {"ACTION_ORIENTATION", 9, MYSQL_TYPE_STRING, 0, 0, 0}, + {"ACTION_TIMING", 6, MYSQL_TYPE_STRING, 0, 0, "Timing"}, + {"ACTION_REFERENCE_OLD_TABLE", NAME_LEN, MYSQL_TYPE_STRING, 0, 1, 0}, + {"ACTION_REFERENCE_NEW_TABLE", NAME_LEN, MYSQL_TYPE_STRING, 0, 1, 0}, + {"ACTION_REFERENCE_OLD_ROW", 3, MYSQL_TYPE_STRING, 0, 0, 0}, + {"ACTION_REFERENCE_NEW_ROW", 3, MYSQL_TYPE_STRING, 0, 0, 0}, + {"CREATED", 0, MYSQL_TYPE_TIMESTAMP, 0, 0, "Created"}, + {0, 0, MYSQL_TYPE_STRING, 0, 0, 0} +}; + + ST_FIELD_INFO variables_fields_info[]= { {"Variable_name", 80, MYSQL_TYPE_STRING, 0, 0, "Variable_name"}, @@ -3897,6 +4010,8 @@ fill_open_tables, make_old_format, 0, -1, -1, 1}, {"STATUS", variables_fields_info, create_schema_table, fill_status, make_old_format, 0, -1, -1, 1}, + {"TRIGGERS", triggers_fields_info, create_schema_table, + get_all_tables, make_old_format, get_schema_triggers_record, 5, 6, 0}, {"VARIABLES", variables_fields_info, create_schema_table, fill_variables, make_old_format, 0, -1, -1, 1}, {0, 0, 0, 0, 0, 0, 0, 0, 0} --- 1.260/sql/sql_table.cc 2005-07-13 10:48:03 +01:00 +++ 1.261/sql/sql_table.cc 2005-07-19 10:35:11 +01:00 @@ -23,6 +23,8 @@ #include <hash.h> #include <myisam.h> #include <my_dir.h> +#include "sp_head.h" +#include "sql_trigger.h" #ifdef __WIN__ #include <io.h> @@ -290,16 +292,14 @@ if (!(new_error=my_delete(path,MYF(MY_WME)))) { some_tables_deleted=1; - /* - Destroy triggers for this table if there are any. - - We won't need this as soon as we will have new .FRM format, - in which we will store trigger definitions in the same .FRM - files as table descriptions. - */ - strmov(end, triggers_file_ext); - if (!access(path, F_OK)) - new_error= my_delete(path, MYF(MY_WME)); + if (Table_triggers_list::drop_all_triggers(thd, db, + table->table_name)) + { + push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_WARN_TRG_DROP_FAILURE, + ER(ER_WARN_TRG_DROP_FAILURE), + db, table->table_name); + } } error|= new_error; } --- 1.404/sql/sql_yacc.yy 2005-07-11 18:10:45 +01:00 +++ 1.405/sql/sql_yacc.yy 2005-07-19 10:35:13 +01:00 @@ -38,6 +38,7 @@ #include "sp_pcontext.h" #include "sp_rcontext.h" #include "sp.h" +#include "sql_trigger.h" #include <myisam.h> #include <myisammrg.h> @@ -598,6 +599,7 @@ %token TRAILING %token TRANSACTION_SYM %token TRIGGER_SYM +%token TRIGGERS_SYM %token TRIM %token TRUE_SYM %token TRUNCATE_SYM @@ -1265,7 +1267,7 @@ } opt_view_list AS select_init check_option {} - | CREATE TRIGGER_SYM ident trg_action_time trg_event + | CREATE TRIGGER_SYM sp_name trg_action_time trg_event ON table_ident FOR_SYM EACH_SYM ROW_SYM { LEX *lex= Lex; @@ -1284,6 +1286,7 @@ sp->m_type= TYPE_ENUM_TRIGGER; lex->sphead= sp; + lex->spname= $3; /* We have to turn of CLIENT_MULTI_QUERIES while parsing a stored procedure, otherwise yylex will chop it into pieces @@ -1294,7 +1297,7 @@ bzero((char *)&lex->sp_chistics, sizeof(st_sp_chistics)); lex->sphead->m_chistics= &lex->sp_chistics; - lex->sphead->m_body_begin= lex->tok_start; + lex->sphead->m_body_begin= lex->ptr; } sp_proc_stmt { @@ -1302,14 +1305,12 @@ sp_head *sp= lex->sphead; lex->sql_command= SQLCOM_CREATE_TRIGGER; - sp->init_strings(YYTHD, lex, NULL); + sp->init_strings(YYTHD, lex, $3); /* Restore flag if it was cleared above */ if (sp->m_old_cmq) YYTHD->client_capabilities |= CLIENT_MULTI_QUERIES; sp->restore_thd_mem_root(YYTHD); - lex->ident= $3; - /* We have to do it after parsing trigger body, because some of sp_proc_stmt alternatives are not saving/restoring LEX, so @@ -5902,19 +5903,11 @@ lex->sql_command= SQLCOM_DROP_VIEW; lex->drop_if_exists= $3; } - | DROP TRIGGER_SYM ident '.' ident + | DROP TRIGGER_SYM sp_name { LEX *lex= Lex; - lex->sql_command= SQLCOM_DROP_TRIGGER; - /* QQ: Could we loosen lock type in certain cases ? */ - if (!lex->select_lex.add_table_to_list(YYTHD, - new Table_ident($3), - (LEX_STRING*) 0, - TL_OPTION_UPDATING, - TL_WRITE)) - YYABORT; - lex->ident= $5; + lex->spname= $3; } ; @@ -6279,6 +6272,15 @@ if (prepare_schema_table(YYTHD, lex, 0, SCH_TABLE_NAMES)) YYABORT; } + | opt_full TRIGGERS_SYM opt_db wild_and_where + { + LEX *lex= Lex; + lex->sql_command= SQLCOM_SELECT; + lex->orig_sql_command= SQLCOM_SHOW_TRIGGERS; + lex->select_lex.db= $3; + if (prepare_schema_table(YYTHD, lex, 0, SCH_TRIGGERS)) + YYABORT; + } | TABLE_SYM STATUS_SYM opt_db wild_and_where { LEX *lex= Lex; @@ -7581,6 +7583,7 @@ | TEMPTABLE_SYM {} | TEXT_SYM {} | TRANSACTION_SYM {} + | TRIGGERS_SYM {} | TIMESTAMP {} | TIMESTAMP_ADD {} | TIMESTAMP_DIFF {} --- 1.102/sql/table.h 2005-07-13 10:48:04 +01:00 +++ 1.103/sql/table.h 2005-07-19 10:35:13 +01:00 @@ -282,7 +282,7 @@ SCH_COLLATION_CHARACTER_SET_APPLICABILITY, SCH_PROCEDURES, SCH_STATISTICS, SCH_VIEWS, SCH_USER_PRIVILEGES, SCH_SCHEMA_PRIVILEGES, SCH_TABLE_PRIVILEGES, SCH_COLUMN_PRIVILEGES, SCH_TABLE_CONSTRAINTS, SCH_KEY_COLUMN_USAGE, - SCH_TABLE_NAMES, SCH_OPEN_TABLES, SCH_STATUS, SCH_VARIABLES + SCH_TABLE_NAMES, SCH_OPEN_TABLES, SCH_STATUS, SCH_TRIGGERS, SCH_VARIABLES }; --- 1.39/sql/share/errmsg.txt 2005-07-13 11:20:38 +01:00 +++ 1.40/sql/share/errmsg.txt 2005-07-19 10:35:09 +01:00 @@ -5370,3 +5370,9 @@ eng "Scale may not be larger than the precision (column '%-.64s')." ER_WRONG_LOCK_OF_SYSTEM_TABLE eng "You can't combine write-locking of system '%-.64s.%-.64s' table with other tables" +ER_TRG_IN_WRONG_SCHEMA + eng "Trigger in wrong schema" +ER_WARN_TRG_DROP_FAILURE + eng "An error occuring while dropping triggers for '%-.64s.%-.64s'" +ER_WARN_TRG_DUPLICATE + eng "Table '%-.64s' has duplicate trigger name: '%-.64s'" --- 1.89/mysql-test/r/view.result 2005-07-12 23:58:11 +01:00 +++ 1.90/mysql-test/r/view.result 2005-07-19 10:35:06 +01:00 @@ -1245,7 +1245,7 @@ s1 select * from t1; s1 -drop trigger t1.t1_bi; +drop trigger t1_bi; drop view v1; drop table t1; create table t1 (s1 tinyint); --- 1.84/mysql-test/t/view.test 2005-07-12 23:58:11 +01:00 +++ 1.85/mysql-test/t/view.test 2005-07-19 10:35:07 +01:00 @@ -1182,7 +1182,7 @@ insert into v1 values (0); select * from v1; select * from t1; -drop trigger t1.t1_bi; +drop trigger t1_bi; drop view v1; drop table t1; --- 1.12/sql/parse_file.cc 2005-06-01 14:35:03 +01:00 +++ 1.13/sql/parse_file.cc 2005-07-19 10:35:08 +01:00 @@ -185,6 +185,8 @@ TABLE) parameters parameters description max_versions number of versions to save + exclusive do not overwrite previous version (ignores max_versions) + no_error suppress error message on failure RETURN FALSE - OK @@ -195,7 +197,7 @@ sql_create_definition_file(const LEX_STRING *dir, const LEX_STRING *file_name, const LEX_STRING *type, gptr base, File_option *parameters, - uint max_versions) + uint max_versions, bool exclusive, bool no_error) { File handler; IO_CACHE file; @@ -203,6 +205,7 @@ ulonglong old_version= ULONGLONG_MAX; int path_end; File_option *param; + myf errflag= no_error ? MYF(0) : MYF(MY_WME); DBUG_ENTER("sql_create_definition_file"); DBUG_PRINT("enter", ("Dir: %s, file: %s, base 0x%lx", dir->str, file_name->str, (ulong) base)); @@ -210,16 +213,20 @@ fn_format(path, file_name->str, dir->str, 0, MY_UNPACK_FILENAME); path_end= strlen(path); - // temporary file name - path[path_end]='~'; - path[path_end+1]= '\0'; - if ((handler= my_create(path, CREATE_MODE, O_RDWR | O_TRUNC, - MYF(MY_WME))) <= 0) + if (!exclusive) + { + // temporary file name + path[path_end]='~'; + path[path_end+1]= '\0'; + } + if ((handler= my_create(path, CREATE_MODE, + O_RDWR | (exclusive ? O_EXCL : O_TRUNC), + errflag)) <= 0) { DBUG_RETURN(TRUE); } - if (init_io_cache(&file, handler, 0, SEQ_READ_APPEND, 0L, 0, MYF(MY_WME))) + if (init_io_cache(&file, handler, 0, SEQ_READ_APPEND, 0L, 0, errflag)) goto err_w_file; // write header (file signature) @@ -242,10 +249,13 @@ if (end_io_cache(&file)) goto err_w_file; - if (my_close(handler, MYF(MY_WME))) + if (my_close(handler, errflag)) { DBUG_RETURN(TRUE); } + + if (exclusive) + DBUG_RETURN(FALSE) // archive copies management path[path_end]='\0'; @@ -262,7 +272,7 @@ fn_format(path_arc, "arc", dir->str, "", MY_UNPACK_FILENAME); if (access(path_arc, F_OK)) { - if (my_mkdir(path_arc, 0777, MYF(MY_WME))) + if (my_mkdir(path_arc, 0777, errflag)) { DBUG_RETURN(TRUE); } @@ -270,7 +280,7 @@ my_snprintf(path_to, FN_REFLEN, "%s/%s-%04lu", path_arc, file_name->str, (ulong) old_version); - if (my_rename(path, path_to, MYF(MY_WME))) + if (my_rename(path, path_to, errflag)) { DBUG_RETURN(TRUE); } @@ -281,7 +291,7 @@ my_snprintf(path_to, FN_REFLEN, "%s/%s-%04lu", path_arc, file_name->str, (ulong)(old_version - max_versions)); - if (!access(path_arc, F_OK) && my_delete(path_to, MYF(MY_WME))) + if (!access(path_arc, F_OK) && my_delete(path_to, errflag)) { DBUG_RETURN(TRUE); } @@ -289,7 +299,7 @@ } else { - if (my_delete(path, MYF(MY_WME))) // no backups + if (my_delete(path, errflag)) // no backups { DBUG_RETURN(TRUE); } @@ -301,7 +311,7 @@ char path_to[FN_REFLEN]; memcpy(path_to, path, path_end+1); path[path_end]='~'; - if (my_rename(path, path_to, MYF(MY_WME))) + if (my_rename(path, path_to, errflag)) { DBUG_RETURN(TRUE); } @@ -310,7 +320,7 @@ err_w_cache: end_io_cache(&file); err_w_file: - my_close(handler, MYF(MY_WME)); + my_close(handler, errflag); DBUG_RETURN(TRUE); } @@ -406,6 +416,7 @@ parser->start= sign + 1; parser->content_ok= 1; + parser->file_ctime= stat_info.st_ctime; DBUG_RETURN(parser); --- 1.7/sql/parse_file.h 2004-10-06 23:44:11 +01:00 +++ 1.8/sql/parse_file.h 2005-07-19 10:35:09 +01:00 @@ -45,19 +45,22 @@ my_bool sql_create_definition_file(const LEX_STRING *dir, const LEX_STRING *file_name, const LEX_STRING *type, - gptr base, File_option *parameters, uint versions); + gptr base, File_option *parameters, uint versions, + bool exclusive, bool no_error); class File_parser: public Sql_alloc { char *buff, *start, *end; LEX_STRING file_type; my_bool content_ok; + time_t file_ctime; public: - File_parser() :buff(0), start(0), end(0), content_ok(0) + File_parser() :buff(0), start(0), end(0), content_ok(0), file_ctime(0) { file_type.str= 0; file_type.length= 0; } my_bool ok() { return content_ok; } LEX_STRING *type() { return &file_type; } + time_t ctime() { return file_ctime; } my_bool parse(gptr base, MEM_ROOT *mem_root, struct File_option *parameters, uint required); --- 1.53/sql/sql_view.cc 2005-07-12 16:33:56 +01:00 +++ 1.54/sql/sql_view.cc 2005-07-19 10:35:12 +01:00 @@ -607,7 +607,7 @@ } if (sql_create_definition_file(&dir, &file, view_file_type, - (gptr)view, view_parameters, 3)) + (gptr)view, view_parameters, 3, 0, 0)) { DBUG_RETURN(thd->net.report_error? -1 : 1); } --- 1.11/mysql-test/r/trigger.result 2005-07-13 13:22:14 +01:00 +++ 1.12/mysql-test/r/trigger.result 2005-07-19 10:35:06 +01:00 @@ -12,13 +12,13 @@ select @a; @a 1 -drop trigger t1.trg; +drop trigger trg; create trigger trg before insert on t1 for each row set @a:=new.i; insert into t1 values (123); select @a; @a 123 -drop trigger t1.trg; +drop trigger trg; drop table t1; create table t1 (i int not null, j int); create trigger trg before insert on t1 for each row @@ -33,7 +33,7 @@ i j 1 10 2 3 -drop trigger t1.trg| +drop trigger trg| drop table t1| create table t1 (i int not null primary key); create trigger trg after insert on t1 for each row @@ -43,7 +43,7 @@ select @a; @a 2:3:4:5 -drop trigger t1.trg; +drop trigger trg; drop table t1; create table t1 (aid int not null primary key, balance int not null default 0); insert into t1 values (1, 1000), (2,3000); @@ -65,7 +65,7 @@ aid balance 1 1500 2 3000 -drop trigger t1.trg| +drop trigger trg| drop table t1| create table t1 (i int); insert into t1 values (1),(2),(3),(4); @@ -76,7 +76,7 @@ select @total_change; @total_change 2 -drop trigger t1.trg; +drop trigger trg; drop table t1; create table t1 (i int); insert into t1 values (1),(2),(3),(4); @@ -87,7 +87,7 @@ select @del_sum; @del_sum 6 -drop trigger t1.trg; +drop trigger trg; drop table t1; create table t1 (i int); insert into t1 values (1),(2),(3),(4); @@ -97,7 +97,7 @@ select @del; @del 1 -drop trigger t1.trg; +drop trigger trg; drop table t1; create table t1 (i int, j int); create trigger trg1 before insert on t1 for each row @@ -137,9 +137,9 @@ 1 20 2 -1 3 20 -drop trigger t1.trg1; -drop trigger t1.trg2; -drop trigger t1.trg3; +drop trigger trg1; +drop trigger trg2; +drop trigger trg3; drop table t1; create table t1 (id int not null primary key, data int); create trigger t1_bi before insert on t1 for each row @@ -197,7 +197,7 @@ event INSERT INTO t1 id=1 data='one' INSERT INTO t1 id=2 data='two' -drop trigger t1.t1_ai; +drop trigger t1_ai; create trigger t1_bi before insert on t1 for each row begin if exists (select id from t3 where id=new.fk) then @@ -271,6 +271,7 @@ 3 NULL drop table t1, t2; create table t1 (i int); +create table t3 (i int); create trigger trg before insert on t1 for each row set @a:= old.i; ERROR HY000: There is no OLD row in on INSERT trigger create trigger trg before delete on t1 for each row set @a:= new.i; @@ -292,14 +293,19 @@ ERROR HY000: Trigger already exists create trigger trg2 before insert on t1 for each row set @a:=1; ERROR HY000: Trigger already exists -drop trigger t1.trg; -drop trigger t1.trg; +create trigger trg before insert on t3 for each row set @a:=1; +ERROR HY000: Trigger already exists +create trigger trg2 before insert on t3 for each row set @a:=1; +drop trigger trg2; +drop trigger trg; +drop trigger trg; ERROR HY000: Trigger does not exist create view v1 as select * from t1; create trigger trg before insert on v1 for each row set @a:=1; ERROR HY000: Trigger's 'v1' is view or temporary table drop view v1; drop table t1; +drop table t3; create temporary table t1 (i int); create trigger trg before insert on t1 for each row set @a:=1; ERROR HY000: Trigger's 't1' is view or temporary table @@ -307,7 +313,7 @@ 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; create table t1 (i int) engine=myisam; insert into t1 values (1), (2); @@ -318,8 +324,8 @@ select @del_before, @del_after; @del_before @del_after 3 3 -drop trigger t1.trg1; -drop trigger t1.trg2; +drop trigger trg1; +drop trigger trg2; drop table t1; create table t1 (a int); create trigger trg1 before insert on t1 for each row set new.a= 10; @@ -336,6 +342,15 @@ create trigger trg1 before insert on t1 for each row set @a:= 1; drop database mysqltest; use test; +create database mysqltest; +create table mysqltest.t1 (i int); +create trigger trg1 before insert on mysqltest.t1 for each row set @a:= 1; +ERROR HY000: Trigger in wrong schema +use mysqltest; +create trigger test.trg1 before insert on t1 for each row set @a:= 1; +ERROR HY000: Trigger in wrong schema +drop database mysqltest; +use test; create table t1 (i int, j int default 10, k int not null, key (k)); create table t2 (i int); insert into t1 (i, k) values (1, 1); @@ -549,7 +564,7 @@ 1 1 2 2 alter table t1 add primary key (i); -drop trigger t1.bi; +drop trigger bi; insert into t1 values (2, 4) on duplicate key update k= k + 10; ERROR 42S22: Unknown column 'bt' in 'NEW' select * from t1; --- 1.15/mysql-test/t/trigger.test 2005-07-13 13:22:14 +01:00 +++ 1.16/mysql-test/t/trigger.test 2005-07-19 10:35:07 +01:00 @@ -17,13 +17,13 @@ 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 @@ 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 @@ 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 @@ 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 @@ 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 @@ 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 @@ 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 @@ 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 @@ 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 @@ # 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 @@ # # 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 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 1361 @@ -322,6 +327,7 @@ drop view v1; drop table t1; +drop table t3; create temporary table t1 (i int); --error 1361 @@ -339,7 +345,7 @@ 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 @@ 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 @@ 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 @@ # 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; --- 1.20/sql/sql_trigger.cc 2005-07-09 18:55:10 +01:00 +++ 1.21/sql/sql_trigger.cc 2005-07-19 10:35:12 +01:00 @@ -16,27 +16,45 @@ #include "mysql_priv.h" +#include <my_dir.h> #include "sp_head.h" #include "sql_trigger.h" #include "parse_file.h" static const LEX_STRING triggers_file_type= {(char *)"TRIGGERS", 8}; +static const LEX_STRING trigname_file_type= {(char *)"TRIGGER", 7}; const char * const triggers_file_ext= ".TRG"; +const char * const trigname_file_ext= ".TRI"; /* Table of .TRG file field descriptors. We have here only one field now because in nearest future .TRG files will be merged into .FRM files (so we don't need something like md5 or created fields). + Have added trigger names so that statements don't have to be parsed + during drop operation. */ static File_option triggers_file_parameters[]= { - {{(char*)"triggers", 8}, offsetof(class Table_triggers_list, definitions_list), + {{(char*)"triggers", 8}, offsetof(TRIGGERS_INFO, definitions_list), + FILE_OPTIONS_STRLIST}, + {{(char*)"trigger_names", 13}, offsetof(TRIGGERS_INFO, names_list), FILE_OPTIONS_STRLIST}, {{0, 0}, 0, FILE_OPTIONS_STRING} }; +/* + Table of .TRI file field descriptors. + We have here only one field for associated table name. +*/ +static File_option trigname_file_parameters[]= +{ + {{(char*)"table", 5}, 0, + FILE_OPTIONS_STRING}, + {{0, 0}, 0, FILE_OPTIONS_STRING} +}; + /* Create or drop trigger for table. @@ -59,6 +77,10 @@ */ bool mysql_create_or_drop_trigger(THD *thd, TABLE_LIST *tables, bool create) { + LEX *lex= thd->lex; + char path[FN_REFLEN+1], dir_buff[FN_REFLEN], file_buff[FN_REFLEN]; + LEX_STRING dir, file, table_name; + int fd= -1; TABLE *table; bool result= 0; @@ -68,6 +90,12 @@ QQ: This function could be merged in mysql_alter_table() function But do we want this ? */ + + if (!create && + !(tables= lex->select_lex.add_table_to_list(thd, + mysql_table_for_trigger(thd, lex->spname, 0), + (LEX_STRING*) 0, TL_OPTION_UPDATING, TL_WRITE))) + DBUG_RETURN(TRUE); if (open_and_lock_tables(thd, tables)) DBUG_RETURN(TRUE); @@ -94,6 +122,17 @@ DBUG_RETURN(TRUE); } + /* + Trigger must be in the same schema as target table + */ + if (my_strcasecmp(system_charset_info, table->s->db, + lex->spname->m_db.str ? lex->spname->m_db.str : + thd->db)) + { + my_error(create ? ER_TRG_IN_WRONG_SCHEMA : ER_TRG_DOES_NOT_EXIST, MYF(0)); + DBUG_RETURN(TRUE); + } + if (!table->triggers) { if (!create) @@ -104,6 +143,7 @@ if (!(table->triggers= new (&table->mem_root) Table_triggers_list(table))) DBUG_RETURN(TRUE); + DBUG_PRINT("info",("new triggers list 0x%08x", table->triggers)); } /* @@ -131,10 +171,43 @@ } VOID(pthread_mutex_lock(&LOCK_open)); + + /* + Use the filesystem to enforce trigger namespace constraints + */ + + strxnmov(dir_buff, FN_REFLEN, mysql_data_home, "/", tables->db, "/", NullS); + dir.length= unpack_filename(dir_buff, dir_buff); + dir.str= dir_buff; + file.length= strxnmov(file_buff, FN_REFLEN, lex->spname->m_name.str, + trigname_file_ext, NullS) - file_buff; + file.str= file_buff; + fn_format(path, file.str, dir.str, 0, MY_UNPACK_FILENAME); + table_name.str= tables->table_name; + table_name.length= strlen(tables->table_name); + + if (create && sql_create_definition_file(&dir, &file, &trigname_file_type, + (gptr)&table_name, + trigname_file_parameters, + 0, 1, 1)) + { + my_message(ER_TRG_ALREADY_EXISTS, ER(ER_TRG_ALREADY_EXISTS), MYF(0)); + result= TRUE; + goto end; + } + result= (create ? table->triggers->create_trigger(thd, tables): table->triggers->drop_trigger(thd, tables)); + if (((create && result) || (!create && !result)) && + my_delete(path, MYF(0))) + { + sql_print_warning("Could not delete trigger file: '%s', error: %d", + path, my_errno); + } + +end: /* It is sensible to invalidate table in any case */ close_cached_table(thd, table); VOID(pthread_mutex_unlock(&LOCK_open)); @@ -157,6 +230,37 @@ /* + Deletes the .TRG file for a table + + SYNOPSIS + rm_trigger_file() + path - char buffer of size FN_REFLEN + db - table's database name + table_name - table's name + table - pointer to table object + names_only - stop after loading raw trigger defn + + RETURN VALUE + False - success + True - error +*/ +static bool rm_trigger_file(char *path, char *db, char *table_name) +{ + /* + TODO: Probably instead of removing .TRG file we should move + to archive directory but this should be done as part of + parse_file.cc functionality (because we will need it + elsewhere). + */ + strxnmov(path, FN_REFLEN-1, mysql_data_home, "/", db, "/", + table_name, triggers_file_ext, NullS); + path[FN_REFLEN-1]= '\0'; + unpack_filename(path, path); + return my_delete(path, MYF(MY_WME)); +} + + +/* Create trigger for table. SYNOPSIS @@ -177,7 +281,7 @@ LEX_STRING dir, file; LEX_STRING *trg_def, *name; Item_trigger_field *trg_field; - List_iterator_fast<LEX_STRING> it(names_list); + List_iterator_fast<LEX_STRING> it(info.names_list); /* We don't allow creation of several triggers of the same type yet */ if (bodies[lex->trg_chistics.event][lex->trg_chistics.action_time]) @@ -189,7 +293,7 @@ /* Let us check if trigger with the same name exists */ while ((name= it++)) { - if (my_strcasecmp(system_charset_info, lex->ident.str, + if (my_strcasecmp(system_charset_info, lex->spname->m_name.str, name->str) == 0) { my_message(ER_TRG_ALREADY_EXISTS, ER(ER_TRG_ALREADY_EXISTS), MYF(0)); @@ -244,14 +348,19 @@ */ if (!(trg_def= (LEX_STRING *)alloc_root(&table->mem_root, sizeof(LEX_STRING))) || - definitions_list.push_back(trg_def, &table->mem_root)) + !(name= (LEX_STRING *)alloc_root(&table->mem_root, + sizeof(LEX_STRING))) || + info.definitions_list.push_back(trg_def, &table->mem_root) || + info.names_list.push_back(name, &table->mem_root)) return 1; trg_def->str= thd->query; trg_def->length= thd->query_length; + *name= lex->spname->m_name; return sql_create_definition_file(&dir, &file, &triggers_file_type, - (gptr)this, triggers_file_parameters, 3); + (gptr)&info, triggers_file_parameters, 3, + 0, 0); } @@ -272,14 +381,14 @@ { LEX *lex= thd->lex; LEX_STRING *name; - List_iterator_fast<LEX_STRING> it_name(names_list); - List_iterator<LEX_STRING> it_def(definitions_list); + List_iterator_fast<LEX_STRING> it_name(info.names_list); + List_iterator<LEX_STRING> it_def(info.definitions_list); while ((name= it_name++)) { it_def++; - if (my_strcasecmp(system_charset_info, lex->ident.str, + if (my_strcasecmp(system_charset_info, lex->spname->m_name.str, name->str) == 0) { /* @@ -288,20 +397,10 @@ */ it_def.remove(); - if (definitions_list.is_empty()) + if (info.definitions_list.is_empty()) { char path[FN_REFLEN]; - - /* - TODO: Probably instead of removing .TRG file we should move - to archive directory but this should be done as part of - parse_file.cc functionality (because we will need it - elsewhere). - */ - strxnmov(path, FN_REFLEN, mysql_data_home, "/", tables->db, "/", - tables->table_name, triggers_file_ext, NullS); - unpack_filename(path, path); - return my_delete(path, MYF(MY_WME)); + return rm_trigger_file(path, tables->db, tables->table_name); } else { @@ -317,8 +416,8 @@ file.str= file_buff; return sql_create_definition_file(&dir, &file, &triggers_file_type, - (gptr)this, - triggers_file_parameters, 3); + (gptr)&info, + triggers_file_parameters, 3, 0, 0); } } } @@ -330,8 +429,8 @@ Table_triggers_list::~Table_triggers_list() { - for (int i= 0; i < 3; i++) - for (int j= 0; j < 2; j++) + for (int i= 0; i < (int)TRG_EVENT_MAX; i++) + for (int j= 0; j < (int)TRG_ACTION_MAX; j++) delete bodies[i][j]; if (record1_field) @@ -388,13 +487,15 @@ db - table's database name table_name - table's name table - pointer to table object + names_only - stop after loading raw trigger defn RETURN VALUE False - success True - error */ bool Table_triggers_list::check_n_load(THD *thd, const char *db, - const char *table_name, TABLE *table) + const char *table_name, TABLE *table, + bool names_only) { char path_buff[FN_REFLEN]; LEX_STRING path; @@ -428,11 +529,12 @@ if (!triggers) DBUG_RETURN(1); - if (parser->parse((gptr)triggers, &table->mem_root, + if (parser->parse((gptr)&triggers->info, &table->mem_root, triggers_file_parameters, 1)) DBUG_RETURN(1); table->triggers= triggers; + DBUG_PRINT("info",("new triggers list 0x%08x", triggers)); /* Construct key that will represent triggers for this table in the set @@ -446,22 +548,30 @@ strmov(strmov(strmov(triggers->sroutines_key.str+1, db), "."), table_name); + if (names_only && triggers->info.names_list.elements) + DBUG_RETURN(0); + /* TODO: This could be avoided if there is no triggers for UPDATE and DELETE. */ - if (triggers->prepare_record1_accessors(table)) + if (!names_only && triggers->prepare_record1_accessors(table)) DBUG_RETURN(1); - List_iterator_fast<LEX_STRING> it(triggers->definitions_list); + List_iterator_fast<LEX_STRING> it(triggers->info.definitions_list); LEX_STRING *trg_create_str, *trg_name_str; char *trg_name_buff; LEX *old_lex= thd->lex, lex; + bool old_fmt= test(triggers->info.names_list.elements == 0); thd->lex= &lex; while ((trg_create_str= it++)) { + char path_buff[FN_REFLEN+1]; + LEX_STRING path, trg_table_name; + int fd; + lex_start(thd, (uchar*)trg_create_str->str, trg_create_str->length); if (yyparse((void *)thd) || thd->is_fatal_error) @@ -478,24 +588,65 @@ goto err_with_lex_cleanup; } + strxnmov(path_buff, FN_REFLEN, mysql_data_home, "/", db, "/", + lex.spname->m_name.str, trigname_file_ext, NullS); + path.length= unpack_filename(path_buff, path_buff); + path.str= path_buff; + + if (!(parser= sql_parse_prepare(&path, thd->mem_root, 1)) || + strncmp(trigname_file_type.str, parser->type()->str, + parser->type()->length) || + parser->parse((gptr)&trg_table_name, thd->mem_root, + trigname_file_parameters, 1)) + { + sql_print_warning("Cannot open trigger file: '%s', error: %d", + path_buff, my_errno); + push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE, + ER_WARN_TRG_DUPLICATE, + "Missing name file for trigger '%s'", + lex.spname->m_name.str); + } + else + { + if (my_strcasecmp(system_charset_info, trg_table_name.str, + table_name)) + { + sql_print_warning("Table '%s' has duplicate trigger name: '%s' on '%s'", + table_name, lex.spname->m_name.str, + trg_table_name.str); + push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE, + ER_TRG_DOES_NOT_EXIST, + ER(ER_WARN_TRG_DUPLICATE), + table_name, lex.spname->m_name.str); + } + else + { + lex.sphead->m_created= (longlong) parser->ctime(); + } + } + triggers->bodies[lex.trg_chistics.event] [lex.trg_chistics.action_time]= lex.sphead; lex.sphead= 0; - if (!(trg_name_buff= alloc_root(&table->mem_root, - sizeof(LEX_STRING) + - lex.ident.length + 1))) - goto err_with_lex_cleanup; - - trg_name_str= (LEX_STRING *)trg_name_buff; - trg_name_buff+= sizeof(LEX_STRING); - memcpy(trg_name_buff, lex.ident.str, - lex.ident.length + 1); - trg_name_str->str= trg_name_buff; - trg_name_str->length= lex.ident.length; - - if (triggers->names_list.push_back(trg_name_str, &table->mem_root)) - goto err_with_lex_cleanup; + if (old_fmt) + { + if (!(trg_name_buff= alloc_root(&table->mem_root, + sizeof(LEX_STRING) + + lex.spname->m_name.length + 1))) + goto err_with_lex_cleanup; + + trg_name_str= (LEX_STRING *)trg_name_buff; + trg_name_buff+= sizeof(LEX_STRING); + memcpy(trg_name_buff, lex.spname->m_name.str, + lex.spname->m_name.length + 1); + trg_name_str->str= trg_name_buff; + trg_name_str->length= lex.spname->m_name.length; + + if (triggers->info.names_list.push_back(trg_name_str, + &table->mem_root)) + goto err_with_lex_cleanup; + } /* Let us bind Item_trigger_field objects representing access to fields @@ -508,7 +659,7 @@ */ for (Item_trigger_field *trg_field= (Item_trigger_field *)(lex.trg_table_fields.first); - trg_field; + trg_field && !names_only; trg_field= trg_field->next_trg_field) trg_field->setup_field(thd, table); @@ -535,4 +686,192 @@ } DBUG_RETURN(1); +} + + +/* + Obtains and returns trigger metadata + + SYNOPSIS + get_trigger_info() + thd - current thread context + event - trigger event type + time_type - trigger action time + order - ordinal index for trigger type, currently only 0 + name - returns name of trigger + stmt - returns statement of trigger + created - returns creation time of trigger + + RETURN VALUE + False - success + True - error +*/ +bool Table_triggers_list::get_trigger_info(THD *thd, trg_event_type event, + trg_action_time_type time_type, + int trigger_order, + LEX_STRING *trigger_name, + LEX_STRING *trigger_stmt, + TIME *created) +{ + sp_head *body; + DBUG_ENTER("get_trigger_info"); + if (trigger_order == 0 && (body= bodies[event][time_type])) + { + *trigger_name= body->m_name; + *trigger_stmt= body->m_body; + bzero(created, sizeof(TIME)); + + if (body->m_created) + { + struct tm tmp_tm; + time_t time= (time_t)body->m_created; + gmtime_r(&time, &tmp_tm); + localtime_to_TIME(created, &tmp_tm); + } + DBUG_RETURN(0); + } + DBUG_RETURN(1); +} + + +/* + Find table identifier from trigger identifier. + + SYNOPSIS + mysql_table_for_trigger() + thd - current thread context + trig - identifier for trigger + no_error - set if this should not emit an error message + + RETURN VALUE + 0 - error +*/ +Table_ident *mysql_table_for_trigger(THD *thd, sp_name *trig, bool no_error) +{ + const char *db= !trig->m_db.str ? thd->db : trig->m_db.str; + char path_buff[FN_REFLEN]; + LEX_STRING path; + File_parser *parser; + DBUG_ENTER("mysql_table_for_trigger"); + + strxnmov(path_buff, FN_REFLEN, mysql_data_home, "/", db, "/", + trig->m_name.str, trigname_file_ext, NullS); + path.length= unpack_filename(path_buff, path_buff); + path.str= path_buff; + if (!access(path_buff, F_OK) && + (parser= sql_parse_prepare(&path, thd->mem_root, 1))) + { + if (!strncmp(trigname_file_type.str, parser->type()->str, + parser->type()->length)) + { + LEX_STRING table_name; + if (!(parser->parse((gptr)&table_name, thd->mem_root, + trigname_file_parameters, 1))) + { + Table_ident *ident= trig->m_db.str ? + new Table_ident(thd, trig->m_db, table_name, 0) : + new Table_ident(table_name); + DBUG_RETURN(ident); + } + DBUG_RETURN(0); + } + } + + if (!no_error) + { + my_message(ER_TRG_DOES_NOT_EXIST, ER(ER_TRG_DOES_NOT_EXIST), MYF(0)); + } + DBUG_RETURN(0); +} + + +/* + Drop all triggers for table. + + SYNOPSIS + drop_all_triggers() + thd - current thread context + db - schema for table + name - name for table + + NOTE + The calling thread should hold the LOCK_open mutex; + + RETURN VALUE + False - success + True - error +*/ +bool Table_triggers_list::drop_all_triggers(THD *thd, char *db, char *name) +{ + TABLE table; + char path_buff[FN_REFLEN]; + LEX_STRING path, trg_table_name; + bool result= 0; + DBUG_ENTER("drop_all_triggers"); + + bzero(&table, sizeof(table)); + init_alloc_root(&table.mem_root, 8192, 0); + + safe_mutex_assert_owner(&LOCK_open); + + if (Table_triggers_list::check_n_load(thd, db, name, &table, 1)) + { + DBUG_PRINT("info",("failed to load triggers")); + result= 1; + goto end; + } + if (table.triggers) + { + LEX_STRING *trigger; + List_iterator_fast<LEX_STRING> it_name(table.triggers->info.names_list); + + while ((trigger= it_name++)) + { + File_parser *parser; + + strxnmov(path_buff, FN_REFLEN, mysql_data_home, "/", db, "/", + trigger->str, trigname_file_ext, NullS); + path.length= unpack_filename(path_buff, path_buff); + path.str= path_buff; + + if (access(path_buff, F_OK)) + continue; + if (!(parser= sql_parse_prepare(&path, thd->mem_root, 1)) || + strncmp(trigname_file_type.str, parser->type()->str, + parser->type()->length) || + parser->parse((gptr)&trg_table_name, thd->mem_root, + trigname_file_parameters, 1)) + sql_print_warning("Could not parse file '%s', deleting anyway.", + path_buff); + else + if (my_strcasecmp(system_charset_info, trg_table_name.str, name)) + { + sql_print_warning("Table '%s' has duplicate trigger name: '%s'", + name, trigger->str); + result= 1; + continue; + } + if (my_delete(path_buff, MYF(0))) + { + sql_print_warning("Could not delete trigger file: '%s', error: %d", + path, my_errno); + result= 1; + continue; + } + DBUG_PRINT("info",("dropped trigger %s", trigger->str)); + } + + if (rm_trigger_file(path_buff, db, name) || result) + { + DBUG_PRINT("info",("failed to drop triggers")); + result= 1; + goto end; + } + } + DBUG_PRINT("info",("dropped triggers for %s.%s",db,name)); +end: + if (table.triggers) + delete table.triggers; + free_root(&table.mem_root, MYF(0)); + DBUG_RETURN(result); } --- 1.9/sql/sql_trigger.h 2005-07-09 18:51:53 +01:00 +++ 1.10/sql/sql_trigger.h 2005-07-19 10:35:12 +01:00 @@ -15,6 +15,22 @@ Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ +typedef struct triggers_info_st +{ + /* + Names of triggers. + Should correspond to order of triggers on definitions_list, + used in CREATE/DROP TRIGGER for looking up trigger by name. + */ + List<LEX_STRING> names_list; + + /* + Field responsible for storing triggers definitions in file. + It have to be public because we are using it directly from parser. + */ + List<LEX_STRING> definitions_list; +} TRIGGERS_INFO; + /* This class holds all information about triggers of table. @@ -23,7 +39,7 @@ class Table_triggers_list: public Sql_alloc { /* Triggers as SPs grouped by event, action_time */ - sp_head *bodies[3][2]; + sp_head *bodies[TRG_EVENT_MAX][TRG_ACTION_MAX]; /* Copy of TABLE::Field array with field pointers set to TABLE::record[1] buffer instead of TABLE::record[0] (used for OLD values in on UPDATE @@ -39,12 +55,7 @@ Field **old_field; /* TABLE instance for which this triggers list object was created */ TABLE *table; - /* - Names of triggers. - Should correspond to order of triggers on definitions_list, - used in CREATE/DROP TRIGGER for looking up trigger by name. - */ - List<LEX_STRING> names_list; + TRIGGERS_INFO info; /* Key representing triggers for this table in set of all stored routines used by statement. @@ -55,12 +66,6 @@ LEX_STRING sroutines_key; public: - /* - Field responsible for storing triggers definitions in file. - It have to be public because we are using it directly from parser. - */ - List<LEX_STRING> definitions_list; - Table_triggers_list(TABLE *table_arg): record1_field(0), table(table_arg) { @@ -121,9 +126,14 @@ return res; } + bool get_trigger_info(THD *thd, trg_event_type event, + trg_action_time_type time_type, int trigger_order, + LEX_STRING *trigger_name, LEX_STRING *trigger_stmt, + TIME *created); static bool check_n_load(THD *thd, const char *db, const char *table_name, - TABLE *table); + TABLE *table, bool names_only); + static bool drop_all_triggers(THD *thd, char *db, char *table_name); bool has_delete_triggers() { @@ -143,3 +153,5 @@ private: bool prepare_record1_accessors(TABLE *table); }; + +Table_ident *mysql_table_for_trigger(THD *thd, sp_name *trig, bool no_error); --- 1.4/mysql-test/r/rpl_sp.result 2005-06-19 16:39:02 +01:00 +++ 1.5/mysql-test/r/rpl_sp.result 2005-07-19 10:35:06 +01:00 @@ -237,7 +237,7 @@ a 10 delete from t1; -drop trigger t1.trg; +drop trigger trg; insert into t1 values (1); select * from t1; a @@ -248,7 +248,7 @@ master-bin.000002 # Query 1 # use `mysqltest1`; create trigger trg before insert on t1 for each row set new.a= 10 master-bin.000002 # Query 1 # use `mysqltest1`; insert into t1 values (1) master-bin.000002 # Query 1 # use `mysqltest1`; delete from t1 -master-bin.000002 # Query 1 # use `mysqltest1`; drop trigger t1.trg +master-bin.000002 # Query 1 # use `mysqltest1`; drop trigger trg master-bin.000002 # Query 1 # use `mysqltest1`; insert into t1 values (1) select * from t1; a --- 1.3/mysql-test/t/rpl_sp.test 2005-05-06 17:52:14 +01:00 +++ 1.4/mysql-test/t/rpl_sp.test 2005-07-19 10:35:07 +01:00 @@ -249,7 +249,7 @@ connection master; delete from t1; -drop trigger t1.trg; +drop trigger trg; insert into t1 values (1); select * from t1; --replace_column 2 # 5 # --- 1.60/mysql-test/r/information_schema.result 2005-07-13 09:29:37 +01:00 +++ 1.61/mysql-test/r/information_schema.result 2005-07-19 10:35:06 +01:00 @@ -48,6 +48,7 @@ COLUMN_PRIVILEGES TABLE_CONSTRAINTS KEY_COLUMN_USAGE +TRIGGERS columns_priv db func @@ -77,6 +78,7 @@ TABLES TABLES TABLE_PRIVILEGES TABLE_PRIVILEGES TABLE_CONSTRAINTS TABLE_CONSTRAINTS +TRIGGERS TRIGGERS tables_priv tables_priv time_zone time_zone time_zone_leap_second time_zone_leap_second @@ -94,6 +96,7 @@ TABLES TABLES TABLE_PRIVILEGES TABLE_PRIVILEGES TABLE_CONSTRAINTS TABLE_CONSTRAINTS +TRIGGERS TRIGGERS tables_priv tables_priv time_zone time_zone time_zone_leap_second time_zone_leap_second @@ -111,6 +114,7 @@ TABLES TABLES TABLE_PRIVILEGES TABLE_PRIVILEGES TABLE_CONSTRAINTS TABLE_CONSTRAINTS +TRIGGERS TRIGGERS tables_priv tables_priv time_zone time_zone time_zone_leap_second time_zone_leap_second @@ -577,6 +581,7 @@ TABLES TABLE_PRIVILEGES TABLE_CONSTRAINTS +TRIGGERS create database information_schema; ERROR HY000: Can't create database 'information_schema'; database exists use information_schema; @@ -585,6 +590,7 @@ TABLES TEMPORARY TABLE_PRIVILEGES TEMPORARY TABLE_CONSTRAINTS TEMPORARY +TRIGGERS TEMPORARY create table t1(a int); ERROR 42S02: Unknown table 't1' in information_schema use test; @@ -596,6 +602,7 @@ TABLES TABLE_PRIVILEGES TABLE_CONSTRAINTS +TRIGGERS select table_name from tables where table_name='user'; table_name user @@ -690,7 +697,7 @@ CREATE VIEW a1 (t_CRASHME) AS SELECT f1 FROM t_crashme GROUP BY f1; CREATE VIEW a2 AS SELECT t_CRASHME FROM a1; count(*) -100 +101 drop view a2, a1; drop table t_crashme; select table_schema,table_name, column_name from @@ -701,6 +708,8 @@ information_schema ROUTINES ROUTINE_DEFINITION information_schema ROUTINES SQL_MODE information_schema VIEWS VIEW_DEFINITION +information_schema TRIGGERS ACTION_CONDITION +information_schema TRIGGERS ACTION_STATEMENT select table_name, column_name, data_type from information_schema.columns where data_type = 'datetime'; table_name column_name data_type @@ -709,6 +718,7 @@ TABLES CHECK_TIME datetime ROUTINES CREATED datetime ROUTINES LAST_ALTERED datetime +TRIGGERS CREATED datetime SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES A WHERE NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS B @@ -755,8 +765,71 @@ flush privileges; SELECT table_schema, count(*) FROM information_schema.TABLES GROUP BY TABLE_SCHEMA; table_schema count(*) -information_schema 15 +information_schema 16 mysql 17 +create table t1 (i int, j int); +create trigger trg1 before insert on t1 for each row +begin +if new.j > 10 then +set new.j := 10; +end if; +end| +create trigger trg2 before update on t1 for each row +begin +if old.i % 2 = 0 then +set new.j := -1; +end if; +end| +create trigger trg3 after update on t1 for each row +begin +if new.j = -1 then +set @fired:= "Yes"; +end if; +end| +show triggers; +Trigger Event Table Statement Timing Created +trg1 INSERT t1 +begin +if new.j > 10 then +set new.j := 10; +end if; +end BEFORE # +trg2 UPDATE t1 +begin +if old.i % 2 = 0 then +set new.j := -1; +end if; +end BEFORE # +trg3 UPDATE t1 +begin +if new.j = -1 then +set @fired:= "Yes"; +end if; +end AFTER # +select * from information_schema.triggers; +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED +NULL test trg1 INSERT NULL test t1 0 NULL +begin +if new.j > 10 then +set new.j := 10; +end if; +end ROW BEFORE NULL NULL OLD NEW # +NULL test trg2 UPDATE NULL test t1 0 NULL +begin +if old.i % 2 = 0 then +set new.j := -1; +end if; +end ROW BEFORE NULL NULL OLD NEW # +NULL test trg3 UPDATE NULL test t1 0 NULL +begin +if new.j = -1 then +set @fired:= "Yes"; +end if; +end ROW AFTER NULL NULL OLD NEW # +drop trigger trg1; +drop trigger trg2; +drop trigger trg3; +drop table t1; create database mysqltest; create table mysqltest.t1 (f1 int, f2 int); create table mysqltest.t2 (f1 int); --- 1.42/mysql-test/t/information_schema.test 2005-07-05 23:24:32 +01:00 +++ 1.43/mysql-test/t/information_schema.test 2005-07-19 10:35:06 +01:00 @@ -505,6 +505,43 @@ # SELECT table_schema, count(*) FROM information_schema.TABLES GROUP BY TABLE_SCHEMA; + +# +# TRIGGERS table test +# +create table t1 (i int, j int); + +delimiter |; +create trigger trg1 before insert on t1 for each row +begin + if new.j > 10 then + set new.j := 10; + end if; +end| +create trigger trg2 before update on t1 for each row +begin + if old.i % 2 = 0 then + set new.j := -1; + end if; +end| +create trigger trg3 after update on t1 for each row +begin + if new.j = -1 then + set @fired:= "Yes"; + end if; +end| +delimiter ;| +--replace_column 6 # +show triggers; +--replace_column 17 # +select * from information_schema.triggers; + +drop trigger trg1; +drop trigger trg2; +drop trigger trg3; +drop table t1; + + # # Bug #10964 Information Schema:Authorization check on privilege tables is improper # --- 1.2/mysql-test/r/information_schema_db.result 2005-05-24 11:35:16 +01:00 +++ 1.3/mysql-test/r/information_schema_db.result 2005-07-19 10:35:06 +01:00 @@ -16,11 +16,13 @@ COLUMN_PRIVILEGES TABLE_CONSTRAINTS KEY_COLUMN_USAGE +TRIGGERS show tables from INFORMATION_SCHEMA like 'T%'; Tables_in_information_schema (T%) TABLES TABLE_PRIVILEGES TABLE_CONSTRAINTS +TRIGGERS create database `inf%`; use `inf%`; show tables; --- 1.82/sql/sp.cc 2005-07-13 11:20:38 +01:00 +++ 1.83/sql/sp.cc 2005-07-19 10:35:09 +01:00 @@ -1442,8 +1442,8 @@ { Sroutine_hash_entry **last_cached_routine_ptr= (Sroutine_hash_entry **)lex->sroutines_list.next; - for (int i= 0; i < 3; i++) - for (int j= 0; j < 2; j++) + for (int i= 0; i < (int)TRG_EVENT_MAX; i++) + for (int j= 0; j < (int)TRG_ACTION_MAX; j++) if (triggers->bodies[i][j]) { (void)triggers->bodies[i][j]->add_used_tables_to_table_list(thd,
| Thread | ||
|---|---|---|
| • bk commit into 5.0 tree (acurtis:1.1923) BUG#5892 | antony | 19 Jul |
© 1995-2008 MySQL AB. All rights reserved.
Page generated in 0.973 sec. using MySQL 5.1.14-beta-log