DespoinaLog_molgenis35Tomolgenis14 – Trac

Context Navigation

← Previous Version
View Latest Version
Next Version →

Version 1 (modified by antonak, 12 years ago) (diff)
--

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") ;