This is an automated email from the ASF dual-hosted git repository. nvazquez pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/cloudstack.git
commit eb641417850c8826b84873861ea1e96cd2662c71 Merge: 56f4da6 e507b57 Author: nicolas <[email protected]> AuthorDate: Thu Sep 2 12:16:06 2021 -0300 Merge branch '4.15' into main .../com/cloud/upgrade/DatabaseUpgradeChecker.java | 7 ++- .../com/cloud/upgrade/dao/Upgrade41510to41520.java | 66 ++++++++++++++++++++++ ...e41510to41600.java => Upgrade41520to41600.java} | 10 ++-- ...cleanup.sql => schema-41510to41520-cleanup.sql} | 2 +- ...to41600-cleanup.sql => schema-41510to41520.sql} | 2 +- ...cleanup.sql => schema-41520to41600-cleanup.sql} | 2 +- ...ma-41510to41600.sql => schema-41520to41600.sql} | 2 +- 7 files changed, 79 insertions(+), 12 deletions(-) diff --cc engine/schema/src/main/java/com/cloud/upgrade/DatabaseUpgradeChecker.java index 867fc02,5cc1be8..9925044 --- a/engine/schema/src/main/java/com/cloud/upgrade/DatabaseUpgradeChecker.java +++ b/engine/schema/src/main/java/com/cloud/upgrade/DatabaseUpgradeChecker.java @@@ -71,7 -72,6 +72,7 @@@ import com.cloud.upgrade.dao.Upgrade413 import com.cloud.upgrade.dao.Upgrade41310to41400; import com.cloud.upgrade.dao.Upgrade41400to41500; import com.cloud.upgrade.dao.Upgrade41500to41510; - import com.cloud.upgrade.dao.Upgrade41510to41600; ++import com.cloud.upgrade.dao.Upgrade41520to41600; import com.cloud.upgrade.dao.Upgrade420to421; import com.cloud.upgrade.dao.Upgrade421to430; import com.cloud.upgrade.dao.Upgrade430to440; @@@ -198,8 -198,7 +199,8 @@@ public class DatabaseUpgradeChecker imp .next("4.14.0.0", new Upgrade41400to41500()) .next("4.14.1.0", new Upgrade41400to41500()) .next("4.15.0.0", new Upgrade41500to41510()) - .next("4.15.1.0", new Upgrade41510to41600()) - .next("4.15.2.0", new Upgrade41510to41600()) + .next("4.15.1.0", new Upgrade41510to41520()) ++ .next("4.15.2.0", new Upgrade41520to41600()) .build(); } diff --cc engine/schema/src/main/java/com/cloud/upgrade/dao/Upgrade41520to41600.java index 358fa57,0000000..eea3a58 mode 100644,000000..100644 --- a/engine/schema/src/main/java/com/cloud/upgrade/dao/Upgrade41520to41600.java +++ b/engine/schema/src/main/java/com/cloud/upgrade/dao/Upgrade41520to41600.java @@@ -1,248 -1,0 +1,248 @@@ +// 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. + +package com.cloud.upgrade.dao; + +import java.io.InputStream; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.HashMap; +import java.util.HashSet; +import java.util.Map; +import java.util.Set; + +import org.apache.log4j.Logger; + +import com.cloud.hypervisor.Hypervisor; +import com.cloud.utils.exception.CloudRuntimeException; + - public class Upgrade41510to41600 implements DbUpgrade, DbUpgradeSystemVmTemplate { ++public class Upgrade41520to41600 implements DbUpgrade, DbUpgradeSystemVmTemplate { + - final static Logger LOG = Logger.getLogger(Upgrade41510to41600.class); ++ final static Logger LOG = Logger.getLogger(Upgrade41520to41600.class); + + @Override + public String[] getUpgradableVersionRange() { - return new String[] {"4.15.1.0", "4.16.0.0"}; ++ return new String[] {"4.15.2.0", "4.16.0.0"}; + } + + @Override + public String getUpgradedVersion() { + return "4.16.0.0"; + } + + @Override + public boolean supportsRollingUpgrade() { + return false; + } + + @Override + public InputStream[] getPrepareScripts() { - final String scriptFile = "META-INF/db/schema-41510to41600.sql"; ++ final String scriptFile = "META-INF/db/schema-41520to41600.sql"; + final InputStream script = Thread.currentThread().getContextClassLoader().getResourceAsStream(scriptFile); + if (script == null) { + throw new CloudRuntimeException("Unable to find " + scriptFile); + } + + return new InputStream[] {script}; + } + + @Override + public void performDataMigration(Connection conn) { + } + + @Override + @SuppressWarnings("serial") + public void updateSystemVmTemplates(final Connection conn) { + LOG.debug("Updating System Vm template IDs"); + final Set<Hypervisor.HypervisorType> hypervisorsListInUse = new HashSet<Hypervisor.HypervisorType>(); + try (PreparedStatement pstmt = conn.prepareStatement("select distinct(hypervisor_type) from `cloud`.`cluster` where removed is null"); ResultSet rs = pstmt.executeQuery()) { + while (rs.next()) { + switch (Hypervisor.HypervisorType.getType(rs.getString(1))) { + case XenServer: + hypervisorsListInUse.add(Hypervisor.HypervisorType.XenServer); + break; + case KVM: + hypervisorsListInUse.add(Hypervisor.HypervisorType.KVM); + break; + case VMware: + hypervisorsListInUse.add(Hypervisor.HypervisorType.VMware); + break; + case Hyperv: + hypervisorsListInUse.add(Hypervisor.HypervisorType.Hyperv); + break; + case LXC: + hypervisorsListInUse.add(Hypervisor.HypervisorType.LXC); + break; + case Ovm3: + hypervisorsListInUse.add(Hypervisor.HypervisorType.Ovm3); + break; + default: + break; + } + } + } catch (final SQLException e) { + LOG.error("updateSystemVmTemplates: Exception caught while getting hypervisor types from clusters: " + e.getMessage()); + throw new CloudRuntimeException("updateSystemVmTemplates:Exception while getting hypervisor types from clusters", e); + } + + final Map<Hypervisor.HypervisorType, String> NewTemplateNameList = new HashMap<Hypervisor.HypervisorType, String>() { + { + put(Hypervisor.HypervisorType.KVM, "systemvm-kvm-4.16.0"); + put(Hypervisor.HypervisorType.VMware, "systemvm-vmware-4.16.0"); + put(Hypervisor.HypervisorType.XenServer, "systemvm-xenserver-4.16.0"); + put(Hypervisor.HypervisorType.Hyperv, "systemvm-hyperv-4.16.0"); + put(Hypervisor.HypervisorType.LXC, "systemvm-lxc-4.16.0"); + put(Hypervisor.HypervisorType.Ovm3, "systemvm-ovm3-4.16.0"); + } + }; + + final Map<Hypervisor.HypervisorType, String> routerTemplateConfigurationNames = new HashMap<Hypervisor.HypervisorType, String>() { + { + put(Hypervisor.HypervisorType.KVM, "router.template.kvm"); + put(Hypervisor.HypervisorType.VMware, "router.template.vmware"); + put(Hypervisor.HypervisorType.XenServer, "router.template.xenserver"); + put(Hypervisor.HypervisorType.Hyperv, "router.template.hyperv"); + put(Hypervisor.HypervisorType.LXC, "router.template.lxc"); + put(Hypervisor.HypervisorType.Ovm3, "router.template.ovm3"); + } + }; + + final Map<Hypervisor.HypervisorType, String> newTemplateUrl = new HashMap<Hypervisor.HypervisorType, String>() { + { + put(Hypervisor.HypervisorType.KVM, "https://download.cloudstack.org/systemvm/4.16/systemvmtemplate-4.16.0-kvm.qcow2.bz2";); + put(Hypervisor.HypervisorType.VMware, "https://download.cloudstack.org/systemvm/4.16/systemvmtemplate-4.16.0-vmware.ova";); + put(Hypervisor.HypervisorType.XenServer, "https://download.cloudstack.org/systemvm/4.16/systemvmtemplate-4.16.0-xen.vhd.bz2";); + put(Hypervisor.HypervisorType.Hyperv, "https://download.cloudstack.org/systemvm/4.16/systemvmtemplate-4.16.0-hyperv.vhd.zip";); + put(Hypervisor.HypervisorType.LXC, "https://download.cloudstack.org/systemvm/4.16/systemvmtemplate-4.16.0-kvm.qcow2.bz2";); + put(Hypervisor.HypervisorType.Ovm3, "https://download.cloudstack.org/systemvm/4.16/systemvmtemplate-4.16.0-ovm.raw.bz2";); + } + }; + + final Map<Hypervisor.HypervisorType, String> newTemplateChecksum = new HashMap<Hypervisor.HypervisorType, String>() { + { + put(Hypervisor.HypervisorType.KVM, "81b3e48bb934784a13555a43c5ef5ffb"); + put(Hypervisor.HypervisorType.XenServer, "1b178a5dbdbe090555515340144c6017"); + put(Hypervisor.HypervisorType.VMware, "e6a88e518c57d6f36c096c4204c3417f"); + put(Hypervisor.HypervisorType.Hyperv, "5c94da45337cf3e1910dcbe084d4b9ad"); + put(Hypervisor.HypervisorType.LXC, "81b3e48bb934784a13555a43c5ef5ffb"); + put(Hypervisor.HypervisorType.Ovm3, "875c5c65455fc06c4a012394410db375"); + } + }; + + for (final Map.Entry<Hypervisor.HypervisorType, String> hypervisorAndTemplateName : NewTemplateNameList.entrySet()) { + LOG.debug("Updating " + hypervisorAndTemplateName.getKey() + " System Vms"); + try (PreparedStatement pstmt = conn.prepareStatement("select id from `cloud`.`vm_template` where name = ? and removed is null order by id desc limit 1")) { + // Get systemvm template id for corresponding hypervisor + long templateId = -1; + pstmt.setString(1, hypervisorAndTemplateName.getValue()); + try (ResultSet rs = pstmt.executeQuery()) { + if (rs.next()) { + templateId = rs.getLong(1); + } + } catch (final SQLException e) { + LOG.error("updateSystemVmTemplates: Exception caught while getting ids of templates: " + e.getMessage()); + throw new CloudRuntimeException("updateSystemVmTemplates: Exception caught while getting ids of templates", e); + } + + // change template type to SYSTEM + if (templateId != -1) { + try (PreparedStatement templ_type_pstmt = conn.prepareStatement("update `cloud`.`vm_template` set type='SYSTEM' where id = ?");) { + templ_type_pstmt.setLong(1, templateId); + templ_type_pstmt.executeUpdate(); + } catch (final SQLException e) { + LOG.error("updateSystemVmTemplates:Exception while updating template with id " + templateId + " to be marked as 'system': " + e.getMessage()); + throw new CloudRuntimeException("updateSystemVmTemplates:Exception while updating template with id " + templateId + " to be marked as 'system'", e); + } + // update template ID of system Vms + try (PreparedStatement update_templ_id_pstmt = conn + .prepareStatement("update `cloud`.`vm_instance` set vm_template_id = ? where type <> 'User' and hypervisor_type = ? and removed is NULL");) { + update_templ_id_pstmt.setLong(1, templateId); + update_templ_id_pstmt.setString(2, hypervisorAndTemplateName.getKey().toString()); + update_templ_id_pstmt.executeUpdate(); + } catch (final Exception e) { + LOG.error("updateSystemVmTemplates:Exception while setting template for " + hypervisorAndTemplateName.getKey().toString() + " to " + templateId + + ": " + e.getMessage()); + throw new CloudRuntimeException("updateSystemVmTemplates:Exception while setting template for " + hypervisorAndTemplateName.getKey().toString() + " to " + + templateId, e); + } + + // Change value of global configuration parameter + // router.template.* for the corresponding hypervisor + try (PreparedStatement update_pstmt = conn.prepareStatement("UPDATE `cloud`.`configuration` SET value = ? WHERE name = ?");) { + update_pstmt.setString(1, hypervisorAndTemplateName.getValue()); + update_pstmt.setString(2, routerTemplateConfigurationNames.get(hypervisorAndTemplateName.getKey())); + update_pstmt.executeUpdate(); + } catch (final SQLException e) { + LOG.error("updateSystemVmTemplates:Exception while setting " + routerTemplateConfigurationNames.get(hypervisorAndTemplateName.getKey()) + " to " + + hypervisorAndTemplateName.getValue() + ": " + e.getMessage()); + throw new CloudRuntimeException("updateSystemVmTemplates:Exception while setting " + + routerTemplateConfigurationNames.get(hypervisorAndTemplateName.getKey()) + " to " + hypervisorAndTemplateName.getValue(), e); + } + + // Change value of global configuration parameter + // minreq.sysvmtemplate.version for the ACS version + try (PreparedStatement update_pstmt = conn.prepareStatement("UPDATE `cloud`.`configuration` SET value = ? WHERE name = ?");) { + update_pstmt.setString(1, "4.16.0"); + update_pstmt.setString(2, "minreq.sysvmtemplate.version"); + update_pstmt.executeUpdate(); + } catch (final SQLException e) { + LOG.error("updateSystemVmTemplates:Exception while setting 'minreq.sysvmtemplate.version' to 4.16.0: " + e.getMessage()); + throw new CloudRuntimeException("updateSystemVmTemplates:Exception while setting 'minreq.sysvmtemplate.version' to 4.16.0", e); + } + } else { + if (hypervisorsListInUse.contains(hypervisorAndTemplateName.getKey())) { + throw new CloudRuntimeException(getUpgradedVersion() + hypervisorAndTemplateName.getKey() + " SystemVm template not found. Cannot upgrade system Vms"); + } else { + LOG.warn(getUpgradedVersion() + hypervisorAndTemplateName.getKey() + " SystemVm template not found. " + hypervisorAndTemplateName.getKey() + + " hypervisor is not used, so not failing upgrade"); + // Update the latest template URLs for corresponding + // hypervisor + try (PreparedStatement update_templ_url_pstmt = conn + .prepareStatement("UPDATE `cloud`.`vm_template` SET url = ? , checksum = ? WHERE hypervisor_type = ? AND type = 'SYSTEM' AND removed is null order by id desc limit 1");) { + update_templ_url_pstmt.setString(1, newTemplateUrl.get(hypervisorAndTemplateName.getKey())); + update_templ_url_pstmt.setString(2, newTemplateChecksum.get(hypervisorAndTemplateName.getKey())); + update_templ_url_pstmt.setString(3, hypervisorAndTemplateName.getKey().toString()); + update_templ_url_pstmt.executeUpdate(); + } catch (final SQLException e) { + LOG.error("updateSystemVmTemplates:Exception while updating 'url' and 'checksum' for hypervisor type " + + hypervisorAndTemplateName.getKey().toString() + ": " + e.getMessage()); + throw new CloudRuntimeException("updateSystemVmTemplates:Exception while updating 'url' and 'checksum' for hypervisor type " + + hypervisorAndTemplateName.getKey().toString(), e); + } + } + } + } catch (final SQLException e) { + LOG.error("updateSystemVmTemplates:Exception while getting ids of templates: " + e.getMessage()); + throw new CloudRuntimeException("updateSystemVmTemplates:Exception while getting ids of templates", e); + } + } + LOG.debug("Updating System Vm Template IDs Complete"); + } + + @Override + public InputStream[] getCleanupScripts() { - final String scriptFile = "META-INF/db/schema-41510to41600-cleanup.sql"; ++ final String scriptFile = "META-INF/db/schema-41520to41600-cleanup.sql"; + final InputStream script = Thread.currentThread().getContextClassLoader().getResourceAsStream(scriptFile); + if (script == null) { + throw new CloudRuntimeException("Unable to find " + scriptFile); + } + + return new InputStream[] {script}; + } +} diff --cc engine/schema/src/main/resources/META-INF/db/schema-41520to41600-cleanup.sql index bbba515,0000000..e69d34f mode 100644,000000..100644 --- a/engine/schema/src/main/resources/META-INF/db/schema-41520to41600-cleanup.sql +++ b/engine/schema/src/main/resources/META-INF/db/schema-41520to41600-cleanup.sql @@@ -1,21 -1,0 +1,21 @@@ +-- 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 cleanup from 4.15.1.0 to 4.16.0.0 ++-- Schema upgrade cleanup from 4.15.2.0 to 4.16.0.0 +--; + diff --cc engine/schema/src/main/resources/META-INF/db/schema-41520to41600.sql index cc43cc6,0000000..9a30ed5 mode 100644,000000..100644 --- a/engine/schema/src/main/resources/META-INF/db/schema-41520to41600.sql +++ b/engine/schema/src/main/resources/META-INF/db/schema-41520to41600.sql @@@ -1,759 -1,0 +1,759 @@@ +-- 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.15.1.0 to 4.16.0.0 ++-- Schema upgrade from 4.15.2.0 to 4.16.0.0 +--; + +-- Adding dynamic scalable flag for service offering table +ALTER TABLE `cloud`.`service_offering` ADD COLUMN `dynamic_scaling_enabled` tinyint(1) unsigned NOT NULL DEFAULT 1 COMMENT 'true(1) if VM needs to be dynamically scalable of cpu or memory'; +DROP VIEW IF EXISTS `cloud`.`service_offering_view`; +CREATE 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`, + `disk_offering`.`disk_size` AS `root_disk_size`, + `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`, + `service_offering`.`dynamic_scaling_enabled` AS `dynamic_scaling_enabled`, + `vsphere_storage_policy`.`value` AS `vsphere_storage_policy`, + GROUP_CONCAT(DISTINCT(domain.id)) AS domain_id, + GROUP_CONCAT(DISTINCT(domain.uuid)) AS domain_uuid, + GROUP_CONCAT(DISTINCT(domain.name)) AS domain_name, + GROUP_CONCAT(DISTINCT(domain.path)) AS domain_path, + GROUP_CONCAT(DISTINCT(zone.id)) AS zone_id, + GROUP_CONCAT(DISTINCT(zone.uuid)) AS zone_uuid, + GROUP_CONCAT(DISTINCT(zone.name)) AS zone_name, + IFNULL(`min_compute_details`.`value`, `cpu`) AS min_cpu, + IFNULL(`max_compute_details`.`value`, `cpu`) AS max_cpu, + IFNULL(`min_memory_details`.`value`, `ram_size`) AS min_memory, + IFNULL(`max_memory_details`.`value`, `ram_size`) AS max_memory + FROM + `cloud`.`service_offering` + INNER JOIN + `cloud`.`disk_offering_view` AS `disk_offering` ON service_offering.id = disk_offering.id + LEFT JOIN + `cloud`.`service_offering_details` AS `domain_details` ON `domain_details`.`service_offering_id` = `disk_offering`.`id` AND `domain_details`.`name`='domainid' + LEFT JOIN + `cloud`.`domain` AS `domain` ON FIND_IN_SET(`domain`.`id`, `domain_details`.`value`) + LEFT JOIN + `cloud`.`service_offering_details` AS `zone_details` ON `zone_details`.`service_offering_id` = `disk_offering`.`id` AND `zone_details`.`name`='zoneid' + LEFT JOIN + `cloud`.`data_center` AS `zone` ON FIND_IN_SET(`zone`.`id`, `zone_details`.`value`) + LEFT JOIN + `cloud`.`service_offering_details` AS `min_compute_details` ON `min_compute_details`.`service_offering_id` = `disk_offering`.`id` + AND `min_compute_details`.`name` = 'mincpunumber' + LEFT JOIN + `cloud`.`service_offering_details` AS `max_compute_details` ON `max_compute_details`.`service_offering_id` = `disk_offering`.`id` + AND `max_compute_details`.`name` = 'maxcpunumber' + LEFT JOIN + `cloud`.`service_offering_details` AS `min_memory_details` ON `min_memory_details`.`service_offering_id` = `disk_offering`.`id` + AND `min_memory_details`.`name` = 'minmemory' + LEFT JOIN + `cloud`.`service_offering_details` AS `max_memory_details` ON `max_memory_details`.`service_offering_id` = `disk_offering`.`id` + AND `max_memory_details`.`name` = 'maxmemory' + LEFT JOIN + `cloud`.`service_offering_details` AS `vsphere_storage_policy` ON `vsphere_storage_policy`.`service_offering_id` = `disk_offering`.`id` + AND `vsphere_storage_policy`.`name` = 'storagepolicy' + WHERE + `disk_offering`.`state`='Active' + GROUP BY + `service_offering`.`id`; + +--; +-- Stored procedure to do idempotent column add; +-- This is copied from schema-41000to41100.sql +--; +DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_ADD_COLUMN`; + +CREATE PROCEDURE `cloud`.`IDEMPOTENT_ADD_COLUMN` ( + IN in_table_name VARCHAR(200), + IN in_column_name VARCHAR(200), + IN in_column_definition VARCHAR(1000) +) +BEGIN + + DECLARE CONTINUE HANDLER FOR 1060 BEGIN END; SET @ddl = CONCAT('ALTER TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', 'ADD COLUMN') ; SET @ddl = CONCAT(@ddl, ' ', in_column_name); SET @ddl = CONCAT(@ddl, ' ', in_column_definition); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; END; + +CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.account','created', 'datetime DEFAULT NULL COMMENT ''date created'' AFTER `state` '); +CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.domain','created', 'datetime DEFAULT NULL COMMENT ''date created'' AFTER `next_child_seq` '); +CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud_usage.account','created', 'datetime DEFAULT NULL COMMENT ''date created'' AFTER `state` '); + +DROP VIEW IF EXISTS `cloud`.`account_view`; +CREATE VIEW `cloud`.`account_view` AS +select + `account`.`id` AS `id`, + `account`.`uuid` AS `uuid`, + `account`.`account_name` AS `account_name`, + `account`.`type` AS `type`, + `account`.`role_id` AS `role_id`, + `account`.`state` AS `state`, + `account`.`created` AS `created`, + `account`.`removed` AS `removed`, + `account`.`cleanup_needed` AS `cleanup_needed`, + `account`.`network_domain` AS `network_domain` , + `account`.`default` AS `default`, + `domain`.`id` AS `domain_id`, + `domain`.`uuid` AS `domain_uuid`, + `domain`.`name` AS `domain_name`, + `domain`.`path` AS `domain_path`, + `data_center`.`id` AS `data_center_id`, + `data_center`.`uuid` AS `data_center_uuid`, + `data_center`.`name` AS `data_center_name`, + `account_netstats_view`.`bytesReceived` AS `bytesReceived`, + `account_netstats_view`.`bytesSent` AS `bytesSent`, + `vmlimit`.`max` AS `vmLimit`, + `vmcount`.`count` AS `vmTotal`, + `runningvm`.`vmcount` AS `runningVms`, + `stoppedvm`.`vmcount` AS `stoppedVms`, + `iplimit`.`max` AS `ipLimit`, + `ipcount`.`count` AS `ipTotal`, + `free_ip_view`.`free_ip` AS `ipFree`, + `volumelimit`.`max` AS `volumeLimit`, + `volumecount`.`count` AS `volumeTotal`, + `snapshotlimit`.`max` AS `snapshotLimit`, + `snapshotcount`.`count` AS `snapshotTotal`, + `templatelimit`.`max` AS `templateLimit`, + `templatecount`.`count` AS `templateTotal`, + `vpclimit`.`max` AS `vpcLimit`, + `vpccount`.`count` AS `vpcTotal`, + `projectlimit`.`max` AS `projectLimit`, + `projectcount`.`count` AS `projectTotal`, + `networklimit`.`max` AS `networkLimit`, + `networkcount`.`count` AS `networkTotal`, + `cpulimit`.`max` AS `cpuLimit`, + `cpucount`.`count` AS `cpuTotal`, + `memorylimit`.`max` AS `memoryLimit`, + `memorycount`.`count` AS `memoryTotal`, + `primary_storage_limit`.`max` AS `primaryStorageLimit`, + `primary_storage_count`.`count` AS `primaryStorageTotal`, + `secondary_storage_limit`.`max` AS `secondaryStorageLimit`, + `secondary_storage_count`.`count` AS `secondaryStorageTotal`, + `async_job`.`id` AS `job_id`, + `async_job`.`uuid` AS `job_uuid`, + `async_job`.`job_status` AS `job_status`, + `async_job`.`account_id` AS `job_account_id` +from + `cloud`.`free_ip_view`, + `cloud`.`account` + inner join + `cloud`.`domain` ON account.domain_id = domain.id + left join + `cloud`.`data_center` ON account.default_zone_id = data_center.id + left join + `cloud`.`account_netstats_view` ON account.id = account_netstats_view.account_id + left join + `cloud`.`resource_limit` vmlimit ON account.id = vmlimit.account_id + and vmlimit.type = 'user_vm' + left join + `cloud`.`resource_count` vmcount ON account.id = vmcount.account_id + and vmcount.type = 'user_vm' + left join + `cloud`.`account_vmstats_view` runningvm ON account.id = runningvm.account_id + and runningvm.state = 'Running' + left join + `cloud`.`account_vmstats_view` stoppedvm ON account.id = stoppedvm.account_id + and stoppedvm.state = 'Stopped' + left join + `cloud`.`resource_limit` iplimit ON account.id = iplimit.account_id + and iplimit.type = 'public_ip' + left join + `cloud`.`resource_count` ipcount ON account.id = ipcount.account_id + and ipcount.type = 'public_ip' + left join + `cloud`.`resource_limit` volumelimit ON account.id = volumelimit.account_id + and volumelimit.type = 'volume' + left join + `cloud`.`resource_count` volumecount ON account.id = volumecount.account_id + and volumecount.type = 'volume' + left join + `cloud`.`resource_limit` snapshotlimit ON account.id = snapshotlimit.account_id + and snapshotlimit.type = 'snapshot' + left join + `cloud`.`resource_count` snapshotcount ON account.id = snapshotcount.account_id + and snapshotcount.type = 'snapshot' + left join + `cloud`.`resource_limit` templatelimit ON account.id = templatelimit.account_id + and templatelimit.type = 'template' + left join + `cloud`.`resource_count` templatecount ON account.id = templatecount.account_id + and templatecount.type = 'template' + left join + `cloud`.`resource_limit` vpclimit ON account.id = vpclimit.account_id + and vpclimit.type = 'vpc' + left join + `cloud`.`resource_count` vpccount ON account.id = vpccount.account_id + and vpccount.type = 'vpc' + left join + `cloud`.`resource_limit` projectlimit ON account.id = projectlimit.account_id + and projectlimit.type = 'project' + left join + `cloud`.`resource_count` projectcount ON account.id = projectcount.account_id + and projectcount.type = 'project' + left join + `cloud`.`resource_limit` networklimit ON account.id = networklimit.account_id + and networklimit.type = 'network' + left join + `cloud`.`resource_count` networkcount ON account.id = networkcount.account_id + and networkcount.type = 'network' + left join + `cloud`.`resource_limit` cpulimit ON account.id = cpulimit.account_id + and cpulimit.type = 'cpu' + left join + `cloud`.`resource_count` cpucount ON account.id = cpucount.account_id + and cpucount.type = 'cpu' + left join + `cloud`.`resource_limit` memorylimit ON account.id = memorylimit.account_id + and memorylimit.type = 'memory' + left join + `cloud`.`resource_count` memorycount ON account.id = memorycount.account_id + and memorycount.type = 'memory' + left join + `cloud`.`resource_limit` primary_storage_limit ON account.id = primary_storage_limit.account_id + and primary_storage_limit.type = 'primary_storage' + left join + `cloud`.`resource_count` primary_storage_count ON account.id = primary_storage_count.account_id + and primary_storage_count.type = 'primary_storage' + left join + `cloud`.`resource_limit` secondary_storage_limit ON account.id = secondary_storage_limit.account_id + and secondary_storage_limit.type = 'secondary_storage' + left join + `cloud`.`resource_count` secondary_storage_count ON account.id = secondary_storage_count.account_id + and secondary_storage_count.type = 'secondary_storage' + left join + `cloud`.`async_job` ON async_job.instance_id = account.id + and async_job.instance_type = 'Account' + and async_job.job_status = 0; + + +DROP VIEW IF EXISTS `cloud`.`domain_view`; +CREATE VIEW `cloud`.`domain_view` AS +select + `domain`.`id` AS `id`, + `domain`.`parent` AS `parent`, + `domain`.`name` AS `name`, + `domain`.`uuid` AS `uuid`, + `domain`.`owner` AS `owner`, + `domain`.`path` AS `path`, + `domain`.`level` AS `level`, + `domain`.`child_count` AS `child_count`, + `domain`.`next_child_seq` AS `next_child_seq`, + `domain`.`created` AS `created`, + `domain`.`removed` AS `removed`, + `domain`.`state` AS `state`, + `domain`.`network_domain` AS `network_domain`, + `domain`.`type` AS `type`, + `vmlimit`.`max` AS `vmLimit`, + `vmcount`.`count` AS `vmTotal`, + `iplimit`.`max` AS `ipLimit`, + `ipcount`.`count` AS `ipTotal`, + `volumelimit`.`max` AS `volumeLimit`, + `volumecount`.`count` AS `volumeTotal`, + `snapshotlimit`.`max` AS `snapshotLimit`, + `snapshotcount`.`count` AS `snapshotTotal`, + `templatelimit`.`max` AS `templateLimit`, + `templatecount`.`count` AS `templateTotal`, + `vpclimit`.`max` AS `vpcLimit`, + `vpccount`.`count` AS `vpcTotal`, + `projectlimit`.`max` AS `projectLimit`, + `projectcount`.`count` AS `projectTotal`, + `networklimit`.`max` AS `networkLimit`, + `networkcount`.`count` AS `networkTotal`, + `cpulimit`.`max` AS `cpuLimit`, + `cpucount`.`count` AS `cpuTotal`, + `memorylimit`.`max` AS `memoryLimit`, + `memorycount`.`count` AS `memoryTotal`, + `primary_storage_limit`.`max` AS `primaryStorageLimit`, + `primary_storage_count`.`count` AS `primaryStorageTotal`, + `secondary_storage_limit`.`max` AS `secondaryStorageLimit`, + `secondary_storage_count`.`count` AS `secondaryStorageTotal` +from + `cloud`.`domain` + left join + `cloud`.`resource_limit` vmlimit ON domain.id = vmlimit.domain_id + and vmlimit.type = 'user_vm' + left join + `cloud`.`resource_count` vmcount ON domain.id = vmcount.domain_id + and vmcount.type = 'user_vm' + left join + `cloud`.`resource_limit` iplimit ON domain.id = iplimit.domain_id + and iplimit.type = 'public_ip' + left join + `cloud`.`resource_count` ipcount ON domain.id = ipcount.domain_id + and ipcount.type = 'public_ip' + left join + `cloud`.`resource_limit` volumelimit ON domain.id = volumelimit.domain_id + and volumelimit.type = 'volume' + left join + `cloud`.`resource_count` volumecount ON domain.id = volumecount.domain_id + and volumecount.type = 'volume' + left join + `cloud`.`resource_limit` snapshotlimit ON domain.id = snapshotlimit.domain_id + and snapshotlimit.type = 'snapshot' + left join + `cloud`.`resource_count` snapshotcount ON domain.id = snapshotcount.domain_id + and snapshotcount.type = 'snapshot' + left join + `cloud`.`resource_limit` templatelimit ON domain.id = templatelimit.domain_id + and templatelimit.type = 'template' + left join + `cloud`.`resource_count` templatecount ON domain.id = templatecount.domain_id + and templatecount.type = 'template' + left join + `cloud`.`resource_limit` vpclimit ON domain.id = vpclimit.domain_id + and vpclimit.type = 'vpc' + left join + `cloud`.`resource_count` vpccount ON domain.id = vpccount.domain_id + and vpccount.type = 'vpc' + left join + `cloud`.`resource_limit` projectlimit ON domain.id = projectlimit.domain_id + and projectlimit.type = 'project' + left join + `cloud`.`resource_count` projectcount ON domain.id = projectcount.domain_id + and projectcount.type = 'project' + left join + `cloud`.`resource_limit` networklimit ON domain.id = networklimit.domain_id + and networklimit.type = 'network' + left join + `cloud`.`resource_count` networkcount ON domain.id = networkcount.domain_id + and networkcount.type = 'network' + left join + `cloud`.`resource_limit` cpulimit ON domain.id = cpulimit.domain_id + and cpulimit.type = 'cpu' + left join + `cloud`.`resource_count` cpucount ON domain.id = cpucount.domain_id + and cpucount.type = 'cpu' + left join + `cloud`.`resource_limit` memorylimit ON domain.id = memorylimit.domain_id + and memorylimit.type = 'memory' + left join + `cloud`.`resource_count` memorycount ON domain.id = memorycount.domain_id + and memorycount.type = 'memory' + left join + `cloud`.`resource_limit` primary_storage_limit ON domain.id = primary_storage_limit.domain_id + and primary_storage_limit.type = 'primary_storage' + left join + `cloud`.`resource_count` primary_storage_count ON domain.id = primary_storage_count.domain_id + and primary_storage_count.type = 'primary_storage' + left join + `cloud`.`resource_limit` secondary_storage_limit ON domain.id = secondary_storage_limit.domain_id + and secondary_storage_limit.type = 'secondary_storage' + left join + `cloud`.`resource_count` secondary_storage_count ON domain.id = secondary_storage_count.domain_id + and secondary_storage_count.type = 'secondary_storage'; + + +DROP VIEW IF EXISTS `cloud`.`user_vm_view`; +CREATE + VIEW `user_vm_view` AS +SELECT + `vm_instance`.`id` AS `id`, + `vm_instance`.`name` AS `name`, + `user_vm`.`display_name` AS `display_name`, + `user_vm`.`user_data` AS `user_data`, + `account`.`id` AS `account_id`, + `account`.`uuid` AS `account_uuid`, + `account`.`account_name` AS `account_name`, + `account`.`type` AS `account_type`, + `domain`.`id` AS `domain_id`, + `domain`.`uuid` AS `domain_uuid`, + `domain`.`name` AS `domain_name`, + `domain`.`path` AS `domain_path`, + `projects`.`id` AS `project_id`, + `projects`.`uuid` AS `project_uuid`, + `projects`.`name` AS `project_name`, + `instance_group`.`id` AS `instance_group_id`, + `instance_group`.`uuid` AS `instance_group_uuid`, + `instance_group`.`name` AS `instance_group_name`, + `vm_instance`.`uuid` AS `uuid`, + `vm_instance`.`user_id` AS `user_id`, + `vm_instance`.`last_host_id` AS `last_host_id`, + `vm_instance`.`vm_type` AS `type`, + `vm_instance`.`limit_cpu_use` AS `limit_cpu_use`, + `vm_instance`.`created` AS `created`, + `vm_instance`.`state` AS `state`, + `vm_instance`.`update_time` AS `update_time`, + `vm_instance`.`removed` AS `removed`, + `vm_instance`.`ha_enabled` AS `ha_enabled`, + `vm_instance`.`hypervisor_type` AS `hypervisor_type`, + `vm_instance`.`instance_name` AS `instance_name`, + `vm_instance`.`guest_os_id` AS `guest_os_id`, + `vm_instance`.`display_vm` AS `display_vm`, + `guest_os`.`uuid` AS `guest_os_uuid`, + `vm_instance`.`pod_id` AS `pod_id`, + `host_pod_ref`.`uuid` AS `pod_uuid`, + `vm_instance`.`private_ip_address` AS `private_ip_address`, + `vm_instance`.`private_mac_address` AS `private_mac_address`, + `vm_instance`.`vm_type` AS `vm_type`, + `data_center`.`id` AS `data_center_id`, + `data_center`.`uuid` AS `data_center_uuid`, + `data_center`.`name` AS `data_center_name`, + `data_center`.`is_security_group_enabled` AS `security_group_enabled`, + `data_center`.`networktype` AS `data_center_type`, + `host`.`id` AS `host_id`, + `host`.`uuid` AS `host_uuid`, + `host`.`name` AS `host_name`, + `vm_template`.`id` AS `template_id`, + `vm_template`.`uuid` AS `template_uuid`, + `vm_template`.`name` AS `template_name`, + `vm_template`.`display_text` AS `template_display_text`, + `vm_template`.`enable_password` AS `password_enabled`, + `iso`.`id` AS `iso_id`, + `iso`.`uuid` AS `iso_uuid`, + `iso`.`name` AS `iso_name`, + `iso`.`display_text` AS `iso_display_text`, + `service_offering`.`id` AS `service_offering_id`, + `svc_disk_offering`.`uuid` AS `service_offering_uuid`, + `disk_offering`.`uuid` AS `disk_offering_uuid`, + `disk_offering`.`id` AS `disk_offering_id`, + (CASE + WHEN ISNULL(`service_offering`.`cpu`) THEN `custom_cpu`.`value` + ELSE `service_offering`.`cpu` + END) AS `cpu`, + (CASE + WHEN ISNULL(`service_offering`.`speed`) THEN `custom_speed`.`value` + ELSE `service_offering`.`speed` + END) AS `speed`, + (CASE + WHEN ISNULL(`service_offering`.`ram_size`) THEN `custom_ram_size`.`value` + ELSE `service_offering`.`ram_size` + END) AS `ram_size`, + `backup_offering`.`uuid` AS `backup_offering_uuid`, + `backup_offering`.`id` AS `backup_offering_id`, + `svc_disk_offering`.`name` AS `service_offering_name`, + `disk_offering`.`name` AS `disk_offering_name`, + `backup_offering`.`name` AS `backup_offering_name`, + `storage_pool`.`id` AS `pool_id`, + `storage_pool`.`uuid` AS `pool_uuid`, + `storage_pool`.`pool_type` AS `pool_type`, + `volumes`.`id` AS `volume_id`, + `volumes`.`uuid` AS `volume_uuid`, + `volumes`.`device_id` AS `volume_device_id`, + `volumes`.`volume_type` AS `volume_type`, + `security_group`.`id` AS `security_group_id`, + `security_group`.`uuid` AS `security_group_uuid`, + `security_group`.`name` AS `security_group_name`, + `security_group`.`description` AS `security_group_description`, + `nics`.`id` AS `nic_id`, + `nics`.`uuid` AS `nic_uuid`, + `nics`.`device_id` AS `nic_device_id`, + `nics`.`network_id` AS `network_id`, + `nics`.`ip4_address` AS `ip_address`, + `nics`.`ip6_address` AS `ip6_address`, + `nics`.`ip6_gateway` AS `ip6_gateway`, + `nics`.`ip6_cidr` AS `ip6_cidr`, + `nics`.`default_nic` AS `is_default_nic`, + `nics`.`gateway` AS `gateway`, + `nics`.`netmask` AS `netmask`, + `nics`.`mac_address` AS `mac_address`, + `nics`.`broadcast_uri` AS `broadcast_uri`, + `nics`.`isolation_uri` AS `isolation_uri`, + `vpc`.`id` AS `vpc_id`, + `vpc`.`uuid` AS `vpc_uuid`, + `networks`.`uuid` AS `network_uuid`, + `networks`.`name` AS `network_name`, + `networks`.`traffic_type` AS `traffic_type`, + `networks`.`guest_type` AS `guest_type`, + `user_ip_address`.`id` AS `public_ip_id`, + `user_ip_address`.`uuid` AS `public_ip_uuid`, + `user_ip_address`.`public_ip_address` AS `public_ip_address`, + `ssh_keypairs`.`keypair_name` AS `keypair_name`, + `resource_tags`.`id` AS `tag_id`, + `resource_tags`.`uuid` AS `tag_uuid`, + `resource_tags`.`key` AS `tag_key`, + `resource_tags`.`value` AS `tag_value`, + `resource_tags`.`domain_id` AS `tag_domain_id`, + `domain`.`uuid` AS `tag_domain_uuid`, + `domain`.`name` AS `tag_domain_name`, + `resource_tags`.`account_id` AS `tag_account_id`, + `account`.`account_name` AS `tag_account_name`, + `resource_tags`.`resource_id` AS `tag_resource_id`, + `resource_tags`.`resource_uuid` AS `tag_resource_uuid`, + `resource_tags`.`resource_type` AS `tag_resource_type`, + `resource_tags`.`customer` AS `tag_customer`, + `async_job`.`id` AS `job_id`, + `async_job`.`uuid` AS `job_uuid`, + `async_job`.`job_status` AS `job_status`, + `async_job`.`account_id` AS `job_account_id`, + `affinity_group`.`id` AS `affinity_group_id`, + `affinity_group`.`uuid` AS `affinity_group_uuid`, + `affinity_group`.`name` AS `affinity_group_name`, + `affinity_group`.`description` AS `affinity_group_description`, + `vm_instance`.`dynamically_scalable` AS `dynamically_scalable` +FROM + (((((((((((((((((((((((((((((((((`user_vm` + JOIN `vm_instance` ON (((`vm_instance`.`id` = `user_vm`.`id`) + AND ISNULL(`vm_instance`.`removed`)))) + JOIN `account` ON ((`vm_instance`.`account_id` = `account`.`id`))) + JOIN `domain` ON ((`vm_instance`.`domain_id` = `domain`.`id`))) + LEFT JOIN `guest_os` ON ((`vm_instance`.`guest_os_id` = `guest_os`.`id`))) + LEFT JOIN `host_pod_ref` ON ((`vm_instance`.`pod_id` = `host_pod_ref`.`id`))) + LEFT JOIN `projects` ON ((`projects`.`project_account_id` = `account`.`id`))) + LEFT JOIN `instance_group_vm_map` ON ((`vm_instance`.`id` = `instance_group_vm_map`.`instance_id`))) + LEFT JOIN `instance_group` ON ((`instance_group_vm_map`.`group_id` = `instance_group`.`id`))) + LEFT JOIN `data_center` ON ((`vm_instance`.`data_center_id` = `data_center`.`id`))) + LEFT JOIN `host` ON ((`vm_instance`.`host_id` = `host`.`id`))) + LEFT JOIN `vm_template` ON ((`vm_instance`.`vm_template_id` = `vm_template`.`id`))) + LEFT JOIN `vm_template` `iso` ON ((`iso`.`id` = `user_vm`.`iso_id`))) + LEFT JOIN `service_offering` ON ((`vm_instance`.`service_offering_id` = `service_offering`.`id`))) + LEFT JOIN `disk_offering` `svc_disk_offering` ON ((`vm_instance`.`service_offering_id` = `svc_disk_offering`.`id`))) + LEFT JOIN `disk_offering` ON ((`vm_instance`.`disk_offering_id` = `disk_offering`.`id`))) + LEFT JOIN `backup_offering` ON ((`vm_instance`.`backup_offering_id` = `backup_offering`.`id`))) + LEFT JOIN `volumes` ON ((`vm_instance`.`id` = `volumes`.`instance_id`))) + LEFT JOIN `storage_pool` ON ((`volumes`.`pool_id` = `storage_pool`.`id`))) + LEFT JOIN `security_group_vm_map` ON ((`vm_instance`.`id` = `security_group_vm_map`.`instance_id`))) + LEFT JOIN `security_group` ON ((`security_group_vm_map`.`security_group_id` = `security_group`.`id`))) + LEFT JOIN `nics` ON (((`vm_instance`.`id` = `nics`.`instance_id`) + AND ISNULL(`nics`.`removed`)))) + LEFT JOIN `networks` ON ((`nics`.`network_id` = `networks`.`id`))) + LEFT JOIN `vpc` ON (((`networks`.`vpc_id` = `vpc`.`id`) + AND ISNULL(`vpc`.`removed`)))) + LEFT JOIN `user_ip_address` ON ((`user_ip_address`.`vm_id` = `vm_instance`.`id`))) + LEFT JOIN `user_vm_details` `ssh_details` ON (((`ssh_details`.`vm_id` = `vm_instance`.`id`) + AND (`ssh_details`.`name` = 'SSH.PublicKey')))) + LEFT JOIN `ssh_keypairs` ON (((`ssh_keypairs`.`public_key` = `ssh_details`.`value`) + AND (`ssh_keypairs`.`account_id` = `account`.`id`)))) + LEFT JOIN `resource_tags` ON (((`resource_tags`.`resource_id` = `vm_instance`.`id`) + AND (`resource_tags`.`resource_type` = 'UserVm')))) + LEFT JOIN `async_job` ON (((`async_job`.`instance_id` = `vm_instance`.`id`) + AND (`async_job`.`instance_type` = 'VirtualMachine') + AND (`async_job`.`job_status` = 0)))) + LEFT JOIN `affinity_group_vm_map` ON ((`vm_instance`.`id` = `affinity_group_vm_map`.`instance_id`))) + LEFT JOIN `affinity_group` ON ((`affinity_group_vm_map`.`affinity_group_id` = `affinity_group`.`id`))) + LEFT JOIN `user_vm_details` `custom_cpu` ON (((`custom_cpu`.`vm_id` = `vm_instance`.`id`) + AND (`custom_cpu`.`name` = 'CpuNumber')))) + LEFT JOIN `user_vm_details` `custom_speed` ON (((`custom_speed`.`vm_id` = `vm_instance`.`id`) + AND (`custom_speed`.`name` = 'CpuSpeed')))) + LEFT JOIN `user_vm_details` `custom_ram_size` ON (((`custom_ram_size`.`vm_id` = `vm_instance`.`id`) + AND (`custom_ram_size`.`name` = 'memory')))); + +-- Update name for global configuration user.vm.readonly.ui.details +Update configuration set name='user.vm.readonly.details' where name='user.vm.readonly.ui.details'; + +-- Update name for global configuration 'user.vm.readonly.ui.details' to 'user.vm.denied.details' +UPDATE `cloud`.`configuration` SET name='user.vm.denied.details' WHERE name='user.vm.blacklisted.details'; + +-- Update name for global configuration 'blacklisted.routes' to 'denied.routes' +UPDATE `cloud`.`configuration` SET name='denied.routes', description='Routes that are denied, can not be used for Static Routes creation for the VPC Private Gateway' WHERE name='blacklisted.routes'; + +-- Rename 'master_node_count' to 'control_node_count' in kubernetes_cluster table +ALTER TABLE `cloud`.`kubernetes_cluster` CHANGE master_node_count control_node_count bigint NOT NULL default '0' COMMENT 'the number of the control nodes deployed for this Kubernetes cluster'; + +UPDATE `cloud`.`domain_router` SET redundant_state = 'PRIMARY' WHERE redundant_state = 'MASTER'; + +DROP TABLE IF EXISTS `cloud`.`external_bigswitch_vns_devices`; +DROP TABLE IF EXISTS `cloud`.`template_s3_ref`; +DROP TABLE IF EXISTS `cloud`.`template_swift_ref`; +DROP TABLE IF EXISTS `cloud`.`template_ovf_properties`; +DROP TABLE IF EXISTS `cloud`.`op_host_upgrade`; +DROP TABLE IF EXISTS `cloud`.`stack_maid`; +DROP TABLE IF EXISTS `cloud`.`volume_host_ref`; +DROP TABLE IF EXISTS `cloud`.`template_host_ref`; +DROP TABLE IF EXISTS `cloud`.`swift`; + +ALTER TABLE `cloud`.`snapshots` DROP FOREIGN KEY `fk_snapshots__s3_id` ; +ALTER TABLE `cloud`.`snapshots` DROP COLUMN `s3_id` ; +DROP TABLE IF EXISTS `cloud`.`s3`; + +-- Re-create host view to prevent multiple entries for hosts with multiple tags +DROP VIEW IF EXISTS `cloud`.`host_view`; +CREATE VIEW `cloud`.`host_view` AS + SELECT + host.id, + host.uuid, + host.name, + host.status, + host.disconnected, + host.type, + host.private_ip_address, + host.version, + host.hypervisor_type, + host.hypervisor_version, + host.capabilities, + host.last_ping, + host.created, + host.removed, + host.resource_state, + host.mgmt_server_id, + host.cpu_sockets, + host.cpus, + host.speed, + host.ram, + cluster.id cluster_id, + cluster.uuid cluster_uuid, + cluster.name cluster_name, + cluster.cluster_type, + data_center.id data_center_id, + data_center.uuid data_center_uuid, + data_center.name data_center_name, + data_center.networktype data_center_type, + host_pod_ref.id pod_id, + host_pod_ref.uuid pod_uuid, + host_pod_ref.name pod_name, + GROUP_CONCAT(DISTINCT(host_tags.tag)) AS tag, + guest_os_category.id guest_os_category_id, + guest_os_category.uuid guest_os_category_uuid, + guest_os_category.name guest_os_category_name, + mem_caps.used_capacity memory_used_capacity, + mem_caps.reserved_capacity memory_reserved_capacity, + cpu_caps.used_capacity cpu_used_capacity, + cpu_caps.reserved_capacity cpu_reserved_capacity, + async_job.id job_id, + async_job.uuid job_uuid, + async_job.job_status job_status, + async_job.account_id job_account_id, + oobm.enabled AS `oobm_enabled`, + oobm.power_state AS `oobm_power_state`, + ha_config.enabled AS `ha_enabled`, + ha_config.ha_state AS `ha_state`, + ha_config.provider AS `ha_provider`, + `last_annotation_view`.`annotation` AS `annotation`, + `last_annotation_view`.`created` AS `last_annotated`, + `user`.`username` AS `username` + FROM + `cloud`.`host` + LEFT JOIN + `cloud`.`cluster` ON host.cluster_id = cluster.id + LEFT JOIN + `cloud`.`data_center` ON host.data_center_id = data_center.id + LEFT JOIN + `cloud`.`host_pod_ref` ON host.pod_id = host_pod_ref.id + LEFT JOIN + `cloud`.`host_details` ON host.id = host_details.host_id + AND host_details.name = 'guest.os.category.id' + LEFT JOIN + `cloud`.`guest_os_category` ON guest_os_category.id = CONVERT ( host_details.value, UNSIGNED ) + LEFT JOIN + `cloud`.`host_tags` ON host_tags.host_id = host.id + LEFT JOIN + `cloud`.`op_host_capacity` mem_caps ON host.id = mem_caps.host_id + AND mem_caps.capacity_type = 0 + LEFT JOIN + `cloud`.`op_host_capacity` cpu_caps ON host.id = cpu_caps.host_id + AND cpu_caps.capacity_type = 1 + LEFT JOIN + `cloud`.`async_job` ON async_job.instance_id = host.id + AND async_job.instance_type = 'Host' + AND async_job.job_status = 0 + LEFT JOIN + `cloud`.`oobm` ON oobm.host_id = host.id + left join + `cloud`.`ha_config` ON ha_config.resource_id=host.id + and ha_config.resource_type='Host' + LEFT JOIN + `cloud`.`last_annotation_view` ON `last_annotation_view`.`entity_uuid` = `host`.`uuid` + LEFT JOIN + `cloud`.`user` ON `user`.`uuid` = `last_annotation_view`.`user_uuid` + GROUP BY + `host`.`id`; + +-- 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() + FROM DUAL + 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 (10, 'Ubuntu 20.04 LTS', 'KVM', 'default', 'Ubuntu 20.04 LTS'); +CALL ADD_GUEST_OS_AND_HYPERVISOR_MAPPING (10, 'Ubuntu 21.04', 'KVM', 'default', 'Ubuntu 21.04'); +CALL ADD_GUEST_OS_AND_HYPERVISOR_MAPPING (9, 'pfSense 2.4', 'KVM', 'default', 'pfSense 2.4'); +CALL ADD_GUEST_OS_AND_HYPERVISOR_MAPPING (9, 'OpenBSD 6.7', 'KVM', 'default', 'OpenBSD 6.7'); +CALL ADD_GUEST_OS_AND_HYPERVISOR_MAPPING (9, 'OpenBSD 6.8', 'KVM', 'default', 'OpenBSD 6.8'); +CALL ADD_GUEST_OS_AND_HYPERVISOR_MAPPING (1, 'AlmaLinux 8.3', 'KVM', 'default', 'AlmaLinux 8.3'); + +-- Alter value column of *_details table to prevent NULL values +UPDATE cloud.account_details SET value='' WHERE value IS NULL; +ALTER TABLE cloud.account_details MODIFY value varchar(255) NOT NULL; +UPDATE cloud.cluster_details SET value='' WHERE value IS NULL; +ALTER TABLE cloud.cluster_details MODIFY value varchar(255) NOT NULL; +UPDATE cloud.data_center_details SET value='' WHERE value IS NULL; +ALTER TABLE cloud.data_center_details MODIFY value varchar(1024) NOT NULL; +UPDATE cloud.domain_details SET value='' WHERE value IS NULL; +ALTER TABLE cloud.domain_details MODIFY value varchar(255) NOT NULL; +UPDATE cloud.image_store_details SET value='' WHERE value IS NULL; +ALTER TABLE cloud.image_store_details MODIFY value varchar(255) NOT NULL; +UPDATE cloud.storage_pool_details SET value='' WHERE value IS NULL; +ALTER TABLE cloud.storage_pool_details MODIFY value varchar(255) NOT NULL; +UPDATE cloud.template_deploy_as_is_details SET value='' WHERE value IS NULL; +ALTER TABLE cloud.template_deploy_as_is_details MODIFY value text NOT NULL; +UPDATE cloud.user_vm_deploy_as_is_details SET value='' WHERE value IS NULL; +ALTER TABLE cloud.user_vm_deploy_as_is_details MODIFY value text NOT NULL; +UPDATE cloud.user_vm_details SET value='' WHERE value IS NULL; +ALTER TABLE cloud.user_vm_details MODIFY value varchar(5120) NOT NULL;
