diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/alert_types.pl | 8 | ||||
| -rw-r--r-- | db/alert_types.sql | 2 | ||||
| -rwxr-xr-x | db/rerun_dbic_loader.pl | 38 | ||||
| -rw-r--r-- | db/schema.sql | 38 | ||||
| -rw-r--r-- | db/schema_0001-add_sessions_and_users_and_dbic_fields.sql | 57 | ||||
| -rw-r--r-- | db/schema_0002-create_users_from_problems_and_link.sql | 53 | ||||
| -rw-r--r-- | db/schema_0003-create_users_from_alerts_and_link.sql | 29 | ||||
| -rw-r--r-- | db/schema_0004-create_users_from_comments_and_link.sql | 49 |
8 files changed, 260 insertions, 14 deletions
diff --git a/db/alert_types.pl b/db/alert_types.pl index fd1243982..3d1d131dc 100644 --- a/db/alert_types.pl +++ b/db/alert_types.pl @@ -27,7 +27,7 @@ _('The latest local problems reported by users'), # New problems around a postcode with a particular state - _('New problems NEAR {{POSTCODE}} on FixMyStreet'), + _('New problems near {{POSTCODE}} on FixMyStreet'), _('The latest local problems reported by users'), # New problems sent to a particular council @@ -35,10 +35,10 @@ _('The latest problems for {{COUNCIL}} reported by users'), # New problems within a particular ward sent to a particular council - _('New problems for {{COUNCIL}} within {{WARD}} ward on FixMyStreet' + _('New problems for {{COUNCIL}} within {{WARD}} ward on FixMyStreet'), _('The latest problems for {{COUNCIL}} within {{WARD}} ward reported by users'), # New problems within a particular voting area (ward, constituency, whatever) - _('New problems within {{NAME}}''s boundary on FixMyStreet'), - _('The latest problems within {{NAME}}''s boundary reported by users'), + _('New problems within {{NAME}}\'s boundary on FixMyStreet'), + _('The latest problems within {{NAME}}\'s boundary reported by users'), diff --git a/db/alert_types.sql b/db/alert_types.sql index fb1aac1d0..eca83bb6f 100644 --- a/db/alert_types.sql +++ b/db/alert_types.sql @@ -71,7 +71,7 @@ insert into alert_type item_table, item_where, item_order, item_title, item_link, item_description, template) values ('postcode_local_problems_state', '', '', - 'New problems NEAR {{POSTCODE}} on FixMyStreet', '/', 'The latest local problems reported by users', + 'New problems near {{POSTCODE}} on FixMyStreet', '/', 'The latest local problems reported by users', 'problem_find_nearby(?, ?, ?) as nearby,problem', 'nearby.problem_id = problem.id and problem.state in (?)', 'created desc', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby'); diff --git a/db/rerun_dbic_loader.pl b/db/rerun_dbic_loader.pl new file mode 100755 index 000000000..152d319b1 --- /dev/null +++ b/db/rerun_dbic_loader.pl @@ -0,0 +1,38 @@ +#!/usr/bin/env perl + +use strict; +use warnings; + +# This script inspects the current state of the database and then amends the +# FixMyStreet::DB::Result::* files to suit. After running the changes should be +# inspected before the code is commited. + +use FixMyStreet; +use DBIx::Class::Schema::Loader qw/ make_schema_at /; + +# create a exclude statement that filters out the table that we are not +# interested in +my @tables_to_ignore = ( + 'debugdate', # + 'flickr_imported', # + 'partial_user', # + 'textmystreet', # +); +my $exclude = '^(?:' . join( '|', @tables_to_ignore ) . ')$'; + +make_schema_at( + 'FixMyStreet::DB', + { + debug => 0, # switch on to be chatty + dump_directory => './perllib', # edit files in place + exclude => qr{$exclude}, # ignore some tables + generate_pod => 0, # no need for pod + overwrite_modifications => 1, # don't worry that the md5 is wrong + + # add in some extra components + components => [ 'FilterColumn', 'InflateColumn::DateTime', 'EncodedColumn' ], + + }, + FixMyStreet->dbic_connect_info(), +); + diff --git a/db/schema.sql b/db/schema.sql index c58abaf48..9c5b3d8fd 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -66,6 +66,7 @@ create function ms_current_timestamp() -- Who to send problems for a specific MaPit area ID to create table contacts ( + id serial primary key, area_id integer not null, category text not null default 'Other', email text not null, @@ -86,6 +87,7 @@ create unique index contacts_area_id_category_idx on contacts(area_id, category) create table contacts_history ( contacts_history_id serial not null primary key, + contact_id integer not null, area_id integer not null, category text not null default 'Other', email text not null, @@ -105,7 +107,7 @@ create table contacts_history ( create function contacts_updated() returns trigger as ' begin - insert into contacts_history (area_id, category, email, editor, whenedited, note, confirmed, deleted) values (new.area_id, new.category, new.email, new.editor, new.whenedited, new.note, new.confirmed, new.deleted); + insert into contacts_history (contact_id, area_id, category, email, editor, whenedited, note, confirmed, deleted) values (new.id, new.area_id, new.category, new.email, new.editor, new.whenedited, new.note, new.confirmed, new.deleted); return new; end; ' language 'plpgsql'; @@ -115,6 +117,22 @@ create trigger contacts_update_trigger after update on contacts create trigger contacts_insert_trigger after insert on contacts for each row execute procedure contacts_updated(); +-- table for sessions - needed by Catalyst::Plugin::Session::Store::DBIC +create table sessions ( + id char(72) primary key, + session_data text, + expires integer +); + +-- users table +create table users ( + id serial not null primary key, + email text not null unique, + name text, + phone text, + password text not null default '' +); + -- Problems reported by users of site create table problem ( id serial not null primary key, @@ -132,9 +150,8 @@ create table problem ( used_map boolean not null, -- User's details + user_id int references users(id) not null, name text not null, - email text not null, - phone text not null, anonymous boolean not null, -- External information @@ -161,6 +178,7 @@ create table problem ( send_questionnaire boolean not null default 't' ); create index problem_state_latitude_longitude_idx on problem(state, latitude, longitude); +create index problem_user_id_idx on problem ( user_id ); create table questionnaire ( id serial not null primary key, @@ -244,8 +262,9 @@ create function problem_find_nearby(double precision, double precision, double p create table comment ( id serial not null primary key, problem_id integer not null references problem(id), + user_id int references users(id) not null, + anonymous bool not null, name text, -- null means anonymous - email text not null, website text, created timestamp not null default ms_current_timestamp(), confirmed timestamp, @@ -265,6 +284,7 @@ create table comment ( -- and should be highlighted in the display? ); +create index comment_user_id_idx on comment(user_id); create index comment_problem_id_idx on comment(problem_id); create index comment_problem_id_created_idx on comment(problem_id, created); @@ -300,7 +320,7 @@ create table alert ( alert_type text not null references alert_type(ref), parameter text, -- e.g. Problem ID for new updates, Longitude for local problem alerts parameter2 text, -- e.g. Latitude for local problem alerts - email text not null, + user_id int references users(id) not null, confirmed integer not null default 0, lang text not null default 'en-gb', cobrand text not null default '' check (cobrand ~* '^[a-z0-9]*$'), @@ -308,11 +328,11 @@ create table alert ( whensubscribed timestamp not null default ms_current_timestamp(), whendisabled timestamp default null ); -create index alert_email_idx on alert(email); +create index alert_user_id_idx on alert ( user_id ); create index alert_alert_type_confirmed_whendisabled_idx on alert(alert_type, confirmed, whendisabled); create index alert_whendisabled_cobrand_idx on alert(whendisabled, cobrand); create index alert_whensubscribed_confirmed_cobrand_idx on alert(whensubscribed, confirmed, cobrand); --- Possible indexes - unique (alert_type,email,parameter) +-- Possible indexes - unique (alert_type,user_id,parameter) create table alert_sent ( alert_id integer not null references alert(id), @@ -340,9 +360,8 @@ create table flickr_imported ( create unique index flickr_imported_id_idx on flickr_imported(id); create table abuse ( - email text not null + email text primary key check( lower(email) = email ) ); -create unique index abuse_email_idx on abuse(lower(email)); create table textmystreet ( name text not null, @@ -367,3 +386,4 @@ create table admin_log ( or action = 'resend'), whenedited timestamp not null default ms_current_timestamp() ); + diff --git a/db/schema_0001-add_sessions_and_users_and_dbic_fields.sql b/db/schema_0001-add_sessions_and_users_and_dbic_fields.sql new file mode 100644 index 000000000..b22ab7dfe --- /dev/null +++ b/db/schema_0001-add_sessions_and_users_and_dbic_fields.sql @@ -0,0 +1,57 @@ +-- These are changes needed to the schema to support moving over to DBIx::Class + +begin; + +-- table for sessions - needed by Catalyst::Plugin::Session::Store::DBIC +CREATE TABLE sessions ( + id CHAR(72) PRIMARY KEY, + session_data TEXT, + expires INTEGER +); + +-- users table +create table users ( + id serial not null primary key, + email text not null unique, + name text, + phone text, + password text not null default '' +); + +--- add PK to contacts table +ALTER TABLE contacts + ADD COLUMN id SERIAL PRIMARY KEY; + +AlTER TABLE contacts_history + ADD COLUMN contact_id integer; + +update contacts_history + set contact_id = ( + select id + from contacts + where contacts_history.category = contacts.category + and contacts_history.area_id = contacts.area_id + ); + +-- Note any categories that have been deleted will cause the following +-- line to fail, and they'll need to be deleted manually first. + +AlTER TABLE contacts_history + alter COLUMN contact_id SET NOT NULL; + +create or replace function contacts_updated() + returns trigger as ' + begin + insert into contacts_history (contact_id, area_id, category, email, editor, whenedited, note, confirmed, deleted) values (new.id, new.area_id, new.category, new.email, new.editor, new.whenedited, new.note, new.confirmed, new.deleted); + return new; + end; +' language 'plpgsql'; + + +--- add pk and lowercase check to abuse +drop index abuse_email_idx; +update abuse set email = lower(email); +alter table abuse add check( lower(email) = email ); +alter table abuse add primary key(email); + +commit; diff --git a/db/schema_0002-create_users_from_problems_and_link.sql b/db/schema_0002-create_users_from_problems_and_link.sql new file mode 100644 index 000000000..bfb502a96 --- /dev/null +++ b/db/schema_0002-create_users_from_problems_and_link.sql @@ -0,0 +1,53 @@ +-- link the problems to the users, creating the users as required. Removes the +-- email field from the problems. +-- +-- Uses the most recently used non-anonymmous name as the name for the user. + +begin; + +-- create users from the problems +INSERT INTO users (email) + SELECT distinct( lower( email ) ) FROM problem; + +-- add a user id to the problems +ALTER TABLE problem + ADD COLUMN user_id INT REFERENCES users(id); + +-- populate the user_ids +update problem + set user_id = ( + select id + from users + where users.email = lower( problem.email ) + ); + +-- create the index now that the entries have been made +create index problem_user_id_idx on problem ( user_id ); + +-- add names from the problems +UPDATE users + SET name = ( + select name from problem + where user_id = users.id + order by created desc + limit 1 + ), + phone = ( + select phone from problem + where user_id = users.id + order by created desc + limit 1 + ); + + +-- make the problems user id not null etc +ALTER TABLE problem + ALTER COLUMN user_id SET NOT NULL; + +-- drop emails from the problems +ALTER TABLE problem + DROP COLUMN email; +ALTER TABLE problem + DROP COLUMN phone; + +commit; diff --git a/db/schema_0003-create_users_from_alerts_and_link.sql b/db/schema_0003-create_users_from_alerts_and_link.sql new file mode 100644 index 000000000..6861e5340 --- /dev/null +++ b/db/schema_0003-create_users_from_alerts_and_link.sql @@ -0,0 +1,29 @@ +begin; + +-- create any users that don't already exist +INSERT INTO users (email) + SELECT distinct( lower( alert.email ) ) FROM alert + LEFT JOIN users ON lower( users.email ) = lower( alert.email ) + WHERE users.id IS NULL; + +ALTER table alert + ADD COLUMN user_id INT REFERENCES users(id); + +-- populate the user ids in the alert table +UPDATE alert + SET user_id = ( + SELECT id + FROM users + WHERE users.email = lower( alert.email ) + ); + +CREATE INDEX alert_user_id_idx on alert ( user_id ); + +-- tidy up now everythings in place +ALTER table alert + ALTER COLUMN user_id SET NOT NULL; + +ALTER table alert + DROP COLUMN email; + +commit; diff --git a/db/schema_0004-create_users_from_comments_and_link.sql b/db/schema_0004-create_users_from_comments_and_link.sql new file mode 100644 index 000000000..6343aec58 --- /dev/null +++ b/db/schema_0004-create_users_from_comments_and_link.sql @@ -0,0 +1,49 @@ +begin; + +-- create any users that don't already exist +INSERT INTO users (email) + SELECT distinct( lower( comment.email ) ) FROM comment + LEFT JOIN users ON lower( users.email ) = lower( comment.email ) + WHERE users.id IS NULL; + +ALTER table comment + ADD COLUMN user_id INT REFERENCES users(id); + +-- populate the user ids in the comment table +UPDATE comment + SET user_id = ( + SELECT id + FROM users + WHERE users.email = lower( comment.email ) + ); + +CREATE INDEX comment_user_id_idx on comment ( user_id ); + +UPDATE users + SET name = ( + select name from comment + where user_id = users.id + order by created desc + limit 1 + ) +WHERE users.name IS NULL; + +-- set up the anonymous flag +ALTER table comment + ADD COLUMN anonymous BOOL; + +UPDATE comment SET anonymous = false WHERE name <> ''; + +UPDATE comment SET anonymous = true WHERE anonymous is NULL; + +-- tidy up now everythings in place +ALTER table comment + ALTER COLUMN user_id SET NOT NULL; + +ALTER table comment + ALTER COLUMN anonymous SET NOT NULL; + +ALTER table comment + DROP COLUMN email; + +commit; |