File: sqlauto/sql00000000_init.sql

Recommend this page to a friend!
  Classes of Istvan Dobrentei  >  PHP DB Migration Library  >  sqlauto/sql00000000_init.sql  >  Download  
File: sqlauto/sql00000000_init.sql
Role: Auxiliary data
Content type: text/plain
Description: Auxiliary data
Class: PHP DB Migration Library
Migrate MySQL database tables with given SQL files
Author: By
Last change:
Date: 1 year ago
Size: 2,230 bytes
 

Contents

Class file image Download
-- Place here the initial database schema -->


-- End of schema creation

DELIMITER $$

DROP PROCEDURE IF EXISTS `AddColumnIfNotExists` $$

CREATE PROCEDURE `AddColumnIfNotExists`(
        IN tableName text,
        IN fieldName text,
        IN fieldDef text)
BEGIN
    IF NOT EXISTS(
        SELECT *
        FROM
            information_schema.columns
        WHERE
            `table_schema` COLLATE utf8_unicode_ci = DATABASE() AND
            `table_name` COLLATE utf8_unicode_ci =  tableName AND
            `column_name` COLLATE utf8_unicode_ci = fieldName
    )
    THEN
        SET @ddl = CONCAT('ALTER TABLE ', DATABASE(), '.', tableName, ' ADD COLUMN ', fieldName, ' ', fieldDef);
        PREPARE stmt FROM @ddl;
        EXECUTE stmt;
    END IF;
END $$

DROP PROCEDURE IF EXISTS `AddIndexIfNotExists` $$

CREATE PROCEDURE `AddIndexIfNotExists`(
        IN tableName text,
        IN indexName text,
        IN indexColumns text)
BEGIN
    IF NOT EXISTS(
        SELECT *
        FROM
            information_schema.statistics
        WHERE
            `table_schema` COLLATE utf8_unicode_ci = DATABASE() AND
            `table_name` COLLATE utf8_unicode_ci = tableName AND
            `index_name` COLLATE utf8_unicode_ci = indexName
    )
    THEN
        SET @ddl = CONCAT('CREATE INDEX ', indexName, ' ON ', DATABASE(), '.', tableName, ' (', indexColumns, ')');
        PREPARE stmt FROM @ddl;
        EXECUTE stmt;
    END IF;
END $$

DROP PROCEDURE IF EXISTS `AddConstraintIfNotExists` $$

CREATE PROCEDURE `AddConstraintIfNotExists`(
        IN tableName text,
        IN fieldType text,
        IN fieldDef text)
BEGIN
    IF NOT EXISTS(
        SELECT *
        FROM
            information_schema.table_constraints
        WHERE
            `constraint_schema` COLLATE utf8_unicode_ci = DATABASE() AND
            `table_schema` COLLATE utf8_unicode_ci = DATABASE() AND
            `table_name` COLLATE utf8_unicode_ci = tableName AND
            `constraint_type` COLLATE utf8_unicode_ci = fieldType
    )
    THEN
        SET @ddl = CONCAT('ALTER TABLE ', DATABASE(), '.', tableName, ' ADD ', fieldType, ' ', fieldDef);
        PREPARE stmt FROM @ddl;
        EXECUTE stmt;
    END IF;
END $$

DELIMITER ;

For more information send a message to info at phpclasses dot org.