(cloudstack) branch main updated: engine-schema: make adding/dropping indexes idempotent (#9171)

[rohit]

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 <abhishek.mr...@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 <abhishek.mr...@gmail.com>
    
    ---------
    
    Signed-off-by: Abhishek Kumar <abhishek.mr...@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;