c/bmaas/bmdb/model: split query files
This was starting to become slightly unwieldy.
Change-Id: I8f9d63ff6f844f1452fca2a98d253a9582a3ee8d
Reviewed-on: https://review.monogon.dev/c/monogon/+/1085
Tested-by: Jenkins CI
Reviewed-by: Leopold Schabel <leo@monogon.tech>
diff --git a/cloud/bmaas/bmdb/model/BUILD.bazel b/cloud/bmaas/bmdb/model/BUILD.bazel
index 7a0f122..aea0d7b 100644
--- a/cloud/bmaas/bmdb/model/BUILD.bazel
+++ b/cloud/bmaas/bmdb/model/BUILD.bazel
@@ -7,7 +7,9 @@
importpath = "source.monogon.dev/cloud/bmaas/bmdb/model",
migrations = glob(["migrations/*sql"]),
queries = [
- "queries.sql",
+ "queries_base.sql",
+ "queries_tags.sql",
+ "queries_workflows.sql",
],
)
diff --git a/cloud/bmaas/bmdb/model/queries.sql b/cloud/bmaas/bmdb/model/queries.sql
deleted file mode 100644
index 8f40aac..0000000
--- a/cloud/bmaas/bmdb/model/queries.sql
+++ /dev/null
@@ -1,172 +0,0 @@
--- name: NewMachine :one
-INSERT INTO machines (
- machine_created_at
-) VALUES (
- now()
-)
-RETURNING *;
-
--- name: NewSession :one
-INSERT INTO sessions (
- session_component_name, session_runtime_info, session_created_at, session_interval_seconds, session_deadline
-) VALUES (
- $1, $2, now(), $3, (now() + $3 * interval '1 second')
-)
-RETURNING *;
-
--- name: SessionPoke :exec
--- Update a given session with a new deadline. Must be called in the same
--- transaction as SessionCheck to ensure the session is still alive.
-UPDATE sessions
-SET session_deadline = now() + session_interval_seconds * interval '1 second'
-WHERE session_id = $1;
-
--- name: SessionCheck :many
--- SessionCheck returns a session by ID if that session is still valid (ie. its
--- deadline hasn't expired).
-SELECT *
-FROM sessions
-WHERE session_id = $1
-AND session_deadline > now();
-
--- name: StartWork :exec
-INSERT INTO work (
- machine_id, session_id, process
-) VALUES (
- $1, $2, $3
-);
-
--- name: FinishWork :exec
-DELETE FROM work
-WHERE machine_id = $1
- AND session_id = $2
- AND process = $3;
-
--- name: MachineAddProvided :exec
-INSERT INTO machine_provided (
- machine_id, provider, provider_id
-) VALUES (
- $1, $2, $3
-);
-
--- name: MachineSetAgentStarted :exec
-INSERT INTO machine_agent_started (
- machine_id, agent_started_at, agent_public_key
-) VALUES (
- $1, $2, $3
-) ON CONFLICT (machine_id) DO UPDATE SET
- agent_started_at = $2,
- agent_public_key = $3
-;
-
--- name: MachineSetAgentHeartbeat :exec
-INSERT INTO machine_agent_heartbeat (
- machine_id, agent_heartbeat_at
-) VALUES (
- $1, $2
-) ON CONFLICT (machine_id) DO UPDATE SET
- agent_heartbeat_at = $2
-;
-
--- name: MachineSetHardwareReport :exec
-INSERT INTO machine_hardware_report (
- machine_id, hardware_report_raw
-) VALUES (
- $1, $2
-) ON CONFLICT (machine_id) DO UPDATE SET
- hardware_report_raw = $2
-;
-
--- name: MachineSetOSInstallationRequest :exec
-INSERT INTO machine_os_installation_request (
- machine_id, generation, os_installation_request_raw
-) VALUES (
- $1, $2, $3
-) ON CONFLICT (machine_id) DO UPDATE SET
- generation = $2,
- os_installation_request_raw = $3
-;
-
--- name: MachineSetOSInstallationReport :exec
-INSERT INTO machine_os_installation_report (
- machine_id, generation
-) VALUES (
- $1, $2
-) ON CONFLICT (machine_id) DO UPDATE SET
- generation = $2
-;
-
--- name: GetMachinesForAgentStart :many
--- Get machines that need agent installed for the first time. Machine can be
--- assumed to be 'new', with no previous attempts or failures.
-SELECT
- machine_provided.*
-FROM machines
-INNER JOIN machine_provided ON machines.machine_id = machine_provided.machine_id
-LEFT JOIN work ON machines.machine_id = work.machine_id AND work.process = 'ShepherdInstall'
-LEFT JOIN machine_agent_started ON machines.machine_id = machine_agent_started.machine_id
-WHERE
- machine_agent_started.machine_id IS NULL
- -- TODO(q3k): exclude machines which are not expected to run the agent (eg.
- -- are already exposed to a user).
- AND work.machine_id IS NULL
-LIMIT $1;
-
--- name: GetMachineForAgentRecovery :many
--- Get machines that need agent installed after something went wrong. Either
--- the agent started but never responded, or the agent stopped responding at
--- some point, or the machine is being reinstalled after failure. Assume some
--- work needs to be performed on the shepherd side to diagnose and recover
--- whatever state the machine truly is in.
-SELECT
- machine_provided.*
-FROM machines
-INNER JOIN machine_provided ON machines.machine_id = machine_provided.machine_id
-LEFT JOIN work ON machines.machine_id = work.machine_id AND work.process = 'ShepherdInstall'
-LEFT JOIN machine_agent_started ON machines.machine_id = machine_agent_started.machine_id
-LEFT JOIN machine_agent_heartbeat ON machines.machine_id = machine_agent_heartbeat.machine_id
-WHERE
- -- Only act on machines where the agent is expected to have been started.
- machine_agent_started.machine_id IS NOT NULL
- AND (
- -- No heartbeat 30 minutes after starting the agent.
- (
- machine_agent_heartbeat.machine_id IS NULL
- AND now() > (machine_agent_started.agent_started_at + interval '30 minutes')
- )
- -- Heartbeats ceased for 10 minutes.
- OR (
- machine_agent_heartbeat.machine_id IS NOT NULL
- AND now() > (machine_agent_heartbeat.agent_heartbeat_at + interval '10 minutes')
- )
- )
- AND work.machine_id IS NULL
-LIMIT $1;
-
--- name: GetExactMachineForOSInstallation :many
-SELECT
- machine_os_installation_request.*
-FROM machines
-LEFT JOIN machine_os_installation_request ON machines.machine_id = machine_os_installation_request.machine_id
-LEFT JOIN machine_os_installation_report ON machines.machine_id = machine_os_installation_report.machine_id
-WHERE
- -- We are only interested in one concrete machine.
- machines.machine_id = $1
- -- We must have an installation request.
- AND machine_os_installation_request.machine_id IS NOT NULL
- -- And we either must have no installation report, or the installation
- -- report's generation must not match the installation request's generation.
- AND (
- machine_os_installation_report.machine_id IS NULL
- OR machine_os_installation_report.generation != machine_os_installation_request.generation
- )
-LIMIT $2;
-
--- name: AuthenticateAgentConnection :many
-SELECT
- machine_agent_started.*
-FROM machines
-INNER JOIN machine_agent_started ON machines.machine_id = machine_agent_started.machine_id
-WHERE
- machines.machine_id = $1
- AND machine_agent_started.agent_public_key = $2;
diff --git a/cloud/bmaas/bmdb/model/queries_base.sql b/cloud/bmaas/bmdb/model/queries_base.sql
new file mode 100644
index 0000000..60d1737
--- /dev/null
+++ b/cloud/bmaas/bmdb/model/queries_base.sql
@@ -0,0 +1,44 @@
+-- name: NewMachine :one
+INSERT INTO machines (
+ machine_created_at
+) VALUES (
+ now()
+)
+RETURNING *;
+
+-- name: NewSession :one
+INSERT INTO sessions (
+ session_component_name, session_runtime_info, session_created_at, session_interval_seconds, session_deadline
+) VALUES (
+ $1, $2, now(), $3, (now() + $3 * interval '1 second')
+)
+RETURNING *;
+
+-- name: SessionPoke :exec
+-- Update a given session with a new deadline. Must be called in the same
+-- transaction as SessionCheck to ensure the session is still alive.
+UPDATE sessions
+SET session_deadline = now() + session_interval_seconds * interval '1 second'
+WHERE session_id = $1;
+
+-- name: SessionCheck :many
+-- SessionCheck returns a session by ID if that session is still valid (ie. its
+-- deadline hasn't expired).
+SELECT *
+FROM sessions
+WHERE session_id = $1
+AND session_deadline > now();
+
+-- name: StartWork :exec
+INSERT INTO work (
+ machine_id, session_id, process
+) VALUES (
+ $1, $2, $3
+);
+
+-- name: FinishWork :exec
+DELETE FROM work
+WHERE machine_id = $1
+ AND session_id = $2
+ AND process = $3;
+
diff --git a/cloud/bmaas/bmdb/model/queries_tags.sql b/cloud/bmaas/bmdb/model/queries_tags.sql
new file mode 100644
index 0000000..ebf4e4d
--- /dev/null
+++ b/cloud/bmaas/bmdb/model/queries_tags.sql
@@ -0,0 +1,54 @@
+-- name: MachineAddProvided :exec
+INSERT INTO machine_provided (
+ machine_id, provider, provider_id
+) VALUES (
+ $1, $2, $3
+);
+
+-- name: MachineSetAgentStarted :exec
+INSERT INTO machine_agent_started (
+ machine_id, agent_started_at, agent_public_key
+) VALUES (
+ $1, $2, $3
+) ON CONFLICT (machine_id) DO UPDATE SET
+ agent_started_at = $2,
+ agent_public_key = $3
+;
+
+-- name: MachineSetAgentHeartbeat :exec
+INSERT INTO machine_agent_heartbeat (
+ machine_id, agent_heartbeat_at
+) VALUES (
+ $1, $2
+) ON CONFLICT (machine_id) DO UPDATE SET
+ agent_heartbeat_at = $2
+;
+
+-- name: MachineSetHardwareReport :exec
+INSERT INTO machine_hardware_report (
+ machine_id, hardware_report_raw
+) VALUES (
+ $1, $2
+) ON CONFLICT (machine_id) DO UPDATE SET
+ hardware_report_raw = $2
+;
+
+-- name: MachineSetOSInstallationRequest :exec
+INSERT INTO machine_os_installation_request (
+ machine_id, generation, os_installation_request_raw
+) VALUES (
+ $1, $2, $3
+) ON CONFLICT (machine_id) DO UPDATE SET
+ generation = $2,
+ os_installation_request_raw = $3
+;
+
+-- name: MachineSetOSInstallationReport :exec
+INSERT INTO machine_os_installation_report (
+ machine_id, generation
+) VALUES (
+ $1, $2
+) ON CONFLICT (machine_id) DO UPDATE SET
+ generation = $2
+;
+
diff --git a/cloud/bmaas/bmdb/model/queries_workflows.sql b/cloud/bmaas/bmdb/model/queries_workflows.sql
new file mode 100644
index 0000000..d2e9966
--- /dev/null
+++ b/cloud/bmaas/bmdb/model/queries_workflows.sql
@@ -0,0 +1,79 @@
+-- name: GetMachinesForAgentStart :many
+-- Get machines that need agent installed for the first time. Machine can be
+-- assumed to be 'new', with no previous attempts or failures.
+SELECT
+ machine_provided.*
+FROM machines
+INNER JOIN machine_provided ON machines.machine_id = machine_provided.machine_id
+LEFT JOIN work ON machines.machine_id = work.machine_id AND work.process = 'ShepherdInstall'
+LEFT JOIN machine_agent_started ON machines.machine_id = machine_agent_started.machine_id
+WHERE
+ machine_agent_started.machine_id IS NULL
+ -- TODO(q3k): exclude machines which are not expected to run the agent (eg.
+ -- are already exposed to a user).
+ AND work.machine_id IS NULL
+LIMIT $1;
+
+-- name: GetMachineForAgentRecovery :many
+-- Get machines that need agent installed after something went wrong. Either
+-- the agent started but never responded, or the agent stopped responding at
+-- some point, or the machine is being reinstalled after failure. Assume some
+-- work needs to be performed on the shepherd side to diagnose and recover
+-- whatever state the machine truly is in.
+SELECT
+ machine_provided.*
+FROM machines
+INNER JOIN machine_provided ON machines.machine_id = machine_provided.machine_id
+LEFT JOIN work ON machines.machine_id = work.machine_id AND work.process = 'ShepherdInstall'
+LEFT JOIN machine_agent_started ON machines.machine_id = machine_agent_started.machine_id
+LEFT JOIN machine_agent_heartbeat ON machines.machine_id = machine_agent_heartbeat.machine_id
+WHERE
+ -- Only act on machines where the agent is expected to have been started.
+ machine_agent_started.machine_id IS NOT NULL
+ AND (
+ -- No heartbeat 30 minutes after starting the agent.
+ (
+ machine_agent_heartbeat.machine_id IS NULL
+ AND now() > (machine_agent_started.agent_started_at + interval '30 minutes')
+ )
+ -- Heartbeats ceased for 10 minutes.
+ OR (
+ machine_agent_heartbeat.machine_id IS NOT NULL
+ AND now() > (machine_agent_heartbeat.agent_heartbeat_at + interval '10 minutes')
+ )
+ )
+ AND work.machine_id IS NULL
+LIMIT $1;
+
+-- name: AuthenticateAgentConnection :many
+-- Used by bmdb server to verify incoming connections.
+SELECT
+ machine_agent_started.*
+FROM machines
+INNER JOIN machine_agent_started ON machines.machine_id = machine_agent_started.machine_id
+WHERE
+ machines.machine_id = $1
+ AND machine_agent_started.agent_public_key = $2;
+
+-- name: GetExactMachineForOSInstallation :many
+-- Get OS installation request for a given machine ID. Used by the bmdb server
+-- to tell agent whether there's a pending installation request for the machine
+-- it's running on.
+SELECT
+ machine_os_installation_request.*
+FROM machines
+LEFT JOIN machine_os_installation_request ON machines.machine_id = machine_os_installation_request.machine_id
+LEFT JOIN machine_os_installation_report ON machines.machine_id = machine_os_installation_report.machine_id
+WHERE
+ -- We are only interested in one concrete machine.
+ machines.machine_id = $1
+ -- We must have an installation request.
+ AND machine_os_installation_request.machine_id IS NOT NULL
+ -- And we either must have no installation report, or the installation
+ -- report's generation must not match the installation request's generation.
+ AND (
+ machine_os_installation_report.machine_id IS NULL
+ OR machine_os_installation_report.generation != machine_os_installation_request.generation
+ )
+LIMIT $2;
+