/*
* This library is part of OpenCms -
* the Open Source Content Management System
*
* Copyright (c) Alkacon Software GmbH (http://www.alkacon.com)
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* For further information about Alkacon Software GmbH, please see the
* company website: http://www.alkacon.com
*
* For further information about OpenCms, please see the
* project website: http://www.opencms.org
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*/
package org.opencms.db.generic;
import org.opencms.db.CmsDbContext;
import org.opencms.db.CmsDbPool;
import org.opencms.file.CmsProject;
import org.opencms.main.CmsLog;
import org.opencms.main.CmsRuntimeException;
import org.opencms.util.CmsCollectionsGenericWrapper;
import org.opencms.util.CmsStringUtil;
import org.opencms.util.CmsUUID;
import java.io.ByteArrayInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Properties;
import org.apache.commons.logging.Log;
/**
* Generic (ANSI-SQL) implementation of the SQL manager.<p>
*
* @since 6.0.0
*/
public class CmsSqlManager extends org.opencms.db.CmsSqlManager {
/** A pattern being replaced in SQL queries to generate SQL queries to access online/offline tables. */
protected static final String QUERY_PROJECT_SEARCH_PATTERN = "_${PROJECT}_";
/** The log object for this class. */
private static final Log LOG = CmsLog.getLog(CmsSqlManager.class);
/** The filename/path of the SQL query properties. */
private static final String QUERY_PROPERTIES = "org/opencms/db/generic/query.properties";
/** Serial version UID required for safe serialization. */
private static final long serialVersionUID = -5994026786008303964L;
/** A map to cache queries with replaced search patterns. */
protected Map<String, String> m_cachedQueries;
/** The type ID of the driver (vfs, user, project or history) from where this SQL manager is referenced. */
protected int m_driverType;
/** The pool URL to get connections from the JDBC driver manager, including DBCP's pool URL prefix. */
protected String m_poolUrl;
/** A map holding all SQL queries. */
protected Map<String, String> m_queries;
/**
* Creates a new, empty SQL manager.<p>
*/
public CmsSqlManager() {
m_cachedQueries = new HashMap<String, String>();
m_queries = new HashMap<String, String>();
loadQueryProperties(QUERY_PROPERTIES);
}
/**
* Creates a new instance of a SQL manager.<p>
*
* @param classname the classname of the SQL manager
*
* @return a new instance of the SQL manager
*/
public static org.opencms.db.generic.CmsSqlManager getInstance(String classname) {
org.opencms.db.generic.CmsSqlManager sqlManager;
try {
Object objectInstance = Class.forName(classname).newInstance();
sqlManager = (org.opencms.db.generic.CmsSqlManager)objectInstance;
} catch (Throwable t) {
LOG.error(Messages.get().getBundle().key(Messages.LOG_SQL_MANAGER_INIT_FAILED_1, classname), t);
sqlManager = null;
}
if (CmsLog.INIT.isInfoEnabled()) {
CmsLog.INIT.info(Messages.get().getBundle().key(Messages.INIT_DRIVER_SQL_MANAGER_1, classname));
}
return sqlManager;
}
/**
* Replaces the project search pattern in SQL queries by the pattern _ONLINE_ or _OFFLINE_ depending on the
* specified project ID.<p>
*
* @param projectId the ID of the current project
* @param query the SQL query
* @return String the SQL query with the table key search pattern replaced
*/
protected static String replaceProjectPattern(CmsUUID projectId, String query) {
// make the statement project dependent
String replacePattern = ((projectId == null) || projectId.equals(CmsProject.ONLINE_PROJECT_ID))
? "_ONLINE_"
: "_OFFLINE_";
return CmsStringUtil.substitute(query, QUERY_PROJECT_SEARCH_PATTERN, replacePattern);
}
/**
* Attempts to close the connection, statement and result set after a statement has been executed.<p>
*
* @param dbc the current database context
* @param con the JDBC connection
* @param stmnt the statement
* @param res the result set
*/
public void closeAll(CmsDbContext dbc, Connection con, Statement stmnt, ResultSet res) {
// NOTE: we have to close Connections/Statements that way, because a dbcp PoolablePreparedStatement
// is not a DelegatedStatement; for that reason its not removed from the trace of the connection when it is closed.
// So, the connection tries to close it again when the connection is closed itself;
// as a result there is an error that forces the connection to be destroyed and not pooled
if (dbc == null) {
LOG.error(Messages.get().getBundle().key(Messages.LOG_NULL_DB_CONTEXT_0));
}
try {
// first, close the result set
if (res != null) {
res.close();
}
} catch (SQLException e) {
LOG.debug(e.getLocalizedMessage(), e);
} finally {
res = null;
}
try {
// close the statement
if (stmnt != null) {
stmnt.close();
}
} catch (SQLException e) {
LOG.debug(e.getLocalizedMessage(), e);
} finally {
stmnt = null;
}
try {
// close the connection
if ((con != null) && !con.isClosed()) {
con.close();
}
} catch (SQLException e) {
LOG.debug(e.getLocalizedMessage(), e);
} finally {
con = null;
}
}
/**
* Retrieves the value of the designated column in the current row of this ResultSet object as
* a byte array in the Java programming language.<p>
*
* The bytes represent the raw values returned by the driver. Overwrite this method if another
* database server requires a different handling of byte attributes in tables.<p>
*
* @param res the result set
* @param attributeName the name of the table attribute
*
* @return byte[] the column value; if the value is SQL NULL, the value returned is null
*
* @throws SQLException if a database access error occurs
*/
public byte[] getBytes(ResultSet res, String attributeName) throws SQLException {
return res.getBytes(attributeName);
}
/**
* Returns a JDBC connection from the connection pool.<p>
*
* Use this method to get a connection for reading/writing project independent data.<p>
*
* @param dbc the current database context
*
* @return a JDBC connection
*
* @throws SQLException if the project id is not supported
*/
public Connection getConnection(CmsDbContext dbc) throws SQLException {
if (dbc == null) {
LOG.error(Messages.get().getBundle().key(Messages.LOG_NULL_DB_CONTEXT_0));
}
// match the ID to a JDBC pool URL of the OpenCms JDBC pools {online|offline|backup}
return getConnectionByUrl(m_poolUrl);
}
/**
* Returns a PreparedStatement for a JDBC connection specified by the key of a SQL query
* and the CmsProject.<p>
*
* @param con the JDBC connection
* @param project the specified CmsProject
* @param queryKey the key of the SQL query
*
* @return PreparedStatement a new PreparedStatement containing the pre-compiled SQL statement
*
* @throws SQLException if a database access error occurs
*/
public PreparedStatement getPreparedStatement(Connection con, CmsProject project, String queryKey)
throws SQLException {
return getPreparedStatement(con, project.getUuid(), queryKey);
}
/**
* Returns a PreparedStatement for a JDBC connection specified by the key of a SQL query
* and the project-ID.<p>
*
* @param con the JDBC connection
* @param projectId the ID of the specified CmsProject
* @param queryKey the key of the SQL query
*
* @return PreparedStatement a new PreparedStatement containing the pre-compiled SQL statement
*
* @throws SQLException if a database access error occurs
*/
public PreparedStatement getPreparedStatement(Connection con, CmsUUID projectId, String queryKey)
throws SQLException {
String rawSql = readQuery(projectId, queryKey);
return getPreparedStatementForSql(con, rawSql);
}
/**
* Returns a PreparedStatement for a JDBC connection specified by the key of a SQL query.<p>
*
* @param con the JDBC connection
* @param queryKey the key of the SQL query
* @return PreparedStatement a new PreparedStatement containing the pre-compiled SQL statement
* @throws SQLException if a database access error occurs
*/
public PreparedStatement getPreparedStatement(Connection con, String queryKey) throws SQLException {
String rawSql = readQuery(CmsUUID.getNullUUID(), queryKey);
return getPreparedStatementForSql(con, rawSql);
}
/**
* Returns a PreparedStatement for a JDBC connection specified by the SQL query.<p>
*
* @param con the JDBC connection
* @param query the SQL query
* @return PreparedStatement a new PreparedStatement containing the pre-compiled SQL statement
* @throws SQLException if a database access error occurs
*/
public PreparedStatement getPreparedStatementForSql(Connection con, String query) throws SQLException {
// unfortunately, this wrapper is essential, because some JDBC driver
// implementations don't accept the delegated objects of DBCP's connection pool.
return con.prepareStatement(query);
}
/**
* Initializes this SQL manager.<p>
*
* @param driverType the type ID of the driver (vfs,user,project or history) from where this SQL manager is referenced
* @param poolUrl the pool URL to get connections from the JDBC driver manager
*/
public void init(int driverType, String poolUrl) {
if (!poolUrl.startsWith(CmsDbPool.DBCP_JDBC_URL_PREFIX)) {
poolUrl = CmsDbPool.DBCP_JDBC_URL_PREFIX + poolUrl;
}
m_driverType = driverType;
m_poolUrl = poolUrl;
}
/**
* Searches for the SQL query with the specified key and CmsProject.<p>
*
* @param project the specified CmsProject
* @param queryKey the key of the SQL query
* @return the the SQL query in this property list with the specified key
*/
public String readQuery(CmsProject project, String queryKey) {
return readQuery(project.getUuid(), queryKey);
}
/**
* Searches for the SQL query with the specified key and project-ID.<p>
*
* For projectIds ≠ 0, the pattern {@link #QUERY_PROJECT_SEARCH_PATTERN} in table names of queries is
* replaced with "_ONLINE_" or "_OFFLINE_" to choose the right database
* tables for SQL queries that are project dependent!
*
* @param projectId the ID of the specified CmsProject
* @param queryKey the key of the SQL query
* @return the the SQL query in this property list with the specified key
*/
public String readQuery(CmsUUID projectId, String queryKey) {
String key;
if ((projectId != null) && !projectId.isNullUUID()) {
// id 0 is special, please see below
StringBuffer buffer = new StringBuffer(128);
buffer.append(queryKey);
if (projectId.equals(CmsProject.ONLINE_PROJECT_ID)) {
buffer.append("_ONLINE");
} else {
buffer.append("_OFFLINE");
}
key = buffer.toString();
} else {
key = queryKey;
}
// look up the query in the cache
String query = m_cachedQueries.get(key);
if (query == null) {
// the query has not been cached yet
// get the SQL statement from the properties hash
query = readQuery(queryKey);
if (query == null) {
throw new CmsRuntimeException(Messages.get().container(Messages.ERR_QUERY_NOT_FOUND_1, queryKey));
}
// replace control chars.
query = CmsStringUtil.substitute(query, "\t", " ");
query = CmsStringUtil.substitute(query, "\n", " ");
if ((projectId != null) && !projectId.isNullUUID()) {
// a project ID = 0 is an internal indicator that a project-independent
// query was requested - further regex operations are not required then
query = CmsSqlManager.replaceProjectPattern(projectId, query);
}
// to minimize costs, all statements with replaced expressions are cached in a map
m_cachedQueries.put(key, query);
}
return query;
}
/**
* Searches for the SQL query with the specified key.<p>
*
* @param queryKey the SQL query key
* @return the the SQL query in this property list with the specified key
*/
public String readQuery(String queryKey) {
String value = m_queries.get(queryKey);
if (value == null) {
if (LOG.isErrorEnabled()) {
LOG.error(Messages.get().getBundle().key(Messages.LOG_QUERY_NOT_FOUND_1, queryKey));
}
}
return value;
}
/**
* Sets the designated parameter to the given Java array of bytes.<p>
*
* The driver converts this to an SQL VARBINARY or LONGVARBINARY (depending on the argument's
* size relative to the driver's limits on VARBINARY values) when it sends it to the database.
*
* @param statement the PreparedStatement where the content is set
* @param pos the first parameter is 1, the second is 2, ...
* @param content the parameter value
* @throws SQLException if a database access error occurs
*/
public void setBytes(PreparedStatement statement, int pos, byte[] content) throws SQLException {
if (content.length < 2000) {
statement.setBytes(pos, content);
} else {
statement.setBinaryStream(pos, new ByteArrayInputStream(content), content.length);
}
}
/**
* Replaces null or empty Strings with a String with one space character <code>" "</code>.<p>
*
* @param value the string to validate
* @return the validate string or a String with one space character if the validated string is null or empty
*/
public String validateEmpty(String value) {
if (CmsStringUtil.isNotEmpty(value)) {
return value;
}
return " ";
}
/**
* Loads a Java properties hash containing SQL queries.<p>
*
* @param propertyFilename the package/filename of the properties hash
*/
protected void loadQueryProperties(String propertyFilename) {
Properties properties = new Properties();
try {
properties.load(getClass().getClassLoader().getResourceAsStream(propertyFilename));
m_queries.putAll(CmsCollectionsGenericWrapper.<String, String> map(properties));
replaceQuerySearchPatterns();
} catch (Throwable t) {
if (LOG.isErrorEnabled()) {
LOG.error(
Messages.get().getBundle().key(Messages.LOG_LOAD_QUERY_PROP_FILE_FAILED_1, propertyFilename),
t);
}
properties = null;
}
}
/**
* Replaces patterns ${XXX} by another property value, if XXX is a property key with a value.<p>
*/
protected synchronized void replaceQuerySearchPatterns() {
String currentKey = null;
String currentValue = null;
int startIndex = 0;
int endIndex = 0;
int lastIndex = 0;
Iterator<String> allKeys = m_queries.keySet().iterator();
while (allKeys.hasNext()) {
currentKey = allKeys.next();
currentValue = m_queries.get(currentKey);
startIndex = 0;
endIndex = 0;
lastIndex = 0;
while ((startIndex = currentValue.indexOf("${", lastIndex)) != -1) {
endIndex = currentValue.indexOf('}', startIndex);
if ((endIndex != -1) && !currentValue.startsWith(QUERY_PROJECT_SEARCH_PATTERN, startIndex - 1)) {
String replaceKey = currentValue.substring(startIndex + 2, endIndex);
String searchPattern = currentValue.substring(startIndex, endIndex + 1);
String replacePattern = this.readQuery(replaceKey);
if (replacePattern != null) {
currentValue = CmsStringUtil.substitute(currentValue, searchPattern, replacePattern);
}
}
lastIndex = endIndex + 2;
}
m_queries.put(currentKey, currentValue);
}
}
}