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:27 UTC
[cloudstack] branch main updated (01b79e797c2 -> 72b6ab9c512)
This is an automated email from the ASF dual-hosted git repository.
rohit pushed a change to branch main
in repository https://gitbox.apache.org/repos/asf/cloudstack.git
from 01b79e797c2 Merge remote-tracking branch 'origin/4.17' into main
add d331b2f98fd schema: Add upgrade path from 4.17.1.0 to 4.17.2.0 (#6981)
new 72b6ab9c512 Merge remote-tracking branch 'origin/4.17' into main
The 1 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
Summary of changes:
.../com/cloud/upgrade/DatabaseUpgradeChecker.java | 15 ++++----
.../com/cloud/upgrade/dao/Upgrade41700to41710.java | 2 +-
...e41110to41120.java => Upgrade41710to41720.java} | 45 ++++++++++++----------
...e41710to41800.java => Upgrade41720to41800.java} | 11 +++---
.../META-INF/db/schema-41710to41800-cleanup.sql | 20 ----------
...cleanup.sql => schema-41720to41800-cleanup.sql} | 3 +-
...ma-41710to41800.sql => schema-41720to41800.sql} | 3 +-
7 files changed, 43 insertions(+), 56 deletions(-)
copy engine/schema/src/main/java/com/cloud/upgrade/dao/{Upgrade41110to41120.java => Upgrade41710to41720.java} (60%)
rename engine/schema/src/main/java/com/cloud/upgrade/dao/{Upgrade41710to41800.java => Upgrade41720to41800.java} (97%)
delete mode 100644 engine/schema/src/main/resources/META-INF/db/schema-41710to41800-cleanup.sql
copy engine/schema/src/main/resources/META-INF/db/{schema-421to430-cleanup.sql => schema-41720to41800-cleanup.sql} (93%)
rename engine/schema/src/main/resources/META-INF/db/{schema-41710to41800.sql => schema-41720to41800.sql} (99%)
[cloudstack] 01/01: Merge remote-tracking branch 'origin/4.17' into main
Posted by ro...@apache.org.
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 -----