diff options
-rw-r--r-- | sql/sp.cc | 326 | ||||
-rw-r--r-- | sql/sp.h | 29 | ||||
-rw-r--r-- | sql/sp_head.cc | 151 | ||||
-rw-r--r-- | sql/sp_head.h | 7 | ||||
-rw-r--r-- | sql/sql_parse.cc | 40 |
5 files changed, 262 insertions, 291 deletions
diff --git a/sql/sp.cc b/sql/sp.cc index f21186de021..79daed9a627 100644 --- a/sql/sp.cc +++ b/sql/sp.cc @@ -442,16 +442,36 @@ sp_returns_type(THD *thd, String &result, sp_head *sp) delete field; } -static int -db_create_routine(THD *thd, int type, sp_head *sp) + +/** + Write stored-routine object into mysql.proc. + + This operation stores attributes of the stored procedure/function into + the mysql.proc. + + @param thd Thread context. + @param type Stored routine type + (TYPE_ENUM_PROCEDURE or TYPE_ENUM_FUNCTION). + @param sp Stored routine object to store. + + @return Error code. SP_OK is returned on success. Other SP_ constants are + used to indicate about errors. +*/ + +int +sp_create_routine(THD *thd, int type, sp_head *sp) { int ret; TABLE *table; char definer[USER_HOST_BUFF_SIZE]; - DBUG_ENTER("db_create_routine"); + + DBUG_ENTER("sp_create_routine"); DBUG_PRINT("enter", ("type: %d name: %.*s",type,sp->m_name.length, sp->m_name.str)); + DBUG_ASSERT(type == TYPE_ENUM_PROCEDURE || + type == TYPE_ENUM_FUNCTION); + /* This statement will be replicated as a statement, even when using row-based replication. The flag will be reset at the end of the @@ -586,15 +606,33 @@ done: } -static int -db_drop_routine(THD *thd, int type, sp_name *name) +/** + Delete the record for the stored routine object from mysql.proc. + + The operation deletes the record for the stored routine specified by name + from the mysql.proc table and invalidates the stored-routine cache. + + @param thd Thread context. + @param type Stored routine type + (TYPE_ENUM_PROCEDURE or TYPE_ENUM_FUNCTION) + @param name Stored routine name. + + @return Error code. SP_OK is returned on success. Other SP_ constants are + used to indicate about errors. +*/ + +int +sp_drop_routine(THD *thd, int type, sp_name *name) { TABLE *table; int ret; - DBUG_ENTER("db_drop_routine"); + DBUG_ENTER("sp_drop_routine"); DBUG_PRINT("enter", ("type: %d name: %.*s", type, name->m_name.length, name->m_name.str)); + DBUG_ASSERT(type == TYPE_ENUM_PROCEDURE || + type == TYPE_ENUM_FUNCTION); + /* This statement will be replicated as a statement, even when using row-based replication. The flag will be reset at the end of the @@ -618,6 +656,8 @@ db_drop_routine(THD *thd, int type, sp_name *name) thd->binlog_query(THD::MYSQL_QUERY_TYPE, thd->query, thd->query_length, FALSE, FALSE); } + + sp_cache_invalidate(); } close_thread_tables(thd); @@ -625,15 +665,35 @@ db_drop_routine(THD *thd, int type, sp_name *name) } -static int -db_update_routine(THD *thd, int type, sp_name *name, st_sp_chistics *chistics) +/** + Find and updated the record for the stored routine object in mysql.proc. + + The operation finds the record for the stored routine specified by name + in the mysql.proc table and updates it with new attributes. After + successful update, the cache is invalidated. + + @param thd Thread context. + @param type Stored routine type + (TYPE_ENUM_PROCEDURE or TYPE_ENUM_FUNCTION) + @param name Stored routine name. + @param chistics New values of stored routine attributes to write. + + @return Error code. SP_OK is returned on success. Other SP_ constants are + used to indicate about errors. +*/ + +int +sp_update_routine(THD *thd, int type, sp_name *name, st_sp_chistics *chistics) { TABLE *table; int ret; - DBUG_ENTER("db_update_routine"); + DBUG_ENTER("sp_update_routine"); DBUG_PRINT("enter", ("type: %d name: %.*s", type, name->m_name.length, name->m_name.str)); + + DBUG_ASSERT(type == TYPE_ENUM_PROCEDURE || + type == TYPE_ENUM_FUNCTION); /* This statement will be replicated as a statement, even when using row-based replication. The flag will be reset at the end of the @@ -670,6 +730,8 @@ db_update_routine(THD *thd, int type, sp_name *name, st_sp_chistics *chistics) thd->binlog_query(THD::MYSQL_QUERY_TYPE, thd->query, thd->query_length, FALSE, FALSE); } + + sp_cache_invalidate(); } close_thread_tables(thd); @@ -755,13 +817,28 @@ print_field_values(THD *thd, TABLE *table, } -static int -db_show_routine_status(THD *thd, int type, const char *wild) +/** + Implement SHOW STATUS statement for stored routines. + + @param thd Thread context. + @param type Stored routine type + (TYPE_ENUM_PROCEDURE or TYPE_ENUM_FUNCTION) + @param name_pattern Stored routine name pattern. + + @return Error code. SP_OK is returned on success. Other SP_ constants are + used to indicate about errors. +*/ + +int +sp_show_status_routine(THD *thd, int type, const char *name_pattern) { TABLE *table; TABLE_LIST tables; int res; - DBUG_ENTER("db_show_routine_status"); + DBUG_ENTER("sp_show_status_routine"); + + DBUG_ASSERT(type == TYPE_ENUM_PROCEDURE || + type == TYPE_ENUM_FUNCTION); memset(&tables, 0, sizeof(tables)); tables.db= (char*)"mysql"; @@ -789,14 +866,14 @@ db_show_routine_status(THD *thd, int type, const char *wild) { switch (used_field->field_type) { case MYSQL_TYPE_TIMESTAMP: - field_list.push_back(item= - new Item_return_date_time(used_field->field_name, - MYSQL_TYPE_DATETIME)); + item= new Item_return_date_time(used_field->field_name, + MYSQL_TYPE_DATETIME); + field_list.push_back(item); break; default: - field_list.push_back(item=new Item_empty_string(used_field->field_name, - used_field-> - field_length)); + item= new Item_empty_string(used_field->field_name, + used_field->field_length); + field_list.push_back(item); break; } } @@ -840,13 +917,16 @@ db_show_routine_status(THD *thd, int type, const char *wild) res= (res == HA_ERR_END_OF_FILE) ? 0 : SP_INTERNAL_ERROR; goto err_case1; } - if ((res= print_field_values(thd, table, used_fields, type, wild))) - goto err_case1; - while (!table->file->index_next(table->record[0])) + + do { - if ((res= print_field_values(thd, table, used_fields, type, wild))) + res= print_field_values(thd, table, used_fields, type, name_pattern); + + if (res) goto err_case1; } + while (!table->file->index_next(table->record[0])); + res= SP_OK; } @@ -913,9 +993,60 @@ err: } -/***************************************************************************** - PROCEDURE -******************************************************************************/ +/** + Implement SHOW CREATE statement for stored routines. + + The operation finds the stored routine object specified by name and then + calls sp_head::show_create_routine() for the object. + + @param thd Thread context. + @param type Stored routine type + (TYPE_ENUM_PROCEDURE or TYPE_ENUM_FUNCTION) + @param name Stored routine name. + + @return Error status. + @retval FALSE on success + @retval TRUE on error +*/ + +bool +sp_show_create_routine(THD *thd, int type, sp_name *name) +{ + bool err_status= TRUE; + sp_head *sp; + sp_cache *cache = type == TYPE_ENUM_PROCEDURE ? + thd->sp_proc_cache : thd->sp_func_cache; + + DBUG_ENTER("sp_show_create_routine"); + DBUG_PRINT("enter", ("name: %.*s", name->m_name.length, name->m_name.str)); + + DBUG_ASSERT(type == TYPE_ENUM_PROCEDURE || + type == TYPE_ENUM_FUNCTION); + + if (type == TYPE_ENUM_PROCEDURE) + { + /* + SHOW CREATE PROCEDURE may require two instances of one sp_head + object when SHOW CREATE PROCEDURE is called for the procedure that + is being executed. Basically, there is no actual recursion, so we + increase the recursion limit for this statement (kind of hack). + + SHOW CREATE FUNCTION does not require this because SHOW CREATE + statements are prohibitted within stored functions. + */ + + thd->variables.max_sp_recursion_depth++; + } + + if ((sp= sp_find_routine(thd, type, name, &cache, FALSE))) + err_status= sp->show_create_routine(thd, type); + + if (type == TYPE_ENUM_PROCEDURE) + thd->variables.max_sp_recursion_depth--; + + DBUG_RETURN(err_status); +} + /* Obtain object representing stored procedure/function by its name from @@ -1109,151 +1240,6 @@ sp_routine_exists_in_table(THD *thd, int type, sp_name *name) } -int -sp_create_procedure(THD *thd, sp_head *sp) -{ - int ret; - DBUG_ENTER("sp_create_procedure"); - DBUG_PRINT("enter", ("name: %.*s", sp->m_name.length, sp->m_name.str)); - - ret= db_create_routine(thd, TYPE_ENUM_PROCEDURE, sp); - DBUG_RETURN(ret); -} - - -int -sp_drop_procedure(THD *thd, sp_name *name) -{ - int ret; - DBUG_ENTER("sp_drop_procedure"); - DBUG_PRINT("enter", ("name: %.*s", name->m_name.length, name->m_name.str)); - - ret= db_drop_routine(thd, TYPE_ENUM_PROCEDURE, name); - if (!ret) - sp_cache_invalidate(); - DBUG_RETURN(ret); -} - - -int -sp_update_procedure(THD *thd, sp_name *name, st_sp_chistics *chistics) -{ - int ret; - DBUG_ENTER("sp_update_procedure"); - DBUG_PRINT("enter", ("name: %.*s", name->m_name.length, name->m_name.str)); - - ret= db_update_routine(thd, TYPE_ENUM_PROCEDURE, name, chistics); - if (!ret) - sp_cache_invalidate(); - DBUG_RETURN(ret); -} - - -int -sp_show_create_procedure(THD *thd, sp_name *name) -{ - int ret= SP_KEY_NOT_FOUND; - sp_head *sp; - DBUG_ENTER("sp_show_create_procedure"); - DBUG_PRINT("enter", ("name: %.*s", name->m_name.length, name->m_name.str)); - - /* - Increase the recursion limit for this statement. SHOW CREATE PROCEDURE - does not do actual recursion. - */ - thd->variables.max_sp_recursion_depth++; - if ((sp= sp_find_routine(thd, TYPE_ENUM_PROCEDURE, name, - &thd->sp_proc_cache, FALSE))) - ret= sp->show_create_procedure(thd); - - thd->variables.max_sp_recursion_depth--; - DBUG_RETURN(ret); -} - - -int -sp_show_status_procedure(THD *thd, const char *wild) -{ - int ret; - DBUG_ENTER("sp_show_status_procedure"); - - ret= db_show_routine_status(thd, TYPE_ENUM_PROCEDURE, wild); - DBUG_RETURN(ret); -} - - -/***************************************************************************** - FUNCTION -******************************************************************************/ - -int -sp_create_function(THD *thd, sp_head *sp) -{ - int ret; - DBUG_ENTER("sp_create_function"); - DBUG_PRINT("enter", ("name: %.*s", sp->m_name.length, sp->m_name.str)); - - ret= db_create_routine(thd, TYPE_ENUM_FUNCTION, sp); - DBUG_RETURN(ret); -} - - -int -sp_drop_function(THD *thd, sp_name *name) -{ - int ret; - DBUG_ENTER("sp_drop_function"); - DBUG_PRINT("enter", ("name: %.*s", name->m_name.length, name->m_name.str)); - - ret= db_drop_routine(thd, TYPE_ENUM_FUNCTION, name); - if (!ret) - sp_cache_invalidate(); - DBUG_RETURN(ret); -} - - -int -sp_update_function(THD *thd, sp_name *name, st_sp_chistics *chistics) -{ - int ret; - DBUG_ENTER("sp_update_procedure"); - DBUG_PRINT("enter", ("name: %.*s", name->m_name.length, name->m_name.str)); - - ret= db_update_routine(thd, TYPE_ENUM_FUNCTION, name, chistics); - if (!ret) - sp_cache_invalidate(); - DBUG_RETURN(ret); -} - - -int -sp_show_create_function(THD *thd, sp_name *name) -{ - sp_head *sp; - DBUG_ENTER("sp_show_create_function"); - DBUG_PRINT("enter", ("name: %.*s", name->m_name.length, name->m_name.str)); - - if ((sp= sp_find_routine(thd, TYPE_ENUM_FUNCTION, name, - &thd->sp_func_cache, FALSE))) - { - int ret= sp->show_create_function(thd); - - DBUG_RETURN(ret); - } - DBUG_RETURN(SP_KEY_NOT_FOUND); -} - - -int -sp_show_status_function(THD *thd, const char *wild) -{ - int ret; - DBUG_ENTER("sp_show_status_function"); - ret= db_show_routine_status(thd, TYPE_ENUM_FUNCTION, wild); - DBUG_RETURN(ret); -} - - /* Structure that represents element in the set of stored routines used by statement or routine. @@ -44,37 +44,20 @@ sp_exist_routines(THD *thd, TABLE_LIST *procs, bool any, bool no_error); int sp_routine_exists_in_table(THD *thd, int type, sp_name *name); -int -sp_create_procedure(THD *thd, sp_head *sp); - -int -sp_drop_procedure(THD *thd, sp_name *name); - +bool +sp_show_create_routine(THD *thd, int type, sp_name *name); int -sp_update_procedure(THD *thd, sp_name *name, st_sp_chistics *chistics); +sp_show_status_routine(THD *thd, int type, const char *wild); int -sp_show_create_procedure(THD *thd, sp_name *name); +sp_create_routine(THD *thd, int type, sp_head *sp); int -sp_show_status_procedure(THD *thd, const char *wild); +sp_update_routine(THD *thd, int type, sp_name *name, st_sp_chistics *chistics); int -sp_create_function(THD *thd, sp_head *sp); - -int -sp_drop_function(THD *thd, sp_name *name); - -int -sp_update_function(THD *thd, sp_name *name, st_sp_chistics *chistics); - -int -sp_show_create_function(THD *thd, sp_name *name); - -int -sp_show_status_function(THD *thd, const char *wild); - +sp_drop_routine(THD *thd, int type, sp_name *name); /* Procedures for pre-caching of stored routines and building table list diff --git a/sql/sp_head.cc b/sql/sp_head.cc index 2f78f0b24dd..5aa0409e001 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -637,17 +637,10 @@ int sp_head::create(THD *thd) { DBUG_ENTER("sp_head::create"); - int ret; - DBUG_PRINT("info", ("type: %d name: %s params: %s body: %s", m_type, m_name.str, m_params.str, m_body.str)); - if (m_type == TYPE_ENUM_FUNCTION) - ret= sp_create_function(thd, this); - else - ret= sp_create_procedure(thd, this); - - DBUG_RETURN(ret); + DBUG_RETURN(sp_create_routine(thd, m_type, this)); } sp_head::~sp_head() @@ -2104,49 +2097,97 @@ bool check_show_routine_access(THD *thd, sp_head *sp, bool *full_access) } -int -sp_head::show_create_procedure(THD *thd) +/** + Implement SHOW CREATE statement for stored routines. + + @param thd Thread context. + @param type Stored routine type + (TYPE_ENUM_PROCEDURE or TYPE_ENUM_FUNCTION) + + @return Error status. + @retval FALSE on success + @retval TRUE on error +*/ + +bool +sp_head::show_create_routine(THD *thd, int type) { + const char *col1_caption= type == TYPE_ENUM_PROCEDURE ? + "Procedure" : "Function"; + + const char *col3_caption= type == TYPE_ENUM_PROCEDURE ? + "Create Procedure" : "Create Function"; + + bool err_status; + Protocol *protocol= thd->protocol; - char buff[2048]; - String buffer(buff, sizeof(buff), system_charset_info); - int res; - List<Item> field_list; + List<Item> fields; + LEX_STRING sql_mode; + bool full_access; - DBUG_ENTER("sp_head::show_create_procedure"); - DBUG_PRINT("info", ("procedure %s", m_name.str)); + + DBUG_ENTER("sp_head::show_create_routine"); + DBUG_PRINT("info", ("routine %s", m_name.str)); + + DBUG_ASSERT(type == TYPE_ENUM_PROCEDURE || + type == TYPE_ENUM_FUNCTION); if (check_show_routine_access(thd, this, &full_access)) - DBUG_RETURN(1); + DBUG_RETURN(TRUE); - sys_var_thd_sql_mode::symbolic_mode_representation(thd, m_sql_mode, - &sql_mode); - field_list.push_back(new Item_empty_string("Procedure", NAME_CHAR_LEN)); - field_list.push_back(new Item_empty_string("sql_mode", sql_mode.length)); - // 1024 is for not to confuse old clients - Item_empty_string *definition= - new Item_empty_string("Create Procedure", max(buffer.length(),1024)); - definition->maybe_null= TRUE; - field_list.push_back(definition); + sys_var_thd_sql_mode::symbolic_mode_representation( + thd, m_sql_mode, &sql_mode); + + /* Send header. */ + + fields.push_back(new Item_empty_string(col1_caption, NAME_LEN)); + fields.push_back(new Item_empty_string("sql_mode", sql_mode.length)); + + { + /* + NOTE: SQL statement field must be not less than 1024 in order not to + confuse old clients. + */ + + Item_empty_string *stmt_fld= + new Item_empty_string(col3_caption, + max(m_defstr.length, 1024)); + + stmt_fld->maybe_null= TRUE; + + fields.push_back(stmt_fld); + } + + if (protocol->send_fields(&fields, + Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF)) + { + DBUG_RETURN(TRUE); + } + + /* Send data. */ - if (protocol->send_fields(&field_list, Protocol::SEND_NUM_ROWS | - Protocol::SEND_EOF)) - DBUG_RETURN(1); protocol->prepare_for_resend(); + protocol->store(m_name.str, m_name.length, system_charset_info); - protocol->store((char*) sql_mode.str, sql_mode.length, system_charset_info); + protocol->store(sql_mode.str, sql_mode.length, system_charset_info); + if (full_access) - protocol->store(m_defstr.str, m_defstr.length, system_charset_info); + protocol->store(m_defstr.str, m_defstr.length, &my_charset_bin); else protocol->store_null(); - res= protocol->write(); - send_eof(thd); - DBUG_RETURN(res); + err_status= protocol->write(); + + if (!err_status) + send_eof(thd); + + DBUG_RETURN(err_status); } + + /* Add instruction to SP @@ -2170,48 +2211,6 @@ void sp_head::add_instr(sp_instr *instr) } -int -sp_head::show_create_function(THD *thd) -{ - Protocol *protocol= thd->protocol; - char buff[2048]; - String buffer(buff, sizeof(buff), system_charset_info); - int res; - List<Item> field_list; - LEX_STRING sql_mode; - bool full_access; - DBUG_ENTER("sp_head::show_create_function"); - DBUG_PRINT("info", ("procedure %s", m_name.str)); - - if (check_show_routine_access(thd, this, &full_access)) - DBUG_RETURN(1); - - sys_var_thd_sql_mode::symbolic_mode_representation(thd, m_sql_mode, - &sql_mode); - field_list.push_back(new Item_empty_string("Function",NAME_CHAR_LEN)); - field_list.push_back(new Item_empty_string("sql_mode", sql_mode.length)); - Item_empty_string *definition= - new Item_empty_string("Create Function", max(buffer.length(),1024)); - definition->maybe_null= TRUE; - field_list.push_back(definition); - - if (protocol->send_fields(&field_list, - Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF)) - DBUG_RETURN(1); - protocol->prepare_for_resend(); - protocol->store(m_name.str, m_name.length, system_charset_info); - protocol->store(sql_mode.str, sql_mode.length, system_charset_info); - if (full_access) - protocol->store(m_defstr.str, m_defstr.length, system_charset_info); - else - protocol->store_null(); - res= protocol->write(); - send_eof(thd); - - DBUG_RETURN(res); -} - - /* Do some minimal optimization of the code: 1) Mark used instructions diff --git a/sql/sp_head.h b/sql/sp_head.h index 8ebba314ec5..4e3e11e468d 100644 --- a/sql/sp_head.h +++ b/sql/sp_head.h @@ -225,11 +225,8 @@ public: bool execute_procedure(THD *thd, List<Item> *args); - int - show_create_procedure(THD *thd); - - int - show_create_function(THD *thd); + bool + show_create_routine(THD *thd, int type); void add_instr(sp_instr *instr); diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 6d6f159a299..b791491ab08 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -3928,11 +3928,15 @@ create_sp_error: already puts on CREATE FUNCTION. */ /* Conditionally writes to binlog */ - if (lex->sql_command == SQLCOM_ALTER_PROCEDURE) - sp_result= sp_update_procedure(thd, lex->spname, - &lex->sp_chistics); - else - sp_result= sp_update_function(thd, lex->spname, &lex->sp_chistics); + + int type= lex->sql_command == SQLCOM_ALTER_PROCEDURE ? + TYPE_ENUM_PROCEDURE : + TYPE_ENUM_FUNCTION; + + sp_result= sp_update_routine(thd, + type, + lex->spname, + &lex->sp_chistics); } } switch (sp_result) @@ -3982,10 +3986,12 @@ create_sp_error: } #endif /* Conditionally writes to binlog */ - if (lex->sql_command == SQLCOM_DROP_PROCEDURE) - sp_result= sp_drop_procedure(thd, lex->spname); - else - sp_result= sp_drop_function(thd, lex->spname); + + int type= lex->sql_command == SQLCOM_DROP_PROCEDURE ? + TYPE_ENUM_PROCEDURE : + TYPE_ENUM_FUNCTION; + + sp_result= sp_drop_routine(thd, type, lex->spname); } else { @@ -4042,8 +4048,8 @@ create_sp_error: } case SQLCOM_SHOW_CREATE_PROC: { - if (sp_show_create_procedure(thd, lex->spname) != SP_OK) - { /* We don't distinguish between errors for now */ + if (sp_show_create_routine(thd, TYPE_ENUM_PROCEDURE, lex->spname)) + { my_error(ER_SP_DOES_NOT_EXIST, MYF(0), SP_COM_STRING(lex), lex->spname->m_name.str); goto error; @@ -4052,8 +4058,8 @@ create_sp_error: } case SQLCOM_SHOW_CREATE_FUNC: { - if (sp_show_create_function(thd, lex->spname) != SP_OK) - { /* We don't distinguish between errors for now */ + if (sp_show_create_routine(thd, TYPE_ENUM_FUNCTION, lex->spname)) + { my_error(ER_SP_DOES_NOT_EXIST, MYF(0), SP_COM_STRING(lex), lex->spname->m_name.str); goto error; @@ -4063,14 +4069,14 @@ create_sp_error: #ifdef NOT_USED case SQLCOM_SHOW_STATUS_PROC: { - res= sp_show_status_procedure(thd, (lex->wild ? - lex->wild->ptr() : NullS)); + res= sp_show_status_routine(thd, TYPE_ENUM_PROCEDURE, + (lex->wild ? lex->wild->ptr() : NullS)); break; } case SQLCOM_SHOW_STATUS_FUNC: { - res= sp_show_status_function(thd, (lex->wild ? - lex->wild->ptr() : NullS)); + res= sp_show_status_routine(thd, TYPE_ENUM_FUNCTION, + (lex->wild ? lex->wild->ptr() : NullS)); break; } #endif |