- Copy the new war file to tomcat's directory :
- 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) in order to change only the scheme NOT the data
=============================WITHOUT 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;
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;
