Vijay Samuel's Blog

Stored Procedure Interface for Drizzle

Posted on: March 23, 2011

I ‘ve been doing some reading on Stored Procedures and how they are being defined and executed. These are some of the points which I think should be covered in our Stored Procedure Interface and some of my suggestions. I’m open to suggestions and criticism. According to what we had discussed in the channel we need make the stored procedure interface pluggable. So, a part of the interface will reside within drizzled and the client part of the interface will reside within the plugin itself.

I personally feel we could work on this interface on a series of 5 to 6 iterations.

1) Write grammar for our stored procedures, a lexical analyser and some parser code using flex and bison. I think we could abide to the SQL standards as much as possible from the earlier stages so that we don’t need to refactor much later on. After we write the grammar we need to test thoroughly!!! The earlier we find bugs the better.

2) Update sql_lex and sql_yacc so that the new keywords STORED and PROCEDURE are understood by our SQL grammar. Update the client code so
that we are able to use the STORED and PROCEDURE keywords. Update bison code to CREATE and DROP Stored Procedures. Use EXECUTE_SYM to execute the stored procedures.

3) We need to store our stored procedures on tables so we will have to write protobuffers for the new fragment of code that is going to enable us to store the stored procedures on the tables.

Now, after the third pass we could merge the code into trunk and _technically_ we should be able to run stored procedures that have only SQL statements. Once we get this working we should be able add the rest of the features with patches.

4) Determine a convention for denoting variables. SQL Server uses @ prefixed to names to denote that the given name is a variable. Enable stored procedures to accept input parameters. We will be needing to re write protobuffers because we need to use these variables in our tables and give special meaning to them in the future.( i.e if they are IN, OUT or INOUT). The interface will not support IN, OUT and INOUT in this pass though.

5) Add support for IN, OUT and INOUT. We need to think of a good way to prevent modification of IN variables. I do not know how to make a table
entry readonly. We also need to be able to return values in the case of OUT and INOUT variables. We could have a column that denotes if a variable is IN OUT or INOUT and based on the entry give write permissions on that variable. Just a suggestion.

6) Add SET to the stored procedures grammar. This will enable us to use local variables. The protobuffers need to be re written so that the local variables can be stored in out tables.

I need to do alot of reading on google protobuffers and brush up on flex and bison. The first three iterations are hardest according to me. I hope I made some sense in these notes.

Please do comment on any mistakes that I ‘ve made so that I could work on them. Better ways on approaching this problem are also welcome. 🙂


11 Responses to "Stored Procedure Interface for Drizzle"


really nice to see you’re looking into this 🙂

Just a few comments:

“update sql_lex and sql_yacc so that the new keywords STORED and PROCEDURE are understood by our SQL grammar.”

I don’t think STORED is a standard keyword.

“Determine a convention for denoting variables. SQL Server uses @ prefixed to names to denote that the given name is a variable.”

In standard SQL, there is no “special” convention. The requirement for an @ like in SQL server is non-standard. In standard SQL, a variable name is simply an identifier, and it can be regular or quoted, just like identifiers for columns etc.

(If you like, I can send you the 2003 version of the standard, at least, what I could download from the web – just send me a email so I know where to send it to)

Hi Roland,
My bad, STORED is not a standard key word. Only PROCEDURE is. Thanks for the heads up on the naming convention of variables.


I too agree with the comment by Roland Bouman. i never saw “STORED” as keyword and keeping @ in front of variable also not good (it is T-SQL way)

probably this will be wrong place to ask question. but let me ask this.
is this proposed stored procedure interface will work as interface for views aswell?. i hope it should.
what you think about it?

Hi Jobin,
Will make a note of all these suggestions and I’m not very sure about your query regarding views. You have to talk to krow and monty regarding that.

[…] View full post on Planet Drizzle […]

It may be easier to start off not writing a whole new language but instead take something like one of the javascript libraries and use it. This would provide an existing language to start testing the plug in interface with.

Also, I think stored procs in JS would really be something nice for the modern web.

Hi stewart,
Brian wishes that we stick to the ANSI SQL language for now.


Yes, as Stewart pointed out, inventing a new language for stored procedures will gain you (and users) nothing, except the requirement to maintain it indefinitely, whereas using a readily available general-purpose language and runtime will free you to work on the hard database problems, and offload various hard problems such as supporting a user community for a custom language, to other people.

Hi Mikael,
Brian wishes that we stick to the ANSI SQL language for now.


Oh my goodness! Impressive article dude! Many thanks,
However I am having problems with your RSS.
I don’t understand why I cannot subscribe to it.

Is there anybody having similar RSS issues? Anybody
who knows the answer will you kindly respond? Thanks!!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

My Timeline

March 2011
« Dec   Oct »

I, Me and Myself

My Blog Stats

  • 6,969 hits

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 4 other followers

%d bloggers like this: