53 lines
1.3 KiB
PL/PgSQL
53 lines
1.3 KiB
PL/PgSQL
CREATE TABLE boards (
|
|
board_id VARCHAR(5) PRIMARY KEY,
|
|
board_name VARCHAR(32) NOT NULL,
|
|
options JSON
|
|
);
|
|
|
|
CREATE TABLE threads (
|
|
board_id VARCHAR(5) NOT NULL,
|
|
thread_id BIGINT NOT NULL,
|
|
thread_name VARCHAR(32),
|
|
posts_ids BIGINT[],
|
|
is_locked boolean NOT NULL,
|
|
is_pinned boolean NOT NULL,
|
|
options VARCHAR(255),
|
|
PRIMARY KEY(board_id, thread_id)
|
|
);
|
|
|
|
CREATE TABLE media (
|
|
media_id VARCHAR(22) PRIMARY KEY -- format: ${thread_id}-${media_number}, len(thread_id) = 20, + "-" + len(media_number) = 1 = 22
|
|
);
|
|
|
|
CREATE TABLE posts (
|
|
board_id VARCHAR(5) NOT NULL,
|
|
thread_id BIGINT NOT NULL,
|
|
post_id BIGINT NOT NULL,
|
|
options VARCHAR(255),
|
|
content TEXT NOT NULL,
|
|
media_ids VARCHAR(22)[],
|
|
is_root BOOL NOT NULL,
|
|
timestamp TIMESTAMP NOT NULL,
|
|
user_ip CIDR NOT NULL,
|
|
PRIMARY KEY(board_id, thread_id, post_id)
|
|
);
|
|
|
|
CREATE TABLE admins (
|
|
login VARCHAR(16) PRIMARY KEY,
|
|
password_hash CHAR(60) NOT NULL,
|
|
privelege_name VARCHAR(16) NOT NULL
|
|
);
|
|
|
|
CREATE TABLE priveleges (
|
|
privelege_name VARCHAR(16) PRIMARY KEY,
|
|
access_level INT2 NOT NULL
|
|
);
|
|
|
|
CREATE TABLE bans (
|
|
ban_id SERIAL8 PRIMARY KEY,
|
|
user_ip CIDR NOT NULL,
|
|
duration INT8, -- duration in seconds
|
|
reason VARCHAR(255),
|
|
date DATE NOT NULL,
|
|
admin_issued VARCHAR(16) NOT NULL
|
|
); |