Hanarion commented on code in PR #12144:
URL: https://github.com/apache/cloudstack/pull/12144#discussion_r4501458563
##########
engine/schema/src/main/resources/META-INF/db/schema-42300to2400.sql:
##########
@@ -239,3 +239,32 @@ CREATE TABLE IF NOT EXISTS
`cloud`.`instance_boot_group_details` (
PRIMARY KEY (`id`),
CONSTRAINT `fk_instance_boot_group_details__group_id` FOREIGN KEY
(`boot_group_id`) REFERENCES `instance_boot_group` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+
+CREATE TABLE IF NOT EXISTS `cloud`.`service_offering_category` (
+ `id` bigint unsigned NOT NULL auto_increment,
+ `name` varchar(255) NOT NULL,
+ `uuid` varchar(40) NOT NULL,
+ `sort_key` int NOT NULL DEFAULT 0,
+ `is_default` tinyint(1) NOT NULL DEFAULT 0,
+ PRIMARY KEY (`id`),
+ CONSTRAINT `uc_service_offering_category__uuid` UNIQUE (`uuid`),
+ CONSTRAINT `uc_service_offering_category__name` UNIQUE (`name`)
+) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
+
+CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.service_offering_category',
'is_default', 'tinyint(1) NOT NULL DEFAULT 0');
+-- The previous implementation reserved ID 1 for the default, even if it was
renamed.
+UPDATE `cloud`.`service_offering_category` AS `legacy`
+LEFT JOIN (SELECT `id` FROM `cloud`.`service_offering_category` WHERE
`is_default` = 1 LIMIT 1) AS `current_default` ON 1 = 1
+SET `legacy`.`is_default` = 1
+WHERE `legacy`.`id` = 1 AND `current_default`.`id` IS NULL;
+INSERT INTO `cloud`.`service_offering_category` (name, uuid, is_default)
+SELECT 'Default', UUID(), 1
+WHERE NOT EXISTS (SELECT 1 FROM `cloud`.`service_offering_category` WHERE
`is_default` = 1)
+ON DUPLICATE KEY UPDATE `is_default` = 1;
+
+CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.service_offering', 'category_id',
'bigint unsigned DEFAULT NULL');
+UPDATE `cloud`.`service_offering` SET `category_id` = (SELECT `id` FROM
`cloud`.`service_offering_category` WHERE `is_default` = 1)
+WHERE `category_id` IS NULL;
+CALL `cloud`.`IDEMPOTENT_DROP_FOREIGN_KEY`('cloud.service_offering',
'fk_service_offering__category_id');
+ALTER TABLE `cloud`.`service_offering` MODIFY COLUMN `category_id` bigint
unsigned NOT NULL;
+ALTER TABLE `cloud`.`service_offering` ADD CONSTRAINT
`fk_service_offering__category_id` FOREIGN KEY (`category_id`) REFERENCES
`cloud`.`service_offering_category` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
Review Comment:
It should already be idempotent, the `IDEMPOTENT_DROP_FOREIGN_KEY` right
above drops it if it exists before it's added again. I ran the block twice on a
DB to check and it's fine. I can't use `IDEMPOTENT_ADD_FOREIGN_KEY` because it
forces `ON DELETE CASCADE` and we need `RESTRICT` for this one
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
