Repository navigation
Expand file tree
/
Copy pathusers.sql
More file actions
171 lines (159 loc) · 3.47 KB
/
Copy pathusers.sql
File metadata and controls
171 lines (159 loc) · 3.47 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
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
-- name: GetUserByID :one
SELECT
*
FROM
users
WHERE
id = $1
LIMIT
1;
-- name: GetUsersByIDs :many
SELECT * FROM users WHERE id = ANY(@ids :: uuid [ ]);
-- name: GetUserByEmailOrUsername :one
SELECT
*
FROM
users
WHERE
LOWER(username) = LOWER(@username)
OR email = @email
LIMIT
1;
-- name: GetUserCount :one
SELECT
COUNT(*)
FROM
users;
-- name: GetActiveUserCount :one
SELECT
COUNT(*)
FROM
users
WHERE
status = 'active'::public.user_status;
-- name: InsertUser :one
INSERT INTO
users (
id,
email,
username,
hashed_password,
created_at,
updated_at,
rbac_roles,
login_type
)
VALUES
($1, $2, $3, $4, $5, $6, $7, $8) RETURNING *;
-- name: UpdateUserProfile :one
UPDATE
users
SET
email = $2,
username = $3,
avatar_url = $4,
updated_at = $5
WHERE
id = $1 RETURNING *;
-- name: UpdateUserRoles :one
UPDATE
users
SET
-- Remove all duplicates from the roles.
rbac_roles = ARRAY(SELECT DISTINCT UNNEST(@granted_roles :: text[]))
WHERE
id = @id
RETURNING *;
-- name: UpdateUserHashedPassword :exec
UPDATE
users
SET
hashed_password = $2
WHERE
id = $1;
-- name: GetUsers :many
SELECT
*
FROM
users
WHERE
CASE
-- This allows using the last element on a page as effectively a cursor.
-- This is an important option for scripts that need to paginate without
-- duplicating or missing data.
WHEN @after_id :: uuid != '00000000-00000000-00000000-00000000' THEN (
-- The pagination cursor is the last ID of the previous page.
-- The query is ordered by the created_at field, so select all
-- rows after the cursor.
(created_at, id) > (
SELECT
created_at, id
FROM
users
WHERE
id = @after_id
)
)
ELSE true
END
-- Start filters
-- Filter by name, email or username
AND CASE
WHEN @search :: text != '' THEN (
email ILIKE concat('%', @search, '%')
OR username ILIKE concat('%', @search, '%')
)
ELSE true
END
-- Filter by status
AND CASE
-- @status needs to be a text because it can be empty, If it was
-- user_status enum, it would not.
WHEN cardinality(@status :: user_status[]) > 0 THEN
status = ANY(@status :: user_status[])
ELSE true
END
-- Filter by rbac_roles
AND CASE
-- @rbac_role allows filtering by rbac roles. If 'member' is included, show everyone, as
-- everyone is a member.
WHEN cardinality(@rbac_role :: text[]) > 0 AND 'member' != ANY(@rbac_role :: text[]) THEN
rbac_roles && @rbac_role :: text[]
ELSE true
END
-- End of filters
ORDER BY
-- Deterministic and consistent ordering of all users, even if they share
-- a timestamp. This is to ensure consistent pagination.
(created_at, id) ASC OFFSET @offset_opt
LIMIT
-- A null limit means "no limit", so 0 means return all
NULLIF(@limit_opt :: int, 0);
-- name: UpdateUserStatus :one
UPDATE
users
SET
status = $2,
updated_at = $3
WHERE
id = $1 RETURNING *;
-- name: GetAuthorizationUserRoles :one
-- This function returns roles for authorization purposes. Implied member roles
-- are included.
SELECT
-- username is returned just to help for logging purposes
-- status is used to enforce 'suspended' users, as all roles are ignored
-- when suspended.
id, username, status,
array_cat(
-- All users are members
array_append(users.rbac_roles, 'member'),
-- All org_members get the org-member role for their orgs
array_append(organization_members.roles, 'organization-member:'||organization_members.organization_id::text)) :: text[]
AS roles
FROM
users
LEFT JOIN organization_members
ON id = user_id
WHERE
id = @user_id;