Archives for August 2007

Last week, I released some code that would enable MySQL to evaluate Tcl scripts as a stored function, which I called tcl-mysql-udf. My friend Steve asked if I could prepare a Win32 DLL binary for him, so I worked on that tonight and am releasing version 0.2, along with the DLL:

• Source: tcl-mysql-udf-0.2.tar.gz (328K)
• Win32 DLL: tcl-mysql-udf-0.2.dll.zip (144K)

In order to play with this, you’ll need the following prerequisites installed:

• MySQL (I’m testing on 5.1.21-beta)
• Tcl (I’m testing on 8.4.15.0)

Inside the tcl-mysql-udf-0.2-dll.zip will be the file tcl-mysql-udf.dll. On MySQL 5.0.x, it expects it to reside in the Program Files\MySQL\MySQL Server 5.0\bin directory, so copy it there. On MySQL 5.1.x, however, it expects it to be in the Program Files\MySQL\MySQL Server 5.1\lib directory. Pay close attention to what version of MySQL you’re using and the correct directory to copy the DLL into.

Once you’ve got everything installed and copied to the right locations so far, connect to your MySQL database with your favorite MySQL client, and issue the following command (you only type what’s in bold):

mysql> CREATE FUNCTION TCL RETURNS STRING SONAME 'tcl-mysql-udf.dll';
Query OK, 0 rows affected (0.10 sec)

We can check what version of Tcl we’ve loaded this way:

mysql> SELECT TCL('info patchlevel') AS script;
+--------+
| script |
+--------+
| 8.4.15 |
+--------+
1 row in set (0.04 sec)

Here’s a goofy example of storing Tcl scripts in the database and having MySQL evaluate them:

mysql> CREATE TABLE code (
n INT NOT NULL AUTO_INCREMENT,
script TEXT NOT NULL,
PRIMARY KEY pk_code (n)
) ENGINE=MyISAM;
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO code (script) VALUES
('set x 123'),
('expr {$x + 432}'),
('clock format [clock seconds]'),
('incr x [clock seconds]'),
('expr {$x * rand()}');
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

So, we now have a table with five rows in it, each row containing a Tcl script. We can have MySQL evaluate those Tcl scripts like this:

mysql> SELECT n, TCL(script)
FROM code
ORDER BY n;

+---+---------------------------------------------------+
| n | TCL(script)                                       |
+---+---------------------------------------------------+
| 1 | 123                                               |
| 2 | 555                                               |
| 3 | Fri Aug 31 12:41:25 AM Eastern Daylight Time 2007 |
| 4 | 1188535408                                        |
| 5 | 6935485.39171                                     |
+---+---------------------------------------------------+
5 rows in set (0.05 sec)

So what, right? How about fetching HTML documents via HTTP, right from within MySQL?

mysql> CREATE TABLE urls (
n INT NOT NULL AUTO_INCREMENT,
url VARCHAR(255) NOT NULL,
PRIMARY KEY pk_urls (n)
) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO urls (url) VALUES
('http://dossy.org/'),
('http://aolserver.com/'),
('http://njgeeks.org/');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT n, url, TCL(
'package require http;'
'set url [lindex $args 0];'
'set token [http::geturl $url];'
'set data [http::data $token];'
'http::cleanup $token;'
'regexp -all -inline {<title>.*?</title>} $data',
url) AS script
FROM urls
ORDER BY n \G

*************************** 1. row ***************************
     n: 1
   url: http://dossy.org/
script: {<title>Dossy's Blog</title>}
*************************** 2. row ***************************
     n: 2
   url: http://aolserver.com/
script: <title>AOLserver</title>
*************************** 3. row ***************************
     n: 3
   url: http://njgeeks.org/
script: {<title>NJ Geeks | - New Jersey's IT Community</title>}
3 rows in set (1.54 sec)

This query uses the Tcl “http” package to fetch the documents specified by the url column and plucks out the <title> tag using a regular expression. The TCL() stored function takes a variable number of arguments, the first being the Tcl script to evaluate, followed by zero or more arguments that are placed into the $args Tcl variable. In this case, we pass in “url” so that on each row, we execute our Tcl script with the value from that row.

Be aware that this stored function is a security issue: allowing database users to execute arbitrary code has obvious risks, especially since that code will be executed as the user that the MySQL server is running as.

Hopefully this is enough to get you started and might even give you an idea as to how this could be useful to you in some way. If you have any questions, just leave them in the comments below.

Tags: MySQL, Tcl, Win32, stored function

In the previous blog entry, I mentioned evaluating Tcl scripts from within MySQL using the User-Defined Function (UDF) capability already in MySQL. For example, it would allow you to do this:

CREATE FUNCTION TCL RETURNS STRING SONAME 'libtcl-mysql-udf.so';

SELECT TCL('expr {2 + 2}');

Well, after a few hours of productive coding and hacking, I want to share tcl-mysql-udf-0.1.tar.gz with you.

I’ve done very limited testing on this and on very little sleep, so I wholly expect there to be really stupid mistakes in there–but, it does “work” on my personal Debian 4.0 Linux and MySQL 5.1.20 setup with Tcl 8.4.12. If you want to play around with this, I strongly urge you to read over the README included in the tarball, as it should have all the relevant bits of information necessary to get you started.

A quick demonstration of what’s currently possible with this version:

mysql> SELECT TCL('info patchlevel') AS script;
+--------+
| script |
+--------+
| 8.4.12 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT TCL('info commands') AS script \G
*************************** 1. row ***************************
script: tell socket subst open eof pwd glob list exec pid time eval lrange fblocked lsearch gets case lappend proc break variable llength return linsert error catch clock info split array if fconfigure concat join lreplace source fcopy global switch update close
1 row in set (0.00 sec)

Currently, Tcl errors result in the function returning NULL–I haven’t figured out how to propagate the error string back up, yet.

mysql> SELECT TCL('error foo') AS script;
+--------+
| script |
+--------+
| NULL   |
+--------+
1 row in set (0.01 sec)

Also, the maximum length string that the function can return is currently hard-coded to 255 characters.

mysql> SELECT LENGTH(TCL('string repeat x 1024')) AS script;
+--------+
| script |
+--------+
|    255 |
+--------+
1 row in set (0.07 sec)

I implemented the function as accepting a variable number of arguments, the first being the Tcl script, followed by values that get appended to the $args Tcl variable–as though it were a Tcl proc call, in a sense.

mysql> SELECT TCL('concat [llength $args] $args', 'abc', 123, NOW()) AS script;
+---------------------------------+
| script                          |
+---------------------------------+
| 3 abc 123 {2007-08-25 08:50:35} |
+---------------------------------+
1 row in set (0.05 sec)

If you’re familiar with Tcl and MySQL and aren’t afraid of compiling something and fooling around with it in your dev playground, I’d love to hear what you think of this code. Can you see any use for it? What else would it need to be able to do before it would be useful to you?

Tags: MySQL, Tcl, User-Defined Function, UDF