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
|
-- name: GetRootGroups :many
SELECT name, COALESCE(description, '') FROM groups WHERE parent_group IS NULL;
-- name: GetGroupByPath :one
WITH RECURSIVE group_path_cte AS (
SELECT
id,
parent_group,
name,
1 AS depth
FROM groups
WHERE name = ($1::text[])[1]
AND parent_group IS NULL
UNION ALL
SELECT
g.id,
g.parent_group,
g.name,
group_path_cte.depth + 1
FROM groups g
JOIN group_path_cte ON g.parent_group = group_path_cte.id
WHERE g.name = ($1::text[])[group_path_cte.depth + 1]
AND group_path_cte.depth + 1 <= cardinality($1::text[])
)
SELECT
g.id,
g.name,
g.parent_group,
COALESCE(g.description, '') AS description,
EXISTS (
SELECT 1
FROM user_group_roles ugr
WHERE ugr.user_id = $2
AND ugr.group_id = g.id
) AS has_role
FROM group_path_cte c
JOIN groups g ON g.id = c.id
WHERE c.depth = cardinality($1::text[]);
-- name: GetReposInGroup :many
SELECT name, COALESCE(description, '') FROM repos WHERE group_id = $1;
-- name: GetSubgroups :many
SELECT name, COALESCE(description, '') FROM groups WHERE parent_group = $1;
|
