Copilot commented on code in PR #12144:
URL: https://github.com/apache/cloudstack/pull/12144#discussion_r4501412757
##########
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:
This upgrade adds the `category_id` column and the new category table, but
it never recreates `cloud.service_offering_view`. Existing 4.23 databases will
keep the old view without `category_id`, `category_uuid`, and `category_name`;
once the updated `ServiceOfferingJoinVO` is deployed, queries such as
`listServiceOfferings` will fail because Hibernate selects columns that the
view does not expose. Recreate/replace the view in this upgrade after the table
and column changes, matching the updated view definition.
##########
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:
The new migration still adds this foreign key with a raw `ALTER TABLE`, so
rerunning the upgrade or retrying after a partial upgrade fails with a
duplicate-constraint error. This script already uses the project's idempotent
foreign-key helper immediately above; use an idempotent equivalent that also
preserves the intended `RESTRICT`/`CASCADE` actions.
--
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]
