Stored Procedure Interface
The goal of this project is to design and develop a stored procedure interface which will enable users to write, store and run procedures on the server and make the whole thing pluggable so that users not needing this interface may opt not to use the interface. Stored procedures was stripped out during the intial fork from MySQL 6.0 but taking into account the requests from users of Drizzle it has become a need right now.
Blueprint information
- Status:
- Started
- Approver:
- None
- Priority:
- Medium
- Drafter:
- None
- Direction:
- Needs approval
- Assignee:
- Vijay Samuel
- Definition:
- New
- Series goal:
- None
- Implementation:
-
Good progress
- Milestone target:
- None
- Started by
- Vijay Samuel
- Completed by
Related branches
Related bugs
Sprints
Whiteboard
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. --------> COMPLETED
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 PROCEDURE keywords. Update bison code to CREATE and DROP Stored Procedures. Use EXECUTE to execute the stored procedures. -----> COMPLETED
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. -----> COMPLETED
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.
