Book: "Pentaho Kettle Solutions", Matt Casters, Roland Bouman, & Jos van Dongen, Wiley 2010 Book: "Pentaho Solutions", Roland Bouman & Jos van Dongen, Wiley 2009

Tuesday, June 05, 2007

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

8 comments:

lnickers said...

Hello. I'm trying to compile the latest mysqludf_xql (lib_mysqludf_xql-0.9.6)

under WinXP Visual Studio 2005 and while linkingI'm getting:

Linking...
Creating library C:\wamp\www\lib_mysqludf_xql\lib_mysqludf_xql\Release\lib_mysqludf_xql.lib and object C:\wamp\www\lib_mysqludf_xql\lib_mysqludf_xql\Release\lib_mysqludf_xql.exp
lib_mysqludf_xql.obj : error LNK2001: unresolved external symbol _xmlTextWriterWriteCDATA
lib_mysqludf_xql.obj : error LNK2001: unresolved external symbol _xmlTextWriterWriteString
lib_mysqludf_xql.obj : error LNK2001: unresolved external symbol _xmlFreeTextWriter
lib_mysqludf_xql.obj : error LNK2001: unresolved external symbol _xmlTextWriterStartDocument
lib_mysqludf_xql.obj : error LNK2001: unresolved external symbol _xmlTextWriterWritePI
lib_mysqludf_xql.obj : error LNK2001: unresolved external symbol _xmlNewTextWriterMemory
lib_mysqludf_xql.obj : error LNK2001: unresolved external symbol _xmlTextWriterWriteAttribute
lib_mysqludf_xql.obj : error LNK2001: unresolved external symbol _xmlBufferCreate
lib_mysqludf_xql.obj : error LNK2001: unresolved external symbol _xmlBufferFree
lib_mysqludf_xql.obj : error LNK2001: unresolved external symbol _xmlTextWriterWriteRaw
lib_mysqludf_xql.obj : error LNK2001: unresolved external symbol _xmlTextWriterFlush
lib_mysqludf_xql.obj : error LNK2001: unresolved external symbol _xmlTextWriterWriteDTD
lib_mysqludf_xql.obj : error LNK2001: unresolved external symbol _xmlTextWriterEndElement
lib_mysqludf_xql.obj : error LNK2001: unresolved external symbol _xmlEncodeSpecialChars
lib_mysqludf_xql.obj : error LNK2001: unresolved external symbol _xmlTextWriterEndDocument
lib_mysqludf_xql.obj : error LNK2001: unresolved external symbol _xmlTextWriterWriteComment
lib_mysqludf_xql.obj : error LNK2001: unresolved external symbol _xmlTextWriterStartElement
lib_mysqludf_xql.obj : error LNK2001: unresolved external symbol _xmlBufferEmpty
lib_mysqludf_xql.obj : error LNK2001: unresolved external symbol _xmlTextWriterWriteElement
C:\wamp\www\lib_mysqludf_xql\lib_mysqludf_xql\Release\lib_mysqludf_xql.dll : fatal error LNK1120: 19 unresolved externals

when linking.

Am I doing somthing wrong?

Please email me if possible

Larry
lnickers@gmail.com

Roland Bouman said...

Hi!

I have no idea - sorry.

But, maybe this will get you started:

http://groups.google.com/group/mysql-udf-repository/browse_thread/thread/618ed5c45dff07d2


This thread includes a CMake script that compiles lib_mysqludf_xql for windows.

I hope that helps ;)

Abhishek Soni said...

Hello am getting this problem when I make latest lib_mysqludf_xql-0.9.6 on linux. The error is that its not able to find mysql.h

with following errors :

lib_mysqludf_xql.cc:102: error: ‘my_bool’ does not name a type
lib_mysqludf_xql.cc:103: error: variable or field ‘xql_comment_deinit’
declared void
lib_mysqludf_xql.cc:103: error: ‘UDF_INIT’ was not declared in this
scope
lib_mysqludf_xql.cc:103: error: ‘initid’ was not declared in this
scope
lib_mysqludf_xql.cc:104: error: ‘UDF_INIT’ was not declared in this
scope
lib_mysqludf_xql.cc:104: error: ‘initid’ was not declared in this
scope
lib_mysqludf_xql.cc:104: error: ‘UDF_ARGS’ was not declared in this
scope
lib_mysqludf_xql.cc:104: error: ‘args’ was not declared in this scope
lib_mysqludf_xql.cc:104: error: expected primary-expression before
‘char’
lib_mysqludf_xql.cc:104: error: expected primary-expression before
‘unsigned’
lib_mysqludf_xql.cc:104: error: expected primary-expression before
‘char’
lib_mysqludf_xql.cc:104: error: expected primary-expression before
‘char’
lib_mysqludf_xql.cc:104: error: initializer expression list treated as
compound expression
lib_mysqludf_xql.cc:106: error: ‘my_bool’ does not name a type
lib_mysqludf_xql.cc:107: error: variable or field ‘xql_cdata_deinit’
declared void
lib_mysqludf_xql.cc:107: error: ‘UDF_INIT’ was not declared in this
scope
lib_mysqludf_xql.cc:107: error: ‘initid’ was not declared in this
scope
lib_mysqludf_xql.cc:108: error: ‘UDF_INIT’ was not declared in this
scope
lib_mysqludf_xql.cc:108: error: ‘initid’ was not declared in this
scope
lib_mysqludf_xql.cc:108: error: ‘UDF_ARGS’ was not declared in this
scope
lib_mysqludf_xql.cc:108: error: ‘args’ was not declared in this scope
lib_mysqludf_xql.cc:108: error: expected primary-expression before
‘char’
lib_mysqludf_xql.cc:108: error: expected primary-expression before
‘unsigned’
lib_mysqludf_xql.cc:108: error: expected primary-expression before
‘char’
lib_mysqludf_xql.cc:108: error: expected primary-expression before
‘char’
lib_mysqludf_xql.cc:108: error: initializer expression list treated as
compound expression
lib_mysqludf_xql.cc:110: error: ‘my_bool’ does not name a type
lib_mysqludf_xql.cc:111: error: variable or field ‘xql_encode_deinit’
declared void
lib_mysqludf_xql.cc:111: error: ‘UDF_INIT’ was not declared in this
scope
lib_mysqludf_xql.cc:111: error: ‘initid’ was not declared in this
scope
lib_mysqludf_xql.cc:112: error: ‘UDF_INIT’ was not declared in this
scope
lib_mysqludf_xql.cc:112: error: ‘initid’ was not declared in this
scope
lib_mysqludf_xql.cc:112: error: ‘UDF_ARGS’ was not declared in this
scope
lib_mysqludf_xql.cc:112: error: ‘args’ was not declared in this scope
lib_mysqludf_xql.cc:112: error: expected primary-expression before
‘char’
lib_mysqludf_xql.cc:112: error: expected primary-expression before
‘unsigned’
lib_mysqludf_xql.cc:112: error: expected primary-expression before
‘char’
lib_mysqludf_xql.cc:112: error: expected primary-expression before
‘char’
lib_mysqludf_xql.cc:112: error: initializer expression list treated as
compound expression
lib_mysqludf_xql.cc:324: error: ‘my_bool’ does not name a type
lib_mysqludf_xql.cc:329: error: variable or field
‘lib_mysqludf_xql_info_deinit’ declared void
lib_mysqludf_xql.cc:329: error: redefinition of ‘int
lib_mysqludf_xql_info_deinit’
lib_mysqludf_xql.cc:62: error: ‘int lib_mysqludf_xql_info_deinit’
previously defined here
lib_mysqludf_xql.cc:329: error: ‘UDF_INIT’ was not declared in this
scope
lib_mysqludf_xql.cc:329: error: ‘initid’ was not declared in this
scope
lib_mysqludf_xql.cc:333: error: redefinition of ‘char*
lib_mysqludf_xql_info’
lib_mysqludf_xql.cc:63: error: ‘char* lib_mysqludf_xql_info’
previously defined here
lib_mysqludf_xql.cc:333: error: ‘UDF_INIT’ was not declared in this
scope
lib_mysqludf_xql.cc:333: error: ‘initid’ was not declared in this
scope
lib_mysqludf_xql.cc:333: error: ‘UDF_ARGS’ was not declared in this
scope
lib_mysqludf_xql.cc:333: error: ‘args’ was not declared in this scope
lib_mysqludf_xql.cc:333: error: expected primary-expression before
‘char’
lib_mysqludf_xql.cc:333: error: expected primary-expression before
‘unsigned’
lib_mysqludf_xql.cc:333: error: expected primary-expression before
‘char’
lib_mysqludf_xql.cc:333: error: expected primary-expression before
‘char’
lib_mysqludf_xql.cc:345: error: ‘my_bool’ does not name a type
lib_mysqludf_xql.cc:356: error: variable or field ‘xql_test_deinit’
declared void
lib_mysqludf_xql.cc:356: error: redefinition of ‘int xql_test_deinit’
lib_mysqludf_xql.cc:66: error: ‘int xql_test_deinit’ previously
defined here
lib_mysqludf_xql.cc:356: error: ‘UDF_INIT’ was not declared in this
scope
lib_mysqludf_xql.cc:356: error: ‘initid’ was not declared in this
scope
lib_mysqludf_xql.cc:361: error: redefinition of ‘char* xql_test’
lib_mysqludf_xql.cc:67: error: ‘char* xql_test’ previously defined
here
lib_mysqludf_xql.cc:361: error: ‘UDF_INIT’ was not declared in this
scope
lib_mysqludf_xql.cc:361: error: ‘initid’ was not declared in this
scope
lib_mysqludf_xql.cc:361: error: ‘UDF_ARGS’ was not declared in this
scope
lib_mysqludf_xql.cc:361: error: ‘args’ was not declared in this scope
lib_mysqludf_xql.cc:361: error: expected primary-expression before
‘char’
lib_mysqludf_xql.cc:361: error: expected primary-expression before
‘unsigned’
lib_mysqludf_xql.cc:361: error: expected primary-expression before
‘char’
lib_mysqludf_xql.cc:361: error: expected primary-expression before
‘char’
lib_mysqludf_xql.cc:385: error: ‘my_bool’ does not name a type
lib_mysqludf_xql.cc:403: error: variable or field
‘xql_textdecl_deinit’ declared void
lib_mysqludf_xql.cc:403: error: redefinition of ‘int
xql_textdecl_deinit’
lib_mysqludf_xql.cc:70: error: ‘int xql_textdecl_deinit’ previously
defined here
lib_mysqludf_xql.cc:403: error: ‘UDF_INIT’ was not declared in this
scope
lib_mysqludf_xql.cc:403: error: ‘initid’ was not declared in this
scope
lib_mysqludf_xql.cc:408: error: redefinition of ‘char* xql_textdecl’
lib_mysqludf_xql.cc:71: error: ‘char* xql_textdecl’ previously defined
here
lib_mysqludf_xql.cc:408: error: ‘UDF_INIT’ was not declared in this
scope
lib_mysqludf_xql.cc:408: error: ‘initid’ was not declared in this
scope
lib_mysqludf_xql.cc:408: error: ‘UDF_ARGS’ was not declared in this
scope
lib_mysqludf_xql.cc:408: error: ‘args’ was not declared in this scope
lib_mysqludf_xql.cc:408: error: expected primary-expression before
‘char’
lib_mysqludf_xql.cc:408: error: expected primary-expression before
‘unsigned’
lib_mysqludf_xql.cc:408: error: expected primary-expression before
‘char’
lib_mysqludf_xql.cc:408: error: expected primary-expression before
‘char’
lib_mysqludf_xql.cc:442: error: ‘my_bool’ does not name a type
lib_mysqludf_xql.cc:457: error: variable or field ‘xql_pi_deinit’
declared void
lib_mysqludf_xql.cc:457: error: redefinition of ‘int xql_pi_deinit’
lib_mysqludf_xql.cc:74: error: ‘int xql_pi_deinit’ previously defined
here
lib_mysqludf_xql.cc:457: error: ‘UDF_INIT’ was not declared in this
scope
lib_mysqludf_xql.cc:457: error: ‘initid’ was not declared in this
scope
lib_mysqludf_xql.cc:462: error: redefinition of ‘char* xql_pi’
lib_mysqludf_xql.cc:75: error: ‘char* xql_pi’ previously defined here
lib_mysqludf_xql.cc:462: error: ‘UDF_INIT’ was not declared in this
scope
lib_mysqludf_xql.cc:462: error: ‘initid’ was not declared in this
scope
lib_mysqludf_xql.cc:462: error: ‘UDF_ARGS’ was not declared in this
scope
lib_mysqludf_xql.cc:462: error: ‘args’ was not declared in this scope
lib_mysqludf_xql.cc:462: error: expected primary-expression before
‘char’
lib_mysqludf_xql.cc:462: error: expected primary-expression before
‘unsigned’
lib_mysqludf_xql.cc:462: error: expected primary-expression before
‘char’
lib_mysqludf_xql.cc:462: error: expected primary-expression before
‘char’
lib_mysqludf_xql.cc:492: error: ‘my_bool’ does not name a type
lib_mysqludf_xql.cc:511: error: variable or field ‘xql_dtd_deinit’
declared void
lib_mysqludf_xql.cc:511: error: redefinition of ‘int xql_dtd_deinit’
lib_mysqludf_xql.cc:78: error: ‘int xql_dtd_deinit’ previously defined
here
lib_mysqludf_xql.cc:511: error: ‘UDF_INIT’ was not declared in this
scope
lib_mysqludf_xql.cc:511: error: ‘initid’ was not declared in this
scope
lib_mysqludf_xql.cc:516: error: redefinition of ‘char* xql_dtd’
lib_mysqludf_xql.cc:79: error: ‘char* xql_dtd’ previously defined here
lib_mysqludf_xql.cc:516: error: ‘UDF_INIT’ was not declared in this
scope
lib_mysqludf_xql.cc:516: error: ‘initid’ was not declared in this
scope
lib_mysqludf_xql.cc:516: error: ‘UDF_ARGS’ was not declared in this
scope
lib_mysqludf_xql.cc:516: error: ‘args’ was not declared in this scope
lib_mysqludf_xql.cc:516: error: expected primary-expression before
‘char’
lib_mysqludf_xql.cc:516: error: expected primary-expression before
‘unsigned’
lib_mysqludf_xql.cc:516: error: expected primary-expression before
‘char’
lib_mysqludf_xql.cc:516: error: expected primary-expression before
‘char’
lib_mysqludf_xql.cc:550: error: ‘my_bool’ does not name a type
lib_mysqludf_xql.cc:586: error: variable or field ‘xql_element_deinit’
declared void
lib_mysqludf_xql.cc:586: error: redefinition of ‘int
xql_element_deinit’
lib_mysqludf_xql.cc:83: error: ‘int xql_element_deinit’ previously
defined here
lib_mysqludf_xql.cc:586: error: ‘UDF_INIT’ was not declared in this
scope
lib_mysqludf_xql.cc:586: error: ‘initid’ was not declared in this
scope
lib_mysqludf_xql.cc:591: error: redefinition of ‘char* xql_element’
lib_mysqludf_xql.cc:84: error: ‘char* xql_element’ previously defined
here
lib_mysqludf_xql.cc:591: error: ‘UDF_INIT’ was not declared in this
scope
lib_mysqludf_xql.cc:591: error: ‘initid’ was not declared in this
scope
lib_mysqludf_xql.cc:591: error: ‘UDF_ARGS’ was not declared in this
scope
lib_mysqludf_xql.cc:591: error: ‘args’ was not declared in this scope
lib_mysqludf_xql.cc:591: error: expected primary-expression before
‘char’
lib_mysqludf_xql.cc:591: error: expected primary-expression before
‘unsigned’
lib_mysqludf_xql.cc:591: error: expected primary-expression before
‘char’
lib_mysqludf_xql.cc:591: error: expected primary-expression before
‘char’


Any solution will be appreciated.

Abhishek Soni
(abhishek.soni@rancoretech.com)
Rancore Technologies
Gurgaon,India

Roland Bouman said...

Please file bugs at

http://bugs.mysqludf.com/

Pritam said...

hi I am getting error

Incorrect parameters in the call to stored function 'xql_element'

what is cause

Roland Bouman said...

Pritam, do me a favor and put yourself in my shoes for a second. Suppose you received a message like you just sent me, would you have enough information to provide a remotely sensible answer?

Thanks for understanding.

Pritam said...

Hi Roland
Thanks for reply,

We have new server set up with Linux and Mysql.
We have installed lib_mysqludf_xql there. In old server strored procedure which use mysqludf working fine , the code is

SET @qry = concat('select xql_agg(''manufacturers'' as `xql:root`, xql_element('
,' ''manufacturer'' as `xql:child`, manufacturername, manufacturerid as id, pcount)) from'
,' (select pm.manufacturerid, pm.manufacturername, count(*) as pcount from ', @tprod
,' pm LEFT JOIN ',@tprice, ' pr ON pm.productid = pr.productid where 1=1', @condn
, ' group by pm.manufacturerid, pm.manufacturername)as t INTO @mfrlist');

PREPARE qry from @qry; EXECUTE qry; DEALLOCATE PREPARE qry;

In old server mysql location is in “var\lib” but on new server it is in “data02\mysql”.

Also we face proplem while installing lib_mysqludf_xql but finally it got installed. I am new to Linux and Mysql environment, error “Incorrect parameters in the call to stored function 'xql_element'” it is showing even lib_mysqludf_xql is not installed even after lib_mysqludf_xql is installed same error is there.

Roland Bouman said...

Pritam, I looked for a bit, but can't seem to find an obvious problem. Perhaps you can file a bug at mysqludf.org