Changes between Version 2 and Version 3 of DespoinaLog_molgenis35Tomolgenis14

Timestamp:

2013-02-22T11:24:06+01:00 (12 years ago)

Author:

antonak

Comment:

--

DespoinaLog_molgenis35Tomolgenis14

@@ -2 +2 @@
 
 {{{
+
+=============================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
-
+  `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;
+
+
 }}}