-- the 'btrel' table  lists which tables are to be considered bi-temporal ones
-- each table listed here MUST
--
--  * NOT have any 'primary key' or 'foreign key' modifiers
--
--  * have the following fields AMMENDED to the normal table schema :
-- 
--	vt_begin timestamp(0) without time zone not null,
--	vt_end   timestamp(0) without time zone not null,
--	tt_start timestamp(0) without time zone not null,
--	tt_stop  timestamp(0) without time zone not null,
--	
--	btpk int not null,
--	
--	btid serial unique not null,
--
--    or 
--
--	btid int default nextval('<relation>_btid_seq') unique not null,
--
--    Note : btid must be a auto-increment type field, a serial type may be
--           used but the tuples will start ordering from '1'.  i've found
--           it's more natural to have this number start from '0' and
--           define my own sequence for each bi-temporal tabel to acheive this
--	     where each table's seq is name <relation>_btid_seq and it's
--           definition is such that it starts from '0'
--

drop table btrel;
create table btrel
(
  relname text,

  primary key (relname)
);


-- the btfield relations lists all the fields of each btrel
-- note that a position (0 based index) is also included to indicate the
-- field's position in the relation

drop table btfield;
create table btfield
(
  relname text,
  field text,
  pos int not null,

  constraint c check (pos >=0),
  primary key (relname, field),
  foreign key (relname)
   references btrel (relname)
);


-- th 'btpk' relation list those fields which are to be considered the 
-- primary keys of each btrel 

drop table btpk;
create table btpk
(
  relname text,
  field text,

  primary key (relname, field),
  foreign key (relname, field)
   references btfield (relname, field)
);

-- 'btfkactions' lists possible fk actions for bitemporal tables
-- currently the only supported action is 'cascade' and anything other than
-- is ignored

drop table btfkactions;
create table btfkactions
(
  action text,

  primary key (action)
);

--define bt fk actions - only cascade currenlty has any real meaning
insert into btfkactions values ('no action');
insert into btfkactions values ('restrict');
insert into btfkactions values ('cascade');
insert into btfkactions values ('set null');
insert into btfkactions values ('set default');


-- the 'btfk' relation enumerates all the foreign keys of the bi-tmeporal
-- tables listed in 'btrel'

drop table btfk;
create table btfk
(
  referent_relname text,
  referent_field text,
  referenced_relname text,
  referenced_field text,
  btfkno int not null,
  delete_action text not null,
  update_action text not null,

  primary key (referent_relname, referent_field,
               referenced_relname, referenced_field),
  foreign key (referent_relname, referent_field)
   references btfield (relname, field),
  foreign key (referenced_relname, referenced_field)
   references btpk (relname, field),
  foreign key (delete_action)
    references btfkactions,
  foreign key (update_action)
    references btfkactions
);