Hi, I've been doing some research for routines, triggers and events. I feel that comparing to the Ajaxify job, this job may give me greater sense of achievement. But, I insist, as the ajaxification of PMA is just started, it's very essential to build a common framework on request processing and do some refactor work. So I still hope that I can take the Ajaxify work,rather than 'Support for Procedure'.
So the following is my ideas for 'Support for Procedure', please give me some suggestions.
1 main page for routines,triggers and events. I prefer to create a new tab in table view for routines,triggers and events together, create three (or four) html table for them, in each table their metadatas and some operation icons are displayed. the operation icons include "alter","delete", and for functions and procedures, "call". Besides, User can create new items and perform multi-item operation. INFORMATION_SCHEMA does not have a PARAMETERS Table until MySQL 5.5, so for the older version the parameter of routines cannot be seen in this main page.
Then I'll develop seperate mananging module for routines,triggers and events.In ajax mode, it'll display in a popup JQuery UI Dialog and as a new page otherwise. 2 stored procedures and functions User need to fill in: Definer;(select;default:current user, this should set unchangable if user do not have SUPER privilege;else it will be a list box) Routine Type:(select;Procedure or Function) Procedure or Function Name;(text-input) Parameter List;(including:select:[IN | OUT | INOUT],text-input:param_name and select:type. for function, every param is IN;the type options should include the enum type which is defined in the table. for MySQL 5.5- , we need to fetch the parameter list from SHOW CREATE PROCEDURE command) Characteristic: COMMENT;(text-input) LANGUAGE;(According to the MYSQL official doc, actually only SQL is accepted) DETERMINISTIC;(checkbox;default:Yes ) SQL SECURITY;(radio;default:Definer) other characteristics;(radio{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA},only advisory to the server) Return type;(the same as type in Parameter List.for function only) Routine_body;(textarea)
users can call a procedure or function in the main page. If a Procedure returns by OUT or INOUT params, we'll add a select statement after calling.
3 triggers CREATE Trigger requires SUPER priviledge, so non-SUPER user shouldn't access the this managing page.
User need to fill in: Definer;(select;default:current user) Trigger name;(text-input) Trigger time;(select;BEFORE or AFTER) Trigger event;(select;INSERT|UPDATE|DELETE) Trigger body;(textarea)
Users don't have to input table name because we already know which table this trigger attached to.
4 events User need to fill in: Definer;(select;default:current user, this should set unchangable if user do not have SUPER privilege;else it will be a combo box) Event_name;(text-input) Schedule: At timestamp(+ interval)(text-input + select) or Every interval Starts(...) Ends(...)(text-input + select); ON COMPLETION [OR NOT] PRESERVE;(selet;defautl no) ENABLE | DISABLE | DISABLE ON SLAVE;(select;default ENABLE) COMMENT;(text-input) Event Body;(textarea)
5 editor for routines,triggers and events There are plenty of restrictions for them, in http://dev.mysql.com/doc/mysql-reslimits-excerpt/5.5/en/stored-program-restr... so IMO, we can warn the user when he's inputting some illegal statements.
And still, I have some questions. Does mysql have some statistics on triggers and events?Can a procedure output some data directly and meanwhile return some value with OUT|INOUT param?