forked from coder/coder
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathtailnet.sql
More file actions
130 lines (114 loc) · 2.78 KB
/
tailnet.sql
File metadata and controls
130 lines (114 loc) · 2.78 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
-- name: UpsertTailnetCoordinator :one
INSERT INTO
tailnet_coordinators (
id,
heartbeat_at
)
VALUES
($1, now() at time zone 'utc')
ON CONFLICT (id)
DO UPDATE SET
id = $1,
heartbeat_at = now() at time zone 'utc'
RETURNING *;
-- name: CleanTailnetCoordinators :exec
DELETE
FROM tailnet_coordinators
WHERE heartbeat_at < now() - INTERVAL '24 HOURS';
-- name: CleanTailnetLostPeers :exec
DELETE
FROM tailnet_peers
WHERE updated_at < now() - INTERVAL '24 HOURS' AND status = 'lost'::tailnet_status;
-- name: CleanTailnetTunnels :exec
DELETE FROM tailnet_tunnels
WHERE updated_at < now() - INTERVAL '24 HOURS' AND
NOT EXISTS (
SELECT 1 FROM tailnet_peers
WHERE id = tailnet_tunnels.src_id AND coordinator_id = tailnet_tunnels.coordinator_id
);
-- name: UpsertTailnetPeer :one
INSERT INTO
tailnet_peers (
id,
coordinator_id,
node,
status,
updated_at
)
VALUES
($1, $2, $3, $4, now() at time zone 'utc')
ON CONFLICT (id, coordinator_id)
DO UPDATE SET
id = $1,
coordinator_id = $2,
node = $3,
status = $4,
updated_at = now() at time zone 'utc'
RETURNING *;
-- name: UpdateTailnetPeerStatusByCoordinator :exec
UPDATE
tailnet_peers
SET
status = $2
WHERE
coordinator_id = $1;
-- name: DeleteTailnetPeer :one
DELETE
FROM tailnet_peers
WHERE id = $1 and coordinator_id = $2
RETURNING id, coordinator_id;
-- name: GetTailnetPeers :many
SELECT * FROM tailnet_peers WHERE id = $1;
-- name: UpsertTailnetTunnel :one
INSERT INTO
tailnet_tunnels (
coordinator_id,
src_id,
dst_id,
updated_at
)
VALUES
($1, $2, $3, now() at time zone 'utc')
ON CONFLICT (coordinator_id, src_id, dst_id)
DO UPDATE SET
coordinator_id = $1,
src_id = $2,
dst_id = $3,
updated_at = now() at time zone 'utc'
RETURNING *;
-- name: DeleteTailnetTunnel :one
DELETE
FROM tailnet_tunnels
WHERE coordinator_id = $1 and src_id = $2 and dst_id = $3
RETURNING coordinator_id, src_id, dst_id;
-- name: DeleteAllTailnetTunnels :exec
DELETE
FROM tailnet_tunnels
WHERE coordinator_id = $1 and src_id = $2;
-- name: GetTailnetTunnelPeerIDs :many
SELECT dst_id as peer_id, coordinator_id, updated_at
FROM tailnet_tunnels
WHERE tailnet_tunnels.src_id = $1
UNION
SELECT src_id as peer_id, coordinator_id, updated_at
FROM tailnet_tunnels
WHERE tailnet_tunnels.dst_id = $1;
-- name: GetTailnetTunnelPeerBindings :many
SELECT id AS peer_id, coordinator_id, updated_at, node, status
FROM tailnet_peers
WHERE id IN (
SELECT dst_id as peer_id
FROM tailnet_tunnels
WHERE tailnet_tunnels.src_id = $1
UNION
SELECT src_id as peer_id
FROM tailnet_tunnels
WHERE tailnet_tunnels.dst_id = $1
);
-- For PG Coordinator HTMLDebug
-- name: GetAllTailnetCoordinators :many
SELECT * FROM tailnet_coordinators;
-- name: GetAllTailnetPeers :many
SELECT * FROM tailnet_peers;
-- name: GetAllTailnetTunnels :many
SELECT * FROM tailnet_tunnels;