-- This file has commands for creating the tables in the oasis database
-- used for consistancy checking.

-- Bruno Wolff III
-- July 4, 2002

-- inst - instance table
--   code - unique internal code
--   id - external instance code
--   descr - instance descrription
--   instid - oracle instance name
--   host - orcale server domain name
--   port - oracle server port
--   aphost - ps app server domain name
--   apport - ps app server port

create table inst (
  code serial primary key,
  id text unique not null
    constraint bad_id check (id ~ '^[A-Z0-9]+$'),
  descr text
    constraint bad_descr check (descr ~ '^[\041-\176]+( [\041-\176]+)*$'),
  instid text not null
    constraint bad_instid check (instid ~ '^[-a-zA-Z0-9._]+$'),
  host text not null
    constraint bad_host check (host ~ '^[-a-zA-Z0-9.]+$'),
  port int4 not null
    constraint bad_port check (port >= 0 and port < 65536),
  aphost text not null
    constraint bad_aphost check (host ~ '^[-a-zA-Z0-9.]+$'),
  apport int4 not null
    constraint bad_apport check (apport >= 0 and apport < 65536)
);

-- Catch impossible app server config

create unique index inst_u_app on inst (aphost, apport);

-- Catch impossible db server config

create unique index inst_u_db on inst (host, port, instid);

grant select, insert, update, delete on inst to group ps_admin;
grant select on inst to group ps_security;

-- oper - operator table
--   code - unique internal code
--   id - peoplesoft operator ID
--   emplid - Internal (to PS) person ID number
--   descr - the operator's name

-- Operators should have matching alpha accounts and peoplesoft
-- oprids (with oprtype = 0).

create table oper (
  code serial primary key,
  id text unique not null
    constraint bad_id check (id ~ '^[A-Z0-9]+$'),
  emplid text
    constraint bad_emplid check (emplid ~ '^[A-Z0-9]+$'),
  descr text
    constraint bad_descr check (descr ~ '^[\041-\176]+( [\041-\176]+)*$')
);

grant select, insert, update, delete on oper to group ps_admin;
grant select on oper to group ps_security;

-- class - class table
--   code - unique internal code
--   id - peoplesoft class ID
--   descr - class descrription

-- Classes should have matching peoplesoft oprids (with oprtype = 1).

create table class (
  code serial primary key,
  id text unique not null
    constraint bad_id check (id ~ '^[A-Z0-9]+$'),
  descr text
    constraint bad_descr check (descr ~ '^[\041-\176]+( [\041-\176]+)*$')
);

grant select, insert, update, delete on class to group ps_admin;
grant select on class to group ps_security;

-- attr - primary class attributes table
--   code - internal attribute code
--   id - external attribute code
--   descr - description of the function allowed by the attribute

create table attr (
  code serial primary key,
  id text unique not null
    constraint bad_id check (id ~ '^[A-Z0-9]+$'),
  descr text
    constraint bad_descr check (descr ~ '^[\041-\176]+( [\041-\176]+)*$')
);

grant select, insert, update, delete on attr to group ps_admin;
grant select on attr to group ps_security;

-- imply - implied attributes table
--   attrcode - attribute granted by a role
--   implcode - other attribute implied (covered) by the above attribute

-- This table is not to be used recursively. All recursively covered
-- attributes should be included. This table must be reflexive.
-- To work properly this relation must be its own transitive closure.

create table imply (
  attrcode int4 references attr (code),
  implcode int4 references attr (code),
  primary key (attrcode, implcode)
);

-- The foreign key is used to force the imply table to be reflexive.
-- Since both imply and attr reference each other, one of the references
-- must be deferred and it needs to be this one to do things easily.

alter table attr add
  foreign key (code, code) references imply (attrcode, implcode) deferrable;

grant select, insert, update, delete on imply to group ps_admin;
grant select on imply to group ps_security;

-- oracle - Oracle access modes
--   code - internal access mode code
--   id - external access mode code
--   descr - description of the access mode

create table oracle (
  code serial primary key,
  id text unique not null
    constraint bad_id check (id ~ '^[A-Z0-9]+$'),
  descr text
    constraint bad_descr check (descr ~ '^[\041-\176]+( [\041-\176]+)*$')
);

grant select, insert, update, delete on oracle to group ps_admin;
grant select on oracle to group ps_security;

-- prim - list of which classes are primary classes

create table prim (
  code int4 primary key references class
);

grant select, insert, update, delete on prim to group ps_admin;
grant select on prim to group ps_security;

-- primattr - primary class attribute table
--   primcode - primary class used to provide authorization combination
--   attrcode - attribute authorized by this primary class

create table primattr (
  primcode int4 references prim (code),
  attrcode int4 references attr (code),
  primary key (primcode, attrcode)
);

grant select, insert, update, delete on primattr to group ps_admin;
grant select on primattr to group ps_security;

-- role - role table
--   code - unique internal code
--   id - external role code
--   descr - role description

create table role (
  code serial primary key,
  id text unique not null
    constraint bad_id check (id ~ '^[A-Z0-9]+$'),
  descr text
    constraint bad_descr check (descr ~ '^[\041-\176]+( [\041-\176]+)*$')
);

grant select, insert, update, delete on role to group ps_admin;
grant select on role to group ps_security;

-- base - list of which roles are base roles

create table base (
  code int4 primary key references role
);

grant select, insert, update, delete on base to group ps_admin;
grant select on base to group ps_security;

-- cust - data custodian table
--   code - unique internal code
--   id - external data custodian code
--   descr - role description
--   email - local part of custodian email reflector address

create table cust (
  code serial primary key,
  id text unique not null
    constraint bad_id check (id ~ '^[A-Z0-9]+$'),
  descr text
    constraint bad_descr check (descr ~ '^[\041-\176]+( [\041-\176]+)*$'),
  email text
    constraint bad_email check (email ~ '^[-a-z0-9_]+$')
);

grant select, insert, update, delete on cust to group ps_admin;
grant select on cust to group ps_security;

-- operrole - list of roles for operators by instance

create table operrole (
  opercode int4 references oper (code),
  rolecode int4 references role (code),
  instcode int4 references inst (code),
  primary key (instcode, opercode, rolecode)
);

grant select, insert, update, delete on operrole to group ps_admin;
grant select on operrole to group ps_security;

-- roleclass - list of classes for roles by instance

create table roleclass (
  rolecode int4 references role (code),
  classcode int4 references class (code),
  instcode int4 references inst (code),
  primary key (instcode, rolecode, classcode)
);

grant select, insert, update, delete on roleclass to group ps_admin;
grant select on roleclass to group ps_security;

-- roleattr - list of primary class attributes for roles

create table roleattr (
  rolecode int4 references role (code),
  attrcode int4 references attr (code),
  instcode int4 references inst (code),
  primary key (instcode, rolecode, attrcode)
);

grant select, insert, update, delete on roleattr to group ps_admin;
grant select on roleattr to group ps_security;

-- roleora - list of Oracle access modes for roles

create table roleora (
  rolecode int4 references role (code),
  oracode int4 references oracle (code),
  instcode int4 references inst (code),
  primary key (instcode, rolecode, oracode)
);

grant select, insert, update, delete on roleora to group ps_admin;
grant select on roleora to group ps_security;

-- classcust - list of data custodians for classes

create table classcust (
  classcode int4 references class (code),
  custcode int4 references cust (code),
  primary key (classcode, custcode)
);

grant select, insert, update, delete on classcust to group ps_admin;
grant select on classcust to group ps_security;

-- rolecust - list of data custodians for roles

create table rolecust (
  rolecode int4 references role (code),
  custcode int4 references cust (code),
  primary key (rolecode, custcode)
);

grant select, insert, update, delete on rolecust to group ps_admin;
grant select on rolecust to group ps_security;

-- sharedef - share definition table
--   code - unique internal code
--   id - external instance code
--   descr - instance descrription

create table sharedef (
  code serial primary key,
  id text unique not null
    constraint bad_id check (id ~ '^[A-Z0-9_]+$'),
  descr text
    constraint bad_descr check (descr ~ '^[\041-\176]+( [\041-\176]+)*$')
);

grant select, insert, update, delete on sharedef to group ps_admin;
grant select on sharedef to group ps_security;

-- accessdef - share access mode definitions
--   code - unique internal code
--   id - external instance code
--   descr - instance descrription

create table accessdef (
  code serial primary key,
  id text unique not null
    constraint bad_id check (id ~ '^[A-Z0-9]+$'),
  descr text
    constraint bad_descr check (descr ~ '^[\041-\176]+( [\041-\176]+)*$')
);

grant select, insert, update, delete on accessdef to group ps_admin;
grant select on accessdef to group ps_security;

-- share - share access allowed by inst - role

create table share (
  sharecode int4 references sharedef (code),
  instcode int4 references inst (code),
  rolecode int4 references role (code),
  accesscode int4 references accessdef (code),
  primary key (sharecode, instcode, rolecode, accesscode)
);

grant select, insert, update, delete on share to group ps_admin;
grant select on share to group ps_security;

-- operprim - view to provide a primary class for an operator on an instance

-- A primary class is suitable if it provides all of the attributes listed
-- by an operator's roles and no attributes not listed by any of their roles.
-- There are special handling rules for attributes that imply other attributes.

-- The limit 1 clause is used to pick one primary class if there are
-- more than one suitable classes (which there shouldn't be) to allow
-- the view to be used in more places (where at most one row can be returned).
-- The join with class and ordering by class id is to pick a consistant
-- primary class in the case where more than one class is suitable as long
-- as the externally exposed information (which doesn't include any of the
-- internal codes used as primary keys) stays the same.

create view operprim (opercode, primcode, instcode)
  as select distinct on (inst.code, oper.code)
    oper.code, prim.code, inst.code
    from oper, prim, inst, class
    where
      prim.code = class.code and
      exists (
	select * from operrole
	  where
	    operrole.instcode = inst.code and
	    operrole.opercode = oper.code
      ) and
      not exists (
        select roleattr.attrcode from operrole, roleattr
          where
	    operrole.opercode = oper.code and
	    operrole.instcode = inst.code and
            roleattr.rolecode = operrole.rolecode and
            roleattr.instcode = operrole.instcode
        except
        select imply.implcode from primattr, imply
          where
            primattr.primcode = prim.code and
            primattr.attrcode = imply.attrcode
      ) and
      not exists (
        select primattr.attrcode from primattr
          where primattr.primcode = prim.code
        except
        select imply.implcode from operrole, roleattr, imply
          where
	    operrole.opercode = oper.code and
	    operrole.instcode = inst.code and
            roleattr.rolecode = operrole.rolecode and
            roleattr.instcode = operrole.instcode and
            roleattr.attrcode = imply.attrcode
      )
    order by inst.code, oper.code, class.id;

grant select on operprim to group ps_admin;
grant select on operprim to group ps_security;