| -- 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. |
| |
| -- VIEW `cloud`.`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`.`state` AS `state`, |
| `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` 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' |
| GROUP BY |
| `service_offering`.`id`; |
