pasty/schema.sql
2024-01-12 15:13:48 +02:00

22 lines
673 B
SQL

CREATE TABLE snippets (
id SERIAL PRIMARY KEY,
url VARCHAR(12) UNIQUE NOT NULL,
access_key bytea NOT NULL,
access_count INTEGER DEFAULT 0
);
CREATE TABLE snippet_content (
id SERIAL PRIMARY KEY,
revision INTEGER NOT NULL DEFAULT 0,
owner_snippet INTEGER NOT NULL,
ts TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
body TEXT NOT NULL,
origin TEXT,
FOREIGN KEY (owner_snippet) REFERENCES snippets(id) ON DELETE CASCADE
);
ALTER TABLE snippets ADD COLUMN body INTEGER,
ADD FOREIGN KEY (body) REFERENCES snippet_content(id) ON DELETE SET NULL;
CREATE INDEX idx_url ON snippets(url);
CREATE INDEX idx_content ON snippet_content(owner_snippet);