diff options
| -rw-r--r-- | db/schema.sql | 76 |
1 files changed, 57 insertions, 19 deletions
diff --git a/db/schema.sql b/db/schema.sql index 5dff81955..fed680560 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -4,7 +4,7 @@ -- Copyright (c) 2006 UK Citizens Online Democracy. All rights reserved. -- Email: matthew@mysociety.org; WWW: http://www.mysociety.org/ -- --- $Id: schema.sql,v 1.1 2006-09-19 17:31:28 matthew Exp $ +-- $Id: schema.sql,v 1.2 2006-09-20 12:43:58 francis Exp $ -- -- secret @@ -13,6 +13,57 @@ create table secret ( secret text not null ); +-- If a row is present, that is date which is "today". Used for debugging +-- to advance time without having to wait. +create table debugdate ( + override_today date +); + +-- Returns the date of "today", which can be overriden for testing. +create function ms_current_date() + returns date as ' + declare + today date; + begin + today = (select override_today from debugdate); + if today is not null then + return today; + else + return current_date; + end if; + + end; +' language 'plpgsql' stable; + +-- Returns the timestamp of current time, but with possibly overriden "today". +create function ms_current_timestamp() + returns timestamp as ' + declare + today date; + begin + today = (select override_today from debugdate); + if today is not null then + return today + current_time; + else + return current_timestamp; + end if; + end; +' language 'plpgsql'; + + +-- users, but call the table person rather than user so we don't have to quote +-- its name in every statement.... +create table person ( + id serial not null primary key, + name text, + email text not null, + password text, + website text, + numlogins integer not null default 0 +); + +create unique index person_email_idx on person(email); + -- categories in which problems can lie -- create table category ( -- id serial not null primary key, @@ -38,19 +89,6 @@ create table contacts ( email text not null ); --- users, but call the table person rather than user so we don't have to quote --- its name in every statement.... -create table person ( - id serial not null primary key, - name text, - email text not null, - password text, - website text, - numlogins integer not null default 0 -); - -create unique index person_email_idx on person(email); - -- angle_between A1 A2 -- Given two angles A1 and A2 on a circle expressed in radians, return the -- smallest angle between them. @@ -122,14 +160,14 @@ create table message ( problem_id integer not null references problem(id), circumstance text not null, circumstance_count int not null default 0, - whencreated timestamp not null default current_timestamp(), + whencreated timestamp not null default ms_current_timestamp(), fromaddress text not null default 'bci' check (fromaddress in ('bci', 'creator')), -- content of message emailtemplatename text, emailsubject text, - emailbody text, + emailbody text ); create unique index message_problem_id_circumstance_idx on message(problem_id, circumstance, circumstance_count); @@ -143,7 +181,7 @@ create table comment ( name text not null, website text, - whenposted timestamp not null default current_timestamp(), + whenposted timestamp not null default ms_current_timestamp(), text text not null, -- as entered by comment author ishidden boolean not null default false -- hidden from view -- other fields? one to indicate whether this was written by the council @@ -173,7 +211,7 @@ create table alert ( latitude double precision, longitude double precision, - whensubscribed timestamp not null default current_timestamp(), + whensubscribed timestamp not null default ms_current_timestamp(), whendisabled timestamp default null -- set if alert has been turned off ); @@ -191,7 +229,7 @@ create table alert_sent ( -- which comment for event code "/comments" comment_id integer references comment(id), - whenqueued timestamp not null default current_timestamp() + whenqueued timestamp not null default ms_current_timestamp() ); create index alert_sent_alert_id_idx on alert_sent(alert_id); |