engine/schema/src/main/resources/META-INF/db/schema-41120to41200.sql - cloudstack - Git at Google

 -- Licensed to the Apache Software Foundation (ASF) under one
 -- or more contributor license agreements.  See the NOTICE file
 -- distributed with this work for additional information
 -- regarding copyright ownership.  The ASF licenses this file
 -- to you under the Apache License, Version 2.0 (the
 -- "License"); you may not use this file except in compliance
 -- with the License.  You may obtain a copy of the License at
 --
 --   http://www.apache.org/licenses/LICENSE-2.0
 --
 -- Unless required by applicable law or agreed to in writing,
 -- software distributed under the License is distributed on an
 -- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 -- KIND, either express or implied.  See the License for the
 -- specific language governing permissions and limitations
 -- under the License.

 --;
 -- Schema upgrade from 4.11.2.0 to 4.12.0.0
 --;

 -- [CLOUDSTACK-10314] Add reason column to ACL rule table
 ALTER TABLE `cloud`.`network_acl_item` ADD COLUMN `reason` VARCHAR(2500) AFTER `display`;

 -- [CLOUDSTACK-9846] Make provision to store content and subject for Alerts in separate columns.
 ALTER TABLE `cloud`.`alert` ADD COLUMN `content` VARCHAR(5000);

 -- Fix the name of the column used to hold IPv4 range in 'vlan' table.
 ALTER TABLE `vlan` CHANGE `description` `ip4_range` varchar(255);

 -- [CLOUDSTACK-10344] bug when moving ACL rules (change order with drag and drop)
 -- We are only adding the permission to the default rules. Any custom rule must be configured by the root admin.
 INSERT INTO `cloud`.`role_permissions` (`uuid`, `role_id`, `rule`, `permission`, `sort_order`) values (UUID(), 2, 'moveNetworkAclItem', 'ALLOW', 100) ON DUPLICATE KEY UPDATE rule=rule;
 INSERT INTO `cloud`.`role_permissions` (`uuid`, `role_id`, `rule`, `permission`, `sort_order`) values (UUID(), 3, 'moveNetworkAclItem', 'ALLOW', 302) ON DUPLICATE KEY UPDATE rule=rule;
 INSERT INTO `cloud`.`role_permissions` (`uuid`, `role_id`, `rule`, `permission`, `sort_order`) values (UUID(), 4, 'moveNetworkAclItem', 'ALLOW', 260) ON DUPLICATE KEY UPDATE rule=rule;

 UPDATE `cloud`.`async_job` SET `removed` = now() WHERE `removed` IS NULL;

 -- PR#1448 update description of 'execute.in.sequence.network.element.commands' parameter to reflect an unused command that has been removed. The removed class command is 'UserDataCommand'.
 update `cloud`.`configuration` set description = 'If set to true, DhcpEntryCommand, SavePasswordCommand, VmDataCommand will be synchronized on the agent side. If set to false, these commands become asynchronous. Default value is false.' where name = 'execute.in.sequence.network.element.commands';

 INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Storage', 'DEFAULT', 'StorageManager', 'kvm.storage.offline.migration.wait', '10800', 'Timeout in seconds for offline (non-live) storage migration to complete on KVM', '10800', null, 'Global', 0);
 INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Storage', 'DEFAULT', 'StorageManager', 'kvm.storage.online.migration.wait', '10800', 'Timeout in seconds for online (live) storage migration to complete on KVM (migrateVirtualMachineWithVolume)', '10800', null, 'Global', 0);
 INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Storage', 'DEFAULT', 'StorageManager', 'max.number.managed.clustered.file.systems', '200', 'XenServer and VMware only: Maximum number of managed SRs or datastores per compute cluster', '200', null, 'Cluster', 0);

 -- add KVM Guest OS mapping for Windows Server 2019
 INSERT IGNORE INTO `cloud`.`guest_os` (id, uuid, category_id, display_name, created) VALUES (276, UUID(), 6, 'Windows Server 2019 (64-bit)', now());
 INSERT IGNORE INTO `cloud`.`guest_os_hypervisor` (uuid, hypervisor_type, hypervisor_version, guest_os_name, guest_os_id, created, is_user_defined) VALUES (UUID(), 'KVM', 'default', 'Windows Server 2019', 276, now(), 0);

 -- changed fingerprint type to TEXT, it avoids db exception when creating the certificate issue #3123
 ALTER TABLE `cloud`.`sslcerts` MODIFY `fingerprint` TEXT;

 -- add KVM / qemu io bursting options PR 3133
 alter table `cloud`.`disk_offering` add `bytes_read_rate_max` bigint(20) default null after `bytes_read_rate`;
 alter table `cloud`.`disk_offering` add `bytes_read_rate_max_length` bigint(20) default null after `bytes_read_rate_max`;
 alter table `cloud`.`disk_offering` add `bytes_write_rate_max` bigint(20) default null after `bytes_write_rate`;
 alter table `cloud`.`disk_offering` add `bytes_write_rate_max_length` bigint(20) default null after `bytes_write_rate_max`;
 alter table `cloud`.`disk_offering` add `iops_read_rate_max` bigint(20) default null after `iops_read_rate`;
 alter table `cloud`.`disk_offering` add `iops_read_rate_max_length` bigint(20) default null after `iops_read_rate_max`;
 alter table `cloud`.`disk_offering` add `iops_write_rate_max` bigint(20) default null after `iops_write_rate`;
 alter table `cloud`.`disk_offering` add `iops_write_rate_max_length` bigint(20) default null after `iops_write_rate_max`;

 ALTER VIEW `cloud`.`disk_offering_view` AS
     SELECT
         `disk_offering`.`id` AS `id`,
         `disk_offering`.`uuid` AS `uuid`,
         `disk_offering`.`name` AS `name`,
         `disk_offering`.`display_text` AS `display_text`,
         `disk_offering`.`provisioning_type` AS `provisioning_type`,
         `disk_offering`.`disk_size` AS `disk_size`,
         `disk_offering`.`min_iops` AS `min_iops`,
         `disk_offering`.`max_iops` AS `max_iops`,
         `disk_offering`.`created` AS `created`,
         `disk_offering`.`tags` AS `tags`,
         `disk_offering`.`customized` AS `customized`,
         `disk_offering`.`customized_iops` AS `customized_iops`,
         `disk_offering`.`removed` AS `removed`,
         `disk_offering`.`use_local_storage` AS `use_local_storage`,
         `disk_offering`.`system_use` AS `system_use`,
         `disk_offering`.`hv_ss_reserve` AS `hv_ss_reserve`,
         `disk_offering`.`bytes_read_rate` AS `bytes_read_rate`,
         `disk_offering`.`bytes_read_rate_max` AS `bytes_read_rate_max`,
         `disk_offering`.`bytes_read_rate_max_length` AS `bytes_read_rate_max_length`,
         `disk_offering`.`bytes_write_rate` AS `bytes_write_rate`,
         `disk_offering`.`bytes_write_rate_max` AS `bytes_write_rate_max`,
         `disk_offering`.`bytes_write_rate_max_length` AS `bytes_write_rate_max_length`,
         `disk_offering`.`iops_read_rate` AS `iops_read_rate`,
         `disk_offering`.`iops_read_rate_max` AS `iops_read_rate_max`,
         `disk_offering`.`iops_read_rate_max_length` AS `iops_read_rate_max_length`,
         `disk_offering`.`iops_write_rate` AS `iops_write_rate`,
         `disk_offering`.`iops_write_rate_max` AS `iops_write_rate_max`,
         `disk_offering`.`iops_write_rate_max_length` AS `iops_write_rate_max_length`,
         `disk_offering`.`cache_mode` AS `cache_mode`,
         `disk_offering`.`sort_key` AS `sort_key`,
         `disk_offering`.`type` AS `type`,
         `disk_offering`.`display_offering` AS `display_offering`,
         `domain`.`id` AS `domain_id`,
         `domain`.`uuid` AS `domain_uuid`,
         `domain`.`name` AS `domain_name`,
         `domain`.`path` AS `domain_path`
     FROM
         (`disk_offering`
         LEFT JOIN `domain` ON ((`disk_offering`.`domain_id` = `domain`.`id`)))
     WHERE
         (`disk_offering`.`state` = 'ACTIVE');


 ALTER VIEW `cloud`.`service_offering_view` AS
     SELECT
         `service_offering`.`id` AS `id`,
         `disk_offering`.`uuid` AS `uuid`,
         `disk_offering`.`name` AS `name`,
         `disk_offering`.`display_text` AS `display_text`,
         `disk_offering`.`provisioning_type` AS `provisioning_type`,
         `disk_offering`.`created` AS `created`,
         `disk_offering`.`tags` AS `tags`,
         `disk_offering`.`removed` AS `removed`,
         `disk_offering`.`use_local_storage` AS `use_local_storage`,
         `disk_offering`.`system_use` AS `system_use`,
         `disk_offering`.`customized_iops` AS `customized_iops`,
         `disk_offering`.`min_iops` AS `min_iops`,
         `disk_offering`.`max_iops` AS `max_iops`,
         `disk_offering`.`hv_ss_reserve` AS `hv_ss_reserve`,
         `disk_offering`.`bytes_read_rate` AS `bytes_read_rate`,
         `disk_offering`.`bytes_read_rate_max` AS `bytes_read_rate_max`,
         `disk_offering`.`bytes_read_rate_max_length` AS `bytes_read_rate_max_length`,
         `disk_offering`.`bytes_write_rate` AS `bytes_write_rate`,
         `disk_offering`.`bytes_write_rate_max` AS `bytes_write_rate_max`,
         `disk_offering`.`bytes_write_rate_max_length` AS `bytes_write_rate_max_length`,
         `disk_offering`.`iops_read_rate` AS `iops_read_rate`,
         `disk_offering`.`iops_read_rate_max` AS `iops_read_rate_max`,
         `disk_offering`.`iops_read_rate_max_length` AS `iops_read_rate_max_length`,
         `disk_offering`.`iops_write_rate` AS `iops_write_rate`,
         `disk_offering`.`iops_write_rate_max` AS `iops_write_rate_max`,
         `disk_offering`.`iops_write_rate_max_length` AS `iops_write_rate_max_length`,
         `disk_offering`.`cache_mode` AS `cache_mode`,
         `service_offering`.`cpu` AS `cpu`,
         `service_offering`.`speed` AS `speed`,
         `service_offering`.`ram_size` AS `ram_size`,
         `service_offering`.`nw_rate` AS `nw_rate`,
         `service_offering`.`mc_rate` AS `mc_rate`,
         `service_offering`.`ha_enabled` AS `ha_enabled`,
         `service_offering`.`limit_cpu_use` AS `limit_cpu_use`,
         `service_offering`.`host_tag` AS `host_tag`,
         `service_offering`.`default_use` AS `default_use`,
         `service_offering`.`vm_type` AS `vm_type`,
         `service_offering`.`sort_key` AS `sort_key`,
         `service_offering`.`is_volatile` AS `is_volatile`,
         `service_offering`.`deployment_planner` AS `deployment_planner`,
         `domain`.`id` AS `domain_id`,
         `domain`.`uuid` AS `domain_uuid`,
         `domain`.`name` AS `domain_name`,
         `domain`.`path` AS `domain_path`
     FROM
         ((`service_offering`
         JOIN `disk_offering` ON ((`service_offering`.`id` = `disk_offering`.`id`)))
         LEFT JOIN `domain` ON ((`disk_offering`.`domain_id` = `domain`.`id`)))
     WHERE
         (`disk_offering`.`state` = 'Active');

 -- PR#2578 New column for listManagementServers API call
 ALTER TABLE `mshost` ADD COLUMN `uuid` varchar(40) AFTER `name`;
	-- Licensed to the Apache Software Foundation (ASF) under one
	-- or more contributor license agreements. See the NOTICE file
	-- distributed with this work for additional information
	-- regarding copyright ownership. The ASF licenses this file
	-- to you under the Apache License, Version 2.0 (the
	-- "License"); you may not use this file except in compliance
	-- with the License. You may obtain a copy of the License at
	--
	-- http://www.apache.org/licenses/LICENSE-2.0
	--
	-- Unless required by applicable law or agreed to in writing,
	-- software distributed under the License is distributed on an
	-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
	-- KIND, either express or implied. See the License for the
	-- specific language governing permissions and limitations
	-- under the License.

	--;
	-- Schema upgrade from 4.11.2.0 to 4.12.0.0
	--;

	-- [CLOUDSTACK-10314] Add reason column to ACL rule table
	ALTER TABLE `cloud`.`network_acl_item` ADD COLUMN `reason` VARCHAR(2500) AFTER `display`;

	-- [CLOUDSTACK-9846] Make provision to store content and subject for Alerts in separate columns.
	ALTER TABLE `cloud`.`alert` ADD COLUMN `content` VARCHAR(5000);

	-- Fix the name of the column used to hold IPv4 range in 'vlan' table.
	ALTER TABLE `vlan` CHANGE `description` `ip4_range` varchar(255);

	-- [CLOUDSTACK-10344] bug when moving ACL rules (change order with drag and drop)
	-- We are only adding the permission to the default rules. Any custom rule must be configured by the root admin.
	INSERT INTO `cloud`.`role_permissions` (`uuid`, `role_id`, `rule`, `permission`, `sort_order`) values (UUID(), 2, 'moveNetworkAclItem', 'ALLOW', 100) ON DUPLICATE KEY UPDATE rule=rule;
	INSERT INTO `cloud`.`role_permissions` (`uuid`, `role_id`, `rule`, `permission`, `sort_order`) values (UUID(), 3, 'moveNetworkAclItem', 'ALLOW', 302) ON DUPLICATE KEY UPDATE rule=rule;
	INSERT INTO `cloud`.`role_permissions` (`uuid`, `role_id`, `rule`, `permission`, `sort_order`) values (UUID(), 4, 'moveNetworkAclItem', 'ALLOW', 260) ON DUPLICATE KEY UPDATE rule=rule;

	UPDATE `cloud`.`async_job` SET `removed` = now() WHERE `removed` IS NULL;

	-- PR#1448 update description of 'execute.in.sequence.network.element.commands' parameter to reflect an unused command that has been removed. The removed class command is 'UserDataCommand'.
	update `cloud`.`configuration` set description = 'If set to true, DhcpEntryCommand, SavePasswordCommand, VmDataCommand will be synchronized on the agent side. If set to false, these commands become asynchronous. Default value is false.' where name = 'execute.in.sequence.network.element.commands';

	INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Storage', 'DEFAULT', 'StorageManager', 'kvm.storage.offline.migration.wait', '10800', 'Timeout in seconds for offline (non-live) storage migration to complete on KVM', '10800', null, 'Global', 0);
	INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Storage', 'DEFAULT', 'StorageManager', 'kvm.storage.online.migration.wait', '10800', 'Timeout in seconds for online (live) storage migration to complete on KVM (migrateVirtualMachineWithVolume)', '10800', null, 'Global', 0);
	INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Storage', 'DEFAULT', 'StorageManager', 'max.number.managed.clustered.file.systems', '200', 'XenServer and VMware only: Maximum number of managed SRs or datastores per compute cluster', '200', null, 'Cluster', 0);

	-- add KVM Guest OS mapping for Windows Server 2019
	INSERT IGNORE INTO `cloud`.`guest_os` (id, uuid, category_id, display_name, created) VALUES (276, UUID(), 6, 'Windows Server 2019 (64-bit)', now());
	INSERT IGNORE INTO `cloud`.`guest_os_hypervisor` (uuid, hypervisor_type, hypervisor_version, guest_os_name, guest_os_id, created, is_user_defined) VALUES (UUID(), 'KVM', 'default', 'Windows Server 2019', 276, now(), 0);

	-- changed fingerprint type to TEXT, it avoids db exception when creating the certificate issue #3123
	ALTER TABLE `cloud`.`sslcerts` MODIFY `fingerprint` TEXT;

	-- add KVM / qemu io bursting options PR 3133
	alter table `cloud`.`disk_offering` add `bytes_read_rate_max` bigint(20) default null after `bytes_read_rate`;
	alter table `cloud`.`disk_offering` add `bytes_read_rate_max_length` bigint(20) default null after `bytes_read_rate_max`;
	alter table `cloud`.`disk_offering` add `bytes_write_rate_max` bigint(20) default null after `bytes_write_rate`;
	alter table `cloud`.`disk_offering` add `bytes_write_rate_max_length` bigint(20) default null after `bytes_write_rate_max`;
	alter table `cloud`.`disk_offering` add `iops_read_rate_max` bigint(20) default null after `iops_read_rate`;
	alter table `cloud`.`disk_offering` add `iops_read_rate_max_length` bigint(20) default null after `iops_read_rate_max`;
	alter table `cloud`.`disk_offering` add `iops_write_rate_max` bigint(20) default null after `iops_write_rate`;
	alter table `cloud`.`disk_offering` add `iops_write_rate_max_length` bigint(20) default null after `iops_write_rate_max`;

	ALTER VIEW `cloud`.`disk_offering_view` AS
	SELECT
	`disk_offering`.`id` AS `id`,
	`disk_offering`.`uuid` AS `uuid`,
	`disk_offering`.`name` AS `name`,
	`disk_offering`.`display_text` AS `display_text`,
	`disk_offering`.`provisioning_type` AS `provisioning_type`,
	`disk_offering`.`disk_size` AS `disk_size`,
	`disk_offering`.`min_iops` AS `min_iops`,
	`disk_offering`.`max_iops` AS `max_iops`,
	`disk_offering`.`created` AS `created`,
	`disk_offering`.`tags` AS `tags`,
	`disk_offering`.`customized` AS `customized`,
	`disk_offering`.`customized_iops` AS `customized_iops`,
	`disk_offering`.`removed` AS `removed`,
	`disk_offering`.`use_local_storage` AS `use_local_storage`,
	`disk_offering`.`system_use` AS `system_use`,
	`disk_offering`.`hv_ss_reserve` AS `hv_ss_reserve`,
	`disk_offering`.`bytes_read_rate` AS `bytes_read_rate`,
	`disk_offering`.`bytes_read_rate_max` AS `bytes_read_rate_max`,
	`disk_offering`.`bytes_read_rate_max_length` AS `bytes_read_rate_max_length`,
	`disk_offering`.`bytes_write_rate` AS `bytes_write_rate`,
	`disk_offering`.`bytes_write_rate_max` AS `bytes_write_rate_max`,
	`disk_offering`.`bytes_write_rate_max_length` AS `bytes_write_rate_max_length`,
	`disk_offering`.`iops_read_rate` AS `iops_read_rate`,
	`disk_offering`.`iops_read_rate_max` AS `iops_read_rate_max`,
	`disk_offering`.`iops_read_rate_max_length` AS `iops_read_rate_max_length`,
	`disk_offering`.`iops_write_rate` AS `iops_write_rate`,
	`disk_offering`.`iops_write_rate_max` AS `iops_write_rate_max`,
	`disk_offering`.`iops_write_rate_max_length` AS `iops_write_rate_max_length`,
	`disk_offering`.`cache_mode` AS `cache_mode`,
	`disk_offering`.`sort_key` AS `sort_key`,
	`disk_offering`.`type` AS `type`,
	`disk_offering`.`display_offering` AS `display_offering`,
	`domain`.`id` AS `domain_id`,
	`domain`.`uuid` AS `domain_uuid`,
	`domain`.`name` AS `domain_name`,
	`domain`.`path` AS `domain_path`
	FROM
	(`disk_offering`
	LEFT JOIN `domain` ON ((`disk_offering`.`domain_id` = `domain`.`id`)))
	WHERE
	(`disk_offering`.`state` = 'ACTIVE');


	ALTER VIEW `cloud`.`service_offering_view` AS
	SELECT
	`service_offering`.`id` AS `id`,
	`disk_offering`.`uuid` AS `uuid`,
	`disk_offering`.`name` AS `name`,
	`disk_offering`.`display_text` AS `display_text`,
	`disk_offering`.`provisioning_type` AS `provisioning_type`,
	`disk_offering`.`created` AS `created`,
	`disk_offering`.`tags` AS `tags`,
	`disk_offering`.`removed` AS `removed`,
	`disk_offering`.`use_local_storage` AS `use_local_storage`,
	`disk_offering`.`system_use` AS `system_use`,
	`disk_offering`.`customized_iops` AS `customized_iops`,
	`disk_offering`.`min_iops` AS `min_iops`,
	`disk_offering`.`max_iops` AS `max_iops`,
	`disk_offering`.`hv_ss_reserve` AS `hv_ss_reserve`,
	`disk_offering`.`bytes_read_rate` AS `bytes_read_rate`,
	`disk_offering`.`bytes_read_rate_max` AS `bytes_read_rate_max`,
	`disk_offering`.`bytes_read_rate_max_length` AS `bytes_read_rate_max_length`,
	`disk_offering`.`bytes_write_rate` AS `bytes_write_rate`,
	`disk_offering`.`bytes_write_rate_max` AS `bytes_write_rate_max`,
	`disk_offering`.`bytes_write_rate_max_length` AS `bytes_write_rate_max_length`,
	`disk_offering`.`iops_read_rate` AS `iops_read_rate`,
	`disk_offering`.`iops_read_rate_max` AS `iops_read_rate_max`,
	`disk_offering`.`iops_read_rate_max_length` AS `iops_read_rate_max_length`,
	`disk_offering`.`iops_write_rate` AS `iops_write_rate`,
	`disk_offering`.`iops_write_rate_max` AS `iops_write_rate_max`,
	`disk_offering`.`iops_write_rate_max_length` AS `iops_write_rate_max_length`,
	`disk_offering`.`cache_mode` AS `cache_mode`,
	`service_offering`.`cpu` AS `cpu`,
	`service_offering`.`speed` AS `speed`,
	`service_offering`.`ram_size` AS `ram_size`,
	`service_offering`.`nw_rate` AS `nw_rate`,
	`service_offering`.`mc_rate` AS `mc_rate`,
	`service_offering`.`ha_enabled` AS `ha_enabled`,
	`service_offering`.`limit_cpu_use` AS `limit_cpu_use`,
	`service_offering`.`host_tag` AS `host_tag`,
	`service_offering`.`default_use` AS `default_use`,
	`service_offering`.`vm_type` AS `vm_type`,
	`service_offering`.`sort_key` AS `sort_key`,
	`service_offering`.`is_volatile` AS `is_volatile`,
	`service_offering`.`deployment_planner` AS `deployment_planner`,
	`domain`.`id` AS `domain_id`,
	`domain`.`uuid` AS `domain_uuid`,
	`domain`.`name` AS `domain_name`,
	`domain`.`path` AS `domain_path`
	FROM
	((`service_offering`
	JOIN `disk_offering` ON ((`service_offering`.`id` = `disk_offering`.`id`)))
	LEFT JOIN `domain` ON ((`disk_offering`.`domain_id` = `domain`.`id`)))
	WHERE
	(`disk_offering`.`state` = 'Active');

	-- PR#2578 New column for listManagementServers API call
	ALTER TABLE `mshost` ADD COLUMN `uuid` varchar(40) AFTER `name`;