diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/downgrade_0051---0050.sql | 36 | ||||
| -rw-r--r-- | db/downgrade_0052---0051.sql | 5 | ||||
| -rw-r--r-- | db/downgrade_0053---0052.sql | 5 | ||||
| -rw-r--r-- | db/downgrade_0054---0053.sql | 39 | ||||
| -rw-r--r-- | db/downgrade_0055---0054.sql | 6 | ||||
| -rw-r--r-- | db/fixture.sql (renamed from db/alert_types.sql) | 52 | ||||
| -rw-r--r-- | db/migrate_from_osgb36_to_wgs84.pl | 201 | ||||
| -rwxr-xr-x | db/rerun_dbic_loader.pl | 11 | ||||
| -rw-r--r-- | db/schema.sql | 79 | ||||
| -rw-r--r-- | db/schema_0051-inactive-contact-state.sql | 45 | ||||
| -rw-r--r-- | db/schema_0052-translation-table.sql | 13 | ||||
| -rw-r--r-- | db/schema_0053-add-report-extra-fields-table.sql | 11 | ||||
| -rw-r--r-- | db/schema_0054-add-state-table.sql | 51 | ||||
| -rw-r--r-- | db/schema_0055-add-default-to-reponsepriority.sql | 5 |
14 files changed, 289 insertions, 270 deletions
diff --git a/db/downgrade_0051---0050.sql b/db/downgrade_0051---0050.sql new file mode 100644 index 000000000..424420e29 --- /dev/null +++ b/db/downgrade_0051---0050.sql @@ -0,0 +1,36 @@ +BEGIN; + +ALTER TABLE contacts ADD confirmed boolean; +ALTER TABLE contacts ADD deleted boolean; + +UPDATE contacts SET confirmed='t', deleted='t' WHERE state = 'deleted'; +UPDATE contacts SET confirmed='f', deleted='t' WHERE state = 'inactive'; +UPDATE contacts SET confirmed='t', deleted='f' WHERE state = 'confirmed'; +UPDATE contacts SET confirmed='f', deleted='f' WHERE state = 'unconfirmed'; + +ALTER TABLE contacts ALTER COLUMN confirmed SET NOT NULL; +ALTER TABLE contacts ALTER COLUMN deleted SET NOT NULL; +ALTER TABLE contacts DROP COLUMN state; + +ALTER TABLE contacts_history ADD confirmed boolean; +ALTER TABLE contacts_history ADD deleted boolean; + +UPDATE contacts_history SET confirmed='t', deleted='t' WHERE state = 'deleted'; +UPDATE contacts_history SET confirmed='f', deleted='t' WHERE state = 'inactive'; +UPDATE contacts_history SET confirmed='t', deleted='f' WHERE state = 'confirmed'; +UPDATE contacts_history SET confirmed='f', deleted='f' WHERE state = 'unconfirmed'; + +ALTER TABLE contacts_history ALTER COLUMN confirmed SET NOT NULL; +ALTER TABLE contacts_history ALTER COLUMN deleted SET NOT NULL; +ALTER TABLE contacts_history DROP COLUMN state; + +CREATE OR REPLACE FUNCTION contacts_updated() + returns trigger as ' + begin + insert into contacts_history (contact_id, body_id, category, email, editor, whenedited, note, confirmed, deleted) values (new.id, new.body_id, new.category, new.email, new.editor, new.whenedited, new.note, new.confirmed, new.deleted); + return new; + end; +' language 'plpgsql'; + +COMMIT; + diff --git a/db/downgrade_0052---0051.sql b/db/downgrade_0052---0051.sql new file mode 100644 index 000000000..715b4549f --- /dev/null +++ b/db/downgrade_0052---0051.sql @@ -0,0 +1,5 @@ +BEGIN; + +DROP TABLE translation; + +COMMIT; diff --git a/db/downgrade_0053---0052.sql b/db/downgrade_0053---0052.sql new file mode 100644 index 000000000..fdad6d0b8 --- /dev/null +++ b/db/downgrade_0053---0052.sql @@ -0,0 +1,5 @@ +BEGIN; + +DROP TABLE report_extra_fields; + +COMMIT; diff --git a/db/downgrade_0054---0053.sql b/db/downgrade_0054---0053.sql new file mode 100644 index 000000000..6fcc41c33 --- /dev/null +++ b/db/downgrade_0054---0053.sql @@ -0,0 +1,39 @@ +BEGIN; + +DROP TABLE state; + +ALTER TABLE problem ADD CONSTRAINT problem_state_check CHECK ( + state = 'unconfirmed' + or state = 'hidden' + or state = 'partial' + or state = 'confirmed' + or state = 'investigating' + or state = 'planned' + or state = 'in progress' + or state = 'action scheduled' + or state = 'fixed' + or state = 'fixed - council' + or state = 'fixed - user' + or state = 'closed' + or state = 'unable to fix' + or state = 'not responsible' + or state = 'duplicate' + or state = 'internal referral' +); +ALTER TABLE comment ADD CONSTRAINT comment_problem_state_check CHECK ( + problem_state = 'confirmed' + or problem_state = 'investigating' + or problem_state = 'planned' + or problem_state = 'in progress' + or problem_state = 'action scheduled' + or problem_state = 'fixed' + or problem_state = 'fixed - council' + or problem_state = 'fixed - user' + or problem_state = 'closed' + or problem_state = 'unable to fix' + or problem_state = 'not responsible' + or problem_state = 'duplicate' + or problem_state = 'internal referral' +); + +COMMIT; diff --git a/db/downgrade_0055---0054.sql b/db/downgrade_0055---0054.sql new file mode 100644 index 000000000..a38d74ded --- /dev/null +++ b/db/downgrade_0055---0054.sql @@ -0,0 +1,6 @@ +BEGIN; + +ALTER TABLE response_priorities DROP COLUMN is_default; + +COMMIT; + diff --git a/db/alert_types.sql b/db/fixture.sql index 471fd905f..840906223 100644 --- a/db/alert_types.sql +++ b/db/fixture.sql @@ -1,3 +1,13 @@ +INSERT INTO state (label, type, name) VALUES ('investigating', 'open', 'Investigating'); +INSERT INTO state (label, type, name) VALUES ('in progress', 'open', 'In progress'); +INSERT INTO state (label, type, name) VALUES ('planned', 'open', 'Planned'); +INSERT INTO state (label, type, name) VALUES ('action scheduled', 'open', 'Action scheduled'); +INSERT INTO state (label, type, name) VALUES ('unable to fix', 'closed', 'No further action'); +INSERT INTO state (label, type, name) VALUES ('not responsible', 'closed', 'Not responsible'); +INSERT INTO state (label, type, name) VALUES ('duplicate', 'closed', 'Duplicate'); +INSERT INTO state (label, type, name) VALUES ('internal referral', 'closed', 'Internal referral'); +INSERT INTO state (label, type, name) VALUES ('fixed', 'fixed', 'Fixed'); + -- New updates on a particular problem report insert into alert_type (ref, head_sql_query, head_table, @@ -18,11 +28,8 @@ insert into alert_type values ('new_problems', '', '', 'New problems on FixMyStreet', '/', 'The latest problems reported by users', 'problem', - 'problem.non_public = ''f'' and problem.state in - (''confirmed'', ''investigating'', ''planned'', ''in progress'', - ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'' - ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'', - ''internal referral'' )', + 'problem.non_public = ''f'' and problem.state NOT IN + (''unconfirmed'', ''hidden'', ''partial'')', 'created desc', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem'); @@ -46,11 +53,8 @@ insert into alert_type values ('local_problems', '', '', 'New local problems on FixMyStreet', '/', 'The latest local problems reported by users', 'problem_find_nearby(?, ?, ?) as nearby,problem', - 'nearby.problem_id = problem.id and problem.non_public = ''f'' and problem.state in - (''confirmed'', ''investigating'', ''planned'', ''in progress'', - ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'', - ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'', - ''internal referral'')', + 'nearby.problem_id = problem.id and problem.non_public = ''f'' and problem.state NOT IN + (''unconfirmed'', ''hidden'', ''partial'')', 'created desc', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby'); @@ -74,11 +78,8 @@ insert into alert_type values ('postcode_local_problems', '', '', '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.non_public = ''f'' and problem.state in - (''confirmed'', ''investigating'', ''planned'', ''in progress'', - ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'', - ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'', - ''internal referral'')', + 'nearby.problem_id = problem.id and problem.non_public = ''f'' and problem.state NOT IN + (''unconfirmed'', ''hidden'', ''partial'')', 'created desc', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby'); @@ -102,11 +103,8 @@ insert into alert_type values ('council_problems', '', '', 'New problems to {{COUNCIL}} on FixMyStreet', '/reports', 'The latest problems for {{COUNCIL}} reported by users', 'problem', - 'problem.non_public = ''f'' and problem.state in - (''confirmed'', ''investigating'', ''planned'', ''in progress'', - ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'', - ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'', - ''internal referral'' ) AND + 'problem.non_public = ''f'' and problem.state NOT IN + (''unconfirmed'', ''hidden'', ''partial'') AND regexp_split_to_array(bodies_str, '','') && ARRAY[?]', 'created desc', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-council' @@ -122,11 +120,8 @@ values ('ward_problems', '', '', 'New problems for {{COUNCIL}} within {{WARD}} ward on FixMyStreet', '/reports', 'The latest problems for {{COUNCIL}} within {{WARD}} ward reported by users', 'problem', - 'problem.non_public = ''f'' and problem.state in - (''confirmed'', ''investigating'', ''planned'', ''in progress'', - ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'', - ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'', - ''internal referral'' ) AND + 'problem.non_public = ''f'' and problem.state NOT IN + (''unconfirmed'', ''hidden'', ''partial'') AND (regexp_split_to_array(bodies_str, '','') && ARRAY[?] or bodies_str is null) and areas like ''%,''||?||'',%''', 'created desc', @@ -142,11 +137,8 @@ insert into alert_type values ('area_problems', '', '', 'New problems within {{NAME}}''s boundary on FixMyStreet', '/reports', 'The latest problems within {{NAME}}''s boundary reported by users', 'problem', - 'problem.non_public = ''f'' and problem.state in - (''confirmed'', ''investigating'', ''planned'', ''in progress'', - ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'', - ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'', - ''internal referral'' ) AND + 'problem.non_public = ''f'' and problem.state NOT IN + (''unconfirmed'', ''hidden'', ''partial'') AND areas like ''%,''||?||'',%''', 'created desc', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-area' diff --git a/db/migrate_from_osgb36_to_wgs84.pl b/db/migrate_from_osgb36_to_wgs84.pl deleted file mode 100644 index 676ffea9b..000000000 --- a/db/migrate_from_osgb36_to_wgs84.pl +++ /dev/null @@ -1,201 +0,0 @@ -#!/usr/bin/env perl - -use strict; -use warnings; - -=head1 DESCRIPTION - -This script will take a FMS database with eastings and northings in and migrate -it to latitude and longitude. It touches the following tables and functions: - -=cut - -use FindBin; -use lib "$FindBin::Bin/../perllib"; -use lib "$FindBin::Bin/../commonlib/perllib"; - -use mySociety::Config; -use mySociety::DBHandle qw(dbh); -use Utils; - -BEGIN { - mySociety::Config::set_file("$FindBin::Bin/../conf/general"); - mySociety::DBHandle::configure( - Name => mySociety::Config::get('FMS_DB_NAME'), - User => mySociety::Config::get('FMS_DB_USER'), - Password => mySociety::Config::get('FMS_DB_PASS'), - Host => mySociety::Config::get( 'FMS_DB_HOST', undef ), - Port => mySociety::Config::get( 'FMS_DB_PORT', undef ) - ); -} - -my $UPDATE_BATCH_SIZE = 500; - -migrate_problem_table(); -migrate_problem_find_nearby_function(); -migrate_alert_table(); - -=head2 problem table - -Add columns 'latitude' and 'longitude'. -Update all entries coverting from e,n to lon,lat. -Make the lat, lon columns not null. -Drop the 'problem_state_easting_northing_idx' index. -Create new index 'problem_state_latitude_longitude_idx'. -Drop the 'easting' and 'northing' columns. - -=cut - -sub migrate_problem_table { - - my $dbh = dbh(); - - # add columns - print "add latitude, longitude columns\n"; - $dbh->do("ALTER TABLE problem ADD $_ double precision") - for qw(latitude longitude); - $dbh->commit; - - # create a query for rows that need converting - my $rows_to_convert_query = $dbh->prepare( # - "SELECT id, easting, northing FROM problem" - . " WHERE latitude is NULL limit $UPDATE_BATCH_SIZE" - ); - - # update query - my $update_lat_lon_query = $dbh->prepare( # - "UPDATE problem SET latitude = ?, longitude = ? WHERE id = ?" - ); - - # loop through the entries in batches updating rows that need it. Do this in - # Perl rather than SQL for conveniance. - while (1) { - $rows_to_convert_query->execute; - last unless $rows_to_convert_query->rows; - while ( my $r = $rows_to_convert_query->fetchrow_hashref ) { - my ( $latitude, $longitude ) = - Utils::convert_en_to_latlon( $r->{easting}, $r->{northing} ); - print "update problem $r->{id}: ( $latitude, $longitude )\n"; - $update_lat_lon_query->execute( $latitude, $longitude, $r->{id} ); - } - $dbh->commit; # every batch of updates - } - - print "make latitude, longitude columns not null\n"; - $dbh->do("ALTER TABLE problem ALTER COLUMN $_ SET NOT NULL") - for qw(latitude longitude); - $dbh->commit; - - # drop old index, create new one - print "drop and create indexes\n"; - $dbh->do("DROP INDEX problem_state_easting_northing_idx"); - $dbh->do( "CREATE INDEX problem_state_latitude_longitude_idx " - . "ON problem(state, latitude, longitude)" ); - $dbh->commit; - - # drop columns - print "drop easting, northing columns\n"; - $dbh->do("ALTER TABLE problem DROP $_") for qw(easting northing); - $dbh->commit; - -} - -=head2 problem_find_nearby function - -Convert to use lat and long. -Also swap parameter order so that it is lat,lon rather than lon,lat to be consistent with pledgebank etc - -=cut - -sub migrate_problem_find_nearby_function { - my $dbh = dbh(); - - print "drop the existing problem_find_nearby function\n"; - $dbh->do( -"DROP FUNCTION problem_find_nearby ( double precision, double precision, double precision)" - ); - - print "create the new one\n"; - $dbh->do(<<'SQL_END'); - create function problem_find_nearby(double precision, double precision, double precision) - returns setof problem_nearby_match as - ' - -- trunc due to inaccuracies in floating point arithmetic - select problem.id, - R_e() * acos(trunc( - (sin(radians($1)) * sin(radians(latitude)) - + cos(radians($1)) * cos(radians(latitude)) - * cos(radians($2 - longitude)))::numeric, 14) - ) as distance - from problem - where - longitude is not null and latitude is not null - and radians(latitude) > radians($1) - ($3 / R_e()) - and radians(latitude) < radians($1) + ($3 / R_e()) - and (abs(radians($1)) + ($3 / R_e()) > pi() / 2 -- case where search pt is near pole - or angle_between(radians(longitude), radians($2)) - < $3 / (R_e() * cos(radians($1 + $3 / R_e())))) - -- ugly -- unable to use attribute name "distance" here, sadly - and R_e() * acos(trunc( - (sin(radians($1)) * sin(radians(latitude)) - + cos(radians($1)) * cos(radians(latitude)) - * cos(radians($2 - longitude)))::numeric, 14) - ) < $3 - order by distance desc -' language sql -SQL_END - - $dbh->commit; -} - -=head2 alert table - -NOTE: only for alert_types 'local_problems' or 'local_problems_state' - -parameter: convert easting to longitude -parameter2: convert nothing to latitude - -create a new column 'is_migrated' to use during migration in case of crash. - -=cut - -sub migrate_alert_table { - my $dbh = dbh(); - - print "Adding 'is_migrated' column\n"; - $dbh->do("ALTER TABLE alert ADD COLUMN is_migrated bool DEFAULT false"); - $dbh->commit; - - # create a query for rows that need converting - my $rows_to_convert_query = $dbh->prepare( # - "SELECT id, parameter, parameter2 FROM alert" - . " WHERE alert_type IN ('local_problems','local_problems_state')" - . " AND is_migrated = false" - . " LIMIT $UPDATE_BATCH_SIZE" - ); - - # update query - my $update_lat_lon_query = $dbh->prepare( # - "UPDATE alert SET parameter2 = ?, parameter = ?, is_migrated = true" - . " WHERE id = ?" - ); - - # loop through the entries in batches updating rows that need it. Do this in - # Perl rather than SQL for conveniance. - while (1) { - $rows_to_convert_query->execute; - last unless $rows_to_convert_query->rows; - while ( my $r = $rows_to_convert_query->fetchrow_hashref ) { - my ( $latitude, $longitude ) = - Utils::convert_en_to_latlon( $r->{parameter}, $r->{parameter2} ); - print "update alert $r->{id}: ( $latitude, $longitude )\n"; - $update_lat_lon_query->execute( $latitude, $longitude, $r->{id} ); - } - $dbh->commit; # every batch of updates - } - - print "drop 'is_migrated' column\n"; - $dbh->do("ALTER TABLE alert DROP COLUMN is_migrated"); - $dbh->commit; -} - diff --git a/db/rerun_dbic_loader.pl b/db/rerun_dbic_loader.pl index 958b28241..cf6e89ab2 100755 --- a/db/rerun_dbic_loader.pl +++ b/db/rerun_dbic_loader.pl @@ -3,6 +3,13 @@ use strict; use warnings; +BEGIN { + use File::Basename qw(dirname); + use File::Spec; + my $d = dirname(File::Spec->rel2abs($0)); + require "$d/../setenv.pl"; +} + # 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. @@ -20,13 +27,15 @@ my @tables_to_ignore = ( my $exclude = '^(?:' . join( '|', @tables_to_ignore ) . ')$'; make_schema_at( - 'FixMyStreet::DB', + 'FixMyStreet::DB::Schema', { 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 + result_namespace => '+FixMyStreet::DB::Result', + resultset_namespace => '+FixMyStreet::DB::ResultSet', # add in some extra components components => [ 'FilterColumn', 'InflateColumn::DateTime', 'EncodedColumn' ], diff --git a/db/schema.sql b/db/schema.sql index 18c1533d9..f428ff59d 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -69,8 +69,12 @@ create table contacts ( body_id integer not null references body(id), category text not null default 'Other', email text not null, - confirmed boolean not null, - deleted boolean not null, + state text not null check ( + state = 'unconfirmed' + or state = 'confirmed' + or state = 'inactive' + or state = 'deleted' + ), -- last editor editor text not null, @@ -102,8 +106,12 @@ create table contacts_history ( body_id integer not null, category text not null default 'Other', email text not null, - confirmed boolean not null, - deleted boolean not null, + state text not null check ( + state = 'unconfirmed' + or state = 'confirmed' + or state = 'inactive' + or state = 'deleted' + ), -- editor editor text not null, @@ -118,7 +126,7 @@ create table contacts_history ( create function contacts_updated() returns trigger as ' begin - insert into contacts_history (contact_id, body_id, category, email, editor, whenedited, note, confirmed, deleted) values (new.id, new.body_id, new.category, new.email, new.editor, new.whenedited, new.note, new.confirmed, new.deleted); + insert into contacts_history (contact_id, body_id, category, email, editor, whenedited, note, state) values (new.id, new.body_id, new.category, new.email, new.editor, new.whenedited, new.note, new.state); return new; end; ' language 'plpgsql'; @@ -136,6 +144,7 @@ CREATE TABLE response_priorities ( name text not null, description text, external_id text, + is_default boolean not null default 'f', unique(body_id, name) ); @@ -169,24 +178,7 @@ create table problem ( -- Metadata created timestamp not null default current_timestamp, confirmed timestamp, - state text not null check ( - state = 'unconfirmed' - or state = 'confirmed' - or state = 'investigating' - or state = 'planned' - or state = 'in progress' - or state = 'action scheduled' - or state = 'closed' - or state = 'fixed' - or state = 'fixed - council' - or state = 'fixed - user' - or state = 'hidden' - or state = 'partial' - or state = 'unable to fix' - or state = 'not responsible' - or state = 'duplicate' - or state = 'internal referral' - ), + state text not null, lang text not null default 'en-gb', service text not null default '', cobrand text not null default '' check (cobrand ~* '^[a-z0-9_]*$'), @@ -319,21 +311,7 @@ create table comment ( cobrand_data text not null default '' check (cobrand_data ~* '^[a-z0-9_]*$'), -- Extra data used in cobranded versions of the site mark_fixed boolean not null, mark_open boolean not null default 'f', - problem_state text check ( - problem_state = 'confirmed' - or problem_state = 'investigating' - or problem_state = 'planned' - or problem_state = 'in progress' - or problem_state = 'action scheduled' - or problem_state = 'closed' - or problem_state = 'fixed' - or problem_state = 'fixed - council' - or problem_state = 'fixed - user' - or problem_state = 'unable to fix' - or problem_state = 'not responsible' - or problem_state = 'duplicate' - or problem_state = 'internal referral' - ), + problem_state text, -- other fields? one to indicate whether this was written by the council -- and should be highlighted in the display? external_id text, @@ -520,3 +498,28 @@ CREATE TABLE contact_defect_types ( ALTER TABLE problem ADD COLUMN defect_type_id int REFERENCES defect_types(id); + +CREATE TABLE translation ( + id serial not null primary key, + tbl text not null, + object_id integer not null, + col text not null, + lang text not null, + msgstr text not null, + unique(tbl, object_id, col, lang) +); + +CREATE TABLE report_extra_fields ( + id serial not null primary key, + name text not null, + cobrand text, + language text, + extra text +); + +CREATE TABLE state ( + id serial not null primary key, + label text not null unique, + type text not null check (type = 'open' OR type = 'closed' OR type = 'fixed'), + name text not null unique +); diff --git a/db/schema_0051-inactive-contact-state.sql b/db/schema_0051-inactive-contact-state.sql new file mode 100644 index 000000000..f29455f18 --- /dev/null +++ b/db/schema_0051-inactive-contact-state.sql @@ -0,0 +1,45 @@ +BEGIN; + +ALTER TABLE contacts ADD state text; + +ALTER TABLE contacts ADD CONSTRAINT contacts_state_check CHECK ( + state = 'unconfirmed' + or state = 'confirmed' + or state = 'inactive' + or state = 'deleted' +); + +UPDATE contacts SET state = 'deleted' WHERE deleted; +UPDATE contacts SET state = 'confirmed' WHERE confirmed AND NOT deleted; +UPDATE contacts SET state = 'unconfirmed' WHERE NOT confirmed AND NOT deleted; + +ALTER TABLE contacts ALTER COLUMN state SET NOT NULL; +ALTER TABLE contacts DROP COLUMN confirmed; +ALTER TABLE contacts DROP COLUMN deleted; + +ALTER TABLE contacts_history ADD state text; + +ALTER TABLE contacts_history ADD CONSTRAINT contacts_history_state_check CHECK ( + state = 'unconfirmed' + or state = 'confirmed' + or state = 'inactive' + or state = 'deleted' +); + +UPDATE contacts_history SET state = 'deleted' WHERE deleted; +UPDATE contacts_history SET state = 'confirmed' WHERE confirmed AND NOT deleted; +UPDATE contacts_history SET state = 'unconfirmed' WHERE NOT confirmed AND NOT deleted; + +ALTER TABLE contacts_history ALTER COLUMN state SET NOT NULL; +ALTER TABLE contacts_history DROP COLUMN confirmed; +ALTER TABLE contacts_history DROP COLUMN deleted; + +CREATE OR REPLACE FUNCTION contacts_updated() + returns trigger as ' + begin + insert into contacts_history (contact_id, body_id, category, email, editor, whenedited, note, state) values (new.id, new.body_id, new.category, new.email, new.editor, new.whenedited, new.note, new.state); + return new; + end; +' language 'plpgsql'; + +COMMIT; diff --git a/db/schema_0052-translation-table.sql b/db/schema_0052-translation-table.sql new file mode 100644 index 000000000..95df499cc --- /dev/null +++ b/db/schema_0052-translation-table.sql @@ -0,0 +1,13 @@ +BEGIN; + +CREATE TABLE translation ( + id serial not null primary key, + tbl text not null, + object_id integer not null, + col text not null, + lang text not null, + msgstr text not null, + unique(tbl, object_id, col, lang) +); + +COMMIT; diff --git a/db/schema_0053-add-report-extra-fields-table.sql b/db/schema_0053-add-report-extra-fields-table.sql new file mode 100644 index 000000000..be92abd47 --- /dev/null +++ b/db/schema_0053-add-report-extra-fields-table.sql @@ -0,0 +1,11 @@ +BEGIN; + +CREATE TABLE report_extra_fields ( + id serial not null primary key, + name text not null, + cobrand text, + language text, + extra text +); + +COMMIT; diff --git a/db/schema_0054-add-state-table.sql b/db/schema_0054-add-state-table.sql new file mode 100644 index 000000000..c4be36015 --- /dev/null +++ b/db/schema_0054-add-state-table.sql @@ -0,0 +1,51 @@ +BEGIN; + +CREATE TABLE state ( + id serial not null primary key, + label text not null unique, + type text not null check (type = 'open' OR type = 'closed' OR type = 'fixed'), + name text not null unique +); + +INSERT INTO state (label, type, name) VALUES ('investigating', 'open', 'Investigating'); +INSERT INTO state (label, type, name) VALUES ('in progress', 'open', 'In progress'); +INSERT INTO state (label, type, name) VALUES ('planned', 'open', 'Planned'); +INSERT INTO state (label, type, name) VALUES ('action scheduled', 'open', 'Action scheduled'); +INSERT INTO state (label, type, name) VALUES ('unable to fix', 'closed', 'No further action'); +INSERT INTO state (label, type, name) VALUES ('not responsible', 'closed', 'Not responsible'); +INSERT INTO state (label, type, name) VALUES ('duplicate', 'closed', 'Duplicate'); +INSERT INTO state (label, type, name) VALUES ('internal referral', 'closed', 'Internal referral'); +INSERT INTO state (label, type, name) VALUES ('fixed', 'fixed', 'Fixed'); + +ALTER TABLE problem DROP CONSTRAINT problem_state_check; +ALTER TABLE comment DROP CONSTRAINT comment_problem_state_check; + +UPDATE alert_type SET item_where = 'nearby.problem_id = problem.id + and problem.non_public = ''f'' + and problem.state NOT IN (''hidden'', ''unconfirmed'', ''partial'')' + WHERE ref = 'postcode_local_problems'; +UPDATE alert_type set item_where = 'problem.non_public = ''f'' + and problem.state NOT IN (''hidden'', ''unconfirmed'', ''partial'')' + WHERE ref = 'new_problems'; +UPDATE alert_type set item_where = 'problem.non_public = ''f'' + and problem.state in (''fixed'', ''fixed - user'', ''fixed - council'')' + WHERE ref = 'new_fixed_problems'; +UPDATE alert_type set item_where = 'nearby.problem_id = problem.id + and problem.non_public = ''f'' + and problem.state NOT IN (''hidden'', ''unconfirmed'', ''partial'')' + WHERE ref = 'local_problems'; +UPDATE alert_type set item_where = 'problem.non_public = ''f'' + AND problem.state NOT IN (''hidden'', ''unconfirmed'', ''partial'') + AND regexp_split_to_array(bodies_str, '','') && ARRAY[?]' + WHERE ref = 'council_problems'; +UPDATE alert_type set item_where = 'problem.non_public = ''f'' + AND problem.state NOT IN (''hidden'', ''unconfirmed'', ''partial'') + AND (regexp_split_to_array(bodies_str, '','') && ARRAY[?] or bodies_str is null) and + areas like ''%,''||?||'',%''' + WHERE ref = 'ward_problems'; +UPDATE alert_type set item_where = 'problem.non_public = ''f'' + AND problem.state NOT IN (''hidden'', ''unconfirmed'', ''partial'') + AND areas like ''%,''||?||'',%''' + WHERE ref = 'area_problems'; + +COMMIT; diff --git a/db/schema_0055-add-default-to-reponsepriority.sql b/db/schema_0055-add-default-to-reponsepriority.sql new file mode 100644 index 000000000..0f8b72e13 --- /dev/null +++ b/db/schema_0055-add-default-to-reponsepriority.sql @@ -0,0 +1,5 @@ +BEGIN; + +ALTER TABLE response_priorities ADD is_default boolean not null default 'f'; + +COMMIT; |