diff options
| -rwxr-xr-x | bin/update-schema | 1 | ||||
| -rw-r--r-- | db/alert_types.sql | 42 | ||||
| -rw-r--r-- | db/downgrade_0054---0053.sql | 39 | ||||
| -rw-r--r-- | db/schema.sql | 42 | ||||
| -rw-r--r-- | db/schema_0054-add-state-table.sql | 41 | ||||
| -rw-r--r-- | perllib/FixMyStreet/DB/Result/State.pm | 38 |
6 files changed, 140 insertions, 63 deletions
diff --git a/bin/update-schema b/bin/update-schema index 32c00ff5e..9a4f05813 100755 --- a/bin/update-schema +++ b/bin/update-schema @@ -195,6 +195,7 @@ else { # (assuming schema change files are never half-applied, which should be the case) sub get_db_version { return 'EMPTY' if ! table_exists('problem'); + return '0054' if table_exists('state'); return '0053' if table_exists('report_extra_fields'); return '0052' if table_exists('translation'); return '0051' if column_exists('contacts', 'state'); diff --git a/db/alert_types.sql b/db/alert_types.sql index 471fd905f..84f446f2c 100644 --- a/db/alert_types.sql +++ b/db/alert_types.sql @@ -18,11 +18,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 +43,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 +68,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 +93,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 +110,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 +127,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/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/schema.sql b/db/schema.sql index ed930a13e..12e66b53a 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -177,24 +177,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_]*$'), @@ -327,21 +310,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, @@ -546,3 +515,10 @@ CREATE TABLE report_extra_fields ( 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'), + name text not null unique +); diff --git a/db/schema_0054-add-state-table.sql b/db/schema_0054-add-state-table.sql new file mode 100644 index 000000000..c052f145a --- /dev/null +++ b/db/schema_0054-add-state-table.sql @@ -0,0 +1,41 @@ +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'), + name text not null unique +); + +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/perllib/FixMyStreet/DB/Result/State.pm b/perllib/FixMyStreet/DB/Result/State.pm new file mode 100644 index 000000000..6a689d595 --- /dev/null +++ b/perllib/FixMyStreet/DB/Result/State.pm @@ -0,0 +1,38 @@ +use utf8; +package FixMyStreet::DB::Result::State; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; +__PACKAGE__->load_components("FilterColumn", "InflateColumn::DateTime", "EncodedColumn"); +__PACKAGE__->table("state"); +__PACKAGE__->add_columns( + "id", + { + data_type => "integer", + is_auto_increment => 1, + is_nullable => 0, + sequence => "state_id_seq", + }, + "label", + { data_type => "text", is_nullable => 0 }, + "type", + { data_type => "text", is_nullable => 0 }, + "name", + { data_type => "text", is_nullable => 0 }, +); +__PACKAGE__->set_primary_key("id"); +__PACKAGE__->add_unique_constraint("state_label_key", ["label"]); +__PACKAGE__->add_unique_constraint("state_name_key", ["name"]); + + +# Created by DBIx::Class::Schema::Loader v0.07035 @ 2017-08-22 15:17:43 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:dvtAOpeYqEF9T3otHHgLqw + + +# You can replace this text with custom code or comments, and it will be preserved on regeneration +1; |