From 9510f2342c94983d11f9819cc1332c52bf4743ef Mon Sep 17 00:00:00 2001 From: Aron Roberts Date: Mon, 13 Jun 2011 20:16:37 +0000 Subject: [PATCH] CSPACE-4077: Creation of database indexes and modification of field datatypes during 'cspace' server startup / post-init now works once again, by specifying that we're connecting via JDBC to the nuxeo repository when performing these actions. --- .../services/common/ServiceMain.java | 4 +- .../services/common/init/AddIndices.java | 28 ++++++-- .../common/init/ModifyFieldDatatypes.java | 13 +++- .../services/common/storage/JDBCTools.java | 67 +++++++++++++++++-- 4 files changed, 100 insertions(+), 12 deletions(-) diff --git a/services/common/src/main/java/org/collectionspace/services/common/ServiceMain.java b/services/common/src/main/java/org/collectionspace/services/common/ServiceMain.java index 058bf3889..c0dd989d4 100644 --- a/services/common/src/main/java/org/collectionspace/services/common/ServiceMain.java +++ b/services/common/src/main/java/org/collectionspace/services/common/ServiceMain.java @@ -56,7 +56,9 @@ public class ServiceMain { private static final String DEFAULT_ADMIN_PASSWORD = "Administrator"; private static final String DEFAULT_READER_PASSWORD = "reader"; - public static String DEFAULT_REPOSITORY_NAME = "CspaceDS"; + public static final String NUXEO_REPOSITORY_NAME = "NuxeoDS"; + public static final String CSPACE_REPOSITORY_NAME = "CspaceDS"; + public static final String DEFAULT_REPOSITORY_NAME = CSPACE_REPOSITORY_NAME; private ServiceMain() { //empty 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 92ad73a34..529bc5f89 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 @@ -131,7 +131,16 @@ public class AddIndices extends InitHandler implements IInitHandler { throw new Exception("Unrecognized database system " + databaseProductType); } if (sql != null && ! sql.trim().isEmpty()) { - rows = JDBCTools.executeUpdate(sql); + // Assumes indicies will only be created at post-init time + // for the Nuxeo repository. + // + // To date, for the CSpace repository, indices have typically been + // created during the build process, via manual or generated + // DDL SQL scripts. + // + // If this assumption is no longer valid, we might instead + // identify the relevant repository from the table name here. + rows = JDBCTools.executeUpdate(sql, JDBCTools.getNuxeoRepositoryName()); logger.trace("Index added to column ("+columnName+") on table ("+tableName+")"); } return rows; @@ -147,8 +156,10 @@ public class AddIndices extends InitHandler implements IInitHandler { // FIXME: May need to qualify table name by database/catalog, // as table names likely will not be globally unique across same // (although index names *may* be unique within scope). - // - Pass in database name as parameter, retrieved via - // getDatabaseName(field) in onRepositoryInitialized, above. + // + // If we do need to do this, we might: + // - Pass in the database name as a parameter to this method, retrieved + // via getDatabaseName(field) in onRepositoryInitialized, above. // - Add 'IN databaseName' after tableName in MySQL variant, below. // (PostgreSQL variant, below, uses a view that doesn't include // a foreign key for associating a database/catalog to the index.) @@ -173,7 +184,16 @@ public class AddIndices extends InitHandler implements IInitHandler { } try { - conn = JDBCTools.getConnection(JDBCTools.getDefaultRepositoryName()); + // Assumes indicies will only be created at post-init time + // for the Nuxeo repository. + // + // To date, for the CSpace repository, indices have typically been + // created during the build process, via manual or generated + // DDL SQL scripts. + // + // If this assumption is no longer valid, we might instead + // identify the relevant repository from the table name here. + conn = JDBCTools.getConnection(JDBCTools.getNuxeoRepositoryName()); stmt = conn.createStatement(); rs = stmt.executeQuery(sql); if (rs.last()) { 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 9f9788a16..d2638f0df 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 @@ -82,7 +82,16 @@ public class ModifyFieldDatatypes extends InitHandler implements IInitHandler { } else { throw new Exception("Unrecognized database system."); } - rows = JDBCTools.executeUpdate(sql); + // Assumes field datatypes will only be modified at post-init + // time for the Nuxeo repository. + // + // To date, for the CSpace repository, field datatypes have + // typically been specified during the build process, via + // manual or generated DDL SQL scripts. + // + // If this assumption is no longer valid, we might instead + // identify the relevant repository from the table name here. + rows = JDBCTools.executeUpdate(sql, JDBCTools.getNuxeoRepositoryName()); } } catch (Exception e) { throw e; @@ -138,7 +147,7 @@ public class ModifyFieldDatatypes extends InitHandler implements IInitHandler { } try { - conn = JDBCTools.getConnection(JDBCTools.getDefaultRepositoryName()); + conn = JDBCTools.getConnection(JDBCTools.getNuxeoRepositoryName()); stmt = conn.createStatement(); rs = stmt.executeQuery(sql); while (rs.next()) { 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 4348ac9de..b29914a30 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 @@ -26,6 +26,7 @@ import javax.naming.InitialContext; import javax.naming.NamingException; import javax.security.auth.login.LoginException; import javax.sql.DataSource; +import java.sql.DatabaseMetaData; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; @@ -42,7 +43,7 @@ public class JDBCTools { final static Logger logger = LoggerFactory.getLogger(JDBCTools.class); public static Connection getConnection(String repositoryName) throws LoginException, SQLException { - if (Tools.isEmpty(repositoryName)) { + if (Tools.isBlank(repositoryName)) { repositoryName = getDefaultRepositoryName(); } InitialContext ctx = null; @@ -59,21 +60,31 @@ public class JDBCTools { LoginException le = new LoginException("Error looking up DataSource from: " + repositoryName); le.initCause(ex); throw le; + } catch (SQLException e) { + throw e; } finally { if (ctx != null) { try { ctx.close(); } catch (Exception e) { + // Do nothing with exception in 'finally' clause. } } } } - + public static ResultSet executeQuery(String sql) throws Exception { + return executeQuery(sql, getDefaultRepositoryName()); + } + + public static ResultSet executeQuery(String sql, String repositoryName) throws Exception { Connection conn = null; Statement stmt = null; try { - conn = getConnection(getDefaultRepositoryName()); + if (Tools.isBlank(repositoryName)) { + repositoryName = getDefaultRepositoryName(); + } + conn = getConnection(repositoryName); stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql); stmt.close(); @@ -99,12 +110,19 @@ public class JDBCTools { } } } - + public static int executeUpdate(String sql) throws Exception { + return executeUpdate(sql, getDefaultRepositoryName()); + } + + public static int executeUpdate(String sql, String repositoryName) throws Exception { Connection conn = null; Statement stmt = null; try { - conn = getConnection(getDefaultRepositoryName()); + if (Tools.isBlank(repositoryName)) { + repositoryName = getDefaultRepositoryName(); + } + conn = getConnection(repositoryName); stmt = conn.createStatement(); int rows = stmt.executeUpdate(sql); stmt.close(); @@ -140,6 +158,16 @@ public class JDBCTools { } } + /** + * Returns the database product name, from the metadata for a + * JDBC connection to the default repository. + * + * Assumes that the database product name will be the same for the + * default repository and for all other repositories to which JDBC + * connections will be made, through the methods of this class. + * + * @return the database product name + */ public static String getDatabaseProductName() { String productName = ""; Connection conn = null; @@ -160,6 +188,13 @@ public class JDBCTools { return productName; } + /** + * Returns an enumerated value uniquely identifying the database product type; + * e.g. MySQL, PostgreSQL, based on the database product name. + * + * @return an enumerated value identifying the database product type + * @throws Exception + */ public static DatabaseProductType getDatabaseProductType() throws Exception { DatabaseProductType productType = DatabaseProductType.UNRECOGNIZED; String productName = getDatabaseProductName(); @@ -176,4 +211,26 @@ public class JDBCTools { public static String getDefaultRepositoryName() { return ServiceMain.DEFAULT_REPOSITORY_NAME; } + + public static String getNuxeoRepositoryName() { + return ServiceMain.NUXEO_REPOSITORY_NAME; + } + + /** + * Prints metadata, such as database username and connection URL, + * for an open JDBC connection. This is a utility method for use + * during debugging. + * + * @param conn an open JDBC Connection + * @throws SQLException + */ + private static void printConnectionMetaData(Connection conn) throws SQLException { + if (conn != null) { + DatabaseMetaData metadata = conn.getMetaData(); + // FIXME: Outputs via System.out, rather than Logger, for + // cases where this may be called during server startup. + System.out.println("username=" + metadata.getUserName()); + System.out.println("database url=" + metadata.getURL()); + } + } } -- 2.47.3