Log in

No account? Create an account

Stored Procedures or Server Side Scripting?

« previous entry | next entry »
Jun. 10th, 2009 | 11:24 am

Here is a bit of code I worked up for us a recently for Drizzle:

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 " . $_[0] . "!"
-> |
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)

Stored Procedures!?!

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:

  • Any language should be pluggable. We won't have a native language.
  • We only support re-entrant engines. This solves all of the pre-locking problems that exist currently. We haven't removed 40% of the locks in Drizzle, only to have to come up with a bunch of new ones to support engines which were never built to handle this stuff.
  • While it won't be required, we will focus first on enabling scripting languages that are not in-process. Why is that? We don't want anyone to crash the database. Informix had this problem early on and got a bad rep for it. We want to avoid this.
  • We will enable driver writers to be able to communicate in a native way. AKA if you are writing something in Java, you will be able to use a JDBC interface inside of the database. For Perl DBI, etc. I want to be able to test my SP's in any environment. The difference between running in of the database, and out of should be trivial or non-existent.

    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 :)
  • Link | Leave a comment |

    Comments {14}

    Perl stored procedures

    from: atcurtis
    date: Jun. 10th, 2009 08:23 pm (UTC)

    Hey Brian,

    Did you ever get around to looking at the stored proc work that Eric and I did for MySQL?

    I just wish I had more time to play with Drizzle.

    Reply | Thread

    Brian "Krow" Aker

    Re: Perl stored procedures

    from: krow
    date: Jun. 10th, 2009 10:01 pm (UTC)

    No, I haven't. It was too mixed in with the MySQL code for me to make heads or tails of it. The above is just a prototype I did (aka... it will get tossed).

    You would be welcome to work on a final solution.

    Reply | Parent | Thread

    Re: Perl stored procedures

    from: jzawodn
    date: Jun. 11th, 2009 11:08 pm (UTC)

    The more I think about how we might use this, I realize there's a need for ad-hoc scripting (think per-session) and global (per-servers) scripting.

    If I need to do a one-time fixup of something or crunch data in a new way, it's great if I could drop the code in and call it.

    But for longer-term stuff that we're going to say developers can expect to exist and be supported, we need a way to make those available to everyone who connects.

    It's kind of like global vs. session stuff in MySQL today.

    The way we deploy code, it'd be great if global procedures were loaded from a directory (tree?) of files. We can use our admin tools to make sure the files exist and are kept up-to-date. Of course, some sort of "reload" command would be hand for when the file(s) change.

    But for session-specific, I'd imagine the user can just create them on the fly, much like a temporary table.

    As for syntax... Hmm.

    Reply | Parent | Thread