Vijay Samuel's Blog

Archive for March 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. πŸ™‚


Its almost a year since I first became a part of the Drizzle family and its been an amazing experience so far. I first came to Drizzle with an aspiration to become a Google Summer of Code intern. I did realise that dream under the guidance of my mentor Brian Aker. Google Summer of Code was awesome. I got to work onΒ  the command line options processing system alongside Brian, Monty Taylor and Jay Pipes. Summer of Code 2010 was a huge success and I did manage some good work into trunk.

Even though summer of code was over I did hang around at Drizzle and do some struct to class refactoring, a lot of bug fixes and some tamil utf-8 test cases. I feel proud to work along side such great people and learn new technologies with them as Drizzle grows. The number of friends I have made here are countless, Stewart Smith, Andrew Hutchings, David Shrewsbury, Patrick Crews, just to name a few. πŸ™‚

I recently approached Brian and Monty regarding what I could do for the coming Summer of Code and Brian was quick to respond. πŸ™‚ Stored Procedure Interface was his reply and SPI is what I am going to propose for the upcoming Summer of Code. Brian is very excited about it and so are many devs at #drizzle including myself. I will be coming up withΒ  a description regarding this project in a few days time and I ‘m open to any comments.

I almost forgot about the GA! The GA of Drizzle is coming out in a few days. I’m pretty excited to be a part of the team and I will definitely contribute more to Drizzle.. Thank you guys! I do love working at #drizzle. πŸ™‚ πŸ™‚ πŸ™‚

My Timeline

March 2011
« Dec   Oct »

I, Me and Myself

My Blog Stats

  • 7,158 hits

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

Join 4 other followers