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 import java.util.UUID;
  16
  17 import org.apache.log4j.Logger;
  18
  19 import com.sun.tools.xjc.reader.gbind.Sequence;
  20
  21 import eu.etaxonomy.cdm.database.update.ClassChanger;
  22 import eu.etaxonomy.cdm.database.update.ColumnAdder;
  23 import eu.etaxonomy.cdm.database.update.ColumnNameChanger;
  24 import eu.etaxonomy.cdm.database.update.ColumnRemover;
  25 import eu.etaxonomy.cdm.database.update.ColumnTypeChanger;
  26 import eu.etaxonomy.cdm.database.update.ISchemaUpdater;
  27 import eu.etaxonomy.cdm.database.update.ISchemaUpdaterStep;
  28 import eu.etaxonomy.cdm.database.update.MnTableCreator;
  29 import eu.etaxonomy.cdm.database.update.SchemaUpdaterBase;
  30 import eu.etaxonomy.cdm.database.update.SimpleSchemaUpdaterStep;
  31 import eu.etaxonomy.cdm.database.update.SingleTermRemover;
  32 import eu.etaxonomy.cdm.database.update.SortIndexUpdater;
  33 import eu.etaxonomy.cdm.database.update.TableCreator;
  34 import eu.etaxonomy.cdm.database.update.TableDroper;
  35 import eu.etaxonomy.cdm.database.update.TableNameChanger;
  36 import eu.etaxonomy.cdm.database.update.TermMover;
  37 import eu.etaxonomy.cdm.database.update.TreeIndexUpdater;
  38 import eu.etaxonomy.cdm.database.update.VocabularyCreator;
  39 import eu.etaxonomy.cdm.database.update.v30_31.SchemaUpdater_30_301;
  40 import eu.etaxonomy.cdm.model.common.AnnotationType;
  41 import eu.etaxonomy.cdm.model.common.ExtensionType;
  42 import eu.etaxonomy.cdm.model.common.Language;
  43 import eu.etaxonomy.cdm.model.common.MarkerType;
  44 import eu.etaxonomy.cdm.model.common.OriginalSourceType;
  45 import eu.etaxonomy.cdm.model.common.TermType;
  46 import eu.etaxonomy.cdm.model.description.Feature;
  47 import eu.etaxonomy.cdm.model.description.MeasurementUnit;
  48 import eu.etaxonomy.cdm.model.description.NaturalLanguageTerm;
  49 import eu.etaxonomy.cdm.model.description.State;
  50 import eu.etaxonomy.cdm.model.description.StatisticalMeasure;
  51 import eu.etaxonomy.cdm.model.description.TextFormat;
  52 import eu.etaxonomy.cdm.model.location.NamedArea;
  53 import eu.etaxonomy.cdm.model.location.NamedAreaLevel;
  54 import eu.etaxonomy.cdm.model.location.NamedAreaType;
  55 import eu.etaxonomy.cdm.model.location.ReferenceSystem;
  56 import eu.etaxonomy.cdm.model.location.Country;
  57 import eu.etaxonomy.cdm.model.name.HybridRelationshipType;
  58 import eu.etaxonomy.cdm.model.name.NameRelationshipType;
  59 import eu.etaxonomy.cdm.model.name.NameTypeDesignationStatus;
  60 import eu.etaxonomy.cdm.model.name.NomenclaturalStatusType;
  61 import eu.etaxonomy.cdm.model.name.Rank;
  62 import eu.etaxonomy.cdm.model.name.SpecimenTypeDesignationStatus;
  63 import eu.etaxonomy.cdm.model.occurrence.DerivationEventType;
  64 import eu.etaxonomy.cdm.model.occurrence.DerivedUnit;
  65 import eu.etaxonomy.cdm.model.occurrence.MaterialOrMethodEvent;
  66 import eu.etaxonomy.cdm.model.occurrence.PreservationMethod;
  67 import eu.etaxonomy.cdm.model.occurrence.SpecimenOrObservationType;
  68 import eu.etaxonomy.cdm.model.reference.Reference;
  69 import eu.etaxonomy.cdm.model.reference.ReferenceType;
  70 import eu.etaxonomy.cdm.model.taxon.SynonymRelationshipType;
  71 import eu.etaxonomy.cdm.model.taxon.TaxonRelationshipType;
  72
  73
  74 /**
  75  * @author a.mueller
  76  * @created Jun 06, 2013
  77  */
  78 public class SchemaUpdater_31_33 extends SchemaUpdaterBase {
  79
  80         private static final Logger logger = Logger.getLogger(SchemaUpdater_31_33.class);
  81         private static final String startSchemaVersion = "3.0.1.0.201104190000";
  82         private static final String endSchemaVersion = "3.3.0.0.201308010000";
  83
  84 // ********************** FACTORY METHOD *******************************************
  85
  86         public static SchemaUpdater_31_33 NewInstance(){
  87                 return new SchemaUpdater_31_33();
  88         }
  89
  90         /**
  91          * @param startSchemaVersion
  92          * @param endSchemaVersion
  93          */
  94         protected SchemaUpdater_31_33() {
  95                 super(startSchemaVersion, endSchemaVersion);
  96         }
  97
  98         /* (non-Javadoc)
  99          * @see eu.etaxonomy.cdm.database.update.SchemaUpdaterBase#getUpdaterList()
 100          */
 101         @Override
 102         protected List<ISchemaUpdaterStep> getUpdaterList() {
 103
 104                 String stepName;
 105                 String tableName;
 106                 ISchemaUpdaterStep step;
 107                 String columnName;
 108
 109                 //CHECKS
 110
 111                 //remove SpecimenOrObservationBase_Media #3597
 112                   //TODO check if Description -Specimen Relation has M:M data
 113                 if (false){
 114                         throw new RuntimeException("Required check for SpecimenOrObservationBase_Media");
 115                 }else{
 116                         logger.warn("CHECKS for inconsistent data not running !!!!");
 117                 }
 118
 119
 120                 List<ISchemaUpdaterStep> stepList = new ArrayList<ISchemaUpdaterStep>();
 121
 122                 //TODO Does it throw exception if table does not exist?
 123                 //Was in Schemaupdater_301_31 which was never used and later deleted (r18331).
 124                 //drop TypeDesignationBase_TaxonNameBase   //from schemaUpdater 301_31
 125                 stepName = "Drop duplicate TypeDesignation-TaxonName table";
 126                 tableName = "TypeDesignationBase_TaxonNameBase";
 127                 step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT);
 128                 stepList.add(step);
 129
 130                 //create original source type column
 131                 stepName = "Create original source type column";
 132                 tableName = "OriginalSourceBase";
 133                 columnName = "sourceType";
 134                 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 4, INCLUDE_AUDIT);
 135                 ((ColumnAdder)step).setNotNull(true);
 136                 stepList.add(step);
 137
 138                 //update original source type
 139                 updateOriginalSourceType(stepList);
 140
 141                 //create and update elevenation max, remove error column
 142                 updateElevationMax(stepList);
 143
 144                 //create TaxonNode tree index
 145                 stepName = "Create taxon node tree index";
 146                 tableName = "TaxonNode";
 147                 columnName = "treeIndex";
 148                 //TODO NOT NULL unclear  //see also columnTypeChanger
 149                 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT);
 150                 stepList.add(step);
 151
 152                 //update treeindex for taxon nodes
 153                 stepName = "Update TaxonNode treeindex";
 154                 tableName = "TaxonNode";
 155                 String treeIdColumnName = "classification_id";
 156                 step = TreeIndexUpdater.NewInstance(stepName, tableName, treeIdColumnName, columnName, INCLUDE_AUDIT);
 157                 stepList.add(step);
 158
 159                 //create TaxonNode sort index column
 160                 stepName = "Create taxon node sort index column";
 161                 tableName = "TaxonNode";
 162                 columnName = "sortIndex";
 163                 step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false, null);
 164                 stepList.add(step);
 165
 166                 //TODO implement sorted behaviour in model first !!
 167                 //update sortindex
 168                 stepName = "Update sort index on TaxonNode children";
 169                 tableName = "TaxonNode";
 170                 String parentIdColumn = "parent_id";
 171                 String sortIndexColumn = "sortIndex";
 172                 SortIndexUpdater updateSortIndex = SortIndexUpdater.NewInstance(stepName, tableName, parentIdColumn, sortIndexColumn, INCLUDE_AUDIT);
 173                 stepList.add(updateSortIndex);
 174
 175
 176                 //create feature node tree index
 177                 stepName = "Create feature node tree index";
 178                 tableName = "FeatureNode";
 179                 columnName = "treeIndex";
 180                 //TODO NOT NULL unclear
 181                 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT);
 182                 stepList.add(step);
 183
 184                 //update tree index for feature node
 185                 stepName = "Update FeatureNode treeindex";
 186                 tableName = "FeatureNode";
 187                 treeIdColumnName = "featuretree_id";
 188                 step = TreeIndexUpdater.NewInstance(stepName, tableName, treeIdColumnName, columnName, INCLUDE_AUDIT);
 189                 stepList.add(step);
 190
 191                 //update introduced: adventitious (casual) label
 192                 //#3540
 193                 stepName = "Update introduced: adventitious (casual) label";
 194                 String query = " UPDATE Representation r " +
 195                                 " SET r.abbreviatedlabel = 'ia' " +
 196                                 " WHERE r.abbreviatedlabel = 'id' AND r.label = 'introduced: adventitious (casual)' ";
 197                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing("Representation");
 198                 stepList.add(step);
 199
 200                 //termType for DefinedTerms and TermVocabulary, no type must be null
 201                 stepName = "Create termType column in DefinedTermBase";
 202                 tableName = "DefinedTermBase";
 203                 columnName = "termType";
 204                 //TODO NOT NULL unclear
 205                 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT);
 206                 stepList.add(step);
 207
 208                 stepName = "Create termType column in TermVocabulary";
 209                 tableName = "TermVocabulary";
 210                 columnName = "termType";
 211                 //TODO NOT NULL unclear
 212                 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 4, INCLUDE_AUDIT);
 213                 stepList.add(step);
 214
 215
 216                 //update termType for DefinedTerms, no type must be null
 217                 updateTermTypesForTerms(stepList);
 218
 219                 //update termType for TermVocabulary, no type must be null
 220                 updateTermTypesForVocabularies(stepList);
 221
 222                 //update DTYPE of DefinedTerms
 223                 updateDtypeOfDefinedTerms(stepList);
 224
 225                 //idInVocabulary for DefinedTerms
 226                 stepName = "Create idInVocabulary column in DefinedTermBase";
 227                 tableName = "DefinedTermBase";
 228                 columnName = "idInVocabulary";
 229                 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT);
 230                 stepList.add(step);
 231
 232                 //update idInVocabulary
 233                 updateIdInVocabulary(stepList);
 234
 235                 //rankClass (#3521)
 236                 stepName = "Create rankClass column in DefinedTermBase";
 237                 tableName = "DefinedTermBase";
 238                 columnName = "rankClass";
 239                 //TODO NOT NULL unclear
 240                 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT);
 241                 stepList.add(step);
 242
 243                 //update rankClass (#3521)
 244                 step = RankClassUpdater.NewInstance();
 245                 stepList.add(step);
 246
 247                 //update datatype->CLOB for URIs. (DefinedTerms, TermVocabulary, Reference, Rights, MediaRepresentationPart )
 248                 //#3345,    TODO adapt type to <65k
 249                 //TODO sequence.sequence has been changed #3360
 250                 changeUriType(stepList);
 251
 252                 //Annotation.linkbackUri change name #3374
 253                 stepName = "Update url to uri (->clob) for Annotation.linkbackUri";
 254                 columnName = "linkbackUrl";
 255                 String newColumnName = "linkbackUri";
 256                 tableName = "Annotation";
 257                 //TODO check non MySQL and with existing data (probably does not exist)
 258                 step = ColumnNameChanger.NewClobInstance(stepName, tableName, columnName, newColumnName, INCLUDE_AUDIT);
 259                 stepList.add(step);
 260
 261                 //update Sicilia -> Sicily
 262                 //#3540
 263                 stepName = "Update Sicilia -> Sicily";
 264                 query = " UPDATE Representation r " +
 265                                 " SET r.label = 'Sicily', r.text = 'Sicily' " +
 266                                 " WHERE (r.abbreviatedlabel = 'SIC-SI'  OR r.abbreviatedlabel = 'SIC')  AND r.label = 'Sicilia' ";
 267                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing("Representation");
 268                 stepList.add(step);
 269
 270                 //remove homotypical group form type designation base
 271                 stepName = "Remove column homotypical group in type designation base";
 272                 tableName = "TypeDesignationBase";
 273                 String oldColumnName = "homotypicalgroup_id";
 274                 step = ColumnRemover.NewInstance(stepName, tableName, oldColumnName, INCLUDE_AUDIT);
 275                 stepList.add(step);
 276
 277                 //add publish flag #1780
 278                 addPublishFlag(stepList);
 279
 280                 //add columns abbrevTitle, abbrevTitleCache and protectedAbbrevTitleCache to Reference
 281                 stepName = "Add abbrevTitle to Reference";
 282                 tableName = "Reference";
 283                 columnName = "abbrevTitle";
 284                 int length = 255;
 285                 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, length, INCLUDE_AUDIT);
 286                 stepList.add(step);
 287
 288                 stepName = "Add abbrevTitleCache to Reference";
 289                 tableName = "Reference";
 290                 columnName = "abbrevTitleCache";
 291                 length = 1023;
 292                 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, length, INCLUDE_AUDIT);
 293                 stepList.add(step);
 294
 295                 stepName = "Add protectedAbbrevTitleCache to Reference";
 296                 tableName = "Reference";
 297                 columnName = "protectedAbbrevTitleCache";
 298                 step = ColumnAdder.NewBooleanInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false);
 299                 stepList.add(step);
 300
 301                 //update abbrevTitle, protectedAbbrevTitle and abbrevTitleCache in Reference
 302                 updateAbbrevTitle(stepList);
 303
 304                 //add doi to reference
 305                 stepName = "Add doi to Reference";
 306                 tableName = "Reference";
 307                 columnName = "doi";
 308                 length = 255;
 309                 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, length, INCLUDE_AUDIT);
 310                 stepList.add(step);
 311
 312
 313                 //add start number to PolytomousKey
 314                 stepName = "Add start number column to PolytomousKey";
 315                 tableName = "PolytomousKey";
 316                 columnName = "startNumber";
 317                 Integer defaultValue = 1;
 318                 step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT,  defaultValue, true);
 319                 stepList.add(step);
 320
 321                 //add recordBasis  to specimenOrObservationBase
 322                 stepName = "Add recordBasis to SpecimenOrObservationBase";
 323                 tableName = "SpecimenOrObservationBase";
 324                 columnName = "recordBasis";
 325                 length = 4;  //TODO needed?
 326                 //TODO NOT NULL
 327                 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, length, INCLUDE_AUDIT);
 328                 stepList.add(step);
 329
 330                 //update recordBasis
 331                 updateRecordBasis(stepList);
 332
 333                 //update specimenOrObservationBase DTYPE with DerivedUnit where necessary
 334                 stepName = "Update Specimen -> DerivedUnit";
 335                 query = " UPDATE SpecimenOrObservationBase sob " +
 336                                 " SET sob.DTYPE = 'DerivedUnit' " +
 337                                 " WHERE sob.DTYPE = 'Specimen' OR sob.DTYPE = 'Fossil' OR sob.DTYPE = 'LivingBeing' OR sob.DTYPE = 'Observation' ";
 338                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing("SpecimenOrObservationBase");
 339                 stepList.add(step);
 340
 341                 stepName = "Update Specimen -> DerivedUnit";
 342                 String newClass = "eu.etaxonomy.cdm.model.occurrence.DerivedUnit";
 343                 String[] oldClassPaths = new String[]{
 344                                 "eu.etaxonomy.cdm.model.occurrence.Specimen"
 345                                 ,"eu.etaxonomy.cdm.model.occurrence.Fossil"
 346                                 ,"eu.etaxonomy.cdm.model.occurrence.LivingBeing"
 347                                 ,"eu.etaxonomy.cdm.model.occurrence.Observation"
 348                         };
 349                 step = ClassChanger.NewIdentifiableInstance(stepName, tableName, newClass, oldClassPaths, INCLUDE_AUDIT);
 350                 stepList.add(step);
 351
 352
 353                 //update DTYPE FieldObservation -> FieldUnit #3351
 354                 stepName = "Update FieldObservation -> FieldUnit";
 355                 query = " UPDATE SpecimenOrObservationBase sob " +
 356                                 " SET sob.DTYPE = 'FieldUnit' " +
 357                                 " WHERE sob.DTYPE = 'FieldObservation' ";
 358                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing("SpecimenOrObservationBase");
 359                 stepList.add(step);
 360
 361                 stepName = "Update Specimen -> DerivedUnit";
 362                 newClass = "eu.etaxonomy.cdm.model.occurrence.FieldUnit";
 363                 oldClassPaths = new String[]{
 364                                 "eu.etaxonomy.cdm.model.occurrence.FieldObservation"
 365                         };
 366                 step = ClassChanger.NewIdentifiableInstance(stepName, tableName, newClass, oldClassPaths, INCLUDE_AUDIT);
 367                 stepList.add(step);
 368
 369                 //add kindOfUnit to SpecimenOrObservationBase
 370                 stepName = "Add kindOfUnit column to SpecimenOrObservationBase";
 371                 tableName = "SpecimenOrObservationBase";
 372                 columnName = "kindOfUnit_id";
 373                 String relatedTable = "DefinedTermBase";
 374                 step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT,  false, relatedTable);
 375                 stepList.add(step);
 376
 377                 //remove citation_id and citation micro-reference columns from Media table #2541
 378                 //FIXME first check if columns are always empty
 379                 stepName = "Remove citation column from Media";
 380                 tableName = "Media";
 381                 columnName = "citation_id";
 382                 step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
 383                 stepList.add(step);
 384
 385                 stepName = "Remove citation microreference column from Media";
 386                 tableName = "Media";
 387                 columnName = "citationMicroReference";
 388                 step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
 389                 stepList.add(step);
 390
 391                 //update length of all title caches and full title cache in names  #1592
 392                 updateTitleCacheLength(stepList);
 393
 394                 //rename FK column states_id -> stateData_id in DescriptionElementBase_StateData(+AUD)  #2923
 395                 stepName = "Update states_id to stateData_id in DescriptionElementBase_StateData";
 396                 tableName = "DescriptionElementBase_StateData";
 397                 oldColumnName = "states_id";
 398                 newColumnName = "stateData_id";
 399                 step = ColumnNameChanger.NewIntegerInstance(stepName, tableName, oldColumnName, newColumnName, INCLUDE_AUDIT);
 400                 stepList.add(step);
 401
 402                 //specimen descriptions #3571
 403                 //add column DescriptionBase.Specimen_ID  #3571
 404                 stepName = "Add specimen_id column to DescriptionBase";
 405                 tableName = "DescriptionBase";
 406                 columnName = "specimen_id";
 407                 boolean notNull = false;
 408                 String referencedTable = "SpecimenOrObservationBase";
 409                 step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, notNull, referencedTable);
 410                 stepList.add(step);
 411
 412                 //update DescriptionBase.Specimen_ID data  #3571
 413                 updateDescriptionSpecimenRelation(stepList);
 414
 415                 //remove tables DescriptionBase_SpecimenOrObservationBase(_AUD)  #3571
 416                 stepName = "Remove table DescriptionBase_SpecimenOrObservationBase";
 417                 tableName = "DescriptionBase_SpecimenOrObservationBase";
 418                 step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT);
 419                 stepList.add(step);
 420
 421                 //change column type for reference type
 422                 //TODO test with non-Mysql
 423                 stepName = "Change column type for Reference.type";
 424                 tableName = "Reference";
 425                 columnName = "refType";
 426                 Integer defaultValueStr = -1;
 427                 notNull = true;
 428                 int size = 3;
 429                 step = ColumnTypeChanger.NewInt2StringInstance(stepName, tableName, columnName, size, true, defaultValueStr, notNull);
 430                 stepList.add(step);
 431
 432                 //update reference type
 433                 updateReferenceType(stepList);
 434
 435                 //create table CdmPreference  #3555
 436                 stepName = "Create table 'CdmPreference'";
 437                 tableName = "CdmPreference";
 438                 TableCreator stepPref = TableCreator.NewInstance(stepName, tableName,
 439                                 new String[]{"key_subject", "key_predicate","value"},  //colNames
 440                                 new String[]{"string_100", "string_200","string_1023",},  // columnTypes
 441                                 new String[]{null, "DefinedTermBase",null},  //referencedTables
 442                                 ! INCLUDE_AUDIT, false);
 443                 stepPref.setPrimaryKeyParams("key_subject, key_predicate", null);
 444                 stepList.add(stepPref);
 445                 //FIXME length of key >= 1000
 446
 447                 //TODO fill CdmPreferences with default values
 448
 449                 //update RightsTerm to RightsType #1306
 450                 stepName = "Update RightsTerm -> RightsType";
 451                 String updateSql = "UPDATE DefinedTermBase SET DTYPE = 'RightsType'  WHERE DTYPE = 'RightsTerm'";
 452                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, updateSql).setDefaultAuditing("DefinedTermBase");
 453                 stepList.add(step);
 454
 455                 //update Rights table to RightsInfo
 456                 updateRights2RightsInfo(stepList);
 457
 458                 //Remove column isDescriptionSeparated from FeatureTree #3678
 459                 stepName = "Remove column isDescriptionSeparated from FeatureTree";
 460                 tableName = "FeatureTree";
 461                 columnName = "descriptionSeparated";
 462                 step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
 463                 stepList.add(step);
 464
 465                 //remove table Sequence_GenBankAccession #3552
 466                 stepName = "Remove table Sequence_GenBankAccession";
 467                 tableName = "Sequence_GenBankAccession";
 468                 step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT);
 469                 stepList.add(step);
 470
 471                 //remove table GenBankAccession #3552
 472                 stepName = "Remove table GenBankAccession";
 473                 tableName = "GenBankAccession";
 474                 step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT);
 475                 stepList.add(step);
 476
 477                 //remove table Sequence_Credit #3360
 478                 stepName = "Remove table Sequence_Credit";
 479                 tableName = "Sequence_Credit";
 480                 step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT);
 481                 stepList.add(step);
 482
 483                 //remove table Sequence_Extension #3360
 484                 stepName = "Remove table Sequence_Extension";
 485                 tableName = "Sequence_Extension";
 486                 step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT);
 487                 stepList.add(step);
 488
 489                 //remove table Sequence_OriginalSourceBase #3360
 490                 stepName = "Remove table Sequence_OriginalSourceBase";
 491                 tableName = "Sequence_OriginalSourceBase";
 492                 step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT);
 493                 stepList.add(step);
 494
 495                 //remove table Sequence_OriginalSourceBase #3360
 496                 stepName = "Remove table Sequence_Rights";
 497                 tableName = "Sequence_Rights";
 498                 step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT);
 499                 stepList.add(step);
 500
 501                 //remove old sequence columns
 502                 removeOldSequenceColumns(stepList);
 503
 504                 //add MediaSpecimen column #3614
 505                 stepName = "Add mediaSpecimen column to SpecimenOrObservationBase";
 506                 tableName = "SpecimenOrObservationBase";
 507                 columnName = "mediaSpecimen_id";
 508                 notNull = false;
 509                 referencedTable = "Media";
 510                 step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, notNull, referencedTable);
 511                 stepList.add(step);
 512
 513                 //remove DescriptionBase_Feature  #2202
 514                 stepName = "Remove table DescriptionBase_Feature";
 515                 tableName = "DescriptionBase_Feature";
 516                 step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT);
 517                 stepList.add(step);
 518
 519                 //add timeperiod to columns to description element base #3312
 520                 addTimeperiodToDescriptionElement(stepList);
 521
 522
 523                 //move specimen imdages
 524                 stepName = "Move images from SpecimenOrObservationBase_Media to image gallery";
 525                 step = SpecimenMediaMoverUpdater.NewInstance();
 526                 stepList.add(step);
 527
 528                 //SpecimenOrObservationBase_Media #3597
 529                 stepName = "Remove table SpecimenOrObservationBase_Media";
 530                 tableName = "SpecimenOrObservationBase_Media";
 531                 step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT);
 532                 stepList.add(step);
 533
 534                 //all molecular (#3360) and related updates
 535                 updateMolecularAndRelated(stepList);
 536
 537                 //update vocabulary representaitons
 538                 step = TermVocabularyRepresentationUpdater.NewInstance();
 539                 stepList.add(step);
 540
 541                 return stepList;
 542         }
 543
 544         private void updateMolecularAndRelated(List<ISchemaUpdaterStep> stepList) {
 545                 String stepName;
 546                 String tableName;
 547                 ISchemaUpdaterStep step;
 548                 String columnName;
 549                 int length;
 550                 Integer defaultValue;
 551                 String referencedTable;
 552
 553                 //Primer #3360
 554                 stepName = "Create table 'Primer'";
 555                 tableName = "Primer";
 556                 step = TableCreator.NewAnnotatableInstance(stepName, tableName,
 557                                 new String[]{"label","sequence_id","publishedIn_id"},  //colNames
 558                                 new String[]{"string_255","int","int"},  // columnTypes
 559                                 new String[]{null,Sequence.class.getSimpleName(),Reference.class.getSimpleName()},  //referencedTables
 560                                 INCLUDE_AUDIT);
 561                 stepList.add(step);
 562
 563                 //MaterialOrMethod #3360
 564                 stepName = "Create table 'MaterialOrMethodEvent'";
 565                 tableName = MaterialOrMethodEvent.class.getSimpleName();
 566                 step = TableCreator.NewEventInstance(stepName, tableName,
 567                                 new String[]{"DTYPE", "strain","temperature","materialMethodTerm_id", "forwardPrimer_id","reversePrimer_id","medium_id"},  //colNames
 568                                 new String[]{"string_255", "string_255", "double", "int","int", "int", "int"},  // columnTypes
 569                                 new String[]{null, null, null, "DefinedTermBase","Primer","Primer","DefinedTermBase"},  //referencedTables
 570                                 INCLUDE_AUDIT);
 571                 stepList.add(step);
 572
 573                 stepName = "Remove preservation column from SpecimenOrObservationBase";
 574                 //to fully remove all foreign keys, maybe there is a better way todo so
 575                 //we don't expect any preservation information to exist in any CDM database
 576                 tableName = "SpecimenOrObservationBase";
 577                 String oldColumnName = "preservation_id";
 578                 step = ColumnRemover.NewInstance(stepName, tableName, oldColumnName, INCLUDE_AUDIT);
 579                 stepList.add(step);
 580
 581                 stepName = "Add new preservation column to SpecimenOrObservationBase";
 582                 tableName = "SpecimenOrObservationBase";
 583                 String newColumnName = "preservation_id";
 584                 boolean notNull = false;
 585                 step = ColumnAdder.NewIntegerInstance(stepName, tableName, newColumnName, INCLUDE_AUDIT, notNull, "MaterialOrMethodEvent");
 586                 stepList.add(step);
 587
 588
 589 //              //Cloning #3360
 590 //              stepName = "Create table 'Cloning'";
 591 //              tableName = "Cloning";
 592 //              String matMetName = MaterialOrMethodEvent.class.getSimpleName();
 593 //              step = TableCreator.NewEventInstance(stepName, tableName,
 594 //                              new String[]{"method_id","forwardPrimer_id","reversePrimer_id"},  //colNames
 595 //                              new String[]{"string_255", "int","int","int"},  // columnTypes
 596 //                              new String[]{null, matMetName,"Primer","Primer"},  //referencedTables
 597 //                              INCLUDE_AUDIT);
 598 //              stepList.add(step);
 599 //
 600
 601                 //Amplification #3360
 602                 stepName = "Create table 'Amplification'";
 603                 tableName = "Amplification";
 604                 step = TableCreator.NewEventInstance(stepName, tableName,
 605                                 new String[]{"dnaSample_id","dnaMarker_id","forwardPrimer_id","reversePrimer_id","purification_id","cloning_id", "gelPhoto_id", "successful","successText","ladderUsed","electrophoresisVoltage","gelRunningTime","gelConcentration"},  //colNames
 606                                 new String[]{"int","int","int","int","int","int","int", "bit","string_255","string_255","double","double","double"},  // columnTypes
 607                                 new String[]{"SpecimenOrObservationBase","DefinedTermBase","Primer","Primer","MaterialOrMethodEvent", "MaterialOrMethodEvent", "Media", null, null, null, null, null, null},  //referencedTables
 608                                 INCLUDE_AUDIT);
 609                 stepList.add(step);
 610
 611                 //SingleRead #3360
 612                 stepName = "Create table 'SingleRead'";
 613                 tableName = "SingleRead";
 614                 step = TableCreator.NewEventInstance(stepName, tableName,
 615                                 new String[]{"amplification_id","materialOrMethod_id","primer_id","pherogram_id","direction","sequence_length"},  //colNames
 616                                 new String[]{"int","int","int","int","string_3","int"},  // columnTypes
 617                                 new String[]{"Amplification","MaterialOrMethodEvent", "Primer","Media", null, null},  //referencedTables
 618                                 INCLUDE_AUDIT);
 619                 //TODO length sequence_string
 620                 stepList.add(step);
 621
 622                 //sequence - consensussequence_string  #3360
 623                 stepName= "Add sequence_string to single read";
 624                 columnName = "sequence_string";
 625                 step = ColumnAdder.NewClobInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
 626                 stepList.add(step);
 627
 628                 //amplification - single reads  #3360
 629                 stepName= "Add single reads to amplification";
 630                 String firstTable =  "Amplification";
 631                 String secondTable =  "SingleRead";
 632                 step = MnTableCreator.NewMnInstance(stepName, firstTable, null, secondTable, null, SchemaUpdaterBase.INCLUDE_AUDIT, false, true);
 633                 stepList.add(step);
 634
 635                 //sequence - single reads  #3360
 636                 stepName= "Add single reads to sequence";
 637                 firstTable =  "Sequence";
 638                 secondTable =  "SingleRead";
 639                 step = MnTableCreator.NewMnInstance(stepName, firstTable, null, secondTable, null, SchemaUpdaterBase.INCLUDE_AUDIT, false, true);
 640                 stepList.add(step);
 641
 642                 //sequence - barcode  #3360
 643                 stepName= "Add barcodesequencepart_length to sequence";
 644                 tableName = "Sequence";
 645                 columnName = "barcodeSequencePart_length";
 646                 defaultValue = null;
 647                 step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, defaultValue, false);
 648                 stepList.add(step);
 649
 650                 //sequence - barcode  #3360
 651                 stepName= "Add barcodesequencepart_string to sequence";
 652                 tableName = "Sequence";
 653                 columnName = "barcodeSequencePart_string";
 654                 step = ColumnAdder.NewClobInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
 655                 stepList.add(step);
 656
 657                 //sequence - consensussequence_length  #3360
 658                 stepName= "Add consensusSequence_length to sequence";
 659                 tableName = "Sequence";
 660                 columnName = "consensusSequence_length";
 661                 defaultValue = null;
 662                 step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, defaultValue, false);
 663                 stepList.add(step);
 664
 665                 //sequence - consensussequence_string  #3360
 666                 stepName= "Add consensusSequence_string to sequence";
 667                 tableName = "Sequence";
 668                 columnName = "consensusSequence_string";
 669                 step = ColumnAdder.NewClobInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
 670                 stepList.add(step);
 671
 672                 //sequence - contigFile  #3360
 673                 stepName= "Add contigFile to sequence";
 674                 tableName = "Sequence";
 675                 columnName = "contigFile_id";
 676                 referencedTable = "Media";
 677                 step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false, referencedTable);
 678                 stepList.add(step);
 679
 680                 //sequence - boldprocessid  #3360
 681                 stepName= "Add boldprocessId to sequence";
 682                 tableName = "Sequence";
 683                 columnName = "boldProcessId";
 684                 length = 20;
 685                 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, length, INCLUDE_AUDIT);
 686                 stepList.add(step);
 687
 688                 //sequence - boldprocessid  #3360
 689                 stepName= "Add geneticAccessionNumber to sequence";
 690                 tableName = "Sequence";
 691                 columnName = "geneticAccessionNumber";
 692                 length = 20;
 693                 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, length, INCLUDE_AUDIT);
 694                 stepList.add(step);
 695
 696                 //sequence - haplotype  #3360
 697                 stepName= "Add haplotype to sequence";
 698                 tableName = "Sequence";
 699                 columnName = "haplotype";
 700                 length = 100;
 701                 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, length, INCLUDE_AUDIT);
 702                 stepList.add(step);
 703
 704                 //sequence - isBarcode  #3360
 705                 stepName= "Add isBarcode to sequence";
 706                 tableName = "Sequence";
 707                 columnName = "isBarcode";
 708                 step = ColumnAdder.NewBooleanInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false);
 709                 stepList.add(step);
 710
 711                 //sequence - dnaMarker  #3360
 712                 stepName= "Add dnaMarker to sequence";
 713                 tableName = "Sequence";
 714                 columnName = "dnaMarker_id";
 715                 referencedTable = "DefinedTermBase";
 716                 step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false, referencedTable);
 717                 stepList.add(step);
 718
 719                 //sequence - dnaSample  #3360
 720                 stepName= "Add dnaSample to sequence";
 721                 tableName = "Sequence";
 722                 columnName = "dnaSample_id";
 723                 referencedTable = "SpecimenOrObservationBase";
 724                 step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false, referencedTable);
 725                 stepList.add(step);
 726         }
 727
 728         private void addPublishFlag(List<ISchemaUpdaterStep> stepList) {
 729                 String stepName;
 730                 String tableName;
 731                 ISchemaUpdaterStep step;
 732                 String columnName;
 733                 String query;
 734
 735                 //TaxonBase
 736
 737                 //add publish flag to taxon
 738                 stepName = "Add publish flag column to taxon base";
 739                 tableName = "TaxonBase";
 740                 columnName = "publish";
 741                 step = ColumnAdder.NewBooleanInstance(stepName, tableName, columnName, INCLUDE_AUDIT, true);
 742                 stepList.add(step);
 743
 744                 //update publish with existing publish false markers
 745                 stepName = "update TaxonBase publish if publish false markers exist";
 746                 query = " UPDATE TaxonBase tb " +
 747                                 " SET publish = 0 " +
 748                                 " WHERE tb.id IN ( " +
 749                                 " SELECT DISTINCT MN.TaxonBase_id " +
 750                                 " FROM Marker m INNER JOIN TaxonBase_Marker MN ON MN.markers_id = m.id " +
 751                                 " INNER JOIN DefinedTermBase markerType ON m.markertype_id = markerType.id " +
 752                                 " WHERE m.flag = 0 AND markerType.uuid = '0522c2b3-b21c-400c-80fc-a251c3501dbc' " +
 753                                 ")" ;
 754                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, query, "TaxonBase");
 755                 stepList.add(step);
 756
 757                 //remove publish marker MN table
 758                 stepName = "Remove existing TaxonBase publish markers MN";
 759                 query = " DELETE "  +
 760                                 " FROM TaxonBase_Marker " +
 761                                 " WHERE markers_id IN ( " +
 762                                         " SELECT m.id " +
 763                                         " FROM Marker m INNER JOIN DefinedTermBase mType ON m.markertype_id = mType.id " +
 764                                         " WHERE mType.uuid = '0522c2b3-b21c-400c-80fc-a251c3501dbc'  " +
 765                                 ")" ;
 766                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, query, "TaxonBase_Marker");
 767                 stepList.add(step);
 768
 769                 //update publish with existing publish false markers
 770                 stepName = "Remove existing TaxonBase publish markers";
 771                 query = " DELETE "  +
 772                                 " FROM Marker " +
 773                                 " WHERE id NOT IN " +
 774                                                 " (SELECT MN.markers_id FROM TaxonBase_Marker MN) " +
 775                                         " AND (markedObj_type = 'eu.etaxonomy.cdm.model.taxon.Synonym' OR markedObj_type = 'eu.etaxonomy.cdm.model.taxon.Taxon') " +
 776                                         " AND markertype_id IN ( " +
 777                                                 "SELECT id FROM DefinedTermBase WHERE uuid = '0522c2b3-b21c-400c-80fc-a251c3501dbc' " +
 778                                                 ")"  ;
 779                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query);  //AUD does not have markedObj_type
 780                 stepList.add(step);
 781
 782                 //SpecimenOrObservationBase
 783
 784                 //add publish flag to specimen
 785                 stepName = "Add publish flag column to SpecimenOrObservationBase";
 786                 tableName = "SpecimenOrObservationBase";
 787                 columnName = "publish";
 788                 step = ColumnAdder.NewBooleanInstance(stepName, tableName, columnName, INCLUDE_AUDIT, true);
 789                 stepList.add(step);
 790
 791                 //update publish with existing publish false markers
 792                 stepName = "update SpecimenOrObservationBase publish if publish false markers exist";
 793                 query = " UPDATE SpecimenOrObservationBase sob " +
 794                                 " SET publish = 0 " +
 795                                 " WHERE sob.id IN ( " +
 796                                 " SELECT DISTINCT MN.SpecimenOrObservationBase_id " +
 797                                 " FROM Marker m INNER JOIN SpecimenOrObservationBase_Marker MN ON MN.markers_id = m.id " +
 798                                         " INNER JOIN DefinedTermBase markerType ON m.markertype_id = markerType.id " +
 799                                 " WHERE m.flag = 0 AND markerType.uuid = '0522c2b3-b21c-400c-80fc-a251c3501dbc' " +
 800                                 ")" ;
 801                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, query, "SpecimenOrObservationBase");
 802                 stepList.add(step);
 803
 804                 //remove publish marker MN table
 805                 stepName = "Remove existing SpecimenOrObservationBase publish markers MN";
 806                 query = " DELETE "  +
 807                                 " FROM SpecimenOrObservationBase_Marker " +
 808                                 " WHERE markers_id IN ( " +
 809                                         " SELECT m.id " +
 810                                         " FROM Marker m INNER JOIN DefinedTermBase mType ON m.markertype_id = mType.id " +
 811                                         " WHERE mType.uuid = '0522c2b3-b21c-400c-80fc-a251c3501dbc'  " +
 812                                 ")" ;
 813                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, query, "SpecimenOrObservationBase_Marker");
 814                 stepList.add(step);
 815
 816                 //update publish with existing publish false markers
 817                 stepName = "Remove existing SpecimenOrObservationBase publish markers";
 818                 query = " DELETE "  +
 819                                 " FROM Marker " +
 820                                 " WHERE id NOT IN " +
 821                                         " (SELECT MN.markers_id FROM SpecimenOrObservationBase_Marker MN) " +
 822                                         " AND (markedObj_type = 'eu.etaxonomy.cdm.model.occurrence.DerivedUnit' " +
 823                                                 "OR markedObj_type = 'eu.etaxonomy.cdm.model.occurrence.FieldObservation' " +
 824                                                 "OR markedObj_type = 'eu.etaxonomy.cdm.model.occurrence.FieldUnit' " +
 825                                                 "OR markedObj_type = 'eu.etaxonomy.cdm.model.occurrence.Specimen' " +
 826                                                 "OR markedObj_type = 'eu.etaxonomy.cdm.model.occurrence.Fossil' " +
 827                                                 "OR markedObj_type = 'eu.etaxonomy.cdm.model.occurrence.LivingBeing' " +
 828                                                 "OR markedObj_type = 'eu.etaxonomy.cdm.model.occurrence.Observation' " +
 829                                                 "OR markedObj_type = 'eu.etaxonomy.cdm.model.occurrence.MediaSpecimen' " +
 830                                                 "OR markedObj_type = 'eu.etaxonomy.cdm.model.occurrence.TissueSample' " +
 831                                                 "OR markedObj_type = 'eu.etaxonomy.cdm.model.molecular.DnaSample') " +
 832                                         " AND markertype_id IN ( " +
 833                                                 "SELECT id FROM DefinedTermBase WHERE uuid = '0522c2b3-b21c-400c-80fc-a251c3501dbc' " +
 834                                                 ")" ;
 835                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query);
 836                 stepList.add(step);
 837
 838                 //remove all audited markers if no current markers exist
 839                 //this may remove more audited markers then expected but we do accept this here
 840                 stepName = "Remove all audited markers if no current markers exist";
 841                 query = " DELETE "  +
 842                                 " FROM Marker_AUD " +
 843                                 " WHERE id NOT IN (SELECT id FROM Marker ) " +
 844                                         " AND markertype_id IN ( " +
 845                                                 "SELECT id FROM DefinedTermBase WHERE uuid = '0522c2b3-b21c-400c-80fc-a251c3501dbc' " +
 846                                                 ")" ;
 847                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query);
 848                 stepList.add(step);
 849
 850         }
 851
 852         private void updateRights2RightsInfo(List<ISchemaUpdaterStep> stepList) {
 853                 //#2945
 854                 String stepName = "Update Rights to RightsInfo";
 855                 String tableName = "Rights";
 856                 String newTableName = "RightsInfo";
 857                 ISchemaUpdaterStep step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
 858                 stepList.add(step);
 859
 860                 stepName = "Update AgentBase_Rights to RightsInfo";
 861                 tableName = "AgentBase_Rights";
 862                 newTableName = "AgentBase_RightsInfo";
 863                 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
 864                 stepList.add(step);
 865
 866                 stepName = "Update Rights_Annotation to RightsInfo";
 867                 tableName = "Rights_Annotation";
 868                 newTableName = "RightsInfo_Annotation";
 869                 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
 870                 stepList.add(step);
 871
 872                 stepName = "Update Rights_id column in RightsInfo_Annotation";
 873                 tableName = "RightsInfo_Annotation";
 874                 String columnName = "Rights_Id";
 875                 String newColumnName = "RightsInfo_id";
 876                 step = ColumnNameChanger.NewIntegerInstance(stepName, tableName, columnName, newColumnName, INCLUDE_AUDIT);
 877                 stepList.add(step);
 878
 879                 stepName = "Update Rights_Marker to RightsInfo";
 880                 tableName = "Rights_Marker";
 881                 newTableName = "RightsInfo_Marker";
 882                 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
 883                 stepList.add(step);
 884
 885                 stepName = "Update Rights_id column in RightsInfo_Marker";
 886                 tableName = "RightsInfo_Marker";
 887                 columnName = "Rights_Id";
 888                 newColumnName = "RightsInfo_id";
 889                 step = ColumnNameChanger.NewIntegerInstance(stepName, tableName, columnName, newColumnName, INCLUDE_AUDIT);
 890                 stepList.add(step);
 891
 892                 stepName = "Update Classification_Rights to RightsInfo";
 893                 tableName = "Classification_Rights";
 894                 newTableName = "Classification_RightsInfo";
 895                 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
 896                 stepList.add(step);
 897
 898                 stepName = "Update Collection_Rights to RightsInfo";
 899                 tableName = "Collection_Rights";
 900                 newTableName = "Collection_RightsInfo";
 901                 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
 902                 stepList.add(step);
 903
 904                 stepName = "Update DefinedTermBase_Rights to RightsInfo";
 905                 tableName = "DefinedTermBase_Rights";
 906                 newTableName = "DefinedTermBase_RightsInfo";
 907                 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
 908                 stepList.add(step);
 909
 910                 stepName = "Update DescriptionBase_Rights to RightsInfo";
 911                 tableName = "DescriptionBase_Rights";
 912                 newTableName = "DescriptionBase_RightsInfo";
 913                 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
 914                 stepList.add(step);
 915
 916                 stepName = "Update FeatureTree_Rights to RightsInfo";
 917                 tableName = "FeatureTree_Rights";
 918                 newTableName = "FeatureTree_RightsInfo";
 919                 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
 920                 stepList.add(step);
 921
 922                 stepName = "Update Media_Rights to RightsInfo";
 923                 tableName = "Media_Rights";
 924                 newTableName = "Media_RightsInfo";
 925                 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
 926                 stepList.add(step);
 927
 928                 stepName = "Update PolytomousKey_Rights to RightsInfo";
 929                 tableName = "PolytomousKey_Rights";
 930                 newTableName = "PolytomousKey_RightsInfo";
 931                 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
 932                 stepList.add(step);
 933
 934                 stepName = "Update Reference_Rights to RightsInfo";
 935                 tableName = "Reference_Rights";
 936                 newTableName = "Reference_RightsInfo";
 937                 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
 938                 stepList.add(step);
 939
 940                 stepName = "Update SpecimenOrObservationBase_Rights to RightsInfo";
 941                 tableName = "SpecimenOrObservationBase_Rights";
 942                 newTableName = "SpecimenOrObservationBase_RightsInfo";
 943                 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
 944                 stepList.add(step);
 945
 946                 stepName = "Update TaxonBase_Rights to RightsInfo";
 947                 tableName = "TaxonBase_Rights";
 948                 newTableName = "TaxonBase_RightsInfo";
 949                 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
 950                 stepList.add(step);
 951
 952                 stepName = "Update TaxonNameBase_Rights to RightsInfo";
 953                 tableName = "TaxonNameBase_Rights";
 954                 newTableName = "TaxonNameBase_RightsInfo";
 955                 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
 956                 stepList.add(step);
 957
 958                 stepName = "Update TermVocabulary_Rights to RightsInfo";
 959                 tableName = "TermVocabulary_Rights";
 960                 newTableName = "TermVocabulary_RightsInfo";
 961                 step = TableNameChanger.NewInstance(stepName, tableName, newTableName, INCLUDE_AUDIT);
 962                 stepList.add(step);
 963         }
 964
 965         private void updateReferenceType(List<ISchemaUpdaterStep> stepList) {
 966
 967                 String baseQuery = " UPDATE Reference " +
 968                         " SET refType = '%s' " +
 969                         " WHERE refType = '%s' ";
 970                 Integer index = 0;
 971                 String tableName = "Reference";
 972
 973                 //0-Article
 974                 String stepName = "Update reference refType for Article";
 975                 String query = String.format(baseQuery, ReferenceType.Article.getKey(), String.valueOf(index++));
 976                 ISchemaUpdaterStep step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
 977                 stepList.add(step);
 978
 979                 //1-Book
 980                 stepName = "Update reference refType for Book";
 981                 query = String.format(baseQuery, ReferenceType.Book.getKey(), String.valueOf(index++));
 982                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
 983                 stepList.add(step);
 984
 985                 //2-Book Section
 986                 stepName = "Update reference refType for Book Section";
 987                 query = String.format(baseQuery, ReferenceType.BookSection.getKey(), String.valueOf(index++));
 988                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
 989                 stepList.add(step);
 990
 991                 //3-CD / DVD
 992                 stepName = "Update reference refType for CD";
 993                 query = String.format(baseQuery, ReferenceType.CdDvd.getKey(), String.valueOf(index++));
 994                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
 995                 stepList.add(step);
 996
 997                 //4-Database
 998                 stepName = "Update reference refType for Database";
 999                 query = String.format(baseQuery, ReferenceType.Database.getKey(), String.valueOf(index++));
1000                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1001                 stepList.add(step);
1002
1003                 //5-Generic
1004                 stepName = "Update reference refType for Generic";
1005                 query = String.format(baseQuery, ReferenceType.Generic.getKey(), String.valueOf(index++));
1006                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1007                 stepList.add(step);
1008
1009                 //6-InProceedings
1010                 stepName = "Update reference refType for InProceedings";
1011                 query = String.format(baseQuery, ReferenceType.InProceedings.getKey(), String.valueOf(index++));
1012                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1013                 stepList.add(step);
1014
1015                 //7-Journal
1016                 stepName = "Update reference refType for Journal";
1017                 query = String.format(baseQuery, ReferenceType.Journal.getKey(), String.valueOf(index++));
1018                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1019                 stepList.add(step);
1020
1021                 //8-Map
1022                 stepName = "Update reference refType for Map";
1023                 query = String.format(baseQuery, ReferenceType.Map.getKey(), String.valueOf(index++));
1024                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1025                 stepList.add(step);
1026
1027                 //9-Patent
1028                 stepName = "Update reference refType for Patent";
1029                 query = String.format(baseQuery, ReferenceType.Patent.getKey(), String.valueOf(index++));
1030                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1031                 stepList.add(step);
1032
1033                 //10-Personal Communication
1034                 stepName = "Update reference refType for Personal Communication";
1035                 query = String.format(baseQuery, ReferenceType.PersonalCommunication.getKey(), String.valueOf(index++));
1036                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1037                 stepList.add(step);
1038
1039                 //11-PrintSeries
1040                 stepName = "Update reference refType for PrintSeries";
1041                 query = String.format(baseQuery, ReferenceType.PrintSeries.getKey(), String.valueOf(index++));
1042                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1043                 stepList.add(step);
1044
1045                 //12-Proceedings
1046                 stepName = "Update reference refType for Proceedings";
1047                 query = String.format(baseQuery, ReferenceType.Proceedings.getKey(), String.valueOf(index++));
1048                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);;
1049                 stepList.add(step);
1050
1051                 //13-Report
1052                 stepName = "Update reference refType for Report";
1053                 query = String.format(baseQuery, ReferenceType.Report.getKey(), String.valueOf(index++));
1054                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);;
1055                 stepList.add(step);
1056
1057                 //14-Thesis
1058                 stepName = "Update reference refType for Thesis";
1059                 query = String.format(baseQuery, ReferenceType.Thesis.getKey(), String.valueOf(index++));
1060                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);;
1061                 stepList.add(step);
1062
1063                 //15-WebPage
1064                 stepName = "Update reference refType for WebPage";
1065                 query = String.format(baseQuery, ReferenceType.WebPage.getKey(), String.valueOf(index++));
1066                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);;
1067                 stepList.add(step);
1068         }
1069
1070         private void updateRecordBasis(List<ISchemaUpdaterStep> stepList) {
1071                 String stepName = "Update recordBasis for SpecimenOrObservationBase";
1072                 String tableName = "SpecimenOrObservationBase";
1073
1074                 //Field Unit
1075                 String query = " UPDATE " + tableName +
1076                                 " SET recordBasis = '" + SpecimenOrObservationType.FieldUnit.getKey() + "' " +
1077                                 " WHERE DTYPE = 'FieldUnit' OR DTYPE = 'FieldObservation'";
1078                 ISchemaUpdaterStep step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1079                 stepList.add(step);
1080
1081                 //DerivedUnit
1082                 query = " UPDATE " + tableName +
1083                                 " SET recordBasis = '" + SpecimenOrObservationType.DerivedUnit.getKey() + "' " +
1084                                 " WHERE DTYPE = '" + DerivedUnit.class.getSimpleName() + "'";
1085                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1086                 stepList.add(step);
1087
1088                 //Living Being
1089                 query = " UPDATE " + tableName +
1090                                 " SET recordBasis = '" + SpecimenOrObservationType.LivingSpecimen.getKey() + "' " +
1091                                 " WHERE DTYPE = 'LivingBeing'";
1092                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1093                 stepList.add(step);
1094
1095                 //Observation
1096                 query = " UPDATE " + tableName +
1097                                 " SET recordBasis = '" + SpecimenOrObservationType.Observation.getKey() + "' " +
1098                                 " WHERE DTYPE = 'Observation'";
1099                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1100                 stepList.add(step);
1101
1102                 //Preserved Specimen
1103                 query = " UPDATE " + tableName +
1104                                 " SET recordBasis = '" + SpecimenOrObservationType.PreservedSpecimen.getKey() + "' " +
1105                                 " WHERE DTYPE = 'Specimen'";
1106                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1107                 stepList.add(step);
1108
1109                 //Fossil
1110                 query = " UPDATE " + tableName +
1111                                 " SET recordBasis = '" + SpecimenOrObservationType.Fossil.getKey() + "' " +
1112                                 " WHERE DTYPE = 'Fossil'";
1113                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1114                 stepList.add(step);
1115
1116                 //DnaSample
1117                 query = " UPDATE " + tableName +
1118                                 " SET recordBasis = '" + SpecimenOrObservationType.DnaSample.getKey() + "' " +
1119                                 " WHERE DTYPE = 'DnaSample'";
1120                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1121                 stepList.add(step);
1122
1123                 //Unknown as default (if not yet handled before)
1124                 query = " UPDATE " + tableName +
1125                                 " SET recordBasis = '" + SpecimenOrObservationType.Unknown.getKey() + "' " +
1126                                 " WHERE recordBasis IS NULL ";
1127                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1128                 stepList.add(step);
1129         }
1130
1131         //update length of all title caches and full title cache in names
1132         //TODO test for H2, Postgres, SqlServer
1133         //https://dev.e-taxonomy.eu/trac/ticket/1592
1134          private void updateTitleCacheLength(List<ISchemaUpdaterStep> stepList) {
1135                 String stepName;
1136                 String tableName;
1137                 ISchemaUpdaterStep step;
1138                 String columnName;
1139                 int size = 800;
1140
1141                 stepName = "Change length of TaxonNameBase fullTitleCache";
1142                 tableName = "TaxonNameBase";
1143                 columnName = "fullTitleCache";
1144                 step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT);
1145                 stepList.add(step);
1146
1147                 stepName = "Change length of TaxonNameBase title cache";
1148                 tableName = "TaxonNameBase";
1149                 columnName = "titleCache";
1150                 step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT);
1151                 stepList.add(step);
1152
1153                 stepName = "Change length of TaxonBase title cache";
1154                 tableName = "TaxonNameBase";
1155                 columnName = "titleCache";
1156                 step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT);
1157                 stepList.add(step);
1158
1159                 stepName = "Change length of Classification title cache";
1160                 tableName = "Classification";
1161                 columnName = "titleCache";
1162                 step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT);
1163                 stepList.add(step);
1164
1165                 stepName = "Change length of DescriptionBase title cache";
1166                 tableName = "DescriptionBase";
1167                 columnName = "titleCache";
1168                 step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT);
1169                 stepList.add(step);
1170
1171                 stepName = "Change length of FeatureTree title cache";
1172                 tableName = "FeatureTree";
1173                 columnName = "titleCache";
1174                 step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT);
1175                 stepList.add(step);
1176
1177                 stepName = "Change length of Collection title cache";
1178                 tableName = "Collection";
1179                 columnName = "titleCache";
1180                 step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT);
1181                 stepList.add(step);
1182
1183                 stepName = "Change length of Reference title cache";
1184                 tableName = "Reference";
1185                 columnName = "titleCache";
1186                 step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT);
1187                 stepList.add(step);
1188
1189                 stepName = "Change length of Media title cache";
1190                 tableName = "Media";
1191                 columnName = "titleCache";
1192                 step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT);
1193                 stepList.add(step);
1194
1195                 stepName = "Change length of PolytomousKey title cache";
1196                 tableName = "PolytomousKey";
1197                 columnName = "titleCache";
1198                 step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT);
1199                 stepList.add(step);
1200
1201                 stepName = "Change length of SpecimenOrObservationBase title cache";
1202                 tableName = "SpecimenOrObservationBase";
1203                 columnName = "titleCache";
1204                 step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT);
1205                 stepList.add(step);
1206
1207                 stepName = "Change length of DefinedTermBase title cache";
1208                 tableName = "DefinedTermBase";
1209                 columnName = "titleCache";
1210                 step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT);
1211                 stepList.add(step);
1212
1213                 stepName = "Change length of TermVocabulary title cache";
1214                 tableName = "TermVocabulary";
1215                 columnName = "titleCache";
1216                 step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT);
1217                 stepList.add(step);
1218
1219          }
1220
1221
1222         private void updateDescriptionSpecimenRelation(List<ISchemaUpdaterStep> stepList) {
1223                 //TODO warn if multiple entries for 1 description exists
1224                 String sqlCount = " SELECT count(*) as n " +
1225                                 " FROM DescriptionBase_SpecimenOrObservationBase MN " +
1226                                 " GROUP BY MN.descriptions_id " +
1227                                 " HAVING count(*) > 1 " +
1228                                 " ORDER BY MN.descriptions_id, MN.describedspecimenorobservations_id ";
1229
1230                 //TODO ... and log the concrete records
1231 //              FROM DescriptionBase_SpecimenOrObservationBase ds
1232 //              WHERE ds.descriptions_id IN (
1233 //              SELECT MN.descriptions_id
1234 //              FROM DescriptionBase_SpecimenOrObservationBase MN
1235 //              GROUP BY MN.descriptions_id
1236 //              HAVING count(*) > 1
1237 //              )
1238 //              ORDER BY descriptions_id, describedspecimenorobservations_id
1239
1240                 //TODO test for H2, Postgresql AND SQLServer (later will need TOP 1)
1241                 String stepName = "update Description - Specimen relation data  ";
1242                 String sql = " UPDATE DescriptionBase db " +
1243                 " SET db.specimen_id =  " +
1244                 " (SELECT  MN.describedspecimenorobservations_id " +
1245                 " FROM DescriptionBase_SpecimenOrObservationBase MN " +
1246                 " WHERE MN.descriptions_id = db.id " +
1247                 " LIMIT 1 " +
1248                 ")";
1249                 //TODO _AUD
1250                 ISchemaUpdaterStep step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, sql);
1251                 stepList.add(step);
1252
1253         }
1254
1255         private void updateAbbrevTitle(List<ISchemaUpdaterStep> stepList) {
1256                 String tableName = "Reference";
1257
1258                 String stepName = "Update abbrevTitleCache for protected title caches with title";
1259                 String query = " UPDATE Reference r " +
1260                                 " SET r.abbrevTitle = r.title, r.abbrevTitleCache = r.titleCache, r.protectedAbbrevTitleCache = r.protectedTitleCache";
1261 //                              + " WHERE r.title IS NOT NULL AND r.protectedTitleCache = 1 ";
1262                 ISchemaUpdaterStep step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1263                 stepList.add(step);
1264
1265 //              stepName = "Update abbrevTitleCache for protected title caches with no title";
1266 //              query = " UPDATE Reference r " +
1267 //                              " SET r.abbrevTitleCache = r.titleCache, r.protectedAbbrevTitleCache = r.protectedTitleCache" +
1268 //                              " WHERE r.title IS NULL AND r.protectedTitleCache = 1 ";
1269 //              step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setAuditing("Reference");
1270 //              stepList.add(step);
1271
1272 //              stepName = "Update abbrevTitleCache for protected title caches with title";
1273 //              query = " UPDATE Reference r " +
1274 //                              " SET r.abbrevTitle = r.title, r.abbrevTitleCache = r.titleCache, r.protectedAbbrevTitleCache = r.protectedTitleCache" +
1275 //                              " WHERE r.title IS NOT NULL AND r.protectedTitleCache = 0 ";
1276 //              step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setAuditing("Reference");
1277 //              stepList.add(step);
1278
1279                 stepName = "Update reference title, set null where abbrev title very likely";
1280                 query = " UPDATE Reference r " +
1281                                 " SET r.title = NULL " +
1282                                 " WHERE r.title IS NOT NULL AND r.protectedTitleCache = 0 AND " +
1283                                         " ( LENGTH(r.title) <= 15 AND title like '%.%.%' OR LENGTH(r.title) < 30 AND title like '%.%.%.%' OR LENGTH(r.title) < 45 AND title like '%.%.%.%.%' OR LENGTH(r.title) < 60 AND title like '%.%.%.%.%.%' " +
1284                                         ")" ;
1285                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1286                 stepList.add(step);
1287
1288
1289                 stepName = "Update reference abbrevTitle, set null where abbrev title very unlikely";
1290                 query = " UPDATE Reference r " +
1291                                 " SET r.abbrevTitle = NULL " +
1292                                 " WHERE r.title IS NOT NULL AND r.protectedTitleCache = 0 AND " +
1293                                         " ( title NOT like '%.%' OR LENGTH(r.title) > 30 AND title NOT like '%.%.%' " +
1294                                         ")" ;
1295                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1296                 stepList.add(step);
1297
1298         }
1299
1300         private void removeOldSequenceColumns(List<ISchemaUpdaterStep> stepList) {
1301                 //TODO also remove Identifiable attributes ??
1302
1303                 //remove citation microreference
1304                 String stepName = "Remove citationmicroreference column";
1305                 String tableName = "Sequence";
1306                 String columnName = "citationMicroReference";
1307                 ISchemaUpdaterStep step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
1308                 stepList.add(step);
1309
1310                 //remove datesequenced
1311                 stepName = "Remove datesequenced column";
1312                 columnName = "datesequenced";
1313                 step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
1314                 stepList.add(step);
1315
1316                 //remove length
1317                 stepName = "Remove length column";
1318                 columnName = "length";
1319                 step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
1320                 stepList.add(step);
1321
1322                 //remove sequence
1323                 stepName = "Remove sequence column";
1324                 columnName = "sequence";
1325                 step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
1326                 stepList.add(step);
1327
1328                 //remove locus_id
1329                 stepName = "Remove locus_id column";
1330                 columnName = "locus_id";
1331                 step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
1332                 stepList.add(step);
1333
1334                 //remove publishedin_id
1335                 stepName = "Remove publishedin_id column";
1336                 columnName = "publishedin_id";
1337                 step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
1338                 stepList.add(step);
1339
1340                 //remove barcode
1341                 stepName = "Remove barcode column";
1342                 columnName = "barcode";
1343                 step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
1344                 stepList.add(step);
1345         }
1346
1347         private void updateIdInVocabulary(List<ISchemaUpdaterStep> stepList) {
1348                 String tableName = "DefinedTermBase";
1349
1350                 String queryVocUuid = " UPDATE DefinedTermBase dtb INNER JOIN TermVocabulary voc ON voc.id = dtb.vocabulary_id" +
1351                                 " SET dtb.idInVocabulary = (SELECT abbreviatedlabel " +
1352                                         " FROM DefinedTermBase_Representation MN " +
1353                                         " INNER JOIN Representation r ON r.id = MN.representations_id " +
1354                                         " WHERE MN.DefinedTermBase_id = dtb.id) " +
1355                                 " WHERE voc.uuid = '%s'";
1356
1357                 //Languages (ISO)
1358                 String stepName = "Update idInVocabulary for Languages ";
1359                 String query = "UPDATE DefinedTermBase dtb INNER JOIN TermVocabulary voc ON voc.id = dtb.vocabulary_id " +
1360                                 " SET dtb.idInVocabulary = dtb.iso639_2 "+
1361                                 " WHERE voc.uuid = '45ac7043-7f5e-4f37-92f2-3874aaaef2de' ";
1362                 ISchemaUpdaterStep step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName); //not fully correct as we should join with TermVoc_AUD but good enough for this usecase
1363                 stepList.add(step);
1364
1365                 //Undefined Languages => all
1366                 stepName = "Update idInVocabulary for undefined languages";
1367                 String uuid = "7fd1e6d0-2e76-4dfa-bad9-2673dd042c28";
1368                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1369                 stepList.add(step);
1370
1371                 //Waterbody & Country => all
1372                 stepName = "Update idInVocabulary for WaterbodyOrCountries";
1373                 uuid = "006b1870-7347-4624-990f-e5ed78484a1a";
1374                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1375                 stepList.add(step);
1376
1377                 //TdwgAreas => all
1378                 stepName = "Update idInVocabulary for TDWG areas";
1379                 uuid = NamedArea.uuidTdwgAreaVocabulary.toString();
1380                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1381                 stepList.add(step);
1382
1383                 //Rank => some
1384                 stepName = "Update idInVocabulary for ranks";
1385                 uuid = "ef0d1ce1-26e3-4e83-b47b-ca74eed40b1b";
1386                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1387                 stepList.add(step);
1388
1389                 //avoid duplicate for section (bot.)
1390                 stepName = "Update idInVoc for section (bot.)";
1391                 String sql = " UPDATE DefinedTermBase SET idInVocabulary = 'sect.(bot.)' WHERE uuid = '3edff68f-8527-49b5-bf91-7e4398bb975c'";
1392                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, "DefinedTermBase");
1393                 stepList.add(step);
1394
1395                 //avoid duplicate for subsection (bot.)
1396                 stepName = "Update idInVoc for subsection (bot.)";
1397                 sql = " UPDATE DefinedTermBase SET idInVocabulary = 'subsect.(bot.)' WHERE uuid = 'd20f5b61-d463-4448-8f8a-c1ff1f262f59'";
1398                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, "DefinedTermBase");
1399                 stepList.add(step);
1400
1401                 //avoid duplicate for section (zool.)
1402                 stepName = "Update idInVoc for section (zool.)";
1403                 sql = " UPDATE DefinedTermBase SET idInVocabulary = 'sect.(zool.)' WHERE uuid = '691d371e-10d7-43f0-93db-3d7fa1a62c54'";
1404                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, "DefinedTermBase");
1405                 stepList.add(step);
1406
1407                 //avoid duplicate for subsection (zool.)
1408                 stepName = "Update idInVoc for subsection (zool.)";
1409                 sql = " UPDATE DefinedTermBase SET idInVocabulary = 'subsect.(zool.)' WHERE uuid = '0ed32d28-adc4-4303-a9ca-68e2acd67e33'";
1410                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, "DefinedTermBase");
1411                 stepList.add(step);
1412
1413
1414                 //SpecimenTypeDesignationStatus => all
1415                 stepName = "Update idInVocabulary for SpecimenTypeDesignationStatus";
1416                 uuid = "ab177bd7-d3c8-4e58-a388-226fff6ba3c2";
1417                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1418                 stepList.add(step);
1419
1420                 //avoid duplicate for PT
1421                 stepName = "Update idInVoc for Phototype (PhT) to avoid duplicate for PT";
1422                 sql = " UPDATE DefinedTermBase SET idInVocabulary = 'PhT' WHERE uuid = 'b7807acc-f559-474e-ad4a-e7a41e085e34'";
1423                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, "DefinedTermBase");
1424                 stepList.add(step);
1425
1426                 //NameTypeDesignationStatus => all
1427                 stepName = "Update idInVocabulary for NameTypeDesignationStatus";
1428                 uuid = "ab60e738-4d09-4c24-a1b3-9466b01f9f55";
1429                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1430                 stepList.add(step);
1431
1432                 //NomenclaturalStatusType => all, abbrevs.
1433                 stepName = "Update idInVocabulary for NomenclaturalStatusType";
1434                 uuid = "bb28cdca-2f8a-4f11-9c21-517e9ae87f1f";
1435                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1436                 stepList.add(step);
1437
1438                 //TaxonRelationshipType, all but 2 (Invalid Designation for, Misapplied Name for)
1439                 stepName = "Update idInVocabulary for TaxonRelationshipType";
1440                 uuid = "15db0cf7-7afc-4a86-a7d4-221c73b0c9ac";
1441                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1442                 stepList.add(step);
1443
1444                 //avoid duplicate for Misapplied Name (remove '-')
1445                 stepName = "Update idInVoc for Misapplied Name Relationship";
1446                 sql = " UPDATE DefinedTermBase SET idInVocabulary = NULL WHERE uuid = '1ed87175-59dd-437e-959e-0d71583d8417'";
1447                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, "DefinedTermBase");
1448                 stepList.add(step);
1449
1450                 //avoid duplicate for Invalid designation (remove '-')
1451                 stepName = "Update idInVoc for Invalid Designation";
1452                 sql = " UPDATE DefinedTermBase SET idInVocabulary = NULL WHERE uuid = '605b1d01-f2b1-4544-b2e0-6f08def3d6ed'";
1453                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, "DefinedTermBase");
1454                 stepList.add(step);
1455
1456                 //PresenceTerm => all
1457                 stepName = "Update idInVocabulary for PresenceTerm";
1458                 uuid = "adbbbe15-c4d3-47b7-80a8-c7d104e53a05";
1459                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1460                 stepList.add(step);
1461
1462                 //AbsenceTerm => all
1463                 stepName = "Update idInVocabulary for AbsenceTerm";
1464                 uuid = "5cd438c8-a8a1-4958-842e-169e83e2ceee";
1465                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1466                 stepList.add(step);
1467
1468                 //Sex => all
1469                 stepName = "Update idInVocabulary for Sex";
1470                 uuid = "9718b7dd-8bc0-4cad-be57-3c54d4d432fe";
1471                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1472                 stepList.add(step);
1473
1474                 //ExtensionType => all
1475                 stepName = "Update idInVocabulary for ExtensionType";
1476                 uuid = "117cc307-5bd4-4b10-9b2f-2e14051b3b20";
1477                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1478                 stepList.add(step);
1479
1480                 //ReferenceSystem => all
1481                 stepName = "Update idInVocabulary for ReferenceSystem";
1482                 uuid = "ec6376e5-0c9c-4f5c-848b-b288e6c17a86";
1483                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1484                 stepList.add(step);
1485
1486                 //DeterminationModifier => all
1487                 stepName = "Update idInVocabulary for DeterminationModifier";
1488                 uuid = "fe87ea8d-6e0a-4e5d-b0da-0ab8ea67ca77";
1489                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(queryVocUuid, uuid)).setDefaultAuditing(tableName);
1490                 stepList.add(step);
1491
1492                 //InstitutionType, MeasurementUnit, Scope, Stage, State, TextFormat, Modifier, PreservationMethod => dummies
1493                 stepName = "Update idInVocabulary for dummy terms in several vocabularies";
1494                 query = " UPDATE DefinedTermBase dtb " +
1495                                 " SET dtb.idInVocabulary = (SELECT abbreviatedlabel " +
1496                                         " FROM DefinedTermBase_Representation MN " +
1497                                         " INNER JOIN Representation r ON r.id = MN.representations_id " +
1498                                         " WHERE MN.DefinedTermBase_id = dtb.id) " +
1499                                 " WHERE dtb.termType IN ('%s','%s','%s','%s','%s','%s','%s','%s')";
1500                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, String.format(query,
1501                                 TermType.InstitutionType.getKey(), TermType.MeasurementUnit.getKey(),
1502                                 TermType.Scope.getKey(), TermType.Stage.getKey(), TermType.State.getKey(),
1503                                 TermType.TextFormat.getKey(), TermType.Modifier.getKey(), TermType.Method.getKey()))
1504                                 .setDefaultAuditing(tableName);
1505                 stepList.add(step);
1506
1507                 stepName = "Update idInVocabulary for dummy state";
1508                 query = " UPDATE DefinedTermBase dtb " +
1509                                 " SET dtb.idinvocabulary = 'std' " +
1510                                 " WHERE dtb.uuid = '881b9c80-626d-47a6-b308-a63ee5f4178f' ";
1511                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query);
1512                 stepList.add(step);
1513
1514                 stepName = "Update idInVocabulary for dummy stage";
1515                 query = " UPDATE DefinedTermBase dtb " +
1516                                 " SET dtb.idinvocabulary = 'sgd' " +
1517                                 " WHERE dtb.uuid = '48f8e8a7-a2ac-4974-9ce8-6944afc5095e' ";
1518                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query);
1519                 stepList.add(step);
1520
1521                 stepName = "Update idInVocabulary for dummy modifier";
1522                 query = " UPDATE DefinedTermBase dtb " +
1523                                 " SET dtb.idinvocabulary = 'md' " +
1524                                 " WHERE dtb.uuid = 'efc38dad-205c-4028-ad9d-ae509a14b37a' ";
1525                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query);
1526                 stepList.add(step);
1527
1528                 //Remove state dummy
1529                 stepName = "Remove state dummy if possible";
1530                 uuid = "881b9c80-626d-47a6-b308-a63ee5f4178f";
1531                 String checkUsed = " SELECT count(*) as n FROM StateData sd " +
1532                                 " WHERE sd.state_id = %d ";
1533                 step = SingleTermRemover.NewInstance(stepName, uuid, checkUsed);
1534                 stepList.add(step);
1535
1536                 //Remove institution type dummy
1537                 stepName = "Remove institution type dummy term";
1538                 uuid = "bea94a6c-472b-421c-abc1-52f797c51dbf";
1539                 checkUsed = " SELECT count(*) as n FROM AgentBase_DefinedTermBase MN " +
1540                                 " WHERE MN.types_id = %d ";
1541                 step = SingleTermRemover.NewInstance(stepName, uuid, checkUsed);
1542                 stepList.add(step);
1543
1544                 //Remove measurement unit dummy
1545                 stepName = "Remove measurement unit dummy term";
1546                 uuid = "e19dd590-5be8-4c93-978f-b78554116289";
1547                 checkUsed = " SELECT count(*) as n FROM DescriptionElementBase deb " +
1548                                 " WHERE deb.unit_id = %d ";
1549                 step = SingleTermRemover.NewInstance(stepName, uuid, checkUsed);
1550                 stepList.add(step);
1551
1552                 //Remove scope dummy
1553                 stepName = "Remove scope dummy term";
1554                 uuid = "2ace7f1f-4ce6-47e1-8a65-e3f6b724876c";
1555                 checkUsed = " SELECT count(*) as n FROM DescriptionBase_Scope MN " +
1556                                 " WHERE MN.scopes_id = %d ";
1557                 step = SingleTermRemover.NewInstance(stepName, uuid, checkUsed);
1558                 stepList.add(step);
1559
1560                 //Remove stage dummy
1561                 stepName = "Remove stage dummy term";
1562                 uuid = "48f8e8a7-a2ac-4974-9ce8-6944afc5095e";
1563                 checkUsed = " SELECT count(*) as n FROM DescriptionBase_Scope MN " +
1564                                 " WHERE MN.scopes_id = %d ";
1565                 String checkUsed2 = " SELECT count(*) as n FROM SpecimenOrObservationBase osb " +
1566                                 " WHERE osb.lifestage_id =  %d ";
1567                 step = SingleTermRemover.NewInstance(stepName, uuid, checkUsed)
1568                                 .addCheckUsedQuery(checkUsed2);
1569                 stepList.add(step);
1570
1571                 //Remove text format dummy
1572                 stepName = "Remove text format dummy if possible";
1573                 uuid = "5d095782-d99c-46bc-a158-edb2e47c9b63";
1574                 checkUsed = " SELECT count(*) as n FROM DescriptionElementBase deb " +
1575                                 " WHERE deb.format_id = %d ";
1576                 step = SingleTermRemover.NewInstance(stepName, uuid, checkUsed);
1577                 stepList.add(step);
1578
1579                 //Remove modifier dummy
1580                 stepName = "Remove modifier dummy if possible";
1581                 uuid = "efc38dad-205c-4028-ad9d-ae509a14b37a";
1582                 checkUsed = " SELECT count(*) as n FROM DescriptionElementBase_Modifier MN " +
1583                                 " WHERE MN.modifiers_id = %d ";
1584                 checkUsed2 = " SELECT count(*) as n FROM StateData_DefinedTermBase MN " +
1585                                 " WHERE MN.modifiers_id = %d ";
1586                 String checkUsed3 = " SELECT count(*) as n FROM StatisticalMeasurementValue_DefinedTermBase MN " +
1587                                 " WHERE MN.modifiers_id = %d ";
1588                 step = SingleTermRemover.NewInstance(stepName, uuid, checkUsed)
1589                                 .addCheckUsedQuery(checkUsed2)
1590                                 .addCheckUsedQuery(checkUsed3);
1591                 stepList.add(step);
1592
1593                 //Remove text preservation method dummy
1594                 stepName = "Remove preservation method dummy if possible";
1595                 uuid = "3edc2633-365b-4a9b-bc3a-f3f85f59dbdf";
1596                 checkUsed = " SELECT count(*) as n FROM SpecimenOrObservationBase osb " +
1597                                 " WHERE osb.preservation_id =  %d ";
1598                 step = SingleTermRemover.NewInstance(stepName, uuid, checkUsed);
1599                 stepList.add(step);
1600
1601                 //Split Country Vocabulary  #3700
1602                 stepName = "Create Waterbody vocabulary";
1603                 UUID uuidVocabulary = UUID.fromString("35a62b25-f541-4f12-a7c7-17d90dec3e03");
1604                 String description = "Major Waterbodies of the World";
1605                 String label = "Waterbody";
1606                 String abbrev = null;
1607                 boolean isOrdered = false;
1608                 TermType termType = TermType.NamedArea;
1609                 Class<?> termClass = NamedArea.class;
1610                 step = VocabularyCreator.NewVocabularyInstance(uuidVocabulary, description, label, abbrev, isOrdered, termClass, termType);
1611                 stepList.add(step);
1612
1613                 stepName = "Move waterbodies to new vocabulary";
1614                 UUID newVocabulary = UUID.fromString("35a62b25-f541-4f12-a7c7-17d90dec3e03");
1615                 step = TermMover.NewInstance(stepName, newVocabulary, "aa96ca19-46ab-6365-af29-e4842f13eb4c")
1616                         .addTermUuid(UUID.fromString("36aea55c-46ab-6365-af29-e4842f13eb4c"))
1617                         .addTermUuid(UUID.fromString("36aea55c-892c-6365-af29-e4842f13eb4c"))
1618                         .addTermUuid(UUID.fromString("36aea55c-892c-4114-af29-d4b287f76fab"))
1619                         .addTermUuid(UUID.fromString("aa96ca19-892c-4114-af29-d4b287f76fab"))
1620                         .addTermUuid(UUID.fromString("aa96ca19-892c-4114-a494-d4b287f76fab"))
1621                         .addTermUuid(UUID.fromString("d4cf6c57-892c-4114-bf57-96886eb7108a"))
1622                         .addTermUuid(UUID.fromString("d4cf6c57-892c-c953-a494-96886eb7108a"))
1623                         .addTermUuid(UUID.fromString("aa96ca19-46ab-c953-a494-96886eb7108a"))
1624                         .addTermUuid(UUID.fromString("aa96ca19-46ab-4114-a494-96886eb7108a"))
1625                         ;
1626                 stepList.add(step);
1627
1628                 //update waterbody uuids   #3705   AND waterbody DTYPE to NamedArea and sortindex new #3700
1629                 stepName = "Update waterbody uuids";
1630                 sql =" UPDATE DefinedTermBase SET DTYPE = 'NamedArea', orderindex = 1, uuid = 'af4271e5-8897-4e6f-9db7-54ea4f28cfc0' WHERE uuid = 'aa96ca19-46ab-6365-af29-e4842f13eb4c' ";
1631                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, "DefinedTermBase");
1632                 stepList.add(step);
1633                 sql =" UPDATE DefinedTermBase SET DTYPE = 'NamedArea', orderindex = 2, uuid = '77e79804-1b17-4c99-873b-933fe216e3da' WHERE uuid = '36aea55c-46ab-6365-af29-e4842f13eb4c' ";
1634                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, "DefinedTermBase");
1635                 stepList.add(step);
1636                 sql =" UPDATE DefinedTermBase SET DTYPE = 'NamedArea', orderindex = 3, uuid = '3d68a327-104c-49d5-a2d8-c71c6600181b' WHERE uuid = '36aea55c-892c-6365-af29-e4842f13eb4c' ";
1637                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, "DefinedTermBase");
1638                 stepList.add(step);
1639                 sql =" UPDATE DefinedTermBase SET DTYPE = 'NamedArea', orderindex = 4, uuid = 'ff744a37-5990-462c-9c20-1e85a9943851' WHERE uuid = '36aea55c-892c-4114-af29-d4b287f76fab' ";
1640                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, "DefinedTermBase");
1641                 stepList.add(step);
1642                 sql =" UPDATE DefinedTermBase SET DTYPE = 'NamedArea', orderindex = 5, uuid = 'ef04f363-f67f-4a2c-8d98-110de4c5f654' WHERE uuid = 'aa96ca19-892c-4114-af29-d4b287f76fab' ";
1643                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, "DefinedTermBase");
1644                 stepList.add(step);
1645                 sql =" UPDATE DefinedTermBase SET DTYPE = 'NamedArea', orderindex = 6, uuid = '8811a47e-29d6-4455-8f83-8916b78a692f' WHERE uuid = 'aa96ca19-892c-4114-a494-d4b287f76fab' ";
1646                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, "DefinedTermBase");
1647                 stepList.add(step);
1648                 sql =" UPDATE DefinedTermBase SET DTYPE = 'NamedArea', orderindex = 7, uuid = '4cb4bbae-9aab-426c-9025-e34f809165af' WHERE uuid = 'd4cf6c57-892c-4114-bf57-96886eb7108a' ";
1649                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, "DefinedTermBase");
1650                 stepList.add(step);
1651                 sql =" UPDATE DefinedTermBase SET DTYPE = 'NamedArea', orderindex = 8, uuid = '598fec0e-b93a-4947-a1f3-601e380797f7' WHERE uuid = 'd4cf6c57-892c-c953-a494-96886eb7108a' ";
1652                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, "DefinedTermBase");
1653                 stepList.add(step);
1654                 sql =" UPDATE DefinedTermBase SET DTYPE = 'NamedArea', orderindex = 9, uuid = 'ee69385e-6c80-405c-be6e-974e9fd1e297' WHERE uuid = 'aa96ca19-46ab-c953-a494-96886eb7108a' ";
1655                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, "DefinedTermBase");
1656                 stepList.add(step);
1657                 sql =" UPDATE DefinedTermBase SET DTYPE = 'NamedArea', orderindex = 10, uuid = '8dc16e70-74b8-4143-95cf-a659a319a854' WHERE uuid = 'aa96ca19-46ab-4114-a494-96886eb7108a' ";
1658                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, "DefinedTermBase");
1659                 stepList.add(step);
1660
1661                 //update DTYPE for country
1662                 stepName = "Update DTYPE for Countries";
1663                 sql =" UPDATE DefinedTermBase SET DTYPE = 'Country' WHERE DTYPE = 'WaterbodyOrCountry' ";
1664                 step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, "DefinedTermBase");
1665                 stepList.add(step);
1666
1667                 //Rename tables
1668                 stepName = "Rename DefinedTermBase_WaterbodyOrCountry";
1669                 String oldName = "DefinedTermBase_WaterbodyOrCountry";
1670                 String newName = "DefinedTermBase_Country";
1671                 step = TableNameChanger.NewInstance(stepName, oldName, newName, INCLUDE_AUDIT);
1672                 stepList.add(step);
1673
1674                 //rename column
1675                 stepName = "Rename DefinedTermBase_Country.waterbodiesorcountries_id";
1676                 tableName = "DefinedTermBase_Country";
1677                 String oldColumnName = "waterbodiesorcountries_id";
1678                 String newColumnName = "countries_id";
1679                 step = ColumnNameChanger.NewIntegerInstance(stepName, tableName, oldColumnName, newColumnName, INCLUDE_AUDIT);
1680                 stepList.add(step);
1681
1682
1683                 //NULL for empty strings
1684                 stepName = "Update idInVocabulary, replace empty strings by null";
1685                 query = "Update DefinedTermBase dtb SET idInVocabulary = NULL WHERE idInVocabulary = ''";
1686                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1687                 stepList.add(step);
1688
1689                 //MarkerType, AnnotationType, NamedAreaType, NamedAreaLevel, Feature, Continent, DerivationEventType, StatisticalMeasure, RightsType,SynonymRelationshipType & HybridRelationshipType & NameRelationshipType
1690                 //=> none
1691
1692                 //DnaMarker => yes but no entries
1693
1694                 //Clean up empty abbreviated labels in representations
1695                 stepName = "Update abbreviated label, replace empty strings by null";
1696                 query = "Update Representation r SET r.abbreviatedLabel = NULL WHERE r.abbreviatedLabel = ''";
1697                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing("Representation");  //AUD not needed
1698                 stepList.add(step);
1699
1700         }
1701
1702         private void updateTermTypesForVocabularies( List<ISchemaUpdaterStep> stepList) {
1703                 //vocabularies with terms
1704                 for (TermType termType : TermType.values()){
1705                         updateTermTypeForVocabularies(stepList, termType);
1706                 }
1707
1708                 String tableName = "TermVocabulary";
1709                 //TODO _AUD
1710                 //Natural Language Terms
1711                 String stepName = "Updater termType for NaturalLanguageTerms";
1712                 String query = "UPDATE TermVocabulary voc " +
1713                                 " SET voc.termType = '" + TermType.NaturalLanguageTerm.getKey() + "' " +
1714                                 " WHERE voc.uuid = 'fdaba4b0-5c14-11df-a08a-0800200c9a66'";
1715                 ISchemaUpdaterStep step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1716                 stepList.add(step);
1717
1718                 //remaining vocabularies
1719                 stepName = "Updater termType for remaining vocabularies";
1720                 query = "UPDATE TermVocabulary voc " +
1721                                 " SET voc.termType = '"+ TermType.Unknown.getKey() +"' " +
1722                                 " WHERE voc.termType IS NULL";
1723                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1724                 stepList.add(step);
1725
1726
1727         }
1728
1729         private void updateTermTypeForVocabularies(List<ISchemaUpdaterStep> stepList, TermType termType) {
1730                 String stepName = "Updater vocabulary termType for " + termType.toString();
1731                 String query = "UPDATE TermVocabulary voc " +
1732                                 " SET voc.termType = '" + termType.getKey() + "' " +
1733                                 " WHERE Exists (SELECT * FROM DefinedTermBase dtb WHERE dtb.termType = '" + termType.getKey() + "' AND dtb.vocabulary_id = voc.id)";
1734                 ISchemaUpdaterStep step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing("TermVocabulary");  //AUD not fully correct as subselect should also work on AUD, good enough for our purposes
1735                 stepList.add(step);
1736         }
1737
1738         /**
1739          * @param stepList
1740          * @param stepName
1741          */
1742         private void updateTermTypesForTerms(List<ISchemaUpdaterStep> stepList) {
1743                 String stepName = "Update termType for NamedAreas";
1744                 String tableName = "DefinedTermBase";
1745
1746                 //NamedArea
1747                 String query = " UPDATE DefinedTermBase " +
1748                                 " SET termType = '" + TermType.NamedArea.getKey() + "' " +
1749                                 " WHERE DTYPE = '" + NamedArea.class.getSimpleName() + "' OR DTYPE = 'TdwgArea' OR DTYPE = '"+ Country.class.getSimpleName() + "' OR DTYPE = 'Continent' ";
1750                 ISchemaUpdaterStep step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1751                 stepList.add(step);
1752
1753                 //Lanugage
1754                 query = " UPDATE DefinedTermBase " +
1755                                 " SET termType = '" + TermType.Language.getKey() + "' " +
1756                                 " WHERE DTYPE = '" + Language.class.getSimpleName() + "' ";
1757                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1758                 stepList.add(step);
1759
1760                 //RANK
1761                 query = " UPDATE DefinedTermBase " +
1762                                 " SET termType = '" + TermType.Rank.getKey() + "' " +
1763                                 " WHERE DTYPE = '" + Rank.class.getSimpleName() + "' ";
1764                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1765                 stepList.add(step);
1766
1767                 //Feature
1768                 query = " UPDATE DefinedTermBase " +
1769                                 " SET termType = '" + TermType.Feature.getKey() + "' " +
1770                                 " WHERE DTYPE = '" + Feature.class.getSimpleName() + "' ";
1771                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1772                 stepList.add(step);
1773
1774                 //AnnotationType
1775                 query = " UPDATE DefinedTermBase " +
1776                                 " SET termType = '" + TermType.AnnotationType.getKey() + "' " +
1777                                 " WHERE DTYPE = '" + AnnotationType.class.getSimpleName() + "' ";
1778                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1779                 stepList.add(step);
1780
1781                 //MarkerType
1782                 query = " UPDATE DefinedTermBase " +
1783                                 " SET termType = '" + TermType.MarkerType.getKey() + "' " +
1784                                 " WHERE DTYPE = '" + MarkerType.class.getSimpleName() + "' ";
1785                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1786                 stepList.add(step);
1787
1788                 //ExtensionType
1789                 query = " UPDATE DefinedTermBase " +
1790                                 " SET termType = '" + TermType.ExtensionType.getKey() + "' " +
1791                                 " WHERE DTYPE = '" + ExtensionType.class.getSimpleName() + "' ";
1792                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1793                 stepList.add(step);
1794
1795                 //DerivationEventType
1796                 query = " UPDATE DefinedTermBase " +
1797                                 " SET termType = '" + TermType.DerivationEventType.getKey() + "' " +
1798                                 " WHERE DTYPE = '" + DerivationEventType.class.getSimpleName() + "' ";
1799                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1800                 stepList.add(step);
1801
1802                 //PresenceAbsenceTerm
1803                 query = " UPDATE DefinedTermBase " +
1804                                 " SET termType = '" + TermType.PresenceAbsenceTerm.getKey() + "' " +
1805                                 " WHERE DTYPE = 'PresenceTerm' OR DTYPE = 'AbsenceTerm'";
1806                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1807                 stepList.add(step);
1808
1809                 //NomenclaturalStatusType
1810                 query = " UPDATE DefinedTermBase " +
1811                                 " SET termType = '" + TermType.NomenclaturalStatusType.getKey() + "' " +
1812                                 " WHERE DTYPE = '" + NomenclaturalStatusType.class.getSimpleName() + "' ";
1813                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1814                 stepList.add(step);
1815
1816                 //NameRelationshipType
1817                 query = " UPDATE DefinedTermBase " +
1818                                 " SET termType = '" + TermType.NameRelationshipType.getKey() + "' " +
1819                                 " WHERE DTYPE = '" + NameRelationshipType.class.getSimpleName() + "' ";
1820                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1821                 stepList.add(step);
1822
1823                 //HybridRelationshipType
1824                 query = " UPDATE DefinedTermBase " +
1825                                 " SET termType = '" + TermType.HybridRelationshipType.getKey() + "' " +
1826                                 " WHERE DTYPE = '" + HybridRelationshipType.class.getSimpleName() + "' ";
1827                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1828                 stepList.add(step);
1829
1830                 //SynonymRelationshipType
1831                 query = " UPDATE DefinedTermBase " +
1832                                 " SET termType = '" + TermType.SynonymRelationshipType.getKey() + "' " +
1833                                 " WHERE DTYPE = '" + SynonymRelationshipType.class.getSimpleName() + "' ";
1834                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1835                 stepList.add(step);
1836
1837                 //TaxonRelationshipType
1838                 query = " UPDATE DefinedTermBase " +
1839                                 " SET termType = '" + TermType.TaxonRelationshipType.getKey() + "' " +
1840                                 " WHERE DTYPE = '" + TaxonRelationshipType.class.getSimpleName() + "' ";
1841                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1842                 stepList.add(step);
1843
1844                 //NameTypeDesignationStatus
1845                 query = " UPDATE DefinedTermBase " +
1846                                 " SET termType = '" + TermType.NameTypeDesignationStatus.getKey() + "' " +
1847                                 " WHERE DTYPE = '" + NameTypeDesignationStatus.class.getSimpleName() + "' ";
1848                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1849                 stepList.add(step);
1850
1851                 //SpecimenTypeDesignationStatus
1852                 query = " UPDATE DefinedTermBase " +
1853                                 " SET termType = '" + TermType.SpecimenTypeDesignationStatus.getKey() + "' " +
1854                                 " WHERE DTYPE = '" + SpecimenTypeDesignationStatus.class.getSimpleName() + "' ";
1855                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1856                 stepList.add(step);
1857
1858                 //InstitutionType
1859                 query = " UPDATE DefinedTermBase " +
1860                                 " SET termType = '" + TermType.InstitutionType.getKey() + "' " +
1861                                 " WHERE DTYPE = 'InstitutionType' ";
1862                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1863                 stepList.add(step);
1864
1865                 //NamedAreaType
1866                 query = " UPDATE DefinedTermBase " +
1867                                 " SET termType = '" + TermType.NamedAreaType.getKey() + "' " +
1868                                 " WHERE DTYPE = '" + NamedAreaType.class.getSimpleName() + "' ";
1869                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1870                 stepList.add(step);
1871
1872                 //NamedAreaLevel
1873                 query = " UPDATE DefinedTermBase " +
1874                                 " SET termType = '" + TermType.NamedAreaLevel.getKey() + "' " +
1875                                 " WHERE DTYPE = '" + NamedAreaLevel.class.getSimpleName() + "' ";
1876                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1877                 stepList.add(step);
1878
1879                 //RightsType
1880                 query = " UPDATE DefinedTermBase " +
1881                                 " SET termType = '" + TermType.RightsType.getKey() + "' " +
1882                                 " WHERE DTYPE = 'RightsType' OR DTYPE = 'RightsTerm' ";
1883                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1884                 stepList.add(step);
1885
1886                 //MeasurementUnit
1887                 query = " UPDATE DefinedTermBase " +
1888                                 " SET termType = '" + TermType.MeasurementUnit.getKey() + "' " +
1889                                 " WHERE DTYPE = '" + MeasurementUnit.class.getSimpleName() + "' ";
1890                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1891                 stepList.add(step);
1892
1893                 //StatisticalMeasure
1894                 query = " UPDATE DefinedTermBase " +
1895                                 " SET termType = '" + TermType.StatisticalMeasure.getKey() + "' " +
1896                                 " WHERE DTYPE = '" + StatisticalMeasure.class.getSimpleName() + "' ";
1897                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1898                 stepList.add(step);
1899
1900                 //PreservationMethod
1901                 query = " UPDATE DefinedTermBase " +
1902                                 " SET termType = '" + TermType.Method.getKey() + "' " +
1903                                 " WHERE DTYPE = '" + PreservationMethod.class.getSimpleName() + "' ";
1904                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1905                 stepList.add(step);
1906
1907                 //Modifier
1908                 query = " UPDATE DefinedTermBase " +
1909                                 " SET termType = '" + TermType.Modifier.getKey() + "' " +
1910                                 " WHERE DTYPE = 'Modifier' ";
1911                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1912                 stepList.add(step);
1913
1914                 //Scope
1915                 query = " UPDATE DefinedTermBase " +
1916                                 " SET termType = '" + TermType.Scope.getKey() + "' " +
1917                                 " WHERE DTYPE = 'Scope' ";
1918                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1919                 stepList.add(step);
1920
1921                 //Stage
1922                 query = " UPDATE DefinedTermBase " +
1923                                 " SET termType = '" + TermType.Stage.getKey() + "' " +
1924                                 " WHERE DTYPE = 'Stage' ";
1925                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1926                 stepList.add(step);
1927
1928                 //Sex
1929                 query = " UPDATE DefinedTermBase " +
1930                                 " SET termType = '" + TermType.Sex.getKey() + "' " +
1931                                 " WHERE DTYPE = 'Sex' ";
1932                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1933                 stepList.add(step);
1934
1935                 //ReferenceSystem
1936                 query = " UPDATE DefinedTermBase " +
1937                                 " SET termType = '" + TermType.ReferenceSystem.getKey() + "' " +
1938                                 " WHERE DTYPE = '" + ReferenceSystem.class.getSimpleName() + "' ";
1939                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1940                 stepList.add(step);
1941
1942                 //State
1943                 query = " UPDATE DefinedTermBase " +
1944                                 " SET termType = '" + TermType.State.getKey() + "' " +
1945                                 " WHERE DTYPE = '" + State.class.getSimpleName() + "' ";
1946                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1947                 stepList.add(step);
1948
1949                 //NaturalLanguageTerm
1950                 query = " UPDATE DefinedTermBase " +
1951                                 " SET termType = '" + TermType.NaturalLanguageTerm.getKey() + "' " +
1952                                 " WHERE DTYPE = '" + NaturalLanguageTerm.class.getSimpleName() + "' ";
1953                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1954                 stepList.add(step);
1955
1956                 //TextFormat
1957                 query = " UPDATE DefinedTermBase " +
1958                                 " SET termType = '" + TermType.TextFormat.getKey() + "' " +
1959                                 " WHERE DTYPE = '" + TextFormat.class.getSimpleName() + "' ";
1960                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1961                 stepList.add(step);
1962
1963                 //DeterminationModifier
1964                 query = " UPDATE DefinedTermBase " +
1965                                 " SET termType = '" + TermType.DeterminationModifier.getKey() + "' " +
1966                                 " WHERE DTYPE = 'DeterminationModifier' ";
1967                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1968                 stepList.add(step);
1969
1970
1971         }
1972
1973         /**
1974          * @param stepList
1975          */
1976         private void updateDtypeOfDefinedTerms(List<ISchemaUpdaterStep> stepList) {
1977                 String tableName = "DefinedTermBase";
1978
1979                 //update DTYPE for institution type and modifiers (Stage, Scope, Sex, DeterminationModifier, Modifier) -> DefinedTerm
1980                 String stepName = "Update DTYPE for TDWG Areas";
1981                 String query = " UPDATE DefinedTermBase " +
1982                                 " SET DTYPE = 'DefinedTerm' " +
1983                                 " WHERE DTYPE = 'Stage' OR DTYPE = 'Scope' OR DTYPE = 'Sex' OR DTYPE = 'DeterminationModifier'  " +
1984                                         " OR DTYPE = 'Modifier' OR DTYPE = 'InstitutionType' ";
1985                 ISchemaUpdaterStep step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1986                 stepList.add(step);
1987
1988
1989                 //update DTYPE for TDWG Areas and Continents -> NamedArea
1990                 stepName = "Update DTYPE for TDWG Areas and Continents";
1991                 query = " UPDATE DefinedTermBase " +
1992                                 " SET DTYPE = 'NamedArea' " +
1993                                 " WHERE DTYPE = 'TdwgArea' OR DTYPE = 'Continent' ";
1994                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
1995                 stepList.add(step);
1996
1997         }
1998
1999         /**
2000          * @param stepList
2001          */
2002         private void changeUriType(List<ISchemaUpdaterStep> stepList) {
2003                 //#3345
2004                 String stepName;
2005                 String tableName;
2006                 ISchemaUpdaterStep step;
2007                 String columnName;
2008
2009                 stepName = "Update uri to clob for DefinedTermBase";
2010                 tableName = "DefinedTermBase";
2011                 columnName = "uri";
2012                 step = ColumnTypeChanger.NewClobInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
2013                 stepList.add(step);
2014
2015                 stepName = "Update uri to clob for TermVocabulary";
2016                 tableName = "TermVocabulary";
2017                 columnName = "uri";
2018                 step = ColumnTypeChanger.NewClobInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
2019                 stepList.add(step);
2020
2021                 //TODO are uri and termsourceuri needed ???
2022                 stepName = "Update termsourceuri to clob for TermVocabulary";
2023                 tableName = "TermVocabulary";
2024                 columnName = "termsourceuri";
2025                 step = ColumnTypeChanger.NewClobInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
2026                 stepList.add(step);
2027
2028                 stepName = "Update uri to clob for Reference";
2029                 tableName = "Reference";
2030                 columnName = "uri";
2031                 step = ColumnTypeChanger.NewClobInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
2032                 stepList.add(step);
2033
2034                 stepName = "Update uri to clob for Rights";
2035                 tableName = "Rights";
2036                 columnName = "uri";
2037                 step = ColumnTypeChanger.NewClobInstance(stepName, tableName, columnName,  INCLUDE_AUDIT);
2038                 stepList.add(step);
2039
2040                 stepName = "Update uri to clob for MediaRepresentationPart";
2041                 tableName = "MediaRepresentationPart";
2042                 columnName = "uri";
2043                 step = ColumnTypeChanger.NewClobInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
2044                 stepList.add(step);
2045
2046                 //TODO still needed??
2047                 stepName = "Update uri to clob for FeatureTree";
2048                 tableName = "FeatureTree";
2049                 columnName = "uri";
2050                 step = ColumnTypeChanger.NewClobInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
2051                 stepList.add(step);
2052
2053                 //Annotation.linkbackUri (change from URL to URI)
2054                 stepName = "Update url to uri (->clob) for Annotation.linkbackUri";
2055                 tableName = "Annotation";
2056                 columnName = "linkbackUrl";
2057                 step = ColumnTypeChanger.NewClobInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
2058                 stepList.add(step);
2059
2060         }
2061
2062         /**
2063          * @param stepList
2064          * @return
2065          */
2066         private void addTimeperiodToDescriptionElement(
2067                         List<ISchemaUpdaterStep> stepList) {
2068                 String stepName;
2069                 String tableName;
2070                 ISchemaUpdaterStep step;
2071                 String columnName;
2072                 //start  #3312
2073                 stepName = "Create time period start column in description element base";
2074                 tableName = "DescriptionElementBase";
2075                 columnName = "timeperiod_start";
2076                 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
2077                 stepList.add(step);
2078
2079                 //end #3312
2080                 stepName = "Create time period end column in description element base";
2081                 tableName = "DescriptionElementBase";
2082                 columnName = "timeperiod_end";
2083                 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
2084                 stepList.add(step);
2085
2086                 //freetext #3312
2087                 stepName = "Create time period freetext column in description element base";
2088                 tableName = "DescriptionElementBase";
2089                 columnName = "timeperiod_freetext";
2090                 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
2091                 stepList.add(step);
2092
2093                 return;
2094         }
2095
2096         private void updateElevationMax(List<ISchemaUpdaterStep> stepList) {
2097                 //create column
2098                 String stepName = "Create absoluteElevationMax column";
2099                 String tableName = "GatheringEvent";
2100                 String columnName = "absoluteElevationMax";
2101                 ISchemaUpdaterStep step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false, null);
2102                 stepList.add(step);
2103
2104                 String audTableName = "GatheringEvent";
2105                 //update max
2106                 stepName = "Update gathering elevation max";
2107                 //all audits to unknown type
2108                 String query = " UPDATE GatheringEvent ge " +
2109                                 " SET ge.absoluteElevationMax = ge.absoluteElevation + ge.absoluteElevationError,  " +
2110                                 "     ge.absoluteElevation =  ge.absoluteElevation - ge.absoluteElevationError" +
2111                                 " WHERE ge.absoluteElevationError is not null ";
2112                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(audTableName);
2113                 stepList.add(step);
2114                 //TODO same for AUD
2115
2116                 //remove error column
2117                 stepName = "Remove elevationErrorRadius column";
2118                 tableName = "GatheringEvent";
2119                 columnName = "absoluteElevationError";
2120                 step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
2121                 stepList.add(step);
2122
2123                 //create column absoluteElevationText
2124                 stepName = "Create absoluteElevationText column";
2125                 tableName = "GatheringEvent";
2126                 columnName = "absoluteElevationText";
2127                 //TODO size
2128                 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT);
2129                 stepList.add(step);
2130
2131                 //retype distanceToGround
2132                 stepName = "Rname distanceToGround column";
2133                 tableName = "GatheringEvent";
2134                 String strOldColumnName = "distanceToGround";
2135                 step = ColumnTypeChanger.NewInt2DoubleInstance(stepName, tableName, strOldColumnName, INCLUDE_AUDIT);
2136                 stepList.add(step);
2137
2138                 //create column distanceToGroundMax
2139                 stepName = "Create distanceToGroundMax column";
2140                 tableName = "GatheringEvent";
2141                 columnName = "distanceToGroundMax";
2142                 step = ColumnAdder.NewDoubleInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false);
2143                 stepList.add(step);
2144
2145
2146                 //create column distanceToGroundText
2147                 stepName = "Create distanceToGroundText column";
2148                 tableName = "GatheringEvent";
2149                 columnName = "distanceToGroundText";
2150                 //TODO size
2151                 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT);
2152                 stepList.add(step);
2153
2154                 //retype distanceToGround
2155                 stepName = "Rname distanceToWaterSurface column";
2156                 tableName = "GatheringEvent";
2157                 strOldColumnName = "distanceToWaterSurface";
2158                 step = ColumnTypeChanger.NewInt2DoubleInstance(stepName, tableName, strOldColumnName, INCLUDE_AUDIT);
2159                 stepList.add(step);
2160
2161                 //create column distanceToWaterSurface
2162                 stepName = "Create distanceToWaterSurfaceMax column";
2163                 tableName = "GatheringEvent";
2164                 columnName = "distanceToWaterSurfaceMax";
2165                 step = ColumnAdder.NewDoubleInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false);
2166                 stepList.add(step);
2167
2168
2169                 //create column distanceToGroundText
2170                 stepName = "Create distanceToWaterSurfaceText column";
2171                 tableName = "GatheringEvent";
2172                 columnName = "distanceToWaterSurfaceText";
2173                 //TODO size
2174                 step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT);
2175                 stepList.add(step);
2176
2177         }
2178
2179         /**
2180          * @param stepList
2181          */
2182         private void updateOriginalSourceType(List<ISchemaUpdaterStep> stepList) {
2183                 String stepName;
2184                 String typeAttrName = "sourceType";
2185                 ISchemaUpdaterStep step;
2186                 String tableName = "OriginalSourceBase";
2187
2188                  //all data to unknown
2189                 stepName = "Update original source type column: set all to unknown";
2190                 String query = String.format("UPDATE OriginalSourceBase " +
2191                                 " SET %s = '%s' ", typeAttrName, OriginalSourceType.Unknown.getKey());
2192                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
2193                 stepList.add(step);
2194
2195                  //all IMPORTS recognized by idInSOurce and by missing nameInSource
2196                 stepName = "Update original source type column: set to 'import' where possible";
2197                 query = String.format("UPDATE OriginalSourceBase " +
2198                                 " SET %s = '%s' " +
2199                                 " WHERE " +
2200                                 "((idInSource IS NOT NULL) OR (idNamespace IS NOT NULL))  AND " +
2201                                 "( nameUsedInSource_id IS NULL AND originalNameString IS NULL ) ", typeAttrName, OriginalSourceType.Import.getKey());
2202                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
2203                 stepList.add(step);
2204
2205                  //all PRIMARY TAXONOMIC SOURCES recognized by missing idInSource and namespace and by existing citation
2206                 stepName = "Update original source type column: set to 'primary taxonomic source' where possible";
2207                 query = String.format("UPDATE OriginalSourceBase SET  %s = '%s' WHERE " +
2208                                 "(idInSource IS NULL AND idNamespace IS NULL) AND " +
2209                                 "( citation_id IS NOT NULL ) ", typeAttrName, OriginalSourceType.PrimaryTaxonomicSource.getKey());
2210                 step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query).setDefaultAuditing(tableName);
2211                 stepList.add(step);
2212         }
2213
2214         /* (non-Javadoc)
2215          * @see eu.etaxonomy.cdm.database.update.SchemaUpdaterBase#getNextUpdater()
2216          */
2217         @Override
2218         public ISchemaUpdater getNextUpdater() {
2219                 return null;
2220         }
2221
2222         /* (non-Javadoc)
2223          * @see eu.etaxonomy.cdm.database.update.SchemaUpdaterBase#getPreviousUpdater()
2224          */
2225         @Override
2226         public ISchemaUpdater getPreviousUpdater() {
2227                 return SchemaUpdater_30_301.NewInstance();
2228         }
2229
2230 }