Michael Kublin has uploaded a new change for review. Change subject: engine: Improve permissions queries ......................................................................
engine: Improve permissions queries The following patch contains two improvements: 1. I removed unneeded call to some functions at permission_view, and called it small_permission_view. The following functions were perform some calculations which is not needed. 2. I introduced a new splitter function and I using it with out join and IN expression With multiple paragraphs if necessary. Change-Id: I62fa797fbf286513183f0a3c4409ce79bec35342 Signed-off-by: Michael Kublin <[email protected]> --- M backend/manager/dbscripts/create_functions.sql M backend/manager/dbscripts/create_views.sql 2 files changed, 55 insertions(+), 41 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-engine refs/changes/25/9825/1 diff --git a/backend/manager/dbscripts/create_functions.sql b/backend/manager/dbscripts/create_functions.sql index ff36956..bfce725 100644 --- a/backend/manager/dbscripts/create_functions.sql +++ b/backend/manager/dbscripts/create_functions.sql @@ -31,7 +31,15 @@ END; $function$ LANGUAGE plpgsql IMMUTABLE; - +CREATE OR REPLACE FUNCTION public.fnNewSplitter(ids TEXT) RETURNS SETOF UUID AS +$function$ +BEGIN + IF ids != '' THEN + RETURN QUERY + SELECT CAST(regexp_split_to_table(ids, ',') AS UUID); + END IF; +END; $function$ +LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION public.fnSplitter(ids TEXT) RETURNS SETOF idTextType AS diff --git a/backend/manager/dbscripts/create_views.sql b/backend/manager/dbscripts/create_views.sql index 24bbbb8..bcbc3a0 100644 --- a/backend/manager/dbscripts/create_views.sql +++ b/backend/manager/dbscripts/create_views.sql @@ -775,6 +775,14 @@ FROM permissions INNER JOIN roles ON permissions.role_id = roles.id; +CREATE OR REPLACE VIEW small_permissions_view +AS + +SELECT permissions.id as id, permissions.role_id as role_id, permissions.ad_element_id as ad_element_id, permissions.object_id as object_id, permissions.object_type_id as object_type_id, + roles.name as role_name, roles.role_type as role_type, roles.allows_viewing_children as allows_viewing_children +FROM permissions INNER JOIN +roles ON permissions.role_id = roles.id; + -- --SELECT storages.id, storages.storage, storages.storage_pool_id, storages.storage_type, storage_pool.name, @@ -1089,10 +1097,8 @@ -- Flatten all the objects a user can get permissions on them CREATE OR REPLACE VIEW user_flat_groups AS --- User with all its groups -SELECT users.user_id AS user_id, ad_groups.id AS granted_id -FROM ad_groups, users -WHERE ad_groups.id IN (SELECT id FROM fnsplitteruuid(users.group_ids)) +SELECT users.user_id AS user_id, fnNewSplitter(users.group_ids) AS granted_id +FROM users UNION ALL -- The user itself SELECT user_id, user_id FROM users @@ -1106,22 +1112,22 @@ CREATE OR REPLACE VIEW user_vds_groups_permissions_view_base (entity_id, granted_id) AS SELECT object_id, ad_element_id -FROM permissions_view +FROM small_permissions_view WHERE object_type_id = 9 AND role_type = 2 -- Or the object is a VM in the cluster UNION ALL SELECT vds_group_id, ad_element_id FROM vm_static -INNER JOIN permissions_view ON object_id = vm_guid AND object_type_id = 2 AND role_type=2 +INNER JOIN small_permissions_view ON object_id = vm_guid AND object_type_id = 2 AND role_type=2 -- Or the object is the Data Center containing the Cluster UNION ALL SELECT vds_group_id, ad_element_id FROM vds_groups -INNER JOIN permissions_view ON object_id = vds_groups.storage_pool_id AND object_type_id = 14 AND role_type = 2 +INNER JOIN small_permissions_view ON object_id = vds_groups.storage_pool_id AND object_type_id = 14 AND role_type = 2 -- Or the user has permissions on system; UNION ALL SELECT vds_group_id, ad_element_id -FROM permissions_view +FROM small_permissions_view CROSS JOIN vds_groups WHERE object_type_id = 1 AND role_type=2; @@ -1137,29 +1143,29 @@ CREATE OR REPLACE VIEW user_storage_pool_permissions_view_base (entity_id, granted_id) AS SELECT object_id, ad_element_id -FROM permissions_view +FROM small_permissions_view WHERE object_type_id = 14 AND role_type = 2 -- Or the object is a cluster in the data center UNION ALL SELECT storage_pool_id, ad_element_id FROM vds_groups -INNER JOIN permissions_view ON object_id = vds_groups.vds_group_id AND object_type_id = 9 AND role_type = 2 +INNER JOIN small_permissions_view ON object_id = vds_groups.vds_group_id AND object_type_id = 9 AND role_type = 2 -- Or the object is vm pool in the data center UNION ALL SELECT storage_pool_id, ad_element_id FROM vds_groups INNER JOIN vm_pools ON vds_groups.vds_group_id = vm_pools.vds_group_id -INNER JOIN permissions_view ON object_id = vm_pools.vm_pool_id AND object_type_id = 5 AND role_type = 2 +INNER JOIN small_permissions_view ON object_id = vm_pools.vm_pool_id AND object_type_id = 5 AND role_type = 2 -- Or the object is a VM in the data center UNION ALL SELECT storage_pool_id, ad_element_id FROM vm_static INNER JOIN vds_groups ON vds_groups.vds_group_id = vm_static.vds_group_id -INNER JOIN permissions_view ON object_id = vm_guid AND object_type_id = 2 AND role_type = 2 +INNER JOIN small_permissions_view ON object_id = vm_guid AND object_type_id = 2 AND role_type = 2 -- Or the user has permission on system UNION ALL SELECT storage_pool.id, ad_element_id -FROM permissions_view +FROM small_permissions_view CROSS JOIN storage_pool WHERE object_type_id = 1 AND role_type = 2; @@ -1175,7 +1181,7 @@ CREATE OR REPLACE VIEW user_storage_domain_permissions_view_base (entity_id, granted_id) AS SELECT object_id, ad_element_id -FROM permissions_view +FROM small_permissions_view WHERE object_type_id = 11 AND role_type = 2 -- Or the user has permissions on a VM in the storage domain UNION ALL @@ -1183,27 +1189,27 @@ FROM storage_domains INNER JOIN vds_groups ON vds_groups.storage_pool_id = storage_domains.storage_pool_id INNER JOIN vm_static ON vds_groups.vds_group_id = vm_static.vds_group_id -INNER JOIN permissions_view ON object_id = vm_static.vm_guid AND object_type_id = 2 AND role_type = 2 +INNER JOIN small_permissions_view ON object_id = vm_static.vm_guid AND object_type_id = 2 AND role_type = 2 -- Or the user has permissions on a template in the storage domain UNION ALL SELECT storage_id, ad_element_id FROM vm_templates_storage_domain -INNER JOIN permissions_view ON vmt_guid = permissions_view.object_id AND object_type_id = 4 AND role_type = 2 +INNER JOIN small_permissions_view ON vmt_guid = small_permissions_view.object_id AND object_type_id = 4 AND role_type = 2 -- Or the user has permissions on a VM created from a template in the storage domain UNION ALL SELECT storage_id, ad_element_id FROM vm_static INNER JOIN vm_templates_storage_domain ON vm_static.vmt_guid = vm_templates_storage_domain.vmt_guid -INNER JOIN permissions_view ON vm_static.vm_guid = object_id AND objecT_type_id = 2 AND role_type = 2 +INNER JOIN small_permissions_view ON vm_static.vm_guid = object_id AND objecT_type_id = 2 AND role_type = 2 -- Or the user has permissions on the Data Center containing the storage domain UNION ALL SELECT storage_domains.id, ad_element_id FROM storage_domains -INNER JOIN permissions_view ON object_id = storage_domains.storage_pool_id AND object_type_id = 14 AND role_type = 2 +INNER JOIN small_permissions_view ON object_id = storage_domains.storage_pool_id AND object_type_id = 14 AND role_type = 2 -- Or the user has permissions on System UNION ALL SELECT storage_domains.id, ad_element_id -FROM permissions_view +FROM small_permissions_view CROSS JOIN storage_domains WHERE object_type_id = 1 AND role_type = 2; @@ -1219,18 +1225,18 @@ CREATE OR REPLACE VIEW user_vds_permissions_view_base (entity_id, granted_id) AS SELECT object_id, ad_element_id -FROM permissions_view +FROM small_permissions_view WHERE object_type_id = 3 AND role_type = 2 -- Or the user has permissions on a VM in the cluster or Data Center that contains the host UNION ALL SELECT vds_id, ad_element_id FROM vds -INNER JOIN permissions_view ON (object_id = vds_group_id AND object_type_id = 9) OR +INNER JOIN small_permissions_view ON (object_id = vds_group_id AND object_type_id = 9) OR (object_id = storage_pool_id AND object_type_id = 14) AND role_type = 2 -- Or the user has permissions on System UNION ALL SELECT vds_id, ad_element_id -FROM permissions_view +FROM small_permissions_view CROSS JOIN vds WHERE object_type_id = 1 AND role_type = 2; @@ -1246,28 +1252,28 @@ CREATE OR REPLACE VIEW user_vm_pool_permissions_view_base (entity_id, granted_id) AS SELECT object_id, ad_element_id -FROM permissions_view +FROM small_permissions_view WHERE object_type_id = 5 AND role_type = 2 -- Or the user has permissions on a VM from the pool UNION ALL SELECT vm_pool_id, ad_element_id FROM vm_pool_map -INNER JOIN permissions_view ON object_id = vm_guid AND object_type_id = 2 AND role_type = 2 +INNER JOIN small_permissions_view ON object_id = vm_guid AND object_type_id = 2 AND role_type = 2 -- Or the user has permissions on the cluster containing the pool UNION ALL SELECT vm_pool_id, ad_element_id FROM vm_pools -INNER JOIN permissions_view ON object_id = vds_group_id AND object_type_id = 9 AND allows_viewing_children AND role_type = 2 +INNER JOIN small_permissions_view ON object_id = vds_group_id AND object_type_id = 9 AND allows_viewing_children AND role_type = 2 -- Or the user has permission on the data center containing the VM pool UNION ALL SELECT vm_pool_id, ad_element_id FROM vm_pools INNER JOIN vds_groups ON vm_pools.vds_group_id = vds_groups.vds_group_id -INNER JOIN permissions_view ON object_id = storage_pool_id AND object_type_id = 14 AND allows_viewing_children AND role_type = 2 +INNER JOIN small_permissions_view ON object_id = storage_pool_id AND object_type_id = 14 AND allows_viewing_children AND role_type = 2 -- Or the user has permissions on System UNION ALL SELECT vm_pool_id, ad_element_id -FROM permissions_view +FROM small_permissions_view CROSS JOIN vm_pools WHERE object_type_id = 1 AND allows_viewing_children AND role_type = 2; @@ -1283,23 +1289,23 @@ CREATE OR REPLACE VIEW user_vm_template_permissions_view_base (entity_id, granted_id) AS SELECT object_id, ad_element_id -FROM permissions_view +FROM small_permissions_view WHERE object_type_id = 4 AND role_type = 2 -- Or the user has permissions on a VM created from the tempalate UNION ALL SELECT vmt_guid, ad_element_id FROM vm_static -INNER JOIN permissions_view ON object_id = vm_static.vm_guid AND object_type_id = 2 AND role_type = 2 +INNER JOIN small_permissions_view ON object_id = vm_static.vm_guid AND object_type_id = 2 AND role_type = 2 -- Or the user has permissions on the data center containing the template UNION ALL SELECT vmt_guid, ad_element_id FROM vm_static INNER JOIN vds_groups ON vds_groups.vds_group_id = vm_static.vds_group_id -INNER JOIN permissions_view ON object_id = storage_pool_id AND object_type_id = 14 AND allows_viewing_children AND role_type = 2 +INNER JOIN small_permissions_view ON object_id = storage_pool_id AND object_type_id = 14 AND allows_viewing_children AND role_type = 2 -- Or the user has permissions on system UNION ALL SELECT vm_guid, ad_element_id -FROM permissions_view +FROM small_permissions_view CROSS JOIN vm_static WHERE object_type_id = 1 AND allows_viewing_children AND role_type = 2 AND vm_static.entity_type::text = 'TEMPLATE'::text; @@ -1315,23 +1321,23 @@ CREATE OR REPLACE VIEW user_vm_permissions_view_base (entity_id, granted_id) AS SELECT object_id, ad_element_id -FROM permissions_view +FROM small_permissions_view WHERE object_type_id = 2 AND role_type = 2 -- Or the user has permissions on the cluster containing the VM UNION ALL SELECT vm_guid, ad_element_id FROM vm_static -INNER JOIN permissions_view ON object_id = vds_group_id AND object_type_id = 9 AND allows_viewing_children AND role_type = 2 +INNER JOIN small_permissions_view ON object_id = vds_group_id AND object_type_id = 9 AND allows_viewing_children AND role_type = 2 -- Or the user has permissions on the data center containing the VM UNION ALL SELECT vm_guid, ad_element_id FROM vm_static INNER JOIN vds_groups ON vds_groups.vds_group_id = vm_static.vds_group_id -INNER JOIN permissions_view ON object_id = storage_pool_id AND object_type_id = 14 AND allows_viewing_children AND role_type = 2 +INNER JOIN small_permissions_view ON object_id = storage_pool_id AND object_type_id = 14 AND allows_viewing_children AND role_type = 2 -- Or the user has permissions on system UNION ALL SELECT vm_guid, ad_element_id -FROM permissions_view +FROM small_permissions_view CROSS JOIN vm_static WHERE object_type_id = 1 AND allows_viewing_children AND role_type = 2; @@ -1346,7 +1352,7 @@ CREATE OR REPLACE VIEW user_disk_permissions_view_base (entity_id, granted_id) AS SELECT object_id, ad_element_id -FROM permissions_view +FROM small_permissions_view WHERE object_type_id = 19 AND role_type = 2 -- Or the user has permissions on the VM the disk is attached to UNION ALL @@ -1365,18 +1371,18 @@ SELECT images.image_group_id, ad_element_id FROM image_storage_domain_map INNER JOIN images ON images.image_guid = image_storage_domain_map.image_id -INNER JOIN permissions_view ON object_id = storage_domain_id AND object_type_id = 11 AND allows_viewing_children AND role_type = 2 +INNER JOIN small_permissions_view ON object_id = storage_domain_id AND object_type_id = 11 AND allows_viewing_children AND role_type = 2 -- Or the user has permissions on the data center containing the storage pool constaining the disk UNION ALL SELECT images.image_group_id, ad_element_id FROM image_storage_domain_map INNER JOIN storage_pool_iso_map ON image_storage_domain_map.storage_domain_id = storage_pool_iso_map.storage_id INNER JOIN images ON images.image_guid = image_storage_domain_map.image_id -INNER JOIN permissions_view ON object_id = storage_pool_id AND object_type_id = 14 AND allows_viewing_children AND role_type = 2 +INNER JOIN small_permissions_view ON object_id = storage_pool_id AND object_type_id = 14 AND allows_viewing_children AND role_type = 2 -- Or the user has permissions on system UNION ALL SELECT device_id, ad_element_id -FROM permissions_view +FROM small_permissions_view CROSS JOIN vm_device WHERE object_type_id = 1 AND allows_viewing_children AND role_type = 2; @@ -1390,7 +1396,7 @@ CREATE OR REPLACE VIEW user_permissions_permissions_view (entity_id, user_id) AS SELECT DISTINCT id, user_id -FROM permissions_view +FROM small_permissions_view JOIN user_flat_groups ON granted_id = ad_element_id; -- To view, visit http://gerrit.ovirt.org/9825 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I62fa797fbf286513183f0a3c4409ce79bec35342 Gerrit-PatchSet: 1 Gerrit-Project: ovirt-engine Gerrit-Branch: master Gerrit-Owner: Michael Kublin <[email protected]> _______________________________________________ Engine-patches mailing list [email protected] http://lists.ovirt.org/mailman/listinfo/engine-patches
