1 class PermissionTable < ActiveRecord::Migration[5.0]
3 create_table :materialized_permissions, :id => false do |t|
7 t.string :target_owner_uuid
11 ActiveRecord::Base.connection.execute %{
12 create or replace function compute_permission_table ()
13 returns table(user_uuid character varying (27),
14 target_uuid character varying (27),
16 target_owner_uuid character varying(27),
21 WITH RECURSIVE perm_value(name, val) AS (
22 VALUES ('can_read'::text,(1)::smallint), ('can_login'::text,1), ('can_write'::text,2), ('can_manage'::text,3)
23 ), perm_edges(tail_uuid, head_uuid, val, follow, trashed) AS (
24 SELECT links.tail_uuid,
27 ((pv.val = 3) OR (groups.uuid IS NOT NULL)) AS follow,
28 (0)::smallint AS trashed,
29 (0)::smallint AS followtrash
31 LEFT JOIN perm_value pv ON ((pv.name = (links.name)::text)))
32 LEFT JOIN public.groups ON (((pv.val < 3) AND ((groups.uuid)::text = (links.head_uuid)::text))))
33 WHERE ((links.link_class)::text = 'permission'::text)
35 SELECT groups.owner_uuid,
40 WHEN ((groups.trash_at IS NOT NULL) AND (groups.trash_at < clock_timestamp())) THEN 1
45 ), perm(val, follow, user_uuid, target_uuid, trashed) AS (
46 SELECT (3)::smallint AS val,
48 (users.uuid)::character varying(32) AS user_uuid,
49 (users.uuid)::character varying(32) AS target_uuid,
50 (0)::smallint AS trashed
53 SELECT (LEAST((perm_1.val)::integer, edges.val))::smallint AS val,
56 (edges.head_uuid)::character varying(32) AS target_uuid,
57 ((GREATEST((perm_1.trashed)::integer, edges.trashed) * edges.followtrash))::smallint AS trashed
59 JOIN perm_edges edges ON ((perm_1.follow AND ((edges.tail_uuid)::text = (perm_1.target_uuid)::text))))
61 SELECT perm.user_uuid,
63 max(perm.val) AS perm_level,
65 WHEN true THEN perm.target_uuid
66 ELSE NULL::character varying
67 END AS target_owner_uuid,
68 max(perm.trashed) AS trashed
70 GROUP BY perm.user_uuid, perm.target_uuid,
72 WHEN true THEN perm.target_uuid
73 ELSE NULL::character varying
78 ActiveRecord::Base.connection.execute "DROP MATERIALIZED VIEW IF EXISTS materialized_permission_view;"
82 drop_table :materialized_permissions