SYNTAX HELPERS
No matter how skilled you are as a programmer, when dealing with a rich language (or a rich library),there is little chance that you will remember the syntax of all the routines, especially if such routines need more than two parameters each.
This is even truer for MySQL stored routines, where the language does not allow niceties like routines with variable number of parameters or default values for parameters that are not included in the call list.
I solved the problem storing the syntax in a couple of tables, and having a few functions returning such syntax for each routine and some procedures to list routines by a regular expression.
Let's start with these last ones.
The most general procedure is my_routines(pattern)
Here are all routines having "array_get" in their names:
call my_routines('array_get');
+----------------+--------------------------+--------------+
| routine_schema | routine_name | routine_type |
+----------------+--------------------------+--------------+
| my_db | array_get | function |
| my_db | array_get_key_by_index | function |
| my_db | array_get_value_by_index | function |
| my_db | array_get_value_by_key | function |
+----------------+--------------------------+--------------+And here are all the routines outside the "arrays" section that use arrays. call my_routines('^[^a].*array');
+----------------+------------------------------+--------------+
| routine_schema | routine_name | routine_type |
+----------------+------------------------------+--------------+
| my_db | for_each_array_item | procedure |
| my_db | for_each_array_item_complete | procedure |
| my_db | for_each_array_item_simple | procedure |
+----------------+------------------------------+--------------+To get the list of all routines, then call my_routines('.');If I want only functions, then I use my_functions, instead of my_routines, and if I want the list of procedures, there is my_procedures.You can see the detailed syntax for a routine with fsyntax (= function syntax) and psyntax (= procedure syntax). Why two separate routines for what seems to be a unique job? Because a function and a routine can have the same name (I said some reasoning about this in the user guide for arrays). Anyway, here are some examples:
select fsyntax('array_get')\G
*************************** 1. row ***************************
fsyntax('array_get'):
** function my_db.array_get
function array_get
p_array_name varchar(30),
p_array_ndx_key varchar(30)
--
retrieves a value from array p_array_name.
If p_array_ndx_key is a number, then array_get_value_by_index is used,
otherwise it is treated as a key, and array_get_value_by_key is used.
(simulates Perl hashes or PHP arrays)
--
returns the array value
--
see also: array_get_value_by_index, array_get_value_by_key
select psyntax('array_clear')\G
*************************** 1. row ***************************
psyntax('array_clear'):
** procedure my_db.array_clear
procedure array_clear
p_array_name varchar(30),
--
removes all items from a given array
--
returns 1 on success, 0 on failure (if p_array_name does not exist)
--
see also: array_drop
--
sets variable @array_clear.The latter case is an example of what I was saying. You can have array_clear as a function and array_clear as a procedure, with separate (albeit similar) syntax pages.USING NAMED PARAMETERS
So far, it was the easy part. It may seem a long job writing all these reference pages, but it is not hard. The difficult part is putting the command together, making sure that you are using the right number of parameters in the correct order.This is something that makes me love Perl, for instance, where I can write routines that accept named parameters. For example, the for_each_counter routine could be called in Perl as
for_each_counter(Notice that the order of parameters does not matter, since they are recognized by name, rather than by position. My main goal was to emulate this calling syntax as much as I could, and I came up with simple_sp(routine_to_call, array_parameters). With this, I need to enter the parameters into an array, and then call the routine through simple_sp.
counter_start => 1,
counter_delta => 1,
counter_end => 10,
sql_command => 'SELECT $N * $N ');
select array_set('test', 'sql_command', 'SELECT $N * $N '),
array_set('test', 'counter_start', 1),
array_set('test', 'counter_end', 10),
array_set('test', 'counter_delta' 1)\G
call simple_sp('for_each_counter', 'test');The result is very much like the one I was looking for. I can set the parameters, in the order that I remember them, and simple_sp will put them in the right order. Stop for a moment, now. I know you are asking "how does it do that?"There was an additional part in the syntax stuff that I haven't mentioned yet. So far, we know that we have a list of all routines with their syntax. However, there is also a table containing, for each routine, a list of all parameters in the appropriate order. This list was a bit painful, because MySQL information_schema still lacks a PARAMETERS table, so I had to provide one of my own, and to avoid populating that table manually I had to use an external program (in Perl, if you must ask) that will read the SHOW CREATE {PROCEDURE|FUNCTION} statement and extract the parameters.
After that, I have a table where the parameters for each routine are stored in calling order. When I call simple_sp with my array of parameters, the routine creates another array with the parameters in the correct order, and fills its values from the corresponding keys in the first array. Then, it transforms the array into a string, the string is used to create a dynamic query, which is executed and everybody is happy.
Even easier named parameters
But I kept thinking about this problem, until I came up with the idea of exploiting the DBMS a bit more, and making it able to parse a list of named parameters similar to the one that Perl accepts. When I created array_from_pair_list, I knew that the solution was there:call simple_spl(This simple_spl (=simple stored procedure list) actually works! The only drawback is that I need to set the default list separator to a semicolon (";") instead of a comma, because commas are quite common inside a SQL statement. In case you feel that the default list separator and pair separator are clashing with something in your statements, you can set them with the user variables @list_separator (default ";") and @pair_separator (default "=>").
'for_each_counter';
'counter_start => 1;
counter_delta => 1;
counter_end => 10;
sql_command => SELECT $N * $N ');
Parting thought for the MySQL developers: wouldn't be good if this feature were included in MySQL language itself?
No comments:
Post a Comment