?

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

    awfief

    (no subject)

    from: awfief
    date: Jun. 10th, 2009 07:46 pm (UTC)
    Link

    2 things --

    1) Where do you specify what language your SP should be interpreted by?

    2) Your sp is named "perl_hello", and you run it by "CALL sp_ins".

    3) You SELECT @perl. when @perl never showed up in the sp, nor was it assigned at CALL time......(ie, SET @perl:=CALL sp_ins('Brian');)

    Reply | Thread

    Brian "Krow" Aker

    (no subject)

    from: krow
    date: Jun. 10th, 2009 09:46 pm (UTC)
    Link

    This was a prototype. I had hardcoded quite a bite of it to test it. Notice that there is only one returnable value in one specific variable :)

    For a CREATE PROCEDURE we would need to specify which plugin to send the code too.

    Reply | Parent | Thread

    fantastic

    from: anonymous
    date: Jun. 10th, 2009 08:01 pm (UTC)
    Link

    This is a wonderful development, and will help organizations who require a server side API to the database. This way you don't have to fix the performance and feature limitations of the MySQL stored routine implemenation, but still deliver what people like myself need: server side scripting.

    Reply | Thread

    Perl stored procedures

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

    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)
    Link

    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)
    Link

    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

    What's the difference?

    from: anonymous
    date: Jun. 12th, 2009 04:51 am (UTC)
    Link

    Brian,

    I just don't get how you're differentiating between stored procedures and server-side scripting. In what way are those different? It's two terms for the same thing.

    --Josh Berkus

    Reply | Thread

    Brian "Krow" Aker

    Re: What's the difference?

    from: krow
    date: Jun. 12th, 2009 02:26 pm (UTC)
    Link

    Here is my concern, there seems to be a common concept of "stored procedures are some SQL like language". There is an expectation there... I'd like that to be broken, and I would like to think a bit more about how to do scripting on the fly in the server.

    Obviously PG has this thought out a bit, since it already has SP's in a couple of languages. MySQL had Perl based functions in 2001, but the concept never took off.

    Reply | Parent | Thread

    Performance Penalty?

    from: anonymous
    date: Jun. 15th, 2009 05:01 pm (UTC)
    Link

    Won't keeping things off-process mean a significant performance penalty?
    From my experience sometimes people move logic from the application to the database when it's very chatty in nature of called extremely often.
    Spawning a new process (thus loading a new VM / interpreter ?) could mean a lot of overhead for a possibly very light workload.

    --
    Manuel Padilha
    http://blogs.fe.up.pt/mpadilha

    Reply | Thread

    Brian "Krow" Aker

    Re: Performance Penalty?

    from: krow
    date: Jun. 15th, 2009 05:08 pm (UTC)
    Link

    If you use a shared memory transport, then the issues around in-process pretty much go away.

    Reply | Parent | Thread

    Adding Java plugin

    from: hodgesrm
    date: Jun. 21st, 2009 03:40 pm (UTC)
    Link

    Hi Brian,

    I love Java dearly but kind of wonder whether you really want drizzle attached to a JVM. It's an awful lot of baggage. Scripting languages like Perl and Python seem a lot better, provided they run fast and are reasonably safe. Java does allow you to incorporate very powerful and fast business logic but at some point you have to wonder whether that's better outside the DBMS.

    Cheers, Robert

    Reply | Thread

    Brian "Krow" Aker

    Re: Adding Java plugin

    from: krow
    date: Jun. 22nd, 2009 01:29 am (UTC)
    Link

    I am thinking that we will just provide and interface, and leave it up to others to decide what language plugins that they want to write.

    Personally, I'd only be up for writing a perl one :)

    Reply | Parent | Thread