-
Notifications
You must be signed in to change notification settings - Fork 1.3k
Expand file tree
/
Copy pathtailnet.sql
More file actions
129 lines (113 loc) · 2.99 KB
/
tailnet.sql
File metadata and controls
129 lines (113 loc) · 2.99 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
-- 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 :many
UPDATE
tailnet_peers
SET
status = $2
WHERE
coordinator_id = $1
RETURNING id;
-- 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 :many
DELETE
FROM tailnet_tunnels
WHERE coordinator_id = $1 and src_id = $2
RETURNING src_id, dst_id;
-- 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;
-- name: GetTailnetTunnelPeerIDsBatch :many
SELECT src_id AS lookup_id, dst_id AS peer_id, coordinator_id, updated_at
FROM tailnet_tunnels WHERE src_id = ANY(@ids :: uuid[])
UNION ALL
SELECT dst_id AS lookup_id, src_id AS peer_id, coordinator_id, updated_at
FROM tailnet_tunnels WHERE dst_id = ANY(@ids :: uuid[]);
-- name: GetTailnetTunnelPeerBindingsBatch :many
SELECT tp.id AS peer_id, tp.coordinator_id, tp.updated_at, tp.node, tp.status,
tunnels.lookup_id
FROM (
SELECT dst_id AS peer_id, src_id AS lookup_id
FROM tailnet_tunnels WHERE src_id = ANY(@ids :: uuid[])
UNION
SELECT src_id AS peer_id, dst_id AS lookup_id
FROM tailnet_tunnels WHERE dst_id = ANY(@ids :: uuid[])
) tunnels
INNER JOIN tailnet_peers tp ON tp.id = tunnels.peer_id;