{"id":1855,"date":"2010-03-17T18:02:34","date_gmt":"2010-03-17T08:02:34","guid":{"rendered":"http:\/\/www.flamingspork.com\/blog\/?p=1855"},"modified":"2014-10-08T09:16:07","modified_gmt":"2014-10-07T23:16:07","slug":"stored-proceduresfunctions-for-drizzle","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2010\/03\/17\/stored-proceduresfunctions-for-drizzle\/","title":{"rendered":"Stored Procedures\/Functions for Drizzle"},"content":{"rendered":"

Previously, in “Thoughts on Thoughts on Drizzle<\/a>” I theorized that one of the major reasons why we did not see lots of people jumping at stored procedures in MySQL<\/a> was that it wasn’t in their native language (for lack of a better term). We’ve seen External Language Stored Procedures<\/a> for MySQL that let you write stored procedures in some other languages…. but I felt something was missing.<\/p>\n

Firstly, I wanted a language I was really familiar with and comfortable writing complex things in.<\/p>\n

Secondly, it should be compiled so that it runs as fast as possible.<\/p>\n

Thirdly, it shouldn’t just be linking to a pre-compiled library (drizzle function plugins do that already)<\/p>\n

So… the obvious choice was C.<\/p>\n

I have a really, really, really early prototype:
\n
\ndrizzle> SELECT LIBTCC(\"int foo(char* s) { s[0]='4'; s[1]='2'; s[2]=0; return 2; }\") AS RESULT;
\n<\/code><\/p>\n

+--------+\r\n| RESULT |\r\n+--------+\r\n| 42     |\r\n+--------+\r\n1 row in set (0 sec)<\/pre>\n
or… a bit more sophisticated:
\n
\ndrizzle> SELECT LIBTCC(\"#include <string.h>\\nint foo(char* s) { strcpy(s,\\\"Hello World!\\\");; return strlen(s); }\") AS RESULT;
\n<\/code><\/p>\n
+--------------+\r\n| RESULT       |\r\n+--------------+\r\n| Hello World! |\r\n+--------------+\r\n1 row in set (0 sec)<\/pre>\n
I’m using a function as a bit of a cheat… but the string is passed to libtcc<\/a> (modified so it’s a shared library so I can load it into drizzle<\/a>), where it is compiled into native object code (in my case x86-64) and then run.<\/p>\n
With the right bits of foo… I could allow calling of all sorts of server functions…. such as those to execute SQL inside the current transaction context.<\/p>\n
There are a number of reasons why this is Pure Evil(TM):<\/p>\n