Deprecated: Joomla\Input\Input implements the Serializable interface, which is deprecated. Implement __serialize() and __unserialize() instead (or in addition, if support for old PHP versions is necessary) in /homepages/13/d380392445/htdocs/Jlive/libraries/vendor/joomla/input/src/Input.php on line 41

Deprecated: Return type of Joomla\Input\Input::count() should either be compatible with Countable::count(): int, or the #[\ReturnTypeWillChange] attribute should be used to temporarily suppress the notice in /homepages/13/d380392445/htdocs/Jlive/libraries/vendor/joomla/input/src/Input.php on line 170
Using PHP to create MySQL database procedures - Macrotone Blogs

Macrotone Blogs

Macrotone blogs upon Joomla, our products and other matters.
Categories
Tags
Archives
Calendar
Categories:   All Categories
Suggested keywords
x
Font size: + –
  Print

Using PHP to create MySQL database procedures

6145 Hits

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.

PHP calling MySQL functions and procedures
Oracle based packages available

About the author

Geoffrey Chapman

Geoffrey Chapman

Subscribe to updates from author Unsubscribe to updates from author Geoffrey Chapman

An IT professional with a wide experience of IT systems, specialising in Database Management and Security.

Author's recent posts
More posts from author
 
Go To Top

Joomla! Debug Console

Session

Profile Information

Memory Usage

Database Queries