forked from coder/coder
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathworkspaces.sql
More file actions
318 lines (299 loc) · 7.08 KB
/
workspaces.sql
File metadata and controls
318 lines (299 loc) · 7.08 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
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
-- name: GetWorkspaceByID :one
SELECT
*
FROM
workspaces
WHERE
id = $1
LIMIT
1;
-- name: GetWorkspaceByWorkspaceAppID :one
SELECT
*
FROM
workspaces
WHERE
workspaces.id = (
SELECT
workspace_id
FROM
workspace_builds
WHERE
workspace_builds.job_id = (
SELECT
job_id
FROM
workspace_resources
WHERE
workspace_resources.id = (
SELECT
resource_id
FROM
workspace_agents
WHERE
workspace_agents.id = (
SELECT
agent_id
FROM
workspace_apps
WHERE
workspace_apps.id = @workspace_app_id
)
)
)
);
-- name: GetWorkspaceByAgentID :one
SELECT
*
FROM
workspaces
WHERE
workspaces.id = (
SELECT
workspace_id
FROM
workspace_builds
WHERE
workspace_builds.job_id = (
SELECT
job_id
FROM
workspace_resources
WHERE
workspace_resources.id = (
SELECT
resource_id
FROM
workspace_agents
WHERE
workspace_agents.id = @agent_id
)
)
);
-- name: GetWorkspaces :many
SELECT
workspaces.*, COUNT(*) OVER () as count
FROM
workspaces
LEFT JOIN LATERAL (
SELECT
workspace_builds.transition,
provisioner_jobs.id AS provisioner_job_id,
provisioner_jobs.started_at,
provisioner_jobs.updated_at,
provisioner_jobs.canceled_at,
provisioner_jobs.completed_at,
provisioner_jobs.error
FROM
workspace_builds
LEFT JOIN
provisioner_jobs
ON
provisioner_jobs.id = workspace_builds.job_id
WHERE
workspace_builds.workspace_id = workspaces.id
ORDER BY
build_number DESC
LIMIT
1
) latest_build ON TRUE
WHERE
-- Optionally include deleted workspaces
workspaces.deleted = @deleted
AND CASE
WHEN @status :: text != '' THEN
CASE
WHEN @status = 'pending' THEN
latest_build.started_at IS NULL
WHEN @status = 'starting' THEN
latest_build.started_at IS NOT NULL AND
latest_build.canceled_at IS NULL AND
latest_build.completed_at IS NULL AND
latest_build.updated_at - INTERVAL '30 seconds' < NOW() AND
latest_build.transition = 'start'::workspace_transition
WHEN @status = 'running' THEN
latest_build.completed_at IS NOT NULL AND
latest_build.canceled_at IS NULL AND
latest_build.error IS NULL AND
latest_build.transition = 'start'::workspace_transition
WHEN @status = 'stopping' THEN
latest_build.started_at IS NOT NULL AND
latest_build.canceled_at IS NULL AND
latest_build.completed_at IS NULL AND
latest_build.updated_at - INTERVAL '30 seconds' < NOW() AND
latest_build.transition = 'stop'::workspace_transition
WHEN @status = 'stopped' THEN
latest_build.completed_at IS NOT NULL AND
latest_build.canceled_at IS NULL AND
latest_build.error IS NULL AND
latest_build.transition = 'stop'::workspace_transition
WHEN @status = 'failed' THEN
(latest_build.canceled_at IS NOT NULL AND
latest_build.error IS NOT NULL) OR
(latest_build.completed_at IS NOT NULL AND
latest_build.error IS NOT NULL)
WHEN @status = 'canceling' THEN
latest_build.canceled_at IS NOT NULL AND
latest_build.completed_at IS NULL
WHEN @status = 'canceled' THEN
latest_build.canceled_at IS NOT NULL AND
latest_build.completed_at IS NOT NULL
WHEN @status = 'deleted' THEN
latest_build.started_at IS NOT NULL AND
latest_build.canceled_at IS NULL AND
latest_build.completed_at IS NOT NULL AND
latest_build.updated_at - INTERVAL '30 seconds' < NOW() AND
latest_build.transition = 'delete'::workspace_transition
WHEN @status = 'deleting' THEN
latest_build.completed_at IS NOT NULL AND
latest_build.canceled_at IS NULL AND
latest_build.error IS NULL AND
latest_build.transition = 'delete'::workspace_transition
ELSE
true
END
ELSE true
END
-- Filter by owner_id
AND CASE
WHEN @owner_id :: uuid != '00000000-0000-0000-0000-000000000000'::uuid 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) AND deleted = false)
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) AND deleted = false)
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
-- Filter by agent status
-- has-agent: is only applicable for workspaces in "start" transition. Stopped and deleted workspaces don't have agents.
AND CASE
WHEN @has_agent :: text != '' THEN
(
SELECT COUNT(*)
FROM
workspace_resources
JOIN
workspace_agents
ON
workspace_agents.resource_id = workspace_resources.id
WHERE
workspace_resources.job_id = latest_build.provisioner_job_id AND
latest_build.transition = 'start'::workspace_transition AND
@has_agent = (
CASE
WHEN workspace_agents.first_connected_at IS NULL THEN
CASE
WHEN workspace_agents.connection_timeout_seconds > 0 AND NOW() - workspace_agents.created_at > workspace_agents.connection_timeout_seconds * INTERVAL '1 second' THEN
'timeout'
ELSE
'connecting'
END
WHEN workspace_agents.disconnected_at > workspace_agents.last_connected_at THEN
'disconnected'
WHEN NOW() - workspace_agents.last_connected_at > INTERVAL '1 second' * @agent_inactive_disconnect_timeout_seconds :: bigint THEN
'disconnected'
WHEN workspace_agents.last_connected_at IS NOT NULL THEN
'connected'
ELSE
NULL
END
)
) > 0
ELSE true
END
-- Authorize Filter clause will be injected below in GetAuthorizedWorkspaces
-- @authorize_filter
ORDER BY
last_used_at DESC
LIMIT
CASE
WHEN @limit_ :: integer > 0 THEN
@limit_
END
OFFSET
@offset_
;
-- 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: 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;