News from The MySQL UDF Repository

The MySQL UDF Repository is moving forward! Although it was only recently launched we've been contacted by quite a few interested parties already.

For example, the MySQL Repository has been mentioned by Ronald Bradford in this week's Logbuffer, the "Carnival of the Vanities" hosted by the Pythian Group. Thanks Ronald!

To facilitate the community process, we've set up a Google Group. The actual repository will remain located where it is now, but if you want to keep track of what is going on, please contact us to become a member of the group.

Becoming a member does not oblige you to anything, but please do take a moment to read about the intended usage of the group. You might also want to read a little bit more about what the MySQL UDF Repository tries to be. Here you will find a few ideas on how to achieve some of the goals of the MySQL UDF repository. All these goals are crucial to make the mission of the MySQL UDF Repository succeed.

MySQL UDF Repository Welcomes Arnold Daniels

We are glad and proud to welcome Arnold Daniels as a member of the MySQL UDF Repository Google Group!

Arnold is part of Javeline and also author of libmyxql. Although him joining is good news in itself, it gets even better: in his blog Arnold has announced that he will move libmyxql to the repository!!

Moving libmyxql over to the repository is a logical step for a few reasons. There has been a proposal for an XML library on the roadmap. If you have seen Arnold's library it is immediately clear that it offers a few of the most important requirements to be added to the repository:

• It is a coherent collection of functions targeted at a set of similar tasks


• It is already reasonably documented


• The documentation includes examples


• The functions have been given short yet intuitive names, and are properly prefixed to avoid name clashes

Arnold confided to me that in spite of the apparent quality, the library did not receive as much attention as he'd anticipated, and I hope that the move to the repository will remedy this.

That said, a few changes will be made to the library as it is now:

• The code will be modified to compile on windows, linux and Mac OS/X


• The name of the source file and binary will be changed to lib_mysqludf_xql for the sake of consistency within the repository


• The mapping from relational data to XML will be enormously simplified, making it much easier for the user to call the functions

On order to grasp how it is possible to improve the usability of this already good library, take a look at the following signature of the xql_element function from lib_mysqludf_xql:

xql_element(
    tagName, content
,   att_name1, att_value2,..., att_nameN, att_valueN
)

So, we can pass the tagname of the element first, then optionally, the contents of the xml element, and then optionally a list of name/value pairs to specify a set of attributes for the XML element. When applied to the film table in the sakila database, it could look like this:

mysql> select xql_element(
    ->            'film'
    ->        ,   title
    ->        ,   'film_id'
    ->        ,   film_id
    ->        ,   'release_year'
    ->        ,   release_year
    ->        )
    -> from   film
    -> limit  1

And the result would be something like this:

<film id="1" release_year="2006">ACADEMY DINOSAUR</film>

This is all fine, but we think there is a better way.

Generating XML from relational data is a matter of translation of one data model to another data model. As it happens, data models usully bear a great deal of similarity - a film row becomes a film tag, a film_id column becomes a film_id attribute and so on and so forth. It is likely that in most cases, one would like to preserve the metadata across models. As it happens, the MySQL UDF interface for argument processing contains a very useful but undocumented feature which allows the previous result to be generated with a call like this:

mysql> select xql_element(
    ->            title as film
    ->        ,   film_id
    ->        ,   release_year
    ->        )
    -> from   film
    -> limit  1

So what is actually happening here? Well, for film_id and release_year we can simply re-use the expression text of the udf argument as attribute name. In our example, we want the value of the title column inside our film tag, so in that case we need to provide an alias so that it reads title as film.

The ability to use the expression text in the UDF is not a very well known feature. This is probably because it is undocumented. However, you can find the definition in mysql_com.h:

typedef struct st_udf_args
{
  unsigned int arg_count;  /* Number of arguments */
  enum Item_result *arg_type;  /* Pointer to item_results */
  char **args;    /* Pointer to argument */
  unsigned long *lengths;  /* Length of string arguments */
  char *maybe_null;   /* Set to 1 for all maybe_null args */
  char **attributes;                    /* Pointer to attribute name */
  unsigned long *attribute_lengths;     /* Length of attribute arguments */
} UDF_ARGS;

So, inside an UDF, args->attributes[i] holds the expression text or alias of the argument, and args->attribute_lenghts[i] holds the length of the string in args->attributes[i].

We have already successfully used this feature of the UDF interface extensively in the lib_mysqludf_json library.

If you are interested in lib_mysqludf_xml, or if you want to contribute, drop us a line and we'll make sure you get updated.

New library: lib_mysqludf_udf

We've also added a new library to the repository to make it easier to inspect the internals of the UDF interface. This library is primarily intended for UDF authors, and for debugging purposes.

Internally we have already been able to use it to answer a question like this "Is the string length returned by an UDF limited to 64kb, or not?" With lib_mysqludf_udf the answer is simple:

mysql> select length(udf_arg_value(repeat(' ',2 * 65535)));
+----------------------------------------------+
| length(udf_arg_value(repeat(' ',2 * 65535))) |
+----------------------------------------------+
|                                       131070 |
+----------------------------------------------+
1 row in set (0.02 sec)

So, no, UDF's can return more than 64k ;)

Coming Soon: Compiling for Windows using Visual Studio Express

Shortly we will be delivering Windows binaries of the repository libraries, compiled with Visual Studio Express. Please allow for some time for us to publish the details concerning the installation procedure.

For those that are interested in building the sources themselves with visual studio, we will shortly publish a tutorial on how to go about that.

Ok - well, that's it for today - hope to see you soon on our google group.

Roland Bouman