Using PHP to create MySQL database procedures

There was a requirement to create MySQL database procedures and functions for a future feature of the Password Control plugin component. Much time and effort was put into finding a solution that would work. Extensive searching of the available documentation and internet resources didn't really help hence this blog.

Extensive attempts using the SQL update files and the manifest file failed to produce a result. In the end a solution was found using the manifest installation script file and writting PHP code.

First the use of the SQL upgrade scripts was investigated. Unfortunately Joomla/PHP doesn't seem to want to recognise the 'DELIMITER' keyword required by MySQL. I never found a work around for this and believe it is a PHP limitation.

Second attempts to use the 'old' Joomla 1.5 'install/uninstall/update' syntax didn't work either. I was not too surprised by this result at all.

Early attempts to use the manifest file defined 'scriptfile' were also unsuccessful. This was due to the script file not being invoked. The name of the class defined in the script file appears to be critical. Having previously used a script file for a component where there was an underscore character in the class name working successfully, it took some time before I eventually removed the underscores from the class name for the plugin script file. Once this was done the script ran successfully on install, uninstall and update as it should.

Now I could beging to write some PHP code to get the database procedures working. Code like the following worked successfully.

$query="DROP PROCEDURE IF EXISTS `upgrade_plg_database_to_0_0_5`;";
$db->setQuery($query);
$db->query();

$query="CREATE PROCEDURE upgrade_plg_database_to_0_0_5() ";
$query.= "\nBEGIN";
$query.= "\n IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()";
$query.= "\n        AND COLUMN_NAME='seq_id' AND TABLE_NAME=`#__passwordcontrol`) ) THEN";
$query.= "\n     ALTER TABLE `#__passwordcontrol`";
$query.= "\n        DROP FOREIGN KEY `#__passwordcontrol_ibfk_1`,";
$query.= "\n        ADD seq_id INT NOT NULL DEFAULT '0' COMMENT 'Sequential password counter' AFTER uid;";
$query.= "\n   ALTER TABLE `#__passwordcontrol`";
$query.= "\n        ADD CONSTRAINT `#__passwordcontrol_ibfk_1` FOREIGN KEY (uid) REFERENCES `#__users` (id) ON UPDATE RESTRICT ON DELETE CASCADE;";
$query.= "\n     ALTER TABLE `#__passwordcontrol`";
$query.= "\n        ADD INDEX idx_uid_seqid (uid, seq_id);";
$query.= "\n   ALTER TABLE `#__passwordcontrol` DROP INDEX idx_uid;";
$query.= "\n END IF;";
$query.= "\nEND";
$db->setQuery($query);
$db->query();

Note that the end of the query doesn't require a semicolon.

This solution avoids the need to get the user to run phpAdmin to load the procedures into the database, or to get the user to run a separate post-install script to update the database.