ref #5974 fix update script for synonym duplicates

diff --git a/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v40_41/SchemaUpdater_40_41.java b/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v40_41/SchemaUpdater_40_41.java
index 40da034757808e35923c7a57fd14dfde1ffbe470..c367577d3cee0a04b87e32097cafe2a452984ffa 100644 (file)
--- a/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v40_41/SchemaUpdater_40_41.java
+++ b/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v40_41/SchemaUpdater_40_41.java
@@ -288,22 +288,22 @@ public class SchemaUpdater_40_41 extends SchemaUpdaterBase {
     }
 
        private void removeSynonymRelationships_5974(List<ISchemaUpdaterStep> stepList) {
-           //add partial to Synonym
-        String stepName = "Add partial to Synonym";
+           //add partial column to Synonym
+        String stepName = "Add partial column to Synonym";
         String tableName = "TaxonBase";
         String newColumnName = "partial";
         ISchemaUpdaterStep step = ColumnAdder.NewBooleanInstance(stepName, tableName, newColumnName, INCLUDE_AUDIT, false);
         stepList.add(step);
 
-           //add proParte to Synonym
-        stepName = "Add proParte to Synonym";
+           //add proParte column to Synonym
+        stepName = "Add proParte column to Synonym";
         tableName = "TaxonBase";
         newColumnName = "proParte";
         step = ColumnAdder.NewBooleanInstance(stepName, tableName, newColumnName, INCLUDE_AUDIT, false);
         stepList.add(step);
 
-           //add type to Synonym
-        stepName = "Add type to Synonym";
+           //add type column to Synonym
+        stepName = "Add type column to Synonym";
         tableName = "TaxonBase";
         newColumnName = "type_id";
         String referencedTable = "DefinedTermBase";
@@ -319,10 +319,15 @@ public class SchemaUpdater_40_41 extends SchemaUpdaterBase {
         stepList.add(step);
 
            //move data
+        //move duplicates first
+        step = SynonymDeduplicator.NewInstance();
+        stepList.add(step);
+
         //update pro parte
         stepName = "Update proParte";
         String updateSql = "UPDATE @@TaxonBase@@ syn " +
-                " SET proParte = (SELECT DISTINCT proParte FROM @@SynonymRelationship@@ sr WHERE sr.relatedFrom_id = syn.id)";
+                " SET proParte = (SELECT DISTINCT proParte FROM @@SynonymRelationship@@ sr WHERE sr.relatedFrom_id = syn.id) " +
+                " WHERE acceptedTaxon_id IS NULL ";
 //        -- WHERE EXISTS (SELECT proParte FROM SynonymRelationship sr WHERE sr.relatedFrom_id = syn.id AND sr.proParte = 1);
         step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, updateSql, "TaxonBase", -99)
                 //.addDefaultAuditing("SynonymRelationship")  //difficult to implement due to non-uniqueness in subquery
@@ -332,7 +337,8 @@ public class SchemaUpdater_40_41 extends SchemaUpdaterBase {
         //update partial
         stepName = "Update partial";
         updateSql = "UPDATE @@TaxonBase@@ syn " +
-                " SET partial=(SELECT DISTINCT partial FROM @@SynonymRelationship@@ sr WHERE sr.relatedFrom_id = syn.id) ";
+                " SET partial=(SELECT DISTINCT partial FROM @@SynonymRelationship@@ sr WHERE sr.relatedFrom_id = syn.id) " +
+                " WHERE acceptedTaxon_id IS NULL ";
         step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, updateSql, "TaxonBase", -99)
                 //.addDefaultAuditing("SynonymRelationship")  //difficult to implement due to non-uniqueness in subquery
                 ;
@@ -341,15 +347,17 @@ public class SchemaUpdater_40_41 extends SchemaUpdaterBase {
         //update synonym type
         stepName = "Update Synonym type";
         updateSql = "UPDATE @@TaxonBase@@ syn " +
-                " SET type_id=(SELECT DISTINCT type_id FROM @@SynonymRelationship@@ sr WHERE sr.relatedFrom_id = syn.id)";
-//        String updateSqlAud = updateSql.replace("TaxonBase", "TaxonBase_AUD").replace("SynonymRelationship", "SynonymRelationship_AUD");
+                " SET type_id=(SELECT DISTINCT type_id FROM @@SynonymRelationship@@ sr WHERE sr.relatedFrom_id = syn.id)" +
+                " WHERE acceptedTaxon_id IS NULL ";
+        //        String updateSqlAud = updateSql.replace("TaxonBase", "TaxonBase_AUD").replace("SynonymRelationship", "SynonymRelationship_AUD");
         step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, updateSql, "TaxonBase", -99);
         stepList.add(step);
 
         //update acceptedTaxon_id
         stepName = "Update acceptedTaxon_id";
         updateSql = "UPDATE @@TaxonBase@@ syn " +
-                " SET acceptedTaxon_id=(SELECT DISTINCT relatedTo_id FROM @@SynonymRelationship@@ sr WHERE sr.relatedFrom_id = syn.id)";
+                " SET acceptedTaxon_id=(SELECT DISTINCT relatedTo_id FROM @@SynonymRelationship@@ sr WHERE sr.relatedFrom_id = syn.id)" +
+                " WHERE acceptedTaxon_id IS NULL ";
 //        updateSqlAud = updateSql.replace("TaxonBase", "TaxonBase_AUD").replace("SynonymRelationship", "SynonymRelationship_AUD");
         step = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, updateSql, "TaxonBase", -99);
         stepList.add(step);

diff --git a/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v40_41/SynonymDeduplicator.java b/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v40_41/SynonymDeduplicator.java
new file mode 100644 (file)
index 0000000..81c5ab1
--- /dev/null
+++ b/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v40_41/SynonymDeduplicator.java
@@ -0,0 +1,89 @@
+/**
+* Copyright (C) 2016 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.v40_41;
+
+import java.sql.ResultSet;
+import java.sql.SQLException;
+
+import org.apache.log4j.Logger;
+
+import eu.etaxonomy.cdm.common.monitor.IProgressMonitor;
+import eu.etaxonomy.cdm.database.ICdmDataSource;
+import eu.etaxonomy.cdm.database.update.CaseType;
+import eu.etaxonomy.cdm.database.update.ITermUpdaterStep;
+import eu.etaxonomy.cdm.database.update.SchemaUpdaterStepBase;
+
+/**
+ * Creates new synonym records for all synonym having >1 synonym relationships.
+ * Expects unplaced, excluded and taxonomicChildrenCount to not exist anymore
+ * @author a.mueller
+ * @date 14.11.2016
+ *
+ */
+public class SynonymDeduplicator extends SchemaUpdaterStepBase<SynonymDeduplicator> implements ITermUpdaterStep{
+    @SuppressWarnings("unused")
+    private static final Logger logger = Logger.getLogger(SynonymDeduplicator.class);
+
+    /**
+     * @return
+     */
+    public static SynonymDeduplicator NewInstance() {
+        return new SynonymDeduplicator();
+    }
+
+    private static final String stepName = "Deduplicate synonyms code";
+
+    /**
+     * @param stepName
+     */
+    protected SynonymDeduplicator() {
+        super(stepName);
+    }
+
+    String idListSelect = " SELECT sr.uuid ";
+    String selectAll = " SELECT syn.DTYPE, (SELECT Max(id)+1 FROM TaxonBase), " +
+            " sr.created, sr.uuid, sr.updated, " +
+            " syn.lsid_authority, syn.lsid_lsid, syn.lsid_namespace, syn.lsid_object, syn.lsid_revision, " +
+            " syn.protectedtitlecache, syn.titleCache, syn.appendedphrase, " +
+            " syn.doubtful, syn.usenamecache, syn.taxonstatusunknown, " +
+            " sr.createdby_id,sr.updatedby_id, " +
+            " syn.name_id, syn.sec_id, syn.publish, syn.secMicroReference,"
+            + "sr.partial, sr.proParte, sr.type_id, sr.relatedTo_id ";
+    String insert = " INSERT INTO @@TaxonBase@@ (DTYPE, id, created,uuid, updated,"
+            + "lsid_authority,lsid_lsid,lsid_namespace,lsid_object,lsid_revision, protectedtitlecache,titleCache, appendedphrase,"
+            + "doubtful, usenamecache,taxonstatusunknown,"
+            + "createdby_id, updatedby_id, name_id, sec_id, publish, secMicroReference,"
+            + "partial, proParte, type_id, acceptedTaxon_id)";
+
+    String fromSQL =
+       " FROM @@TaxonBase@@ syn INNER JOIN @@SynonymRelationship@@ sr ON sr.relatedfrom_id = syn.id ";
+    String whereSQL =  " WHERE EXISTS ( " +
+           "  SELECT * FROM @@SynonymRelationship@@ srFirst " +
+           "  WHERE srFirst.id < sr.id " +
+           "    AND srFirst.relatedfrom_id = sr.relatedfrom_id " +
+        " )";
+
+    @Override
+    public Integer invoke(ICdmDataSource datasource, IProgressMonitor monitor, CaseType caseType) throws SQLException {
+
+        String listSql = caseType.replaceTableNames(idListSelect + fromSQL + whereSQL);
+        ResultSet rs = datasource.executeQuery(listSql);
+        while (rs.next()){
+            String uuid = "'" + rs.getString(1) +"'";
+            //create new synonym
+            String update = insert + selectAll + fromSQL + " WHERE sr.uuid = " + uuid;
+            datasource.executeUpdate(caseType.replaceTableNames(update));
+            //remove old relationship
+            String delete = "DELETE FROM @@SynonymRelationship@@ WHERE uuid = " + uuid;
+            datasource.executeUpdate(caseType.replaceTableNames(delete));
+        }
+
+        return 0;
+    }
+}