fix #5634 Update excluded/unplaced for TaxonNodes with no taxon_id

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 0cd432cafd341b694c99c9ada7def5c3e6f7ce82..a26802a26bf54a125d690fc1f0a0af5455c09fdb 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
@@ -26,7 +26,8 @@ import eu.etaxonomy.cdm.database.ICdmDataSource;
  * @date 13.09.2010\r
  *\r
  */\r
-public class SimpleSchemaUpdaterStep extends SchemaUpdaterStepBase<SimpleSchemaUpdaterStep> implements ISchemaUpdaterStep, ITermUpdaterStep{\r
+public class SimpleSchemaUpdaterStep extends SchemaUpdaterStepBase<SimpleSchemaUpdaterStep>\r
+                implements ISchemaUpdaterStep, ITermUpdaterStep{\r
        private static final Logger logger = Logger.getLogger(SimpleSchemaUpdaterStep.class);\r
 \r
        private final Map<DatabaseTypeEnum, String> queryMap = new HashMap<DatabaseTypeEnum, String>();\r
@@ -172,16 +173,32 @@ public class SimpleSchemaUpdaterStep extends SchemaUpdaterStepBase<SimpleSchemaU
 \r
        /**\r
         * For certain database types one may define special queries.<BR>\r
-        * Don't forget to put case-mask (@@) for table names\r
+        * Don't forget to put case-mask (@@) for table names and also\r
+        * add AUD query if required.\r
         * @param dbType database type\r
         * @param query query to use for the given database type.\r
         * @return this schema updater step\r
+     * @see #putAudited(DatabaseTypeEnum, String)\r
         */\r
        public SimpleSchemaUpdaterStep put(DatabaseTypeEnum dbType, String query) {\r
                queryMap.put(dbType, query);\r
                return this;\r
        }\r
 \r
+       /**\r
+     * For certain database types one may define special queries.\r
+     * This is for the AUD query.<BR>\r
+     * Don't forget to put case-mask (@@) for table names\r
+     * @param dbType database type\r
+     * @param query query to use for the given database type.\r
+     * @return this schema updater step\r
+     * @see #put(DatabaseTypeEnum, String)\r
+     */\r
+    public SimpleSchemaUpdaterStep putAudited(DatabaseTypeEnum dbType, String query) {\r
+        auditQueryMap.put(dbType, query);\r
+        return this;\r
+    }\r
+\r
        /**\r
         * Defines the non audited table name for computing the audited query.\r
         * @param nonAuditedTableName uncased table name that is to be audited\r

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 5585cd476c0c68d13f7c3c671302a56483c8a03d..ab90bab067a709858fad0f0fb87f5b4fadb3e79e 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
@@ -16,6 +16,7 @@ import java.util.List;
 import org.apache.log4j.Logger;
 
 import eu.etaxonomy.cdm.common.UTF8;
+import eu.etaxonomy.cdm.database.DatabaseTypeEnum;
 import eu.etaxonomy.cdm.database.update.ColumnAdder;
 import eu.etaxonomy.cdm.database.update.ColumnRemover;
 import eu.etaxonomy.cdm.database.update.ISchemaUpdater;
@@ -160,18 +161,20 @@ public class SchemaUpdater_40_41 extends SchemaUpdaterBase {
         //#3925
         //Move excluded from Taxon to TaxonNode
         stepName = "Move excluded from Taxon to TaxonNode";
-        query = "UPDATE @@TaxonNode@@ " +
-                " SET excluded = 1 " +
-                " WHERE id IN (SELECT id FROM "
-                +       "(SELECT tn.id FROM @@TaxonBase@@ tb  INNER JOIN @@TaxonNode@@ tn ON tn.taxon_id = tb.id  WHERE tb.excluded = 1)"
-                + " as drvTbl)";
         query = "UPDATE @@TaxonNode@@ tn " +
                 " SET excluded = (SELECT excluded FROM @@TaxonBase@@ tb WHERE tb.id = tn.taxon_id)";
-
         simpleStep = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, query, "TaxonNode", -99)
                 .addDefaultAuditing("TaxonBase");
         stepList.add(simpleStep);
 
+        stepName = "Move excluded from Taxon to TaxonNode/set null to false";
+        query = "UPDATE @@TaxonNode@@ SET excluded = 0 WHERE excluded IS NULL";
+        simpleStep = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, query, "TaxonNode", -99);
+        simpleStep.put(DatabaseTypeEnum.PostgreSQL, query.replace("0", "false"));
+        simpleStep.putAudited(DatabaseTypeEnum.PostgreSQL, query.replace("TaxonNode","TaxonNode_AUD").replace("0", "false"));
+        stepList.add(simpleStep);
+
+
         //#3925
         //remove excluded from TaxonNode
         stepName = "Remove excluded from TaxonBase";
@@ -191,19 +194,22 @@ public class SchemaUpdater_40_41 extends SchemaUpdaterBase {
 
         //#3925
         //Move unplaced from Taxon to TaxonNode
+
         stepName = "Move unplaced from Taxon to TaxonNode";
-        query = "UPDATE @@TaxonNode@@ " +
-                " SET unplaced = 1 " +
-                " WHERE id IN (SELECT id FROM "
-                +       "(SELECT tn.id FROM @@TaxonBase@@ tb  INNER JOIN @@TaxonNode@@ tn ON tn.taxon_id = tb.id  WHERE tb.unplaced = 1)"
-                + " as drvTbl)";
         query = "UPDATE @@TaxonNode@@ tn " +
                 " SET unplaced = (SELECT unplaced FROM @@TaxonBase@@ tb WHERE tb.id = tn.taxon_id)";
-
         simpleStep = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, query, "TaxonNode", -99)
                 .addDefaultAuditing("TaxonBase");
         stepList.add(simpleStep);
 
+        stepName = "Move unplaced from Taxon to TaxonNode/set null to false";
+        query = "UPDATE @@TaxonNode@@ SET unplaced = 0 WHERE unplaced IS NULL";
+        simpleStep = SimpleSchemaUpdaterStep.NewAuditedInstance(stepName, query, "TaxonNode", -99);
+        simpleStep.put(DatabaseTypeEnum.PostgreSQL, query.replace("0", "false"));
+        simpleStep.putAudited(DatabaseTypeEnum.PostgreSQL, query.replace("TaxonNode","TaxonNode_AUD").replace("0", "false"));
+        stepList.add(simpleStep);
+
+
         //#3925
         //remove unplaced from TaxonNode
         stepName = "Remove unplaced from TaxonBase";