diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/downgrade_0067---0066.sql | 6 | ||||
| -rwxr-xr-x | db/rerun_dbic_loader.pl | 5 | ||||
| -rw-r--r-- | db/schema.sql | 16 | ||||
| -rw-r--r-- | db/schema_0067-user-roles.sql | 19 |
4 files changed, 45 insertions, 1 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/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..93d73ab00 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -73,6 +73,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, 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; |