86 lines
3.1 KiB
MySQL
86 lines
3.1 KiB
MySQL
|
CREATE TABLE pools (
|
||
|
|
pool_id TEXT PRIMARY KEY,
|
||
|
|
limit_bytes BIGINT NOT NULL CHECK (limit_bytes > 0),
|
||
|
|
used_bytes BIGINT NOT NULL DEFAULT 0 CHECK (used_bytes >= 0),
|
||
|
|
reserved_bytes BIGINT NOT NULL DEFAULT 0 CHECK (reserved_bytes >= 0),
|
||
|
|
CONSTRAINT pools_live_bytes_within_limit
|
||
|
|
CHECK (used_bytes + reserved_bytes <= limit_bytes)
|
||
|
|
);
|
||
|
|
|
||
|
|
INSERT INTO pools (pool_id, limit_bytes, used_bytes, reserved_bytes)
|
||
|
|
VALUES ('shared', 10737418240, 0, 0);
|
||
|
|
|
||
|
|
CREATE TABLE mutations (
|
||
|
|
user_id TEXT NOT NULL,
|
||
|
|
kind TEXT NOT NULL,
|
||
|
|
operation_id TEXT NOT NULL,
|
||
|
|
pool_id TEXT NOT NULL REFERENCES pools (pool_id),
|
||
|
|
object_key TEXT NOT NULL,
|
||
|
|
relative_path TEXT NOT NULL,
|
||
|
|
declared_size BIGINT NOT NULL,
|
||
|
|
content_sha256 TEXT,
|
||
|
|
prior_size BIGINT NOT NULL,
|
||
|
|
prior_sha256 TEXT,
|
||
|
|
growth_bytes BIGINT NOT NULL,
|
||
|
|
state TEXT NOT NULL,
|
||
|
|
external_state TEXT NOT NULL DEFAULT 'idle',
|
||
|
|
dispatch_started_at TIMESTAMPTZ,
|
||
|
|
request_deadline_at TIMESTAMPTZ,
|
||
|
|
worker_lease_until TIMESTAMPTZ,
|
||
|
|
settled_at TIMESTAMPTZ,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
|
|
PRIMARY KEY (user_id, kind, operation_id),
|
||
|
|
CONSTRAINT mutations_kind_check
|
||
|
|
CHECK (kind IN ('put', 'delete')),
|
||
|
|
CONSTRAINT mutations_declared_size_check
|
||
|
|
CHECK (declared_size >= 0),
|
||
|
|
CONSTRAINT mutations_prior_size_check
|
||
|
|
CHECK (prior_size >= 0),
|
||
|
|
CONSTRAINT mutations_growth_bytes_check
|
||
|
|
CHECK (growth_bytes >= 0),
|
||
|
|
CONSTRAINT mutations_state_check
|
||
|
|
CHECK (state IN ('prepared', 'applying', 'succeeded', 'failed', 'rejected')),
|
||
|
|
CONSTRAINT mutations_external_state_check
|
||
|
|
CHECK (external_state IN ('idle', 'pending', 'settled')),
|
||
|
|
CONSTRAINT mutations_dispatch_timing_check CHECK (
|
||
|
|
external_state <> 'pending'
|
||
|
|
OR (
|
||
|
|
dispatch_started_at IS NOT NULL
|
||
|
|
AND request_deadline_at IS NOT NULL
|
||
|
|
AND worker_lease_until IS NOT NULL
|
||
|
|
)
|
||
|
|
)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX mutations_active_key_idx
|
||
|
|
ON mutations (object_key)
|
||
|
|
WHERE state IN ('prepared', 'applying');
|
||
|
|
|
||
|
|
CREATE INDEX mutations_active_idx
|
||
|
|
ON mutations (state)
|
||
|
|
WHERE state IN ('prepared', 'applying');
|
||
|
|
|
||
|
|
CREATE INDEX mutations_recovery_idx
|
||
|
|
ON mutations (worker_lease_until, request_deadline_at, updated_at)
|
||
|
|
WHERE state IN ('prepared', 'applying');
|
||
|
|
|
||
|
|
CREATE TABLE key_locks (
|
||
|
|
object_key TEXT PRIMARY KEY,
|
||
|
|
user_id TEXT NOT NULL,
|
||
|
|
kind TEXT NOT NULL,
|
||
|
|
operation_id TEXT NOT NULL,
|
||
|
|
acquired_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
|
|
FOREIGN KEY (user_id, kind, operation_id)
|
||
|
|
REFERENCES mutations (user_id, kind, operation_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE objects_meta (
|
||
|
|
object_key TEXT PRIMARY KEY,
|
||
|
|
pool_id TEXT NOT NULL REFERENCES pools (pool_id),
|
||
|
|
size_bytes BIGINT NOT NULL,
|
||
|
|
content_sha256 TEXT NOT NULL,
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
|
|
CONSTRAINT objects_meta_size_bytes_check
|
||
|
|
CHECK (size_bytes >= 0)
|
||
|
|
);
|