From 418ab4fcf8039c1e69a2f09606e621f0df563c25 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Andreas=20M=C3=BCller?= Date: Mon, 12 Aug 2013 14:36:12 +0000 Subject: [PATCH] update script for tree indices (#2947) --- .gitattributes | 1 + .../cdm/database/update/TreeIndexUpdater.java | 112 ++++++++++++++++++ .../update/v31_33/SchemaUpdater_31_33.java | 28 ++++- 3 files changed, 135 insertions(+), 6 deletions(-) create mode 100644 cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/TreeIndexUpdater.java diff --git a/.gitattributes b/.gitattributes index 038d40922e..2a16b552a1 100644 --- a/.gitattributes +++ b/.gitattributes @@ -1266,6 +1266,7 @@ cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/TableDroper.ja cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/TableNameChanger.java -text cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/TermRepresentationUpdater.java -text cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/TermUpdaterBase.java -text +cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/TreeIndexUpdater.java -text cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/UniqueIndexDropper.java -text cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/UpdaterBase.java -text cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/VocabularyCreator.java -text diff --git a/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/TreeIndexUpdater.java b/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/TreeIndexUpdater.java new file mode 100644 index 0000000000..dbe9ab41dc --- /dev/null +++ b/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/TreeIndexUpdater.java @@ -0,0 +1,112 @@ +// $Id$ +/** +* Copyright (C) 2009 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; + +import java.sql.SQLException; + +import org.apache.log4j.Logger; + +import eu.etaxonomy.cdm.common.monitor.IProgressMonitor; +import eu.etaxonomy.cdm.database.ICdmDataSource; + +/** + * @author a.mueller + * @date 09.08.2013 + * + */ +public class TreeIndexUpdater extends SchemaUpdaterStepBase implements ISchemaUpdaterStep { + private static final Logger logger = Logger.getLogger(TreeIndexUpdater.class); + + private String tableName; + private String indexColumnName = "treeIndex"; + private boolean includeAudTable; + private String treeIdColumnName; + private String parentIdColumnName = "parent_id"; + + public static final TreeIndexUpdater NewInstance(String stepName, String tableName, String treeIdColumnName, boolean includeAudTable){ + return new TreeIndexUpdater(stepName, tableName, treeIdColumnName, null, includeAudTable); + } + + public static final TreeIndexUpdater NewInstance(String stepName, String tableName, String treeIdColumnName, String indexColumnName, boolean includeAudTable){ + return new TreeIndexUpdater(stepName, tableName, treeIdColumnName, indexColumnName, includeAudTable); + } + + + protected TreeIndexUpdater(String stepName, String tableName, String treeIdColumnName, String indexColumnName, boolean includeAudTable) { + super(stepName); + this.tableName = tableName; + this.treeIdColumnName = treeIdColumnName; + this.indexColumnName = indexColumnName == null ? this.indexColumnName : indexColumnName; + this.includeAudTable = includeAudTable; + } + + @Override + public Integer invoke(ICdmDataSource datasource, IProgressMonitor monitor) throws SQLException { + boolean result = true; + result &= indexTable(tableName, datasource, monitor); + if (includeAudTable){ + String aud = "_AUD"; + result &= indexTable(tableName + aud, datasource, monitor); + } + return (result == true )? 0 : null; + } + + private boolean indexTable(String tableName, ICdmDataSource datasource, IProgressMonitor monitor) throws SQLException { + boolean result = true; + +// String charType = "CHAR"; //TODO may depend on database type + + //clean up //this should not happen with correct "delete" implementation + String sql = String.format(" DELETE FROM %s WHERE %s IS NULL ", tableName, treeIdColumnName); + datasource.executeUpdate(sql); + + sql = String.format(" UPDATE %s SET %s = NULL", tableName, indexColumnName); + datasource.executeUpdate(sql); + + //start + sql = String.format(" UPDATE %s tn " + + " SET tn.%s = CONCAT('#c', tn.%s, '#') " + + " WHERE tn.%s IS NULL AND tn.%s IS NOT NULL ", + tableName, indexColumnName, treeIdColumnName, parentIdColumnName, treeIdColumnName); + datasource.executeUpdate(sql); + + //width search index creation + String sqlCount = String.format(" SELECT count(*) as n " + + " FROM %s child INNER JOIN %s parent ON child.%s = parent.id " + + " WHERE parent.%s IS NOT NULL AND child.%s IS NULL ", + tableName, tableName, parentIdColumnName, indexColumnName, indexColumnName); + + Long n; + do { + + sql = String.format(" UPDATE %s child INNER JOIN %s parent ON child.%s = parent.id " + + " SET child.%s = CONCAT( parent.%s, child.id, '#') " + + " WHERE parent.%s IS NOT NULL AND child.%s IS NULL ", + tableName, tableName, parentIdColumnName, indexColumnName, indexColumnName, + indexColumnName, indexColumnName); + datasource.executeUpdate(sql); + + n = (Long)datasource.getSingleValue(sqlCount); + } while (n > 0) ; + + sqlCount = String.format(" SELECT count(*) as n " + + " FROM %s " + + " WHERE %s IS NULL ", tableName, indexColumnName); + n = (Long)datasource.getSingleValue(sqlCount); + if (n > 0){ + String message = "There are tree nodes with no tree index in %s. This indicates that there is a problem in the tree structure of 1 or more classifications."; + logger.error(String.format(message, tableName)); + } + + return result; + } + + +} 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 44e529ea4b..940fef2bb8 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 @@ -28,6 +28,7 @@ import eu.etaxonomy.cdm.database.update.SchemaUpdaterBase; import eu.etaxonomy.cdm.database.update.SimpleSchemaUpdaterStep; import eu.etaxonomy.cdm.database.update.TableCreator; import eu.etaxonomy.cdm.database.update.TableDroper; +import eu.etaxonomy.cdm.database.update.TreeIndexUpdater; 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; @@ -90,6 +91,11 @@ public class SchemaUpdater_31_33 extends SchemaUpdaterBase { @Override protected List getUpdaterList() { + String stepName; + String tableName; + ISchemaUpdaterStep step; + String columnName; + //CHECKS //remove SpecimenOrObservationBase_Media #3597 @@ -106,15 +112,15 @@ public class SchemaUpdater_31_33 extends SchemaUpdaterBase { //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 - String stepName = "Drop duplicate TypeDesignation-TaxonName table"; - String tableName = "TypeDesignationBase_TaxonNameBase"; - ISchemaUpdaterStep step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT); + stepName = "Drop duplicate TypeDesignation-TaxonName table"; + tableName = "TypeDesignationBase_TaxonNameBase"; + step = TableDroper.NewInstance(stepName, tableName, INCLUDE_AUDIT); stepList.add(step); //create original source type column stepName = "Create original source type column"; tableName = "OriginalSourceBase"; - String columnName = "sourceType"; + columnName = "sourceType"; step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 4, INCLUDE_AUDIT); ((ColumnAdder)step).setNotNull(true); stepList.add(step); @@ -133,7 +139,12 @@ public class SchemaUpdater_31_33 extends SchemaUpdaterBase { step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT); stepList.add(step); - //FIXME update tree index + //update treeindex for taxon nodes + stepName = "Update TaxonNode treeindex"; + tableName = "TaxonNode"; + String treeIdColumnName = "classification_id"; + step = TreeIndexUpdater.NewInstance(stepName, tableName, treeIdColumnName, columnName, INCLUDE_AUDIT); + stepList.add(step); //create TaxonNode sort index column stepName = "Create taxon node sort index column"; @@ -154,7 +165,12 @@ public class SchemaUpdater_31_33 extends SchemaUpdaterBase { step = ColumnAdder.NewStringInstance(stepName, tableName, columnName, 255, INCLUDE_AUDIT); stepList.add(step); - //FIXME 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); + stepList.add(step); //update introduced: adventitious (casual) label //#3540 -- 2.34.1