aboutsummaryrefslogtreecommitdiffstats
path: root/db
diff options
context:
space:
mode:
Diffstat (limited to 'db')
-rw-r--r--db/alert_types.sql7
-rw-r--r--db/alert_types_eha.pl28
-rw-r--r--db/alert_types_eha.sql95
-rw-r--r--db/downgrade_0037---0036.sql1
-rw-r--r--db/downgrade_0038---0037.sql2
-rwxr-xr-xdb/rerun_dbic_loader.pl1
-rw-r--r--db/schema.sql91
-rw-r--r--db/schema_0032-moderation.sql4
-rw-r--r--db/schema_0033-speed-up-nearby.sql47
-rw-r--r--db/schema_0034-remove-ms_current_timestamp.sql16
-rw-r--r--db/schema_0035-bodies_str-tidying.sql31
-rw-r--r--db/schema_0036-add-underscore-to-cobrand.sql17
-rw-r--r--db/schema_0037-response-templates.sql8
-rw-r--r--db/schema_0038-time-spent.sql2
14 files changed, 169 insertions, 181 deletions
diff --git a/db/alert_types.sql b/db/alert_types.sql
index 4022ddaf7..471fd905f 100644
--- a/db/alert_types.sql
+++ b/db/alert_types.sql
@@ -93,7 +93,7 @@ values ('postcode_local_problems_state', '', '',
'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
+-- New problems sent to a particular body
insert into alert_type
(ref, head_sql_query, head_table,
head_title, head_link, head_description,
@@ -107,8 +107,7 @@ values ('council_problems', '', '',
''fixed'', ''fixed - council'', ''fixed - user'', ''closed'',
''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'',
''internal referral'' ) AND
- (bodies_str like ''%''||?||''%'' or bodies_str is null) and
- areas like ''%,''||?||'',%''',
+ regexp_split_to_array(bodies_str, '','') && ARRAY[?]',
'created desc',
'{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-council'
);
@@ -128,7 +127,7 @@ values ('ward_problems', '', '',
''fixed'', ''fixed - council'', ''fixed - user'', ''closed'',
''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'',
''internal referral'' ) AND
- (bodies_str like ''%''||?||''%'' or bodies_str is null) and
+ (regexp_split_to_array(bodies_str, '','') && ARRAY[?] 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.pl b/db/alert_types_eha.pl
deleted file mode 100644
index b090522e6..000000000
--- a/db/alert_types_eha.pl
+++ /dev/null
@@ -1,28 +0,0 @@
-# This file is only here so that the strings from alert_types_eha.sql
-# get into the .po translation file.
-
-# New problems anywhere on the site
- _('New reports on reportemptyhomes.com'),
-
-# New fixed problems anywhere on the site
- _('Properties recently reported as put back to use on reportemptyhomes.com'),
- _('The latest properties reported back to use by users'),
-
-# New problems around a location
- _('New local reports on reportemptyhomes.com'),
- _('The latest local reports reported by users'),
-
-# New problems around a postcode
- _('New reports on reportemptyhomes.com near {{POSTCODE}}'),
-
-# New problems sent to a particular council
- _('New reports to {{COUNCIL}} on reportemptyhomes.com'),
- _('The latest reports for {{COUNCIL}} reported by users'),
-
-# New problems within a particular ward sent to a particular council
- _('New reports for {{COUNCIL}} within {{WARD}} ward on reportemptyhomes.com'),
- _('The latest reports for {{COUNCIL}} within {{WARD}} ward reported by users'),
-
-# New problems within a particular voting area (ward, constituency, whatever)
- _('New reports within {{NAME}}\'s boundary on reportemptyhomes.com'),
- _('The latest reports within {{NAME}}\'s boundary reported by users'),
diff --git a/db/alert_types_eha.sql b/db/alert_types_eha.sql
deleted file mode 100644
index e4ec0c986..000000000
--- a/db/alert_types_eha.sql
+++ /dev/null
@@ -1,95 +0,0 @@
--- New updates on a particular problem report
-insert into alert_type
-(ref, head_sql_query, head_table,
- head_title, head_link, head_description,
- item_table, item_where, item_order,
- item_title, item_link, item_description, template)
-values ('new_updates', 'select * from problem where id=?', 'problem',
- 'Updates on {{title}}', '/', 'Updates on {{title}}',
- 'comment', 'comment.state=''confirmed''', 'created desc',
- 'Update by {{name}}', '/report/{{problem_id}}#comment_{{id}}', '{{text}}', 'alert-update');
-
--- New problems anywhere on the site
-insert into alert_type
-(ref, head_sql_query, head_table,
- head_title, head_link, head_description,
- item_table, item_where, item_order,
- item_title, item_link, item_description, template)
-values ('new_problems', '', '',
- 'New reports on reportemptyhomes.com', '/', 'The latest empty properties reported by users',
- 'problem', '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
-insert into alert_type
-(ref, head_sql_query, head_table,
- head_title, head_link, head_description,
- item_table, item_where, item_order,
- item_title, item_link, item_description, template)
-values ('new_fixed_problems', '', '',
- 'Properties recently reported as put back to use on reportemptyhomes.com', '/', 'The latest properties reported back to use by users',
- 'problem', 'problem.state in (''fixed'', ''sixed - council'', ''fixed - user'')', 'lastupdate desc',
- '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem');
-
--- New problems around a location
-insert into alert_type
-(ref, head_sql_query, head_table,
- head_title, head_link, head_description,
- item_table, item_where, item_order,
- item_title, item_link, item_description, template)
-values ('local_problems', '', '',
- 'New local reports on reportemptyhomes.com', '/', 'The latest local reports 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',
- '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby');
-
--- New problems around a postcode
-insert into alert_type
-(ref, head_sql_query, head_table,
- head_title, head_link, head_description,
- item_table, item_where, item_order,
- item_title, item_link, item_description, template)
-values ('postcode_local_problems', '', '',
- 'New reports on reportemptyhomes.com near {{POSTCODE}}', '/', 'The latest local reports 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',
- '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby');
-
--- New problems sent to a particular council
-insert into alert_type
-(ref, head_sql_query, head_table,
- head_title, head_link, head_description,
- item_table, item_where, item_order,
- 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 (bodies_str like ''%''||?||''%''
- or bodies_str is null) and areas like ''%,''||?||'',%''', 'created desc',
- '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-council'
-);
-
--- New problems within a particular ward sent to a particular council
-insert into alert_type
-(ref, head_sql_query, head_table,
- head_title, head_link, head_description,
- item_table, item_where, item_order,
- item_title, item_link, item_description, template)
-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 (bodies_str like ''%''||?||''%''
- or bodies_str is null) and areas like ''%,''||?||'',%''', 'created desc',
- '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-ward'
-);
-
--- New problems within a particular voting area (ward, constituency, whatever)
-insert into alert_type
-(ref, head_sql_query, head_table,
- head_title, head_link, head_description,
- item_table, item_where, item_order,
- item_title, item_link, item_description, template)
-values ('area_problems', '', '',
- 'New reports within {{NAME}}''s boundary on reportemptyhomes.com', '/reports',
- 'The latest reports 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',
- '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-area'
-);
-
diff --git a/db/downgrade_0037---0036.sql b/db/downgrade_0037---0036.sql
new file mode 100644
index 000000000..39f73b308
--- /dev/null
+++ b/db/downgrade_0037---0036.sql
@@ -0,0 +1 @@
+drop table response_templates;
diff --git a/db/downgrade_0038---0037.sql b/db/downgrade_0038---0037.sql
new file mode 100644
index 000000000..11a0d11ad
--- /dev/null
+++ b/db/downgrade_0038---0037.sql
@@ -0,0 +1,2 @@
+alter table admin_log
+ drop column time_spent;
diff --git a/db/rerun_dbic_loader.pl b/db/rerun_dbic_loader.pl
index 152d319b1..d7dfd1c10 100755
--- a/db/rerun_dbic_loader.pl
+++ b/db/rerun_dbic_loader.pl
@@ -13,7 +13,6 @@ 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 = (
- 'debugdate', #
'flickr_imported', #
'partial_user', #
'textmystreet', #
diff --git a/db/schema.sql b/db/schema.sql
index 5bac02bce..72fccd7f3 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -11,27 +11,6 @@ create table secret (
secret text not null
);
--- If a row is present, that is date which is "today". Used for debugging
--- to advance time without having to wait.
-create table debugdate (
- override_today date
-);
-
--- Returns the timestamp of current time, but with possibly overriden "today".
-create function ms_current_timestamp()
- returns timestamp as '
- declare
- today date;
- begin
- today = (select override_today from debugdate);
- if today is not null then
- return today + current_time;
- else
- return current_timestamp;
- end if;
- end;
-' language 'plpgsql';
-
-- table for sessions - needed by Catalyst::Plugin::Session::Store::DBIC
create table sessions (
id char(72) primary key,
@@ -153,6 +132,7 @@ create table problem (
latitude double precision not null,
longitude double precision not null,
bodies_str text, -- the body(s) we'll report this problem to
+ bodies_missing text, -- the body(s) we had no contact details for
areas text not null, -- the mapit areas this location is in
category text not null default 'Other',
title text not null,
@@ -171,7 +151,7 @@ create table problem (
external_team text,
-- Metadata
- created timestamp not null default ms_current_timestamp(),
+ created timestamp not null default current_timestamp,
confirmed timestamp,
state text not null check (
state = 'unconfirmed'
@@ -193,9 +173,9 @@ create table problem (
),
lang text not null default 'en-gb',
service text not null default '',
- cobrand text not null default '' check (cobrand ~* '^[a-z0-9]*$'),
- cobrand_data text not null default '' check (cobrand_data ~* '^[a-z0-9]*$'), -- Extra data used in cobranded versions of the site
- lastupdate timestamp not null default ms_current_timestamp(),
+ cobrand text not null default '' check (cobrand ~* '^[a-z0-9_]*$'),
+ cobrand_data text not null default '' check (cobrand_data ~* '^[a-z0-9_]*$'), -- Extra data used in cobranded versions of the site
+ lastupdate timestamp not null default current_timestamp,
whensent timestamp,
send_questionnaire boolean not null default 't',
extra text, -- extra fields required for open311
@@ -226,6 +206,8 @@ create table problem (
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 index problem_radians_latitude_longitude_idx on problem(radians(latitude), radians(longitude));
+create index problem_bodies_str_array_idx on problem USING gin(regexp_split_to_array(bodies_str, ','));
create table questionnaire (
id serial not null primary key,
@@ -242,17 +224,6 @@ create table questionnaire (
create index questionnaire_problem_id_idx on questionnaire using btree (problem_id);
--- angle_between A1 A2
--- Given two angles A1 and A2 on a circle expressed in radians, return the
--- smallest angle between them.
-create function angle_between(double precision, double precision)
- returns double precision as '
-select case
- when abs($1 - $2) > pi() then 2 * pi() - abs($1 - $2)
- else abs($1 - $2)
- end;
-' language sql immutable;
-
-- R_e
-- Radius of the earth, in km. This is something like 6372.8 km:
-- http://en.wikipedia.org/wiki/Earth_radius
@@ -274,11 +245,11 @@ create function problem_find_nearby(double precision, double precision, double p
-- table or results set in memory. That means we can't check the values of
-- the parameters, sadly.
-- Through sheer laziness, just use great-circle distance; that'll be off
- -- by ~0.1%:
- -- http://www.ga.gov.au/nmd/geodesy/datums/distance.jsp
+ -- by ~0.1%.
-- We index locations on lat/lon so that we can select the locations which lie
-- within a wedge of side about 2 * DISTANCE. That cuts down substantially
-- on the amount of work we have to do.
+ -- http://janmatuschek.de/LatitudeLongitudeBoundingCoordinates
'
-- trunc due to inaccuracies in floating point arithmetic
select problem.id,
@@ -292,9 +263,13 @@ create function problem_find_nearby(double precision, double precision, double p
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()))))
+ and (
+ abs(radians($1)) + ($3 / R_e()) > pi() / 2 -- case where search pt is near pole
+ or (
+ radians(longitude) > radians($2) - asin(sin($3 / R_e())/cos(radians($1)))
+ and radians(longitude) < radians($2) + asin(sin($3 / R_e())/cos(radians($1)))
+ )
+ )
-- ugly -- unable to use attribute name "distance" here, sadly
and R_e() * acos(trunc(
(sin(radians($1)) * sin(radians(latitude))
@@ -302,7 +277,7 @@ create function problem_find_nearby(double precision, double precision, double p
* cos(radians($2 - longitude)))::numeric, 14)
) < $3
order by distance desc
-' language sql; -- should be "stable" rather than volatile per default?
+' language sql stable;
-- Comments/q&a on problems.
@@ -313,7 +288,7 @@ create table comment (
anonymous bool not null,
name text, -- null means anonymous
website text,
- created timestamp not null default ms_current_timestamp(),
+ created timestamp not null default current_timestamp,
confirmed timestamp,
text text not null, -- as entered by comment author
photo bytea,
@@ -322,9 +297,9 @@ create table comment (
or state = 'confirmed'
or state = 'hidden'
),
- cobrand text not null default '' check (cobrand ~* '^[a-z0-9]*$'),
+ cobrand text not null default '' check (cobrand ~* '^[a-z0-9_]*$'),
lang text not null default 'en-gb',
- cobrand_data text not null default '' check (cobrand_data ~* '^[a-z0-9]*$'), -- Extra data used in cobranded versions of the site
+ 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 (
@@ -361,7 +336,7 @@ create table token (
scope text not null,
token text not null,
data bytea not null,
- created timestamp not null default ms_current_timestamp(),
+ created timestamp not null default current_timestamp,
primary key (scope, token)
);
@@ -391,9 +366,9 @@ create table alert (
user_id int references users(id) not null,
confirmed integer not null default 0,
lang text not null default 'en-gb',
- cobrand text not null default '' check (cobrand ~* '^[a-z0-9]*$'),
- cobrand_data text not null default '' check (cobrand_data ~* '^[a-z0-9]*$'), -- Extra data used in cobranded versions of the site
- whensubscribed timestamp not null default ms_current_timestamp(),
+ cobrand text not null default '' check (cobrand ~* '^[a-z0-9_]*$'),
+ cobrand_data text not null default '' check (cobrand_data ~* '^[a-z0-9_]*$'), -- Extra data used in cobranded versions of the site
+ whensubscribed timestamp not null default current_timestamp,
whendisabled timestamp default null
);
create index alert_user_id_idx on alert ( user_id );
@@ -405,7 +380,7 @@ create index alert_whensubscribed_confirmed_cobrand_idx on alert(whensubscribed,
create table alert_sent (
alert_id integer not null references alert(id),
parameter text, -- e.g. Update ID for new updates
- whenqueued timestamp not null default ms_current_timestamp()
+ whenqueued timestamp not null default current_timestamp
);
create index alert_sent_alert_id_parameter_idx on alert_sent(alert_id, parameter);
@@ -450,9 +425,10 @@ create table admin_log (
),
object_id integer not null,
action text not null,
- whenedited timestamp not null default ms_current_timestamp(),
+ whenedited timestamp not null default current_timestamp,
user_id int references users(id) null,
- reason text not null default ''
+ reason text not null default '',
+ time_spent int not null default 0
);
create table moderation_original_data (
@@ -468,7 +444,7 @@ create table moderation_original_data (
anonymous bool not null,
-- Metadata
- created timestamp not null default ms_current_timestamp()
+ created timestamp not null default current_timestamp
);
create table user_body_permissions (
@@ -482,3 +458,12 @@ create table user_body_permissions (
),
unique(user_id, body_id, permission_type)
);
+
+create table response_templates (
+ id serial not null primary key,
+ body_id int references body(id) not null,
+ title text not null,
+ text text not null,
+ created timestamp not null default current_timestamp,
+ unique(body_id, title)
+);
diff --git a/db/schema_0032-moderation.sql b/db/schema_0032-moderation.sql
index b3caded1e..79be3a4c9 100644
--- a/db/schema_0032-moderation.sql
+++ b/db/schema_0032-moderation.sql
@@ -1,6 +1,8 @@
-- was created in previous versions of this branch
DROP TABLE IF EXISTS moderation_log;
+BEGIN;
+
alter table admin_log add column
user_id int references users(id) null;
@@ -34,3 +36,5 @@ create table user_body_permissions (
),
unique(user_id, body_id, permission_type)
);
+
+COMMIT;
diff --git a/db/schema_0033-speed-up-nearby.sql b/db/schema_0033-speed-up-nearby.sql
new file mode 100644
index 000000000..845f3c22e
--- /dev/null
+++ b/db/schema_0033-speed-up-nearby.sql
@@ -0,0 +1,47 @@
+begin;
+
+create index problem_radians_latitude_longitude_idx on problem(radians(latitude), radians(longitude));
+
+drop function angle_between(double precision, double precision);
+
+create or replace function problem_find_nearby(double precision, double precision, double precision)
+ returns setof problem_nearby_match as
+ -- Write as SQL function so that we don't have to construct a temporary
+ -- table or results set in memory. That means we can't check the values of
+ -- the parameters, sadly.
+ -- Through sheer laziness, just use great-circle distance; that'll be off
+ -- by ~0.1%.
+ -- We index locations on lat/lon so that we can select the locations which lie
+ -- within a wedge of side about 2 * DISTANCE. That cuts down substantially
+ -- on the amount of work we have to do.
+ -- http://janmatuschek.de/LatitudeLongitudeBoundingCoordinates
+'
+ -- 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 (
+ radians(longitude) > radians($2) - asin(sin($3 / R_e())/cos(radians($1)))
+ and radians(longitude) < radians($2) + asin(sin($3 / R_e())/cos(radians($1)))
+ )
+ )
+ -- 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 stable;
+
+commit;
diff --git a/db/schema_0034-remove-ms_current_timestamp.sql b/db/schema_0034-remove-ms_current_timestamp.sql
new file mode 100644
index 000000000..347099877
--- /dev/null
+++ b/db/schema_0034-remove-ms_current_timestamp.sql
@@ -0,0 +1,16 @@
+begin;
+
+drop table debugdate;
+
+ALTER TABLE problem ALTER COLUMN created SET DEFAULT current_timestamp;
+ALTER TABLE problem ALTER COLUMN lastupdate SET DEFAULT current_timestamp;
+ALTER TABLE comment ALTER COLUMN created SET DEFAULT current_timestamp;
+ALTER TABLE token ALTER COLUMN created SET DEFAULT current_timestamp;
+ALTER TABLE alert ALTER COLUMN whensubscribed SET DEFAULT current_timestamp;
+ALTER TABLE alert_sent ALTER COLUMN whenqueued SET DEFAULT current_timestamp;
+ALTER TABLE admin_log ALTER COLUMN whenedited SET DEFAULT current_timestamp;
+ALTER TABLE moderation_original_data ALTER COLUMN created SET DEFAULT current_timestamp;
+
+drop function ms_current_timestamp();
+
+commit;
diff --git a/db/schema_0035-bodies_str-tidying.sql b/db/schema_0035-bodies_str-tidying.sql
new file mode 100644
index 000000000..f5e1dbbdd
--- /dev/null
+++ b/db/schema_0035-bodies_str-tidying.sql
@@ -0,0 +1,31 @@
+begin;
+
+alter table problem add bodies_missing text;
+
+update problem
+ set bodies_missing = split_part(bodies_str, '|', 2),
+ bodies_str = split_part(bodies_str, '|', 1)
+ where bodies_str like '%|%';
+
+create index problem_bodies_str_array_idx on problem USING gin(regexp_split_to_array(bodies_str, ','));
+
+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'',
+ ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'',
+ ''internal referral'' ) 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 in
+ (''confirmed'', ''investigating'', ''planned'', ''in progress'',
+ ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'',
+ ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'',
+ ''internal referral'' ) AND
+ (regexp_split_to_array(bodies_str, '','') && ARRAY[?] or bodies_str is null) and
+ areas like ''%,''||?||'',%'''
+ WHERE ref = 'ward_problems';
+
+commit;
diff --git a/db/schema_0036-add-underscore-to-cobrand.sql b/db/schema_0036-add-underscore-to-cobrand.sql
new file mode 100644
index 000000000..7bc381e8e
--- /dev/null
+++ b/db/schema_0036-add-underscore-to-cobrand.sql
@@ -0,0 +1,17 @@
+BEGIN;
+
+ALTER TABLE problem DROP CONSTRAINT problem_cobrand_check;
+ALTER TABLE problem DROP CONSTRAINT problem_cobrand_data_check;
+ALTER TABLE comment DROP CONSTRAINT comment_cobrand_check;
+ALTER TABLE comment DROP CONSTRAINT comment_cobrand_data_check;
+ALTER TABLE alert DROP CONSTRAINT alert_cobrand_check;
+ALTER TABLE alert DROP CONSTRAINT alert_cobrand_data_check;
+
+ALTER TABLE problem ADD CONSTRAINT problem_cobrand_check CHECK (cobrand ~* '^[a-z0-9_]*$');
+ALTER TABLE problem ADD CONSTRAINT problem_cobrand_data_check CHECK (cobrand_data ~* '^[a-z0-9_]*$');
+ALTER TABLE comment ADD CONSTRAINT comment_cobrand_check CHECK (cobrand ~* '^[a-z0-9_]*$');
+ALTER TABLE comment ADD CONSTRAINT comment_cobrand_data_check CHECK (cobrand_data ~* '^[a-z0-9_]*$');
+ALTER TABLE alert ADD CONSTRAINT alert_cobrand_check CHECK (cobrand ~* '^[a-z0-9_]*$');
+ALTER TABLE alert ADD CONSTRAINT alert_cobrand_data_check CHECK (cobrand_data ~* '^[a-z0-9_]*$');
+
+COMMIT;
diff --git a/db/schema_0037-response-templates.sql b/db/schema_0037-response-templates.sql
new file mode 100644
index 000000000..4534e1e84
--- /dev/null
+++ b/db/schema_0037-response-templates.sql
@@ -0,0 +1,8 @@
+create table response_templates (
+ id serial not null primary key,
+ body_id int references body(id) not null,
+ title text not null,
+ text text not null,
+ created timestamp not null default current_timestamp,
+ unique(body_id, title)
+);
diff --git a/db/schema_0038-time-spent.sql b/db/schema_0038-time-spent.sql
new file mode 100644
index 000000000..093eb4086
--- /dev/null
+++ b/db/schema_0038-time-spent.sql
@@ -0,0 +1,2 @@
+alter table admin_log
+ add column time_spent int not null default 0;
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-24 09:38:09 +0000