April 16th, 2006

Mysql 5 and stored procedures and Functions

I am still in the process of upgrading Tracker to take advantage of the new features in mysql 5.

One of the most eagerly awaited features is support for stored procedures (and functions). These give me the ability to seperate virtually all the DB logic from the application logic (meaning less c code) and test the DB logic imdependently of the application (meaning better and easier debugging). It also speeds up development as more logic can be written in mysql's stored procedure langauge (which mixes procedural code and sql seamlessly) .

Here's a sample one that retrieves all metadata types in tracker's DB. (the pClass parameter can be "*" to get all metadata types or a prefix like "File" to only get "File.*" metdata types. The pWriteableOnly parameter indicates whether to retrieve only writeable metadata types.)


CREATE PROCEDURE SelectMetadataTypes (pClass varchar(16), pWriteableOnly bool)
BEGIN

Declare pClassLike varchar(16);

SELECT CONCAT(pClass, '.%') into pClassLike;

IF (pWritaebleOnly = 0) THEN

IF (Class = '*') THEN
SELECT MetaName, ID, DataTypeID, Embedded, Writeable FROM MetaDataTypes;
ELSE
SELECT MetaName, ID, DataTypeID, Embedded, Writeable FROM MetaDataTypes WHERE MetaName like pClassLike;
END IF;

ELSE
IF (Class = '*') THEN
SELECT MetaName, ID, DataTypeID, Embedded, Writeable FROM MetaDataTypes where writeable = 1;
ELSE
SELECT MetaName, ID, DataTypeID, Embedded, Writeable FROM MetaDataTypes WHERE MetaName like pClassLike and writeable = 1;
END IF;

END IF;
END

and to call the above from c code:

mysql_query (db, "Call SelectMetadataTypes ('File', 0) ");
  • Current Music
    Barber's Adagio for Strings