diff options
| author | unknown <dlenev@brandersnatch.localdomain> | 2004-09-07 16:29:46 +0400 |
|---|---|---|
| committer | unknown <dlenev@brandersnatch.localdomain> | 2004-09-07 16:29:46 +0400 |
| commit | 52ac4935e7e00218bf7abde7b841a2e5111cc819 (patch) | |
| tree | 1da70dd5c1cc43e315c793e1a029a25723888964 | |
| parent | df9401560442947c0580c1cc4bd1c0886e5439a4 (diff) | |
| download | mariadb-git-52ac4935e7e00218bf7abde7b841a2e5111cc819.tar.gz | |
WL#1218 "Triggers". Some very preliminary version of patch.
Mostly needed for Monty for him getting notion what needed for triggers
from new .FRM format.
Things to be done:
- Right placement of trigger's invocations
- Right handling of errors in triggers (including transaction rollback)
- Support for priviliges
- Right handling of DROP/RENAME table (hope that it will be handled automatically
with merging of .TRG into .FRM file)
- Saving/restoring some information critical for trigger creation and replication
with their definitions (e.g. sql_mode, creator, ...)
- Replication
Already has some known bugs so probably not for general review.
include/mysqld_error.h:
Added trigger related error codes.
sql/Makefile.am:
Added sql_trigger.* files to list of sources from which mysqld is built.
sql/item.cc:
Added Item_trigger_field class representing field of new/old version of row which is
inserted/updated/deleted and for which trigger is invoked.
sql/item.h:
Added enums for describing trigger properties. (Here because of dependencies).
Added Item_trigger_field class representing field of new/old version of row which is
inserted/updated/deleted and for which trigger is invoked.
sql/item_func.cc:
Added Item_func_set_user_var::print_as_stmt() for printing of assignment to ser variable
from stored procedures.
Added comment clarifying why we don't need set no_send_ok when calling stored functions.
sql/item_func.h:
Added Item_func_set_user_var::print_as_stmt() for printing of assignment to ser variable
from stored procedures.
sql/lex.h:
Symbols used in statements defining triggers were added.
sql/mysql_priv.h:
Exported some functions needed for triggers implementation.
sql/parse_file.cc:
Cleaned up FILE_OPTIONS_STRLIST handling (needed for triggers).
sql/parse_file.h:
Cleaned up FILE_OPTIONS_STRLIST handling (needed for triggers).
sql/share/czech/errmsg.txt:
Added trigger related error messages.
sql/share/danish/errmsg.txt:
Added trigger related error messages.
sql/share/dutch/errmsg.txt:
Added trigger related error messages.
sql/share/english/errmsg.txt:
Added trigger related error messages.
sql/share/estonian/errmsg.txt:
Added trigger related error messages.
sql/share/french/errmsg.txt:
Added trigger related error messages.
sql/share/german/errmsg.txt:
Added trigger related error messages.
sql/share/greek/errmsg.txt:
Added trigger related error messages.
sql/share/hungarian/errmsg.txt:
Added trigger related error messages.
sql/share/italian/errmsg.txt:
Added trigger related error messages.
sql/share/japanese/errmsg.txt:
Added trigger related error messages.
sql/share/korean/errmsg.txt:
Added trigger related error messages.
sql/share/norwegian-ny/errmsg.txt:
Added trigger related error messages.
sql/share/norwegian/errmsg.txt:
Added trigger related error messages.
sql/share/polish/errmsg.txt:
Added trigger related error messages.
sql/share/portuguese/errmsg.txt:
Added trigger related error messages.
sql/share/romanian/errmsg.txt:
Added trigger related error messages.
sql/share/russian/errmsg.txt:
Added trigger related error messages.
sql/share/serbian/errmsg.txt:
Added trigger related error messages.
sql/share/slovak/errmsg.txt:
Added trigger related error messages.
sql/share/spanish/errmsg.txt:
Added trigger related error messages.
sql/share/swedish/errmsg.txt:
Added trigger related error messages.
sql/share/ukrainian/errmsg.txt:
Added trigger related error messages.
sql/sp_head.cc:
sp_head::init_strings(): added support for triggers
(we don't have most of strings for them)
sp_head::execute_function(): triggers don't require RETURN
sp_head::reset_lex(): added propagation of trigger properties to LEX used for
parsing of statements composing trigger body.
Added two more SP instructions:
- sp_instr_set_user_var for setting user variables in stored functions and triggers
(we can't use sp_instr_stmt and "SET @a:=..." statement since it will close open
tables and thus break execution of calling statement.
- sp_instr_set_trigger_field for assignment to fields of row being updated/inserted in
triggers
sql/sp_head.h:
Added new type of sp_head object - TYPE_ENUM_TRIGGER
Added two more SP instructions:
- sp_instr_set_user_var for setting user variables in stored functions and triggers
(we can't use sp_instr_stmt and "SET @a:=..." statement since it will close open
tables and thus break execution of calling statement.
- sp_instr_set_trigger_field for assignment to fields of row being updated/inserted in
triggers
sql/sql_base.cc:
Now freeing memory occupied by trigger related structures when closing tables.
open_unireg_entry(): added loading of triggers for table.
find_field_in_real_table() is now public since it required by Item_trigger_field in item.cc
sql/sql_delete.cc:
Added triggers invocation for DELETE (should be fixed for sure :))
sql/sql_insert.cc:
Added triggers invocation for INSERT (should be fixed for sure :))
sql/sql_lex.cc:
Added trg_new_row_fake_var variable pointer to which is used for distinguishing between
assignment to NEW row field (when parsing trigger definition) and structured variable.
Added initialization of LEX->trg_table to lex_start(). (When triggers are parsed during
opening of table it points to table object.)
sql/sql_lex.h:
Added trigger related commands.
Added st_trg_chistics structure describing properties of trigger being created
(as struct and as LEX member).
Added trg_new_row_fake_var variable pointer to which is used for
distinguishing between assignment to NEW row field (when parsing trigger definition)
and structured variable.
Added LEX::trg_table which points to table being opened when we are parsing trigger
definition while opening table.
sql/sql_parse.cc:
Added support for CREATE/DROP TRIGGER commands.
sql/sql_table.cc:
Cleanup. close_cached_table() always returns 0 and its result is not analyzed in most
places so chenged its return type to void.
sql/sql_update.cc:
Added triggers invocation for UPDATE (should be fixed for sure :))
sql/sql_yacc.yy:
Added support for CREATE/DROP TRIGGER statements.
Added support for OLD/NEW row identifiers in trigger.
Made assignment to user variables not to break execution of stored function or trigger.
sql/table.h:
Added TABLE::triggers member representing triggers for this table.
49 files changed, 1664 insertions, 78 deletions
diff --git a/include/mysqld_error.h b/include/mysqld_error.h index 0075e51f72d..fa5fd3db2b0 100644 --- a/include/mysqld_error.h +++ b/include/mysqld_error.h @@ -370,4 +370,9 @@ #define ER_VIEW_WRONG_LIST 1351 #define ER_WARN_VIEW_MERGE 1352 #define ER_WARN_VIEW_WITHOUT_KEY 1353 -#define ER_ERROR_MESSAGES 354 +#define ER_TRG_ALREADY_EXISTS 1354 +#define ER_TRG_DOES_NOT_EXIST 1355 +#define ER_TRG_ON_VIEW_OR_TEMP_TABLE 1356 +#define ER_TRG_CANT_CHANGE_ROW 1357 +#define ER_TRG_NO_SUCH_ROW_IN_TRG 1358 +#define ER_ERROR_MESSAGES 359 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 0ca557a8fc4..9cfa12b6365 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, @@ -2251,6 +2253,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 758429c0994..354152611ae 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 }; @@ -439,6 +439,7 @@ public: class Item_field :public Item_ident { +protected: void set_field(Field *field); public: Field *field,*result_field; @@ -1152,6 +1153,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 c2c93586af8..a7bfb727ff7 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) { @@ -3296,6 +3306,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 023c4752720..fc72435bb52 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)}, @@ -468,6 +469,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 ba19ee15ce6..48db2f37796 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -471,6 +471,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); @@ -611,6 +612,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); @@ -637,6 +639,10 @@ Field *find_field_in_table(THD *thd, TABLE_LIST *tables, const char *name, bool check_grant_table, bool check_grant_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 231780f437e..4bd526d7136 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 81bc1e733c6..7b5c2cb327a 100644 --- a/sql/share/czech/errmsg.txt +++ b/sql/share/czech/errmsg.txt @@ -366,3 +366,8 @@ character-set=latin2 "View's SELECT and view's field list have different column counts" "View merge algorithm can't be used here for now (assumed undefined algorithm)" "View being update does not have complete key of underlying table in it" +"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 f16d0700be1..7fd0ef6270d 100644 --- a/sql/share/danish/errmsg.txt +++ b/sql/share/danish/errmsg.txt @@ -360,3 +360,8 @@ character-set=latin1 "View's SELECT and view's field list have different column counts" "View merge algorithm can't be used here for now (assumed undefined algorithm)" "View being update does not have complete key of underlying table in it" +"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 e4a7d2ffe02..159bace6667 100644 --- a/sql/share/dutch/errmsg.txt +++ b/sql/share/dutch/errmsg.txt @@ -368,3 +368,8 @@ character-set=latin1 "View's SELECT and view's field list have different column counts" "View merge algorithm can't be used here for now (assumed undefined algorithm)" "View being update does not have complete key of underlying table in it" +"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 c13eee75392..2306df21321 100644 --- a/sql/share/english/errmsg.txt +++ b/sql/share/english/errmsg.txt @@ -357,3 +357,8 @@ character-set=latin1 "View's SELECT and view's field list have different column counts" "View merge algorithm can't be used here for now (assumed undefined algorithm)" "View being update does not have complete key of underlying table in it" +"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 4ff08a9c8c7..03877b4656b 100644 --- a/sql/share/estonian/errmsg.txt +++ b/sql/share/estonian/errmsg.txt @@ -362,3 +362,8 @@ character-set=latin7 "View's SELECT and view's field list have different column counts" "View merge algorithm can't be used here for now (assumed undefined algorithm)" "View being update does not have complete key of underlying table in it" +"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 a1d0448ebf1..f1d68fca6d7 100644 --- a/sql/share/french/errmsg.txt +++ b/sql/share/french/errmsg.txt @@ -357,3 +357,8 @@ character-set=latin1 "View's SELECT and view's field list have different column counts" "View merge algorithm can't be used here for now (assumed undefined algorithm)" "View being update does not have complete key of underlying table in it" +"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 ac7cf16b7b4..800409bb07f 100644 --- a/sql/share/german/errmsg.txt +++ b/sql/share/german/errmsg.txt @@ -369,3 +369,8 @@ character-set=latin1 "View's SELECT and view's field list have different column counts" "View merge algorithm can't be used here for now (assumed undefined algorithm)" "View being update does not have complete key of underlying table in it" +"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 f505766bbe9..b94364d256c 100644 --- a/sql/share/greek/errmsg.txt +++ b/sql/share/greek/errmsg.txt @@ -357,3 +357,8 @@ character-set=greek "View's SELECT and view's field list have different column counts" "View merge algorithm can't be used here for now (assumed undefined algorithm)" "View being update does not have complete key of underlying table in it" +"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 4f328d07825..b8560317b48 100644 --- a/sql/share/hungarian/errmsg.txt +++ b/sql/share/hungarian/errmsg.txt @@ -359,3 +359,8 @@ character-set=latin2 "View's SELECT and view's field list have different column counts" "View merge algorithm can't be used here for now (assumed undefined algorithm)" "View being update does not have complete key of underlying table in it" +"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 af5ebc4c3e6..7c72fb03476 100644 --- a/sql/share/italian/errmsg.txt +++ b/sql/share/italian/errmsg.txt @@ -357,3 +357,8 @@ character-set=latin1 "View's SELECT and view's field list have different column counts" "View merge algorithm can't be used here for now (assumed undefined algorithm)" "View being update does not have complete key of underlying table in it" +"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 2a230bdf17d..4c1bf8e5cc0 100644 --- a/sql/share/japanese/errmsg.txt +++ b/sql/share/japanese/errmsg.txt @@ -359,3 +359,8 @@ character-set=ujis "View's SELECT and view's field list have different column counts" "View merge algorithm can't be used here for now (assumed undefined algorithm)" "View being update does not have complete key of underlying table in it" +"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 ed1751bbc18..45479950378 100644 --- a/sql/share/korean/errmsg.txt +++ b/sql/share/korean/errmsg.txt @@ -357,3 +357,8 @@ character-set=euckr "View's SELECT and view's field list have different column counts" "View merge algorithm can't be used here for now (assumed undefined algorithm)" "View being update does not have complete key of underlying table in it" +"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 70951dfc1f4..02096bc46b9 100644 --- a/sql/share/norwegian-ny/errmsg.txt +++ b/sql/share/norwegian-ny/errmsg.txt @@ -359,3 +359,8 @@ character-set=latin1 "View's SELECT and view's field list have different column counts" "View merge algorithm can't be used here for now (assumed undefined algorithm)" "View being update does not have complete key of underlying table in it" +"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 d6b0fa9303b..615014cef94 100644 --- a/sql/share/norwegian/errmsg.txt +++ b/sql/share/norwegian/errmsg.txt @@ -359,3 +359,8 @@ character-set=latin1 "View's SELECT and view's field list have different column counts" "View merge algorithm can't be used here for now (assumed undefined algorithm)" "View being update does not have complete key of underlying table in it" +"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 6914661f188..9368dd927d6 100644 --- a/sql/share/polish/errmsg.txt +++ b/sql/share/polish/errmsg.txt @@ -361,3 +361,8 @@ character-set=latin2 "View's SELECT and view's field list have different column counts" "View merge algorithm can't be used here for now (assumed undefined algorithm)" "View being update does not have complete key of underlying table in it" +"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 3aa6071ff5b..12c4c43bfe0 100644 --- a/sql/share/portuguese/errmsg.txt +++ b/sql/share/portuguese/errmsg.txt @@ -358,3 +358,8 @@ character-set=latin1 "View's SELECT and view's field list have different column counts" "View merge algorithm can't be used here for now (assumed undefined algorithm)" "View being update does not have complete key of underlying table in it" +"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 87691de7ce9..4b04ef3729e 100644 --- a/sql/share/romanian/errmsg.txt +++ b/sql/share/romanian/errmsg.txt @@ -361,3 +361,8 @@ character-set=latin2 "View's SELECT and view's field list have different column counts" "View merge algorithm can't be used here for now (assumed undefined algorithm)" "View being update does not have complete key of underlying table in it" +"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 55116a24b4a..b73b7d8d7b9 100644 --- a/sql/share/russian/errmsg.txt +++ b/sql/share/russian/errmsg.txt @@ -359,3 +359,8 @@ character-set=koi8r "View SELECT É ÓÐÉÓÏË ÐÏÌÅÊ view ÉÍÅÀÔ ÒÁÚÎÏÅ ËÏÌÉÞÅÓÔ×Ï ÓÔÏÌÂÃÏ×" "áÌÇÏÒÉÔÍ ÓÌÉÑÎÉÑ view ÎÅ ÍÏÖÅÔ ÂÙÔØ ÉÓÐÏÌØÚÏ×ÁÎ ÓÅÊÞÁÓ (ÁÌÇÏÒÉÔÍ ÂÕÄÅÔ ÎÅÏÐÅÒÅÄÅÌÅÎÎÙÍ)" "ïÂÎÏ×ÌÑÅÍÙÊ view ÎÅ ÓÏÄÅÒÖÉÔ ËÌÀÞÁ ÉÓÐÏÌØÚÏ×ÁÎÎÏÊ × ÎÅÍ ÔÁÂÌÉÃ(Ù)" +"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 a08e65149ef..d0f4e0e64eb 100644 --- a/sql/share/serbian/errmsg.txt +++ b/sql/share/serbian/errmsg.txt @@ -363,3 +363,8 @@ character-set=cp1250 "View's SELECT and view's field list have different column counts" "View merge algorithm can't be used here for now (assumed undefined algorithm)" "View being update does not have complete key of underlying table in it" +"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 222461bab51..66f75e5b44d 100644 --- a/sql/share/slovak/errmsg.txt +++ b/sql/share/slovak/errmsg.txt @@ -365,3 +365,8 @@ character-set=latin2 "View's SELECT and view's field list have different column counts" "View merge algorithm can't be used here for now (assumed undefined algorithm)" "View being update does not have complete key of underlying table in it" +"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 791dcacfcc2..080fba045a4 100644 --- a/sql/share/spanish/errmsg.txt +++ b/sql/share/spanish/errmsg.txt @@ -359,3 +359,8 @@ character-set=latin1 "View's SELECT and view's field list have different column counts" "View merge algorithm can't be used here for now (assumed undefined algorithm)" "View being update does not have complete key of underlying table in it" +"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 0c562525ffa..0d4e4d489f4 100644 --- a/sql/share/swedish/errmsg.txt +++ b/sql/share/swedish/errmsg.txt @@ -357,3 +357,8 @@ character-set=latin1 "View's SELECT and view's field list have different column counts" "View merge algorithm can't be used here for now (assumed undefined algorithm)" "View being update does not have complete key of underlying table in it" +"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 cb483e3ed99..a1369730790 100644 --- a/sql/share/ukrainian/errmsg.txt +++ b/sql/share/ukrainian/errmsg.txt @@ -362,3 +362,8 @@ character-set=koi8u "View SELECT ¦ ÐÅÒÅÌ¦Ë ÓÔÏ×ÂÃ¦× view ÍÁÀÔØ Ò¦ÚÎÕ Ë¦ÌØ˦ÓÔØ ÓËÏ×Âæ×" "áÌÇÏÒÉÔÍ ÚÌÉ×ÁÎÎÑ view ÎÅ ÍÏÖÅ ÂÕÔÉ ×ÉËÏÒÉÓÔÁÎÉÊ ÚÁÒÁÚ (ÁÌÇÏÒÉÔÍ ÂÕÄÅ ÎÅ×ÉÚÎÁÞÅÎÉÊ)" "View, ÝÏ ÏÎÏ×ÌÀÅÔØÓÑ, ΊͦÓÔÉÔØ ÐÏ×ÎÏÇÏ ËÌÀÞÁ ÔÁÂÌÉæ(Ø), ÝÏ ×ÉËÏÒ¦ÓÔÁÎÁ × ÎØÀÏÍÕ" +"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 89114e4f62b..44357170ff5 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -242,31 +242,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 @@ -508,8 +520,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) @@ -683,6 +697,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; mysql_init_query(thd, true); // Only init lex sublex->sp_lex_in_use= FALSE; DBUG_VOID_RETURN; @@ -1041,6 +1058,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 fd6ecfd7320..1622d197d81 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); @@ -342,6 +343,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 781e7273e7a..1060c9cd25d 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> @@ -41,10 +43,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))) @@ -209,6 +207,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 } @@ -790,6 +789,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); @@ -1053,6 +1053,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; } @@ -1081,6 +1082,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 @@ -1469,6 +1471,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). @@ -1497,6 +1502,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; @@ -1998,10 +2004,10 @@ Field *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 910b673dc32..0453990fbac 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 33b99d4ccf9..72ca7cf5dea 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 @@ -299,6 +301,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) { @@ -321,6 +329,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 d96c6371ff1..8a1b59d9585 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++) @@ -130,6 +136,7 @@ LEX *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 9e615d2ce9f..83154b277f5 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 }; @@ -606,6 +607,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 @@ -710,6 +720,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 be063211a7d..212339151a4 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -3828,6 +3828,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 ea57536d7c1..c79005ad83f 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, @@ -3130,12 +3129,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 7d49c422194..bc72589b142 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); @@ -341,6 +343,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], @@ -356,6 +362,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 af6694c6318..65b364c6387 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 @@ -1200,6 +1202,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: @@ -1730,14 +1783,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; @@ -2155,6 +2208,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 {} @@ -5210,7 +5279,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 @@ -6200,18 +6283,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; @@ -6640,13 +6775,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)); @@ -6754,18 +6950,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 5e00820a6e5..6bf700cc684 100644 --- a/sql/table.h +++ b/sql/table.h @@ -63,6 +63,7 @@ typedef struct st_filesort_info class Field_timestamp; class Field_blob; +class Table_triggers_list; struct st_table { handler *file; @@ -144,6 +145,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; |