diff options
49 files changed, 1665 insertions, 79 deletions
diff --git a/include/mysqld_error.h b/include/mysqld_error.h index 432919db04b..1787d98900d 100644 --- a/include/mysqld_error.h +++ b/include/mysqld_error.h @@ -374,4 +374,9 @@ #define ER_VIEW_INVALID 1355 #define ER_SP_NO_DROP_SP 1356 #define ER_SP_GOTO_IN_HNDLR 1357 -#define ER_ERROR_MESSAGES 358 +#define ER_TRG_ALREADY_EXISTS 1358 +#define ER_TRG_DOES_NOT_EXIST 1359 +#define ER_TRG_ON_VIEW_OR_TEMP_TABLE 1360 +#define ER_TRG_CANT_CHANGE_ROW 1361 +#define ER_TRG_NO_SUCH_ROW_IN_TRG 1362 +#define ER_ERROR_MESSAGES 363 diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result new file mode 100644 index 00000000000..a7a845fa3e0 --- /dev/null +++ b/mysql-test/r/trigger.result @@ -0,0 +1,171 @@ +drop table if exists t1, t2; +drop view if exists v1; +create table t1 (i int); +create trigger trg before insert on t1 for each row set @a:=1; +set @a:=0; +select @a; +@a +0 +insert into t1 values (1); +select @a; +@a +1 +drop trigger t1.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 table t1; +create table t1 (i int not null, j int); +create trigger trg before insert on t1 for each row +begin +if isnull(new.j) then +set new.j:= new.i * 10; +end if; +end| +insert into t1 (i) values (1)| +insert into t1 (i,j) values (2, 3)| +select * from t1| +i j +1 10 +2 3 +drop trigger t1.trg| +drop table t1| +create table t1 (i int not null primary key); +create trigger trg after insert on t1 for each row +set @a:= if(@a,concat(@a, ":", new.i), new.i); +set @a:=""; +insert into t1 values (2),(3),(4),(5); +select @a; +@a +2:3:4:5 +drop trigger t1.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); +create trigger trg before update on t1 for each row +begin +declare loc_err varchar(255); +if abs(new.balance - old.balance) > 1000 then +set new.balance:= old.balance; +set loc_err := concat("Too big change for aid = ", new.aid); +set @update_failed:= if(@update_failed, concat(@a, ":", loc_err), loc_err); +end if; +end| +set @update_failed:=""| +update t1 set balance=1500| +select @update_failed; +select * from t1| +@update_failed +Too big change for aid = 2 +aid balance +1 1500 +2 3000 +drop trigger t1.trg| +drop table t1| +create table t1 (i int); +insert into t1 values (1),(2),(3),(4); +create trigger trg after update on t1 for each row +set @total_change:=@total_change + new.i - old.i; +set @total_change:=0; +update t1 set i=3; +select @total_change; +@total_change +2 +drop trigger t1.trg; +drop table t1; +create table t1 (i int); +insert into t1 values (1),(2),(3),(4); +create trigger trg before delete on t1 for each row +set @del_sum:= @del_sum + old.i; +set @del_sum:= 0; +delete from t1 where i <= 3; +select @del_sum; +@del_sum +6 +drop trigger t1.trg; +drop table t1; +create table t1 (i int); +insert into t1 values (1),(2),(3),(4); +create trigger trg after delete on t1 for each row set @del:= 1; +set @del:= 0; +delete from t1 where i <> 0; +select @del; +@del +1 +drop trigger t1.trg; +drop table t1; +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| +set @fired:=""; +insert into t1 values (1,2),(2,3),(3,14); +select @fired; +@fired + +select * from t1; +i j +1 2 +2 3 +3 10 +update t1 set j= 20; +select @fired; +@fired +Yes +select * from t1; +i j +1 20 +2 -1 +3 20 +drop trigger t1.trg1; +drop trigger t1.trg2; +drop trigger t1.trg3; +drop table t1; +create table t1 (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; +ERROR HY000: There is no NEW row in on DELETE trigger +create trigger trg before update on t1 for each row set old.i:=1; +ERROR HY000: Updating of OLD row is not allowed in trigger +create trigger trg before delete on t1 for each row set new.i:=1; +ERROR HY000: There is no NEW row in on DELETE trigger +create trigger trg after update on t1 for each row set new.i:=1; +ERROR HY000: Updating of NEW row is not allowed in after trigger +create trigger trg before insert on t2 for each row set @a:=1; +ERROR 42S02: Table 'test.t2' doesn't exist +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 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; +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; +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 +drop table t1; diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test new file mode 100644 index 00000000000..f7b9ebc8d2c --- /dev/null +++ b/mysql-test/t/trigger.test @@ -0,0 +1,195 @@ +# +# Basic triggers test +# + +--disable_warnings +drop table if exists t1, t2; +drop view if exists v1; +--enable_warnings + +create table t1 (i int); + +# let us test some very simple trigger +create trigger trg before insert on t1 for each row set @a:=1; +set @a:=0; +select @a; +insert into t1 values (1); +select @a; +drop trigger t1.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 table t1; + +# Let us test before insert trigger +# Such triggers can be used for setting complex default values +create table t1 (i int not null, j int); +delimiter |; +create trigger trg before insert on t1 for each row +begin + if isnull(new.j) then + set new.j:= new.i * 10; + end if; +end| +insert into t1 (i) values (1)| +insert into t1 (i,j) values (2, 3)| +select * from t1| +drop trigger t1.trg| +drop table t1| +delimiter ;| + +# After insert trigger +# Useful for aggregating data +create table t1 (i int not null primary key); +create trigger trg after insert on t1 for each row + set @a:= if(@a,concat(@a, ":", new.i), new.i); +set @a:=""; +insert into t1 values (2),(3),(4),(5); +select @a; +drop trigger t1.trg; +drop table t1; + +# Before update trigger +# (In future we will achieve this via proper error handling in triggers) +create table t1 (aid int not null primary key, balance int not null default 0); +insert into t1 values (1, 1000), (2,3000); +delimiter |; +create trigger trg before update on t1 for each row +begin + declare loc_err varchar(255); + if abs(new.balance - old.balance) > 1000 then + set new.balance:= old.balance; + set loc_err := concat("Too big change for aid = ", new.aid); + set @update_failed:= if(@update_failed, concat(@a, ":", loc_err), loc_err); + end if; +end| +set @update_failed:=""| +update t1 set balance=1500| +select @update_failed; +select * from t1| +drop trigger t1.trg| +drop table t1| +delimiter ;| + +# After update trigger +create table t1 (i int); +insert into t1 values (1),(2),(3),(4); +create trigger trg after update on t1 for each row + set @total_change:=@total_change + new.i - old.i; +set @total_change:=0; +update t1 set i=3; +select @total_change; +drop trigger t1.trg; +drop table t1; + +# Before delete trigger +# This can be used for aggregation too :) +create table t1 (i int); +insert into t1 values (1),(2),(3),(4); +create trigger trg before delete on t1 for each row + set @del_sum:= @del_sum + old.i; +set @del_sum:= 0; +delete from t1 where i <= 3; +select @del_sum; +drop trigger t1.trg; +drop table t1; + +# After delete trigger. +# Just run out of imagination. +create table t1 (i int); +insert into t1 values (1),(2),(3),(4); +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 table t1; + +# Several triggers on one table +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 ;| +set @fired:=""; +insert into t1 values (1,2),(2,3),(3,14); +select @fired; +select * from t1; +update t1 set j= 20; +select @fired; +select * from t1; + +drop trigger t1.trg1; +drop trigger t1.trg2; +drop trigger t1.trg3; +drop table t1; + + +# +# Test of wrong column specifiers in triggers +# +create table t1 (i int); + +--error 1358 +create trigger trg before insert on t1 for each row set @a:= old.i; +--error 1358 +create trigger trg before delete on t1 for each row set @a:= new.i; +--error 1357 +create trigger trg before update on t1 for each row set old.i:=1; +--error 1358 +create trigger trg before delete on t1 for each row set new.i:=1; +--error 1357 +create trigger trg after update on t1 for each row set new.i:=1; +# TODO: We should also test wrong field names here, we don't do it now +# because proper error handling is not in place yet. + + +# +# Let us test various trigger creation errors +# +# +--error 1146 +create trigger trg before insert on t2 for each row set @a:=1; + +create trigger trg before insert on t1 for each row set @a:=1; +--error 1354 +create trigger trg after insert on t1 for each row set @a:=1; +--error 1354 +create trigger trg2 before insert on t1 for each row set @a:=1; +drop trigger t1.trg; + +--error 1355 +drop trigger t1.trg; + +create view v1 as select * from t1; +--error 1356 +create trigger trg before insert on v1 for each row set @a:=1; +drop view v1; + +drop table t1; + +create temporary table t1 (i int); +--error 1356 +create trigger trg before insert on t1 for each row set @a:=1; +drop table t1; diff --git a/sql/Makefile.am b/sql/Makefile.am index 175cc3786cf..e0fcfa0fd3c 100644 --- a/sql/Makefile.am +++ b/sql/Makefile.am @@ -61,7 +61,7 @@ noinst_HEADERS = item.h item_func.h item_sum.h item_cmpfunc.h \ spatial.h gstream.h client_settings.h tzfile.h \ tztime.h examples/ha_example.h examples/ha_archive.h \ sp_head.h sp_pcontext.h sp_rcontext.h sp.h sp_cache.h \ - parse_file.h sql_view.h + parse_file.h sql_view.h sql_trigger.h mysqld_SOURCES = sql_lex.cc sql_handler.cc \ item.cc item_sum.cc item_buff.cc item_func.cc \ item_cmpfunc.cc item_strfunc.cc item_timefunc.cc \ @@ -95,7 +95,7 @@ mysqld_SOURCES = sql_lex.cc sql_handler.cc \ tztime.cc my_time.c \ examples/ha_example.cc examples/ha_archive.cc \ sp_head.cc sp_pcontext.cc sp_rcontext.cc sp.cc \ - sp_cache.cc parse_file.cc + sp_cache.cc parse_file.cc sql_trigger.cc gen_lex_hash_SOURCES = gen_lex_hash.cc gen_lex_hash_LDADD = $(LDADD) $(CXXLDFLAGS) mysql_tzinfo_to_sql_SOURCES = mysql_tzinfo_to_sql.cc diff --git a/sql/item.cc b/sql/item.cc index efd94716dc7..afe76818726 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -24,6 +24,8 @@ #include "my_dir.h" #include "sp_rcontext.h" #include "sql_acl.h" +#include "sp_head.h" +#include "sql_trigger.h" static void mark_as_dependent(THD *thd, SELECT_LEX *last, SELECT_LEX *current, @@ -2381,6 +2383,97 @@ void Item_insert_value::print(String *str) str->append(')'); } + +/* + Bind item representing field of row being changed in trigger + to appropriate Field object. + + SYNOPSIS + setup_field() + thd - current thread context + table - table of trigger (and where we looking for fields) + event - type of trigger event + + NOTE + This function does almost the same as fix_fields() for Item_field + but is invoked during trigger definition parsing and takes TABLE + object as its argument. + + RETURN VALUES + 0 ok + 1 field was not found. +*/ +bool Item_trigger_field::setup_field(THD *thd, TABLE *table, + enum trg_event_type event) +{ + bool result= 1; + uint field_idx= (uint)-1; + bool save_set_query_id= thd->set_query_id; + + /* TODO: Think more about consequences of this step. */ + thd->set_query_id= 0; + + if (find_field_in_real_table(thd, table, field_name, + strlen(field_name), 0, 0, + &field_idx)) + { + field= (row_version == OLD_ROW && event == TRG_EVENT_UPDATE) ? + table->triggers->old_field[field_idx] : + table->field[field_idx]; + result= 0; + } + + thd->set_query_id= save_set_query_id; + + return result; +} + + +bool Item_trigger_field::eq(const Item *item, bool binary_cmp) const +{ + return item->type() == TRIGGER_FIELD_ITEM && + row_version == ((Item_trigger_field *)item)->row_version && + !my_strcasecmp(system_charset_info, field_name, + ((Item_trigger_field *)item)->field_name); +} + + +bool Item_trigger_field::fix_fields(THD *thd, + TABLE_LIST *table_list, + Item **items) +{ + /* + Since trigger is object tightly associated with TABLE object most + of its set up can be performed during trigger loading i.e. trigger + parsing! So we have little to do in fix_fields. :) + FIXME may be we still should bother about permissions here. + */ + DBUG_ASSERT(fixed == 0); + // QQ: May be this should be moved to setup_field? + set_field(field); + fixed= 1; + return 0; +} + + +void Item_trigger_field::print(String *str) +{ + str->append((row_version == NEW_ROW) ? "NEW" : "OLD", 3); + str->append('.'); + str->append(field_name); +} + + +void Item_trigger_field::cleanup() +{ + /* + Since special nature of Item_trigger_field we should not do most of + things from Item_field::cleanup() or Item_ident::cleanup() here. + */ + Item::cleanup(); +} + + /* If item is a const function, calculate it and return a const item The original item is freed if not returned diff --git a/sql/item.h b/sql/item.h index dd4a402bcb8..391e6f367b8 100644 --- a/sql/item.h +++ b/sql/item.h @@ -99,7 +99,7 @@ public: PROC_ITEM,COND_ITEM, REF_ITEM, FIELD_STD_ITEM, FIELD_VARIANCE_ITEM, INSERT_VALUE_ITEM, SUBSELECT_ITEM, ROW_ITEM, CACHE_ITEM, TYPE_HOLDER, - PARAM_ITEM}; + PARAM_ITEM, TRIGGER_FIELD_ITEM}; enum cond_result { COND_UNDEF,COND_OK,COND_TRUE,COND_FALSE }; @@ -440,6 +440,7 @@ public: class Item_field :public Item_ident { +protected: void set_field(Field *field); public: Field *field,*result_field; @@ -1196,6 +1197,57 @@ public: } }; + +/* + We need this two enums here instead of sql_lex.h because + at least one of them is used by Item_trigger_field interface. + + Time when trigger is invoked (i.e. before or after row actually + inserted/updated/deleted). +*/ +enum trg_action_time_type +{ + TRG_ACTION_BEFORE= 0, TRG_ACTION_AFTER= 1 +}; + +/* + Event on which trigger is invoked. +*/ +enum trg_event_type +{ + TRG_EVENT_INSERT= 0 , TRG_EVENT_UPDATE= 1, TRG_EVENT_DELETE= 2 +}; + +/* + Represents NEW/OLD version of field of row which is + changed/read in trigger. + + Note: For this item actual binding to Field object happens not during + fix_fields() (like for Item_field) but during parsing of trigger + definition, when table is opened, with special setup_field() call. +*/ +class Item_trigger_field : public Item_field +{ +public: + /* Is this item represents row from NEW or OLD row ? */ + enum row_version_type {OLD_ROW, NEW_ROW}; + row_version_type row_version; + + Item_trigger_field(row_version_type row_ver_par, + const char *field_name_par): + Item_field((const char *)NULL, (const char *)NULL, field_name_par), + row_version(row_ver_par) + {} + bool setup_field(THD *thd, TABLE *table, enum trg_event_type event); + enum Type type() const { return TRIGGER_FIELD_ITEM; } + bool eq(const Item *item, bool binary_cmp) const; + bool fix_fields(THD *, struct st_table_list *, Item **); + void print(String *str); + table_map used_tables() const { return (table_map)0L; } + void cleanup(); +}; + + class Item_cache: public Item { protected: diff --git a/sql/item_func.cc b/sql/item_func.cc index f9ab1886a23..a8c3feef19b 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -2563,6 +2563,16 @@ void Item_func_set_user_var::print(String *str) } +void Item_func_set_user_var::print_as_stmt(String *str) +{ + str->append("set @", 5); + str->append(name.str, name.length); + str->append(":=", 2); + args[0]->print(str); + str->append(')'); +} + + String * Item_func_get_user_var::val_str(String *str) { @@ -3312,6 +3322,11 @@ Item_func_sp::execute(Item **itp) sp_change_security_context(thd, m_sp, &save_ctx); #endif + /* + We don't need to surpress senfing of ok packet here (by setting + thd->net.no_send_ok to true), because we are not allowing statements + in functions now. + */ res= m_sp->execute_function(thd, args, arg_count, itp); #ifndef NO_EMBEDDED_ACCESS_CHECKS diff --git a/sql/item_func.h b/sql/item_func.h index 8636bcd6507..064bbb4937b 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -945,6 +945,7 @@ public: bool fix_fields(THD *thd, struct st_table_list *tables, Item **ref); void fix_length_and_dec(); void print(String *str); + void print_as_stmt(String *str); const char *func_name() const { return "set_user_var"; } }; diff --git a/sql/lex.h b/sql/lex.h index 957aa3159e7..b148fc7d6bc 100644 --- a/sql/lex.h +++ b/sql/lex.h @@ -167,6 +167,7 @@ static SYMBOL symbols[] = { { "DUMPFILE", SYM(DUMPFILE)}, { "DUPLICATE", SYM(DUPLICATE_SYM)}, { "DYNAMIC", SYM(DYNAMIC_SYM)}, + { "EACH", SYM(EACH_SYM)}, { "ELSE", SYM(ELSE)}, { "ELSEIF", SYM(ELSEIF_SYM)}, { "ENABLE", SYM(ENABLE_SYM)}, @@ -470,6 +471,7 @@ static SYMBOL symbols[] = { { "TO", SYM(TO_SYM)}, { "TRAILING", SYM(TRAILING)}, { "TRANSACTION", SYM(TRANSACTION_SYM)}, + { "TRIGGER", SYM(TRIGGER_SYM)}, { "TRUE", SYM(TRUE_SYM)}, { "TRUNCATE", SYM(TRUNCATE_SYM)}, { "TYPE", SYM(TYPE_SYM)}, diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index a100aa0cd3a..84370d94f47 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -472,6 +472,7 @@ int mysql_rm_table_part2_with_lock(THD *thd, TABLE_LIST *tables, bool log_query); int quick_rm_table(enum db_type base,const char *db, const char *table_name); +void close_cached_table(THD *thd, TABLE *table); bool mysql_rename_tables(THD *thd, TABLE_LIST *table_list); bool mysql_change_db(THD *thd,const char *name); void mysql_parse(THD *thd,char *inBuf,uint length); @@ -612,6 +613,7 @@ int mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, Item **conds); int mysql_delete(THD *thd, TABLE_LIST *table, COND *conds, SQL_LIST *order, ha_rows rows, ulong options); int mysql_truncate(THD *thd, TABLE_LIST *table_list, bool dont_send_ok=0); +int mysql_create_or_drop_trigger(THD *thd, TABLE_LIST *tables, bool create); TABLE *open_ltable(THD *thd, TABLE_LIST *table_list, thr_lock_type update); TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT* mem, bool *refresh); @@ -640,6 +642,10 @@ find_field_in_table(THD *thd, TABLE_LIST *table_list, bool check_grants_table, bool check_grants_view, bool allow_rowid, uint *cached_field_index_ptr); +Field * +find_field_in_real_table(THD *thd, TABLE *table, const char *name, + uint length, bool check_grants, bool allow_rowid, + uint *cached_field_index_ptr); #ifdef HAVE_OPENSSL #include <openssl/des.h> struct st_des_keyblock diff --git a/sql/parse_file.cc b/sql/parse_file.cc index 729f49dcbbe..d10d395d6e5 100644 --- a/sql/parse_file.cc +++ b/sql/parse_file.cc @@ -46,7 +46,7 @@ write_escaped_string(IO_CACHE *file, LEX_STRING *val_s) { /* Should be in sync with read_escaped_string() and - parse_quated_escaped_string() + parse_quoted_escaped_string() */ switch(*ptr) { case '\\': // escape character @@ -154,11 +154,10 @@ write_parameter(IO_CACHE *file, gptr base, File_option *parameter, LEX_STRING *str; while ((str= it++)) { - num.set((ulonglong)str->length, &my_charset_bin); - // ',' after string to detect list continuation + // We need ' ' after string to detect list continuation if ((!first && my_b_append(file, (const byte *)" ", 1)) || my_b_append(file, (const byte *)"\'", 1) || - my_b_append(file, (const byte *)str->str, str->length) || + write_escaped_string(file, str) || my_b_append(file, (const byte *)"\'", 1)) { DBUG_RETURN(TRUE); @@ -486,7 +485,7 @@ read_escaped_string(char *ptr, char *eol, LEX_STRING *str) return TRUE; /* Should be in sync with write_escaped_string() and - parse_quated_escaped_string() + parse_quoted_escaped_string() */ switch(*ptr) { case '\\': @@ -562,7 +561,7 @@ parse_escaped_string(char *ptr, char *end, MEM_ROOT *mem_root, LEX_STRING *str) */ static char * -parse_quated_escaped_string(char *ptr, char *end, +parse_quoted_escaped_string(char *ptr, char *end, MEM_ROOT *mem_root, LEX_STRING *str) { char *eol; @@ -684,7 +683,6 @@ File_parser::parse(gptr base, MEM_ROOT *mem_root, my_error(ER_FPARSER_ERROR_IN_PARAMETER, MYF(0), parameter->name.str, line); DBUG_RETURN(TRUE); - DBUG_RETURN(TRUE); } break; } @@ -724,6 +722,7 @@ File_parser::parse(gptr base, MEM_ROOT *mem_root, /* TODO: remove play with mem_root, when List will be able to store MEM_ROOT* pointer for list elements allocation + FIXME: we can't handle empty lists */ sql_mem= my_pthread_getspecific_ptr(MEM_ROOT*, THR_MALLOC); list= (List<LEX_STRING>*)(base + parameter->offset); @@ -741,7 +740,7 @@ File_parser::parse(gptr base, MEM_ROOT *mem_root, sizeof(LEX_STRING))) || list->push_back(str)) goto list_err; - if(!(ptr= parse_quated_escaped_string(ptr, end, mem_root, str))) + if(!(ptr= parse_quoted_escaped_string(ptr, end, mem_root, str))) goto list_err_w_message; switch (*ptr) { case '\n': diff --git a/sql/parse_file.h b/sql/parse_file.h index 6a426aa0423..cf34a5095a4 100644 --- a/sql/parse_file.h +++ b/sql/parse_file.h @@ -27,7 +27,8 @@ enum file_opt_type { FILE_OPTIONS_REV, /* Revision version number (ulonglong) */ FILE_OPTIONS_TIMESTAMP, /* timestamp (LEX_STRING have to be allocated with length 20 (19+1) */ - FILE_OPTIONS_STRLIST /* list of strings (List<char*>) */ + FILE_OPTIONS_STRLIST /* list of escaped strings + (List<LEX_STRING>) */ }; struct File_option diff --git a/sql/share/czech/errmsg.txt b/sql/share/czech/errmsg.txt index 35310f5210f..aa23a7a005a 100644 --- a/sql/share/czech/errmsg.txt +++ b/sql/share/czech/errmsg.txt @@ -370,3 +370,8 @@ character-set=latin2 "View '%-.64s.%-.64s' references invalid table(s) or column(s)" "Can't drop a %s from within another stored routine" "GOTO is not allowed in a stored procedure handler" +"Trigger already exists" +"Trigger does not exist" +"Trigger's '%-.64s' is view or temporary table" +"Updating of %s row is not allowed in %strigger" +"There is no %s row in %s trigger" diff --git a/sql/share/danish/errmsg.txt b/sql/share/danish/errmsg.txt index d6a0a9d81e8..ac66eff0b5f 100644 --- a/sql/share/danish/errmsg.txt +++ b/sql/share/danish/errmsg.txt @@ -364,3 +364,8 @@ character-set=latin1 "View '%-.64s.%-.64s' references invalid table(s) or column(s)" "Can't drop a %s from within another stored routine" "GOTO is not allowed in a stored procedure handler" +"Trigger already exists" +"Trigger does not exist" +"Trigger's '%-.64s' is view or temporary table" +"Updating of %s row is not allowed in %strigger" +"There is no %s row in %s trigger" diff --git a/sql/share/dutch/errmsg.txt b/sql/share/dutch/errmsg.txt index 8fd5277d4ec..1f85a9ed42f 100644 --- a/sql/share/dutch/errmsg.txt +++ b/sql/share/dutch/errmsg.txt @@ -372,3 +372,8 @@ character-set=latin1 "View '%-.64s.%-.64s' references invalid table(s) or column(s)" "Can't drop a %s from within another stored routine" "GOTO is not allowed in a stored procedure handler" +"Trigger already exists" +"Trigger does not exist" +"Trigger's '%-.64s' is view or temporary table" +"Updating of %s row is not allowed in %strigger" +"There is no %s row in %s trigger" diff --git a/sql/share/english/errmsg.txt b/sql/share/english/errmsg.txt index a60881b5df6..ebf58088c9f 100644 --- a/sql/share/english/errmsg.txt +++ b/sql/share/english/errmsg.txt @@ -361,3 +361,8 @@ character-set=latin1 "View '%-.64s.%-.64s' references invalid table(s) or column(s)" "Can't drop a %s from within another stored routine" "GOTO is not allowed in a stored procedure handler" +"Trigger already exists" +"Trigger does not exist" +"Trigger's '%-.64s' is view or temporary table" +"Updating of %s row is not allowed in %strigger" +"There is no %s row in %s trigger" diff --git a/sql/share/estonian/errmsg.txt b/sql/share/estonian/errmsg.txt index 2e8a9270cd3..b446f133241 100644 --- a/sql/share/estonian/errmsg.txt +++ b/sql/share/estonian/errmsg.txt @@ -366,3 +366,8 @@ character-set=latin7 "View '%-.64s.%-.64s' references invalid table(s) or column(s)" "Can't drop a %s from within another stored routine" "GOTO is not allowed in a stored procedure handler" +"Trigger already exists" +"Trigger does not exist" +"Trigger's '%-.64s' is view or temporary table" +"Updating of %s row is not allowed in %strigger" +"There is no %s row in %s trigger" diff --git a/sql/share/french/errmsg.txt b/sql/share/french/errmsg.txt index b8ec7866a68..db5647abcb6 100644 --- a/sql/share/french/errmsg.txt +++ b/sql/share/french/errmsg.txt @@ -361,3 +361,8 @@ character-set=latin1 "View '%-.64s.%-.64s' references invalid table(s) or column(s)" "Can't drop a %s from within another stored routine" "GOTO is not allowed in a stored procedure handler" +"Trigger already exists" +"Trigger does not exist" +"Trigger's '%-.64s' is view or temporary table" +"Updating of %s row is not allowed in %strigger" +"There is no %s row in %s trigger" diff --git a/sql/share/german/errmsg.txt b/sql/share/german/errmsg.txt index c28792cc8c6..ae9110ff301 100644 --- a/sql/share/german/errmsg.txt +++ b/sql/share/german/errmsg.txt @@ -373,3 +373,8 @@ character-set=latin1 "View '%-.64s.%-.64s' references invalid table(s) or column(s)" "Can't drop a %s from within another stored routine" "GOTO is not allowed in a stored procedure handler" +"Trigger already exists" +"Trigger does not exist" +"Trigger's '%-.64s' is view or temporary table" +"Updating of %s row is not allowed in %strigger" +"There is no %s row in %s trigger" diff --git a/sql/share/greek/errmsg.txt b/sql/share/greek/errmsg.txt index 8ceb24bd376..c674d038b16 100644 --- a/sql/share/greek/errmsg.txt +++ b/sql/share/greek/errmsg.txt @@ -361,3 +361,8 @@ character-set=greek "View '%-.64s.%-.64s' references invalid table(s) or column(s)" "Can't drop a %s from within another stored routine" "GOTO is not allowed in a stored procedure handler" +"Trigger already exists" +"Trigger does not exist" +"Trigger's '%-.64s' is view or temporary table" +"Updating of %s row is not allowed in %strigger" +"There is no %s row in %s trigger" diff --git a/sql/share/hungarian/errmsg.txt b/sql/share/hungarian/errmsg.txt index f47684f9225..47ff00223af 100644 --- a/sql/share/hungarian/errmsg.txt +++ b/sql/share/hungarian/errmsg.txt @@ -363,3 +363,8 @@ character-set=latin2 "View '%-.64s.%-.64s' references invalid table(s) or column(s)" "Can't drop a %s from within another stored routine" "GOTO is not allowed in a stored procedure handler" +"Trigger already exists" +"Trigger does not exist" +"Trigger's '%-.64s' is view or temporary table" +"Updating of %s row is not allowed in %strigger" +"There is no %s row in %s trigger" diff --git a/sql/share/italian/errmsg.txt b/sql/share/italian/errmsg.txt index eb241ef08a0..eae07ddefcc 100644 --- a/sql/share/italian/errmsg.txt +++ b/sql/share/italian/errmsg.txt @@ -361,3 +361,8 @@ character-set=latin1 "View '%-.64s.%-.64s' references invalid table(s) or column(s)" "Can't drop a %s from within another stored routine" "GOTO is not allowed in a stored procedure handler" +"Trigger already exists" +"Trigger does not exist" +"Trigger's '%-.64s' is view or temporary table" +"Updating of %s row is not allowed in %strigger" +"There is no %s row in %s trigger" diff --git a/sql/share/japanese/errmsg.txt b/sql/share/japanese/errmsg.txt index 166d6e8e1c1..5c36473eda8 100644 --- a/sql/share/japanese/errmsg.txt +++ b/sql/share/japanese/errmsg.txt @@ -363,3 +363,8 @@ character-set=ujis "View '%-.64s.%-.64s' references invalid table(s) or column(s)" "Can't drop a %s from within another stored routine" "GOTO is not allowed in a stored procedure handler" +"Trigger already exists" +"Trigger does not exist" +"Trigger's '%-.64s' is view or temporary table" +"Updating of %s row is not allowed in %strigger" +"There is no %s row in %s trigger" diff --git a/sql/share/korean/errmsg.txt b/sql/share/korean/errmsg.txt index 66486fd8a0f..1e1ffbeec20 100644 --- a/sql/share/korean/errmsg.txt +++ b/sql/share/korean/errmsg.txt @@ -361,3 +361,8 @@ character-set=euckr "View '%-.64s.%-.64s' references invalid table(s) or column(s)" "Can't drop a %s from within another stored routine" "GOTO is not allowed in a stored procedure handler" +"Trigger already exists" +"Trigger does not exist" +"Trigger's '%-.64s' is view or temporary table" +"Updating of %s row is not allowed in %strigger" +"There is no %s row in %s trigger" diff --git a/sql/share/norwegian-ny/errmsg.txt b/sql/share/norwegian-ny/errmsg.txt index 739d40178df..5e6879dd415 100644 --- a/sql/share/norwegian-ny/errmsg.txt +++ b/sql/share/norwegian-ny/errmsg.txt @@ -363,3 +363,8 @@ character-set=latin1 "View '%-.64s.%-.64s' references invalid table(s) or column(s)" "Can't drop a %s from within another stored routine" "GOTO is not allowed in a stored procedure handler" +"Trigger already exists" +"Trigger does not exist" +"Trigger's '%-.64s' is view or temporary table" +"Updating of %s row is not allowed in %strigger" +"There is no %s row in %s trigger" diff --git a/sql/share/norwegian/errmsg.txt b/sql/share/norwegian/errmsg.txt index 227e16e1590..0f97b65a46f 100644 --- a/sql/share/norwegian/errmsg.txt +++ b/sql/share/norwegian/errmsg.txt @@ -363,3 +363,8 @@ character-set=latin1 "View '%-.64s.%-.64s' references invalid table(s) or column(s)" "Can't drop a %s from within another stored routine" "GOTO is not allowed in a stored procedure handler" +"Trigger already exists" +"Trigger does not exist" +"Trigger's '%-.64s' is view or temporary table" +"Updating of %s row is not allowed in %strigger" +"There is no %s row in %s trigger" diff --git a/sql/share/polish/errmsg.txt b/sql/share/polish/errmsg.txt index 62e10e778f2..aed5d1c5827 100644 --- a/sql/share/polish/errmsg.txt +++ b/sql/share/polish/errmsg.txt @@ -365,3 +365,8 @@ character-set=latin2 "View '%-.64s.%-.64s' references invalid table(s) or column(s)" "Can't drop a %s from within another stored routine" "GOTO is not allowed in a stored procedure handler" +"Trigger already exists" +"Trigger does not exist" +"Trigger's '%-.64s' is view or temporary table" +"Updating of %s row is not allowed in %strigger" +"There is no %s row in %s trigger" diff --git a/sql/share/portuguese/errmsg.txt b/sql/share/portuguese/errmsg.txt index 08c03fcaf2a..b2acb008932 100644 --- a/sql/share/portuguese/errmsg.txt +++ b/sql/share/portuguese/errmsg.txt @@ -362,3 +362,8 @@ character-set=latin1 "View '%-.64s.%-.64s' references invalid table(s) or column(s)" "Can't drop a %s from within another stored routine" "GOTO is not allowed in a stored procedure handler" +"Trigger already exists" +"Trigger does not exist" +"Trigger's '%-.64s' is view or temporary table" +"Updating of %s row is not allowed in %strigger" +"There is no %s row in %s trigger" diff --git a/sql/share/romanian/errmsg.txt b/sql/share/romanian/errmsg.txt index 0f6df4f919b..f065504da5b 100644 --- a/sql/share/romanian/errmsg.txt +++ b/sql/share/romanian/errmsg.txt @@ -365,3 +365,8 @@ character-set=latin2 "View '%-.64s.%-.64s' references invalid table(s) or column(s)" "Can't drop a %s from within another stored routine" "GOTO is not allowed in a stored procedure handler" +"Trigger already exists" +"Trigger does not exist" +"Trigger's '%-.64s' is view or temporary table" +"Updating of %s row is not allowed in %strigger" +"There is no %s row in %s trigger" diff --git a/sql/share/russian/errmsg.txt b/sql/share/russian/errmsg.txt index 71c983ff55e..56b0b9c8953 100644 --- a/sql/share/russian/errmsg.txt +++ b/sql/share/russian/errmsg.txt @@ -360,6 +360,11 @@ character-set=koi8r "View SELECT É ÓÐÉÓÏË ÐÏÌÅÊ view ÉÍÅÀÔ ÒÁÚÎÏÅ ËÏÌÉÞÅÓÔ×Ï ÓÔÏÌÂÃÏ×" "áÌÇÏÒÉÔÍ ÓÌÉÑÎÉÑ view ÎÅ ÍÏÖÅÔ ÂÙÔØ ÉÓÐÏÌØÚÏ×ÁÎ ÓÅÊÞÁÓ (ÁÌÇÏÒÉÔÍ ÂÕÄÅÔ ÎÅÏÐÅÒÅÄÅÌÅÎÎÙÍ)" "ïÂÎÏ×ÌÑÅÍÙÊ view ÎÅ ÓÏÄÅÒÖÉÔ ËÌÀÞÁ ÉÓÐÏÌØÚÏ×ÁÎÎÏÊ × ÎÅÍ ÔÁÂÌÉÃ(Ù)" -"View '%-.64s.%-.64s' ÓÓÙÌÁÅÔÓÑ ÎÁ ÎÅÓÕÝÅÓÔ×ÕÀÝÉÅ ÔÁÂÌÉÃÙ ÉÌÉ ÓÌÏÌÂÃÙ" +"View '%-.64s.%-.64s' ÓÓÙÌÁÅÔÓÑ ÎÁ ÎÅÓÕÝÅÓÔ×ÕÀÝÉÅ ÔÁÂÌÉÃÙ ÉÌÉ ÓÔÏÌÂÃÙ" "Can't drop a %s from within another stored routine" "GOTO is not allowed in a stored procedure handler" +"Trigger already exists" +"Trigger does not exist" +"Trigger's '%-.64s' is view or temporary table" +"Updating of %s row is not allowed in %strigger" +"There is no %s row in %s trigger" diff --git a/sql/share/serbian/errmsg.txt b/sql/share/serbian/errmsg.txt index bff4fad6108..a3ecba11e2e 100644 --- a/sql/share/serbian/errmsg.txt +++ b/sql/share/serbian/errmsg.txt @@ -367,3 +367,8 @@ character-set=cp1250 "View '%-.64s.%-.64s' references invalid table(s) or column(s)" "Can't drop a %s from within another stored routine" "GOTO is not allowed in a stored procedure handler" +"Trigger already exists" +"Trigger does not exist" +"Trigger's '%-.64s' is view or temporary table" +"Updating of %s row is not allowed in %strigger" +"There is no %s row in %s trigger" diff --git a/sql/share/slovak/errmsg.txt b/sql/share/slovak/errmsg.txt index 363ba86afc7..88be98bb6f4 100644 --- a/sql/share/slovak/errmsg.txt +++ b/sql/share/slovak/errmsg.txt @@ -369,3 +369,8 @@ character-set=latin2 "View '%-.64s.%-.64s' references invalid table(s) or column(s)" "Can't drop a %s from within another stored routine" "GOTO is not allowed in a stored procedure handler" +"Trigger already exists" +"Trigger does not exist" +"Trigger's '%-.64s' is view or temporary table" +"Updating of %s row is not allowed in %strigger" +"There is no %s row in %s trigger" diff --git a/sql/share/spanish/errmsg.txt b/sql/share/spanish/errmsg.txt index 7ab9b416c2e..c35a2b9a917 100644 --- a/sql/share/spanish/errmsg.txt +++ b/sql/share/spanish/errmsg.txt @@ -363,3 +363,8 @@ character-set=latin1 "View '%-.64s.%-.64s' references invalid table(s) or column(s)" "Can't drop a %s from within another stored routine" "GOTO is not allowed in a stored procedure handler" +"Trigger already exists" +"Trigger does not exist" +"Trigger's '%-.64s' is view or temporary table" +"Updating of %s row is not allowed in %strigger" +"There is no %s row in %s trigger" diff --git a/sql/share/swedish/errmsg.txt b/sql/share/swedish/errmsg.txt index b7add017e44..00c3e4b0016 100644 --- a/sql/share/swedish/errmsg.txt +++ b/sql/share/swedish/errmsg.txt @@ -361,3 +361,8 @@ character-set=latin1 "View '%-.64s.%-.64s' references invalid table(s) or column(s)" "Can't drop a %s from within another stored routine" "GOTO is not allowed in a stored procedure handler" +"Trigger already exists" +"Trigger does not exist" +"Trigger's '%-.64s' is view or temporary table" +"Updating of %s row is not allowed in %strigger" +"There is no %s row in %s trigger" diff --git a/sql/share/ukrainian/errmsg.txt b/sql/share/ukrainian/errmsg.txt index 364a2a03c16..c5329fccaea 100644 --- a/sql/share/ukrainian/errmsg.txt +++ b/sql/share/ukrainian/errmsg.txt @@ -366,3 +366,8 @@ character-set=koi8u "View '%-.64s.%-.64s' ÐÏÓÉÌÁ¤ÔÓÑ ÎÁ ÎŦÓÎÕÀÞ¦ ÔÁÂÌÉæ ÁÂÏ ÓÔÏ×Âæ" "Can't drop a %s from within another stored routine" "GOTO is not allowed in a stored procedure handler" +"Trigger already exists" +"Trigger does not exist" +"Trigger's '%-.64s' is view or temporary table" +"Updating of %s row is not allowed in %strigger" +"There is no %s row in %s trigger" diff --git a/sql/sp_head.cc b/sql/sp_head.cc index 4bb06fd8172..3ff5f06103f 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -292,31 +292,43 @@ sp_head::init_strings(THD *thd, LEX *lex, sp_name *name) /* During parsing, we must use thd->mem_root */ MEM_ROOT *root= &thd->mem_root; - DBUG_PRINT("info", ("name: %*.s%*s", - name->m_db.length, name->m_db.str, - name->m_name.length, name->m_name.str)); /* We have to copy strings to get them into the right memroot */ - if (name->m_db.length == 0) + if (name) + { + DBUG_PRINT("info", ("name: %*.s%*s", + name->m_db.length, name->m_db.str, + name->m_name.length, name->m_name.str)); + + if (name->m_db.length == 0) + { + m_db.length= (thd->db ? strlen(thd->db) : 0); + m_db.str= strmake_root(root, (thd->db ? thd->db : ""), m_db.length); + } + else + { + m_db.length= name->m_db.length; + m_db.str= strmake_root(root, name->m_db.str, name->m_db.length); + } + m_name.length= name->m_name.length; + m_name.str= strmake_root(root, name->m_name.str, name->m_name.length); + + if (name->m_qname.length == 0) + name->init_qname(thd); + m_qname.length= name->m_qname.length; + m_qname.str= strmake_root(root, name->m_qname.str, m_qname.length); + } + else { m_db.length= (thd->db ? strlen(thd->db) : 0); m_db.str= strmake_root(root, (thd->db ? thd->db : ""), m_db.length); } - else + + if (m_param_begin && m_param_end) { - m_db.length= name->m_db.length; - m_db.str= strmake_root(root, name->m_db.str, name->m_db.length); + m_params.length= m_param_end - m_param_begin; + m_params.str= strmake_root(root, + (char *)m_param_begin, m_params.length); } - m_name.length= name->m_name.length; - m_name.str= strmake_root(root, name->m_name.str, name->m_name.length); - - if (name->m_qname.length == 0) - name->init_qname(thd); - m_qname.length= name->m_qname.length; - m_qname.str= strmake_root(root, name->m_qname.str, m_qname.length); - - m_params.length= m_param_end- m_param_begin; - m_params.str= strmake_root(root, - (char *)m_param_begin, m_params.length); if (m_returns_begin && m_returns_end) { /* QQ KLUDGE: We can't seem to cut out just the type in the parser @@ -579,8 +591,10 @@ sp_head::execute_function(THD *thd, Item **argp, uint argcount, Item **resp) thd->spcont= nctx; ret= execute(thd); - if (ret == 0) + + if (m_type == TYPE_ENUM_FUNCTION && ret == 0) { + /* We need result only in function but not in trigger */ Item *it= nctx->get_result(); if (it) @@ -767,6 +781,9 @@ sp_head::reset_lex(THD *thd) /* And keep the SP stuff too */ sublex->sphead= oldlex->sphead; sublex->spcont= oldlex->spcont; + /* And trigger related stuff too */ + sublex->trg_chistics= oldlex->trg_chistics; + sublex->trg_table= oldlex->trg_table; sublex->sp_lex_in_use= FALSE; DBUG_VOID_RETURN; } @@ -1229,6 +1246,60 @@ sp_instr_set::print(String *str) } // +// sp_instr_set_user_var +// +int +sp_instr_set_user_var::execute(THD *thd, uint *nextp) +{ + int res= 0; + + DBUG_ENTER("sp_instr_set_user_var::execute"); + /* + It is ok to pass 0 as 3rd argument to fix_fields() since + Item_func_set_user_var::fix_fields() won't use it. + QQ: Still unsure what should we return in case of error 1 or -1 ? + */ + if (!m_set_var_item.fixed && m_set_var_item.fix_fields(thd, 0, 0) || + m_set_var_item.check() || m_set_var_item.update()) + res= -1; + *nextp= m_ip + 1; + DBUG_RETURN(res); +} + +void +sp_instr_set_user_var::print(String *str) +{ + m_set_var_item.print_as_stmt(str); +} + +// +// sp_instr_set_trigger_field +// +int +sp_instr_set_trigger_field::execute(THD *thd, uint *nextp) +{ + int res= 0; + + DBUG_ENTER("sp_instr_set_trigger_field::execute"); + /* QQ: Still unsure what should we return in case of error 1 or -1 ? */ + if (!value->fixed && value->fix_fields(thd, 0, &value) || + trigger_field.fix_fields(thd, 0, 0) || + (value->save_in_field(trigger_field.field, 0) < 0)) + res= -1; + *nextp= m_ip + 1; + DBUG_RETURN(res); +} + +void +sp_instr_set_trigger_field::print(String *str) +{ + str->append("set ", 4); + trigger_field.print(str); + str->append(":=", 2); + value->print(str); +} + +// // sp_instr_jump // int diff --git a/sql/sp_head.h b/sql/sp_head.h index c0881661ad1..a253d9edcbe 100644 --- a/sql/sp_head.h +++ b/sql/sp_head.h @@ -28,6 +28,7 @@ // in the CREATE TABLE command. #define TYPE_ENUM_FUNCTION 1 #define TYPE_ENUM_PROCEDURE 2 +#define TYPE_ENUM_TRIGGER 3 Item_result sp_map_result_type(enum enum_field_types type); @@ -377,6 +378,71 @@ private: }; // class sp_instr_set : public sp_instr +/* + Set user variable instruction. + Used in functions and triggers to set user variables because we don't + want use sp_instr_stmt + "SET @a:=..." statement in this case since + latter will close all tables and thus will ruin execution of statement + calling/invoking this function/trigger. +*/ +class sp_instr_set_user_var : public sp_instr +{ + sp_instr_set_user_var(const sp_instr_set_user_var &); + void operator=(sp_instr_set_user_var &); + +public: + + sp_instr_set_user_var(uint ip, LEX_STRING var, Item *val) + : sp_instr(ip), m_set_var_item(var, val) + {} + + virtual ~sp_instr_set_user_var() + {} + + virtual int execute(THD *thd, uint *nextp); + + virtual void print(String *str); + +private: + + Item_func_set_user_var m_set_var_item; +}; // class sp_instr_set_user_var : public sp_instr + + +/* + Set NEW/OLD row field value instruction. Used in triggers. +*/ +class sp_instr_set_trigger_field : public sp_instr +{ + sp_instr_set_trigger_field(const sp_instr_set_trigger_field &); + void operator=(sp_instr_set_trigger_field &); + +public: + + sp_instr_set_trigger_field(uint ip, LEX_STRING field_name, Item *val) + : sp_instr(ip), + trigger_field(Item_trigger_field::NEW_ROW, field_name.str), + value(val) + {} + + virtual ~sp_instr_set_trigger_field() + {} + + virtual int execute(THD *thd, uint *nextp); + + virtual void print(String *str); + + bool setup_field(THD *thd, TABLE *table, enum trg_event_type event) + { + return trigger_field.setup_field(thd, table, event); + } +private: + + Item_trigger_field trigger_field; + Item *value; +}; // class sp_instr_trigger_field : public sp_instr + + class sp_instr_jump : public sp_instr { sp_instr_jump(const sp_instr_jump &); /* Prevent use of these */ diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 5fcfe945f27..44ce10981ad 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -20,6 +20,8 @@ #include "mysql_priv.h" #include "sql_acl.h" #include "sql_select.h" +#include "sp_head.h" +#include "sql_trigger.h" #include <m_ctype.h> #include <my_dir.h> #include <hash.h> @@ -42,10 +44,6 @@ static my_bool open_new_frm(const char *path, const char *alias, uint db_stat, uint prgflag, uint ha_open_flags, TABLE *outparam, TABLE_LIST *table_desc, MEM_ROOT *mem_root); -static Field *find_field_in_real_table(THD *thd, TABLE *table, - const char *name, uint length, - bool check_grants, bool allow_rowid, - uint *cached_field_index_ptr); extern "C" byte *table_cache_key(const byte *record,uint *length, my_bool not_used __attribute__((unused))) @@ -210,6 +208,7 @@ OPEN_TABLE_LIST *list_open_tables(THD *thd, const char *wild) void intern_close_table(TABLE *table) { // Free all structures free_io_cache(table); + delete table->triggers; if (table->file) VOID(closefrm(table)); // close file } @@ -747,6 +746,7 @@ TABLE *reopen_name_locked_table(THD* thd, TABLE_LIST* table_list) !(table->table_cache_key =memdup_root(&table->mem_root,(char*) key, key_length))) { + delete table->triggers; closefrm(table); pthread_mutex_unlock(&LOCK_open); DBUG_RETURN(0); @@ -1010,6 +1010,7 @@ bool reopen_table(TABLE *table,bool locked) if (!(tmp.table_cache_key= memdup_root(&tmp.mem_root,db, table->key_length))) { + delete tmp.triggers; closefrm(&tmp); // End of memory goto end; } @@ -1038,6 +1039,7 @@ bool reopen_table(TABLE *table,bool locked) tmp.next= table->next; tmp.prev= table->prev; + delete table->triggers; if (table->file) VOID(closefrm(table)); // close file, free everything @@ -1424,6 +1426,9 @@ static int open_unireg_entry(THD *thd, TABLE *entry, const char *db, if (error == 5) DBUG_RETURN(0); // we have just opened VIEW + if (Table_triggers_list::check_n_load(thd, db, name, entry)) + goto err; + /* If we are here, there was no fatal error (but error may be still unitialized). @@ -1452,6 +1457,7 @@ static int open_unireg_entry(THD *thd, TABLE *entry, const char *db, */ sql_print_error("Error: when opening HEAP table, could not allocate \ memory to write 'DELETE FROM `%s`.`%s`' to the binary log",db,name); + delete entry->triggers; if (entry->file) closefrm(entry); goto err; @@ -1983,10 +1989,10 @@ find_field_in_table(THD *thd, TABLE_LIST *table_list, # pointer to field */ -static Field *find_field_in_real_table(THD *thd, TABLE *table, - const char *name, uint length, - bool check_grants, bool allow_rowid, - uint *cached_field_index_ptr) +Field *find_field_in_real_table(THD *thd, TABLE *table, + const char *name, uint length, + bool check_grants, bool allow_rowid, + uint *cached_field_index_ptr) { Field **field_ptr, *field; uint cached_field_index= *cached_field_index_ptr; diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index 089c0c00c3b..587cb28bcb1 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -26,6 +26,8 @@ #include "mysql_priv.h" #include "ha_innodb.h" #include "sql_select.h" +#include "sp_head.h" +#include "sql_trigger.h" int mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, SQL_LIST *order, ha_rows limit, ulong options) @@ -160,6 +162,11 @@ int mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, SQL_LIST *order, // thd->net.report_error is tested to disallow delete row on error if (!(select && select->skip_record())&& !thd->net.report_error ) { + + if (table->triggers) + table->triggers->process_triggers(thd, TRG_EVENT_DELETE, + TRG_ACTION_BEFORE); + if (!(error=table->file->delete_row(table->record[0]))) { deleted++; @@ -183,6 +190,10 @@ int mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, SQL_LIST *order, error= 1; break; } + + if (table->triggers) + table->triggers->process_triggers(thd, TRG_EVENT_DELETE, + TRG_ACTION_AFTER); } else table->file->unlock_row(); // Row failed selection, release lock on it diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 857c500f200..a5c1999da8c 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -19,6 +19,8 @@ #include "mysql_priv.h" #include "sql_acl.h" +#include "sp_head.h" +#include "sql_trigger.h" static int check_null_fields(THD *thd,TABLE *entry); #ifndef EMBEDDED_LIBRARY @@ -302,6 +304,12 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list, break; } } + + // FIXME: Actually we should do this before check_null_fields. + // Or even go into write_record ? + if (table->triggers) + table->triggers->process_triggers(thd, TRG_EVENT_INSERT, TRG_ACTION_BEFORE); + #ifndef EMBEDDED_LIBRARY if (lock_type == TL_WRITE_DELAYED) { @@ -324,6 +332,9 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list, id= thd->last_insert_id; } thd->row_count++; + + if (table->triggers) + table->triggers->process_triggers(thd, TRG_EVENT_INSERT, TRG_ACTION_AFTER); } /* diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 07888a8443b..a809a0dc1b4 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -24,6 +24,12 @@ #include "sp.h" #include "sp_head.h" +/* + We are using pointer to this variable for distinguishing between assignment + to NEW row field (when parsing trigger definition) and structured variable. +*/ +sys_var_long_ptr trg_new_row_fake_var(0, 0); + /* Macros to look like lex */ #define yyGet() *(lex->ptr++) @@ -129,6 +135,7 @@ void lex_start(THD *thd, uchar *buf,uint length) lex->duplicates= DUP_ERROR; lex->sphead= NULL; lex->spcont= NULL; + lex->trg_table= NULL; extern byte *sp_lex_spfuns_key(const byte *ptr, uint *plen, my_bool first); hash_free(&lex->spfuns); diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 1c479444485..803a8ef2a23 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -85,6 +85,7 @@ enum enum_sql_command { SQLCOM_SHOW_STATUS_PROC, SQLCOM_SHOW_STATUS_FUNC, SQLCOM_PREPARE, SQLCOM_EXECUTE, SQLCOM_DEALLOCATE_PREPARE, SQLCOM_CREATE_VIEW, SQLCOM_DROP_VIEW, + SQLCOM_CREATE_TRIGGER, SQLCOM_DROP_TRIGGER, /* This should be the last !!! */ SQLCOM_END }; @@ -608,6 +609,15 @@ struct st_sp_chistics bool detistic; }; + +struct st_trg_chistics +{ + enum trg_action_time_type action_time; + enum trg_event_type event; +}; + +extern sys_var_long_ptr trg_new_row_fake_var; + /* The state of the lex parsing. This is saved in the THD struct */ typedef struct st_lex @@ -713,6 +723,14 @@ typedef struct st_lex rexecuton */ bool empty_field_list_on_rset; + /* Characterstics of trigger being created */ + st_trg_chistics trg_chistics; + /* + Points to table being opened when we are parsing trigger definition + while opening table. 0 if we are parsing user provided CREATE TRIGGER + or any other statement. Used for NEW/OLD row field lookup in trigger. + */ + TABLE *trg_table; st_lex() { diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 546183563c9..f4fb80daa88 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -3837,6 +3837,20 @@ purposes internal to the MySQL server", MYF(0)); res= mysql_drop_view(thd, first_table, thd->lex->drop_mode); break; } + case SQLCOM_CREATE_TRIGGER: + { + /* We don't care much about trigger body at that point */ + delete lex->sphead; + lex->sphead= 0; + + res= mysql_create_or_drop_trigger(thd, all_tables, 1); + break; + } + case SQLCOM_DROP_TRIGGER: + { + res= mysql_create_or_drop_trigger(thd, all_tables, 0); + break; + } default: /* Impossible */ send_ok(thd); break; diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 93fb7930da7..1530f8f7fcc 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -1509,7 +1509,7 @@ static void wait_while_table_is_used(THD *thd,TABLE *table, Win32 clients must also have a WRITE LOCK on the table ! */ -static bool close_cached_table(THD *thd, TABLE *table) +void close_cached_table(THD *thd, TABLE *table) { DBUG_ENTER("close_cached_table"); @@ -1525,7 +1525,6 @@ static bool close_cached_table(THD *thd, TABLE *table) /* When lock on LOCK_open is freed other threads can continue */ pthread_cond_broadcast(&COND_refresh); - DBUG_RETURN(0); } static int send_check_errmsg(THD *thd, TABLE_LIST* table, @@ -3131,12 +3130,7 @@ int mysql_alter_table(THD *thd,char *new_db, char *new_name, close the original table at before doing the rename */ table_name=thd->strdup(table_name); // must be saved - if (close_cached_table(thd, table)) - { // Aborted - VOID(quick_rm_table(new_db_type,new_db,tmp_name)); - VOID(pthread_mutex_unlock(&LOCK_open)); - goto err; - } + close_cached_table(thd, table); table=0; // Marker that table is closed } #if (!defined( __WIN__) && !defined( __EMX__) && !defined( OS2)) diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc new file mode 100644 index 00000000000..5e08192d0c1 --- /dev/null +++ b/sql/sql_trigger.cc @@ -0,0 +1,434 @@ +#include "mysql_priv.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 char * const triggers_file_ext= ".TRG"; + +/* + 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). +*/ +static File_option triggers_file_parameters[]= +{ + {{(char*)"triggers", 8}, offsetof(Table_triggers_list, definitions_list), + FILE_OPTIONS_STRLIST}, + {{NULL, 0}, 0, FILE_OPTIONS_STRING} +}; + + +/* + Create or drop trigger for table. + + SYNOPSIS + mysql_create_or_drop_trigger() + thd - current thread context (including trigger definition in LEX) + tables - table list containing one table for which trigger is created. + create - whenever we create (true) or drop (false) trigger + + NOTE + This function is mainly responsible for opening and locking of table and + invalidation of all its instances in table cache after trigger creation. + Real work on trigger creation/dropping is done inside Table_triggers_list + methods. + + RETURN VALUE + 0 - Success, non-0 in case of error. +*/ +int mysql_create_or_drop_trigger(THD *thd, TABLE_LIST *tables, bool create) +{ + TABLE *table; + int result= 0; + + DBUG_ENTER("mysql_create_or_drop_trigger"); + + /* + QQ: This function could be merged in mysql_alter_table() function + But do we want this ? + */ + + if (open_and_lock_tables(thd, tables)) + DBUG_RETURN(-1); + + // TODO: We should check if user has TRIGGER privilege for table here. + + table= tables->table; + + /* + We do not allow creation of triggers on views or temporary tables. + We have to do this check here and not in + Table_triggers_list::create_trigger() because we want to avoid messing + with table cash for views and temporary tables. + */ + if (tables->view || table->tmp_table != NO_TMP_TABLE) + { + my_error(ER_TRG_ON_VIEW_OR_TEMP_TABLE, MYF(0), tables->alias); + DBUG_RETURN(-1); + } + + if (!table->triggers) + { + if (!create) + { + my_error(ER_TRG_DOES_NOT_EXIST, MYF(0)); + DBUG_RETURN(-1); + } + + if (!(table->triggers= new (&table->mem_root) Table_triggers_list())) + DBUG_RETURN(-1); + } + + /* + We don't want perform our operations while global read lock is held + so we have to wait until its end and then prevent it from occuring + again until we are done. (Acquiring LOCK_open is not enough because + global read lock is held without helding LOCK_open). + */ + if (wait_if_global_read_lock(thd, 0)) + DBUG_RETURN(-1); + + VOID(pthread_mutex_lock(&LOCK_open)); + if ((create ? table->triggers->create_trigger(thd, tables): + table->triggers->drop_trigger(thd, tables))) + result= -1; + + /* It is sensible to invalidate table in any case */ + close_cached_table(thd, table); + VOID(pthread_mutex_unlock(&LOCK_open)); + start_waiting_global_read_lock(thd); + + if (!result) + send_ok(thd); + + DBUG_RETURN(result); +} + + +/* + Create trigger for table. + + SYNOPSIS + create_trigger() + thd - current thread context (including trigger definition in LEX) + tables - table list containing one open table for which trigger is + created. + + RETURN VALUE + False - success + True - error +*/ +bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables) +{ + LEX *lex= thd->lex; + TABLE *table= tables->table; + char dir_buff[FN_REFLEN], file_buff[FN_REFLEN]; + LEX_STRING dir, file; + MEM_ROOT *old_global_root; + LEX_STRING *trg_def, *name; + List_iterator_fast<LEX_STRING> it(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]) + { + my_error(ER_TRG_ALREADY_EXISTS, MYF(0)); + return 1; + } + + /* Let us check if trigger with the same name exists */ + while ((name= it++)) + { + if (my_strcasecmp(system_charset_info, lex->name_and_length.str, + name->str) == 0) + { + my_error(ER_TRG_ALREADY_EXISTS, MYF(0)); + return 1; + } + } + + /* + Here we are creating file with triggers and save all triggers in it. + sql_create_definition_file() files handles renaming and backup of older + versions + */ + 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, tables->real_name, + triggers_file_ext, NullS) - file_buff; + file.str= file_buff; + + old_global_root= my_pthread_getspecific_ptr(MEM_ROOT*, THR_MALLOC); + my_pthread_setspecific_ptr(THR_MALLOC, &table->mem_root); + + /* + Soon we will invalidate table object and thus Table_triggers_list object + so don't care about place to which trg_def->ptr points and other + invariants (e.g. we don't bother to update names_list) + + QQ: Hmm... probably we should not care about setting up active thread + mem_root too. + */ + if (!(trg_def= (LEX_STRING *)alloc_root(&table->mem_root, + sizeof(LEX_STRING))) || + definitions_list.push_back(trg_def)) + { + my_pthread_setspecific_ptr(THR_MALLOC, old_global_root); + return 1; + } + + trg_def->str= thd->query; + trg_def->length= thd->query_length; + + my_pthread_setspecific_ptr(THR_MALLOC, old_global_root); + + return sql_create_definition_file(&dir, &file, &triggers_file_type, + (gptr)this, triggers_file_parameters, 3); +} + + +/* + Drop trigger for table. + + SYNOPSIS + drop_trigger() + thd - current thread context (including trigger definition in LEX) + tables - table list containing one open table for which trigger is + dropped. + + RETURN VALUE + False - success + True - error +*/ +bool Table_triggers_list::drop_trigger(THD *thd, TABLE_LIST *tables) +{ + LEX *lex= thd->lex; + LEX_STRING *name; + List_iterator_fast<LEX_STRING> it_name(names_list); + List_iterator<LEX_STRING> it_def(definitions_list); + + while ((name= it_name++)) + { + it_def++; + + if (my_strcasecmp(system_charset_info, lex->name_and_length.str, + name->str) == 0) + { + /* + Again we don't care much about other things required for + clean trigger removing since table will be reopened anyway. + */ + it_def.remove(); + + if (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->real_name, triggers_file_ext, NullS); + unpack_filename(path, path); + return my_delete(path, MYF(MY_WME)); + } + else + { + char dir_buff[FN_REFLEN], file_buff[FN_REFLEN]; + LEX_STRING dir, file; + + 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, tables->real_name, + triggers_file_ext, NullS) - file_buff; + file.str= file_buff; + + return sql_create_definition_file(&dir, &file, &triggers_file_type, + (gptr)this, + triggers_file_parameters, 3); + } + } + } + + my_error(ER_TRG_DOES_NOT_EXIST, MYF(0)); + return 1; +} + + +Table_triggers_list::~Table_triggers_list() +{ + for (int i= 0; i < 3; i++) + for (int j= 0; j < 2; j++) + delete bodies[i][j]; + + if (old_field) + for (Field **fld_ptr= old_field; *fld_ptr; fld_ptr++) + delete *fld_ptr; +} + + +/* + Check whenever .TRG file for table exist and load all triggers it contains. + + SYNOPSIS + check_n_load() + thd - current thread context + db - table's database name + table_name - table's name + table - pointer to table object + + RETURN VALUE + False - success + True - error +*/ +bool Table_triggers_list::check_n_load(THD *thd, const char *db, + const char *table_name, TABLE *table) +{ + char path_buff[FN_REFLEN]; + LEX_STRING path; + File_parser *parser; + MEM_ROOT *old_global_mem_root; + + DBUG_ENTER("Table_triggers_list::check_n_load"); + + strxnmov(path_buff, FN_REFLEN, mysql_data_home, "/", db, "/", table_name, + triggers_file_ext, NullS); + path.length= unpack_filename(path_buff, path_buff); + path.str= path_buff; + + // QQ: should we analyze errno somehow ? + if (access(path_buff, F_OK)) + DBUG_RETURN(0); + + /* + File exists so we got to load triggers + FIXME: A lot of things to do here e.g. how about other funcs and being + more paranoical ? + */ + + if ((parser= sql_parse_prepare(&path, &table->mem_root, 1))) + { + if (!strncmp(triggers_file_type.str, parser->type()->str, + parser->type()->length)) + { + int i; + Table_triggers_list *triggers_info= + new (&table->mem_root) Table_triggers_list(); + + if (!triggers_info) + DBUG_RETURN(1); + + if (parser->parse((gptr)triggers_info, &table->mem_root, + triggers_file_parameters, 1)) + DBUG_RETURN(1); + + table->triggers= triggers_info; + + /* + We have to prepare array of Field objects which will represent OLD.* + row values by referencing to record[1] instead of record[0] + + TODO: This could be avoided if there is no ON UPDATE trigger. + */ + if (!(triggers_info->old_field= + (Field **)alloc_root(&table->mem_root, (table->fields + 1) * + sizeof(Field*)))) + DBUG_RETURN(1); + + for (i= 0; i < table->fields; i++) + { + /* + QQ: it is supposed that it is ok to use this function for field + cloning... + */ + if (!(triggers_info->old_field[i]= + table->field[i]->new_field(&table->mem_root, table))) + DBUG_RETURN(1); + triggers_info->old_field[i]->move_field((my_ptrdiff_t) + (table->record[1] - + table->record[0])); + } + triggers_info->old_field[i]= 0; + + 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; + + thd->lex= &lex; + + while ((trg_create_str= it++)) + { + lex_start(thd, (uchar*)trg_create_str->str, trg_create_str->length); + mysql_init_query(thd, true); + lex.trg_table= table; + if (yyparse((void *)thd) || thd->is_fatal_error) + { + /* + Free lex associated resources + QQ: Do we really need all this stuff here ? + */ + if (lex.sphead) + { + if (&lex != thd->lex) + thd->lex->sphead->restore_lex(thd); + delete lex.sphead; + } + goto err_with_lex_cleanup; + } + + triggers_info->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.name_and_length.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.name_and_length.str, + lex.name_and_length.length + 1); + trg_name_str->str= trg_name_buff; + trg_name_str->length= lex.name_and_length.length; + + old_global_mem_root= my_pthread_getspecific_ptr(MEM_ROOT*, THR_MALLOC); + my_pthread_setspecific_ptr(THR_MALLOC, &table->mem_root); + + if (triggers_info->names_list.push_back(trg_name_str)) + goto err_with_lex_cleanup; + + my_pthread_setspecific_ptr(THR_MALLOC, old_global_mem_root); + + lex_end(&lex); + } + thd->lex= old_lex; + + DBUG_RETURN(0); + +err_with_lex_cleanup: + // QQ: anything else ? + lex_end(&lex); + thd->lex= old_lex; + DBUG_RETURN(1); + } + + /* + We don't care about this error message much because .TRG files will + be merged into .FRM anyway. + */ + my_error(ER_WRONG_OBJECT, MYF(0), table_name, triggers_file_ext, "TRIGGER"); + DBUG_RETURN(1); + } + + DBUG_RETURN(1); +} diff --git a/sql/sql_trigger.h b/sql/sql_trigger.h new file mode 100644 index 00000000000..8ab2ab003f8 --- /dev/null +++ b/sql/sql_trigger.h @@ -0,0 +1,62 @@ +/* + This class holds all information about triggers of table. + + QQ: Will it be merged into TABLE in future ? +*/ +class Table_triggers_list: public Sql_alloc +{ + /* Triggers as SPs grouped by event, action_time */ + sp_head *bodies[3][2]; + /* + Copy of TABLE::Field array with field pointers set to old version + of record, used for OLD values in trigger on UPDATE. + */ + Field **old_field; + /* + 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; + +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(): + old_field(0) + { + bzero((char *)bodies, sizeof(bodies)); + } + ~Table_triggers_list(); + + bool create_trigger(THD *thd, TABLE_LIST *table); + bool drop_trigger(THD *thd, TABLE_LIST *table); + bool process_triggers(THD *thd, trg_event_type event, + trg_action_time_type time_type) + { + int res= 0; + + if (bodies[event][time_type]) + { + /* + Similar to function invocation we don't need to surpress sending of + ok packets here because don't allow execute statements from trigger. + + FIXME: We should juggle with security context here (because trigger + should be invoked with creator rights). + */ + res= bodies[event][time_type]->execute_function(thd, 0, 0, 0); + } + + return res; + } + + static bool check_n_load(THD *thd, const char *db, const char *table_name, + TABLE *table); + + friend class Item_trigger_field; +}; diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 9d7134aee84..1fd32516198 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -23,6 +23,8 @@ #include "mysql_priv.h" #include "sql_acl.h" #include "sql_select.h" +#include "sp_head.h" +#include "sql_trigger.h" static bool safe_update_on_fly(JOIN_TAB *join_tab, List<Item> *fields); @@ -354,6 +356,10 @@ int mysql_update(THD *thd, if (fill_record(fields,values, 0) || thd->net.report_error) break; /* purecov: inspected */ found++; + + if (table->triggers) + table->triggers->process_triggers(thd, TRG_EVENT_UPDATE, TRG_ACTION_BEFORE); + if (compare_record(table, query_id)) { if (!(error=table->file->update_row((byte*) table->record[1], @@ -369,6 +375,10 @@ int mysql_update(THD *thd, break; } } + + if (table->triggers) + table->triggers->process_triggers(thd, TRG_EVENT_UPDATE, TRG_ACTION_AFTER); + if (!--limit && using_limit) { error= -1; // Simulate end of file diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index ba3a36f2c34..41432cee673 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -242,6 +242,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); %token DISTINCT %token DUPLICATE_SYM %token DYNAMIC_SYM +%token EACH_SYM %token ENABLE_SYM %token ENCLOSED %token ESCAPED @@ -411,6 +412,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); %token TO_SYM %token TRAILING %token TRANSACTION_SYM +%token TRIGGER_SYM %token TRUE_SYM %token TYPE_SYM %token TYPES_SYM @@ -1204,6 +1206,57 @@ create: } opt_view_list AS select_init check_option {} + | CREATE TRIGGER_SYM ident trg_action_time trg_event + ON table_ident FOR_SYM EACH_SYM ROW_SYM + { + LEX *lex= Lex; + sp_head *sp; + + lex->name_and_length= $3; + + /* QQ: Could we loosen lock type in certain cases ? */ + if (!lex->select_lex.add_table_to_list(YYTHD, $7, + (LEX_STRING*) 0, + TL_OPTION_UPDATING, + TL_WRITE)) + YYABORT; + + if (lex->sphead) + { + net_printf(YYTHD, ER_SP_NO_RECURSIVE_CREATE, "TRIGGER"); + YYABORT; + } + + sp= new sp_head(); + sp->reset_thd_mem_root(YYTHD); + sp->init(lex); + + sp->m_type= TYPE_ENUM_TRIGGER; + lex->sphead= sp; + /* + We have to turn of CLIENT_MULTI_QUERIES while parsing a + stored procedure, otherwise yylex will chop it into pieces + at each ';'. + */ + sp->m_old_cmq= YYTHD->client_capabilities & CLIENT_MULTI_QUERIES; + YYTHD->client_capabilities &= ~CLIENT_MULTI_QUERIES; + + bzero((char *)&lex->sp_chistics, sizeof(st_sp_chistics)); + lex->sphead->m_chistics= &lex->sp_chistics; + lex->sphead->m_body_begin= lex->tok_start; + } + sp_proc_stmt + { + LEX *lex= Lex; + sp_head *sp= lex->sphead; + + lex->sql_command= SQLCOM_CREATE_TRIGGER; + sp->init_strings(YYTHD, lex, NULL); + /* Restore flag if it was cleared above */ + if (sp->m_old_cmq) + YYTHD->client_capabilities |= CLIENT_MULTI_QUERIES; + sp->restore_thd_mem_root(YYTHD); + } ; sp_name: @@ -1738,14 +1791,14 @@ sp_proc_stmt: if (lex->sql_command != SQLCOM_SET_OPTION || ! lex->var_list.is_empty()) { - /* Currently we can't handle queries inside a FUNCTION, - ** because of the way table locking works. - ** This is unfortunate, and limits the usefulness of functions - ** a great deal, but it's nothing we can do about this at the - ** moment. - */ - if (lex->sphead->m_type == TYPE_ENUM_FUNCTION && - lex->sql_command != SQLCOM_SET_OPTION) + /* + Currently we can't handle queries inside a FUNCTION or + TRIGGER, because of the way table locking works. This is + unfortunate, and limits the usefulness of functions and + especially triggers a tremendously, but it's nothing we + can do about this at the moment. + */ + if (lex->sphead->m_type != TYPE_ENUM_PROCEDURE) { send_error(YYTHD, ER_SP_BADSTATEMENT); YYABORT; @@ -2278,6 +2331,22 @@ sp_unlabeled_control: } ; +trg_action_time: + BEFORE_SYM + { Lex->trg_chistics.action_time= TRG_ACTION_BEFORE; } + | AFTER_SYM + { Lex->trg_chistics.action_time= TRG_ACTION_AFTER; } + ; + +trg_event: + INSERT + { Lex->trg_chistics.event= TRG_EVENT_INSERT; } + | UPDATE_SYM + { Lex->trg_chistics.event= TRG_EVENT_UPDATE; } + | DELETE_SYM + { Lex->trg_chistics.event= TRG_EVENT_DELETE; } + ; + create2: '(' create2a {} | opt_create_table_options create3 {} @@ -5347,7 +5416,21 @@ drop: lex->sql_command= SQLCOM_DROP_VIEW; lex->drop_if_exists= $3; } - ; + | DROP TRIGGER_SYM ident '.' ident + { + 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->name_and_length= $5; + } + ; table_list: table_name @@ -6338,18 +6421,70 @@ simple_ident_q: { THD *thd= YYTHD; LEX *lex= thd->lex; - SELECT_LEX *sel= lex->current_select; - if (sel->no_table_names_allowed) - { - my_printf_error(ER_TABLENAME_NOT_ALLOWED_HERE, - ER(ER_TABLENAME_NOT_ALLOWED_HERE), - MYF(0), $1.str, thd->where); - } - $$= (sel->parsing_place != SELECT_LEX_NODE::IN_HAVING || - sel->get_in_sum_expr() > 0) ? - (Item*) new Item_field(NullS,$1.str,$3.str) : - (Item*) new Item_ref(0,0,NullS,$1.str,$3.str); - } + + /* + FIXME This will work ok in simple_ident_nospvar case because + we can't meet simple_ident_nospvar in trigger now. But it + should be changed in future. + */ + if (lex->sphead && lex->sphead->m_type == TYPE_ENUM_TRIGGER && + (!my_strcasecmp(system_charset_info, $1.str, "NEW") || + !my_strcasecmp(system_charset_info, $1.str, "OLD"))) + { + bool new_row= ($1.str[0]=='N' || $1.str[0]=='n'); + + if (lex->trg_chistics.event == TRG_EVENT_INSERT && + !new_row) + { + net_printf(YYTHD, ER_TRG_NO_SUCH_ROW_IN_TRG, "OLD", + "on INSERT"); + YYABORT; + } + + if (lex->trg_chistics.event == TRG_EVENT_DELETE && + new_row) + { + net_printf(YYTHD, ER_TRG_NO_SUCH_ROW_IN_TRG, "NEW", + "on DELETE"); + YYABORT; + } + + Item_trigger_field *trg_fld= + new Item_trigger_field(new_row ? Item_trigger_field::NEW_ROW : + Item_trigger_field::OLD_ROW, + $3.str); + + if (lex->trg_table && + trg_fld->setup_field(thd, lex->trg_table, + lex->trg_chistics.event)) + { + /* + FIXME. Far from perfect solution. See comment for + "SET NEW.field_name:=..." for more info. + */ + net_printf(YYTHD, ER_BAD_FIELD_ERROR, $3.str, + new_row ? "NEW": "OLD"); + YYABORT; + } + + $$= (Item *)trg_fld; + } + else + { + SELECT_LEX *sel= lex->current_select; + + if (sel->no_table_names_allowed) + { + my_printf_error(ER_TABLENAME_NOT_ALLOWED_HERE, + ER(ER_TABLENAME_NOT_ALLOWED_HERE), + MYF(0), $1.str, thd->where); + } + $$= (sel->parsing_place != SELECT_LEX_NODE::IN_HAVING || + sel->get_in_sum_expr() > 0) ? + (Item*) new Item_field(NullS,$1.str,$3.str) : + (Item*) new Item_ref(0,0,NullS,$1.str,$3.str); + } + } | '.' ident '.' ident { THD *thd= YYTHD; @@ -6779,13 +6914,74 @@ opt_var_ident_type: option_value: '@' ident_or_text equal expr { - Lex->var_list.push_back(new set_var_user(new Item_func_set_user_var($2,$4))); + LEX *lex= Lex; + + if (lex->sphead && lex->sphead->m_type != TYPE_ENUM_PROCEDURE) + { + /* + We have to use special instruction in functions and triggers + because sp_instr_stmt will close all tables and thus ruin + execution of statement invoking function or trigger. + */ + sp_instr_set_user_var *i= + new sp_instr_set_user_var(lex->sphead->instructions(), + $2, $4); + lex->sphead->add_instr(i); + } + else + lex->var_list.push_back(new set_var_user(new Item_func_set_user_var($2,$4))); + } | internal_variable_name equal set_expr_or_default { LEX *lex=Lex; - if ($1.var) + if ($1.var == &trg_new_row_fake_var) + { + /* We are in trigger and assigning value to field of new row */ + Item *it; + sp_instr_set_trigger_field *i; + if ($3 && $3->type() == Item::SUBSELECT_ITEM) + { /* + QQ For now, just disallow subselects as values + Unfortunately this doesn't helps in case when we have + subselect deeper in expression. + */ + send_error(YYTHD, ER_SP_SUBSELECT_NYI); + YYABORT; + } + if ($3) + it= $3; + else + { + /* QQ: Shouldn't this be field's default value ? */ + it= new Item_null(); + } + i= new sp_instr_set_trigger_field(lex->sphead->instructions(), + $1.base_name, it); + if (lex->trg_table && i->setup_field(YYTHD, lex->trg_table, + lex->trg_chistics.event)) + { + /* + FIXME. Now we are catching this kind of errors only + during opening tables. But this doesn't save us from most + common user error - misspelling field name, because we + will bark too late in this case... Moreover it is easy to + make table unusable with such kind of error... + + So in future we either have to parse trigger definition + second time during create trigger or gather all trigger + fields in one list and perform setup_field() for them as + separate stage. + + Error message also should be improved. + */ + net_printf(YYTHD, ER_BAD_FIELD_ERROR, $1.base_name, "NEW"); + YYABORT; + } + lex->sphead->add_instr(i); + } + else if ($1.var) { /* System variable */ lex->var_list.push_back(new set_var(lex->option_type, $1.var, &$1.base_name, $3)); @@ -6891,18 +7087,46 @@ internal_variable_name: } | ident '.' ident { + LEX *lex= Lex; if (check_reserved_words(&$1)) { yyerror(ER(ER_SYNTAX_ERROR)); YYABORT; } - sys_var *tmp=find_sys_var($3.str, $3.length); - if (!tmp) - YYABORT; - if (!tmp->is_struct()) - net_printf(YYTHD, ER_VARIABLE_IS_NOT_STRUCT, $3.str); - $$.var= tmp; - $$.base_name= $1; + if (lex->sphead && lex->sphead->m_type == TYPE_ENUM_TRIGGER && + (!my_strcasecmp(system_charset_info, $1.str, "NEW") || + !my_strcasecmp(system_charset_info, $1.str, "OLD"))) + { + if ($1.str[0]=='O' || $1.str[0]=='o') + { + net_printf(YYTHD, ER_TRG_CANT_CHANGE_ROW, "OLD", ""); + YYABORT; + } + if (lex->trg_chistics.event == TRG_EVENT_DELETE) + { + net_printf(YYTHD, ER_TRG_NO_SUCH_ROW_IN_TRG, "NEW", + "on DELETE"); + YYABORT; + } + if (lex->trg_chistics.action_time == TRG_ACTION_AFTER) + { + net_printf(YYTHD, ER_TRG_CANT_CHANGE_ROW, "NEW", "after "); + YYABORT; + } + /* This special combination will denote field of NEW row */ + $$.var= &trg_new_row_fake_var; + $$.base_name= $3; + } + else + { + sys_var *tmp=find_sys_var($3.str, $3.length); + if (!tmp) + YYABORT; + if (!tmp->is_struct()) + net_printf(YYTHD, ER_VARIABLE_IS_NOT_STRUCT, $3.str); + $$.var= tmp; + $$.base_name= $1; + } } | DEFAULT '.' ident { diff --git a/sql/table.h b/sql/table.h index b7cabe21638..2d90a935b74 100644 --- a/sql/table.h +++ b/sql/table.h @@ -73,6 +73,7 @@ typedef struct st_filesort_info class Field_timestamp; class Field_blob; +class Table_triggers_list; struct st_table { handler *file; @@ -154,6 +155,8 @@ struct st_table { REGINFO reginfo; /* field connections */ MEM_ROOT mem_root; GRANT_INFO grant; + /* Table's triggers, 0 if there are no of them */ + Table_triggers_list *triggers; char *table_cache_key; char *table_name,*real_name,*path; |