/*
* 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, 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.configuration.CmsConfigurationManager;
import org.opencms.configuration.CmsParameterConfiguration;
import org.opencms.db.CmsDbConsistencyException;
import org.opencms.db.CmsDbContext;
import org.opencms.db.CmsDbSqlException;
import org.opencms.db.CmsDriverManager;
import org.opencms.db.CmsPreparedStatementLongParameter;
import org.opencms.db.CmsPreparedStatementStringParameter;
import org.opencms.db.CmsSubscriptionFilter;
import org.opencms.db.CmsSubscriptionReadMode;
import org.opencms.db.CmsVisitEntry;
import org.opencms.db.CmsVisitEntryFilter;
import org.opencms.db.CmsVisitedByFilter;
import org.opencms.db.I_CmsDriver;
import org.opencms.db.I_CmsPreparedStatementParameter;
import org.opencms.db.I_CmsSubscriptionDriver;
import org.opencms.file.CmsDataAccessException;
import org.opencms.file.CmsGroup;
import org.opencms.file.CmsResource;
import org.opencms.file.CmsUser;
import org.opencms.file.history.I_CmsHistoryResource;
import org.opencms.main.CmsException;
import org.opencms.main.CmsLog;
import org.opencms.main.OpenCms;
import org.opencms.security.CmsPrincipal;
import org.opencms.util.CmsFileUtil;
import org.opencms.util.CmsPair;
import org.opencms.util.CmsStringUtil;
import org.opencms.util.CmsUUID;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import org.apache.commons.logging.Log;
/**
* Generic implementation of the user tracking and subscription driver interface.<p>
*
* @since 8.0.0
*/
public class CmsSubscriptionDriver implements I_CmsDriver, I_CmsSubscriptionDriver {
/** The log object for this class. */
private static final Log LOG = CmsLog.getLog(org.opencms.db.generic.CmsSubscriptionDriver.class);
/** A reference to the driver manager used by this driver. */
protected CmsDriverManager m_driverManager;
/** The SQL manager used by this driver. */
protected CmsSqlManager m_sqlManager;
/**
* @see org.opencms.db.I_CmsSubscriptionDriver#deleteVisits(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.db.CmsVisitEntryFilter)
*/
public void deleteVisits(CmsDbContext dbc, String poolName, CmsVisitEntryFilter filter)
throws CmsDataAccessException {
Connection conn = null;
PreparedStatement stmt = null;
try {
if (CmsStringUtil.isNotEmpty(poolName)) {
conn = m_sqlManager.getConnection(poolName);
} else {
conn = m_sqlManager.getConnection(dbc);
}
// compose statement
StringBuffer queryBuf = new StringBuffer(256);
queryBuf.append(m_sqlManager.readQuery("C_VISIT_DELETE_ENTRIES"));
CmsPair<String, List<I_CmsPreparedStatementParameter>> conditionsAndParams = prepareVisitConditions(filter);
queryBuf.append(conditionsAndParams.getFirst());
if (LOG.isDebugEnabled()) {
LOG.debug(queryBuf.toString());
}
stmt = m_sqlManager.getPreparedStatementForSql(conn, queryBuf.toString());
List<I_CmsPreparedStatementParameter> params = conditionsAndParams.getSecond();
for (int i = 0; i < params.size(); i++) {
I_CmsPreparedStatementParameter param = conditionsAndParams.getSecond().get(i);
param.insertIntoStatement(stmt, i + 1);
}
// execute
stmt.executeUpdate();
} catch (SQLException e) {
throw new CmsDbSqlException(Messages.get().container(
Messages.ERR_GENERIC_SQL_1,
CmsDbSqlException.getErrorQuery(stmt)), e);
} finally {
m_sqlManager.closeAll(dbc, conn, stmt, null);
}
}
/**
* @see org.opencms.db.I_CmsSubscriptionDriver#getDateLastVisitedBy(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.file.CmsUser, org.opencms.file.CmsResource)
*/
public long getDateLastVisitedBy(CmsDbContext dbc, String poolName, CmsUser user, CmsResource resource)
throws CmsException {
CmsVisitEntryFilter filter = CmsVisitEntryFilter.ALL.filterResource(resource.getStructureId()).filterUser(
user.getId());
List<CmsVisitEntry> entries = readVisits(dbc, poolName, filter);
if (!entries.isEmpty()) {
return entries.get(0).getDate();
}
return 0;
}
/**
* @see org.opencms.db.I_CmsSubscriptionDriver#getSqlManager()
*/
public CmsSqlManager getSqlManager() {
return m_sqlManager;
}
/**
* @see org.opencms.db.I_CmsDriver#init(org.opencms.db.CmsDbContext, org.opencms.configuration.CmsConfigurationManager, java.util.List, org.opencms.db.CmsDriverManager)
*/
public void init(
CmsDbContext dbc,
CmsConfigurationManager configurationManager,
List<String> successiveDrivers,
CmsDriverManager driverManager) {
CmsParameterConfiguration config = configurationManager.getConfiguration();
String poolUrl = config.get("db.subscription.pool");
String classname = config.get("db.subscription.sqlmanager");
m_sqlManager = initSqlManager(classname);
m_sqlManager.init(I_CmsSubscriptionDriver.DRIVER_TYPE_ID, poolUrl);
m_driverManager = driverManager;
if (CmsLog.INIT.isInfoEnabled()) {
CmsLog.INIT.info(Messages.get().getBundle().key(Messages.INIT_ASSIGNED_POOL_1, poolUrl));
}
if ((successiveDrivers != null) && !successiveDrivers.isEmpty()) {
if (LOG.isWarnEnabled()) {
LOG.warn(Messages.get().getBundle().key(
Messages.LOG_SUCCESSIVE_DRIVERS_UNSUPPORTED_1,
getClass().getName()));
}
}
}
/**
* @see org.opencms.db.I_CmsSubscriptionDriver#initSqlManager(java.lang.String)
*/
public org.opencms.db.generic.CmsSqlManager initSqlManager(String classname) {
return CmsSqlManager.getInstance(classname);
}
/**
* @see org.opencms.db.I_CmsSubscriptionDriver#markResourceAsVisitedBy(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.file.CmsResource, org.opencms.file.CmsUser)
*/
public void markResourceAsVisitedBy(CmsDbContext dbc, String poolName, CmsResource resource, CmsUser user)
throws CmsDataAccessException {
boolean entryExists = false;
CmsVisitEntryFilter filter = CmsVisitEntryFilter.ALL.filterResource(resource.getStructureId()).filterUser(
user.getId());
// delete existing visited entry for the resource
if (readVisits(dbc, OpenCms.getSubscriptionManager().getPoolName(), filter).size() > 0) {
entryExists = true;
deleteVisits(dbc, OpenCms.getSubscriptionManager().getPoolName(), filter);
}
CmsVisitEntry entry = new CmsVisitEntry(user.getId(), System.currentTimeMillis(), resource.getStructureId());
addVisit(dbc, poolName, entry);
if (!entryExists) {
// new entry, check if maximum number of stored visited resources is exceeded
PreparedStatement stmt = null;
Connection conn = null;
ResultSet res = null;
int count = 0;
List<Long> dates = new ArrayList<Long>();
try {
conn = m_sqlManager.getConnection(poolName);
stmt = m_sqlManager.getPreparedStatement(conn, dbc.currentProject(), "C_VISITED_USER_COUNT_1");
stmt.setString(1, user.getId().toString());
res = stmt.executeQuery();
if (res.next()) {
count = res.getInt(1);
while (res.next()) {
// do nothing only move through all rows because of mssql odbc driver
}
} else {
throw new CmsDbConsistencyException(Messages.get().container(
Messages.ERR_COUNTING_VISITED_RESOURCES_1,
user.getName()));
}
int maxCount = OpenCms.getSubscriptionManager().getMaxVisitedCount();
if (count > maxCount) {
// delete old visited entries
m_sqlManager.closeAll(dbc, null, stmt, res);
stmt = m_sqlManager.getPreparedStatement(
conn,
dbc.currentProject(),
"C_VISITED_USER_DELETE_GETDATE_2");
stmt.setString(1, user.getId().toString());
stmt.setInt(2, count - maxCount);
res = stmt.executeQuery();
while (res.next()) {
// get last date of result set
dates.add(Long.valueOf(res.getLong(1)));
}
}
} catch (SQLException e) {
throw new CmsDbSqlException(Messages.get().container(
Messages.ERR_GENERIC_SQL_1,
CmsDbSqlException.getErrorQuery(stmt)), e);
} finally {
m_sqlManager.closeAll(dbc, conn, stmt, res);
}
long deleteDate = 0;
for (Long date : dates) {
deleteDate = date.longValue();
if (deleteDate > 0) {
filter = CmsVisitEntryFilter.ALL.filterUser(user.getId()).filterTo(deleteDate);
deleteVisits(dbc, OpenCms.getSubscriptionManager().getPoolName(), filter);
}
}
}
}
/**
* @see org.opencms.db.I_CmsSubscriptionDriver#readAllSubscribedResources(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.security.CmsPrincipal)
*/
public List<CmsResource> readAllSubscribedResources(CmsDbContext dbc, String poolName, CmsPrincipal principal)
throws CmsDataAccessException {
PreparedStatement stmt = null;
Connection conn = null;
ResultSet res = null;
CmsResource currentResource = null;
List<CmsResource> resources = new ArrayList<CmsResource>();
try {
conn = m_sqlManager.getConnection(poolName);
stmt = m_sqlManager.getPreparedStatement(conn, dbc.currentProject(), "C_SUBSCRIPTION_READ_ALL_1");
stmt.setString(1, principal.getId().toString());
res = stmt.executeQuery();
while (res.next()) {
currentResource = m_driverManager.getVfsDriver(dbc).createFile(
res,
dbc.currentProject().getUuid(),
false);
resources.add(currentResource);
}
} catch (SQLException e) {
throw new CmsDbSqlException(Messages.get().container(
Messages.ERR_GENERIC_SQL_1,
CmsDbSqlException.getErrorQuery(stmt)), e);
} finally {
m_sqlManager.closeAll(dbc, conn, stmt, res);
}
return resources;
}
/**
* @see org.opencms.db.I_CmsSubscriptionDriver#readResourcesVisitedBy(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.db.CmsVisitedByFilter)
*/
public List<CmsResource> readResourcesVisitedBy(CmsDbContext dbc, String poolName, CmsVisitedByFilter filter)
throws CmsDataAccessException {
PreparedStatement stmt = null;
Connection conn = null;
ResultSet res = null;
CmsResource currentResource = null;
StringBuffer conditions = new StringBuffer(256);
List<String> params = new ArrayList<String>(1);
List<CmsResource> resources = new ArrayList<CmsResource>();
try {
// path filter
if (CmsStringUtil.isNotEmpty(filter.getParentPath())) {
CmsResource parent = m_driverManager.getVfsDriver(dbc).readResource(
dbc,
dbc.currentProject().getUuid(),
filter.getParentPath(),
false);
conditions.append(BEGIN_INCLUDE_CONDITION);
if (filter.isIncludeSubFolders()) {
conditions.append(m_sqlManager.readQuery(dbc.currentProject(), "C_RESOURCES_SELECT_BY_PATH_PREFIX"));
params.add(CmsFileUtil.addTrailingSeparator(CmsVfsDriver.escapeDbWildcard(filter.getParentPath()))
+ "%");
} else {
conditions.append(m_sqlManager.readQuery(dbc.currentProject(), "C_RESOURCES_SELECT_BY_PARENT_UUID"));
params.add(parent.getStructureId().toString());
}
conditions.append(END_CONDITION);
}
conn = m_sqlManager.getConnection(poolName);
String query = m_sqlManager.readQuery(dbc.currentProject(), "C_VISITED_USER_READ_4");
query = CmsStringUtil.substitute(query, "%(CONDITIONS)", conditions.toString());
stmt = m_sqlManager.getPreparedStatementForSql(conn, query);
stmt.setString(1, filter.getUser().getId().toString());
stmt.setLong(2, filter.getFromDate());
stmt.setLong(3, filter.getToDate());
for (int i = 0; i < params.size(); i++) {
stmt.setString(i + 4, params.get(i));
}
res = stmt.executeQuery();
while (res.next()) {
currentResource = m_driverManager.getVfsDriver(dbc).createFile(
res,
dbc.currentProject().getUuid(),
false);
resources.add(currentResource);
}
} catch (SQLException e) {
throw new CmsDbSqlException(Messages.get().container(
Messages.ERR_GENERIC_SQL_1,
CmsDbSqlException.getErrorQuery(stmt)), e);
} finally {
m_sqlManager.closeAll(dbc, conn, stmt, res);
}
return resources;
}
/**
* @see org.opencms.db.I_CmsSubscriptionDriver#readSubscribedDeletedResources(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.file.CmsUser, java.util.List, org.opencms.file.CmsResource, boolean, long)
*/
public List<I_CmsHistoryResource> readSubscribedDeletedResources(
CmsDbContext dbc,
String poolName,
CmsUser user,
List<CmsGroup> groups,
CmsResource parent,
boolean includeSubFolders,
long deletedFrom) throws CmsDataAccessException {
PreparedStatement stmt = null;
Connection conn = null;
ResultSet res = null;
List<I_CmsHistoryResource> resources = new ArrayList<I_CmsHistoryResource>();
Set<CmsUUID> historyIDs = new HashSet<CmsUUID>();
List<String> principalIds = new ArrayList<String>();
// add user ID
principalIds.add(user.getId().toString());
// add group IDs
if ((groups != null) && !groups.isEmpty()) {
Iterator<CmsGroup> it = groups.iterator();
while (it.hasNext()) {
principalIds.add(it.next().getId().toString());
}
}
StringBuffer conditions = new StringBuffer(256);
List<String> params = new ArrayList<String>();
conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETED"));
if (principalIds.size() == 1) {
// single principal filter
conditions.append(BEGIN_INCLUDE_CONDITION);
conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETED_FILTER_PRINCIPAL_SINGLE"));
params.add(principalIds.get(0));
conditions.append(END_CONDITION);
} else {
// multiple principals filter
conditions.append(BEGIN_INCLUDE_CONDITION);
conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETED_FILTER_PRINCIPALS"));
conditions.append(BEGIN_CONDITION);
Iterator<String> it = principalIds.iterator();
while (it.hasNext()) {
params.add(it.next());
conditions.append("?");
if (it.hasNext()) {
conditions.append(", ");
}
}
conditions.append(END_CONDITION);
conditions.append(END_CONDITION);
}
try {
conn = m_sqlManager.getConnection(poolName);
stmt = m_sqlManager.getPreparedStatementForSql(conn, conditions.toString());
// set parameters
stmt.setLong(1, deletedFrom);
for (int i = 0; i < params.size(); i++) {
stmt.setString(i + 2, params.get(i));
}
res = stmt.executeQuery();
while (res.next()) {
historyIDs.add(new CmsUUID(res.getString(1)));
}
} catch (SQLException e) {
throw new CmsDbSqlException(Messages.get().container(
Messages.ERR_GENERIC_SQL_1,
CmsDbSqlException.getErrorQuery(stmt)), e);
} finally {
m_sqlManager.closeAll(dbc, conn, stmt, res);
}
// get the matching history resources from the found structure IDs
String parentFolderPath = "";
if (parent != null) {
parentFolderPath = CmsResource.getFolderPath(parent.getRootPath());
}
for (Iterator<CmsUUID> i = historyIDs.iterator(); i.hasNext();) {
CmsUUID id = i.next();
int version = m_driverManager.getHistoryDriver(dbc).readLastVersion(dbc, id);
if (version > 0) {
I_CmsHistoryResource histRes = m_driverManager.getHistoryDriver(dbc).readResource(dbc, id, version);
if (parent != null) {
if (!includeSubFolders
&& !parentFolderPath.equals(CmsResource.getFolderPath(histRes.getRootPath()))) {
// deleted history resource is not in the specified parent folder, skip it
continue;
} else if (includeSubFolders && !histRes.getRootPath().startsWith(parentFolderPath)) {
// deleted history resource is not in the specified parent folder or sub folder, skip it
continue;
}
}
resources.add(histRes);
}
}
return resources;
}
/**
* @see org.opencms.db.I_CmsSubscriptionDriver#readSubscribedResources(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.db.CmsSubscriptionFilter)
*/
public List<CmsResource> readSubscribedResources(CmsDbContext dbc, String poolName, CmsSubscriptionFilter filter)
throws CmsDataAccessException {
PreparedStatement stmt = null;
Connection conn = null;
ResultSet res = null;
CmsResource currentResource = null;
List<CmsResource> resources = new ArrayList<CmsResource>();
String queryBuf = m_sqlManager.readQuery(dbc.currentProject(), "C_SUBSCRIPTION_FILTER_READ");
StringBuffer conditions = new StringBuffer(256);
List<I_CmsPreparedStatementParameter> params = new ArrayList<I_CmsPreparedStatementParameter>();
boolean userDefined = filter.getUser() != null;
boolean groupsDefined = !filter.getGroups().isEmpty();
if (!groupsDefined && !userDefined) {
filter.setUser(dbc.currentUser());
userDefined = true;
}
// check if a user has been set for the "visited" and "unvisited" mode
if (!filter.getMode().isAll() && (filter.getUser() == null)) {
// change the mode, without user the other modes are not applicable
filter.setMode(CmsSubscriptionReadMode.ALL);
}
List<String> principalIds = new ArrayList<String>();
// add user ID
if (userDefined) {
principalIds.add(filter.getUser().getId().toString());
}
// add group IDs
if (groupsDefined) {
Iterator<CmsGroup> it = filter.getGroups().iterator();
while (it.hasNext()) {
principalIds.add(it.next().getId().toString());
}
}
if (principalIds.size() == 1) {
// single principal filter
conditions.append(BEGIN_CONDITION);
conditions.append(m_sqlManager.readQuery(dbc.currentProject(), "C_SUBSCRIPTION_FILTER_PRINCIPAL_SINGLE"));
params.add(new CmsPreparedStatementStringParameter(principalIds.get(0)));
conditions.append(END_CONDITION);
} else {
// multiple principals filter
conditions.append(BEGIN_CONDITION);
conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_FILTER_PRINCIPALS"));
conditions.append(BEGIN_CONDITION);
Iterator<String> it = principalIds.iterator();
while (it.hasNext()) {
params.add(new CmsPreparedStatementStringParameter(it.next()));
conditions.append("?");
if (it.hasNext()) {
conditions.append(", ");
}
}
conditions.append(END_CONDITION);
conditions.append(m_sqlManager.readQuery(dbc.currentProject(), "C_SUBSCRIPTION_FILTER_PRINCIPALS_END"));
conditions.append(END_CONDITION);
}
// path filter
if (CmsStringUtil.isNotEmpty(filter.getParentPath())) {
CmsResource parent = m_driverManager.getVfsDriver(dbc).readResource(
dbc,
dbc.currentProject().getUuid(),
filter.getParentPath(),
false);
conditions.append(BEGIN_INCLUDE_CONDITION);
if (filter.isIncludeSubFolders()) {
conditions.append(m_sqlManager.readQuery(dbc.currentProject(), "C_RESOURCES_SELECT_BY_PATH_PREFIX"));
params.add(new CmsPreparedStatementStringParameter(
CmsFileUtil.addTrailingSeparator(CmsVfsDriver.escapeDbWildcard(filter.getParentPath())) + "%"));
} else {
conditions.append(m_sqlManager.readQuery(dbc.currentProject(), "C_RESOURCES_SELECT_BY_PARENT_UUID"));
params.add(new CmsPreparedStatementStringParameter(parent.getStructureId().toString()));
}
conditions.append(END_CONDITION);
}
// check from and to date
if ((filter.getFromDate() > 0) || (filter.getToDate() < Long.MAX_VALUE)) {
conditions.append(BEGIN_INCLUDE_CONDITION);
conditions.append(m_sqlManager.readQuery(
dbc.currentProject(),
"C_SUBSCRIPTION_FILTER_RESOURCES_DATE_MODIFIED"));
params.add(new CmsPreparedStatementLongParameter(filter.getFromDate()));
params.add(new CmsPreparedStatementLongParameter(filter.getToDate()));
conditions.append(END_CONDITION);
}
try {
conn = m_sqlManager.getConnection(poolName);
queryBuf = CmsStringUtil.substitute(queryBuf, "%(CONDITIONS)", conditions.toString());
if (LOG.isDebugEnabled()) {
LOG.debug(queryBuf.toString());
}
stmt = m_sqlManager.getPreparedStatementForSql(conn, queryBuf);
// set parameters
for (int i = 0; i < params.size(); i++) {
I_CmsPreparedStatementParameter param = params.get(i);
param.insertIntoStatement(stmt, i + 1);
}
res = stmt.executeQuery();
while (res.next()) {
currentResource = m_driverManager.getVfsDriver(dbc).createFile(
res,
dbc.currentProject().getUuid(),
false);
resources.add(currentResource);
}
} catch (SQLException e) {
throw new CmsDbSqlException(Messages.get().container(
Messages.ERR_GENERIC_SQL_1,
CmsDbSqlException.getErrorQuery(stmt)), e);
} finally {
m_sqlManager.closeAll(dbc, conn, stmt, res);
}
// filter the result if in visited/unvisited mode (faster as creating a query with even more joined tables)
if (!filter.getMode().isAll()) {
List<CmsResource> result = new ArrayList<CmsResource>(resources.size());
for (Iterator<CmsResource> i = resources.iterator(); i.hasNext();) {
CmsResource resource = i.next();
long visitedDate = 0;
try {
visitedDate = m_driverManager.getDateLastVisitedBy(dbc, poolName, filter.getUser(), resource);
} catch (CmsException e) {
throw new CmsDbSqlException(Messages.get().container(Messages.ERR_GENERIC_SQL_0), e);
}
if (filter.getMode().isUnVisited() && (visitedDate >= resource.getDateLastModified())) {
// unvisited mode: resource was visited after the last modification, skip it
continue;
}
if (filter.getMode().isVisited() && (resource.getDateLastModified() > visitedDate)) {
// visited mode: resource was not visited after last modification, skip it
continue;
}
// add the current resource to the result
result.add(resource);
}
resources = result;
}
return resources;
}
/**
* Reads {@link CmsVisitEntry} objects from the database.<p>
*
* @param dbc the database context to use
* @param poolName the name of the pool which should be used for the database operation
* @param filter a filter for constraining the list of results
*
* @return a list of visit entries
*
* @throws CmsDataAccessException if the database operation fails
*/
public List<CmsVisitEntry> readVisits(CmsDbContext dbc, String poolName, CmsVisitEntryFilter filter)
throws CmsDataAccessException {
List<CmsVisitEntry> entries = new ArrayList<CmsVisitEntry>();
Connection conn = null;
PreparedStatement stmt = null;
ResultSet res = null;
try {
if (CmsStringUtil.isNotEmpty(poolName)) {
conn = m_sqlManager.getConnection(poolName);
} else {
conn = m_sqlManager.getConnection(dbc);
}
// compose statement
StringBuffer queryBuf = new StringBuffer(256);
queryBuf.append(m_sqlManager.readQuery("C_VISIT_READ_ENTRIES"));
CmsPair<String, List<I_CmsPreparedStatementParameter>> conditionsAndParameters = prepareVisitConditions(filter);
List<I_CmsPreparedStatementParameter> params = conditionsAndParameters.getSecond();
queryBuf.append(conditionsAndParameters.getFirst());
if (LOG.isDebugEnabled()) {
LOG.debug(queryBuf.toString());
}
stmt = m_sqlManager.getPreparedStatementForSql(conn, queryBuf.toString());
for (int i = 0; i < params.size(); i++) {
I_CmsPreparedStatementParameter param = params.get(i);
param.insertIntoStatement(stmt, i + 1);
}
// execute
res = stmt.executeQuery();
while (res.next()) {
// get results
entries.add(internalReadVisitEntry(res));
}
} catch (SQLException e) {
throw new CmsDbSqlException(Messages.get().container(
Messages.ERR_GENERIC_SQL_1,
CmsDbSqlException.getErrorQuery(stmt)), e);
} finally {
m_sqlManager.closeAll(dbc, conn, stmt, res);
}
return entries;
}
/**
* @see org.opencms.db.I_CmsSubscriptionDriver#setSubscribedResourceAsDeleted(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.file.CmsResource)
*/
public void setSubscribedResourceAsDeleted(CmsDbContext dbc, String poolName, CmsResource resource)
throws CmsDataAccessException {
PreparedStatement stmt = null;
Connection conn = null;
long deletedTime = System.currentTimeMillis();
try {
conn = getSqlManager().getConnection(poolName);
// set resource as deleted for all users and groups
stmt = m_sqlManager.getPreparedStatement(conn, "C_SUBSCRIPTION_UPDATE_DATE_2");
stmt.setLong(1, deletedTime);
stmt.setString(2, resource.getStructureId().toString());
stmt.executeUpdate();
} catch (SQLException e) {
throw new CmsDbSqlException(Messages.get().container(
Messages.ERR_GENERIC_SQL_1,
CmsDbSqlException.getErrorQuery(stmt)), e);
} finally {
m_sqlManager.closeAll(dbc, conn, stmt, null);
}
}
/**
* @see org.opencms.db.I_CmsSubscriptionDriver#subscribeResourceFor(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.security.CmsPrincipal, org.opencms.file.CmsResource)
*/
public void subscribeResourceFor(CmsDbContext dbc, String poolName, CmsPrincipal principal, CmsResource resource)
throws CmsDataAccessException {
ResultSet res = null;
PreparedStatement stmt = null;
Connection conn = null;
try {
conn = getSqlManager().getConnection(poolName);
stmt = m_sqlManager.getPreparedStatement(conn, "C_SUBSCRIPTION_CHECK_2");
stmt.setString(1, principal.getId().toString());
stmt.setString(2, resource.getStructureId().toString());
res = stmt.executeQuery();
// only create subscription entry if principal is not subscribed to resource
if (res.next()) {
while (res.next()) {
// do nothing only move through all rows because of mssql odbc driver
}
} else {
// subscribe principal
m_sqlManager.closeAll(dbc, null, stmt, null);
stmt = m_sqlManager.getPreparedStatement(conn, "C_SUBSCRIPTION_CREATE_2");
stmt.setString(1, principal.getId().toString());
stmt.setString(2, resource.getStructureId().toString());
stmt.executeUpdate();
}
} catch (SQLException e) {
throw new CmsDbSqlException(Messages.get().container(
Messages.ERR_GENERIC_SQL_1,
CmsDbSqlException.getErrorQuery(stmt)), e);
} finally {
m_sqlManager.closeAll(dbc, conn, stmt, res);
}
}
/**
* @see org.opencms.db.I_CmsSubscriptionDriver#unsubscribeAllDeletedResources(org.opencms.db.CmsDbContext, java.lang.String, long)
*/
public void unsubscribeAllDeletedResources(CmsDbContext dbc, String poolName, long deletedTo)
throws CmsDataAccessException {
PreparedStatement stmt = null;
Connection conn = null;
try {
conn = getSqlManager().getConnection(poolName);
StringBuffer conditions = new StringBuffer(256);
// unsubscribe all deleted resources
conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETE"));
conditions.append(BEGIN_CONDITION);
conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETE_FILTER_DATE"));
conditions.append(END_CONDITION);
stmt = m_sqlManager.getPreparedStatementForSql(conn, conditions.toString());
stmt.setLong(1, deletedTo);
stmt.executeUpdate();
} catch (SQLException e) {
throw new CmsDbSqlException(Messages.get().container(
Messages.ERR_GENERIC_SQL_1,
CmsDbSqlException.getErrorQuery(stmt)), e);
} finally {
m_sqlManager.closeAll(dbc, conn, stmt, null);
}
}
/**
* @see org.opencms.db.I_CmsSubscriptionDriver#unsubscribeAllResourcesFor(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.security.CmsPrincipal)
*/
public void unsubscribeAllResourcesFor(CmsDbContext dbc, String poolName, CmsPrincipal principal)
throws CmsDataAccessException {
PreparedStatement stmt = null;
Connection conn = null;
try {
if (principal != null) {
conn = getSqlManager().getConnection(poolName);
StringBuffer conditions = new StringBuffer(256);
conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETE"));
conditions.append(BEGIN_CONDITION);
conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETE_FILTER_PRINCIPAL"));
conditions.append(END_CONDITION);
stmt = m_sqlManager.getPreparedStatementForSql(conn, conditions.toString());
stmt.setString(1, principal.getId().toString());
stmt.executeUpdate();
}
} catch (SQLException e) {
throw new CmsDbSqlException(Messages.get().container(
Messages.ERR_GENERIC_SQL_1,
CmsDbSqlException.getErrorQuery(stmt)), e);
} finally {
m_sqlManager.closeAll(dbc, conn, stmt, null);
}
}
/**
* @see org.opencms.db.I_CmsSubscriptionDriver#unsubscribeResourceFor(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.security.CmsPrincipal, org.opencms.file.CmsResource)
*/
public void unsubscribeResourceFor(CmsDbContext dbc, String poolName, CmsPrincipal principal, CmsResource resource)
throws CmsDataAccessException {
PreparedStatement stmt = null;
Connection conn = null;
try {
conn = getSqlManager().getConnection(poolName);
StringBuffer conditions = new StringBuffer(256);
conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETE"));
conditions.append(BEGIN_CONDITION);
conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETE_FILTER_PRINCIPAL"));
conditions.append(END_CONDITION);
conditions.append(BEGIN_INCLUDE_CONDITION);
conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETE_FILTER_STRUCTURE"));
conditions.append(END_CONDITION);
stmt = m_sqlManager.getPreparedStatementForSql(conn, conditions.toString());
stmt.setString(1, principal.getId().toString());
stmt.setString(2, resource.getStructureId().toString());
stmt.executeUpdate();
} catch (SQLException e) {
throw new CmsDbSqlException(Messages.get().container(
Messages.ERR_GENERIC_SQL_1,
CmsDbSqlException.getErrorQuery(stmt)), e);
} finally {
m_sqlManager.closeAll(dbc, conn, stmt, null);
}
}
/**
* @see org.opencms.db.I_CmsSubscriptionDriver#unsubscribeResourceForAll(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.file.CmsResource)
*/
public void unsubscribeResourceForAll(CmsDbContext dbc, String poolName, CmsResource resource)
throws CmsDataAccessException {
PreparedStatement stmt = null;
Connection conn = null;
try {
conn = getSqlManager().getConnection(poolName);
StringBuffer conditions = new StringBuffer(256);
// unsubscribe resource for all principals
conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETE"));
conditions.append(BEGIN_CONDITION);
conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETE_FILTER_STRUCTURE"));
conditions.append(END_CONDITION);
stmt = m_sqlManager.getPreparedStatementForSql(conn, conditions.toString());
stmt.setString(1, resource.getStructureId().toString());
stmt.executeUpdate();
} catch (SQLException e) {
throw new CmsDbSqlException(Messages.get().container(
Messages.ERR_GENERIC_SQL_1,
CmsDbSqlException.getErrorQuery(stmt)), e);
} finally {
m_sqlManager.closeAll(dbc, conn, stmt, null);
}
}
/**
* Adds an entry to the table of visits.<p>
*
* @param dbc the database context to use
* @param poolName the name of the database pool to use
* @param visit the visit bean
*
* @throws CmsDbSqlException if the database operation fails
*/
protected void addVisit(CmsDbContext dbc, String poolName, CmsVisitEntry visit) throws CmsDbSqlException {
Connection conn = null;
PreparedStatement stmt = null;
try {
if (CmsStringUtil.isNotEmpty(poolName)) {
conn = m_sqlManager.getConnection(poolName);
} else {
conn = m_sqlManager.getConnection(dbc);
}
stmt = m_sqlManager.getPreparedStatement(conn, "C_VISIT_CREATE_3");
stmt.setString(1, visit.getUserId().toString());
stmt.setLong(2, visit.getDate());
stmt.setString(3, visit.getStructureId() == null ? null : visit.getStructureId().toString());
try {
stmt.executeUpdate();
} catch (SQLException e) {
// ignore, most likely a duplicate entry
LOG.debug(
Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)).key(),
e);
}
} catch (SQLException e) {
throw new CmsDbSqlException(Messages.get().container(
Messages.ERR_GENERIC_SQL_1,
CmsDbSqlException.getErrorQuery(stmt)), e);
} finally {
try {
m_sqlManager.closeAll(dbc, conn, stmt, null);
} catch (Throwable t) {
// this could happen during shutdown
LOG.debug(t.getLocalizedMessage(), t);
}
}
}
/**
* Creates a new {@link CmsVisitEntry} object from the given result set entry.<p>
*
* @param res the result set
*
* @return the new {@link CmsVisitEntry} object
*
* @throws SQLException if something goes wrong
*/
protected CmsVisitEntry internalReadVisitEntry(ResultSet res) throws SQLException {
CmsUUID userId = new CmsUUID(res.getString(m_sqlManager.readQuery("C_VISIT_USER_ID")));
long date = res.getLong(m_sqlManager.readQuery("C_VISIT_DATE"));
CmsUUID structureId = new CmsUUID(res.getString(m_sqlManager.readQuery("C_VISIT_STRUCTURE_ID")));
return new CmsVisitEntry(userId, date, structureId);
}
/**
* Build the whole WHERE SQL statement part for the given visit entry filter.<p>
*
* @param filter the filter
*
* @return a pair containing both the SQL and the parameters for it
*/
protected CmsPair<String, List<I_CmsPreparedStatementParameter>> prepareVisitConditions(CmsVisitEntryFilter filter) {
List<I_CmsPreparedStatementParameter> params = new ArrayList<I_CmsPreparedStatementParameter>();
StringBuffer conditions = new StringBuffer();
// user id filter
if (filter.getUserId() != null) {
if (conditions.length() == 0) {
conditions.append(BEGIN_CONDITION);
} else {
conditions.append(BEGIN_INCLUDE_CONDITION);
}
conditions.append(m_sqlManager.readQuery("C_VISIT_FILTER_USER_ID"));
params.add(new CmsPreparedStatementStringParameter(filter.getUserId().toString()));
conditions.append(END_CONDITION);
}
// resource id filter
if (filter.getStructureId() != null) {
if (conditions.length() == 0) {
conditions.append(BEGIN_CONDITION);
} else {
conditions.append(BEGIN_INCLUDE_CONDITION);
}
conditions.append(m_sqlManager.readQuery("C_VISIT_FILTER_STRUCTURE_ID"));
params.add(new CmsPreparedStatementStringParameter(filter.getStructureId().toString()));
conditions.append(END_CONDITION);
}
// date from filter
if (filter.getDateFrom() != CmsResource.DATE_RELEASED_DEFAULT) {
if (conditions.length() == 0) {
conditions.append(BEGIN_CONDITION);
} else {
conditions.append(BEGIN_INCLUDE_CONDITION);
}
conditions.append(m_sqlManager.readQuery("C_VISIT_FILTER_DATE_FROM"));
params.add(new CmsPreparedStatementLongParameter(filter.getDateFrom()));
conditions.append(END_CONDITION);
}
// date to filter
if (filter.getDateTo() != CmsResource.DATE_RELEASED_DEFAULT) {
if (conditions.length() == 0) {
conditions.append(BEGIN_CONDITION);
} else {
conditions.append(BEGIN_INCLUDE_CONDITION);
}
conditions.append(m_sqlManager.readQuery("C_VISIT_FILTER_DATE_TO"));
params.add(new CmsPreparedStatementLongParameter(filter.getDateTo()));
conditions.append(END_CONDITION);
}
return CmsPair.create(conditions.toString(), params);
}
}