<commit_before>DROP VIEW auth_users;
DROP VIEW auth_user_groups;
DROP FUNCTION self_and_subgroups(integer);
DROP FUNCTION subgroups_append(integer, integer[]);

CREATE VIEW auth_users (login, password) AS
    SELECT login_values.value, password_values.value
    FROM contact_field_value AS login_values
    JOIN contact_field_value AS password_values ON (login_values.contact_id=password_values.contact_id AND password_values.contact_field_id=2)
    WHERE login_values.contact_field_id=1;

CREATE FUNCTION subgroups_append(integer, integer[]) RETURNS SETOF integer AS $$
    -- Get $2 as a setof (this sucks!)
    SELECT id FROM contact_group WHERE id=ANY($2)
    UNION
    -- Add direct children
    SELECT subgroup_id FROM group_in_group WHERE father_id=$1 AND NOT subgroup_id=ANY($2)
    UNION
    -- Add all familly of direct children's
--#TODO: create function self_and_directsubgroups pour éviter l'appel récursif si un enfant est enfant de lui même!
    SELECT subgroups_append(subgroup_id, $2) FROM group_in_group WHERE father_id=$1 AND NOT subgroup_id=ANY($2)
$$ LANGUAGE SQL STABLE;


CREATE FUNCTION self_and_subgroups(integer) RETURNS SETOF integer AS $$
    SELECT $1
    UNION
    SELECT subgroups_append($1, '{}'::integer[]);
$$ LANGUAGE SQL STABLE;


-- select contact_group.id, contact_group.name, array(select self_and_subgroups(id)) from contact_group;

-- admins:
-- select * from contact where exists (select * from contact_in_group where contact_id=contact.id and group_id in (select self_and_subgroups(8)));

-- SELECT contact_group.id, self_and_subgroups(contact_group.id) as sg FROM contact_group;

-- SELECT * FROM contact_in_group, (SELECT contact_group.id AS joined_group_id, self_and_subgroups(contact_group.id) as sub_group_id FROM contact_group) AS group_tree WHERE contact_in_group.group_id=group_tree.sub_group_id ORDER BY contact_id;
--  contact_id | group_id | operator | member | invited | joined_group_id | sub_group_id 
-- ------------+----------+----------+--------+---------+-----------------+--------------
--           1 |        8 | f        | t      | f       |               8 |            8
--           1 |        9 | f        | t      | f       |               9 |            9
--           1 |       35 | f        | t      | f       |              35 |           35
--           1 |       21 | f        | t      | f       |              21 |           21
--           1 |       20 | f        | t      | f       |              20 |           20
--           1 |       19 | f        | t      | f       |              19 |           19
--           1 |       34 | f        | t      | f       |              34 |           34
--           1 |       36 | f        | t      | f       |              36 |           36
--           1 |        5 | f        | t      | f       |               4 |            5<commit_msg>More research<commit_after>DROP VIEW auth_users;
DROP VIEW auth_user_groups;
DROP FUNCTION self_and_subgroups(integer);
DROP FUNCTION subgroups_append(integer, integer[]);

CREATE VIEW auth_users (login, password) AS
    SELECT login_values.value, password_values.value
    FROM contact_field_value AS login_values
    JOIN contact_field_value AS password_values ON (login_values.contact_id=password_values.contact_id AND password_values.contact_field_id=2)
    WHERE login_values.contact_field_id=1;

CREATE FUNCTION subgroups_append(integer, integer[]) RETURNS SETOF integer AS $$
    -- Get $2 as a setof (this sucks!)
    SELECT id FROM contact_group WHERE id=ANY($2)
    UNION
    -- Add direct children
    SELECT subgroup_id FROM group_in_group WHERE father_id=$1 AND NOT subgroup_id=ANY($2)
    UNION
    -- Add all familly of direct children's
--#TODO: create function self_and_directsubgroups pour éviter l'appel récursif si un enfant est enfant de lui même!
    SELECT subgroups_append(subgroup_id, $2) FROM group_in_group WHERE father_id=$1 AND NOT subgroup_id=ANY($2)
$$ LANGUAGE SQL STABLE;


CREATE FUNCTION self_and_subgroups(integer) RETURNS SETOF integer AS $$
    SELECT $1
    UNION
    SELECT subgroups_append($1, '{}'::integer[]);
$$ LANGUAGE SQL STABLE;



-- CREATE FUNCTION subgroups_append2(integer, integer[]) RETURNS SETOF integer AS $$
--    SELECT subgroup_id FROM group_in_group WHERE father_id=$1 AND NOT subgroup_id=ANY($2)
-- $$ LANGUAGE SQL STABLE;
-- select arraycontains(array[1,2,3], array[2]);a
-- select array_lower('{7,8,9}'::int[], 1);
-- select array_upper('{7,8,9}'::int[], 1);
-- select ('{7,8,9}'::int[])[2];


-- select contact_group.id, contact_group.name, array(select self_and_subgroups(id)) from contact_group;

-- admins:
-- select * from contact where exists (select * from contact_in_group where contact_id=contact.id and group_id in (select self_and_subgroups(8)));

-- SELECT contact_group.id, self_and_subgroups(contact_group.id) as sg FROM contact_group;

-- SELECT * FROM contact_in_group, (SELECT contact_group.id AS joined_group_id, self_and_subgroups(contact_group.id) as sub_group_id FROM contact_group) AS group_tree WHERE contact_in_group.group_id=group_tree.sub_group_id ORDER BY contact_id;
--  contact_id | group_id | operator | member | invited | joined_group_id | sub_group_id 
-- ------------+----------+----------+--------+---------+-----------------+--------------
--           1 |        8 | f        | t      | f       |               8 |            8
--           1 |        9 | f        | t      | f       |               9 |            9
--           1 |       35 | f        | t      | f       |              35 |           35
--           1 |       21 | f        | t      | f       |              21 |           21
--           1 |       20 | f        | t      | f       |              20 |           20
--           1 |       19 | f        | t      | f       |              19 |           19
--           1 |       34 | f        | t      | f       |              34 |           34
--           1 |       36 | f        | t      | f       |              36 |           36
--           1 |        5 | f        | t      | f       |               4 |            5
{'fraction_non_alphanumeric': 0.11725155788783208, 'fraction_numerical': 0.024762217120367334, 'mean_word_length': 1.808011049723757, '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': 36, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2585753', 'n_tokens_mistral': 1879, 'n_tokens_neox': 1785, 'n_words': 513}