diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/alert_types.sql | 24 | ||||
| -rw-r--r-- | db/schema.sql | 41 |
2 files changed, 62 insertions, 3 deletions
diff --git a/db/alert_types.sql b/db/alert_types.sql new file mode 100644 index 000000000..33a2ae093 --- /dev/null +++ b/db/alert_types.sql @@ -0,0 +1,24 @@ +insert into alert_type +(ref, head_sql_query, head_table, head_title, head_link, head_description, + item_table, item_where, item_order, item_title, item_link, item_description, template) +values ('new_updates', 'select * from problem where id=?', 'problem', + 'Updates on {{title}}', '/?id={{id}}', 'Updates on {{title}}', + 'comment', 'comment.state=\'confirmed\'', 'created desc', + 'Update by {{name}}', '/?id={{problem_id}}#comment_{{id}}', '{{text}}', 'alert-update'); + +insert into alert_type +(ref, head_sql_query, head_table, head_title, head_link, head_description, + item_table, item_where, item_order, item_title, item_link, item_description, template) +values ('new_problems', '', '', + 'New problems on Neighbourhood Fix-It', '/', 'The latest problems reported by users', + 'problem', 'problem.state in (\'confirmed\', \'fixed\')', 'created desc', + '{{title}}', '/?id={{id}}', '{{detail}}', 'alert-problem'); + +insert into alert_type +(ref, head_sql_query, head_table, head_title, head_link, head_description, + item_table, item_where, item_order, item_title, item_link, item_description, template) +values ('local_problems', '', '', + 'New local problems on Neighbourhood Fix-It', '/', 'The latest local problems reported by users', + 'problem_find_nearby(?, ?, 10) as nearby,problem', 'nearby.problem_id = problem.id and problem.state in (\'confirmed\', \'fixed\')', 'created desc', + '{{title}}', '/?id={{id}}', '{{detail}}', 'alert-problem'); + diff --git a/db/schema.sql b/db/schema.sql index 408ed0da9..8ecaddacd 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.15 2007-01-24 20:55:30 matthew Exp $ +-- $Id: schema.sql,v 1.16 2007-01-26 01:01:23 matthew Exp $ -- -- secret @@ -202,7 +202,7 @@ create table comment ( name text not null, email text not null, website text, - whenposted timestamp not null default ms_current_timestamp(), + created timestamp not null default ms_current_timestamp(), text text not null, -- as entered by comment author state text not null check ( state = 'unconfirmed' @@ -216,7 +216,7 @@ create table comment ( ); create index comment_problem_id_idx on comment(problem_id); -create index comment_problem_id_whenposted_idx on comment(problem_id, whenposted); +create index comment_problem_id_created_idx on comment(problem_id, created); -- Tokens for confirmations create table token ( @@ -227,3 +227,38 @@ create table token ( primary key (scope, token) ); +-- Alerts + +create table alert_type ( + ref text not null primary key, + head_sql_query text not null, + head_table text not null, + head_title text not null, + head_link text not null, + head_description text not null, + item_table text not null, + item_where text not null, + item_order text not null, + item_title text not null, + item_link text not null, + item_description text not null +); + +create table alert ( + id serial not null primary key, + alert_type text not null references alert_type(ref), + parameter text, -- e.g. Problem ID for new updates + parameter2 text, -- e.g. Latitude for local problem alerts + email text not null, + confirmed integer not null default 0, + whensubscribed timestamp not null default ms_current_timestamp(), + whendisabled timestamp default null +); +-- Possible indexes - email, alert_type, whendisabled, unique (alert_type,email,parameter) + +create table alert_sent ( + alert_id integer not null references alert(id), + parameter text, -- e.g. Update ID for new updates + whenqueued timestamp not null default ms_current_timestamp() +); + |