+ ActiveRecord::Base.connection.execute("INSERT INTO trashed_groups select * from compute_trashed()")
+
+ # Get a set of permission by searching the graph and following
+ # ownership and permission links.
+ #
+ # edges() - a subselect with the union of ownership and permission links
+ #
+ # traverse_graph() - recursive query, from the starting node,
+ # self-join with edges to find outgoing permissions.
+ # Re-runs the query on new rows until there are no more results.
+ # This accomplishes a breadth-first search of the permission graph.
+ #
+ ActiveRecord::Base.connection.execute %{
+create or replace function search_permission_graph (starting_uuid varchar(27),
+ starting_perm integer)
+ returns table (target_uuid varchar(27), val integer, traverse_owned bool)
+STABLE
+language SQL
+as $$
+WITH RECURSIVE edges(tail_uuid, head_uuid, val) as (
+ select groups.owner_uuid, groups.uuid, (3) from groups
+ union
+ select links.tail_uuid,
+ links.head_uuid,
+ CASE
+ WHEN links.name = 'can_read' THEN 1
+ WHEN links.name = 'can_login' THEN 1
+ WHEN links.name = 'can_write' THEN 2
+ WHEN links.name = 'can_manage' THEN 3
+ END as val
+ from links
+ where links.link_class='permission'
+ ),
+ traverse_graph(target_uuid, val, traverse_owned) as (
+ values (starting_uuid, starting_perm,
+ (starting_uuid like '_____-j7d0g-_______________' or
+ (starting_uuid like '_____-tpzed-_______________' and starting_perm >= 3)))
+ union
+ (select edges.head_uuid,
+ least(edges.val, traverse_graph.val),
+ (edges.head_uuid like '_____-j7d0g-_______________' or
+ (edges.head_uuid like '_____-tpzed-_______________' and edges.val >= 3))
+ from edges
+ join traverse_graph on (traverse_graph.target_uuid = edges.tail_uuid)
+ where traverse_graph.traverse_owned))
+ select target_uuid, max(val), bool_or(traverse_owned) from traverse_graph
+ group by (target_uuid) ;
+$$;
+}
+
+ ActiveRecord::Base.connection.execute %{
+create or replace function compute_permission_subgraph (perm_origin_uuid varchar(27),
+ starting_uuid varchar(27),
+ starting_perm integer)
+returns table (user_uuid varchar(27), target_uuid varchar(27), val integer, traverse_owned bool)
+STABLE
+language SQL
+as $$
+with
+perm_from_start(perm_origin_uuid, target_uuid, val, traverse_owned) as (
+ select perm_origin_uuid, target_uuid, val, traverse_owned
+ from search_permission_graph(starting_uuid, starting_perm)),
+
+ link_perms(perm_origin_uuid, target_uuid, val, traverse_owned) as (
+ select links.tail_uuid as perm_origin_uuid, ps.target_uuid, ps.val, true
+ from links, lateral search_permission_graph(links.head_uuid,
+ CASE
+ WHEN links.name = 'can_read' THEN 1
+ WHEN links.name = 'can_login' THEN 1
+ WHEN links.name = 'can_write' THEN 2
+ WHEN links.name = 'can_manage' THEN 3
+ END) as ps
+ where links.link_class='permission' and
+ (not (links.tail_uuid = perm_origin_uuid and links.head_uuid = starting_uuid)) and
+ links.tail_uuid not in (select target_uuid from perm_from_start where traverse_owned) and
+ links.head_uuid in (select target_uuid from perm_from_start)),
+
+ user_perms(perm_origin_uuid, target_uuid, val, traverse_owned) as (
+ select x.target_uuid as perm_origin_uuid, y.target_uuid, y.val, y.traverse_owned
+ from (select * from perm_from_start union select * from link_perms) as x,
+ lateral search_permission_graph(x.target_uuid, 3) as y
+ where x.target_uuid like '_____-tpzed-_______________'
+ ),
+
+ all_perms(perm_origin_uuid, target_uuid, val, traverse_owned) as (
+ select * from perm_from_start
+ union
+ select * from link_perms
+ union
+ select * from user_perms
+ )
+
+ select v.user_uuid, v.target_uuid, max(v.perm_level), bool_or(v.traverse_owned) from
+ (select materialized_permissions.user_uuid,
+ u.target_uuid,
+ least(u.val, materialized_permissions.perm_level) as perm_level,
+ u.traverse_owned
+ from all_perms as u
+ join materialized_permissions on (u.perm_origin_uuid = materialized_permissions.target_uuid)
+ where materialized_permissions.traverse_owned
+ union
+ select perm_origin_uuid as user_uuid, target_uuid, val as perm_level, traverse_owned
+ from all_perms
+ where perm_origin_uuid like '_____-tpzed-_______________') as v
+ group by v.user_uuid, v.target_uuid
+$$;
+ }
+