Guys - while I'm testing the 4.0 RC by upgrading from 3.0.1, I'm running into
some exceptions being thrown when database upgrade SQL doesn't work (e.g.
trying to add a column to a table that already has that column). I don't want
to focus on the errors at this moment, but more about how we write SQL to
update the db…
Personally I'd prefer stored procedures for this as they support the ability to
use test logic.
e.g. instead of…
ALTER TABLE `cloud`.`user_vm` ADD COLUMN `update_parameters` tinyint(1) NOT
NULL DEFAULT 1 COMMENT 'Defines if the parameters need to be set for the vm';
We would have
----
DELIMITER $$
DROP PROCEDURE IF EXISTS upgrade_database_3_0_2_to_4_0 $$
CREATE PROCEDURE upgrade_database_3_0_2_to_4_0()
BEGIN
-- 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 'cloud'.'user_vm' ADD COLUMN 'update_parameters' tinyint(1) NOT
NULL DEFAULT 1 COMMENT 'Defines if the parameters need to be set for the vm';
END IF;
END $$
CALL upgrade_database_3_0_1_to_4_0() $$
DELIMITER ;
----
Thoughts? It's obviously heavier, but my thought is we could have a single
stored procedure in each upgrade script named similar to above, not a procedure
for each modification...
John
