X-Git-Url: https://dev.e-taxonomy.eu/gitweb/cdmlib.git/blobdiff_plain/219acb965c82dff00ca8a665fd70e9a5f3ebc2ca..7d84de65196bbb2d37f39be3beef76c8990cf741:/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v31_33/SchemaUpdater_31_33.java diff --git a/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v31_33/SchemaUpdater_31_33.java b/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v31_33/SchemaUpdater_31_33.java index e3574e838a..0ce81ad07e 100644 --- a/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v31_33/SchemaUpdater_31_33.java +++ b/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v31_33/SchemaUpdater_31_33.java @@ -1,22 +1,22 @@ // $Id$ /** -* Copyright (C) 2007 EDIT -* European Distributed Institute of Taxonomy -* http://www.e-taxonomy.eu -* -* The contents of this file are subject to the Mozilla Public License Version 1.1 -* See LICENSE.TXT at the top of this package for the full license terms. -*/ + * Copyright (C) 2007 EDIT + * European Distributed Institute of Taxonomy + * http://www.e-taxonomy.eu + * + * The contents of this file are subject to the Mozilla Public License Version 1.1 + * See LICENSE.TXT at the top of this package for the full license terms. + */ package eu.etaxonomy.cdm.database.update.v31_33; import java.util.ArrayList; import java.util.List; +import java.util.UUID; import org.apache.log4j.Logger; -import com.sun.tools.xjc.reader.gbind.Sequence; - +import eu.etaxonomy.cdm.database.update.ClassChanger; import eu.etaxonomy.cdm.database.update.ColumnAdder; import eu.etaxonomy.cdm.database.update.ColumnNameChanger; import eu.etaxonomy.cdm.database.update.ColumnRemover; @@ -26,30 +26,33 @@ import eu.etaxonomy.cdm.database.update.ISchemaUpdaterStep; import eu.etaxonomy.cdm.database.update.MnTableCreator; import eu.etaxonomy.cdm.database.update.SchemaUpdaterBase; import eu.etaxonomy.cdm.database.update.SimpleSchemaUpdaterStep; +import eu.etaxonomy.cdm.database.update.SingleTermRemover; +import eu.etaxonomy.cdm.database.update.SortIndexUpdater; import eu.etaxonomy.cdm.database.update.TableCreator; import eu.etaxonomy.cdm.database.update.TableDroper; +import eu.etaxonomy.cdm.database.update.TableNameChanger; +import eu.etaxonomy.cdm.database.update.TermMover; import eu.etaxonomy.cdm.database.update.TreeIndexUpdater; -import eu.etaxonomy.cdm.database.update.v24_25.SortIndexUpdater; +import eu.etaxonomy.cdm.database.update.VocabularyCreator; import eu.etaxonomy.cdm.database.update.v30_31.SchemaUpdater_30_301; import eu.etaxonomy.cdm.model.common.AnnotationType; import eu.etaxonomy.cdm.model.common.ExtensionType; import eu.etaxonomy.cdm.model.common.Language; import eu.etaxonomy.cdm.model.common.MarkerType; -import eu.etaxonomy.cdm.model.common.MaterialAndMethod; import eu.etaxonomy.cdm.model.common.OriginalSourceType; import eu.etaxonomy.cdm.model.common.TermType; import eu.etaxonomy.cdm.model.description.Feature; import eu.etaxonomy.cdm.model.description.MeasurementUnit; import eu.etaxonomy.cdm.model.description.NaturalLanguageTerm; -import eu.etaxonomy.cdm.model.description.PolytomousKeyNode; import eu.etaxonomy.cdm.model.description.State; import eu.etaxonomy.cdm.model.description.StatisticalMeasure; import eu.etaxonomy.cdm.model.description.TextFormat; +import eu.etaxonomy.cdm.model.location.Country; import eu.etaxonomy.cdm.model.location.NamedArea; import eu.etaxonomy.cdm.model.location.NamedAreaLevel; import eu.etaxonomy.cdm.model.location.NamedAreaType; import eu.etaxonomy.cdm.model.location.ReferenceSystem; -import eu.etaxonomy.cdm.model.location.WaterbodyOrCountry; +import eu.etaxonomy.cdm.model.molecular.Sequence; import eu.etaxonomy.cdm.model.name.HybridRelationshipType; import eu.etaxonomy.cdm.model.name.NameRelationshipType; import eu.etaxonomy.cdm.model.name.NameTypeDesignationStatus; @@ -57,29 +60,33 @@ import eu.etaxonomy.cdm.model.name.NomenclaturalStatusType; import eu.etaxonomy.cdm.model.name.Rank; import eu.etaxonomy.cdm.model.name.SpecimenTypeDesignationStatus; import eu.etaxonomy.cdm.model.occurrence.DerivationEventType; +import eu.etaxonomy.cdm.model.occurrence.DerivedUnit; +import eu.etaxonomy.cdm.model.occurrence.MaterialOrMethodEvent; import eu.etaxonomy.cdm.model.occurrence.PreservationMethod; -import eu.etaxonomy.cdm.model.occurrence.SpecimenOrObservationBase; +import eu.etaxonomy.cdm.model.occurrence.SpecimenOrObservationType; import eu.etaxonomy.cdm.model.reference.Reference; +import eu.etaxonomy.cdm.model.reference.ReferenceType; import eu.etaxonomy.cdm.model.taxon.SynonymRelationshipType; import eu.etaxonomy.cdm.model.taxon.TaxonRelationshipType; - /** * @author a.mueller * @created Jun 06, 2013 */ public class SchemaUpdater_31_33 extends SchemaUpdaterBase { - private static final Logger logger = Logger.getLogger(SchemaUpdater_31_33.class); + private static final Logger logger = Logger + .getLogger(SchemaUpdater_31_33.class); private static final String startSchemaVersion = "3.0.1.0.201104190000"; - private static final String endSchemaVersion = "3.3.0.0.201308010000"; - -// ********************** FACTORY METHOD ******************************************* - - public static SchemaUpdater_31_33 NewInstance(){ + private static final String endSchemaVersion = "3.3.0.0.201309240000"; + + // ********************** FACTORY METHOD + // ******************************************* + + public static SchemaUpdater_31_33 NewInstance() { return new SchemaUpdater_31_33(); } - + /** * @param startSchemaVersion * @param endSchemaVersion @@ -87,914 +94,1949 @@ public class SchemaUpdater_31_33 extends SchemaUpdaterBase { protected SchemaUpdater_31_33() { super(startSchemaVersion, endSchemaVersion); } - - /* (non-Javadoc) - * @see eu.etaxonomy.cdm.database.update.SchemaUpdaterBase#getUpdaterList() - */ + @Override protected List getUpdaterList() { - + String stepName; String tableName; ISchemaUpdaterStep step; String columnName; - - //CHECKS - - //remove SpecimenOrObservationBase_Media #3597 - //TODO check if SpecimenOrObservationBase_Media has data => move to first position, don't run update if data exists - //TODO check if Description -Specimen Relation has M:M data - if (false){ - throw new RuntimeException("Required check for SpecimenOrObservationBase_Media"); - }else{ + + // CHECKS + + // remove SpecimenOrObservationBase_Media #3597 + // TODO check if Description -Specimen Relation has M:M data + if (false) { + throw new RuntimeException( + "Required check for SpecimenOrObservationBase_Media"); + } else { logger.warn("CHECKS for inconsistent data not running !!!!"); } - - + List stepList = new ArrayList(); - - //TODO Does it throw exception if table does not exist? - //Was in Schemaupdater_301_31 which was never used and later deleted (r18331). - //drop TypeDesignationBase_TaxonNameBase //from schemaUpdater 301_31 + + // Was in Schemaupdater_301_31 which was never used and later deleted + // (r18331). + // drop TypeDesignationBase_TaxonNameBase //from schemaUpdater 301_31 stepName = "Drop duplicate TypeDesignation-TaxonName table"; tableName = "TypeDesignationBase_TaxonNameBase"; step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT); stepList.add(step); - - //create original source type column + + // create original source type column stepName = "Create original source type column"; tableName = "OriginalSourceBase"; columnName = "sourceType"; - step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 4, INCLUDE_AUDIT); - ((ColumnAdder)step).setNotNull(true); + step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, + 4, INCLUDE_AUDIT); + ((ColumnAdder) step).setNotNull(true); stepList.add(step); - - //update original source type + + // update original source type updateOriginalSourceType(stepList); - - //create and update elevenation max, remove error column + + // create and update elevenation max, remove error column updateElevationMax(stepList); - - //create TaxonNode tree index + + // create TaxonNode tree index stepName = "Create taxon node tree index"; tableName = "TaxonNode"; columnName = "treeIndex"; - //TODO NOT NULL unclear - step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT); + // TODO NOT NULL unclear //see also columnTypeChanger + step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, + 255, INCLUDE_AUDIT); stepList.add(step); - - //update treeindex for taxon nodes + + // update treeindex for taxon nodes stepName = "Update TaxonNode treeindex"; tableName = "TaxonNode"; String treeIdColumnName = "classification_id"; - step = TreeIndexUpdater.NewInstance(stepName, tableName, treeIdColumnName, columnName, INCLUDE_AUDIT); + step = TreeIndexUpdater.NewInstance(stepName, tableName, + treeIdColumnName, columnName, ! INCLUDE_AUDIT); //update does no yet wok for ANSI SQL (e.g. PosGres / H2 with multiple entries for same id in AUD table) stepList.add(step); - - //create TaxonNode sort index column + + // create TaxonNode sort index column stepName = "Create taxon node sort index column"; tableName = "TaxonNode"; columnName = "sortIndex"; - step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false, null); + step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, + INCLUDE_AUDIT, false, null); stepList.add(step); - - //TODO implement sorted behaviour in model first !! - //update sortindex + + // update sortindex stepName = "Update sort index on TaxonNode children"; tableName = "TaxonNode"; String parentIdColumn = "parent_id"; String sortIndexColumn = "sortIndex"; - SortIndexUpdater updateSortIndex = SortIndexUpdater.NewInstance(stepName, tableName, parentIdColumn, sortIndexColumn, INCLUDE_AUDIT); + SortIndexUpdater updateSortIndex = SortIndexUpdater.NewInstance( + stepName, tableName, parentIdColumn, sortIndexColumn, + INCLUDE_AUDIT); stepList.add(updateSortIndex); - - //create feature node tree index + // Classification root nodes sort index + stepName = "Create classification root node sort index column"; + tableName = "Classification_TaxonNode"; + step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, + INCLUDE_AUDIT, false, null); + stepList.add(step); + + stepName = "Update sort index on classification child nodes"; + parentIdColumn = "Classification_id"; + String idColumn = "rootnodes_id"; + updateSortIndex = SortIndexUpdater.NewInstance(stepName, tableName, + parentIdColumn, sortIndexColumn, idColumn, INCLUDE_AUDIT); + stepList.add(updateSortIndex); + + // create feature node tree index stepName = "Create feature node tree index"; tableName = "FeatureNode"; columnName = "treeIndex"; - //TODO NOT NULL unclear - step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT); + // TODO NOT NULL unclear + step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, + 255, INCLUDE_AUDIT); stepList.add(step); - - //update tree index for feature node + + // update tree index for feature node stepName = "Update FeatureNode treeindex"; tableName = "FeatureNode"; treeIdColumnName = "featuretree_id"; - step = TreeIndexUpdater.NewInstance(stepName, tableName, treeIdColumnName, columnName, INCLUDE_AUDIT); + step = TreeIndexUpdater.NewInstance(stepName, tableName, + treeIdColumnName, columnName, ! INCLUDE_AUDIT); // see comment for TaxonTree stepList.add(step); - - //update introduced: adventitious (casual) label - //#3540 + + // update introduced: adventitious (casual) label + // #3540 stepName = "Update introduced: adventitious (casual) label"; - String query = " UPDATE representation r " + - " SET r.abbreviatedlabel = 'ia' " + - " WHERE r.abbreviatedlabel = 'id' AND r.label = 'introduced: adventitious (casual)' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); + String query = " UPDATE @@Representation@@ " + + " SET abbreviatedlabel = 'ia' " + + " WHERE abbreviatedlabel = 'id' AND label = 'introduced: adventitious (casual)' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing("Representation"); stepList.add(step); - - //termType for DefinedTerms and TermVocabulary, no type must be null + + // termType for DefinedTerms and TermVocabulary, no type must be null stepName = "Create termType column in DefinedTermBase"; tableName = "DefinedTermBase"; columnName = "termType"; - //TODO NOT NULL unclear - step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT); + // TODO NOT NULL unclear + step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, + 255, INCLUDE_AUDIT); stepList.add(step); - + stepName = "Create termType column in TermVocabulary"; tableName = "TermVocabulary"; columnName = "termType"; - //TODO NOT NULL unclear - step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 4, INCLUDE_AUDIT); + // TODO NOT NULL unclear + step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, + 4, INCLUDE_AUDIT); stepList.add(step); - - - //update termType for DefinedTerms, no type must be null + + // update termType for DefinedTerms, no type must be null updateTermTypesForTerms(stepList); - - //update termType for TermVocabulary, no type must be null + + // update termType for TermVocabulary, no type must be null updateTermTypesForVocabularies(stepList); - //update DTYPE of DefinedTerms + // update DTYPE of DefinedTerms updateDtypeOfDefinedTerms(stepList); - //idInVocabulary for DefinedTerms + // idInVocabulary for DefinedTerms stepName = "Create idInVocabulary column in DefinedTermBase"; tableName = "DefinedTermBase"; columnName = "idInVocabulary"; - step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT); + step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, + 255, INCLUDE_AUDIT); stepList.add(step); - - //update idInVocabulary + + // update idInVocabulary updateIdInVocabulary(stepList); - - //rankClass (#3521) + + // rankClass (#3521) stepName = "Create rankClass column in DefinedTermBase"; tableName = "DefinedTermBase"; columnName = "rankClass"; - //TODO NOT NULL unclear - step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT); + // TODO NOT NULL unclear + step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, + 255, INCLUDE_AUDIT); stepList.add(step); - - //update rankClass (#3521) + + // update rankClass (#3521) step = RankClassUpdater.NewInstance(); stepList.add(step); - - //update datatype->CLOB for URIs. (DefinedTerms, TermVocabulary, Reference, Rights, MediaRepresentationPart ) - //#3345, TODO adapt type to <65k - //TODO sequence.sequence has been changed #3360 + + // update datatype->CLOB for URIs. (DefinedTerms, TermVocabulary, + // Reference, Rights, MediaRepresentationPart ) + // #3345, TODO2 adapt type to <65k -> see #3954 + // sequence.sequence has been changed #3360 changeUriType(stepList); - - //update Sicilia -> Sicily - //#3540 + // Annotation.linkbackUri change name #3374 + stepName = "Update url to uri (->clob) for Annotation.linkbackUri"; + columnName = "linkbackUrl"; + String newColumnName = "linkbackUri"; + tableName = "Annotation"; + // TODO check non MySQL and with existing data (probably does not exist) + step = ColumnNameChanger.NewClobInstance(stepName, tableName, + columnName, newColumnName, INCLUDE_AUDIT); + stepList.add(step); + + // update Sicilia -> Sicily + // #3540 stepName = "Update Sicilia -> Sicily"; - query = " UPDATE representation r " + - " SET r.label = 'Sicily', r.text = 'Sicily' " + - " WHERE (r.abbreviatedlabel = 'SIC-SI' OR r.abbreviatedlabel = 'SIC') AND r.label = 'Sicilia' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); + query = " UPDATE @@Representation@@ " + + " SET label = 'Sicily', text = 'Sicily' " + + " WHERE (abbreviatedlabel = 'SIC-SI' OR abbreviatedlabel = 'SIC') AND label = 'Sicilia' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing("Representation"); stepList.add(step); - - //remove homotypical group form type designation base + + // remove homotypical group form type designation base stepName = "Remove column homotypical group in type designation base"; tableName = "TypeDesignationBase"; String oldColumnName = "homotypicalgroup_id"; - step = ColumnRemover.NewInstance(stepName, tableName, oldColumnName, INCLUDE_AUDIT); - stepList.add(step); - - //add publish flag to taxon - stepName = "Add publish flag column to taxon base"; - tableName = "TaxonBase"; - columnName = "publish"; - step = ColumnAdder.NewBooleanInstance(stepName, tableName, columnName, INCLUDE_AUDIT, true); - stepList.add(step); - - //add publish flag to specimen - stepName = "Add publish flag column to SpecimenOrObservationBase"; - tableName = "SpecimenOrObservationBase"; - columnName = "publish"; - step = ColumnAdder.NewBooleanInstance(stepName, tableName, columnName, INCLUDE_AUDIT, true); + step = ColumnRemover.NewInstance(stepName, tableName, oldColumnName, + INCLUDE_AUDIT); stepList.add(step); - - //add columns abbrevTitle, abbrevTitleCache and protectedAbbrevTitleCache to Reference + + // add publish flag #1780 + addPublishFlag(stepList); + + // add columns abbrevTitle, abbrevTitleCache and + // protectedAbbrevTitleCache to Reference stepName = "Add abbrevTitle to Reference"; tableName = "Reference"; columnName = "abbrevTitle"; int length = 255; - step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, length, INCLUDE_AUDIT); + step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, + length, INCLUDE_AUDIT); stepList.add(step); stepName = "Add abbrevTitleCache to Reference"; tableName = "Reference"; columnName = "abbrevTitleCache"; length = 1023; - step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, length, INCLUDE_AUDIT); + step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, + length, INCLUDE_AUDIT); stepList.add(step); - + stepName = "Add protectedAbbrevTitleCache to Reference"; tableName = "Reference"; columnName = "protectedAbbrevTitleCache"; - step = ColumnAdder.NewBooleanInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false); + step = ColumnAdder.NewBooleanInstance(stepName, tableName, columnName, + INCLUDE_AUDIT, false); stepList.add(step); - - //update abbrevTitle, protectedAbbrevTitle and abbrevTitleCache in Reference + + // update abbrevTitle, protectedAbbrevTitle and abbrevTitleCache in + // Reference updateAbbrevTitle(stepList); - - //add doi to reference + + //remove figure #2539 + stepName = "Remove Figure class"; + query = "UPDATE @@Media@@ SET DTYPE = 'Media' WHERE DTYPE = 'Figure'"; + step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, query, "Media", 99); + stepList .add(step); + + // add doi to reference stepName = "Add doi to Reference"; tableName = "Reference"; columnName = "doi"; length = 255; - step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, length, INCLUDE_AUDIT); + step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, + length, INCLUDE_AUDIT); stepList.add(step); - - - //add start number to PolytomousKey + + // add start number to PolytomousKey stepName = "Add start number column to PolytomousKey"; tableName = "PolytomousKey"; columnName = "startNumber"; Integer defaultValue = 1; - step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, defaultValue, true); + step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, + INCLUDE_AUDIT, defaultValue, true); stepList.add(step); - - //add recordBasis to specimenOrObservationBase + + // add recordBasis to specimenOrObservationBase stepName = "Add recordBasis to SpecimenOrObservationBase"; tableName = "SpecimenOrObservationBase"; columnName = "recordBasis"; - length = 4; //TODO needed? - step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, length, INCLUDE_AUDIT); + length = 4; // TODO needed? + // TODO NOT NULL + step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, + length, INCLUDE_AUDIT); + stepList.add(step); + + // update recordBasis + updateRecordBasis(stepList); + + // update specimenOrObservationBase DTYPE with DerivedUnit where + // necessary + stepName = "Update Specimen -> DerivedUnit"; + query = " UPDATE @@SpecimenOrObservationBase@@ " + + " SET DTYPE = 'DerivedUnit' " + + " WHERE DTYPE = 'Specimen' OR DTYPE = 'Fossil' OR DTYPE = 'LivingBeing' OR DTYPE = 'Observation' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing("SpecimenOrObservationBase"); stepList.add(step); - - //update specimenOrObservationBase DTYPE with DerivedUnit where necessary + stepName = "Update Specimen -> DerivedUnit"; - query = " UPDATE SpecimenOrObservationBase sob " + - " SET sob.DTYPE = 'DerivedUnit' " + - " WHERE sob.DTYPE = 'Specimen' OR sob.DTYPE = 'Fossil' OR sob.DTYPE = 'LivingBeing' OR sob.DTYPE = 'Observation' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); + String newClass = "eu.etaxonomy.cdm.model.occurrence.DerivedUnit"; + String[] oldClassPaths = new String[] { + "eu.etaxonomy.cdm.model.occurrence.Specimen", + "eu.etaxonomy.cdm.model.occurrence.Fossil", + "eu.etaxonomy.cdm.model.occurrence.LivingBeing", + "eu.etaxonomy.cdm.model.occurrence.Observation" }; + step = ClassChanger.NewIdentifiableInstance(stepName, tableName, + newClass, oldClassPaths, INCLUDE_AUDIT); stepList.add(step); - //update DTYPE FieldObservation -> FieldUnit #3351 + // update DTYPE FieldObservation -> FieldUnit #3351 stepName = "Update FieldObservation -> FieldUnit"; - query = " UPDATE SpecimenOrObservationBase sob " + - " SET sob.DTYPE = 'FieldUnit' " + - " WHERE sob.DTYPE = 'FieldObservation' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); + query = " UPDATE @@SpecimenOrObservationBase@@ " + + " SET DTYPE = 'FieldUnit' " + + " WHERE DTYPE = 'FieldObservation' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing("SpecimenOrObservationBase"); stepList.add(step); - - //add kindOfUnit to SpecimenOrObservationBase + + stepName = "Update Specimen -> DerivedUnit"; + newClass = "eu.etaxonomy.cdm.model.occurrence.FieldUnit"; + oldClassPaths = new String[] { "eu.etaxonomy.cdm.model.occurrence.FieldObservation" }; + step = ClassChanger.NewIdentifiableInstance(stepName, tableName, + newClass, oldClassPaths, INCLUDE_AUDIT); + stepList.add(step); + + // add kindOfUnit to SpecimenOrObservationBase stepName = "Add kindOfUnit column to SpecimenOrObservationBase"; tableName = "SpecimenOrObservationBase"; columnName = "kindOfUnit_id"; String relatedTable = "DefinedTermBase"; - step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, true, relatedTable); + step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, + INCLUDE_AUDIT, false, relatedTable); stepList.add(step); - //remove citation_id and citation micro-reference columns from Media table #2541 - //FIXME first check if columns are always empty + // remove citation_id and citation micro-reference columns from Media + // table #2541 + // FIXME first check if columns are always empty stepName = "Remove citation column from Media"; tableName = "Media"; columnName = "citation_id"; - step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT); + step = ColumnRemover.NewInstance(stepName, tableName, columnName, + INCLUDE_AUDIT); stepList.add(step); - + stepName = "Remove citation microreference column from Media"; tableName = "Media"; columnName = "citationMicroReference"; - step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT); + step = ColumnRemover.NewInstance(stepName, tableName, columnName, + INCLUDE_AUDIT); stepList.add(step); - - //update length of all title caches and full title cache in names #1592 + + // update length of all title caches and full title cache in names #1592 updateTitleCacheLength(stepList); - - - //rename FK column states_id -> stateData_id in DescriptionElementBase_StateData(+AUD) #2923 + + // rename FK column states_id -> stateData_id in + // DescriptionElementBase_StateData(+AUD) #2923 stepName = "Update states_id to stateData_id in DescriptionElementBase_StateData"; tableName = "DescriptionElementBase_StateData"; oldColumnName = "states_id"; - String newColumnName = "stateData_id"; - step = ColumnNameChanger.NewIntegerInstance(stepName, tableName, oldColumnName, newColumnName, INCLUDE_AUDIT); + newColumnName = "stateData_id"; + step = ColumnNameChanger.NewIntegerInstance(stepName, tableName, + oldColumnName, newColumnName, INCLUDE_AUDIT); stepList.add(step); - - //specimen descriptions #3571 - //add column DescriptionBase.Specimen_ID #3571 + + // specimen descriptions #3571 + // add column DescriptionBase.Specimen_ID #3571 stepName = "Add specimen_id column to DescriptionBase"; tableName = "DescriptionBase"; columnName = "specimen_id"; boolean notNull = false; String referencedTable = "SpecimenOrObservationBase"; - step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, notNull, referencedTable); + step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, + INCLUDE_AUDIT, notNull, referencedTable); stepList.add(step); - - //update DescriptionBase.Specimen_ID data #3571 + + // update DescriptionBase.Specimen_ID data #3571 updateDescriptionSpecimenRelation(stepList); - //remove tables DescriptionBase_SpecimenOrObservationBase(_AUD) #3571 + // remove tables DescriptionBase_SpecimenOrObservationBase(_AUD) #3571 stepName = "Remove table DescriptionBase_SpecimenOrObservationBase"; tableName = "DescriptionBase_SpecimenOrObservationBase"; step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT); stepList.add(step); - - //create table CdmPreferences #3555 - stepName = "Create table 'CdmPreferences'"; - tableName = "CdmPreferences"; - TableCreator stepPref = TableCreator.NewInstance(stepName, tableName, - new String[]{"key_subject", "key_predicate","value"}, //colNames - new String[]{"string_100", "string_200","string_1023",}, // columnTypes - new String[]{null, "DefinedTermBase",null}, //referencedTables - ! INCLUDE_AUDIT, false); + + // change column type for reference type + stepName = "Change column type for Reference.type"; + tableName = "Reference"; + columnName = "refType"; + Integer defaultValueStr = -1; + notNull = true; + int size = 3; + step = ColumnTypeChanger.NewInt2StringInstance(stepName, tableName, + columnName, size, true, defaultValueStr, notNull); + stepList.add(step); + + // update reference type + updateReferenceType(stepList); + + // create table CdmPreference #3555 + stepName = "Create table 'CdmPreference'"; + tableName = "CdmPreference"; + TableCreator stepPref = TableCreator.NewInstance(stepName, tableName, + new String[] { "key_subject", "key_predicate", "value" }, // colNames + new String[] { "string_100", "string_100", "string_1023", }, // columnTypes + new String[] { null, "DefinedTermBase", null }, // referencedTables + !INCLUDE_AUDIT, false); stepPref.setPrimaryKeyParams("key_subject, key_predicate", null); stepList.add(stepPref); - //FIXME length of key >= 1000 - - //TODO fill CdmPreferences with default values - //update RightsTerm to RightsType #1306 + // update RightsTerm to RightsType #1306 stepName = "Update RightsTerm -> RightsType"; - String updateSql = "UPDATE DefinedTermBase SET DTYPE = 'RightsType' WHERE DTYPE = 'RightsTerm'"; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, updateSql); + String updateSql = "UPDATE @@DefinedTermBase@@ SET DTYPE = 'RightsType' WHERE DTYPE = 'RightsTerm'"; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, updateSql, 99) + .setDefaultAuditing("DefinedTermBase"); stepList.add(step); - - //remove table Sequence_GenBankAccession #3552 + + // update Rights table to RightsInfo + updateRights2RightsInfo(stepList); + + // Remove column isDescriptionSeparated from FeatureTree #3678 + stepName = "Remove column isDescriptionSeparated from FeatureTree"; + tableName = "FeatureTree"; + columnName = "descriptionSeparated"; + step = ColumnRemover.NewInstance(stepName, tableName, columnName, + INCLUDE_AUDIT); + stepList.add(step); + + // remove table Sequence_GenBankAccession #3552 stepName = "Remove table Sequence_GenBankAccession"; tableName = "Sequence_GenBankAccession"; step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT); stepList.add(step); - - //remove table GenBankAccession #3552 + + // remove table GenBankAccession #3552 stepName = "Remove table GenBankAccession"; tableName = "GenBankAccession"; step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT); stepList.add(step); - - //remove old sequence columns + + // remove table Sequence_Credit #3360 + stepName = "Remove table Sequence_Credit"; + tableName = "Sequence_Credit"; + step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT); + stepList.add(step); + + // remove table Sequence_Extension #3360 + stepName = "Remove table Sequence_Extension"; + tableName = "Sequence_Extension"; + step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT); + stepList.add(step); + + //remove table Sequence_Media #3360 + stepName = "Remove table Sequence_Media"; + tableName = "Sequence_Media"; + step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT); + stepList.add(step); + + // remove table Sequence_OriginalSourceBase #3360 + stepName = "Remove table Sequence_OriginalSourceBase"; + tableName = "Sequence_OriginalSourceBase"; + step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT); + stepList.add(step); + + // remove table Sequence_OriginalSourceBase #3360 + stepName = "Remove table Sequence_Rights"; + tableName = "Sequence_Rights"; + step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT); + stepList.add(step); + + // remove old sequence columns removeOldSequenceColumns(stepList); - - //add MediaSpecimen column #3614 + + // add MediaSpecimen column #3614 stepName = "Add mediaSpecimen column to SpecimenOrObservationBase"; tableName = "SpecimenOrObservationBase"; columnName = "mediaSpecimen_id"; notNull = false; referencedTable = "Media"; - step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, notNull, referencedTable); + step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, + INCLUDE_AUDIT, notNull, referencedTable); stepList.add(step); - - //remove DescriptionBase_Feature #2202 + + // remove DescriptionBase_Feature #2202 stepName = "Remove table DescriptionBase_Feature"; tableName = "DescriptionBase_Feature"; step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT); stepList.add(step); - - //add timeperiod to columns to description element base #3312 + + // add timeperiod to columns to description element base #3312 addTimeperiodToDescriptionElement(stepList); + // move specimen images + stepName = "Move images from SpecimenOrObservationBase_Media to image gallery"; + step = SpecimenMediaMoverUpdater.NewInstance(); + stepList.add(step); - //TODO add DnaMarker vocabulary and terms #3591 => TermUpdater - - //SpecimenOrObservationBase_Media #3597 + // SpecimenOrObservationBase_Media #3597 stepName = "Remove table SpecimenOrObservationBase_Media"; tableName = "SpecimenOrObservationBase_Media"; step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT); stepList.add(step); - - - //Amplification #3360 + + // all molecular (#3360) and related updates + updateMolecularAndRelated(stepList); + + // update vocabulary representaitons + step = TermVocabularyRepresentationUpdater.NewInstance(); + stepList.add(step); + + return stepList; + } + + private void updateMolecularAndRelated(List stepList) { + String stepName; + String tableName; + ISchemaUpdaterStep step; + String columnName; + int length; + Integer defaultValue; + String referencedTable; + + // Primer #3360 stepName = "Create table 'Primer'"; tableName = "Primer"; - step = TableCreator.NewAnnotatableInstance(stepName, tableName, - new String[]{"label","sequence_id","publishedIn_id"}, //colNames - new String[]{"string_255","int","int"}, // columnTypes - new String[]{null,Sequence.class.getSimpleName(),Reference.class.getSimpleName()}, //referencedTables + step = TableCreator.NewAnnotatableInstance(stepName, tableName, + new String[] { "label", "sequence_id", "publishedIn_id" }, // colNames + new String[] { "string_255", "int", "int" }, // columnTypes + new String[] { null, Sequence.class.getSimpleName(), + Reference.class.getSimpleName() }, // referencedTables INCLUDE_AUDIT); stepList.add(step); - - //MaterialAndMethod #3360 - stepName = "Create table 'MaterialAndMethod'"; - tableName = MaterialAndMethod.class.getSimpleName(); - step = TableCreator.NewAnnotatableInstance(stepName, tableName, - new String[]{"DTYPE", "materialMethodTerm_id","materialMethodText"}, //colNames - new String[]{"string_255", "int","string_1000",}, // columnTypes - new String[]{null, "DefinedTermBase",null}, //referencedTables + + // MaterialOrMethod #3360 + stepName = "Create table 'MaterialOrMethodEvent'"; + tableName = MaterialOrMethodEvent.class.getSimpleName(); + step = TableCreator.NewEventInstance(stepName, tableName, new String[] { + "DTYPE", "strain", "temperature", "definedMaterialOrMethod_id", + "forwardPrimer_id", "reversePrimer_id", "medium_id" }, // colNames + new String[] { "string_255", "string_255", "double", "int", + "int", "int", "int" }, // columnTypes + new String[] { null, null, null, "DefinedTermBase", "Primer", + "Primer", "DefinedTermBase" }, // referencedTables INCLUDE_AUDIT); stepList.add(step); - - //Cloning #3360 - stepName = "Create table 'Cloning'"; - tableName = "Cloning"; - String matMetName = MaterialAndMethod.class.getSimpleName(); - step = TableCreator.NewEventInstance(stepName, tableName, - new String[]{"strain","method_id","forwardPrimer_id","reversePrimer_id"}, //colNames - new String[]{"string_255", "int","int","int"}, // columnTypes - new String[]{null, matMetName,"Primer","Primer"}, //referencedTables + + stepName = "Remove preservation column from SpecimenOrObservationBase"; + // to fully remove all foreign keys, maybe there is a better way to do so + // we don't expect any preservation information to exist in any CDM + // database + tableName = "SpecimenOrObservationBase"; + String oldColumnName = "preservation_id"; + step = ColumnRemover.NewInstance(stepName, tableName, oldColumnName, INCLUDE_AUDIT); stepList.add(step); - - - //Amplification #3360 + + stepName = "Add new preservation column to SpecimenOrObservationBase"; + tableName = "SpecimenOrObservationBase"; + String newColumnName = "preservation_id"; + boolean notNull = false; + step = ColumnAdder.NewIntegerInstance(stepName, tableName, + newColumnName, INCLUDE_AUDIT, notNull, "MaterialOrMethodEvent"); + stepList.add(step); + + // Amplification #3360 stepName = "Create table 'Amplification'"; tableName = "Amplification"; - step = TableCreator.NewEventInstance(stepName, tableName, - new String[]{"dnaSample_id","dnaMarker_id","forwardPrimer_id","reversePrimer_id","purification_id","cloning_id", "gelPhoto_id", "successful","successText","ladderUsed","electrophoresisVoltage","gelRunningTime","gelConcentration"}, //colNames - new String[]{"int","int","int","int","int","int","int", "bit","string_255","string_255","double","double","double"}, // columnTypes - new String[]{"SpecimenOrObservationBase","DefinedTermBase","Primer","Primer",matMetName, matMetName, "Media", null, null, null, null, null, null}, //referencedTables + step = TableCreator.NewEventInstance(stepName, tableName, + new String[] { "dnaSample_id", "dnaMarker_id", + "forwardPrimer_id", "reversePrimer_id", + "purification_id", "cloning_id", "gelPhoto_id", + "successful", "successText", "ladderUsed", + "electrophoresisVoltage", "gelRunningTime", + "gelConcentration" }, // colNames + new String[] { "int", "int", "int", "int", "int", "int", "int", + "bit", "string_255", "string_255", "double", "double", + "double" }, // columnTypes + new String[] { "SpecimenOrObservationBase", "DefinedTermBase", + "Primer", "Primer", "MaterialOrMethodEvent", + "MaterialOrMethodEvent", "Media", null, null, null, + null, null, null }, // referencedTables INCLUDE_AUDIT); stepList.add(step); - - //SingleRead #3360 + + // SingleRead #3360 stepName = "Create table 'SingleRead'"; tableName = "SingleRead"; - step = TableCreator.NewEventInstance(stepName, tableName, - new String[]{"amplification_id","materialAndMethod_id","primer_id","pherogram_id","direction","sequence_length"}, //colNames - new String[]{"int","int","int","int","int","int"}, // columnTypes - new String[]{"Amplification",matMetName, "Primer","Media", null, null}, //referencedTables + step = TableCreator.NewEventInstance(stepName, tableName, new String[] { + "amplification_id", "materialOrMethod_id", "primer_id", + "pherogram_id", "direction", "sequence_length" }, // colNames + new String[] { "int", "int", "int", "int", "string_3", "int" }, // columnTypes + new String[] { "Amplification", "MaterialOrMethodEvent", + "Primer", "Media", null, null }, // referencedTables INCLUDE_AUDIT); - //TODO length sequence_string stepList.add(step); - - //sequence - consensussequence_string #3360 - stepName= "Add sequence_string to single read"; + + // sequence - consensussequence_string #3360 + stepName = "Add sequence_string to single read"; columnName = "sequence_string"; - step = ColumnAdder.NewClobInstance(stepName, tableName, columnName, INCLUDE_AUDIT); + step = ColumnAdder.NewClobInstance(stepName, tableName, columnName, + INCLUDE_AUDIT); stepList.add(step); - - //amplification - single reads #3360 - stepName= "Add single reads to amplification"; - String firstTable = "Amplification"; - String secondTable = "SingleRead"; - step = MnTableCreator.NewMnInstance(stepName, firstTable, null, secondTable, null, SchemaUpdaterBase.INCLUDE_AUDIT, false, true); + + // amplification - single reads #3360 + stepName = "Add single reads to amplification"; + String firstTable = "Amplification"; + String secondTable = "SingleRead"; + step = MnTableCreator + .NewMnInstance(stepName, firstTable, null, secondTable, null, + SchemaUpdaterBase.INCLUDE_AUDIT, false, true); stepList.add(step); - - //sequence - single reads #3360 - stepName= "Add single reads to sequence"; - firstTable = "Sequence"; - secondTable = "SingleRead"; - step = MnTableCreator.NewMnInstance(stepName, firstTable, null, secondTable, null, SchemaUpdaterBase.INCLUDE_AUDIT, false, true); + + // sequence - single reads #3360 + stepName = "Add single reads to sequence"; + firstTable = "Sequence"; + secondTable = "SingleRead"; + step = MnTableCreator + .NewMnInstance(stepName, firstTable, null, secondTable, null, + SchemaUpdaterBase.INCLUDE_AUDIT, false, true); stepList.add(step); - - //sequence - barcode #3360 - stepName= "Add barcodesequencepart_length to sequence"; + + // sequence - barcode #3360 + stepName = "Add barcodesequencepart_length to sequence"; tableName = "Sequence"; columnName = "barcodeSequencePart_length"; defaultValue = null; - step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, defaultValue, false); + step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, + INCLUDE_AUDIT, defaultValue, false); stepList.add(step); - //sequence - barcode #3360 - stepName= "Add barcodesequencepart_string to sequence"; + // sequence - barcode #3360 + stepName = "Add barcodesequencepart_string to sequence"; tableName = "Sequence"; columnName = "barcodeSequencePart_string"; - step = ColumnAdder.NewClobInstance(stepName, tableName, columnName, INCLUDE_AUDIT); + step = ColumnAdder.NewClobInstance(stepName, tableName, columnName, + INCLUDE_AUDIT); stepList.add(step); - - //sequence - consensussequence_length #3360 - stepName= "Add consensusSequence_length to sequence"; + + // sequence - consensussequence_length #3360 + stepName = "Add consensusSequence_length to sequence"; tableName = "Sequence"; columnName = "consensusSequence_length"; defaultValue = null; - step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, defaultValue, false); + step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, + INCLUDE_AUDIT, defaultValue, false); stepList.add(step); - //sequence - consensussequence_string #3360 - stepName= "Add consensusSequence_string to sequence"; + // sequence - consensussequence_string #3360 + stepName = "Add consensusSequence_string to sequence"; tableName = "Sequence"; columnName = "consensusSequence_string"; - step = ColumnAdder.NewClobInstance(stepName, tableName, columnName, INCLUDE_AUDIT); + step = ColumnAdder.NewClobInstance(stepName, tableName, columnName, + INCLUDE_AUDIT); stepList.add(step); - - //sequence - contigFile #3360 - stepName= "Add contigFile to sequence"; + + // sequence - contigFile #3360 + stepName = "Add contigFile to sequence"; tableName = "Sequence"; columnName = "contigFile_id"; referencedTable = "Media"; - step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false, referencedTable); + step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, + INCLUDE_AUDIT, false, referencedTable); stepList.add(step); - - //sequence - boldprocessid #3360 - stepName= "Add boldprocessId to sequence"; + + // sequence - boldprocessid #3360 + stepName = "Add boldprocessId to sequence"; tableName = "Sequence"; columnName = "boldProcessId"; length = 20; - step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, length, INCLUDE_AUDIT); + step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, + length, INCLUDE_AUDIT); stepList.add(step); - //sequence - boldprocessid #3360 - stepName= "Add geneticAccessionNumber to sequence"; + // sequence - boldprocessid #3360 + stepName = "Add geneticAccessionNumber to sequence"; tableName = "Sequence"; columnName = "geneticAccessionNumber"; length = 20; - step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, length, INCLUDE_AUDIT); + step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, + length, INCLUDE_AUDIT); stepList.add(step); - - //sequence - haplotype #3360 - stepName= "Add haplotype to sequence"; + + // sequence - haplotype #3360 + stepName = "Add haplotype to sequence"; tableName = "Sequence"; columnName = "haplotype"; length = 100; - step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, length, INCLUDE_AUDIT); + step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, + length, INCLUDE_AUDIT); stepList.add(step); - //sequence - isBarcode #3360 - stepName= "Add isBarcode to sequence"; + // sequence - isBarcode #3360 + stepName = "Add isBarcode to sequence"; tableName = "Sequence"; columnName = "isBarcode"; - step = ColumnAdder.NewBooleanInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false); + step = ColumnAdder.NewBooleanInstance(stepName, tableName, columnName, + INCLUDE_AUDIT, false); stepList.add(step); - - //sequence - dnaMarker #3360 - stepName= "Add dnaMarker to sequence"; + + // sequence - dnaMarker #3360 + stepName = "Add dnaMarker to sequence"; tableName = "Sequence"; columnName = "dnaMarker_id"; referencedTable = "DefinedTermBase"; - step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false, referencedTable); + step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, + INCLUDE_AUDIT, false, referencedTable); stepList.add(step); - - //sequence - dnaSample #3360 - stepName= "Add dnaSample to sequence"; + + // sequence - dnaSample #3360 + stepName = "Add dnaSample to sequence"; tableName = "Sequence"; columnName = "dnaSample_id"; referencedTable = "SpecimenOrObservationBase"; - step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false, referencedTable); + step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, + INCLUDE_AUDIT, false, referencedTable); stepList.add(step); - - return stepList; } - //update length of all title caches and full title cache in names - //TODO test for H2, Postgres, SqlServer - //https://dev.e-taxonomy.eu/trac/ticket/1592 - private void updateTitleCacheLength(List stepList) { + private void addPublishFlag(List stepList) { String stepName; String tableName; ISchemaUpdaterStep step; String columnName; - int size = 800; - - stepName = "Change length of TaxonNameBase fullTitleCache"; - tableName = "TaxonNameBase"; - columnName = "fullTitleCache"; - step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT); - stepList.add(step); + String query; - stepName = "Change length of TaxonNameBase title cache"; - tableName = "TaxonNameBase"; - columnName = "titleCache"; - step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT); + // TaxonBase + + // add publish flag to taxon + stepName = "Add publish flag column to taxon base"; + tableName = "TaxonBase"; + columnName = "publish"; + step = ColumnAdder.NewBooleanInstance(stepName, tableName, columnName, + INCLUDE_AUDIT, true); + stepList.add(step); + + // update publish with existing publish false markers + stepName = "update TaxonBase publish if publish false markers exist"; + query = " UPDATE @@TaxonBase@@ " + + " SET publish = @FALSE@ " + + " WHERE id IN ( " + + " SELECT DISTINCT MN.TaxonBase_id " + + " FROM @@Marker@@ m INNER JOIN @@TaxonBase_Marker@@ MN ON MN.markers_id = m.id " + + " INNER JOIN @@DefinedTermBase@@ markerType ON m.markertype_id = markerType.id " + + " WHERE m.flag = @FALSE@ AND markerType.uuid = '0522c2b3-b21c-400c-80fc-a251c3501dbc' " + + ")"; + step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, query, "TaxonBase", 99); + stepList.add(step); + + // remove publish marker MN table + stepName = "Remove existing TaxonBase publish markers MN"; + query = " DELETE " + + " FROM @@TaxonBase_Marker@@ " + + " WHERE markers_id IN ( " + + " SELECT m.id " + + " FROM @@Marker@@ m INNER JOIN @@DefinedTermBase@@ mType ON m.markertype_id = mType.id " + + " WHERE mType.uuid = '0522c2b3-b21c-400c-80fc-a251c3501dbc' " + + ")"; + step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, query, "TaxonBase_Marker", 99); + stepList.add(step); + + // update publish with existing publish false markers + stepName = "Remove existing TaxonBase publish markers"; + query = " DELETE " + + " FROM @@Marker@@ " + + " WHERE id NOT IN " + + " (SELECT MN.markers_id FROM @@TaxonBase_Marker@@ MN) " + + " AND (markedObj_type = 'eu.etaxonomy.cdm.model.taxon.Synonym' OR markedObj_type = 'eu.etaxonomy.cdm.model.taxon.Taxon') " + + " AND markertype_id IN ( " + + "SELECT id FROM @@DefinedTermBase@@ WHERE uuid = '0522c2b3-b21c-400c-80fc-a251c3501dbc' " + + ")"; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99); // AUD does not have markedObj_type + stepList.add(step); + + // SpecimenOrObservationBase + + // add publish flag to specimen + stepName = "Add publish flag column to SpecimenOrObservationBase"; + tableName = "SpecimenOrObservationBase"; + columnName = "publish"; + step = ColumnAdder.NewBooleanInstance(stepName, tableName, columnName, + INCLUDE_AUDIT, true); + stepList.add(step); + + // update publish with existing publish false markers + stepName = "update SpecimenOrObservationBase publish if publish false markers exist"; + query = " UPDATE @@SpecimenOrObservationBase@@ " + + " SET publish = @FALSE@ " + + " WHERE id IN ( " + + " SELECT DISTINCT MN.SpecimenOrObservationBase_id " + + " FROM @@Marker@@ m INNER JOIN @@SpecimenOrObservationBase_Marker@@ MN ON MN.markers_id = m.id " + + " INNER JOIN @@DefinedTermBase@@ markerType ON m.markertype_id = markerType.id " + + " WHERE m.flag = @FALSE@ AND markerType.uuid = '0522c2b3-b21c-400c-80fc-a251c3501dbc' " + + ")"; + step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, query, "SpecimenOrObservationBase", 99); + stepList.add(step); + + // remove publish marker MN table + stepName = "Remove existing SpecimenOrObservationBase publish markers MN"; + query = " DELETE " + + " FROM @@SpecimenOrObservationBase_Marker@@ " + + " WHERE markers_id IN ( " + + " SELECT m.id " + + " FROM @@Marker@@ m INNER JOIN @@DefinedTermBase@@ mType ON m.markertype_id = mType.id " + + " WHERE mType.uuid = '0522c2b3-b21c-400c-80fc-a251c3501dbc' " + + ")"; + step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, query, "SpecimenOrObservationBase_Marker", 99); + stepList.add(step); + + // update publish with existing publish false markers + stepName = "Remove existing SpecimenOrObservationBase publish markers"; + query = " DELETE " + + " FROM @@Marker@@ " + + " WHERE id NOT IN " + + " (SELECT MN.markers_id FROM @@SpecimenOrObservationBase_Marker@@ MN) " + + " AND (markedObj_type = 'eu.etaxonomy.cdm.model.occurrence.DerivedUnit' " + + "OR markedObj_type = 'eu.etaxonomy.cdm.model.occurrence.FieldObservation' " + + "OR markedObj_type = 'eu.etaxonomy.cdm.model.occurrence.FieldUnit' " + + "OR markedObj_type = 'eu.etaxonomy.cdm.model.occurrence.Specimen' " + + "OR markedObj_type = 'eu.etaxonomy.cdm.model.occurrence.Fossil' " + + "OR markedObj_type = 'eu.etaxonomy.cdm.model.occurrence.LivingBeing' " + + "OR markedObj_type = 'eu.etaxonomy.cdm.model.occurrence.Observation' " + + "OR markedObj_type = 'eu.etaxonomy.cdm.model.occurrence.MediaSpecimen' " + + "OR markedObj_type = 'eu.etaxonomy.cdm.model.occurrence.TissueSample' " + + "OR markedObj_type = 'eu.etaxonomy.cdm.model.molecular.DnaSample') " + + " AND markertype_id IN ( " + + "SELECT id FROM @@DefinedTermBase@@ WHERE uuid = '0522c2b3-b21c-400c-80fc-a251c3501dbc' " + + ")"; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99); + stepList.add(step); + + // remove all audited markers if no current markers exist + // this may remove more audited markers then expected but we do accept + // this here + stepName = "Remove all audited markers if no current markers exist"; + query = " DELETE " + + " FROM @@Marker_AUD@@ " + + " WHERE id NOT IN (SELECT id FROM @@Marker@@ ) " + + " AND markertype_id IN ( " + + "SELECT id FROM @@DefinedTermBase@@ WHERE uuid = '0522c2b3-b21c-400c-80fc-a251c3501dbc' " + + ")"; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99); stepList.add(step); - - stepName = "Change length of TaxonBase title cache"; - tableName = "TaxonNameBase"; - columnName = "titleCache"; - step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT); + + } + + private void updateRights2RightsInfo(List stepList) { + // #2945 + String stepName = "Update Rights to RightsInfo"; + String tableName = "Rights"; + String newTableName = "RightsInfo"; + ISchemaUpdaterStep step = TableNameChanger.NewInstance(stepName, + tableName, newTableName, INCLUDE_AUDIT); stepList.add(step); - stepName = "Change length of Classification title cache"; - tableName = "Classification"; - columnName = "titleCache"; - step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT); + stepName = "Update AgentBase_Rights to RightsInfo"; + tableName = "AgentBase_Rights"; + newTableName = "AgentBase_RightsInfo"; + step = TableNameChanger.NewInstance(stepName, tableName, newTableName, + INCLUDE_AUDIT); stepList.add(step); - stepName = "Change length of DescriptionBase title cache"; - tableName = "DescriptionBase"; - columnName = "titleCache"; - step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT); + stepName = "Update Rights_Annotation to RightsInfo"; + tableName = "Rights_Annotation"; + newTableName = "RightsInfo_Annotation"; + step = TableNameChanger.NewInstance(stepName, tableName, newTableName, + INCLUDE_AUDIT); stepList.add(step); - stepName = "Change length of FeatureTree title cache"; - tableName = "FeatureTree"; - columnName = "titleCache"; - step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT); + stepName = "Update Rights_id column in RightsInfo_Annotation"; + tableName = "RightsInfo_Annotation"; + String columnName = "Rights_Id"; + String newColumnName = "RightsInfo_id"; + step = ColumnNameChanger.NewIntegerInstance(stepName, tableName, + columnName, newColumnName, INCLUDE_AUDIT); stepList.add(step); - stepName = "Change length of Collection title cache"; - tableName = "Collection"; - columnName = "titleCache"; - step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT); + stepName = "Update Rights_Marker to RightsInfo"; + tableName = "Rights_Marker"; + newTableName = "RightsInfo_Marker"; + step = TableNameChanger.NewInstance(stepName, tableName, newTableName, + INCLUDE_AUDIT); stepList.add(step); - stepName = "Change length of Reference title cache"; - tableName = "Reference"; - columnName = "titleCache"; - step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT); + stepName = "Update Rights_id column in RightsInfo_Marker"; + tableName = "RightsInfo_Marker"; + columnName = "Rights_Id"; + newColumnName = "RightsInfo_id"; + step = ColumnNameChanger.NewIntegerInstance(stepName, tableName, + columnName, newColumnName, INCLUDE_AUDIT); stepList.add(step); - stepName = "Change length of Media title cache"; - tableName = "Media"; - columnName = "titleCache"; - step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT); + stepName = "Update Classification_Rights to RightsInfo"; + tableName = "Classification_Rights"; + newTableName = "Classification_RightsInfo"; + step = TableNameChanger.NewInstance(stepName, tableName, newTableName, + INCLUDE_AUDIT); stepList.add(step); - stepName = "Change length of PolytomousKey title cache"; - tableName = "PolytomousKey"; - columnName = "titleCache"; - step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT); + stepName = "Update Collection_Rights to RightsInfo"; + tableName = "Collection_Rights"; + newTableName = "Collection_RightsInfo"; + step = TableNameChanger.NewInstance(stepName, tableName, newTableName, + INCLUDE_AUDIT); stepList.add(step); - stepName = "Change length of SpecimenOrObservationBase title cache"; - tableName = "SpecimenOrObservationBase"; - columnName = "titleCache"; - step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT); + stepName = "Update DefinedTermBase_Rights to RightsInfo"; + tableName = "DefinedTermBase_Rights"; + newTableName = "DefinedTermBase_RightsInfo"; + step = TableNameChanger.NewInstance(stepName, tableName, newTableName, + INCLUDE_AUDIT); + stepList.add(step); + + stepName = "Update DescriptionBase_Rights to RightsInfo"; + tableName = "DescriptionBase_Rights"; + newTableName = "DescriptionBase_RightsInfo"; + step = TableNameChanger.NewInstance(stepName, tableName, newTableName, + INCLUDE_AUDIT); + stepList.add(step); + + stepName = "Update FeatureTree_Rights to RightsInfo"; + tableName = "FeatureTree_Rights"; + newTableName = "FeatureTree_RightsInfo"; + step = TableNameChanger.NewInstance(stepName, tableName, newTableName, + INCLUDE_AUDIT); + stepList.add(step); + + stepName = "Update Media_Rights to RightsInfo"; + tableName = "Media_Rights"; + newTableName = "Media_RightsInfo"; + step = TableNameChanger.NewInstance(stepName, tableName, newTableName, + INCLUDE_AUDIT); + stepList.add(step); + + stepName = "Update PolytomousKey_Rights to RightsInfo"; + tableName = "PolytomousKey_Rights"; + newTableName = "PolytomousKey_RightsInfo"; + step = TableNameChanger.NewInstance(stepName, tableName, newTableName, + INCLUDE_AUDIT); + stepList.add(step); + + stepName = "Update Reference_Rights to RightsInfo"; + tableName = "Reference_Rights"; + newTableName = "Reference_RightsInfo"; + step = TableNameChanger.NewInstance(stepName, tableName, newTableName, + INCLUDE_AUDIT); + stepList.add(step); + + stepName = "Update SpecimenOrObservationBase_Rights to RightsInfo"; + tableName = "SpecimenOrObservationBase_Rights"; + newTableName = "SpecimenOrObservationBase_RightsInfo"; + step = TableNameChanger.NewInstance(stepName, tableName, newTableName, + INCLUDE_AUDIT); + stepList.add(step); + + stepName = "Update TaxonBase_Rights to RightsInfo"; + tableName = "TaxonBase_Rights"; + newTableName = "TaxonBase_RightsInfo"; + step = TableNameChanger.NewInstance(stepName, tableName, newTableName, + INCLUDE_AUDIT); + stepList.add(step); + + stepName = "Update TaxonNameBase_Rights to RightsInfo"; + tableName = "TaxonNameBase_Rights"; + newTableName = "TaxonNameBase_RightsInfo"; + step = TableNameChanger.NewInstance(stepName, tableName, newTableName, + INCLUDE_AUDIT); + stepList.add(step); + + stepName = "Update TermVocabulary_Rights to RightsInfo"; + tableName = "TermVocabulary_Rights"; + newTableName = "TermVocabulary_RightsInfo"; + step = TableNameChanger.NewInstance(stepName, tableName, newTableName, + INCLUDE_AUDIT); + stepList.add(step); + } + + private void updateReferenceType(List stepList) { + + String baseQuery = " UPDATE @@Reference@@ " + " SET refType = '%s' " + + " WHERE refType = '%s' "; + Integer index = 0; + String tableName = "Reference"; + + // 0-Article + String stepName = "Update reference refType for Article"; + String query = String.format(baseQuery, ReferenceType.Article.getKey(), + String.valueOf(index++)); + ISchemaUpdaterStep step = SimpleSchemaUpdaterStep + .NewNonAuditedInstance(stepName, query, 99).setDefaultAuditing(tableName); + stepList.add(step); + + // 1-Book + stepName = "Update reference refType for Book"; + query = String.format(baseQuery, ReferenceType.Book.getKey(), + String.valueOf(index++)); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // 2-Book Section + stepName = "Update reference refType for Book Section"; + query = String.format(baseQuery, ReferenceType.BookSection.getKey(), + String.valueOf(index++)); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // 3-CD / DVD + stepName = "Update reference refType for CD"; + query = String.format(baseQuery, ReferenceType.CdDvd.getKey(), + String.valueOf(index++)); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // 4-Database + stepName = "Update reference refType for Database"; + query = String.format(baseQuery, ReferenceType.Database.getKey(), + String.valueOf(index++)); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // 5-Generic + stepName = "Update reference refType for Generic"; + query = String.format(baseQuery, ReferenceType.Generic.getKey(), + String.valueOf(index++)); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // 6-InProceedings + stepName = "Update reference refType for InProceedings"; + query = String.format(baseQuery, ReferenceType.InProceedings.getKey(), + String.valueOf(index++)); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // 7-Journal + stepName = "Update reference refType for Journal"; + query = String.format(baseQuery, ReferenceType.Journal.getKey(), + String.valueOf(index++)); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // 8-Map + stepName = "Update reference refType for Map"; + query = String.format(baseQuery, ReferenceType.Map.getKey(), + String.valueOf(index++)); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // 9-Patent + stepName = "Update reference refType for Patent"; + query = String.format(baseQuery, ReferenceType.Patent.getKey(), + String.valueOf(index++)); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // 10-Personal Communication + stepName = "Update reference refType for Personal Communication"; + query = String.format(baseQuery, + ReferenceType.PersonalCommunication.getKey(), + String.valueOf(index++)); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // 11-PrintSeries + stepName = "Update reference refType for PrintSeries"; + query = String.format(baseQuery, ReferenceType.PrintSeries.getKey(), + String.valueOf(index++)); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // 12-Proceedings + stepName = "Update reference refType for Proceedings"; + query = String.format(baseQuery, ReferenceType.Proceedings.getKey(), + String.valueOf(index++)); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + ; + stepList.add(step); + + // 13-Report + stepName = "Update reference refType for Report"; + query = String.format(baseQuery, ReferenceType.Report.getKey(), + String.valueOf(index++)); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + ; + stepList.add(step); + + // 14-Thesis + stepName = "Update reference refType for Thesis"; + query = String.format(baseQuery, ReferenceType.Thesis.getKey(), + String.valueOf(index++)); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + ; + stepList.add(step); + + // 15-WebPage + stepName = "Update reference refType for WebPage"; + query = String.format(baseQuery, ReferenceType.WebPage.getKey(), + String.valueOf(index++)); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + ; + stepList.add(step); + } + + private void updateRecordBasis(List stepList) { + String stepName = "Update recordBasis for SpecimenOrObservationBase"; + String tableName = "@@SpecimenOrObservationBase@@"; + + // Field Unit + String query = " UPDATE " + tableName + " SET recordBasis = '" + + SpecimenOrObservationType.FieldUnit.getKey() + "' " + + " WHERE DTYPE = 'FieldUnit' OR DTYPE = 'FieldObservation'"; + ISchemaUpdaterStep step = SimpleSchemaUpdaterStep + .NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // DerivedUnit + query = " UPDATE " + tableName + " SET recordBasis = '" + + SpecimenOrObservationType.DerivedUnit.getKey() + "' " + + " WHERE DTYPE = '" + DerivedUnit.class.getSimpleName() + "'"; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // Living Being + query = " UPDATE " + tableName + " SET recordBasis = '" + + SpecimenOrObservationType.LivingSpecimen.getKey() + "' " + + " WHERE DTYPE = 'LivingBeing'"; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // Observation + query = " UPDATE " + tableName + " SET recordBasis = '" + + SpecimenOrObservationType.Observation.getKey() + "' " + + " WHERE DTYPE = 'Observation'"; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // Preserved Specimen + query = " UPDATE " + tableName + " SET recordBasis = '" + + SpecimenOrObservationType.PreservedSpecimen.getKey() + "' " + + " WHERE DTYPE = 'Specimen'"; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // Fossil + query = " UPDATE " + tableName + " SET recordBasis = '" + + SpecimenOrObservationType.Fossil.getKey() + "' " + + " WHERE DTYPE = 'Fossil'"; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // DnaSample + query = " UPDATE " + tableName + " SET recordBasis = '" + + SpecimenOrObservationType.DnaSample.getKey() + "' " + + " WHERE DTYPE = 'DnaSample'"; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // Unknown as default (if not yet handled before) + query = " UPDATE " + tableName + " SET recordBasis = '" + + SpecimenOrObservationType.Unknown.getKey() + "' " + + " WHERE recordBasis IS NULL "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + } + + // update length of all title caches and full title cache in names + // TODO test for H2, Postgres, SqlServer + // https://dev.e-taxonomy.eu/trac/ticket/1592 + private void updateTitleCacheLength(List stepList) { + String stepName; + String tableName; + ISchemaUpdaterStep step; + String columnName; + int size = 800; + + stepName = "Change length of TaxonNameBase fullTitleCache"; + tableName = "TaxonNameBase"; + columnName = "fullTitleCache"; + step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, + columnName, size, INCLUDE_AUDIT); + stepList.add(step); + + stepName = "Change length of TaxonNameBase title cache"; + tableName = "TaxonNameBase"; + columnName = "titleCache"; + step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, + columnName, size, INCLUDE_AUDIT); + stepList.add(step); + + stepName = "Change length of TaxonBase title cache"; + tableName = "TaxonBase"; + columnName = "titleCache"; + step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, + columnName, size, INCLUDE_AUDIT); + stepList.add(step); + + stepName = "Change length of Classification title cache"; + tableName = "Classification"; + columnName = "titleCache"; + step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, + columnName, size, INCLUDE_AUDIT); + stepList.add(step); + + stepName = "Change length of DescriptionBase title cache"; + tableName = "DescriptionBase"; + columnName = "titleCache"; + step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, + columnName, size, INCLUDE_AUDIT); + stepList.add(step); + + stepName = "Change length of FeatureTree title cache"; + tableName = "FeatureTree"; + columnName = "titleCache"; + step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, + columnName, size, INCLUDE_AUDIT); + stepList.add(step); + + stepName = "Change length of Collection title cache"; + tableName = "Collection"; + columnName = "titleCache"; + step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, + columnName, size, INCLUDE_AUDIT); + stepList.add(step); + + stepName = "Change length of Reference title cache"; + tableName = "Reference"; + columnName = "titleCache"; + step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, + columnName, size, INCLUDE_AUDIT); + stepList.add(step); + + stepName = "Change length of Media title cache"; + tableName = "Media"; + columnName = "titleCache"; + step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, + columnName, size, INCLUDE_AUDIT); + stepList.add(step); + + stepName = "Change length of PolytomousKey title cache"; + tableName = "PolytomousKey"; + columnName = "titleCache"; + step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, + columnName, size, INCLUDE_AUDIT); + stepList.add(step); + + stepName = "Change length of SpecimenOrObservationBase title cache"; + tableName = "SpecimenOrObservationBase"; + columnName = "titleCache"; + step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, + columnName, size, INCLUDE_AUDIT); stepList.add(step); stepName = "Change length of DefinedTermBase title cache"; tableName = "DefinedTermBase"; columnName = "titleCache"; - step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT); + step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, + columnName, size, INCLUDE_AUDIT); stepList.add(step); - + stepName = "Change length of TermVocabulary title cache"; tableName = "TermVocabulary"; columnName = "titleCache"; - step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, columnName, size , INCLUDE_AUDIT); + step = ColumnTypeChanger.NewStringSizeInstance(stepName, tableName, + columnName, size, INCLUDE_AUDIT); stepList.add(step); - } - + } - private void updateDescriptionSpecimenRelation(List stepList) { - //TODO warn if multiple entries for 1 description exists - String sqlCount = " SELECT count(*) as n " + - " FROM DescriptionBase_SpecimenOrObservationBase MN " + - " GROUP BY MN.descriptions_id " + - " HAVING count(*) > 1 " + - " ORDER BY MN.descriptions_id, MN.describedspecimenorobservations_id "; - - //TODO ... and log the concrete records -// FROM DescriptionBase_SpecimenOrObservationBase ds -// WHERE ds.descriptions_id IN ( -// SELECT MN.descriptions_id -// FROM DescriptionBase_SpecimenOrObservationBase MN -// GROUP BY MN.descriptions_id -// HAVING count(*) > 1 -// ) -// ORDER BY descriptions_id, describedspecimenorobservations_id - - //TODO test for H2, Postgresql AND SQLServer (later will need TOP 1) - String stepName = "update Description - Specimen relation data "; - String sql = " UPDATE DescriptionBase db " + - " SET db.specimen_id = " + - " (SELECT MN.describedspecimenorobservations_id " + - " FROM DescriptionBase_SpecimenOrObservationBase MN " + - " WHERE MN.descriptions_id = db.id " + - " LIMIT 1 " + - ")"; - ISchemaUpdaterStep step = SimpleSchemaUpdaterStep.NewInstance(stepName, sql); + private void updateDescriptionSpecimenRelation( + List stepList) { + // TODO warn if multiple entries for 1 description exists -> won't do, as this is currently not expected + String sqlCount = " SELECT count(*) as n " + + " FROM DescriptionBase_SpecimenOrObservationBase MN " + + " GROUP BY MN.descriptions_id " + + " HAVING count(*) > 1 " + + " ORDER BY MN.descriptions_id, MN.describedspecimenorobservations_id "; + + // TODO ... and log the concrete records + // FROM DescriptionBase_SpecimenOrObservationBase ds + // WHERE ds.descriptions_id IN ( + // SELECT MN.descriptions_id + // FROM DescriptionBase_SpecimenOrObservationBase MN + // GROUP BY MN.descriptions_id + // HAVING count(*) > 1 + // ) + // ORDER BY descriptions_id, describedspecimenorobservations_id + + // TODO test for H2, Postgresql AND SQLServer (later will need TOP 1) + String stepName = "UPDATE Description - Specimen relation data "; + String sql = " UPDATE @@DescriptionBase@@ " + " SET specimen_id = " + + " (SELECT MN.describedspecimenorobservations_id " + + " FROM @@DescriptionBase_SpecimenOrObservationBase@@ MN " + + " WHERE MN.descriptions_id = @@DescriptionBase@@.id " + " LIMIT 1 " + ")"; + ISchemaUpdaterStep step = SimpleSchemaUpdaterStep + .NewNonAuditedInstance(stepName, sql, 99); stepList.add(step); - + } private void updateAbbrevTitle(List stepList) { + String tableName = "Reference"; String stepName = "Update abbrevTitleCache for protected title caches with title"; - String query = " UPDATE Reference r " + - " SET r.abbrevTitle = r.title, r.abbrevTitleCache = r.titleCache, r.protectedAbbrevTitleCache = r.protectedTitleCache"; -// + " WHERE r.title IS NOT NULL AND r.protectedTitleCache = 1 "; - ISchemaUpdaterStep step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - -// stepName = "Update abbrevTitleCache for protected title caches with no title"; -// query = " UPDATE Reference r " + -// " SET r.abbrevTitleCache = r.titleCache, r.protectedAbbrevTitleCache = r.protectedTitleCache" + -// " WHERE r.title IS NULL AND r.protectedTitleCache = 1 "; -// step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); -// stepList.add(step); - -// stepName = "Update abbrevTitleCache for protected title caches with title"; -// query = " UPDATE Reference r " + -// " SET r.abbrevTitle = r.title, r.abbrevTitleCache = r.titleCache, r.protectedAbbrevTitleCache = r.protectedTitleCache" + -// " WHERE r.title IS NOT NULL AND r.protectedTitleCache = 0 "; -// step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); -// stepList.add(step); - + String query = " UPDATE @@Reference@@ " + + " SET abbrevTitle = left(title, 255), abbrevTitleCache = titleCache, protectedAbbrevTitleCache = protectedTitleCache"; + // + " WHERE r.title IS NOT NULL AND r.protectedTitleCache = 1 "; + ISchemaUpdaterStep step = SimpleSchemaUpdaterStep + .NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // stepName = + // "Update abbrevTitleCache for protected title caches with no title"; + // query = " UPDATE Reference r " + + // " SET r.abbrevTitleCache = r.titleCache, r.protectedAbbrevTitleCache = r.protectedTitleCache" + // + + // " WHERE r.title IS NULL AND r.protectedTitleCache = 1 "; + // step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, + // query).setAuditing("Reference"); + // stepList.add(step); + + // stepName = + // "Update abbrevTitleCache for protected title caches with title"; + // query = " UPDATE Reference r " + + // " SET r.abbrevTitle = r.title, r.abbrevTitleCache = r.titleCache, r.protectedAbbrevTitleCache = r.protectedTitleCache" + // + + // " WHERE r.title IS NOT NULL AND r.protectedTitleCache = 0 "; + // step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, + // query).setAuditing("Reference"); + // stepList.add(step); + stepName = "Update reference title, set null where abbrev title very likely"; - query = " UPDATE Reference r " + - " SET r.title = NULL " + - " WHERE r.title IS NOT NULL AND r.protectedTitleCache = 0 AND " + - " ( 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 '%.%.%.%.%.%' " + - ")" ; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); + query = " UPDATE @@Reference@@ " + + " SET title = NULL " + + " WHERE title IS NOT NULL AND protectedTitleCache = @FALSE@ AND " + + " ( LENGTH(title) <= 15 AND title like '%.%.%' OR LENGTH(title) < 30 AND title like '%.%.%.%' OR LENGTH(title) < 45 AND title like '%.%.%.%.%' OR LENGTH(title) < 60 AND title like '%.%.%.%.%.%' " + + ")"; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); stepList.add(step); - stepName = "Update reference abbrevTitle, set null where abbrev title very unlikely"; - query = " UPDATE Reference r " + - " SET r.abbrevTitle = NULL " + - " WHERE r.title IS NOT NULL AND r.protectedTitleCache = 0 AND " + - " ( title NOT like '%.%' OR LENGTH(r.title) > 30 AND title NOT like '%.%.%' " + - ")" ; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - + query = " UPDATE @@Reference@@ " + + " SET abbrevTitle = NULL " + + " WHERE title IS NOT NULL AND protectedTitleCache = @FALSE@ AND " + + " ( title NOT like '%.%' OR LENGTH(title) > 30 AND title NOT like '%.%.%' " + + ")"; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + } private void removeOldSequenceColumns(List stepList) { - //TODO also remove Identifiable attributes ?? - - //remove citationmicroreference + + // remove citation microreference String stepName = "Remove citationmicroreference column"; String tableName = "Sequence"; String columnName = "citationMicroReference"; - ISchemaUpdaterStep step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT); + ISchemaUpdaterStep step = ColumnRemover.NewInstance(stepName, + tableName, columnName, INCLUDE_AUDIT); stepList.add(step); - - //remove datesequenced + + // remove datesequenced stepName = "Remove datesequenced column"; columnName = "datesequenced"; - step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT); + step = ColumnRemover.NewInstance(stepName, tableName, columnName, + INCLUDE_AUDIT); stepList.add(step); - - //remove length + + // remove length stepName = "Remove length column"; columnName = "length"; - step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT); + step = ColumnRemover.NewInstance(stepName, tableName, columnName, + INCLUDE_AUDIT); stepList.add(step); - - //remove sequence + + // remove sequence stepName = "Remove sequence column"; columnName = "sequence"; - step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT); - stepList.add(step); + step = ColumnRemover.NewInstance(stepName, tableName, columnName, + INCLUDE_AUDIT); + stepList.add(step); - //remove locus_id + // remove locus_id stepName = "Remove locus_id column"; columnName = "locus_id"; - step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT); + step = ColumnRemover.NewInstance(stepName, tableName, columnName, + INCLUDE_AUDIT); stepList.add(step); - - //remove publishedin_id + + // remove publishedin_id stepName = "Remove publishedin_id column"; columnName = "publishedin_id"; - step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT); - stepList.add(step); - - //remove barcode + step = ColumnRemover.NewInstance(stepName, tableName, columnName, + INCLUDE_AUDIT); + stepList.add(step); + + // remove barcode stepName = "Remove barcode column"; columnName = "barcode"; - step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT); - stepList.add(step); - } + step = ColumnRemover.NewInstance(stepName, tableName, columnName, + INCLUDE_AUDIT); + stepList.add(step); - private void updateIdInVocabulary(List stepList) { + // identifiable columns + // remove lsid_authority + stepName = "Remove lsid_authority"; + columnName = "lsid_authority"; + step = ColumnRemover.NewInstance(stepName, tableName, columnName, + INCLUDE_AUDIT); + stepList.add(step); - String queryVocUuid = " UPDATE DefinedTermBase dtb INNER JOIN TermVocabulary voc ON voc.id = dtb.vocabulary_id" + - " SET dtb.idInVocabulary = (SELECT abbreviatedlabel " + - " FROM DefinedTermBase_Representation MN " + - " INNER JOIN Representation r ON r.id = MN.representations_id " + - " WHERE MN.DefinedTermBase_id = dtb.id) " + - " WHERE voc.uuid = '%s'"; - - //Languages (ISO) - String stepName = "Update idInVocabulary for Languages "; - String query = "UPDATE DefinedTermBase dtb INNER JOIN TermVocabulary voc ON voc.id = dtb.vocabulary_id " + - " SET dtb.idInVocabulary = dtb.iso639_2 "+ - " WHERE voc.uuid = '45ac7043-7f5e-4f37-92f2-3874aaaef2de' "; - ISchemaUpdaterStep step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); + // remove lsid_lsid + stepName = "Remove lsid_lsid"; + columnName = "lsid_lsid"; + step = ColumnRemover.NewInstance(stepName, tableName, columnName, + INCLUDE_AUDIT); stepList.add(step); - //Undefined Languages => all + // remove lsid_namespace + stepName = "Remove lsid_namespace"; + columnName = "lsid_namespace"; + step = ColumnRemover.NewInstance(stepName, tableName, columnName, + INCLUDE_AUDIT); + stepList.add(step); + + // remove lsid_object + stepName = "Remove lsid_object"; + columnName = "lsid_object"; + step = ColumnRemover.NewInstance(stepName, tableName, columnName, + INCLUDE_AUDIT); + stepList.add(step); + + // remove lsid_revision + stepName = "Remove lsid_revision"; + columnName = "lsid_revision"; + step = ColumnRemover.NewInstance(stepName, tableName, columnName, + INCLUDE_AUDIT); + stepList.add(step); + + // remove protectedTitleCache + stepName = "Remove protectedTitleCache"; + columnName = "protectedTitleCache"; + step = ColumnRemover.NewInstance(stepName, tableName, columnName, + INCLUDE_AUDIT); + stepList.add(step); + + // remove titleCache + stepName = "Remove titleCache"; + columnName = "titleCache"; + step = ColumnRemover.NewInstance(stepName, tableName, columnName, + INCLUDE_AUDIT); + stepList.add(step); + + } + + private void updateIdInVocabulary(List stepList) { + String tableName = "DefinedTermBase"; + + //NOT ANSI - works with MySQL http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql +// String queryVocUuid = " UPDATE @@DefinedTermBase@@ dtb INNER JOIN @@TermVocabulary@@ voc ON voc.id = dtb.vocabulary_id" +// + " SET dtb.idInVocabulary = (SELECT abbreviatedlabel " +// + " FROM @@DefinedTermBase_Representation@@ MN " +// + " INNER JOIN @@Representation@@ r ON r.id = MN.representations_id " +// + " WHERE MN.DefinedTermBase_id = dtb.id) " +// + " WHERE voc.uuid = '%s'"; + + //ANSI + String queryVocUuid = " UPDATE @@DefinedTermBase@@ " + + " SET idInVocabulary = " + + " (SELECT abbreviatedlabel " + + " FROM @@DefinedTermBase_Representation@@ MN " + + " INNER JOIN @@Representation@@ r ON r.id = MN.representations_id " + + " WHERE MN.DefinedTermBase_id = @@DefinedTermBase@@.id) " + + " WHERE EXISTS (SELECT * FROM @@TermVocabulary@@ voc WHERE voc.id = @@DefinedTermBase@@.vocabulary_id " + + " AND voc.uuid = '%s') "; + + + // Languages (ISO) + String stepName = "Update idInVocabulary for Languages "; +// String query = "UPDATE @@DefinedTermBase@@ dtb INNER JOIN @@TermVocabulary@@ voc ON voc.id = dtb.vocabulary_id " +// + " SET dtb.idInVocabulary = dtb.iso639_2 " +// + " WHERE voc.uuid = '45ac7043-7f5e-4f37-92f2-3874aaaef2de' "; + String query = "UPDATE @@DefinedTermBase@@ " + + " SET idInVocabulary = iso639_2 " + + " WHERE EXISTS (SELECT * FROM @@TermVocabulary@@ voc WHERE voc.id = @@DefinedTermBase@@.vocabulary_id " + + " AND voc.uuid = '45ac7043-7f5e-4f37-92f2-3874aaaef2de') "; + ISchemaUpdaterStep step = SimpleSchemaUpdaterStep + .NewNonAuditedInstance(stepName, query, 99).setDefaultAuditing( + tableName); // not fully correct as we should join with + // TermVoc_AUD but good enough for this usecase + stepList.add(step); + + // Undefined Languages => all stepName = "Update idInVocabulary for undefined languages"; String uuid = "7fd1e6d0-2e76-4dfa-bad9-2673dd042c28"; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, queryVocUuid); - stepList.add(step); - - //Waterbody & Country => all + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, + String.format(queryVocUuid, uuid), 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // Waterbody & Country => all stepName = "Update idInVocabulary for WaterbodyOrCountries"; uuid = "006b1870-7347-4624-990f-e5ed78484a1a"; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, queryVocUuid); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, + String.format(queryVocUuid, uuid), 99) + .setDefaultAuditing(tableName); stepList.add(step); - - //TdwgAreas => all + + // TdwgAreas => all stepName = "Update idInVocabulary for TDWG areas"; uuid = NamedArea.uuidTdwgAreaVocabulary.toString(); - step = SimpleSchemaUpdaterStep.NewInstance(stepName, String.format(queryVocUuid, uuid)); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, + String.format(queryVocUuid, uuid), 99) + .setDefaultAuditing(tableName); stepList.add(step); - - //Rank => some + + // Rank => some stepName = "Update idInVocabulary for ranks"; uuid = "ef0d1ce1-26e3-4e83-b47b-ca74eed40b1b"; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, String.format(queryVocUuid, uuid)); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, + String.format(queryVocUuid, uuid), 99) + .setDefaultAuditing(tableName); stepList.add(step); - - //SpecimenTypeDesignationStatus => alle + + // avoid duplicate for section (bot.) + stepName = "Update idInVoc for section (bot.)"; + String sql = " UPDATE @@DefinedTermBase@@ " + + " SET idInVocabulary = 'sect.(bot.)' " + + " WHERE uuid = '3edff68f-8527-49b5-bf91-7e4398bb975c'"; + step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, + "DefinedTermBase", 99); + stepList.add(step); + + // avoid duplicate for subsection (bot.) + stepName = "Update idInVoc for subsection (bot.)"; + sql = " UPDATE @@DefinedTermBase@@ " + + " SET idInVocabulary = 'subsect.(bot.)' " + + " WHERE uuid = 'd20f5b61-d463-4448-8f8a-c1ff1f262f59'"; + step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, + "DefinedTermBase", 99); + stepList.add(step); + + // avoid duplicate for section (zool.) + stepName = "Update idInVoc for section (zool.)"; + sql = " UPDATE @@DefinedTermBase@@ SET idInVocabulary = 'sect.(zool.)' WHERE uuid = '691d371e-10d7-43f0-93db-3d7fa1a62c54'"; + step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, + "DefinedTermBase", 99); + stepList.add(step); + + // avoid duplicate for subsection (zool.) + stepName = "Update idInVoc for subsection (zool.)"; + sql = " UPDATE @@DefinedTermBase@@ SET idInVocabulary = 'subsect.(zool.)' WHERE uuid = '0ed32d28-adc4-4303-a9ca-68e2acd67e33'"; + step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, + "DefinedTermBase", 99); + stepList.add(step); + + // SpecimenTypeDesignationStatus => all stepName = "Update idInVocabulary for SpecimenTypeDesignationStatus"; uuid = "ab177bd7-d3c8-4e58-a388-226fff6ba3c2"; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, String.format(queryVocUuid, uuid)); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, + String.format(queryVocUuid, uuid), 99) + .setDefaultAuditing(tableName); stepList.add(step); - //NameTypeDesignationStatus => alle + // avoid duplicate for PT + stepName = "Update idInVoc for Phototype (PhT) to avoid duplicate for PT"; + sql = " UPDATE @@DefinedTermBase@@ SET idInVocabulary = 'PhT' WHERE uuid = 'b7807acc-f559-474e-ad4a-e7a41e085e34'"; + step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, + "DefinedTermBase", 99); + stepList.add(step); + + // NameTypeDesignationStatus => all stepName = "Update idInVocabulary for NameTypeDesignationStatus"; uuid = "ab60e738-4d09-4c24-a1b3-9466b01f9f55"; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, String.format(queryVocUuid, uuid)); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, + String.format(queryVocUuid, uuid), 99) + .setDefaultAuditing(tableName); stepList.add(step); - - //NomenclaturalStatusType => all, abbrevs. + + // NomenclaturalStatusType => all, abbrevs. stepName = "Update idInVocabulary for NomenclaturalStatusType"; uuid = "bb28cdca-2f8a-4f11-9c21-517e9ae87f1f"; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, String.format(queryVocUuid, uuid)); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, + String.format(queryVocUuid, uuid), 99) + .setDefaultAuditing(tableName); stepList.add(step); - //TaxonRelationshipType, all but 2 (Invalid Designation for, Misapplied Name for) + // TaxonRelationshipType, all but 2 (Invalid Designation for, Misapplied + // Name for) stepName = "Update idInVocabulary for TaxonRelationshipType"; uuid = "15db0cf7-7afc-4a86-a7d4-221c73b0c9ac"; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, String.format(queryVocUuid, uuid)); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, + String.format(queryVocUuid, uuid), 99) + .setDefaultAuditing(tableName); stepList.add(step); - - //PresenceTerm => all + + // avoid duplicate for Misapplied Name (remove '-') + stepName = "Update idInVoc for Misapplied Name Relationship"; + sql = " UPDATE @@DefinedTermBase@@ SET idInVocabulary = NULL WHERE uuid = '1ed87175-59dd-437e-959e-0d71583d8417'"; + step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, + "DefinedTermBase", 99); + stepList.add(step); + + // avoid duplicate for Invalid designation (remove '-') + stepName = "Update idInVoc for Invalid Designation"; + sql = " UPDATE @@DefinedTermBase@@ SET idInVocabulary = NULL WHERE uuid = '605b1d01-f2b1-4544-b2e0-6f08def3d6ed'"; + step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, + "DefinedTermBase", 99); + stepList.add(step); + + // PresenceTerm => all stepName = "Update idInVocabulary for PresenceTerm"; uuid = "adbbbe15-c4d3-47b7-80a8-c7d104e53a05"; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, String.format(queryVocUuid, uuid)); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, + String.format(queryVocUuid, uuid), 99) + .setDefaultAuditing(tableName); stepList.add(step); - //AbsenceTerm => all + // AbsenceTerm => all stepName = "Update idInVocabulary for AbsenceTerm"; uuid = "5cd438c8-a8a1-4958-842e-169e83e2ceee"; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, String.format(queryVocUuid, uuid)); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, + String.format(queryVocUuid, uuid), 99) + .setDefaultAuditing(tableName); stepList.add(step); - - //Sex => all + + // Sex => all stepName = "Update idInVocabulary for Sex"; uuid = "9718b7dd-8bc0-4cad-be57-3c54d4d432fe"; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, String.format(queryVocUuid, uuid)); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, + String.format(queryVocUuid, uuid), 99) + .setDefaultAuditing(tableName); stepList.add(step); - //ExtensionType => all + // ExtensionType => all stepName = "Update idInVocabulary for ExtensionType"; uuid = "117cc307-5bd4-4b10-9b2f-2e14051b3b20"; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, String.format(queryVocUuid, uuid)); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, + String.format(queryVocUuid, uuid), 99) + .setDefaultAuditing(tableName); stepList.add(step); - //ReferenceSystem => all + // ReferenceSystem => all stepName = "Update idInVocabulary for ReferenceSystem"; uuid = "ec6376e5-0c9c-4f5c-848b-b288e6c17a86"; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, String.format(queryVocUuid, uuid)); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, + String.format(queryVocUuid, uuid), 99) + .setDefaultAuditing(tableName); stepList.add(step); - - //DeterminationModifier => all + + // DeterminationModifier => all stepName = "Update idInVocabulary for DeterminationModifier"; uuid = "fe87ea8d-6e0a-4e5d-b0da-0ab8ea67ca77"; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, String.format(queryVocUuid, uuid)); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, + String.format(queryVocUuid, uuid), 99) + .setDefaultAuditing(tableName); stepList.add(step); - //InstitutionType, MeasurementUnit, Scope, Stage, State, TextFormat, Modifier, PreservationMethod => dummies + // InstitutionType, MeasurementUnit, Scope, Stage, State, TextFormat, + // Modifier, PreservationMethod => dummies stepName = "Update idInVocabulary for dummy terms in several vocabularies"; - query = " UPDATE DefinedTermBase dtb " + - " SET dtb.idInVocabulary = (SELECT abbreviatedlabel " + - " FROM DefinedTermBase_Representation MN " + - " INNER JOIN Representation r ON r.id = MN.representations_id " + - " WHERE MN.DefinedTermBase_id = dtb.id) " + - " WHERE dtb.termType IN ('%s','%s','%s','%s','%s','%s','%s','%s')"; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, String.format(query, - TermType.InstitutionType.getKey(), TermType.MeasurementUnit.getKey(), - TermType.Scope.getKey(), TermType.Stage.getKey(), TermType.State.getKey(), - TermType.TextFormat.getKey(), TermType.Modifier.getKey(), TermType.PreservationMethod.getKey())); + query = " UPDATE @@DefinedTermBase@@ " + + " SET idInVocabulary = (SELECT abbreviatedlabel " + + " FROM @@DefinedTermBase_Representation@@ MN " + + " INNER JOIN @@Representation@@ r ON r.id = MN.representations_id " + + " WHERE MN.DefinedTermBase_id = @@DefinedTermBase@@.id) " + + " WHERE termType IN ('%s','%s','%s','%s','%s','%s','%s','%s')"; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance( + stepName, + String.format(query, TermType.InstitutionType.getKey(), + TermType.MeasurementUnit.getKey(), + TermType.Scope.getKey(), TermType.Stage.getKey(), + TermType.State.getKey(), TermType.TextFormat.getKey(), + TermType.Modifier.getKey(), TermType.Method.getKey()), 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + stepName = "Update idInVocabulary for dummy state"; + query = " UPDATE @@DefinedTermBase@@ " + + " SET idinvocabulary = 'std' " + + " WHERE uuid = '881b9c80-626d-47a6-b308-a63ee5f4178f' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99); + stepList.add(step); + + stepName = "Update idInVocabulary for dummy stage"; + query = " UPDATE @@DefinedTermBase@@ " + + " SET idinvocabulary = 'sgd' " + + " WHERE uuid = '48f8e8a7-a2ac-4974-9ce8-6944afc5095e' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99); + stepList.add(step); + + stepName = "Update idInVocabulary for dummy modifier"; + query = " UPDATE @@DefinedTermBase@@ " + + " SET idinvocabulary = 'md' " + + " WHERE uuid = 'efc38dad-205c-4028-ad9d-ae509a14b37a' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99); + stepList.add(step); + + // Remove state dummy + stepName = "Remove state dummy if possible"; + uuid = "881b9c80-626d-47a6-b308-a63ee5f4178f"; + String checkUsed = " SELECT count(*) as n FROM @@StateData@@ sd " + + " WHERE sd.state_id = %d "; + step = SingleTermRemover.NewInstance(stepName, uuid, checkUsed, 99); + stepList.add(step); + + // Remove institution type dummy + stepName = "Remove institution type dummy term"; + uuid = "bea94a6c-472b-421c-abc1-52f797c51dbf"; + checkUsed = " SELECT count(*) as n FROM @@AgentBase_DefinedTermBase@@ MN " + + " WHERE MN.types_id = %d "; + step = SingleTermRemover.NewInstance(stepName, uuid, checkUsed, 99); + stepList.add(step); + + // Remove measurement unit dummy + stepName = "Remove measurement unit dummy term"; + uuid = "e19dd590-5be8-4c93-978f-b78554116289"; + checkUsed = " SELECT count(*) as n FROM @@DescriptionElementBase@@ deb " + + " WHERE deb.unit_id = %d "; + step = SingleTermRemover.NewInstance(stepName, uuid, checkUsed, 99); + stepList.add(step); + + // Remove scope dummy + stepName = "Remove scope dummy term"; + uuid = "2ace7f1f-4ce6-47e1-8a65-e3f6b724876c"; + checkUsed = " SELECT count(*) as n FROM @@DescriptionBase_Scope@@ MN " + + " WHERE MN.scopes_id = %d "; + step = SingleTermRemover.NewInstance(stepName, uuid, checkUsed, 99); + stepList.add(step); + + // Remove stage dummy + stepName = "Remove stage dummy term"; + uuid = "48f8e8a7-a2ac-4974-9ce8-6944afc5095e"; + checkUsed = " SELECT count(*) as n FROM @@DescriptionBase_Scope@@ MN " + + " WHERE MN.scopes_id = %d "; + String checkUsed2 = " SELECT count(*) as n FROM @@SpecimenOrObservationBase@@ osb " + + " WHERE osb.lifestage_id = %d "; + step = SingleTermRemover.NewInstance(stepName, uuid, checkUsed, 99) + .addCheckUsedQuery(checkUsed2, 99); + stepList.add(step); + + // Remove text format dummy + stepName = "Remove text format dummy if possible"; + uuid = "5d095782-d99c-46bc-a158-edb2e47c9b63"; + checkUsed = " SELECT count(*) as n FROM @@DescriptionElementBase@@ deb " + + " WHERE deb.format_id = %d "; + step = SingleTermRemover.NewInstance(stepName, uuid, checkUsed, 99); + stepList.add(step); + + // Remove modifier dummy + stepName = "Remove modifier dummy if possible"; + uuid = "efc38dad-205c-4028-ad9d-ae509a14b37a"; + checkUsed = " SELECT count(*) as n FROM @@DescriptionElementBase_Modifier@@ MN " + + " WHERE MN.modifiers_id = %d "; + checkUsed2 = " SELECT count(*) as n FROM @@StateData_DefinedTermBase@@ MN " + + " WHERE MN.modifiers_id = %d "; + String checkUsed3 = " SELECT count(*) as n FROM @@StatisticalMeasurementValue_DefinedTermBase@@ MN " + + " WHERE MN.modifiers_id = %d "; + step = SingleTermRemover.NewInstance(stepName, uuid, checkUsed, 99) + .addCheckUsedQuery(checkUsed2, 99).addCheckUsedQuery(checkUsed3, 99); + stepList.add(step); + + // Remove text preservation method dummy + stepName = "Remove preservation method dummy if possible"; + uuid = "3edc2633-365b-4a9b-bc3a-f3f85f59dbdf"; + checkUsed = " SELECT count(*) as n FROM @@SpecimenOrObservationBase@@ osb " + + " WHERE osb.preservation_id = %d "; + step = SingleTermRemover.NewInstance(stepName, uuid, checkUsed, 99); + stepList.add(step); + + // Split Country Vocabulary #3700 + stepName = "Create Waterbody vocabulary"; + UUID uuidVocabulary = UUID + .fromString("35a62b25-f541-4f12-a7c7-17d90dec3e03"); + String description = "Major Waterbodies of the World"; + String label = "Waterbody"; + String abbrev = null; + boolean isOrdered = false; + TermType termType = TermType.NamedArea; + Class termClass = NamedArea.class; + step = VocabularyCreator.NewVocabularyInstance(uuidVocabulary, + description, label, abbrev, isOrdered, termClass, termType); + stepList.add(step); + + stepName = "Move waterbodies to new vocabulary"; + UUID newVocabulary = UUID.fromString("35a62b25-f541-4f12-a7c7-17d90dec3e03"); + step = TermMover + .NewInstance(stepName, newVocabulary, + "aa96ca19-46ab-6365-af29-e4842f13eb4c") + .addTermUuid(UUID.fromString("36aea55c-46ab-6365-af29-e4842f13eb4c")) + .addTermUuid(UUID.fromString("36aea55c-892c-6365-af29-e4842f13eb4c")) + .addTermUuid(UUID.fromString("36aea55c-892c-4114-af29-d4b287f76fab")) + .addTermUuid(UUID.fromString("aa96ca19-892c-4114-af29-d4b287f76fab")) + .addTermUuid(UUID.fromString("aa96ca19-892c-4114-a494-d4b287f76fab")) + .addTermUuid(UUID.fromString("d4cf6c57-892c-4114-bf57-96886eb7108a")) + .addTermUuid(UUID.fromString("d4cf6c57-892c-c953-a494-96886eb7108a")) + .addTermUuid(UUID.fromString("aa96ca19-46ab-c953-a494-96886eb7108a")) + .addTermUuid(UUID.fromString("aa96ca19-46ab-4114-a494-96886eb7108a")); + stepList.add(step); + + // update waterbody uuids #3705 AND waterbody DTYPE to NamedArea and + // sortindex new #3700 + stepName = "Update waterbody uuids"; + sql = " UPDATE @@DefinedTermBase@@ SET DTYPE = 'NamedArea', orderindex = 1, uuid = 'af4271e5-8897-4e6f-9db7-54ea4f28cfc0' WHERE uuid = 'aa96ca19-46ab-6365-af29-e4842f13eb4c' "; + step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, "DefinedTermBase", 99); + stepList.add(step); + sql = " UPDATE @@DefinedTermBase@@ SET DTYPE = 'NamedArea', orderindex = 2, uuid = '77e79804-1b17-4c99-873b-933fe216e3da' WHERE uuid = '36aea55c-46ab-6365-af29-e4842f13eb4c' "; + step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, + "DefinedTermBase", 99); + stepList.add(step); + sql = " UPDATE @@DefinedTermBase@@ SET DTYPE = 'NamedArea', orderindex = 3, uuid = '3d68a327-104c-49d5-a2d8-c71c6600181b' WHERE uuid = '36aea55c-892c-6365-af29-e4842f13eb4c' "; + step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, + "DefinedTermBase", 99); + stepList.add(step); + sql = " UPDATE @@DefinedTermBase@@ SET DTYPE = 'NamedArea', orderindex = 4, uuid = 'ff744a37-5990-462c-9c20-1e85a9943851' WHERE uuid = '36aea55c-892c-4114-af29-d4b287f76fab' "; + step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, + "DefinedTermBase", 99); + stepList.add(step); + sql = " UPDATE @@DefinedTermBase@@ SET DTYPE = 'NamedArea', orderindex = 5, uuid = 'ef04f363-f67f-4a2c-8d98-110de4c5f654' WHERE uuid = 'aa96ca19-892c-4114-af29-d4b287f76fab' "; + step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, + "DefinedTermBase", 99); + stepList.add(step); + sql = " UPDATE @@DefinedTermBase@@ SET DTYPE = 'NamedArea', orderindex = 6, uuid = '8811a47e-29d6-4455-8f83-8916b78a692f' WHERE uuid = 'aa96ca19-892c-4114-a494-d4b287f76fab' "; + step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, + "DefinedTermBase", 99); + stepList.add(step); + sql = " UPDATE @@DefinedTermBase@@ SET DTYPE = 'NamedArea', orderindex = 7, uuid = '4cb4bbae-9aab-426c-9025-e34f809165af' WHERE uuid = 'd4cf6c57-892c-4114-bf57-96886eb7108a' "; + step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, + "DefinedTermBase", 99); + stepList.add(step); + sql = " UPDATE @@DefinedTermBase@@ SET DTYPE = 'NamedArea', orderindex = 8, uuid = '598fec0e-b93a-4947-a1f3-601e380797f7' WHERE uuid = 'd4cf6c57-892c-c953-a494-96886eb7108a' "; + step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, + "DefinedTermBase", 99); + stepList.add(step); + sql = " UPDATE @@DefinedTermBase@@ SET DTYPE = 'NamedArea', orderindex = 9, uuid = 'ee69385e-6c80-405c-be6e-974e9fd1e297' WHERE uuid = 'aa96ca19-46ab-c953-a494-96886eb7108a' "; + step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, + "DefinedTermBase", 99); + stepList.add(step); + sql = " UPDATE @@DefinedTermBase@@ SET DTYPE = 'NamedArea', orderindex = 10, uuid = '8dc16e70-74b8-4143-95cf-a659a319a854' WHERE uuid = 'aa96ca19-46ab-4114-a494-96886eb7108a' "; + step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, + "DefinedTermBase", 99); + stepList.add(step); + + // update DTYPE for country + stepName = "Update DTYPE for Countries"; + sql = " UPDATE @@DefinedTermBase@@ SET DTYPE = 'Country' WHERE DTYPE = 'WaterbodyOrCountry' "; + step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, sql, + "DefinedTermBase", 99); + stepList.add(step); + + // Rename tables + stepName = "Rename DefinedTermBase_WaterbodyOrCountry"; + String oldName = "DefinedTermBase_WaterbodyOrCountry"; + String newName = "DefinedTermBase_Country"; + step = TableNameChanger.NewInstance(stepName, oldName, newName, + INCLUDE_AUDIT); stepList.add(step); - - //NULL for empty strings + + // rename column + stepName = "Rename DefinedTermBase_Country.waterbodiesorcountries_id"; + tableName = "DefinedTermBase_Country"; + String oldColumnName = "waterbodiesorcountries_id"; + String newColumnName = "countries_id"; + step = ColumnNameChanger.NewIntegerInstance(stepName, tableName, + oldColumnName, newColumnName, INCLUDE_AUDIT); + stepList.add(step); + + // NULL for empty strings stepName = "Update idInVocabulary, replace empty strings by null"; - query = "Update DefinedTermBase dtb SET idInVocabulary = NULL WHERE idInVocabulary = ''"; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); + query = "Update @@DefinedTermBase@@ dtb SET idInVocabulary = NULL WHERE idInVocabulary = ''"; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); stepList.add(step); - //MarkerType, AnnotationType, NamedAreaType, NamedAreaLevel, Feature, Continent, DerivationEventType, StatisticalMeasure, RightsType,SynonymRelationshipType & HybridRelationshipType & NameRelationshipType - //=> none - - //DnaMarker => yes but no entries - - //Clean up empty abbreviated labels in representations + // MarkerType, AnnotationType, NamedAreaType, NamedAreaLevel, Feature, + // Continent, DerivationEventType, StatisticalMeasure, + // RightsType,SynonymRelationshipType & HybridRelationshipType & + // NameRelationshipType + // => none + + // DnaMarker => yes but no entries + + // Clean up empty abbreviated labels in representations stepName = "Update abbreviated label, replace empty strings by null"; - query = "Update Representation r SET r.abbreviatedLabel = NULL WHERE r.abbreviatedLabel = ''"; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); + query = "UPDATE @@Representation@@ SET abbreviatedLabel = NULL WHERE abbreviatedLabel = ''"; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing("Representation"); // AUD not needed stepList.add(step); } - private void updateTermTypesForVocabularies( List stepList) { - //vocabularies with terms - for (TermType termType : TermType.values()){ + private void updateTermTypesForVocabularies( + List stepList) { + // vocabularies with terms + for (TermType termType : TermType.values()) { updateTermTypeForVocabularies(stepList, termType); } - - //Natural Language Terms + + String tableName = "TermVocabulary"; + // Natural Language Terms String stepName = "Updater termType for NaturalLanguageTerms"; - String query = "UPDATE TermVocabulary voc " + - " SET voc.termType = '" + TermType.NaturalLanguageTerm.getKey() + "' " + - " WHERE voc.uuid = 'fdaba4b0-5c14-11df-a08a-0800200c9a66'"; - ISchemaUpdaterStep step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); + String query = "UPDATE @@TermVocabulary@@ " + " SET termType = '" + + TermType.NaturalLanguageTerm.getKey() + "' " + + " WHERE uuid = 'fdaba4b0-5c14-11df-a08a-0800200c9a66'"; + ISchemaUpdaterStep step = SimpleSchemaUpdaterStep + .NewNonAuditedInstance(stepName, query, 99).setDefaultAuditing( + tableName); stepList.add(step); - //remaining vocabularies + // remaining vocabularies stepName = "Updater termType for remaining vocabularies"; - query = "UPDATE TermVocabulary voc " + - " SET voc.termType = '"+ TermType.Unknown.getKey() +"' " + - " WHERE voc.termType IS NULL"; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); + query = "UPDATE @@TermVocabulary@@ " + " SET termType = '" + + TermType.Unknown.getKey() + "' " + + " WHERE termType IS NULL"; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); stepList.add(step); - } - private void updateTermTypeForVocabularies(List stepList, TermType termType) { - String stepName = "Updater vocabulary termType for " + termType.toString(); - String query = "UPDATE TermVocabulary voc " + - " SET voc.termType = '" + termType.getKey() + "' " + - " WHERE Exists (SELECT * FROM DefinedTermBase dtb WHERE dtb.termType = '" + termType.getKey() + "' AND dtb.vocabulary_id = voc.id)"; - ISchemaUpdaterStep step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); + private void updateTermTypeForVocabularies( + List stepList, TermType termType) { + String stepName = "Updater vocabulary termType for " + + termType.toString(); + String query = "UPDATE @@TermVocabulary@@ " + + " SET termType = '" + + termType.getKey() + + "' " + + " WHERE Exists (SELECT * FROM @@DefinedTermBase@@ dtb WHERE dtb.termType = '" + + termType.getKey() + "' AND dtb.vocabulary_id = @@TermVocabulary@@.id)"; + ISchemaUpdaterStep step = SimpleSchemaUpdaterStep + .NewNonAuditedInstance(stepName, query, 99).setDefaultAuditing( + "TermVocabulary"); // AUD not fully correct as subselect + // should also work on AUD, good enough for our purposes stepList.add(step); } @@ -1004,307 +2046,398 @@ public class SchemaUpdater_31_33 extends SchemaUpdaterBase { */ private void updateTermTypesForTerms(List stepList) { String stepName = "Update termType for NamedAreas"; + String tableName = "DefinedTermBase"; + //NamedArea - String query = " UPDATE DefinedTermBase " + + String query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + TermType.NamedArea.getKey() + "' " + - " WHERE DTYPE = '" + NamedArea.class.getSimpleName() + "' OR DTYPE = 'TdwgArea' OR DTYPE = '"+ WaterbodyOrCountry.class.getSimpleName() + "' OR DTYPE = 'Continent' "; - ISchemaUpdaterStep step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //Lanugage - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.Language.getKey() + "' " + - " WHERE DTYPE = '" + Language.class.getSimpleName() + "' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //RANK - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.Rank.getKey() + "' " + - " WHERE DTYPE = '" + Rank.class.getSimpleName() + "' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //Feature - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.Feature.getKey() + "' " + - " WHERE DTYPE = '" + Feature.class.getSimpleName() + "' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); + " WHERE DTYPE = '" + NamedArea.class.getSimpleName() + "' OR DTYPE = 'TdwgArea' " + + "OR DTYPE = 'WaterbodyOrCountry' OR DTYPE = '"+ Country.class.getSimpleName() + "' OR DTYPE = 'Continent' "; + ISchemaUpdaterStep step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99).setDefaultAuditing(tableName); + stepList.add(step); + + // Lanugage + stepName = "Update termType for Language"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.Language.getKey() + "' " + " WHERE DTYPE = '" + + Language.class.getSimpleName() + "' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // RANK + stepName = "Update termType for Rank"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.Rank.getKey() + "' " + " WHERE DTYPE = '" + + Rank.class.getSimpleName() + "' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // Feature + stepName = "Update termType for Feature"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.Feature.getKey() + "' " + " WHERE DTYPE = '" + + Feature.class.getSimpleName() + "' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // AnnotationType + stepName = "Update termType for Annotation Type"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.AnnotationType.getKey() + "' " + " WHERE DTYPE = '" + + AnnotationType.class.getSimpleName() + "' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // MarkerType + stepName = "Update termType for Marker Type"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.MarkerType.getKey() + "' " + " WHERE DTYPE = '" + + MarkerType.class.getSimpleName() + "' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // ExtensionType + stepName = "Update termType for Extension Type"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.ExtensionType.getKey() + "' " + " WHERE DTYPE = '" + + ExtensionType.class.getSimpleName() + "' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // DerivationEventType + stepName = "Update termType for DerivationEvent Type"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.DerivationEventType.getKey() + "' " + + " WHERE DTYPE = '" + + DerivationEventType.class.getSimpleName() + "' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // PresenceAbsenceTerm + stepName = "Update termType for PresenceAbsence Term"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.PresenceAbsenceTerm.getKey() + "' " + + " WHERE DTYPE = 'PresenceTerm' OR DTYPE = 'AbsenceTerm'"; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // NomenclaturalStatusType + stepName = "Update termType for NomenclaturalStatusType"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.NomenclaturalStatusType.getKey() + "' " + + " WHERE DTYPE = '" + + NomenclaturalStatusType.class.getSimpleName() + "' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // NameRelationshipType + stepName = "Update termType for NameRelationship Type"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.NameRelationshipType.getKey() + "' " + + " WHERE DTYPE = '" + + NameRelationshipType.class.getSimpleName() + "' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // HybridRelationshipType + stepName = "Update termType for HybridRelationship Type"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.HybridRelationshipType.getKey() + "' " + + " WHERE DTYPE = '" + + HybridRelationshipType.class.getSimpleName() + "' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // SynonymRelationshipType + stepName = "Update termType for SynonymRelationship Type"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.SynonymRelationshipType.getKey() + "' " + + " WHERE DTYPE = '" + + SynonymRelationshipType.class.getSimpleName() + "' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // TaxonRelationshipType + stepName = "Update termType for TaxonRelationship Type"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.TaxonRelationshipType.getKey() + "' " + + " WHERE DTYPE = '" + + TaxonRelationshipType.class.getSimpleName() + "' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // NameTypeDesignationStatus + stepName = "Update termType for NameTypeDesignationStatus"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.NameTypeDesignationStatus.getKey() + "' " + + " WHERE DTYPE = '" + + NameTypeDesignationStatus.class.getSimpleName() + "' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // SpecimenTypeDesignationStatus + stepName = "Update termType for SpecimenTypeDesignationStatus"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.SpecimenTypeDesignationStatus.getKey() + "' " + + " WHERE DTYPE = '" + + SpecimenTypeDesignationStatus.class.getSimpleName() + "' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // InstitutionType + stepName = "Update termType for Institution Type"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.InstitutionType.getKey() + "' " + + " WHERE DTYPE = 'InstitutionType' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // NamedAreaType + stepName = "Update termType for NamedArea Type"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.NamedAreaType.getKey() + "' " + " WHERE DTYPE = '" + + NamedAreaType.class.getSimpleName() + "' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // NamedAreaLevel + stepName = "Update termType for NamedArea Level"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.NamedAreaLevel.getKey() + "' " + " WHERE DTYPE = '" + + NamedAreaLevel.class.getSimpleName() + "' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // RightsType + stepName = "Update termType for Rights Type"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.RightsType.getKey() + "' " + + " WHERE DTYPE = 'RightsType' OR DTYPE = 'RightsTerm' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // MeasurementUnit + stepName = "Update termType for MeasurementUnit"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.MeasurementUnit.getKey() + "' " + " WHERE DTYPE = '" + + MeasurementUnit.class.getSimpleName() + "' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // StatisticalMeasure + stepName = "Update termType for Statistical Measure"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.StatisticalMeasure.getKey() + "' " + + " WHERE DTYPE = '" + StatisticalMeasure.class.getSimpleName() + + "' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // PreservationMethod + stepName = "Update termType for Preservation Method"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.Method.getKey() + "' " + " WHERE DTYPE = '" + + PreservationMethod.class.getSimpleName() + "' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // Modifier + stepName = "Update termType for Modifier"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.Modifier.getKey() + "' " + + " WHERE DTYPE = 'Modifier' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // Scope + stepName = "Update termType for Scope"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.Scope.getKey() + "' " + " WHERE DTYPE = 'Scope' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // Stage + stepName = "Update termType for Stage"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.Stage.getKey() + "' " + " WHERE DTYPE = 'Stage' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // Sex + stepName = "Update termType for Sex"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.Sex.getKey() + "' " + " WHERE DTYPE = 'Sex' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // ReferenceSystem + stepName = "Update termType for ReferenceSystem"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.ReferenceSystem.getKey() + "' " + " WHERE DTYPE = '" + + ReferenceSystem.class.getSimpleName() + "' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // State + stepName = "Update termType for State"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.State.getKey() + "' " + " WHERE DTYPE = '" + + State.class.getSimpleName() + "' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // NaturalLanguageTerm + stepName = "Update termType for NaturalLanguageTerm"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.NaturalLanguageTerm.getKey() + "' " + + " WHERE DTYPE = '" + + NaturalLanguageTerm.class.getSimpleName() + "' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // TextFormat + stepName = "Update termType for TextFormat"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.TextFormat.getKey() + "' " + " WHERE DTYPE = '" + + TextFormat.class.getSimpleName() + "' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // DeterminationModifier + stepName = "Update termType for DeterminationModifier"; + query = " UPDATE @@DefinedTermBase@@ " + " SET termType = '" + + TermType.DeterminationModifier.getKey() + "' " + + " WHERE DTYPE = 'DeterminationModifier' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); stepList.add(step); - //AnnotationType - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.AnnotationType.getKey() + "' " + - " WHERE DTYPE = '" + AnnotationType.class.getSimpleName() + "' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //MarkerType - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.MarkerType.getKey() + "' " + - " WHERE DTYPE = '" + MarkerType.class.getSimpleName() + "' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //ExtensionType - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.ExtensionType.getKey() + "' " + - " WHERE DTYPE = '" + ExtensionType.class.getSimpleName() + "' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //DerivationEventType - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.DerivationEventType.getKey() + "' " + - " WHERE DTYPE = '" + DerivationEventType.class.getSimpleName() + "' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //PresenceAbsenceTerm - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.PresenceAbsenceTerm.getKey() + "' " + - " WHERE DTYPE = 'PresenceTerm' OR DTYPE = 'AbsenceTerm'"; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //NomenclaturalStatusType - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.NomenclaturalStatusType.getKey() + "' " + - " WHERE DTYPE = '" + NomenclaturalStatusType.class.getSimpleName() + "' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //NameRelationshipType - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.NameRelationshipType.getKey() + "' " + - " WHERE DTYPE = '" + NameRelationshipType.class.getSimpleName() + "' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //HybridRelationshipType - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.HybridRelationshipType.getKey() + "' " + - " WHERE DTYPE = '" + HybridRelationshipType.class.getSimpleName() + "' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //SynonymRelationshipType - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.SynonymRelationshipType.getKey() + "' " + - " WHERE DTYPE = '" + SynonymRelationshipType.class.getSimpleName() + "' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //TaxonRelationshipType - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.TaxonRelationshipType.getKey() + "' " + - " WHERE DTYPE = '" + TaxonRelationshipType.class.getSimpleName() + "' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //NameTypeDesignationStatus - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.NameTypeDesignationStatus.getKey() + "' " + - " WHERE DTYPE = '" + NameTypeDesignationStatus.class.getSimpleName() + "' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //SpecimenTypeDesignationStatus - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.SpecimenTypeDesignationStatus.getKey() + "' " + - " WHERE DTYPE = '" + SpecimenTypeDesignationStatus.class.getSimpleName() + "' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //InstitutionType - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.InstitutionType.getKey() + "' " + - " WHERE DTYPE = 'InstitutionType' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //NamedAreaType - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.NamedAreaType.getKey() + "' " + - " WHERE DTYPE = '" + NamedAreaType.class.getSimpleName() + "' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //NamedAreaLevel - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.NamedAreaLevel.getKey() + "' " + - " WHERE DTYPE = '" + NamedAreaLevel.class.getSimpleName() + "' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //RightsType - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.RightsType.getKey() + "' " + - " WHERE DTYPE = 'RightsType' OR DTYPE = 'RightsTerm' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //MeasurementUnit - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.MeasurementUnit.getKey() + "' " + - " WHERE DTYPE = '" + MeasurementUnit.class.getSimpleName() + "' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //StatisticalMeasure - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.StatisticalMeasure.getKey() + "' " + - " WHERE DTYPE = '" + StatisticalMeasure.class.getSimpleName() + "' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //PreservationMethod - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.PreservationMethod.getKey() + "' " + - " WHERE DTYPE = '" + PreservationMethod.class.getSimpleName() + "' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //Modifier - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.Modifier.getKey() + "' " + - " WHERE DTYPE = 'Modifier' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //Scope - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.Scope.getKey() + "' " + - " WHERE DTYPE = 'Scope' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //Stage - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.Stage.getKey() + "' " + - " WHERE DTYPE = 'Stage' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //Sex - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.Sex.getKey() + "' " + - " WHERE DTYPE = 'Sex' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //ReferenceSystem - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.ReferenceSystem.getKey() + "' " + - " WHERE DTYPE = '" + ReferenceSystem.class.getSimpleName() + "' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //State - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.State.getKey() + "' " + - " WHERE DTYPE = '" + State.class.getSimpleName() + "' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //NaturalLanguageTerm - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.NaturalLanguageTerm.getKey() + "' " + - " WHERE DTYPE = '" + NaturalLanguageTerm.class.getSimpleName() + "' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //TextFormat - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.TextFormat.getKey() + "' " + - " WHERE DTYPE = '" + TextFormat.class.getSimpleName() + "' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //DeterminationModifier - query = " UPDATE DefinedTermBase " + - " SET termType = '" + TermType.DeterminationModifier.getKey() + "' " + - " WHERE DTYPE = 'DeterminationModifier' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - } /** * @param stepList */ private void updateDtypeOfDefinedTerms(List stepList) { + String tableName = "DefinedTermBase"; - //update DTYPE for institution type and modifiers (Stage, Scope, Sex, DeterminationModifier, Modifier) -> DefinedTerm + // update DTYPE for institution type and modifiers (Stage, Scope, Sex, + // DeterminationModifier, Modifier) -> DefinedTerm String stepName = "Update DTYPE for TDWG Areas"; - String query = " UPDATE DefinedTermBase " + - " SET DTYPE = 'DefinedTerm' " + - " WHERE DTYPE = 'Stage' OR DTYPE = 'Scope' OR DTYPE = 'Sex' OR DTYPE = 'DeterminationModifier' " + - " OR DTYPE = 'Modifier' OR DTYPE = 'InstitutionType' "; - ISchemaUpdaterStep step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); + String query = " UPDATE @@DefinedTermBase@@ " + + " SET DTYPE = 'DefinedTerm' " + + " WHERE DTYPE = 'Stage' OR DTYPE = 'Scope' OR DTYPE = 'Sex' OR DTYPE = 'DeterminationModifier' " + + " OR DTYPE = 'Modifier' OR DTYPE = 'InstitutionType' "; + ISchemaUpdaterStep step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); stepList.add(step); - - - //update DTYPE for TDWG Areas and Continents -> NamedArea + + // update DTYPE for TDWG Areas and Continents -> NamedArea stepName = "Update DTYPE for TDWG Areas and Continents"; - query = " UPDATE DefinedTermBase " + - " SET DTYPE = 'NamedArea' " + - " WHERE DTYPE = 'TdwgArea' OR DTYPE = 'Continent' "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); + query = " UPDATE @@DefinedTermBase@@ " + " SET DTYPE = 'NamedArea' " + + " WHERE DTYPE = 'TdwgArea' OR DTYPE = 'Continent' "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); stepList.add(step); - + } /** * @param stepList */ private void changeUriType(List stepList) { + // #3345 String stepName; String tableName; ISchemaUpdaterStep step; + String columnName; + stepName = "Update uri to clob for DefinedTermBase"; tableName = "DefinedTermBase"; - String oldColumnName = "uri"; - step = ColumnTypeChanger.NewClobInstance(stepName, tableName, oldColumnName, INCLUDE_AUDIT); + columnName = "uri"; + step = ColumnTypeChanger.NewClobInstance(stepName, tableName, + columnName, INCLUDE_AUDIT); stepList.add(step); - + stepName = "Update uri to clob for TermVocabulary"; tableName = "TermVocabulary"; - oldColumnName = "uri"; - step = ColumnTypeChanger.NewClobInstance(stepName, tableName, oldColumnName, INCLUDE_AUDIT); + columnName = "uri"; + step = ColumnTypeChanger.NewClobInstance(stepName, tableName, + columnName, INCLUDE_AUDIT); stepList.add(step); - - //TODO are uri and termsourceuri needed ??? + + // are uri and termsourceuri needed -> see #3955 stepName = "Update termsourceuri to clob for TermVocabulary"; tableName = "TermVocabulary"; - oldColumnName = "termsourceuri"; - step = ColumnTypeChanger.NewClobInstance(stepName, tableName, oldColumnName, INCLUDE_AUDIT); + columnName = "termsourceuri"; + step = ColumnTypeChanger.NewClobInstance(stepName, tableName, + columnName, INCLUDE_AUDIT); stepList.add(step); - + stepName = "Update uri to clob for Reference"; tableName = "Reference"; - oldColumnName = "uri"; - step = ColumnTypeChanger.NewClobInstance(stepName, tableName, oldColumnName, INCLUDE_AUDIT); + columnName = "uri"; + step = ColumnTypeChanger.NewClobInstance(stepName, tableName, + columnName, INCLUDE_AUDIT); stepList.add(step); - + stepName = "Update uri to clob for Rights"; tableName = "Rights"; - oldColumnName = "uri"; - step = ColumnTypeChanger.NewClobInstance(stepName, tableName, oldColumnName, INCLUDE_AUDIT); + columnName = "uri"; + step = ColumnTypeChanger.NewClobInstance(stepName, tableName, + columnName, INCLUDE_AUDIT); stepList.add(step); stepName = "Update uri to clob for MediaRepresentationPart"; tableName = "MediaRepresentationPart"; - oldColumnName = "uri"; - step = ColumnTypeChanger.NewClobInstance(stepName, tableName, oldColumnName, INCLUDE_AUDIT); + columnName = "uri"; + step = ColumnTypeChanger.NewClobInstance(stepName, tableName, + columnName, INCLUDE_AUDIT); stepList.add(step); - - //TODO still needed?? + + // still needed?? stepName = "Update uri to clob for FeatureTree"; tableName = "FeatureTree"; - oldColumnName = "uri"; - step = ColumnTypeChanger.NewClobInstance(stepName, tableName, oldColumnName, INCLUDE_AUDIT); + columnName = "uri"; + step = ColumnTypeChanger.NewClobInstance(stepName, tableName, + columnName, INCLUDE_AUDIT); + stepList.add(step); + + // Annotation.linkbackUri (change from URL to URI) + stepName = "Update url to uri (->clob) for Annotation.linkbackUri"; + tableName = "Annotation"; + columnName = "linkbackUrl"; + step = ColumnTypeChanger.NewClobInstance(stepName, tableName, + columnName, INCLUDE_AUDIT); stepList.add(step); } @@ -1319,109 +2452,120 @@ public class SchemaUpdater_31_33 extends SchemaUpdaterBase { String tableName; ISchemaUpdaterStep step; String columnName; - //start #3312 + // start #3312 stepName = "Create time period start column in description element base"; tableName = "DescriptionElementBase"; columnName = "timeperiod_start"; - step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, INCLUDE_AUDIT); + step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, + INCLUDE_AUDIT); stepList.add(step); - //end #3312 + // end #3312 stepName = "Create time period end column in description element base"; tableName = "DescriptionElementBase"; columnName = "timeperiod_end"; - step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, INCLUDE_AUDIT); + step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, + INCLUDE_AUDIT); stepList.add(step); - //freetext #3312 + // freetext #3312 stepName = "Create time period freetext column in description element base"; tableName = "DescriptionElementBase"; columnName = "timeperiod_freetext"; - step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, INCLUDE_AUDIT); + step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, + INCLUDE_AUDIT); stepList.add(step); - + return; } private void updateElevationMax(List stepList) { - //create column + // create column String stepName = "Create absoluteElevationMax column"; String tableName = "GatheringEvent"; String columnName = "absoluteElevationMax"; - ISchemaUpdaterStep step = ColumnAdder.NewIntegerInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false, null); + ISchemaUpdaterStep step = ColumnAdder.NewIntegerInstance(stepName, + tableName, columnName, INCLUDE_AUDIT, false, null); stepList.add(step); - - - //update max + + String audTableName = "GatheringEvent"; + // update max stepName = "Update gathering elevation max"; - //all audits to unknown type - String query = " UPDATE GatheringEvent ge " + - " SET ge.absoluteElevationMax = ge.absoluteElevation + ge.absoluteElevationError, " + - " ge.absoluteElevation = ge.absoluteElevation - ge.absoluteElevationError" + - " WHERE ge.absoluteElevationError is not null "; - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - //TODO same for AUD - - //remove error column + // all audits to unknown type + String query = " UPDATE @@GatheringEvent@@ " + + " SET absoluteElevationMax = absoluteElevation + absoluteElevationError, " + + " absoluteElevation = absoluteElevation - absoluteElevationError" + + " WHERE absoluteElevationError is not null "; + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(audTableName); + stepList.add(step); + // TODO same for AUD + + // remove error column stepName = "Remove elevationErrorRadius column"; tableName = "GatheringEvent"; columnName = "absoluteElevationError"; - step = ColumnRemover.NewInstance(stepName, tableName, columnName, INCLUDE_AUDIT); + step = ColumnRemover.NewInstance(stepName, tableName, columnName, + INCLUDE_AUDIT); stepList.add(step); - - //create column absoluteElevationText + + // create column absoluteElevationText stepName = "Create absoluteElevationText column"; tableName = "GatheringEvent"; columnName = "absoluteElevationText"; - //TODO size - step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT); + int size = 30; + step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, + size, INCLUDE_AUDIT); stepList.add(step); - - //retype distanceToGround + + // retype distanceToGround stepName = "Rname distanceToGround column"; tableName = "GatheringEvent"; String strOldColumnName = "distanceToGround"; - step = ColumnTypeChanger.NewInt2DoubleInstance(stepName, tableName, strOldColumnName, INCLUDE_AUDIT); + step = ColumnTypeChanger.NewInt2DoubleInstance(stepName, tableName, + strOldColumnName, INCLUDE_AUDIT); stepList.add(step); - - //create column distanceToGroundMax + + // create column distanceToGroundMax stepName = "Create distanceToGroundMax column"; tableName = "GatheringEvent"; columnName = "distanceToGroundMax"; - step = ColumnAdder.NewDoubleInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false); + step = ColumnAdder.NewDoubleInstance(stepName, tableName, columnName, + INCLUDE_AUDIT, false); stepList.add(step); - - - //create column distanceToGroundText + + // create column distanceToGroundText stepName = "Create distanceToGroundText column"; tableName = "GatheringEvent"; columnName = "distanceToGroundText"; - //TODO size - step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT); + size = 30; + step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, + size, INCLUDE_AUDIT); stepList.add(step); - - //retype distanceToGround + + // retype distanceToGround stepName = "Rname distanceToWaterSurface column"; tableName = "GatheringEvent"; strOldColumnName = "distanceToWaterSurface"; - step = ColumnTypeChanger.NewInt2DoubleInstance(stepName, tableName, strOldColumnName, INCLUDE_AUDIT); + step = ColumnTypeChanger.NewInt2DoubleInstance(stepName, tableName, + strOldColumnName, INCLUDE_AUDIT); stepList.add(step); - - //create column distanceToWaterSurface + + // create column distanceToWaterSurface stepName = "Create distanceToWaterSurfaceMax column"; tableName = "GatheringEvent"; columnName = "distanceToWaterSurfaceMax"; - step = ColumnAdder.NewDoubleInstance(stepName, tableName, columnName, INCLUDE_AUDIT, false); + step = ColumnAdder.NewDoubleInstance(stepName, tableName, columnName, + INCLUDE_AUDIT, false); stepList.add(step); - - - //create column distanceToGroundText + + // create column distanceToGroundText stepName = "Create distanceToWaterSurfaceText column"; tableName = "GatheringEvent"; columnName = "distanceToWaterSurfaceText"; - //TODO size - step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT); + size = 30; + step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, + size, INCLUDE_AUDIT); stepList.add(step); } @@ -1433,46 +2577,47 @@ public class SchemaUpdater_31_33 extends SchemaUpdaterBase { String stepName; String typeAttrName = "sourceType"; ISchemaUpdaterStep step; - stepName = "Update original source type column in OriginalSourceBase_AUD: set all to unknown"; - //all audits to unknown type - String query = String.format("UPDATE OriginalSourceBase_AUD SET %s = '%s' ", typeAttrName, OriginalSourceType.Unknown.getKey()); - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //all data to unknown + String tableName = "OriginalSourceBase"; + + // all data to unknown stepName = "Update original source type column: set all to unknown"; - query = String.format("UPDATE OriginalSourceBase SET %s = '%s' ", typeAttrName, OriginalSourceType.Unknown.getKey()); - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); + String query = String.format("UPDATE @@OriginalSourceBase@@ " + + " SET %s = '%s' ", typeAttrName, + OriginalSourceType.Unknown.getKey()); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); stepList.add(step); - - //all IMPORTS recognized by idInSOurce and by missing nameInSource + + // all IMPORTS recognized by idInSOurce and by missing nameInSource stepName = "Update original source type column: set to 'import' where possible"; - query = String.format("UPDATE OriginalSourceBase SET %s = '%s' WHERE " + - "((idInSource IS NOT NULL) OR (idNamespace IS NOT NULL)) AND " + - "( nameUsedInSource_id IS NULL AND originalNameString IS NULL ) ", typeAttrName, OriginalSourceType.Import.getKey()); - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); - stepList.add(step); - - //all PRIMARY TAXONOMIC SOURCES recognized by missing idInSource and namespace and by existing citation + query = String + .format("UPDATE @@OriginalSourceBase@@ " + + " SET %s = '%s' " + + " WHERE " + + "((idInSource IS NOT NULL) OR (idNamespace IS NOT NULL)) AND " + + "( nameUsedInSource_id IS NULL AND originalNameString IS NULL ) ", + typeAttrName, OriginalSourceType.Import.getKey()); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); + stepList.add(step); + + // all PRIMARY TAXONOMIC SOURCES recognized by missing idInSource and + // namespace and by existing citation stepName = "Update original source type column: set to 'primary taxonomic source' where possible"; - query = String.format("UPDATE OriginalSourceBase SET %s = '%s' WHERE " + - "(idInSource IS NULL AND idNamespace IS NULL) AND " + - "( citation_id IS NOT NULL ) ", typeAttrName, OriginalSourceType.PrimaryTaxonomicSource.getKey()); - step = SimpleSchemaUpdaterStep.NewInstance(stepName, query); + query = String.format("UPDATE @@OriginalSourceBase@@ SET %s = '%s' WHERE " + + "(idInSource IS NULL AND idNamespace IS NULL) AND " + + "( citation_id IS NOT NULL ) ", typeAttrName, + OriginalSourceType.PrimaryTaxonomicSource.getKey()); + step = SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepName, query, 99) + .setDefaultAuditing(tableName); stepList.add(step); } - /* (non-Javadoc) - * @see eu.etaxonomy.cdm.database.update.SchemaUpdaterBase#getNextUpdater() - */ @Override public ISchemaUpdater getNextUpdater() { - return null; + return SchemaUpdater_33_331.NewInstance(); } - /* (non-Javadoc) - * @see eu.etaxonomy.cdm.database.update.SchemaUpdaterBase#getPreviousUpdater() - */ @Override public ISchemaUpdater getPreviousUpdater() { return SchemaUpdater_30_301.NewInstance();