diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/alert_types.sql | 8 | ||||
| -rw-r--r-- | db/alert_types_eha.sql | 8 | ||||
| -rw-r--r-- | db/schema.sql | 4 | ||||
| -rw-r--r-- | db/schema_0031-rename-problem-council-column.sql | 5 |
4 files changed, 15 insertions, 10 deletions
diff --git a/db/alert_types.sql b/db/alert_types.sql index 4116d29dc..1de79e722 100644 --- a/db/alert_types.sql +++ b/db/alert_types.sql @@ -83,8 +83,8 @@ insert into alert_type item_title, item_link, item_description, template) 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'') and (council like ''%''||?||''%'' - or council is null) and areas like ''%,''||?||'',%''', 'created desc', + 'problem', 'problem.non_public = ''f'' and problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and (bodies_str like ''%''||?||''%'' + or bodies_str is null) and areas like ''%,''||?||'',%''', 'created desc', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-council' ); @@ -97,8 +97,8 @@ insert into alert_type 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'') and (council like ''%''||?||''%'' - or council is null) and areas like ''%,''||?||'',%''', 'created desc', + 'problem', 'problem.non_public = ''f'' and problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and (bodies_str like ''%''||?||''%'' + or bodies_str is null) and areas like ''%,''||?||'',%''', 'created desc', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-ward' ); diff --git a/db/alert_types_eha.sql b/db/alert_types_eha.sql index af074717b..e4ec0c986 100644 --- a/db/alert_types_eha.sql +++ b/db/alert_types_eha.sql @@ -61,8 +61,8 @@ insert into alert_type item_title, item_link, item_description, template) values ('council_problems', '', '', 'New reports to {{COUNCIL}} on reportemptyhomes.com', '/reports', 'The latest reports for {{COUNCIL}} reported by users', - 'problem', 'problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and (council like ''%''||?||''%'' - or council is null) and areas like ''%,''||?||'',%''', 'created desc', + 'problem', 'problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and (bodies_str like ''%''||?||''%'' + or bodies_str is null) and areas like ''%,''||?||'',%''', 'created desc', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-council' ); @@ -75,8 +75,8 @@ insert into alert_type values ('ward_problems', '', '', 'New reports for {{COUNCIL}} within {{WARD}} ward on reportemptyhomes.com', '/reports', 'The latest reports for {{COUNCIL}} within {{WARD}} ward reported by users', - 'problem', 'problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and (council like ''%''||?||''%'' - or council is null) and areas like ''%,''||?||'',%''', 'created desc', + 'problem', 'problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and (bodies_str like ''%''||?||''%'' + or bodies_str is null) and areas like ''%,''||?||'',%''', 'created desc', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-ward' ); diff --git a/db/schema.sql b/db/schema.sql index 9fd4ce332..1608b08ba 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -124,8 +124,8 @@ create table problem ( postcode text not null, latitude double precision not null, longitude double precision not null, - council text, -- the council(s) we'll report this problem to - areas text not null, -- the voting areas this location is in + bodies_str text, -- the body(s) we'll report this problem to + areas text not null, -- the mapit areas this location is in category text not null default 'Other', title text not null, detail text not null, diff --git a/db/schema_0031-rename-problem-council-column.sql b/db/schema_0031-rename-problem-council-column.sql new file mode 100644 index 000000000..87542a192 --- /dev/null +++ b/db/schema_0031-rename-problem-council-column.sql @@ -0,0 +1,5 @@ +begin; + +ALTER TABLE problem RENAME COLUMN council TO bodies_str; + +commit; |