-- M4 tables installation script for Mysql --

-- version: 0.01 (tested ok)
-- author: Timm Euler and Martin Scholz
-- date: september 2006

-- changes against oracle version:

-- a) drop table ... cascade constraints;  --> drop table ... cascade;
-- b) use small case for table and column names, mysql is case sensitive 
-- c) number --> integer
-- d) varchar2(xx) --> varchar(xx)
-- e) comment lines must start with "-- " instead of "--" (additional blank)
-- f) MySQL doesn's support sequences, therefore a dedicated table 
--    called m4sequence_t is used for storing the latest global M4 id

-- ----------------------------------------------
-- ------------    create tables    -------------
-- ----------------------------------------------

-- the MySql-specific sequence table:
create table m4sequence_t (
  m4_id   integer   not null,
  constraint global_m4 primary key (m4_id)
) engine = InnoDB;

-- table case_t
create table case_t (
  ca_id          integer         not null,
  ca_name        varchar(100) not null,
  ca_mode        varchar(15)  not null,
  constraint caid_pk
    primary key ( ca_id ),
  constraint caname_uq
    unique (ca_name),
  constraint camode_ch
    check (ca_mode in ('DESIGN', 'TEST', 'FINAL'))
) engine = InnoDB;


-- table con_datatype_t
create table con_datatype_t (
  condt_id    integer        not null,
  condt_name  varchar(100)  not null,
  constraint condtid_pk
    primary key ( condt_id )
) engine = InnoDB;


-- table col_datatype_t
create table col_datatype_t (
  coldt_id    integer        not null,
  coldt_name  varchar(100)  not null,
  constraint coldtid_pk
    primary key ( coldt_id )
) engine = InnoDB;


-- table concept_t
create table concept_t (
  con_id           integer          not null,
  con_caid         integer,
  con_name         varchar(100)  not null,
  con_type         varchar(10)   not null,
  constraint conid_pk
    primary key ( con_id ),
  constraint concase_case_fk
    foreign key ( con_caid )
      references case_t (ca_id),
  constraint con_caid_name_uq
    unique (con_caid, con_name),
  constraint contype_ch
    check (con_type in ('BASE', 'DB', 'MINING'))
) engine = InnoDB;


-- table columnset_t
create table columnset_t (
  cs_id       integer          not null,
  cs_schema   varchar(100)  not null,
  cs_name     varchar(100)  not null,
  cs_type     varchar(5)    not null,
  cs_sql      varchar(4000),
  cs_conid    integer,
  cs_msbranch varchar(1000),
  constraint csid_pk
    primary key ( cs_id ),
  constraint csconcept_fk
    foreign key (cs_conid)
      references concept_t (con_id),
  constraint cstype_ch
    check (cs_type in ('T', 'V'))
) engine = InnoDB;


-- table conceptisa_t
create table conceptisa_t (
  cisa_id          integer        not null,
  cisa_superconid  integer        not null,
  cisa_subconid    integer        not null,
  constraint cisaid_pk
    primary key ( cisa_id ),
  constraint cisasubconid_fk
    foreign key (cisa_subconid)
      references concept_t (con_id),
  constraint cisasuperconid_fk
    foreign key (cisa_superconid)
      references concept_t (con_id)
) engine = InnoDB;


-- table projection_t
create table projection_t (
  pro_id         integer        not null,
  pro_fromconid  integer        not null,
  pro_toconid    integer        not null,
  constraint proid_pk
    primary key ( pro_id ),
  constraint profromconid_fk
    foreign key (pro_fromconid)
      references concept_t (con_id),
  constraint protoconid_fk
    foreign key (pro_toconid)
      references concept_t (con_id)
) engine = InnoDB;


-- table mcfeature_t
create table mcfeature_t (
  mcf_id     integer        not null,
  mcf_name   varchar(100)  not null,
  mcf_conid  integer,
  mcf_valid  varchar(5),
  constraint mcfid_pk
    primary key ( mcf_id ),
  constraint mcfconid_fk
    foreign key (mcf_conid)
      references concept_t (con_id),
  constraint mcfconname_uq
    unique (mcf_conid, mcf_name) 
) engine = InnoDB;


-- table column_t
create table column_t (
  col_id       integer         not null,
  col_name     varchar(100) not null,
  col_csid     integer,
  col_coldtid  integer         not null,
  col_sql      varchar(4000),
  constraint colid_pk
    primary key ( col_id ),
  constraint colcoldtid_fk
    foreign key (col_coldtid)
      references col_datatype_t (coldt_id),
  constraint colcsid_fk
    foreign key (col_csid)
      references columnset_t (cs_id)
) engine = InnoDB;


-- table keyhead_t
create table keyhead_t (
  kh_id      integer        not null,
  kh_name    varchar(100)  not null,
  kh_pkcsid  integer,
  kh_fkcsid  integer,
  constraint khid_pk
    primary key ( kh_id ),
  constraint khfkcsid_fk
    foreign key (kh_fkcsid)
      references columnset_t (cs_id),
  constraint khpkcsid_fk
    foreign key (kh_pkcsid)
      references columnset_t (cs_id)
) engine = InnoDB;


-- table keymember_t
create table keymember_t (
  km_id       integer        not null,
  km_khid     integer        not null,
  km_pkcolid  integer,
  km_fkcolid  integer,
  km_pos      integer (5)    not null,
  km_fktype   varchar(10),
  constraint kmid_pk
    primary key ( km_id ),
  constraint kmfkcolid_fk
    foreign key (km_fkcolid)
      references column_t (col_id),
  constraint kmkhid_fk
    foreign key (km_khid)
      references keyhead_t (kh_id),
  constraint kmpkcolid_fk
    foreign key (km_pkcolid)
      references column_t (col_id)
) engine = InnoDB;


-- table relation_t
create table relation_t (
  rel_id           integer         not null,
  rel_name         varchar(100) not null,
  rel_fromconid    integer,
  rel_toconid      integer,
  rel_fromkid      integer,
  rel_tokid        integer,
  rel_csid         integer,
  constraint relid_pk
    primary key ( rel_id ),
  constraint relcsid_fk
    foreign key (rel_csid)
      references columnset_t (cs_id),
  constraint relfromconid_fk
    foreign key (rel_fromconid)
      references concept_t (con_id),
  constraint relfromkid_fk
    foreign key (rel_fromkid)
      references keyhead_t (kh_id),
  constraint reltoconid_fk
    foreign key (rel_toconid)
      references concept_t (con_id),
  constraint reltokid_fk
    foreign key (rel_tokid)
      references keyhead_t (kh_id)
) engine = InnoDB;


-- table relationisa_t
create table relationisa_t (
  risa_id          integer        not null,
  risa_superrelid  integer        not null,
  risa_subrelid    integer        not null,
  constraint risaid_pk
    primary key ( risa_id ),
  constraint risasubrelid_fk
    foreign key (risa_subrelid)
      references relation_t (rel_id),
  constraint risasuperrelid_fk
    foreign key (risa_superrelid)
      references relation_t (rel_id)
) engine = InnoDB;


-- table baseattrib_t
create table baseattrib_t (
  ba_id          integer        not null,
  ba_name        varchar(100)  not null,
  ba_condtid     integer        not null,
  ba_relevance   varchar(5),
  ba_attribtype  varchar(15)  not null,
  ba_mcfid       integer,
  constraint baid_pk
    primary key ( ba_id ),
  constraint bacondtid_fk
    foreign key (ba_condtid)
      references con_datatype_t (condt_id),
  constraint bamcfid_fk
    foreign key (ba_mcfid)
      references mcfeature_t (mcf_id),
  constraint barelevance_ch
    check (ba_relevance in ('YES', 'NO')) ,
  constraint baattribtype_ch
    check (ba_attribtype in ('BASE', 'DB', 'MINING'))
) engine = InnoDB;


-- table ba_concept_t
create table ba_concept_t (
  bc_id		integer	not null,
  bc_baid	integer	not null,
  bc_conid	integer	not null,
  constraint bacon_id
    primary key ( bc_id ),
  constraint bacon_ba_fk
    foreign key ( bc_baid )
      references baseattrib_t (ba_id),
  constraint bacon_con_fk
    foreign key ( bc_conid )
      references concept_t (con_id)
) engine = InnoDB;

create index bacon_ba_idx  on ba_concept_t (bc_baid);
create index bacon_con_idx on ba_concept_t (bc_conid);

-- table ba_column_t
create table ba_column_t (
  bac_id	integer	not null,
  bac_baid	integer	not null,
  bac_colid	integer	not null,
  constraint bacid_pk
    primary key ( bac_id ),
  constraint bacba_fk
    foreign key ( bac_baid )
      references baseattrib_t (ba_id),
  constraint baccol_fk
    foreign key ( bac_colid )
      references column_t (col_id)
) engine = InnoDB;

create index bac_ba_idx  on ba_column_t (bac_baid);
create index bac_col_idx on ba_column_t (bac_colid);

-- table csstatist_t
create table csstatist_t (
  csst_id    integer        not null,
  csst_csid  integer        not null,
  csst_all   integer (20),
  csst_ord   integer (5),
  csst_nom   integer (5),
  csst_time  integer (5),
  constraint csstid_pk
    primary key ( csst_id ),
  constraint csstcsid_fk
    foreign key (csst_csid)
      references columnset_t (cs_id)
) engine = InnoDB;


-- table colstatist1_t
create table colstatist1_t (
  colst1_id        integer        not null,
  colst1_colid     integer        not null,
  colst1_unique    integer (20),
  colst1_missing   integer (20),
  colst1_min       varchar(100),
  colst1_max       varchar(100),
  colst1_avg       float,
  colst1_stddev    float,
  colst1_variance  float,
  colst1_median    varchar(100),
  colst1_modal     varchar(100),
  constraint colst1id_pk
    primary key ( colst1_id ),
  constraint colst1colid_fk
    foreign key (colst1_colid)
      references column_t (col_id)
) engine = InnoDB;


-- table colstatist2_t
create table colstatist2_t (
  colst2_id         integer        not null,
  colst2_colid      integer        not null,
  colst2_distvalue  varchar(100)  not null,
  colst2_distcount  integer (20)   not null,
  colst2_distmin    float,
  colst2_distmax    float,
  constraint colst2id_pk
    primary key ( colst2_id ),
  constraint colst2colid_fk
    foreign key (colst2_colid)
      references column_t (col_id)
) engine = InnoDB;


-- table value_t
create table value_t (
  v_id       integer        not null,
  v_condtid  integer        not null,
  v_name     varchar(100),
  v_value    varchar(4000) not null,
  constraint vid_pk
    primary key ( v_id ),
  constraint vcondtid_fk
    foreign key (v_condtid)
      references con_datatype_t (condt_id)
) engine = InnoDB;


-- table operator_t
create table operator_t (
  op_id       integer           not null,
  op_name     varchar(1000)  not null,
  op_loop     varchar(5),
  op_multi    varchar(5),
  op_manual   varchar(5),
  op_realize  varchar(100),
  constraint opid_pk
    primary key ( op_id ),
  constraint opmanual_ch
    check ( op_manual in ('YES', 'NO')) ,
  constraint oploop_ch
    check ( op_loop in ('YES', 'NO')) ,
  constraint opmulti_ch
    check ( op_multi in ('YES', 'NO'))
) engine = InnoDB;


-- table op_params_t
create table op_params_t (
  param_id     integer           not null,
  op_id	       integer           not null,
  minarg       integer		not null,
  maxarg       integer,
  name	       varchar(100)	not null,
  io	       varchar(5)	not null,
  type	       varchar(5)	not null,
  docu	       varchar(400),
  constraint opparamid_pk
    primary key ( param_id ),
  constraint opparamopid_fk
    foreign key (op_id)
      references operator_t (op_id)
      on delete cascade
) engine = InnoDB;


-- table op_constr_t
create table op_constr_t (
  constr_id    integer           not null,
  constr_opid  integer           not null,
  constr_type  varchar(10)     not null,
  constr_obj1  varchar(100)	not null,
  constr_obj2  varchar(100),
  constr_docu  varchar(400),
  constr_sql   varchar(1000),
  constraint opconstrid_pk
    primary key ( constr_id ),
  constraint opconstrid_fk
    foreign key (constr_opid)
      references operator_t (op_id)
      on delete cascade
) engine = InnoDB;


-- table op_cond_t
create table op_cond_t (
  cond_id      integer           not null,
  cond_opid    integer           not null,
  cond_type    varchar(10)     not null,
  cond_obj1    varchar(100)	not null,
  cond_obj2    varchar(100),
  cond_docu    varchar(400),
  cond_sql     varchar(1000),
  constraint opcondid_pk
    primary key ( cond_id ),
  constraint opcondid_fk
    foreign key (cond_opid)
      references operator_t (op_id)
      on delete cascade
) engine = InnoDB;


-- table op_assert_t
create table op_assert_t (
  assert_id    integer           not null,
  assert_opid  integer           not null,
  assert_type  varchar(10)     not null,
  assert_obj1  varchar(100)	not null,
  assert_obj2  varchar(100),
  assert_docu  varchar(400),
  assert_sql   varchar(1000),
  constraint opassertid_pk
    primary key ( assert_id ),
  constraint opassertid_fk
    foreign key ( assert_opid )
      references operator_t (op_id)
      on delete cascade
) engine = InnoDB;

-- table chain_t
create table chain_t (
  ch_id        integer           not null,
  ch_caseid    integer           not null,
  ch_name      varchar(100)	not null,
  ch_parent    integer,
  ch_descript  varchar(400),
  constraint chainid_pk
    primary key ( ch_id ),
  constraint chca_fk
    foreign key (ch_caseid)
      references case_t (ca_id),
  constraint chch_fk
    foreign key (ch_parent)
      references chain_t (ch_id),
  constraint chcasename_uq
    unique (ch_caseid, ch_name) 
) engine = InnoDB;


-- table step_t
create table step_t (
  st_id             integer        not null,
  st_name	    varchar(100) not null,
  st_caid           integer,
  st_nr             integer (5),
  st_opid           integer,
  st_loopnr         integer (5),
  st_multistepcond  varchar(1000),
  st_chid	    integer,
  constraint stid_pk
    primary key ( st_id ),
  constraint stcaid_fk
    foreign key (st_caid)
      references case_t (ca_id),
  constraint stopid_fk
    foreign key (st_opid)
      references operator_t (op_id),
  constraint stchid_fk
    foreign key (st_chid)
      references chain_t (ch_id),
  constraint stcasename_uq
    unique (st_caid, st_name) 
) engine = InnoDB;


-- table stepsequence_t
create table stepsequence_t (
  sts_id               integer        not null,
  sts_stid             integer        not null,
  sts_successorstid    integer,
  constraint stsid_pk
    primary key ( sts_id ),
  constraint stsstid_fk
    foreign key (sts_stid)
      references step_t (st_id),
  constraint stssuccessorstid_fk
    foreign key (sts_successorstid)
      references step_t (st_id)
) engine = InnoDB;


-- table parameter_t
create table parameter_t (
  par_id        integer         not null,
  par_name      varchar(100) not null,
  par_objid     integer,
  par_objtype   varchar(20)  not null,
  par_opid      integer         not null,
  par_type      varchar(10)  not null,
  par_nr        integer (3)     not null,
  par_stid      integer         not null,
  par_stloopnr  integer (5),
  constraint parid_pk
    primary key ( par_id ),
  constraint paropid_fk
    foreign key (par_opid)
      references operator_t (op_id),
  constraint parstid_fk
    foreign key (par_stid)
      references step_t (st_id),
  constraint parstepname_uq
    unique (par_stid, par_name) ,
  constraint parobjtype_ch
    check ( par_objtype in ('CON', 'REL', 'BA', 'V', 'MCF', 'FUNC')) ,
  constraint partype_ch
    check ( par_type in ('IN', 'OUT'))
) engine = InnoDB;


-- table docu_t
create table docu_t (
  doc_id       integer        not null,
  doc_objid    integer        not null,
  doc_objtype  varchar(20)  not null,
  doc_text     varchar(4000)  not null,
  constraint docid_pk
    primary key ( doc_id )
) engine = InnoDB;

create table revstep_t (
  rev_id      integer    not null,
  rev_orgstid    integer    not null,
  rev_revstid integer    not null,
  constraint revst_pk
    primary key (rev_id),
  constraint rev_to_step_fk1
    foreign key (rev_orgstid)
      references step_t (st_id),
  constraint rev_to_step_fk2
    foreign key (rev_revstid)
      references step_t (st_id)
) engine = InnoDB;
      

-- ----------------------------------------------
-- --------------   system tables   -------------
-- ----------------------------------------------

-- insert into con_datatype
insert into con_datatype_t values (1,'NOMINAL');
insert into con_datatype_t values (2,'CATEGORIAL');
insert into con_datatype_t values (3,'KEYATTRIB');
insert into con_datatype_t values (4,'CONSTANT');
insert into con_datatype_t values (5,'TIMEGROUP');
insert into con_datatype_t values (6,'SPATIAL');
insert into con_datatype_t values (7,'NUMERIC');
insert into con_datatype_t values (8,'ORDINAL');
insert into con_datatype_t values (9,'SCALAR');
insert into con_datatype_t values (10,'TIME');
insert into con_datatype_t values (11,'BINARY');

-- insert into col_datatype
insert into col_datatype_t values (12,'NUMBER');
insert into col_datatype_t values (13,'STRING');
insert into col_datatype_t values (14,'DATE');
insert into col_datatype_t values (15,'KEY');

-- insert into m4sequence_t
insert into m4sequence_t values (100000);

-- --------------------------------------------------

create table m4trash_t (
  m4id		integer       not null, 
  m4table       varchar(50) not null,
  stepid	integer	     not null,
  constraint m4trash_fk
    foreign key ( stepid )
      references step_t (st_id)
) engine = InnoDB;

create table dbtrash_t (
  objtype	varchar(5)   not null,
  objname	varchar(100) not null,
  stepid	integer        not null,
  schemaname	varchar(200),
  constraint dbtrash_objtype_ch
    check(objtype in ('T', 'V', 'F', 'I', 'PK', 'FK')),
  constraint dbtrash_fk
    foreign key ( stepid )
      references step_t (st_id)
) engine = InnoDB;

create table m4_access_t
( object_id     varchar(100) not null,
  object_type   varchar(25) not null,
  client_name   varchar(50) not null,
  access_type   varchar(10) not null,
  constraint m4_acc_pk
    primary key ( object_id, object_type, client_name ),
  constraint acctype_ch
    check (access_type in ('READ', 'WRITE'))
) engine = InnoDB;

create table hci_coord_t
( obj_id        integer          not null
, obj_name      varchar(100)   not null
, obj_type      varchar(25)    not null
, context_id    integer          null
, context_name  varchar(100)   null
, context_type  varchar(25)    null
, x             integer          not null
, y             integer          not null
, constraint hci_c_uq
  unique ( obj_id, obj_name, obj_type
              , context_id, context_name, context_type)
, constraint obj_type_ch
    check ( obj_type in ('CON','ST', 'REL', 'CH'))
) engine = InnoDB;

create table op_group_t (
  opg_id     integer        not null,
  opg_parent integer,
  opg_opid   integer,
  opg_name   varchar(100) not null,
  constraint opg_pk
    primary key (opg_id),
  constraint opgop_fk
    foreign key (opg_opid)
      references operator_t (op_id)  
) engine = InnoDB;


-- --------------------------------------------------

commit;
