* data live in the old mysql database (molgenis14 ) so you need to run the steps of the migration script : https://www.dropbox.com/home/BBmri%20App/migration_issue?select=migration-script.rtf)
   *      CREATE TABLE !`BiobankSubCategory` (

  !`id` int(11) NOT NULL,

  PRIMARY KEY (!`id`),

  CONSTRAINT !`biobanksubcategory_ibfk_1` FOREIGN KEY (!`id`) REFERENCES !`OntologyTerm` (!`id`) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO !BiobankSubCategory (id)

SELECT id

FROM !BiobankCategory

WHERE id  NOT IN ("281", "284", "285") ;

CREATE TABLE !BiobankPersonRole (

	id INTEGER NOT NULL

	, PRIMARY KEY(id)

);

alter table !OntologyTerm modify   column !__Type enum('!OntologyTerm','!PersonRole','Species','!AlternateId','!BiobankCategory','!BiobankTopic','!BiobankDataType', '!BiobankSubCategory','!BiobankPersonRole');

ALTER TABLE Biobank DROP FOREIGN KEY biobank_ibfk_5; 

ALTER TABLE !BiobankSubCategory ADD FOREIGN KEY (id) REFERENCES !OntologyTerm (id) ON DELETE CASCADE; 

ALTER TABLE `!BiobankSubCategory`  

ADD CONSTRAINT !`biobank_ibfk_5` 

    FOREIGN KEY (!`id`) REFERENCES !OntologyTerm (id) ON DELETE CASCADE; AUTO UPARXEI HDH APO THN DHMIOURGIA TOU)

ALTER TABLE Biobank ADD FOREIGN KEY (!SubCategory) REFERENCES !BiobankSubCategory (id) ON DELETE RESTRICT; 

ALTER TABLE `Biobank`  

ADD CONSTRAINT !`biobank_ibfk_885` 

    FOREIGN KEY (`!SubCategory`) REFERENCES !BiobankSubCategory (id)  ON DELETE CASCADE;

DELETE FROM !BiobankCategory

WHERE id  NOT IN ("281", "284", "285") ;   

ALTER TABLE !BiobankPersonRole ADD FOREIGN KEY (id) REFERENCES !PersonRole (id) ON DELETE CASCADE;

ALTER TABLE `!BiobankPersonRole`  

ADD CONSTRAINT `!BiobankPersonRole_ibfk_33` 

    FOREIGN KEY (`id`) REFERENCES !PersonRole (id)  ON DELETE CASCADE;

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='282';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='283';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='286';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='287';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='288';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='289';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='290';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='291';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='292';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='293';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='295';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='296';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='297';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='298';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='564';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='299';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='294';

select * from !OntologyTerm where name="Software Engineer";

delete from !OntologyTerm where id=561;

select * from !BiobankPersonRole;

insert into !BiobankPersonRole values(561);

select * from !OntologyTerm where name="coordinator";

delete from !OntologyTerm where id=593;

select * from !BiobankPersonRole;

insert into !BiobankPersonRole values(277);

select * from !OntologyTerm where name="postdoc";

delete from !OntologyTerm where id=594;

 select * from !BiobankPersonRole;

insert into !BiobankPersonRole values(278);

select * from !OntologyTerm where name="administrator";

delete from !OntologyTerm where id=595;

select * from !BiobankPersonRole;

insert into !BiobankPersonRole values(279);

select * from !OntologyTerm where name="IT responsible";

delete from !OntologyTerm where id=596;

select * from !BiobankPersonRole;

insert into !BiobankPersonRole values(280);

 

COMMIT;

======================================================================================

!SubCategory

biobank_ibfk_1

  CONSTRAINT !`biobank_ibfk_5` FOREIGN KEY (!`SubCategory`) REFERENCES !`BiobankSubCategory` (!`id`)

  CONSTRAINT !`biobank_ibfk_5` FOREIGN KEY (!`SubCategory`) REFERENCES !`BiobankSubCategory` (!`id`),

=============================WITH COMMENTS ===========================================

CREATE TABLE !`BiobankSubCategory` (

  !`id` int(11) NOT NULL,

  PRIMARY KEY (!`id`),

  CONSTRAINT !`biobanksubcategory_ibfk_1` FOREIGN KEY (!`id`) REFERENCES !`OntologyTerm` (!`id`) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

/* move data from !BiobankCategory to !BiobankSubCategory table */

INSERT INTO !BiobankSubCategory (id)

SELECT id

FROM !BiobankCategory

WHERE id  NOT IN ("281", "284", "285") ;

/*WHERE name NOT IN ("A. Core Biobanks, DNA available", "B: Supporting biobanks, DNA not yet available" ,"C: Biobanken in opbouw") ;* - IF NEEDED CORRECT THE IDs/

/* alter constraints */

ALTER TABLE Biobank DROP FOREIGN KEY biobank_ibfk_5; 

ALTER TABLE !BiobankSubCategory ADD FOREIGN KEY (id) REFERENCES !OntologyTerm (id) ON DELETE CASCADE; 

NOT OK : ERROR 1050 (42S01): Table './bbmri/!BiobankSubCategory' already exists

THEN USE :  

ALTER TABLE `!BiobankSubCategory`  

ADD CONSTRAINT !`id` 

    FOREIGN KEY (!`id`) REFERENCES !OntologyTerm (id) ON DELETE CASCADE;

ALTER TABLE Biobank ADD FOREIGN KEY (!SubCategory) REFERENCES !BiobankSubCategory (id) ON DELETE RESTRICT; 

NOT OK : ERROR 1050 (42S01): Table './bbmri/Biobank' already exists

THEN USE : 

ALTER TABLE `Biobank`  

ADD CONSTRAINT `!SubCategory` 

    FOREIGN KEY (`!SubCategory`) REFERENCES !BiobankSubCategory (id)  ON DELETE CASCADE;

/* delete moved data from !BiobankCategory */

DELETE FROM !BiobankCategory

WHERE id  NOT IN ("281", "284", "285") ;   

/*IF NEEDED CORRECT THE IDs

WHERE name IN ("A. Core Biobanks, DNA available", "B: Supporting biobanks, DNA not yet available" ,"C: Biobanken in opbouw") ;

*/

CREATE TABLE !BiobankPersonRole (

	id INTEGER NOT NULL

	, PRIMARY KEY(id)

);

ALTER TABLE !BiobankPersonRole ADD FOREIGN KEY (id) REFERENCES !PersonRole (id) ON DELETE CASCADE;

alter table !OntologyTerm modify   column !__Type enum('!OntologyTerm','!PersonRole','Species','!AlternateId','!BiobankCategory','!BiobankTopic','!BiobankDataType', '!BiobankSubCategory','!BiobankPersonRole');

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='282';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='283';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='286';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='287';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='288';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='289';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='290';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='291';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='292';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='293';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='295';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='296';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='297';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='298';

UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='564';

/*below we resolve the problem of duplicate entries in ontology term for !PersonRole*/

select * from !OntologyTerm where name="Software Engineer";

delete from !OntologyTerm where id=561;

select * from !BiobankPersonRole;

insert into !BiobankPersonRole values(561);

select * from !OntologyTerm where name="coordinator";

delete from !OntologyTerm where id=593;

select * from !BiobankPersonRole;

insert into !BiobankPersonRole values(277);

select * from !OntologyTerm where name="postdoc";

delete from !OntologyTerm where id=594;

 select * from !BiobankPersonRole;

insert into !BiobankPersonRole values(278);

select * from !OntologyTerm where name="administrator";

delete from !OntologyTerm where id=595;

select * from !BiobankPersonRole;

insert into !BiobankPersonRole values(279);

select * from !OntologyTerm where name="IT responsible";

delete from !OntologyTerm where id=596;

select * from !BiobankPersonRole;

insert into !BiobankPersonRole values(280);

 

COMMIT;