MySQL General Purpose Stored Routines Library

more logos

MySQL General Purpose Stored Routines Library

Ver 1.0

PURPOSE

This routine library will collect general purpose MySQL 5 stored procedures and functions. What is to be considered "general purpose"? Any routine that enhances the language itself or that can be useful in an abstract database fits the bill.

Since its appearance, MySQL stored routines language has proved itself useful and powerful, and yet it lacks many features that we have become accustomed to in other languages, and we would like to see in MySQL::SP as well. Stored routines that improve the language espressiveness and usability fall in this category.

Furthermore, there are common tasks that can get accomplished by a stored routine, and are not related to a particular database. Also these routines are good candidates for this library.

Presentation article

CURRENT CATEGORIES

At the very beginning of this project, I have put some categories that got populated faster than others, and thus they became, in my view, the ones with the highest priority.

GLOBALS

An even more general purpose module among the general purpose routines. This module covers miscellaneous routines and provides support for global variables that survive across connections.

article on globals

ARRAYS AND BASIC DATA STRUCTURES

Perhaps the biggest disappointment when MySQL::SP language was disclosed was the lack of arrays. Not as a SQL type, of which frankly I can do without, as it breaks normalization, but as a language component. Without arrays, and without things like queues and stacks, I feel that my programming abilities are severely cut down.

Thus this first component, which inmplements named arrays where you access items by index, but that can also be treated like Perl hashes, where items are accessed by name. Built-in with them are some stack and queue features, so that an array is automatically a queue, if accessed with shift/unshift, or a stack, if accessed with push/pop.

Further routines to transform arrays to strings and strings to arrays make this library ready for prime time.

article on arrays

"FOR EACH" LOOPS

I don't know you, but I find myself doing quite often things like

$ for $X in `mysql -B -N -e 'show tables'` ; \
    do perl -pe "something with $X" | \ 
    mysql -e "something else with $X" ; done

i.e. I get a list out of mysql, treat the list with some sort of filter, and then feed it back to the database.

Sometimes I fire up a quick Perl script to do the whole task, but most of the times the whole thing would be just a simple matter if MySQL had a FOR loop that you can use from a regular SQL statement.

This component came out of this need. Now I have routines that accept some parameters and perform for me a loop with a counter, or using a dataset of table values, or walk through an array.

Article on for_each loops

NAMED PARAMETERS

In the process of making the "for each" routines, I came to the disappointing discovery that I had to use quite a lot of parameters if I wanted to achieve some powerful results. When you have routines that need more than three parameters, as every programmer knows, it is quite difficult to get the order right, and many mistakes can come from this situation. Thus, I wanted to implement a simple way of calling my complex routines with maned parameters, something that in Perl I do like this:

  function_name( 
    db_name    => 'world', 
    table_name => 'City', 
    expression => 'where CountryCode = "ITA"'
  );

This way, if I misplace one of the parameters, it does not matter, because they are retrieved by name. My routine does almost this ("almost", because lack of language power prevents an exact implementation). It lets you pass an array of named parameters (key + value) and a hidden mechanism will sort out the parameters and pass them to the receiving routine in the correct order.

The corresponding MySQL code would be:

    select array_set('mypar', 'db_name', 'world'), 
           array_set('mypar', 'table_name', 'City'), 
           array_set('mypar', 'expression', 'where CountryCode = "ITA"')\G
    call simple_sp('function_name', 'mypar');

As a bonus, I now have an array of parameters that I can reuse when I need them, perhaps after changing one or two parameter values.

Article on syntax helpers and named parameters

SYNTAX HELPERS

As I said, my routines can grow their parameters quite fast, and remembering all of them can be difficult even for their own author. Here comes another component that registers all the routines syntax, and has a way of showing you a quick excerpt of their usage.

This helper is also a key component of the routines that deal with named parameters.

In case you were wondering, the reference guide you can see in the CVS repository was created by just these two calls:

    call for_each_table_value_simple(
            database(),
            '_routine_syntax',
            'routine_name',
            'routine_type="function"', 
            'select fsyntax("$I1")')\G
    call for_each_table_value_simple(
            database(),
            '_routine_syntax',
            'routine_name',
            'routine_type="procedure"', 
            'select psyntax("$I1")')\G

TESTING

Test units are now available for each module, and support for making your own testing is provided through some routines that make the process of testing quite easy.

article on testing

LEGAL NOTICE

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA

Categories

Related links

more logos