My research and experiments haven't yielded an answer yet, so I am hoping for some help.
I am modifying the install file of an application which in previous versions did not have a column which I want to add now. I do not want to add the column manually, but in the installation file and only if the new column does not already exist in the table.
The table is created as follows:
CREATE TABLE IF NOT EXISTS `#__comm_subscribers` ( `subscriber_id` int(11) NOT NULL auto_increment, `user_id` int(11) NOT NULL default '0', `subscriber_name` varchar(64) NOT NULL default '', `subscriber_surname` varchar(64) NOT NULL default '', `subscriber_email` varchar(64) NOT NULL default '', `confirmed` tinyint(1) NOT NULL default '0', `subscribe_date` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`subscriber_id`), UNIQUE KEY `subscriber_email` (`subscriber_email`) ) ENGINE=MyISAM CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' COMMENT='Subscribers for Comm are stored here.';
If I add the following, below the create table statement, then I am not sure what happens if the column already exists (and perhaps is populated):
ALTER TABLE `#__comm_subscribers` ADD `subscriber_surname`; ALTER TABLE `#__comm_subscribers` MODIFY `subscriber_surname` varchar(64) NOT NULL default '';
So, I tried the following which I found somewhere. This does not seem to work but I am not entirely sure I used it properly.
/*delimiter '//' CREATE PROCEDURE addcol() BEGIN IF NOT EXISTS( SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='subscriber_surname' AND TABLE_NAME='#__comm_subscribers' ) THEN ALTER TABLE `#__comm_subscribers` ADD COLUMN `subscriber_surname` varchar(64) NOT NULL default ''; END IF; END; // delimiter ';' CALL addcol(); DROP PROCEDURE addcol;*/
Does anyone have a good way to do this?
This question is related to
Here is a working solution (just tried out with MySQL 5.0 on Solaris):
DELIMITER $$ DROP PROCEDURE IF EXISTS upgrade_database_1_0_to_2_0 $$ CREATE PROCEDURE upgrade_database_1_0_to_2_0() BEGIN -- rename a table safely IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='my_old_table_name') ) THEN RENAME TABLE my_old_table_name TO my_new_table_name, END IF; -- add a column safely IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND COLUMN_NAME='my_additional_column' AND TABLE_NAME='my_table_name') ) THEN ALTER TABLE my_table_name ADD my_additional_column varchar(2048) NOT NULL DEFAULT ''; END IF; END $$ CALL upgrade_database_1_0_to_2_0() $$ DELIMITER ;
On a first glance it probably looks more complicated than it should, but we have to deal with following problems here:
IFstatements only work in stored procedures, not when run directly, e.g. in mysql client
SHOW COLUMNSdoes not work in stored procedure so have to use INFORMATION_SCHEMA
DATABASE()returns the name of the currently selected database.