diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/alert_types.sql | 18 | ||||
| -rw-r--r-- | db/generate_secret.sql | 8 | ||||
| -rw-r--r-- | db/migrate_from_osgb36_to_wgs84.pl | 10 | ||||
| -rw-r--r-- | db/schema.sql | 28 | ||||
| -rw-r--r-- | db/schema_0021-add_external_source_columns_to_problem.sql | 8 | ||||
| -rw-r--r-- | db/schema_0022-add_interest_count_to_problems.sql | 6 | ||||
| -rw-r--r-- | db/schema_0023-add_can_be_devolved_and_category_config.sql | 13 | ||||
| -rw-r--r-- | db/schema_0024-add_non_public_categories_and_problems.sql | 37 | ||||
| -rw-r--r-- | db/schema_0026-change_interest_count_default.sql | 9 | ||||
| -rw-r--r-- | db/schema_0027-add_sub_category_to_problem.sql | 6 |
10 files changed, 127 insertions, 16 deletions
diff --git a/db/alert_types.sql b/db/alert_types.sql index 4ebd33cf9..4116d29dc 100644 --- a/db/alert_types.sql +++ b/db/alert_types.sql @@ -17,7 +17,7 @@ insert into alert_type item_title, item_link, item_description, template) values ('new_problems', '', '', 'New problems on FixMyStreet', '/', 'The latest problems reported by users', - 'problem', 'problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'')', 'created desc', + 'problem', 'problem.non_public = ''f'' and problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'')', 'created desc', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem'); -- New fixed problems anywhere on the site @@ -28,7 +28,7 @@ insert into alert_type item_title, item_link, item_description, template) values ('new_fixed_problems', '', '', 'Problems recently reported fixed on FixMyStreet', '/', 'The latest problems reported fixed by users', - 'problem', 'problem.state in (''fixed'', ''fixed - user'', ''fixed - council'')', 'lastupdate desc', + 'problem', 'problem.non_public = ''f'' and problem.state in (''fixed'', ''fixed - user'', ''fixed - council'')', 'lastupdate desc', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem'); -- New problems around a location @@ -39,7 +39,7 @@ insert into alert_type item_title, item_link, item_description, template) 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.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'')', 'created desc', + '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'')', 'created desc', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby'); -- New problems around a location @@ -50,7 +50,7 @@ insert into alert_type item_title, item_link, item_description, template) values ('local_problems_state', '', '', '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.state in (?)', 'created desc', + 'problem_find_nearby(?, ?, ?) as nearby,problem', 'nearby.problem_id = problem.id and problem.non_public = ''f'' and problem.state in (?)', 'created desc', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby'); -- New problems around a postcode @@ -61,7 +61,7 @@ insert into alert_type item_title, item_link, item_description, template) 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.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'')', 'created desc', + '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'')', 'created desc', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby'); -- New problems around a postcode with a particular state @@ -72,7 +72,7 @@ insert into alert_type 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', - 'problem_find_nearby(?, ?, ?) as nearby,problem', 'nearby.problem_id = problem.id and problem.state in (?)', 'created desc', + 'problem_find_nearby(?, ?, ?) as nearby,problem', 'nearby.problem_id = problem.id and problem.non_public = ''f'' and problem.state in (?)', 'created desc', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby'); -- New problems sent to a particular council @@ -83,7 +83,7 @@ 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.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and (council like ''%''||?||''%'' + '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', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-council' ); @@ -97,7 +97,7 @@ 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.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and (council like ''%''||?||''%'' + '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', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-ward' ); @@ -111,7 +111,7 @@ 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.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and areas like ''%,''||?||'',%''', 'created desc', + 'problem.non_public = ''f'' and problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and areas like ''%,''||?||'',%''', 'created desc', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-area' ); diff --git a/db/generate_secret.sql b/db/generate_secret.sql new file mode 100644 index 000000000..ad4b48200 --- /dev/null +++ b/db/generate_secret.sql @@ -0,0 +1,8 @@ +-- assumes there's a secret table (from the schema.sql) +-- use your own secret if you have one :-) +-- otherwise you can use this to populate the secret table with a random secret + +-- empty the table in case it has a value already (i.e., this is *destructive*!) +delete from secret; + +insert into secret values (md5(random()::text)); diff --git a/db/migrate_from_osgb36_to_wgs84.pl b/db/migrate_from_osgb36_to_wgs84.pl index abd504fb8..676ffea9b 100644 --- a/db/migrate_from_osgb36_to_wgs84.pl +++ b/db/migrate_from_osgb36_to_wgs84.pl @@ -21,11 +21,11 @@ use Utils; BEGIN { mySociety::Config::set_file("$FindBin::Bin/../conf/general"); mySociety::DBHandle::configure( - Name => mySociety::Config::get('BCI_DB_NAME'), - User => mySociety::Config::get('BCI_DB_USER'), - Password => mySociety::Config::get('BCI_DB_PASS'), - Host => mySociety::Config::get( 'BCI_DB_HOST', undef ), - Port => mySociety::Config::get( 'BCI_DB_PORT', undef ) + 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 ) ); } diff --git a/db/schema.sql b/db/schema.sql index 437541898..021c5561d 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -81,7 +81,16 @@ create table contacts ( note text not null, -- extra fields required for open311 - extra text + extra text, + + -- for things like missed bin collections + non_public boolean default 'f', + + -- per contact endpoint configuration + endpoint text, + jurisdiction text default '', + api_key text default '', + send_method text ); create unique index contacts_area_id_category_idx on contacts(area_id, category); @@ -202,10 +211,24 @@ create table problem ( send_fail_timestamp timestamp, -- record send_method used, which can be used to infer usefulness of external_id - send_method_used text + send_method_used text, + + -- for things like missed bin collections + non_public BOOLEAN default 'f', + + -- record details about messages from external sources, eg. message manager + external_source text, + external_source_id text, + + -- number of me toos + interest_count integer default 0, + + -- subcategory to enable filtering in reporting -- + subcategory text ); create index problem_state_latitude_longitude_idx on problem(state, latitude, longitude); create index problem_user_id_idx on problem ( user_id ); +create index problem_external_body_idx on problem(lower(external_body)); create table questionnaire ( id serial not null primary key, @@ -447,5 +470,6 @@ create table open311conf ( send_comments boolean not null default 'f', comment_user_id int references users(id), suppress_alerts boolean not null default 'f', + can_be_devolved boolean not null default 'f', send_extended_statuses boolean not null default 'f' ); diff --git a/db/schema_0021-add_external_source_columns_to_problem.sql b/db/schema_0021-add_external_source_columns_to_problem.sql new file mode 100644 index 000000000..a74bcce7d --- /dev/null +++ b/db/schema_0021-add_external_source_columns_to_problem.sql @@ -0,0 +1,8 @@ +begin; + +ALTER table problem + ADD column external_source TEXT; +ALTER table problem + ADD column external_source_id TEXT; + +commit; diff --git a/db/schema_0022-add_interest_count_to_problems.sql b/db/schema_0022-add_interest_count_to_problems.sql new file mode 100644 index 000000000..62092aa0a --- /dev/null +++ b/db/schema_0022-add_interest_count_to_problems.sql @@ -0,0 +1,6 @@ +begin; + +ALTER table problem + ADD COLUMN interest_count integer; + +commit; diff --git a/db/schema_0023-add_can_be_devolved_and_category_config.sql b/db/schema_0023-add_can_be_devolved_and_category_config.sql new file mode 100644 index 000000000..6eba0919a --- /dev/null +++ b/db/schema_0023-add_can_be_devolved_and_category_config.sql @@ -0,0 +1,13 @@ +begin; + +ALTER table open311conf + ADD column can_be_devolved BOOL NOT NULL DEFAULT 'f'; + +ALTER table contacts + ADD column endpoint TEXT, + ADD column jurisdiction TEXT DEFAULT '', + ADD column api_key TEXT DEFAULT '', + ADD column send_method TEXT +; + +commit; diff --git a/db/schema_0024-add_non_public_categories_and_problems.sql b/db/schema_0024-add_non_public_categories_and_problems.sql new file mode 100644 index 000000000..c133b3ef2 --- /dev/null +++ b/db/schema_0024-add_non_public_categories_and_problems.sql @@ -0,0 +1,37 @@ +BEGIN; + + ALTER TABLE contacts + ADD COLUMN non_public BOOLEAN DEFAULT 'f'; + + ALTER TABLE problem + ADD COLUMN non_public BOOLEAN DEFAULT 'f'; + + + UPDATE alert_type set item_where = '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'')' WHERE ref = 'postcode_local_problems'; + UPDATE alert_type set item_where = 'problem.non_public = ''f'' and problem.state in + (''confirmed'', ''investigating'', ''planned'', ''in progress'', + ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'' )' 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 in + (''confirmed'', ''investigating'', ''planned'', ''in progress'', + ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'')' WHERE ref = 'local_problems'; + UPDATE alert_type set item_where = '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 ''%,''||?||'',%''' WHERE ref = 'council_problems'; + UPDATE alert_type set item_where = '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 ''%,''||?||'',%''' WHERE ref = 'ward_problems'; + UPDATE alert_type set item_where = 'problem.non_public = ''f'' and problem.state in + (''confirmed'', ''investigating'', ''planned'', ''in progress'', + ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and + areas like ''%,''||?||'',%''' WHERE ref = 'area_problems'; + +commit; + +COMMIT; diff --git a/db/schema_0026-change_interest_count_default.sql b/db/schema_0026-change_interest_count_default.sql new file mode 100644 index 000000000..7c78f6fe8 --- /dev/null +++ b/db/schema_0026-change_interest_count_default.sql @@ -0,0 +1,9 @@ +BEGIN; + + ALTER TABLE problem + ALTER COLUMN interest_count SET DEFAULT 0; + + UPDATE problem SET interest_count = 0 + WHERE interest_count IS NULL; + +COMMIT; diff --git a/db/schema_0027-add_sub_category_to_problem.sql b/db/schema_0027-add_sub_category_to_problem.sql new file mode 100644 index 000000000..6787bd371 --- /dev/null +++ b/db/schema_0027-add_sub_category_to_problem.sql @@ -0,0 +1,6 @@ +begin; + +ALTER TABLE problem + ADD COLUMN subcategory TEXT; + +commit; |