MySQL Bugs: #56361: Table Methods

Bug #56361	Table Methods
Submitted:	30 Aug 2010 11:04	Modified:	29 May 2012 13:58
Reporter:	Winfried Trümper	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Good day,

I'm re-factoring a medium size SQL-only application. The application is working well courtesy to the very reasonable extensions MySQL provides. After editing a few thousand lines of SQL code I'd suggest another extension MySQL could provide: method calls.

My personal motivation is the global name space for stored program names. Once you start using stored progams a lot, their names tend to get long. Mine are between 20 and 45 characters, heavily prefixed in order to avoid namespace clashes.

There are already methods in Oracle and DB2, so I will call my suggestion row methods. A SQL row method would be a SQL function with four special properties:

* inside the body there would be THIS. in analogy to NEW. and OLD. for triggers,
* the call to the function can only be done via the primary key for the table,
* its name would be local to the table(s) it belongs to,
* no deterministic behaviour (not applicable).

An illustration of the idea is given as informal SQL code at the end and I'm refering to it in the following. The withdraw SQL row method for the accounts SQL table is used in the following fashion:

        SELECT acc_number!withdraw(7.89)
        FROM accounts
        WHERE acc_number = '123456';

Expressed a conventional function, you have to call:

        SELECT some_namespace_clash_safe_name_for_withdraw(acc_number, 7.89)
        FROM accounts
        WHERE acc_number = '123456';
- or simply -
	SELECT some_namespace_clash_safe_name_for_withdraw('123456', 7.89);

Keep in mind you have several developers working on hundreds of functions, so your function names have to be very long and speaking in order to avoid namespace clashes. Binding functions to tables can be so much safer and efficient. Actually it is very SQLish, because it brings functions more closely to set operations. Look at the implementation of some_namespace_clash_safe_name_for_withdraw, it requires a SELECT for every invokation. That might be the right thing to do or horribly ineffective.

Ideally row methods would also work with any primary key for joined tables. :]

Thanks

-Winfried

CREATE TABLE accounts (
       acc_number varchar(16),
       acc_holder varchar(32),
       acc_amount double,
       PRIMARY KEY (acc_number)
);
--
INSERT INTO accounts VALUES
       ('123456', 'Winfried', 78.9);
--
CREATE TABLE statements (
       acc_number varchar(16),
       acc_amount double,
       acc_time TIMESTAMP NOT NULL default NOW()
);
--

How to repeat:
DROP FUNCTION IF EXISTS some_namespace_clash_safe_name_for_withdraw;
DELIMITER //
CREATE FUNCTION some_namespace_clash_safe_name_for_withdraw(arg_number varchar(16), arg_amount double)
RETURNS double
-- ... the usal stuff
BEGIN
        DECLARE this_amount double;

        UPDATE accounts
        SET acc_amount = acc_amount + arg_amount
        WHERE acc_number = arg_number;

        SELECT acc_amount
        INTO this_amount
        FROM accounts
        WHERE acc_number = arg_number;

        INSERT INTO statements VALUES
                (arg_number, arg_amount, NOW());

        RETURN(this_amount);
END //
DELIMITER ;
--
SELECT some_namespace_clash_safe_name_for_withdraw('123456', 7.89);

Suggested fix:
DELIMITER //
CREATE FUNCTION accounts.withdraw(arg_amount double)
RETURNS double
ROW_METHOD
-- ... the usal stuff
BEGIN
        THIS.acc_amount = THIS.acc_amount + arg_amount;
        INSERT INTO statements VALUES
                (THIS.acc_number, arg_amount, NOW());
        RETURN(THIS.acc_amount);
END //
DELIMITER ;
--
SELECT acc_number!withdraw(789)
FROM accounts
WHERE acc_number = 123456;

Are you referring to something similar to object types with methods in Oracle RDBMS? Tables in SQL do NOT have methods in any SQL Standard I know about...

Changed the synopsis to Table Methods after evaluating and editing the MySQL source. Overall it should be more consistent that way. The definition of a table method now reads:

DELIMITER //
CREATE FUNCTION withdraw(arg_amount double)
RETURNS double
METHOD_TABLE 'accounts' /* marking the definition as a Table Method */
BEGIN
        THIS.acc_amount = THIS.acc_amount + arg_amount;
        INSERT INTO statements VALUES
                (THIS.acc_number, arg_amount, NOW());
        RETURN(THIS.acc_amount);
END //
DELIMITER ;

I appreciate the attention you give to this feature request. Regarding your questions:

#1 The approach appears different from the object types available in Oracle RDBMS.

#2 A few reserved methods already exist since MySQL 5.0, although the documentation uses a different terminology.

Object Oriented terminology and Relational terminology correspond in the following way: class=table, instance/object=row, method=... Methods are functions, which belong to a class and can be invoked on an object. That applies to triggers: they belong to a table and they are invoked on a row. Thus triggers are methods, to be more precise: they are constructors and destructors.

However, constructors and destructors are called implicitly, so no calling syntax for triggers would remind anybody of "hey look, we already got methods in MySQL". Actually a special invocation syntax for MySQL methods is required to make it smooth for multi-table selects. See my next message for a comparison.

Regarding the method definition, I suggest a TABLE clause for functions, which enables the use of THIS in the SP code. It is meant as the analogy to NEW and OLD for triggers. The alternative would be a CREATE METHOD syntax - too heavy.

In short, my suggestion is the generalization of an excellent feature already contained in MySQL. 

Again, thank you for your interest.

-Winfried

C++ method invocation:

account_object = somehow get it;
account_object.withdraw(789);

MySQL table method invocation:

SELECT withdraw(789)
FROM accounts
WHERE acc_number = 123456;

Inside the withdraw function the alias THIS provides full access to the retrieved row. Qualified for a multi-table SELECT:

SELECT accounts!withdraw(789)
FROM accounts INNER JOIN ...
WHERE acc_number = 123456;

The need for (fully) qualified method names for multi-table SELECT effectively prevents  multiple inheritance (MI). A matter of personal taste whether it's good or bad. However, MI needs a method resolution order and that would require further keywords. Too complex to implement.

Requirements for table methods:

* METHOD_TABLE clause for functions, which takes the associated table name.

* ALTER TABLE proc ADD COLUMN method_table char(64) AFTER db;

* THIS alias in analogy to NEW and OLD for triggers

* Method invocation syntax for multi-table select

* Check at start of SP execution whether function is a table method

Benefits of table methods:

* Same as for triggers: potentially faster SP, more concise code

* Creating a unique selling point with a relatively small modification of existing code.