001/*
002 * This library is part of OpenCms -
003 * the Open Source Content Management System
004 *
005 * Copyright (c) Alkacon Software GmbH & Co. KG (https://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, please see the
018 * company website: https://www.alkacon.com
019 *
020 * For further information about OpenCms, please see the
021 * project website: https://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.configuration.CmsConfigurationManager;
031import org.opencms.configuration.CmsParameterConfiguration;
032import org.opencms.db.CmsDbConsistencyException;
033import org.opencms.db.CmsDbContext;
034import org.opencms.db.CmsDbSqlException;
035import org.opencms.db.CmsDriverManager;
036import org.opencms.db.CmsPreparedStatementLongParameter;
037import org.opencms.db.CmsPreparedStatementStringParameter;
038import org.opencms.db.CmsSubscriptionFilter;
039import org.opencms.db.CmsSubscriptionReadMode;
040import org.opencms.db.CmsVisitEntry;
041import org.opencms.db.CmsVisitEntryFilter;
042import org.opencms.db.CmsVisitedByFilter;
043import org.opencms.db.I_CmsDriver;
044import org.opencms.db.I_CmsPreparedStatementParameter;
045import org.opencms.db.I_CmsSubscriptionDriver;
046import org.opencms.file.CmsDataAccessException;
047import org.opencms.file.CmsGroup;
048import org.opencms.file.CmsResource;
049import org.opencms.file.CmsUser;
050import org.opencms.file.history.I_CmsHistoryResource;
051import org.opencms.main.CmsException;
052import org.opencms.main.CmsLog;
053import org.opencms.main.OpenCms;
054import org.opencms.security.CmsPrincipal;
055import org.opencms.util.CmsFileUtil;
056import org.opencms.util.CmsPair;
057import org.opencms.util.CmsStringUtil;
058import org.opencms.util.CmsUUID;
059
060import java.sql.Connection;
061import java.sql.PreparedStatement;
062import java.sql.ResultSet;
063import java.sql.SQLException;
064import java.util.ArrayList;
065import java.util.HashSet;
066import java.util.Iterator;
067import java.util.List;
068import java.util.Set;
069
070import org.apache.commons.logging.Log;
071
072/**
073 * Generic implementation of the user tracking and subscription driver interface.<p>
074 *
075 * @since 8.0.0
076 */
077public class CmsSubscriptionDriver implements I_CmsDriver, I_CmsSubscriptionDriver {
078
079    /** The log object for this class. */
080    private static final Log LOG = CmsLog.getLog(org.opencms.db.generic.CmsSubscriptionDriver.class);
081
082    /** A reference to the driver manager used by this driver. */
083    protected CmsDriverManager m_driverManager;
084
085    /** The SQL manager used by this driver. */
086    protected CmsSqlManager m_sqlManager;
087
088    /**
089     * @see org.opencms.db.I_CmsSubscriptionDriver#deleteVisits(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.db.CmsVisitEntryFilter)
090     */
091    public void deleteVisits(CmsDbContext dbc, String poolName, CmsVisitEntryFilter filter)
092    throws CmsDataAccessException {
093
094        Connection conn = null;
095        PreparedStatement stmt = null;
096
097        try {
098            if (CmsStringUtil.isNotEmpty(poolName)) {
099                conn = m_sqlManager.getConnection(poolName);
100            } else {
101                conn = m_sqlManager.getConnection(dbc);
102            }
103
104            // compose statement
105            StringBuffer queryBuf = new StringBuffer(256);
106            queryBuf.append(m_sqlManager.readQuery("C_VISIT_DELETE_ENTRIES"));
107
108            CmsPair<String, List<I_CmsPreparedStatementParameter>> conditionsAndParams = prepareVisitConditions(filter);
109            queryBuf.append(conditionsAndParams.getFirst());
110            if (LOG.isDebugEnabled()) {
111                LOG.debug(queryBuf.toString());
112            }
113            stmt = m_sqlManager.getPreparedStatementForSql(conn, queryBuf.toString());
114            List<I_CmsPreparedStatementParameter> params = conditionsAndParams.getSecond();
115            for (int i = 0; i < params.size(); i++) {
116                I_CmsPreparedStatementParameter param = conditionsAndParams.getSecond().get(i);
117                param.insertIntoStatement(stmt, i + 1);
118            }
119
120            // execute
121            stmt.executeUpdate();
122        } catch (SQLException e) {
123            throw new CmsDbSqlException(
124                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
125                e);
126        } finally {
127            m_sqlManager.closeAll(dbc, conn, stmt, null);
128        }
129    }
130
131    /**
132     * @see org.opencms.db.I_CmsSubscriptionDriver#getDateLastVisitedBy(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.file.CmsUser, org.opencms.file.CmsResource)
133     */
134    public long getDateLastVisitedBy(CmsDbContext dbc, String poolName, CmsUser user, CmsResource resource)
135    throws CmsException {
136
137        CmsVisitEntryFilter filter = CmsVisitEntryFilter.ALL.filterResource(resource.getStructureId()).filterUser(
138            user.getId());
139        List<CmsVisitEntry> entries = readVisits(dbc, poolName, filter);
140        if (!entries.isEmpty()) {
141            return entries.get(0).getDate();
142        }
143        return 0;
144    }
145
146    /**
147     * @see org.opencms.db.I_CmsSubscriptionDriver#getSqlManager()
148     */
149    public CmsSqlManager getSqlManager() {
150
151        return m_sqlManager;
152    }
153
154    /**
155     * @see org.opencms.db.I_CmsDriver#init(org.opencms.db.CmsDbContext, org.opencms.configuration.CmsConfigurationManager, java.util.List, org.opencms.db.CmsDriverManager)
156     */
157    public void init(
158        CmsDbContext dbc,
159        CmsConfigurationManager configurationManager,
160        List<String> successiveDrivers,
161        CmsDriverManager driverManager) {
162
163        CmsParameterConfiguration config = configurationManager.getConfiguration();
164
165        String poolUrl = config.get("db.subscription.pool");
166        String classname = config.get("db.subscription.sqlmanager");
167        m_sqlManager = initSqlManager(classname);
168        m_sqlManager.init(I_CmsSubscriptionDriver.DRIVER_TYPE_ID, poolUrl);
169
170        m_driverManager = driverManager;
171
172        if (CmsLog.INIT.isInfoEnabled()) {
173            CmsLog.INIT.info(Messages.get().getBundle().key(Messages.INIT_ASSIGNED_POOL_1, poolUrl));
174        }
175
176        if ((successiveDrivers != null) && !successiveDrivers.isEmpty()) {
177            if (LOG.isWarnEnabled()) {
178                LOG.warn(
179                    Messages.get().getBundle().key(
180                        Messages.LOG_SUCCESSIVE_DRIVERS_UNSUPPORTED_1,
181                        getClass().getName()));
182            }
183        }
184    }
185
186    /**
187     * @see org.opencms.db.I_CmsSubscriptionDriver#initSqlManager(java.lang.String)
188     */
189    public org.opencms.db.generic.CmsSqlManager initSqlManager(String classname) {
190
191        return CmsSqlManager.getInstance(classname);
192    }
193
194    /**
195     * @see org.opencms.db.I_CmsSubscriptionDriver#markResourceAsVisitedBy(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.file.CmsResource, org.opencms.file.CmsUser)
196     */
197    public void markResourceAsVisitedBy(CmsDbContext dbc, String poolName, CmsResource resource, CmsUser user)
198    throws CmsDataAccessException {
199
200        boolean entryExists = false;
201        CmsVisitEntryFilter filter = CmsVisitEntryFilter.ALL.filterResource(resource.getStructureId()).filterUser(
202            user.getId());
203        // delete existing visited entry for the resource
204        if (readVisits(dbc, OpenCms.getSubscriptionManager().getPoolName(), filter).size() > 0) {
205            entryExists = true;
206            deleteVisits(dbc, OpenCms.getSubscriptionManager().getPoolName(), filter);
207        }
208
209        CmsVisitEntry entry = new CmsVisitEntry(user.getId(), System.currentTimeMillis(), resource.getStructureId());
210        addVisit(dbc, poolName, entry);
211
212        if (!entryExists) {
213            // new entry, check if maximum number of stored visited resources is exceeded
214            PreparedStatement stmt = null;
215            Connection conn = null;
216            ResultSet res = null;
217            int count = 0;
218
219            List<Long> dates = new ArrayList<Long>();
220
221            try {
222                conn = m_sqlManager.getConnection(poolName);
223                stmt = m_sqlManager.getPreparedStatement(conn, dbc.currentProject(), "C_VISITED_USER_COUNT_1");
224
225                stmt.setString(1, user.getId().toString());
226                res = stmt.executeQuery();
227
228                if (res.next()) {
229                    count = res.getInt(1);
230                    while (res.next()) {
231                        // do nothing only move through all rows because of mssql odbc driver
232                    }
233                } else {
234                    throw new CmsDbConsistencyException(
235                        Messages.get().container(Messages.ERR_COUNTING_VISITED_RESOURCES_1, user.getName()));
236                }
237
238                int maxCount = OpenCms.getSubscriptionManager().getMaxVisitedCount();
239                if (count > maxCount) {
240                    // delete old visited entries
241                    m_sqlManager.closeAll(dbc, null, stmt, res);
242                    stmt = m_sqlManager.getPreparedStatement(
243                        conn,
244                        dbc.currentProject(),
245                        "C_VISITED_USER_DELETE_GETDATE_2");
246
247                    stmt.setString(1, user.getId().toString());
248                    stmt.setInt(2, count - maxCount);
249                    res = stmt.executeQuery();
250                    while (res.next()) {
251                        // get last date of result set
252                        dates.add(Long.valueOf(res.getLong(1)));
253                    }
254                }
255            } catch (SQLException e) {
256                throw new CmsDbSqlException(
257                    Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
258                    e);
259            } finally {
260                m_sqlManager.closeAll(dbc, conn, stmt, res);
261            }
262            long deleteDate = 0;
263            for (Long date : dates) {
264                deleteDate = date.longValue();
265                if (deleteDate > 0) {
266                    filter = CmsVisitEntryFilter.ALL.filterUser(user.getId()).filterTo(deleteDate);
267                    deleteVisits(dbc, OpenCms.getSubscriptionManager().getPoolName(), filter);
268                }
269            }
270        }
271    }
272
273    /**
274     * @see org.opencms.db.I_CmsSubscriptionDriver#readAllSubscribedResources(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.security.CmsPrincipal)
275     */
276    public List<CmsResource> readAllSubscribedResources(CmsDbContext dbc, String poolName, CmsPrincipal principal)
277    throws CmsDataAccessException {
278
279        PreparedStatement stmt = null;
280        Connection conn = null;
281        ResultSet res = null;
282        CmsResource currentResource = null;
283        List<CmsResource> resources = new ArrayList<CmsResource>();
284
285        try {
286            conn = m_sqlManager.getConnection(poolName);
287            stmt = m_sqlManager.getPreparedStatement(conn, dbc.currentProject(), "C_SUBSCRIPTION_READ_ALL_1");
288
289            stmt.setString(1, principal.getId().toString());
290            res = stmt.executeQuery();
291
292            while (res.next()) {
293                currentResource = m_driverManager.getVfsDriver(
294                    dbc).createFile(dbc, res, dbc.currentProject().getUuid(), false);
295                resources.add(currentResource);
296            }
297        } catch (SQLException e) {
298            throw new CmsDbSqlException(
299                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
300                e);
301        } finally {
302            m_sqlManager.closeAll(dbc, conn, stmt, res);
303        }
304        return resources;
305    }
306
307    /**
308     * @see org.opencms.db.I_CmsSubscriptionDriver#readResourcesVisitedBy(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.db.CmsVisitedByFilter)
309     */
310    public List<CmsResource> readResourcesVisitedBy(CmsDbContext dbc, String poolName, CmsVisitedByFilter filter)
311    throws CmsDataAccessException {
312
313        PreparedStatement stmt = null;
314        Connection conn = null;
315        ResultSet res = null;
316        CmsResource currentResource = null;
317        StringBuffer conditions = new StringBuffer(256);
318        List<String> params = new ArrayList<String>(1);
319        List<CmsResource> resources = new ArrayList<CmsResource>();
320
321        try {
322            // path filter
323            if (CmsStringUtil.isNotEmpty(filter.getParentPath())) {
324                CmsResource parent = m_driverManager.getVfsDriver(
325                    dbc).readResource(dbc, dbc.currentProject().getUuid(), filter.getParentPath(), false);
326                conditions.append(BEGIN_INCLUDE_CONDITION);
327                if (filter.isIncludeSubFolders()) {
328                    conditions.append(
329                        m_sqlManager.readQuery(dbc.currentProject(), "C_RESOURCES_SELECT_BY_PATH_PREFIX"));
330                    params.add(
331                        CmsFileUtil.addTrailingSeparator(CmsVfsDriver.escapeDbWildcard(filter.getParentPath())) + "%");
332                } else {
333                    conditions.append(
334                        m_sqlManager.readQuery(dbc.currentProject(), "C_RESOURCES_SELECT_BY_PARENT_UUID"));
335                    params.add(parent.getStructureId().toString());
336                }
337                conditions.append(END_CONDITION);
338            }
339
340            conn = m_sqlManager.getConnection(poolName);
341            String query = m_sqlManager.readQuery(dbc.currentProject(), "C_VISITED_USER_READ_4");
342            query = CmsStringUtil.substitute(query, "%(CONDITIONS)", conditions.toString());
343            stmt = m_sqlManager.getPreparedStatementForSql(conn, query);
344
345            stmt.setString(1, filter.getUser().getId().toString());
346            stmt.setLong(2, filter.getFromDate());
347            stmt.setLong(3, filter.getToDate());
348            for (int i = 0; i < params.size(); i++) {
349                stmt.setString(i + 4, params.get(i));
350            }
351
352            res = stmt.executeQuery();
353
354            while (res.next()) {
355                currentResource = m_driverManager.getVfsDriver(
356                    dbc).createFile(dbc, res, dbc.currentProject().getUuid(), false);
357                resources.add(currentResource);
358            }
359        } catch (SQLException e) {
360            throw new CmsDbSqlException(
361                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
362                e);
363        } finally {
364            m_sqlManager.closeAll(dbc, conn, stmt, res);
365        }
366        return resources;
367    }
368
369    /**
370     * @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)
371     */
372    public List<I_CmsHistoryResource> readSubscribedDeletedResources(
373        CmsDbContext dbc,
374        String poolName,
375        CmsUser user,
376        List<CmsGroup> groups,
377        CmsResource parent,
378        boolean includeSubFolders,
379        long deletedFrom)
380    throws CmsDataAccessException {
381
382        PreparedStatement stmt = null;
383        Connection conn = null;
384        ResultSet res = null;
385        List<I_CmsHistoryResource> resources = new ArrayList<I_CmsHistoryResource>();
386        Set<CmsUUID> historyIDs = new HashSet<CmsUUID>();
387
388        List<String> principalIds = new ArrayList<String>();
389        // add user ID
390        principalIds.add(user.getId().toString());
391        // add group IDs
392        if ((groups != null) && !groups.isEmpty()) {
393            Iterator<CmsGroup> it = groups.iterator();
394            while (it.hasNext()) {
395                principalIds.add(it.next().getId().toString());
396            }
397        }
398
399        StringBuffer conditions = new StringBuffer(256);
400        List<String> params = new ArrayList<String>();
401        conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETED"));
402
403        if (principalIds.size() == 1) {
404            // single principal filter
405            conditions.append(BEGIN_INCLUDE_CONDITION);
406            conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETED_FILTER_PRINCIPAL_SINGLE"));
407            params.add(principalIds.get(0));
408            conditions.append(END_CONDITION);
409        } else {
410            // multiple principals filter
411            conditions.append(BEGIN_INCLUDE_CONDITION);
412            conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETED_FILTER_PRINCIPALS"));
413            conditions.append(BEGIN_CONDITION);
414            Iterator<String> it = principalIds.iterator();
415            while (it.hasNext()) {
416                params.add(it.next());
417                conditions.append("?");
418                if (it.hasNext()) {
419                    conditions.append(", ");
420                }
421            }
422            conditions.append(END_CONDITION);
423            conditions.append(END_CONDITION);
424        }
425
426        try {
427            conn = m_sqlManager.getConnection(poolName);
428            stmt = m_sqlManager.getPreparedStatementForSql(conn, conditions.toString());
429
430            // set parameters
431            stmt.setLong(1, deletedFrom);
432            for (int i = 0; i < params.size(); i++) {
433                stmt.setString(i + 2, params.get(i));
434            }
435            res = stmt.executeQuery();
436            while (res.next()) {
437                historyIDs.add(new CmsUUID(res.getString(1)));
438            }
439        } catch (SQLException e) {
440            throw new CmsDbSqlException(
441                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
442                e);
443        } finally {
444            m_sqlManager.closeAll(dbc, conn, stmt, res);
445        }
446
447        // get the matching history resources from the found structure IDs
448        String parentFolderPath = "";
449        if (parent != null) {
450            parentFolderPath = CmsResource.getFolderPath(parent.getRootPath());
451        }
452        for (Iterator<CmsUUID> i = historyIDs.iterator(); i.hasNext();) {
453            CmsUUID id = i.next();
454            int version = m_driverManager.getHistoryDriver(dbc).readLastVersion(dbc, id);
455            if (version > 0) {
456                I_CmsHistoryResource histRes = m_driverManager.getHistoryDriver(dbc).readResource(dbc, id, version);
457                if (parent != null) {
458                    if (!includeSubFolders
459                        && !parentFolderPath.equals(CmsResource.getFolderPath(histRes.getRootPath()))) {
460                        // deleted history resource is not in the specified parent folder, skip it
461                        continue;
462                    } else if (includeSubFolders && !histRes.getRootPath().startsWith(parentFolderPath)) {
463                        // deleted history resource is not in the specified parent folder or sub folder, skip it
464                        continue;
465                    }
466                }
467                resources.add(histRes);
468            }
469        }
470
471        return resources;
472    }
473
474    /**
475     * @see org.opencms.db.I_CmsSubscriptionDriver#readSubscribedResources(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.db.CmsSubscriptionFilter)
476     */
477    public List<CmsResource> readSubscribedResources(CmsDbContext dbc, String poolName, CmsSubscriptionFilter filter)
478    throws CmsDataAccessException {
479
480        PreparedStatement stmt = null;
481        Connection conn = null;
482        ResultSet res = null;
483        CmsResource currentResource = null;
484        List<CmsResource> resources = new ArrayList<CmsResource>();
485
486        String queryBuf = m_sqlManager.readQuery(dbc.currentProject(), "C_SUBSCRIPTION_FILTER_READ");
487
488        StringBuffer conditions = new StringBuffer(256);
489        List<I_CmsPreparedStatementParameter> params = new ArrayList<I_CmsPreparedStatementParameter>();
490
491        boolean userDefined = filter.getUser() != null;
492        boolean groupsDefined = !filter.getGroups().isEmpty();
493        if (!groupsDefined && !userDefined) {
494            filter.setUser(dbc.currentUser());
495            userDefined = true;
496        }
497        // check if a user has been set for the "visited" and "unvisited" mode
498        if (!filter.getMode().isAll() && (filter.getUser() == null)) {
499            // change the mode, without user the other modes are not applicable
500            filter.setMode(CmsSubscriptionReadMode.ALL);
501        }
502
503        List<String> principalIds = new ArrayList<String>();
504        // add user ID
505        if (userDefined) {
506            principalIds.add(filter.getUser().getId().toString());
507        }
508        // add group IDs
509        if (groupsDefined) {
510            Iterator<CmsGroup> it = filter.getGroups().iterator();
511            while (it.hasNext()) {
512                principalIds.add(it.next().getId().toString());
513            }
514        }
515
516        if (principalIds.size() == 1) {
517            // single principal filter
518            conditions.append(BEGIN_CONDITION);
519            conditions.append(m_sqlManager.readQuery(dbc.currentProject(), "C_SUBSCRIPTION_FILTER_PRINCIPAL_SINGLE"));
520            params.add(new CmsPreparedStatementStringParameter(principalIds.get(0)));
521            conditions.append(END_CONDITION);
522        } else {
523            // multiple principals filter
524            conditions.append(BEGIN_CONDITION);
525            conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_FILTER_PRINCIPALS"));
526            conditions.append(BEGIN_CONDITION);
527            Iterator<String> it = principalIds.iterator();
528            while (it.hasNext()) {
529                params.add(new CmsPreparedStatementStringParameter(it.next()));
530                conditions.append("?");
531                if (it.hasNext()) {
532                    conditions.append(", ");
533                }
534            }
535            conditions.append(END_CONDITION);
536            conditions.append(m_sqlManager.readQuery(dbc.currentProject(), "C_SUBSCRIPTION_FILTER_PRINCIPALS_END"));
537            conditions.append(END_CONDITION);
538        }
539
540        // path filter
541        if (CmsStringUtil.isNotEmpty(filter.getParentPath())) {
542            CmsResource parent = m_driverManager.getVfsDriver(
543                dbc).readResource(dbc, dbc.currentProject().getUuid(), filter.getParentPath(), false);
544            conditions.append(BEGIN_INCLUDE_CONDITION);
545            if (filter.isIncludeSubFolders()) {
546                conditions.append(m_sqlManager.readQuery(dbc.currentProject(), "C_RESOURCES_SELECT_BY_PATH_PREFIX"));
547                params.add(
548                    new CmsPreparedStatementStringParameter(
549                        CmsFileUtil.addTrailingSeparator(CmsVfsDriver.escapeDbWildcard(filter.getParentPath())) + "%"));
550            } else {
551                conditions.append(m_sqlManager.readQuery(dbc.currentProject(), "C_RESOURCES_SELECT_BY_PARENT_UUID"));
552                params.add(new CmsPreparedStatementStringParameter(parent.getStructureId().toString()));
553            }
554            conditions.append(END_CONDITION);
555        }
556
557        // check from and to date
558        if ((filter.getFromDate() > 0) || (filter.getToDate() < Long.MAX_VALUE)) {
559            conditions.append(BEGIN_INCLUDE_CONDITION);
560            conditions.append(
561                m_sqlManager.readQuery(dbc.currentProject(), "C_SUBSCRIPTION_FILTER_RESOURCES_DATE_MODIFIED"));
562            params.add(new CmsPreparedStatementLongParameter(filter.getFromDate()));
563            params.add(new CmsPreparedStatementLongParameter(filter.getToDate()));
564            conditions.append(END_CONDITION);
565        }
566
567        try {
568            conn = m_sqlManager.getConnection(poolName);
569            queryBuf = CmsStringUtil.substitute(queryBuf, "%(CONDITIONS)", conditions.toString());
570            if (LOG.isDebugEnabled()) {
571                LOG.debug(queryBuf.toString());
572            }
573            stmt = m_sqlManager.getPreparedStatementForSql(conn, queryBuf);
574
575            // set parameters
576            for (int i = 0; i < params.size(); i++) {
577                I_CmsPreparedStatementParameter param = params.get(i);
578                param.insertIntoStatement(stmt, i + 1);
579            }
580            res = stmt.executeQuery();
581
582            while (res.next()) {
583                currentResource = m_driverManager.getVfsDriver(
584                    dbc).createFile(dbc, res, dbc.currentProject().getUuid(), false);
585                resources.add(currentResource);
586            }
587        } catch (SQLException e) {
588            throw new CmsDbSqlException(
589                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
590                e);
591        } finally {
592            m_sqlManager.closeAll(dbc, conn, stmt, res);
593        }
594
595        // filter the result if in visited/unvisited mode (faster as creating a query with even more joined tables)
596        if (!filter.getMode().isAll()) {
597            List<CmsResource> result = new ArrayList<CmsResource>(resources.size());
598            for (Iterator<CmsResource> i = resources.iterator(); i.hasNext();) {
599                CmsResource resource = i.next();
600                long visitedDate = 0;
601                try {
602                    visitedDate = m_driverManager.getDateLastVisitedBy(dbc, poolName, filter.getUser(), resource);
603                } catch (CmsException e) {
604                    throw new CmsDbSqlException(Messages.get().container(Messages.ERR_GENERIC_SQL_0), e);
605                }
606                if (filter.getMode().isUnVisited() && (visitedDate >= resource.getDateLastModified())) {
607                    // unvisited mode: resource was visited after the last modification, skip it
608                    continue;
609                }
610                if (filter.getMode().isVisited() && (resource.getDateLastModified() > visitedDate)) {
611                    // visited mode: resource was not visited after last modification, skip it
612                    continue;
613                }
614                // add the current resource to the result
615                result.add(resource);
616            }
617            resources = result;
618        }
619        return resources;
620    }
621
622    /**
623     * Reads {@link CmsVisitEntry} objects from the database.<p>
624     *
625     * @param dbc the database context to use
626     * @param poolName the name of the pool which should be used for the database operation
627     * @param filter a filter for constraining the list of results
628     *
629     * @return a list of visit entries
630     *
631     * @throws CmsDataAccessException if the database operation fails
632     */
633    public List<CmsVisitEntry> readVisits(CmsDbContext dbc, String poolName, CmsVisitEntryFilter filter)
634    throws CmsDataAccessException {
635
636        List<CmsVisitEntry> entries = new ArrayList<CmsVisitEntry>();
637
638        Connection conn = null;
639        PreparedStatement stmt = null;
640        ResultSet res = null;
641
642        try {
643            if (CmsStringUtil.isNotEmpty(poolName)) {
644                conn = m_sqlManager.getConnection(poolName);
645            } else {
646                conn = m_sqlManager.getConnection(dbc);
647            }
648
649            // compose statement
650            StringBuffer queryBuf = new StringBuffer(256);
651            queryBuf.append(m_sqlManager.readQuery("C_VISIT_READ_ENTRIES"));
652            CmsPair<String, List<I_CmsPreparedStatementParameter>> conditionsAndParameters = prepareVisitConditions(
653                filter);
654            List<I_CmsPreparedStatementParameter> params = conditionsAndParameters.getSecond();
655            queryBuf.append(conditionsAndParameters.getFirst());
656
657            if (LOG.isDebugEnabled()) {
658                LOG.debug(queryBuf.toString());
659            }
660            stmt = m_sqlManager.getPreparedStatementForSql(conn, queryBuf.toString());
661            for (int i = 0; i < params.size(); i++) {
662                I_CmsPreparedStatementParameter param = params.get(i);
663                param.insertIntoStatement(stmt, i + 1);
664            }
665
666            // execute
667            res = stmt.executeQuery();
668            while (res.next()) {
669                // get results
670                entries.add(internalReadVisitEntry(res));
671            }
672        } catch (SQLException e) {
673            throw new CmsDbSqlException(
674                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
675                e);
676        } finally {
677            m_sqlManager.closeAll(dbc, conn, stmt, res);
678        }
679        return entries;
680    }
681
682    /**
683     * @see org.opencms.db.I_CmsSubscriptionDriver#setSubscribedResourceAsDeleted(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.file.CmsResource)
684     */
685    public void setSubscribedResourceAsDeleted(CmsDbContext dbc, String poolName, CmsResource resource)
686    throws CmsDataAccessException {
687
688        PreparedStatement stmt = null;
689        Connection conn = null;
690        long deletedTime = System.currentTimeMillis();
691
692        try {
693            conn = getSqlManager().getConnection(poolName);
694            // set resource as deleted for all users and groups
695            stmt = m_sqlManager.getPreparedStatement(conn, "C_SUBSCRIPTION_UPDATE_DATE_2");
696            stmt.setLong(1, deletedTime);
697            stmt.setString(2, resource.getStructureId().toString());
698            stmt.executeUpdate();
699        } catch (SQLException e) {
700            throw new CmsDbSqlException(
701                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
702                e);
703        } finally {
704            m_sqlManager.closeAll(dbc, conn, stmt, null);
705        }
706
707    }
708
709    /**
710     * @see org.opencms.db.I_CmsSubscriptionDriver#subscribeResourceFor(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.security.CmsPrincipal, org.opencms.file.CmsResource)
711     */
712    public void subscribeResourceFor(CmsDbContext dbc, String poolName, CmsPrincipal principal, CmsResource resource)
713    throws CmsDataAccessException {
714
715        ResultSet res = null;
716        PreparedStatement stmt = null;
717        Connection conn = null;
718
719        try {
720            conn = getSqlManager().getConnection(poolName);
721            stmt = m_sqlManager.getPreparedStatement(conn, "C_SUBSCRIPTION_CHECK_2");
722            stmt.setString(1, principal.getId().toString());
723            stmt.setString(2, resource.getStructureId().toString());
724            res = stmt.executeQuery();
725
726            // only create subscription entry if principal is not subscribed to resource
727            if (res.next()) {
728                while (res.next()) {
729                    // do nothing only move through all rows because of mssql odbc driver
730                }
731            } else {
732                // subscribe principal
733                m_sqlManager.closeAll(dbc, null, stmt, null);
734                stmt = m_sqlManager.getPreparedStatement(conn, "C_SUBSCRIPTION_CREATE_2");
735                stmt.setString(1, principal.getId().toString());
736                stmt.setString(2, resource.getStructureId().toString());
737                stmt.executeUpdate();
738            }
739        } catch (SQLException e) {
740            throw new CmsDbSqlException(
741                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
742                e);
743        } finally {
744            m_sqlManager.closeAll(dbc, conn, stmt, res);
745        }
746    }
747
748    /**
749     * @see org.opencms.db.I_CmsSubscriptionDriver#unsubscribeAllDeletedResources(org.opencms.db.CmsDbContext, java.lang.String, long)
750     */
751    public void unsubscribeAllDeletedResources(CmsDbContext dbc, String poolName, long deletedTo)
752    throws CmsDataAccessException {
753
754        PreparedStatement stmt = null;
755        Connection conn = null;
756
757        try {
758            conn = getSqlManager().getConnection(poolName);
759            StringBuffer conditions = new StringBuffer(256);
760
761            // unsubscribe all deleted resources
762            conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETE"));
763            conditions.append(BEGIN_CONDITION);
764            conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETE_FILTER_DATE"));
765            conditions.append(END_CONDITION);
766            stmt = m_sqlManager.getPreparedStatementForSql(conn, conditions.toString());
767            stmt.setLong(1, deletedTo);
768            stmt.executeUpdate();
769
770        } catch (SQLException e) {
771            throw new CmsDbSqlException(
772                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
773                e);
774        } finally {
775            m_sqlManager.closeAll(dbc, conn, stmt, null);
776        }
777    }
778
779    /**
780     * @see org.opencms.db.I_CmsSubscriptionDriver#unsubscribeAllResourcesFor(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.security.CmsPrincipal)
781     */
782    public void unsubscribeAllResourcesFor(CmsDbContext dbc, String poolName, CmsPrincipal principal)
783    throws CmsDataAccessException {
784
785        PreparedStatement stmt = null;
786        Connection conn = null;
787
788        try {
789            if (principal != null) {
790                conn = getSqlManager().getConnection(poolName);
791                StringBuffer conditions = new StringBuffer(256);
792
793                conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETE"));
794                conditions.append(BEGIN_CONDITION);
795                conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETE_FILTER_PRINCIPAL"));
796                conditions.append(END_CONDITION);
797                stmt = m_sqlManager.getPreparedStatementForSql(conn, conditions.toString());
798                stmt.setString(1, principal.getId().toString());
799                stmt.executeUpdate();
800            }
801        } catch (SQLException e) {
802            throw new CmsDbSqlException(
803                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
804                e);
805        } finally {
806            m_sqlManager.closeAll(dbc, conn, stmt, null);
807        }
808    }
809
810    /**
811     * @see org.opencms.db.I_CmsSubscriptionDriver#unsubscribeResourceFor(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.security.CmsPrincipal, org.opencms.file.CmsResource)
812     */
813    public void unsubscribeResourceFor(CmsDbContext dbc, String poolName, CmsPrincipal principal, CmsResource resource)
814    throws CmsDataAccessException {
815
816        PreparedStatement stmt = null;
817        Connection conn = null;
818
819        try {
820            conn = getSqlManager().getConnection(poolName);
821            StringBuffer conditions = new StringBuffer(256);
822            conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETE"));
823            conditions.append(BEGIN_CONDITION);
824            conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETE_FILTER_PRINCIPAL"));
825            conditions.append(END_CONDITION);
826            conditions.append(BEGIN_INCLUDE_CONDITION);
827            conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETE_FILTER_STRUCTURE"));
828            conditions.append(END_CONDITION);
829            stmt = m_sqlManager.getPreparedStatementForSql(conn, conditions.toString());
830            stmt.setString(1, principal.getId().toString());
831            stmt.setString(2, resource.getStructureId().toString());
832            stmt.executeUpdate();
833        } catch (SQLException e) {
834            throw new CmsDbSqlException(
835                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
836                e);
837        } finally {
838            m_sqlManager.closeAll(dbc, conn, stmt, null);
839        }
840    }
841
842    /**
843     * @see org.opencms.db.I_CmsSubscriptionDriver#unsubscribeResourceForAll(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.file.CmsResource)
844     */
845    public void unsubscribeResourceForAll(CmsDbContext dbc, String poolName, CmsResource resource)
846    throws CmsDataAccessException {
847
848        PreparedStatement stmt = null;
849        Connection conn = null;
850
851        try {
852            conn = getSqlManager().getConnection(poolName);
853            StringBuffer conditions = new StringBuffer(256);
854
855            // unsubscribe resource for all principals
856            conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETE"));
857            conditions.append(BEGIN_CONDITION);
858            conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETE_FILTER_STRUCTURE"));
859            conditions.append(END_CONDITION);
860            stmt = m_sqlManager.getPreparedStatementForSql(conn, conditions.toString());
861            stmt.setString(1, resource.getStructureId().toString());
862            stmt.executeUpdate();
863        } catch (SQLException e) {
864            throw new CmsDbSqlException(
865                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
866                e);
867        } finally {
868            m_sqlManager.closeAll(dbc, conn, stmt, null);
869        }
870    }
871
872    /**
873     * Adds an entry to the table of visits.<p>
874     *
875     * @param dbc the database context to use
876     * @param poolName the name of the database pool to use
877     * @param visit the visit bean
878     *
879     * @throws CmsDbSqlException if the database operation fails
880     */
881    protected void addVisit(CmsDbContext dbc, String poolName, CmsVisitEntry visit) throws CmsDbSqlException {
882
883        Connection conn = null;
884        PreparedStatement stmt = null;
885
886        try {
887            if (CmsStringUtil.isNotEmpty(poolName)) {
888                conn = m_sqlManager.getConnection(poolName);
889            } else {
890                conn = m_sqlManager.getConnection(dbc);
891            }
892
893            stmt = m_sqlManager.getPreparedStatement(conn, "C_VISIT_CREATE_3");
894
895            stmt.setString(1, visit.getUserId().toString());
896            stmt.setLong(2, visit.getDate());
897            stmt.setString(3, visit.getStructureId() == null ? null : visit.getStructureId().toString());
898            try {
899                stmt.executeUpdate();
900            } catch (SQLException e) {
901                // ignore, most likely a duplicate entry
902                LOG.debug(
903                    Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)).key(),
904                    e);
905            }
906
907        } catch (SQLException e) {
908            throw new CmsDbSqlException(
909                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
910                e);
911        } finally {
912            try {
913                m_sqlManager.closeAll(dbc, conn, stmt, null);
914            } catch (Throwable t) {
915                // this could happen during shutdown
916                LOG.debug(t.getLocalizedMessage(), t);
917            }
918        }
919    }
920
921    /**
922     * Creates a new {@link CmsVisitEntry} object from the given result set entry.<p>
923     *
924     * @param res the result set
925     *
926     * @return the new {@link CmsVisitEntry} object
927     *
928     * @throws SQLException if something goes wrong
929     */
930    protected CmsVisitEntry internalReadVisitEntry(ResultSet res) throws SQLException {
931
932        CmsUUID userId = new CmsUUID(res.getString(m_sqlManager.readQuery("C_VISIT_USER_ID")));
933        long date = res.getLong(m_sqlManager.readQuery("C_VISIT_DATE"));
934        CmsUUID structureId = new CmsUUID(res.getString(m_sqlManager.readQuery("C_VISIT_STRUCTURE_ID")));
935        return new CmsVisitEntry(userId, date, structureId);
936    }
937
938    /**
939     * Build the whole WHERE SQL statement part for the given visit entry filter.<p>
940     *
941     * @param filter the filter
942     *
943     * @return a pair containing both the SQL and the parameters for it
944     */
945    protected CmsPair<String, List<I_CmsPreparedStatementParameter>> prepareVisitConditions(
946        CmsVisitEntryFilter filter) {
947
948        List<I_CmsPreparedStatementParameter> params = new ArrayList<I_CmsPreparedStatementParameter>();
949        StringBuffer conditions = new StringBuffer();
950
951        // user id filter
952        if (filter.getUserId() != null) {
953            if (conditions.length() == 0) {
954                conditions.append(BEGIN_CONDITION);
955            } else {
956                conditions.append(BEGIN_INCLUDE_CONDITION);
957            }
958            conditions.append(m_sqlManager.readQuery("C_VISIT_FILTER_USER_ID"));
959            params.add(new CmsPreparedStatementStringParameter(filter.getUserId().toString()));
960            conditions.append(END_CONDITION);
961        }
962
963        // resource id filter
964        if (filter.getStructureId() != null) {
965            if (conditions.length() == 0) {
966                conditions.append(BEGIN_CONDITION);
967            } else {
968                conditions.append(BEGIN_INCLUDE_CONDITION);
969            }
970            conditions.append(m_sqlManager.readQuery("C_VISIT_FILTER_STRUCTURE_ID"));
971            params.add(new CmsPreparedStatementStringParameter(filter.getStructureId().toString()));
972            conditions.append(END_CONDITION);
973        }
974
975        // date from filter
976        if (filter.getDateFrom() != CmsResource.DATE_RELEASED_DEFAULT) {
977            if (conditions.length() == 0) {
978                conditions.append(BEGIN_CONDITION);
979            } else {
980                conditions.append(BEGIN_INCLUDE_CONDITION);
981            }
982            conditions.append(m_sqlManager.readQuery("C_VISIT_FILTER_DATE_FROM"));
983            params.add(new CmsPreparedStatementLongParameter(filter.getDateFrom()));
984            conditions.append(END_CONDITION);
985        }
986
987        // date to filter
988        if (filter.getDateTo() != CmsResource.DATE_RELEASED_DEFAULT) {
989            if (conditions.length() == 0) {
990                conditions.append(BEGIN_CONDITION);
991            } else {
992                conditions.append(BEGIN_INCLUDE_CONDITION);
993            }
994            conditions.append(m_sqlManager.readQuery("C_VISIT_FILTER_DATE_TO"));
995            params.add(new CmsPreparedStatementLongParameter(filter.getDateTo()));
996            conditions.append(END_CONDITION);
997        }
998        return CmsPair.create(conditions.toString(), params);
999    }
1000
1001}