error.sql

application/sql

Download

create table entity(
    entity_id BIGSERIAL PRIMARY KEY
);

create table permission
(
    entity_type VARCHAR NOT NULL,
    permission  varchar NOT NULL,
    UNIQUE (entity_type, permission)
);

create table access
(
    id              BIGSERIAL PRIMARY KEY,
    entity_id       BIGINT    NOT NULL REFERENCES entity (entity_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
    content_id      BIGINT    NOT NULL REFERENCES entity (entity_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
    entity_type     VARCHAR   NOT NULL,
    permission      varchar   NOT NULL,
    FOREIGN KEY (entity_type, permission) REFERENCES permission (entity_type, permission),
    UNIQUE (entity_id, content_id, permission) DEFERRABLE INITIALLY DEFERRED
);

insert into entity(entity_id) values(nextval('entity_entity_id_seq'));

insert into permission(entity_type, permission)
VALUES ('PERSON', 'VACATION_EDIT')
ON CONFLICT DO NOTHING;

INSERT INTO access (entity_id, content_id, entity_type, permission)
VALUES (1, 1, 'PERSON', 'VACATION_EDIT');


alter table access
    add column start_timestamp    timestamp not null DEFAULT CURRENT_TIMESTAMP,
    add column end_timestamp      timestamp,
    add column tsrange            TSRANGE   NOT NULL GENERATED ALWAYS AS (tsrange(start_timestamp, end_timestamp, '[)')) STORED
;