cloud/bmaas/bmdb: init
This adds the initial Bare Metal Database structure. This change focuses
on a session/work mechanism which is the foundation on which we will
build worker components. It allows lease-like mechanics on machines,
letting us not have to use 'standard' work queues in the BMaaS project.
Change-Id: I42c3f4384c64fd90dbeab8ff9652a6f611be81d4
Reviewed-on: https://review.monogon.dev/c/monogon/+/953
Tested-by: Jenkins CI
Reviewed-by: Lorenz Brun <lorenz@monogon.tech>
diff --git a/cloud/bmaas/bmdb/model/BUILD.bazel b/cloud/bmaas/bmdb/model/BUILD.bazel
new file mode 100644
index 0000000..7a0f122
--- /dev/null
+++ b/cloud/bmaas/bmdb/model/BUILD.bazel
@@ -0,0 +1,28 @@
+load("@io_bazel_rules_go//go:def.bzl", "go_library")
+load("//build/sqlc:sqlc.bzl", "sqlc_go_library")
+
+sqlc_go_library(
+ name = "sqlc_model",
+ dialect = "cockroachdb",
+ importpath = "source.monogon.dev/cloud/bmaas/bmdb/model",
+ migrations = glob(["migrations/*sql"]),
+ queries = [
+ "queries.sql",
+ ],
+)
+
+go_library(
+ name = "model",
+ srcs = ["migrations.go"],
+ embed = [
+ ":sqlc_model", # keep
+ ],
+ embedsrcs = glob(["migrations/*sql"]),
+ importpath = "source.monogon.dev/cloud/bmaas/bmdb/model",
+ visibility = ["//visibility:public"],
+ deps = [
+ "@com_github_golang_migrate_migrate_v4//source",
+ "@com_github_golang_migrate_migrate_v4//source/iofs",
+ "@com_github_google_uuid//:uuid", # keep
+ ],
+)
diff --git a/cloud/bmaas/bmdb/model/migrations.go b/cloud/bmaas/bmdb/model/migrations.go
new file mode 100644
index 0000000..2c07768
--- /dev/null
+++ b/cloud/bmaas/bmdb/model/migrations.go
@@ -0,0 +1,15 @@
+package model
+
+import (
+ "embed"
+
+ "github.com/golang-migrate/migrate/v4/source"
+ "github.com/golang-migrate/migrate/v4/source/iofs"
+)
+
+//go:embed migrations/*.sql
+var migrationData embed.FS
+
+func MigrationsSource() (source.Driver, error) {
+ return iofs.New(migrationData, "migrations")
+}
diff --git a/cloud/bmaas/bmdb/model/migrations/1662136250_initial.down.sql b/cloud/bmaas/bmdb/model/migrations/1662136250_initial.down.sql
new file mode 100644
index 0000000..6a85991
--- /dev/null
+++ b/cloud/bmaas/bmdb/model/migrations/1662136250_initial.down.sql
@@ -0,0 +1,6 @@
+DROP TABLE machine_agent_report;
+DROP TABLE machine_agent_installed;
+DROP TABLE machine_provided;
+DROP TABLE work;
+DROP TABLE sessions;
+DROP TABLE machines;
diff --git a/cloud/bmaas/bmdb/model/migrations/1662136250_initial.up.sql b/cloud/bmaas/bmdb/model/migrations/1662136250_initial.up.sql
new file mode 100644
index 0000000..b31324f
--- /dev/null
+++ b/cloud/bmaas/bmdb/model/migrations/1662136250_initial.up.sql
@@ -0,0 +1,73 @@
+CREATE TABLE machines (
+ machine_id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
+ machine_created_at TIMESTAMPTZ NOT NULL
+);
+
+
+-- Sessions are maintained by components as they work on the rest of the machine
+-- database. Once a session is created, it must be maintained by its owning
+-- component by repeatedly 'poking' it, ie. updating the heartbeat_deadline
+-- value to be some point in the future.
+--
+-- TODO: garbage collect old sessions.
+CREATE TABLE sessions (
+ session_id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
+ -- Name of component which created this session. Human-readable.
+ session_component_name STRING NOT NULL,
+ -- Node name, hostname:port, pod name, whatever. Something to tell where
+ -- a particular component is running. Human-readable.
+ session_runtime_info STRING NOT NULL,
+ -- Time at which this session was created.
+ session_created_at TIMESTAMPTZ NOT NULL,
+ -- Number of seconds by which session_deadline (counting from now())
+ -- is bumped up every time the session is poked.
+ session_interval_seconds INT NOT NULL,
+ -- Deadline after which this session should not be considered valid anymore.
+ session_deadline TIMESTAMPTZ NOT NULL
+);
+
+CREATE TYPE process AS ENUM (
+ -- Reserved for unit tests.
+ 'UnitTest1',
+ 'UnitTest2'
+);
+
+-- Work items map a session to work performed on a machine. Multiple work items
+-- can exist per session, and thus, a session can back multiple items of work
+-- acting on multiple machines. These are optionally created by components to
+-- indicate some long-running process being performed on a machine, and will
+-- lock out the same process from being run simultaneously, eg. in a
+-- concurrently running instance of the same component.
+CREATE TABLE work (
+ -- Machine that this work is being performed on. Prevent deleting machines
+ -- that have active work on them.
+ machine_id UUID NOT NULL REFERENCES machines(machine_id) ON DELETE RESTRICT,
+ -- Session that this work item is tied to. If the session expires, so does
+ -- the work item.
+ session_id UUID NOT NULL REFERENCES sessions(session_id) ON DELETE CASCADE,
+ -- Human-readable process name.
+ process process NOT NULL,
+ UNIQUE (machine_id, process),
+ CONSTRAINT "primary" PRIMARY KEY (machine_id, session_id, process)
+);
+
+-- The following three tables are for illustrative purposes only.
+
+CREATE TABLE machine_provided (
+ machine_id UUID NOT NULL REFERENCES machines(machine_id) ON DELETE CASCADE,
+ provider STRING NOT NULL,
+ provider_id STRING NOT NULL,
+ CONSTRAINT "primary" PRIMARY KEY (machine_id)
+);
+
+CREATE TABLE machine_agent_installed (
+ machine_id UUID NOT NULL REFERENCES machines(machine_id) ON DELETE CASCADE,
+ CONSTRAINT "primary" PRIMARY KEY (machine_id)
+);
+
+CREATE TABLE machine_agent_report (
+ machine_id UUID NOT NULL REFERENCES machines(machine_id) ON DELETE CASCADE,
+ shape_cpu_count INT NOT NULL,
+ shape_memory_megabytes INT NOT NULL,
+ CONSTRAINT "primary" PRIMARY KEY (machine_id)
+);
diff --git a/cloud/bmaas/bmdb/model/queries.sql b/cloud/bmaas/bmdb/model/queries.sql
new file mode 100644
index 0000000..ee3f618
--- /dev/null
+++ b/cloud/bmaas/bmdb/model/queries.sql
@@ -0,0 +1,64 @@
+-- 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;
+
+-- Example tag processing queries follow.
+
+-- name: MachineAddProvided :exec
+INSERT INTO machine_provided (
+ machine_id, provider, provider_id
+) VALUES (
+ $1, $2, $3
+);
+
+-- name: GetMachinesNeedingInstall :many
+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 = 'NecromancerInstall'
+LEFT JOIN machine_agent_installed ON machines.machine_id = machine_agent_installed.machine_id
+WHERE machine_agent_installed.machine_id IS NULL
+ AND work.machine_id IS NULL
+LIMIT $1;
+