fix #9785 Update script for missing measurement unit_ids (matrix)

diff --git a/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/SimpleSchemaUpdaterStep.java b/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/SimpleSchemaUpdaterStep.java
index 81e63705fd967b16ba76b2a9648a17771d4c23a3..c1ff54339516ab8ce8fc36dcebbe60fe45fb0cd6 100644 (file)
--- a/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/SimpleSchemaUpdaterStep.java
+++ b/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/SimpleSchemaUpdaterStep.java
@@ -14,7 +14,8 @@ import java.util.List;
 import java.util.Map;
 
 import org.apache.commons.lang.StringUtils;
-import org.apache.logging.log4j.LogManager;import org.apache.logging.log4j.Logger;
+import org.apache.logging.log4j.LogManager;
+import org.apache.logging.log4j.Logger;
 
 import eu.etaxonomy.cdm.common.monitor.IProgressMonitor;
 import eu.etaxonomy.cdm.database.DatabaseTypeEnum;
@@ -33,7 +34,8 @@ public class SimpleSchemaUpdaterStep extends SchemaUpdaterStepBase {
        private final Map<DatabaseTypeEnum, String> auditQueryMap = new HashMap<>();
 
        private boolean includeAudit = false;
-//     private String tableName;
+       private boolean withErrorRecovery = false;
+       private String errorRecoveryMessage;
 
 // *************************** FACTORY ********************************/
 
@@ -79,7 +81,6 @@ public class SimpleSchemaUpdaterStep extends SchemaUpdaterStepBase {
                return new SimpleSchemaUpdaterStep(stepList, stepName, defaultQuery, audit, null, defaultQueryForAuditedTables);
        }
 
-
 //************************ CONSTRUCTOR ***********************************/
 
        private SimpleSchemaUpdaterStep(List<ISchemaUpdaterStep> stepList, String stepName, String defaultQuery,
@@ -140,12 +141,17 @@ public class SimpleSchemaUpdaterStep extends SchemaUpdaterStepBase {
        private boolean executeQuery(ICdmDataSource datasource,  String replacedQuery, SchemaUpdateResult result) {
                try {
                        datasource.executeUpdate(replacedQuery);
+                       return true;
                } catch (SQLException e) {
                        logger.error(e);
-                       result.addException(e, "Unexpected SQL Exception", getStepName());
-                       return false;
+                       if (withErrorRecovery) {
+                           result.addException(e, "Unexpected SQL Exception", getStepName());
+                           return false;
+                       }else {
+                           result.addError(errorRecoveryMessage, e, getStepName());
+                           return true;
+                       }
                }
-               return true;
        }
 
        private void makeAuditedQuery(DatabaseTypeEnum dbType, String tableName, boolean addTable){
@@ -216,4 +222,15 @@ public class SimpleSchemaUpdaterStep extends SchemaUpdaterStepBase {
         return this;
     }
 
+    /**
+     * Setting error recovery will not make the step fail if the query execution
+     * throws an exception. Only an error will be reported with the given
+     * <code>message</code>.
+     */
+    public SimpleSchemaUpdaterStep withErrorRecovery(String message) {
+        this.withErrorRecovery = true;
+        this.errorRecoveryMessage = message;
+        return this;
+    }
+
 }

diff --git a/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v529_532/SchemaUpdater_5290_5320.java b/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v529_532/SchemaUpdater_5290_5320.java
index 878e11a38d1e9e153934e4d0137cb463187cf436..ddbabff49ed6256ea6a9bea499c606bfc34df6d3 100644 (file)
--- a/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v529_532/SchemaUpdater_5290_5320.java
+++ b/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v529_532/SchemaUpdater_5290_5320.java
@@ -19,6 +19,7 @@ import eu.etaxonomy.cdm.database.update.ColumnAdder;
 import eu.etaxonomy.cdm.database.update.ISchemaUpdater;
 import eu.etaxonomy.cdm.database.update.ISchemaUpdaterStep;
 import eu.etaxonomy.cdm.database.update.SchemaUpdaterBase;
+import eu.etaxonomy.cdm.database.update.SimpleSchemaUpdaterStep;
 import eu.etaxonomy.cdm.database.update.TermRepresentationUpdater;
 import eu.etaxonomy.cdm.database.update.UniqueIndexDropper;
 import eu.etaxonomy.cdm.database.update.v527_529.SchemaUpdater_5271_5290;
@@ -97,6 +98,27 @@ public class SchemaUpdater_5290_5320 extends SchemaUpdaterBase {
         label = "México Distrito Federal";
         TermRepresentationUpdater.NewInstanceWithTitleCache(stepList, stepName, uuidTerm, description, label, abbrev, uuidEnglish);
 
+        //#9785 Add missing unit_ids
+        stepName = "Add missing unit_ids";
+        String query = "UPDATE DescriptionElementBase deb LEFT OUTER JOIN DefinedTermBase fe ON fe.id = deb.feature_id"
+                + "SET deb.unit_id = ("
+                + "        SELECT MN2.recommendedMeasurementUnits_id"
+                + "        FROM DefinedTermBase fe2 INNER JOIN DefinedTermBase_MeasurementUnit MN2 ON MN2.Feature_id = fe2.id"
+                + "        WHERE fe.id = fe2.id AND (fe2.DTYPE = 'Feature' OR fe2.DTYPE = 'Character')"
+                + "        GROUP BY fe2.id"
+                + "        HAVING COUNT(*) = 1"
+                + ")"
+                + "WHERE deb.DTYPE = 'QuantitativeData' AND deb.unit_id IS NULL"
+                + "AND fe.id IN ("
+                + "    SELECT fe.id "
+                + "    FROM DefinedTermBase fe INNER JOIN DefinedTermBase_MeasurementUnit MN ON MN.Feature_id = fe.id INNER JOIN DefinedTermBase mu ON MN.recommendedMeasurementUnits_id = mu.id"
+                + "    WHERE fe.DTYPE = 'Feature' OR fe.DTYPE = 'Character'"
+                + "    GROUP BY fe.id, mu.id"
+                + "    HAVING COUNT(*) = 1"
+                + ")";
+        SimpleSchemaUpdaterStep.NewNonAuditedInstance(stepList, stepName, query, size)
+             .withErrorRecovery("SQL statement for adding missing measurement unit_ids failed");
+
                return stepList;
     }
 }
\ No newline at end of file