Moving from MSSQL to MySQL.

Richard Quadling
Richard Quadling used Ask the Experts™
on
Hi.

I'm trying to build a generic DB change script, one which will be used to alter the INTERNAL db and then copied to the TEST and LIVE servers for running to make sure the DB schemas match and then we can move the data.

The reason for this is that manually amending the DBs and then someone forgets and BOOM!!! Usual sort of thing. So, having a mechanism to make the DB changes in order needs to be implemented.

The ability to add new columns to existing tables, create new tables, indexes, alter data if required, etc. All the usual stuff.


What I'm not sure (I'm 3 days into active MySQL development - used MS SQL for the last 7 years) is can I do ....

begin transaction

statement1
if error then
  rollback transaction
  exit
endif

statement2
if error then
  rollback transaction
  exit
endif

statement3
if error then
  rollback transaction
  exit
endif

commit transaction

Open in new window


So, say I want to add a column called "publish_merchant" which is a bit type to the table sw_merchants if it doesn't exist with a default of 0, how would I do that?

I can get bits working ...

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'datafeeds' AND TABLE_NAME = 'sw_merch' AND COLUMN_NAME = 'publish_merchant'

Open in new window


But I can't see how to "wrap" that up in a test firstly or how to wrap that in error handling.

AND then how to have multiple statements so that all or none of the changes work.

Over time, new features will be added to the DB and these changes need to be synched as part of the app.


Given the nature of my issue, have I chosen the right approach? Is there a better approach?

I can't just clone the DB. Only SOME of the data moves from INTERNAL (datafeeds). DataFeeds creates data for use in the TEST and LIVE sites. But some data is for the use of INTERNAL only. So, just cloning the entire DB is out.

Any ideas.

Regards,

Richard.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can use something like this for testing:

IF (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'datafeeds' AND TABLE_NAME = 'sw_merch' AND COLUMN_NAME = 'publish_merchant') IS NULL

And set autocommit off at the beginning of your stored proc so you explicitely have to commit the whole transaction:

SET autocommit = 0

More on mysql errors can be found here:

http://dev.mysql.com/doc/refman/5.1/en/innodb-error-handling.html
Richard QuadlingSenior Software Developer

Author

Commented:
OK. I'm frustrated. I've done this a million times in MS SQL. What am I doing wrong with mySQL?

Current code below just tells me I've got a "Syntax Error" ...

/**
 * $Id$
 *
 * This is the SQL Script that must be run to update the following databases:
 * 1 - Integration : datafeeds - unswitchd
 * 2 - Integration : fshopdb - switched
 * 3 - Production  : fshopdb - switched
 *
 * There will always be two "versions" of the code as there are switched and unswitched tables.
 *
 * Watch out for the switch tables. If the switch is set to 1 then use 2 and vice versa.
 */
DROP PROCEDURE IF EXISTS `test`.`DataFeeds_DatabaseUpgrade`;

DELIMITER $$

CREATE PROCEDURE `test`.`DataFeeds_DatabaseUpgrade`()
BEGIN
/**
 * RQuadling : 2012/01/05 19:12
 *
 * SW_MERCH.publish_merchant column
 *
 * :: START ::
 */
-- datafeeds
IF (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'datafeeds' AND TABLE_NAME = 'sw_merch' AND COLUMN_NAME = 'publish_merchant') IS NULL THEN
    BEGIN
    ALTER TABLE sw_merch ADD COLUMN publish_merchant BIT NOT NULL DEFAULT 0 COMMENT 'Used to indicate if this retailer/merchant should be exported.';
    END

-- fsshopdb -- Remember to use the OTHER table for the switching.
IF (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'fshopdb' AND TABLE_NAME = 'table_switch') IS NOT NULL THEN
    BEGIN
    SELECT @i_Switch = switch FROM fshopdb.table_switch;

    IF 1 = @i_Switch THEN
        BEGIN
        IF (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'fshopdb' AND TABLE_NAME = 'sw_merch_2' AND COLUMN_NAME = 'publish_merchant') IS NULL THEN
            BEGIN
            ALTER TABLE sw_merch_2 ADD COLUMN publish_merchant BIT NOT NULL DEFAULT 0 COMMENT 'Used to indicate if this retailer/merchant should be exported.';
            END
        END

    IF 2 = @i_Switch THEN
        BEGIN
        IF (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'fshopdb' AND TABLE_NAME = 'sw_merch_1' AND COLUMN_NAME = 'publish_merchant') IS NULL THEN
            BEGIN
            ALTER TABLE sw_merch_1 ADD COLUMN publish_merchant BIT NOT NULL DEFAULT 0 COMMENT 'Used to indicate if this retailer/merchant should be exported.';
            END
        END
END$$

DELIMITER ;

CALL `test`.`DataFeeds_DatabaseUpgrade`();

Open in new window


Now, no matter where I put ; (after END's), all I do is move the error code around.

I'm very tired of this. This is not a complex query. Just test to see if the column exists, if not, add it. Simply.


ARGH!!!!


Any help - PLEASE

Where are your End Ifs ? Too much tsql I think ;-)

http://dev.mysql.com/doc/refman/5.0/en/if-statement.html
Richard QuadlingSenior Software Developer

Author

Commented:
Got it!!!!

-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `DataFeeds_DatabaseUpgrade`()
BEGIN
/**
 * RQuadling : 2012/01/05 19:12
 *
 * SW_MERCH.publish_merchant column
 *
 * :: START ::
 */
-- datafeeds
IF NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'datafeeds' AND TABLE_NAME = 'sw_merch' AND COLUMN_NAME = 'publish_merchant') THEN
    ALTER TABLE sw_merch ADD COLUMN publish_merchant BIT NOT NULL DEFAULT 0 COMMENT 'Used to indicate if this retailer/merchant should be exported.';
    INSERT INTO DatabaseUpgrades.Upgrades(UpgradeNotes) VALUES('Added sw_merch.publish_merchant column');
END IF;

-- fsshopdb -- Remember to use the OTHER table for the switching.
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'fshopdb' AND TABLE_NAME = 'table_switch') THEN
    SELECT @i_Switch = switch FROM fshopdb.table_switch;

    IF 1 = @i_Switch AND NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'fshopdb' AND TABLE_NAME = 'sw_merch_2' AND COLUMN_NAME = 'publish_merchant') THEN
        ALTER TABLE sw_merch_2 ADD COLUMN publish_merchant BIT NOT NULL DEFAULT 0 COMMENT 'Used to indicate if this retailer/merchant should be exported.';
        INSERT INTO DatabaseUpgrades.Upgrades(UpgradeNotes) VALUES('Added sw_merch_2.publish_merchant column');
    END IF;

    IF 2 = @i_Switch AND NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'fshopdb' AND TABLE_NAME = 'sw_merch_1' AND COLUMN_NAME = 'publish_merchant') THEN
        ALTER TABLE sw_merch_1 ADD COLUMN publish_merchant BIT NOT NULL DEFAULT 0 COMMENT 'Used to indicate if this retailer/merchant should be exported.';
        INSERT INTO DatabaseUpgrades.Upgrades(UpgradeNotes) VALUES('Added sw_merch_1.publish_merchant column');
    END IF;
END IF;
/**
 * :: START ::
 */

END

Open in new window


This is from the created stored procedure.

Looks good so far...
Richard QuadlingSenior Software Developer

Author

Commented:
Yeah. WAY too much T-SQL!!! Thank you for your pointers.

The main issue was the lack of flow control code OUTSIDE of a stored procedure. Grrrrr.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial