DespoinaLog_molgenis35Tomolgenis14 – Trac

Context Navigation

Version 5 (modified by antonak, 11 years ago) (diff)
--

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;   <----------IF THIS FAILS USE THE NEXT : 
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; <----------IF THIS FAILS USE THE NEXT : 


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;

Download in other formats:

Plain Text