cdmlib-model/src/main/resources/update/update_r2.1.2.2_r2.4.1.2.mysql.sql

   1 -- #################### SCHEMA #######################################################
   2
   3
   4 -- change symmetric to symmetrical to be compatible with PostGreSQL
   5  ALTER TABLE `DefinedTermBase` CHANGE `symmetric` `symmetrical` bit(1) NULL  ;
   6
   7 -- change model version to 2.4
   8 UPDATE CdmMetaData SET value = '2.4.1.2.201004231015' WHERE propertyname = 0
   9
  10 -- ##################### TERMS ########################################################
  11
  12 -- absence term max orderindex
  13 SELECT (@maxAbsenceOrderIndex := max(orderindex)) AS b FROM DefinedTermBase WHERE DTYPE = 'AbsenceTerm';
  14
  15 -- native reported in error
  16 SELECT (@presenceOrderIndex := orderindex) AS a FROM DefinedTermBase WHERE uuid = '4ba212ef-041e-418d-9d43-2ebb191b61d8';
  17 UPDATE DefinedTermBase SET uuid = '61cee840-801e-41d8-bead-015ad866c2f1', DTYPE = 'AbsenceTerm', vocabulary_id = 18, orderindex = @maxAbsenceOrderIndex + 1 WHERE uuid = '4ba212ef-041e-418d-9d43-2ebb191b61d8';
  18 UPDATE DefinedTermBase SET orderindex = orderindex -1 WHERE DTYPE = 'PresenceTerm' AND orderindex > @presenceOrderIndex ;
  19
  20
  21 -- introduced reported in error
  22 SELECT (@presenceOrderIndex := orderindex) AS a FROM DefinedTermBase WHERE uuid = '826239f7-45b7-42b5-857c-c1f852cfad6b';
  23 UPDATE DefinedTermBase SET uuid = 'aeec2947-2700-4623-8e32-9e3a430569d1', DTYPE = 'AbsenceTerm', vocabulary_id = 18, orderindex = @maxAbsenceOrderIndex + 2 WHERE uuid = '826239f7-45b7-42b5-857c-c1f852cfad6b';
  24 UPDATE DefinedTermBase SET orderindex = orderindex -1 WHERE DTYPE = 'PresenceTerm' AND orderindex > @presenceOrderIndex ;
  25
  26
  27 -- cultivated reported in error
  28 SELECT (@presenceOrderIndex := orderindex) AS a FROM DefinedTermBase WHERE uuid = 'b47f1679-0d0c-4ea7-a2e4-80709ea791c6';
  29 UPDATE DefinedTermBase SET uuid = '9d4d3431-177a-4abe-8e4b-1558573169d6', DTYPE = 'AbsenceTerm', vocabulary_id = 18, orderindex = @maxAbsenceOrderIndex + 3 WHERE uuid = 'b47f1679-0d0c-4ea7-a2e4-80709ea791c6';
  30 UPDATE DefinedTermBase SET orderindex = orderindex -1 WHERE DTYPE = 'PresenceTerm' AND orderindex > @presenceOrderIndex ;
  31
  32
  33 -- **** doubtfully present ************
  34 UPDATE DefinedTermBase SET orderindex = orderindex + 1 WHERE DTYPE = 'PresenceTerm' AND orderindex > 1 ;
  35
  36 INSERT INTO DefinedTermBase (DTYPE, id, uuid, created, protectedtitlecache, titleCache, orderindex, defaultcolor, vocabulary_id)
  37 SELECT 'PresenceTerm' ,  (@defTermId := max(id)+1)  as maxId , '85a60279-a4c2-4f53-bc57-466028a4b3db', '2010-04-01 10:15:00', b'0', 'class eu.etaxonomy.cdm.model.description.PresenceTerm: 75a60279-a4c2-4f53-bc57-466028a4b3db',2, '8dd320', 17
  38 FROM DefinedTermBase ;
  39
  40 -- language
  41 SELECT ( @langId := id) as langId FROM DefinedTermBase WHERE uuid = 'e9f8cdb7-6819-44e8-95d3-e2d0690c3523';
  42
  43 -- representation
  44 INSERT INTO Representation (id, created, uuid, text, abbreviatedlabel, label, language_id)
  45 SELECT  ( @repId := max(id)+1 ) AS maxId ,'2010-04-01 18:49:07','6453ae2f-5aed-4055-880d-44a86da2bbcc', 'present: doubtfully present','pd','doubtfully present', @langId
  46 FROM Representation;
  47 ;
  48   -- defTerm <-> representation
  49 INSERT INTO DefinedTermBase_Representation (DefinedTermBase_id, representations_id)
  50 VALUES (@defTermId,@repId);
  51
  52 -- insert new vocabulary NameFeature----
  53  INSERT INTO TermVocabulary (id, DTYPE, uuid, protectedTitleCache, titleCache, termsourceuri)
  54 SELECT ( @vocId := max(id)+1 ) AS maxId , 'TermVocabulary', 'a7ca3eef-4092-49e1-beec-ed5096193e5e', FALSE, 'class eu.etaxonomy.cdm.model.common.TermVocabulary: a7ca3eef-4092-49e1-beec-ed5096193e5e', 'eu.etaxonomy.cdm.model.description.Feature'
  55 FROM TermVocabulary;
  56
  57 INSERT INTO Representation (id, created, uuid, text, abbreviatedlabel, label, language_id)
  58 SELECT  ( @repId := max(id)+1 ) AS maxId ,'2010-04-01 18:49:07','44b0012d-98de-431d-8c9b-85c014a7a6a9', 'eu.etaxonomy.cdm.model.description.Feature',null,'Name Feature', @langId
  59 FROM Representation;
  60
  61 INSERT INTO TermVocabulary_Representation (TermVocabulary_id, representations_id)
  62 VALUES (@vocId,@repId);
  63
  64
  65  -- change the vocabulary id for "Protologue" and "Additional Publication"
  66  UPDATE DefinedTermBase SET vocabulary_id = @vocId, uuid = '2c355c16-cb04-4858-92bf-8da8d56dea95' WHERE uuid = 'cb2eab09-6d9d-4e43-8ad2-873f23400930' ;
  67  UPDATE DefinedTermBase SET vocabulary_id = @vocId, uuid = '71b356c5-1e3f-4f5d-9b0f-c2cf8ae7779f' WHERE uuid = '7f1fd111-fc52-49f0-9e75-d0097f576b2d'  ;
  68
  69  -- change the text of Protolog to Protologue --
  70   UPDATE Representation SET text = 'Protologue', label = 'Protologue' WHERE text like 'Protol%';
  71
  72
  73 -- **** reference system: google earth ************
  74 SELECT ( @refSysVocId := id) as vocId FROM TermVocabulary WHERE uuid = 'ec6376e5-0c9c-4f5c-848b-b288e6c17a86';
  75
  76 INSERT INTO DefinedTermBase (DTYPE, id, uuid, created, protectedtitlecache, titleCache, orderindex, defaultcolor, vocabulary_id)
  77 SELECT 'ReferenceSystem' ,  (@defTermId := max(id)+1)  as maxId , '1bb67042-2814-4b09-9e76-c8c1e68aa281', '2010-06-01 10:15:00', b'0', 'Google Earth', null, null, @refSysVocId
  78 FROM DefinedTermBase ;
  79
  80 -- language english
  81 SELECT ( @langId := id) as langId FROM DefinedTermBase WHERE uuid = 'e9f8cdb7-6819-44e8-95d3-e2d0690c3523';
  82
  83 -- representation
  84 INSERT INTO Representation (id, created, uuid, text, abbreviatedlabel, label, language_id)
  85 SELECT  ( @repId := max(id)+1 ) AS maxId ,'2010-06-01 18:49:07','fadb1730-9936-44e7-8911-884a84662b08', 'Google Earth','Google','Google Earth', @langId
  86 FROM Representation;
  87 ;
  88
  89  -- defTerm <-> representation
  90 INSERT INTO DefinedTermBase_Representation (DefinedTermBase_id, representations_id)
  91 VALUES (@defTermId,@repId);