drizzle> DELIMITER |
Note that there is no semicolon after the '|' symbol, which we will use as the delimiter for our purposes. You have to choose a delimiter that does not appear in your procedure, and it can be more than one character.
drizzle> CREATE PROCEDURE perl_hello (param1 string)
-> return "Hello " . $_ . "!"
Query OK, 0 rows affected (0.05 sec)
drizzle> CALL perl_hello('Brian');
Query OK, 1 row affected (0.00 sec)
drizzle> DELIMITER ;
drizzle> SELECT @perl\G
*************************** 1. row ***************************
@perl: Hello Brian!
1 row in set (0.00 sec)
In an actual language!?!
About a week ago I was talking to a CTO for a company who is looking at adoption of Drizzle. One of things he came back with was "I don't need stored procedures, but I do need server side scripting".
Back at the very first MySQL User's Conference we had a debate over the future of stored procedures in MySQL. I and some others really wanted the first stored procedure language to be external, David really wanted it to be PHP. I didn't see the value in implementing a single language. I thought people would be more interested in writing code in whatever language they wanted. Also, I figured that an external system would allow for different groups to develop languages more rapidly.
Fast forward to when we began Drizzle. Parsers are where you spend a lot of your time. The smaller the parser the better off you are. So I went to task removing all of the signs of the SP language from Drizzle. We have been free of them now for over a year now (yes, long before we went public). Things are finally shaping up so that when we begin on Bell, our next milestone, stored procedures, or something like them, are now on our list.
Though are they stored procedures, or is this server-side scripting?
A few premises of the design:
I am a little bit torn about using the SP call/creation SQL commands in Drizzle. You won't be doing the typical SP language (well... unless someone wants to write a plugin for them!). I would also like to encourage people to think differently about what writing server side code should look like. Personally I don't feel that stored procedures are the right solution for a lot of the cases, keep your business logic in your application layer(!), but we also know that users expect to be able to be able to run code locally. Triggering/Callback mechanisms can be very useful though, and enabling them is a part of this. Doing Triggers today in C is simple, but that is not something that everyone should/would/could want to do.
Putting this in the plugin structure means no overhead to the parser or the rest of the database. Keeping them out of process means no drain or memory expansion of the Database. SMP boxes will benefit because you can confine the language VM to a particular set of processors/amount of memory.
We don't want the database to ever blow up because of bugs in the execution language!
And if you never want them? You never load the plugin in the first place.
Why Perl? I've embedded Perl for years and know how to make it work. I've only done Java once, so I will leave that to other experts.
I suspect I can find a Java person somewhere inside of Sun :)