Repository navigation
Expand file tree
/
Copy pathworkspaces.sql
More file actions
133 lines (124 loc) · 2.27 KB
/
Copy pathworkspaces.sql
File metadata and controls
133 lines (124 loc) · 2.27 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
-- name: GetWorkspaceByID :one
SELECT
*
FROM
workspaces
WHERE
id = $1
LIMIT
1;
-- name: GetWorkspaces :many
SELECT
*
FROM
workspaces
WHERE
-- Optionally include deleted workspaces
workspaces.deleted = @deleted
-- Filter by owner_id
AND CASE
WHEN @owner_id :: uuid != '00000000-00000000-00000000-00000000' THEN
owner_id = @owner_id
ELSE true
END
-- Filter by owner_name
AND CASE
WHEN @owner_username :: text != '' THEN
owner_id = (SELECT id FROM users WHERE lower(username) = lower(@owner_username))
ELSE true
END
-- Filter by template_name
-- There can be more than 1 template with the same name across organizations.
-- Use the organization filter to restrict to 1 org if needed.
AND CASE
WHEN @template_name :: text != '' THEN
template_id = ANY(SELECT id FROM templates WHERE lower(name) = lower(@template_name))
ELSE true
END
-- Filter by template_ids
AND CASE
WHEN array_length(@template_ids :: uuid[], 1) > 0 THEN
template_id = ANY(@template_ids)
ELSE true
END
-- Filter by name, matching on substring
AND CASE
WHEN @name :: text != '' THEN
name ILIKE '%' || @name || '%'
ELSE true
END
;
-- name: GetWorkspaceByOwnerIDAndName :one
SELECT
*
FROM
workspaces
WHERE
owner_id = @owner_id
AND deleted = @deleted
AND LOWER("name") = LOWER(@name)
ORDER BY created_at DESC;
-- name: GetWorkspaceOwnerCountsByTemplateIDs :many
SELECT
template_id,
COUNT(DISTINCT owner_id)
FROM
workspaces
WHERE
template_id = ANY(@ids :: uuid [ ])
-- Ignore deleted workspaces
AND deleted != true
GROUP BY
template_id;
-- name: InsertWorkspace :one
INSERT INTO
workspaces (
id,
created_at,
updated_at,
owner_id,
organization_id,
template_id,
name,
autostart_schedule,
ttl
)
VALUES
($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING *;
-- name: UpdateWorkspaceDeletedByID :exec
UPDATE
workspaces
SET
deleted = $2
WHERE
id = $1;
-- name: UpdateWorkspace :one
UPDATE
workspaces
SET
name = $2
WHERE
id = $1
AND deleted = false
RETURNING *;
-- name: UpdateWorkspaceAutostart :exec
UPDATE
workspaces
SET
autostart_schedule = $2
WHERE
id = $1;
-- name: UpdateWorkspaceTTL :exec
UPDATE
workspaces
SET
ttl = $2
WHERE
id = $1;
-- name: UpdateWorkspaceLastUsedAt :exec
UPDATE
workspaces
SET
last_used_at = $2
WHERE
id = $1;