--
-- DocumentStore Database creation
--

-- $Id$

CREATE TABLE PEOPLE (
        id      serial          PRIMARY KEY,
        nick    varchar(16)     UNIQUE NOT NULL,
        passwd  varchar(14)     NOT NULL,
        name    varchar(64),
        surname varchar(64)     NOT NULL,
        email   varchar(96)     UNIQUE NOT NULL
) ;

CREATE TABLE GROUPS (
        id              serial          PRIMARY KEY,
        name            varchar(32)     UNIQUE NOT NULL,
        deflevel        smallint        DEFAULT 15
) ;

CREATE TABLE PG (
        groupID integer         REFERENCES GROUPS (id),
        userID  integer         REFERENCES PEOPLE (id),
        level   smallint,
        CHECK   (level BETWEEN 0 AND 32)
) ;

CREATE TABLE TYPES (
        id      serial          PRIMARY KEY,
        descr   varchar(64)     UNIQUE NOT NULL,
        mime    varchar(96)     DEFAULT 'application/octet-stream' NOT NULL
) ;

CREATE TABLE STATUSES (
        id      serial          PRIMARY KEY,
        descr   varchar(64)     UNIQUE NOT NULL
) ;

CREATE TABLE DOCUMENTS (
        id              serial          PRIMARY KEY,
        title           varchar(255)    NOT NULL,
        type            integer         REFERENCES TYPES (id),
        published       date            NOT NULL,
        updated         date            NOT NULL,
        descr           varchar(200),
        url             varchar(128)    UNIQUE NOT NULL,
        status          integer         REFERENCES STATUSES (id)
) ;

CREATE TABLE AUTHORS (
        id              serial          PRIMARY KEY,
        name            varchar(128)    UNIQUE NOT NULL
) ;

CREATE TABLE AD (
        authorID        integer         REFERENCES AUTHORS (id),
        docID           integer         REFERENCES DOCUMENTS (id)
) ;

CREATE TABLE SECTIONS (
        id              serial          PRIMARY KEY,
        descr           varchar(64)     UNIQUE NOT NULL
) ;

CREATE TABLE SD (
        sectionID       integer         REFERENCES SECTIONS (id),
        docID           integer         REFERENCES DOCUMENTS (id)
) ;

CREATE TABLE ACCESS_LIST (
        groupID         integer         REFERENCES GROUPS (id),
        level           smallint,
        docID           integer         REFERENCES DOCUMENTS (id),
        CHECK           (level BETWEEN 0 AND 32)
) ;