1 class PermissionTable < ActiveRecord::Migration[5.0]
3 create_table :materialized_permissions, :id => false do |t|
7 t.string :target_owner_uuid
10 ActiveRecord::Base.connection.execute %{
11 create or replace function compute_permission_table ()
12 returns table(user_uuid character varying (27),
13 target_uuid character varying (27),
15 target_owner_uuid character varying(27))
19 WITH RECURSIVE perm_value(name, val) AS (
20 VALUES ('can_read'::text,(1)::smallint), ('can_login'::text,1), ('can_write'::text,2), ('can_manage'::text,3)
21 ), perm_edges(tail_uuid, head_uuid, val, follow) AS (
22 SELECT links.tail_uuid,
25 ((pv.val = 3) OR (groups.uuid IS NOT NULL)) AS follow
27 LEFT JOIN perm_value pv ON ((pv.name = (links.name)::text)))
28 LEFT JOIN public.groups ON (((pv.val < 3) AND ((groups.uuid)::text = (links.head_uuid)::text))))
29 WHERE ((links.link_class)::text = 'permission'::text)
31 SELECT groups.owner_uuid,
36 ), perm(val, follow, user_uuid, target_uuid) AS (
37 SELECT (3)::smallint AS val,
39 (users.uuid)::character varying(32) AS user_uuid,
40 (users.uuid)::character varying(32) AS target_uuid
43 SELECT (LEAST((perm_1.val)::integer, edges.val))::smallint AS val,
46 (edges.head_uuid)::character varying(32) AS target_uuid
48 JOIN perm_edges edges ON ((perm_1.follow AND ((edges.tail_uuid)::text = (perm_1.target_uuid)::text))))
50 SELECT perm.user_uuid,
52 max(perm.val) AS perm_level,
54 WHEN true THEN perm.target_uuid
55 ELSE NULL::character varying
56 END AS target_owner_uuid
58 GROUP BY perm.user_uuid, perm.target_uuid,
60 WHEN true THEN perm.target_uuid
61 ELSE NULL::character varying
66 ActiveRecord::Base.connection.execute %{
67 create or replace function project_subtree (starting_uuid varchar(27))
68 returns table (target_uuid varchar(27))
73 project_subtree(uuid) as (
74 values (starting_uuid)
76 select groups.uuid from groups join project_subtree on (groups.owner_uuid = project_subtree.uuid)
78 select uuid from project_subtree;
82 ActiveRecord::Base.connection.execute %{
83 create or replace function project_subtree_with_trash_at (starting_uuid varchar(27), starting_trash_at timestamp)
84 returns table (target_uuid varchar(27), trash_at timestamp)
89 project_subtree(uuid, trash_at) as (
90 values (starting_uuid, starting_trash_at)
92 select groups.uuid, LEAST(project_subtree.trash_at, groups.trash_at)
93 from groups join project_subtree on (groups.owner_uuid = project_subtree.uuid)
95 select uuid, trash_at from project_subtree;
99 create_table :trashed_groups, :id => false do |t|
103 add_index :trashed_groups, :group_uuid, :unique => true
105 ActiveRecord::Base.connection.execute %{
106 create or replace function compute_trashed ()
107 returns table (uuid varchar(27), trash_at timestamp)
111 select ps.target_uuid as group_uuid, ps.trash_at from groups,
112 lateral project_subtree_with_trash_at(groups.uuid, groups.trash_at) ps
113 where groups.owner_uuid like '_____-tpzed-_______________'
117 ActiveRecord::Base.connection.execute "DROP MATERIALIZED VIEW IF EXISTS materialized_permission_view;"
121 drop_table :materialized_permissions
122 drop_table :trashed_groups
124 ActiveRecord::Base.connection.execute "DROP function compute_permission_table ();"
125 ActiveRecord::Base.connection.execute "DROP function project_subtree (varchar(27));"
126 ActiveRecord::Base.connection.execute "DROP function project_subtree_with_trash_at (varchar(27), timestamp);"
127 ActiveRecord::Base.connection.execute "DROP function compute_trashed ();"