Source code

001/*
002 * This library is part of OpenCms -
003 * the Open Source Content Management System
004 *
005 * Copyright (c) Alkacon Software GmbH & Co. KG (http://www.alkacon.com)
006 *
007 * This library is free software; you can redistribute it and/or
008 * modify it under the terms of the GNU Lesser General Public
009 * License as published by the Free Software Foundation; either
010 * version 2.1 of the License, or (at your option) any later version.
011 *
012 * This library is distributed in the hope that it will be useful,
013 * but WITHOUT ANY WARRANTY; without even the implied warranty of
014 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
015 * Lesser General Public License for more details.
016 *
017 * For further information about Alkacon Software GmbH & Co. KG, please see the
018 * company website: http://www.alkacon.com
019 *
020 * For further information about OpenCms, please see the
021 * project website: http://www.opencms.org
022 *
023 * You should have received a copy of the GNU Lesser General Public
024 * License along with this library; if not, write to the Free Software
025 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
026 */
027
028package org.opencms.db.generic;
029
030import org.opencms.db.CmsDbContext;
031import org.opencms.file.CmsProject;
032import org.opencms.main.CmsLog;
033import org.opencms.main.CmsRuntimeException;
034import org.opencms.util.CmsCollectionsGenericWrapper;
035import org.opencms.util.CmsStringUtil;
036import org.opencms.util.CmsUUID;
037
038import java.io.ByteArrayInputStream;
039import java.sql.Connection;
040import java.sql.PreparedStatement;
041import java.sql.ResultSet;
042import java.sql.SQLException;
043import java.sql.Statement;
044import java.util.HashMap;
045import java.util.Iterator;
046import java.util.Map;
047import java.util.Properties;
048import java.util.concurrent.ConcurrentHashMap;
049
050import org.apache.commons.logging.Log;
051
052/**
053 * Generic (ANSI-SQL) implementation of the SQL manager.<p>
054 *
055 * @since 6.0.0
056 */
057public class CmsSqlManager extends org.opencms.db.CmsSqlManager {
058
059    /** A pattern being replaced in SQL queries to generate SQL queries to access online/offline tables. */
060    protected static final String QUERY_PROJECT_SEARCH_PATTERN = "_${PROJECT}_";
061
062    /** The log object for this class. */
063    private static final Log LOG = CmsLog.getLog(CmsSqlManager.class);
064
065    /** The filename/path of the SQL query properties. */
066    private static final String QUERY_PROPERTIES = "org/opencms/db/generic/query.properties";
067
068    /** A map to cache queries with replaced search patterns. */
069    protected ConcurrentHashMap<String, String> m_cachedQueries;
070
071    /** The type ID of the driver (vfs, user, project or history) from where this SQL manager is referenced. */
072    protected int m_driverType;
073
074    /** The pool URL to get connections from the JDBC driver manager, including DBCP's pool URL prefix. */
075    protected String m_poolUrl;
076
077    /** A map holding all SQL queries. */
078    protected Map<String, String> m_queries;
079
080    /**
081     * Creates a new, empty SQL manager.<p>
082     */
083    public CmsSqlManager() {
084
085        m_cachedQueries = new ConcurrentHashMap<String, String>();
086        m_queries = new HashMap<String, String>();
087        loadQueryProperties(QUERY_PROPERTIES);
088    }
089
090    /**
091     * Creates a new instance of a SQL manager.<p>
092     *
093     * @param classname the classname of the SQL manager
094     *
095     * @return a new instance of the SQL manager
096     */
097    public static org.opencms.db.generic.CmsSqlManager getInstance(String classname) {
098
099        org.opencms.db.generic.CmsSqlManager sqlManager;
100
101        try {
102            Object objectInstance = Class.forName(classname).newInstance();
103            sqlManager = (org.opencms.db.generic.CmsSqlManager)objectInstance;
104        } catch (Throwable t) {
105            LOG.error(Messages.get().getBundle().key(Messages.LOG_SQL_MANAGER_INIT_FAILED_1, classname), t);
106            sqlManager = null;
107        }
108
109        if (CmsLog.INIT.isInfoEnabled()) {
110            CmsLog.INIT.info(Messages.get().getBundle().key(Messages.INIT_DRIVER_SQL_MANAGER_1, classname));
111        }
112
113        return sqlManager;
114
115    }
116
117    /**
118     * Replaces the project search pattern in SQL queries by the pattern _ONLINE_ or _OFFLINE_ depending on the
119     * specified project ID.<p>
120     *
121     * @param projectId the ID of the current project
122     * @param query the SQL query
123     * @return String the SQL query with the table key search pattern replaced
124     */
125    protected static String replaceProjectPattern(CmsUUID projectId, String query) {
126
127        // make the statement project dependent
128        String replacePattern = ((projectId == null) || projectId.equals(CmsProject.ONLINE_PROJECT_ID))
129        ? "_ONLINE_"
130        : "_OFFLINE_";
131        return CmsStringUtil.substitute(query, QUERY_PROJECT_SEARCH_PATTERN, replacePattern);
132    }
133
134    /**
135     * Attempts to close the connection, statement and result set after a statement has been executed.<p>
136     *
137     * @param dbc the current database context
138     * @param con the JDBC connection
139     * @param stmnt the statement
140     * @param res the result set
141     */
142    public void closeAll(CmsDbContext dbc, Connection con, Statement stmnt, ResultSet res) {
143
144        // NOTE: The following note is outdated, since we are now using HikariCP as a connection pool,
145        // which doesn't have prepared statement pooling. Closing the connection in a finally block or using
146        // the try-with-resources syntax is sufficient.
147
148        // NOTE: (outdated) we have to close Connections/Statements that way, because a dbcp PoolablePreparedStatement
149        // is not a DelegatedStatement; for that reason its not removed from the trace of the connection when it is closed.
150        // So, the connection tries to close it again when the connection is closed itself;
151        // as a result there is an error that forces the connection to be destroyed and not pooled
152
153        if (dbc == null) {
154            LOG.error(Messages.get().getBundle().key(Messages.LOG_NULL_DB_CONTEXT_0));
155        }
156
157        try {
158            // first, close the result set
159            if (res != null) {
160                res.close();
161            }
162        } catch (SQLException e) {
163            LOG.debug(e.getLocalizedMessage(), e);
164        } finally {
165            res = null;
166        }
167
168        try {
169            // close the statement
170            if (stmnt != null) {
171                stmnt.close();
172            }
173        } catch (SQLException e) {
174            LOG.debug(e.getLocalizedMessage(), e);
175        } finally {
176            stmnt = null;
177        }
178
179        try {
180            // close the connection
181            if ((con != null) && !con.isClosed()) {
182                con.close();
183            }
184        } catch (SQLException e) {
185            LOG.debug(e.getLocalizedMessage(), e);
186        } finally {
187            con = null;
188        }
189
190    }
191
192    /**
193     * Retrieves the value of the designated column in the current row of this ResultSet object as
194     * a byte array in the Java programming language.<p>
195     *
196     * The bytes represent the raw values returned by the driver. Overwrite this method if another
197     * database server requires a different handling of byte attributes in tables.<p>
198     *
199     * @param res the result set
200     * @param attributeName the name of the table attribute
201     *
202     * @return byte[] the column value; if the value is SQL NULL, the value returned is null
203     *
204     * @throws SQLException if a database access error occurs
205     */
206    public byte[] getBytes(ResultSet res, String attributeName) throws SQLException {
207
208        return res.getBytes(attributeName);
209    }
210
211    /**
212     * Returns a JDBC connection from the connection pool.<p>
213     *
214     * Use this method to get a connection for reading/writing project independent data.<p>
215     *
216     * @param dbc the current database context
217     *
218     * @return a JDBC connection
219     *
220     * @throws SQLException if the project id is not supported
221     */
222    public Connection getConnection(CmsDbContext dbc) throws SQLException {
223
224        if (dbc == null) {
225            LOG.error(Messages.get().getBundle().key(Messages.LOG_NULL_DB_CONTEXT_0));
226        }
227        // match the ID to a JDBC pool URL of the OpenCms JDBC pools {online|offline|backup}
228        return getConnectionByUrl(m_poolUrl);
229    }
230
231    /**
232     * Returns a PreparedStatement for a JDBC connection specified by the key of a SQL query
233     * and the CmsProject.<p>
234     *
235     * @param con the JDBC connection
236     * @param project the specified CmsProject
237     * @param queryKey the key of the SQL query
238     *
239     * @return PreparedStatement a new PreparedStatement containing the pre-compiled SQL statement
240     *
241     * @throws SQLException if a database access error occurs
242     */
243    public PreparedStatement getPreparedStatement(Connection con, CmsProject project, String queryKey)
244    throws SQLException {
245
246        return getPreparedStatement(con, project.getUuid(), queryKey);
247    }
248
249    /**
250     * Returns a PreparedStatement for a JDBC connection specified by the key of a SQL query
251     * and the project-ID.<p>
252     *
253     * @param con the JDBC connection
254     * @param projectId the ID of the specified CmsProject
255     * @param queryKey the key of the SQL query
256     *
257     * @return PreparedStatement a new PreparedStatement containing the pre-compiled SQL statement
258     *
259     * @throws SQLException if a database access error occurs
260     */
261    public PreparedStatement getPreparedStatement(Connection con, CmsUUID projectId, String queryKey)
262    throws SQLException {
263
264        String rawSql = readQuery(projectId, queryKey);
265        return getPreparedStatementForSql(con, rawSql);
266    }
267
268    /**
269     * Returns a PreparedStatement for a JDBC connection specified by the key of a SQL query.<p>
270     *
271     * @param con the JDBC connection
272     * @param queryKey the key of the SQL query
273     * @return PreparedStatement a new PreparedStatement containing the pre-compiled SQL statement
274     * @throws SQLException if a database access error occurs
275     */
276    public PreparedStatement getPreparedStatement(Connection con, String queryKey) throws SQLException {
277
278        String rawSql = readQuery(CmsUUID.getNullUUID(), queryKey);
279        return getPreparedStatementForSql(con, rawSql);
280    }
281
282    /**
283     * Returns a PreparedStatement for a JDBC connection specified by the SQL query.<p>
284     *
285     * @param con the JDBC connection
286     * @param query the SQL query
287     * @return PreparedStatement a new PreparedStatement containing the pre-compiled SQL statement
288     * @throws SQLException if a database access error occurs
289     */
290    public PreparedStatement getPreparedStatementForSql(Connection con, String query) throws SQLException {
291
292        // unfortunately, this wrapper is essential, because some JDBC driver
293        // implementations don't accept the delegated objects of DBCP's connection pool.
294        return con.prepareStatement(query);
295    }
296
297    /**
298     * Initializes this SQL manager.<p>
299     *
300     * @param driverType the type ID of the driver (vfs,user,project or history) from where this SQL manager is referenced
301     * @param poolUrl the pool URL to get connections from the JDBC driver manager
302     */
303    public void init(int driverType, String poolUrl) {
304
305        m_driverType = driverType;
306        m_poolUrl = poolUrl;
307
308    }
309
310    /**
311     * Searches for the SQL query with the specified key and CmsProject.<p>
312     *
313     * @param project the specified CmsProject
314     * @param queryKey the key of the SQL query
315     * @return the the SQL query in this property list with the specified key
316     */
317    public String readQuery(CmsProject project, String queryKey) {
318
319        return readQuery(project.getUuid(), queryKey);
320    }
321
322    /**
323     * Searches for the SQL query with the specified key and project-ID.<p>
324     *
325     * For projectIds &ne; 0, the pattern {@link #QUERY_PROJECT_SEARCH_PATTERN} in table names of queries is
326     * replaced with "_ONLINE_" or "_OFFLINE_" to choose the right database
327     * tables for SQL queries that are project dependent!
328     *
329     * @param projectId the ID of the specified CmsProject
330     * @param queryKey the key of the SQL query
331     * @return the the SQL query in this property list with the specified key
332     */
333    public String readQuery(CmsUUID projectId, String queryKey) {
334
335        String key;
336        if ((projectId != null) && !projectId.isNullUUID()) {
337            // id 0 is special, please see below
338            StringBuffer buffer = new StringBuffer(128);
339            buffer.append(queryKey);
340            if (projectId.equals(CmsProject.ONLINE_PROJECT_ID)) {
341                buffer.append("_ONLINE");
342            } else {
343                buffer.append("_OFFLINE");
344            }
345            key = buffer.toString();
346        } else {
347            key = queryKey;
348        }
349
350        // look up the query in the cache
351        String query = m_cachedQueries.get(key);
352
353        if (query == null) {
354            // the query has not been cached yet
355            // get the SQL statement from the properties hash
356            query = readQuery(queryKey);
357
358            if (query == null) {
359                throw new CmsRuntimeException(Messages.get().container(Messages.ERR_QUERY_NOT_FOUND_1, queryKey));
360            }
361
362            // replace control chars.
363            query = CmsStringUtil.substitute(query, "\t", " ");
364            query = CmsStringUtil.substitute(query, "\n", " ");
365
366            if ((projectId != null) && !projectId.isNullUUID()) {
367                // a project ID = 0 is an internal indicator that a project-independent
368                // query was requested - further regex operations are not required then
369                query = CmsSqlManager.replaceProjectPattern(projectId, query);
370            }
371
372            // to minimize costs, all statements with replaced expressions are cached in a map
373            m_cachedQueries.put(key, query);
374        }
375
376        return query;
377    }
378
379    /**
380     * Searches for the SQL query with the specified key.<p>
381     *
382     * @param queryKey the SQL query key
383     * @return the the SQL query in this property list with the specified key
384     */
385    public String readQuery(String queryKey) {
386
387        String value = m_queries.get(queryKey);
388        if (value == null) {
389            if (LOG.isErrorEnabled()) {
390                LOG.error(Messages.get().getBundle().key(Messages.LOG_QUERY_NOT_FOUND_1, queryKey));
391            }
392        }
393        return value;
394    }
395
396    /**
397     * Sets the designated parameter to the given Java array of bytes.<p>
398     *
399     * The driver converts this to an SQL VARBINARY or LONGVARBINARY (depending on the argument's
400     * size relative to the driver's limits on VARBINARY values) when it sends it to the database.
401     *
402     * @param statement the PreparedStatement where the content is set
403     * @param pos the first parameter is 1, the second is 2, ...
404     * @param content the parameter value
405     * @throws SQLException if a database access error occurs
406     */
407    public void setBytes(PreparedStatement statement, int pos, byte[] content) throws SQLException {
408
409        if (content.length < 2000) {
410            statement.setBytes(pos, content);
411        } else {
412            statement.setBinaryStream(pos, new ByteArrayInputStream(content), content.length);
413        }
414    }
415
416    /**
417     * Replaces null or empty Strings with a String with one space character <code>" "</code>.<p>
418     *
419     * @param value the string to validate
420     * @return the validate string or a String with one space character if the validated string is null or empty
421     */
422    public String validateEmpty(String value) {
423
424        if (CmsStringUtil.isNotEmpty(value)) {
425            return value;
426        }
427
428        return " ";
429    }
430
431    /**
432     * Loads a Java properties hash containing SQL queries.<p>
433     *
434     * @param propertyFilename the package/filename of the properties hash
435     */
436    protected void loadQueryProperties(String propertyFilename) {
437
438        Properties properties = new Properties();
439
440        try {
441            properties.load(getClass().getClassLoader().getResourceAsStream(propertyFilename));
442            m_queries.putAll(CmsCollectionsGenericWrapper.<String, String> map(properties));
443            replaceQuerySearchPatterns();
444        } catch (Throwable t) {
445            if (LOG.isErrorEnabled()) {
446                LOG.error(
447                    Messages.get().getBundle().key(Messages.LOG_LOAD_QUERY_PROP_FILE_FAILED_1, propertyFilename),
448                    t);
449            }
450
451            properties = null;
452        }
453    }
454
455    /**
456     * Replaces patterns ${XXX} by another property value, if XXX is a property key with a value.<p>
457     */
458    protected synchronized void replaceQuerySearchPatterns() {
459
460        String currentKey = null;
461        String currentValue = null;
462        int startIndex = 0;
463        int endIndex = 0;
464        int lastIndex = 0;
465
466        Iterator<String> allKeys = m_queries.keySet().iterator();
467        while (allKeys.hasNext()) {
468            currentKey = allKeys.next();
469            currentValue = m_queries.get(currentKey);
470            startIndex = 0;
471            endIndex = 0;
472            lastIndex = 0;
473
474            while ((startIndex = currentValue.indexOf("${", lastIndex)) != -1) {
475                endIndex = currentValue.indexOf('}', startIndex);
476                if ((endIndex != -1) && !currentValue.startsWith(QUERY_PROJECT_SEARCH_PATTERN, startIndex - 1)) {
477
478                    String replaceKey = currentValue.substring(startIndex + 2, endIndex);
479                    String searchPattern = currentValue.substring(startIndex, endIndex + 1);
480                    String replacePattern = this.readQuery(replaceKey);
481
482                    if (replacePattern != null) {
483                        currentValue = CmsStringUtil.substitute(currentValue, searchPattern, replacePattern);
484                    }
485                }
486
487                lastIndex = endIndex + 2;
488            }
489            m_queries.put(currentKey, currentValue);
490        }
491    }
492}