-- This is a test for the metadata to be used
-- In the NetTraveler experiments and tests
-- February 08, 2006

-- This metadata defines a catalog that has been 
-- horizontally partitioned.
-- In horizontal partitioning all sites will have the 
-- same schema but each site will contain data
-- specific to it (think of it as a bank that has
-- many branches and each branch stores information
-- about transactions
-- carried in it). Using this partitioning scheme, a
-- table that contains a key that uniquely identifies
-- each site can be created to easily find the
-- sites that are involved in a query.

-- Create the database to store the
-- NetTraveler metadata. This step must be done 
-- manually.
-- create database nt_meta;

-- Create the tables for the metadata

-- Site table. Info about the data sources sites.
-- id = ID of the service
-- type = Type of service (IG=, DSS=, QSB=)
-- ip = IP address of the service
-- port = Port where the service is listening
-- impl = Implementation type of the service. (Web service, sockets)
create table site(
       id varchar(15) not null CHECK (id > 0),
       type int not null,
       ip varchar(40) not null,
       port int not null default 8080,
       impl int  not null,
       primary key (id)
);

-- Table of peers.
-- States a relation between two services that are peers.
-- Peerism is bidirectional. Hence if a service A is peer of
-- a service B then B is also a peer of A. To maintain this
-- relation, there will be two records peer each peer
-- relation.

create table peer(
       s1 varchar(15),
       s2 varchar(15) ,
       primary key(s1, s2),
       foreign key (s1) references site(id) on delete cascade,
       foreign key (s2) references site(id) on delete cascade
);
-- This table tells which QSBs know which IGs
create table knows(
       qsb varchar(15) ,
       ig  varchar(15) ,
       primary key(qsb, ig),
       foreign key (qsb) references site(id) on delete cascade,
       foreign key (ig) references site(id) on delete cascade
);

-- Catalogs metadata
--create table catalog(
  --     id int not null CHECK (id > 0),
    --   name varchar(100) not null,
      -- primary key(id)
--);

--  Metadata of relations
create table relation(
       id int not null CHECK (id > 0),
       name varchar(100) not null,
       primary key(id)
);

-- Holds the attributes of every relation
create table attribute(
       id int not null CHECK (id > 0),
       rid int, 
       name varchar(100) not null,
       type int not null,
       pos int not null CHECK (pos >= 0),
       primary key (id),
       foreign key (rid) references relation (id) on delete cascade 
);

-- Key table
-- Tells the attribute that is unique for a relation in
-- a site
create table keys(
    id    int	not null CHECK (id > 0),
	aid   int	not null,
    cid   int	not null,
    isKey boolean not null,
    primary key(id),
    foreign key(aid) references attribute(id) on delete cascade,
    foreign key(cid) references catalog(id) on delete cascade       
);

create table replication_meta(
	rid	int 	not null,
	numbuckets int not null,
	foreign key(rid) references relation (id) on delete cascade
);

create table site_stats(
	minvalue	double	not null,
	maxvalue 	double 	not null,
	perprocess	double	not null,
	rtype		integer	not null,
	id			varchar(15) not null,
	foreign key(id) references site(id) on delete cascade
);
-- Relation of keys to sites
--create table haskey{
--       kid int not null,
--       sid varchar(15) not null,
--       val   varchar(100) not null, --The value of the key as a string
 --     primary key(kid,sid),
--       foreign key(kid) references keys(id) on delete cascade,
--       foreign key(sid) references site(id) on delete cascade
--};

-- Store table
-- Relation-to-site mapping. For each relation tells
-- which site has it.
create table store(
       rid int not null,
       sid varchar(15) not null,
       primary key (rid, sid),
       foreign key (rid) references relation(id) on delete cascade,
       foreign key(sid) references site(id) on delete cascade
);