benvin
721f4c1af3
Storage + CRUD (migration 0006, model, id-keyed store, REST handlers) plus compiler rendering: each section is owned by a device and projected into that device's rendered config (hosts/providers/routes/routing_rules).
49 lines
1.9 KiB
SQL
49 lines
1.9 KiB
SQL
-- Per-device long-tail sections: hosts, providers (multi-ISP), static routes,
|
|
-- and routing rules (rtrules). Each is owned by a device and renders into that
|
|
-- device's config. (Nested option structs on host/provider are deferred.)
|
|
|
|
CREATE TABLE hosts (
|
|
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
device TEXT NOT NULL REFERENCES devices(name) ON DELETE CASCADE,
|
|
zone TEXT NOT NULL,
|
|
interface TEXT NOT NULL,
|
|
addresses JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
exclusions JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
dynamic BOOLEAN NOT NULL DEFAULT false
|
|
);
|
|
|
|
CREATE TABLE providers (
|
|
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
device TEXT NOT NULL REFERENCES devices(name) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
number INT NOT NULL,
|
|
mark INT NOT NULL DEFAULT 0,
|
|
duplicate TEXT NOT NULL DEFAULT '',
|
|
interface TEXT NOT NULL,
|
|
gateway TEXT NOT NULL DEFAULT '',
|
|
copy JSONB NOT NULL DEFAULT '[]'::jsonb
|
|
);
|
|
|
|
CREATE TABLE routes (
|
|
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
device TEXT NOT NULL REFERENCES devices(name) ON DELETE CASCADE,
|
|
provider TEXT NOT NULL DEFAULT '',
|
|
dest TEXT NOT NULL,
|
|
gateway TEXT NOT NULL DEFAULT '',
|
|
oif TEXT NOT NULL DEFAULT '', -- egress interface (tomswall route "device")
|
|
persistent BOOLEAN NOT NULL DEFAULT false,
|
|
comment TEXT NOT NULL DEFAULT ''
|
|
);
|
|
|
|
CREATE TABLE routing_rules (
|
|
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
device TEXT NOT NULL REFERENCES devices(name) ON DELETE CASCADE,
|
|
source TEXT NOT NULL DEFAULT '',
|
|
dest TEXT NOT NULL DEFAULT '',
|
|
provider TEXT NOT NULL,
|
|
priority INT NOT NULL DEFAULT 0,
|
|
persistent BOOLEAN NOT NULL DEFAULT false,
|
|
mark TEXT NOT NULL DEFAULT '',
|
|
comment TEXT NOT NULL DEFAULT ''
|
|
);
|