db.migration.V1_0_11__create_table_service_context.sql Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of database Show documentation
Show all versions of database Show documentation
Swisscom's Open Service Broker API implementation
DROP PROCEDURE IF EXISTS create_table_service_context;
DELIMITER //
CREATE PROCEDURE create_table_service_context()
BEGIN
CREATE TABLE service_context
(
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
platform VARCHAR(255) NOT NULL
);
CREATE TABLE service_context_detail
(
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`_key` VARCHAR(255) NOT NULL,
`_value` VARCHAR(255) DEFAULT 'NULL' NULL,
service_context_id BIGINT NOT NULL,
CONSTRAINT FK_service_context_detail2context
FOREIGN KEY (service_context_id) REFERENCES service_context (id)
);
CREATE INDEX idx_service_context_platform
ON service_context (platform);
CREATE INDEX idx_service_context_detail_key
ON service_context_detail (`_key`);
CREATE INDEX idx_service_context_detail_value
ON service_context_detail (`_value`);
CREATE INDEX idx_service_context_detail_key_value
ON service_context_detail (`_key`, `_value`);
ALTER TABLE service_instance
ADD COLUMN service_context_id BIGINT;
ALTER TABLE service_instance
ADD CONSTRAINT FK_service_instance2service_context
FOREIGN KEY (service_context_id) REFERENCES service_context (id);
ALTER TABLE service_binding
ADD COLUMN service_context_id BIGINT;
ALTER TABLE service_binding
ADD CONSTRAINT FK_service_binding2service_context
FOREIGN KEY (service_context_id) REFERENCES service_context (id);
ALTER TABLE provision_request
ADD COLUMN service_context_id BIGINT;
ALTER TABLE provision_request
ADD CONSTRAINT FK_provision_request2service_context
FOREIGN KEY (service_context_id) REFERENCES service_context (id);
END//
DELIMITER ;
CALL create_table_service_context();
DROP PROCEDURE IF EXISTS migrate_cf_context;
DELIMITER //
CREATE PROCEDURE migrate_cf_context()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE sc_id INT; -- service_context_id
DECLARE si_id INT; -- service_instance_id
DECLARE orgid, spaceid VARCHAR(255);
DECLARE scd_not_found INT DEFAULT FALSE;
DECLARE cur1 CURSOR FOR SELECT
si.id,
si.org,
si.space
FROM service_instance si
WHERE si.org IS NOT NULL AND si.service_context_id IS NULL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
read_loop: LOOP
BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
FETCH cur1
INTO si_id, orgid, spaceid;
IF done
THEN
LEAVE read_loop;
END IF;
END;
scd_search_block: BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND SET scd_not_found = TRUE;
SET scd_not_found = FALSE;
SELECT scd1.service_context_id
INTO sc_id
FROM service_context sc
JOIN service_context_detail scd1 ON sc.id = scd1.service_context_id
JOIN service_context_detail scd2 ON sc.id = scd2.service_context_id
WHERE sc.platform = 'cloudfoundry '
AND scd1.`_key` = 'organization_guid' AND scd1.`_value` = orgid
AND scd2.`_key` = 'space_guid' AND scd2.`_value` = spaceid;
IF scd_not_found
THEN
INSERT INTO service_context (platform)
VALUES ('cloudfoundry');
SELECT LAST_INSERT_ID()
INTO sc_id;
INSERT INTO service_context_detail (`_key`, `_value`, service_context_id)
VALUES ('organization_guid', orgid, sc_id);
INSERT INTO service_context_detail (`_key`, `_value`, service_context_id)
VALUES ('space_guid', spaceid, sc_id);
COMMIT;
END IF;
LEAVE scd_search_block;
END;
UPDATE service_instance si
SET service_context_id = sc_id
WHERE si.id = si_id;
COMMIT;
END LOOP;
COMMIT;
CLOSE cur1;
END//
DELIMITER ;
CALL migrate_cf_context();
DROP PROCEDURE IF EXISTS migrate_provision_request_cf_context;
DELIMITER //
CREATE PROCEDURE migrate_provision_request_cf_context()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE sc_id INT; -- service_context_id
DECLARE pr_id INT; -- provision_request id
DECLARE orgid, spaceid VARCHAR(255);
DECLARE scd_not_found INT DEFAULT FALSE;
DECLARE cur1 CURSOR FOR SELECT
pr.id,
pr.organization_guid,
pr.space_guid
FROM provision_request pr
WHERE pr.organization_guid IS NOT NULL AND pr.service_context_id IS NULL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
read_loop: LOOP
BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
FETCH cur1
INTO pr_id, orgid, spaceid;
IF done
THEN
LEAVE read_loop;
END IF;
END;
scd_search_block: BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND SET scd_not_found = TRUE;
SET scd_not_found = FALSE;
SELECT scd1.service_context_id
INTO sc_id
FROM service_context sc
JOIN service_context_detail scd1 ON sc.id = scd1.service_context_id
JOIN service_context_detail scd2 ON sc.id = scd2.service_context_id
WHERE sc.platform = 'cloudfoundry '
AND scd1.`_key` = 'organization_guid' AND scd1.`_value` = orgid
AND scd2.`_key` = 'space_guid' AND scd2.`_value` = spaceid;
IF scd_not_found
THEN
INSERT INTO service_context (platform)
VALUES ('cloudfoundry');
SELECT LAST_INSERT_ID()
INTO sc_id;
INSERT INTO service_context_detail (`_key`, `_value`, service_context_id)
VALUES ('organization_guid', orgid, sc_id);
INSERT INTO service_context_detail (`_key`, `_value`, service_context_id)
VALUES ('space_guid', spaceid, sc_id);
COMMIT;
END IF;
LEAVE scd_search_block;
END;
UPDATE provision_request pr
SET service_context_id = sc_id
WHERE pr.id = pr_id;
COMMIT;
END LOOP;
COMMIT;
CLOSE cur1;
END//
DELIMITER ;
CALL migrate_provision_request_cf_context();
/* VERIFY MIGRATION AND DROP COLUMNS 'ORG' AND 'SPACE' FROM SERVICE_INSTANCE */
DROP PROCEDURE IF EXISTS remove_org_space_fields_from_service_instance;
DELIMITER //
CREATE PROCEDURE remove_org_space_fields_from_service_instance()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE ERROR_MSG CHAR(255);
DECLARE siid INT; -- service_instance_id
DECLARE cur1 CURSOR FOR SELECT si.id
FROM service_instance si
WHERE si.org IS NOT NULL AND si.service_context_id IS NULL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
read_loop: LOOP
FETCH cur1
INTO siid;
IF NOT done
THEN
SELECT CONCAT('SERVICE INSTANCE WAS NOT MIGRATED: ', siid)
INTO ERROR_MSG;
SIGNAL SQLSTATE '90000'
SET MESSAGE_TEXT = ERROR_MSG;
END IF;
LEAVE read_loop;
END LOOP;
ALTER TABLE service_instance
DROP COLUMN org;
ALTER TABLE service_instance
DROP COLUMN space;
CLOSE cur1;
END//
DELIMITER ;
CALL remove_org_space_fields_from_service_instance();
DROP PROCEDURE IF EXISTS remove_org_space_fields_from_provision_request;
DELIMITER //
CREATE PROCEDURE remove_org_space_fields_from_provision_request()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE ERROR_MSG CHAR(255);
DECLARE pr_id INT; -- provision_request id
DECLARE cur1 CURSOR FOR SELECT pr.id
FROM provision_request pr
WHERE pr.organization_guid IS NOT NULL AND pr.service_context_id IS NULL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
read_loop: LOOP
FETCH cur1
INTO pr_id;
IF NOT done
THEN
SELECT CONCAT('PROVISION_REQUEST WAS NOT MIGRATED: ', pr_id)
INTO ERROR_MSG;
SIGNAL SQLSTATE '90001'
SET MESSAGE_TEXT = ERROR_MSG;
END IF;
LEAVE read_loop;
END LOOP;
ALTER TABLE provision_request
DROP COLUMN organization_guid,
DROP COLUMN space_guid;
CLOSE cur1;
END//
DELIMITER ;
CALL remove_org_space_fields_from_provision_request();
© 2015 - 2025 Weber Informatics LLC | Privacy Policy