66 lines
1.9 KiB
SQL
66 lines
1.9 KiB
SQL
-- LegacyWorlds Beta 5
|
|
-- PostgreSQL database scripts
|
|
--
|
|
-- 13-main-manual.sql
|
|
--
|
|
-- Tables for the manual and its index
|
|
--
|
|
-- Copyright(C) 2004-2007, DeepClone Development
|
|
-- --------------------------------------------------------
|
|
|
|
|
|
|
|
-- Connect to the database
|
|
\c legacyworlds legacyworlds_admin
|
|
|
|
|
|
|
|
--
|
|
-- Manual sections
|
|
--
|
|
CREATE TABLE main.man_section (
|
|
id BIGSERIAL NOT NULL PRIMARY KEY,
|
|
version VARCHAR(16) NOT NULL,
|
|
lang VARCHAR(4) NOT NULL REFERENCES main.lang (txt),
|
|
in_section BIGINT REFERENCES main.man_section (id) ON DELETE SET NULL,
|
|
after_section BIGINT REFERENCES main.man_section (id) ON DELETE SET NULL,
|
|
link_to BIGINT REFERENCES main.man_section (id) ON DELETE SET NULL,
|
|
name VARCHAR(64) NOT NULL,
|
|
last_update INT NOT NULL,
|
|
is_page BOOLEAN NOT NULL,
|
|
in_menu BOOLEAN NOT NULL,
|
|
title VARCHAR(128) NOT NULL,
|
|
contents TEXT NOT NULL
|
|
);
|
|
|
|
CREATE UNIQUE INDEX man_section_unique ON main.man_section (version, lang, name);
|
|
CREATE INDEX man_section_in_section ON main.man_section (in_section);
|
|
CREATE INDEX man_section_after_section ON main.man_section (after_section);
|
|
CREATE INDEX man_section_link_to ON main.man_section (link_to);
|
|
|
|
GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE main.man_section TO legacyworlds;
|
|
GRANT SELECT,UPDATE ON main.man_section_id_seq TO legacyworlds;
|
|
|
|
|
|
--
|
|
-- Manual index
|
|
--
|
|
CREATE TABLE main.man_index (
|
|
word VARCHAR(32) NOT NULL,
|
|
wcount INT NOT NULL CHECK(wcount > 0),
|
|
lang VARCHAR(4) NOT NULL REFERENCES main.lang (txt),
|
|
section BIGINT NOT NULL REFERENCES main.man_section (id) ON DELETE CASCADE,
|
|
PRIMARY KEY (word, lang, section)
|
|
);
|
|
GRANT SELECT,INSERT,DELETE ON TABLE main.man_index TO legacyworlds;
|
|
|
|
|
|
--
|
|
-- Banned words
|
|
--
|
|
CREATE TABLE main.man_index_ban (
|
|
lang VARCHAR(4) NOT NULL REFERENCES main.lang (txt),
|
|
word VARCHAR(32) NOT NULL,
|
|
PRIMARY KEY (lang,word)
|
|
);
|
|
GRANT SELECT ON TABLE main.man_index_ban TO legacyworlds;
|