teaching-api/prisma/view-migrations/views/view__all_document_user_permissions.sql at df841290bea455ececaf36d5733a39f3c325923f · GBSL-Informatik/teaching-api · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
-- view: view__all_document_user_permissions

-- assumption: all child documents of a document share the same document_root_id
SELECT
    document_root_id,
    user_id,
    access,
    document_id,
    root_user_permission_id,
    root_group_permission_id,
    group_id,
    ROW_NUMBER() OVER (PARTITION BY document_root_id, user_id, document_id ORDER BY access DESC) AS access_rank
FROM (
        -- get all documents where the user **is the author**
        SELECT
            document_roots.id AS document_root_id,
            documents.author_id AS user_id,
            document_roots.access AS access,
            documents.id AS document_id,
            NULL::uuid AS root_user_permission_id,
            NULL::uuid AS root_group_permission_id,
            NULL::uuid AS group_id
        FROM
            document_roots
            INNER JOIN documents ON document_roots.id = documents.document_root_id
    UNION ALL
        -- get all documents where the user **is not the author** but has shared access
        SELECT
            document_roots.id AS document_root_id,
            all_users.id AS user_id,
            CASE
                WHEN document_roots.shared_access <= document_roots.access THEN document_roots.shared_access
                ELSE document_roots.access
            END AS access,
            documents.id AS document_id,
            NULL::uuid AS root_user_permission_id,
            NULL::uuid AS root_group_permission_id,
            NULL::uuid AS group_id
        FROM
            document_roots
            INNER JOIN documents ON document_roots.id = documents.document_root_id
            CROSS JOIN users all_users
        WHERE documents.author_id != all_users.id
            AND (
                document_roots.shared_access='RO_DocumentRoot'
                OR
                document_roots.shared_access='RW_DocumentRoot'
            )
    UNION ALL
        -- get all documents where the user has been granted shared access
        -- or the access has been extended by user permissions
        SELECT
            document_roots.id AS document_root_id,
            rup.user_id AS user_id,
            rup.access AS access,
            documents.id AS document_id,
            rup.id AS root_user_permission_id,
            NULL::uuid AS root_group_permission_id,
            NULL::uuid AS group_id
        FROM
            document_roots
            LEFT JOIN documents ON document_roots.id=documents.document_root_id
            LEFT JOIN root_user_permissions rup
                ON (
                    document_roots.id = rup.document_root_id
                    AND (
                        documents.author_id = rup.user_id
                        OR
                        rup.access >= document_roots.shared_access
                    )
                )
        WHERE rup.user_id IS NOT NULL
    UNION ALL
        -- all group-based permissions for the documents author
        SELECT
            document_roots.id AS document_root_id,
            user_to_sg.user_id AS user_id,
            rgp.access AS access,
            documents.id AS document_id,
            NULL::uuid AS root_user_permission_id,
            rgp.id AS root_group_permission_id,
            sg.id AS group_id
        FROM
            document_roots
            INNER JOIN root_group_permissions rgp ON document_roots.id=rgp.document_root_id
            INNER JOIN student_groups sg ON rgp.student_group_id=sg.id
            LEFT JOIN documents ON document_roots.id=documents.document_root_id
            LEFT JOIN user_student_groups user_to_sg
                ON (
                    user_to_sg.student_group_id=sg.id
                    AND (
                        user_to_sg.user_id=documents.author_id
                        OR documents.author_id is null
                    )
                )
        WHERE user_to_sg.user_id IS NOT NULL
    UNION ALL
        -- all group based permissions for the user, which is not the author
        SELECT
            document_roots.id AS document_root_id,
            user_to_sg.user_id AS user_id,
            rgp.access AS access,
            documents.id AS document_id,
            NULL::uuid AS root_user_permission_id,
            rgp.id AS root_group_permission_id,
            sg.id AS group_id
        FROM
            document_roots
            INNER JOIN root_group_permissions rgp
                ON (
                    document_roots.id=rgp.document_root_id
                    AND rgp.access >= document_roots.shared_access
                )
            INNER JOIN student_groups sg ON rgp.student_group_id=sg.id
            LEFT JOIN documents ON document_roots.id=documents.document_root_id
            LEFT JOIN user_student_groups user_to_sg
                ON (
                    user_to_sg.student_group_id=sg.id
                    AND user_to_sg.user_id!=documents.author_id
                )
        WHERE user_to_sg.user_id IS NOT NULL
) as doc_user_permissions