CREATE OR REPLACE VIEW memory_and_disk_images_storage_domain_view AS

SELECT images.image_guid AS image_guid,
    storage_domain_static.storage_name AS storage_name,
    storage_pool_iso_map.storage_pool_id AS storage_pool_id,
    storage_domain_static.storage_type AS storage_type,
    images.creation_date AS creation_date,
    images.size AS size,
    images.it_guid AS it_guid,
    snap.description AS description,
    images.ParentId AS ParentId,
    images.lastModified AS lastModified,
    snap.app_list AS app_list,
    image_storage_domain_map.storage_domain_id AS storage_id,
    images.vm_snapshot_id AS vm_snapshot_id,
    images.volume_type AS volume_type,
    images.volume_format AS volume_format,
    images.imageStatus AS imageStatus,
    images.image_group_id AS image_group_id,
    images.active,
    images.volume_classification,
    vms_for_disk_view.entity_type AS entity_type,
    array_to_string(vms_for_disk_view.array_vm_names, ',') AS vm_names,
    COALESCE(array_upper(vms_for_disk_view.array_vm_names, 1), 0) AS number_of_vms,
    array_to_string(vms_for_disk_view.array_template_version_name, ',') AS template_version_names,
    base_disks.disk_id,
    base_disks.disk_alias AS disk_alias,
    base_disks.disk_description AS disk_description,
    base_disks.shareable AS shareable,
    base_disks.wipe_after_delete AS wipe_after_delete,
    base_disks.propagate_errors,
    base_disks.sgio AS sgio,
    image_storage_domain_map.quota_id AS quota_id,
    quota.quota_name AS quota_name,
    storage_pool.quota_enforcement_type,
    image_storage_domain_map.disk_profile_id AS disk_profile_id,
    disk_profiles.name AS disk_profile_name,
    disk_image_dynamic.actual_size AS actual_size,
    disk_image_dynamic.read_rate AS read_rate,
    disk_image_dynamic.write_rate AS write_rate,
    disk_image_dynamic.read_latency_seconds AS read_latency_seconds,
    disk_image_dynamic.write_latency_seconds AS write_latency_seconds,
    disk_image_dynamic.flush_latency_seconds AS flush_latency_seconds,
    base_disks.alignment AS alignment,
    base_disks.disk_storage_type AS disk_storage_type,
    base_disks.cinder_volume_type AS cinder_volume_type,
    base_disks.last_alignment_scan AS last_alignment_scan,
    storage_domains_ovf_info.ovf_disk_id IS NOT NULL AS ovf_store,
    COALESCE(memory_snap.memory_metadata_disk_id, memory_snap.memory_dump_disk_id) IS NOT NULL AS memory_image,
    image_transfers.phase AS image_transfer_phase,
    image_transfers.bytes_sent AS image_transfer_bytes_sent,
    image_transfers.bytes_total AS image_transfer_bytes_total
FROM images
LEFT JOIN disk_image_dynamic
    ON images.image_guid = disk_image_dynamic.image_id
LEFT JOIN base_disks
    ON images.image_group_id = base_disks.disk_id
LEFT JOIN vms_for_disk_view
    ON vms_for_disk_view.device_id = images.image_group_id
LEFT JOIN image_storage_domain_map
    ON image_storage_domain_map.image_id = images.image_guid
LEFT JOIN storage_domain_static
    ON image_storage_domain_map.storage_domain_id = storage_domain_static.id
LEFT JOIN snapshots snap
    ON images.vm_snapshot_id = snap.snapshot_id
LEFT JOIN snapshots memory_snap
    ON images.image_group_id IN (memory_snap.memory_metadata_disk_id, memory_snap.memory_dump_disk_id)
LEFT JOIN storage_domains_ovf_info
    ON storage_domains_ovf_info.ovf_disk_id = images.image_group_id
LEFT JOIN quota
    ON image_storage_domain_map.quota_id = quota.id
LEFT JOIN disk_profiles
    ON image_storage_domain_map.disk_profile_id = disk_profiles.id
LEFT JOIN storage_pool_iso_map
    ON storage_pool_iso_map.storage_id = storage_domain_static.id
LEFT JOIN storage_pool
    ON storage_pool.id = storage_pool_iso_map.storage_pool_id
LEFT JOIN image_transfers
    ON images.image_group_id = image_transfers.disk_id
WHERE images.image_guid != '00000000-0000-0000-0000-000000000000';

