/*
*****************************************************************
BEGIN TIMEWEAVER DATAMODEL (INTENDED DATABASE - POSTGRESQL 7.3.4)
*****************************************************************
by Jonathan Grall (grall@mit.edu)
*/
/*
**********************************
CREATE PLPGSQL & STORED PROCEDURES
**********************************
*/
/*
Commands needed to initialise a new database. This should
be loaded with \i before any other files.
*/
/* Install plpgsql as a language in the database. I can't help but
feel a little guilty doing such a horrific thing intentionally. */
CREATE FUNCTION plpgsql_call_handler () RETURNS language_handler
AS '/usr/lib/pgsql/plpgsql.so', 'plpgsql_call_handler'
LANGUAGE c;
CREATE PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
-- Function print_time_difference
-- takes a timestamp that it compares to the current time (using time())
-- prints an informative time difference for the user
--
-- If the timestamp provided is within the last 12 hours the function
-- prints the time difference in hours and minutes e.g. 10 hours 4 minutes
-- ago. If the timestamp provided was more than 12 hours ago but still on
-- the current date it prints something like 'today at 10:31am'.
-- If the timestamp provided is more than 12 hours ago but the day before
-- the current date, then it prints something like 'yesterday at
-- 6:43pm'. Otheriwse the full date and time are printed.
--
--@param timestamp date the date to investigate
--@return string the time difference
--
CREATE FUNCTION print_time_difference(timestamp with time zone) RETURNS text
AS '
DECLARE
time_to_compare ALIAS FOR $1;
right_now timestamp;
time_difference text;
the_interval_difference interval;
the_current_day integer;
the_current_month integer;
the_current_year integer;
time_to_compare_day integer;
time_to_compare_month integer;
time_to_compare_year integer;
the_day text;
the_hour_of_time_to_compare text;
the_minute_of_time_to_compare text;
hours_ago integer;
minutes_ago integer;
BEGIN
--Set the current time variable to now
right_now := now();
--Get the pices of the dates out for comparison
the_current_day := extract(day from right_now);
the_current_month := extract(month from right_now);
the_current_year := extract(year from right_now);
time_to_compare_day := extract(day from time_to_compare);
time_to_compare_month := extract(month from time_to_compare);
time_to_compare_year := extract(year from time_to_compare);
the_hour_of_time_to_compare := extract(hour from time_to_compare);
the_minute_of_time_to_compare := extract(minute from time_to_compare);
IF the_current_day = time_to_compare_day AND the_current_month = time_to_compare_month AND the_current_year = time_to_compare_year THEN
the_day := ''today'';
END IF;
IF the_current_day - 1 = time_to_compare_day AND the_current_month = time_to_compare_month AND the_current_year = time_to_compare_year THEN
the_day := ''yesterday'';
END IF;
--Find the time difference as an interval
the_interval_difference := age(right_now, time_to_compare);
minutes_ago := extract(minutes from the_interval_difference);
hours_ago := extract(hours from the_interval_difference);
IF interval ''12 hours'' >= the_interval_difference AND hours_ago = 0 THEN
time_difference := minutes_ago || '' minutes ago'';
return time_difference;
END IF;
IF interval ''12 hours'' >= the_interval_difference AND hours_ago = 1 THEN
time_difference := hours_ago || '' hour '' || minutes_ago || '' minutes ago'';
return time_difference;
END IF;
IF interval ''12 hours'' >= the_interval_difference THEN
time_difference := hours_ago || '' hours '' || minutes_ago || '' minutes ago'';
return time_difference;
END IF;
IF length(the_hour_of_time_to_compare) < 2 THEN
the_hour_of_time_to_compare = ''0'' || the_hour_of_time_to_compare;
END IF;
IF length(the_minute_of_time_to_compare) < 2 THEN
the_minute_of_time_to_compare = ''0'' || the_minute_of_time_to_compare;
END IF;
IF the_day = ''yesterday'' OR the_day = ''today'' THEN
time_difference := ''at '' || the_hour_of_time_to_compare || '':'' || the_minute_of_time_to_compare || '' '' || the_day;
return time_difference;
END IF;
return to_char(time_to_compare,''DD Mon YYYY HH24:MI'');
END;
' LANGUAGE 'plpgsql';
/*
**********************************
CREATE COMMUNITY TABLES
**********************************
*/
/*** SEQUENCES ***/
/* Sequence for generating unique user_ids. */
CREATE SEQUENCE user_id_sequence START 2; -- 1 is reserved for anonymous user
/* Sequence for generating unique group_ids. */
CREATE SEQUENCE group_id_sequence START 1;
/*** TABLES ***/
/* ISO country codes - provides a mapping of country codes to country
names */
CREATE TABLE iso_country_codes (
country_name text UNIQUE NOT NULL,
country_code char(2) PRIMARY KEY
);
/* Load ISO country codes into iso_country_codes from a file. */
COPY iso_country_codes FROM '/var/www/lib/jgrall.com/datafiles/countries.txt' USING DELIMITERS ';';
/* Stores user information such as name, email address, username, and password. */
CREATE TABLE users(
/*** REQUIRED INFORMATION COLLECTED AT REGISTRATION OR COMPUTED BY THE SYSTEM ***/
/* The lengths of all inputs should be limited by the front
end (via HTML form parameters, PHP, or both.) */
/* Primary key. */
user_id integer PRIMARY KEY,
/* Unique identifier. Users are free to list an email address as
their username, but they don't have to. */
username text NOT NULL UNIQUE,
/* We encrypt passwords using PHP's crypt function. */
password text NOT NULL,
/* Generous length to allow for large addresses. Possible
validity according to the RFC standard is judged through
regexps in PHP, and confirmed by account activation
procedures. Email address is forced unique. */
email text NOT NULL UNIQUE,
first_names text NOT NULL,
last_name text NOT NULL,
registration_date timestamp NOT NULL,
/* Whether or not this account is currently active. This is
expected to be false in three main situations: (1) A user has
registered online but not yet visited the account activation
link mailed in response to registration. (2) The user has
been banned. (3) The user professes to have lost his/her password. In this
case the account is deactivated until they reactivate it by clicking on an
link sent via email. */
active_p char(1) NOT NULL DEFAULT 'f' CHECK (active_p IN ('t','f')),
/* For statistical purposes. */
last_login timestamp NOT NULL,
/* Name of the remote host from which the user last logged in,
if available from PHP. */
last_host text,
/* Allow for future geospatial specialisation. */
country_code char(2) NOT NULL REFERENCES iso_country_codes (country_code),
postal_code text NOT NULL,
/* OPTIONAL INFORMATION COLLECTED VIA PERSONAL PROFILE PAGE */
telephone text,
/* Allow for obscenely long personal URLs. */
url text,
company text,
job_title text,
/* Free-form text or HTML description, biography, etc. */
personal_statement text,
/* Picture, corporate logo, whatever they'd like on their profile page. */
personal_image bytea
);
/* Maps names of groups to unique group identifiers. */
CREATE TABLE groups(
group_id integer PRIMARY KEY,
group_name text UNIQUE NOT NULL
);
/* Maps user ids to the ids of the groups they belong to. */
CREATE TABLE user_group_map(
group_id integer NOT NULL REFERENCES groups,
user_id integer NOT NULL REFERENCES users,
/* A user can't be in a group twice.*/
constraint user_group_pairings_unique UNIQUE(user_id, group_id)
);
/* Stores a mapping between user_ids and session_ids to be used in
account activation procedures when a new user registers or an existing
user loses his/her password. Entries are removed from this table upon
account (re)activation. */
CREATE TABLE user_session_map(
user_id integer NOT NULL references users,
session_id text NOT NULL UNIQUE
--No unique constraint wanted - we want people to be able to request password mailings multiple times - even within the same session
--We will remove all rows corresponding to them in the table when they have actually gone and changed their password.
);
/*** INDICES (Explicitly created). ***/
CREATE INDEX users_email_index ON users(email);
CREATE INDEX users_username_index ON users(username);
CREATE INDEX user_session_map_user_id_index ON user_session_map(user_id);
CREATE INDEX user_session_map_session_id_index ON user_session_map(session_id);
/*
**********************************
CREATE CONTENT TABLES
**********************************
*/
/*** SEQUENCES ***/
CREATE SEQUENCE timeline_sequence START 2; -- 1 reserved for top-level timeline
CREATE SEQUENCE content_sequence START 1;
CREATE SEQUENCE category_sequence START 9;
/*** TABLES ***/
/* Table of topics content can be tagged with. */
CREATE TABLE timelines (
timeline_id integer PRIMARY KEY,
timeline_name text NOT NULL,
timeline_sequence_names text NOT NULL, -- e.g. 20th Century History:Science & Technology:Computer Chips
timeline_sequence text NOT NULL, -- e.g. 1:3:8 sequence of timeline_ids
parent_timeline integer REFERENCES timelines(timeline_id)
);
CREATE INDEX timelines_sequence_index ON timelines(timeline_sequence);
CREATE INDEX timelines_sequence_names_index ON timelines(timeline_sequence_names);
CREATE INDEX timelines_sequence_parent_timeline_index ON timelines(parent_timeline);
/*** MASTER CONTENT TABLE ***/
/* Content attributes not allowed to change from version to version. */
CREATE TABLE content_master (
content_id integer PRIMARY KEY,
/* Discretise the possibilities with a check in statement,
providing some resistance to dumb errors at the cost of having
to manually change table constraints if a new content_type is
needed. Presumably this won't happen very often.
For now we have
o event: a TimeWeaver historical event (text & associated dates & authors)
o image: JPEG, GIF, or PNG */
content_type text NOT NULL CHECK (content_type IN ('event', 'image')),
/* The content ID of the piece of content to which this is
refers (if any). For articles, news, static, image, and threads,
this will be NULL, since none may be a explicit reference to any
other particular piece of content. Comments will use the
content ID of the content to which they respond.
*/
refers_to integer REFERENCES content_master(content_id)
);
/* Content attributes allowed to change from version to version. */
CREATE TABLE content_versions (
content_id integer NOT NULL REFERENCES content_master(content_id),
/* Should monotonically increase. */
version_number integer NOT NULL,
/* The contributor. This architecture allows only one author
of any given piece of content to be displayed. This author
will always be that user who submitted the most recent version
of the content.
The client expressly requests a special "rumor mill" section
allowing anonymous posters. Solution is to define an
Anonymous user to whom all content in the anonymous forum
belongs. */
author integer NOT NULL REFERENCES users(user_id),
/* The hostname of the remote machine from which this version
of this piece of content was submitted (if available from
PHP). */
submitted_from_host text,
/* Timestamp from when this version was submitted. */
creation_date timestamp NOT NULL,
start_year integer NOT NULL,
end_year integer,
/* Start date - FOR EVENTS*/
start_date timestamp,
/* End date - FOR EVENTS*/
end_date timestamp,
/* Title for the content. */
title text NOT NULL,
/* A summary, title, caption, or other metadescription,
required of all content (even images). */
one_line_summary text NOT NULL,
/* Body of the content. */
body bytea NOT NULL,
/* The client has expressed a desire for user-submitted
comments to be immediately visible UNLESS posted to the
anonymous forum, so there may be no sensible default. NULL
must be treated as web-invisible. parent_offline means that
this child is hidden because its parent (as specified by
refers_to was taken offline). */
editorial_status text CHECK (editorial_status IN
('submitted','rejected','approved','parent_offline')),
/* Mime-type of the content, e.g. text/html, text/xml, image/jpeg. */
mime_type text NOT NULL,
/* Is this restricted-access content, viewable only by
administrators and full subscribers? 't' means restricted. */
restricted_p char(1) NOT NULL DEFAULT 'f' CHECK (restricted_p in ('t','f')),
/* Flag to say whether this is the most current version of a
particular content_id. This is a planned denormalisation for
efficiency purposes. */
current_version_p char(1) NOT NULL CHECK(current_version_p IN ('t','f')),
/*We'll collect credited_sources for images & force it in the scripts.*/
credited_source text,
/* Primary key for this table is a combination of content_id
and version_number. */
PRIMARY KEY (content_id,version_number)
);
CREATE TABLE category_map(
category_id integer PRIMARY KEY,
category_name text
);
CREATE TABLE content_category_map(
content_id integer REFERENCES content_master(content_id),
category_id integer REFERENCES category_map(category_id),
unique(content_id,category_id)
);
/* maps events to timelines. */
CREATE TABLE event_timelines_map(
content_id integer PRIMARY KEY REFERENCES content_master(content_id),
timeline_id integer REFERENCES timelines(timeline_id),
unique(content_id,timeline_id) --no double mappings
);
CREATE INDEX event_timelines_map_timelines_id_index ON event_timelines_map(timeline_id);
CREATE INDEX content_category_map_content_id_index ON content_category_map(content_id);
CREATE INDEX content_category_map_category_id_index ON content_category_map(category_id);
/*** GENERAL TRIGGERS ***/
/* On addition of a piece of content to content_versions, change
current_version_p to false for all other versions of this piece of
content. */
CREATE OR REPLACE FUNCTION instantiate_new_current_version_p() RETURNS TRIGGER AS '
DECLARE
BEGIN
UPDATE content_versions SET current_version_p =''f''
WHERE content_id = NEW.content_id
AND version_number != NEW.version_number;
UPDATE content_versions SET current_version_p=''t''
WHERE content_id =NEW.content_id
AND version_number = NEW.version_number;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER instantiate_new_current_version_p_trigger AFTER INSERT ON content_versions
FOR EACH ROW EXECUTE PROCEDURE instantiate_new_current_version_p();
/* If a version of a piece of content is deleted (i.e. it is reverted
to a previous version), find its previous version and assign its
current_version_p to true. */
CREATE OR REPLACE FUNCTION revert_current_version_p() RETURNS TRIGGER AS '
DECLARE
BEGIN
UPDATE content_versions SET current_version_p=''t''
WHERE content_id = OLD.content_id
AND version_number = OLD.version_number - 1;
RETURN OLD;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER revert_current_version_p_trigger AFTER DELETE ON content_versions
FOR EACH ROW EXECUTE PROCEDURE revert_current_version_p();
/* Maintain consistency in the editorial status field among a piece of
content and all those pieces of content referring to it. If content
ID 14 expires, and 21 and 22 refer to it, they should be expired too
and not show up on the site. If content 16 is removed (that is,
soft-deleted--editorial_status set to 'rejected') then from the
perspective of site visibility, so should be all content referring to
it, recursively.
Thus the logic for deactivation is as follows: if editorial_status was
just set to rejected, expired, or parent_offline, set the
editorial_status of all currently APPROVED children to parent_offline.
For reactivation: if editorial_status is set to APPROVED (from any other state),
set all children with status PARENT_OFFLINE and set them to APPROVED.
*/
CREATE OR REPLACE FUNCTION maintain_editorial_status_consistency() RETURNS TRIGGER AS '
DECLARE
BEGIN
IF NEW.editorial_status=''rejected'' OR NEW.editorial_status=''parent_offline'' THEN
UPDATE content_versions SET editorial_status=''parent_offline''
WHERE content_master.refers_to=NEW.content_id
AND content_master.content_id = content_versions.content_id
AND editorial_status != ''parent_offline'';
ELSIF NEW.editorial_status=''approved'' THEN
UPDATE content_versions SET editorial_status=''approved''
WHERE content_master.refers_to=NEW.content_id
AND content_master.content_id = content_versions.content_id
AND editorial_status=''parent_offline'';
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER maintain_editorial_status_consistency_trigger AFTER UPDATE ON content_versions
FOR EACH ROW EXECUTE PROCEDURE maintain_editorial_status_consistency();
/*** TOP-LEVEL TIMELINE ***/
/* Create the special topic for the rumor mill. */
INSERT INTO timelines(timeline_id, timeline_name, timeline_sequence, timeline_sequence_names, parent_timeline)
VALUES (1,'20th Century History','1','20th Century History',NULL);
/*** INDICES ***/
/* Expanding the content table into threaded discussion forums
requires a lot of searching. Define some indices to make this search
reasonable. */
CREATE INDEX content_master_refers_to_index ON content_master(refers_to);
CREATE INDEX content_versions_current_version_p_index ON content_versions(current_version_p);
/*** VIEWS ***/
/* All content. Holy cow! */
CREATE VIEW content_all
AS
SELECT content_master.content_id, content_type, refers_to, start_date, end_date,
version_number, author, creation_date, title, one_line_summary, body, editorial_status,
mime_type, restricted_p, current_version_p, credited_source, start_year, end_year
FROM content_master, content_versions
WHERE content_master.content_id=content_versions.content_id;
/* The newest version of every piece of content. */
CREATE VIEW content_current
AS
SELECT content_master.content_id, content_type, refers_to, start_date, end_date,
version_number, author, creation_date, title, one_line_summary, body, editorial_status,
mime_type, restricted_p, current_version_p, credited_source, start_year, end_year
FROM content_master, content_versions
WHERE content_master.content_id=content_versions.content_id
AND content_versions.current_version_p = 't';
/* Content which is fine to show on the web to anyone. */
CREATE VIEW content_unrestricted_viewable
AS
SELECT * FROM content_current
WHERE editorial_status='approved'
AND (restricted_p = 'f');
/* Stuff it's fine to show to administrators. */
CREATE VIEW content_restricted_viewable
AS
SELECT * FROM content_current
WHERE editorial_status='approved';
/* Content awaiting approval by an editor/administrator. */
CREATE VIEW content_awaiting_approval
AS
SELECT * FROM content_current
WHERE editorial_status='submitted';
/*
**********************************
INSERT INITIAL DATA
**********************************
*/
/*** INITIAL SETUP ***/
/* Add some groups. */
INSERT INTO groups (group_id, group_name) VALUES (nextval('group_id_sequence'),'administrators');
INSERT INTO groups (group_id, group_name) VALUES (nextval('group_id_sequence'),'members');
/* Add some users. */
INSERT INTO users (user_id, username, password, email, first_names, last_name, registration_date,
last_login, country_code, postal_code)
VALUES
(1,'anonymous','$1$XeGAhRhw$vDtVikuvpawmWASHlDaQL1','anonymous@jgrall.com','Anonymous',
'User', now(), now(), 'US', '00000');
INSERT INTO users (user_id, username, password, email, first_names, last_name, registration_date,
last_login, country_code, postal_code)
VALUES
(nextval('user_id_sequence'),'nhunter','$1$XeGAhRhw$vDtVikuvpawmWASHlDaQL1','nhunter@mit.edu','Nick',
'Hunter', now(), now(), 'US', '02139');
INSERT INTO users (user_id, username, password, email, first_names, last_name, registration_date,
last_login, country_code, postal_code)
VALUES
(nextval('user_id_sequence'),'grall','$1$XeGAhRhw$vDtVikuvpawmWASHlDaQL1','grall@mit.edu','Jonathan',
'Grall', now(), now(), 'US', '02109');
INSERT INTO users (user_id, username, password, email, first_names, last_name, registration_date,
last_login, country_code, postal_code)
VALUES
(nextval('user_id_sequence'),'fendt','$1$XeGAhRhw$vDtVikuvpawmWASHlDaQL1','fendt@mit.edu','Kurt',
'Fendt', now(), now(), 'US', '02139');
/*
Passwords are hashed using one-way algorithm
*/
/* Initial administrators Nick Hunter, Kurt Fendt, Jonathan Grall*/
INSERT INTO user_group_map (user_id, group_id) VALUES (1,2);
INSERT INTO user_group_map (user_id, group_id) VALUES (2,1);
INSERT INTO user_group_map (user_id, group_id) VALUES (3,1);
INSERT INTO user_group_map (user_id, group_id) VALUES (4,1);
/*Currently supported categories*/
INSERT INTO category_map (category_id,category_name) VALUES (1,'Important People');
INSERT INTO category_map (category_id,category_name) VALUES (2,'Technological Breakthroughs');
INSERT INTO category_map (category_id,category_name) VALUES (3,'Company Stories');
INSERT INTO category_map (category_id,category_name) VALUES (4,'Disasters');
INSERT INTO category_map (category_id,category_name) VALUES (5,'Well Known Products/Creations');
INSERT INTO category_map (category_id,category_name) VALUES (6,'Political Events');
INSERT INTO category_map (category_id,category_name) VALUES (7,'Conflicts');
INSERT INTO category_map (category_id,category_name) VALUES (8,'Turning Points');
/* Activate a few users for development purposes. */
UPDATE users SET active_p='t' WHERE username='nhunter'
OR username='grall'
OR username='fendt'
OR username='anonymous';
|
