Changes between Version 33 and Version 34 of SopUpdateBbmriApp


Ignore:
Timestamp:
2013-05-29T14:25:05+02:00 (11 years ago)
Author:
antonak
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • SopUpdateBbmriApp

    v33 v34  
    5858 * <apply mysql commands>
    5959
    60 '''2.8 Backup & replace the war file on server''':
    61 
     60=== '''2.8 Backup & replace the war file on server''': ===
    6261 * The war file is in /usr/share/tomcat6/webapps/biobanks.war
    6362 * BACKUP : cp  /usr/share/tomcat6/webapps/biobanks.war ~/warFiles/<date_bbmri.war>
    … …  
    7877= 5. Deploy at live server =
    7978== Copy war & db from test to live ==
    80  * From your computer: 
    81    * scp  molgenis@molgenis35.target.rug.nl:./usr/share/tomcat6/webapps/biobanks.war biobanksLatestTest.war 
    82    * scp  molgenis@molgenis35.target.rug.nl:../databaseDumps/mysql<date>LIVEdata.mysql LIVEdataSynchonized.mysql  
    83  * To live server: 
    84    * scp  biobanksLatestTest.war  molgenis@molgenis14.target.rug.nl:./biobanksLatestTest.war  
    85    * scp  LIVEdataSynchonized.mysql molgenis@molgenis14.target.rug.nl:../databaseDumps/LIVEdataSynchonized.mysql
    86  * in live server : 
     79 * From your computer:
     80   * scp  molgenis@molgenis35.target.rug.nl:./usr/share/tomcat6/webapps/biobanks.war biobanksLatestTest.war
     81   * scp  molgenis@molgenis35.target.rug.nl:../databaseDumps/mysql<date>LIVEdata.mysql LIVEdataSynchonized.mysql
     82 * To live server:
     83   * scp  biobanksLatestTest.war  molgenis@molgenis14.target.rug.nl:./biobanksLatestTest.war
     84   * scp  LIVEdataSynchonized.mysql molgenis@molgenis14.target.rug.nl:../databaseDumps/LIVEdataSynchonized.mysql
     85 * in live server :
    8786   * backup your database & application before moving the new files:
    88      * cp   /usr/share/tomcat6/webapps/biobanks.war  ~/warFiles/biobanks<date>.war
     87     * cp   /usr/share/tomcat6/webapps/biobanks.war  ~/warFiles/biobanks<date>.war
    8988     * mysqldump bbmri -u molgenis –pmolgenis > ~/databaseDumps/<date>bbmriDB.mysql
    90    * Replace the war file on server as in step 2.8 
    91  * check your deployment at !https://catalogue.bbmri.nl/biobanks/molgenis.do 
    92 
    93 
     89   * Replace the war file on server as in step 2.8
     90 * check your deployment at !https://catalogue.bbmri.nl/biobanks/molgenis.do
    9491
    9592'''Appendix'''
    … …  
    9996
    10097{{{
    101    CREATE TABLE !`BiobankSubCategory` (
    102     !`id` int(11) NOT NULL,
    103     PRIMARY KEY (!`id`),
    104     CONSTRAINT !`biobanksubcategory_ibfk_1` FOREIGN KEY (!`id`) REFERENCES !`OntologyTerm` (!`id`) ON DELETE CASCADE
    105   ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    106    
    107    
    108   INSERT INTO !BiobankSubCategory (id)
    109   SELECT id
    110   FROM !BiobankCategory
    111   WHERE id  NOT IN ("281", "284", "285") ;
    112    
    113   CREATE TABLE !BiobankPersonRole (
    114               id INTEGER NOT NULL
    115               , PRIMARY KEY(id)
    116   );
    117    
    118   alter table !OntologyTerm modify   column !__Type enum('!OntologyTerm','!PersonRole','Species','!AlternateId','!BiobankCategory','!BiobankTopic','!BiobankDataType', '!BiobankSubCategory','!BiobankPersonRole');
    119    
    120    
    121   ALTER TABLE Biobank DROP FOREIGN KEY biobank_ibfk_5;
    122    
    123   ALTER TABLE !BiobankSubCategory ADD FOREIGN KEY (id) REFERENCES !OntologyTerm (id) ON DELETE CASCADE;
    124    
    125   ALTER TABLE !`BiobankSubCategory` 
    126   ADD CONSTRAINT !`biobank_ibfk_5`
    127       FOREIGN KEY (!`id`) REFERENCES !OntologyTerm (id) ON DELETE CASCADE; this exists already from his creation)
    128    
    129   ALTER TABLE Biobank ADD FOREIGN KEY (!SubCategory) REFERENCES !BiobankSubCategory (id) ON DELETE RESTRICT;
    130    
    131   ALTER TABLE !`Biobank` 
    132   ADD CONSTRAINT !`biobank_ibfk_885`
    133       FOREIGN KEY (!`SubCategory`) REFERENCES !BiobankSubCategory (id)  ON DELETE CASCADE;
    134    
    135    
    136    
    137   DELETE FROM !BiobankCategory
    138   WHERE id  NOT IN ("281", "284", "285") ;  
    139    
    140    
    141    
    142   ALTER TABLE !BiobankPersonRole ADD FOREIGN KEY (id) REFERENCES !PersonRole (id) ON DELETE CASCADE;
    143    
    144    
    145   ALTER TABLE !`BiobankPersonRole` 
    146   ADD CONSTRAINT !`BiobankPersonRole_ibfk_33`
    147       FOREIGN KEY (!`id`) REFERENCES !PersonRole (id)  ON DELETE CASCADE;
    148    
    149   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='282';
    150   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='283';
    151   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='286';
    152   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='287';
    153   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='288';
    154   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='289';
    155   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='290';
    156   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='291';
    157   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='292';
    158   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='293';
    159   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='295';
    160   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='296';
    161   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='297';
    162   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='298';
    163   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='564';
    164   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='299';
    165   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='294';
    166    
    167    
    168    
    169   select * from !OntologyTerm where name="Software Engineer";
    170   delete from !OntologyTerm where id=561;
    171   select * from !BiobankPersonRole;
    172   insert into !BiobankPersonRole values(561);
    173    
    174   select * from !OntologyTerm where name="coordinator";
    175   delete from !OntologyTerm where id=593;
    176   select * from !BiobankPersonRole;
    177   insert into !BiobankPersonRole values(277);
    178    
    179   select * from !OntologyTerm where name="postdoc";
    180   delete from !OntologyTerm where id=594;
    181    select * from !BiobankPersonRole;
    182   insert into !BiobankPersonRole values(278);
    183    
    184   select * from !OntologyTerm where name="administrator";
    185   delete from !OntologyTerm where id=595;
    186   select * from !BiobankPersonRole;
    187   insert into !BiobankPersonRole values(279);
    188    
    189    
    190   select * from !OntologyTerm where name="IT responsible";
    191   delete from !OntologyTerm where id=596;
    192   select * from !BiobankPersonRole;
    193   insert into !BiobankPersonRole values(280);
    194    
    195   //how to make values of Person Role appear in ui , you have to delete some old from DB :
    196    
    197   create a new Coordinator Role in the ui and remember the id 591 (select * from !PersonRole; --> the latest one, or check from ui )
    198   correct the Person table with t he new entry(that is not show in the ui) :
    199    
    200   update Person set Roles=591 where Roles=277
    201    
    202   delete the old one : 
    203    
    204   delete from !PersonRole where id=277;
    205   delete from  !BiobankPersonRole  where id=277;
    206   delete from !OntologyTerm  where id=277;
    207    
    208   repeat the same step for all of them
    209    
    210    
    211   COMMIT;
    212    
    213   ===========================================================
    214   !SubCategory
    215   biobank_ibfk_1
    216     CONSTRAINT !`biobank_ibfk_5` FOREIGN KEY (!`SubCategory`) REFERENCES !`BiobankSubCategory` (!`id`)
    217     CONSTRAINT !`biobank_ibfk_5` FOREIGN KEY (!`SubCategory`) REFERENCES !`BiobankSubCategory` (!`id`),
    218    
    219    
    220   CREATE TABLE !BiobankPersonRole (
    221               id INTEGER NOT NULL
    222               , PRIMARY KEY(id)
    223   );
    224    
    225   ALTER TABLE !BiobankPersonRole ADD FOREIGN KEY (id) REFERENCES !PersonRole (id) ON DELETE CASCADE;
    226    
    227   alter table !OntologyTerm modify   column !__Type enum('!OntologyTerm','!PersonRole','Species','!AlternateId','!BiobankCategory','!BiobankTopic','!BiobankDataType', '!BiobankSubCategory','!BiobankPersonRole');
    228    
    229   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='282';
    230   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='283';
    231   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='286';
    232   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='287';
    233   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='288';
    234   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='289';
    235   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='290';
    236   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='291';
    237   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='292';
    238   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='293';
    239   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='295';
    240   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='296';
    241   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='297';
    242   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='298';
    243   UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='564';
    244    
    245   /*below we resolve the problem of duplicate entries in ontology term for !PersonRole*/
    246    
    247   select * from !OntologyTerm where name="Software Engineer";
    248   delete from !OntologyTerm where id=561;
    249   select * from !BiobankPersonRole;
    250   insert into !BiobankPersonRole values(561);
    251    
    252   select * from !OntologyTerm where name="coordinator";
    253   delete from !OntologyTerm where id=593;
    254   select * from !BiobankPersonRole;
    255   insert into !BiobankPersonRole values(277);
    256    
    257   select * from !OntologyTerm where name="postdoc";
    258   delete from !OntologyTerm where id=594;
    259    select * from !BiobankPersonRole;
    260   insert into !BiobankPersonRole values(278);
    261    
    262   select * from !OntologyTerm where name="administrator";
    263   delete from !OntologyTerm where id=595;
    264   select * from !BiobankPersonRole;
    265   insert into !BiobankPersonRole values(279);
    266    
    267    
    268   select * from !OntologyTerm where name="IT responsible";
    269   delete from !OntologyTerm where id=596;
    270   select * from !BiobankPersonRole;
    271   insert into !BiobankPersonRole values(280);
    272    
    273    
    274   COMMIT;
     98   '''How to update bbmri app'''
    27599}}}
     100       '''Quick tutorial'''
     101
     1021) Copy the mysqldump from the live server to the test server [[BR]] 2) Deploy the new war file on the testserver[[BR]] 2.1) Check if there are errors according to changes in the model. There are 2 ways:
     103
     104
     105 * compare the mysql from the live and the testserver
     106 * check in the application if there are errors
     107
     1082.2) If there are changes, make the migration script[[BR]]
     109
     110'''Detailed tutorial'''
     111
     112'''1. Export war & mysql file locally'''
     113
     114'''1.1 Run the app on your molgenis-legacy workspace (using tomcat, not standalone)'''
     115
     116
     117 1. Export the war file
     118   1. In eclipse: File--> export --> Web --> war file
     119 1. The database is updated as follows: apply the mysql changes that you have made locally to the database on the server, so that you have the latest version of data for the server and the changes you want applied there. About the database changes you need to track them in a migration a file like one in the end of this document.
     120
     121'''2. Deploy to test server'''
     122
     123
     124 1. As also listed here: http://www.molgenis.org/wiki/MolgenisServers the '''test server''' is at molgenis35(https://molgenis35.target.rug.nl/biobanks/) and the '''production server''' at molgenis14(https://catalogue.bbmri.nl/biobanks/) .
     125 1. First deploy at test server, notify [mailto:david.van.enckevort@nbic.nl david.van.enckevort AT nbic.nl]. He will notify BBMRI office, they will review the changes and if everything is ok proceed to updating bbmri production server.
     126 1. Deploy at test server: molgenis35 (Take a look here also http://www.molgenis.org/wiki/SopCreateMolgenisVM#SopDeploymentForDevs ) :
     127   1. Ask credentials for molgenis35/molgenis14 from Pieter or Morris.
     128
     129'''2.1 Create migration script according to data in live server'''
     130
     131
     132 1. If you know the exact changes of the database, compared to the old version in live server, then the migration script is consisted of the commands that synchornize the two versions.
     133 1. You do not know, you can can compare the mysql dump files between the live and the local version. This is easier if you dump only the schemas (not the data) .
     134   1. First ssh to molgenis14, create file & copy it
     135     1. ssh molgenis@molgenis14.target.rug.nl
     136     1. create a dump file: mysqldump --no-data, -d bbmri -u molgenis –pmolgenis > ~/databaseDumps/<date>bbmriDBlive.mysql
     137     1. LOCALLY : scp molgenis@molgenis14.target.rug.nl:./databaseDumps/<date>bbmriDB.mysql ./mysql<date>LIVEdata.mysql
     138   1. create a local dump file: mysqldump --no-data, -d bbmri -u molgenis –pmolgenis > ~/databaseDumps/<date>bbmriDBlocal.mysql
     139   1. diff <date>bbmriDBlive.mysql <date>bbmriDBlocal.mysql
     140
     141'''2.2 Copy war file & migration script from your computer to test server'''
     142
     143
     144 1. Copy the war file and the mysql migration script from your computer to the server as follows:
     145   1. scp <filename>.war molgenis@molgenis35.target.rug.nl:./
     146   1. scp <migrationScriptFilename> molgenis@molgenis35.target.rug.nl:./
     147
     148'''2.3 Copy live data from your computer to test server'''
     149
     150
     151 1. scp ./mysql<date>LIVEdata.mysql molgenis@molgenis35.target.rug.nl:./databaseDumps/mysql<date>LIVEdata.mysql
     152
     153'''2.4 Deploy at test server'''
     154
     155
     156 1. Ssh to the server:
     157   * ssh molgenis@molgenis35.target.rug.nl
     158   * You are now at /srv/molgenis/ where your files where copied.
     159
     160'''2.5 Backup Database'''
     161
     162
     163 1. mysqldump bbmri -u molgenis –pmolgenis > ~/databaseDumps/<date>bbmriDB.mysql
     164 1. Check the dumbed file if it is not just an error file …
     165
     166'''2.6 Insert the data from live server to test server'''
     167
     168
     169 * '''Insert live data (copied from your computer previously)'''
     170   * mysql -u molgenis -pmolgenis bbmri < ./databaseDumps/mysql<date>LIVEdata.mysql
     171
     172'''2.7 Apply migration steps:'''
     173
     174
     175 * mysql -u molgenis –pmolgenis
     176 * use bbmri
     177 * <apply mysql commands>
     178
     179'''2.8 Backup & replace the war file on server:'''
     180
     181
     182 * The war file is in /usr/share/tomcat6/webapps/biobanks.war
     183 * BACKUP : cp /usr/share/tomcat6/webapps/biobanks.war ~/warFiles/<date_bbmri.war>
     184 * Delete the old *.war (Do NOT stop tomcat; leave it running): rm /usr/share/tomcat6/webapps/biobanks.war
     185 * Wait a few seconds; Tomcat will notice the *.war is gone and then it will remove the unzipped folder.
     186 * Copy the the new *.war to the webapps dir; Tomcat will detect the new *.war and unzip it.
     187   * cp <filename>.war /usr/share/tomcat6/webapps/biobanks.war
     188 * You can check the tomcat & mysql service with:
     189   * sudo service tomcat6 status
     190   * sudo service mysqld status
     191
     192'''3. Check your deployment'''
     193
     194
     195 * Check your deployment at: https://molgenis35.target.rug.nl/biobanks/
     196
     197'''4. Inform bbmri office'''
     198
     199
     200 * Email David [mailto:david.van.enckevort@nbic.nl david.van.enckevort AT nbic.nl]. and wait till they test the server. When that step is done you can deploy to live server.
     201
     202'''5. Deploy at live server'''
     203
     204'''Copy war & db from test to live'''
     205
     206
     207 * From your computer:
     208   * scp molgenis@molgenis35.target.rug.nl:./usr/share/tomcat6/webapps/biobanks.war biobanksLatestTest.war
     209   * scp molgenis@molgenis35.target.rug.nl:../databaseDumps/mysql<date>LIVEdata.mysql LIVEdataSynchonized.mysql
     210 * To live server:
     211   * scp biobanksLatestTest.war molgenis@molgenis14.target.rug.nl:./biobanksLatestTest.war
     212   * scp LIVEdataSynchonized.mysql molgenis@molgenis14.target.rug.nl:../databaseDumps/LIVEdataSynchonized.mysql
     213 * in live server :
     214   * backup your database & application before moving the new files:
     215     * cp /usr/share/tomcat6/webapps/biobanks.war ~/warFiles/biobanks<date>.war
     216     * mysqldump bbmri -u molgenis –pmolgenis > ~/databaseDumps/<date>bbmriDB.mysql
     217   * Replace the war file on server as in step 2.8
     218 * check your deployment at !https://catalogue.bbmri.nl/biobanks/molgenis.do
     219
     220'''Appendix'''
     221
     222
     223 * Find miscellaneous issues here: http://www.molgenis.org/wiki/DespoinaLog
     224 * An old example migration file:
     225
     226   CREATE TABLE !`BiobankSubCategory` (
     227
     228    !`id` int(11) NOT NULL,
     229
     230    PRIMARY KEY (!`id`),
     231
     232    CONSTRAINT !`biobanksubcategory_ibfk_1` FOREIGN KEY (!`id`) REFERENCES !`OntologyTerm` (!`id`) ON DELETE CASCADE
     233
     234  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
     235
     236   
     237
     238   
     239
     240  INSERT INTO !BiobankSubCategory (id)
     241
     242  SELECT id
     243
     244  FROM !BiobankCategory
     245
     246  WHERE id  NOT IN ("281", "284", "285") ;
     247
     248   
     249
     250  CREATE TABLE !BiobankPersonRole (
     251
     252              id INTEGER NOT NULL
     253
     254              , PRIMARY KEY(id)
     255
     256  );
     257
     258   
     259
     260  alter table !OntologyTerm modify   column !__Type enum('!OntologyTerm','!PersonRole','Species','!AlternateId','!BiobankCategory','!BiobankTopic','!BiobankDataType', '!BiobankSubCategory','!BiobankPersonRole');
     261
     262   
     263
     264   
     265
     266  ALTER TABLE Biobank DROP FOREIGN KEY biobank_ibfk_5;
     267
     268   
     269
     270  ALTER TABLE !BiobankSubCategory ADD FOREIGN KEY (id) REFERENCES !OntologyTerm (id) ON DELETE CASCADE;
     271
     272   
     273
     274  ALTER TABLE !`BiobankSubCategory` 
     275
     276  ADD CONSTRAINT !`biobank_ibfk_5`
     277
     278      FOREIGN KEY (!`id`) REFERENCES !OntologyTerm (id) ON DELETE CASCADE; this exists already from his creation)
     279
     280   
     281
     282  ALTER TABLE Biobank ADD FOREIGN KEY (!SubCategory) REFERENCES !BiobankSubCategory (id) ON DELETE RESTRICT;
     283
     284   
     285
     286  ALTER TABLE !`Biobank` 
     287
     288  ADD CONSTRAINT !`biobank_ibfk_885`
     289
     290      FOREIGN KEY (!`SubCategory`) REFERENCES !BiobankSubCategory (id)  ON DELETE CASCADE;
     291
     292   
     293
     294   
     295
     296   
     297
     298  DELETE FROM !BiobankCategory
     299
     300  WHERE id  NOT IN ("281", "284", "285") ;  
     301
     302   
     303
     304   
     305
     306   
     307
     308  ALTER TABLE !BiobankPersonRole ADD FOREIGN KEY (id) REFERENCES !PersonRole (id) ON DELETE CASCADE;
     309
     310   
     311
     312   
     313
     314  ALTER TABLE !`BiobankPersonRole` 
     315
     316  ADD CONSTRAINT !`BiobankPersonRole_ibfk_33`
     317
     318      FOREIGN KEY (!`id`) REFERENCES !PersonRole (id)  ON DELETE CASCADE;
     319
     320   
     321
     322  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='282';
     323
     324  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='283';
     325
     326  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='286';
     327
     328  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='287';
     329
     330  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='288';
     331
     332  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='289';
     333
     334  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='290';
     335
     336  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='291';
     337
     338  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='292';
     339
     340  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='293';
     341
     342  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='295';
     343
     344  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='296';
     345
     346  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='297';
     347
     348  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='298';
     349
     350  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='564';
     351
     352  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='299';
     353
     354  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='294';
     355
     356   
     357
     358   
     359
     360   
     361
     362  select * from !OntologyTerm where name="Software Engineer";
     363
     364  delete from !OntologyTerm where id=561;
     365
     366  select * from !BiobankPersonRole;
     367
     368  insert into !BiobankPersonRole values(561);
     369
     370   
     371
     372  select * from !OntologyTerm where name="coordinator";
     373
     374  delete from !OntologyTerm where id=593;
     375
     376  select * from !BiobankPersonRole;
     377
     378  insert into !BiobankPersonRole values(277);
     379
     380   
     381
     382  select * from !OntologyTerm where name="postdoc";
     383
     384  delete from !OntologyTerm where id=594;
     385
     386   select * from !BiobankPersonRole;
     387
     388  insert into !BiobankPersonRole values(278);
     389
     390   
     391
     392  select * from !OntologyTerm where name="administrator";
     393
     394  delete from !OntologyTerm where id=595;
     395
     396  select * from !BiobankPersonRole;
     397
     398  insert into !BiobankPersonRole values(279);
     399
     400   
     401
     402   
     403
     404  select * from !OntologyTerm where name="IT responsible";
     405
     406  delete from !OntologyTerm where id=596;
     407
     408  select * from !BiobankPersonRole;
     409
     410  insert into !BiobankPersonRole values(280);
     411
     412   
     413
     414  //how to make values of Person Role appear in ui , you have to delete some old from DB :
     415
     416   
     417
     418  create a new Coordinator Role in the ui and remember the id 591 (select * from !PersonRole; --> the latest one, or check from ui )
     419
     420  correct the Person table with t he new entry(that is not show in the ui) :
     421
     422   
     423
     424  update Person set Roles=591 where Roles=277
     425
     426   
     427
     428  delete the old one : 
     429
     430   
     431
     432  delete from !PersonRole where id=277;
     433
     434  delete from  !BiobankPersonRole  where id=277;
     435
     436  delete from !OntologyTerm  where id=277;
     437
     438   
     439
     440  repeat the same step for all of them
     441
     442   
     443
     444   
     445
     446  COMMIT;
     447
     448   
     449
     450  ===========================================================
     451
     452  !SubCategory
     453
     454  biobank_ibfk_1
     455
     456    CONSTRAINT !`biobank_ibfk_5` FOREIGN KEY (!`SubCategory`) REFERENCES !`BiobankSubCategory` (!`id`)
     457
     458    CONSTRAINT !`biobank_ibfk_5` FOREIGN KEY (!`SubCategory`) REFERENCES !`BiobankSubCategory` (!`id`),
     459
     460   
     461
     462   
     463
     464  CREATE TABLE !BiobankPersonRole (
     465
     466              id INTEGER NOT NULL
     467
     468              , PRIMARY KEY(id)
     469
     470  );
     471
     472   
     473
     474  ALTER TABLE !BiobankPersonRole ADD FOREIGN KEY (id) REFERENCES !PersonRole (id) ON DELETE CASCADE;
     475
     476   
     477
     478  alter table !OntologyTerm modify   column !__Type enum('!OntologyTerm','!PersonRole','Species','!AlternateId','!BiobankCategory','!BiobankTopic','!BiobankDataType', '!BiobankSubCategory','!BiobankPersonRole');
     479
     480   
     481
     482  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='282';
     483
     484  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='283';
     485
     486  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='286';
     487
     488  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='287';
     489
     490  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='288';
     491
     492  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='289';
     493
     494  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='290';
     495
     496  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='291';
     497
     498  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='292';
     499
     500  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='293';
     501
     502  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='295';
     503
     504  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='296';
     505
     506  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='297';
     507
     508  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='298';
     509
     510  UPDATE !OntologyTerm  SET !__Type="!BiobankSubCategory" WHERE id='564';
     511
     512   
     513
     514  /*below we resolve the problem of duplicate entries in ontology term for !PersonRole*/
     515
     516   
     517
     518  select * from !OntologyTerm where name="Software Engineer";
     519
     520  delete from !OntologyTerm where id=561;
     521
     522  select * from !BiobankPersonRole;
     523
     524  insert into !BiobankPersonRole values(561);
     525
     526   
     527
     528  select * from !OntologyTerm where name="coordinator";
     529
     530  delete from !OntologyTerm where id=593;
     531
     532  select * from !BiobankPersonRole;
     533
     534  insert into !BiobankPersonRole values(277);
     535
     536   
     537
     538  select * from !OntologyTerm where name="postdoc";
     539
     540  delete from !OntologyTerm where id=594;
     541
     542   select * from !BiobankPersonRole;
     543
     544  insert into !BiobankPersonRole values(278);
     545
     546   
     547
     548  select * from !OntologyTerm where name="administrator";
     549
     550  delete from !OntologyTerm where id=595;
     551
     552  select * from !BiobankPersonRole;
     553
     554  insert into !BiobankPersonRole values(279);
     555
     556   
     557
     558   
     559
     560  select * from !OntologyTerm where name="IT responsible";
     561
     562  delete from !OntologyTerm where id=596;
     563
     564  select * from !BiobankPersonRole;
     565
     566  insert into !BiobankPersonRole values(280);
     567
     568   
     569
     570   
     571
     572  COMMIT;