From b7ee5fd35e91a68abe805e158333e9ca9ce09365 Mon Sep 17 00:00:00 2001 From: Laramie Crocker Date: Tue, 1 Feb 2011 20:42:22 +0000 Subject: [PATCH] CSPACE-2497 --- .../services/common/init/AddIndices.java | 97 ++++++++++++++----- .../services/common/init/InitHandler.java | 8 -- .../services/common/storage/JDBCTools.java | 33 +++---- 3 files changed, 88 insertions(+), 50 deletions(-) diff --git a/services/common/src/main/java/org/collectionspace/services/common/init/AddIndices.java b/services/common/src/main/java/org/collectionspace/services/common/init/AddIndices.java index 1a340fa82..f4491e6ef 100755 --- a/services/common/src/main/java/org/collectionspace/services/common/init/AddIndices.java +++ b/services/common/src/main/java/org/collectionspace/services/common/init/AddIndices.java @@ -17,6 +17,7 @@ */ package org.collectionspace.services.common.init; +import org.collectionspace.services.common.Tools; 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; @@ -29,6 +30,42 @@ import java.util.List; /** * AddIndices, post-init action to add indexes to the database. + * + * In the configuration file, you may have sets of table names and column names, or you may use params to send a + * comma-separated list of column names and a table name. That is, both of these are equivalent: + * + * The single column per element version: + * + * + * <s:initHandler xmlns:s='http://collectionspace.org/services/common/service'> + <s:classname>org.collectionspace.services.common.init.AddIndices</s:classname> + <s:params> + <s:field> + <s:table>nuxeo.organizations_common</s:table> + <s:col>inAuthority</s:col> + </s:field> + <s:field> + <s:table>nuxeo.organizations_common</s:table> + <s:col>displayName</s:col> + </s:field> + <s:field> + <s:table>nuxeo.organizations_common</s:table> + <s:col>shortIdentifier</s:col> + </s:field> + </s:params> + </s:initHandler> + + The csv version: + + <s:initHandler xmlns:s='http://collectionspace.org/services/common/service'> + <s:classname>org.collectionspace.services.common.init.AddIndices</s:classname> + <s:params> + <s:field> + <s:table>nuxeo.organizations_common</s:table> + <s:param>inAuthority,displayName,shortIdentifier</s:param> + </s:field> + </s:params> + </s:initHandler> * * $LastChangedRevision: $ * $LastChangedDate: $ @@ -38,38 +75,52 @@ public class AddIndices extends InitHandler implements IInitHandler { final Logger logger = LoggerFactory.getLogger(AddIndices.class); private final static String INDEX_SUFFIX = "_idx"; + /** See the class javadoc for this class: it shows the syntax supported in the configuration params. + */ @Override public void onRepositoryInitialized(ServiceBindingType sbt, List fields, List properties) throws Exception { //todo: all post-init tasks for services, or delegate to services that override. int rows = 0; String sql = ""; - if (logger.isInfoEnabled()) { - logger.info("Modifying field datatypes for " + sbt.getName() - + " for repository domain " + sbt.getRepositoryDomain().trim() + "..."); - } - DatabaseProductType databaseProductType = getDatabaseProductType(); + logger.info("Modifying field datatypes for " + sbt.getName() + + " for repository domain " + sbt.getRepositoryDomain().trim() + "..."); + for (Field field : fields) { - try { - // 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 " + field.getCol() + INDEX_SUFFIX + " ON " + field.getTable() + " (" + field.getCol() + ")"; - } else if (databaseProductType == DatabaseProductType.POSTGRESQL) { - sql = "CREATE INDEX ON " + field.getTable() + " (" + field.getCol() + ")"; - } else { - throw new Exception("Unrecognized database system " + databaseProductType); + String tableName = field.getTable(); + String fieldName = field.getCol(); + String param = field.getParam(); + if(Tools.notEmpty(param) && (param.indexOf(',')>-1)){ + String[] fieldNames = param.split(","); + for (String fn: fieldNames){ + addOneIndex(tableName, fn); } - rows = executeUpdate(sql); - } catch (Exception e) { - throw e; + } else { + addOneIndex(tableName, fieldName); } + } + } - //call something like this: services.common.storage.DBUtils.addIndex(String tablename, String fields[]); - //for every field that has an authRef, do ... - // --> Connection conn = getConnection(); - //see parameter that you need for adding indices to SQL. - + private int addOneIndex(String tableName, String columnName){ + int rows = 0; + String sql = ""; + try { + DatabaseProductType databaseProductType = 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 + ")"; + } else if (databaseProductType == DatabaseProductType.POSTGRESQL) { + 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+")"); + return rows; + } catch (Throwable e) { + logger.info("Index NOT added to column ("+columnName+") on table ("+tableName+") SQL: "+sql+" ERROR: "+e); + return -1; } } 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 9ab73478f..bd574ab6d 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 @@ -68,14 +68,6 @@ public class InitHandler implements IInitHandler { } } - public ResultSet executeQuery(String sql) throws Exception { - return JDBCTools.executeQuery(sql); - } - - public void closeResultSet(ResultSet rs) throws SQLException { - rs.close(); - } - public int executeUpdate(String sql) throws Exception { return JDBCTools.executeUpdate(sql); } 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 ae74f1644..6dab8730c 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 @@ -78,17 +78,12 @@ public class JDBCTools { ResultSet rs = stmt.executeQuery(sql); stmt.close(); return rs; //don't call rs.close() here ... Let caller close and catch any exceptions. - } catch (RuntimeException rte) { - logger.debug("Exception in createDefaultAccounts: " + rte.getLocalizedMessage()); - logger.debug(rte.getStackTrace().toString()); - throw rte; } catch (SQLException sqle) { SQLException tempException = sqle; while (null != tempException) { // SQLExceptions can be chained. Loop to log all. logger.debug("SQL Exception: " + sqle.getLocalizedMessage()); tempException = tempException.getNextException(); } - logger.debug(sqle.getStackTrace().toString()); throw new RuntimeException("SQL problem in executeQuery: ", sqle); } finally { try { @@ -120,12 +115,16 @@ public class JDBCTools { throw rte; } catch (SQLException sqle) { SQLException tempException = sqle; + String msg = ""; while (null != tempException) { // SQLExceptions can be chained. Loop to log all. - logger.debug("SQL Exception: " + sqle.getLocalizedMessage()); + if (! msg.isEmpty()){ + msg = msg +"::next::"; + } + msg = msg + sqle.getLocalizedMessage(); + logger.debug("SQL Exception: " + msg); tempException = tempException.getNextException(); } - logger.debug(sqle.getStackTrace().toString()); - throw new RuntimeException("SQL problem in executeUpdate: ", sqle); + throw new RuntimeException("SQL problem in executeUpdate: "+msg, sqle); } finally { try { if (conn != null) { @@ -163,17 +162,13 @@ public class JDBCTools { public static DatabaseProductType getDatabaseProductType() throws Exception { DatabaseProductType productType = DatabaseProductType.UNRECOGNIZED; - try { - 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); - } - } catch (Exception e) { - throw e; + 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; } -- 2.47.3