create sequence parent_btid_seq minvalue 0 start 0;
create table parent 
(
  pid int not null,
  description text not null,

  -- fields required for btpgsql
  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,
  btid int default nextval('parent_btid_seq') unique not null,

  constraint c0 check 
    (vt_begin < vt_end and tt_start <= tt_stop)
);
create index parent_idx
on parent (pid, vt_begin, vt_end, tt_start, tt_stop);


create sequence child_btid_seq minvalue 0 start 0;
create table child 
(
  cid int not null,
  pid int not null,
  description text not null,

  -- fields required for btpgsql
  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,
  btid integer default nextval('child_btid_seq') unique not null,

  constraint c0 check 
    (vt_begin < vt_end and tt_start <= tt_stop)
);
create index child_idx
on child (cid, pid, vt_begin, vt_end, tt_start, tt_stop);


create sequence grandchild_btid_seq minvalue 0 start 0;
create table grandchild 
(
  gid int not null,
  cid int not null,
  description text not null,

  -- fields required for btpgsql
  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,
  btid integer default nextval('grandchild_btid_seq') unique not null,

  constraint c0 check 
    (vt_begin < vt_end and tt_start <= tt_stop)
);
create index grandchild_idx
on grandchild (gid, cid, vt_begin, vt_end, tt_start, tt_stop);


-- bt table population
-- note that btschema.sql must have been run first!

-- define bt tables
insert into btrel values ('parent');
insert into btrel values ('child');
insert into btrel values ('grandchild');

-- define bt tables' fields 
insert into btfield values ('parent','pid',         0);
insert into btfield values ('parent','description', 1);
insert into btfield values ('parent','vt_begin',    2);
insert into btfield values ('parent','vt_end',      3);
insert into btfield values ('parent','tt_start',    4);
insert into btfield values ('parent','tt_stop',     5);
insert into btfield values ('parent','btpk',        6);
insert into btfield values ('parent','btid',        7);

insert into btfield values ('child','cid',         0);
insert into btfield values ('child','pid',         1);
insert into btfield values ('child','description', 2);
insert into btfield values ('child','vt_begin',    3);
insert into btfield values ('child','vt_end',      4);
insert into btfield values ('child','tt_start',    5);
insert into btfield values ('child','tt_stop',     6);
insert into btfield values ('child','btpk',        7);
insert into btfield values ('child','btid',        8);

insert into btfield values ('grandchild','gid',         0);
insert into btfield values ('grandchild','cid',         1);
insert into btfield values ('grandchild','description', 2);
insert into btfield values ('grandchild','vt_begin',    3);
insert into btfield values ('grandchild','vt_end',      4);
insert into btfield values ('grandchild','tt_start',    5);
insert into btfield values ('grandchild','tt_stop',     6);
insert into btfield values ('grandchild','btpk',        7);
insert into btfield values ('grandchild','btid',        8);

--define bt tables' pks
insert into btpk values ('parent','pid');

insert into btpk values ('child','cid');
insert into btpk values ('child','pid');

insert into btpk values ('grandchild','gid');
insert into btpk values ('grandchild','cid');

--define bt fk actions
-- 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');

--define bt tables' fks
insert into btfk values 
('child','pid','parent','pid', 0,'cascade','restrict');

insert into btfk values 
('grandchild','cid','child','cid', 0,'cascade','restrict');