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, 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.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(dbc).createFile(
294                    res,
295                    dbc.currentProject().getUuid(),
296                    false);
297                resources.add(currentResource);
298            }
299        } catch (SQLException e) {
300            throw new CmsDbSqlException(
301                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
302                e);
303        } finally {
304            m_sqlManager.closeAll(dbc, conn, stmt, res);
305        }
306        return resources;
307    }
308
309    /**
310     * @see org.opencms.db.I_CmsSubscriptionDriver#readResourcesVisitedBy(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.db.CmsVisitedByFilter)
311     */
312    public List<CmsResource> readResourcesVisitedBy(CmsDbContext dbc, String poolName, CmsVisitedByFilter filter)
313    throws CmsDataAccessException {
314
315        PreparedStatement stmt = null;
316        Connection conn = null;
317        ResultSet res = null;
318        CmsResource currentResource = null;
319        StringBuffer conditions = new StringBuffer(256);
320        List<String> params = new ArrayList<String>(1);
321        List<CmsResource> resources = new ArrayList<CmsResource>();
322
323        try {
324            // path filter
325            if (CmsStringUtil.isNotEmpty(filter.getParentPath())) {
326                CmsResource parent = m_driverManager.getVfsDriver(dbc).readResource(
327                    dbc,
328                    dbc.currentProject().getUuid(),
329                    filter.getParentPath(),
330                    false);
331                conditions.append(BEGIN_INCLUDE_CONDITION);
332                if (filter.isIncludeSubFolders()) {
333                    conditions.append(
334                        m_sqlManager.readQuery(dbc.currentProject(), "C_RESOURCES_SELECT_BY_PATH_PREFIX"));
335                    params.add(
336                        CmsFileUtil.addTrailingSeparator(CmsVfsDriver.escapeDbWildcard(filter.getParentPath())) + "%");
337                } else {
338                    conditions.append(
339                        m_sqlManager.readQuery(dbc.currentProject(), "C_RESOURCES_SELECT_BY_PARENT_UUID"));
340                    params.add(parent.getStructureId().toString());
341                }
342                conditions.append(END_CONDITION);
343            }
344
345            conn = m_sqlManager.getConnection(poolName);
346            String query = m_sqlManager.readQuery(dbc.currentProject(), "C_VISITED_USER_READ_4");
347            query = CmsStringUtil.substitute(query, "%(CONDITIONS)", conditions.toString());
348            stmt = m_sqlManager.getPreparedStatementForSql(conn, query);
349
350            stmt.setString(1, filter.getUser().getId().toString());
351            stmt.setLong(2, filter.getFromDate());
352            stmt.setLong(3, filter.getToDate());
353            for (int i = 0; i < params.size(); i++) {
354                stmt.setString(i + 4, params.get(i));
355            }
356
357            res = stmt.executeQuery();
358
359            while (res.next()) {
360                currentResource = m_driverManager.getVfsDriver(dbc).createFile(
361                    res,
362                    dbc.currentProject().getUuid(),
363                    false);
364                resources.add(currentResource);
365            }
366        } catch (SQLException e) {
367            throw new CmsDbSqlException(
368                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
369                e);
370        } finally {
371            m_sqlManager.closeAll(dbc, conn, stmt, res);
372        }
373        return resources;
374    }
375
376    /**
377     * @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)
378     */
379    public List<I_CmsHistoryResource> readSubscribedDeletedResources(
380        CmsDbContext dbc,
381        String poolName,
382        CmsUser user,
383        List<CmsGroup> groups,
384        CmsResource parent,
385        boolean includeSubFolders,
386        long deletedFrom) throws CmsDataAccessException {
387
388        PreparedStatement stmt = null;
389        Connection conn = null;
390        ResultSet res = null;
391        List<I_CmsHistoryResource> resources = new ArrayList<I_CmsHistoryResource>();
392        Set<CmsUUID> historyIDs = new HashSet<CmsUUID>();
393
394        List<String> principalIds = new ArrayList<String>();
395        // add user ID
396        principalIds.add(user.getId().toString());
397        // add group IDs
398        if ((groups != null) && !groups.isEmpty()) {
399            Iterator<CmsGroup> it = groups.iterator();
400            while (it.hasNext()) {
401                principalIds.add(it.next().getId().toString());
402            }
403        }
404
405        StringBuffer conditions = new StringBuffer(256);
406        List<String> params = new ArrayList<String>();
407        conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETED"));
408
409        if (principalIds.size() == 1) {
410            // single principal filter
411            conditions.append(BEGIN_INCLUDE_CONDITION);
412            conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETED_FILTER_PRINCIPAL_SINGLE"));
413            params.add(principalIds.get(0));
414            conditions.append(END_CONDITION);
415        } else {
416            // multiple principals filter
417            conditions.append(BEGIN_INCLUDE_CONDITION);
418            conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETED_FILTER_PRINCIPALS"));
419            conditions.append(BEGIN_CONDITION);
420            Iterator<String> it = principalIds.iterator();
421            while (it.hasNext()) {
422                params.add(it.next());
423                conditions.append("?");
424                if (it.hasNext()) {
425                    conditions.append(", ");
426                }
427            }
428            conditions.append(END_CONDITION);
429            conditions.append(END_CONDITION);
430        }
431
432        try {
433            conn = m_sqlManager.getConnection(poolName);
434            stmt = m_sqlManager.getPreparedStatementForSql(conn, conditions.toString());
435
436            // set parameters
437            stmt.setLong(1, deletedFrom);
438            for (int i = 0; i < params.size(); i++) {
439                stmt.setString(i + 2, params.get(i));
440            }
441            res = stmt.executeQuery();
442            while (res.next()) {
443                historyIDs.add(new CmsUUID(res.getString(1)));
444            }
445        } catch (SQLException e) {
446            throw new CmsDbSqlException(
447                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
448                e);
449        } finally {
450            m_sqlManager.closeAll(dbc, conn, stmt, res);
451        }
452
453        // get the matching history resources from the found structure IDs
454        String parentFolderPath = "";
455        if (parent != null) {
456            parentFolderPath = CmsResource.getFolderPath(parent.getRootPath());
457        }
458        for (Iterator<CmsUUID> i = historyIDs.iterator(); i.hasNext();) {
459            CmsUUID id = i.next();
460            int version = m_driverManager.getHistoryDriver(dbc).readLastVersion(dbc, id);
461            if (version > 0) {
462                I_CmsHistoryResource histRes = m_driverManager.getHistoryDriver(dbc).readResource(dbc, id, version);
463                if (parent != null) {
464                    if (!includeSubFolders
465                        && !parentFolderPath.equals(CmsResource.getFolderPath(histRes.getRootPath()))) {
466                        // deleted history resource is not in the specified parent folder, skip it
467                        continue;
468                    } else if (includeSubFolders && !histRes.getRootPath().startsWith(parentFolderPath)) {
469                        // deleted history resource is not in the specified parent folder or sub folder, skip it
470                        continue;
471                    }
472                }
473                resources.add(histRes);
474            }
475        }
476
477        return resources;
478    }
479
480    /**
481     * @see org.opencms.db.I_CmsSubscriptionDriver#readSubscribedResources(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.db.CmsSubscriptionFilter)
482     */
483    public List<CmsResource> readSubscribedResources(CmsDbContext dbc, String poolName, CmsSubscriptionFilter filter)
484    throws CmsDataAccessException {
485
486        PreparedStatement stmt = null;
487        Connection conn = null;
488        ResultSet res = null;
489        CmsResource currentResource = null;
490        List<CmsResource> resources = new ArrayList<CmsResource>();
491
492        String queryBuf = m_sqlManager.readQuery(dbc.currentProject(), "C_SUBSCRIPTION_FILTER_READ");
493
494        StringBuffer conditions = new StringBuffer(256);
495        List<I_CmsPreparedStatementParameter> params = new ArrayList<I_CmsPreparedStatementParameter>();
496
497        boolean userDefined = filter.getUser() != null;
498        boolean groupsDefined = !filter.getGroups().isEmpty();
499        if (!groupsDefined && !userDefined) {
500            filter.setUser(dbc.currentUser());
501            userDefined = true;
502        }
503        // check if a user has been set for the "visited" and "unvisited" mode
504        if (!filter.getMode().isAll() && (filter.getUser() == null)) {
505            // change the mode, without user the other modes are not applicable
506            filter.setMode(CmsSubscriptionReadMode.ALL);
507        }
508
509        List<String> principalIds = new ArrayList<String>();
510        // add user ID
511        if (userDefined) {
512            principalIds.add(filter.getUser().getId().toString());
513        }
514        // add group IDs
515        if (groupsDefined) {
516            Iterator<CmsGroup> it = filter.getGroups().iterator();
517            while (it.hasNext()) {
518                principalIds.add(it.next().getId().toString());
519            }
520        }
521
522        if (principalIds.size() == 1) {
523            // single principal filter
524            conditions.append(BEGIN_CONDITION);
525            conditions.append(m_sqlManager.readQuery(dbc.currentProject(), "C_SUBSCRIPTION_FILTER_PRINCIPAL_SINGLE"));
526            params.add(new CmsPreparedStatementStringParameter(principalIds.get(0)));
527            conditions.append(END_CONDITION);
528        } else {
529            // multiple principals filter
530            conditions.append(BEGIN_CONDITION);
531            conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_FILTER_PRINCIPALS"));
532            conditions.append(BEGIN_CONDITION);
533            Iterator<String> it = principalIds.iterator();
534            while (it.hasNext()) {
535                params.add(new CmsPreparedStatementStringParameter(it.next()));
536                conditions.append("?");
537                if (it.hasNext()) {
538                    conditions.append(", ");
539                }
540            }
541            conditions.append(END_CONDITION);
542            conditions.append(m_sqlManager.readQuery(dbc.currentProject(), "C_SUBSCRIPTION_FILTER_PRINCIPALS_END"));
543            conditions.append(END_CONDITION);
544        }
545
546        // path filter
547        if (CmsStringUtil.isNotEmpty(filter.getParentPath())) {
548            CmsResource parent = m_driverManager.getVfsDriver(dbc).readResource(
549                dbc,
550                dbc.currentProject().getUuid(),
551                filter.getParentPath(),
552                false);
553            conditions.append(BEGIN_INCLUDE_CONDITION);
554            if (filter.isIncludeSubFolders()) {
555                conditions.append(m_sqlManager.readQuery(dbc.currentProject(), "C_RESOURCES_SELECT_BY_PATH_PREFIX"));
556                params.add(
557                    new CmsPreparedStatementStringParameter(
558                        CmsFileUtil.addTrailingSeparator(CmsVfsDriver.escapeDbWildcard(filter.getParentPath())) + "%"));
559            } else {
560                conditions.append(m_sqlManager.readQuery(dbc.currentProject(), "C_RESOURCES_SELECT_BY_PARENT_UUID"));
561                params.add(new CmsPreparedStatementStringParameter(parent.getStructureId().toString()));
562            }
563            conditions.append(END_CONDITION);
564        }
565
566        // check from and to date
567        if ((filter.getFromDate() > 0) || (filter.getToDate() < Long.MAX_VALUE)) {
568            conditions.append(BEGIN_INCLUDE_CONDITION);
569            conditions.append(
570                m_sqlManager.readQuery(dbc.currentProject(), "C_SUBSCRIPTION_FILTER_RESOURCES_DATE_MODIFIED"));
571            params.add(new CmsPreparedStatementLongParameter(filter.getFromDate()));
572            params.add(new CmsPreparedStatementLongParameter(filter.getToDate()));
573            conditions.append(END_CONDITION);
574        }
575
576        try {
577            conn = m_sqlManager.getConnection(poolName);
578            queryBuf = CmsStringUtil.substitute(queryBuf, "%(CONDITIONS)", conditions.toString());
579            if (LOG.isDebugEnabled()) {
580                LOG.debug(queryBuf.toString());
581            }
582            stmt = m_sqlManager.getPreparedStatementForSql(conn, queryBuf);
583
584            // set parameters
585            for (int i = 0; i < params.size(); i++) {
586                I_CmsPreparedStatementParameter param = params.get(i);
587                param.insertIntoStatement(stmt, i + 1);
588            }
589            res = stmt.executeQuery();
590
591            while (res.next()) {
592                currentResource = m_driverManager.getVfsDriver(dbc).createFile(
593                    res,
594                    dbc.currentProject().getUuid(),
595                    false);
596                resources.add(currentResource);
597            }
598        } catch (SQLException e) {
599            throw new CmsDbSqlException(
600                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
601                e);
602        } finally {
603            m_sqlManager.closeAll(dbc, conn, stmt, res);
604        }
605
606        // filter the result if in visited/unvisited mode (faster as creating a query with even more joined tables)
607        if (!filter.getMode().isAll()) {
608            List<CmsResource> result = new ArrayList<CmsResource>(resources.size());
609            for (Iterator<CmsResource> i = resources.iterator(); i.hasNext();) {
610                CmsResource resource = i.next();
611                long visitedDate = 0;
612                try {
613                    visitedDate = m_driverManager.getDateLastVisitedBy(dbc, poolName, filter.getUser(), resource);
614                } catch (CmsException e) {
615                    throw new CmsDbSqlException(Messages.get().container(Messages.ERR_GENERIC_SQL_0), e);
616                }
617                if (filter.getMode().isUnVisited() && (visitedDate >= resource.getDateLastModified())) {
618                    // unvisited mode: resource was visited after the last modification, skip it
619                    continue;
620                }
621                if (filter.getMode().isVisited() && (resource.getDateLastModified() > visitedDate)) {
622                    // visited mode: resource was not visited after last modification, skip it
623                    continue;
624                }
625                // add the current resource to the result
626                result.add(resource);
627            }
628            resources = result;
629        }
630        return resources;
631    }
632
633    /**
634     * Reads {@link CmsVisitEntry} objects from the database.<p>
635     *
636     * @param dbc the database context to use
637     * @param poolName the name of the pool which should be used for the database operation
638     * @param filter a filter for constraining the list of results
639     *
640     * @return a list of visit entries
641     *
642     * @throws CmsDataAccessException if the database operation fails
643     */
644    public List<CmsVisitEntry> readVisits(CmsDbContext dbc, String poolName, CmsVisitEntryFilter filter)
645    throws CmsDataAccessException {
646
647        List<CmsVisitEntry> entries = new ArrayList<CmsVisitEntry>();
648
649        Connection conn = null;
650        PreparedStatement stmt = null;
651        ResultSet res = null;
652
653        try {
654            if (CmsStringUtil.isNotEmpty(poolName)) {
655                conn = m_sqlManager.getConnection(poolName);
656            } else {
657                conn = m_sqlManager.getConnection(dbc);
658            }
659
660            // compose statement
661            StringBuffer queryBuf = new StringBuffer(256);
662            queryBuf.append(m_sqlManager.readQuery("C_VISIT_READ_ENTRIES"));
663            CmsPair<String, List<I_CmsPreparedStatementParameter>> conditionsAndParameters = prepareVisitConditions(
664                filter);
665            List<I_CmsPreparedStatementParameter> params = conditionsAndParameters.getSecond();
666            queryBuf.append(conditionsAndParameters.getFirst());
667
668            if (LOG.isDebugEnabled()) {
669                LOG.debug(queryBuf.toString());
670            }
671            stmt = m_sqlManager.getPreparedStatementForSql(conn, queryBuf.toString());
672            for (int i = 0; i < params.size(); i++) {
673                I_CmsPreparedStatementParameter param = params.get(i);
674                param.insertIntoStatement(stmt, i + 1);
675            }
676
677            // execute
678            res = stmt.executeQuery();
679            while (res.next()) {
680                // get results
681                entries.add(internalReadVisitEntry(res));
682            }
683        } catch (SQLException e) {
684            throw new CmsDbSqlException(
685                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
686                e);
687        } finally {
688            m_sqlManager.closeAll(dbc, conn, stmt, res);
689        }
690        return entries;
691    }
692
693    /**
694     * @see org.opencms.db.I_CmsSubscriptionDriver#setSubscribedResourceAsDeleted(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.file.CmsResource)
695     */
696    public void setSubscribedResourceAsDeleted(CmsDbContext dbc, String poolName, CmsResource resource)
697    throws CmsDataAccessException {
698
699        PreparedStatement stmt = null;
700        Connection conn = null;
701        long deletedTime = System.currentTimeMillis();
702
703        try {
704            conn = getSqlManager().getConnection(poolName);
705            // set resource as deleted for all users and groups
706            stmt = m_sqlManager.getPreparedStatement(conn, "C_SUBSCRIPTION_UPDATE_DATE_2");
707            stmt.setLong(1, deletedTime);
708            stmt.setString(2, resource.getStructureId().toString());
709            stmt.executeUpdate();
710        } catch (SQLException e) {
711            throw new CmsDbSqlException(
712                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
713                e);
714        } finally {
715            m_sqlManager.closeAll(dbc, conn, stmt, null);
716        }
717
718    }
719
720    /**
721     * @see org.opencms.db.I_CmsSubscriptionDriver#subscribeResourceFor(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.security.CmsPrincipal, org.opencms.file.CmsResource)
722     */
723    public void subscribeResourceFor(CmsDbContext dbc, String poolName, CmsPrincipal principal, CmsResource resource)
724    throws CmsDataAccessException {
725
726        ResultSet res = null;
727        PreparedStatement stmt = null;
728        Connection conn = null;
729
730        try {
731            conn = getSqlManager().getConnection(poolName);
732            stmt = m_sqlManager.getPreparedStatement(conn, "C_SUBSCRIPTION_CHECK_2");
733            stmt.setString(1, principal.getId().toString());
734            stmt.setString(2, resource.getStructureId().toString());
735            res = stmt.executeQuery();
736
737            // only create subscription entry if principal is not subscribed to resource
738            if (res.next()) {
739                while (res.next()) {
740                    // do nothing only move through all rows because of mssql odbc driver
741                }
742            } else {
743                // subscribe principal
744                m_sqlManager.closeAll(dbc, null, stmt, null);
745                stmt = m_sqlManager.getPreparedStatement(conn, "C_SUBSCRIPTION_CREATE_2");
746                stmt.setString(1, principal.getId().toString());
747                stmt.setString(2, resource.getStructureId().toString());
748                stmt.executeUpdate();
749            }
750        } catch (SQLException e) {
751            throw new CmsDbSqlException(
752                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
753                e);
754        } finally {
755            m_sqlManager.closeAll(dbc, conn, stmt, res);
756        }
757    }
758
759    /**
760     * @see org.opencms.db.I_CmsSubscriptionDriver#unsubscribeAllDeletedResources(org.opencms.db.CmsDbContext, java.lang.String, long)
761     */
762    public void unsubscribeAllDeletedResources(CmsDbContext dbc, String poolName, long deletedTo)
763    throws CmsDataAccessException {
764
765        PreparedStatement stmt = null;
766        Connection conn = null;
767
768        try {
769            conn = getSqlManager().getConnection(poolName);
770            StringBuffer conditions = new StringBuffer(256);
771
772            // unsubscribe all deleted resources
773            conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETE"));
774            conditions.append(BEGIN_CONDITION);
775            conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETE_FILTER_DATE"));
776            conditions.append(END_CONDITION);
777            stmt = m_sqlManager.getPreparedStatementForSql(conn, conditions.toString());
778            stmt.setLong(1, deletedTo);
779            stmt.executeUpdate();
780
781        } catch (SQLException e) {
782            throw new CmsDbSqlException(
783                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
784                e);
785        } finally {
786            m_sqlManager.closeAll(dbc, conn, stmt, null);
787        }
788    }
789
790    /**
791     * @see org.opencms.db.I_CmsSubscriptionDriver#unsubscribeAllResourcesFor(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.security.CmsPrincipal)
792     */
793    public void unsubscribeAllResourcesFor(CmsDbContext dbc, String poolName, CmsPrincipal principal)
794    throws CmsDataAccessException {
795
796        PreparedStatement stmt = null;
797        Connection conn = null;
798
799        try {
800            if (principal != null) {
801                conn = getSqlManager().getConnection(poolName);
802                StringBuffer conditions = new StringBuffer(256);
803
804                conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETE"));
805                conditions.append(BEGIN_CONDITION);
806                conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETE_FILTER_PRINCIPAL"));
807                conditions.append(END_CONDITION);
808                stmt = m_sqlManager.getPreparedStatementForSql(conn, conditions.toString());
809                stmt.setString(1, principal.getId().toString());
810                stmt.executeUpdate();
811            }
812        } catch (SQLException e) {
813            throw new CmsDbSqlException(
814                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
815                e);
816        } finally {
817            m_sqlManager.closeAll(dbc, conn, stmt, null);
818        }
819    }
820
821    /**
822     * @see org.opencms.db.I_CmsSubscriptionDriver#unsubscribeResourceFor(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.security.CmsPrincipal, org.opencms.file.CmsResource)
823     */
824    public void unsubscribeResourceFor(CmsDbContext dbc, String poolName, CmsPrincipal principal, CmsResource resource)
825    throws CmsDataAccessException {
826
827        PreparedStatement stmt = null;
828        Connection conn = null;
829
830        try {
831            conn = getSqlManager().getConnection(poolName);
832            StringBuffer conditions = new StringBuffer(256);
833            conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETE"));
834            conditions.append(BEGIN_CONDITION);
835            conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETE_FILTER_PRINCIPAL"));
836            conditions.append(END_CONDITION);
837            conditions.append(BEGIN_INCLUDE_CONDITION);
838            conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETE_FILTER_STRUCTURE"));
839            conditions.append(END_CONDITION);
840            stmt = m_sqlManager.getPreparedStatementForSql(conn, conditions.toString());
841            stmt.setString(1, principal.getId().toString());
842            stmt.setString(2, resource.getStructureId().toString());
843            stmt.executeUpdate();
844        } catch (SQLException e) {
845            throw new CmsDbSqlException(
846                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
847                e);
848        } finally {
849            m_sqlManager.closeAll(dbc, conn, stmt, null);
850        }
851    }
852
853    /**
854     * @see org.opencms.db.I_CmsSubscriptionDriver#unsubscribeResourceForAll(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.file.CmsResource)
855     */
856    public void unsubscribeResourceForAll(CmsDbContext dbc, String poolName, CmsResource resource)
857    throws CmsDataAccessException {
858
859        PreparedStatement stmt = null;
860        Connection conn = null;
861
862        try {
863            conn = getSqlManager().getConnection(poolName);
864            StringBuffer conditions = new StringBuffer(256);
865
866            // unsubscribe resource for all principals
867            conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETE"));
868            conditions.append(BEGIN_CONDITION);
869            conditions.append(m_sqlManager.readQuery("C_SUBSCRIPTION_DELETE_FILTER_STRUCTURE"));
870            conditions.append(END_CONDITION);
871            stmt = m_sqlManager.getPreparedStatementForSql(conn, conditions.toString());
872            stmt.setString(1, resource.getStructureId().toString());
873            stmt.executeUpdate();
874        } catch (SQLException e) {
875            throw new CmsDbSqlException(
876                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
877                e);
878        } finally {
879            m_sqlManager.closeAll(dbc, conn, stmt, null);
880        }
881    }
882
883    /**
884     * Adds an entry to the table of visits.<p>
885     *
886     * @param dbc the database context to use
887     * @param poolName the name of the database pool to use
888     * @param visit the visit bean
889     *
890     * @throws CmsDbSqlException if the database operation fails
891     */
892    protected void addVisit(CmsDbContext dbc, String poolName, CmsVisitEntry visit) throws CmsDbSqlException {
893
894        Connection conn = null;
895        PreparedStatement stmt = null;
896
897        try {
898            if (CmsStringUtil.isNotEmpty(poolName)) {
899                conn = m_sqlManager.getConnection(poolName);
900            } else {
901                conn = m_sqlManager.getConnection(dbc);
902            }
903
904            stmt = m_sqlManager.getPreparedStatement(conn, "C_VISIT_CREATE_3");
905
906            stmt.setString(1, visit.getUserId().toString());
907            stmt.setLong(2, visit.getDate());
908            stmt.setString(3, visit.getStructureId() == null ? null : visit.getStructureId().toString());
909            try {
910                stmt.executeUpdate();
911            } catch (SQLException e) {
912                // ignore, most likely a duplicate entry
913                LOG.debug(
914                    Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)).key(),
915                    e);
916            }
917
918        } catch (SQLException e) {
919            throw new CmsDbSqlException(
920                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
921                e);
922        } finally {
923            try {
924                m_sqlManager.closeAll(dbc, conn, stmt, null);
925            } catch (Throwable t) {
926                // this could happen during shutdown
927                LOG.debug(t.getLocalizedMessage(), t);
928            }
929        }
930    }
931
932    /**
933     * Creates a new {@link CmsVisitEntry} object from the given result set entry.<p>
934     *
935     * @param res the result set
936     *
937     * @return the new {@link CmsVisitEntry} object
938     *
939     * @throws SQLException if something goes wrong
940     */
941    protected CmsVisitEntry internalReadVisitEntry(ResultSet res) throws SQLException {
942
943        CmsUUID userId = new CmsUUID(res.getString(m_sqlManager.readQuery("C_VISIT_USER_ID")));
944        long date = res.getLong(m_sqlManager.readQuery("C_VISIT_DATE"));
945        CmsUUID structureId = new CmsUUID(res.getString(m_sqlManager.readQuery("C_VISIT_STRUCTURE_ID")));
946        return new CmsVisitEntry(userId, date, structureId);
947    }
948
949    /**
950     * Build the whole WHERE SQL statement part for the given visit entry filter.<p>
951     *
952     * @param filter the filter
953     *
954     * @return a pair containing both the SQL and the parameters for it
955     */
956    protected CmsPair<String, List<I_CmsPreparedStatementParameter>> prepareVisitConditions(
957        CmsVisitEntryFilter filter) {
958
959        List<I_CmsPreparedStatementParameter> params = new ArrayList<I_CmsPreparedStatementParameter>();
960        StringBuffer conditions = new StringBuffer();
961
962        // user id filter
963        if (filter.getUserId() != null) {
964            if (conditions.length() == 0) {
965                conditions.append(BEGIN_CONDITION);
966            } else {
967                conditions.append(BEGIN_INCLUDE_CONDITION);
968            }
969            conditions.append(m_sqlManager.readQuery("C_VISIT_FILTER_USER_ID"));
970            params.add(new CmsPreparedStatementStringParameter(filter.getUserId().toString()));
971            conditions.append(END_CONDITION);
972        }
973
974        // resource id filter
975        if (filter.getStructureId() != null) {
976            if (conditions.length() == 0) {
977                conditions.append(BEGIN_CONDITION);
978            } else {
979                conditions.append(BEGIN_INCLUDE_CONDITION);
980            }
981            conditions.append(m_sqlManager.readQuery("C_VISIT_FILTER_STRUCTURE_ID"));
982            params.add(new CmsPreparedStatementStringParameter(filter.getStructureId().toString()));
983            conditions.append(END_CONDITION);
984        }
985
986        // date from filter
987        if (filter.getDateFrom() != CmsResource.DATE_RELEASED_DEFAULT) {
988            if (conditions.length() == 0) {
989                conditions.append(BEGIN_CONDITION);
990            } else {
991                conditions.append(BEGIN_INCLUDE_CONDITION);
992            }
993            conditions.append(m_sqlManager.readQuery("C_VISIT_FILTER_DATE_FROM"));
994            params.add(new CmsPreparedStatementLongParameter(filter.getDateFrom()));
995            conditions.append(END_CONDITION);
996        }
997
998        // date to filter
999        if (filter.getDateTo() != CmsResource.DATE_RELEASED_DEFAULT) {
1000            if (conditions.length() == 0) {
1001                conditions.append(BEGIN_CONDITION);
1002            } else {
1003                conditions.append(BEGIN_INCLUDE_CONDITION);
1004            }
1005            conditions.append(m_sqlManager.readQuery("C_VISIT_FILTER_DATE_TO"));
1006            params.add(new CmsPreparedStatementLongParameter(filter.getDateTo()));
1007            conditions.append(END_CONDITION);
1008        }
1009        return CmsPair.create(conditions.toString(), params);
1010    }
1011
1012}