EinsatzOnline/sqlschema.sql

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)
);