You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudstack.apache.org by nv...@apache.org on 2021/09/02 15:18:06 UTC
[cloudstack] 01/01: Merge branch '4.15' into main
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 <ni...@gmail.com>
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;