You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudstack.apache.org by ro...@apache.org on 2024/06/10 11:02:37 UTC
(cloudstack) branch main updated: engine-schema: make adding/dropping indexes idempotent (#9171)
This is an automated email from the ASF dual-hosted git repository.
rohit pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudstack.git
The following commit(s) were added to refs/heads/main by this push:
new 21d07132add engine-schema: make adding/dropping indexes idempotent (#9171)
21d07132add is described below
commit 21d07132add186c7d6a9ae180ce9ccff8035fdab
Author: Abhishek Kumar <ab...@gmail.com>
AuthorDate: Mon Jun 10 16:32:30 2024 +0530
engine-schema: make adding/dropping indexes idempotent (#9171)
* engine-schema: make adding/dropping indexes idempotent
Fixes #9170
* fix
Signed-off-by: Abhishek Kumar <ab...@gmail.com>
---------
Signed-off-by: Abhishek Kumar <ab...@gmail.com>
---
.../resources/META-INF/db/schema-41910to42000.sql | 19 ++++++++++++++-----
1 file changed, 14 insertions(+), 5 deletions(-)
diff --git a/engine/schema/src/main/resources/META-INF/db/schema-41910to42000.sql b/engine/schema/src/main/resources/META-INF/db/schema-41910to42000.sql
index bece8f1b091..3c2288ed077 100644
--- a/engine/schema/src/main/resources/META-INF/db/schema-41910to42000.sql
+++ b/engine/schema/src/main/resources/META-INF/db/schema-41910to42000.sql
@@ -23,11 +23,20 @@
CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.resource_limit', 'tag', 'varchar(64) DEFAULT NULL COMMENT "tag for the limit" ');
CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.resource_count', 'tag', 'varchar(64) DEFAULT NULL COMMENT "tag for the resource count" ');
CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.resource_reservation', 'tag', 'varchar(64) DEFAULT NULL COMMENT "tag for the resource reservation" ');
-ALTER TABLE `resource_count`
-DROP INDEX `i_resource_count__type_accountId`,
-DROP INDEX `i_resource_count__type_domaintId`,
-ADD UNIQUE INDEX `i_resource_count__type_tag_accountId` (`type`,`tag`,`account_id`),
-ADD UNIQUE INDEX `i_resource_count__type_tag_domaintId` (`type`,`tag`,`domain_id`);
+CALL `cloud`.`IDEMPOTENT_DROP_INDEX`('i_resource_count__type_accountId', 'cloud.resource_count');
+CALL `cloud`.`IDEMPOTENT_DROP_INDEX`('i_resource_count__type_domaintId', 'cloud.resource_count');
+
+DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_ADD_UNIQUE_INDEX`;
+CREATE PROCEDURE `cloud`.`IDEMPOTENT_ADD_UNIQUE_INDEX` (
+ IN in_table_name VARCHAR(200),
+ IN in_index_name VARCHAR(200),
+ IN in_index_definition VARCHAR(1000)
+)
+BEGIN
+ DECLARE CONTINUE HANDLER FOR 1061 BEGIN END; SET @ddl = CONCAT('ALTER TABLE ', in_table_name, ' ', 'ADD UNIQUE INDEX ', in_index_name, ' ', in_index_definition); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; END;
+
+CALL `cloud`.`IDEMPOTENT_ADD_UNIQUE_INDEX`('cloud.resource_count', 'i_resource_count__type_tag_accountId', '(type, tag, account_id)');
+CALL `cloud`.`IDEMPOTENT_ADD_UNIQUE_INDEX`('cloud.resource_count', 'i_resource_count__type_tag_domainId', '(type, tag, domain_id)');
ALTER TABLE `cloud`.`resource_reservation`
MODIFY COLUMN `amount` bigint NOT NULL;