[GitHub] [cloudstack] shwstppr commented on a change in pull request #4699: Add new registers in guest_os

[GitBox <gi...@apache.org>]

shwstppr commented on a change in pull request #4699:
URL: https://github.com/apache/cloudstack/pull/4699#discussion_r582654727



##########
File path: engine/schema/src/main/resources/META-INF/db/schema-41510to41600.sql
##########
@@ -301,3 +301,43 @@ from
         left join
     `cloud`.`resource_count` secondary_storage_count ON domain.id = secondary_storage_count.domain_id
         and secondary_storage_count.type = 'secondary_storage';
+
+-- PR#4699 Drop the procedure `ADD_GUEST_OS_AND_HYPERVISOR_MAPPING` if it already exist.
+DROP PROCEDURE IF EXISTS `cloud`.`ADD_GUEST_OS_AND_HYPERVISOR_MAPPING`;
+
+-- PR#4699 Create the procedure `ADD_GUEST_OS_AND_HYPERVISOR_MAPPING` to add guest_os and guest_os_hypervisor mapping.
+CREATE PROCEDURE `cloud`.`ADD_GUEST_OS_AND_HYPERVISOR_MAPPING` (
+    IN guest_os_category_id bigint(20) unsigned,
+    IN guest_os_display_name VARCHAR(255),
+    IN guest_os_hypervisor_hypervisor_type VARCHAR(32),
+    IN guest_os_hypervisor_hypervisor_version VARCHAR(32),
+    IN guest_os_hypervisor_guest_os_name VARCHAR(255)
+)
+BEGIN	
+	INSERT  INTO cloud.guest_os (uuid, category_id, display_name, created) 
+	SELECT 	UUID(), guest_os_category_id, guest_os_display_name, now()	
+	WHERE 	not exists( SELECT  1 
+	                    FROM    cloud.guest_os
+	                    WHERE   cloud.guest_os.category_id = guest_os_category_id
+	                    AND     cloud.guest_os.display_name = guest_os_display_name);	
+						
+	INSERT  INTO cloud.guest_os_hypervisor (uuid, hypervisor_type, hypervisor_version, guest_os_name, guest_os_id, created) 
+	SELECT 	UUID(), guest_os_hypervisor_hypervisor_type, guest_os_hypervisor_hypervisor_version, guest_os_hypervisor_guest_os_name, guest_os.id, now()
+	FROM 	cloud.guest_os
+	WHERE 	guest_os.category_id = guest_os_category_id
+	AND 	guest_os.display_name = guest_os_display_name
+	AND	NOT EXISTS (SELECT  1 
+	                    FROM    cloud.guest_os_hypervisor as hypervisor
+	                    WHERE   hypervisor_type = guest_os_hypervisor_hypervisor_type			
+	                    AND     hypervisor_version = guest_os_hypervisor_hypervisor_version
+	                    AND     hypervisor.guest_os_id = guest_os.id
+	                    AND     hypervisor.guest_os_name = guest_os_hypervisor_guest_os_name);    
+END;
+
+-- PR#4699 Call procedure `ADD_GUEST_OS_AND_HYPERVISOR_MAPPING` to add new data to guest_os and guest_os_hypervisor.
+CALL ADD_GUEST_OS_AND_HYPERVISOR_MAPPING (1, 'Ubuntu 20.04 LTS', 'KVM', 'default', 'Ubuntu 20.04 LTS');
+CALL ADD_GUEST_OS_AND_HYPERVISOR_MAPPING (1, 'Ubuntu 21.04', 'KVM', 'default', 'Ubuntu 21.04');
+CALL ADD_GUEST_OS_AND_HYPERVISOR_MAPPING (1, 'pfSense 2.4', 'KVM', 'default', 'pfSense 2.4');
+CALL ADD_GUEST_OS_AND_HYPERVISOR_MAPPING (1, 'OpenBSD 6.7', 'KVM', 'default', 'OpenBSD 6.7');
+CALL ADD_GUEST_OS_AND_HYPERVISOR_MAPPING (1, 'OpenBSD 6.8', 'KVM', 'default', 'OpenBSD 6.8');
+CALL ADD_GUEST_OS_AND_HYPERVISOR_MAPPING (1, 'AlmaLinux 8.3', 'KVM', 'default', 'AlmaLinux 8.3');

Review comment:
       category_id used here for each entry is 1 while for Ubuntu releases we have 10. Not sure for others it should be 7 or 9. It is 9 for FreeBSD in current db




----------------------------------------------------------------
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.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org