diff options
Diffstat (limited to 'db/schema.sql')
| -rw-r--r-- | db/schema.sql | 91 |
1 files changed, 38 insertions, 53 deletions
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) +); |