cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v31_33/SchemaUpdater_31_33.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.v31_33;
  12
  13 import java.util.ArrayList;
  14 import java.util.List;
  15
  16 import org.apache.log4j.Logger;
  17
  18 import eu.etaxonomy.cdm.database.update.ColumnAdder;
  19 import eu.etaxonomy.cdm.database.update.ColumnRemover;
  20 import eu.etaxonomy.cdm.database.update.ISchemaUpdater;
  21 import eu.etaxonomy.cdm.database.update.ISchemaUpdaterStep;
  22 import eu.etaxonomy.cdm.database.update.SchemaUpdaterBase;
  23 import eu.etaxonomy.cdm.database.update.SimpleSchemaUpdaterStep;
  24 import eu.etaxonomy.cdm.database.update.TableDroper;
  25 import eu.etaxonomy.cdm.database.update.v30_31.SchemaUpdater_30_301;
  26
  27
  28 /**
  29  * NOT YET USED
  30  * @author a.mueller
  31  * @created Oct 11, 2011
  32  */
  33 public class SchemaUpdater_31_33 extends SchemaUpdaterBase {
  34
  35
  36         @SuppressWarnings("unused")
  37         private static final Logger logger = Logger.getLogger(SchemaUpdater_31_33.class);
  38         private static final String startSchemaVersion = "3.0.1.0.201104190000";
  39         private static final String endSchemaVersion = "3.3.0.0.201306010000";
  40
  41 // ********************** FACTORY METHOD *******************************************
  42
  43         public static SchemaUpdater_31_33 NewInstance(){
  44                 return new SchemaUpdater_31_33();
  45         }
  46
  47         /**
  48          * @param startSchemaVersion
  49          * @param endSchemaVersion
  50          */
  51         protected SchemaUpdater_31_33() {
  52                 super(startSchemaVersion, endSchemaVersion);
  53         }
  54
  55         /* (non-Javadoc)
  56          * @see eu.etaxonomy.cdm.database.update.SchemaUpdaterBase#getUpdaterList()
  57          */
  58         @Override
  59         protected List<ISchemaUpdaterStep> getUpdaterList() {
  60
  61                 List<ISchemaUpdaterStep> stepList = new ArrayList<ISchemaUpdaterStep>();
  62
  63                 //TODO Does it throw exception if table does not exist?
  64                 //Was in Schemaupdater_301_31 which was never used and later deleted (r18331).
  65                 //drop TypeDesignationBase_TaxonNameBase   //from schemaUpdater 301_31
  66                 String stepName = "Drop duplicate TypeDesignation-TaxonName table";
  67                 String tableName = "TypeDesignationBase_TaxonNameBase";
  68                 ISchemaUpdaterStep step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT);
  69                 stepList.add(step);
  70
  71                 //create original source type column
  72                 stepName = "Create original source type column";
  73                 tableName = "OriginalSourceBase";
  74                 String columnName = "type";
  75                 //TODO NOT NULL unclear
  76                 step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, true, null);
  77                 stepList.add(step);
  78
  79                 //TODO ?? update original source type
  80                 updateOriginalSourceType(stepList);
  81
  82                 //create and update elevenation max, remove error column
  83                 updateElevationMax(stepList);
  84
  85                 //create taxon node tree index
  86                 stepName = "Create taxon node tree index";
  87                 tableName = "TaxonNode";
  88                 columnName = "treeIndex";
  89                 //TODO NOT NULL unclear
  90                 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT);
  91                 stepList.add(step);
  92
  93                 //TODO update tree index
  94
  95                 //create original source type column
  96                 stepName = "Create taxon node sort index column";
  97                 tableName = "TaxonNode";
  98                 columnName = "sortIndex";
  99                 step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false, null);
 100                 stepList.add(step);
 101
 102                 //TODO implement sorted behaviour in model first !!
 103                 //TODO update sortindex (similar updater exists already for FeatureNode#sortIndex in schema update 25_30
 104
 105                 //create feature node tree index
 106                 stepName = "Create feature node tree index";
 107                 tableName = "FeatureNode";
 108                 columnName = "treeIndex";
 109                 //TODO NOT NULL unclear
 110                 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT);
 111                 stepList.add(step);
 112
 113                 //TODO update tree index for feature node
 114
 115                 //update introduced: adventitious (casual) label
 116                 //#3540
 117                 stepName = "Update introduced: adventitious (casual) label";
 118                 String query = " UPDATE representation r " +
 119                                 " SET r.abbreviatedlabel = 'ia' " +
 120                                 " WHERE r.abbreviatedlabel = 'id' AND r.label = 'introduced: adventitious (casual)' ";
 121                 step = SimpleSchemaUpdaterStep.NewInstance(stepName, query);
 122                 stepList.add(step);
 123
 124                 //TODO update idInVocabulary for DefinedTerms
 125
 126                 //TODO update termType for DefinedTerms and TermVocabulary, no type must be null
 127
 128                 //TODO update DTYPE for modifiers (Stage, Scope, Sex, DeterminationModifier, Modifier -> DefinedTerm)
 129
 130
 131                 //TODO update rankClass (#3521)
 132
 133                 //TODO change column type for DistanceToWaterSurface und DistanceToGround
 134
 135                 //TODO add column for DistanceToWaterSurfaceMax/Text und DistanceToGroundMax/Text
 136
 137                 //TODO update datatype of sequence.sequence => CLOB (keeping data not necessary #3325)
 138                 //NOTE: column has been changed: #3360
 139
 140                 //TODO update datatype->CLOB for URIs. (DefinedTerms, TermVocabulary, Reference
 141                 //Rights, MediaRepresentationPart ) #3345
 142
 143                 //update Sicilia -> Sicily
 144                 //#3540
 145                 stepName = "Update Sicilia -> Sicily";
 146                 query = " UPDATE representation r " +
 147                                 " SET r.label = 'Sicily', r.text = 'Sicily' " +
 148                                 " WHERE (r.abbreviatedlabel = 'SIC-SI'  OR r.abbreviatedlabel = 'SIC')  AND r.label = 'Sicilia' ";
 149                 step = SimpleSchemaUpdaterStep.NewInstance(stepName, query);
 150                 stepList.add(step);
 151
 152                 //remove homotypical group form type designation base
 153                 stepName = "Remove column homotypical group in type designation base";
 154                 tableName = "TypeDesignationBase";
 155                 String oldColumnName = "homotypicalgroup_id";
 156                 step = ColumnRemover.NewInstance(stepName, tableName, oldColumnName, INCLUDE_AUDIT);
 157                 stepList.add(step);
 158
 159                 //add publish flag to taxon
 160                 stepName = "Add publish flag column to taxon base";
 161                 tableName = "TaxonBase";
 162                 columnName = "publish";
 163                 step = ColumnAdder.NewBooleanInstance(stepName, tableName, columnName, INCLUDE_AUDIT, true);
 164                 stepList.add(step);
 165
 166                 //add publish flag to specimen
 167                 stepName = "Add publish flag column to SpecimenOrObservationBase";
 168                 tableName = "SpecimenOrObservationBase";
 169                 columnName = "publish";
 170                 step = ColumnAdder.NewBooleanInstance(stepName, tableName, columnName, INCLUDE_AUDIT, true);
 171                 stepList.add(step);
 172
 173                 //TODO add columns abbrevTitle, abbrevTitleCache and protectedAbbrevTitleCache to Reference
 174
 175
 176                 //add start number to PolytomousKey
 177                 stepName = "Add start number column to PolytomousKey";
 178                 tableName = "PolytomousKey";
 179                 columnName = "startNumber";
 180                 Integer defaultValue = 1;
 181                 step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT,  defaultValue, true);
 182                 stepList.add(step);
 183
 184                 //TODO add specimenOrObservation basis of record to SpecimenOrObservationBase
 185
 186                 //TODO update specimenOrObservationBase DTYPE with DefinedTerm where necessary
 187
 188
 189                 //TODO update DTYPE FieldObservation -> FieldUnit #3351
 190                 stepName = "Update FieldObservation -> FieldUnit";
 191                 query = " UPDATE SpecimenOrObservationBase sob " +
 192                                 " SET sob.DTYPE = 'FieldUnit' " +
 193                                 " WHERE sob.DTYPE = 'FieldObservation' ";
 194                 step = SimpleSchemaUpdaterStep.NewInstance(stepName, query);
 195                 stepList.add(step);
 196
 197                 //add kindOfUnit to SpecimenOrObservationBase
 198                 stepName = "Add kindOfUnit column to SpecimenOrObservationBase";
 199                 tableName = "SpecimenOrObservationBase";
 200                 columnName = "kindOfUnit";
 201                 String relatedTable = "DefinedTermBase";
 202                 step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT,  true, relatedTable);
 203                 stepList.add(step);
 204
 205                 //TODO remove citation_id and citationmicroreference columns from Media table #2541
 206                 //first check if columns are always empty
 207
 208                 //TODO update length of all title caches and full title cache in names
 209                 //https://dev.e-taxonomy.eu/trac/ticket/1592
 210
 211                 //TODO rename FK column states_id -> stateData_id in DescriptionElementBase_StateData(+AUD)  #2923
 212
 213                 //TODO add sortIndex column to TaxonNode and fill with values (compare with FeatureNode filling, however, this
 214 //              had a bad performance
 215
 216                 //specimen descriptions #3571
 217                 //TODO add column DescriptionBase.Specimen_ID  #3571
 218                 stepName = "Add specimen_id column to DescriptionBase";
 219                 tableName = "SpecimenOrObservationBase";
 220                 columnName = "specimen_id";
 221                 boolean notNull = false;
 222                 String referencedTable = "SpecimenOrObservationBase";
 223                 step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, notNull, referencedTable);
 224                 stepList.add(step);
 225
 226                 //TODO update DescriptionBase.Specimen_ID data  #3571
 227
 228                 //TODO remove tables DescriptionBase_SpecimenOrObservationBase(_AUD)  #3571
 229                 stepName = "Remove table DescriptionBase_SpecimenOrObservationBase";
 230                 tableName = "DescriptionBase_SpecimenOrObservationBase";
 231                 TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT);
 232                 stepList.add(step);
 233
 234                 //TODO create table CdmPreferences  #3555
 235
 236                 //TODO fill CdmPreferences with default values
 237
 238                 //update RightsTerm to RightsType #1306
 239                 stepName = "Update RightsTerm -> RightsType";
 240                 String updateSql = "UPDATE DefinedTermBase SET DTYPE = 'RightsType'  WHERE DTYPE = 'RightsTerm'";
 241                 step = SimpleSchemaUpdaterStep.NewInstance(stepName, updateSql);
 242                 stepList.add(step);
 243
 244                 //remove table Sequence_GenBankAccession #3552
 245                 stepName = "Remove table Sequence_GenBankAccession";
 246                 tableName = "Sequence_GenBankAccession";
 247                 TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT);
 248                 stepList.add(step);
 249
 250                 //remove table GenBankAccession #3552
 251                 stepName = "Remove table GenBankAccession";
 252                 tableName = "GenBankAccession";
 253                 TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT);
 254                 stepList.add(step);
 255
 256                 //TODO (read first #3360) add columns GeneticAccessionNumber(String) to Sequence
 257
 258                 //TODO update molecular data #3360
 259
 260
 261                 //add MediaSpecimen column #3614
 262                 stepName = "Add mediaSpecimen column to SpecimenOrObservationBase";
 263                 tableName = "SpecimenOrObservationBase";
 264                 columnName = "mediaSpecimen_id";
 265                 notNull = false;
 266                 referencedTable = "Media";
 267                 step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, notNull, referencedTable);
 268                 stepList.add(step);
 269
 270                 //remove DescriptionBase_Feature  #2202
 271                 stepName = "Remove table DescriptionBase_Feature";
 272                 tableName = "DescriptionBase_Feature";
 273                 TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT);
 274                 stepList.add(step);
 275
 276                 return stepList;
 277         }
 278
 279         private void updateElevationMax(List<ISchemaUpdaterStep> stepList) {
 280                 //create column
 281                 String stepName = "Create absoluteElevationMax column";
 282                 String tableName = "GatheringEvent";
 283                 String columnName = "absoluteElevationMax";
 284                 ISchemaUpdaterStep step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false, null);
 285                 stepList.add(step);
 286
 287
 288                 //update max
 289                 stepName = "Update gathering elevation max";
 290                 //all audits to unknown type
 291                 String query = " UPDATE GatheringEvent ge " +
 292                                 " SET ge.absoluteElevationMax = ge.elevation + ge.elevationErrorRadius,  " +
 293                                 "     ge.absoluteElevation =  ge.elevationErrorRadius - ge.elevationErrorRadius" +
 294                                 " WHERE ge.elevationErrorRadius is not null ";
 295                 step = SimpleSchemaUpdaterStep.NewInstance(stepName, query);
 296                 stepList.add(step);
 297                 //TODO same for AUD
 298
 299                 //remove error column
 300                 stepName = "Remove elevationErrorRadius column";
 301                 tableName = "GatheringEvent";
 302                 columnName = "elevationErrorRadius";
 303                 step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
 304                 stepList.add(step);
 305
 306
 307         }
 308
 309         /**
 310          * @param stepList
 311          */
 312         private void updateOriginalSourceType(List<ISchemaUpdaterStep> stepList) {
 313                 String stepName;
 314                 ISchemaUpdaterStep step;
 315                 stepName = "Create original source type column";
 316                 //all audits to unknown type
 317                 String query = "UPDATE OriginalSourceBase_AUD SET type = 0 ";
 318                 step = SimpleSchemaUpdaterStep.NewInstance(stepName, query);
 319                 stepList.add(step);
 320
 321                  //all data to unknown
 322                 query = "UPDATE OriginalSourceBase SET type = 0 ";
 323                 step = SimpleSchemaUpdaterStep.NewInstance(stepName, query);
 324                 stepList.add(step);
 325
 326                  //all imports recognized by idInSOurce and by missing nameInSource
 327                 query = "UPDATE OriginalSourceBase SET type = 3 WHERE " +
 328                                 "((idInSource IS NOT NULL) OR (idNamespace IS NOT NULL))  AND " +
 329                                 "( nameUsedInSource IS NULL AND originalNameString IS NULL ) ";
 330                 step = SimpleSchemaUpdaterStep.NewInstance(stepName, query);
 331                 stepList.add(step);
 332                  //all imports recognized by idInSOurce and by missing nameInSource
 333                 query = "UPDATE OriginalSourceBase SET type = 1 WHERE " +
 334                                 "(idInSource IS NULL AND idNamespace IS NULL) AND " +
 335                                 "( citation IS NOT NULL ) ";
 336                 step = SimpleSchemaUpdaterStep.NewInstance(stepName, query);
 337                 stepList.add(step);
 338         }
 339
 340         /* (non-Javadoc)
 341          * @see eu.etaxonomy.cdm.database.update.SchemaUpdaterBase#getNextUpdater()
 342          */
 343         @Override
 344         public ISchemaUpdater getNextUpdater() {
 345                 return null;
 346         }
 347
 348         /* (non-Javadoc)
 349          * @see eu.etaxonomy.cdm.database.update.SchemaUpdaterBase#getPreviousUpdater()
 350          */
 351         @Override
 352         public ISchemaUpdater getPreviousUpdater() {
 353                 return SchemaUpdater_30_301.NewInstance();
 354         }
 355
 356 }