diff options
37 files changed, 469 insertions, 173 deletions
diff --git a/Docs/sp-implemented.txt b/Docs/sp-implemented.txt index 97d4df2b62c..b3b12b7edb8 100644 --- a/Docs/sp-implemented.txt +++ b/Docs/sp-implemented.txt @@ -11,6 +11,8 @@ Summary of Not Yet Implemented: - SQL-99 COMMIT (related to BEGIN/END) - DECLARE CURSOR ... - FOR-loops (as it requires cursors) + - CASCADE/RESTRICT for ALTER and DROP + - ALTER/DROP METHOD (as it implies User Defined Types) Summary of what's implemented: @@ -66,15 +68,23 @@ List of what's implemented: databases.) -Open questions: +Closed questions: - What is the expected result when creating a procedure with a name that already exists? An error or overwrite? + Answer: Error + - Do PROCEDUREs and FUNCTIONs share namespace or not? I think not, but the we need to flag the type in the mysql.proc table and the name alone is not a unique key any more, or, we have separate tables. (Unfortunately, mysql.func is already taken. Use "sfunc" and maybe even rename "proc" into "sproc" while we still can, for consistency?) + Answer: Same tables, with an additional key-field for the type. + + +Open questions: + - SQL-99 variables and parameters are typed. For the present we don't do any type checking, since this is the way MySQL works. I still don't know - if we should keep it this way, or implement type checking. + if we should keep it this way, or implement type checking. Possibly we + should have optional, uset-settable, type checking. diff --git a/include/mysqld_error.h b/include/mysqld_error.h index f52fc75be86..48e7f42c707 100644 --- a/include/mysqld_error.h +++ b/include/mysqld_error.h @@ -276,5 +276,5 @@ #define ER_SP_LABEL_MISMATCH 1257 #define ER_SP_UNINIT_VAR 1258 #define ER_SP_BADSELECT 1259 -#define ER_ERROR_MESSAGES 260 - +#define ER_SP_BADRETURN 1260 +#define ER_ERROR_MESSAGES 261 diff --git a/mysql-test/install_test_db.sh b/mysql-test/install_test_db.sh index aff00f6ce00..ed3b0fd683e 100644 --- a/mysql-test/install_test_db.sh +++ b/mysql-test/install_test_db.sh @@ -251,8 +251,9 @@ if test ! -f $mdata/proc.frm then c_p="$c_p CREATE TABLE proc (" c_p="$c_p name char(64) binary DEFAULT '' NOT NULL," + c_p="$c_p type enum('function','procedure') NOT NULL," c_p="$c_p body blob DEFAULT '' NOT NULL," - c_p="$c_p PRIMARY KEY (name)" + c_p="$c_p PRIMARY KEY (name,type)" c_p="$c_p )" c_p="$c_p comment='Stored Procedures';" fi diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index ddc8c805f78..ba2709ebb7e 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -375,4 +375,20 @@ create table test.t2 select * from test.t1; insert into test.t2 values (concat(x, "2"), y+2); end; drop procedure create_select; +create function foo returns real soname "foo.so"; +Can't open shared library 'foo.so' (errno: 22 foo.so: cannot open shared object file: No such file or director) +create function e() returns double +return 2.7182818284590452354; +drop function e; +create function fac(n int unsigned) returns bigint unsigned +begin +declare f bigint unsigned; +set f = 1; +while n > 1 do +set f = f * n; +set n = n - 1; +end while; +return f; +end; +drop function fac; drop table t1; diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 55aa287b8b0..2be927c78ff 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -433,5 +433,32 @@ end| #drop table t2| drop procedure create_select| +# Check that we get the right error, i.e. UDF declaration parses correctly, +# but foo.so doesn't exist. +--error 1126 +create function foo returns real soname "foo.so"| + +# A minimal, constant FUNCTION. +create function e() returns double + return 2.7182818284590452354| + +drop function e| + + +# A function with flow control and a RETURN statement +create function fac(n int unsigned) returns bigint unsigned +begin + declare f bigint unsigned; + + set f = 1; + while n > 1 do + set f = f * n; + set n = n - 1; + end while; + return f; +end| + +drop function fac| + delimiter ;| drop table t1; diff --git a/scripts/mysql_install_db.sh b/scripts/mysql_install_db.sh index 8ff5653d299..b7dc3fce194 100644 --- a/scripts/mysql_install_db.sh +++ b/scripts/mysql_install_db.sh @@ -312,8 +312,9 @@ then c_p="$c_p CREATE TABLE proc (" c_p="$c_p name char(64) binary DEFAULT '' NOT NULL," + c_p="$c_p type enum('function','procedure') NOT NULL," c_p="$c_p body blob DEFAULT '' NOT NULL," - c_p="$c_p PRIMARY KEY (name)" + c_p="$c_p PRIMARY KEY (name,type)" c_p="$c_p )" c_p="$c_p comment='Stored Procedures';" fi diff --git a/sql/lex.h b/sql/lex.h index 0cec9ef8a5d..33fbb90d21f 100644 --- a/sql/lex.h +++ b/sql/lex.h @@ -175,7 +175,7 @@ static SYMBOL symbols[] = { { "FOR", SYM(FOR_SYM),0,0}, { "FULL", SYM(FULL),0,0}, { "FULLTEXT", SYM(FULLTEXT_SYM),0,0}, - { "FUNCTION", SYM(UDF_SYM),0,0}, + { "FUNCTION", SYM(FUNCTION_SYM),0,0}, { "GEOMETRY", SYM(GEOMETRY_SYM),0,0}, { "GLOBAL", SYM(GLOBAL_SYM),0,0}, { "GRANT", SYM(GRANT),0,0}, @@ -332,7 +332,8 @@ static SYMBOL symbols[] = { { "USER_RESOURCES", SYM(RESOURCES),0,0}, { "RESTORE", SYM(RESTORE_SYM),0,0}, { "RESTRICT", SYM(RESTRICT),0,0}, - { "RETURNS", SYM(UDF_RETURNS_SYM),0,0}, + { "RETURN", SYM(RETURN_SYM),0,0}, + { "RETURNS", SYM(RETURNS_SYM),0,0}, { "REVOKE", SYM(REVOKE),0,0}, { "RIGHT", SYM(RIGHT),0,0}, { "RLIKE", SYM(REGEXP),0,0}, /* Like in mSQL2 */ diff --git a/sql/share/czech/errmsg.txt b/sql/share/czech/errmsg.txt index dd2df4c5243..67a7fc8259d 100644 --- a/sql/share/czech/errmsg.txt +++ b/sql/share/czech/errmsg.txt @@ -270,3 +270,4 @@ v/* "End-label without match" "Referring to uninitialized variable" "SELECT in a stored procedure must have INTO" +"RETURN is only allowed in a stored FUNCTION" diff --git a/sql/share/danish/errmsg.txt b/sql/share/danish/errmsg.txt index 3c66c5a64f2..193d8204c7d 100644 --- a/sql/share/danish/errmsg.txt +++ b/sql/share/danish/errmsg.txt @@ -264,3 +264,4 @@ "End-label without match" "Referring to uninitialized variable" "SELECT in a stored procedure must have INTO" +"RETURN is only allowed in a stored FUNCTION" diff --git a/sql/share/dutch/errmsg.txt b/sql/share/dutch/errmsg.txt index cdae9092602..e328bc64cfd 100644 --- a/sql/share/dutch/errmsg.txt +++ b/sql/share/dutch/errmsg.txt @@ -272,3 +272,4 @@ "End-label without match" "Referring to uninitialized variable" "SELECT in a stored procedure must have INTO" +"RETURN is only allowed in a stored FUNCTION" diff --git a/sql/share/english/errmsg.txt b/sql/share/english/errmsg.txt index 6aa45c2ced2..b2e56d8b701 100644 --- a/sql/share/english/errmsg.txt +++ b/sql/share/english/errmsg.txt @@ -261,3 +261,4 @@ "End-label without match" "Referring to uninitialized variable" "SELECT in a stored procedure must have INTO" +"RETURN is only allowed in a stored FUNCTION" diff --git a/sql/share/estonian/errmsg.txt b/sql/share/estonian/errmsg.txt index af32e4cc630..46b7240dd6d 100644 --- a/sql/share/estonian/errmsg.txt +++ b/sql/share/estonian/errmsg.txt @@ -266,3 +266,4 @@ "End-label without match" "Referring to uninitialized variable" "SELECT in a stored procedure must have INTO" +"RETURN is only allowed in a stored FUNCTION" diff --git a/sql/share/french/errmsg.txt b/sql/share/french/errmsg.txt index 147948d567f..5dba7cd4739 100644 --- a/sql/share/french/errmsg.txt +++ b/sql/share/french/errmsg.txt @@ -261,3 +261,4 @@ "End-label without match" "Referring to uninitialized variable" "SELECT in a stored procedure must have INTO" +"RETURN is only allowed in a stored FUNCTION" diff --git a/sql/share/german/errmsg.txt b/sql/share/german/errmsg.txt index c3b4713241a..0a90b30d07c 100644 --- a/sql/share/german/errmsg.txt +++ b/sql/share/german/errmsg.txt @@ -271,3 +271,4 @@ "End-label without match" "Referring to uninitialized variable" "SELECT in a stored procedure must have INTO" +"RETURN is only allowed in a stored FUNCTION" diff --git a/sql/share/greek/errmsg.txt b/sql/share/greek/errmsg.txt index 43ba3c3b173..0d64095b2e3 100644 --- a/sql/share/greek/errmsg.txt +++ b/sql/share/greek/errmsg.txt @@ -261,3 +261,4 @@ "End-label without match" "Referring to uninitialized variable" "SELECT in a stored procedure must have INTO" +"RETURN is only allowed in a stored FUNCTION" diff --git a/sql/share/hungarian/errmsg.txt b/sql/share/hungarian/errmsg.txt index 9355daa634e..4927b9b86e0 100644 --- a/sql/share/hungarian/errmsg.txt +++ b/sql/share/hungarian/errmsg.txt @@ -263,3 +263,4 @@ "End-label without match" "Referring to uninitialized variable" "SELECT in a stored procedure must have INTO" +"RETURN is only allowed in a stored FUNCTION" diff --git a/sql/share/italian/errmsg.txt b/sql/share/italian/errmsg.txt index aee2c229f80..7a38bb4c9f0 100644 --- a/sql/share/italian/errmsg.txt +++ b/sql/share/italian/errmsg.txt @@ -261,3 +261,4 @@ "End-label without match" "Referring to uninitialized variable" "SELECT in a stored procedure must have INTO" +"RETURN is only allowed in a stored FUNCTION" diff --git a/sql/share/japanese/errmsg.txt b/sql/share/japanese/errmsg.txt index 2b7515c12d1..21c891fb982 100644 --- a/sql/share/japanese/errmsg.txt +++ b/sql/share/japanese/errmsg.txt @@ -263,3 +263,4 @@ "End-label without match" "Referring to uninitialized variable" "SELECT in a stored procedure must have INTO" +"RETURN is only allowed in a stored FUNCTION" diff --git a/sql/share/korean/errmsg.txt b/sql/share/korean/errmsg.txt index b8e0e7a4f25..61aa944c92d 100644 --- a/sql/share/korean/errmsg.txt +++ b/sql/share/korean/errmsg.txt @@ -261,3 +261,4 @@ "End-label without match" "Referring to uninitialized variable" "SELECT in a stored procedure must have INTO" +"RETURN is only allowed in a stored FUNCTION" diff --git a/sql/share/norwegian-ny/errmsg.txt b/sql/share/norwegian-ny/errmsg.txt index 15894decc4a..527db96e708 100644 --- a/sql/share/norwegian-ny/errmsg.txt +++ b/sql/share/norwegian-ny/errmsg.txt @@ -263,3 +263,4 @@ "End-label without match" "Referring to uninitialized variable" "SELECT in a stored procedure must have INTO" +"RETURN is only allowed in a stored FUNCTION" diff --git a/sql/share/norwegian/errmsg.txt b/sql/share/norwegian/errmsg.txt index 01ce52ec060..9af96c322c6 100644 --- a/sql/share/norwegian/errmsg.txt +++ b/sql/share/norwegian/errmsg.txt @@ -263,3 +263,4 @@ "End-label without match" "Referring to uninitialized variable" "SELECT in a stored procedure must have INTO" +"RETURN is only allowed in a stored FUNCTION" diff --git a/sql/share/polish/errmsg.txt b/sql/share/polish/errmsg.txt index a2440d5928e..cec72e6a1a2 100644 --- a/sql/share/polish/errmsg.txt +++ b/sql/share/polish/errmsg.txt @@ -265,3 +265,4 @@ "End-label without match" "Referring to uninitialized variable" "SELECT in a stored procedure must have INTO" +"RETURN is only allowed in a stored FUNCTION" diff --git a/sql/share/portuguese/errmsg.txt b/sql/share/portuguese/errmsg.txt index 86b44f4d97d..93f1bc4af81 100644 --- a/sql/share/portuguese/errmsg.txt +++ b/sql/share/portuguese/errmsg.txt @@ -261,3 +261,4 @@ "End-label without match" "Referring to uninitialized variable" "SELECT in a stored procedure must have INTO" +"RETURN is only allowed in a stored FUNCTION" diff --git a/sql/share/romanian/errmsg.txt b/sql/share/romanian/errmsg.txt index f6cc343a70d..c6cd6efc9a0 100644 --- a/sql/share/romanian/errmsg.txt +++ b/sql/share/romanian/errmsg.txt @@ -265,3 +265,4 @@ "End-label without match" "Referring to uninitialized variable" "SELECT in a stored procedure must have INTO" +"RETURN is only allowed in a stored FUNCTION" diff --git a/sql/share/russian/errmsg.txt b/sql/share/russian/errmsg.txt index 33dc4e5d3b8..ecad689594d 100644 --- a/sql/share/russian/errmsg.txt +++ b/sql/share/russian/errmsg.txt @@ -264,3 +264,4 @@ "End-label without match" "Referring to uninitialized variable" "SELECT in a stored procedure must have INTO" +"RETURN is only allowed in a stored FUNCTION" diff --git a/sql/share/serbian/errmsg.txt b/sql/share/serbian/errmsg.txt index eb699fc812c..456df149576 100644 --- a/sql/share/serbian/errmsg.txt +++ b/sql/share/serbian/errmsg.txt @@ -257,3 +257,4 @@ "End-label without match" "Referring to uninitialized variable" "SELECT in a stored procedure must have INTO" +"RETURN is only allowed in a stored FUNCTION" diff --git a/sql/share/slovak/errmsg.txt b/sql/share/slovak/errmsg.txt index b1253642081..a2a9f94e970 100644 --- a/sql/share/slovak/errmsg.txt +++ b/sql/share/slovak/errmsg.txt @@ -269,3 +269,4 @@ "End-label without match" "Referring to uninitialized variable" "SELECT in a stored procedure must have INTO" +"RETURN is only allowed in a stored FUNCTION" diff --git a/sql/share/spanish/errmsg.txt b/sql/share/spanish/errmsg.txt index 6be74571317..9faddd8b7f1 100644 --- a/sql/share/spanish/errmsg.txt +++ b/sql/share/spanish/errmsg.txt @@ -262,3 +262,4 @@ "End-label without match" "Referring to uninitialized variable" "SELECT in a stored procedure must have INTO" +"RETURN is only allowed in a stored FUNCTION" diff --git a/sql/share/swedish/errmsg.txt b/sql/share/swedish/errmsg.txt index 512314cf856..33359dbf7aa 100644 --- a/sql/share/swedish/errmsg.txt +++ b/sql/share/swedish/errmsg.txt @@ -261,3 +261,4 @@ "End-label without match" "Referring to uninitialized variable" "SELECT in a stored procedure must have INTO" +"RETURN is only allowed in a stored FUNCTION" diff --git a/sql/share/ukrainian/errmsg.txt b/sql/share/ukrainian/errmsg.txt index 337157829a9..dfdb3e1c378 100644 --- a/sql/share/ukrainian/errmsg.txt +++ b/sql/share/ukrainian/errmsg.txt @@ -266,3 +266,4 @@ "End-label without match" "Referring to uninitialized variable" "SELECT in a stored procedure must have INTO" +"RETURN is only allowed in a stored FUNCTION" diff --git a/sql/sp.cc b/sql/sp.cc index 2d4cf97bce1..eb3a9871ab5 100644 --- a/sql/sp.cc +++ b/sql/sp.cc @@ -19,79 +19,170 @@ #include "sp.h" #include "sp_head.h" -// Finds the SP 'name'. Currently this always reads from the database -// and prepares (parse) it, but in the future it will first look in -// the in-memory cache for SPs. (And store newly prepared SPs there of -// course.) -sp_head * -sp_find_procedure(THD *thd, Item_string *iname) +/* + * + * DB storage of Stored PROCEDUREs and FUNCTIONs + * + */ + +static int +db_find_routine_aux(THD *thd, int type, char *name, uint namelen, + enum thr_lock_type ltype, TABLE **tablep) { - DBUG_ENTER("sp_find_procedure"); - extern int yyparse(void *thd); - LEX *tmplex; + DBUG_ENTER("db_find_routine_aux"); + DBUG_PRINT("enter", ("type: %d name: %*s", type, namelen, name)); TABLE *table; TABLE_LIST tables; - const char *defstr; - String *name; - sp_head *sp = NULL; + byte key[65]; // We know name is 64 and the enum is 1 byte + uint keylen; + int ret; + + // Put the key together + keylen= namelen; + if (keylen > sizeof(key)-1) + keylen= sizeof(key)-1; + memcpy(key, name, keylen); + memset(key+keylen, (int)' ', sizeof(key)-1 - keylen); // Pad with space + key[sizeof(key)-1]= type; + keylen= sizeof(key); - name = iname->const_string(); - DBUG_PRINT("enter", ("name: %*s", name->length(), name->c_ptr())); memset(&tables, 0, sizeof(tables)); tables.db= (char*)"mysql"; tables.real_name= tables.alias= (char*)"proc"; - if (! (table= open_ltable(thd, &tables, TL_READ))) - DBUG_RETURN(NULL); + if (! (table= open_ltable(thd, &tables, ltype))) + DBUG_RETURN(SP_OPEN_TABLE_FAILED); if (table->file->index_read_idx(table->record[0], 0, - (byte*)name->c_ptr(), name->length(), + key, keylen, HA_READ_KEY_EXACT)) - goto done; + { + close_thread_tables(thd); + DBUG_RETURN(SP_KEY_NOT_FOUND); + } + *tablep= table; - if ((defstr= get_field(&thd->mem_root, table->field[1])) == NULL) - goto done; + DBUG_RETURN(SP_OK); +} + +static int +db_find_routine(THD *thd, int type, char *name, uint namelen, sp_head **sphp) +{ + DBUG_ENTER("db_find_routine"); + DBUG_PRINT("enter", ("type: %d name: %*s", type, namelen, name)); + extern int yyparse(void *thd); + LEX *tmplex; + TABLE *table; + const char *defstr; + int ret; // QQ Set up our own mem_root here??? + ret= db_find_routine_aux(thd, type, name, namelen, TL_READ, &table); + if (ret != SP_OK) + goto done; + if ((defstr= get_field(&thd->mem_root, table->field[2])) == NULL) + { + ret= SP_GET_FIELD_FAILED; + goto done; + } + tmplex= lex_start(thd, (uchar*)defstr, strlen(defstr)); if (yyparse(thd) || thd->is_fatal_error || tmplex->sphead == NULL) - goto done; // Error + ret= SP_PARSE_ERROR; else - sp = tmplex->sphead; + *sphp= tmplex->sphead; done: - if (table) + if (ret == SP_OK && table) close_thread_tables(thd); - DBUG_RETURN(sp); + DBUG_RETURN(ret); } -int -sp_create_procedure(THD *thd, char *name, uint namelen, char *def, uint deflen) +static int +db_create_routine(THD *thd, int type, + char *name, uint namelen, char *def, uint deflen) { - DBUG_ENTER("sp_create_procedure"); - DBUG_PRINT("enter", ("name: %*s def: %*s", namelen, name, deflen, def)); - int ret= 0; + DBUG_ENTER("db_create_routine"); + DBUG_PRINT("enter", ("type: %d name: %*s def: %*s", type, namelen, name, deflen, def)); + int ret; TABLE *table; TABLE_LIST tables; memset(&tables, 0, sizeof(tables)); tables.db= (char*)"mysql"; tables.real_name= tables.alias= (char*)"proc"; - /* Allow creation of procedures even if we can't open proc table */ + if (! (table= open_ltable(thd, &tables, TL_WRITE))) + ret= SP_OPEN_TABLE_FAILED; + else { - ret= -1; - goto done; + restore_record(table, 2); // Get default values for fields + + table->field[0]->store(name, namelen, default_charset_info); + table->field[1]->store((longlong)type); + table->field[2]->store(def, deflen, default_charset_info); + + if (table->file->write_row(table->record[0])) + ret= SP_WRITE_ROW_FAILED; + else + ret= SP_OK; } - restore_record(table, 2); // Get default values for fields + if (ret == SP_OK && table) + close_thread_tables(thd); + DBUG_RETURN(ret); +} - table->field[0]->store(name, namelen, default_charset_info); - table->field[1]->store(def, deflen, default_charset_info); +static int +db_drop_routine(THD *thd, int type, char *name, uint namelen) +{ + DBUG_ENTER("db_drop_routine"); + DBUG_PRINT("enter", ("type: %d name: %*s", type, namelen, name)); + TABLE *table; + int ret; - ret= table->file->write_row(table->record[0]); + ret= db_find_routine_aux(thd, type, name, namelen, TL_WRITE, &table); + if (ret == SP_OK) + { + if (table->file->delete_row(table->record[0])) + ret= SP_DELETE_ROW_FAILED; + } + + if (ret == SP_OK && table) + close_thread_tables(thd); + DBUG_RETURN(ret); +} + + +/* + * + * PROCEDURE + * + */ + +sp_head * +sp_find_procedure(THD *thd, LEX_STRING *name) +{ + DBUG_ENTER("sp_find_procedure"); + sp_head *sp; + + DBUG_PRINT("enter", ("name: %*s", name->length, name->str)); + + if (db_find_routine(thd, TYPE_ENUM_PROCEDURE, + name->str, name->length, &sp) != SP_OK) + sp= NULL; + + DBUG_RETURN(sp); +} + +int +sp_create_procedure(THD *thd, char *name, uint namelen, char *def, uint deflen) +{ + DBUG_ENTER("sp_create_procedure"); + DBUG_PRINT("enter", ("name: %*s def: %*s", namelen, name, deflen, def)); + int ret; + + ret= db_create_routine(thd, TYPE_ENUM_PROCEDURE, name, namelen, def, deflen); - done: - close_thread_tables(thd); DBUG_RETURN(ret); } @@ -100,26 +191,55 @@ sp_drop_procedure(THD *thd, char *name, uint namelen) { DBUG_ENTER("sp_drop_procedure"); DBUG_PRINT("enter", ("name: %*s", namelen, name)); - TABLE *table; - TABLE_LIST tables; + int ret; - tables.db= (char *)"mysql"; - tables.real_name= tables.alias= (char *)"proc"; - if (! (table= open_ltable(thd, &tables, TL_WRITE))) - goto err; - if (! table->file->index_read_idx(table->record[0], 0, - (byte *)name, namelen, - HA_READ_KEY_EXACT)) - { - int error; + ret= db_drop_routine(thd, TYPE_ENUM_PROCEDURE, name, namelen); + + DBUG_RETURN(ret); +} - if ((error= table->file->delete_row(table->record[0]))) - table->file->print_error(error, MYF(0)); - } - close_thread_tables(thd); - DBUG_RETURN(0); - err: - close_thread_tables(thd); - DBUG_RETURN(-1); +/* + * + * FUNCTION + * + */ + +sp_head * +sp_find_function(THD *thd, LEX_STRING *name) +{ + DBUG_ENTER("sp_find_function_i"); + sp_head *sp; + + DBUG_PRINT("enter", ("name: %*s", name->length, name->str)); + + if (db_find_routine(thd, TYPE_ENUM_FUNCTION, + name->str, name->length, &sp) != SP_OK) + sp= NULL; + + DBUG_RETURN(sp); +} + +int +sp_create_function(THD *thd, char *name, uint namelen, char *def, uint deflen) +{ + DBUG_ENTER("sp_create_function"); + DBUG_PRINT("enter", ("name: %*s def: %*s", namelen, name, deflen, def)); + int ret; + + ret= db_create_routine(thd, TYPE_ENUM_FUNCTION, name, namelen, def, deflen); + + DBUG_RETURN(ret); +} + +int +sp_drop_function(THD *thd, char *name, uint namelen) +{ + DBUG_ENTER("sp_drop_function"); + DBUG_PRINT("enter", ("name: %*s", namelen, name)); + int ret; + + ret= db_drop_routine(thd, TYPE_ENUM_FUNCTION, name, namelen); + + DBUG_RETURN(ret); } @@ -18,11 +18,17 @@ #ifndef _SP_H_ #define _SP_H_ -// -// Finds a stored procedure given its name. Returns NULL if not found. -// +// Return codes from sp_create_* and sp_drop_*: +#define SP_OK 0 +#define SP_KEY_NOT_FOUND -1 +#define SP_OPEN_TABLE_FAILED -2 +#define SP_WRITE_ROW_FAILED -3 +#define SP_DELETE_ROW_FAILED -4 +#define SP_GET_FIELD_FAILED -5 +#define SP_PARSE_ERROR -6 + sp_head * -sp_find_procedure(THD *thd, Item_string *name); +sp_find_procedure(THD *thd, LEX_STRING *name); int sp_create_procedure(THD *thd, char *name, uint namelen, char *def, uint deflen); @@ -30,15 +36,14 @@ sp_create_procedure(THD *thd, char *name, uint namelen, char *def, uint deflen); int sp_drop_procedure(THD *thd, char *name, uint namelen); -#if 0 + sp_head * -sp_find_function(THD *thd, Item_string *name); +sp_find_function(THD *thd, LEX_STRING *name); int sp_create_function(THD *thd, char *name, uint namelen, char *def, uint deflen); int sp_drop_function(THD *thd, char *name, uint namelen); -#endif #endif /* _SP_H_ */ diff --git a/sql/sp_head.cc b/sql/sp_head.cc index ff487429ec7..8682fa2cd9d 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -102,11 +102,20 @@ sp_head::create(THD *thd) DBUG_ENTER("sp_head::create"); String *name= m_name->const_string(); String *def= m_defstr->const_string(); + int ret; + + DBUG_PRINT("info", ("type: %d name: %s def: %s", + m_type, name->c_ptr(), def->c_ptr())); + if (m_type == TYPE_ENUM_FUNCTION) + ret= sp_create_function(thd, + name->c_ptr(), name->length(), + def->c_ptr(), def->length()); + else + ret= sp_create_procedure(thd, + name->c_ptr(), name->length(), + def->c_ptr(), def->length()); - DBUG_PRINT("info", ("name: %s def: %s", name->c_ptr(), def->c_ptr())); - DBUG_RETURN(sp_create_procedure(thd, - name->c_ptr(), name->length(), - def->c_ptr(), def->length())); + DBUG_RETURN(ret); } int @@ -127,7 +136,7 @@ sp_head::execute(THD *thd) { uint i; List_iterator_fast<Item> li(m_call_lex->value_list); - Item *it = li++; // Skip first one, it's the procedure name + Item *it; nctx = new sp_rcontext(csize); if (! octx) @@ -184,7 +193,7 @@ sp_head::execute(THD *thd) if (ret == 0 && csize > 0) { List_iterator_fast<Item> li(m_call_lex->value_list); - Item *it = li++; // Skip first one, it's the procedure name + Item *it; // Copy back all OUT or INOUT values to the previous frame, or // set global user variables @@ -273,23 +282,18 @@ sp_head::restore_lex(THD *thd) // Collect some data from the sub statement lex. if (thd->lex.sql_command == SQLCOM_CALL) { - // We know they are Item_strings (since we put them there ourselves) // It would be slightly faster to keep the list sorted, but we need // an "insert before" method to do that. - Item_string *proc= static_cast<Item_string*>(thd->lex.value_list.head()); - String *snew= proc->val_str(NULL); - List_iterator_fast<Item_string> li(m_calls); - Item_string *it; + char *proc= thd->lex.udf.name.str; - while ((it= li++)) - { - String *sold= it->val_str(NULL); + List_iterator_fast<char *> li(m_calls); + char **it; - if (stringcmp(snew, sold) == 0) + while ((it= li++)) + if (strcasecmp(proc, *it) == 0) break; - } if (! it) - m_calls.push_back(proc); + m_calls.push_back(&proc); } // Merge used tables diff --git a/sql/sp_head.h b/sql/sp_head.h index 820193e8184..b0f57757f98 100644 --- a/sql/sp_head.h +++ b/sql/sp_head.h @@ -24,6 +24,11 @@ #include <stddef.h> +// Values for the type enum. This reflects the order of the enum declaration +// in the CREATE TABLE command. +#define TYPE_ENUM_FUNCTION 1 +#define TYPE_ENUM_PROCEDURE 2 + struct sp_label; class sp_instr; @@ -35,8 +40,10 @@ class sp_head : public Sql_alloc public: + int m_type; // TYPE_ENUM_FUNCTION or TYPE_ENUM_PROCEDURE + enum enum_field_types m_returns; // For FUNCTIONs only my_bool m_simple_case; // TRUE if parsing simple case, FALSE otherwise - List<Item_string> m_calls; // Called procedures. + List<char *> m_calls; // Called procedures. List<char *> m_tables; // Used tables. static void *operator new(size_t size) @@ -87,6 +94,15 @@ public: void backpatch(struct sp_label *); + char *name(uint *lenp = 0) const + { + String *n= m_name->const_string(); + + if (lenp) + *lenp= n->length(); + return n->c_ptr(); + } + private: Item_string *m_name; @@ -99,7 +115,7 @@ private: struct sp_label *lab; sp_instr *instr; } bp_t; - List<bp_t> m_backpatch; // Instructions needing backpaching + List<bp_t> m_backpatch; // Instructions needing backpatching inline sp_instr * get_instr(uint i) diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 5e3a0a5762b..9a4b4b8bd08 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -75,8 +75,8 @@ enum enum_sql_command { SQLCOM_SHOW_WARNS, SQLCOM_EMPTY_QUERY, SQLCOM_SHOW_ERRORS, SQLCOM_SHOW_COLUMN_TYPES, SQLCOM_SHOW_TABLE_TYPES, SQLCOM_SHOW_PRIVILEGES, SQLCOM_HELP, - SQLCOM_CREATE_PROCEDURE, SQLCOM_CALL, SQLCOM_DROP_PROCEDURE, - SQLCOM_ALTER_PROCEDURE, + SQLCOM_CREATE_PROCEDURE, SQLCOM_CALL, + SQLCOM_DROP_PROCEDURE, SQLCOM_ALTER_PROCEDURE,SQLCOM_ALTER_FUNCTION, /* This should be the last !!! */ SQLCOM_END diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index ce592b8ae97..a4ff6a91b33 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -2763,26 +2763,24 @@ mysql_execute_command(THD *thd) res=mysqld_show_create_db(thd,lex->name,&lex->create_info); break; } - case SQLCOM_CREATE_FUNCTION: - if (check_access(thd,INSERT_ACL,"mysql",0,1)) - break; + case SQLCOM_CREATE_FUNCTION: // UDF function + { + if (check_access(thd,INSERT_ACL,"mysql",0,1)) + break; #ifdef HAVE_DLOPEN - if (!(res = mysql_create_function(thd,&lex->udf))) - send_ok(thd); + sp_head *sph= sp_find_function(thd, &lex->udf.name); + if (sph) + { + net_printf(thd, ER_UDF_EXISTS, lex->udf.name.str); + goto error; + } + if (!(res = mysql_create_function(thd,&lex->udf))) + send_ok(thd); #else - res= -1; + res= -1; #endif - break; - case SQLCOM_DROP_FUNCTION: - if (check_access(thd,DELETE_ACL,"mysql",0,1)) break; -#ifdef HAVE_DLOPEN - if (!(res = mysql_drop_function(thd,&lex->udf.name))) - send_ok(thd); -#else - res= -1; -#endif - break; + } case SQLCOM_REVOKE: case SQLCOM_GRANT: { @@ -2951,7 +2949,7 @@ mysql_execute_command(THD *thd) res= -1; thd->options&= ~(ulong) (OPTION_BEGIN | OPTION_STATUS_NO_TRANS_UPDATE); break; - case SQLCOM_CREATE_PROCEDURE: + case SQLCOM_CREATE_PROCEDURE: // FUNCTION too (but not UDF!) if (!lex->sphead) { send_error(thd, ER_SP_NO_RECURSIVE_CREATE); @@ -2959,22 +2957,35 @@ mysql_execute_command(THD *thd) } else { + uint namelen; + char *name= lex->sphead->name(&namelen); + udf_func *udf = find_udf(name, namelen); + + if (udf) + { + net_printf(thd, ER_UDF_EXISTS, name); + goto error; + } res= lex->sphead->create(thd); - if (res != 0) + switch (res) { + case SP_OK: + send_ok(thd); + break; + case SP_WRITE_ROW_FAILED: send_error(thd, ER_SP_ALREADY_EXISTS); goto error; + default: + send_error(thd, ER_SP_STORE_FAILED); + goto error; } - send_ok(thd); } break; case SQLCOM_CALL: { - Item_string *s; sp_head *sp; - s= (Item_string*)lex->value_list.head(); - sp= sp_find_procedure(thd, s); + sp= sp_find_procedure(thd, &lex->udf.name); if (! sp) { send_error(thd, ER_SP_DOES_NOT_EXIST); @@ -3002,12 +3013,14 @@ mysql_execute_command(THD *thd) } break; case SQLCOM_ALTER_PROCEDURE: + case SQLCOM_ALTER_FUNCTION: { - Item_string *s; sp_head *sp; - s= (Item_string*)lex->value_list.head(); - sp= sp_find_procedure(thd, s); + if (lex->sql_command == SQLCOM_ALTER_PROCEDURE) + sp= sp_find_procedure(thd, &lex->udf.name); + else + sp= sp_find_function(thd, &lex->udf.name); if (! sp) { send_error(thd, ER_SP_DOES_NOT_EXIST); @@ -3022,28 +3035,41 @@ mysql_execute_command(THD *thd) } break; case SQLCOM_DROP_PROCEDURE: + case SQLCOM_DROP_FUNCTION: { - Item_string *s; - sp_head *sp; - - s = (Item_string*)lex->value_list.head(); - sp = sp_find_procedure(thd, s); - if (! sp) - { - send_error(thd, ER_SP_DOES_NOT_EXIST); - goto error; - } + if (lex->sql_command == SQLCOM_DROP_PROCEDURE) + res= sp_drop_procedure(thd, lex->udf.name.str, lex->udf.name.length); else { - String *name = s->const_string(); - - res= sp_drop_procedure(thd, name->c_ptr(), name->length()); - if (res != 0) + res= sp_drop_function(thd, lex->udf.name.str, lex->udf.name.length); +#ifdef HAVE_DLOPEN + if (res == SP_KEY_NOT_FOUND) { - send_error(thd, ER_SP_DROP_FAILED); - goto error; + udf_func *udf = find_udf(lex->udf.name.str, lex->udf.name.length); + if (udf) + { + if (check_access(thd, DELETE_ACL, "mysql", 0, 1)) + goto error; + if (!(res = mysql_drop_function(thd,&lex->udf.name))) + { + send_ok(thd); + break; + } + } } +#endif + } + switch (res) + { + case SP_OK: send_ok(thd); + break; + case SP_KEY_NOT_FOUND: + send_error(thd, ER_SP_DOES_NOT_EXIST); + goto error; + default: + send_error(thd, ER_SP_DROP_FAILED); + goto error; } } break; diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index ea3b550146b..bfb9d06c16c 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -366,9 +366,10 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize); %token FUNC_ARG1 %token FUNC_ARG2 %token FUNC_ARG3 -%token UDF_RETURNS_SYM +%token RETURN_SYM +%token RETURNS_SYM %token UDF_SONAME_SYM -%token UDF_SYM +%token FUNCTION_SYM %token UNCOMMITTED_SYM %token UNDERSCORE_CHARSET %token UNICODE_SYM @@ -902,27 +903,23 @@ create: lex->name=$4.str; lex->create_info.options=$3; } - | CREATE udf_func_type UDF_SYM IDENT + | CREATE udf_func_type FUNCTION_SYM IDENT { LEX *lex=Lex; - lex->sql_command = SQLCOM_CREATE_FUNCTION; lex->udf.name = $4; lex->udf.type= $2; } - UDF_RETURNS_SYM udf_type UDF_SONAME_SYM TEXT_STRING - { - LEX *lex=Lex; - lex->udf.returns=(Item_result) $7; - lex->udf.dl=$9.str; - } + create_function_tail + {} | CREATE PROCEDURE ident { LEX *lex= Lex; - lex->spcont = new sp_pcontext(); - lex->sphead = new sp_head(&$3, lex); + lex->spcont= new sp_pcontext(); + lex->sphead= new sp_head(&$3, lex); + lex->sphead->m_type= TYPE_ENUM_PROCEDURE; } - '(' sp_dparam_list ')' + '(' sp_pdparam_list ')' { Lex->spcont->set_params(); } @@ -932,15 +929,43 @@ create: } ; +create_function_tail: + RETURNS_SYM udf_type UDF_SONAME_SYM TEXT_STRING + { + LEX *lex=Lex; + lex->sql_command = SQLCOM_CREATE_FUNCTION; + lex->udf.returns=(Item_result) $2; + lex->udf.dl=$4.str; + } + | '(' + { + LEX *lex= Lex; + + lex->sql_command = SQLCOM_CREATE_PROCEDURE; + lex->spcont= new sp_pcontext(); + lex->sphead= new sp_head(&lex->udf.name, lex); + lex->sphead->m_type= TYPE_ENUM_FUNCTION; + } + sp_fdparam_list ')' + { + Lex->spcont->set_params(); + } + RETURNS_SYM type + { + Lex->sphead->m_returns= (enum enum_field_types)$7; + } + sp_proc_stmt + {} + ; + call: CALL_SYM ident { LEX *lex = Lex; lex->sql_command= SQLCOM_CALL; + lex->udf.name= $2; lex->value_list.empty(); - lex->value_list.push_back( - (Item*)new Item_string($2.str, $2.length, default_charset_info)); } '(' sp_cparam_list ')' {} ; @@ -962,18 +987,36 @@ sp_cparams: } ; -/* SP parameter declaration list */ -sp_dparam_list: +/* Stored FUNCTION parameter declaration list */ +sp_fdparam_list: /* Empty */ - | sp_dparams + | sp_fdparams ; -sp_dparams: - sp_dparams ',' sp_dparam - | sp_dparam +sp_fdparams: + sp_fdparams ',' sp_fdparam + | sp_fdparam ; -sp_dparam: +sp_fdparam: + ident type sp_opt_locator + { + Lex->spcont->push(&$1, (enum enum_field_types)$2, sp_param_in); + } + ; + +/* Stored PROCEDURE parameter declaration list */ +sp_pdparam_list: + /* Empty */ + | sp_pdparams + ; + +sp_pdparams: + sp_pdparams ',' sp_pdparam + | sp_pdparam + ; + +sp_pdparam: sp_opt_inout ident type sp_opt_locator { Lex->spcont->push(&$2, @@ -1068,6 +1111,20 @@ sp_proc_stmt: lex->sphead->restore_lex(YYTHD); } } + | RETURN_SYM expr + { + LEX *lex= Lex; + + if (lex->sphead->m_type == TYPE_ENUM_PROCEDURE) + { + send_error(YYTHD, ER_SP_BADRETURN); + YYABORT; + } + else + { + /* QQ nothing yet */ + } + } | IF sp_if END IF {} | CASE_SYM WHEN_SYM { @@ -1886,7 +1943,7 @@ alter: lex->sql_command=SQLCOM_ALTER_DB; lex->name=$3.str; } - | ALTER PROCEDURE opt_specific ident + | ALTER PROCEDURE ident /* QQ Characteristics missing for now */ opt_restrict { @@ -1895,18 +1952,10 @@ alter: /* This is essensially an no-op right now, since we haven't put the characteristics in yet. */ lex->sql_command= SQLCOM_ALTER_PROCEDURE; - lex->value_list.empty(); - lex->value_list.push_back( - (Item*)new Item_string($4.str, $4.length, default_charset_info)); + lex->udf.name= $3; } ; -opt_specific: - /* Empty */ - | - SPECIFIC_SYM - ; - alter_list: | alter_list_item | alter_list ',' alter_list_item; @@ -3540,23 +3589,20 @@ drop: lex->drop_if_exists=$3; lex->name=$4.str; } - | DROP UDF_SYM IDENT + | DROP FUNCTION_SYM IDENT opt_restrict { LEX *lex=Lex; lex->sql_command = SQLCOM_DROP_FUNCTION; - lex->udf.name = $3; + lex->udf.name= $3; } | DROP PROCEDURE ident opt_restrict { LEX *lex=Lex; lex->sql_command = SQLCOM_DROP_PROCEDURE; - lex->value_list.empty(); - lex->value_list.push_back( - (Item*)new Item_string($3.str, $3.length, default_charset_info)); + lex->udf.name= $3; } ; - table_list: table_name | table_list ',' table_name; @@ -4583,7 +4629,7 @@ keyword: | TIMESTAMP {} | TIME_SYM {} | TYPE_SYM {} - | UDF_SYM {} + | FUNCTION_SYM {} | UNCOMMITTED_SYM {} | UNICODE_SYM {} | USE_FRM {} |