[Phpmyadmin-devel] GSOC Idea: Support for procedures

[陈岳峰]

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-restrictions.html
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?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.phpmyadmin.net/pipermail/developers/attachments/20110326/b559cac1/attachment.html>