Changes between Version 3 and Version 4 of SopUpdateBbmriApp


Ignore:
Timestamp:
2013-04-24T11:31:42+02:00 (12 years ago)
Author:
antonak
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • SopUpdateBbmriApp

    v3 v4  
    3636 * Check your deployment at: [https://molgenis14.target.rug.nl/biobanks/ https://molgenis35.target.rug.nl/biobanks/]
    3737
    38      '''The same steps apply to the production server molgenis14 . '''
     38== ''' The same steps apply to the production server molgenis14 . ''' ==
     39     
     40== '''Appendix ''' ==
     41   * Find miscellaneous issues here: http://www.molgenis.org/wiki/DespoinaLog
     42 * An old example migration file: 
     43
     44   
     45{{{
     46   CREATE TABLE !`BiobankSubCategory` (
     47    !`id` int(11) NOT NULL,
     48    PRIMARY KEY (!`id`),
     49    CONSTRAINT !`biobanksubcategory_ibfk_1` FOREIGN KEY (!`id`) REFERENCES !`OntologyTerm` (!`id`) ON DELETE CASCADE
     50  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
     51   
     52   
     53  INSERT INTO !BiobankSubCategory (id)
     54  SELECT id
     55  FROM !BiobankCategory
     56  WHERE id  NOT IN ("281", "284", "285") ;
     57   
     58  CREATE TABLE !BiobankPersonRole (
     59              id INTEGER NOT NULL
     60              , PRIMARY KEY(id)
     61  );
     62   
     63  alter table !OntologyTerm modify   column !__Type enum('!OntologyTerm','!PersonRole','Species','!AlternateId','!BiobankCategory','!BiobankTopic','!BiobankDataType', '!BiobankSubCategory','!BiobankPersonRole');
     64   
     65   
     66  ALTER TABLE Biobank DROP FOREIGN KEY biobank_ibfk_5;
     67   
     68  ALTER TABLE !BiobankSubCategory ADD FOREIGN KEY (id) REFERENCES !OntologyTerm (id) ON DELETE CASCADE;
     69   
     70  ALTER TABLE !`BiobankSubCategory` 
     71  ADD CONSTRAINT !`biobank_ibfk_5`
     72      FOREIGN KEY (!`id`) REFERENCES !OntologyTerm (id) ON DELETE CASCADE; AUTO UPARXEI HDH APO THN DHMIOURGIA TOU)
     73   
     74  ALTER TABLE Biobank ADD FOREIGN KEY (!SubCategory) REFERENCES !BiobankSubCategory (id) ON DELETE RESTRICT;
     75   
     76  ALTER TABLE !`Biobank` 
     77  ADD CONSTRAINT !`biobank_ibfk_885`
     78      FOREIGN KEY (!`SubCategory`) REFERENCES !BiobankSubCategory (id)  ON DELETE CASCADE;
     79   
     80   
     81   
     82  DELETE FROM !BiobankCategory
     83  WHERE id  NOT IN ("281", "284", "285") ;  
     84   
     85   
     86   
     87  ALTER TABLE !BiobankPersonRole ADD FOREIGN KEY (id) REFERENCES !PersonRole (id) ON DELETE CASCADE;
     88   
     89   
     90  ALTER TABLE !`BiobankPersonRole` 
     91  ADD CONSTRAINT !`BiobankPersonRole_ibfk_33`
     92      FOREIGN KEY (!`id`) REFERENCES !PersonRole (id)  ON DELETE CASCADE;
     93   
     94  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='282';
     95  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='283';
     96  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='286';
     97  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='287';
     98  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='288';
     99  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='289';
     100  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='290';
     101  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='291';
     102  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='292';
     103  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='293';
     104  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='295';
     105  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='296';
     106  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='297';
     107  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='298';
     108  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='564';
     109  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='299';
     110  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='294';
     111   
     112   
     113   
     114  select * from !OntologyTerm where name="Software Engineer";
     115  delete from !OntologyTerm where id=561;
     116  select * from !BiobankPersonRole;
     117  insert into !BiobankPersonRole values(561);
     118   
     119  select * from !OntologyTerm where name="coordinator";
     120  delete from !OntologyTerm where id=593;
     121  select * from !BiobankPersonRole;
     122  insert into !BiobankPersonRole values(277);
     123   
     124  select * from !OntologyTerm where name="postdoc";
     125  delete from !OntologyTerm where id=594;
     126   select * from !BiobankPersonRole;
     127  insert into !BiobankPersonRole values(278);
     128   
     129  select * from !OntologyTerm where name="administrator";
     130  delete from !OntologyTerm where id=595;
     131  select * from !BiobankPersonRole;
     132  insert into !BiobankPersonRole values(279);
     133   
     134   
     135  select * from !OntologyTerm where name="IT responsible";
     136  delete from !OntologyTerm where id=596;
     137  select * from !BiobankPersonRole;
     138  insert into !BiobankPersonRole values(280);
     139   
     140  //how to make values of Person Role appear in ui , you have to delete some old from DB :
     141   
     142  create a new Coordinator Role in the ui and remember the id 591 (select * from !PersonRole; --> the latest one, or check from ui )
     143  correct the Person table with t he new entry(that is not show in the ui) :
     144   
     145  update Person set Roles=591 where Roles=277
     146   
     147  delete the old one : 
     148   
     149  delete from !PersonRole where id=277;
     150  delete from  !BiobankPersonRole  where id=277;
     151  delete from !OntologyTerm  where id=277;
     152   
     153  repeat the same step for all of them
     154   
     155   
     156  COMMIT;
     157   
     158  ===========================================================
     159  !SubCategory
     160  biobank_ibfk_1
     161    CONSTRAINT !`biobank_ibfk_5` FOREIGN KEY (!`SubCategory`) REFERENCES !`BiobankSubCategory` (!`id`)
     162    CONSTRAINT !`biobank_ibfk_5` FOREIGN KEY (!`SubCategory`) REFERENCES !`BiobankSubCategory` (!`id`),
     163   
     164   
     165  CREATE TABLE !BiobankPersonRole (
     166              id INTEGER NOT NULL
     167              , PRIMARY KEY(id)
     168  );
     169   
     170  ALTER TABLE !BiobankPersonRole ADD FOREIGN KEY (id) REFERENCES !PersonRole (id) ON DELETE CASCADE;
     171   
     172  alter table !OntologyTerm modify   column !__Type enum('!OntologyTerm','!PersonRole','Species','!AlternateId','!BiobankCategory','!BiobankTopic','!BiobankDataType', '!BiobankSubCategory','!BiobankPersonRole');
     173   
     174  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='282';
     175  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='283';
     176  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='286';
     177  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='287';
     178  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='288';
     179  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='289';
     180  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='290';
     181  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='291';
     182  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='292';
     183  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='293';
     184  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='295';
     185  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='296';
     186  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='297';
     187  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='298';
     188  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='564';
     189   
     190  /*below we resolve the problem of duplicate entries in ontology term for !PersonRole*/
     191   
     192  select * from !OntologyTerm where name="Software Engineer";
     193  delete from !OntologyTerm where id=561;
     194  select * from !BiobankPersonRole;
     195  insert into !BiobankPersonRole values(561);
     196   
     197  select * from !OntologyTerm where name="coordinator";
     198  delete from !OntologyTerm where id=593;
     199  select * from !BiobankPersonRole;
     200  insert into !BiobankPersonRole values(277);
     201   
     202  select * from !OntologyTerm where name="postdoc";
     203  delete from !OntologyTerm where id=594;
     204   select * from !BiobankPersonRole;
     205  insert into !BiobankPersonRole values(278);
     206   
     207  select * from !OntologyTerm where name="administrator";
     208  delete from !OntologyTerm where id=595;
     209  select * from !BiobankPersonRole;
     210  insert into !BiobankPersonRole values(279);
     211   
     212   
     213  select * from !OntologyTerm where name="IT responsible";
     214  delete from !OntologyTerm where id=596;
     215  select * from !BiobankPersonRole;
     216  insert into !BiobankPersonRole values(280);
     217   
     218   
     219  COMMIT;
     220}}}