<commit_before>
    -- Constraint is not used dropping it to clean the dependency before dropping the function.
    ----------------------------------
-- 		create functions		--
----------------------------------

DROP TYPE IF EXISTS idTextType CASCADE;
DROP TYPE IF EXISTS idUuidType CASCADE;
DROP TYPE IF EXISTS booleanResultType CASCADE;
CREATE TYPE idTextType AS(id text);
CREATE TYPE idUuidType AS(id UUID);
CREATE TYPE booleanResultType AS(result BOOLEAN);


CREATE OR REPLACE FUNCTION getGlobalIds(v_name VARCHAR(4000))
RETURNS UUID
   AS $function$
   DECLARE
   v_id  UUID;
BEGIN
   if (v_name = 'system') then
      v_id := 'AAA00000-0000-0000-0000-123456789AAA';
   elsif (v_name = 'everyone') then
      v_id := 'EEE00000-0000-0000-0000-123456789EEE';
   -- bottom is an object which all the objects in the system are its parents
   -- useful to denote we want all objects when checking for permissions
   elsif (v_name = 'bottom') then
      v_id := 'BBB00000-0000-0000-0000-123456789BBB';
   end if;
   return  v_id;
END; $function$
LANGUAGE plpgsql IMMUTABLE;




CREATE OR REPLACE FUNCTION public.fnSplitter(ids TEXT)  RETURNS SETOF idTextType AS
$function$
BEGIN
	RETURN QUERY
		SELECT regexp_split_to_table(ids, ',') AS id;
END; $function$
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION fnSplitterUuid(ids TEXT)  RETURNS SETOF idUuidType AS
$function$
DECLARE
    tempc REFCURSOR;
    currow TEXT;
    result idUuidType;
BEGIN
  IF ids != '' THEN
    OPEN tempc FOR SELECT regexp_split_to_table(ids, ',');
    FETCH tempc INTO currow;
    WHILE FOUND LOOP
        result := CAST (ROW(currow) AS idUuidType);
        RETURN NEXT result;
        FETCH tempc INTO currow;
   END LOOP;
   CLOSE tempc;
  END IF;
END; $function$
LANGUAGE plpgsql;



--All permissions of current user (include groups)
DROP TYPE IF EXISTS user_permissions CASCADE;
CREATE TYPE user_permissions AS(permission_id UUID, role_id UUID, user_id UUID);
CREATE OR REPLACE FUNCTION public.fn_user_permissions(v_userId IN uuid) RETURNS SETOF user_permissions AS
$function$
DECLARE

BEGIN
	RETURN QUERY
	    SELECT
		    permissions.id AS permission_id,
		    permissions.role_id,
		    permissions.ad_element_id AS user_id
	    FROM permissions
		INNER JOIN users ON permissions.ad_element_id = users.user_id
	    WHERE users.user_id = v_userId

	    UNION

	    SELECT
		    permissions.id AS permission_id,
		    permissions.role_id,
		    temp.user_id AS user_id
	    FROM permissions INNER JOIN
	    (
		    -- get all groups of admin users
		    SELECT ad_groups.id group_id, users.user_id
		    FROM ad_groups, users
		    WHERE ad_groups.id IN
		    (SELECT * FROM fnsplitteruuid(users.group_ids))
			AND users.user_id = v_userId ) temp
		ON permissions.ad_element_id = temp.group_id;

END; $function$
LANGUAGE 'plpgsql';



CREATE OR REPLACE FUNCTION get_all_child_roles_of_role(v_roleId UUID)
RETURNS SETOF idUuidType
   AS $function$
   DECLARE
   SWV_Rs idUuidType;
BEGIN
   BEGIN
      CREATE GLOBAL TEMPORARY TABLE tt_TEMP
      (
         id UUID
      ) WITH OIDS;
      exception when others then
         truncate table tt_TEMP;
   END;
   insert into tt_TEMP(id) values(v_roleId);

   insert into tt_TEMP   with recursive c(role_id,role_container_id)
   as(select role_id AS role_id, role_container_id AS role_container_id from roles_relations
   where role_container_id = v_roleId
   union all
   select t.role_id AS role_id, t.role_container_id AS role_container_id
   from c join roles_relations t on c.role_id = t.role_container_id) select distinct role_id from c;

   FOR SWV_Rs IN(SELECT * FROM  tt_TEMP) LOOP
      RETURN NEXT SWV_Rs;
   END LOOP;
   RETURN;
END; $function$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.fn_get_entity_parents(v_entity_id IN uuid, v_object_type IN int4) RETURNS SETOF idUuidType AS
$function$
/*	Gets a list of all parent GUID to the system root (including)

	Object Types (compatible with VdcObjectType, XXX entries are unused currently)
		Unknown XXX,
		System XXX,
        Bottom = 0,
		VM = 2,
		VDS = 3,
		VmTemplate = 4,
		VmPool = 5,
		AdElements XXX,
		Tags XXX,
		Bookmarks XXX,
		VdsGroups = 9,
		MultiLevelAdministration XXX,
		Storage = 11,
		EventNotification XXX,
		ImportExport XXX,
		StoragePool = 14,
		User = 15,
		Role = 16,
		Quota = 17,
		GlusterVolume = 18,
        Disk = 19,
        VmInterface = 20
*/
DECLARE
	v_entity_type int4 := v_object_type;
	system_root_id uuid;
	cluster_id uuid;
	ds_id uuid;
	v_image_id uuid;
	v_storage_id uuid;
    v_vm_id uuid;
    v_vm_interface_id uuid;
    v_storage_pool_id uuid;

BEGIN

	system_root_id := ( SELECT getGlobalIds('system') ); -- hardcoded also in MLA Handler
	CASE
	WHEN v_entity_type = 0 THEN -- Bottom
		RETURN QUERY
			SELECT object_id
			FROM permissions;
	WHEN v_entity_type = 2 THEN -- VM
        -- get cluster id
		cluster_id := ( SELECT vds_group_id FROM vm_static WHERE vm_guid = v_entity_id );
		-- get data center id
		ds_id := ( SELECT storage_pool_id FROM vds_groups WHERE vds_group_id = cluster_id );

		RETURN QUERY
			SELECT system_root_id AS id
			UNION
			SELECT ds_id AS id
			UNION
			SELECT cluster_id AS id
			UNION
			SELECT v_entity_id AS id;
	WHEN v_entity_type = 3 THEN -- VDS
        -- get cluster id
		cluster_id := ( SELECT vds_group_id FROM vds_static WHERE vds_id = v_entity_id );
		-- get data center id
		ds_id := ( SELECT storage_pool_id FROM vds_groups WHERE vds_group_id = cluster_id );

		RETURN QUERY
			SELECT system_root_id AS id
			UNION
			SELECT ds_id AS id
			UNION
			SELECT cluster_id AS id
			UNION
			SELECT v_entity_id AS id;
	WHEN v_entity_type = 4 THEN -- Template
		-- get image id first
		v_image_id := ( SELECT image_guid FROM images i JOIN vm_device vd ON i.image_group_id = vd.device_id WHERE vm_id = v_entity_id limit 1);
		-- get the storage id from images
		v_storage_id := ( SELECT storage_domain_id FROM image_storage_domain_map WHERE image_id = v_image_id limit 1);
		-- finally get data center id
		ds_id := ( SELECT storage_pool_id FROM storage_pool_iso_map WHERE storage_id = v_storage_id );

		RETURN QUERY
			SELECT system_root_id AS id
			UNION
			SELECT ds_id AS id
			UNION
			SELECT v_entity_id AS id;
	WHEN v_entity_type = 5 THEN -- VM Pool
        -- get cluster id
		cluster_id := ( SELECT vds_group_id FROM vm_pools WHERE vm_pool_id = v_entity_id );
		-- get data center id
		ds_id := ( SELECT storage_pool_id FROM vds_groups WHERE vds_group_id = cluster_id );

		RETURN QUERY
			SELECT system_root_id AS id
			UNION
			SELECT ds_id AS id
			UNION
			SELECT cluster_id AS id
			UNION
			SELECT v_entity_id AS id;
	WHEN v_entity_type = 9 THEN -- Cluster
		-- get data center id
		ds_id := ( SELECT storage_pool_id FROM vds_groups WHERE vds_group_id = v_entity_id );

		RETURN QUERY
			SELECT system_root_id AS id
			UNION
			SELECT ds_id AS id
			UNION
			SELECT v_entity_id AS id;
	WHEN v_entity_type = 11 THEN -- Storage Domain

		RETURN QUERY
			SELECT system_root_id AS id
			UNION
			SELECT storage_pool_id as id FROM storage_pool_iso_map WHERE storage_id = v_entity_id
			UNION
			SELECT v_entity_id AS id;
	WHEN v_entity_type = 17 THEN -- Quota
		-- get data center id
		ds_id := ( SELECT storage_pool_id FROM quota WHERE id = v_entity_id );

		RETURN QUERY
			SELECT system_root_id AS id
			UNION
			SELECT ds_id AS id
			UNION
			SELECT v_entity_id AS id;
	WHEN v_entity_type = 18 THEN -- GlusterVolume
        -- get cluster id
		cluster_id := ( SELECT v.cluster_id FROM gluster_volumes v WHERE id = v_entity_id );
		-- get data center id
		ds_id := ( SELECT storage_pool_id FROM vds_groups WHERE vds_group_id = cluster_id );
<commit_msg>core: Failed to activate ISO domain ...(#854492)<commit_after>
    -- Constraint is not used dropping it to clean the dependency before dropping the function.
    ----------------------------------
-- 		create functions		--
----------------------------------

DROP TYPE IF EXISTS idTextType CASCADE;
DROP TYPE IF EXISTS idUuidType CASCADE;
DROP TYPE IF EXISTS booleanResultType CASCADE;
CREATE TYPE idTextType AS(id text);
CREATE TYPE idUuidType AS(id UUID);
CREATE TYPE booleanResultType AS(result BOOLEAN);


CREATE OR REPLACE FUNCTION getGlobalIds(v_name VARCHAR(4000))
RETURNS UUID
   AS $function$
   DECLARE
   v_id  UUID;
BEGIN
   if (v_name = 'system') then
      v_id := 'AAA00000-0000-0000-0000-123456789AAA';
   elsif (v_name = 'everyone') then
      v_id := 'EEE00000-0000-0000-0000-123456789EEE';
   -- bottom is an object which all the objects in the system are its parents
   -- useful to denote we want all objects when checking for permissions
   elsif (v_name = 'bottom') then
      v_id := 'BBB00000-0000-0000-0000-123456789BBB';
   end if;
   return  v_id;
END; $function$
LANGUAGE plpgsql IMMUTABLE;




CREATE OR REPLACE FUNCTION public.fnSplitter(ids TEXT)  RETURNS SETOF idTextType AS
$function$
BEGIN
	RETURN QUERY
		SELECT regexp_split_to_table(ids, ',') AS id;
END; $function$
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION fnSplitterUuid(ids TEXT)  RETURNS SETOF idUuidType AS
$function$
DECLARE
    tempc REFCURSOR;
    currow TEXT;
    result idUuidType;
BEGIN
  IF ids != '' THEN
    OPEN tempc FOR SELECT regexp_split_to_table(ids, ',');
    FETCH tempc INTO currow;
    WHILE FOUND LOOP
        result := CAST (ROW(currow) AS idUuidType);
        RETURN NEXT result;
        FETCH tempc INTO currow;
   END LOOP;
   CLOSE tempc;
  END IF;
END; $function$
LANGUAGE plpgsql;



--All permissions of current user (include groups)
DROP TYPE IF EXISTS user_permissions CASCADE;
CREATE TYPE user_permissions AS(permission_id UUID, role_id UUID, user_id UUID);
CREATE OR REPLACE FUNCTION public.fn_user_permissions(v_userId IN uuid) RETURNS SETOF user_permissions AS
$function$
DECLARE

BEGIN
	RETURN QUERY
	    SELECT
		    permissions.id AS permission_id,
		    permissions.role_id,
		    permissions.ad_element_id AS user_id
	    FROM permissions
		INNER JOIN users ON permissions.ad_element_id = users.user_id
	    WHERE users.user_id = v_userId

	    UNION

	    SELECT
		    permissions.id AS permission_id,
		    permissions.role_id,
		    temp.user_id AS user_id
	    FROM permissions INNER JOIN
	    (
		    -- get all groups of admin users
		    SELECT ad_groups.id group_id, users.user_id
		    FROM ad_groups, users
		    WHERE ad_groups.id IN
		    (SELECT * FROM fnsplitteruuid(users.group_ids))
			AND users.user_id = v_userId ) temp
		ON permissions.ad_element_id = temp.group_id;

END; $function$
LANGUAGE 'plpgsql';



CREATE OR REPLACE FUNCTION get_all_child_roles_of_role(v_roleId UUID)
RETURNS SETOF idUuidType
   AS $function$
   DECLARE
   SWV_Rs idUuidType;
BEGIN
   BEGIN
      CREATE GLOBAL TEMPORARY TABLE tt_TEMP
      (
         id UUID
      ) WITH OIDS;
      exception when others then
         truncate table tt_TEMP;
   END;
   insert into tt_TEMP(id) values(v_roleId);

   insert into tt_TEMP   with recursive c(role_id,role_container_id)
   as(select role_id AS role_id, role_container_id AS role_container_id from roles_relations
   where role_container_id = v_roleId
   union all
   select t.role_id AS role_id, t.role_container_id AS role_container_id
   from c join roles_relations t on c.role_id = t.role_container_id) select distinct role_id from c;

   FOR SWV_Rs IN(SELECT * FROM  tt_TEMP) LOOP
      RETURN NEXT SWV_Rs;
   END LOOP;
   RETURN;
END; $function$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.fn_get_entity_parents(v_entity_id IN uuid, v_object_type IN int4) RETURNS SETOF idUuidType AS
$function$
/*	Gets a list of all parent GUID to the system root (including)

	Object Types (compatible with VdcObjectType, XXX entries are unused currently)
		Unknown XXX,
		System XXX,
        Bottom = 0,
		VM = 2,
		VDS = 3,
		VmTemplate = 4,
		VmPool = 5,
		AdElements XXX,
		Tags XXX,
		Bookmarks XXX,
		VdsGroups = 9,
		MultiLevelAdministration XXX,
		Storage = 11,
		EventNotification XXX,
		ImportExport XXX,
		StoragePool = 14,
		User = 15,
		Role = 16,
		Quota = 17,
		GlusterVolume = 18,
        Disk = 19,
        VmInterface = 20
*/
DECLARE
	v_entity_type int4 := v_object_type;
	system_root_id uuid;
	cluster_id uuid;
	ds_id uuid;
	v_image_id uuid;
	v_storage_id uuid;
    v_vm_id uuid;
    v_vm_interface_id uuid;
    v_storage_pool_id uuid;

BEGIN

	system_root_id := ( SELECT getGlobalIds('system') ); -- hardcoded also in MLA Handler
	CASE
	WHEN v_entity_type = 0 THEN -- Bottom
		RETURN QUERY
			SELECT object_id
			FROM permissions;
	WHEN v_entity_type = 2 THEN -- VM
        -- get cluster id
		cluster_id := ( SELECT vds_group_id FROM vm_static WHERE vm_guid = v_entity_id );
		-- get data center id
		ds_id := ( SELECT storage_pool_id FROM vds_groups WHERE vds_group_id = cluster_id );

		RETURN QUERY
			SELECT system_root_id AS id
			UNION
			SELECT ds_id AS id
			UNION
			SELECT cluster_id AS id
			UNION
			SELECT v_entity_id AS id;
	WHEN v_entity_type = 3 THEN -- VDS
        -- get cluster id
		cluster_id := ( SELECT vds_group_id FROM vds_static WHERE vds_id = v_entity_id );
		-- get data center id
		ds_id := ( SELECT storage_pool_id FROM vds_groups WHERE vds_group_id = cluster_id );

		RETURN QUERY
			SELECT system_root_id AS id
			UNION
			SELECT ds_id AS id
			UNION
			SELECT cluster_id AS id
			UNION
			SELECT v_entity_id AS id;
	WHEN v_entity_type = 4 THEN -- Template
		-- get image id first
		v_image_id := ( SELECT image_guid FROM images i JOIN vm_device vd ON i.image_group_id = vd.device_id WHERE vm_id = v_entity_id limit 1);
		-- get the storage id from images
		v_storage_id := ( SELECT storage_domain_id FROM image_storage_domain_map WHERE image_id = v_image_id limit 1);
		-- finally get data center id
		ds_id := ( SELECT storage_pool_id FROM storage_pool_iso_map WHERE storage_id = v_storage_id );

		RETURN QUERY
			SELECT system_root_id AS id
			UNION
			SELECT ds_id AS id
			UNION
			SELECT v_entity_id AS id;
	WHEN v_entity_type = 5 THEN -- VM Pool
        -- get cluster id
		cluster_id := ( SELECT vds_group_id FROM vm_pools WHERE vm_pool_id = v_entity_id );
		-- get data center id
		ds_id := ( SELECT storage_pool_id FROM vds_groups WHERE vds_group_id = cluster_id );

		RETURN QUERY
			SELECT system_root_id AS id
			UNION
			SELECT ds_id AS id
			UNION
			SELECT cluster_id AS id
			UNION
			SELECT v_entity_id AS id;
	WHEN v_entity_type = 9 THEN -- Cluster
		-- get data center id
		ds_id := ( SELECT storage_pool_id FROM vds_groups WHERE vds_group_id = v_entity_id );

		RETURN QUERY
			SELECT system_root_id AS id
			UNION
			SELECT ds_id AS id
			UNION
			SELECT v_entity_id AS id;
	WHEN v_entity_type = 11 THEN -- Storage Domain

		RETURN QUERY
			SELECT system_root_id AS id
			UNION ALL
			SELECT storage_pool_id as id FROM storage_pool_iso_map WHERE storage_id = v_entity_id
			UNION
			SELECT v_entity_id AS id;
	WHEN v_entity_type = 17 THEN -- Quota
		-- get data center id
		ds_id := ( SELECT storage_pool_id FROM quota WHERE id = v_entity_id );

		RETURN QUERY
			SELECT system_root_id AS id
			UNION
			SELECT ds_id AS id
			UNION
			SELECT v_entity_id AS id;
	WHEN v_entity_type = 18 THEN -- GlusterVolume
        -- get cluster id
		cluster_id := ( SELECT v.cluster_id FROM gluster_volumes v WHERE id = v_entity_id );
		-- get data center id
		ds_id := ( SELECT storage_pool_id FROM vds_groups WHERE vds_group_id = cluster_id );

{'fraction_non_alphanumeric': 0.06724059218678667, 'fraction_numerical': 0.016043827039718253, 'mean_word_length': 3.18161985274066, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, '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': '6262813', 'n_tokens_mistral': 5923, 'n_tokens_neox': 5200, 'n_words': 1707}