--
-- Host: localhost    Database: cloud
-- ------------------------------------------------------


--
-- Dumping routines for database 'cloud'
--
/*!50003 DROP PROCEDURE IF EXISTS `ADD_GUEST_OS_AND_HYPERVISOR_MAPPING` */;
DELIMITER ;;
CREATE DEFINER=`cloud`@`%` PROCEDURE `ADD_GUEST_OS_AND_HYPERVISOR_MAPPING`(     IN guest_os_category_id bigint(20) unsigned,     IN guest_os_display_name VARCHAR(255),     IN guest_os_hypervisor_hypervisor_type VARCHAR(32),     IN guest_os_hypervisor_hypervisor_version VARCHAR(32),     IN guest_os_hypervisor_guest_os_name VARCHAR(255)         )
BEGIN INSERT  INTO cloud.guest_os (uuid, category_id, display_name, created) SELECT 	UUID(), guest_os_category_id, guest_os_display_name, now() FROM    DUAL WHERE 	not exists( SELECT  1                      FROM    cloud.guest_os                      WHERE   cloud.guest_os.category_id = guest_os_category_id                        AND     cloud.guest_os.display_name = guest_os_display_name) ;	INSERT  INTO cloud.guest_os_hypervisor (uuid, hypervisor_type, hypervisor_version, guest_os_name, guest_os_id, created)      SELECT 	UUID(), guest_os_hypervisor_hypervisor_type, guest_os_hypervisor_hypervisor_version, guest_os_hypervisor_guest_os_name, guest_os.id, now()      FROM 	cloud.guest_os      WHERE 	guest_os.category_id = guest_os_category_id        AND 	guest_os.display_name = guest_os_display_name        AND	NOT EXISTS (SELECT  1                           FROM    cloud.guest_os_hypervisor as hypervisor                           WHERE   hypervisor_type = guest_os_hypervisor_hypervisor_type                             AND     hypervisor_version = guest_os_hypervisor_hypervisor_version                             AND     hypervisor.guest_os_id = guest_os.id                             AND     hypervisor.guest_os_name = guest_os_hypervisor_guest_os_name) ;END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `IDEMPOTENT_ADD_COLUMN` */;
DELIMITER ;;
CREATE DEFINER=`cloud`@`%` PROCEDURE `IDEMPOTENT_ADD_COLUMN`(     IN in_table_name VARCHAR(200) , IN in_column_name VARCHAR(200) , IN in_column_definition VARCHAR(1000) )
BEGIN     DECLARE CONTINUE HANDLER FOR 1060 BEGIN END; SET @ddl = CONCAT('ALTER TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', 'ADD COLUMN') ; SET @ddl = CONCAT(@ddl, ' ', in_column_name); SET @ddl = CONCAT(@ddl, ' ', in_column_definition); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `IDEMPOTENT_ADD_KEY` */;
DELIMITER ;;
CREATE DEFINER=`cloud`@`%` PROCEDURE `IDEMPOTENT_ADD_KEY`( 		IN in_index_name VARCHAR(200)     , IN in_table_name VARCHAR(200)     , IN in_key_definition VARCHAR(1000) )
BEGIN     DECLARE CONTINUE HANDLER FOR 1061 BEGIN END; SET @ddl = CONCAT('ALTER TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', ' ADD KEY ') ; SET @ddl = CONCAT(@ddl, ' ', in_index_name); SET @ddl = CONCAT(@ddl, ' ', in_key_definition); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `IDEMPOTENT_ADD_UNIQUE_KEY` */;
DELIMITER ;;
CREATE DEFINER=`cloud`@`%` PROCEDURE `IDEMPOTENT_ADD_UNIQUE_KEY`(     IN in_table_name VARCHAR(200) , IN in_key_name VARCHAR(200) , IN in_key_definition VARCHAR(1000) )
BEGIN     DECLARE CONTINUE HANDLER FOR 1061 BEGIN END; SET @ddl = CONCAT('ALTER TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', 'ADD UNIQUE KEY ', in_key_name); SET @ddl = CONCAT(@ddl, ' ', in_key_definition); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `IDEMPOTENT_CHANGE_COLUMN` */;
DELIMITER ;;
CREATE DEFINER=`cloud`@`%` PROCEDURE `IDEMPOTENT_CHANGE_COLUMN`(     IN in_table_name VARCHAR(200) , IN in_column_name VARCHAR(200) , IN in_column_new_name VARCHAR(200) , IN in_column_new_definition VARCHAR(1000) )
BEGIN     DECLARE CONTINUE HANDLER FOR 1054 BEGIN END; SET @ddl = CONCAT('ALTER TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', 'CHANGE COLUMN') ; SET @ddl = CONCAT(@ddl, ' ', in_column_name); SET @ddl = CONCAT(@ddl, ' ', in_column_new_name); SET @ddl = CONCAT(@ddl, ' ', in_column_new_definition); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `IDEMPOTENT_DROP_FOREIGN_KEY` */;
DELIMITER ;;
CREATE DEFINER=`cloud`@`%` PROCEDURE `IDEMPOTENT_DROP_FOREIGN_KEY`(     IN in_table_name VARCHAR(200) , IN in_foreign_key_name VARCHAR(200) )
BEGIN     DECLARE CONTINUE HANDLER FOR 1091, 1025 BEGIN END; SET @ddl = CONCAT('ALTER TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', ' DROP FOREIGN KEY '); SET @ddl = CONCAT(@ddl, ' ', in_foreign_key_name); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `IDEMPOTENT_DROP_INDEX` */;
DELIMITER ;;
CREATE DEFINER=`cloud`@`%` PROCEDURE `IDEMPOTENT_DROP_INDEX`( 		IN in_index_name VARCHAR(200)     , IN in_table_name VARCHAR(200) )
BEGIN     DECLARE CONTINUE HANDLER FOR 1091 BEGIN END; SET @ddl = CONCAT('DROP INDEX ', in_index_name); SET @ddl = CONCAT(@ddl, ' ', ' ON ') ; SET @ddl = CONCAT(@ddl, ' ', in_table_name); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `IDEMPOTENT_INSERT_GUESTOS_HYPERVISOR_MAPPING` */;
DELIMITER ;;
CREATE DEFINER=`cloud`@`%` PROCEDURE `IDEMPOTENT_INSERT_GUESTOS_HYPERVISOR_MAPPING`(                             IN in_hypervisor_type VARCHAR(32),                             IN in_hypervisor_version VARCHAR(32),                             IN in_guest_os_name VARCHAR(255),                             IN in_guest_os_id BIGINT(20) UNSIGNED,                             IN is_user_defined int(1) UNSIGNED)
BEGIN         IF NOT EXISTS ((SELECT * FROM `cloud`.`guest_os_hypervisor` WHERE             hypervisor_type=in_hypervisor_type AND             hypervisor_version=in_hypervisor_version AND             guest_os_id = in_guest_os_id))         THEN                 INSERT INTO `cloud`.`guest_os_hypervisor` (                         uuid,                         hypervisor_type,                         hypervisor_version,                         guest_os_name,                         guest_os_id,                         created,                         is_user_defined)                         VALUES (                             UUID(),                             in_hypervisor_type,                             in_hypervisor_version,                             in_guest_os_name,                             in_guest_os_id,                             utc_timestamp(),                             is_user_defined                         ); END IF; END ;;
DELIMITER ;


