From 017ba22587303920b76066e40ea6a4d8ce8a3b17 Mon Sep 17 00:00:00 2001 From: Aron Roberts Date: Sat, 5 Feb 2011 00:32:26 +0000 Subject: [PATCH] CSPACE-2497: A post-init task now creates indexes on inAuthority (where relevant), displayName and shortIdentifier fields in all vocabulary and authority fields. Some minor tweaks to JDBC usage within commit.init. No longer configures displayname in locations_common as LARGETEXT, as this field is now indexed. --- .../config/default-repository-config.xml | 1 - .../main/config/services/tenant-bindings.xml | 242 +++++++++++++++++- .../services/common/init/AddIndices.java | 84 +++++- .../services/common/init/InitHandler.java | 9 +- .../common/init/ModifyFieldDatatypes.java | 5 +- .../common/storage/DatabaseProductType.java | 6 +- .../services/common/storage/JDBCTools.java | 30 +-- 7 files changed, 340 insertions(+), 37 deletions(-) diff --git a/3rdparty/nuxeo/nuxeo-database-templates/mysql/collectionspace_mysql/config/default-repository-config.xml b/3rdparty/nuxeo/nuxeo-database-templates/mysql/collectionspace_mysql/config/default-repository-config.xml index 3cc77ca22..85ed84cd9 100644 --- a/3rdparty/nuxeo/nuxeo-database-templates/mysql/collectionspace_mysql/config/default-repository-config.xml +++ b/3rdparty/nuxeo/nuxeo-database-templates/mysql/collectionspace_mysql/config/default-repository-config.xml @@ -338,7 +338,6 @@ - locations_common:displayName locations_common:name locations_common:conditionNote diff --git a/services/common/src/main/config/services/tenant-bindings.xml b/services/common/src/main/config/services/tenant-bindings.xml index cb39e0bff..b4a7f89c2 100644 --- a/services/common/src/main/config/services/tenant-bindings.xml +++ b/services/common/src/main/config/services/tenant-bindings.xml @@ -748,6 +748,19 @@ org.collectionspace.services.vocabulary.nuxeo.VocabularyValidatorHandler + + org.collectionspace.services.common.init.AddIndices + + + nuxeo.vocabularies_common + displayname + + + nuxeo.vocabularies_common + shortidentifier + + + org.collectionspace.services.vocabulary.nuxeo.VocabularyItemValidatorHandler + + org.collectionspace.services.common.init.AddIndices + + + nuxeo.vocabularyitems_common + inauthority + + + nuxeo.vocabularyitems_common + displayname + + + nuxeo.vocabularyitems_common + shortidentifier + + + org.collectionspace.services.organization.nuxeo.OrgAuthorityValidatorHandler + + org.collectionspace.services.common.init.AddIndices + + + nuxeo.orgauthorities_common + displayname + + + nuxeo.orgauthorities_common + shortidentifier + + + org.collectionspace.services.organization.nuxeo.OrganizationValidatorHandler + + org.collectionspace.services.common.init.AddIndices + + + nuxeo.organizations_common + inauthority + + + nuxeo.organizations_common + displayname + + + nuxeo.organizations_common + shortidentifier + + + org.collectionspace.services.person.nuxeo.PersonAuthorityValidatorHandler + + org.collectionspace.services.common.init.AddIndices + + + nuxeo.personauthorities_common + displayname + + + nuxeo.personauthorities_common + shortidentifier + + + org.collectionspace.services.person.nuxeo.PersonValidatorHandler + + org.collectionspace.services.common.init.AddIndices + + + nuxeo.persons_common + inauthority + + + nuxeo.persons_common + displayname + + + nuxeo.persons_common + shortidentifier + + + org.collectionspace.services.location.nuxeo.LocationAuthorityDocumentModelHandler + + org.collectionspace.services.common.init.AddIndices + + + nuxeo.locationauthorities_common + displayname + + + nuxeo.locationauthorities_common + shortidentifier + + + org.collectionspace.services.location.nuxeo.LocationValidatorHandler + + org.collectionspace.services.common.init.AddIndices + + + nuxeo.locations_common + inauthority + + + nuxeo.locations_common + displayname + + + nuxeo.locations_common + shortidentifier + + + - @@ -2353,6 +2472,19 @@ org.collectionspace.services.vocabulary.nuxeo.VocabularyDocumentModelHandler + + org.collectionspace.services.common.init.AddIndices + + + nuxeo.vocabularies_common + displayname + + + nuxeo.vocabularies_common + shortidentifier + + + org.collectionspace.services.vocabulary.nuxeo.VocabularyItemValidatorHandler + + org.collectionspace.services.common.init.AddIndices + + + nuxeo.vocabularyitems_common + inauthority + + + nuxeo.vocabularyitems_common + displayname + + + nuxeo.vocabularyitems_common + shortidentifier + + + org.collectionspace.services.organization.nuxeo.OrgAuthorityValidatorHandler + + org.collectionspace.services.common.init.AddIndices + + + nuxeo.orgauthorities_common + displayname + + + nuxeo.orgauthorities_common + shortidentifier + + + org.collectionspace.services.organization.nuxeo.OrganizationValidatorHandler + + org.collectionspace.services.common.init.AddIndices + + + nuxeo.organizations_common + inauthority + + + nuxeo.organizations_common + displayname + + + nuxeo.organizations_common + shortidentifier + + + authRefcontactNames|contactName authRefsubBodies|subBody - org.collectionspace.services.person.nuxeo.PersonAuthorityValidatorHandler + + org.collectionspace.services.common.init.AddIndices + + + nuxeo.personauthorities_common + displayname + + + nuxeo.personauthorities_common + shortidentifier + + + org.collectionspace.services.person.nuxeo.PersonValidatorHandler + + org.collectionspace.services.common.init.AddIndices + + + nuxeo.persons_common + inauthority + + + nuxeo.persons_common + displayname + + + nuxeo.persons_common + shortidentifier + + + org.collectionspace.services.location.nuxeo.LocationAuthorityDocumentModelHandler + + org.collectionspace.services.common.init.AddIndices + + + nuxeo.locationauthorities_common + displayname + + + nuxeo.locationauthorities_common + shortidentifier + + + org.collectionspace.services.location.nuxeo.LocationValidatorHandler + + org.collectionspace.services.common.init.AddIndices + + + nuxeo.locations_common + inauthority + + + nuxeo.locations_common + displayname + + + nuxeo.locations_common + shortidentifier + + + -1)){ String[] fieldNames = param.split(","); for (String fn: fieldNames){ - addOneIndex(tableName, fn); + rows = addOneIndex(tableName, fn); } } else { - addOneIndex(tableName, fieldName); + rows = addOneIndex(tableName, fieldName); } } } @@ -103,20 +109,34 @@ public class AddIndices extends InitHandler implements IInitHandler { private int addOneIndex(String tableName, String columnName){ int rows = 0; String sql = ""; + String indexName = columnName + INDEX_SUFFIX; try { - DatabaseProductType databaseProductType = getDatabaseProductType(); + DatabaseProductType databaseProductType = JDBCTools.getDatabaseProductType(); // TODO: Consider refactoring this 'if' statement to a general-purpose // mechanism for retrieving and populating catalog/DDL-type SQL statements // appropriate to a particular database product. if (databaseProductType == DatabaseProductType.MYSQL) { - sql = "CREATE INDEX " + columnName + INDEX_SUFFIX + " ON " + tableName + " (" + columnName + ")"; + // If the index already exists, do nothing. + if (indexExists(databaseProductType, tableName, indexName)) { + // FIXME: Can add the option to drop and re-create an index here. + // See MySQL documentation on DROP INDEX. + } else { + sql = "CREATE INDEX " + indexName + " ON " + tableName + " (" + columnName + ")"; + } } else if (databaseProductType == DatabaseProductType.POSTGRESQL) { - sql = "CREATE INDEX ON " + tableName + " (" + columnName + ")"; + if (indexExists(databaseProductType, tableName, indexName)) { + // FIXME: Can add the option to reindex an existing index here. + // See PostgreSQL documentation on REINDEX. + } else { + sql = "CREATE INDEX ON " + tableName + " (" + columnName + ")"; + } } else { throw new Exception("Unrecognized database system " + databaseProductType); } - rows = executeUpdate(sql); - logger.info("Index added to column ("+columnName+") on table ("+tableName+")"); + if (sql != null && ! sql.trim().isEmpty()) { + rows = JDBCTools.executeUpdate(sql); + logger.info("Index added to column ("+columnName+") on table ("+tableName+")"); + } return rows; } catch (Throwable e) { logger.info("Index NOT added to column ("+columnName+") on table ("+tableName+") SQL: "+sql+" ERROR: "+e); @@ -124,4 +144,52 @@ public class AddIndices extends InitHandler implements IInitHandler { } } + private boolean indexExists(DatabaseProductType databaseProductType, + String tableName, String indexName) { + boolean indexExists = false; + int rows = 0; + String sql = ""; + Connection conn = null; + Statement stmt = null; + ResultSet rs = null; + try { + if (databaseProductType == DatabaseProductType.MYSQL) { + sql = "SHOW INDEX FROM " + tableName + " WHERE key_name='" + + indexName + "'"; + conn = JDBCTools.getConnection(JDBCTools.getDefaultRepositoryName()); + stmt = conn.createStatement(); + rs = stmt.executeQuery(sql); + if (rs.last()) { + rows = rs.getRow(); + } + rs.close(); + stmt.close(); + conn.close(); + if (rows > 0) { + indexExists = true; + } + } else if (databaseProductType == DatabaseProductType.POSTGRESQL) { + // FIXME: Add comparable logic for PostgreSQL. + } + } catch (Exception e) { + logger.debug("Error when identifying whether index exists in table " + + tableName + ":" + e.getMessage()); + } finally { + try { + if (rs != null) { + conn.close(); + } + if (conn != null) { + conn.close(); + } + if (stmt != null) { + stmt.close(); + } + } catch (SQLException sqle) { + logger.debug("SQL Exception closing statement/connection in AddIndices: " + sqle.getLocalizedMessage()); + } + } + return indexExists; + } + } diff --git a/services/common/src/main/java/org/collectionspace/services/common/init/InitHandler.java b/services/common/src/main/java/org/collectionspace/services/common/init/InitHandler.java index bd574ab6d..f97a56a9c 100755 --- a/services/common/src/main/java/org/collectionspace/services/common/init/InitHandler.java +++ b/services/common/src/main/java/org/collectionspace/services/common/init/InitHandler.java @@ -26,7 +26,7 @@ import org.collectionspace.services.common.service.InitHandler.Params.Property; import org.slf4j.Logger; import org.slf4j.LoggerFactory; -import java.sql.*; +import java.sql.ResultSet; import java.util.List; /** Concrete class which does nothing, but subclasses may override to do @@ -68,11 +68,4 @@ public class InitHandler implements IInitHandler { } } - public int executeUpdate(String sql) throws Exception { - return JDBCTools.executeUpdate(sql); - } - - public DatabaseProductType getDatabaseProductType() throws Exception { - return JDBCTools.getDatabaseProductType(); - } } diff --git a/services/common/src/main/java/org/collectionspace/services/common/init/ModifyFieldDatatypes.java b/services/common/src/main/java/org/collectionspace/services/common/init/ModifyFieldDatatypes.java index c0fcea3d7..0e72c503d 100644 --- a/services/common/src/main/java/org/collectionspace/services/common/init/ModifyFieldDatatypes.java +++ b/services/common/src/main/java/org/collectionspace/services/common/init/ModifyFieldDatatypes.java @@ -27,6 +27,7 @@ import org.collectionspace.services.common.service.ServiceBindingType; import org.collectionspace.services.common.service.InitHandler.Params.Field; import org.collectionspace.services.common.service.InitHandler.Params.Property; import org.collectionspace.services.common.storage.DatabaseProductType; +import org.collectionspace.services.common.storage.JDBCTools; import org.slf4j.Logger; import org.slf4j.LoggerFactory; @@ -51,7 +52,7 @@ public class ModifyFieldDatatypes extends InitHandler implements IInitHandler { + " for repository domain " + sbt.getRepositoryDomain().trim() + "..."); } try { - DatabaseProductType databaseProductType = getDatabaseProductType(); + DatabaseProductType databaseProductType = JDBCTools.getDatabaseProductType(); String datatype = ""; for (Field field : fields) { datatype = getDatatypeFromLogicalType(databaseProductType, field.getType()); @@ -65,7 +66,7 @@ public class ModifyFieldDatatypes extends InitHandler implements IInitHandler { } else { throw new Exception("Unrecognized database system."); } - rows = executeUpdate(sql); + rows = JDBCTools.executeUpdate(sql); } } catch (Exception e) { throw e; diff --git a/services/common/src/main/java/org/collectionspace/services/common/storage/DatabaseProductType.java b/services/common/src/main/java/org/collectionspace/services/common/storage/DatabaseProductType.java index 20adc5550..a76a19601 100644 --- a/services/common/src/main/java/org/collectionspace/services/common/storage/DatabaseProductType.java +++ b/services/common/src/main/java/org/collectionspace/services/common/storage/DatabaseProductType.java @@ -32,9 +32,13 @@ public class DatabaseProductType { propertiesFileName = name + PROPERTIES_FILE_SUFFIX; } + public String getName() { + return name; + } + @Override public String toString() { - return name; + return getName(); } public String getPropertiesFileName() { diff --git a/services/common/src/main/java/org/collectionspace/services/common/storage/JDBCTools.java b/services/common/src/main/java/org/collectionspace/services/common/storage/JDBCTools.java index 6dab8730c..2b9e9230d 100755 --- a/services/common/src/main/java/org/collectionspace/services/common/storage/JDBCTools.java +++ b/services/common/src/main/java/org/collectionspace/services/common/storage/JDBCTools.java @@ -117,14 +117,14 @@ public class JDBCTools { SQLException tempException = sqle; String msg = ""; while (null != tempException) { // SQLExceptions can be chained. Loop to log all. - if (! msg.isEmpty()){ - msg = msg +"::next::"; + if (!msg.isEmpty()) { + msg = msg + "::next::"; } msg = msg + sqle.getLocalizedMessage(); logger.debug("SQL Exception: " + msg); tempException = tempException.getNextException(); } - throw new RuntimeException("SQL problem in executeUpdate: "+msg, sqle); + throw new RuntimeException("SQL problem in executeUpdate: " + msg, sqle); } finally { try { if (conn != null) { @@ -159,19 +159,19 @@ public class JDBCTools { } return productName; } - - public static DatabaseProductType getDatabaseProductType() throws Exception { - DatabaseProductType productType = DatabaseProductType.UNRECOGNIZED; - String productName = getDatabaseProductName(); - if (productName.matches("(?i).*mysql.*")) { - productType = DatabaseProductType.MYSQL; - } else if (productName.matches("(?i).*postgresql.*")) { - productType = DatabaseProductType.POSTGRESQL; - } else { - throw new Exception("Unrecognized database system " + productName); - } - return productType; + + public static DatabaseProductType getDatabaseProductType() throws Exception { + DatabaseProductType productType = DatabaseProductType.UNRECOGNIZED; + String productName = getDatabaseProductName(); + if (productName.matches("(?i).*mysql.*")) { + productType = DatabaseProductType.MYSQL; + } else if (productName.matches("(?i).*postgresql.*")) { + productType = DatabaseProductType.POSTGRESQL; + } else { + throw new Exception("Unrecognized database system " + productName); } + return productType; + } public static String getDefaultRepositoryName() { return ServiceMain.DEFAULT_REPOSITORY_NAME; -- 2.47.3