--dbnames contains names of all databases.
drop table dbnames cascade constraints;
create table dbnames (
  dbname varchar2(128),
  primary key (dbname)
);
--
--dbtablenames contains all tables belonging to 
--each database.
drop table dbtablenames cascade constraints;
create table dbtablenames (
  dbname    varchar2(128),
  tablename varchar2(128),
  primary key (dbname, tablename),
  foreign key (dbname) references dbnames
);
--
--dbcolumnnames contains all columns belonging
--to each table in each database.
drop table dbcolumnnames cascade constraints;
create table dbcolumnnames (
  dbname    varchar2(128),
  tablename varchar2(128),
  columnname    varchar2(128),
  primary key (dbname, tablename, columnname),
  foreign key (dbname, tablename) references dbtablenames
);
--
--qschemas contains query schema names and title for
--each database. 
drop table qschemas cascade constraints;
create table qschemas (
  dbname varchar2(128),
  qsname varchar2(128),
  header varchar2(1024),
  primary key (dbname, qsname),
  foreign key (dbname) references dbnames
);
--
--qselements contains elements associated with each query
--schema.
drop table qselements cascade constraints;
create table qselements (
  dbname            varchar2(128),
  qsname            varchar2(128),
  element_name      number(5),
  element_title     varchar2(1024),
  associated_table  varchar2(128),
  associated_column varchar2(128),
  element_type      varchar2(128) check(element_type in ('multiselect','select',
              'checkbox','text','submit','reset','radio')),
  element_position  integer,
  primary key (dbname, qsname, element_name),
  foreign key (dbname, qsname) references qschemas on delete cascade
);
--
--create a sequence for element name;
drop sequence element_seq;
create sequence element_seq start with 1000;
--
--qsjointables contains all tables to be used in the
--join condition in the query for each query schema.
drop table qsjointables cascade constraints;
create table qsjointables (
  dbname varchar2(128),
  qsname varchar2(128),
  tablename varchar2(128),
  primary key (dbname, qsname, tablename),
  foreign key (dbname, qsname) references qschemas on delete cascade,
  foreign key (dbname, tablename) references dbtablenames
);
--
--qsoutput contains all table name and column names to be
--used in the display
drop table qsoutput cascade constraints;
create table qsoutput (
  dbname varchar2(128),
  qsname varchar2(128),
  tablename varchar2(128),
  columnname varchar2(128),
  displayname varchar2(128),
  position  integer,
  primary key (dbname, qsname, tablename, columnname),
  foreign key (dbname, qsname, tablename) references qsjointables on delete
cascade,
  foreign key (dbname, tablename, columnname) references dbcolumnnames
);
