From 34302072dc361883aa219e2bad4ac068e36ad1c2 Mon Sep 17 00:00:00 2001 From: remillet Date: Thu, 5 May 2016 21:24:32 -0700 Subject: [PATCH] CSPACE-6949: Adding code to create unique constraint on shortIdenifier column of auth and vocab tables. --- .../AuthorizationCommon.java | 2 +- .../services/common/init/AddIndices.java | 185 ++++++++++++++++-- .../services/common/storage/JDBCTools.java | 6 +- 3 files changed, 173 insertions(+), 20 deletions(-) diff --git a/services/common/src/main/java/org/collectionspace/services/common/authorization_mgt/AuthorizationCommon.java b/services/common/src/main/java/org/collectionspace/services/common/authorization_mgt/AuthorizationCommon.java index 674f75053..e7e99b425 100644 --- a/services/common/src/main/java/org/collectionspace/services/common/authorization_mgt/AuthorizationCommon.java +++ b/services/common/src/main/java/org/collectionspace/services/common/authorization_mgt/AuthorizationCommon.java @@ -1039,7 +1039,7 @@ public class AuthorizationCommon { if (result == null) { if (serviceBinding.getType().equalsIgnoreCase(ServiceBindingUtils.SERVICE_TYPE_SECURITY) == false) { - logger.warn("Could not retrieve a lifecycle transition definition list from: " + logger.debug("Could not retrieve a lifecycle transition definition list from: " + serviceBinding.getName() + " with tenant ID = " + tenantBinding.getId()); 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 f4c26f6ce..d4e0b1d31 100644 --- 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 @@ -25,6 +25,7 @@ import java.util.List; import javax.sql.DataSource; +import org.collectionspace.services.client.AuthorityClient; import org.collectionspace.services.common.api.Tools; import org.collectionspace.services.common.context.ServiceBindingUtils; import org.collectionspace.services.common.storage.DatabaseProductType; @@ -33,6 +34,7 @@ import org.collectionspace.services.config.service.InitHandler.Params.Field; import org.collectionspace.services.config.service.InitHandler.Params.Property; import org.collectionspace.services.config.service.ObjectPartType; import org.collectionspace.services.config.service.ServiceBindingType; +import org.mortbay.log.Log; import org.slf4j.Logger; import org.slf4j.LoggerFactory; @@ -83,6 +85,7 @@ public class AddIndices extends InitHandler implements IInitHandler { final Logger logger = LoggerFactory.getLogger(AddIndices.class); private final static String INDEX_SEP = "_"; private final static String INDEX_SUFFIX = INDEX_SEP + "idx"; + private final static String SHORT_ID = AuthorityClient.SHORT_IDENTIFIER.toLowerCase(); /** See the class javadoc for this class: it shows the syntax supported in the configuration params. @@ -127,38 +130,188 @@ public class AddIndices extends InitHandler implements IInitHandler { } /** + * Checks to see if the uniqueness constraint already exists on this table. * - * Add a DB level uniqueness constraint on the short ID column of this service's common part table. + * @param dataSourceName + * @param repositoryName + * @param cspaceInstanceId + * @param tableName + * @return + * @throws Exception + */ + private boolean shortIdConstraintExists(String dataSourceName, + String repositoryName, + String cspaceInstanceId, + String tableName) throws Exception { + boolean result = false; + + // + // e.g., SELECT constraint_name FROM information_schema.constraint_column_usage WHERE table_name = 'persons_common' AND constraint_name = 'persons_shortid_unique'; + // + String sql; + DatabaseProductType databaseProductType = JDBCTools.getDatabaseProductType(dataSourceName, repositoryName); + if (databaseProductType == DatabaseProductType.POSTGRESQL) { + String constraintName = String.format("%s_%s_unique", tableName, SHORT_ID); + sql = String.format("SELECT constraint_name FROM information_schema.constraint_column_usage WHERE table_name = '%s' AND constraint_name = '%s'", + tableName, constraintName); + } else { + String errorMsg = String.format("Database server type '%s' is not supported by CollectionSpace. Could not create constraint on column '%s' of table '%s'.", + databaseProductType.getName(), SHORT_ID, tableName); + logger.error(errorMsg); + throw new Exception(errorMsg); + } + + Connection conn = null; + Statement stmt = null; + ResultSet rs = null; + try { + conn = JDBCTools.getConnection(dataSourceName, repositoryName, cspaceInstanceId); + stmt = conn.createStatement(); + rs = stmt.executeQuery(sql); + if (rs.next()) { + result = true; + } + } catch (Exception e) { + String errorMsg = e.getLocalizedMessage(); + logger.error(String.format("Error when identifying whether constraint on column '%s' exists in table '%s': %s", + SHORT_ID, tableName, e != null ? e : "Unknown error.")); + throw e; // rethrow it. + } finally { + try { + if (rs != null) { + rs.close(); + } + if (stmt != null) { + stmt.close(); + } + if (conn != null) { + conn.close(); + } + } catch (SQLException sqle) { + logger.error("SQL Exception closing statement/connection in AddIndices: " + sqle.getLocalizedMessage()); + } + } + + return result; + } + + private boolean createShortIdConstraint(String dataSourceName, + String repositoryName, + String cspaceInstanceId, + String tableName) { + boolean result = false; + + String errorMsg = null; + try { + String sql; + DatabaseProductType databaseProductType = JDBCTools.getDatabaseProductType(dataSourceName, repositoryName); + if (databaseProductType == DatabaseProductType.POSTGRESQL) { + String constraintName = String.format("%s_%s_unique", tableName, SHORT_ID); + sql = String.format("ALTER TABLE %s add CONSTRAINT %s UNIQUE (%s)", + tableName, constraintName, SHORT_ID); + } else { + errorMsg = String.format("Database server type '%s' is not supported by CollectionSpace. Could not create constraint on column '%s' of table '%s'.", + databaseProductType.getName(), SHORT_ID, tableName); + throw new Exception(errorMsg); + } + + // + // e.g., ALTER TABLE persons_common add CONSTRAINT persons_shortid_unique UNIQUE (shortidentifier); + // + + try { + int rowsCreated = JDBCTools.executeUpdate(dataSourceName, repositoryName, cspaceInstanceId, sql); // This should return '0' since ALTER statements don't return row counts + if (rowsCreated != 0) { + throw new Exception(String.format("No rows created on SQL update: %s", sql)); + } else { + result = true; + } + } catch (SQLException sqle) { + String errorState = sqle.getSQLState(); + if (errorState != null && errorState.equals(JDBCTools.POSTGRES_UNIQUE_VIOLATION)) { + errorMsg = String.format("*** WARNING *** - The value of the '%s' column in the '%s' table of the '%s' repository should be unique, but is not! Therefore, " + + "we cannot create the NECESSARY database constraint. Please remove the duplicate '%s' value(s) and restart CollectionSpace.", + SHORT_ID, tableName, repositoryName, SHORT_ID); + } else { + errorMsg = String.format("Unexpected %s error=%s : %s", databaseProductType.getName(), errorState, sqle.getLocalizedMessage()); + } + } catch (Exception e) { + errorMsg = e.getLocalizedMessage(); + } + } catch (Exception e) { + errorMsg = e.getLocalizedMessage(); + } + // + // If we failed to create the constraint, log the reason. + // + if (result == false) { + if (errorMsg != null) { + logger.error(errorMsg); + } + logger.error(String.format("*** ERROR *** Encountered problems when trying to create a uniqueness constraint on column '%s' of table '%s' in repository '%s'.", + SHORT_ID, tableName, repositoryName)); + } else { + Log.debug(String.format("Created a uniqueness constraint on column '%s' of table '%s' in repository '%s'.", + SHORT_ID, tableName, repositoryName)); + } + + return result; + } + + /** + * + * Ensure a database level uniqueness constraint exists on the "shortIdentifier" column of this service's common part table. * * @param dataSourceName * @param repositoryName * @param cspaceInstanceId * @param sbt + * @throws Exception */ private void ensureShortIdConstraintOnAuthority(String dataSourceName, String repositoryName, String cspaceInstanceId, ServiceBindingType sbt) { - -// #TEST: -// SELECT constraint_name FROM information_schema.constraint_column_usage WHERE table_name = 'persons_common' AND constraint_name = 'persons_shortid_unique'; -// -// #IF: emptyResult -// ALTER TABLE persons_common add CONSTRAINT persons_shortid_unique UNIQUE (shortidentifier); - String tableName = null; + String errMessage = null; - List objectPartTypes = sbt.getObject().getPart(); - for (ObjectPartType objectPartType : objectPartTypes) { - if (objectPartType.getId().equalsIgnoreCase(ServiceBindingUtils.SERVICE_COMMONPART_ID) == true) { - tableName = objectPartType.getLabel(); - } + try { + // + // Find the common part table name for this service. It's the one with the short ID column + // + List objectPartTypes = sbt.getObject().getPart(); + for (ObjectPartType objectPartType : objectPartTypes) { + if (objectPartType.getId().equalsIgnoreCase(ServiceBindingUtils.SERVICE_COMMONPART_ID) == true) { + tableName = objectPartType.getLabel(); + break; + } + } + + // + // Get an error message ready in case we hit trouble. + // + errMessage = String.format("*** IMPORTANT *** - Encountered problems trying to ensure a uniqueness constraint exists for the '%s' column of table '%s' in repository '%s'. Check the CollectionSpace services logs for details.", + SHORT_ID, tableName, repositoryName); + // + // If the constraint doesn't exist, create it. + // + if (shortIdConstraintExists(dataSourceName, repositoryName, cspaceInstanceId, tableName) == false) { + if (createShortIdConstraint(dataSourceName, repositoryName, cspaceInstanceId, tableName) == true) { + logger.info(String.format("Created uniqueness constraint on '%s' column of table '%s' in repository '%s'.", + SHORT_ID, tableName, repositoryName)); + } else { + logger.error(errMessage); + } + } else { + logger.debug(String.format("Uniqueness constraint already exists on '%s' column of table '%s' in repository '%s'.", + SHORT_ID, tableName, repositoryName)); + } + } catch (Exception e) { + logger.error(errMessage); } - System.out.println(String.format("Added uniqueness constraint on short ID of repo:%s%s service:%s tablename:%s", - repositoryName, cspaceInstanceId, sbt.getName(), tableName)); } - private int addOneIndex(String dataSourceName, + private int addOneIndex(String dataSourceName, String repositoryName, String cspaceInstanceId, String tableName, 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 638fe5904..13fa3b3b9 100644 --- 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 @@ -63,8 +63,8 @@ public class JDBCTools { public static String NUXEO_USER_NAME = "nuxeo"; public static String SQL_WILDCARD = "%"; public static String DATABASE_SELECT_PRIVILEGE_NAME = "SELECT"; + public static String POSTGRES_UNIQUE_VIOLATION = "23505"; - // // Private constants // @@ -352,7 +352,7 @@ public class JDBCTools { } catch (SQLException sqle) { SQLException tempException = sqle; String msg = ""; - while (null != tempException) { // SQLExceptions can be chained. Loop to log all. + while (tempException != null) { // SQLExceptions can be chained. Loop to log all. if (!msg.isEmpty()) { msg = msg + "::next::"; } @@ -360,7 +360,7 @@ public class JDBCTools { logger.debug("SQL Exception: " + msg); tempException = tempException.getNextException(); } - throw new RuntimeException("SQL Exception in executeUpdate: " + msg, sqle); + throw sqle; // rethrow the exception } finally { try { if (stmt != null) { -- 2.47.3