database.sql

--- Database objects

--- Support for multiple Github servers
create table ghservers
(
  ghserverid int unique not null,
  serveruri varchar(255) not null
) organize by row;

--- insert row for the public Github
insert into ghservers values(1,'github.com');

--- Repository data
create table repos
(
  rid int unique not null generated by default as identity (start with 1000, increment by 1),
  rname varchar(255) not null,
  ghserverid int not null,
  oid int not null,  --- this is the org or user
  schedule int not null
) organize by row;

--- Github traffic statistics for views and clones
create table repotraffic
(
  rid int not null,
  tdate date not null,
  viewcount int not null default 0,
  vuniques int not null default 0,
  clonecount int not null default 0,
  cuniques int not null default 0
) organize by row;

--- indexes to improve tdate-based search
create index repotraffic_ix_rid_tdate on repotraffic(rid,tdate);
create index repotraffic_ix_tdate on repotraffic(tdate);

--- The tenant, i.e. the user which accesses Github.
--- Hence the ghuser (Github user) name and access token
create table tenants
(
  tid int unique not null generated by default as identity (start with 1000, increment by 1),
  fname varchar(100),
  lname varchar(100),
  email varchar(255),
  ghuser varchar(255) not null,
  ghtoken varchar(255) not null
) organize by row;


--- Github user or org, needed for full repository name
create table ghorgusers
(
  oid int unique not null generated by default as identity (start with 1000, increment by 1),
  username varchar(255) not null,
  usertype char(1),
  email varchar(255),
  blog varchar(255)
) organize by row;

--- relationship between tenants and repos
--- All the repositories a tenant wants statistics for
create table tenantrepos
(
  tid int not null,
  rid int not null
)  organize by row;

--- info about last data collection runs
--- IDEA: Could extend this to capture elapsed run time to spot any issues
create table systemlog
(
  tid int not null,
  completed timestamp,
  numrepos int,
  state varchar(255)
) organize by row;

--- system administration users, those working with the Python app
create table adminusers
(
  aid int unique not null generated by default as identity (start with 1000, increment by 1),
  auser varchar(255) not null,
  email varchar(255) not null
) organize by row;

--- who has which roles, if admin or maintainer then entry only here
create table adminroles
(
  aid int not null,
  role int not null
) organize by row;

--- if role regarding specific tenant or specific repository, then
--- entry or entries here, too
create table admintenantreporoles
(
  aid int not null,
  tid int not null,
  role int not null,
  rid int
) organize by row;

--- What roles are available? System metadata, not really needed
create table adminrolevalues
(
  role int unique not null,
  desc varchar(255) not null
) organize by row;

--- initialize those FYI values
insert into adminrolevalues values (1,'admin'),(2,'sysmaint'),(4,'tenant'),(8,'tenantstats'),(16,'repostats');

--- View to list available repos per role and adminuser.
--- Identification is by email of system user (adminusers)
--- UNION ALL to look for tenant-level and individual repositories
create view v_adminuserrepos as
(
  select tr.rid, au.aid,au.email, atrr.role from  admintenantreporoles atrr, adminusers au, adminroles ar, tenantrepos tr
  where ar.aid=au.aid
  and atrr.aid=au.aid
  and tr.tid=atrr.tid
  and (bitand(atrr.role,8)>0 or bitand(atrr.role,4)>0)
  union all
  select tr.rid, au.aid,au.email, atrr.role from  admintenantreporoles atrr, adminusers au, adminroles ar, tenantrepos tr
  where ar.aid=au.aid
  and atrr.aid=au.aid
  and tr.tid=atrr.tid
  and tr.rid=atrr.rid
  and (bitand(atrr.role,16)>0)
);

--- view to show statistics for all repos and include full organisation name
create or replace view v_repostats as (
  select r.rid, username as orgname, rname as reponame, tdate, viewcount, vuniques, clonecount, cuniques from repotraffic rt, ghorgusers gu, repos r where r.oid=gu.oid and rt.rid=r.rid
);

--- view to include the full name into the available repos list
create view v_adminrepolist as
(
  select r.rid, gu.username as orgname, r.rname as reponame, v.email from ghorgusers gu, repos r, v_adminuserrepos v where r.oid=gu.oid and v.rid=r.rid
)