Repository navigation
Expand file tree
/
Copy pathprovisionerdaemons.sql
More file actions
67 lines (63 loc) · 1.49 KB
/
Copy pathprovisionerdaemons.sql
File metadata and controls
67 lines (63 loc) · 1.49 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
-- name: GetProvisionerDaemons :many
SELECT
*
FROM
provisioner_daemons;
-- name: GetProvisionerDaemonsByOrganization :many
SELECT
*
FROM
provisioner_daemons
WHERE
organization_id = @organization_id;
-- name: DeleteOldProvisionerDaemons :exec
-- Delete provisioner daemons that have been created at least a week ago
-- and have not connected to coderd since a week.
-- A provisioner daemon with "zeroed" last_seen_at column indicates possible
-- connectivity issues (no provisioner daemon activity since registration).
DELETE FROM provisioner_daemons WHERE (
(created_at < (NOW() - INTERVAL '7 days') AND last_seen_at IS NULL) OR
(last_seen_at IS NOT NULL AND last_seen_at < (NOW() - INTERVAL '7 days'))
);
-- name: UpsertProvisionerDaemon :one
INSERT INTO
provisioner_daemons (
id,
created_at,
"name",
provisioners,
tags,
last_seen_at,
"version",
organization_id,
api_version,
key_id
)
VALUES (
gen_random_uuid(),
@created_at,
@name,
@provisioners,
@tags,
@last_seen_at,
@version,
@organization_id,
@api_version,
@key_id
) ON CONFLICT("organization_id", "name", LOWER(COALESCE(tags ->> 'owner'::text, ''::text))) DO UPDATE SET
provisioners = @provisioners,
tags = @tags,
last_seen_at = @last_seen_at,
"version" = @version,
api_version = @api_version,
organization_id = @organization_id,
key_id = @key_id
RETURNING *;
-- name: UpdateProvisionerDaemonLastSeenAt :exec
UPDATE provisioner_daemons
SET
last_seen_at = @last_seen_at
WHERE
id = @id
AND
last_seen_at <= @last_seen_at;