Changes between Initial Version and Version 1 of DespoinaLog_molgenis35Tomolgenis14


Ignore:
Timestamp:
2013-02-22T11:22:50+01:00 (12 years ago)
Author:
antonak
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • DespoinaLog_molgenis35Tomolgenis14

    v1 v1  
     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)
     2   *      CREATE TABLE !`BiobankSubCategory` (
     3
     4  !`id` int(11) NOT NULL,
     5
     6  PRIMARY KEY (!`id`),
     7
     8  CONSTRAINT !`biobanksubcategory_ibfk_1` FOREIGN KEY (!`id`) REFERENCES !`OntologyTerm` (!`id`) ON DELETE CASCADE
     9
     10) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
     11
     12INSERT INTO !BiobankSubCategory (id)
     13
     14SELECT id
     15
     16FROM !BiobankCategory
     17
     18WHERE id  NOT IN ("281", "284", "285") ;
     19
     20CREATE TABLE !BiobankPersonRole (
     21
     22        id INTEGER NOT NULL
     23
     24        , PRIMARY KEY(id)
     25
     26);
     27
     28alter table !OntologyTerm modify   column !__Type enum('!OntologyTerm','!PersonRole','Species','!AlternateId','!BiobankCategory','!BiobankTopic','!BiobankDataType', '!BiobankSubCategory','!BiobankPersonRole');
     29
     30ALTER TABLE Biobank DROP FOREIGN KEY biobank_ibfk_5; 
     31
     32ALTER TABLE !BiobankSubCategory ADD FOREIGN KEY (id) REFERENCES !OntologyTerm (id) ON DELETE CASCADE; 
     33
     34ALTER TABLE `!BiobankSubCategory`  
     35
     36ADD CONSTRAINT !`biobank_ibfk_5` 
     37
     38    FOREIGN KEY (!`id`) REFERENCES !OntologyTerm (id) ON DELETE CASCADE; AUTO UPARXEI HDH APO THN DHMIOURGIA TOU)
     39
     40ALTER TABLE Biobank ADD FOREIGN KEY (!SubCategory) REFERENCES !BiobankSubCategory (id) ON DELETE RESTRICT; 
     41
     42ALTER TABLE `Biobank`  
     43
     44ADD CONSTRAINT !`biobank_ibfk_885` 
     45
     46    FOREIGN KEY (`!SubCategory`) REFERENCES !BiobankSubCategory (id)  ON DELETE CASCADE;
     47
     48DELETE FROM !BiobankCategory
     49
     50WHERE id  NOT IN ("281", "284", "285") ;   
     51
     52ALTER TABLE !BiobankPersonRole ADD FOREIGN KEY (id) REFERENCES !PersonRole (id) ON DELETE CASCADE;
     53
     54ALTER TABLE `!BiobankPersonRole`  
     55
     56ADD CONSTRAINT `!BiobankPersonRole_ibfk_33` 
     57
     58    FOREIGN KEY (`id`) REFERENCES !PersonRole (id)  ON DELETE CASCADE;
     59
     60UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='282';
     61
     62UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='283';
     63
     64UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='286';
     65
     66UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='287';
     67
     68UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='288';
     69
     70UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='289';
     71
     72UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='290';
     73
     74UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='291';
     75
     76UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='292';
     77
     78UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='293';
     79
     80UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='295';
     81
     82UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='296';
     83
     84UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='297';
     85
     86UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='298';
     87
     88UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='564';
     89
     90UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='299';
     91
     92UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='294';
     93
     94select * from !OntologyTerm where name="Software Engineer";
     95
     96delete from !OntologyTerm where id=561;
     97
     98select * from !BiobankPersonRole;
     99
     100insert into !BiobankPersonRole values(561);
     101
     102select * from !OntologyTerm where name="coordinator";
     103
     104delete from !OntologyTerm where id=593;
     105
     106select * from !BiobankPersonRole;
     107
     108insert into !BiobankPersonRole values(277);
     109
     110select * from !OntologyTerm where name="postdoc";
     111
     112delete from !OntologyTerm where id=594;
     113
     114 select * from !BiobankPersonRole;
     115
     116insert into !BiobankPersonRole values(278);
     117
     118select * from !OntologyTerm where name="administrator";
     119
     120delete from !OntologyTerm where id=595;
     121
     122select * from !BiobankPersonRole;
     123
     124insert into !BiobankPersonRole values(279);
     125
     126select * from !OntologyTerm where name="IT responsible";
     127
     128delete from !OntologyTerm where id=596;
     129
     130select * from !BiobankPersonRole;
     131
     132insert into !BiobankPersonRole values(280);
     133
     134 
     135
     136COMMIT;
     137
     138======================================================================================
     139
     140!SubCategory
     141
     142biobank_ibfk_1
     143
     144  CONSTRAINT !`biobank_ibfk_5` FOREIGN KEY (!`SubCategory`) REFERENCES !`BiobankSubCategory` (!`id`)
     145
     146  CONSTRAINT !`biobank_ibfk_5` FOREIGN KEY (!`SubCategory`) REFERENCES !`BiobankSubCategory` (!`id`),
     147
     148=============================WITH COMMENTS ===========================================
     149
     150CREATE TABLE !`BiobankSubCategory` (
     151
     152  !`id` int(11) NOT NULL,
     153
     154  PRIMARY KEY (!`id`),
     155
     156  CONSTRAINT !`biobanksubcategory_ibfk_1` FOREIGN KEY (!`id`) REFERENCES !`OntologyTerm` (!`id`) ON DELETE CASCADE
     157
     158) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
     159
     160/* move data from !BiobankCategory to !BiobankSubCategory table */
     161
     162INSERT INTO !BiobankSubCategory (id)
     163
     164SELECT id
     165
     166FROM !BiobankCategory
     167
     168WHERE id  NOT IN ("281", "284", "285") ;
     169
     170/*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/
     171
     172/* alter constraints */
     173
     174ALTER TABLE Biobank DROP FOREIGN KEY biobank_ibfk_5; 
     175
     176ALTER TABLE !BiobankSubCategory ADD FOREIGN KEY (id) REFERENCES !OntologyTerm (id) ON DELETE CASCADE; 
     177
     178NOT OK : ERROR 1050 (42S01): Table './bbmri/!BiobankSubCategory' already exists
     179
     180THEN USE :  
     181
     182ALTER TABLE `!BiobankSubCategory`  
     183
     184ADD CONSTRAINT !`id` 
     185
     186    FOREIGN KEY (!`id`) REFERENCES !OntologyTerm (id) ON DELETE CASCADE;
     187
     188ALTER TABLE Biobank ADD FOREIGN KEY (!SubCategory) REFERENCES !BiobankSubCategory (id) ON DELETE RESTRICT; 
     189
     190NOT OK : ERROR 1050 (42S01): Table './bbmri/Biobank' already exists
     191
     192THEN USE : 
     193
     194ALTER TABLE `Biobank`  
     195
     196ADD CONSTRAINT `!SubCategory` 
     197
     198    FOREIGN KEY (`!SubCategory`) REFERENCES !BiobankSubCategory (id)  ON DELETE CASCADE;
     199
     200/* delete moved data from !BiobankCategory */
     201
     202DELETE FROM !BiobankCategory
     203
     204WHERE id  NOT IN ("281", "284", "285") ;   
     205
     206/*IF NEEDED CORRECT THE IDs
     207
     208WHERE name IN ("A. Core Biobanks, DNA available", "B: Supporting biobanks, DNA not yet available" ,"C: Biobanken in opbouw") ;
     209
     210*/
     211
     212CREATE TABLE !BiobankPersonRole (
     213
     214        id INTEGER NOT NULL
     215
     216        , PRIMARY KEY(id)
     217
     218);
     219
     220ALTER TABLE !BiobankPersonRole ADD FOREIGN KEY (id) REFERENCES !PersonRole (id) ON DELETE CASCADE;
     221
     222alter table !OntologyTerm modify   column !__Type enum('!OntologyTerm','!PersonRole','Species','!AlternateId','!BiobankCategory','!BiobankTopic','!BiobankDataType', '!BiobankSubCategory','!BiobankPersonRole');
     223
     224UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='282';
     225
     226UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='283';
     227
     228UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='286';
     229
     230UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='287';
     231
     232UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='288';
     233
     234UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='289';
     235
     236UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='290';
     237
     238UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='291';
     239
     240UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='292';
     241
     242UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='293';
     243
     244UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='295';
     245
     246UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='296';
     247
     248UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='297';
     249
     250UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='298';
     251
     252UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='564';
     253
     254/*below we resolve the problem of duplicate entries in ontology term for !PersonRole*/
     255
     256select * from !OntologyTerm where name="Software Engineer";
     257
     258delete from !OntologyTerm where id=561;
     259
     260select * from !BiobankPersonRole;
     261
     262insert into !BiobankPersonRole values(561);
     263
     264select * from !OntologyTerm where name="coordinator";
     265
     266delete from !OntologyTerm where id=593;
     267
     268select * from !BiobankPersonRole;
     269
     270insert into !BiobankPersonRole values(277);
     271
     272select * from !OntologyTerm where name="postdoc";
     273
     274delete from !OntologyTerm where id=594;
     275
     276 select * from !BiobankPersonRole;
     277
     278insert into !BiobankPersonRole values(278);
     279
     280select * from !OntologyTerm where name="administrator";
     281
     282delete from !OntologyTerm where id=595;
     283
     284select * from !BiobankPersonRole;
     285
     286insert into !BiobankPersonRole values(279);
     287
     288select * from !OntologyTerm where name="IT responsible";
     289
     290delete from !OntologyTerm where id=596;
     291
     292select * from !BiobankPersonRole;
     293
     294insert into !BiobankPersonRole values(280);
     295
     296 
     297
     298COMMIT;