cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v33_34/SchemaUpdater_34_341.java

   1 // $Id$
   2 /**
   3  * Copyright (C) 2007 EDIT
   4  * European Distributed Institute of Taxonomy
   5  * http://www.e-taxonomy.eu
   6  *
   7  * The contents of this file are subject to the Mozilla Public License Version 1.1
   8  * See LICENSE.TXT at the top of this package for the full license terms.
   9  */
  10
  11 package eu.etaxonomy.cdm.database.update.v33_34;
  12
  13 import java.util.ArrayList;
  14 import java.util.Arrays;
  15 import java.util.List;
  16
  17 import org.apache.log4j.Logger;
  18 import org.hibernate.loader.custom.ColumnCollectionAliases;
  19
  20 import eu.etaxonomy.cdm.database.update.ColumnAdder;
  21 import eu.etaxonomy.cdm.database.update.ISchemaUpdater;
  22 import eu.etaxonomy.cdm.database.update.ISchemaUpdaterStep;
  23 import eu.etaxonomy.cdm.database.update.SchemaUpdaterBase;
  24 import eu.etaxonomy.cdm.database.update.SimpleSchemaUpdaterStep;
  25 import eu.etaxonomy.cdm.database.update.TableCreator;
  26 import eu.etaxonomy.cdm.database.update.TableDroper;
  27
  28 /**
  29  * @author a.mueller
  30  * @created Jan 14, 2014
  31  */
  32 public class SchemaUpdater_34_341 extends SchemaUpdaterBase {
  33
  34         @SuppressWarnings("unused")
  35         private static final Logger logger = Logger.getLogger(SchemaUpdater_34_341.class);
  36         private static final String endSchemaVersion = "3.4.1.0.201411210000";
  37         private static final String startSchemaVersion = "3.4.0.0.201407010000";
  38
  39         // ********************** FACTORY METHOD *************************************
  40
  41         public static SchemaUpdater_34_341 NewInstance() {
  42                 return new SchemaUpdater_34_341();
  43         }
  44
  45         /**
  46          * @param startSchemaVersion
  47          * @param endSchemaVersion
  48          */
  49         protected SchemaUpdater_34_341() {
  50                 super(startSchemaVersion, endSchemaVersion);
  51         }
  52
  53         @Override
  54         protected List<ISchemaUpdaterStep> getUpdaterList() {
  55
  56                 String stepName;
  57                 String tableName;
  58                 ISchemaUpdaterStep step;
  59 //              String columnName;
  60                 String newColumnName;
  61 //              String oldColumnName;
  62                 String query;
  63
  64                 List<ISchemaUpdaterStep> stepList = new ArrayList<ISchemaUpdaterStep>();
  65
  66                 //DnaMarker in Primer
  67                 //TODO H2 / PostGreSQL / SQL Server
  68                 stepName = "Add foreign key for Primer.dnaMarker";
  69                 tableName = "Primer";
  70                 newColumnName = "dnaMarker_id";
  71                 boolean notNull = false;
  72                 String referencedTable = "DefinedTermBase";
  73                 step = ColumnAdder.NewIntegerInstance(stepName, tableName, newColumnName, INCLUDE_AUDIT, notNull, referencedTable);
  74                 stepList.add(step);
  75
  76                 //Institution for DerivationEvent
  77                 stepName = "Add foreign key for DerivationEvent.institution";
  78                 tableName = "DerivationEvent";
  79                 newColumnName = "institution_id";
  80                 referencedTable = "AgentBase";
  81                 step = ColumnAdder.NewIntegerInstance(stepName, tableName, newColumnName, INCLUDE_AUDIT, notNull, referencedTable);
  82                 stepList.add(step);
  83
  84                 //Institution for Amplication
  85                 stepName = "Add foreign key for Amplification.institution";
  86                 tableName = "Amplification";
  87                 newColumnName = "institution_id";
  88                 referencedTable = "AgentBase";
  89                 step = ColumnAdder.NewIntegerInstance(stepName, tableName, newColumnName, INCLUDE_AUDIT, notNull, referencedTable);
  90                 stepList.add(step);
  91
  92                 //TaxonName for DeterminationEvent
  93                 stepName = "Add foreign key for DeterminationEvent.taxonName";
  94                 tableName = "DeterminationEvent";
  95                 newColumnName = "taxonname_id";
  96                 referencedTable = "TaxonNameBase";
  97                 step = ColumnAdder.NewIntegerInstance(stepName, tableName, newColumnName, INCLUDE_AUDIT, notNull, referencedTable);
  98                 stepList.add(step);
  99
 100                 //FIXME H2, SQL Server, PostGres
 101                 //update DerivationEvent.taxonname_id
 102                 stepName = "Update taxon name in derivation event";
 103                 query = "UPDATE DeterminationEvent dev " +
 104                                 " SET taxonname_id = (SELECT name_id FROM TaxonBase tb WHERE tb.id = dev.taxon_id) " +
 105                                 " WHERE taxon_id IS NOT NULL ";
 106                 tableName = "DeterminationEvent";
 107                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, query, "", -99);
 108                 stepList.add(step);
 109
 110
 111                 //PAT
 112                 //ad absence term
 113                 stepName = "Create absenceterm column";
 114                 tableName = "DefinedTermBase";
 115                 newColumnName = "absenceterm";
 116                 step = ColumnAdder.NewBooleanInstance(stepName, tableName, newColumnName, INCLUDE_AUDIT, null);
 117                 stepList.add(step);
 118
 119                 //set default value
 120                 stepName ="Update AbsenceTerm vocabulary";
 121                 tableName = "DefinedTermBase";
 122                 query = " UPDATE @@DefinedTermBase@@ " +
 123                 " SET absenceterm = @@FALSE@@ " +
 124                 " WHERE termType = 'PAT' ";
 125                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, query, tableName, 99);
 126                 stepList.add(step);
 127
 128                 //vocabulary for absence terms
 129                 stepName ="Update AbsenceTerm vocabulary";
 130                 tableName = "DefinedTermBase";
 131                 query = " UPDATE @@DefinedTermBase@@ " +
 132                 " SET absenceterm = @@TRUE@@, "
 133                         + " vocabulary_id = "
 134                                         + "(SELECT id FROM @@TermVocabulary@@ "
 135                                         + " WHERE uuid = 'adbbbe15-c4d3-47b7-80a8-c7d104e53a05'),"
 136                           + " orderindex = orderindex + "
 137                                         + " (SELECT max(orderindex) FROM "
 138                                         + " (SELECT * FROM DefinedTermBase dtb2 "
 139                                         + " WHERE dtb2.termtype = 'PAT' AND dtb2.absenceterm = 0 "
 140                                         + ") as tmp )" +
 141                 " WHERE DTYPE = 'AbsenceTerm' ";
 142                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, query, tableName, 99);
 143                 stepList.add(step);
 144
 145                 //PAT  - DTYPE
 146                 stepName ="Update PresenceAbsenceTerms DTYPE";
 147                 tableName = "DefinedTermBase";
 148                 query = " UPDATE @@DefinedTermBase@@ " +
 149                 " SET DTYPE = 'PresenceAbsenceTerm' " +
 150                 " WHERE termType = 'PAT' ";
 151                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, query, tableName, 99);
 152                 stepList.add(step);
 153
 154                 //PAT  - remove absence vocabulary
 155                 stepName ="Remove Absence Vocabulary I";
 156                 tableName = "TermVocabulary_Representation";
 157                 query = " DELETE FROM TermVocabulary_Representation " +
 158                                 " WHERE TermVocabulary_id in (SELECT id FROM TermVocabulary WHERE uuid = '5cd438c8-a8a1-4958-842e-169e83e2ceee') ";
 159                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, query, tableName, 99);
 160                 stepList.add(step);
 161
 162                 //PAT  - remove absence vocabulary
 163                 stepName ="Remove Absence Vocabulary II";
 164                 tableName = "TermVocabulary_Representation";
 165                 query = " DELETE FROM TermVocabulary"
 166                                 + " WHERE uuid = '5cd438c8-a8a1-4958-842e-169e83e2ceee' ";
 167                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, query, tableName, 99);
 168                 stepList.add(step);
 169
 170                 //PAT  - remove absence vocabulary
 171                 stepName ="Remove Absence Vocabulary III";
 172                 tableName = "Representation";
 173                 query = " DELETE FROM Representation "
 174                                 + " WHERE text = 'AbsenceTerm'  AND label = 'AbsenceTerm' ";
 175                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, query, tableName, 99);
 176                 stepList.add(step);
 177
 178                 //PAT  - update representation
 179                 stepName ="Update Presence Absence vocabulary representation";
 180                 tableName = "Representation";
 181                 query = " UPDATE Representation "
 182                                 + " SET text = 'Presence Absence Term', label = 'Presence Absence Term' "
 183                                 + " WHERE text = 'Presence Term'  AND label = 'Presence Term' ";
 184                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, query, tableName, 99);
 185                 stepList.add(step);
 186
 187                 //PAT  - update titlecache
 188                 stepName ="Update Presence Absence vocabulary titlecache";
 189                 tableName = "TermVocabulary";
 190                 query = " UPDATE TermVocabulary "
 191                                 + " SET titleCache = 'Presence Absence Term' "
 192                                 + " WHERE uuid = 'adbbbe15-c4d3-47b7-80a8-c7d104e53a05' ";
 193                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, query, tableName, 99);
 194                 stepList.add(step);
 195
 196
 197                 //SingleReadAlignment
 198                 stepName = "Remove Sequence_SingleRead";  //we assume that this field is not yet used
 199                 tableName = "Sequence_SingleRead";
 200                 step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT, true);
 201                 stepList.add(step);
 202
 203                 //Add SingleReadAlignment
 204                 stepName = "Add SingleReadAlignment";
 205                 tableName = "SingleReadAlignment";
 206                 String[] columnNames = new String[]{"shifts","editedsequence",
 207                                 "consensusalignment_id","singleread_id"};
 208                 String[] columnTypes = new String[]{"clob","clob","int","int"};
 209                 String[] referencedTables = new String[]{null, null,"Sequence","SingleRead"};
 210                 step = TableCreator.NewInstance(stepName, tableName, columnNames, columnTypes,
 211                                 referencedTables, INCLUDE_AUDIT, true);
 212                 stepList.add(step);
 213
 214
 215                 //SpecimenOrObservationBase_Sequence (was incorrect mapping before)
 216                 stepName = "Remove SpecimenOrObservationBase_Sequence";
 217                 tableName = "SpecimenOrObservationBase_Sequence";
 218                 step = TableDroper.NewInstance(stepName, tableName, true, true);
 219                 stepList.add(step);
 220
 221                 return stepList;
 222
 223         }
 224
 225
 226
 227         @Override
 228         public ISchemaUpdater getNextUpdater() {
 229                 return null;
 230         }
 231
 232         @Override
 233         public ISchemaUpdater getPreviousUpdater() {
 234                 return SchemaUpdater_331_34.NewInstance();
 235         }
 236
 237 }