Changes between Version 1 and Version 2 of DespoinaLog_molgenis35Tomolgenis14


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

--

Legend:

Unmodified
Added
Removed
Modified

  • DespoinaLog_molgenis35Tomolgenis14

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