?

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}

    SP creation

    from: brianlmoon
    date: Jun. 10th, 2009 06:36 pm (UTC)
    Link

    I would prefer they are created more like UDFs. Simply point the server at a file and there are the server side scripts. Less mess.

    Reply | Thread

    Brian "Krow" Aker

    Re: SP creation

    from: krow
    date: Jun. 10th, 2009 06:51 pm (UTC)
    Link

    I'd agree. The only advantage in doing the above is that you can replicate the script... but I am not sure that I really want that either.

    The design is pretty open, and really it would be up to the scripting plugin to make that design decision.

    Reply | Parent | Thread