best way to write db update code?

[John Kinsella <jl...@stratosec.co>]

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

RE: best way to write db update code?

[Alex Huang <Al...@citrix.com>]

+1 on the IF check.  The condition should always be checked before performing the actual alteration.

As for the stored procedure, this is meant for writing unit test to test just that statement?  For testing if one statement is correct, I usually just run it inside mysql or workbench.  I think the unit test really should test the entire upgrade as a whole including the java bits.  For that we do have unit test examples in the source.

If we do go stored procedure way though, does it mean we should drop the stored procedure as the cleanup step in the db upgrade or else it would contain a bunch of stored procedures that won't be useful?

--Alex

> -----Original Message-----
> From: John Kinsella [mailto:jlk@stratosec.co]
> Sent: Wednesday, October 24, 2012 12:58 PM
> To: <cl...@incubator.apache.org> <cloudstack-
> dev@incubator.apache.org>
> Subject: best way to write db update code?
> 
> 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