--- Create all tables and views for XMLDB in DB2
--- This script consists of the Example SQL files from "Designing XML Databases"
-- connect to xmldbel user xmldb using xmldb;

-- Create fine-grained storage tables and constraints

create table xdb_doc (
	doc_id		INTEGER		NOT NULL,
	name		VARCHAR(127)	NOT NULL,
	root		INTEGER
);
alter table xdb_doc add primary key (doc_id);

create table xdb_ele (
	doc_id		INTEGER		NOT NULL,
	ele_id		INTEGER		NOT NULL,
	parent_id	INTEGER	,
	tag		VARCHAR(32)	NOT NULL
);
alter table xdb_ele add primary key (doc_id, ele_id);

create table xdb_attr (
	doc_id		INTEGER		NOT NULL,
	attr_id		INTEGER		NOT NULL,
	ele_id		INTEGER		NOT NULL,
	name		VARCHAR(32)	NOT NULL,
	value		VARCHAR(255)
);
alter table xdb_attr add primary key (doc_id, attr_id);

create table xdb_child (
	doc_id		INTEGER		NOT NULL,
	ele_id	        INTEGER		NOT NULL,
	indx		INTEGER		NOT NULL,
	child_class	VARCHAR(4)	NOT NULL,
	child_id	INTEGER		NOT NULL
);
alter table xdb_child add primary key (doc_id, ele_id, indx);

create table xdb_str (
	doc_id		INTEGER		NOT NULL,
	cdata_id	INTEGER		NOT NULL,
	ele_id	        INTEGER		NOT NULL,
	value		VARCHAR(255)	NOT NULL
);
alter table xdb_str add primary key (doc_id, cdata_id);

create table xdb_text (
	doc_id		INTEGER		NOT NULL,
	cdata_id	INTEGER		NOT NULL,
	ele_id	        INTEGER		NOT NULL,
	value		CLOB(10M)	NOT NULL
);
alter table xdb_text add primary key (doc_id, cdata_id);

-- Foreign Keys
alter table xdb_doc add constraint fk_xdb_doc_root
      foreign key (doc_id, root) references xdb_ele (doc_id, ele_id);
alter table xdb_ele add constraint fk_xdb_ele_doc_id
      foreign key (doc_id) references xdb_doc (doc_id);
alter table xdb_attr add constraint fk_xdb_attr_doc_id
      foreign key (doc_id) references xdb_doc (doc_id);
alter table xdb_attr add constraint fk_xdb_attr_ele_id
      foreign key (doc_id, ele_id) references xdb_ele (doc_id, ele_id);
alter table xdb_child add constraint fk_xdb_child_doc
      foreign key (doc_id) references xdb_doc (doc_id);
alter table xdb_child add constraint fk_xdb_child_ele
      foreign key (doc_id, ele_id) references xdb_ele (doc_id, ele_id);
alter table xdb_str add constraint fk_xdb_str_doc_id
      foreign key (doc_id) references xdb_doc (doc_id);
alter table xdb_str add constraint fk_xdb_str_ele_id
      foreign key (doc_id, ele_id) references xdb_ele (doc_id, ele_id);
alter table xdb_text add constraint fk_xdb_text_doc_id
      foreign key (doc_id) references xdb_doc (doc_id);
alter table xdb_text add constraint fk_xdb_text_ele_id
      foreign key (doc_id, ele_id) references xdb_ele (doc_id, ele_id);

-- Additional tables and constraints for medium-grained storage

create table xdb_frag (
	doc_id		INTEGER		NOT NULL,
	frag_id		INTEGER		NOT NULL,
	ele_id		INTEGER		NOT NULL,
	body		CLOB(1M)	NOT NULL
);
alter table xdb_frag add primary key (doc_id, frag_id);

create table xdb_frag_ref (
	doc_id		INTEGER		NOT NULL,
	frag_id		INTEGER		NOT NULL,
	ele_ref		INTEGER		NOT NULL
);
alter table xdb_frag_ref add primary key (doc_id, frag_id, ele_ref);

alter table xdb_frag add constraint fk_xdb_frag_doc_id
      foreign key (doc_id) references xdb_doc (doc_id);
alter table xdb_frag add constraint fk_xdb_frag_ele_id
      foreign key (doc_id, ele_id) references xdb_ele (doc_id, ele_id);
alter table xdb_frag_ref add constraint fk_xdb_frag_ref_do
      foreign key (doc_id) references xdb_doc (doc_id);
alter table xdb_frag_ref add constraint fk_xdb_frag_ref_el
      foreign key (doc_id, ele_ref) references xdb_ele (doc_id, ele_id);
alter table xdb_frag_ref add constraint fk_xdb_frag_ref_fr
      foreign key (doc_id, frag_id) references xdb_frag (doc_id, frag_id);

-- Create coarse-grained storage table and constraint

create table cgrel_doc (
	doc_id		INTEGER		NOT NULL,
	name		VARCHAR(127),
	body		CLOB(10M)
);
alter table cgrel_doc add primary key (doc_id);

-- Create storage table and constraint for links

create table xdb_link (
	s_doc_id	INTEGER		NOT NULL,
	s_link_id	INTEGER		NOT NULL,
	s_ele_id	INTEGER		NOT NULL,
	name		VARCHAR(64)	NOT NULL,
	d_doc_id	INTEGER		NOT NULL,
	d_link_id	INTEGER		NOT NULL,
	d_ele_id	INTEGER		NOT NULL
);
alter table xdb_link add primary key (s_doc_id, s_link_id);

alter table xdb_link add constraint uc_xdb_link_d_doc
      unique (d_doc_id, d_link_id);

alter table xdb_link add constraint fk_xdb_link_s_doc
      foreign key (s_doc_id) references xdb_doc (doc_id);
alter table xdb_link add constraint fk_xdb_link_s_ele
      foreign key (s_doc_id, s_ele_id) references xdb_ele (doc_id, ele_id);
alter table xdb_link add constraint fk_xdb_link_d_doc
      foreign key (d_doc_id) references xdb_doc (doc_id);
alter table xdb_link add constraint fk_xdb_link_d_ele
      foreign key (d_doc_id, d_ele_id) references xdb_ele (doc_id, ele_id);

-- Create view to retrieve subelement relationships

create view xdb_ele_edges_help_v as
select s.ele_id source, d.tag name, c.child_id dest_ref,
       CAST (NULL AS VARCHAR(10)) dest_val, s.doc_id doc
from xdb_ele s,
     xdb_child c,
     xdb_ele d,
     xdb_child dc
where s.doc_id = c.doc_id and
      s.ele_id = c.ele_id and
      c.child_class = 'ELE' and
      d.doc_id = c.doc_id and
      d.ele_id = c.child_id and
      dc.doc_id = d.doc_id and
      dc.ele_id = d.ele_id and
      dc.child_class = 'ELE' and
      dc.indx = 1
union
select s.parent_id source, s.tag name, -1 dest_ref, d.value dest_val,
       s.doc_id doc
from xdb_ele s,
     xdb_child c,
     xdb_str d
where s.doc_id = c.doc_id and
      s.ele_id = c.ele_id and
      c.child_class = 'STR' and
      c.indx = 1 and
      d.doc_id = c.doc_id and
      d.ele_id = c.ele_id and
      d.cdata_id = c.child_id;

-- Create view to retrieve subelement relationships as binary relationships

create view xdb_ele_edges_user_v as
select
      rtrim(char(doc))||'.'||rtrim(char(source)) as source,
      name as name,
      CASE
         WHEN dest_ref = -1
                THEN dest_val
                 ELSE rtrim(char(doc))||'.'||rtrim(char(dest_ref))
      END AS dest
from xdb_ele_edges_help_v v;
