Author: [log in to unmask] Date: Tue Mar 29 16:16:19 2016 New Revision: 4329 Log: Overhaul db connection management in conditions system (on a branch for now). Modified: java/branches/jeremy-dev2/conditions/pom.xml java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/api/BaseConditionsObjectCollection.java java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/cli/AddCommand.java java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/cli/CommandLineTool.java java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/cli/LoadCommand.java java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/AbstractConditionsObjectConverter.java java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/ConditionsRecordConverter.java java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/ConditionsSeriesConverter.java java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/ConditionsTagConverter.java java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/DatabaseConditionsManager.java java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/DatabaseUtilities.java java/branches/jeremy-dev2/conditions/src/test/java/org/hps/conditions/database/DatabaseConditionsManagerTest.java java/branches/jeremy-dev2/conditions/src/test/java/org/hps/conditions/dummy/DummyConditionsObjectConverterTest.java java/branches/jeremy-dev2/conditions/src/test/java/org/hps/conditions/dummy/DummyConditionsObjectTest.java Modified: java/branches/jeremy-dev2/conditions/pom.xml ============================================================================= --- java/branches/jeremy-dev2/conditions/pom.xml (original) +++ java/branches/jeremy-dev2/conditions/pom.xml Tue Mar 29 16:16:19 2016 @@ -60,8 +60,6 @@ <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> - <version>5.1.26</version> - <scope>runtime</scope> </dependency> <dependency> <groupId>org.reflections</groupId> @@ -72,5 +70,10 @@ <artifactId>commons-csv</artifactId> <version>1.1</version> </dependency> + <dependency> + <groupId>org.apache.commons</groupId> + <artifactId>commons-dbcp2</artifactId> + <version>2.1.1</version> + </dependency> </dependencies> </project> Modified: java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/api/BaseConditionsObjectCollection.java ============================================================================= --- java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/api/BaseConditionsObjectCollection.java (original) +++ java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/api/BaseConditionsObjectCollection.java Tue Mar 29 16:16:19 2016 @@ -1,4 +1,6 @@ package org.hps.conditions.api; + +import static org.hps.conditions.database.DatabaseUtilities.safeClose; import java.io.File; import java.io.FileNotFoundException; @@ -109,30 +111,7 @@ } return added; } - - private void checkCollectionId(final ObjectType object) { - // Does this collection have a valid ID yet? - if (this.getCollectionId() != BaseConditionsObject.UNSET_COLLECTION_ID) { - // Does the object that is being added have a collection ID? - if (object.getCollectionId() != BaseConditionsObject.UNSET_COLLECTION_ID) { - // Does the object's collection ID not match? - if (object.getCollectionId() != this.collectionId) { - // Cannot add an object from a different collection. - throw new IllegalArgumentException("Cannot add object with different collection ID: " - + object.getCollectionId()); - } - } else { - try { - // Set the collection ID on the object. - // FIXME: Uses concrete type instead of interface. - ((BaseConditionsObject) object).setCollectionId(this.collectionId); - } catch (final ConditionsObjectException e) { - throw new RuntimeException("Error assigning collection ID " + this.collectionId + " to object.", e); - } - } - } - } - + /** * Add all objects from a collection. * @@ -386,9 +365,7 @@ } } } finally { - if (insertStatement != null) { - insertStatement.close(); - } + safeClose(insertStatement); this.connection.setAutoCommit(true); } } @@ -513,6 +490,7 @@ public final boolean select(final int collectionId) throws SQLException, DatabaseObjectException { this.collectionId = collectionId; Statement statement = null; + ResultSet resultSet = null; boolean selected = false; try { statement = this.connection.createStatement(); @@ -524,33 +502,26 @@ sb.setLength(sb.length() - 2); sb.append(" FROM " + this.tableMetaData.getTableName() + " WHERE collection_id = " + collectionId); final String sql = sb.toString(); - final ResultSet resultSet = statement.executeQuery(sql); + resultSet = statement.executeQuery(sql); while (resultSet.next()) { - try { - final ObjectType newObject = (ObjectType) this.tableMetaData.getObjectClass().newInstance(); - newObject.setConnection(this.connection); - newObject.setTableMetaData(this.tableMetaData); - final int id = resultSet.getInt(1); - ((BaseConditionsObject) newObject).setRowId(id); - int column = 2; - for (final String fieldName : this.tableMetaData.getFieldNames()) { - newObject.setFieldValue(fieldName, resultSet.getObject(column)); - ++column; - } - try { - this.add(newObject); - } catch (final ConditionsObjectException e) { - throw new DatabaseObjectException("Error adding object to collection.", e, newObject); - } - selected = true; - } catch (InstantiationException | IllegalAccessException e) { - throw new RuntimeException(e); - } - } + final ObjectType newObject = (ObjectType) this.tableMetaData.getObjectClass().newInstance(); + newObject.setConnection(this.connection); + newObject.setTableMetaData(this.tableMetaData); + final int id = resultSet.getInt(1); + ((BaseConditionsObject) newObject).setRowId(id); + int column = 2; + for (final String fieldName : this.tableMetaData.getFieldNames()) { + newObject.setFieldValue(fieldName, resultSet.getObject(column)); + ++column; + } + this.add(newObject); + selected = true; + } + } catch (InstantiationException | IllegalAccessException | ConditionsObjectException e) { + throw new RuntimeException(e); } finally { - if (statement != null) { - statement.close(); - } + safeClose(resultSet); + safeClose(statement); } return selected; } Modified: java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/cli/AddCommand.java ============================================================================= --- java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/cli/AddCommand.java (original) +++ java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/cli/AddCommand.java Tue Mar 29 16:16:19 2016 @@ -137,15 +137,10 @@ collectionId, createdBy, notes); LOGGER.info("inserting conditions record ..." + '\n' + conditionsRecord); try { - boolean createdConnection = false; final DatabaseConditionsManager manager = DatabaseConditionsManager.getInstance(); - if (!DatabaseConditionsManager.getInstance().isConnected()) { - createdConnection = manager.openConnection(); - } conditionsRecord.setConnection(manager.getConnection()); conditionsRecord.setTableMetaData(TableRegistry.getTableRegistry().findByTableName("conditions")); conditionsRecord.insert(); - manager.closeConnection(createdConnection); } catch (final SQLException | DatabaseObjectException e) { LOGGER.log(Level.SEVERE, "Error adding conditions record", e); throw new RuntimeException("An error occurred while adding a conditions record.", e); Modified: java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/cli/CommandLineTool.java ============================================================================= --- java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/cli/CommandLineTool.java (original) +++ java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/cli/CommandLineTool.java Tue Mar 29 16:16:19 2016 @@ -156,12 +156,11 @@ // Execute the sub-command. command.execute(commandArguments); + } catch (final Exception e) { e.printStackTrace(); System.exit(1); - } finally { - this.conditionsManager.closeConnection(); - } + } } /** Modified: java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/cli/LoadCommand.java ============================================================================= --- java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/cli/LoadCommand.java (original) +++ java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/cli/LoadCommand.java Tue Mar 29 16:16:19 2016 @@ -68,14 +68,9 @@ if (tableName == null) { throw new IllegalArgumentException("Missing table name."); } - + final DatabaseConditionsManager conditionsManager = DatabaseConditionsManager.getInstance(); - - boolean openedConnection = false; - if (!conditionsManager.isConnected()) { - openedConnection = conditionsManager.openConnection(); - } - + String description = null; if (commandLine.hasOption("d")) { description = commandLine.getOptionValue("d"); @@ -115,8 +110,6 @@ throw new RuntimeException("Error getting collection ID.", e); } - conditionsManager.closeConnection(openedConnection); - LOGGER.info("Collection was loaded successfully!"); } } Modified: java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/AbstractConditionsObjectConverter.java ============================================================================= --- java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/AbstractConditionsObjectConverter.java (original) +++ java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/AbstractConditionsObjectConverter.java Tue Mar 29 16:16:19 2016 @@ -1,15 +1,16 @@ package org.hps.conditions.database; +import java.sql.Connection; import java.sql.SQLException; import java.util.logging.Logger; import org.hps.conditions.api.ConditionsObjectCollection; import org.hps.conditions.api.ConditionsObjectException; import org.hps.conditions.api.ConditionsRecord; +import org.hps.conditions.api.ConditionsRecord.ConditionsRecordCollection; import org.hps.conditions.api.DatabaseObjectException; import org.hps.conditions.api.TableMetaData; import org.hps.conditions.api.TableRegistry; -import org.hps.conditions.api.ConditionsRecord.ConditionsRecordCollection; import org.lcsim.conditions.ConditionsConverter; import org.lcsim.conditions.ConditionsManager; @@ -37,22 +38,24 @@ * @return the conditions object collection * @throws ConditionsObjectException if there is a problem creating the collection */ + /* private static ConditionsObjectCollection<?> createCollection(final DatabaseConditionsManager manager, final ConditionsRecord conditionsRecord, final TableMetaData tableMetaData) throws ConditionsObjectException { ConditionsObjectCollection<?> collection; - try { + try (Connection connection = manager.getConnection()) { collection = tableMetaData.getCollectionClass().newInstance(); if (conditionsRecord != null) { - collection.setConnection(manager.getConnection()); + collection.setConnection(connection); collection.setTableMetaData(tableMetaData); collection.setCollectionId(conditionsRecord.getCollectionId()); } - } catch (InstantiationException | IllegalAccessException e) { + } catch (InstantiationException | IllegalAccessException | SQLException e) { throw new ConditionsObjectException("Error creating conditions object collection.", e); } return collection; } + */ /** * The action to take if multiple overlapping conditions sets are found. The default is using the most recently @@ -80,9 +83,6 @@ // Get the DatabaseConditionsManager which is required for using this converter. final DatabaseConditionsManager databaseConditionsManager = (DatabaseConditionsManager) conditionsManager; - - // Setup connection if necessary. - final boolean openedConnection = databaseConditionsManager.openConnection(); // Get the TableMetaData from the table name. final TableMetaData tableMetaData = TableRegistry.getTableRegistry().findByTableName(name); @@ -122,28 +122,37 @@ } // Create a collection of objects to return. - ConditionsObjectCollection collection = null; - try { - collection = createCollection(databaseConditionsManager, conditionsRecord, tableMetaData); - } catch (final ConditionsObjectException e) { - throw new RuntimeException(e); + ConditionsObjectCollection<?> collection = null; + + try (Connection connection = databaseConditionsManager.getConnection()) { + collection = tableMetaData.getCollectionClass().newInstance(); + if (conditionsRecord != null) { + collection.setConnection(connection); + collection.setTableMetaData(tableMetaData); + //collection.setCollectionId(conditionsRecord.getCollectionId()); + collection.select(conditionsRecord.getCollectionId()); + } + } catch (InstantiationException | IllegalAccessException | SQLException | DatabaseObjectException e) { + throw new RuntimeException("Error creating conditions object collection.", e); } + + + LOGGER.fine("Done loading collection " + conditionsRecord.getCollectionId() + " from table " + name + " with " + collection.size() + " records"); - LOGGER.info("loading conditions set..." + '\n' + conditionsRecord); + //LOGGER.info("loading conditions set..." + '\n' + conditionsRecord); // Select the objects into the collection by the collection ID. + /* try { - collection.select(conditionsRecord.getCollectionId()); + LOGGER.fine("Loading collection " + conditionsRecord.getCollectionId() + " from table " + name + " ..."); + + LOGGER.fine("Done loading collection " + conditionsRecord.getCollectionId() + " from table " + name + "."); } catch (DatabaseObjectException | SQLException e) { throw new RuntimeException("Error creating conditions collection from table " + name + " with collection ID " + conditionsRecord.getCollectionId(), e); } - - if (openedConnection) { - // Close connection if one was opened. - databaseConditionsManager.closeConnection(); - } - + */ + return (T) collection; } Modified: java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/ConditionsRecordConverter.java ============================================================================= --- java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/ConditionsRecordConverter.java (original) +++ java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/ConditionsRecordConverter.java Tue Mar 29 16:16:19 2016 @@ -1,7 +1,12 @@ package org.hps.conditions.database; +import static org.hps.conditions.database.DatabaseUtilities.safeClose; + +import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; +import java.sql.Statement; +import java.util.logging.Logger; import org.hps.conditions.api.ConditionsObject; import org.hps.conditions.api.ConditionsObjectCollection; @@ -19,6 +24,8 @@ */ public final class ConditionsRecordConverter extends AbstractConditionsObjectConverter<ConditionsRecordCollection> { + private static final Logger LOGGER = Logger.getLogger(ConditionsRecordConverter.class.getPackage().getName()); + /** * Get the ConditionsRecords for a run based on current configuration of the conditions system. * @@ -31,39 +38,38 @@ public ConditionsRecordCollection getData(final ConditionsManager manager, final String name) { final DatabaseConditionsManager databaseConditionsManager = DatabaseConditionsManager.getInstance(); - - // Setup connection if necessary. - boolean reopenedConnection = false; - if (!databaseConditionsManager.isConnected()) { - databaseConditionsManager.openConnection(); - reopenedConnection = true; - } - + final TableMetaData tableMetaData = databaseConditionsManager.findTableMetaData(name); if (tableMetaData == null) { throw new RuntimeException("Failed to find meta data with key " + name); } - - final String query = "SELECT * from " + tableMetaData.getTableName() + " WHERE " + "run_start <= " - + manager.getRun() + " AND run_end >= " + manager.getRun(); - - final ResultSet resultSet = databaseConditionsManager.selectQuery(query); - + // Create a collection to return. ConditionsObjectCollection collection; try { - collection = tableMetaData.getCollectionClass().newInstance(); + collection = tableMetaData.getCollectionClass().newInstance(); } catch (InstantiationException | IllegalAccessException e) { throw new RuntimeException(e); } - - try { + + final String query = "SELECT * from " + tableMetaData.getTableName() + " WHERE " + "run_start <= " + + manager.getRun() + " AND run_end >= " + manager.getRun(); + Statement statement = null; + ResultSet resultSet = null; + + LOGGER.fine("conditions records will be found with query: " + query); + + try (Connection connection = databaseConditionsManager.getConnection()) { + statement = connection.createStatement(); + resultSet = statement.executeQuery(query); while (resultSet.next()) { final ConditionsObject conditionsRecord = new ConditionsRecord(); - conditionsRecord.setConnection(databaseConditionsManager.getConnection()); + conditionsRecord.setConnection(connection); conditionsRecord.setTableMetaData(tableMetaData); + LOGGER.fine("getting conditions record with row id " + resultSet.getInt(1)); conditionsRecord.select(resultSet.getInt(1)); + LOGGER.fine("done fetching conditions record from db"); try { collection.add(conditionsRecord); } catch (final ConditionsObjectException e) { @@ -72,15 +78,13 @@ } } catch (final DatabaseObjectException | SQLException e) { throw new RuntimeException("Error creating new conditions record.", e); - } - - // Close the ResultSet and Statement. - DatabaseUtilities.cleanup(resultSet); - - if (reopenedConnection) { - databaseConditionsManager.closeConnection(); - } - + } finally { + safeClose(resultSet); + safeClose(statement); + } + + LOGGER.fine("returning " + collection.size() + " conditions records"); + return this.getType().cast(collection); } Modified: java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/ConditionsSeriesConverter.java ============================================================================= --- java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/ConditionsSeriesConverter.java (original) +++ java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/ConditionsSeriesConverter.java Tue Mar 29 16:16:19 2016 @@ -62,13 +62,6 @@ final DatabaseConditionsManager conditionsManager = DatabaseConditionsManager.getInstance(); - // Setup connection if necessary. - boolean reopenedConnection = false; - if (!conditionsManager.isConnected()) { - conditionsManager.openConnection(); - reopenedConnection = true; - } - // Get the table meta data for the collection type. final TableMetaData tableMetaData = conditionsManager.findTableMetaData(tableName); if (tableMetaData == null) { @@ -99,10 +92,6 @@ series.add((ConditionsObjectCollection<ObjectType>) collection); } - if (reopenedConnection) { - conditionsManager.closeConnection(); - } - // Return new collection. return series; } Modified: java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/ConditionsTagConverter.java ============================================================================= --- java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/ConditionsTagConverter.java (original) +++ java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/ConditionsTagConverter.java Tue Mar 29 16:16:19 2016 @@ -39,9 +39,8 @@ if (dbConditionsManager == null) { throw new IllegalArgumentException("The conditions manager has the wrong type."); } - boolean openedConnection = dbConditionsManager.openConnection(); - Connection connection = DatabaseConditionsManager.getInstance().getConnection(); - try { + + try (Connection connection = DatabaseConditionsManager.getInstance().getConnection()) { PreparedStatement statement = connection.prepareStatement(SELECT_SQL); statement.setString(1, name); ResultSet resultSet = statement.executeQuery(); @@ -55,9 +54,7 @@ throw new RuntimeException(e); } catch (ConditionsObjectException e) { throw new RuntimeException(e); - } finally { - dbConditionsManager.closeConnection(openedConnection); - } + } if (conditionsTagCollection.size() == 0) { throw new IllegalArgumentException("The conditions tag " + name + " does not exist in the database."); } Modified: java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/DatabaseConditionsManager.java ============================================================================= --- java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/DatabaseConditionsManager.java (original) +++ java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/DatabaseConditionsManager.java Tue Mar 29 16:16:19 2016 @@ -1,4 +1,7 @@ package org.hps.conditions.database; + +import static org.hps.conditions.database.DatabaseUtilities.createDataSource; +import static org.hps.conditions.database.DatabaseUtilities.safeClose; import java.io.File; import java.io.FileInputStream; @@ -15,10 +18,11 @@ import java.util.Collections; import java.util.HashSet; import java.util.LinkedHashSet; -import java.util.List; +import java.util.Properties; import java.util.Set; -import java.util.logging.Level; import java.util.logging.Logger; + +import javax.sql.DataSource; import org.hps.conditions.api.ConditionsObject; import org.hps.conditions.api.ConditionsObjectCollection; @@ -57,14 +61,34 @@ */ @SuppressWarnings("rawtypes") public final class DatabaseConditionsManager extends ConditionsManagerImplementation { - - /** - * Name of system property that can be used to specify custom database connection parameters. + + /** + * Initialize the logger. + */ + private static Logger LOGGER = Logger.getLogger(DatabaseConditionsManager.class.getPackage().getName()); + + /* Default connection settings. */ + private static final String DEFAULT_USER = "hpsuser"; + private static final String DEFAULT_PASSWORD = "darkphoton"; + private static final String DEFAULT_HOST = "hpsdb.jlab.org"; + private static final String DEFAULT_DATABASE = "hps_conditions"; + private static final int DEFAULT_PORT = 3306; + private static final int DEFAULT_MAX_CONNECTIONS = 1; + + /* Default database URI. */ + private static final String DEFAULT_CONNECTION_URI = "jdbc:mysql://" + DEFAULT_HOST + "/" + + DEFAULT_DATABASE + "?" + + "user=" + DEFAULT_USER + "&" + + "password=" + DEFAULT_PASSWORD; + // + "max-connections=" + DEFAULT_MAX_CONNECTIONS + "&" + + /** + * Name of system property that can be used to specify custom database connection parameters in a file. */ private static final String CONNECTION_PROPERTY_FILE = "org.hps.conditions.connection.file"; /** - * Connection property resource. + * Name of system property for specifying a connection property file from a class resource. */ private static final String CONNECTION_PROPERTY_RESOURCE = "org.hps.conditions.connection.resource"; @@ -74,21 +98,11 @@ private static final String DEFAULT_CONFIG = "/org/hps/conditions/config/conditions_database_prod.xml"; /** - * The connection properties resource for connecting to the default JLAB database. - */ - private static final String DEFAULT_CONNECTION_PROPERTIES_RESOURCE = "/org/hps/conditions/config/jlab_connection.prop"; - - /** * The Eng Run XML config. */ private static final String ENGRUN_CONFIG = "/org/hps/conditions/config/conditions_database_engrun.xml"; /** - * Initialize the logger. - */ - private static Logger LOGGER = Logger.getLogger(DatabaseConditionsManager.class.getPackage().getName()); - - /** * The Test Run XML config. */ private static final String TEST_RUN_CONFIG = "/org/hps/conditions/config/conditions_database_testrun_2012.xml"; @@ -97,12 +111,143 @@ * The max value for a run to be considered Test Run. */ private static final int TEST_RUN_MAX_RUN = 1365; - - static { - // Set default login timeout of 5 seconds. - DriverManager.setLoginTimeout(30); - } - + + /** + * Data source initialized with default settings. + */ + private DataSource dataSource; + + /** + * True to cache all known conditions sets (from keys) during initialization. + */ + private boolean cacheAllConditions = false; + + /** + * The current set of conditions for the run. + */ + private ConditionsRecordCollection conditionsRecordCollection = null; + + /** + * The currently active conditions tag (empty collection means no tag is active). + */ + private final ConditionsTagCollection conditionsTagCollection = new ConditionsTagCollection(); + + /** + * Create the global registry of conditions object converters. + */ + private final ConverterRegistry converters = ConverterRegistry.create(); + + /** + * The converter for creating the combined ECAL conditions object. + */ + private ConditionsConverter ecalConverter; + + /** + * The default ECAL detector name in the detector geometry. + */ + private String ecalName = "Ecal"; + + /** + * True to freeze the system after initialization. + */ + private boolean freezeAfterInitialize = false; + + /** + * True if the conditions manager was configured from an XML configuration resource or file. + */ + private boolean isConfigured = false; + + /** + * True if the conditions system has been frozen and will ignore updates after it is initialized. + */ + private boolean isFrozen = false; + + /** + * True if the manager has been initialized, e.g. the {@link #setDetector(String, int)} method was called. + */ + private boolean isInitialized = false; + + /** + * True if current run number is from Test Run. + */ + private boolean isTestRun = false; + + /** + * True to setup the SVT detector model with conditions. + */ + private boolean setupSvtDetector = true; + + /** + * The converter for creating the combined SVT conditions object. + */ + private ConditionsConverter svtConverter; + + /** + * The default SVT name in the detector geometry. + */ + private String svtName = "Tracker"; + + /** + * Create the global registry of table meta data. + */ + private final TableRegistry tableRegistry = TableRegistry.getTableRegistry(); + + /** + * The currently applied conditions tags. + */ + private final Set<String> tags = new HashSet<String>(); + + /** + * Create the default <code>DataSource</code> for connecting to the conditions database. + * @return the <code>DataSource</code> for connecting to the conditions database + */ + private static DataSource createDefaultDataSource() { + return createDataSource(DEFAULT_CONNECTION_URI); + } + + /** + * Create a connction URI from properties. + * @param is the input stream with properties settings + * @return the connection URI + */ + private static String createConnectionUriFromProperties(InputStream is) { + + String host = DEFAULT_HOST; + String database = DEFAULT_DATABASE; + String user = DEFAULT_USER; + String password = DEFAULT_PASSWORD; + int port = DEFAULT_PORT; + + // Override default settings with property values. + final Properties properties = new Properties(); + try { + properties.load(is); + } catch (final IOException e) { + throw new RuntimeException(e); + } + if (properties.containsKey("user")) { + user = properties.getProperty("user"); + } + if (properties.containsKey("password")) { + password = properties.getProperty("password"); + } + if (properties.containsKey("host")) { + host = properties.getProperty("host"); + } + if (properties.containsKey("port")) { + port = Integer.parseInt(properties.getProperty("port")); + } + if (properties.containsKey("database")) { + database = properties.getProperty("database"); + } + + return "jdbc:mysql://" + host + ":" + port + "/" + + database + "?" + + "max-connections=" + DEFAULT_MAX_CONNECTIONS + "&" + + "user=" + user + "&" + + "password=" + password; + } + /** * Get the static instance of this class. * @@ -155,116 +300,6 @@ } /** - * True to cache all known conditions sets (from keys) during initialization. - */ - private boolean cacheAllConditions = false; - - /** - * True to close the connection after initialization. - */ - private boolean closeConnectionAfterInitialize = true; - - /** - * The current set of conditions for the run. - */ - private ConditionsRecordCollection conditionsRecordCollection = null; - - /** - * The currently active conditions tag (empty collection means no tag is active). - */ - private final ConditionsTagCollection conditionsTagCollection = new ConditionsTagCollection(); - - /** - * The current database connection. - */ - private Connection connection; - - /** - * The current connection parameters. - */ - private ConnectionParameters connectionParameters; - - /** - * The connection properties file, if one is being used from the command line. - */ - private File connectionPropertiesFile; - - /** - * Create the global registry of conditions object converters. - */ - private final ConverterRegistry converters = ConverterRegistry.create(); - - /** - * The converter for creating the combined ECAL conditions object. - */ - private ConditionsConverter ecalConverter; - - /** - * The default ECAL detector name in the detector geometry. - */ - private String ecalName = "Ecal"; - - /** - * True to freeze the system after initialization. - */ - private boolean freezeAfterInitialize = false; - - /** - * True if the conditions manager was configured from an XML configuration resource or file. - */ - private boolean isConfigured = false; - - /** - * True if manager is connected to the database. - */ - private boolean isConnected = false; - - /** - * True if the conditions system has been frozen and will ignore updates after it is initialized. - */ - private boolean isFrozen = false; - - /** - * True if the manager has been initialized, e.g. the {@link #setDetector(String, int)} method was called. - */ - private boolean isInitialized = false; - - /** - * True if current run number is from Test Run. - */ - private boolean isTestRun = false; - - /** - * Flag used to print connection parameters one time. - */ - private boolean loggedConnectionParameters = false; - - /** - * True to setup the SVT detector model with conditions. - */ - private boolean setupSvtDetector = true; - - /** - * The converter for creating the combined SVT conditions object. - */ - private ConditionsConverter svtConverter; - - /** - * The default SVT name in the detector geometry. - */ - private String svtName = "Tracker"; - - /** - * Create the global registry of table meta data. - */ - private final TableRegistry tableRegistry = TableRegistry.getTableRegistry(); - - /** - * The currently applied conditions tags. - */ - private final Set<String> tags = new HashSet<String>(); - - /** * Class constructor. Calling this will automatically register this manager as the global default. */ protected DatabaseConditionsManager() { @@ -277,7 +312,13 @@ // Setup connection from system property pointing to a resource, if it was set. this.setupConnectionSystemPropertyResource(); - + + // Create the default data source if alternate connection info was not provided. + if (this.dataSource == null) { + LOGGER.fine("creating default data source"); + dataSource = createDefaultDataSource(); + } + // Set run to invalid number. this.setRun(-1); @@ -345,37 +386,6 @@ } /** - * Close the database connection. - */ - public synchronized void closeConnection() { - LOGGER.fine("closing connection"); - if (this.connection != null) { - try { - if (!this.connection.isClosed()) { - this.connection.close(); - } - } catch (final SQLException e) { - throw new RuntimeException(e); - } - } - this.connection = null; - this.isConnected = false; - LOGGER.fine("connection closed"); - } - - /** - * Close the database connection but only if there was a connection opened based on the flag. Otherwise, it should - * be left open. Used in conjunction with return value of {@link #openConnection()}. - * - * @param connectionOpened <code>true</code> to close the connection; <code>false</code> to leave it open - */ - public synchronized void closeConnection(final boolean connectionOpened) { - if (connectionOpened) { - this.closeConnection(); - } - } - - /** * This method will return <code>true</code> if the given collection ID already exists in the table. * * @param tableName the name of the table @@ -384,21 +394,23 @@ */ public boolean collectionExists(final String tableName, final int collectionID) { final String sql = "SELECT * FROM " + tableName + " where collection_id = " + collectionID; - final ResultSet resultSet = this.selectQuery(sql); - try { + int rowCount = 0; + Statement statement = null; + ResultSet resultSet = null; + try (Connection connection = dataSource.getConnection()) { + statement = connection.createStatement(); + resultSet = statement.executeQuery(sql); resultSet.last(); + rowCount = resultSet.getRow(); } catch (final SQLException e) { e.printStackTrace(); - } - int rowCount = 0; - try { - rowCount = resultSet.getRow(); - } catch (final SQLException e) { - e.printStackTrace(); + } finally { + safeClose(resultSet); + safeClose(statement); } return rowCount != 0; - } - + } + /** * Configure this class from an <code>InputStream</code> which should point to an XML document. * @@ -475,22 +487,22 @@ */ public Set<String> getAvailableTags() { LOGGER.fine("getting list of available conditions tags"); - final boolean openedConnection = this.openConnection(); final Set<String> tags = new LinkedHashSet<String>(); - final ResultSet rs = this - .selectQuery("select distinct(tag) from conditions_tags where tag is not null order by tag"); - try { - while (rs.next()) { - tags.add(rs.getString(1)); + Statement statement = null; + ResultSet resultSet = null; + try (Connection connection = dataSource.getConnection()) { + statement = connection.createStatement(); + resultSet = statement.executeQuery("select distinct(tag) from conditions_tags where tag is not null order by tag"); + while (resultSet.next()) { + tags.add(resultSet.getString(1)); } } catch (final SQLException e) { throw new RuntimeException(e); - } - try { - rs.close(); - } catch (final SQLException e) { - LOGGER.log(Level.WARNING, "error closing ResultSet", e); - } + } finally { + safeClose(resultSet); + safeClose(statement); + } + final StringBuffer sb = new StringBuffer(); sb.append("found unique conditions tags: "); for (final String tag : tags) { @@ -498,7 +510,6 @@ } sb.setLength(sb.length() - 1); LOGGER.fine(sb.toString()); - this.closeConnection(openedConnection); return tags; } @@ -511,16 +522,16 @@ */ public synchronized int getCollectionId(final ConditionsObjectCollection<?> collection, final String description) throws SQLException { - + + final String caller = Thread.currentThread().getStackTrace()[2].getClassName(); final String log = "created by " + System.getProperty("user.name") + " using " + caller.substring(caller.lastIndexOf('.') + 1); - final boolean opened = this.openConnection(); PreparedStatement statement = null; ResultSet resultSet = null; int collectionId = -1; - try { - statement = this.connection.prepareStatement( + try (Connection connection = dataSource.getConnection()) { + statement = connection.prepareStatement( "INSERT INTO collections (table_name, log, description, created) VALUES (?, ?, ?, NOW())", Statement.RETURN_GENERATED_KEYS); statement.setString(1, collection.getTableMetaData().getTableName()); @@ -541,9 +552,9 @@ if (statement != null) { statement.close(); } - this.closeConnection(opened); } collection.setCollectionId(collectionId); + return collectionId; } @@ -553,21 +564,28 @@ * @return the list of conditions records for the run */ public ConditionsRecordCollection getConditionsRecords() { - if (this.run == -1 || this.detectorName == null) { - throw new IllegalStateException("Conditions system is not initialized."); - } - // If the collection is null then the new conditions records need to be retrieved from the database. - if (this.conditionsRecordCollection == null) { - - // Get the collection of conditions that are applicable for the current run. - this.conditionsRecordCollection = this.getCachedConditions(ConditionsRecordCollection.class, "conditions") - .getCachedData(); - - // If there is one or more tags enabled then filter the collection by the tag names. - if (this.conditionsTagCollection.size() > 0) { - this.conditionsRecordCollection = this.conditionsTagCollection.filter(this.conditionsRecordCollection); - } - } + + try (Connection connection = dataSource.getConnection()) { + + if (this.run == -1 || this.detectorName == null) { + throw new IllegalStateException("Conditions system is not initialized."); + } + // If the collection is null then the new conditions records need to be retrieved from the database. + if (this.conditionsRecordCollection == null) { + + // Get the collection of conditions that are applicable for the current run. + this.conditionsRecordCollection = this.getCachedConditions(ConditionsRecordCollection.class, "conditions") + .getCachedData(); + + // If there is one or more tags enabled then filter the collection by the tag names. + if (this.conditionsTagCollection.size() > 0) { + this.conditionsRecordCollection = this.conditionsTagCollection.filter(this.conditionsRecordCollection); + } + } + } catch (SQLException e) { + throw new RuntimeException(e); + } + return this.conditionsRecordCollection; } @@ -604,10 +622,11 @@ * @return the JDBC connection */ public Connection getConnection() { - if (!this.isConnected()) { - this.openConnection(); - } - return this.connection; + try { + return dataSource.getConnection(); + } catch (SQLException e) { + throw new RuntimeException(e); + } } /** @@ -676,14 +695,12 @@ */ private void initialize(final String detectorName, final int runNumber) throws ConditionsNotFoundException { - LOGGER.config("initializing with detector " + detectorName + " and run " + runNumber); - - // Clear the conditions cache. - // this.clearCache(); - + LOGGER.config("Initializing conditions with detector " + detectorName + " and run " + runNumber + " ..."); + // Set flag if run number is from Test Run 2012 data. if (isTestRun(runNumber)) { this.isTestRun = true; + LOGGER.config("Test run config is enabled from run number."); } // Is not configured yet? @@ -703,15 +720,13 @@ // Register the converters for this initialization. this.registerConverters(); - // Open the database connection. - this.openConnection(); - // Reset the conditions records to trigger a re-caching. this.conditionsRecordCollection = null; // Call the super class's setDetector method to construct the detector object and activate conditions listeners. - LOGGER.fine("activating default conditions manager"); + LOGGER.fine("calling set detector"); super.setDetector(detectorName, runNumber); + LOGGER.fine("done calling set detector"); // Should all conditions sets be cached? if (this.cacheAllConditions) { @@ -719,34 +734,19 @@ LOGGER.fine("caching conditions sets"); this.cacheConditionsSets(); } - - if (this.closeConnectionAfterInitialize) { - LOGGER.fine("closing connection after initialization"); - // Close the connection. - this.closeConnection(); - } - + // Should the conditions system be frozen now? if (this.freezeAfterInitialize) { // Freeze the conditions system so subsequent updates will be ignored. this.freeze(); LOGGER.config("system was frozen after initialization"); - } + } this.isInitialized = true; - LOGGER.info("conditions system initialized successfully"); - } - - /** - * Check if connected to the database. - * - * @return <code>true</code> if connected - */ - public boolean isConnected() { - return this.isConnected; - } - + LOGGER.info("Conditions system initialized successfully."); + } + /** * True if conditions system is frozen * @@ -821,12 +821,6 @@ if (element != null) { this.isTestRun = Boolean.parseBoolean(element.getText()); LOGGER.config("isTestRun = " + this.isTestRun); - } - - element = node.getChild("closeConnectionAfterInitialize"); - if (element != null) { - this.closeConnectionAfterInitialize = Boolean.parseBoolean(element.getText()); - LOGGER.config("closeConnectionAfterInitialize = " + this.closeConnectionAfterInitialize); } element = node.getChild("loginTimeout"); @@ -836,88 +830,7 @@ LOGGER.config("loginTimeout = " + timeout); } } - - /** - * Create a new collection with the given type. - * - * @param collectionType the collection type - * @return the new collection - */ - public <CollectionType extends ConditionsObjectCollection<?>> CollectionType newCollection( - final Class<CollectionType> collectionType) { - final List<TableMetaData> tableMetaDataList = TableRegistry.getTableRegistry().findByCollectionType( - collectionType); - if (tableMetaDataList.size() > 1) { - throw new RuntimeException("More than one table meta data object returned for type: " - + collectionType.getName()); - } - final TableMetaData tableMetaData = tableMetaDataList.get(0); - CollectionType collection; - try { - collection = collectionType.newInstance(); - } catch (InstantiationException | IllegalAccessException e) { - throw new RuntimeException("Error creating new collection.", e); - } - collection.setTableMetaData(tableMetaData); - collection.setConnection(this.getConnection()); - return collection; - } - - /** - * Create a new collection with the given type and table name. - * - * @param collectionType the collection type - * @param tableName the table name - * @return the new collection - */ - public <CollectionType extends ConditionsObjectCollection<?>> CollectionType newCollection( - final Class<CollectionType> collectionType, final String tableName) { - final TableMetaData tableMetaData = TableRegistry.getTableRegistry().findByTableName(tableName); - CollectionType collection; - try { - collection = collectionType.newInstance(); - } catch (InstantiationException | IllegalAccessException e) { - throw new RuntimeException("Error creating new collection.", e); - } - collection.setTableMetaData(tableMetaData); - collection.setConnection(this.getConnection()); - return collection; - } - - /** - * Open the database connection. - * - * @return <code>true</code> if a connection was opened; <code>false</code> if using an existing connection. - */ - public synchronized boolean openConnection() { - boolean openedConnection = false; - if (!this.isConnected) { - // Do the connection parameters need to be figured out automatically? - if (this.connectionParameters == null) { - // Setup the default read-only connection, which will choose a SLAC or JLab database. - this.connectionParameters = ConnectionParameters.fromResource(DEFAULT_CONNECTION_PROPERTIES_RESOURCE); - } - - if (!this.loggedConnectionParameters) { - // Print out detailed info to the log on first connection within the job. - LOGGER.info("opening connection ... " + '\n' + "connection: " - + this.connectionParameters.getConnectionString() + '\n' + "host: " - + this.connectionParameters.getHostname() + '\n' + "port: " - + this.connectionParameters.getPort() + '\n' + "user: " + this.connectionParameters.getUser() - + '\n' + "database: " + this.connectionParameters.getDatabase()); - this.loggedConnectionParameters = true; - } - - // Create the connection using the parameters. - this.connection = this.connectionParameters.createConnection(); - this.isConnected = true; - openedConnection = true; - } - - // Flag to indicate whether an existing connection was used or not. - return openedConnection; - } - + /** * Register the conditions converters with the manager. */ @@ -942,39 +855,9 @@ // Load the default converters. this.svtConverter = new SvtConditionsConverter(); this.ecalConverter = new EcalConditionsConverter(); - LOGGER.config("registering default conditions converters"); } this.registerConditionsConverter(this.svtConverter); this.registerConditionsConverter(this.ecalConverter); - } - - /** - * This method can be used to perform a database SELECT query. - * - * @param query the SQL query string - * @return the <code>ResultSet</code> from the query - * @throws RuntimeException if there is a query error - */ - ResultSet selectQuery(final String query) { - LOGGER.fine("executing SQL select query ..." + '\n' + query); - ResultSet result = null; - Statement statement = null; - try { - statement = this.connection.createStatement(); - result = statement.executeQuery(query); - } catch (final SQLException x) { - throw new RuntimeException("Error in query: " + query, x); - } - return result; - } - - /** - * Set the connection parameters of the conditions database. - * - * @param connectionParameters the connection parameters - */ - public void setConnectionParameters(final ConnectionParameters connectionParameters) { - this.connectionParameters = connectionParameters; } /** @@ -985,10 +868,15 @@ public void setConnectionProperties(final File file) { LOGGER.config("setting connection properties file " + file.getPath()); if (!file.exists()) { - throw new IllegalArgumentException("The connection properties file does not exist: " - + this.connectionPropertiesFile.getPath()); - } - this.connectionParameters = ConnectionParameters.fromProperties(file); + throw new IllegalArgumentException("The connection properties file " + file.getPath() + " does not exist."); + } + FileInputStream fin = null; + try { + fin = new FileInputStream(file); + } catch (final FileNotFoundException e) { + throw new IllegalArgumentException(file.getPath() + " does not exist.", e); + } + this.dataSource = createDataSource(createConnectionUriFromProperties(fin)); } /** @@ -998,7 +886,8 @@ */ public void setConnectionResource(final String resource) { LOGGER.config("setting connection resource " + resource); - this.connectionParameters = ConnectionParameters.fromResource(resource); + InputStream is = DatabaseConditionsManager.class.getResourceAsStream(resource); + dataSource = createDataSource(createConnectionUriFromProperties(is)); } /** @@ -1078,6 +967,8 @@ CONNECTION_PROPERTY_RESOURCE); if (systemPropertiesConnectionResource != null) { this.setConnectionResource(systemPropertiesConnectionResource); + LOGGER.info("connection setup from system property " + CONNECTION_PROPERTY_FILE + " = " + + systemPropertiesConnectionResource); } } Modified: java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/DatabaseUtilities.java ============================================================================= --- java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/DatabaseUtilities.java (original) +++ java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/DatabaseUtilities.java Tue Mar 29 16:16:19 2016 @@ -1,43 +1,68 @@ package org.hps.conditions.database; +import java.sql.Connection; import java.sql.ResultSet; +import java.sql.SQLException; import java.sql.Statement; +import java.util.logging.Logger; + +import javax.sql.DataSource; + +import org.apache.commons.dbcp2.BasicDataSource; /** * Database utility methods. * * @author Jeremy McCormick, SLAC */ -// TODO: Merge this single method into the manager class or a connection utilities class. public final class DatabaseUtilities { - - /** - * Cleanup a JDBC <code>ResultSet</code> by closing it and its <code>Statement</code> - * - * @param resultSet the database <code>ResultSet</code> - */ - static void cleanup(final ResultSet resultSet) { - Statement statement = null; - try { - statement = resultSet.getStatement(); - } catch (final Exception e) { - } - try { - if (resultSet != null) { - resultSet.close(); + + private static final Logger LOGGER = Logger.getLogger(DatabaseUtilities.class.getPackage().getName()); + + public static void safeClose(final Statement statement) { + if (statement != null) { + try { + statement.close(); + } catch (SQLException e) { + e.printStackTrace(); } - } catch (final Exception e) { - e.printStackTrace(); - } - try { - if (statement != null) { - statement.close(); - } - } catch (final Exception e) { - e.printStackTrace(); } } - + + public static void safeClose(final ResultSet resultSet) { + if (resultSet != null) { + try { + resultSet.close(); + } catch (SQLException e) { + e.printStackTrace(); + } + } + } + + public static void safeClose(Connection connection) { + if (connection != null) { + try { + connection.close(); + } catch (SQLException e) { + e.printStackTrace(); + } + } + } + + /** + * Create a pooled data source from a connection URI. + * @param uri the connection URI + * @return the pooled data source + */ + static DataSource createDataSource(String uri) { + BasicDataSource dataSource = new BasicDataSource(); + dataSource.setUrl(uri); + LOGGER.config("created new data source from URI: " + uri); + //dataSource.setInitialSize(1); + //dataSource.setMaxTotal(1); + return dataSource; + } + /** * Do not allow instantiation. */ Modified: java/branches/jeremy-dev2/conditions/src/test/java/org/hps/conditions/database/DatabaseConditionsManagerTest.java ============================================================================= --- java/branches/jeremy-dev2/conditions/src/test/java/org/hps/conditions/database/DatabaseConditionsManagerTest.java (original) +++ java/branches/jeremy-dev2/conditions/src/test/java/org/hps/conditions/database/DatabaseConditionsManagerTest.java Tue Mar 29 16:16:19 2016 @@ -27,21 +27,12 @@ DatabaseConditionsManager manager = DatabaseConditionsManager.getInstance(); // Check initial state. - TestCase.assertTrue("The conditions manager instance is null.", manager != null); - TestCase.assertFalse("The manager should not be connected.", manager.isConnected()); + TestCase.assertTrue("The conditions manager instance is null.", manager != null); TestCase.assertFalse("The manager should not be initialized.", manager.isInitialized()); TestCase.assertFalse("The manager should not be frozen.", manager.isFrozen()); TestCase.assertTrue("The manager should be setup.", ConditionsManager.isSetup()); - - // Open database connection. - manager.openConnection(); - - // Check that a new collection can be created. - EcalCalibrationCollection newCollection = manager.newCollection(EcalCalibrationCollection.class, "ecal_calibrations"); - TestCase.assertNotNull("New collection should have metadata.", newCollection.getTableMetaData()); - - // Check connection state. - TestCase.assertTrue("The manager should be connected.", manager.isConnected()); + + // Check connection state. TestCase.assertNotNull("The connection is null.", manager.getConnection()); // Turn off SVT detector setup because some required classes are not available from this module. @@ -105,17 +96,11 @@ TestCase.assertTrue("Manager should be configured for test run.", manager.isTestRun()); // Check SLAC connection setup. - manager.closeConnection(); manager.setConnectionResource("/org/hps/conditions/config/slac_connection.prop"); - manager.openConnection(); TestCase.assertTrue("Connection should be slac host.", manager.getConnection().getMetaData().getURL().contains("slac")); // Check JLAB connection setup. - manager.closeConnection(); manager.setConnectionResource("/org/hps/conditions/config/jlab_connection.prop"); - manager.openConnection(); TestCase.assertTrue("Connection should be slac host.", manager.getConnection().getMetaData().getURL().contains("jlab")); - - manager.closeConnection(); } } Modified: java/branches/jeremy-dev2/conditions/src/test/java/org/hps/conditions/dummy/DummyConditionsObjectConverterTest.java ============================================================================= --- java/branches/jeremy-dev2/conditions/src/test/java/org/hps/conditions/dummy/DummyConditionsObjectConverterTest.java (original) +++ java/branches/jeremy-dev2/conditions/src/test/java/org/hps/conditions/dummy/DummyConditionsObjectConverterTest.java Tue Mar 29 16:16:19 2016 @@ -21,7 +21,6 @@ manager.setXmlConfig("/org/hps/conditions/config/conditions_database_no_svt.xml"); manager.registerConditionsConverter(new DummyConditionsObjectConverter()); manager.setDetector("HPS-dummy-detector", 1); - manager.openConnection(); final TableMetaData tableMetaData = TableRegistry.getTableRegistry().findByTableName("dummy"); Modified: java/branches/jeremy-dev2/conditions/src/test/java/org/hps/conditions/dummy/DummyConditionsObjectTest.java ============================================================================= --- java/branches/jeremy-dev2/conditions/src/test/java/org/hps/conditions/dummy/DummyConditionsObjectTest.java (original) +++ java/branches/jeremy-dev2/conditions/src/test/java/org/hps/conditions/dummy/DummyConditionsObjectTest.java Tue Mar 29 16:16:19 2016 @@ -57,7 +57,6 @@ } catch (final SQLException e) { e.printStackTrace(); } - manager.closeConnection(); } /**