// Copyright 2010 Google Inc. // // Licensed under the Apache License, Version 2.0 (the "License"); // you may not use this file except in compliance with the License. // You may obtain a copy of the License at // // http://www.apache.org/licenses/LICENSE-2.0 // // Unless required by applicable law or agreed to in writing, software // distributed under the License is distributed on an "AS IS" BASIS, // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. // See the License for the specific language governing permissions and // limitations under the License. package com.google.enterprise.connector.util.database; import com.google.common.annotations.VisibleForTesting; import com.google.common.base.Strings; import com.google.enterprise.connector.spi.DatabaseResourceBundle; import com.google.enterprise.connector.spi.SpiConstants.DatabaseType; import com.google.enterprise.connector.util.BasicChecksumGenerator; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.logging.Level; import java.util.logging.Logger; import javax.sql.DataSource; /** * Basic database connectivity, table creation and connection pooling. * <p> * The JdbcDatabase wraps a {@link javax.sql.DataSource} instance, * adding additional features specifically beneficial to Connectors * and the ConnectorManager, such as: * <ul><li>Information regarding the underlying database implementation, * including the vendor name, version information, and description.</li> * <li>Maintaining a ConnectionPool for the DataSource.</li> * <li>Manufacturing legal database table names base upon a Connector name.</li> * <li>Creating database tables based upon a supplied DDL, or verifying * that such a table exists.</li> * <li>Methods useful for forming DatabaseResourceBundle filenames for * the specific vendor implementation.</li> * </ul> * * @since 2.8 */ public class JdbcDatabase { private static final Logger LOGGER = Logger.getLogger(JdbcDatabase.class.getName()); private final DataSource dataSource; private final DatabaseConnectionPool connectionPool; private DatabaseType databaseType; private String productName; private String description; private String resourceBundleExtension; public JdbcDatabase(DataSource dataSource) { this.dataSource = dataSource; this.connectionPool = new DatabaseConnectionPool(dataSource); LOGGER.config("Using JDBC DataSource: " + dataSource.toString()); getDatabaseInfo(); } @Override protected void finalize() throws Throwable { shutdown(); } public synchronized void shutdown() { connectionPool.closeConnections(); } /** * Returns {@code true} if the configured {@code JdbcDatabase} is unavailable. * * @return {@code true} if this {@code JdbcDatabase} is disabled, {@code false} * otherwise. */ public boolean isDisabled() { // If I can successfully establish a Connection to the database, assume // the DataSource is functional. Otherwise, consider it disabled. try { dataSource.getConnection().close(); return false; } catch (SQLException e) { return true; } } /** * Return the underlying {@link DataSource} for the database instance. * * * @return the underlying {@link DataSource} for the database instance. */ public DataSource getDataSource() { return dataSource; } /** * Return the {@link DatabaseConnectionPool} used to maintain a collection * of opened {@link Connection Connections} to the {@link DataSource}. * * @return the {@link DatabaseConnectionPool} for this database */ public DatabaseConnectionPool getConnectionPool() { return connectionPool; } /** * Return the {@link DatabaseType} for this database. * * @return the {@link DatabaseType} for this database */ public DatabaseType getDatabaseType() { return databaseType; } /** * Return a sanitzed version of the vendor product name. * This sanitized string is used to create the resourceBundleExtension * and will generally match one of the supported DatabaseTypes. * * @return the sanitized product name */ public String getProductName() { return productName; } /** * Return a description string for this database. * This may be a vendor-supplied string or simply the unsanitized * product name and version string. * * @return the {@code description} string */ public String getDescription() { return description; } @Override public String toString() { return description; } /** * Returns the {@link DatabaseResourceBundle} extension that may be added * to a {@code ResourceBundle baseName} to build the name of the resource * that is specific to the database implementation identified by this * {@code JdbcDatabase} instance. * <p> * This is will be constructed from the database {@code productName}, * {@code majorVersion}, and {@code minorVersion} separated by underbars * ({@code '_'}), for instance MySQL v5.6 will return "{@code _mysql_5_6}". * * @return the resource bundle extension */ public String getResourceBundleExtension() { return resourceBundleExtension; } /** * If the supplied string is not {@code null} or empty, prepend an underscore, * else return the empty string. */ private String bundleNameFragment(String string) { return Strings.isNullOrEmpty(string) ? "" : "_" + string; } /** * Sanitizes the supplied string for use as a portion of a * ResourceBundleExtension. Specifically, a sanitized string * should consist of nothing other than lowercase alphabetics * [a-z], numerics [0-9], underscore '_', and hyphen '-'. * <p> * The following actions are taken sanitize the input string: * <ul><li>Alphabetics are converted to lowercase.</li> * <li>Leading and trailing invalid characters are removed.</li> * <li>The remaining invalid characters are converted to hyphens.</li> * </ul> * <p> * For example, the string "Hello World!" would sanitize to "hello-world". * * @param string the {@code String} to be sanitized. * @return sanitized version of the supplied {@code string} */ private String sanitize(String string) { if (string != null) { return string.toLowerCase().replaceAll("[^a-z0-9_]+", " ").trim() .replace(' ', '-'); } return string; } /** * Extract vendor-specific infromation about this database from * the DataBaseMetaData. This will be used to identify the DatabaseType * and build the DatabaseResourceBundle extenstion for the database. */ private void getDatabaseInfo() { // TODO: Support Manual Configuration of this information, // which would override this detection. // If the database is unavailable, don't try to fetch its metadata. if (isDisabled()) { databaseType = DatabaseType.OTHER; productName = description = "Disabled Database"; resourceBundleExtension = ""; return; } try { Connection connection = connectionPool.getConnection(); try { DatabaseMetaData metaData = connection.getMetaData(); productName = metaData.getDatabaseProductName(); String productVersion = metaData.getDatabaseProductVersion(); int majorVersion = metaData.getDatabaseMajorVersion(); int minorVersion = metaData.getDatabaseMinorVersion(); LOGGER.config("JDBC Database ProductName: " + productName); LOGGER.config("JDBC Database ProductVersion: " + productVersion); LOGGER.config("JDBC Database MajorVersion: " + majorVersion); LOGGER.config("JDBC Database MinorVersion: " + minorVersion); description = productName + " " + productVersion; productName = sanitize(productName); if (productName.equals(DatabaseType.ORACLE.toString())) { // Oracle already has a really long description string. description = productVersion.replace("\n", " "); } else if (productName.equals("microsoft-sql-server")) { // If it looks like "Microsoft SQL Server", shorten name. productName = DatabaseType.SQLSERVER.toString(); } else if (productName.startsWith("db2")) { // IBM embellishes the DB2 productName with extra platform info. productName = "db2"; } else if (productName.indexOf("derby") >= 0) { productName = "derby"; } else if (productName.indexOf("firebird") >= 0) { productName = "firebird"; } else if (productName.indexOf("informix") >= 0) { // Like anyone still uses this. productName = "informix"; } else if (productName.indexOf("sybase") >= 0) { // Both Microsoft and Sybase products are called "SQL Server". productName = "sybase"; } // Most of the others have simple, one-word product names. databaseType = DatabaseType.findDatabaseType(productName); // Determine the DatabaseResourceBundle extension for this DB. resourceBundleExtension = bundleNameFragment(productName) + bundleNameFragment(Integer.toString(majorVersion)) + bundleNameFragment(Integer.toString(minorVersion)); return; } finally { connectionPool.releaseConnection(connection); } } catch (SQLException e) { LOGGER.log(Level.SEVERE, "Failed to retrieve DatabaseMetaData", e); } // Fallback in case we can't get anything from DatabaseMetaData. databaseType = DatabaseType.OTHER; productName = description = "Unknown Database"; resourceBundleExtension = ""; } /** * Returns the maximum table name length for this database vendor. */ public int getMaxTableNameLength() { int maxTableNameLength; try { Connection connection = connectionPool.getConnection(); try { DatabaseMetaData metaData = connection.getMetaData(); maxTableNameLength = metaData.getMaxTableNameLength(); if (maxTableNameLength == 0) { maxTableNameLength = 255; } } finally { connectionPool.releaseConnection(connection); } } catch (SQLException e) { LOGGER.warning("Failed to fetch database maximum table name length."); maxTableNameLength = 30; // Assume the worst. Oracle is 30 chars. } return maxTableNameLength; } /** * Constructs a database table name based up the configured table name prefix * and the Connector name. All attempts are made to make this a straight * concatenation. However if the connector name is too long or contains * invalid SQL identifier characters, then it is constructed from an MD5 hash * of the requested name. * * @param prefix the generated table name will begin with this prefix * @param connectorName the connector name */ public String makeTableName(String prefix, String connectorName) { return makeTableName(prefix, connectorName, getMaxTableNameLength()); } /** * Constructs a database table name based up the configured table name prefix * and the Connector name. All attempts are made to make this a straight * concatenation. However if the connector name is too long or contains * invalid SQL identifier characters, then we hash it. * * @param prefix the generated table name will begin with this prefix * @param connectorName the connector name * @param maxLength the maximum length of the generated table name */ @VisibleForTesting static String makeTableName(String prefix, String connectorName, int maxLength) { prefix = Strings.nullToEmpty(prefix); String suffix; if ((connectorName.matches("[a-z0-9]+[a-z0-9_]*")) && ((connectorName.length() + prefix.length()) <= maxLength)) { suffix = connectorName; } else { BasicChecksumGenerator sumGen = new BasicChecksumGenerator("MD5"); suffix = sumGen.getChecksum(connectorName); if (prefix.length() + suffix.length() > maxLength) { suffix = suffix.substring(0, maxLength - prefix.length()); } } // TODO: Match case of vendor identifiers? return (prefix + suffix).toLowerCase(); } /** * Verify that a table named {@code tableName} exists in the database. * If not, create it, using the supplied DDL statements. * * @param tableName the name of the table to find in the database. * @param createTableDdl DDL statements that may be used to create the * table if it does not exist. If {@code null}, no attempt will * be made to create the table. * * @return {@code true} if the table exists or was successfully created, * {@code false} if the table does not exist. */ public boolean verifyTableExists(String tableName, String[] createTableDdl) { try { // Try to create table. return verifyTableAndThrow(tableName, createTableDdl); } catch (SQLException e1) { // If that fails, we may have multiple clients trying to create the table // at the same time; wait a bit, then try again. try { Thread.sleep(30000); } catch (InterruptedException ignored) {} try { return verifyTableAndThrow(tableName, createTableDdl); } catch (SQLException e2) { LOGGER.log(Level.SEVERE, "Failed to create table " + tableName, e2); return false; } } } /** * Verify that a table named {@code tableName} exists in the database. * If not, create it, using the supplied DDL statements. * * @param tableName the name of the table to find in the database. * @param createTableDdl DDL statements that may be used to create the * table if it does not exist. If {@code null}, no attempt will * be made to create the table. * * @return {@code true} if the table exists or was successfully created, * {@code false} if the table does not exist. * @throws SQLException if table existence could not be determined or if * table creation fails. */ private boolean verifyTableAndThrow(String tableName, String[] createTableDdl) throws SQLException { boolean originalAutoCommit = true; Connection connection = connectionPool.getConnection(); try { originalAutoCommit = connection.getAutoCommit(); connection.setAutoCommit(false); // Check to see if the table already exists. DatabaseMetaData metaData = connection.getMetaData(); // Oracle doesn't do case-insensitive table name searches. String tablePattern; if (metaData.storesUpperCaseIdentifiers()) { tablePattern = tableName.toUpperCase(); } else if (metaData.storesLowerCaseIdentifiers()) { tablePattern = tableName.toLowerCase(); } else { tablePattern = tableName; } // Now quote '%' and '-', a special characters in search patterns. tablePattern = tablePattern.replace("%", metaData.getSearchStringEscape() + "%"); tablePattern = tablePattern.replace("_", metaData.getSearchStringEscape() + "_"); ResultSet tables = metaData.getTables(null, null, tablePattern, null); try { while (tables.next()) { if (tableName.equalsIgnoreCase(tables.getString("TABLE_NAME"))) { LOGGER.config("Found table: " + tableName); return true; } } } finally { tables.close(); } // Our table was not found. if (createTableDdl == null) { return false; } // Create the table using the supplied Create Table DDL. Statement stmt = connection.createStatement(); try { for (String ddlStatement : createTableDdl) { LOGGER.config("Creating table " + tableName + ": " + ddlStatement); stmt.executeUpdate(ddlStatement); } connection.commit(); } finally { stmt.close(); } return true; } catch (SQLException e) { try { connection.rollback(); } catch (SQLException ignored) { } throw e; } finally { try { connection.setAutoCommit(originalAutoCommit); } catch (SQLException ignored) { } connectionPool.releaseConnection(connection); } } }