You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudstack.apache.org by ro...@apache.org on 2022/12/13 07:51:28 UTC
[cloudstack] 01/01: Merge remote-tracking branch 'origin/4.17' into main
This is an automated email from the ASF dual-hosted git repository.
rohit pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudstack.git
commit 72b6ab9c5128cab1fbcb9d2d2dea416b79eda96c
Merge: 01b79e797c2 d331b2f98fd
Author: Rohit Yadav <ro...@shapeblue.com>
AuthorDate: Tue Dec 13 07:50:36 2022 +0000
Merge remote-tracking branch 'origin/4.17' into main
Due to merge conflict, and schema changes in 4.17 branch the previous
4.17.1->4.18.0 DB upgrade path class was renamed to 4.17.2->4.18.0
Signed-off-by: Rohit Yadav <ro...@shapeblue.com>
.../com/cloud/upgrade/DatabaseUpgradeChecker.java | 15 +++--
.../com/cloud/upgrade/dao/Upgrade41700to41710.java | 2 +-
.../com/cloud/upgrade/dao/Upgrade41710to41720.java | 75 ++++++++++++++++++++++
...e41710to41800.java => Upgrade41720to41800.java} | 11 ++--
...cleanup.sql => schema-41720to41800-cleanup.sql} | 4 +-
...ma-41710to41800.sql => schema-41720to41800.sql} | 3 +-
6 files changed, 94 insertions(+), 16 deletions(-)
diff --cc engine/schema/src/main/java/com/cloud/upgrade/DatabaseUpgradeChecker.java
index 51fb135e771,b07328f9f24..728b30fc506
--- a/engine/schema/src/main/java/com/cloud/upgrade/DatabaseUpgradeChecker.java
+++ b/engine/schema/src/main/java/com/cloud/upgrade/DatabaseUpgradeChecker.java
@@@ -76,7 -71,12 +71,13 @@@ 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.Upgrade41510to41520;
import com.cloud.upgrade.dao.Upgrade41520to41600;
+ import com.cloud.upgrade.dao.Upgrade41600to41610;
+ import com.cloud.upgrade.dao.Upgrade41610to41700;
+ import com.cloud.upgrade.dao.Upgrade41700to41710;
+ import com.cloud.upgrade.dao.Upgrade41710to41720;
++import com.cloud.upgrade.dao.Upgrade41720to41800;
import com.cloud.upgrade.dao.Upgrade420to421;
import com.cloud.upgrade.dao.Upgrade421to430;
import com.cloud.upgrade.dao.Upgrade430to440;
@@@ -210,8 -210,7 +211,8 @@@ public class DatabaseUpgradeChecker imp
.next("4.16.1.1", new Upgrade41610to41700())
.next("4.17.0.0", new Upgrade41700to41710())
.next("4.17.0.1", new Upgrade41700to41710())
- .next("4.17.1.0", new Upgrade41710to41800())
- .next("4.17.2.0", new Upgrade41710to41800())
+ .next("4.17.1.0", new Upgrade41710to41720())
++ .next("4.17.2.0", new Upgrade41720to41800())
.build();
}
diff --cc engine/schema/src/main/java/com/cloud/upgrade/dao/Upgrade41720to41800.java
index baa2860b84b,00000000000..fae91310035
mode 100644,000000..100644
--- a/engine/schema/src/main/java/com/cloud/upgrade/dao/Upgrade41720to41800.java
+++ b/engine/schema/src/main/java/com/cloud/upgrade/dao/Upgrade41720to41800.java
@@@ -1,232 -1,0 +1,233 @@@
+// 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 com.cloud.upgrade.SystemVmTemplateRegistration;
+import com.cloud.utils.exception.CloudRuntimeException;
+import org.apache.cloudstack.api.response.UsageTypeResponse;
+import org.apache.cloudstack.usage.UsageTypes;
+import org.apache.cloudstack.utils.reflectiontostringbuilderutils.ReflectionToStringBuilderUtils;
+import org.apache.commons.lang3.time.DateUtils;
+import org.apache.log4j.Logger;
+
+import java.io.InputStream;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.Arrays;
+import java.util.Date;
+import java.util.LinkedHashMap;
+import java.util.List;
+import java.util.Map;
+
- public class Upgrade41710to41800 implements DbUpgrade, DbUpgradeSystemVmTemplate {
++public class Upgrade41720to41800 implements DbUpgrade, DbUpgradeSystemVmTemplate {
++
++ final static Logger LOG = Logger.getLogger(Upgrade41720to41800.class);
+
- final static Logger LOG = Logger.getLogger(Upgrade41710to41800.class);
+ private SystemVmTemplateRegistration systemVmTemplateRegistration;
+
+ @Override
+ public String[] getUpgradableVersionRange() {
- return new String[] {"4.17.1.0", "4.18.0.0"};
++ return new String[] {"4.17.2.0", "4.18.0.0"};
+ }
+
+ @Override
+ public String getUpgradedVersion() {
+ return "4.18.0.0";
+ }
+
+ @Override
+ public boolean supportsRollingUpgrade() {
+ return false;
+ }
+
+ @Override
+ public InputStream[] getPrepareScripts() {
- final String scriptFile = "META-INF/db/schema-41710to41800.sql";
++ final String scriptFile = "META-INF/db/schema-41720to41800.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) {
+ convertQuotaTariffsToNewParadigm(conn);
+ convertVmResourcesQuotaTypesToRunningVmQuotaType(conn);
+ }
+
+ @Override
+ public InputStream[] getCleanupScripts() {
- final String scriptFile = "META-INF/db/schema-41710to41800-cleanup.sql";
++ final String scriptFile = "META-INF/db/schema-41720to41800-cleanup.sql";
+ final InputStream script = Thread.currentThread().getContextClassLoader().getResourceAsStream(scriptFile);
+ if (script == null) {
+ throw new CloudRuntimeException("Unable to find " + scriptFile);
+ }
+
+ return new InputStream[] {script};
+ }
+
+ private void initSystemVmTemplateRegistration() {
+ systemVmTemplateRegistration = new SystemVmTemplateRegistration("");
+ }
+
+ @Override
+ public void updateSystemVmTemplates(Connection conn) {
+ LOG.debug("Updating System Vm template IDs");
+ initSystemVmTemplateRegistration();
+ try {
+ systemVmTemplateRegistration.updateSystemVmTemplates(conn);
+ } catch (Exception e) {
+ throw new CloudRuntimeException("Failed to find / register SystemVM template(s)");
+ }
+ }
+
+ protected void convertQuotaTariffsToNewParadigm(Connection conn) {
+ LOG.info("Converting quota tariffs to new paradigm.");
+
+ List<UsageTypeResponse> usageTypeResponses = UsageTypes.listUsageTypes();
+
+ for (UsageTypeResponse usageTypeResponse : usageTypeResponses) {
+ Integer usageType = usageTypeResponse.getUsageType();
+
+ String tariffTypeDescription = ReflectionToStringBuilderUtils.reflectOnlySelectedFields(usageTypeResponse, "description", "usageType");
+
+ LOG.info(String.format("Converting quota tariffs of type %s to new paradigm.", tariffTypeDescription));
+
+ for (boolean previousTariff : Arrays.asList(true, false)) {
+ Map<Long, Date> tariffs = selectTariffs(conn, usageType, previousTariff, tariffTypeDescription);
+
+ int tariffsSize = tariffs.size();
+ if (tariffsSize < 2) {
+ LOG.info(String.format("Quota tariff of type %s has [%s] %s register(s). Tariffs with less than 2 register do not need to be converted to new paradigm.",
+ tariffTypeDescription, tariffsSize, previousTariff ? "previous of current" : "next to current"));
+ continue;
+ }
+
+ executeUpdateQuotaTariffSetEndDateAndRemoved(conn, usageType, tariffs, previousTariff, tariffTypeDescription);
+ }
+ }
+ }
+
+ protected Map<Long, Date> selectTariffs(Connection conn, Integer usageType, boolean previousTariff, String tariffTypeDescription) {
+ Map<Long, Date> quotaTariffs = new LinkedHashMap<>();
+
+ String selectQuotaTariffs = String.format("SELECT id, effective_on FROM cloud_usage.quota_tariff WHERE %s AND usage_type = ? ORDER BY effective_on, updated_on;",
+ previousTariff ? "usage_name = name" : "removed is null");
+
+ LOG.info(String.format("Selecting %s quota tariffs of type [%s] according to SQL [%s].", previousTariff ? "previous of current" : "next to current",
+ tariffTypeDescription, selectQuotaTariffs));
+
+ try (PreparedStatement pstmt = conn.prepareStatement(selectQuotaTariffs)) {
+ pstmt.setInt(1, usageType);
+
+ try (ResultSet result = pstmt.executeQuery()) {
+ while (result.next()) {
+ quotaTariffs.put(result.getLong("id"), result.getDate("effective_on"));
+ }
+ }
+ return quotaTariffs;
+ } catch (SQLException e) {
+ String message = String.format("Unable to retrieve %s quota tariffs of type [%s] due to [%s].", previousTariff ? "previous" : "next", tariffTypeDescription,
+ e.getMessage());
+ LOG.error(message, e);
+ throw new CloudRuntimeException(message, e);
+ }
+ }
+
+ protected void executeUpdateQuotaTariffSetEndDateAndRemoved(Connection conn, Integer usageType, Map<Long, Date> tariffs, boolean setRemoved, String tariffTypeDescription) {
+ String updateQuotaTariff = String.format("UPDATE cloud_usage.quota_tariff SET end_date = ? %s WHERE id = ?;", setRemoved ? ", removed = ?" : "");
+
+ Object[] ids = tariffs.keySet().toArray();
+
+ LOG.info(String.format("Updating %s registers of %s quota tariffs of type [%s] with SQL [%s].", tariffs.size() - 1, setRemoved ? "previous of current" :
+ "next to current", tariffTypeDescription, updateQuotaTariff));
+
+ for (int i = 0; i < tariffs.size() - 1; i++) {
+ Long id = Long.valueOf(String.valueOf(ids[i]));
+ Long nextId = Long.valueOf(String.valueOf(ids[i + 1]));
+
+ Date endDate = tariffs.get(nextId);
+
+ if (!DateUtils.isSameDay(endDate, tariffs.get(id))) {
+ endDate = DateUtils.addDays(endDate, -1);
+ }
+
+ try (PreparedStatement pstmt = conn.prepareStatement(updateQuotaTariff)) {
+ java.sql.Date sqlEndDate = new java.sql.Date(endDate.getTime());
+ pstmt.setDate(1, sqlEndDate);
+
+ String updateRemoved = "";
+ if (setRemoved) {
+ pstmt.setDate(2, sqlEndDate);
+ pstmt.setLong(3, id);
+
+ updateRemoved = String.format("and \"removed\" to [%s] ", sqlEndDate);
+ } else {
+ pstmt.setLong(2, id);
+ }
+
+ LOG.info(String.format("Updating \"end_date\" to [%s] %sof quota tariff with ID [%s].", sqlEndDate, updateRemoved, id));
+ pstmt.executeUpdate();
+ } catch (SQLException e) {
+ String message = String.format("Unable to update \"end_date\" %s of quota tariffs of usage type [%s] due to [%s].", setRemoved ? "and \"removed\"" : "",
+ usageType, e.getMessage());
+ LOG.error(message, e);
+ throw new CloudRuntimeException(message, e);
+ }
+ }
+ }
+
+ protected void convertVmResourcesQuotaTypesToRunningVmQuotaType(Connection conn) {
+ LOG.info("Converting quota tariffs of type \"vCPU\", \"CPU_SPEED\" and \"MEMORY\" to \"RUNNING_VM\".");
+
+ String insertSql = "INSERT INTO cloud_usage.quota_tariff (usage_type, usage_name, usage_unit, usage_discriminator, currency_value, effective_on, updated_on,"
+ + " updated_by, uuid, name, description, removed, end_date, activation_rule)\n"
+ + "SELECT 1, 'RUNNING_VM', usage_unit, '', 0, effective_on, updated_on, updated_by, UUID(), name, description, removed, end_date,\n"
+ + " CASE\n"
+ + " WHEN usage_type = 15 THEN CONCAT('((value.computingResources ? (value.computingResources.cpuSpeed * value.computingResources.cpuNumber) : 0) / 100) * ', currency_value)\n"
+ + " WHEN usage_type = 16 THEN CONCAT('(value.computingResources ? value.computingResources.cpuNumber : 0) * ', currency_value)\n"
+ + " WHEN usage_type = 17 THEN CONCAT('(value.computingResources ? value.computingResources.memory : 0) * ', currency_value)\n"
+ + " END\n"
+ + "FROM cloud_usage.quota_tariff \n"
+ + "WHERE usage_type in (15, 16, 17) \n"
+ + "AND currency_value > 0.0;";
+
+ try (PreparedStatement pstmt = conn.prepareStatement(insertSql)) {
+ pstmt.executeUpdate();
+ } catch (SQLException e) {
+ String message = String.format("Failed to convert quota tariffs of type \"vCPU\", \"CPU_SPEED\" and \"MEMORY\" to \"RUNNING_VM\" due to [%s].", e.getMessage());
+ LOG.error(message, e);
+ throw new CloudRuntimeException(message, e);
+ }
+
+ LOG.info("Disabling unused quota tariffs of type \"vCPU\", \"CPU_SPEED\" and \"MEMORY\".");
+
+ String updateSql = "UPDATE cloud_usage.quota_tariff SET removed = now() WHERE usage_type in (15, 16, 17) and removed is null;";
+
+ try (PreparedStatement pstmt = conn.prepareStatement(updateSql)) {
+ pstmt.executeUpdate();
+ } catch (SQLException e) {
+ String message = String.format("Failed disable quota tariffs of type \"vCPU\", \"CPU_SPEED\" and \"MEMORY\" due to [%s].", e.getMessage());
+ LOG.error(message, e);
+ throw new CloudRuntimeException(message, e);
+ }
+ }
+}
diff --cc engine/schema/src/main/resources/META-INF/db/schema-41720to41800-cleanup.sql
index d3d586d3e2e,00000000000..9e200d76fb4
mode 100644,000000..100644
--- a/engine/schema/src/main/resources/META-INF/db/schema-41720to41800-cleanup.sql
+++ b/engine/schema/src/main/resources/META-INF/db/schema-41720to41800-cleanup.sql
@@@ -1,20 -1,0 +1,20 @@@
+-- 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.17.1.0 to 4.18.0.0
- --;
++-- Schema upgrade cleanup from 4.17.2.0 to 4.18.0.0
++--;
diff --cc engine/schema/src/main/resources/META-INF/db/schema-41720to41800.sql
index a0c455e6ba7,00000000000..7d2aa8e67c1
mode 100644,000000..100644
--- a/engine/schema/src/main/resources/META-INF/db/schema-41720to41800.sql
+++ b/engine/schema/src/main/resources/META-INF/db/schema-41720to41800.sql
@@@ -1,866 -1,0 +1,867 @@@
+-- 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.17.1.0 to 4.18.0.0
++-- Schema upgrade from 4.17.2.0 to 4.18.0.0
+--;
++
+-- Enable CPU cap for default system offerings;
+UPDATE `cloud`.`service_offering` so
+SET so.limit_cpu_use = 1
+WHERE so.default_use = 1 AND so.vm_type IN ('domainrouter', 'secondarystoragevm', 'consoleproxy', 'internalloadbalancervm', 'elasticloadbalancervm');
+
+-- Idempotent ADD COLUMN
+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;
+
+
+-- Add foreign key procedure to link volumes to passphrase table
+DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_ADD_FOREIGN_KEY`;
+CREATE PROCEDURE `cloud`.`IDEMPOTENT_ADD_FOREIGN_KEY` (
+ IN in_table_name VARCHAR(200),
+ IN in_foreign_table_name VARCHAR(200),
+ IN in_foreign_column_name VARCHAR(200)
+)
+BEGIN
+ DECLARE CONTINUE HANDLER FOR 1005 BEGIN END; SET @ddl = CONCAT('ALTER TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', ' ADD CONSTRAINT '); SET @ddl = CONCAT(@ddl, 'fk_', in_foreign_table_name, '_', in_foreign_column_name); SET @ddl = CONCAT(@ddl, ' FOREIGN KEY (', in_foreign_table_name, '_', in_foreign_column_name, ')'); SET @ddl = CONCAT(@ddl, ' REFERENCES ', in_foreign_table_name, '(', in_foreign_column_name, ')'); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; END;
+
+-- Add passphrase table
+CREATE TABLE IF NOT EXISTS `cloud`.`passphrase` (
+ `id` bigint unsigned NOT NULL auto_increment,
+ `passphrase` varchar(64) DEFAULT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+-- Add passphrase column to volumes table
+CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.volumes', 'passphrase_id', 'bigint unsigned DEFAULT NULL COMMENT ''encryption passphrase id'' ');
+CALL `cloud`.`IDEMPOTENT_ADD_FOREIGN_KEY`('cloud.volumes', 'passphrase', 'id');
+CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.volumes', 'encrypt_format', 'varchar(64) DEFAULT NULL COMMENT ''encryption format'' ');
+
+-- Add encrypt column to disk_offering
+CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.disk_offering', 'encrypt', 'tinyint(1) DEFAULT 0 COMMENT ''volume encrypt requested'' ');
+
+-- add encryption support to disk offering view
+DROP VIEW IF EXISTS `cloud`.`disk_offering_view`;
+CREATE VIEW `cloud`.`disk_offering_view` AS
+SELECT
+ `disk_offering`.`id` AS `id`,
+ `disk_offering`.`uuid` AS `uuid`,
+ `disk_offering`.`name` AS `name`,
+ `disk_offering`.`display_text` AS `display_text`,
+ `disk_offering`.`provisioning_type` AS `provisioning_type`,
+ `disk_offering`.`disk_size` AS `disk_size`,
+ `disk_offering`.`min_iops` AS `min_iops`,
+ `disk_offering`.`max_iops` AS `max_iops`,
+ `disk_offering`.`created` AS `created`,
+ `disk_offering`.`tags` AS `tags`,
+ `disk_offering`.`customized` AS `customized`,
+ `disk_offering`.`customized_iops` AS `customized_iops`,
+ `disk_offering`.`removed` AS `removed`,
+ `disk_offering`.`use_local_storage` AS `use_local_storage`,
+ `disk_offering`.`hv_ss_reserve` AS `hv_ss_reserve`,
+ `disk_offering`.`bytes_read_rate` AS `bytes_read_rate`,
+ `disk_offering`.`bytes_read_rate_max` AS `bytes_read_rate_max`,
+ `disk_offering`.`bytes_read_rate_max_length` AS `bytes_read_rate_max_length`,
+ `disk_offering`.`bytes_write_rate` AS `bytes_write_rate`,
+ `disk_offering`.`bytes_write_rate_max` AS `bytes_write_rate_max`,
+ `disk_offering`.`bytes_write_rate_max_length` AS `bytes_write_rate_max_length`,
+ `disk_offering`.`iops_read_rate` AS `iops_read_rate`,
+ `disk_offering`.`iops_read_rate_max` AS `iops_read_rate_max`,
+ `disk_offering`.`iops_read_rate_max_length` AS `iops_read_rate_max_length`,
+ `disk_offering`.`iops_write_rate` AS `iops_write_rate`,
+ `disk_offering`.`iops_write_rate_max` AS `iops_write_rate_max`,
+ `disk_offering`.`iops_write_rate_max_length` AS `iops_write_rate_max_length`,
+ `disk_offering`.`cache_mode` AS `cache_mode`,
+ `disk_offering`.`sort_key` AS `sort_key`,
+ `disk_offering`.`compute_only` AS `compute_only`,
+ `disk_offering`.`display_offering` AS `display_offering`,
+ `disk_offering`.`state` AS `state`,
+ `disk_offering`.`disk_size_strictness` AS `disk_size_strictness`,
+ `vsphere_storage_policy`.`value` AS `vsphere_storage_policy`,
+ `disk_offering`.`encrypt` AS `encrypt`,
+ 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
+FROM
+ `cloud`.`disk_offering`
+ LEFT JOIN
+ `cloud`.`disk_offering_details` AS `domain_details` ON `domain_details`.`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`.`disk_offering_details` AS `zone_details` ON `zone_details`.`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`.`disk_offering_details` AS `vsphere_storage_policy` ON `vsphere_storage_policy`.`offering_id` = `disk_offering`.`id`
+ AND `vsphere_storage_policy`.`name` = 'storagepolicy'
+WHERE
+ `disk_offering`.`state`='Active'
+GROUP BY
+ `disk_offering`.`id`;
+
+-- add encryption support to service offering view
+DROP VIEW IF EXISTS `cloud`.`service_offering_view`;
+CREATE VIEW `cloud`.`service_offering_view` AS
+SELECT
+ `service_offering`.`id` AS `id`,
+ `service_offering`.`uuid` AS `uuid`,
+ `service_offering`.`name` AS `name`,
+ `service_offering`.`display_text` AS `display_text`,
+ `disk_offering`.`provisioning_type` AS `provisioning_type`,
+ `service_offering`.`created` AS `created`,
+ `disk_offering`.`tags` AS `tags`,
+ `service_offering`.`removed` AS `removed`,
+ `disk_offering`.`use_local_storage` AS `use_local_storage`,
+ `service_offering`.`system_use` AS `system_use`,
+ `disk_offering`.`id` AS `disk_offering_id`,
+ `disk_offering`.`name` AS `disk_offering_name`,
+ `disk_offering`.`uuid` AS `disk_offering_uuid`,
+ `disk_offering`.`display_text` AS `disk_offering_display_text`,
+ `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`,
+ `disk_offering`.`encrypt` AS `encrypt_root`,
+ `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`,
+ `service_offering`.`disk_offering_strictness` AS `disk_offering_strictness`,
+ `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.disk_offering_id = disk_offering.id
+ LEFT JOIN
+ `cloud`.`service_offering_details` AS `domain_details` ON `domain_details`.`service_offering_id` = `service_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` = `service_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` = `service_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` = `service_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` = `service_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` = `service_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` = `service_offering`.`id`
+ AND `vsphere_storage_policy`.`name` = 'storagepolicy'
+WHERE
+ `service_offering`.`state`='Active'
+GROUP BY
+ `service_offering`.`id`;
+
+-- Add cidr_list column to load_balancing_rules
+ALTER TABLE `cloud`.`load_balancing_rules`
+ADD cidr_list VARCHAR(4096);
+
+-- savely add resources in parallel
+-- PR#5984 Create table to persist VM stats.
+DROP TABLE IF EXISTS `cloud`.`resource_reservation`;
+CREATE TABLE `cloud`.`resource_reservation` (
+ `id` bigint unsigned NOT NULL auto_increment COMMENT 'id',
+ `account_id` bigint unsigned NOT NULL,
+ `domain_id` bigint unsigned NOT NULL,
+ `resource_type` varchar(255) NOT NULL,
+ `amount` bigint unsigned NOT NULL,
+ PRIMARY KEY (`id`)
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+-- Alter networks table to add ip6dns1 and ip6dns2
+ALTER TABLE `cloud`.`networks`
+ ADD COLUMN `ip6dns1` varchar(255) DEFAULT NULL COMMENT 'first IPv6 DNS for the network' AFTER `dns2`,
+ ADD COLUMN `ip6dns2` varchar(255) DEFAULT NULL COMMENT 'second IPv6 DNS for the network' AFTER `ip6dns1`;
+-- Alter vpc table to add dns1, dns2, ip6dns1 and ip6dns2
+ALTER TABLE `cloud`.`vpc`
+ ADD COLUMN `dns1` varchar(255) DEFAULT NULL COMMENT 'first IPv4 DNS for the vpc' AFTER `network_domain`,
+ ADD COLUMN `dns2` varchar(255) DEFAULT NULL COMMENT 'second IPv4 DNS for the vpc' AFTER `dns1`,
+ ADD COLUMN `ip6dns1` varchar(255) DEFAULT NULL COMMENT 'first IPv6 DNS for the vpc' AFTER `dns2`,
+ ADD COLUMN `ip6dns2` varchar(255) DEFAULT NULL COMMENT 'second IPv6 DNS for the vpc' AFTER `ip6dns1`;
+
+-- Fix migrateVolume permissions #6224.
+DELETE role_perm
+FROM role_permissions role_perm
+INNER JOIN roles ON role_perm.role_id = roles.id
+WHERE roles.role_type != 'Admin' AND roles.is_default = 1 AND role_perm.rule = 'migrateVolume';
+
+-- VM autoscaling
+
+-- Idempotent ADD COLUMN
+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;
+
+-- Idempotent RENAME COLUMN
+DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_CHANGE_COLUMN`;
+CREATE PROCEDURE `cloud`.`IDEMPOTENT_CHANGE_COLUMN` (
+ IN in_table_name VARCHAR(200)
+, IN in_column_name VARCHAR(200)
+, IN in_column_new_name VARCHAR(200)
+, IN in_column_new_definition VARCHAR(1000)
+)
+BEGIN
+ DECLARE CONTINUE HANDLER FOR 1054 BEGIN END; SET @ddl = CONCAT('ALTER TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', 'CHANGE COLUMN') ; SET @ddl = CONCAT(@ddl, ' ', in_column_name); SET @ddl = CONCAT(@ddl, ' ', in_column_new_name); SET @ddl = CONCAT(@ddl, ' ', in_column_new_definition); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; END;
+
+-- Idempotent ADD UNIQUE KEY
+DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_ADD_UNIQUE_KEY`;
+CREATE PROCEDURE `cloud`.`IDEMPOTENT_ADD_UNIQUE_KEY` (
+ IN in_table_name VARCHAR(200)
+, IN in_key_name VARCHAR(200)
+, IN in_key_definition VARCHAR(1000)
+)
+BEGIN
+ DECLARE CONTINUE HANDLER FOR 1061 BEGIN END; SET @ddl = CONCAT('ALTER TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', 'ADD UNIQUE KEY ', in_key_name); SET @ddl = CONCAT(@ddl, ' ', in_key_definition); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; END;
+
+-- Idempotent DROP FOREIGN KEY
+DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_DROP_FOREIGN_KEY`;
+CREATE PROCEDURE `cloud`.`IDEMPOTENT_DROP_FOREIGN_KEY` (
+ IN in_table_name VARCHAR(200)
+, IN in_foreign_key_name VARCHAR(200)
+)
+BEGIN
+ DECLARE CONTINUE HANDLER FOR 1091, 1025 BEGIN END; SET @ddl = CONCAT('ALTER TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', ' DROP FOREIGN KEY '); SET @ddl = CONCAT(@ddl, ' ', in_foreign_key_name); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; END;
+
+-- Add column 'supports_vm_autoscaling' to 'network_offerings' table
+CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.network_offerings', 'supports_vm_autoscaling', 'boolean default false');
+
+-- Update column 'supports_vm_autoscaling' to 1 if network offerings support Lb
+UPDATE `cloud`.`network_offerings`
+JOIN `cloud`.`ntwk_offering_service_map`
+ON network_offerings.id = ntwk_offering_service_map.network_offering_id
+SET network_offerings.supports_vm_autoscaling = 1
+WHERE ntwk_offering_service_map.service = 'Lb'
+ AND ntwk_offering_service_map.provider IN ('VirtualRouter', 'VpcVirtualRouter', 'Netscaler')
+ AND network_offerings.removed IS NULL;
+
+-- Add column 'name' to 'autoscale_vmgroups' table
+CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.autoscale_vmgroups', 'name', 'VARCHAR(255) DEFAULT NULL COMMENT "name of the autoscale vm group" AFTER `load_balancer_id`');
+UPDATE `cloud`.`autoscale_vmgroups` SET `name` = CONCAT('AutoScale-VmGroup-',id) WHERE `name` IS NULL;
+
+-- Add column 'next_vm_seq' to 'autoscale_vmgroups' table
+CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.autoscale_vmgroups', 'next_vm_seq', 'BIGINT UNSIGNED NOT NULL DEFAULT 1');
+
+-- Add column 'user_data' to 'autoscale_vmprofiles' table
+CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.autoscale_vmprofiles', 'user_data', 'TEXT(32768) AFTER `counter_params`');
+
+-- Add column 'name' to 'autoscale_policies' table
+CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.autoscale_policies', 'name', 'VARCHAR(255) DEFAULT NULL COMMENT "name of the autoscale policy" AFTER `uuid`');
+
+-- Add column 'provider' and update values
+CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.counter', 'provider', 'VARCHAR(255) NOT NULL COMMENT "Network provider name" AFTER `uuid`');
+UPDATE `cloud`.`counter` SET provider = 'Netscaler' WHERE `provider` IS NULL OR `provider` = '';
+
+CALL `cloud`.`IDEMPOTENT_ADD_UNIQUE_KEY`('cloud.counter', 'uc_counter__provider__source__value', '(provider, source, value)');
+
+-- Add new counters for VM autoscaling
+
+INSERT IGNORE INTO `cloud`.`counter` (uuid, provider, source, name, value, created) VALUES (UUID(), 'VirtualRouter', 'cpu', 'VM CPU - average percentage', 'vm.cpu.average.percentage', NOW());
+INSERT IGNORE INTO `cloud`.`counter` (uuid, provider, source, name, value, created) VALUES (UUID(), 'VirtualRouter', 'memory', 'VM Memory - average percentage', 'vm.memory.average.percentage', NOW());
+INSERT IGNORE INTO `cloud`.`counter` (uuid, provider, source, name, value, created) VALUES (UUID(), 'VirtualRouter', 'virtualrouter', 'Public Network - mbps received per vm', 'public.network.received.average.mbps', NOW());
+INSERT IGNORE INTO `cloud`.`counter` (uuid, provider, source, name, value, created) VALUES (UUID(), 'VirtualRouter', 'virtualrouter', 'Public Network - mbps transmit per vm', 'public.network.transmit.average.mbps', NOW());
+INSERT IGNORE INTO `cloud`.`counter` (uuid, provider, source, name, value, created) VALUES (UUID(), 'VirtualRouter', 'virtualrouter', 'Load Balancer - average connections per vm', 'virtual.network.lb.average.connections', NOW());
+
+INSERT IGNORE INTO `cloud`.`counter` (uuid, provider, source, name, value, created) VALUES (UUID(), 'VpcVirtualRouter', 'cpu', 'VM CPU - average percentage', 'vm.cpu.average.percentage', NOW());
+INSERT IGNORE INTO `cloud`.`counter` (uuid, provider, source, name, value, created) VALUES (UUID(), 'VpcVirtualRouter', 'memory', 'VM Memory - average percentage', 'vm.memory.average.percentage', NOW());
+INSERT IGNORE INTO `cloud`.`counter` (uuid, provider, source, name, value, created) VALUES (UUID(), 'VpcVirtualRouter', 'virtualrouter', 'Public Network - mbps received per vm', 'public.network.received.average.mbps', NOW());
+INSERT IGNORE INTO `cloud`.`counter` (uuid, provider, source, name, value, created) VALUES (UUID(), 'VpcVirtualRouter', 'virtualrouter', 'Public Network - mbps transmit per vm', 'public.network.transmit.average.mbps', NOW());
+INSERT IGNORE INTO `cloud`.`counter` (uuid, provider, source, name, value, created) VALUES (UUID(), 'VpcVirtualRouter', 'virtualrouter', 'Load Balancer - average connections per vm', 'virtual.network.lb.average.connections', NOW());
+
+INSERT IGNORE INTO `cloud`.`counter` (uuid, provider, source, name, value, created) VALUES (UUID(), 'None', 'cpu', 'VM CPU - average percentage', 'vm.cpu.average.percentage', NOW());
+INSERT IGNORE INTO `cloud`.`counter` (uuid, provider, source, name, value, created) VALUES (UUID(), 'None', 'memory', 'VM Memory - average percentage', 'vm.memory.average.percentage', NOW());
+
+-- Update autoscale_vmgroups to new state
+
+UPDATE `cloud`.`autoscale_vmgroups` SET state= UPPER(state);
+
+-- Update autoscale_vmgroups so records will not be removed when LB rule is removed
+
+CALL `cloud`.`IDEMPOTENT_DROP_FOREIGN_KEY`('cloud.autoscale_vmgroups', 'fk_autoscale_vmgroup__load_balancer_id');
+
+-- Update autoscale_vmprofiles to make autoscale_user_id optional
+
+ALTER TABLE `cloud`.`autoscale_vmprofiles` MODIFY COLUMN `autoscale_user_id` bigint unsigned;
+
+-- Update autoscale_vmprofiles to rename destroy_vm_grace_period
+
+CALL `cloud`.`IDEMPOTENT_CHANGE_COLUMN`('cloud.autoscale_vmprofiles', 'destroy_vm_grace_period', 'expunge_vm_grace_period', 'int unsigned COMMENT "the time allowed for existing connections to get closed before a vm is expunged"');
+
+-- Create table for VM autoscaling historic data
+
+CREATE TABLE IF NOT EXISTS `cloud`.`autoscale_vmgroup_statistics` (
+ `id` bigint unsigned NOT NULL auto_increment,
+ `vmgroup_id` bigint unsigned NOT NULL,
+ `policy_id` bigint unsigned NOT NULL,
+ `counter_id` bigint unsigned NOT NULL,
+ `resource_id` bigint unsigned DEFAULT NULL,
+ `resource_type` varchar(255) NOT NULL,
+ `raw_value` double NOT NULL,
+ `value_type` varchar(255) NOT NULL,
+ `created` datetime NOT NULL COMMENT 'Date this data is created',
+ `state` varchar(255) NOT NULL COMMENT 'State of the data',
+ PRIMARY KEY (`id`),
+ CONSTRAINT `fk_autoscale_vmgroup_statistics__vmgroup_id` FOREIGN KEY `fk_autoscale_vmgroup_statistics__vmgroup_id` (`vmgroup_id`) REFERENCES `autoscale_vmgroups` (`id`) ON DELETE CASCADE,
+ INDEX `i_autoscale_vmgroup_statistics__vmgroup_id`(`vmgroup_id`),
+ INDEX `i_autoscale_vmgroup_statistics__policy_id`(`policy_id`),
+ INDEX `i_autoscale_vmgroup_statistics__counter_id`(`counter_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+
+
+-- Update Network offering view with supports_vm_autoscaling
+DROP VIEW IF EXISTS `cloud`.`network_offering_view`;
+CREATE VIEW `cloud`.`network_offering_view` AS
+ SELECT
+ `network_offerings`.`id` AS `id`,
+ `network_offerings`.`uuid` AS `uuid`,
+ `network_offerings`.`name` AS `name`,
+ `network_offerings`.`unique_name` AS `unique_name`,
+ `network_offerings`.`display_text` AS `display_text`,
+ `network_offerings`.`nw_rate` AS `nw_rate`,
+ `network_offerings`.`mc_rate` AS `mc_rate`,
+ `network_offerings`.`traffic_type` AS `traffic_type`,
+ `network_offerings`.`tags` AS `tags`,
+ `network_offerings`.`system_only` AS `system_only`,
+ `network_offerings`.`specify_vlan` AS `specify_vlan`,
+ `network_offerings`.`service_offering_id` AS `service_offering_id`,
+ `network_offerings`.`conserve_mode` AS `conserve_mode`,
+ `network_offerings`.`created` AS `created`,
+ `network_offerings`.`removed` AS `removed`,
+ `network_offerings`.`default` AS `default`,
+ `network_offerings`.`availability` AS `availability`,
+ `network_offerings`.`dedicated_lb_service` AS `dedicated_lb_service`,
+ `network_offerings`.`shared_source_nat_service` AS `shared_source_nat_service`,
+ `network_offerings`.`sort_key` AS `sort_key`,
+ `network_offerings`.`redundant_router_service` AS `redundant_router_service`,
+ `network_offerings`.`state` AS `state`,
+ `network_offerings`.`guest_type` AS `guest_type`,
+ `network_offerings`.`elastic_ip_service` AS `elastic_ip_service`,
+ `network_offerings`.`eip_associate_public_ip` AS `eip_associate_public_ip`,
+ `network_offerings`.`elastic_lb_service` AS `elastic_lb_service`,
+ `network_offerings`.`specify_ip_ranges` AS `specify_ip_ranges`,
+ `network_offerings`.`inline` AS `inline`,
+ `network_offerings`.`is_persistent` AS `is_persistent`,
+ `network_offerings`.`internal_lb` AS `internal_lb`,
+ `network_offerings`.`public_lb` AS `public_lb`,
+ `network_offerings`.`egress_default_policy` AS `egress_default_policy`,
+ `network_offerings`.`concurrent_connections` AS `concurrent_connections`,
+ `network_offerings`.`keep_alive_enabled` AS `keep_alive_enabled`,
+ `network_offerings`.`supports_streched_l2` AS `supports_streched_l2`,
+ `network_offerings`.`supports_public_access` AS `supports_public_access`,
+ `network_offerings`.`supports_vm_autoscaling` AS `supports_vm_autoscaling`,
+ `network_offerings`.`for_vpc` AS `for_vpc`,
+ `network_offerings`.`service_package_id` AS `service_package_id`,
+ 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,
+ `offering_details`.value AS internet_protocol
+ FROM
+ `cloud`.`network_offerings`
+ LEFT JOIN
+ `cloud`.`network_offering_details` AS `domain_details` ON `domain_details`.`network_offering_id` = `network_offerings`.`id` AND `domain_details`.`name`='domainid'
+ LEFT JOIN
+ `cloud`.`domain` AS `domain` ON FIND_IN_SET(`domain`.`id`, `domain_details`.`value`)
+ LEFT JOIN
+ `cloud`.`network_offering_details` AS `zone_details` ON `zone_details`.`network_offering_id` = `network_offerings`.`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`.`network_offering_details` AS `offering_details` ON `offering_details`.`network_offering_id` = `network_offerings`.`id` AND `offering_details`.`name`='internetProtocol'
+ GROUP BY
+ `network_offerings`.`id`;
+
+-- UserData as first class resource (PR #6202)
+CREATE TABLE `cloud`.`user_data` (
+ `id` bigint unsigned NOT NULL auto_increment COMMENT 'id',
+ `uuid` varchar(40) NOT NULL COMMENT 'UUID of the user data',
+ `name` varchar(256) NOT NULL COMMENT 'name of the user data',
+ `account_id` bigint unsigned NOT NULL COMMENT 'owner, foreign key to account table',
+ `domain_id` bigint unsigned NOT NULL COMMENT 'domain, foreign key to domain table',
+ `user_data` mediumtext COMMENT 'value of the userdata',
+ `params` mediumtext COMMENT 'value of the comma-separated list of parameters',
+ PRIMARY KEY (`id`),
+ CONSTRAINT `fk_userdata__account_id` FOREIGN KEY(`account_id`) REFERENCES `account` (`id`) ON DELETE CASCADE,
+ CONSTRAINT `fk_userdata__domain_id` FOREIGN KEY(`domain_id`) REFERENCES `domain` (`id`) ON DELETE CASCADE,
+ CONSTRAINT `uc_userdata__uuid` UNIQUE (`uuid`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+ALTER TABLE `cloud`.`user_vm` ADD COLUMN `user_data_id` bigint unsigned DEFAULT NULL COMMENT 'id of the user data' AFTER `user_data`;
+ALTER TABLE `cloud`.`user_vm` ADD COLUMN `user_data_details` mediumtext DEFAULT NULL COMMENT 'value of the comma-separated list of parameters' AFTER `user_data_id`;
+ALTER TABLE `cloud`.`user_vm` ADD CONSTRAINT `fk_user_vm__user_data_id` FOREIGN KEY `fk_user_vm__user_data_id`(`user_data_id`) REFERENCES `user_data`(`id`);
+
+ALTER TABLE `cloud`.`vm_template` ADD COLUMN `user_data_id` bigint unsigned DEFAULT NULL COMMENT 'id of the user data';
+ALTER TABLE `cloud`.`vm_template` ADD COLUMN `user_data_link_policy` varchar(255) DEFAULT NULL COMMENT 'user data link policy with template';
+ALTER TABLE `cloud`.`vm_template` ADD CONSTRAINT `fk_vm_template__user_data_id` FOREIGN KEY `fk_vm_template__user_data_id`(`user_data_id`) REFERENCES `user_data`(`id`);
+
+-- Added userdata details to template
+DROP VIEW IF EXISTS `cloud`.`template_view`;
+CREATE VIEW `cloud`.`template_view` AS
+ SELECT
+ `vm_template`.`id` AS `id`,
+ `vm_template`.`uuid` AS `uuid`,
+ `vm_template`.`unique_name` AS `unique_name`,
+ `vm_template`.`name` AS `name`,
+ `vm_template`.`public` AS `public`,
+ `vm_template`.`featured` AS `featured`,
+ `vm_template`.`type` AS `type`,
+ `vm_template`.`hvm` AS `hvm`,
+ `vm_template`.`bits` AS `bits`,
+ `vm_template`.`url` AS `url`,
+ `vm_template`.`format` AS `format`,
+ `vm_template`.`created` AS `created`,
+ `vm_template`.`checksum` AS `checksum`,
+ `vm_template`.`display_text` AS `display_text`,
+ `vm_template`.`enable_password` AS `enable_password`,
+ `vm_template`.`dynamically_scalable` AS `dynamically_scalable`,
+ `vm_template`.`state` AS `template_state`,
+ `vm_template`.`guest_os_id` AS `guest_os_id`,
+ `guest_os`.`uuid` AS `guest_os_uuid`,
+ `guest_os`.`display_name` AS `guest_os_name`,
+ `vm_template`.`bootable` AS `bootable`,
+ `vm_template`.`prepopulate` AS `prepopulate`,
+ `vm_template`.`cross_zones` AS `cross_zones`,
+ `vm_template`.`hypervisor_type` AS `hypervisor_type`,
+ `vm_template`.`extractable` AS `extractable`,
+ `vm_template`.`template_tag` AS `template_tag`,
+ `vm_template`.`sort_key` AS `sort_key`,
+ `vm_template`.`removed` AS `removed`,
+ `vm_template`.`enable_sshkey` AS `enable_sshkey`,
+ `parent_template`.`id` AS `parent_template_id`,
+ `parent_template`.`uuid` AS `parent_template_uuid`,
+ `source_template`.`id` AS `source_template_id`,
+ `source_template`.`uuid` AS `source_template_uuid`,
+ `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`,
+ `data_center`.`id` AS `data_center_id`,
+ `data_center`.`uuid` AS `data_center_uuid`,
+ `data_center`.`name` AS `data_center_name`,
+ `launch_permission`.`account_id` AS `lp_account_id`,
+ `template_store_ref`.`store_id` AS `store_id`,
+ `image_store`.`scope` AS `store_scope`,
+ `template_store_ref`.`state` AS `state`,
+ `template_store_ref`.`download_state` AS `download_state`,
+ `template_store_ref`.`download_pct` AS `download_pct`,
+ `template_store_ref`.`error_str` AS `error_str`,
+ `template_store_ref`.`size` AS `size`,
+ `template_store_ref`.physical_size AS `physical_size`,
+ `template_store_ref`.`destroyed` AS `destroyed`,
+ `template_store_ref`.`created` AS `created_on_store`,
+ `vm_template_details`.`name` AS `detail_name`,
+ `vm_template_details`.`value` AS `detail_value`,
+ `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`,
+ CONCAT(`vm_template`.`id`,
+ '_',
+ IFNULL(`data_center`.`id`, 0)) AS `temp_zone_pair`,
+ `vm_template`.`direct_download` AS `direct_download`,
+ `vm_template`.`deploy_as_is` AS `deploy_as_is`,
+ `user_data`.`id` AS `user_data_id`,
+ `user_data`.`uuid` AS `user_data_uuid`,
+ `user_data`.`name` AS `user_data_name`,
+ `user_data`.`params` AS `user_data_params`,
+ `vm_template`.`user_data_link_policy` AS `user_data_policy`
+ FROM
+ (((((((((((((`vm_template`
+ JOIN `guest_os` ON ((`guest_os`.`id` = `vm_template`.`guest_os_id`)))
+ JOIN `account` ON ((`account`.`id` = `vm_template`.`account_id`)))
+ JOIN `domain` ON ((`domain`.`id` = `account`.`domain_id`)))
+ LEFT JOIN `projects` ON ((`projects`.`project_account_id` = `account`.`id`)))
+ LEFT JOIN `vm_template_details` ON ((`vm_template_details`.`template_id` = `vm_template`.`id`)))
+ LEFT JOIN `vm_template` `source_template` ON ((`source_template`.`id` = `vm_template`.`source_template_id`)))
+ LEFT JOIN `template_store_ref` ON (((`template_store_ref`.`template_id` = `vm_template`.`id`)
+ AND (`template_store_ref`.`store_role` = 'Image')
+ AND (`template_store_ref`.`destroyed` = 0))))
+ LEFT JOIN `vm_template` `parent_template` ON ((`parent_template`.`id` = `vm_template`.`parent_template_id`)))
+ LEFT JOIN `image_store` ON ((ISNULL(`image_store`.`removed`)
+ AND (`template_store_ref`.`store_id` IS NOT NULL)
+ AND (`image_store`.`id` = `template_store_ref`.`store_id`))))
+ LEFT JOIN `template_zone_ref` ON (((`template_zone_ref`.`template_id` = `vm_template`.`id`)
+ AND ISNULL(`template_store_ref`.`store_id`)
+ AND ISNULL(`template_zone_ref`.`removed`))))
+ LEFT JOIN `data_center` ON (((`image_store`.`data_center_id` = `data_center`.`id`)
+ OR (`template_zone_ref`.`zone_id` = `data_center`.`id`))))
+ LEFT JOIN `launch_permission` ON ((`launch_permission`.`template_id` = `vm_template`.`id`)))
+ LEFT JOIN `user_data` ON ((`user_data`.`id` = `vm_template`.`user_data_id`))
+ LEFT JOIN `resource_tags` ON (((`resource_tags`.`resource_id` = `vm_template`.`id`)
+ AND ((`resource_tags`.`resource_type` = 'Template')
+ OR (`resource_tags`.`resource_type` = 'ISO')))));
+
+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`,
+ `host`.`cluster_id` AS `cluster_id`,
+ `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`,
+ `service_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`,
+ `service_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_details`.`value` AS `keypair_names`,
+ `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`,
+ `autoscale_vmgroups`.`id` AS `autoscale_vmgroup_id`,
+ `autoscale_vmgroups`.`uuid` AS `autoscale_vmgroup_uuid`,
+ `autoscale_vmgroups`.`name` AS `autoscale_vmgroup_name`,
+ `vm_instance`.`dynamically_scalable` AS `dynamically_scalable`,
+ `user_data`.`id` AS `user_data_id`,
+ `user_data`.`uuid` AS `user_data_uuid`,
+ `user_data`.`name` AS `user_data_name`,
+ `user_vm`.`user_data_details` AS `user_data_details`,
+ `vm_template`.`user_data_link_policy` AS `user_data_policy`
+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 `volumes` ON ((`vm_instance`.`id` = `volumes`.`instance_id`)))
+ LEFT JOIN `service_offering` ON ((`vm_instance`.`service_offering_id` = `service_offering`.`id`)))
+ LEFT JOIN `disk_offering` `svc_disk_offering` ON ((`volumes`.`disk_offering_id` = `svc_disk_offering`.`id`)))
+ LEFT JOIN `disk_offering` ON ((`volumes`.`disk_offering_id` = `disk_offering`.`id`)))
+ LEFT JOIN `backup_offering` ON ((`vm_instance`.`backup_offering_id` = `backup_offering`.`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 `user_data` ON ((`user_data`.`id` = `user_vm`.`user_data_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.KeyPairNames'))))
+ 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 `autoscale_vmgroup_vm_map` ON ((`autoscale_vmgroup_vm_map`.`instance_id` = `vm_instance`.`id`)))
+ LEFT JOIN `autoscale_vmgroups` ON ((`autoscale_vmgroup_vm_map`.`vmgroup_id` = `autoscale_vmgroups`.`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'))));
+
+-- Improve alert.email.addresses description #6806.
+UPDATE cloud.configuration
+SET description = 'Comma separated list of email addresses which are going to receive alert emails.'
+WHERE name = 'alert.email.addresses';
+
+-- Improve description of configuration `secstorage.encrypt.copy` #6811.
+UPDATE cloud.configuration
+SET description = "Use SSL method used to encrypt copy traffic between zones. Also ensures that the certificate assigned to the zone is used when
+generating links for external access."
+WHERE name = 'secstorage.encrypt.copy';
+
+-- allow isolated networks without services to be used as is.
+UPDATE `cloud`.`networks` ntwk
+ SET ntwk.state = 'Implemented'
+ WHERE ntwk.network_offering_id in
+ (SELECT id FROM `cloud`.`network_offerings` ntwkoff
+ WHERE (SELECT count(*) FROM `cloud`.`ntwk_offering_service_map` ntwksrvcmp WHERE ntwksrvcmp.network_offering_id = ntwkoff.id) = 0
+ AND ntwkoff.is_persistent = 1) AND
+ ntwk.state = 'Setup' AND
+ ntwk.removed is NULL AND
+ ntwk.guest_type = 'Isolated';
+
+----- PR Quota custom tariffs #5909---
+-- Create column 'uuid'
+ALTER TABLE cloud_usage.quota_tariff
+ ADD COLUMN `uuid` varchar(40);
+
+UPDATE cloud_usage.quota_tariff
+SET uuid = UUID()
+WHERE uuid is null;
+
+ALTER TABLE cloud_usage.quota_tariff
+ MODIFY `uuid` varchar(40) NOT NULL;
+
+
+-- Create column 'name'
+ALTER TABLE cloud_usage.quota_tariff
+ ADD COLUMN `name` text
+ COMMENT 'A name, defined by the user, to the tariff. This column will be used as identifier along the tariff updates.';
+
+UPDATE cloud_usage.quota_tariff
+SET name = case when effective_on <= now() then usage_name else concat(usage_name, '-', id) end
+WHERE name is null;
+
+ALTER TABLE cloud_usage.quota_tariff
+ MODIFY `name` text NOT NULL;
+
+
+-- Create column 'description'
+ALTER TABLE cloud_usage.quota_tariff
+ ADD COLUMN `description` text DEFAULT NULL
+ COMMENT 'The description of the tariff.';
+
+
+-- Create column 'activation_rule'
+ALTER TABLE cloud_usage.quota_tariff
+ ADD COLUMN `activation_rule` text DEFAULT NULL
+ COMMENT 'JS expression that defines when the tariff should be activated.';
+
+
+-- Create column 'removed'
+ALTER TABLE cloud_usage.quota_tariff
+ ADD COLUMN `removed` datetime DEFAULT NULL;
+
+
+-- Create column 'end_date'
+ALTER TABLE cloud_usage.quota_tariff
+ ADD COLUMN `end_date` datetime DEFAULT NULL
+ COMMENT 'Defines the end date of the tariff.';
+
+
+-- Change usage unit to right unit
+UPDATE cloud_usage.quota_tariff
+SET usage_unit = 'Compute*Month'
+WHERE usage_unit = 'Compute-Month';
+
+UPDATE cloud_usage.quota_tariff
+SET usage_unit = 'IP*Month'
+WHERE usage_unit = 'IP-Month';
+
+UPDATE cloud_usage.quota_tariff
+SET usage_unit = 'GB*Month'
+WHERE usage_unit = 'GB-Month';
+
+UPDATE cloud_usage.quota_tariff
+SET usage_unit = 'Policy*Month'
+WHERE usage_unit = 'Policy-Month';
+
+----- PR Quota custom tariffs #5909 -----