Changes between Initial Version and Version 1 of DespoinaLog_molgenis35Tomolgenis14

Timestamp:

2013-02-22T11:22:50+01:00 (13 years ago)

Author:

antonak

Comment:

--

DespoinaLog_molgenis35Tomolgenis14

@@ +1 @@
+ * 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;