149 lines
4.3 KiB
SQL
149 lines
4.3 KiB
SQL
-- LegacyWorlds Beta 5
|
|
-- PostgreSQL database scripts
|
|
--
|
|
-- 13-main-forums.sql
|
|
--
|
|
-- Tables for the forums
|
|
--
|
|
-- Copyright(C) 2004-2007, DeepClone Development
|
|
-- --------------------------------------------------------
|
|
|
|
|
|
|
|
-- Connect to the database
|
|
\c legacyworlds legacyworlds_admin
|
|
|
|
|
|
|
|
--
|
|
-- Forum categories
|
|
--
|
|
CREATE TABLE main.f_category (
|
|
id SERIAL NOT NULL PRIMARY KEY,
|
|
corder INT NOT NULL UNIQUE CHECK(corder >= 0),
|
|
title VARCHAR(64) NOT NULL UNIQUE,
|
|
description TEXT
|
|
);
|
|
|
|
GRANT SELECT,INSERT ON TABLE main.f_category TO legacyworlds;
|
|
GRANT SELECT,UPDATE ON main.f_category_id_seq TO legacyworlds;
|
|
|
|
|
|
--
|
|
-- Forums
|
|
--
|
|
CREATE TABLE main.f_forum (
|
|
id SERIAL NOT NULL PRIMARY KEY,
|
|
category INT NOT NULL REFERENCES main.f_category (id) ON DELETE CASCADE,
|
|
forder INT NOT NULL DEFAULT 0 CHECK(forder >= 0),
|
|
title VARCHAR(64) NOT NULL,
|
|
description TEXT,
|
|
topics INT NOT NULL CHECK(topics >= 0),
|
|
posts INT NOT NULL CHECK(posts >= 0),
|
|
last_post BIGINT NULL,
|
|
user_post BOOLEAN NOT NULL DEFAULT TRUE,
|
|
admin_only BOOLEAN NOT NULL DEFAULT FALSE
|
|
);
|
|
|
|
CREATE UNIQUE INDEX forum_unique ON main.f_forum (category, forder);
|
|
CREATE INDEX forum_last_post ON main.f_forum (last_post);
|
|
|
|
GRANT SELECT,UPDATE,INSERT ON TABLE main.f_forum TO legacyworlds;
|
|
GRANT SELECT,UPDATE ON main.f_forum_id_seq TO legacyworlds;
|
|
|
|
|
|
--
|
|
-- Topics
|
|
--
|
|
CREATE TABLE main.f_topic (
|
|
id BIGSERIAL NOT NULL PRIMARY KEY,
|
|
forum INT NOT NULL REFERENCES main.f_forum (id) ON DELETE CASCADE,
|
|
first_post BIGINT NOT NULL,
|
|
last_post BIGINT NULL,
|
|
sticky BOOLEAN NOT NULL DEFAULT FALSE,
|
|
deleted INT NULL
|
|
);
|
|
|
|
CREATE INDEX topic_forum ON main.f_topic (forum);
|
|
CREATE INDEX topic_fpost ON main.f_topic (first_post);
|
|
CREATE INDEX topic_lpost ON main.f_topic (last_post);
|
|
|
|
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE main.f_topic TO legacyworlds;
|
|
GRANT SELECT,UPDATE ON main.f_topic_id_seq TO legacyworlds;
|
|
|
|
|
|
--
|
|
-- Posts
|
|
--
|
|
CREATE TABLE main.f_post (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
forum INT NOT NULL REFERENCES main.f_forum (id) ON DELETE CASCADE,
|
|
topic BIGINT NULL REFERENCES main.f_topic (id) ON DELETE CASCADE,
|
|
author BIGINT NOT NULL REFERENCES main.account (id),
|
|
reply_to BIGINT NULL REFERENCES main.f_post (id) ON DELETE SET NULL,
|
|
moment INT NOT NULL DEFAULT INT4(EXTRACT(EPOCH FROM NOW())),
|
|
title VARCHAR(100) NOT NULL,
|
|
contents TEXT NOT NULL,
|
|
enable_code BOOLEAN NOT NULL DEFAULT TRUE,
|
|
enable_smileys BOOLEAN NOT NULL DEFAULT TRUE,
|
|
edited INT NULL,
|
|
edited_by BIGINT NULL REFERENCES main.account (id),
|
|
deleted INT NULL
|
|
);
|
|
|
|
CREATE INDEX post_forum ON main.f_post (forum);
|
|
CREATE INDEX post_topic ON main.f_post (topic);
|
|
CREATE INDEX post_author ON main.f_post (author);
|
|
CREATE INDEX post_editor ON main.f_post (edited_by);
|
|
CREATE INDEX post_reply ON main.f_post (reply_to);
|
|
|
|
ALTER TABLE main.f_forum ADD FOREIGN KEY (last_post) REFERENCES main.f_post (id) ON DELETE SET NULL;
|
|
ALTER TABLE main.f_topic ADD FOREIGN KEY (first_post) REFERENCES main.f_post (id) ON DELETE CASCADE;
|
|
ALTER TABLE main.f_topic ADD FOREIGN KEY (last_post) REFERENCES main.f_post (id) ON DELETE SET NULL;
|
|
|
|
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE main.f_post TO legacyworlds;
|
|
GRANT SELECT,UPDATE ON main.f_post_id_seq TO legacyworlds;
|
|
|
|
|
|
--
|
|
-- Read topics
|
|
--
|
|
CREATE TABLE main.f_read (
|
|
reader BIGINT NOT NULL REFERENCES main.account (id),
|
|
topic BIGINT NOT NULL REFERENCES main.f_topic (id) ON DELETE CASCADE,
|
|
PRIMARY KEY (reader, topic)
|
|
);
|
|
|
|
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE main.f_read TO legacyworlds;
|
|
|
|
|
|
--
|
|
-- Smileys and forum codes
|
|
--
|
|
CREATE TABLE main.f_smiley (
|
|
smiley VARCHAR(20) NOT NULL PRIMARY KEY,
|
|
file VARCHAR(20) NOT NULL
|
|
);
|
|
CREATE TABLE main.f_code (
|
|
p_reg_exp VARCHAR(40) NOT NULL PRIMARY KEY,
|
|
replacement VARCHAR(80) NOT NULL
|
|
);
|
|
GRANT SELECT ON main.f_smiley TO legacyworlds;
|
|
GRANT SELECT ON main.f_code TO legacyworlds;
|
|
|
|
|
|
--
|
|
-- Admins, mods, losers
|
|
-- Not everything is useful in the current version so meh.
|
|
--
|
|
CREATE TABLE main.f_admin (
|
|
"user" BIGINT NOT NULL REFERENCES main.account (id) PRIMARY KEY,
|
|
category INT NULL REFERENCES main.f_category (id) ON DELETE CASCADE
|
|
);
|
|
GRANT SELECT,INSERT,UPDATE,DELETE ON main.f_admin TO legacyworlds;
|
|
|
|
CREATE TABLE main.f_moderator (
|
|
"user" BIGINT NOT NULL REFERENCES main.account (id) PRIMARY KEY,
|
|
forum INT NULL REFERENCES main.f_forum (id) ON DELETE CASCADE
|
|
);
|
|
GRANT SELECT,INSERT,UPDATE,DELETE ON main.f_moderator TO legacyworlds;
|