-- Parley schema
-- to get your user and database:
-- createuser -A -d parley
-- createdb -U parley -E UTF8 parley
-- psql -U parley -d parley -f db/parley.psql
\set QUIET 'on'
-- wrap it all in a transaction
BEGIN;
-- put all tables in a separate namespace
CREATE SCHEMA parley;
ALTER SCHEMA parley OWNER TO parley;
-- Create a table in your database for sessions
-- [from Catalyst::Plugin::Session::Store::DBIC docs]
CREATE TABLE parley.sessions (
id CHAR(72) PRIMARY KEY,
session_data TEXT,
expires INTEGER,
-- we like to know when a session was created
created timestamp with time zone
default CURRENT_TIMESTAMP
not null
);
ALTER TABLE parley.sessions OWNER TO parley;
-- we have authentication
CREATE TABLE parley.authentication (
id SERIAL not null primary key,
username text not null,
password text not null,
authenticated boolean NOT NULL default False,
UNIQUE (username)
);
-- a table of acceptable TZ strings
CREATE TABLE parley.preference_time_string (
id SERIAL primary key,
time_string text not null,
sample text not null,
comment text
);
-- some TZ strings
INSERT INTO preference_time_string (time_string, sample) VALUES ('%A %d %B %Y at %R', 'Thursday 13 July 2006 at 18:15');
INSERT INTO preference_time_string (time_string, sample) VALUES ('%F %T', '2006-07-13 18:15:00');
INSERT INTO preference_time_string (time_string, sample, comment) VALUES ('%c','Thu 13 Jul 2006 18:15:00 BST','locale''s date and time');
INSERT INTO preference_time_string (time_string, sample) VALUES ('%A at %R', 'Thursday at 18:15');
INSERT INTO preference_time_string (time_string, sample) VALUES ('%a, %d %b; %R', 'Thu, 13 Jul; 18:15');
INSERT INTO preference_time_string (time_string, sample) VALUES ('%a, %d %b; %R', 'Thu, 13 Jul; 18:15');
INSERT INTO preference_time_string (time_string, sample) VALUES ('%R, %b %d', '18:15, Jul 13');
INSERT INTO preference_time_string (time_string, sample) VALUES ('%R, %b %d ''%y', '18:15, Jul 13 ''06');
CREATE TABLE parley.preference (
id SERIAL primary key,
timezone text not null
default 'UTC',
time_format_id integer references preference_time_string(id),
show_tz boolean default True,
notify_thread_watch boolean default False,
watch_on_post boolean default True,
skin varchar(100) NULL
);
CREATE TABLE parley.person (
id SERIAL primary key,
first_name text not null,
last_name text not null,
email text not null,
forum_name text not null,
preference_id integer
references preference(id),
authentication_id integer
references authentication(id),
suspended boolean not null
default False,
UNIQUE(forum_name),
UNIQUE(email)
);
CREATE TABLE parley.registration_authentication (
id text primary key,
recipient_id integer not null
references person(id),
expires date
);
-- new table used during forgotten password process
CREATE TABLE parley.password_reset (
id text primary key,
recipient_id integer not null
references person(id),
expires timestamp
);
CREATE TABLE parley.forum (
id SERIAL primary key,
name text not null,
description text,
active boolean not null default True,
post_count integer not null default 0,
UNIQUE(name)
);
CREATE TABLE parley.thread (
id SERIAL primary key,
forum_id integer not null
references forum(id),
subject text not null,
created timestamp with time zone
default CURRENT_TIMESTAMP,
creator_id integer not null
references person(id),
post_count integer not null default 0,
view_count integer not null default 0,
active boolean not null default True,
sticky boolean not null default False,
locked boolean not null default False
);
CREATE TABLE parley.post (
id SERIAL primary key,
thread_id integer not null
references thread(id),
subject text,
message text not null,
created timestamp with time zone
default CURRENT_TIMESTAMP,
creator_id integer not null
references person(id),
ip_addr inet,
admin_editor_id integer
references person(id),
locked boolean not null
default False
);
-- add ReplyTo information for post
ALTER TABLE parley.post ADD COLUMN reply_to_id
integer references post(id)
;
-- deal with quoted replies
ALTER TABLE parley.post
ADD COLUMN quoted_post_id
integer REFERENCES post(id);
ALTER TABLE parley.post
ADD COLUMN quoted_text text;
-- add LastPost information
ALTER TABLE parley.thread ADD COLUMN last_post_id
integer references post(id)
;
ALTER TABLE parley.forum ADD COLUMN last_post_id
integer references post(id)
;
-- person posting information
ALTER TABLE parley.person ADD COLUMN last_post_id
integer references post(id)
;
ALTER TABLE parley.person ADD COLUMN post_count
integer NOT NULL default 0
;
-- post edited time (if any)
ALTER TABLE parley.post
ADD COLUMN
edited timestamp with time zone
;
-- when a user last viewed a thread
CREATE TABLE parley.thread_view (
id SERIAL not null primary key,
person_id integer not null references person(id),
thread_id integer not null references thread(id),
timestamp timestamp with time zone not null
default CURRENT_TIMESTAMP,
watched boolean not null default False,
last_notified timestamp with time zone
default NULL,
unique(person_id, thread_id)
);
-- overall it's better to queue outgoing emails, and have a separate process
-- deal with them
CREATE TABLE parley.email_queue (
id SERIAL not null primary key,
queued timestamp with time zone not null
default CURRENT_TIMESTAMP,
recipient_id integer not null references person(id),
cc_id integer references person(id),
bcc_id integer references person(id),
sender text not null,
subject text not null,
text_content text not null,
html_content text,
-- delivery statuses
attempted_delivery boolean not null default False
);
-- a table of person/forum to show who's a moderator
CREATE TABLE parley.forum_moderator (
id SERIAL primary key,
person_id integer not null references person(id),
forum_id integer not null references forum(id),
can_moderate boolean not null default true,
unique(person_id,forum_id)
);
-- a table to contain site terms & conditions
CREATE TABLE parley.terms (
id serial primary key,
created timestamp with time zone
default CURRENT_TIMESTAMP
not null,
content text not null,
change_summary text
);
-- keep track of who agreed to what, and when
CREATE TABLE parley.terms_agreed (
id serial primary key,
person_id integer not null
references person(id),
terms_id integer not null
references terms(id),
accepted_on timestamp with time zone
default CURRENT_TIMESTAMP
not null,
UNIQUE(person_id, terms_id)
);
-- roles / authentication
CREATE TABLE parley.role (
id serial primary key,
idx integer not null default 9999,
name varchar(30) not null unique,
description text
);
CREATE INDEX idx_role_name ON role(name);
CREATE TABLE parley.user_roles (
id serial primary key,
authentication_id integer not null
references authentication(id),
role_id integer not null
references role(id),
UNIQUE (authentication_id, role_id)
);
CREATE INDEX idx_userroles_authenticationid ON user_roles(authentication_id);
CREATE INDEX idx_userroles_roleid ON user_roles(role_id);
-- admin action log actions/messages
CREATE TABLE parley.admin_action (
id serial primary key,
name text not null,
UNIQUE(name)
);
INSERT INTO admin_action (id, name) VALUES (0, 'undefined');
INSERT INTO admin_action (name) VALUES ('suspend_user');
CREATE TABLE parley.log_admin_action (
id serial primary key,
person_id integer not null
references person(id),
admin_id integer not null
references person(id),
created timestamp with time zone
not null
default CURRENT_TIMESTAMP,
action_id integer not null
default 0
references admin_action(id),
message text not null
default 'No Message Supplied'
);
CREATE INDEX idx_adminaction_personid ON log_admin_action(person_id);
CREATE INDEX idx_adminaction_adminid ON log_admin_action(admin_id);
CREATE TABLE parley.ip_ban_type (
id serial primary key,
name text not null,
description text,
UNIQUE(name)
);
INSERT INTO ip_ban_type (name,description) VALUES ('access','Restrict all access by IP');
INSERT INTO ip_ban_type (name,description) VALUES ('login','Restrict login by IP');
INSERT INTO ip_ban_type (name,description) VALUES ('posting','Restrict posting by IP');
INSERT INTO ip_ban_type (name,description) VALUES ('signup','Restrict sign-up by IP');
CREATE TABLE parley.ip_ban (
id serial primary key,
ban_type_id integer not null
references ip_ban_type(id),
ip_range text,
UNIQUE(ban_type_id)
);
--
-- some default stuff
--
-- #0 authentication
INSERT INTO authentication
(id, username, password, authenticated)
VALUES
(0, 'topdog', md5('k1tt3n'), true);
-- #0 preference
INSERT INTO preference
(id, timezone)
VALUES
(0, 'UTC');
-- #0 person info
INSERT INTO person
(id,first_name, last_name, email, forum_name, authentication_id, preference_id)
VALUES
(0, 'Top','Dog','topdog@herlpacker.co.uk','TopDog', 0, 0);
-- #1 authentication
INSERT INTO authentication
(id, username, password, authenticated)
VALUES
(1, 'norm', md5('k1tt3n'), true);
-- #1 preference
INSERT INTO preference
(id, timezone)
VALUES
(1, 'UTC');
-- #1 person info
INSERT INTO person
(id,first_name, last_name, email, forum_name, authentication_id, preference_id)
VALUES
(1, 'Norman','Normal','norman.normal@herlpacker.co.uk','Norman', 1, 1);
-- fix PK sequences
SELECT setval('authentication_id_seq', 1);
SELECT setval('preference_id_seq', 1);
SELECT setval('person_id_seq', 1);
INSERT INTO forum (id, name, description) VALUES (0, 'Off-Topic', 'General off-topic discussion');
INSERT INTO forum (name, description) VALUES ('Suggestions', 'Things you think should be added');
INSERT INTO forum (name, description) VALUES ('Bugs', 'If you find anything broken, report it here');
-- some roles
INSERT INTO role (idx, name, description) VALUES (
0,
'site_moderator',
'Site Moderator'
);
INSERT INTO role (idx, name, description) VALUES (
50,
'suspend_account',
'Suspend Account'
);
INSERT INTO role (idx, name, description) VALUES (
100,
'ip_ban_posting',
'Restrict Posting by IP'
);
INSERT INTO role (idx, name, description) VALUES (
100,
'ip_ban_signup',
'Restrict Sign-Up by IP'
);
INSERT INTO role (idx, name, description) VALUES (
100,
'ip_ban_login',
'Restrict Login by IP'
);
-- make topdog a site_moderator
INSERT INTO user_roles
(authentication_id, role_id)
VALUES (
0,
(SELECT id FROM role WHERE name='site_moderator')
);
-- commit our schema
COMMIT;
-- vim:ft=sql