# repos.sql repo_rights.sql repo_guests.sql repo_grps.sql repo_stats.sql

DROP TABLE %repo_xusers% ;
DROP TABLE %repo_stats% ;
DROP TABLE %repo_group_members% CASCADE ;
DROP TABLE %repo_groups% CASCADE ;
DROP TABLE %repo_rights% ;
DROP TABLE %repos% ;

DROP TABLE %repo_guests% ;
DROP TABLE %repo_mods% ;
DROP TABLE %repo_roots% ;

CREATE TABLE %repo_mods%
( mod text primary key
) ;

CREATE TABLE %repo_roots%
( pid  text primary key
, actv bool NOT NULL DEFAULT FALSE
) ;

CREATE TABLE %repos%
( id     serial primary key
, side   text   NOT NULL
, name   text   NOT NULL 
, ownr   text   NOT NULL
, dscr   text   NOT NULL DEFAULT ''
, wtch   bool   NOT NULL DEFAULT FALSE
, publ   bool   NOT NULL DEFAULT FALSE
, lock   bool   NOT NULL DEFAULT FALSE
, nname  text   UNIQUE
, creat  date   NOT NULL DEFAULT now()
, unique ( side, ownr, name )
) ;

CREATE TABLE %repo_rights%
( id  serial primary key
, pid text NOT NULL
, rid integer NOT NULL REFERENCES %repos% ( id )
, mod text NOT NULL REFERENCES %repo_mods% ( mod )
, man bool NOT NULL default false
, wat bool NOT NULL default false
, unique ( pid, rid )
) ;

CREATE TABLE %repo_guests%
( id   serial primary key
, login  text NOT NULL
, passwd text NOT NULL
, name   text NOT NULL
, email  text NOT NULL UNIQUE
, parent text NOT NULL
, creat  date NOT NULL DEFAULT now()
, nwmail text
, nwcode text
) ;

CREATE TABLE %repo_groups%
( gid serial primary key
, name text NOT NULL
, ownr text NOT NULL
, creat date NOT NULL DEFAULT now()
, UNIQUE ( name )
) ;

CREATE TABLE %repo_group_members%
( id serial primary key
, pid text NOT NULL
, gid integer NOT NULL REFERENCES %repo_groups% ( gid )
, mem bool NOT NULL DEFAULT false
, man bool NOT NULL DEFAULT false
, UNIQUE ( pid, gid )
) ;

CREATE TABLE %repo_stats%
( id   serial primary key
, rid  integer NOT NULL REFERENCES %repos% ( id )
, rev  integer NOT NULL
, size integer NOT NULL
, date text    NOT NULL
, time integer NOT NULL
) ;

CREATE TABLE %repo_xusers%
( id  serial primary key
, pid text NOT NULL UNIQUE
) ;

# inserts -----------------------------------------------

INSERT INTO %repo_mods% ( mod ) VALUES ( 'ro' )  ;
INSERT INTO %repo_mods% ( mod ) VALUES ( 'rw' )  ;
INSERT INTO %repo_mods% ( mod ) VALUES ( 'nx' ) ;

# grants ------------------------------------------------

GRANT INSERT, DELETE ON %repo_roots% TO %db_rupd% ;
GRANT SELECT, UPDATE ON %repo_roots% TO %db_user% ;

GRANT ALL ON %repos% TO %db_user% ;
GRANT ALL ON sequence %repos%_id_seq TO %db_user% ;

GRANT SELECT ON %repo_mods% TO %db_user% ;

GRANT ALL ON %repo_rights% TO %db_user% ;
GRANT ALL ON sequence %repo_rights%_id_seq TO %db_user% ;

GRANT ALL ON %repo_guests% TO %db_user% ;
GRANT ALL ON sequence %repo_guests%_id_seq TO %db_user% ;

GRANT ALL ON %repo_stats% TO %db_user% ;
GRANT ALL ON sequence %repo_stats%_id_seq TO %db_user% ;

GRANT ALL ON %repo_group_members% TO %db_user% ;
GRANT ALL ON sequence %repo_group_members%_id_seq TO %db_user% ;

GRANT ALL ON %repo_groups% TO %db_user% ;
GRANT ALL ON sequence %repo_groups%_gid_seq TO %db_user% ;

GRANT ALL ON %repo_xusers% TO %db_user% ;
GRANT ALL ON sequence %repo_xusers%_id_seq TO %db_user% ;

