benvin
6b600f8c8d
Storage + CRUD (migration 0008, model, id-keyed store, REST handlers) + compiler rendering for mangle, accounting, and tc_devices/tc_classes/tc_filters/ tc_interfaces/tc_priorities, each owned by a device. (Nested tc option structs deferred.)
97 lines
3.6 KiB
SQL
97 lines
3.6 KiB
SQL
-- Traffic-control tier: mangle, accounting, and tc_* (device/class/filter/
|
|
-- interface/priority). Each is owned by a device. (Nested tc option structs on
|
|
-- tc_device/tc_class are deferred.)
|
|
|
|
CREATE TABLE mangle (
|
|
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
device TEXT NOT NULL REFERENCES devices(name) ON DELETE CASCADE,
|
|
action TEXT NOT NULL,
|
|
chain TEXT NOT NULL DEFAULT '',
|
|
mark_value TEXT NOT NULL DEFAULT '',
|
|
source TEXT NOT NULL DEFAULT '',
|
|
dest TEXT NOT NULL DEFAULT '',
|
|
proto TEXT NOT NULL DEFAULT '',
|
|
dport JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
sport JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
"user" TEXT NOT NULL DEFAULT '',
|
|
mark TEXT NOT NULL DEFAULT '',
|
|
length TEXT NOT NULL DEFAULT '',
|
|
tos TEXT NOT NULL DEFAULT '',
|
|
helper TEXT NOT NULL DEFAULT '',
|
|
probability REAL,
|
|
comment TEXT NOT NULL DEFAULT ''
|
|
);
|
|
|
|
CREATE TABLE accounting (
|
|
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
device TEXT NOT NULL REFERENCES devices(name) ON DELETE CASCADE,
|
|
action TEXT NOT NULL,
|
|
section TEXT NOT NULL DEFAULT '',
|
|
chain TEXT NOT NULL DEFAULT '',
|
|
source TEXT NOT NULL DEFAULT '',
|
|
dest TEXT NOT NULL DEFAULT '',
|
|
proto TEXT NOT NULL DEFAULT '',
|
|
dport JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
sport JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
mark TEXT NOT NULL DEFAULT '',
|
|
comment TEXT NOT NULL DEFAULT ''
|
|
);
|
|
|
|
CREATE TABLE tc_devices (
|
|
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
device TEXT NOT NULL REFERENCES devices(name) ON DELETE CASCADE,
|
|
interface TEXT NOT NULL,
|
|
in_bandwidth TEXT NOT NULL DEFAULT '',
|
|
out_bandwidth TEXT NOT NULL DEFAULT '',
|
|
comment TEXT NOT NULL DEFAULT ''
|
|
);
|
|
|
|
CREATE TABLE tc_classes (
|
|
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
device TEXT NOT NULL REFERENCES devices(name) ON DELETE CASCADE,
|
|
interface TEXT NOT NULL,
|
|
mark INT NOT NULL DEFAULT 0,
|
|
rate TEXT NOT NULL DEFAULT '',
|
|
ceil TEXT NOT NULL DEFAULT '',
|
|
priority INT NOT NULL DEFAULT 0,
|
|
comment TEXT NOT NULL DEFAULT ''
|
|
);
|
|
|
|
CREATE TABLE tc_filters (
|
|
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
device TEXT NOT NULL REFERENCES devices(name) ON DELETE CASCADE,
|
|
class TEXT NOT NULL,
|
|
source TEXT NOT NULL DEFAULT '',
|
|
dest TEXT NOT NULL DEFAULT '',
|
|
proto TEXT NOT NULL DEFAULT '',
|
|
dport JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
sport JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
tos TEXT NOT NULL DEFAULT '',
|
|
length INT NOT NULL DEFAULT 0,
|
|
priority INT NOT NULL DEFAULT 0,
|
|
comment TEXT NOT NULL DEFAULT ''
|
|
);
|
|
|
|
CREATE TABLE tc_interfaces (
|
|
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
device TEXT NOT NULL REFERENCES devices(name) ON DELETE CASCADE,
|
|
interface TEXT NOT NULL,
|
|
type TEXT NOT NULL DEFAULT '',
|
|
in_bandwidth TEXT NOT NULL DEFAULT '',
|
|
out_bandwidth TEXT NOT NULL DEFAULT '',
|
|
comment TEXT NOT NULL DEFAULT ''
|
|
);
|
|
|
|
CREATE TABLE tc_priorities (
|
|
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
device TEXT NOT NULL REFERENCES devices(name) ON DELETE CASCADE,
|
|
band INT NOT NULL,
|
|
proto TEXT NOT NULL DEFAULT '',
|
|
dport JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
sport JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
address TEXT NOT NULL DEFAULT '',
|
|
interface TEXT NOT NULL DEFAULT '',
|
|
helper TEXT NOT NULL DEFAULT '',
|
|
comment TEXT NOT NULL DEFAULT ''
|
|
);
|