Vijay Samuel: vjsamuel
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.
URL: http://vjsamuel.wordpress.com/2011/03/23/stored-procedure-interface-for-drizzle/
_______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
