codegirl007
c6b5d6a456
CI / test (pull_request) Successful in 6m16s
Use post_date throughout the new schema while retaining hunt_date only as the legacy migration source.
83 lines
2.4 KiB
SQL
83 lines
2.4 KiB
SQL
CREATE TABLE IF NOT EXISTS users (
|
|
id TEXT PRIMARY KEY,
|
|
username TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
role TEXT NOT NULL DEFAULT 'user' CHECK (role IN ('user', 'admin')),
|
|
email TEXT NOT NULL DEFAULT '',
|
|
avatar_url TEXT NOT NULL DEFAULT '',
|
|
state TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS users_email_lower_uidx
|
|
ON users (lower(email))
|
|
WHERE email <> '';
|
|
|
|
CREATE TABLE IF NOT EXISTS questions (
|
|
id TEXT PRIMARY KEY,
|
|
author_id TEXT NOT NULL REFERENCES users(id),
|
|
title TEXT NOT NULL,
|
|
body TEXT NOT NULL,
|
|
city TEXT NOT NULL DEFAULT '',
|
|
hunt_date TEXT NOT NULL,
|
|
hidden INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_questions_hunt_date ON questions(hunt_date, hidden);
|
|
|
|
CREATE TABLE IF NOT EXISTS votes (
|
|
user_id TEXT NOT NULL REFERENCES users(id),
|
|
question_id TEXT NOT NULL REFERENCES questions(id) ON DELETE CASCADE,
|
|
value INTEGER NOT NULL CHECK (value IN (-1, 1)),
|
|
PRIMARY KEY (user_id, question_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS answers (
|
|
question_id TEXT PRIMARY KEY REFERENCES questions(id) ON DELETE CASCADE,
|
|
author_id TEXT NOT NULL REFERENCES users(id),
|
|
body TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS posts (
|
|
id TEXT PRIMARY KEY,
|
|
parent_id TEXT REFERENCES posts(id) ON DELETE CASCADE,
|
|
author_id TEXT NOT NULL REFERENCES users(id),
|
|
title TEXT NOT NULL DEFAULT '',
|
|
body TEXT NOT NULL,
|
|
city TEXT NOT NULL DEFAULT '',
|
|
post_date TEXT NOT NULL DEFAULT '',
|
|
hidden INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
CHECK (
|
|
(parent_id IS NULL AND title <> '' AND post_date <> '')
|
|
OR
|
|
(parent_id IS NOT NULL AND title = '' AND city = '' AND post_date = '' AND hidden = 0)
|
|
)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_posts_parent_created
|
|
ON posts(parent_id, created_at, id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_posts_root_date
|
|
ON posts(post_date, hidden)
|
|
WHERE parent_id IS NULL;
|
|
|
|
CREATE TABLE IF NOT EXISTS post_votes (
|
|
user_id TEXT NOT NULL REFERENCES users(id),
|
|
post_id TEXT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
|
|
value INTEGER NOT NULL CHECK (value IN (-1, 1)),
|
|
PRIMARY KEY (user_id, post_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
token TEXT PRIMARY KEY,
|
|
data BYTEA NOT NULL,
|
|
expiry TIMESTAMPTZ NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS sessions_expiry_idx ON sessions (expiry);
|