Repository navigation
Expand file tree
/
Copy pathchatprojects.sql
More file actions
110 lines (101 loc) · 3.28 KB
/
Copy pathchatprojects.sql
File metadata and controls
110 lines (101 loc) · 3.28 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
-- name: InsertChatProject :one
INSERT INTO chat_projects (id, organization_id, owner_id, name, description, icon)
VALUES (
COALESCE(sqlc.narg('id')::uuid, gen_random_uuid()),
@organization_id::uuid,
@owner_id::uuid,
@name::text,
@description::text,
@icon::text
)
RETURNING *;
-- name: GetChatProjectByID :one
SELECT *
FROM chat_projects
WHERE id = @id::uuid;
-- name: GetChatProjectByIDForUpdate :one
SELECT *
FROM chat_projects
WHERE id = @id::uuid
FOR UPDATE;
-- name: GetChatProjectsByOwnerID :many
SELECT *
FROM chat_projects
WHERE owner_id = @owner_id::uuid
ORDER BY lower(name), id;
-- name: GetChatProjectsOwnedOrSharedWithUserID :many
-- Entries match regardless of the actions they grant, so callers must
-- authorize each row.
SELECT chat_projects.*
FROM chat_projects
WHERE chat_projects.owner_id = @user_id::uuid
OR (
(
chat_projects.user_acl ? (@user_id::uuid)::text
OR chat_projects.group_acl ?| ARRAY(
SELECT group_members.group_id::text
FROM group_members
WHERE group_members.user_id = @user_id::uuid
UNION ALL
SELECT organization_members.organization_id::text
FROM organization_members
WHERE organization_members.user_id = @user_id::uuid
)
)
AND EXISTS (
SELECT 1
FROM organization_members
WHERE organization_members.user_id = @user_id::uuid
AND organization_members.organization_id = chat_projects.organization_id
)
)
ORDER BY lower(chat_projects.name), chat_projects.id;
-- name: IsChatProjectAccessibleByUserID :one
SELECT EXISTS (
SELECT 1
FROM chat_projects
WHERE chat_projects.id = @project_id::uuid
AND (
chat_projects.owner_id = @user_id::uuid
OR (
EXISTS (
SELECT 1
FROM organization_members
WHERE organization_members.user_id = @user_id::uuid
AND organization_members.organization_id = chat_projects.organization_id
)
AND (
chat_projects.user_acl -> (@user_id::uuid)::text -> 'permissions' ?| ARRAY['read', '*']
OR chat_projects.group_acl -> chat_projects.organization_id::text -> 'permissions' ?| ARRAY['read', '*']
OR EXISTS (
SELECT 1
FROM group_members
WHERE group_members.user_id = @user_id::uuid
AND chat_projects.group_acl -> group_members.group_id::text -> 'permissions' ?| ARRAY['read', '*']
)
)
)
)
)::boolean;
-- name: UpdateChatProjectACLByID :exec
UPDATE chat_projects
SET
user_acl = @user_acl,
group_acl = @group_acl
WHERE id = @id::uuid;
-- name: UpdateChatProjectByID :one
UPDATE chat_projects
SET
name = @name::text,
description = @description::text,
icon = @icon::text,
updated_at = now()
WHERE id = @id::uuid
RETURNING *;
-- name: DeleteChatProjectByID :exec
DELETE FROM chat_projects
WHERE id = @id::uuid;
-- name: CountChatProjectsByOwnerID :one
SELECT COUNT(*)::bigint
FROM chat_projects
WHERE owner_id = @owner_id::uuid;