engine/schema/src/main/resources/META-INF/db/views/cloud.async_job_view.sql

-- 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`.`async_job_view`;

DROP VIEW IF EXISTS `cloud`.`async_job_view`;

CREATE VIEW `cloud`.`async_job_view` AS
select
    account.id account_id,
    account.uuid account_uuid,
    account.account_name account_name,
    account.type account_type,
    domain.id domain_id,
    domain.uuid domain_uuid,
    domain.name domain_name,
    domain.path domain_path,
    user.id user_id,
    user.uuid user_uuid,
    async_job.id,
    async_job.uuid,
    async_job.job_cmd,
    async_job.job_status,
    async_job.job_process_status,
    async_job.job_result_code,
    async_job.job_result,
    async_job.created,
    async_job.removed,
    async_job.instance_type,
    async_job.instance_id,
    async_job.job_executing_msid,
    CASE
        WHEN async_job.instance_type = 'Volume' THEN volumes.uuid
        WHEN
                    async_job.instance_type = 'Template'
                or async_job.instance_type = 'Iso'
            THEN
            vm_template.uuid
        WHEN
                    async_job.instance_type = 'VirtualMachine'
                or async_job.instance_type = 'ConsoleProxy'
                or async_job.instance_type = 'SystemVm'
                or async_job.instance_type = 'DomainRouter'
            THEN
            vm_instance.uuid
        WHEN async_job.instance_type = 'Snapshot' THEN snapshots.uuid
        WHEN async_job.instance_type = 'Host' THEN host.uuid
        WHEN async_job.instance_type = 'StoragePool' THEN storage_pool.uuid
        WHEN async_job.instance_type = 'IpAddress' THEN user_ip_address.uuid
        WHEN async_job.instance_type = 'SecurityGroup' THEN security_group.uuid
        WHEN async_job.instance_type = 'PhysicalNetwork' THEN physical_network.uuid
        WHEN async_job.instance_type = 'TrafficType' THEN physical_network_traffic_types.uuid
        WHEN async_job.instance_type = 'PhysicalNetworkServiceProvider' THEN physical_network_service_providers.uuid
        WHEN async_job.instance_type = 'FirewallRule' THEN firewall_rules.uuid
        WHEN async_job.instance_type = 'Account' THEN acct.uuid
        WHEN async_job.instance_type = 'User' THEN us.uuid
        WHEN async_job.instance_type = 'StaticRoute' THEN static_routes.uuid
        WHEN async_job.instance_type = 'PrivateGateway' THEN vpc_gateways.uuid
        WHEN async_job.instance_type = 'Counter' THEN counter.uuid
        WHEN async_job.instance_type = 'Condition' THEN conditions.uuid
        WHEN async_job.instance_type = 'AutoScalePolicy' THEN autoscale_policies.uuid
        WHEN async_job.instance_type = 'AutoScaleVmProfile' THEN autoscale_vmprofiles.uuid
        WHEN async_job.instance_type = 'AutoScaleVmGroup' THEN autoscale_vmgroups.uuid
        ELSE null
        END instance_uuid
from
    `cloud`.`async_job`
        left join
    `cloud`.`account` ON async_job.account_id = account.id
        left join
    `cloud`.`domain` ON domain.id = account.domain_id
        left join
    `cloud`.`user` ON async_job.user_id = user.id
        left join
    `cloud`.`volumes` ON async_job.instance_id = volumes.id
        left join
    `cloud`.`vm_template` ON async_job.instance_id = vm_template.id
        left join
    `cloud`.`vm_instance` ON async_job.instance_id = vm_instance.id
        left join
    `cloud`.`snapshots` ON async_job.instance_id = snapshots.id
        left join
    `cloud`.`host` ON async_job.instance_id = host.id
        left join
    `cloud`.`storage_pool` ON async_job.instance_id = storage_pool.id
        left join
    `cloud`.`user_ip_address` ON async_job.instance_id = user_ip_address.id
        left join
    `cloud`.`security_group` ON async_job.instance_id = security_group.id
        left join
    `cloud`.`physical_network` ON async_job.instance_id = physical_network.id
        left join
    `cloud`.`physical_network_traffic_types` ON async_job.instance_id = physical_network_traffic_types.id
        left join
    `cloud`.`physical_network_service_providers` ON async_job.instance_id = physical_network_service_providers.id
        left join
    `cloud`.`firewall_rules` ON async_job.instance_id = firewall_rules.id
        left join
    `cloud`.`account` acct ON async_job.instance_id = acct.id
        left join
    `cloud`.`user` us ON async_job.instance_id = us.id
        left join
    `cloud`.`static_routes` ON async_job.instance_id = static_routes.id
        left join
    `cloud`.`vpc_gateways` ON async_job.instance_id = vpc_gateways.id
        left join
    `cloud`.`counter` ON async_job.instance_id = counter.id
        left join
    `cloud`.`conditions` ON async_job.instance_id = conditions.id
        left join
    `cloud`.`autoscale_policies` ON async_job.instance_id = autoscale_policies.id
        left join
    `cloud`.`autoscale_vmprofiles` ON async_job.instance_id = autoscale_vmprofiles.id
        left join
    `cloud`.`autoscale_vmgroups` ON async_job.instance_id = autoscale_vmgroups.id;