61 lines
2.3 KiB
SQL
61 lines
2.3 KiB
SQL
-- Durable quota and mutation protocol state for Nuthatch.
|
|
|
|
CREATE TABLE IF NOT EXISTS 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)
|
|
);
|
|
|
|
INSERT INTO pools (pool_id, limit_bytes, used_bytes, reserved_bytes)
|
|
VALUES ('shared', 10737418240, 0, 0)
|
|
ON CONFLICT (pool_id) DO NOTHING;
|
|
|
|
CREATE TABLE IF NOT EXISTS mutations (
|
|
user_id TEXT NOT NULL,
|
|
kind TEXT NOT NULL CHECK (kind IN ('put', 'delete')),
|
|
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 CHECK (declared_size >= 0),
|
|
content_sha256 TEXT,
|
|
prior_size BIGINT NOT NULL CHECK (prior_size >= 0),
|
|
prior_sha256 TEXT,
|
|
growth_bytes BIGINT NOT NULL CHECK (growth_bytes >= 0),
|
|
state TEXT NOT NULL CHECK (
|
|
state IN ('prepared', 'applying', 'succeeded', 'failed', 'rejected')
|
|
),
|
|
external_state TEXT NOT NULL DEFAULT 'idle' CHECK (
|
|
external_state IN ('idle', 'pending', 'settled')
|
|
),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (user_id, kind, operation_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS mutations_active_key_idx
|
|
ON mutations (object_key)
|
|
WHERE state IN ('prepared', 'applying');
|
|
|
|
CREATE INDEX IF NOT EXISTS mutations_active_idx
|
|
ON mutations (state)
|
|
WHERE state IN ('prepared', 'applying');
|
|
|
|
CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS objects_meta (
|
|
object_key TEXT PRIMARY KEY,
|
|
pool_id TEXT NOT NULL REFERENCES pools (pool_id),
|
|
size_bytes BIGINT NOT NULL CHECK (size_bytes >= 0),
|
|
content_sha256 TEXT NOT NULL,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|