forked from coder/coder
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathgroups.sql
More file actions
127 lines (119 loc) · 2.51 KB
/
groups.sql
File metadata and controls
127 lines (119 loc) · 2.51 KB
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
123
124
125
126
127
-- name: GetGroupByID :one
SELECT
*
FROM
groups
WHERE
id = $1
LIMIT
1;
-- name: GetGroupByOrgAndName :one
SELECT
*
FROM
groups
WHERE
organization_id = $1
AND
name = $2
LIMIT
1;
-- name: GetGroups :many
SELECT
sqlc.embed(groups),
organizations.name AS organization_name,
organizations.display_name AS organization_display_name
FROM
groups
INNER JOIN
organizations ON groups.organization_id = organizations.id
WHERE
true
AND CASE
WHEN @organization_id:: uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN
groups.organization_id = @organization_id
ELSE true
END
AND CASE
-- Filter to only include groups a user is a member of
WHEN @has_member_id::uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN
EXISTS (
SELECT
1
FROM
-- this view handles the 'everyone' group in orgs.
group_members_expanded
WHERE
group_members_expanded.group_id = groups.id
AND
group_members_expanded.user_id = @has_member_id
)
ELSE true
END
AND CASE WHEN array_length(@group_names :: text[], 1) > 0 THEN
groups.name = ANY(@group_names)
ELSE true
END
AND CASE WHEN array_length(@group_ids :: uuid[], 1) > 0 THEN
groups.id = ANY(@group_ids)
ELSE true
END
;
-- name: InsertGroup :one
INSERT INTO groups (
id,
name,
display_name,
organization_id,
avatar_url,
quota_allowance
)
VALUES
($1, $2, $3, $4, $5, $6) RETURNING *;
-- name: InsertMissingGroups :many
-- Inserts any group by name that does not exist. All new groups are given
-- a random uuid, are inserted into the same organization. They have the default
-- values for avatar, display name, and quota allowance (all zero values).
INSERT INTO groups (
id,
name,
organization_id,
source
)
SELECT
gen_random_uuid(),
group_name,
@organization_id,
@source
FROM
UNNEST(@group_names :: text[]) AS group_name
-- If the name conflicts, do nothing.
ON CONFLICT DO NOTHING
RETURNING *;
-- We use the organization_id as the id
-- for simplicity since all users is
-- every member of the org.
-- name: InsertAllUsersGroup :one
INSERT INTO groups (
id,
name,
organization_id
)
VALUES
(sqlc.arg(organization_id), 'Everyone', sqlc.arg(organization_id)) RETURNING *;
-- name: UpdateGroupByID :one
UPDATE
groups
SET
name = @name,
display_name = @display_name,
avatar_url = @avatar_url,
quota_allowance = @quota_allowance
WHERE
id = @id
RETURNING *;
-- name: DeleteGroupByID :exec
DELETE FROM
groups
WHERE
id = $1;