Emergency Response and Rescue Management System - Tool to manage aid organizations and fire departments https://errms.dev
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 
 
 

235 lines
7.0 KiB

create table __diesel_schema_migrations
(
version varchar(50) not null
constraint __diesel_schema_migrations_pkey
primary key,
run_on timestamp default CURRENT_TIMESTAMP not null
);
create table users
(
id uuid default uuid_generate_v1() not null
constraint pk___users___id
primary key,
password text,
email text
);
create table communication_types
(
id uuid default uuid_generate_v1() not null
constraint pk___communication_types___id
primary key,
name text not null
);
create table addresses
(
id uuid default uuid_generate_v1() not null
constraint addresses_pk
primary key,
title text,
street text not null,
number text not null,
zipcode text not null,
city text not null,
geo_location point
);
create table entities
(
entity_id uuid default uuid_generate_v1() not null
constraint entities_pk
primary key
);
create table members
(
entity_id uuid default uuid_generate_v1() not null
constraint pk___members___id
primary key
constraint members_entities_entity_id_fk
references entities
on update cascade on delete cascade,
users_id uuid
constraint fk___members___users_id___users
references users,
firstname text not null,
lastname text not null,
date_of_birth date,
sex smallint,
salutation text,
place_of_birth text,
academic_titles text,
personnel_number integer,
ui_language text
);
create unique index members_personnel_number_uindex
on members (personnel_number);
create table addresses_entities
(
address_id uuid not null
constraint addresses_entities_addresses_id_fk
references addresses
on update cascade on delete cascade,
entitiy_id uuid not null
constraint addresses_entities_entities_entity_id_fk
references entities
on update cascade on delete cascade,
constraint addresses_entities_pk
primary key (address_id, entitiy_id)
);
create table buildings
(
entity_id uuid default uuid_generate_v1() not null
constraint buildings_pk
primary key
constraint buildings_entities_entity_id_fk
references entities
on update cascade on delete cascade,
name text not null,
description text
);
create table vehicle_categories
(
id uuid default uuid_generate_v1() not null
constraint vehicle_categories_pk
primary key,
name text not null,
description text
);
create table vehicles
(
entity_id uuid default uuid_generate_v1() not null
constraint vehicles_pk
primary key
constraint vehicles_entities_entity_id_fk
references entities
on update cascade on delete cascade
constraint vehicles_vehicle_categories_id_fk
references vehicle_categories
on update cascade on delete set null,
identifier text not null,
numberplate text,
description text,
vehicle_category uuid default uuid_generate_v1(),
next_inspection date,
is_operational boolean default true not null,
admissible_total_weight real,
required_license uuid
);
create table communication_targets
(
id uuid default uuid_generate_v1() not null
constraint pk___communication_targets___id
primary key
constraint communication_targets_entities_entity_id_fk
references entities
on update cascade on delete cascade,
entity text not null,
entity_id uuid not null,
com_type uuid not null
constraint fk___communication_target___type___communication_types
references communication_types,
value text not null,
description text,
visibility boolean default false not null
);
create table permissions
(
permission text not null
constraint permissions_pk
primary key,
description text
);
create table roles
(
id text not null
constraint roles_pk
primary key,
description text
);
create table roles_permissions
(
role_id text not null
constraint roles_permissions_roles_id_fk
references roles
on update cascade on delete cascade,
permission_id text not null
constraint roles_permissions_permissions_permission_fk
references permissions
on update cascade on delete cascade,
role_permission_id uuid default uuid_generate_v1() not null
constraint roles_permissions_pk_2
primary key
);
create unique index roles_permissions_role_permission_id_uindex
on roles_permissions (role_permission_id);
create table members_roles
(
member_id uuid not null
constraint members_roles_entities_entity_id_fk
references entities
on update cascade on delete cascade,
role_id text not null
constraint members_roles_roles_id_fk
references roles
on update cascade on delete cascade,
constraint members_roles_pk
primary key (member_id, role_id)
);
create table groups
(
entity_id uuid default uuid_generate_v1() not null
constraint groups_pk
primary key
constraint groups_entities_entity_id_fk
references entities
on update cascade on delete cascade,
group_name text not null,
group_description text
);
create unique index groups_group_name_uindex
on groups (group_name);
create table groups_entities
(
group_id uuid not null
constraint groups_entities_groups_group_id_fk
references groups
on update cascade on delete cascade,
entity_id uuid not null
constraint groups_entities_entities_entity_id_fk
references entities
on update cascade on delete cascade,
constraint groups_entities_pk
primary key (group_id, entity_id)
);
create table roles_permissions_context
(
role_permission_id uuid not null
constraint roles_permissions_contexts_roles_permissions_role_permission_id
references roles_permissions
on update cascade on delete cascade,
entity uuid not null
constraint roles_permissions_contexts_entities_entity_id_fk
references entities
on update cascade on delete cascade,
constraint roles_permissions_context_pk
primary key (role_permission_id, entity)
);