<commit_before>-- ----------------------------------------------------------------------
-- Views
-- ----------------------------------------------------------------------

CREATE OR REPLACE VIEW storage_domain_static_view
AS

SELECT     storage_domain_static.id as id,
			storage_domain_static.storage as storage,
			storage_domain_static.storage_name as storage_name,
			storage_pool_iso_map.storage_pool_id as storage_pool_id,
                        storage_pool_iso_map.status as status,
			storage_domain_static.storage_domain_type as storage_domain_type,
			storage_domain_static.storage_type as storage_type,
                        storage_domain_static.storage_domain_format_type as storage_domain_format_type,
            storage_domain_static.last_time_used_as_master as last_time_used_as_master,
			storage_pool.name as storage_pool_name
FROM        storage_domain_static LEFT OUTER JOIN
storage_pool_iso_map on storage_pool_iso_map.storage_id = storage_domain_static.id
LEFT OUTER JOIN storage_pool ON storage_pool.id = storage_pool_iso_map.storage_pool_id;

CREATE OR REPLACE VIEW vms_for_disk_view
AS
SELECT array_agg(vm_name) as array_vm_names,device_id,entity_type
FROM vm_static
JOIN vm_device ON vm_static.vm_guid = vm_device.vm_id
WHERE device = 'disk'
GROUP BY device_id, entity_type;


CREATE OR REPLACE VIEW images_storage_domain_view
AS

-- TODO: Change code to treat disks values directly instead of through this view.
SELECT images.image_guid as image_guid,
    storage_domain_static_view.storage_name as storage_name,
    storage_domain_static_view.storage as storage_path,
	storage_domain_static_view.storage_pool_id as storage_pool_id,
	images.creation_date as creation_date,
    images.size as size,
    images.it_guid as it_guid,
    snapshots.description as description,
    images.ParentId as ParentId,
    images.lastModified as lastModified,
    snapshots.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,
    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,
    base_disks.disk_alias as disk_alias,
    base_disks.disk_description as disk_description,
    base_disks.shareable as shareable,
    base_disks.disk_interface,
    base_disks.wipe_after_delete as wipe_after_delete,
    base_disks.propagate_errors,
    base_disks.boot as boot,
    images.quota_id as quota_id,
    quota.quota_name as quota_name,
    storage_pool.quota_enforcement_type,
    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
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 images.quota_id = quota.id
LEFT OUTER JOIN storage_pool ON storage_pool.id = quota.storage_pool_id
WHERE images.image_guid != '00000000-0000-0000-0000-000000000000';


CREATE OR REPLACE VIEW storage_domain_file_repos
AS
SELECT
storage_domain_static.id as storage_domain_id,
			storage_domain_static.storage_domain_type as storage_domain_type,
	        storage_pool_iso_map.storage_pool_id as storage_pool_id,
	       	storage_pool_iso_map.status as storage_domain_status,
	repo_file_meta_data.repo_file_name as repo_file_name,
		repo_file_meta_data.size as size,
		repo_file_meta_data.date_created as date_created,
		repo_file_meta_data.last_refreshed as last_refreshed,
		repo_file_meta_data.file_type as file_type,
	vds_dynamic.status as vds_status,
    storage_pool.status as storage_pool_status
FROM storage_domain_static
INNER JOIN storage_pool_iso_map ON storage_domain_static.id = storage_pool_iso_map.storage_id
INNER JOIN storage_pool ON storage_pool.id = storage_pool_iso_map.storage_pool_id
INNER JOIN vds_dynamic ON vds_dynamic.vds_id = storage_pool.spm_vds_id
LEFT OUTER JOIN repo_file_meta_data ON storage_pool_iso_map.storage_id = repo_file_meta_data.repo_domain_id;


CREATE OR REPLACE VIEW vm_images_view
AS
SELECT     array_to_string(array_agg(storage_id), ',') as storage_id, array_to_string(array_agg(storage_path), ',') as storage_path, array_to_string(array_agg(storage_name), ',') as storage_name,
					  images_storage_domain_view.storage_pool_id as storage_pool_id, images_storage_domain_view.image_guid as image_guid,
                      images_storage_domain_view.creation_date as creation_date, disk_image_dynamic.actual_size as actual_size, disk_image_dynamic.read_rate as read_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, disk_image_dynamic.write_rate as write_rate,
                      images_storage_domain_view.size as size, images_storage_domain_view.it_guid as it_guid,
                      images_storage_domain_view.description as description,
                      images_storage_domain_view.ParentId as ParentId, images_storage_domain_view.imageStatus as imageStatus, images_storage_domain_view.lastModified as lastModified,
                      images_storage_domain_view.app_list as app_list, images_storage_domain_view.vm_snapshot_id as vm_snapshot_id,
                      images_storage_domain_view.volume_type as volume_type, images_storage_domain_view.image_group_id as image_group_id,
                      images_storage_domain_view.active as active, images_storage_domain_view.volume_format as volume_format,
                      images_storage_domain_view.disk_interface as disk_interface, images_storage_domain_view.boot as boot, images_storage_domain_view.wipe_after_delete as wipe_after_delete, images_storage_domain_view.propagate_errors as propagate_errors,
                      images_storage_domain_view.entity_type as entity_type,images_storage_domain_view.number_of_vms as number_of_vms,images_storage_domain_view.vm_names as vm_names,
                      images_storage_domain_view.quota_id as quota_id, images_storage_domain_view.quota_name as quota_name, images_storage_domain_view.quota_enforcement_type,
                      images_storage_domain_view.disk_id, images_storage_domain_view.disk_alias as disk_alias, images_storage_domain_view.disk_description as disk_description,images_storage_domain_view.shareable as shareable
FROM         images_storage_domain_view
INNER JOIN disk_image_dynamic ON images_storage_domain_view.image_guid = disk_image_dynamic.image_id
WHERE images_storage_domain_view.active = TRUE
GROUP BY storage_pool_id,image_guid,creation_date,
disk_image_dynamic.actual_size,disk_image_dynamic.read_rate, 
disk_image_dynamic.read_latency_seconds, disk_image_dynamic.write_latency_seconds,
disk_image_dynamic.flush_latency_seconds,disk_image_dynamic.write_rate,size,
it_guid,description,ParentId,imageStatus,lastModified,app_list,vm_snapshot_id,volume_type,
image_group_id,active,volume_format,disk_interface,boot,wipe_after_delete,propagate_errors,number_of_vms,
entity_type,vm_names,quota_id,quota_name,quota_enforcement_type,disk_id,disk_alias,disk_description,shareable;






CREATE OR REPLACE VIEW all_disks
AS
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
FROM
(
    SELECT 0 AS disk_storage_type,
           array_to_string(array_agg(storage_id), ',') as storage_id, -- Storage fields
           array_to_string(array_agg(storage_path), ',') as storage_path,
           array_to_string(array_agg(storage_name), ',') as storage_name,
           storage_pool_id,
           image_guid, -- Image fields
           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, -- Snapshot fields
           ParentId,
           app_list,
           vm_snapshot_id,
           active,
           entity_type,
           number_of_vms,
           vm_names,
           quota_id, -- Quota fields
           quota_name,
           quota_enforcement_type,
           null AS lun_id, -- LUN fields
           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 images_storage_domain_view
    WHERE active = TRUE
    GROUP BY 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,boot,description,ParentId,app_list,vm_snapshot_id,active,entity_type,number_of_vms,vm_names,quota_id,quota_name,quota_enforcement_type
    UNION
    SELECT 1 AS disk_storage_type,
           null AS storage_id, -- Storage domain fields<commit_msg>core:Remove multiple columns in group by to use view<commit_after>-- ----------------------------------------------------------------------
-- Views
-- ----------------------------------------------------------------------

CREATE OR REPLACE VIEW storage_domain_static_view
AS

SELECT     storage_domain_static.id as id,
			storage_domain_static.storage as storage,
			storage_domain_static.storage_name as storage_name,
			storage_pool_iso_map.storage_pool_id as storage_pool_id,
                        storage_pool_iso_map.status as status,
			storage_domain_static.storage_domain_type as storage_domain_type,
			storage_domain_static.storage_type as storage_type,
                        storage_domain_static.storage_domain_format_type as storage_domain_format_type,
            storage_domain_static.last_time_used_as_master as last_time_used_as_master,
			storage_pool.name as storage_pool_name
FROM        storage_domain_static LEFT OUTER JOIN
storage_pool_iso_map on storage_pool_iso_map.storage_id = storage_domain_static.id
LEFT OUTER JOIN storage_pool ON storage_pool.id = storage_pool_iso_map.storage_pool_id;

CREATE OR REPLACE VIEW vms_for_disk_view
AS
SELECT array_agg(vm_name) as array_vm_names,device_id,entity_type
FROM vm_static
JOIN vm_device ON vm_static.vm_guid = vm_device.vm_id
WHERE device = 'disk'
GROUP BY device_id, entity_type;


CREATE OR REPLACE VIEW images_storage_domain_view
AS

-- TODO: Change code to treat disks values directly instead of through this view.
SELECT images.image_guid as image_guid,
    storage_domain_static_view.storage_name as storage_name,
    storage_domain_static_view.storage as storage_path,
	storage_domain_static_view.storage_pool_id as storage_pool_id,
	images.creation_date as creation_date,
    images.size as size,
    images.it_guid as it_guid,
    snapshots.description as description,
    images.ParentId as ParentId,
    images.lastModified as lastModified,
    snapshots.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,
    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,
    base_disks.disk_alias as disk_alias,
    base_disks.disk_description as disk_description,
    base_disks.shareable as shareable,
    base_disks.disk_interface,
    base_disks.wipe_after_delete as wipe_after_delete,
    base_disks.propagate_errors,
    base_disks.boot as boot,
    images.quota_id as quota_id,
    quota.quota_name as quota_name,
    storage_pool.quota_enforcement_type,
    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
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 images.quota_id = quota.id
LEFT OUTER JOIN storage_pool ON storage_pool.id = quota.storage_pool_id
WHERE images.image_guid != '00000000-0000-0000-0000-000000000000';


CREATE OR REPLACE VIEW storage_domain_file_repos
AS
SELECT
storage_domain_static.id as storage_domain_id,
			storage_domain_static.storage_domain_type as storage_domain_type,
	        storage_pool_iso_map.storage_pool_id as storage_pool_id,
	       	storage_pool_iso_map.status as storage_domain_status,
	repo_file_meta_data.repo_file_name as repo_file_name,
		repo_file_meta_data.size as size,
		repo_file_meta_data.date_created as date_created,
		repo_file_meta_data.last_refreshed as last_refreshed,
		repo_file_meta_data.file_type as file_type,
	vds_dynamic.status as vds_status,
    storage_pool.status as storage_pool_status
FROM storage_domain_static
INNER JOIN storage_pool_iso_map ON storage_domain_static.id = storage_pool_iso_map.storage_id
INNER JOIN storage_pool ON storage_pool.id = storage_pool_iso_map.storage_pool_id
INNER JOIN vds_dynamic ON vds_dynamic.vds_id = storage_pool.spm_vds_id
LEFT OUTER JOIN repo_file_meta_data ON storage_pool_iso_map.storage_id = repo_file_meta_data.repo_domain_id;

CREATE OR REPLACE VIEW storage_for_image_view
AS
SELECT images.image_guid as image_id,
	   array_to_string(array_agg(storage_domain_static.storage), ',') as storage_path,
	   array_to_string(array_agg(storage_domain_static.id), ',') storage_id,
	   array_to_string(array_agg(storage_domain_static.storage_name), ',') as storage_name
FROM images
LEFT JOIN image_storage_domain_map ON image_storage_domain_map.image_id = images.image_guid
LEFT OUTER JOIN storage_domain_static ON image_storage_domain_map.storage_domain_id = storage_domain_static.id
GROUP BY images.image_guid;

CREATE OR REPLACE VIEW vm_images_view
AS
SELECT                storage_for_image_view.storage_id as storage_id, storage_for_image_view.storage_path as storage_path, storage_for_image_view.storage_name as storage_name,
					  images_storage_domain_view.storage_pool_id as storage_pool_id, images_storage_domain_view.image_guid as image_guid,
                      images_storage_domain_view.creation_date as creation_date, disk_image_dynamic.actual_size as actual_size, disk_image_dynamic.read_rate as read_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, disk_image_dynamic.write_rate as write_rate,
                      images_storage_domain_view.size as size, images_storage_domain_view.it_guid as it_guid,
                      images_storage_domain_view.description as description,
                      images_storage_domain_view.ParentId as ParentId, images_storage_domain_view.imageStatus as imageStatus, images_storage_domain_view.lastModified as lastModified,
                      images_storage_domain_view.app_list as app_list, images_storage_domain_view.vm_snapshot_id as vm_snapshot_id,
                      images_storage_domain_view.volume_type as volume_type, images_storage_domain_view.image_group_id as image_group_id,
                      images_storage_domain_view.active as active, images_storage_domain_view.volume_format as volume_format,
                      images_storage_domain_view.disk_interface as disk_interface, images_storage_domain_view.boot as boot, images_storage_domain_view.wipe_after_delete as wipe_after_delete, images_storage_domain_view.propagate_errors as propagate_errors,
                      images_storage_domain_view.entity_type as entity_type,images_storage_domain_view.number_of_vms as number_of_vms,images_storage_domain_view.vm_names as vm_names,
                      images_storage_domain_view.quota_id as quota_id, images_storage_domain_view.quota_name as quota_name, images_storage_domain_view.quota_enforcement_type,
                      images_storage_domain_view.disk_id, images_storage_domain_view.disk_alias as disk_alias, images_storage_domain_view.disk_description as disk_description,images_storage_domain_view.shareable as shareable
FROM         images_storage_domain_view
INNER JOIN disk_image_dynamic ON images_storage_domain_view.image_guid = disk_image_dynamic.image_id
INNER JOIN storage_for_image_view ON images_storage_domain_view.image_guid = storage_for_image_view.image_id
WHERE images_storage_domain_view.active = TRUE;


CREATE OR REPLACE VIEW all_disks
AS
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
FROM
(
    SELECT 0 AS disk_storage_type,
           storage_for_image_view.storage_id as storage_id, -- Storage fields
           storage_for_image_view.storage_path as storage_path,
           storage_for_image_view.storage_name as storage_name,
           storage_pool_id,
           image_guid, -- Image fields
           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, -- Snapshot fields
           ParentId,
           app_list,
           vm_snapshot_id,
           active,
           entity_type,
           number_of_vms,
           vm_names,
           quota_id, -- Quota fields
           quota_name,
           quota_enforcement_type,
           null AS lun_id, -- LUN fields
           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 images_storage_domain_view
    INNER JOIN storage_for_image_view ON images_storage_domain_view.image_guid = storage_for_image_view.image_id
    WHERE active = TRUE
    UNION
    SELECT 1 AS disk_storage_type,
           null AS storage_id, -- Storage domain fields
{'fraction_non_alphanumeric': 0.057426427019553625, 'fraction_numerical': 0.003555204424254395, 'mean_word_length': 3.911008729388943, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 4, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5888780', 'n_tokens_mistral': 6384, 'n_tokens_neox': 6134, 'n_words': 1196}