Attachment 940416 Details for Bug 1141543 – Function replacement

[patch] Function replacement

1 (text/plain), 8.00 KB, created by Liran Zelkha on 2014-09-23 12:24:17 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Liran Zelkha

Created: 2014-09-23 12:24:17 UTC

Size: 8.00 KB

patch

obsolete

>Create or replace FUNCTION GetDisksVmGuid(v_vm_guid UUID, v_only_plugged BOOLEAN, v_user_id UUID, v_is_filtered BOOLEAN)
>RETURNS SETOF all_disks_including_snapshots STABLE
>   AS $procedure$
>BEGIN
>      RETURN QUERY SELECT all_disks_including_snapshots.*
>      FROM (SELECT storage_impl.*,
>       bd.disk_id, -- Disk fields
>       bd.disk_interface,
>       bd.wipe_after_delete,
>       bd.propagate_errors,
>       bd.disk_alias,
>       bd.disk_description,
>       bd.shareable,
>       bd.boot,
>       bd.sgio,
>       bd.alignment,
>       bd.last_alignment_scan
>FROM
>(
>    SELECT 0 AS disk_storage_type,
>	   storage_id,
>	   storage_path,
>	   storage_name,
>	   storage_type,
>           storage_pool_id,
>           image_guid,
>           creation_date,
>           actual_size,
>           read_rate,
>           write_rate,
>           read_latency_seconds,
>           write_latency_seconds,
>           flush_latency_seconds,
>           size,
>           it_guid,
>           imageStatus,
>           lastModified,
>           volume_type,
>           volume_format,
>           image_group_id,
>           description, 
>           ParentId,
>           app_list,
>           vm_snapshot_id,
>           active,
>           entity_type,
>           number_of_vms,
>           vm_names,
>           quota_id,
>           quota_name,
>           quota_enforcement_type,
>           ovf_store,
>           disk_profile_id, 
>           disk_profile_name,
>           null AS lun_id, 
>           null AS physical_volume_id,
>           null AS volume_group_id,
>           null AS serial,
>           null AS lun_mapping,
>           null AS vendor_id,
>           null AS product_id,
>           null AS device_size
>    FROM (
>SELECT images.image_guid as image_guid,
>	   	   array_to_string(array_agg(storage_domain_static_view.storage_name), ',') as storage_name,
>	   array_to_string(array_agg(storage_domain_static_view.storage), ',') as storage_path,
>	   cast(array_to_string(array_agg(storage_domain_static_view.id), ',') as uuid) storage_id,
>	   array_to_string(array_agg(storage_domain_static_view.storage_type), ',') storage_type,
>	max(images.creation_date) as creation_date,
>max(    images.size) as size,
>max(    CAST(images.it_guid as varchar(50))) as it_guid,
>    snapshots.description as description,
>max(    CAST(images.ParentId as varchar(50))) as ParentId,
>max(    images.lastModified) as lastModified,
>    snapshots.app_list as app_list,
>max(    CAST(images.vm_snapshot_id as varchar(50))) as vm_snapshot_id,
>max(    images.volume_type) as volume_type,
>max(    images.volume_format) as volume_format,
>max(    images.imageStatus) as imageStatus,
>cast(max(    CAST(images.image_group_id as varchar(50))) as uuid) as image_group_id,
>bool_or(    images.active) as active,
>    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,
>    base_disks.disk_id,
>    max(base_disks.disk_alias) as disk_alias,
>    max(    base_disks.disk_description) as disk_description,
>    bool_or(    base_disks.shareable) as shareable,
>    max(    base_disks.disk_interface) as disk_interface,
>    bool_or(    base_disks.wipe_after_delete) as wipe_after_delete,
>    max(    base_disks.propagate_errors) as ptopagate_errors,
>    bool_or(    base_disks.boot) as boot,
>    max(    base_disks.sgio) as sgio,
>    array_to_string(array_agg(COALESCE(CAST(quota.id as varchar), '')), ',') as quota_id,
>    array_to_string(array_agg(COALESCE(quota.quota_name, '')), ',') as quota_name,
>    max(storage_pool.quota_enforcement_type) as quota_enforcement_type,
>    array_to_string(array_agg(COALESCE(CAST(disk_profiles.id as varchar), '')), ',') as disk_profile_id,
>    array_to_string(array_agg(COALESCE(disk_profiles.name, '')), ',') as disk_profile_name,
>    max(disk_image_dynamic.actual_size) as actual_size,
>max(    disk_image_dynamic.read_rate) as read_rate,
>max(    disk_image_dynamic.write_rate) as write_rate,
>max(    disk_image_dynamic.read_latency_seconds) as read_latency_seconds,
>max(    disk_image_dynamic.write_latency_seconds) as write_latency_seconds,
>max(    disk_image_dynamic.flush_latency_seconds) as flush_latency_seconds,
>max(    base_disks.alignment) as alignment,
>max(    base_disks.last_alignment_scan) as last_alignment_scan,
>bool_or(    EXISTS (SELECT 1 FROM storage_domains_ovf_info WHERE images.image_group_id = storage_domains_ovf_info.ovf_disk_id)) as ovf_store,
>bool_or(    storage_domain_static_view.contains_unregistered_entities) as contains_unregistered_entities,
>storage_pool.id as storage_pool_id
>FROM images
>left outer join disk_image_dynamic on images.image_guid = disk_image_dynamic.image_id
>LEFT OUTER JOIN base_disks ON images.image_group_id = base_disks.disk_id
>LEFT OUTER 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 OUTER JOIN storage_domain_static_view ON image_storage_domain_map.storage_domain_id = storage_domain_static_view.id
>LEFT OUTER JOIN snapshots ON images.vm_snapshot_id = snapshots.snapshot_id
>LEFT OUTER JOIN quota ON image_storage_domain_map.quota_id = quota.id
>LEFT OUTER JOIN disk_profiles ON image_storage_domain_map.disk_profile_id = disk_profiles.id
>LEFT OUTER JOIN storage_pool ON storage_pool.id = storage_domain_static_view.storage_pool_id 
>WHERE images.image_guid != '00000000-0000-0000-0000-000000000000'
>GROUP BY images.image_guid, snapshots.description, snapshots.app_list, vms_for_disk_view.entity_type,vm_names, number_of_vms,base_disks.disk_id, storage_pool.id
>) as images_storage_domain_view
>    UNION
>    SELECT 1 AS disk_storage_type,
>           null AS storage_id, -- Storage domain fields
>           null AS storage_path,
>           null AS storage_name,
>           null AS storage_type,
>           null AS storage_pool_id,
>           null AS image_guid, -- Image fields
>           null AS creation_date,
>           null AS actual_size,
>           null AS read_rate,
>           null AS write_rate,
>           null AS read_latency_seconds,
>           null AS write_latency_seconds,
>           null AS flush_latency_seconds,
>           null AS size,
>           null AS it_guid,
>           null AS imageStatus,
>           null AS lastModified,
>           null AS volume_type,
>           null AS volume_format,
>           dlm.disk_id AS image_group_id,
>           null AS description, -- Snapshot fields
>           null AS ParentId,
>           null AS app_list,
>           null AS vm_snapshot_id,
>           null AS active,
>           vms_for_disk_view.entity_type,
>           COALESCE(array_upper(vms_for_disk_view.array_vm_names,1),0) as number_of_vms,
>           array_to_string(vms_for_disk_view.array_vm_names, ',') as vm_names,
>           null AS quota_id, -- Quota fields
>           null AS quota_name,
>           null AS quota_enforcement_type,
>           false as ovf_store,
>           null AS disk_profile_id, -- disk profile fields
>           null AS disk_profile_name,
>           l.lun_id, -- LUN fields
>           l.physical_volume_id,
>           l.volume_group_id,
>           l.serial,
>           l.lun_mapping,
>           l.vendor_id,
>           l.product_id,
>           l.device_size
>    FROM disk_lun_map dlm
>    JOIN luns l ON l.lun_id = dlm.lun_id
>    LEFT JOIN vms_for_disk_view on vms_for_disk_view.device_id = dlm.disk_id
>) AS storage_impl
>JOIN base_disks bd ON bd.disk_id = storage_impl.image_group_id) all_disks_including_snapshots
>      LEFT JOIN vm_device ON vm_device.device_id = all_disks_including_snapshots.image_group_id AND (NOT v_only_plugged OR is_plugged)
>      WHERE vm_device.vm_id = v_vm_guid
>      AND ((vm_device.snapshot_id IS NULL AND all_disks_including_snapshots.active IS NOT FALSE)
>          OR vm_device.snapshot_id = all_disks_including_snapshots.vm_snapshot_id)
>      AND (NOT v_is_filtered OR EXISTS (SELECT 1
>                                        FROM   user_disk_permissions_view
>                                        WHERE  user_id = v_user_id AND entity_id = all_disks_including_snapshots.disk_id));
>
>END; $procedure$
>LANGUAGE plpgsql;

Actions: View | Diff

Attachments on bug 1141543: 940416 | 942610 | 943920 | 946306