diff options
Diffstat (limited to 'db')
| -rwxr-xr-x | db/rerun_dbic_loader.pl | 46 | ||||
| -rw-r--r-- | db/schema.sql | 23 | ||||
| -rw-r--r-- | db/schema_0001-add_sessions_and_users_and_dbic_fields.sql | 39 | ||||
| -rw-r--r-- | db/schema_0002-create_users_from_problems_and_link.sql | 53 |
4 files changed, 159 insertions, 2 deletions
diff --git a/db/rerun_dbic_loader.pl b/db/rerun_dbic_loader.pl new file mode 100755 index 000000000..13316ad09 --- /dev/null +++ b/db/rerun_dbic_loader.pl @@ -0,0 +1,46 @@ +#!/usr/bin/env perl + +use strict; +use warnings; + +# This script inspects the current state of the database and then ammends 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 = ( + 'admin_log', # + 'alert', # + 'alert_sent', # + 'alert_type', # + 'comment', # + 'contacts_history', # + 'debugdate', # + 'flickr_imported', # + 'partial_user', # + 'questionnaire', # + 'secret', # + 'textmystreet', # +); +my $exclude = '^(?:' . join( '|', @tables_to_ignore ) . ')$'; + +make_schema_at( + 'FixMyStreet::DB', + { + debug => 0, # switch on to be chatty + dump_directory => './lib', # 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'], + + }, + FixMyStreet->dbic_connect_info(), +); + diff --git a/db/schema.sql b/db/schema.sql index c58abaf48..a44b91ca7 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, @@ -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, @@ -367,3 +385,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..aadaa0993 --- /dev/null +++ b/db/schema_0001-add_sessions_and_users_and_dbic_fields.sql @@ -0,0 +1,39 @@ +-- 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 + ); + +AlTER TABLE contacts_history + alter COLUMN contact_id SET NOT NULL; + +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; |