[Phpmyadmin-devel] GSOC Idea: Support for procedures

[Michal Čihař]

Hi

Dne Sat, 26 Mar 2011 23:08:59 +0800
陈岳峰 <dennislyve at gmail.com> napsal(a):

> 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

Please note that while triggers are per table, routines and events are
defined in scope of database. Also I don't think it is good to mix them
all together.

> 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.

I think this deficiency should be workarounded somehow.

> 
> 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.

It requires just TRIGGER privilege.

> 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)

Again, it is just EVENT privilege.

> 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?

Yes, function can return value and still use OUT params.

-- 
	Michal Čihař | http://cihar.com | http://blog.cihar.com
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 836 bytes
Desc: not available
URL: <http://lists.phpmyadmin.net/pipermail/developers/attachments/20110328/4e8d7099/attachment.sig>