aboutsummaryrefslogtreecommitdiffstats
path: root/db
diff options
context:
space:
mode:
Diffstat (limited to 'db')
-rw-r--r--db/downgrade_0067---0066.sql6
-rw-r--r--db/downgrade_0068---0067.sql3
-rw-r--r--db/downgrade_0069---0068.sql12
-rw-r--r--db/downgrade_0070---0069.sql5
-rw-r--r--db/downgrade_0071---0070.sql18
-rw-r--r--db/downgrade_0072---0071.sql21
-rw-r--r--db/fixture.sql18
-rwxr-xr-xdb/rerun_dbic_loader.pl5
-rw-r--r--db/schema.sql34
-rw-r--r--db/schema_0067-user-roles.sql19
-rw-r--r--db/schema_0068-oidc_login.sql3
-rw-r--r--db/schema_0069-admin-log-types.sql17
-rw-r--r--db/schema_0070-alert-type-site-name.sql5
-rw-r--r--db/schema_0071-add-manifest-theme.sql28
-rw-r--r--db/schema_0072-add-staff-contact-state.sql23
15 files changed, 207 insertions, 10 deletions
diff --git a/db/downgrade_0067---0066.sql b/db/downgrade_0067---0066.sql
new file mode 100644
index 000000000..cb74315bd
--- /dev/null
+++ b/db/downgrade_0067---0066.sql
@@ -0,0 +1,6 @@
+BEGIN;
+
+DROP TABLE user_roles;
+DROP TABLE roles;
+
+COMMIT;
diff --git a/db/downgrade_0068---0067.sql b/db/downgrade_0068---0067.sql
new file mode 100644
index 000000000..c12c0374b
--- /dev/null
+++ b/db/downgrade_0068---0067.sql
@@ -0,0 +1,3 @@
+begin;
+alter table users drop column oidc_ids;
+commit;
diff --git a/db/downgrade_0069---0068.sql b/db/downgrade_0069---0068.sql
new file mode 100644
index 000000000..668258524
--- /dev/null
+++ b/db/downgrade_0069---0068.sql
@@ -0,0 +1,12 @@
+BEGIN;
+
+ALTER TABLE admin_log DROP CONSTRAINT admin_log_object_type_check;
+
+ALTER TABLE admin_log ADD CONSTRAINT admin_log_object_type_check CHECK (
+ object_type = 'problem'
+ OR object_type = 'update'
+ OR object_type = 'user'
+ OR object_type = 'moderation'
+);
+
+COMMIT;
diff --git a/db/downgrade_0070---0069.sql b/db/downgrade_0070---0069.sql
new file mode 100644
index 000000000..8e77adec9
--- /dev/null
+++ b/db/downgrade_0070---0069.sql
@@ -0,0 +1,5 @@
+BEGIN;
+
+UPDATE alert_type SET head_title = replace(head_title, '{{SITE_NAME}}', 'FixMyStreet');
+
+COMMIT;
diff --git a/db/downgrade_0071---0070.sql b/db/downgrade_0071---0070.sql
new file mode 100644
index 000000000..ebf206624
--- /dev/null
+++ b/db/downgrade_0071---0070.sql
@@ -0,0 +1,18 @@
+BEGIN;
+
+DROP TABLE manifest_theme;
+
+ALTER TABLE admin_log DROP CONSTRAINT admin_log_object_type_check;
+
+ALTER TABLE admin_log ADD CONSTRAINT admin_log_object_type_check CHECK (
+ object_type = 'problem'
+ OR object_type = 'update'
+ OR object_type = 'user'
+ OR object_type = 'moderation'
+ OR object_type = 'template'
+ OR object_type = 'body'
+ OR object_type = 'category'
+ OR object_type = 'role'
+);
+
+COMMIT;
diff --git a/db/downgrade_0072---0071.sql b/db/downgrade_0072---0071.sql
new file mode 100644
index 000000000..aa649f63d
--- /dev/null
+++ b/db/downgrade_0072---0071.sql
@@ -0,0 +1,21 @@
+BEGIN;
+
+ALTER TABLE contacts DROP CONSTRAINT contacts_state_check;
+
+ALTER TABLE contacts ADD CONSTRAINT contacts_state_check CHECK (
+ state = 'unconfirmed'
+ or state = 'confirmed'
+ or state = 'inactive'
+ or state = 'deleted'
+);
+
+ALTER TABLE contacts_history DROP CONSTRAINT contacts_history_state_check;
+
+ALTER TABLE contacts_history ADD CONSTRAINT contacts_history_state_check CHECK (
+ state = 'unconfirmed'
+ or state = 'confirmed'
+ or state = 'inactive'
+ or state = 'deleted'
+);
+
+COMMIT;
diff --git a/db/fixture.sql b/db/fixture.sql
index 840906223..379353d9f 100644
--- a/db/fixture.sql
+++ b/db/fixture.sql
@@ -26,7 +26,7 @@ insert into alert_type
item_table, item_where, item_order,
item_title, item_link, item_description, template)
values ('new_problems', '', '',
- 'New problems on FixMyStreet', '/', 'The latest problems reported by users',
+ 'New problems on {{SITE_NAME}}', '/', 'The latest problems reported by users',
'problem',
'problem.non_public = ''f'' and problem.state NOT IN
(''unconfirmed'', ''hidden'', ''partial'')',
@@ -40,7 +40,7 @@ insert into alert_type
item_table, item_where, item_order,
item_title, item_link, item_description, template)
values ('new_fixed_problems', '', '',
- 'Problems recently reported fixed on FixMyStreet', '/', 'The latest problems reported fixed by users',
+ 'Problems recently reported fixed on {{SITE_NAME}}', '/', 'The latest problems reported fixed by users',
'problem', 'problem.non_public = ''f'' and problem.state in (''fixed'', ''fixed - user'', ''fixed - council'')', 'lastupdate desc',
'{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem');
@@ -51,7 +51,7 @@ insert into alert_type
item_table, item_where, item_order,
item_title, item_link, item_description, template)
values ('local_problems', '', '',
- 'New local problems on FixMyStreet', '/', 'The latest local problems reported by users',
+ 'New local problems on {{SITE_NAME}}', '/', '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 NOT IN
(''unconfirmed'', ''hidden'', ''partial'')',
@@ -65,7 +65,7 @@ insert into alert_type
item_table, item_where, item_order,
item_title, item_link, item_description, template)
values ('local_problems_state', '', '',
- 'New local problems on FixMyStreet', '/', 'The latest local problems reported by users',
+ 'New local problems on {{SITE_NAME}}', '/', '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 (?)', 'created desc',
'{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby');
@@ -76,7 +76,7 @@ insert into alert_type
item_table, item_where, item_order,
item_title, item_link, item_description, template)
values ('postcode_local_problems', '', '',
- 'New problems near {{POSTCODE}} on FixMyStreet', '/', 'The latest local problems reported by users',
+ 'New problems near {{POSTCODE}} on {{SITE_NAME}}', '/', '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 NOT IN
(''unconfirmed'', ''hidden'', ''partial'')',
@@ -90,7 +90,7 @@ insert into alert_type
item_table, item_where, item_order,
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',
+ 'New problems near {{POSTCODE}} on {{SITE_NAME}}', '/', '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 (?)', 'created desc',
'{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby');
@@ -101,7 +101,7 @@ insert into alert_type
item_table, item_where, item_order,
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',
+ 'New problems to {{COUNCIL}} on {{SITE_NAME}}', '/reports', 'The latest problems for {{COUNCIL}} reported by users',
'problem',
'problem.non_public = ''f'' and problem.state NOT IN
(''unconfirmed'', ''hidden'', ''partial'') AND
@@ -117,7 +117,7 @@ insert into alert_type
item_table, item_where, item_order,
item_title, item_link, item_description, template)
values ('ward_problems', '', '',
- 'New problems for {{COUNCIL}} within {{WARD}} ward on FixMyStreet', '/reports',
+ 'New problems for {{COUNCIL}} within {{WARD}} ward on {{SITE_NAME}}', '/reports',
'The latest problems for {{COUNCIL}} within {{WARD}} ward reported by users',
'problem',
'problem.non_public = ''f'' and problem.state NOT IN
@@ -135,7 +135,7 @@ insert into alert_type
item_table, item_where, item_order,
item_title, item_link, item_description, template)
values ('area_problems', '', '',
- 'New problems within {{NAME}}''s boundary on FixMyStreet', '/reports',
+ 'New problems within {{NAME}}''s boundary on {{SITE_NAME}}', '/reports',
'The latest problems within {{NAME}}''s boundary reported by users', 'problem',
'problem.non_public = ''f'' and problem.state NOT IN
(''unconfirmed'', ''hidden'', ''partial'') AND
diff --git a/db/rerun_dbic_loader.pl b/db/rerun_dbic_loader.pl
index 1eff12d9e..9ee029668 100755
--- a/db/rerun_dbic_loader.pl
+++ b/db/rerun_dbic_loader.pl
@@ -27,7 +27,10 @@ my @tables_to_ignore = (
my $exclude = '^(?:' . join( '|', @tables_to_ignore ) . ')$';
make_schema_at(
- 'FixMyStreet::DB::Schema',
+ # Something funny here if you use FixMyStreet::DB::Schema, where it should be,
+ # as it tries to dump it twice and dies on reload; with this, it works, but
+ # then the changes to DB.pm need removing
+ 'FixMyStreet::DB',
{
debug => 0, # switch on to be chatty
dump_directory => './perllib', # edit files in place
diff --git a/db/schema.sql b/db/schema.sql
index 98005028c..ed21aded6 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -35,6 +35,7 @@ create table users (
title text,
twitter_id bigint unique,
facebook_id bigint unique,
+ oidc_ids text ARRAY,
area_ids integer ARRAY,
extra text
);
@@ -73,6 +74,22 @@ create unique index body_areas_body_id_area_id_idx on body_areas(body_id, area_i
ALTER TABLE users ADD CONSTRAINT users_from_body_fkey
FOREIGN KEY (from_body) REFERENCES body(id);
+-- roles table
+create table roles (
+ id serial not null primary key,
+ body_id integer not null references body(id) ON DELETE CASCADE,
+ name text,
+ permissions text ARRAY,
+ unique(body_id, name)
+);
+
+-- Record which role(s) each user holds
+create table user_roles (
+ id serial not null primary key,
+ role_id integer not null references roles(id) ON DELETE CASCADE,
+ user_id integer not null references users(id) ON DELETE CASCADE
+);
+
-- The contact for a category within a particular body
create table contacts (
id serial primary key,
@@ -83,6 +100,7 @@ create table contacts (
state = 'unconfirmed'
or state = 'confirmed'
or state = 'inactive'
+ or state = 'staff'
or state = 'deleted'
),
@@ -120,6 +138,7 @@ create table contacts_history (
state = 'unconfirmed'
or state = 'confirmed'
or state = 'inactive'
+ or state = 'staff'
or state = 'deleted'
),
@@ -428,6 +447,11 @@ create table admin_log (
or object_type = 'update'
or object_type = 'user'
or object_type = 'moderation'
+ or object_type = 'template'
+ or object_type = 'body'
+ or object_type = 'category'
+ or object_type = 'role'
+ or object_type = 'manifesttheme'
),
object_id integer not null,
action text not null,
@@ -541,3 +565,13 @@ CREATE TABLE state (
type text not null check (type = 'open' OR type = 'closed' OR type = 'fixed'),
name text not null unique
);
+
+CREATE TABLE manifest_theme (
+ id serial not null primary key,
+ cobrand text not null unique,
+ name text not null,
+ short_name text not null,
+ background_colour text,
+ theme_colour text,
+ images text ARRAY
+);
diff --git a/db/schema_0067-user-roles.sql b/db/schema_0067-user-roles.sql
new file mode 100644
index 000000000..d3d773105
--- /dev/null
+++ b/db/schema_0067-user-roles.sql
@@ -0,0 +1,19 @@
+BEGIN;
+
+-- roles table
+create table roles (
+ id serial not null primary key,
+ body_id integer not null references body(id) ON DELETE CASCADE,
+ name text,
+ permissions text ARRAY,
+ unique(body_id, name)
+);
+
+-- Record which role(s) each user holds
+create table user_roles (
+ id serial not null primary key,
+ role_id integer not null references roles(id) ON DELETE CASCADE,
+ user_id integer not null references users(id) ON DELETE CASCADE
+);
+
+COMMIT;
diff --git a/db/schema_0068-oidc_login.sql b/db/schema_0068-oidc_login.sql
new file mode 100644
index 000000000..aadbef4da
--- /dev/null
+++ b/db/schema_0068-oidc_login.sql
@@ -0,0 +1,3 @@
+begin;
+alter table users add column oidc_ids text ARRAY;
+commit;
diff --git a/db/schema_0069-admin-log-types.sql b/db/schema_0069-admin-log-types.sql
new file mode 100644
index 000000000..e4e9362ac
--- /dev/null
+++ b/db/schema_0069-admin-log-types.sql
@@ -0,0 +1,17 @@
+BEGIN;
+
+ALTER TABLE admin_log DROP CONSTRAINT admin_log_object_type_check;
+
+ALTER TABLE admin_log ADD CONSTRAINT admin_log_object_type_check CHECK (
+ object_type = 'problem'
+ OR object_type = 'update'
+ OR object_type = 'user'
+ OR object_type = 'moderation'
+ OR object_type = 'template'
+ OR object_type = 'body'
+ OR object_type = 'category'
+ OR object_type = 'role'
+);
+
+COMMIT;
+
diff --git a/db/schema_0070-alert-type-site-name.sql b/db/schema_0070-alert-type-site-name.sql
new file mode 100644
index 000000000..70a1337fd
--- /dev/null
+++ b/db/schema_0070-alert-type-site-name.sql
@@ -0,0 +1,5 @@
+BEGIN;
+
+UPDATE alert_type SET head_title = replace(head_title, 'FixMyStreet', '{{SITE_NAME}}');
+
+COMMIT;
diff --git a/db/schema_0071-add-manifest-theme.sql b/db/schema_0071-add-manifest-theme.sql
new file mode 100644
index 000000000..308d345f5
--- /dev/null
+++ b/db/schema_0071-add-manifest-theme.sql
@@ -0,0 +1,28 @@
+BEGIN;
+
+CREATE TABLE manifest_theme (
+ id serial not null primary key,
+ cobrand text not null unique,
+ name text not null,
+ short_name text not null,
+ background_colour text,
+ theme_colour text,
+ images text ARRAY
+);
+
+ALTER TABLE admin_log DROP CONSTRAINT admin_log_object_type_check;
+
+ALTER TABLE admin_log ADD CONSTRAINT admin_log_object_type_check CHECK (
+ object_type = 'problem'
+ OR object_type = 'update'
+ OR object_type = 'user'
+ OR object_type = 'moderation'
+ OR object_type = 'template'
+ OR object_type = 'body'
+ OR object_type = 'category'
+ OR object_type = 'role'
+ OR object_type = 'manifesttheme'
+);
+
+
+COMMIT;
diff --git a/db/schema_0072-add-staff-contact-state.sql b/db/schema_0072-add-staff-contact-state.sql
new file mode 100644
index 000000000..efaac2d20
--- /dev/null
+++ b/db/schema_0072-add-staff-contact-state.sql
@@ -0,0 +1,23 @@
+BEGIN;
+
+ALTER TABLE contacts DROP CONSTRAINT contacts_state_check;
+
+ALTER TABLE contacts ADD CONSTRAINT contacts_state_check CHECK (
+ state = 'unconfirmed'
+ or state = 'confirmed'
+ or state = 'inactive'
+ or state = 'staff'
+ or state = 'deleted'
+);
+
+ALTER TABLE contacts_history DROP CONSTRAINT contacts_history_state_check;
+
+ALTER TABLE contacts_history ADD CONSTRAINT contacts_history_state_check CHECK (
+ state = 'unconfirmed'
+ or state = 'confirmed'
+ or state = 'inactive'
+ or state = 'staff'
+ or state = 'deleted'
+);
+
+COMMIT;
generated by cgit v1.2.3 (git 2.25.1) at 2025-09-01 09:41:18 +0000