001/*
002 * This library is part of OpenCms -
003 * the Open Source Content Management System
004 *
005 * Copyright (c) Alkacon Software GmbH & Co. KG (http://www.alkacon.com)
006 *
007 * This library is free software; you can redistribute it and/or
008 * modify it under the terms of the GNU Lesser General Public
009 * License as published by the Free Software Foundation; either
010 * version 2.1 of the License, or (at your option) any later version.
011 *
012 * This library is distributed in the hope that it will be useful,
013 * but WITHOUT ANY WARRANTY; without even the implied warranty of
014 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
015 * Lesser General Public License for more details.
016 *
017 * For further information about Alkacon Software GmbH & Co. KG, please see the
018 * company website: http://www.alkacon.com
019 *
020 * For further information about OpenCms, please see the
021 * project website: http://www.opencms.org
022 *
023 * You should have received a copy of the GNU Lesser General Public
024 * License along with this library; if not, write to the Free Software
025 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
026 */
027
028package org.opencms.db.generic;
029
030import org.opencms.configuration.CmsConfigurationManager;
031import org.opencms.configuration.CmsParameterConfiguration;
032import org.opencms.db.CmsAlias;
033import org.opencms.db.CmsAliasFilter;
034import org.opencms.db.CmsDbConsistencyException;
035import org.opencms.db.CmsDbContext;
036import org.opencms.db.CmsDbEntryNotFoundException;
037import org.opencms.db.CmsDbSqlException;
038import org.opencms.db.CmsDbUtil;
039import org.opencms.db.CmsDriverManager;
040import org.opencms.db.CmsPreparedStatementIntParameter;
041import org.opencms.db.CmsPreparedStatementStringParameter;
042import org.opencms.db.CmsResourceState;
043import org.opencms.db.CmsRewriteAlias;
044import org.opencms.db.CmsRewriteAliasFilter;
045import org.opencms.db.CmsVfsOnlineResourceAlreadyExistsException;
046import org.opencms.db.I_CmsDriver;
047import org.opencms.db.I_CmsPreparedStatementParameter;
048import org.opencms.db.I_CmsProjectDriver;
049import org.opencms.db.I_CmsVfsDriver;
050import org.opencms.db.urlname.CmsUrlNameMappingEntry;
051import org.opencms.db.urlname.CmsUrlNameMappingFilter;
052import org.opencms.file.CmsDataAccessException;
053import org.opencms.file.CmsFile;
054import org.opencms.file.CmsFolder;
055import org.opencms.file.CmsProject;
056import org.opencms.file.CmsProperty;
057import org.opencms.file.CmsPropertyDefinition;
058import org.opencms.file.CmsResource;
059import org.opencms.file.CmsResourceFilter;
060import org.opencms.file.CmsVfsException;
061import org.opencms.file.CmsVfsResourceAlreadyExistsException;
062import org.opencms.file.CmsVfsResourceNotFoundException;
063import org.opencms.file.I_CmsResource;
064import org.opencms.file.history.I_CmsHistoryResource;
065import org.opencms.file.types.CmsResourceTypeJsp;
066import org.opencms.gwt.shared.alias.CmsAliasMode;
067import org.opencms.main.CmsEvent;
068import org.opencms.main.CmsException;
069import org.opencms.main.CmsLog;
070import org.opencms.main.I_CmsEventListener;
071import org.opencms.main.OpenCms;
072import org.opencms.relations.CmsRelation;
073import org.opencms.relations.CmsRelationFilter;
074import org.opencms.relations.CmsRelationType;
075import org.opencms.security.CmsOrganizationalUnit;
076import org.opencms.security.CmsPermissionSet;
077import org.opencms.util.CmsFileUtil;
078import org.opencms.util.CmsPair;
079import org.opencms.util.CmsStringUtil;
080import org.opencms.util.CmsUUID;
081
082import java.io.ByteArrayInputStream;
083import java.sql.Connection;
084import java.sql.PreparedStatement;
085import java.sql.ResultSet;
086import java.sql.SQLException;
087import java.util.ArrayList;
088import java.util.Collection;
089import java.util.Collections;
090import java.util.HashMap;
091import java.util.HashSet;
092import java.util.Iterator;
093import java.util.List;
094import java.util.Map;
095import java.util.Set;
096
097import org.apache.commons.logging.Log;
098
099/**
100 * Generic (ANSI-SQL) database server implementation of the VFS driver methods.<p>
101 *
102 * @since 6.0.0
103 */
104public class CmsVfsDriver implements I_CmsDriver, I_CmsVfsDriver {
105
106    /** Contains the macro replacement value for the offline project. */
107    protected static final String OFFLINE = "OFFLINE";
108
109    /** Contains the macro replacement value for the online project. */
110    protected static final String ONLINE = "ONLINE";
111
112    /** The log object for this class. */
113    private static final Log LOG = CmsLog.getLog(org.opencms.db.generic.CmsVfsDriver.class);
114
115    /** The driver manager. */
116    protected CmsDriverManager m_driverManager;
117
118    /**
119     * This field is temporarily used to compute the versions during publishing.<p>
120     *
121     * @see #publishVersions(CmsDbContext, CmsResource, boolean)
122     */
123    protected List<CmsUUID> m_resOp = new ArrayList<CmsUUID>();
124
125    /** The sql manager. */
126    protected CmsSqlManager m_sqlManager;
127
128    /**
129     * This method prepares the SQL conditions for mapping entries for a given URL name mapping filter.<p>
130     *
131     * @param filter the filter from which the SQL conditions should be generated
132     *
133     * @return a pair consisting of an SQL string and a list of the prepared statement parameters for the SQL
134     */
135    public static CmsPair<String, List<I_CmsPreparedStatementParameter>> prepareUrlNameMappingConditions(
136        CmsUrlNameMappingFilter filter) {
137
138        List<String> sqlConditions = new ArrayList<String>();
139        List<I_CmsPreparedStatementParameter> parameters = new ArrayList<I_CmsPreparedStatementParameter>();
140        if (filter.getName() != null) {
141            sqlConditions.add("NAME = ?");
142            parameters.add(new CmsPreparedStatementStringParameter(filter.getName()));
143        }
144
145        if (filter.getStructureId() != null) {
146            sqlConditions.add("STRUCTURE_ID = ?");
147            parameters.add(new CmsPreparedStatementStringParameter(filter.getStructureId().toString()));
148        }
149
150        if (filter.getNamePattern() != null) {
151            sqlConditions.add(" NAME LIKE ? ");
152            parameters.add(new CmsPreparedStatementStringParameter(filter.getNamePattern()));
153        }
154
155        if ((filter.getStates() != null) && (filter.getStates().length > 0)) {
156            List<String> stateConditions = new ArrayList<String>();
157            stateConditions.add("1 = 0");
158            for (int i = 0; i < filter.getStates().length; i++) {
159                stateConditions.add("STATE = ?");
160                parameters.add(new CmsPreparedStatementIntParameter(filter.getStates()[i]));
161            }
162            sqlConditions.add("( " + CmsStringUtil.listAsString(stateConditions, " OR ") + ")");
163
164        }
165
166        if (filter.getRejectStructureId() != null) {
167            sqlConditions.add(" STRUCTURE_ID <> ? ");
168            parameters.add(new CmsPreparedStatementStringParameter(filter.getRejectStructureId().toString()));
169        }
170
171        if (filter.getLocale() != null) {
172            sqlConditions.add(" LOCALE = ? ");
173            parameters.add(new CmsPreparedStatementStringParameter(filter.getLocale()));
174        }
175
176        String conditionString = CmsStringUtil.listAsString(sqlConditions, " AND ");
177        return CmsPair.create(conditionString, parameters);
178    }
179
180    /**
181     * Escapes the database wildcards within the resource path.<p>
182     *
183     * This method is required to ensure chars in the resource path that have a special
184     * meaning in SQL (for example "_", which is the "any char" operator) are escaped.<p>
185     *
186     * It will escape the following chars:
187     * <ul>
188     * <li>"_" to "|_"</li>
189     * </ul>
190     *
191     * @param path the resource path
192     * @return the escaped resource path
193     */
194    protected static String escapeDbWildcard(String path) {
195
196        return CmsStringUtil.substitute(path, "_", "|_");
197    }
198
199    /**
200     * @see org.opencms.db.I_CmsVfsDriver#addUrlNameMappingEntry(org.opencms.db.CmsDbContext, boolean, org.opencms.db.urlname.CmsUrlNameMappingEntry)
201     */
202    public void addUrlNameMappingEntry(CmsDbContext dbc, boolean online, CmsUrlNameMappingEntry entry)
203    throws CmsDataAccessException {
204
205        Connection conn = null;
206        PreparedStatement stmt = null;
207        String query = m_sqlManager.readQuery("C_ADD_URLNAME_MAPPING");
208        query = replaceProject(query, online);
209        try {
210            conn = m_sqlManager.getConnection(dbc);
211            stmt = m_sqlManager.getPreparedStatementForSql(conn, query);
212            stmt.setString(1, entry.getName());
213            stmt.setString(2, entry.getStructureId().toString());
214            stmt.setInt(3, entry.getState());
215            stmt.setLong(4, entry.getDateChanged());
216            stmt.setString(5, entry.getLocale());
217            stmt.executeUpdate();
218        } catch (SQLException e) {
219            throw wrapException(stmt, e);
220        } finally {
221            m_sqlManager.closeAll(dbc, conn, stmt, null);
222        }
223    }
224
225    /**
226     * Counts the number of siblings of a resource.<p>
227     *
228     * @param dbc the current database context
229     * @param projectId the current project id
230     * @param resourceId the resource id to count the number of siblings from
231     *
232     * @return number of siblings
233     * @throws CmsDataAccessException if something goes wrong
234     */
235    public int countSiblings(CmsDbContext dbc, CmsUUID projectId, CmsUUID resourceId) throws CmsDataAccessException {
236
237        Connection conn = null;
238        PreparedStatement stmt = null;
239        ResultSet res = null;
240        int count = 0;
241
242        try {
243            conn = m_sqlManager.getConnection(dbc);
244
245            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RESOURCES_COUNT_SIBLINGS");
246            stmt.setString(1, resourceId.toString());
247            res = stmt.executeQuery();
248
249            if (res.next()) {
250                count = res.getInt(1);
251                while (res.next()) {
252                    // do nothing only move through all rows because of mssql odbc driver
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
263        return count;
264    }
265
266    /**
267    * @see org.opencms.db.I_CmsVfsDriver#createContent(CmsDbContext, CmsUUID, CmsUUID, byte[])
268    */
269    public void createContent(CmsDbContext dbc, CmsUUID projectId, CmsUUID resourceId, byte[] content)
270    throws CmsDataAccessException {
271
272        Connection conn = null;
273        PreparedStatement stmt = null;
274
275        try {
276            conn = m_sqlManager.getConnection(dbc);
277            // create new offline content
278            stmt = m_sqlManager.getPreparedStatement(conn, "C_OFFLINE_CONTENTS_WRITE");
279            stmt.setString(1, resourceId.toString());
280            if (content.length < 2000) {
281                stmt.setBytes(2, content);
282            } else {
283                stmt.setBinaryStream(2, new ByteArrayInputStream(content), content.length);
284            }
285            stmt.executeUpdate();
286        } catch (SQLException e) {
287            throw new CmsDbSqlException(
288                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
289                e);
290        } finally {
291            m_sqlManager.closeAll(dbc, conn, stmt, null);
292        }
293    }
294
295    /**
296     * @see org.opencms.db.I_CmsVfsDriver#createFile(java.sql.ResultSet, CmsUUID)
297     */
298    public CmsFile createFile(ResultSet res, CmsUUID projectId) throws SQLException {
299
300        CmsUUID structureId = new CmsUUID(res.getString(m_sqlManager.readQuery("C_RESOURCES_STRUCTURE_ID")));
301        CmsUUID resourceId = new CmsUUID(res.getString(m_sqlManager.readQuery("C_RESOURCES_RESOURCE_ID")));
302        int resourceType = res.getInt(m_sqlManager.readQuery("C_RESOURCES_RESOURCE_TYPE"));
303        String resourcePath = res.getString(m_sqlManager.readQuery("C_RESOURCES_RESOURCE_PATH"));
304        int resourceFlags = res.getInt(m_sqlManager.readQuery("C_RESOURCES_RESOURCE_FLAGS"));
305        int resourceState = res.getInt(m_sqlManager.readQuery("C_RESOURCES_STATE"));
306        int structureState = res.getInt(m_sqlManager.readQuery("C_RESOURCES_STRUCTURE_STATE"));
307        long dateCreated = res.getLong(m_sqlManager.readQuery("C_RESOURCES_DATE_CREATED"));
308        long dateLastModified = res.getLong(m_sqlManager.readQuery("C_RESOURCES_DATE_LASTMODIFIED"));
309        long dateReleased = res.getLong(m_sqlManager.readQuery("C_RESOURCES_DATE_RELEASED"));
310        long dateExpired = res.getLong(m_sqlManager.readQuery("C_RESOURCES_DATE_EXPIRED"));
311        int resourceSize = res.getInt(m_sqlManager.readQuery("C_RESOURCES_SIZE"));
312        CmsUUID userCreated = new CmsUUID(res.getString(m_sqlManager.readQuery("C_RESOURCES_USER_CREATED")));
313        CmsUUID userLastModified = new CmsUUID(res.getString(m_sqlManager.readQuery("C_RESOURCES_USER_LASTMODIFIED")));
314        byte[] content = m_sqlManager.getBytes(res, m_sqlManager.readQuery("C_RESOURCES_FILE_CONTENT"));
315        int siblingCount = res.getInt(m_sqlManager.readQuery("C_RESOURCES_SIBLING_COUNT"));
316        long dateContent = res.getLong(m_sqlManager.readQuery("C_RESOURCES_DATE_CONTENT"));
317        int resourceVersion = res.getInt(m_sqlManager.readQuery("C_RESOURCES_VERSION"));
318        int structureVersion = res.getInt(m_sqlManager.readQuery("C_RESOURCES_STRUCTURE_VERSION"));
319
320        // calculate the overall state
321        int newState = (structureState > resourceState) ? structureState : resourceState;
322
323        // in case of folder type ensure, that the root path has a trailing slash
324        if (CmsFolder.isFolderType(resourceType)) {
325            resourcePath = CmsFileUtil.addTrailingSeparator(resourcePath);
326        }
327
328        return new CmsFile(
329            structureId,
330            resourceId,
331            resourcePath,
332            resourceType,
333            resourceFlags,
334            projectId,
335            CmsResourceState.valueOf(newState),
336            dateCreated,
337            userCreated,
338            dateLastModified,
339            userLastModified,
340            dateReleased,
341            dateExpired,
342            siblingCount,
343            resourceSize,
344            dateContent,
345            resourceVersion + structureVersion,
346            content);
347    }
348
349    /**
350     * @see org.opencms.db.I_CmsVfsDriver#createFile(java.sql.ResultSet, CmsUUID, boolean)
351     */
352    public CmsFile createFile(ResultSet res, CmsUUID projectId, boolean hasFileContentInResultSet) throws SQLException {
353
354        byte[] content = null;
355
356        CmsUUID resProjectId = null;
357
358        CmsUUID structureId = new CmsUUID(res.getString(m_sqlManager.readQuery("C_RESOURCES_STRUCTURE_ID")));
359        CmsUUID resourceId = new CmsUUID(res.getString(m_sqlManager.readQuery("C_RESOURCES_RESOURCE_ID")));
360        String resourcePath = res.getString(m_sqlManager.readQuery("C_RESOURCES_RESOURCE_PATH"));
361        int resourceType = res.getInt(m_sqlManager.readQuery("C_RESOURCES_RESOURCE_TYPE"));
362        int resourceFlags = res.getInt(m_sqlManager.readQuery("C_RESOURCES_RESOURCE_FLAGS"));
363        int resourceState = res.getInt(m_sqlManager.readQuery("C_RESOURCES_STATE"));
364        int structureState = res.getInt(m_sqlManager.readQuery("C_RESOURCES_STRUCTURE_STATE"));
365        long dateCreated = res.getLong(m_sqlManager.readQuery("C_RESOURCES_DATE_CREATED"));
366        long dateLastModified = res.getLong(m_sqlManager.readQuery("C_RESOURCES_DATE_LASTMODIFIED"));
367        long dateReleased = res.getLong(m_sqlManager.readQuery("C_RESOURCES_DATE_RELEASED"));
368        long dateExpired = res.getLong(m_sqlManager.readQuery("C_RESOURCES_DATE_EXPIRED"));
369        int resourceSize = res.getInt(m_sqlManager.readQuery("C_RESOURCES_SIZE"));
370        CmsUUID userCreated = new CmsUUID(res.getString(m_sqlManager.readQuery("C_RESOURCES_USER_CREATED")));
371        CmsUUID userLastModified = new CmsUUID(res.getString(m_sqlManager.readQuery("C_RESOURCES_USER_LASTMODIFIED")));
372        CmsUUID lockedInProject = new CmsUUID(res.getString("LOCKED_IN_PROJECT"));
373        int siblingCount = res.getInt(m_sqlManager.readQuery("C_RESOURCES_SIBLING_COUNT"));
374        long dateContent = res.getLong(m_sqlManager.readQuery("C_RESOURCES_DATE_CONTENT"));
375        int resourceVersion = res.getInt(m_sqlManager.readQuery("C_RESOURCES_VERSION"));
376        int structureVersion = res.getInt(m_sqlManager.readQuery("C_RESOURCES_STRUCTURE_VERSION"));
377
378        // in case of folder type ensure, that the root path has a trailing slash
379        if (CmsFolder.isFolderType(resourceType)) {
380            resourcePath = CmsFileUtil.addTrailingSeparator(resourcePath);
381        }
382        if (hasFileContentInResultSet) {
383            content = m_sqlManager.getBytes(res, m_sqlManager.readQuery("C_RESOURCES_FILE_CONTENT"));
384        }
385        resProjectId = lockedInProject;
386        int newState = (structureState > resourceState) ? structureState : resourceState;
387
388        return new CmsFile(
389            structureId,
390            resourceId,
391            resourcePath,
392            resourceType,
393            resourceFlags,
394            resProjectId,
395            CmsResourceState.valueOf(newState),
396            dateCreated,
397            userCreated,
398            dateLastModified,
399            userLastModified,
400            dateReleased,
401            dateExpired,
402            siblingCount,
403            resourceSize,
404            dateContent,
405            resourceVersion + structureVersion,
406            content);
407    }
408
409    /**
410     * @see org.opencms.db.I_CmsVfsDriver#createFolder(java.sql.ResultSet, CmsUUID, boolean)
411     */
412    public CmsFolder createFolder(ResultSet res, CmsUUID projectId, boolean hasProjectIdInResultSet)
413    throws SQLException {
414
415        CmsUUID structureId = new CmsUUID(res.getString(m_sqlManager.readQuery("C_RESOURCES_STRUCTURE_ID")));
416        CmsUUID resourceId = new CmsUUID(res.getString(m_sqlManager.readQuery("C_RESOURCES_RESOURCE_ID")));
417        String resourcePath = res.getString(m_sqlManager.readQuery("C_RESOURCES_RESOURCE_PATH"));
418        int resourceType = res.getInt(m_sqlManager.readQuery("C_RESOURCES_RESOURCE_TYPE"));
419        int resourceFlags = res.getInt(m_sqlManager.readQuery("C_RESOURCES_RESOURCE_FLAGS"));
420        int resourceState = res.getInt(m_sqlManager.readQuery("C_RESOURCES_STATE"));
421        int structureState = res.getInt(m_sqlManager.readQuery("C_RESOURCES_STRUCTURE_STATE"));
422        long dateCreated = res.getLong(m_sqlManager.readQuery("C_RESOURCES_DATE_CREATED"));
423        long dateLastModified = res.getLong(m_sqlManager.readQuery("C_RESOURCES_DATE_LASTMODIFIED"));
424        long dateReleased = res.getLong(m_sqlManager.readQuery("C_RESOURCES_DATE_RELEASED"));
425        long dateExpired = res.getLong(m_sqlManager.readQuery("C_RESOURCES_DATE_EXPIRED"));
426        CmsUUID userCreated = new CmsUUID(res.getString(m_sqlManager.readQuery("C_RESOURCES_USER_CREATED")));
427        CmsUUID userLastModified = new CmsUUID(res.getString(m_sqlManager.readQuery("C_RESOURCES_USER_LASTMODIFIED")));
428        CmsUUID resProjectId = new CmsUUID(res.getString("LOCKED_IN_PROJECT"));
429        int resourceVersion = res.getInt(m_sqlManager.readQuery("C_RESOURCES_VERSION"));
430        int structureVersion = res.getInt(m_sqlManager.readQuery("C_RESOURCES_STRUCTURE_VERSION"));
431        int resourceSize = res.getInt(m_sqlManager.readQuery("C_RESOURCES_SIZE"));
432
433        // in case of folder type ensure, that the root path has a trailing slash
434        if (CmsFolder.isFolderSize(resourceSize)) {
435            resourcePath = CmsFileUtil.addTrailingSeparator(resourcePath);
436        }
437
438        int newState = (structureState > resourceState) ? structureState : resourceState;
439
440        return new CmsFolder(
441            structureId,
442            resourceId,
443            resourcePath,
444            resourceType,
445            resourceFlags,
446            resProjectId,
447            CmsResourceState.valueOf(newState),
448            dateCreated,
449            userCreated,
450            dateLastModified,
451            userLastModified,
452            dateReleased,
453            dateExpired,
454            resourceVersion + structureVersion);
455    }
456
457    /**
458     * @see org.opencms.db.I_CmsVfsDriver#createOnlineContent(org.opencms.db.CmsDbContext, org.opencms.util.CmsUUID, byte[], int, boolean, boolean)
459     */
460    public void createOnlineContent(
461        CmsDbContext dbc,
462        CmsUUID resourceId,
463        byte[] contents,
464        int publishTag,
465        boolean keepOnline,
466        boolean needToUpdateContent)
467    throws CmsDataAccessException {
468
469        Connection conn = null;
470        PreparedStatement stmt = null;
471
472        try {
473            conn = m_sqlManager.getConnection(dbc);
474            boolean dbcHasProjectId = (dbc.getProjectId() != null) && !dbc.getProjectId().isNullUUID();
475
476            if (needToUpdateContent || dbcHasProjectId) {
477                if (dbcHasProjectId || !OpenCms.getSystemInfo().isHistoryEnabled()) {
478                    // remove the online content for this resource id
479                    stmt = m_sqlManager.getPreparedStatement(conn, "C_ONLINE_CONTENTS_DELETE");
480                    stmt.setString(1, resourceId.toString());
481                    stmt.executeUpdate();
482                    m_sqlManager.closeAll(dbc, null, stmt, null);
483                } else {
484                    // put the online content in the history, only if explicit requested
485                    stmt = m_sqlManager.getPreparedStatement(conn, "C_ONLINE_CONTENTS_HISTORY");
486                    stmt.setString(1, resourceId.toString());
487                    stmt.executeUpdate();
488                    m_sqlManager.closeAll(dbc, null, stmt, null);
489                }
490
491                // create new online content
492                stmt = m_sqlManager.getPreparedStatement(conn, "C_ONLINE_CONTENTS_WRITE");
493
494                stmt.setString(1, resourceId.toString());
495                if (contents.length < 2000) {
496                    stmt.setBytes(2, contents);
497                } else {
498                    stmt.setBinaryStream(2, new ByteArrayInputStream(contents), contents.length);
499                }
500                stmt.setInt(3, publishTag);
501                stmt.setInt(4, publishTag);
502                stmt.setInt(5, keepOnline ? 1 : 0);
503                stmt.executeUpdate();
504                m_sqlManager.closeAll(dbc, null, stmt, null);
505            } else {
506                // update old content entry
507                stmt = m_sqlManager.getPreparedStatement(conn, "C_HISTORY_CONTENTS_UPDATE");
508                stmt.setInt(1, publishTag);
509                stmt.setString(2, resourceId.toString());
510                stmt.executeUpdate();
511                m_sqlManager.closeAll(dbc, null, stmt, null);
512
513                if (!keepOnline) {
514                    // put the online content in the history
515                    stmt = m_sqlManager.getPreparedStatement(conn, "C_ONLINE_CONTENTS_HISTORY");
516                    stmt.setString(1, resourceId.toString());
517                    stmt.executeUpdate();
518                    m_sqlManager.closeAll(dbc, null, stmt, null);
519                }
520            }
521        } catch (SQLException e) {
522            throw new CmsDbSqlException(
523                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
524                e);
525        } finally {
526            m_sqlManager.closeAll(dbc, conn, stmt, null);
527        }
528    }
529
530    /**
531     * @see org.opencms.db.I_CmsVfsDriver#createPropertyDefinition(org.opencms.db.CmsDbContext, org.opencms.util.CmsUUID, java.lang.String, org.opencms.file.CmsPropertyDefinition.CmsPropertyType)
532     */
533    public CmsPropertyDefinition createPropertyDefinition(
534        CmsDbContext dbc,
535        CmsUUID projectId,
536        String name,
537        CmsPropertyDefinition.CmsPropertyType type)
538    throws CmsDataAccessException {
539
540        Connection conn = null;
541        PreparedStatement stmt = null;
542
543        try {
544            conn = m_sqlManager.getConnection(dbc);
545            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_PROPERTYDEF_CREATE");
546            stmt.setString(1, new CmsUUID().toString());
547            stmt.setString(2, name);
548            stmt.setInt(3, type.getMode());
549            stmt.executeUpdate();
550        } catch (SQLException e) {
551            throw new CmsDbSqlException(
552                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
553                e);
554        } finally {
555            m_sqlManager.closeAll(dbc, conn, stmt, null);
556        }
557
558        return readPropertyDefinition(dbc, name, projectId);
559    }
560
561    /**
562     * @see org.opencms.db.I_CmsVfsDriver#createRelation(org.opencms.db.CmsDbContext, CmsUUID, org.opencms.relations.CmsRelation)
563     */
564    public void createRelation(CmsDbContext dbc, CmsUUID projectId, CmsRelation relation)
565    throws CmsDataAccessException {
566
567        Connection conn = null;
568        PreparedStatement stmt = null;
569
570        try {
571            conn = m_sqlManager.getConnection(dbc);
572            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_CREATE_RELATION");
573            stmt.setString(1, relation.getSourceId().toString());
574            stmt.setString(2, relation.getSourcePath());
575            stmt.setString(3, relation.getTargetId().toString());
576            stmt.setString(4, relation.getTargetPath());
577            stmt.setInt(5, relation.getType().getId());
578
579            if (LOG.isDebugEnabled()) {
580                LOG.debug(
581                    Messages.get().getBundle().key(
582                        Messages.LOG_CREATE_RELATION_2,
583                        String.valueOf(projectId),
584                        relation));
585            }
586            stmt.executeUpdate();
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, null);
593        }
594
595        if (relation.getType().getId() == CmsRelationType.LOCALE_VARIANT.getId()) {
596            try {
597
598                // Normalizes locale relations after creating a relation.
599                // After creating a locale variant relation from A to B, this statment
600                // removes all locale variant relations which are either
601                //       - from A to somewhere else than B,
602                //       - from B to some other resource
603                //       - to A from some other resources
604                conn = m_sqlManager.getConnection(dbc);
605                stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RELATIONS_NORMALIZE_LOCALE_RELATIONS");
606                stmt.setString(1, relation.getSourceId().toString());
607                stmt.setString(2, relation.getTargetId().toString());
608                stmt.setString(3, relation.getSourceId().toString());
609                stmt.setString(4, relation.getTargetId().toString());
610                if (LOG.isDebugEnabled()) {
611                    LOG.debug(
612                        Messages.get().getBundle().key(
613                            Messages.LOG_CREATE_RELATION_2,
614                            String.valueOf(projectId),
615                            relation));
616                }
617                stmt.executeUpdate();
618            } catch (SQLException e) {
619                throw new CmsDbSqlException(
620                    Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
621                    e);
622            } finally {
623                m_sqlManager.closeAll(dbc, conn, stmt, null);
624            }
625        }
626    }
627
628    /**
629     * @see org.opencms.db.I_CmsVfsDriver#createResource(org.opencms.db.CmsDbContext, CmsUUID, org.opencms.file.CmsResource, byte[])
630     */
631    public CmsResource createResource(CmsDbContext dbc, CmsUUID projectId, CmsResource resource, byte[] content)
632    throws CmsDataAccessException {
633
634        CmsUUID newStructureId = null;
635        Connection conn = null;
636        PreparedStatement stmt = null;
637
638        // check the resource path
639        String resourcePath = CmsFileUtil.removeTrailingSeparator(resource.getRootPath());
640        if (resourcePath.length() > CmsDriverManager.MAX_VFS_RESOURCE_PATH_LENGTH) {
641            throw new CmsDataAccessException(
642                Messages.get().container(
643                    Messages.ERR_RESOURCENAME_TOO_LONG_2,
644                    resourcePath,
645                    new Integer(CmsDriverManager.MAX_VFS_RESOURCE_PATH_LENGTH)));
646        }
647
648        // check if the parent folder of the resource exists and if is not deleted
649        if (!resource.getRootPath().equals("/")) {
650            String parentFolderName = CmsResource.getParentFolder(resource.getRootPath());
651            CmsFolder parentFolder = m_driverManager.getVfsDriver(dbc).readFolder(dbc, projectId, parentFolderName);
652            if (parentFolder.getState().isDeleted()) {
653                throw new CmsDbEntryNotFoundException(
654                    Messages.get().container(Messages.ERR_PARENT_FOLDER_DELETED_1, resource.getRootPath()));
655            }
656        }
657
658        // validate the resource length
659        internalValidateResourceLength(resource);
660
661        // set the resource state and modification dates
662        CmsResourceState newState;
663        long dateModified;
664        long dateCreated;
665        long dateContent = System.currentTimeMillis();
666
667        if (projectId.equals(CmsProject.ONLINE_PROJECT_ID)) {
668            newState = CmsResource.STATE_UNCHANGED;
669            dateCreated = resource.getDateCreated();
670            dateModified = resource.getDateLastModified();
671        } else {
672            newState = CmsResource.STATE_NEW;
673            if (resource.isTouched()) {
674                dateCreated = resource.getDateCreated();
675                dateModified = resource.getDateLastModified();
676            } else {
677                dateCreated = System.currentTimeMillis();
678                dateModified = dateCreated;
679            }
680        }
681
682        // check if the resource already exists
683        newStructureId = resource.getStructureId();
684
685        try {
686            CmsResource existingResource = m_driverManager.getVfsDriver(dbc).readResource(
687                dbc,
688                ((dbc.getProjectId() == null) || dbc.getProjectId().isNullUUID()) ? projectId : dbc.getProjectId(),
689                resourcePath,
690                true);
691            if (existingResource.getState().isDeleted()) {
692                // if an existing resource is deleted, it will be finally removed now.
693                // but we have to reuse its id in order to avoid orphans in the online project
694                newStructureId = existingResource.getStructureId();
695                newState = CmsResource.STATE_CHANGED;
696
697                // remove the existing file and it's properties
698                List<CmsResource> modifiedResources = m_driverManager.getVfsDriver(
699                    dbc).readSiblings(dbc, projectId, existingResource, false);
700                int propertyDeleteOption = (existingResource.getSiblingCount() > 1)
701                ? CmsProperty.DELETE_OPTION_DELETE_STRUCTURE_VALUES
702                : CmsProperty.DELETE_OPTION_DELETE_STRUCTURE_AND_RESOURCE_VALUES;
703                deletePropertyObjects(dbc, projectId, existingResource, propertyDeleteOption);
704                removeFile(dbc, projectId, existingResource);
705
706                OpenCms.fireCmsEvent(
707                    new CmsEvent(
708                        I_CmsEventListener.EVENT_RESOURCES_MODIFIED,
709                        Collections.<String, Object> singletonMap(
710                            I_CmsEventListener.KEY_RESOURCES,
711                            modifiedResources)));
712                OpenCms.fireCmsEvent(
713                    new CmsEvent(
714                        I_CmsEventListener.EVENT_RESOURCE_AND_PROPERTIES_MODIFIED,
715                        Collections.<String, Object> singletonMap(I_CmsEventListener.KEY_RESOURCE, existingResource)));
716            } else {
717                // we have a collision: there exists already a resource with the same path/name which cannot be removed
718                throw new CmsVfsResourceAlreadyExistsException(
719                    Messages.get().container(
720                        Messages.ERR_RESOURCE_WITH_NAME_ALREADY_EXISTS_1,
721                        dbc.removeSiteRoot(resource.getRootPath())));
722            }
723        } catch (CmsVfsResourceNotFoundException e) {
724            // that's what we want in the best case- anything else should be thrown
725        }
726
727        try {
728            // read the parent id
729            String parentId = internalReadParentId(dbc, projectId, resourcePath);
730
731            // use consistent version numbers if the file is being restored
732            int lastVersion = m_driverManager.getHistoryDriver(dbc).readLastVersion(dbc, newStructureId);
733            int newStrVersion = 0;
734            int newResVersion = 0;
735            if (lastVersion > 0) {
736                I_CmsHistoryResource histRes = m_driverManager.getHistoryDriver(dbc).readResource(
737                    dbc,
738                    newStructureId,
739                    lastVersion);
740                newStrVersion = histRes.getStructureVersion();
741                newResVersion = histRes.getResourceVersion();
742            }
743
744            // write the structure
745            conn = m_sqlManager.getConnection(dbc);
746            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_STRUCTURE_WRITE");
747            stmt.setString(1, newStructureId.toString());
748            stmt.setString(2, resource.getResourceId().toString());
749            stmt.setString(3, resourcePath);
750            stmt.setInt(4, newState.getState());
751            stmt.setLong(5, resource.getDateReleased());
752            stmt.setLong(6, resource.getDateExpired());
753            stmt.setString(7, parentId);
754            stmt.setInt(8, newStrVersion); // starting version number
755            stmt.executeUpdate();
756            m_sqlManager.closeAll(dbc, conn, stmt, null);
757
758            if (!validateResourceIdExists(dbc, projectId, resource.getResourceId())) {
759                try {
760                    // create the resource record
761                    conn = m_sqlManager.getConnection(dbc);
762                    stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RESOURCES_WRITE");
763                    stmt.setString(1, resource.getResourceId().toString());
764                    stmt.setInt(2, resource.getTypeId());
765                    stmt.setInt(3, resource.getFlags());
766                    stmt.setLong(4, dateCreated);
767                    stmt.setString(5, resource.getUserCreated().toString());
768                    stmt.setLong(6, dateModified);
769                    stmt.setString(7, resource.getUserLastModified().toString());
770                    stmt.setInt(8, newState.getState());
771                    stmt.setInt(9, resource.getLength());
772                    stmt.setLong(10, dateContent);
773                    stmt.setString(11, projectId.toString());
774                    stmt.setInt(12, 1); // sibling count
775                    stmt.setInt(13, newResVersion); // version number
776                    stmt.executeUpdate();
777                } finally {
778                    m_sqlManager.closeAll(dbc, conn, stmt, null);
779                }
780
781                if (resource.isFile() && (content != null)) {
782                    // create the file content
783                    createContent(dbc, projectId, resource.getResourceId(), content);
784                }
785            } else {
786                if ((content != null) || !resource.getState().isKeep()) {
787                    CmsUUID projLastMod = projectId;
788                    CmsResourceState state = CmsResource.STATE_CHANGED;
789                    if (projectId.equals(CmsProject.ONLINE_PROJECT_ID)) {
790                        // in case a sibling is being published
791                        projLastMod = resource.getProjectLastModified();
792                        state = CmsResource.STATE_UNCHANGED;
793                    }
794                    // update the resource record only if state has changed or new content is provided
795                    int sibCount = countSiblings(dbc, projectId, resource.getResourceId());
796                    conn = m_sqlManager.getConnection(dbc);
797                    stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RESOURCES_UPDATE_RESOURCES");
798                    stmt.setInt(1, resource.getTypeId());
799                    stmt.setInt(2, resource.getFlags());
800                    stmt.setLong(3, dateModified);
801                    stmt.setString(4, resource.getUserLastModified().toString());
802                    stmt.setInt(5, state.getState());
803                    stmt.setInt(6, resource.getLength());
804                    stmt.setLong(7, resource.getDateContent());
805                    stmt.setString(8, projLastMod.toString());
806                    stmt.setInt(9, sibCount);
807                    stmt.setString(10, resource.getResourceId().toString());
808                    stmt.executeUpdate();
809
810                    m_sqlManager.closeAll(dbc, conn, stmt, null);
811                }
812
813                if (resource.isFile()) {
814                    if (content != null) {
815                        // update the file content
816                        writeContent(dbc, resource.getResourceId(), content);
817                    } else if (resource.getState().isKeep()) {
818                        // special case sibling creation - update the link Count
819                        int sibCount = countSiblings(dbc, projectId, resource.getResourceId());
820                        conn = m_sqlManager.getConnection(dbc);
821                        stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RESOURCES_UPDATE_SIBLING_COUNT");
822                        stmt.setInt(1, sibCount);
823                        stmt.setString(2, resource.getResourceId().toString());
824                        stmt.executeUpdate();
825                        m_sqlManager.closeAll(dbc, null, stmt, null);
826
827                        // update the resource flags
828                        stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RESOURCES_UPDATE_FLAGS");
829                        stmt.setInt(1, resource.getFlags());
830                        stmt.setString(2, resource.getResourceId().toString());
831                        stmt.executeUpdate();
832                        m_sqlManager.closeAll(dbc, conn, stmt, null);
833                    }
834                }
835            }
836        } catch (SQLException e) {
837            throw new CmsDbSqlException(
838                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
839                e);
840        } finally {
841            m_sqlManager.closeAll(dbc, conn, stmt, null);
842        }
843        repairBrokenRelations(dbc, projectId, resource.getStructureId(), resource.getRootPath());
844        return readResource(dbc, projectId, newStructureId, false);
845    }
846
847    /**
848     * @see org.opencms.db.I_CmsVfsDriver#createResource(java.sql.ResultSet, CmsUUID)
849     */
850    public CmsResource createResource(ResultSet res, CmsUUID projectId) throws SQLException {
851
852        CmsUUID structureId = new CmsUUID(res.getString(m_sqlManager.readQuery("C_RESOURCES_STRUCTURE_ID")));
853        CmsUUID resourceId = new CmsUUID(res.getString(m_sqlManager.readQuery("C_RESOURCES_RESOURCE_ID")));
854        String resourcePath = res.getString(m_sqlManager.readQuery("C_RESOURCES_RESOURCE_PATH"));
855        int resourceType = res.getInt(m_sqlManager.readQuery("C_RESOURCES_RESOURCE_TYPE"));
856        int resourceFlags = res.getInt(m_sqlManager.readQuery("C_RESOURCES_RESOURCE_FLAGS"));
857        CmsUUID resourceProjectLastModified = new CmsUUID(
858            res.getString(m_sqlManager.readQuery("C_RESOURCES_PROJECT_LASTMODIFIED")));
859        int resourceState = res.getInt(m_sqlManager.readQuery("C_RESOURCES_STATE"));
860        int structureState = res.getInt(m_sqlManager.readQuery("C_RESOURCES_STRUCTURE_STATE"));
861        long dateCreated = res.getLong(m_sqlManager.readQuery("C_RESOURCES_DATE_CREATED"));
862        long dateLastModified = res.getLong(m_sqlManager.readQuery("C_RESOURCES_DATE_LASTMODIFIED"));
863        long dateReleased = res.getLong(m_sqlManager.readQuery("C_RESOURCES_DATE_RELEASED"));
864        long dateExpired = res.getLong(m_sqlManager.readQuery("C_RESOURCES_DATE_EXPIRED"));
865        int resourceSize = res.getInt(m_sqlManager.readQuery("C_RESOURCES_SIZE"));
866        boolean isFolder = CmsFolder.isFolderSize(resourceSize);
867        if (isFolder) {
868            // in case of folder type ensure, that the root path has a trailing slash
869            resourcePath = CmsFileUtil.addTrailingSeparator(resourcePath);
870        }
871        long dateContent = isFolder ? -1 : res.getLong(m_sqlManager.readQuery("C_RESOURCES_DATE_CONTENT"));
872        CmsUUID userCreated = new CmsUUID(res.getString(m_sqlManager.readQuery("C_RESOURCES_USER_CREATED")));
873        CmsUUID userLastModified = new CmsUUID(res.getString(m_sqlManager.readQuery("C_RESOURCES_USER_LASTMODIFIED")));
874        int siblingCount = res.getInt(m_sqlManager.readQuery("C_RESOURCES_SIBLING_COUNT"));
875        int resourceVersion = res.getInt(m_sqlManager.readQuery("C_RESOURCES_VERSION"));
876        int structureVersion = res.getInt(m_sqlManager.readQuery("C_RESOURCES_STRUCTURE_VERSION"));
877
878        int newState = (structureState > resourceState) ? structureState : resourceState;
879        // if there is a change increase the version number
880        int newVersion = resourceVersion + structureVersion + (newState > 0 ? 1 : 0);
881
882        CmsResource newResource = new CmsResource(
883            structureId,
884            resourceId,
885            resourcePath,
886            resourceType,
887            isFolder,
888            resourceFlags,
889            resourceProjectLastModified,
890            CmsResourceState.valueOf(newState),
891            dateCreated,
892            userCreated,
893            dateLastModified,
894            userLastModified,
895            dateReleased,
896            dateExpired,
897            siblingCount,
898            resourceSize,
899            dateContent,
900            newVersion);
901
902        return newResource;
903    }
904
905    /**
906     * @see org.opencms.db.I_CmsVfsDriver#createSibling(org.opencms.db.CmsDbContext, org.opencms.file.CmsProject, org.opencms.file.CmsResource)
907     */
908    public void createSibling(CmsDbContext dbc, CmsProject project, CmsResource resource)
909    throws CmsDataAccessException {
910
911        if (!project.getUuid().equals(CmsProject.ONLINE_PROJECT_ID)) {
912            // this method is only intended to be used during publishing
913            return;
914        }
915
916        // check if the resource already exists
917        CmsResource existingSibling = null;
918        CmsUUID newStructureId = resource.getStructureId();
919
920        Connection conn = null;
921        PreparedStatement stmt = null;
922        try {
923            existingSibling = readResource(dbc, project.getUuid(), resource.getRootPath(), true);
924
925            if (existingSibling.getState().isDeleted()) {
926                // if an existing resource is deleted, it will be finally removed now.
927                // but we have to reuse its id in order to avoid orphans in the online project.
928                newStructureId = existingSibling.getStructureId();
929
930                // remove the existing file and it's properties
931                List<CmsResource> modifiedResources = readSiblings(dbc, project.getUuid(), existingSibling, false);
932                int propertyDeleteOption = (existingSibling.getSiblingCount() > 1)
933                ? CmsProperty.DELETE_OPTION_DELETE_STRUCTURE_VALUES
934                : CmsProperty.DELETE_OPTION_DELETE_STRUCTURE_AND_RESOURCE_VALUES;
935                deletePropertyObjects(dbc, project.getUuid(), existingSibling, propertyDeleteOption);
936                removeFile(dbc, project.getUuid(), existingSibling);
937
938                OpenCms.fireCmsEvent(
939                    new CmsEvent(
940                        I_CmsEventListener.EVENT_RESOURCES_MODIFIED,
941                        Collections.<String, Object> singletonMap(
942                            I_CmsEventListener.KEY_RESOURCES,
943                            modifiedResources)));
944                OpenCms.fireCmsEvent(
945                    new CmsEvent(
946                        I_CmsEventListener.EVENT_RESOURCE_AND_PROPERTIES_MODIFIED,
947                        Collections.<String, Object> singletonMap(I_CmsEventListener.KEY_RESOURCE, existingSibling)));
948            } else {
949                // we have a collision: there exists already a resource with the same path/name which could not be removed
950                throw new CmsVfsResourceAlreadyExistsException(
951                    Messages.get().container(
952                        Messages.ERR_RESOURCE_WITH_NAME_ALREADY_EXISTS_1,
953                        dbc.removeSiteRoot(resource.getRootPath())));
954            }
955        } catch (CmsVfsResourceNotFoundException e) {
956            // that's what we want in the best case- anything else should be thrown
957        }
958
959        // check if a resource with the specified ID already exists
960        if (!validateResourceIdExists(dbc, project.getUuid(), resource.getResourceId())) {
961            throw new CmsVfsResourceNotFoundException(
962                Messages.get().container(
963                    Messages.ERR_CREATE_SIBLING_FILE_NOT_FOUND_1,
964                    dbc.removeSiteRoot(resource.getRootPath())));
965        }
966
967        // write a new structure referring to the resource
968        try {
969            // use consistent version numbers if the file is being restored
970            int lastVersion = m_driverManager.getHistoryDriver(dbc).readLastVersion(dbc, newStructureId);
971            int newStrVersion = 0;
972            if (lastVersion > 0) {
973                I_CmsHistoryResource histRes = m_driverManager.getHistoryDriver(dbc).readResource(
974                    dbc,
975                    newStructureId,
976                    lastVersion);
977                newStrVersion = histRes.getStructureVersion();
978            }
979
980            // read the parent id
981            String parentId = internalReadParentId(dbc, project.getUuid(), resource.getRootPath());
982
983            conn = m_sqlManager.getConnection(dbc);
984
985            // write the structure
986            stmt = m_sqlManager.getPreparedStatement(conn, project, "C_STRUCTURE_WRITE");
987            stmt.setString(1, newStructureId.toString());
988            stmt.setString(2, resource.getResourceId().toString());
989            stmt.setString(3, resource.getRootPath());
990            stmt.setInt(4, CmsResource.STATE_UNCHANGED.getState());
991            stmt.setLong(5, resource.getDateReleased());
992            stmt.setLong(6, resource.getDateExpired());
993            stmt.setString(7, parentId);
994            stmt.setInt(8, newStrVersion); // initial structure version number
995            stmt.executeUpdate();
996            m_sqlManager.closeAll(dbc, conn, stmt, null);
997
998            int sibCount = countSiblings(dbc, project.getUuid(), resource.getResourceId());
999            conn = m_sqlManager.getConnection(dbc);
1000
1001            // update the link Count
1002            stmt = m_sqlManager.getPreparedStatement(conn, project, "C_RESOURCES_UPDATE_SIBLING_COUNT");
1003            stmt.setInt(1, sibCount);
1004            stmt.setString(2, resource.getResourceId().toString());
1005            stmt.executeUpdate();
1006
1007            m_sqlManager.closeAll(dbc, null, stmt, null);
1008
1009            // update the project last modified and flags
1010            stmt = m_sqlManager.getPreparedStatement(conn, project, "C_RESOURCES_UPDATE_RESOURCE_PROJECT");
1011            stmt.setInt(1, resource.getFlags());
1012            stmt.setString(2, resource.getProjectLastModified().toString());
1013            stmt.setString(3, resource.getResourceId().toString());
1014            stmt.executeUpdate();
1015        } catch (SQLException e) {
1016            throw new CmsDbSqlException(
1017                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
1018                e);
1019        } finally {
1020            m_sqlManager.closeAll(dbc, conn, stmt, null);
1021        }
1022        repairBrokenRelations(dbc, project.getUuid(), resource.getStructureId(), resource.getRootPath());
1023    }
1024
1025    /**
1026     * @see org.opencms.db.I_CmsVfsDriver#deleteAliases(org.opencms.db.CmsDbContext, org.opencms.file.CmsProject, org.opencms.db.CmsAliasFilter)
1027     */
1028    public void deleteAliases(CmsDbContext dbc, CmsProject project, CmsAliasFilter filter)
1029    throws CmsDataAccessException {
1030
1031        Connection conn = null;
1032        PreparedStatement stmt = null;
1033        ResultSet res = null;
1034        if (filter.isNullFilter()) {
1035            throw new IllegalArgumentException("Trivial filter is not allowed for deleting aliases.");
1036        }
1037        try {
1038            conn = m_sqlManager.getConnection(dbc);
1039            CmsPair<String, List<String>> filterData = buildAliasConditions(filter);
1040            String sql = "DELETE FROM CMS_ALIASES WHERE " + filterData.getFirst();
1041            stmt = m_sqlManager.getPreparedStatementForSql(conn, sql);
1042            List<String> conditionParams = filterData.getSecond();
1043            for (int i = 0; i < conditionParams.size(); i++) {
1044                stmt.setString(1 + i, conditionParams.get(i));
1045            }
1046            stmt.executeUpdate();
1047        } catch (SQLException e) {
1048            throw new CmsDbSqlException(
1049                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
1050                e);
1051        } finally {
1052            m_sqlManager.closeAll(dbc, conn, stmt, res);
1053        }
1054    }
1055
1056    /**
1057     * @see org.opencms.db.I_CmsVfsDriver#deletePropertyDefinition(org.opencms.db.CmsDbContext, org.opencms.file.CmsPropertyDefinition)
1058     */
1059    public void deletePropertyDefinition(CmsDbContext dbc, CmsPropertyDefinition metadef)
1060    throws CmsDataAccessException {
1061
1062        Connection conn = null;
1063        PreparedStatement stmt = null;
1064
1065        try {
1066            if ((internalCountProperties(dbc, metadef, CmsProject.ONLINE_PROJECT_ID) != 0)
1067                || (internalCountProperties(dbc, metadef, CmsUUID.getOpenCmsUUID()) != 0)) { // HACK: to get an offline project
1068
1069                throw new CmsDataAccessException(
1070                    Messages.get().container(Messages.ERR_DELETE_USED_PROPERTY_1, metadef.getName()));
1071            }
1072
1073            conn = m_sqlManager.getConnection(dbc);
1074
1075            for (int i = 0; i < 2; i++) {
1076                if (i == 0) {
1077                    // delete the offline property definition
1078                    stmt = m_sqlManager.getPreparedStatement(conn, CmsUUID.getOpenCmsUUID(), "C_PROPERTYDEF_DELETE"); // HACK: to get an offline project
1079                } else {
1080                    // delete the online property definition
1081                    stmt = m_sqlManager.getPreparedStatement(
1082                        conn,
1083                        CmsProject.ONLINE_PROJECT_ID,
1084                        "C_PROPERTYDEF_DELETE");
1085                }
1086
1087                stmt.setString(1, metadef.getId().toString());
1088                stmt.executeUpdate();
1089                m_sqlManager.closeAll(dbc, null, stmt, null);
1090            }
1091        } catch (SQLException e) {
1092            throw new CmsDbSqlException(
1093                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
1094                e);
1095        } finally {
1096            m_sqlManager.closeAll(dbc, conn, stmt, null);
1097        }
1098    }
1099
1100    /**
1101     * @see org.opencms.db.I_CmsVfsDriver#deletePropertyObjects(org.opencms.db.CmsDbContext, CmsUUID, org.opencms.file.CmsResource, int)
1102     */
1103    public void deletePropertyObjects(CmsDbContext dbc, CmsUUID projectId, CmsResource resource, int deleteOption)
1104    throws CmsDataAccessException {
1105
1106        Connection conn = null;
1107        PreparedStatement stmt = null;
1108
1109        try {
1110            conn = m_sqlManager.getConnection(dbc);
1111
1112            if (deleteOption == CmsProperty.DELETE_OPTION_DELETE_STRUCTURE_AND_RESOURCE_VALUES) {
1113                // delete both the structure and resource property values mapped to the specified resource
1114                stmt = m_sqlManager.getPreparedStatement(
1115                    conn,
1116                    projectId,
1117                    "C_PROPERTIES_DELETE_ALL_STRUCTURE_AND_RESOURCE_VALUES");
1118                stmt.setString(1, resource.getResourceId().toString());
1119                stmt.setInt(2, CmsProperty.RESOURCE_RECORD_MAPPING);
1120                stmt.setString(3, resource.getStructureId().toString());
1121                stmt.setInt(4, CmsProperty.STRUCTURE_RECORD_MAPPING);
1122            } else if (deleteOption == CmsProperty.DELETE_OPTION_DELETE_STRUCTURE_VALUES) {
1123                // delete the structure values mapped to the specified resource
1124                stmt = m_sqlManager.getPreparedStatement(
1125                    conn,
1126                    projectId,
1127                    "C_PROPERTIES_DELETE_ALL_VALUES_FOR_MAPPING_TYPE");
1128                stmt.setString(1, resource.getStructureId().toString());
1129                stmt.setInt(2, CmsProperty.STRUCTURE_RECORD_MAPPING);
1130            } else if (deleteOption == CmsProperty.DELETE_OPTION_DELETE_RESOURCE_VALUES) {
1131                // delete the resource property values mapped to the specified resource
1132                stmt = m_sqlManager.getPreparedStatement(
1133                    conn,
1134                    projectId,
1135                    "C_PROPERTIES_DELETE_ALL_VALUES_FOR_MAPPING_TYPE");
1136                stmt.setString(1, resource.getResourceId().toString());
1137                stmt.setInt(2, CmsProperty.RESOURCE_RECORD_MAPPING);
1138            } else {
1139                throw new CmsDataAccessException(Messages.get().container(Messages.ERR_INVALID_DELETE_OPTION_1));
1140            }
1141
1142            stmt.executeUpdate();
1143        } catch (SQLException e) {
1144            throw new CmsDbSqlException(
1145                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
1146                e);
1147        } finally {
1148            m_sqlManager.closeAll(dbc, conn, stmt, null);
1149        }
1150    }
1151
1152    /**
1153     * @see org.opencms.db.I_CmsVfsDriver#deleteRelations(org.opencms.db.CmsDbContext, CmsUUID, CmsResource, org.opencms.relations.CmsRelationFilter)
1154     */
1155    public void deleteRelations(CmsDbContext dbc, CmsUUID projectId, CmsResource resource, CmsRelationFilter filter)
1156    throws CmsDataAccessException {
1157
1158        Connection conn = null;
1159        PreparedStatement stmt = null;
1160
1161        try {
1162            conn = m_sqlManager.getConnection(dbc);
1163
1164            if (filter.isSource()) {
1165                List<Object> params = new ArrayList<Object>(7);
1166
1167                StringBuffer queryBuf = new StringBuffer(256);
1168                queryBuf.append(m_sqlManager.readQuery(projectId, "C_DELETE_RELATIONS"));
1169                queryBuf.append(prepareRelationConditions(projectId, filter, resource, params, true));
1170
1171                stmt = m_sqlManager.getPreparedStatementForSql(conn, queryBuf.toString());
1172                for (int i = 0; i < params.size(); i++) {
1173                    if (params.get(i) instanceof Integer) {
1174                        stmt.setInt(i + 1, ((Integer)params.get(i)).intValue());
1175                    } else {
1176                        stmt.setString(i + 1, (String)params.get(i));
1177                    }
1178                }
1179                stmt.executeUpdate();
1180                m_sqlManager.closeAll(dbc, null, stmt, null);
1181            }
1182            if (filter.isTarget()) {
1183                List<Object> params = new ArrayList<Object>(7);
1184
1185                StringBuffer queryBuf = new StringBuffer(256);
1186                queryBuf.append(m_sqlManager.readQuery(projectId, "C_DELETE_RELATIONS"));
1187                queryBuf.append(prepareRelationConditions(projectId, filter, resource, params, false));
1188
1189                stmt = m_sqlManager.getPreparedStatementForSql(conn, queryBuf.toString());
1190                for (int i = 0; i < params.size(); i++) {
1191                    if (params.get(i) instanceof Integer) {
1192                        stmt.setInt(i + 1, ((Integer)params.get(i)).intValue());
1193                    } else {
1194                        stmt.setString(i + 1, (String)params.get(i));
1195                    }
1196                }
1197                stmt.executeUpdate();
1198                m_sqlManager.closeAll(dbc, null, stmt, null);
1199            }
1200        } catch (SQLException e) {
1201            throw new CmsDbSqlException(
1202                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
1203                e);
1204        } finally {
1205            m_sqlManager.closeAll(dbc, conn, stmt, null);
1206        }
1207        // update broken remaining relations
1208        updateBrokenRelations(dbc, projectId, resource.getRootPath());
1209    }
1210
1211    /**
1212     * @see org.opencms.db.I_CmsVfsDriver#deleteRewriteAliases(org.opencms.db.CmsDbContext, org.opencms.db.CmsRewriteAliasFilter)
1213     */
1214    public void deleteRewriteAliases(CmsDbContext dbc, CmsRewriteAliasFilter filter) throws CmsDataAccessException {
1215
1216        Connection conn = null;
1217        PreparedStatement stmt = null;
1218        try {
1219            conn = m_sqlManager.getConnection(dbc);
1220            CmsPair<String, List<Object>> conditionAndParams = prepareRewriteAliasConditions(filter);
1221            String condition = conditionAndParams.getFirst();
1222            List<Object> params = conditionAndParams.getSecond();
1223            String query = "DELETE FROM CMS_REWRITES WHERE " + condition;
1224            stmt = m_sqlManager.getPreparedStatementForSql(conn, query);
1225            CmsDbUtil.fillParameters(stmt, params);
1226            stmt.execute();
1227        } catch (SQLException e) {
1228            throw wrapException(stmt, e);
1229        } finally {
1230            m_sqlManager.closeAll(dbc, conn, stmt, null);
1231        }
1232    }
1233
1234    /**
1235     * @see org.opencms.db.I_CmsVfsDriver#deleteUrlNameMappingEntries(org.opencms.db.CmsDbContext, boolean, org.opencms.db.urlname.CmsUrlNameMappingFilter)
1236     */
1237    public void deleteUrlNameMappingEntries(CmsDbContext dbc, boolean online, CmsUrlNameMappingFilter filter)
1238    throws CmsDataAccessException {
1239
1240        Connection conn = null;
1241        PreparedStatement stmt = null;
1242        try {
1243            conn = m_sqlManager.getConnection(dbc);
1244            String query = m_sqlManager.readQuery("C_DELETE_URLNAME_MAPPINGS");
1245            query = replaceProject(query, online);
1246            stmt = getPreparedStatementForFilter(conn, query, filter);
1247            stmt.executeUpdate();
1248        } catch (SQLException e) {
1249            throw wrapException(stmt, e);
1250        } finally {
1251            m_sqlManager.closeAll(dbc, conn, stmt, null);
1252        }
1253    }
1254
1255    /**
1256     * @see org.opencms.db.I_CmsVfsDriver#destroy()
1257     */
1258    public void destroy() throws Throwable {
1259
1260        m_sqlManager = null;
1261        m_driverManager = null;
1262
1263        if (CmsLog.INIT.isInfoEnabled()) {
1264            CmsLog.INIT.info(Messages.get().getBundle().key(Messages.INIT_SHUTDOWN_DRIVER_1, getClass().getName()));
1265        }
1266    }
1267
1268    /**
1269     * Returns all organizational units for the given resource.<p>
1270     *
1271     * @param dbc the database context
1272     * @param projectId the id of the project
1273     * @param resource the resource
1274     *
1275     * @return a list of {@link org.opencms.security.CmsOrganizationalUnit} objects
1276     *
1277     * @throws CmsDbSqlException if something goes wrong
1278     */
1279    public List<CmsOrganizationalUnit> getResourceOus(CmsDbContext dbc, CmsUUID projectId, CmsResource resource)
1280    throws CmsDbSqlException {
1281
1282        List<CmsOrganizationalUnit> ous = new ArrayList<CmsOrganizationalUnit>();
1283        String resName = resource.getRootPath();
1284        if (resource.isFolder() && !resName.endsWith("/")) {
1285            resName += "/";
1286        }
1287
1288        Connection conn = null;
1289        PreparedStatement stmt = null;
1290        ResultSet res = null;
1291        List<CmsRelation> rels = new ArrayList<CmsRelation>();
1292
1293        try {
1294            conn = m_sqlManager.getConnection(dbc);
1295            stmt = m_sqlManager.getPreparedStatementForSql(
1296                conn,
1297                m_sqlManager.readQuery(projectId, "C_READ_RESOURCE_OUS"));
1298            stmt.setInt(1, CmsRelationType.OU_RESOURCE.getId());
1299            stmt.setString(2, resName);
1300            res = stmt.executeQuery();
1301            while (res.next()) {
1302                rels.add(internalReadRelation(res));
1303            }
1304        } catch (SQLException e) {
1305            throw new CmsDbSqlException(
1306                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
1307                e);
1308        } finally {
1309            m_sqlManager.closeAll(dbc, conn, stmt, res);
1310        }
1311
1312        for (CmsRelation rel : rels) {
1313            try {
1314                ous.add(
1315                    m_driverManager.readOrganizationalUnit(
1316                        dbc,
1317                        rel.getSourcePath().substring(CmsUserDriver.ORGUNIT_BASE_FOLDER.length())));
1318            } catch (CmsException e) {
1319                // should never happen
1320                if (LOG.isErrorEnabled()) {
1321                    LOG.error(e.getLocalizedMessage(), e);
1322                }
1323            }
1324        }
1325        return ous;
1326    }
1327
1328    /**
1329     * @see org.opencms.db.I_CmsVfsDriver#getSqlManager()
1330     */
1331    public CmsSqlManager getSqlManager() {
1332
1333        return m_sqlManager;
1334    }
1335
1336    /**
1337     * @see org.opencms.db.I_CmsVfsDriver#incrementCounter(org.opencms.db.CmsDbContext, java.lang.String)
1338     */
1339    public int incrementCounter(CmsDbContext dbc, String name) throws CmsDataAccessException {
1340
1341        Integer counterObj = internalReadCounter(dbc, name);
1342        int result;
1343        if (counterObj == null) {
1344            internalCreateCounter(dbc, name, 1);
1345            result = 0;
1346        } else {
1347            result = counterObj.intValue();
1348            internalIncrementCounter(dbc, name);
1349        }
1350        return result;
1351    }
1352
1353    /**
1354     * @see org.opencms.db.I_CmsDriver#init(org.opencms.db.CmsDbContext, org.opencms.configuration.CmsConfigurationManager, java.util.List, org.opencms.db.CmsDriverManager)
1355     */
1356    public void init(
1357        CmsDbContext dbc,
1358        CmsConfigurationManager configurationManager,
1359        List<String> successiveDrivers,
1360        CmsDriverManager driverManager) {
1361
1362        CmsParameterConfiguration configuration = configurationManager.getConfiguration();
1363        String poolUrl = configuration.get("db.vfs.pool");
1364        String classname = configuration.get("db.vfs.sqlmanager");
1365        m_sqlManager = initSqlManager(classname);
1366        m_sqlManager.init(I_CmsVfsDriver.DRIVER_TYPE_ID, poolUrl);
1367
1368        m_driverManager = driverManager;
1369
1370        if (CmsLog.INIT.isInfoEnabled()) {
1371            CmsLog.INIT.info(Messages.get().getBundle().key(Messages.INIT_ASSIGNED_POOL_1, poolUrl));
1372        }
1373
1374        if ((successiveDrivers != null) && !successiveDrivers.isEmpty()) {
1375            if (LOG.isWarnEnabled()) {
1376                LOG.warn(
1377                    Messages.get().getBundle().key(
1378                        Messages.LOG_SUCCESSIVE_DRIVERS_UNSUPPORTED_1,
1379                        getClass().getName()));
1380            }
1381        }
1382    }
1383
1384    /**
1385     * @see org.opencms.db.I_CmsVfsDriver#initSqlManager(String)
1386     */
1387    public org.opencms.db.generic.CmsSqlManager initSqlManager(String classname) {
1388
1389        return CmsSqlManager.getInstance(classname);
1390    }
1391
1392    /**
1393     * @see org.opencms.db.I_CmsVfsDriver#insertAlias(org.opencms.db.CmsDbContext, org.opencms.file.CmsProject, org.opencms.db.CmsAlias)
1394     */
1395    public void insertAlias(CmsDbContext dbc, CmsProject project, CmsAlias alias) throws CmsDataAccessException {
1396
1397        Connection conn = null;
1398        PreparedStatement stmt = null;
1399        ResultSet res = null;
1400        try {
1401            conn = m_sqlManager.getConnection(dbc);
1402            stmt = m_sqlManager.getPreparedStatement(conn, project, "C_ALIAS_ADD_4");
1403            stmt.setString(1, alias.getSiteRoot());
1404            stmt.setString(2, alias.getAliasPath());
1405            stmt.setInt(3, alias.getMode().toInt());
1406            stmt.setString(4, alias.getStructureId().toString());
1407            stmt.executeUpdate();
1408
1409        } catch (SQLException e) {
1410            throw new CmsDbSqlException(
1411                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
1412                e);
1413        } finally {
1414            m_sqlManager.closeAll(dbc, conn, stmt, res);
1415        }
1416    }
1417
1418    /**
1419     * @see org.opencms.db.I_CmsVfsDriver#insertRewriteAliases(org.opencms.db.CmsDbContext, java.util.Collection)
1420     */
1421    public void insertRewriteAliases(CmsDbContext dbc, Collection<CmsRewriteAlias> rewriteAliases)
1422    throws CmsDataAccessException {
1423
1424        Connection conn = null;
1425        PreparedStatement stmt = null;
1426        ResultSet res = null;
1427        if (!rewriteAliases.isEmpty()) {
1428            try {
1429                conn = m_sqlManager.getConnection(dbc);
1430                stmt = m_sqlManager.getPreparedStatement(conn, dbc.currentProject(), "C_REWRITE_ALIAS_INSERT_5");
1431                for (CmsRewriteAlias alias : rewriteAliases) {
1432                    stmt.setString(1, alias.getId().toString());
1433                    stmt.setString(2, alias.getSiteRoot());
1434                    stmt.setString(3, alias.getPatternString());
1435                    stmt.setString(4, alias.getReplacementString());
1436                    stmt.setInt(5, alias.getMode().toInt());
1437                    stmt.addBatch();
1438                }
1439                stmt.executeBatch();
1440            } catch (SQLException e) {
1441                throw new CmsDbSqlException(
1442                    Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
1443                    e);
1444            } finally {
1445                m_sqlManager.closeAll(dbc, conn, stmt, res);
1446            }
1447        }
1448    }
1449
1450    /**
1451     * @see org.opencms.db.I_CmsVfsDriver#moveResource(CmsDbContext, CmsUUID, CmsResource, String)
1452     */
1453    public void moveResource(CmsDbContext dbc, CmsUUID projectId, CmsResource source, String destinationPath)
1454    throws CmsDataAccessException {
1455
1456        if ((dbc.getRequestContext() != null)
1457            && (dbc.getRequestContext().getAttribute(REQ_ATTR_CHECK_PERMISSIONS) != null)) {
1458            // only check write permissions
1459            checkWritePermissionsInFolder(dbc, source);
1460            return;
1461        }
1462
1463        // determine destination folder
1464        String destinationFoldername = CmsResource.getParentFolder(destinationPath);
1465
1466        // read the destination folder (will also check read permissions)
1467        CmsFolder destinationFolder = m_driverManager.readFolder(dbc, destinationFoldername, CmsResourceFilter.ALL);
1468
1469        if (!projectId.equals(CmsProject.ONLINE_PROJECT_ID)) {
1470            // check online resource
1471            try {
1472                CmsResource onlineResource = m_driverManager.getVfsDriver(
1473                    dbc).readResource(dbc, CmsProject.ONLINE_PROJECT_ID, destinationPath, true);
1474
1475                if (!onlineResource.getStructureId().equals(source.getStructureId())) {
1476                    // source resource has been moved and it is not the
1477                    // same as the resource that is being trying to move back
1478                    CmsResource offlineResource = null;
1479                    try {
1480                        // read new location in offline project
1481                        offlineResource = readResource(
1482                            dbc,
1483                            dbc.getRequestContext().getCurrentProject().getUuid(),
1484                            onlineResource.getStructureId(),
1485                            true);
1486                    } catch (CmsException e) {
1487                        // should never happen
1488                        if (LOG.isErrorEnabled()) {
1489                            LOG.error(e.getMessage(), e);
1490                        }
1491                    }
1492
1493                    throw new CmsVfsOnlineResourceAlreadyExistsException(
1494                        Messages.get().container(
1495                            Messages.ERR_OVERWRITE_MOVED_RESOURCE_3,
1496                            dbc.removeSiteRoot(source.getRootPath()),
1497                            dbc.removeSiteRoot(destinationPath),
1498                            dbc.removeSiteRoot(offlineResource == null ? "__ERROR__" : offlineResource.getRootPath())));
1499                }
1500            } catch (CmsVfsResourceNotFoundException e) {
1501                // ok, no online resource
1502            }
1503        }
1504
1505        Connection conn = null;
1506        PreparedStatement stmt = null;
1507        ResultSet res = null;
1508
1509        try {
1510            conn = m_sqlManager.getConnection(dbc);
1511
1512            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RESOURCES_MOVE");
1513            stmt.setString(1, CmsFileUtil.removeTrailingSeparator(destinationPath)); // must remove trailing slash
1514            stmt.setString(2, destinationFolder.getStructureId().toString());
1515            stmt.setString(3, source.getStructureId().toString());
1516            stmt.executeUpdate();
1517        } catch (SQLException e) {
1518            throw new CmsDbSqlException(
1519                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
1520                e);
1521        } finally {
1522            m_sqlManager.closeAll(dbc, conn, stmt, res);
1523        }
1524
1525        moveRelations(dbc, projectId, source.getStructureId(), destinationPath);
1526        repairBrokenRelations(dbc, projectId, source.getStructureId(), destinationPath);
1527        // repair project resources
1528        if (!projectId.equals(CmsProject.ONLINE_PROJECT_ID) && (dbc.getRequestContext() != null)) {
1529            String deletedResourceRootPath = source.getRootPath();
1530            dbc.getRequestContext().setAttribute(CmsProjectDriver.DBC_ATTR_READ_PROJECT_FOR_RESOURCE, Boolean.TRUE);
1531            I_CmsProjectDriver projectDriver = m_driverManager.getProjectDriver(dbc);
1532            Iterator<CmsProject> itProjects = projectDriver.readProjects(dbc, deletedResourceRootPath).iterator();
1533            while (itProjects.hasNext()) {
1534                CmsProject project = itProjects.next();
1535                projectDriver.deleteProjectResource(dbc, project.getUuid(), deletedResourceRootPath);
1536                projectDriver.createProjectResource(dbc, project.getUuid(), destinationPath);
1537            }
1538        }
1539    }
1540
1541    /**
1542     * @see org.opencms.db.I_CmsVfsDriver#publishResource(org.opencms.db.CmsDbContext, org.opencms.file.CmsProject, org.opencms.file.CmsResource, org.opencms.file.CmsResource)
1543     */
1544    public void publishResource(
1545        CmsDbContext dbc,
1546        CmsProject onlineProject,
1547        CmsResource onlineResource,
1548        CmsResource offlineResource)
1549    throws CmsDataAccessException {
1550
1551        Connection conn = null;
1552        PreparedStatement stmt = null;
1553
1554        // validate the resource length
1555        internalValidateResourceLength(offlineResource);
1556        int resourceSize = offlineResource.getLength();
1557
1558        String resourcePath = CmsFileUtil.removeTrailingSeparator(offlineResource.getRootPath());
1559
1560        try {
1561            int sibCount = countSiblings(dbc, onlineProject.getUuid(), onlineResource.getResourceId());
1562            boolean resourceExists = validateResourceIdExists(
1563                dbc,
1564                onlineProject.getUuid(),
1565                offlineResource.getResourceId());
1566            conn = m_sqlManager.getConnection(dbc);
1567            if (resourceExists) {
1568                // the resource record exists online already
1569                // update the online resource record
1570                stmt = m_sqlManager.getPreparedStatement(conn, onlineProject, "C_RESOURCES_UPDATE_RESOURCES");
1571                stmt.setInt(1, offlineResource.getTypeId());
1572                stmt.setInt(2, offlineResource.getFlags());
1573                stmt.setLong(3, offlineResource.getDateLastModified());
1574                stmt.setString(4, offlineResource.getUserLastModified().toString());
1575                stmt.setInt(5, CmsResource.STATE_UNCHANGED.getState());
1576                stmt.setInt(6, resourceSize);
1577                stmt.setLong(7, offlineResource.getDateContent());
1578                stmt.setString(8, offlineResource.getProjectLastModified().toString());
1579                stmt.setInt(9, sibCount);
1580                stmt.setString(10, offlineResource.getResourceId().toString());
1581                stmt.executeUpdate();
1582                m_sqlManager.closeAll(dbc, conn, stmt, null);
1583            } else {
1584                // the resource record does NOT exist online yet
1585                // create the resource record online
1586                stmt = m_sqlManager.getPreparedStatement(conn, onlineProject, "C_RESOURCES_WRITE");
1587                stmt.setString(1, offlineResource.getResourceId().toString());
1588                stmt.setInt(2, offlineResource.getTypeId());
1589                stmt.setInt(3, offlineResource.getFlags());
1590                stmt.setLong(4, offlineResource.getDateCreated());
1591                stmt.setString(5, offlineResource.getUserCreated().toString());
1592                stmt.setLong(6, offlineResource.getDateLastModified());
1593                stmt.setString(7, offlineResource.getUserLastModified().toString());
1594                stmt.setInt(8, CmsResource.STATE_UNCHANGED.getState());
1595                stmt.setInt(9, resourceSize);
1596                stmt.setLong(10, offlineResource.getDateContent());
1597                stmt.setString(11, offlineResource.getProjectLastModified().toString());
1598                stmt.setInt(12, 1); // initial siblings count
1599                stmt.setInt(13, 1); // initial resource version
1600                stmt.executeUpdate();
1601                m_sqlManager.closeAll(dbc, conn, stmt, null);
1602            }
1603
1604            // read the parent id
1605            String parentId = internalReadParentId(dbc, onlineProject.getUuid(), resourcePath);
1606            boolean structureExists = validateStructureIdExists(
1607                dbc,
1608                onlineProject.getUuid(),
1609                offlineResource.getStructureId());
1610            conn = m_sqlManager.getConnection(dbc);
1611            if (structureExists) {
1612                // update the online structure record
1613                stmt = m_sqlManager.getPreparedStatement(conn, onlineProject, "C_RESOURCES_UPDATE_STRUCTURE");
1614                stmt.setString(1, offlineResource.getResourceId().toString());
1615                stmt.setString(2, resourcePath);
1616                stmt.setInt(3, CmsResource.STATE_UNCHANGED.getState());
1617                stmt.setLong(4, offlineResource.getDateReleased());
1618                stmt.setLong(5, offlineResource.getDateExpired());
1619                stmt.setString(6, parentId);
1620                stmt.setString(7, offlineResource.getStructureId().toString());
1621                stmt.executeUpdate();
1622                m_sqlManager.closeAll(dbc, null, stmt, null);
1623            } else {
1624                // create the structure record online
1625                stmt = m_sqlManager.getPreparedStatement(conn, onlineProject, "C_STRUCTURE_WRITE");
1626                stmt.setString(1, offlineResource.getStructureId().toString());
1627                stmt.setString(2, offlineResource.getResourceId().toString());
1628                stmt.setString(3, resourcePath);
1629                stmt.setInt(4, CmsResource.STATE_UNCHANGED.getState());
1630                stmt.setLong(5, offlineResource.getDateReleased());
1631                stmt.setLong(6, offlineResource.getDateExpired());
1632                stmt.setString(7, parentId);
1633                stmt.setInt(8, resourceExists ? 1 : 0); // new resources start with 0, new siblings with 1
1634                stmt.executeUpdate();
1635                m_sqlManager.closeAll(dbc, null, stmt, null);
1636            }
1637        } catch (SQLException e) {
1638            throw new CmsDbSqlException(
1639                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
1640                e);
1641        } finally {
1642            m_sqlManager.closeAll(dbc, conn, stmt, null);
1643        }
1644
1645        try (Connection conn2 = m_sqlManager.getConnection(dbc)) {
1646            stmt = m_sqlManager.getPreparedStatement(conn2, onlineProject, "C_RELATIONS_REPAIR_BROKEN");
1647            stmt.setString(1, offlineResource.getStructureId().toString());
1648            stmt.setString(2, offlineResource.getRootPath());
1649            stmt.executeUpdate();
1650        } catch (SQLException e) {
1651            throw new CmsDbSqlException(
1652                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
1653                e);
1654        }
1655    }
1656
1657    /**
1658     * @see org.opencms.db.I_CmsVfsDriver#publishVersions(org.opencms.db.CmsDbContext, org.opencms.file.CmsResource, boolean)
1659     */
1660    public void publishVersions(CmsDbContext dbc, CmsResource resource, boolean firstSibling)
1661    throws CmsDataAccessException {
1662
1663        // if resource is null just flush the internal cache
1664        if (resource == null) {
1665            m_resOp.clear();
1666            return;
1667        }
1668
1669        if (!dbc.getProjectId().isNullUUID() || dbc.currentProject().isOnlineProject()) {
1670            // this method is supposed to be used only in the offline project
1671            return;
1672        }
1673
1674        if (firstSibling) {
1675            // reset the resource operation flag
1676            m_resOp.remove(resource.getResourceId());
1677        }
1678
1679        boolean resOp = false; // assume structure operation
1680
1681        CmsResourceState resState = internalReadResourceState(dbc, dbc.currentProject().getUuid(), resource);
1682        CmsResourceState strState = internalReadStructureState(dbc, dbc.currentProject().getUuid(), resource);
1683
1684        if (!resState.isUnchanged()) {
1685            if (strState.isDeleted()) {
1686                resOp = (resState.isDeleted()
1687                    || (resource.getSiblingCount() == 1)
1688                    || (countSiblings(dbc, dbc.currentProject().getUuid(), resource.getResourceId()) == 1));
1689            } else {
1690                resOp = true;
1691            }
1692        }
1693
1694        if (!firstSibling) {
1695            if (resOp) {
1696                return;
1697            }
1698            if (m_resOp.contains(resource.getResourceId())) {
1699                return;
1700            }
1701        }
1702
1703        // read the offline version numbers
1704        Map<String, Integer> versions = readVersions(
1705            dbc,
1706            dbc.currentProject().getUuid(),
1707            resource.getResourceId(),
1708            resource.getStructureId());
1709        int strVersion = versions.get("structure").intValue();
1710        int resVersion = versions.get("resource").intValue();
1711
1712        if (resOp) {
1713            if (resource.getSiblingCount() > 1) {
1714                m_resOp.add(resource.getResourceId());
1715            }
1716            resVersion++;
1717        }
1718        if (!resOp) {
1719            strVersion++;
1720        }
1721
1722        Connection conn = null;
1723        PreparedStatement stmt = null;
1724        ResultSet res = null;
1725
1726        try {
1727            conn = m_sqlManager.getConnection(dbc);
1728
1729            if (resOp) {
1730                // update the resource version
1731                stmt = m_sqlManager.getPreparedStatement(
1732                    conn,
1733                    CmsProject.ONLINE_PROJECT_ID,
1734                    "C_RESOURCES_UPDATE_RESOURCE_VERSION");
1735                stmt.setInt(1, resVersion);
1736                stmt.setString(2, resource.getResourceId().toString());
1737                stmt.executeUpdate();
1738                m_sqlManager.closeAll(dbc, null, stmt, null);
1739            }
1740            if (!resOp || strState.isNew()) {
1741                // update the structure version
1742                stmt = m_sqlManager.getPreparedStatement(
1743                    conn,
1744                    CmsProject.ONLINE_PROJECT_ID,
1745                    "C_RESOURCES_UPDATE_STRUCTURE_VERSION");
1746                stmt.setInt(1, strVersion);
1747                stmt.setString(2, resource.getStructureId().toString());
1748                stmt.executeUpdate();
1749                m_sqlManager.closeAll(dbc, null, stmt, null);
1750            }
1751        } catch (SQLException e) {
1752            throw new CmsDbSqlException(
1753                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
1754                e);
1755        } finally {
1756            m_sqlManager.closeAll(dbc, conn, stmt, res);
1757        }
1758    }
1759
1760    /**
1761     * @see org.opencms.db.I_CmsVfsDriver#readAliases(org.opencms.db.CmsDbContext, org.opencms.file.CmsProject, org.opencms.db.CmsAliasFilter)
1762     */
1763    public List<CmsAlias> readAliases(CmsDbContext dbc, CmsProject project, CmsAliasFilter filter)
1764    throws CmsDataAccessException {
1765
1766        Connection conn = null;
1767        PreparedStatement stmt = null;
1768        ResultSet res = null;
1769        try {
1770            conn = m_sqlManager.getConnection(dbc);
1771            CmsPair<String, List<String>> conditionPair = buildAliasConditions(filter);
1772            String conditionString = conditionPair.getFirst();
1773            List<String> conditionParams = conditionPair.getSecond();
1774            String sql = "SELECT site_root, path, alias_mode, structure_id FROM CMS_ALIASES WHERE " + conditionString;
1775            stmt = m_sqlManager.getPreparedStatementForSql(conn, sql);
1776            for (int i = 0; i < conditionParams.size(); i++) {
1777                stmt.setString(1 + i, conditionParams.get(i));
1778            }
1779            res = stmt.executeQuery();
1780            List<CmsAlias> result = new ArrayList<CmsAlias>();
1781            while (res.next()) {
1782                CmsAlias alias = internalReadAlias(res);
1783                result.add(alias);
1784            }
1785            return result;
1786        } catch (SQLException e) {
1787            throw new CmsDbSqlException(
1788                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
1789                e);
1790        } finally {
1791            m_sqlManager.closeAll(dbc, conn, stmt, res);
1792        }
1793
1794    }
1795
1796    /**
1797     * @see org.opencms.db.I_CmsVfsDriver#readChildResources(org.opencms.db.CmsDbContext, org.opencms.file.CmsProject, org.opencms.file.CmsResource, boolean, boolean)
1798     */
1799    public List<CmsResource> readChildResources(
1800        CmsDbContext dbc,
1801        CmsProject currentProject,
1802        CmsResource resource,
1803        boolean getFolders,
1804        boolean getFiles)
1805    throws CmsDataAccessException {
1806
1807        List<CmsResource> result = new ArrayList<CmsResource>();
1808        CmsUUID projectId = currentProject.getUuid();
1809
1810        String resourceTypeClause;
1811        if (getFolders && getFiles) {
1812            resourceTypeClause = null;
1813        } else if (getFolders) {
1814            resourceTypeClause = m_sqlManager.readQuery(projectId, "C_RESOURCES_GET_SUBRESOURCES_GET_FOLDERS");
1815        } else {
1816            resourceTypeClause = m_sqlManager.readQuery(projectId, "C_RESOURCES_GET_SUBRESOURCES_GET_FILES");
1817        }
1818        StringBuffer query = new StringBuffer();
1819        query.append(m_sqlManager.readQuery(projectId, "C_RESOURCES_GET_SUBRESOURCES"));
1820        if (resourceTypeClause != null) {
1821            query.append(' ');
1822            query.append(resourceTypeClause);
1823        }
1824
1825        String sizeColumn = m_sqlManager.readQuery("C_RESOURCES_SIZE");
1826
1827        Connection conn = null;
1828        PreparedStatement stmt = null;
1829        ResultSet res = null;
1830        try {
1831            conn = m_sqlManager.getConnection(dbc);
1832            stmt = m_sqlManager.getPreparedStatementForSql(conn, query.toString());
1833            stmt.setString(1, resource.getStructureId().toString());
1834            res = stmt.executeQuery();
1835
1836            while (res.next()) {
1837                long size = res.getInt(sizeColumn);
1838                if (CmsFolder.isFolderSize(size)) {
1839                    result.add(createFolder(res, projectId, false));
1840                } else {
1841                    result.add(createFile(res, projectId, false));
1842                }
1843            }
1844        } catch (SQLException e) {
1845            throw new CmsDbSqlException(
1846                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
1847                e);
1848        } finally {
1849            m_sqlManager.closeAll(dbc, conn, stmt, res);
1850        }
1851
1852        // sort result in memory, this is to avoid DB dependencies in the result order
1853        Collections.sort(result, I_CmsResource.COMPARE_ROOT_PATH_IGNORE_CASE_FOLDERS_FIRST);
1854        return result;
1855    }
1856
1857    /**
1858     * @see org.opencms.db.I_CmsVfsDriver#readContent(org.opencms.db.CmsDbContext, CmsUUID, org.opencms.util.CmsUUID)
1859     */
1860    public byte[] readContent(CmsDbContext dbc, CmsUUID projectId, CmsUUID resourceId) throws CmsDataAccessException {
1861
1862        PreparedStatement stmt = null;
1863        ResultSet res = null;
1864        Connection conn = null;
1865        byte[] byteRes = null;
1866
1867        try {
1868            conn = m_sqlManager.getConnection(dbc);
1869            if (projectId.equals(CmsProject.ONLINE_PROJECT_ID)) {
1870                stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_ONLINE_FILES_CONTENT");
1871            } else {
1872                stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_OFFLINE_FILES_CONTENT");
1873            }
1874            stmt.setString(1, resourceId.toString());
1875            res = stmt.executeQuery();
1876
1877            if (res.next()) {
1878                //query to read Array of bytes for the attribute FILE_CONTENT
1879                byteRes = m_sqlManager.getBytes(res, m_sqlManager.readQuery("C_RESOURCES_FILE_CONTENT"));
1880                while (res.next()) {
1881                    // do nothing only move through all rows because of mssql odbc driver
1882                }
1883            } else {
1884                throw new CmsVfsResourceNotFoundException(
1885                    Messages.get().container(
1886                        Messages.ERR_READ_CONTENT_WITH_RESOURCE_ID_2,
1887                        resourceId,
1888                        Boolean.valueOf(projectId.equals(CmsProject.ONLINE_PROJECT_ID))));
1889            }
1890        } catch (SQLException e) {
1891            throw new CmsDbSqlException(
1892                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
1893                e);
1894        } finally {
1895            m_sqlManager.closeAll(dbc, conn, stmt, res);
1896        }
1897        return byteRes;
1898    }
1899
1900    /**
1901     * @see org.opencms.db.I_CmsVfsDriver#readFolder(org.opencms.db.CmsDbContext, CmsUUID, org.opencms.util.CmsUUID)
1902     */
1903    public CmsFolder readFolder(CmsDbContext dbc, CmsUUID projectId, CmsUUID folderId) throws CmsDataAccessException {
1904
1905        CmsFolder folder = null;
1906        ResultSet res = null;
1907        PreparedStatement stmt = null;
1908        Connection conn = null;
1909
1910        try {
1911            conn = m_sqlManager.getConnection(dbc);
1912            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RESOURCES_READBYID");
1913            stmt.setString(1, folderId.toString());
1914            res = stmt.executeQuery();
1915
1916            if (res.next()) {
1917                folder = createFolder(res, projectId, true);
1918                while (res.next()) {
1919                    // do nothing only move through all rows because of mssql odbc driver
1920                }
1921            } else {
1922                throw new CmsVfsResourceNotFoundException(
1923                    Messages.get().container(Messages.ERR_READ_FOLDER_WITH_ID_1, folderId));
1924            }
1925        } catch (SQLException e) {
1926            throw new CmsDbSqlException(
1927                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
1928                e);
1929        } finally {
1930            m_sqlManager.closeAll(dbc, conn, stmt, res);
1931        }
1932
1933        return folder;
1934    }
1935
1936    /**
1937     * @see org.opencms.db.I_CmsVfsDriver#readFolder(org.opencms.db.CmsDbContext, CmsUUID, java.lang.String)
1938     */
1939    public CmsFolder readFolder(CmsDbContext dbc, CmsUUID projectId, String folderPath) throws CmsDataAccessException {
1940
1941        CmsFolder folder = null;
1942        ResultSet res = null;
1943        PreparedStatement stmt = null;
1944        Connection conn = null;
1945
1946        folderPath = CmsFileUtil.removeTrailingSeparator(folderPath);
1947        try {
1948            conn = m_sqlManager.getConnection(dbc);
1949            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RESOURCES_READ");
1950
1951            stmt.setString(1, folderPath);
1952            res = stmt.executeQuery();
1953
1954            if (res.next()) {
1955                folder = createFolder(res, projectId, true);
1956                while (res.next()) {
1957                    // do nothing only move through all rows because of mssql odbc driver
1958                }
1959            } else {
1960                throw new CmsVfsResourceNotFoundException(
1961                    Messages.get().container(Messages.ERR_READ_FOLDER_1, dbc.removeSiteRoot(folderPath)));
1962            }
1963        } catch (SQLException e) {
1964            throw new CmsDbSqlException(
1965                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
1966                e);
1967        } finally {
1968            m_sqlManager.closeAll(dbc, conn, stmt, res);
1969        }
1970
1971        return folder;
1972
1973    }
1974
1975    /**
1976     * @see org.opencms.db.I_CmsVfsDriver#readParentFolder(org.opencms.db.CmsDbContext, CmsUUID, org.opencms.util.CmsUUID)
1977     */
1978    public CmsFolder readParentFolder(CmsDbContext dbc, CmsUUID projectId, CmsUUID structureId)
1979    throws CmsDataAccessException {
1980
1981        CmsFolder parent = null;
1982        ResultSet res = null;
1983        PreparedStatement stmt = null;
1984        Connection conn = null;
1985        try {
1986            conn = m_sqlManager.getConnection(dbc);
1987            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RESOURCES_READ_PARENT_BY_ID");
1988            stmt.setString(1, structureId.toString());
1989            res = stmt.executeQuery();
1990
1991            if (res.next()) {
1992                parent = new CmsFolder(createResource(res, projectId));
1993                while (res.next()) {
1994                    // do nothing only move through all rows because of mssql odbc driver
1995                }
1996            }
1997        } catch (SQLException e) {
1998            throw new CmsDbSqlException(
1999                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
2000                e);
2001        } finally {
2002            m_sqlManager.closeAll(dbc, conn, stmt, res);
2003        }
2004        return parent;
2005    }
2006
2007    /**
2008     * @see org.opencms.db.I_CmsVfsDriver#readPropertyDefinition(org.opencms.db.CmsDbContext, java.lang.String, CmsUUID)
2009     */
2010    public CmsPropertyDefinition readPropertyDefinition(CmsDbContext dbc, String name, CmsUUID projectId)
2011    throws CmsDataAccessException {
2012
2013        CmsPropertyDefinition propDef = null;
2014        ResultSet res = null;
2015        PreparedStatement stmt = null;
2016        Connection conn = null;
2017
2018        try {
2019            conn = m_sqlManager.getConnection(dbc);
2020            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_PROPERTYDEF_READ");
2021            stmt.setString(1, name);
2022            res = stmt.executeQuery();
2023
2024            // if result set exists - return it
2025            if (res.next()) {
2026                propDef = new CmsPropertyDefinition(
2027                    new CmsUUID(res.getString(m_sqlManager.readQuery("C_PROPERTYDEF_ID"))),
2028                    res.getString(m_sqlManager.readQuery("C_PROPERTYDEF_NAME")),
2029                    CmsPropertyDefinition.CmsPropertyType.valueOf(
2030                        res.getInt(m_sqlManager.readQuery("C_PROPERTYDEF_TYPE"))));
2031                while (res.next()) {
2032                    // do nothing only move through all rows because of mssql odbc driver
2033                }
2034            } else {
2035                throw new CmsDbEntryNotFoundException(
2036                    Messages.get().container(Messages.ERR_NO_PROPERTYDEF_WITH_NAME_1, name));
2037            }
2038        } catch (SQLException e) {
2039            throw new CmsDbSqlException(
2040                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
2041                e);
2042        } finally {
2043            m_sqlManager.closeAll(dbc, conn, stmt, res);
2044        }
2045
2046        return propDef;
2047    }
2048
2049    /**
2050     * @see org.opencms.db.I_CmsVfsDriver#readPropertyDefinitions(org.opencms.db.CmsDbContext, CmsUUID)
2051     */
2052    public List<CmsPropertyDefinition> readPropertyDefinitions(CmsDbContext dbc, CmsUUID projectId)
2053    throws CmsDataAccessException {
2054
2055        ArrayList<CmsPropertyDefinition> propertyDefinitions = new ArrayList<CmsPropertyDefinition>();
2056        ResultSet res = null;
2057        PreparedStatement stmt = null;
2058        Connection conn = null;
2059
2060        try {
2061            conn = m_sqlManager.getConnection(dbc);
2062            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_PROPERTYDEF_READALL");
2063
2064            res = stmt.executeQuery();
2065            while (res.next()) {
2066                propertyDefinitions.add(
2067                    new CmsPropertyDefinition(
2068                        new CmsUUID(res.getString(m_sqlManager.readQuery("C_PROPERTYDEF_ID"))),
2069                        res.getString(m_sqlManager.readQuery("C_PROPERTYDEF_NAME")),
2070                        CmsPropertyDefinition.CmsPropertyType.valueOf(
2071                            res.getInt(m_sqlManager.readQuery("C_PROPERTYDEF_TYPE")))));
2072            }
2073        } catch (SQLException e) {
2074            throw new CmsDbSqlException(
2075                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
2076                e);
2077        } finally {
2078            m_sqlManager.closeAll(dbc, conn, stmt, res);
2079        }
2080        return propertyDefinitions;
2081    }
2082
2083    /**
2084     * @see org.opencms.db.I_CmsVfsDriver#readPropertyObject(org.opencms.db.CmsDbContext, java.lang.String, org.opencms.file.CmsProject, org.opencms.file.CmsResource)
2085     */
2086    public CmsProperty readPropertyObject(CmsDbContext dbc, String key, CmsProject project, CmsResource resource)
2087    throws CmsDataAccessException {
2088
2089        CmsUUID projectId = ((dbc.getProjectId() == null) || dbc.getProjectId().isNullUUID())
2090        ? project.getUuid()
2091        : dbc.getProjectId();
2092
2093        ResultSet res = null;
2094        PreparedStatement stmt = null;
2095        Connection conn = null;
2096        String propertyValue = null;
2097        int mappingType = -1;
2098        CmsProperty property = null;
2099        int resultSize = 0;
2100
2101        try {
2102            conn = m_sqlManager.getConnection(dbc);
2103            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_PROPERTIES_READ");
2104
2105            stmt.setString(1, key);
2106            stmt.setString(2, resource.getStructureId().toString());
2107            stmt.setString(3, resource.getResourceId().toString());
2108            res = stmt.executeQuery();
2109
2110            while (res.next()) {
2111                if (resultSize >= 2) {
2112                    throw new CmsDbConsistencyException(
2113                        Messages.get().container(
2114                            Messages.ERR_TOO_MANY_PROPERTIES_3,
2115                            key,
2116                            resource.getRootPath(),
2117                            new Integer(resultSize)));
2118                }
2119
2120                if (property == null) {
2121                    property = new CmsProperty();
2122                    property.setName(key);
2123                }
2124
2125                propertyValue = res.getString(1);
2126                mappingType = res.getInt(2);
2127
2128                if (mappingType == CmsProperty.STRUCTURE_RECORD_MAPPING) {
2129                    property.setStructureValue(propertyValue);
2130                } else if (mappingType == CmsProperty.RESOURCE_RECORD_MAPPING) {
2131                    property.setResourceValue(propertyValue);
2132                } else {
2133                    throw new CmsDbConsistencyException(
2134                        Messages.get().container(
2135                            Messages.ERR_UNKNOWN_PROPERTY_VALUE_MAPPING_3,
2136                            resource.getRootPath(),
2137                            new Integer(mappingType),
2138                            key));
2139                }
2140
2141                resultSize++;
2142            }
2143        } catch (SQLException e) {
2144            throw new CmsDbSqlException(
2145                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
2146                e);
2147        } finally {
2148            m_sqlManager.closeAll(dbc, conn, stmt, res);
2149        }
2150
2151        return (property != null) ? property : CmsProperty.getNullProperty();
2152    }
2153
2154    /**
2155     * @see org.opencms.db.I_CmsVfsDriver#readPropertyObjects(org.opencms.db.CmsDbContext, org.opencms.file.CmsProject, org.opencms.file.CmsResource)
2156     */
2157    public List<CmsProperty> readPropertyObjects(CmsDbContext dbc, CmsProject project, CmsResource resource)
2158    throws CmsDataAccessException {
2159
2160        CmsUUID projectId = ((dbc.getProjectId() == null) || dbc.getProjectId().isNullUUID())
2161        ? project.getUuid()
2162        : dbc.getProjectId();
2163
2164        ResultSet res = null;
2165        PreparedStatement stmt = null;
2166        Connection conn = null;
2167        int mappingType = -1;
2168        Map<String, CmsProperty> propertyMap = new HashMap<String, CmsProperty>();
2169
2170        String propertyKey;
2171        String propertyValue;
2172        CmsProperty property;
2173
2174        try {
2175            conn = m_sqlManager.getConnection(dbc);
2176            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_PROPERTIES_READALL");
2177            stmt.setString(1, resource.getStructureId().toString());
2178            stmt.setString(2, resource.getResourceId().toString());
2179            res = stmt.executeQuery();
2180
2181            while (res.next()) {
2182                propertyKey = null;
2183                propertyValue = null;
2184                mappingType = -1;
2185
2186                propertyKey = res.getString(1);
2187                propertyValue = res.getString(2);
2188                mappingType = res.getInt(3);
2189
2190                property = propertyMap.get(propertyKey);
2191                if (property == null) {
2192                    // there doesn't exist a property object for this key yet
2193                    property = new CmsProperty();
2194                    property.setName(propertyKey);
2195                    propertyMap.put(propertyKey, property);
2196                }
2197
2198                if (mappingType == CmsProperty.STRUCTURE_RECORD_MAPPING) {
2199                    // this property value is mapped to a structure record
2200                    property.setStructureValue(propertyValue);
2201                } else if (mappingType == CmsProperty.RESOURCE_RECORD_MAPPING) {
2202                    // this property value is mapped to a resource record
2203                    property.setResourceValue(propertyValue);
2204                } else {
2205                    throw new CmsDbConsistencyException(
2206                        Messages.get().container(
2207                            Messages.ERR_UNKNOWN_PROPERTY_VALUE_MAPPING_3,
2208                            resource.getRootPath(),
2209                            new Integer(mappingType),
2210                            propertyKey));
2211                }
2212                property.setOrigin(resource.getRootPath());
2213            }
2214        } catch (SQLException e) {
2215            throw new CmsDbSqlException(
2216                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
2217                e);
2218        } finally {
2219            m_sqlManager.closeAll(dbc, conn, stmt, res);
2220        }
2221
2222        return new ArrayList<CmsProperty>(propertyMap.values());
2223    }
2224
2225    /**
2226     * @see org.opencms.db.I_CmsVfsDriver#readRelations(org.opencms.db.CmsDbContext, CmsUUID, CmsResource, org.opencms.relations.CmsRelationFilter)
2227     */
2228    public List<CmsRelation> readRelations(
2229        CmsDbContext dbc,
2230        CmsUUID projectId,
2231        CmsResource resource,
2232        CmsRelationFilter filter)
2233    throws CmsDataAccessException {
2234
2235        Set<CmsRelation> relations = new HashSet<CmsRelation>();
2236
2237        Connection conn = null;
2238        PreparedStatement stmt = null;
2239        ResultSet res = null;
2240
2241        try {
2242            conn = m_sqlManager.getConnection(dbc);
2243            if (filter.isSource()) {
2244                List<Object> params = new ArrayList<Object>(7);
2245
2246                StringBuffer queryBuf = new StringBuffer(256);
2247                queryBuf.append(m_sqlManager.readQuery(projectId, "C_READ_RELATIONS"));
2248                queryBuf.append(prepareRelationConditions(projectId, filter, resource, params, true));
2249                if (LOG.isDebugEnabled()) {
2250                    LOG.debug(queryBuf.toString());
2251                }
2252
2253                stmt = m_sqlManager.getPreparedStatementForSql(conn, queryBuf.toString());
2254                for (int i = 0; i < params.size(); i++) {
2255                    if (params.get(i) instanceof Integer) {
2256                        stmt.setInt(i + 1, ((Integer)params.get(i)).intValue());
2257                    } else {
2258                        stmt.setString(i + 1, (String)params.get(i));
2259                    }
2260                }
2261                res = stmt.executeQuery();
2262                while (res.next()) {
2263                    relations.add(internalReadRelation(res));
2264                }
2265                m_sqlManager.closeAll(dbc, null, stmt, res);
2266            }
2267
2268            if (filter.isTarget()) {
2269                List<Object> params = new ArrayList<Object>(7);
2270
2271                StringBuffer queryBuf = new StringBuffer(256);
2272                queryBuf.append(m_sqlManager.readQuery(projectId, "C_READ_RELATIONS"));
2273                queryBuf.append(prepareRelationConditions(projectId, filter, resource, params, false));
2274                if (LOG.isDebugEnabled()) {
2275                    LOG.debug(queryBuf.toString());
2276                }
2277
2278                stmt = m_sqlManager.getPreparedStatementForSql(conn, queryBuf.toString());
2279                for (int i = 0; i < params.size(); i++) {
2280                    if (params.get(i) instanceof Integer) {
2281                        stmt.setInt(i + 1, ((Integer)params.get(i)).intValue());
2282                    } else {
2283                        stmt.setString(i + 1, (String)params.get(i));
2284                    }
2285                }
2286                res = stmt.executeQuery();
2287                while (res.next()) {
2288                    relations.add(internalReadRelation(res));
2289                }
2290                m_sqlManager.closeAll(dbc, null, stmt, res);
2291            }
2292        } catch (SQLException e) {
2293            throw new CmsDbSqlException(
2294                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
2295                e);
2296        } finally {
2297            m_sqlManager.closeAll(dbc, conn, stmt, res);
2298        }
2299
2300        List<CmsRelation> result = new ArrayList<CmsRelation>(relations);
2301        Collections.sort(result, CmsRelation.COMPARATOR);
2302        return result;
2303    }
2304
2305    /**
2306     * @see org.opencms.db.I_CmsVfsDriver#readResource(org.opencms.db.CmsDbContext, CmsUUID, org.opencms.util.CmsUUID, boolean)
2307     */
2308    public CmsResource readResource(CmsDbContext dbc, CmsUUID projectId, CmsUUID structureId, boolean includeDeleted)
2309    throws CmsDataAccessException {
2310
2311        CmsResource resource = null;
2312        ResultSet res = null;
2313        PreparedStatement stmt = null;
2314        Connection conn = null;
2315
2316        try {
2317            conn = m_sqlManager.getConnection(dbc);
2318            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RESOURCES_READBYID");
2319
2320            stmt.setString(1, structureId.toString());
2321            res = stmt.executeQuery();
2322
2323            if (res.next()) {
2324                resource = createResource(res, projectId);
2325                while (res.next()) {
2326                    // do nothing only move through all rows because of mssql odbc driver
2327                }
2328            } else {
2329                throw new CmsVfsResourceNotFoundException(
2330                    Messages.get().container(Messages.ERR_READ_RESOURCE_WITH_ID_1, structureId));
2331            }
2332        } catch (SQLException e) {
2333            throw new CmsDbSqlException(
2334                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
2335                e);
2336        } finally {
2337            m_sqlManager.closeAll(dbc, conn, stmt, res);
2338        }
2339
2340        // check if this resource is marked as deleted and if we are allowed to return a deleted resource
2341        if ((resource != null) && resource.getState().isDeleted() && !includeDeleted) {
2342            throw new CmsVfsResourceNotFoundException(
2343                Messages.get().container(
2344                    Messages.ERR_READ_DELETED_RESOURCE_1,
2345                    dbc.removeSiteRoot(resource.getRootPath())));
2346        }
2347
2348        return resource;
2349    }
2350
2351    /**
2352     * @see org.opencms.db.I_CmsVfsDriver#readResource(org.opencms.db.CmsDbContext, CmsUUID, java.lang.String, boolean)
2353     */
2354    public CmsResource readResource(CmsDbContext dbc, CmsUUID projectId, String path, boolean includeDeleted)
2355    throws CmsDataAccessException {
2356
2357        CmsResource resource = null;
2358        ResultSet res = null;
2359        PreparedStatement stmt = null;
2360        Connection conn = null;
2361
2362        // must remove trailing slash
2363        int len = path.length();
2364        path = CmsFileUtil.removeTrailingSeparator(path);
2365        boolean endsWithSlash = (len != path.length());
2366
2367        try {
2368            conn = m_sqlManager.getConnection(dbc);
2369            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RESOURCES_READ");
2370
2371            stmt.setString(1, path);
2372            res = stmt.executeQuery();
2373
2374            if (res.next()) {
2375                resource = createResource(res, projectId);
2376
2377                // check if the resource is a file, it is not allowed to end with a "/" then
2378                if (endsWithSlash && resource.isFile()) {
2379                    throw new CmsVfsResourceNotFoundException(
2380                        Messages.get().container(Messages.ERR_READ_RESOURCE_1, dbc.removeSiteRoot(path + "/")));
2381                }
2382
2383                while (res.next()) {
2384                    // do nothing only move through all rows because of mssql odbc driver
2385                }
2386            } else {
2387                throw new CmsVfsResourceNotFoundException(
2388                    Messages.get().container(Messages.ERR_READ_RESOURCE_1, dbc.removeSiteRoot(path)));
2389            }
2390        } catch (SQLException e) {
2391            throw new CmsDbSqlException(
2392                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
2393                e);
2394        } finally {
2395            m_sqlManager.closeAll(dbc, conn, stmt, res);
2396        }
2397
2398        // check if this resource is marked as deleted and if we are allowed to return a deleted resource
2399        if ((resource != null) && resource.getState().isDeleted() && !includeDeleted) {
2400            throw new CmsVfsResourceNotFoundException(
2401                Messages.get().container(
2402                    Messages.ERR_READ_DELETED_RESOURCE_1,
2403                    dbc.removeSiteRoot(resource.getRootPath())));
2404        }
2405
2406        return resource;
2407    }
2408
2409    /**
2410     * @see org.opencms.db.I_CmsVfsDriver#readResources(org.opencms.db.CmsDbContext, CmsUUID, CmsResourceState, int)
2411     */
2412    public List<CmsResource> readResources(CmsDbContext dbc, CmsUUID projectId, CmsResourceState state, int mode)
2413    throws CmsDataAccessException {
2414
2415        List<CmsResource> result = new ArrayList<CmsResource>();
2416
2417        ResultSet res = null;
2418        PreparedStatement stmt = null;
2419        Connection conn = null;
2420
2421        try {
2422            conn = m_sqlManager.getConnection(dbc);
2423            if (mode == CmsDriverManager.READMODE_MATCHSTATE) {
2424                stmt = m_sqlManager.getPreparedStatement(
2425                    conn,
2426                    projectId,
2427                    "C_RESOURCES_GET_RESOURCE_IN_PROJECT_WITH_STATE");
2428                stmt.setString(1, projectId.toString());
2429                stmt.setInt(2, state.getState());
2430                stmt.setInt(3, state.getState());
2431                stmt.setInt(4, state.getState());
2432                stmt.setInt(5, state.getState());
2433            } else if (mode == CmsDriverManager.READMODE_UNMATCHSTATE) {
2434                stmt = m_sqlManager.getPreparedStatement(
2435                    conn,
2436                    projectId,
2437                    "C_RESOURCES_GET_RESOURCE_IN_PROJECT_WITHOUT_STATE");
2438                stmt.setString(1, projectId.toString());
2439                stmt.setInt(2, state.getState());
2440                stmt.setInt(3, state.getState());
2441            } else {
2442                stmt = m_sqlManager.getPreparedStatement(
2443                    conn,
2444                    projectId,
2445                    "C_RESOURCES_GET_RESOURCE_IN_PROJECT_IGNORE_STATE");
2446                stmt.setString(1, projectId.toString());
2447            }
2448
2449            res = stmt.executeQuery();
2450            while (res.next()) {
2451                CmsResource resource = createResource(res, projectId);
2452                result.add(resource);
2453            }
2454        } catch (SQLException e) {
2455            throw new CmsDbSqlException(
2456                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
2457                e);
2458        } finally {
2459            m_sqlManager.closeAll(dbc, conn, stmt, res);
2460        }
2461
2462        return result;
2463    }
2464
2465    /**
2466     * @see org.opencms.db.I_CmsVfsDriver#readResourcesForPrincipalACE(org.opencms.db.CmsDbContext, org.opencms.file.CmsProject, org.opencms.util.CmsUUID)
2467     */
2468    public List<CmsResource> readResourcesForPrincipalACE(CmsDbContext dbc, CmsProject project, CmsUUID principalId)
2469    throws CmsDataAccessException {
2470
2471        PreparedStatement stmt = null;
2472        Connection conn = null;
2473        ResultSet res = null;
2474        CmsResource currentResource = null;
2475        List<CmsResource> resources = new ArrayList<CmsResource>();
2476
2477        try {
2478            conn = m_sqlManager.getConnection(dbc);
2479            stmt = m_sqlManager.getPreparedStatement(conn, project, "C_SELECT_RESOURCES_FOR_PRINCIPAL_ACE");
2480
2481            stmt.setString(1, principalId.toString());
2482            res = stmt.executeQuery();
2483
2484            while (res.next()) {
2485                currentResource = createFile(res, project.getUuid(), false);
2486                resources.add(currentResource);
2487            }
2488        } catch (SQLException e) {
2489            throw new CmsDbSqlException(
2490                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
2491                e);
2492        } finally {
2493            m_sqlManager.closeAll(dbc, conn, stmt, res);
2494        }
2495        return resources;
2496    }
2497
2498    /**
2499     * @see org.opencms.db.I_CmsVfsDriver#readResourcesForPrincipalAttr(org.opencms.db.CmsDbContext, org.opencms.file.CmsProject, org.opencms.util.CmsUUID)
2500     */
2501    public List<CmsResource> readResourcesForPrincipalAttr(CmsDbContext dbc, CmsProject project, CmsUUID principalId)
2502    throws CmsDataAccessException {
2503
2504        PreparedStatement stmt = null;
2505        Connection conn = null;
2506        ResultSet res = null;
2507        CmsResource currentResource = null;
2508        List<CmsResource> resources = new ArrayList<CmsResource>();
2509
2510        try {
2511            conn = m_sqlManager.getConnection(dbc);
2512            stmt = m_sqlManager.getPreparedStatement(conn, project, "C_SELECT_RESOURCES_FOR_PRINCIPAL_ATTR");
2513
2514            stmt.setString(1, principalId.toString());
2515            stmt.setString(2, principalId.toString());
2516            res = stmt.executeQuery();
2517
2518            while (res.next()) {
2519                currentResource = createFile(res, project.getUuid(), false);
2520                resources.add(currentResource);
2521            }
2522        } catch (SQLException e) {
2523            throw new CmsDbSqlException(
2524                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
2525                e);
2526        } finally {
2527            m_sqlManager.closeAll(dbc, conn, stmt, res);
2528        }
2529        return resources;
2530    }
2531
2532    /**
2533     * @see org.opencms.db.I_CmsVfsDriver#readResourcesWithProperty(org.opencms.db.CmsDbContext, CmsUUID, org.opencms.util.CmsUUID, String, String)
2534     */
2535    public List<CmsResource> readResourcesWithProperty(
2536        CmsDbContext dbc,
2537        CmsUUID projectId,
2538        CmsUUID propertyDef,
2539        String path,
2540        String value)
2541    throws CmsDataAccessException {
2542
2543        List<CmsResource> resources = new ArrayList<CmsResource>();
2544        ResultSet res = null;
2545        PreparedStatement stmt = null;
2546        Connection conn = null;
2547
2548        try {
2549            conn = m_sqlManager.getConnection(dbc);
2550            if (value == null) {
2551                stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RESOURCES_GET_RESOURCE_WITH_PROPERTYDEF");
2552                stmt.setString(1, propertyDef.toString());
2553                stmt.setString(2, path + "%");
2554                stmt.setString(3, propertyDef.toString());
2555                stmt.setString(4, path + "%");
2556            } else {
2557                stmt = m_sqlManager.getPreparedStatement(
2558                    conn,
2559                    projectId,
2560                    "C_RESOURCES_GET_RESOURCE_WITH_PROPERTYDEF_VALUE");
2561                stmt.setString(1, propertyDef.toString());
2562                stmt.setString(2, path + "%");
2563                stmt.setString(3, "%" + value + "%");
2564                stmt.setString(4, propertyDef.toString());
2565                stmt.setString(5, path + "%");
2566                stmt.setString(6, "%" + value + "%");
2567            }
2568            res = stmt.executeQuery();
2569
2570            while (res.next()) {
2571                CmsResource resource = createResource(res, projectId);
2572                resources.add(resource);
2573            }
2574        } catch (SQLException e) {
2575            throw new CmsDbSqlException(
2576                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
2577                e);
2578        } finally {
2579            m_sqlManager.closeAll(dbc, conn, stmt, res);
2580        }
2581
2582        return resources;
2583    }
2584
2585    /**
2586     * @see org.opencms.db.I_CmsVfsDriver#readResourceTree(org.opencms.db.CmsDbContext, CmsUUID, java.lang.String, int, CmsResourceState, long, long, long, long, long, long, int)
2587     */
2588    public List<CmsResource> readResourceTree(
2589        CmsDbContext dbc,
2590        CmsUUID projectId,
2591        String parentPath,
2592        int type,
2593        CmsResourceState state,
2594        long lastModifiedAfter,
2595        long lastModifiedBefore,
2596        long releasedAfter,
2597        long releasedBefore,
2598        long expiredAfter,
2599        long expiredBefore,
2600        int mode)
2601    throws CmsDataAccessException {
2602
2603        List<CmsResource> result = new ArrayList<CmsResource>();
2604
2605        StringBuffer conditions = new StringBuffer();
2606        List<Object> params = new ArrayList<Object>(5);
2607
2608        // prepare the selection criteria
2609        prepareProjectCondition(projectId, mode, conditions, params);
2610        prepareResourceCondition(projectId, mode, conditions);
2611        prepareTypeCondition(projectId, type, mode, conditions, params);
2612        prepareTimeRangeCondition(projectId, lastModifiedAfter, lastModifiedBefore, conditions, params);
2613        prepareReleasedTimeRangeCondition(projectId, releasedAfter, releasedBefore, conditions, params);
2614        prepareExpiredTimeRangeCondition(projectId, expiredAfter, expiredBefore, conditions, params);
2615        preparePathCondition(projectId, parentPath, mode, conditions, params);
2616        prepareStateCondition(projectId, state, mode, conditions, params);
2617
2618        // now read matching resources within the subtree
2619        ResultSet res = null;
2620        PreparedStatement stmt = null;
2621        Connection conn = null;
2622
2623        try {
2624            conn = m_sqlManager.getConnection(dbc);
2625            StringBuffer queryBuf = new StringBuffer(256);
2626            queryBuf.append(m_sqlManager.readQuery(projectId, "C_RESOURCES_READ_TREE"));
2627            queryBuf.append(conditions);
2628            queryBuf.append(" ");
2629            queryBuf.append(m_sqlManager.readQuery(projectId, "C_RESOURCES_ORDER_BY_PATH"));
2630            stmt = m_sqlManager.getPreparedStatementForSql(conn, queryBuf.toString());
2631
2632            for (int i = 0; i < params.size(); i++) {
2633                if (params.get(i) instanceof Integer) {
2634                    stmt.setInt(i + 1, ((Integer)params.get(i)).intValue());
2635                } else if (params.get(i) instanceof Long) {
2636                    stmt.setLong(i + 1, ((Long)params.get(i)).longValue());
2637                } else {
2638                    stmt.setString(i + 1, (String)params.get(i));
2639                }
2640            }
2641
2642            res = stmt.executeQuery();
2643            while (res.next()) {
2644                CmsResource resource = createResource(res, projectId);
2645                result.add(resource);
2646            }
2647
2648        } catch (SQLException e) {
2649            throw new CmsDbSqlException(
2650                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
2651                e);
2652        } finally {
2653            m_sqlManager.closeAll(dbc, conn, stmt, res);
2654        }
2655
2656        return result;
2657    }
2658
2659    /**
2660     * @see org.opencms.db.I_CmsVfsDriver#readRewriteAliases(org.opencms.db.CmsDbContext, org.opencms.db.CmsRewriteAliasFilter)
2661     */
2662    public List<CmsRewriteAlias> readRewriteAliases(CmsDbContext dbc, CmsRewriteAliasFilter filter)
2663    throws CmsDataAccessException {
2664
2665        Connection conn = null;
2666        PreparedStatement stmt = null;
2667        ResultSet res = null;
2668        List<CmsRewriteAlias> result = new ArrayList<CmsRewriteAlias>();
2669        try {
2670            conn = m_sqlManager.getConnection(dbc);
2671            CmsPair<String, List<Object>> conditionAndParams = prepareRewriteAliasConditions(filter);
2672            String condition = conditionAndParams.getFirst();
2673            List<Object> params = conditionAndParams.getSecond();
2674            String query = m_sqlManager.readQuery("C_REWRITE_ALIAS_READ") + condition;
2675            stmt = m_sqlManager.getPreparedStatementForSql(conn, query);
2676            CmsDbUtil.fillParameters(stmt, params);
2677            res = stmt.executeQuery();
2678            while (res.next()) {
2679                int col = 1;
2680                String id = res.getString(col++);
2681                String siteRoot = res.getString(col++);
2682                String patternString = res.getString(col++);
2683                String replacementString = res.getString(col++);
2684                int mode = res.getInt(col++);
2685                CmsRewriteAlias alias = new CmsRewriteAlias(
2686                    new CmsUUID(id),
2687                    siteRoot,
2688                    patternString,
2689                    replacementString,
2690                    CmsAliasMode.fromInt(mode));
2691                result.add(alias);
2692            }
2693            return result;
2694        } catch (SQLException e) {
2695            throw wrapException(stmt, e);
2696        } finally {
2697            m_sqlManager.closeAll(dbc, conn, stmt, res);
2698        }
2699
2700    }
2701
2702    /**
2703     * @see org.opencms.db.I_CmsVfsDriver#readSiblings(org.opencms.db.CmsDbContext, CmsUUID, org.opencms.file.CmsResource, boolean)
2704     */
2705    public List<CmsResource> readSiblings(
2706        CmsDbContext dbc,
2707        CmsUUID projectId,
2708        CmsResource resource,
2709        boolean includeDeleted)
2710    throws CmsDataAccessException {
2711
2712        PreparedStatement stmt = null;
2713        Connection conn = null;
2714        ResultSet res = null;
2715        CmsResource currentResource = null;
2716        List<CmsResource> vfsLinks = new ArrayList<CmsResource>();
2717
2718        try {
2719            conn = m_sqlManager.getConnection(dbc);
2720
2721            if (includeDeleted) {
2722                stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_SELECT_VFS_SIBLINGS");
2723            } else {
2724                stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_SELECT_NONDELETED_VFS_SIBLINGS");
2725            }
2726
2727            stmt.setString(1, resource.getResourceId().toString());
2728            res = stmt.executeQuery();
2729
2730            while (res.next()) {
2731                currentResource = createFile(res, projectId, false);
2732                vfsLinks.add(currentResource);
2733            }
2734        } catch (SQLException e) {
2735            throw new CmsDbSqlException(
2736                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
2737                e);
2738        } finally {
2739            m_sqlManager.closeAll(dbc, conn, stmt, res);
2740        }
2741
2742        return vfsLinks;
2743    }
2744
2745    /**
2746     * Reads the URL name mapping entries which match a given filter.<p>
2747     *
2748     * @param dbc the database context
2749     * @param online if true, reads from the online mapping, else from the offline mapping
2750     * @param filter the filter which the entries to be read should match
2751     *
2752     * @return the mapping entries which match the given filter
2753     *
2754     * @throws CmsDataAccessException if something goes wrong
2755     */
2756    public List<CmsUrlNameMappingEntry> readUrlNameMappingEntries(
2757        CmsDbContext dbc,
2758        boolean online,
2759        CmsUrlNameMappingFilter filter)
2760    throws CmsDataAccessException {
2761
2762        Connection conn = null;
2763        ResultSet resultSet = null;
2764        PreparedStatement stmt = null;
2765        List<CmsUrlNameMappingEntry> result = new ArrayList<CmsUrlNameMappingEntry>();
2766        try {
2767            conn = m_sqlManager.getConnection(dbc);
2768            String query = m_sqlManager.readQuery("C_READ_URLNAME_MAPPINGS");
2769            query = replaceProject(query, online);
2770            stmt = getPreparedStatementForFilter(conn, query, filter);
2771            resultSet = stmt.executeQuery();
2772            while (resultSet.next()) {
2773                CmsUrlNameMappingEntry entry = internalCreateUrlNameMappingEntry(resultSet);
2774                result.add(entry);
2775            }
2776            return result;
2777        } catch (SQLException e) {
2778            throw wrapException(stmt, e);
2779        } finally {
2780            m_sqlManager.closeAll(dbc, conn, stmt, resultSet);
2781        }
2782    }
2783
2784    /**
2785     * @see org.opencms.db.I_CmsVfsDriver#readVersions(org.opencms.db.CmsDbContext, org.opencms.util.CmsUUID, org.opencms.util.CmsUUID, org.opencms.util.CmsUUID)
2786     */
2787    public Map<String, Integer> readVersions(
2788        CmsDbContext dbc,
2789        CmsUUID projectId,
2790        CmsUUID resourceId,
2791        CmsUUID structureId)
2792    throws CmsDataAccessException {
2793
2794        int structureVersion = -1;
2795        int resourceVersion = -1;
2796
2797        Connection conn = null;
2798        PreparedStatement stmt = null;
2799        ResultSet res = null;
2800
2801        try {
2802            conn = m_sqlManager.getConnection(dbc);
2803
2804            // read the offline version numbers, first for the resource entry
2805            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RESOURCES_READ_VERSION_RES");
2806            stmt.setString(1, resourceId.toString());
2807            res = stmt.executeQuery();
2808            if (res.next()) {
2809                resourceVersion = res.getInt(m_sqlManager.readQuery("C_RESOURCES_VERSION"));
2810                while (res.next()) {
2811                    // do nothing only move through all rows because of mssql odbc driver
2812                }
2813            }
2814            m_sqlManager.closeAll(dbc, null, stmt, res);
2815            // then for the structure entry
2816            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RESOURCES_READ_VERSION_STR");
2817            stmt.setString(1, structureId.toString());
2818            res = stmt.executeQuery();
2819            if (res.next()) {
2820                structureVersion = res.getInt(m_sqlManager.readQuery("C_RESOURCES_STRUCTURE_VERSION"));
2821                while (res.next()) {
2822                    // do nothing only move through all rows because of mssql odbc driver
2823                }
2824            }
2825        } catch (SQLException e) {
2826            throw new CmsDbSqlException(
2827                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
2828                e);
2829        } finally {
2830            m_sqlManager.closeAll(dbc, conn, stmt, res);
2831        }
2832        Map<String, Integer> result = new HashMap<String, Integer>();
2833        result.put("structure", new Integer(structureVersion));
2834        result.put(I_CmsEventListener.KEY_RESOURCE, new Integer(resourceVersion));
2835        return result;
2836    }
2837
2838    /**
2839     * @see org.opencms.db.I_CmsVfsDriver#removeFile(org.opencms.db.CmsDbContext, CmsUUID, org.opencms.file.CmsResource)
2840     */
2841    public void removeFile(CmsDbContext dbc, CmsUUID projectId, CmsResource resource) throws CmsDataAccessException {
2842
2843        PreparedStatement stmt = null;
2844        Connection conn = null;
2845        int siblingCount = 0;
2846
2847        try {
2848            conn = m_sqlManager.getConnection(dbc);
2849
2850            // delete the structure record
2851            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_STRUCTURE_DELETE_BY_STRUCTUREID");
2852            stmt.setString(1, resource.getStructureId().toString());
2853            stmt.executeUpdate();
2854
2855            m_sqlManager.closeAll(dbc, conn, stmt, null);
2856
2857            // count the references to the resource
2858            siblingCount = countSiblings(dbc, projectId, resource.getResourceId());
2859
2860            conn = m_sqlManager.getConnection(dbc);
2861            if (siblingCount > 0) {
2862                // update the link Count
2863                stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RESOURCES_UPDATE_SIBLING_COUNT");
2864                stmt.setInt(1, siblingCount);
2865                stmt.setString(2, resource.getResourceId().toString());
2866                stmt.executeUpdate();
2867
2868                m_sqlManager.closeAll(dbc, null, stmt, null);
2869
2870                // update the resource flags
2871                stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RESOURCES_UPDATE_FLAGS");
2872                stmt.setInt(1, resource.getFlags());
2873                stmt.setString(2, resource.getResourceId().toString());
2874                stmt.executeUpdate();
2875
2876            } else {
2877                // if not referenced any longer, also delete the resource and the content record
2878                stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RESOURCES_DELETE_BY_RESOURCEID");
2879                stmt.setString(1, resource.getResourceId().toString());
2880                stmt.executeUpdate();
2881
2882                m_sqlManager.closeAll(dbc, null, stmt, null);
2883
2884                boolean dbcHasProjectId = (dbc.getProjectId() != null) && !dbc.getProjectId().isNullUUID();
2885
2886                // if online we have to keep historical content
2887                if (projectId.equals(CmsProject.ONLINE_PROJECT_ID)) {
2888                    // put the online content in the history
2889                    stmt = m_sqlManager.getPreparedStatement(conn, "C_ONLINE_CONTENTS_HISTORY");
2890                    stmt.setString(1, resource.getResourceId().toString());
2891                    stmt.executeUpdate();
2892                } else if (dbcHasProjectId) {
2893                    // remove current online version
2894                    stmt = m_sqlManager.getPreparedStatement(conn, "C_ONLINE_CONTENTS_DELETE");
2895                    stmt.setString(1, resource.getResourceId().toString());
2896                    stmt.executeUpdate();
2897                } else {
2898                    // delete content records with this resource id
2899                    stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_OFFLINE_FILE_CONTENT_DELETE");
2900                    stmt.setString(1, resource.getResourceId().toString());
2901                    stmt.executeUpdate();
2902                }
2903            }
2904        } catch (SQLException e) {
2905            throw new CmsDbSqlException(
2906                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
2907                e);
2908        } finally {
2909            m_sqlManager.closeAll(dbc, conn, stmt, null);
2910        }
2911    }
2912
2913    /**
2914     * @see org.opencms.db.I_CmsVfsDriver#removeFolder(org.opencms.db.CmsDbContext, org.opencms.file.CmsProject, org.opencms.file.CmsResource)
2915     */
2916    public void removeFolder(CmsDbContext dbc, CmsProject currentProject, CmsResource resource)
2917    throws CmsDataAccessException {
2918
2919        if ((dbc.getRequestContext() != null)
2920            && (dbc.getRequestContext().getAttribute(REQ_ATTR_CHECK_PERMISSIONS) != null)) {
2921            // only check write permissions
2922            checkWritePermissionsInFolder(dbc, resource);
2923            return;
2924        }
2925
2926        // check if the folder has any resources in it
2927        Iterator<CmsResource> childResources = readChildResources(dbc, currentProject, resource, true, true).iterator();
2928
2929        CmsUUID projectId = CmsProject.ONLINE_PROJECT_ID;
2930        if (currentProject.isOnlineProject()) {
2931            projectId = CmsUUID.getOpenCmsUUID(); // HACK: to get an offline project id
2932        }
2933
2934        // collect the names of the resources inside the folder, excluding the moved resources
2935        I_CmsVfsDriver vfsDriver = m_driverManager.getVfsDriver(dbc);
2936        StringBuffer errorResNames = new StringBuffer(128);
2937        while (childResources.hasNext()) {
2938            CmsResource errorRes = childResources.next();
2939            // if deleting offline, or not moved, or just renamed inside the deleted folder
2940            // so, it may remain some orphan online entries for moved resources
2941            // which will be fixed during the publishing of the moved resources
2942            boolean error = !currentProject.isOnlineProject();
2943            if (!error) {
2944                try {
2945                    String originalPath = vfsDriver.readResource(
2946                        dbc,
2947                        projectId,
2948                        errorRes.getRootPath(),
2949                        true).getRootPath();
2950                    error = originalPath.equals(errorRes.getRootPath())
2951                        || originalPath.startsWith(resource.getRootPath());
2952                } catch (CmsVfsResourceNotFoundException e) {
2953                    // ignore
2954                }
2955            }
2956            if (error) {
2957                if (errorResNames.length() != 0) {
2958                    errorResNames.append(", ");
2959                }
2960                errorResNames.append("[" + dbc.removeSiteRoot(errorRes.getRootPath()) + "]");
2961            }
2962        }
2963
2964        // the current implementation only deletes empty folders
2965        if (CmsStringUtil.isNotEmptyOrWhitespaceOnly(errorResNames.toString())) {
2966
2967            throw new CmsVfsException(
2968                Messages.get().container(
2969                    Messages.ERR_DELETE_NONEMTY_FOLDER_2,
2970                    dbc.removeSiteRoot(resource.getRootPath()),
2971                    errorResNames.toString()));
2972        }
2973        internalRemoveFolder(dbc, currentProject, resource);
2974
2975        // remove project resources
2976        String deletedResourceRootPath = resource.getRootPath();
2977        if (dbc.getRequestContext() != null) {
2978            dbc.getRequestContext().setAttribute(CmsProjectDriver.DBC_ATTR_READ_PROJECT_FOR_RESOURCE, Boolean.TRUE);
2979            I_CmsProjectDriver projectDriver = m_driverManager.getProjectDriver(dbc);
2980            Iterator<CmsProject> itProjects = projectDriver.readProjects(dbc, deletedResourceRootPath).iterator();
2981            while (itProjects.hasNext()) {
2982                CmsProject project = itProjects.next();
2983                projectDriver.deleteProjectResource(dbc, project.getUuid(), deletedResourceRootPath);
2984            }
2985        }
2986    }
2987
2988    /**
2989     * @see org.opencms.db.I_CmsVfsDriver#replaceResource(org.opencms.db.CmsDbContext, org.opencms.file.CmsResource, byte[], int)
2990     */
2991    public void replaceResource(CmsDbContext dbc, CmsResource newResource, byte[] resContent, int newResourceType)
2992    throws CmsDataAccessException {
2993
2994        if (resContent == null) {
2995            // nothing to do
2996            return;
2997        }
2998        Connection conn = null;
2999        PreparedStatement stmt = null;
3000        try {
3001            // write the file content
3002            writeContent(dbc, newResource.getResourceId(), resContent);
3003
3004            // update the resource record
3005            conn = m_sqlManager.getConnection(dbc);
3006            stmt = m_sqlManager.getPreparedStatement(conn, dbc.currentProject(), "C_RESOURCE_REPLACE");
3007            stmt.setInt(1, newResourceType);
3008            stmt.setInt(2, resContent.length);
3009            stmt.setLong(3, System.currentTimeMillis());
3010            stmt.setString(4, newResource.getResourceId().toString());
3011            stmt.executeUpdate();
3012
3013        } catch (SQLException e) {
3014            throw new CmsDbSqlException(
3015                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
3016                e);
3017        } finally {
3018            m_sqlManager.closeAll(dbc, conn, stmt, null);
3019        }
3020    }
3021
3022    /**
3023     * @see org.opencms.db.I_CmsVfsDriver#setDriverManager(org.opencms.db.CmsDriverManager)
3024     */
3025    public void setDriverManager(CmsDriverManager driverManager) {
3026
3027        m_driverManager = driverManager;
3028    }
3029
3030    /**
3031     * @see org.opencms.db.I_CmsVfsDriver#setSqlManager(org.opencms.db.CmsSqlManager)
3032     */
3033    public void setSqlManager(org.opencms.db.CmsSqlManager sqlManager) {
3034
3035        m_sqlManager = (CmsSqlManager)sqlManager;
3036    }
3037
3038    /**
3039     * @see org.opencms.db.I_CmsVfsDriver#transferResource(org.opencms.db.CmsDbContext, org.opencms.file.CmsProject, org.opencms.file.CmsResource, org.opencms.util.CmsUUID, org.opencms.util.CmsUUID)
3040     */
3041    public void transferResource(
3042        CmsDbContext dbc,
3043        CmsProject project,
3044        CmsResource resource,
3045        CmsUUID createdUser,
3046        CmsUUID lastModifiedUser)
3047    throws CmsDataAccessException {
3048
3049        if (createdUser == null) {
3050            createdUser = resource.getUserCreated();
3051        }
3052        if (lastModifiedUser == null) {
3053            lastModifiedUser = resource.getUserLastModified();
3054        }
3055
3056        PreparedStatement stmt = null;
3057        Connection conn = null;
3058        try {
3059            conn = m_sqlManager.getConnection(dbc);
3060            stmt = m_sqlManager.getPreparedStatement(conn, project, "C_RESOURCES_TRANSFER_RESOURCE");
3061            stmt.setString(1, createdUser.toString());
3062            stmt.setString(2, lastModifiedUser.toString());
3063            stmt.setString(3, resource.getResourceId().toString());
3064            stmt.executeUpdate();
3065        } catch (SQLException e) {
3066            throw new CmsDbSqlException(
3067                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
3068                e);
3069        } finally {
3070            m_sqlManager.closeAll(dbc, conn, stmt, null);
3071        }
3072    }
3073
3074    /**
3075     * @see org.opencms.db.I_CmsVfsDriver#updateRelations(CmsDbContext, CmsProject, CmsResource)
3076     */
3077    public void updateRelations(CmsDbContext dbc, CmsProject onlineProject, CmsResource offlineResource)
3078    throws CmsDataAccessException {
3079
3080        // delete online relations
3081        I_CmsVfsDriver vfsDriver = m_driverManager.getVfsDriver(dbc);
3082        vfsDriver.deleteRelations(dbc, onlineProject.getUuid(), offlineResource, CmsRelationFilter.TARGETS);
3083
3084        CmsUUID projectId;
3085        if (!dbc.getProjectId().isNullUUID()) {
3086            projectId = CmsProject.ONLINE_PROJECT_ID;
3087        } else {
3088            projectId = dbc.currentProject().getUuid();
3089        }
3090
3091        // copy offline to online relations
3092        CmsUUID dbcProjectId = dbc.getProjectId();
3093        dbc.setProjectId(CmsUUID.getNullUUID());
3094        Iterator<CmsRelation> itRelations = m_driverManager.getVfsDriver(
3095            dbc).readRelations(dbc, projectId, offlineResource, CmsRelationFilter.TARGETS).iterator();
3096        dbc.setProjectId(dbcProjectId);
3097        while (itRelations.hasNext()) {
3098            vfsDriver.createRelation(dbc, onlineProject.getUuid(), itRelations.next());
3099        }
3100    }
3101
3102    /**
3103     * @see org.opencms.db.I_CmsVfsDriver#validateResourceIdExists(org.opencms.db.CmsDbContext, CmsUUID, org.opencms.util.CmsUUID)
3104     */
3105    public boolean validateResourceIdExists(CmsDbContext dbc, CmsUUID projectId, CmsUUID resourceId)
3106    throws CmsDataAccessException {
3107
3108        Connection conn = null;
3109        PreparedStatement stmt = null;
3110        ResultSet res = null;
3111        boolean exists = false;
3112
3113        try {
3114            conn = m_sqlManager.getConnection(dbc);
3115            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RESOURCES_READ_RESOURCE_STATE");
3116            stmt.setString(1, resourceId.toString());
3117
3118            res = stmt.executeQuery();
3119            exists = res.next();
3120        } catch (SQLException e) {
3121            throw new CmsDbSqlException(
3122                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
3123                e);
3124        } finally {
3125            m_sqlManager.closeAll(dbc, conn, stmt, res);
3126        }
3127
3128        return exists;
3129    }
3130
3131    /**
3132     * @see org.opencms.db.I_CmsVfsDriver#validateStructureIdExists(org.opencms.db.CmsDbContext, CmsUUID, org.opencms.util.CmsUUID)
3133     */
3134    public boolean validateStructureIdExists(CmsDbContext dbc, CmsUUID projectId, CmsUUID structureId)
3135    throws CmsDataAccessException {
3136
3137        Connection conn = null;
3138        PreparedStatement stmt = null;
3139        ResultSet res = null;
3140        boolean found = false;
3141        int count = 0;
3142
3143        try {
3144            conn = m_sqlManager.getConnection(dbc);
3145            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RESOURCES_SELECT_STRUCTURE_ID");
3146            stmt.setString(1, structureId.toString());
3147
3148            res = stmt.executeQuery();
3149            if (res.next()) {
3150                count = res.getInt(1);
3151                found = (count == 1);
3152                while (res.next()) {
3153                    // do nothing only move through all rows because of mssql odbc driver
3154                }
3155            } else {
3156                found = false;
3157            }
3158        } catch (SQLException e) {
3159            throw new CmsDbSqlException(
3160                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
3161                e);
3162        } finally {
3163            m_sqlManager.closeAll(dbc, conn, stmt, res);
3164        }
3165
3166        return found;
3167    }
3168
3169    /**
3170     * @see org.opencms.db.I_CmsVfsDriver#writeContent(org.opencms.db.CmsDbContext, org.opencms.util.CmsUUID, byte[])
3171     */
3172    public void writeContent(CmsDbContext dbc, CmsUUID resourceId, byte[] content) throws CmsDataAccessException {
3173
3174        Connection conn = null;
3175        PreparedStatement stmt = null;
3176
3177        try {
3178            conn = m_sqlManager.getConnection(dbc);
3179            stmt = m_sqlManager.getPreparedStatement(conn, dbc.currentProject(), "C_OFFLINE_CONTENTS_UPDATE");
3180            // update the file content in the database.
3181            if (content.length < 2000) {
3182                stmt.setBytes(1, content);
3183            } else {
3184                stmt.setBinaryStream(1, new ByteArrayInputStream(content), content.length);
3185            }
3186            stmt.setString(2, resourceId.toString());
3187            stmt.executeUpdate();
3188        } catch (SQLException e) {
3189            throw new CmsDbSqlException(
3190                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
3191                e);
3192        } finally {
3193            m_sqlManager.closeAll(dbc, conn, stmt, null);
3194        }
3195    }
3196
3197    /**
3198     * @see org.opencms.db.I_CmsVfsDriver#writeLastModifiedProjectId(org.opencms.db.CmsDbContext, org.opencms.file.CmsProject, CmsUUID, org.opencms.file.CmsResource)
3199     */
3200    public void writeLastModifiedProjectId(
3201        CmsDbContext dbc,
3202        CmsProject project,
3203        CmsUUID projectId,
3204        CmsResource resource)
3205    throws CmsDataAccessException {
3206
3207        Connection conn = null;
3208        PreparedStatement stmt = null;
3209
3210        try {
3211            conn = m_sqlManager.getConnection(dbc);
3212            stmt = m_sqlManager.getPreparedStatement(conn, project, "C_RESOURCES_UPDATE_PROJECT_LASTMODIFIED");
3213            stmt.setString(1, projectId.toString());
3214            stmt.setString(2, resource.getResourceId().toString());
3215            stmt.executeUpdate();
3216        } catch (SQLException e) {
3217            throw new CmsDbSqlException(
3218                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
3219                e);
3220        } finally {
3221            m_sqlManager.closeAll(dbc, conn, stmt, null);
3222        }
3223    }
3224
3225    /**
3226     * @see org.opencms.db.I_CmsVfsDriver#writePropertyObject(org.opencms.db.CmsDbContext, org.opencms.file.CmsProject, org.opencms.file.CmsResource, org.opencms.file.CmsProperty)
3227     */
3228    public void writePropertyObject(CmsDbContext dbc, CmsProject project, CmsResource resource, CmsProperty property)
3229    throws CmsDataAccessException {
3230
3231        CmsUUID projectId = ((dbc.getProjectId() == null) || dbc.getProjectId().isNullUUID())
3232        ? project.getUuid()
3233        : dbc.getProjectId();
3234
3235        // TODO: check if we need autocreation for link property definition types too
3236        CmsPropertyDefinition propertyDefinition = null;
3237        try {
3238            // read the property definition
3239            propertyDefinition = readPropertyDefinition(dbc, property.getName(), projectId);
3240        } catch (CmsDbEntryNotFoundException e) {
3241            if (property.autoCreatePropertyDefinition()) {
3242                propertyDefinition = createPropertyDefinition(
3243                    dbc,
3244                    projectId,
3245                    property.getName(),
3246                    CmsPropertyDefinition.TYPE_NORMAL);
3247                try {
3248                    readPropertyDefinition(dbc, property.getName(), CmsProject.ONLINE_PROJECT_ID);
3249                } catch (CmsDataAccessException e1) {
3250                    createPropertyDefinition(
3251                        dbc,
3252                        CmsProject.ONLINE_PROJECT_ID,
3253                        property.getName(),
3254                        CmsPropertyDefinition.TYPE_NORMAL);
3255                }
3256                try {
3257                    m_driverManager.getHistoryDriver(dbc).readPropertyDefinition(dbc, property.getName());
3258                } catch (CmsDataAccessException e1) {
3259                    m_driverManager.getHistoryDriver(dbc).createPropertyDefinition(
3260                        dbc,
3261                        property.getName(),
3262                        CmsPropertyDefinition.TYPE_NORMAL);
3263                }
3264                OpenCms.fireCmsEvent(
3265                    new CmsEvent(
3266                        I_CmsEventListener.EVENT_PROPERTY_DEFINITION_CREATED,
3267                        Collections.<String, Object> singletonMap("propertyDefinition", propertyDefinition)));
3268
3269            } else {
3270                throw new CmsDbEntryNotFoundException(
3271                    Messages.get().container(Messages.ERR_NO_PROPERTYDEF_WITH_NAME_1, property.getName()));
3272            }
3273        }
3274
3275        PreparedStatement stmt = null;
3276        Connection conn = null;
3277
3278        try {
3279            // read the existing property to test if we need the
3280            // insert or update query to write a property value
3281            CmsProperty existingProperty = readPropertyObject(dbc, propertyDefinition.getName(), project, resource);
3282
3283            if (existingProperty.isIdentical(property)) {
3284                // property already has the identical values set, no write required
3285                return;
3286            }
3287
3288            conn = m_sqlManager.getConnection(dbc);
3289
3290            for (int i = 0; i < 2; i++) {
3291                int mappingType = -1;
3292                String value = null;
3293                CmsUUID id = null;
3294                boolean existsPropertyValue = false;
3295                boolean deletePropertyValue = false;
3296
3297                // 1) take any required decisions to choose and fill the correct SQL query
3298
3299                if (i == 0) {
3300                    // write/delete the *structure value* on the first cycle
3301                    if ((existingProperty.getStructureValue() != null) && property.isDeleteStructureValue()) {
3302                        // this property value is marked to be deleted
3303                        deletePropertyValue = true;
3304                    } else {
3305                        value = property.getStructureValue();
3306                        if (CmsStringUtil.isEmptyOrWhitespaceOnly(value)) {
3307                            // no structure value set or the structure value is an empty string,
3308                            // continue with the resource value
3309                            continue;
3310                        }
3311                    }
3312
3313                    // set the vars to be written to the database
3314                    mappingType = CmsProperty.STRUCTURE_RECORD_MAPPING;
3315                    id = resource.getStructureId();
3316                    existsPropertyValue = existingProperty.getStructureValue() != null;
3317                } else {
3318                    // write/delete the *resource value* on the second cycle
3319                    if ((existingProperty.getResourceValue() != null) && property.isDeleteResourceValue()) {
3320                        // this property value is marked to be deleted
3321                        deletePropertyValue = true;
3322                    } else {
3323                        value = property.getResourceValue();
3324                        if (CmsStringUtil.isEmptyOrWhitespaceOnly(value)) {
3325                            // no resource value set or the resource value is an empty string,
3326                            // break out of the loop
3327                            break;
3328                        }
3329                    }
3330
3331                    // set the vars to be written to the database
3332                    mappingType = CmsProperty.RESOURCE_RECORD_MAPPING;
3333                    id = resource.getResourceId();
3334                    existsPropertyValue = existingProperty.getResourceValue() != null;
3335                }
3336
3337                // 2) execute the SQL query
3338                try {
3339                    if (!deletePropertyValue) {
3340                        // insert/update the property value
3341                        if (existsPropertyValue) {
3342                            // {structure|resource} property value already exists- use update statement
3343                            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_PROPERTIES_UPDATE");
3344                            stmt.setString(1, m_sqlManager.validateEmpty(value));
3345                            stmt.setString(2, id.toString());
3346                            stmt.setInt(3, mappingType);
3347                            stmt.setString(4, propertyDefinition.getId().toString());
3348                        } else {
3349                            // {structure|resource} property value doesn't exist- use create statement
3350                            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_PROPERTIES_CREATE");
3351                            stmt.setString(1, new CmsUUID().toString());
3352                            stmt.setString(2, propertyDefinition.getId().toString());
3353                            stmt.setString(3, id.toString());
3354                            stmt.setInt(4, mappingType);
3355                            stmt.setString(5, m_sqlManager.validateEmpty(value));
3356                        }
3357                    } else {
3358                        // {structure|resource} property value marked as deleted- use delete statement
3359                        stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_PROPERTIES_DELETE");
3360                        stmt.setString(1, propertyDefinition.getId().toString());
3361                        stmt.setString(2, id.toString());
3362                        stmt.setInt(3, mappingType);
3363                    }
3364                    stmt.executeUpdate();
3365                } finally {
3366                    m_sqlManager.closeAll(dbc, null, stmt, null);
3367                }
3368            }
3369        } catch (SQLException e) {
3370            throw new CmsDbSqlException(
3371                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
3372                e);
3373        } finally {
3374            m_sqlManager.closeAll(dbc, conn, stmt, null);
3375        }
3376    }
3377
3378    /**
3379     * @see org.opencms.db.I_CmsVfsDriver#writePropertyObjects(org.opencms.db.CmsDbContext, org.opencms.file.CmsProject, org.opencms.file.CmsResource, java.util.List)
3380     */
3381    public void writePropertyObjects(
3382        CmsDbContext dbc,
3383        CmsProject project,
3384        CmsResource resource,
3385        List<CmsProperty> properties)
3386    throws CmsDataAccessException {
3387
3388        CmsProperty property = null;
3389
3390        for (int i = 0; i < properties.size(); i++) {
3391            property = properties.get(i);
3392            writePropertyObject(dbc, project, resource, property);
3393        }
3394    }
3395
3396    /**
3397     * @see org.opencms.db.I_CmsVfsDriver#writeResource(org.opencms.db.CmsDbContext, CmsUUID, org.opencms.file.CmsResource, int)
3398     */
3399    public void writeResource(CmsDbContext dbc, CmsUUID projectId, CmsResource resource, int changed)
3400    throws CmsDataAccessException {
3401
3402        // validate the resource length
3403        internalValidateResourceLength(resource);
3404
3405        String resourcePath = CmsFileUtil.removeTrailingSeparator(resource.getRootPath());
3406
3407        // this task is split into two statements because some DBs (e.g. Oracle) doesn't support multi-table updates
3408        PreparedStatement stmt = null;
3409        Connection conn = null;
3410        long resourceDateModified;
3411
3412        if (resource.isTouched()) {
3413            resourceDateModified = resource.getDateLastModified();
3414        } else {
3415            resourceDateModified = System.currentTimeMillis();
3416        }
3417
3418        CmsResourceState structureState = resource.getState();
3419        CmsResourceState resourceState = resource.getState();
3420        CmsResourceState structureStateOld = internalReadStructureState(dbc, projectId, resource);
3421        CmsResourceState resourceStateOld = internalReadResourceState(dbc, projectId, resource);
3422        CmsUUID projectLastModified = projectId;
3423
3424        if (changed == CmsDriverManager.UPDATE_RESOURCE_STATE) {
3425            resourceState = resourceStateOld;
3426            resourceState = (resourceState.isNew() ? CmsResource.STATE_NEW : CmsResource.STATE_CHANGED);
3427            structureState = structureStateOld;
3428        } else if (changed == CmsDriverManager.UPDATE_STRUCTURE_STATE) {
3429            structureState = structureStateOld;
3430            structureState = (structureState.isNew() ? CmsResource.STATE_NEW : CmsResource.STATE_CHANGED);
3431        } else if (changed == CmsDriverManager.NOTHING_CHANGED) {
3432            projectLastModified = resource.getProjectLastModified();
3433        } else {
3434            resourceState = resourceStateOld;
3435            resourceState = (resourceState.isNew() ? CmsResource.STATE_NEW : CmsResource.STATE_CHANGED);
3436            structureState = structureStateOld;
3437            structureState = (structureState.isNew() ? CmsResource.STATE_NEW : CmsResource.STATE_CHANGED);
3438        }
3439
3440        try {
3441
3442            // read the parent id
3443            String parentId = internalReadParentId(dbc, projectId, resourcePath);
3444            int sibCount = countSiblings(dbc, projectId, resource.getResourceId());
3445
3446            conn = m_sqlManager.getConnection(dbc);
3447
3448            if (changed != CmsDriverManager.UPDATE_STRUCTURE_STATE) {
3449                // if the resource was unchanged
3450                stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RESOURCES_UPDATE_RESOURCES");
3451                stmt.setInt(1, resource.getTypeId());
3452                stmt.setInt(2, resource.getFlags());
3453                stmt.setLong(3, resourceDateModified);
3454                stmt.setString(4, resource.getUserLastModified().toString());
3455                stmt.setInt(5, resourceState.getState());
3456                stmt.setInt(6, resource.getLength());
3457                stmt.setLong(7, resource.getDateContent());
3458                stmt.setString(8, projectLastModified.toString());
3459                stmt.setInt(9, sibCount);
3460                stmt.setString(10, resource.getResourceId().toString());
3461                stmt.executeUpdate();
3462                m_sqlManager.closeAll(dbc, null, stmt, null);
3463            } else {
3464                stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RESOURCES_UPDATE_RESOURCES_WITHOUT_STATE");
3465                stmt.setInt(1, resource.getTypeId());
3466                stmt.setInt(2, resource.getFlags());
3467                stmt.setLong(3, resourceDateModified);
3468                stmt.setString(4, resource.getUserLastModified().toString());
3469                stmt.setInt(5, resource.getLength());
3470                stmt.setLong(6, resource.getDateContent());
3471                stmt.setString(7, projectLastModified.toString());
3472                stmt.setInt(8, sibCount);
3473                stmt.setString(9, resource.getResourceId().toString());
3474                stmt.executeUpdate();
3475                m_sqlManager.closeAll(dbc, null, stmt, null);
3476            }
3477
3478            // update the structure
3479            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RESOURCES_UPDATE_STRUCTURE");
3480            stmt.setString(1, resource.getResourceId().toString());
3481            stmt.setString(2, resourcePath);
3482            stmt.setInt(3, structureState.getState());
3483            stmt.setLong(4, resource.getDateReleased());
3484            stmt.setLong(5, resource.getDateExpired());
3485            stmt.setString(6, parentId);
3486            stmt.setString(7, resource.getStructureId().toString());
3487            stmt.executeUpdate();
3488        } catch (SQLException e) {
3489            throw new CmsDbSqlException(
3490                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
3491                e);
3492        } finally {
3493            m_sqlManager.closeAll(dbc, conn, stmt, null);
3494        }
3495    }
3496
3497    /**
3498     * @see org.opencms.db.I_CmsVfsDriver#writeResourceState(org.opencms.db.CmsDbContext, org.opencms.file.CmsProject, org.opencms.file.CmsResource, int, boolean)
3499     */
3500    public void writeResourceState(
3501        CmsDbContext dbc,
3502        CmsProject project,
3503        CmsResource resource,
3504        int changed,
3505        boolean isPublishing)
3506    throws CmsDataAccessException {
3507
3508        PreparedStatement stmt = null;
3509        Connection conn = null;
3510
3511        if (project.getUuid().equals(CmsProject.ONLINE_PROJECT_ID)) {
3512            return;
3513        }
3514
3515        try {
3516            conn = m_sqlManager.getConnection(dbc);
3517
3518            if (changed == CmsDriverManager.UPDATE_RESOURCE_PROJECT) {
3519                stmt = m_sqlManager.getPreparedStatement(conn, project, "C_RESOURCES_UPDATE_RESOURCE_PROJECT");
3520                stmt.setInt(1, resource.getFlags());
3521                stmt.setString(2, project.getUuid().toString());
3522                stmt.setString(3, resource.getResourceId().toString());
3523                stmt.executeUpdate();
3524                m_sqlManager.closeAll(dbc, null, stmt, null);
3525            }
3526
3527            if (changed == CmsDriverManager.UPDATE_RESOURCE) {
3528                stmt = m_sqlManager.getPreparedStatement(
3529                    conn,
3530                    project,
3531                    "C_RESOURCES_UPDATE_RESOURCE_STATELASTMODIFIED");
3532                stmt.setInt(1, resource.getState().getState());
3533                stmt.setLong(2, resource.getDateLastModified());
3534                stmt.setString(3, resource.getUserLastModified().toString());
3535                stmt.setString(4, project.getUuid().toString());
3536                stmt.setString(5, resource.getResourceId().toString());
3537                stmt.executeUpdate();
3538                m_sqlManager.closeAll(dbc, null, stmt, null);
3539            }
3540
3541            if ((changed == CmsDriverManager.UPDATE_RESOURCE_STATE) || (changed == CmsDriverManager.UPDATE_ALL)) {
3542                stmt = m_sqlManager.getPreparedStatement(conn, project, "C_RESOURCES_UPDATE_RESOURCE_STATE");
3543                stmt.setInt(1, resource.getState().getState());
3544                stmt.setString(2, project.getUuid().toString());
3545                stmt.setString(3, resource.getResourceId().toString());
3546                stmt.executeUpdate();
3547                m_sqlManager.closeAll(dbc, null, stmt, null);
3548            }
3549
3550            if ((changed == CmsDriverManager.UPDATE_STRUCTURE)
3551                || (changed == CmsDriverManager.UPDATE_ALL)
3552                || (changed == CmsDriverManager.UPDATE_STRUCTURE_STATE)) {
3553                stmt = m_sqlManager.getPreparedStatement(conn, project, "C_RESOURCES_UPDATE_STRUCTURE_STATE");
3554                stmt.setInt(1, resource.getState().getState());
3555                stmt.setString(2, resource.getStructureId().toString());
3556                stmt.executeUpdate();
3557                m_sqlManager.closeAll(dbc, null, stmt, null);
3558            }
3559
3560            if ((changed == CmsDriverManager.UPDATE_STRUCTURE) || (changed == CmsDriverManager.UPDATE_ALL)) {
3561                stmt = m_sqlManager.getPreparedStatement(conn, project, "C_RESOURCES_UPDATE_RELEASE_EXPIRED");
3562                stmt.setLong(1, resource.getDateReleased());
3563                stmt.setLong(2, resource.getDateExpired());
3564                stmt.setString(3, resource.getStructureId().toString());
3565                stmt.executeUpdate();
3566                m_sqlManager.closeAll(dbc, null, stmt, null);
3567            }
3568        } catch (SQLException e) {
3569            throw new CmsDbSqlException(
3570                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
3571                e);
3572        } finally {
3573            m_sqlManager.closeAll(dbc, conn, stmt, null);
3574        }
3575
3576        if (isPublishing) {
3577            internalUpdateVersions(dbc, resource);
3578        }
3579    }
3580
3581    /**
3582     * Checks that the current user has write permissions for all subresources of the given folder.<p>
3583     *
3584     * @param dbc the current database context
3585     * @param folder the folder to check
3586     *
3587     * @throws CmsDataAccessException if something goes wrong
3588     */
3589    protected void checkWritePermissionsInFolder(CmsDbContext dbc, CmsResource folder) throws CmsDataAccessException {
3590
3591        ResultSet res = null;
3592        PreparedStatement stmt = null;
3593        Connection conn = null;
3594
3595        CmsUUID projectId = dbc.getRequestContext().getCurrentProject().getUuid();
3596
3597        // first read all subresources with ACEs
3598        List<CmsResource> resources = new ArrayList<CmsResource>();
3599        try {
3600            conn = m_sqlManager.getConnection(dbc);
3601            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RESOURCES_READ_WITH_ACE_1");
3602            stmt.setString(1, folder.getRootPath() + "%");
3603            res = stmt.executeQuery();
3604
3605            while (res.next()) {
3606                resources.add(createResource(res, projectId));
3607            }
3608        } catch (SQLException e) {
3609            throw new CmsDbSqlException(
3610                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
3611                e);
3612        } finally {
3613            m_sqlManager.closeAll(dbc, conn, stmt, res);
3614        }
3615
3616        // check current user write permission for each of these resources
3617        Iterator<CmsResource> itResources = resources.iterator();
3618        while (itResources.hasNext()) {
3619            CmsResource resource = itResources.next();
3620            try {
3621                m_driverManager.getSecurityManager().checkPermissions(
3622                    dbc.getRequestContext(),
3623                    resource,
3624                    CmsPermissionSet.ACCESS_WRITE,
3625                    false,
3626                    CmsResourceFilter.ALL);
3627            } catch (CmsException e) {
3628                throw new CmsDataAccessException(e.getMessageContainer(), e);
3629            }
3630        }
3631
3632        // then check for possible jsp pages without permissions
3633        CmsResourceFilter filter = CmsResourceFilter.ALL;
3634        itResources = readTypesInResourceTree(
3635            dbc,
3636            projectId,
3637            folder.getRootPath(),
3638            CmsResourceTypeJsp.getJspResourceTypeIds(),
3639            filter.getState(),
3640            filter.getModifiedAfter(),
3641            filter.getModifiedBefore(),
3642            filter.getReleaseAfter(),
3643            filter.getReleaseBefore(),
3644            filter.getExpireAfter(),
3645            filter.getExpireBefore(),
3646            CmsDriverManager.READMODE_INCLUDE_TREE).iterator();
3647        while (itResources.hasNext()) {
3648            CmsResource resource = itResources.next();
3649            try {
3650                m_driverManager.getSecurityManager().checkPermissions(
3651                    dbc.getRequestContext(),
3652                    resource,
3653                    CmsPermissionSet.ACCESS_WRITE,
3654                    false,
3655                    CmsResourceFilter.ALL);
3656            } catch (CmsException e) {
3657                throw new CmsDataAccessException(e.getMessageContainer(), e);
3658            }
3659        }
3660    }
3661
3662    /**
3663     * Returns the count of properties for a property definition.<p>
3664     *
3665     * @param dbc the current database context
3666     * @param propertyDefinition the property definition to test
3667     * @param projectId the ID of the current project
3668     *
3669     * @return the amount of properties for a property definition
3670     * @throws CmsDataAccessException if something goes wrong
3671     */
3672    protected int internalCountProperties(CmsDbContext dbc, CmsPropertyDefinition propertyDefinition, CmsUUID projectId)
3673    throws CmsDataAccessException {
3674
3675        ResultSet res = null;
3676        PreparedStatement stmt = null;
3677        Connection conn = null;
3678        int count = 0;
3679
3680        try {
3681            // create statement
3682            conn = m_sqlManager.getConnection(dbc);
3683            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_PROPERTIES_READALL_COUNT");
3684            stmt.setString(1, propertyDefinition.getId().toString());
3685            res = stmt.executeQuery();
3686
3687            if (res.next()) {
3688                count = res.getInt(1);
3689                while (res.next()) {
3690                    // do nothing only move through all rows because of mssql odbc driver
3691                }
3692            } else {
3693                throw new CmsDbConsistencyException(
3694                    Messages.get().container(Messages.ERR_COUNTING_PROPERTIES_1, propertyDefinition.getName()));
3695            }
3696        } catch (SQLException e) {
3697            throw new CmsDbSqlException(
3698                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
3699                e);
3700        } finally {
3701            m_sqlManager.closeAll(dbc, conn, stmt, res);
3702        }
3703
3704        return count;
3705    }
3706
3707    /**
3708     * Creates a new counter.<p>
3709     *
3710     * @param dbc the database context
3711     * @param name the name of the counter to create
3712     * @param value the inital value of the counter
3713     *
3714     * @throws CmsDbSqlException if something goes wrong
3715     */
3716    protected void internalCreateCounter(CmsDbContext dbc, String name, int value) throws CmsDbSqlException {
3717
3718        PreparedStatement stmt = null;
3719        Connection conn = null;
3720        try {
3721            conn = m_sqlManager.getConnection(dbc);
3722            stmt = m_sqlManager.getPreparedStatement(conn, CmsProject.ONLINE_PROJECT_ID, "C_CREATE_COUNTER");
3723            stmt.setString(1, name);
3724            stmt.setInt(2, value);
3725            stmt.executeUpdate();
3726        } catch (SQLException e) {
3727            throw wrapException(stmt, e);
3728        } finally {
3729            m_sqlManager.closeAll(dbc, conn, stmt, null);
3730        }
3731    }
3732
3733    /**
3734     * Creates an URL name mapping entry from a result set.<p>
3735     *
3736     * @param resultSet a result set
3737     * @return the URL name mapping entry created from the result set
3738     *
3739     * @throws SQLException if something goes wrong
3740     */
3741    protected CmsUrlNameMappingEntry internalCreateUrlNameMappingEntry(ResultSet resultSet) throws SQLException {
3742
3743        String name = resultSet.getString(1);
3744        CmsUUID structureId = new CmsUUID(resultSet.getString(2));
3745        int state = resultSet.getInt(3);
3746        long dateChanged = resultSet.getLong(4);
3747        String locale = resultSet.getString(5);
3748        return new CmsUrlNameMappingEntry(name, structureId, state, dateChanged, locale);
3749    }
3750
3751    /**
3752     * Increments a counter.<p>
3753     *
3754     * @param dbc the current db context
3755     * @param name the name of the counter which should be incremented
3756     *
3757     * @throws CmsDbSqlException if something goes wrong
3758     */
3759    protected void internalIncrementCounter(CmsDbContext dbc, String name) throws CmsDbSqlException {
3760
3761        PreparedStatement stmt = null;
3762        Connection conn = null;
3763        ResultSet resultSet = null;
3764        try {
3765            conn = m_sqlManager.getConnection(dbc);
3766            stmt = m_sqlManager.getPreparedStatement(conn, CmsProject.ONLINE_PROJECT_ID, "C_INCREMENT_COUNTER");
3767            stmt.setString(1, name);
3768            stmt.executeUpdate();
3769
3770        } catch (SQLException e) {
3771            throw wrapException(stmt, e);
3772        } finally {
3773            m_sqlManager.closeAll(dbc, conn, stmt, resultSet);
3774        }
3775    }
3776
3777    /**
3778     * Helper method to create an alias object from a result set.<p>
3779     *
3780     * @param resultset the result set
3781     * @return the alias object created from the result set
3782     *
3783     * @throws SQLException if something goes wrong
3784     */
3785    protected CmsAlias internalReadAlias(ResultSet resultset) throws SQLException {
3786
3787        String siteRoot = resultset.getString(1);
3788        String path = resultset.getString(2);
3789        int mode = resultset.getInt(3);
3790        String structId = resultset.getString(4);
3791        return new CmsAlias(new CmsUUID(structId), siteRoot, path, CmsAliasMode.fromInt(mode));
3792    }
3793
3794    /**
3795     * Reads the current value of a counter.<p>
3796     *
3797     * @param dbc the database context
3798     * @param name the name of the counter
3799     * @return the current value of the  counter, or null if the counter was not found
3800     *
3801     * @throws CmsDbSqlException if something goes wrong
3802     */
3803    protected Integer internalReadCounter(CmsDbContext dbc, String name) throws CmsDbSqlException {
3804
3805        PreparedStatement stmt = null;
3806        Connection conn = null;
3807        ResultSet resultSet = null;
3808        try {
3809            conn = m_sqlManager.getConnection(dbc);
3810            stmt = m_sqlManager.getPreparedStatement(conn, CmsProject.ONLINE_PROJECT_ID, "C_READ_COUNTER");
3811            stmt.setString(1, name);
3812            resultSet = stmt.executeQuery();
3813            Integer result = null;
3814            if (resultSet.next()) {
3815                int counter = resultSet.getInt(1);
3816                result = new Integer(counter);
3817                while (resultSet.next()) {
3818                    // for MSSQL
3819                }
3820            }
3821            return result;
3822        } catch (SQLException e) {
3823            throw wrapException(stmt, e);
3824        } finally {
3825            m_sqlManager.closeAll(dbc, conn, stmt, resultSet);
3826        }
3827    }
3828
3829    /**
3830     * Returns the parent id of the given resource.<p>
3831     *
3832     * @param dbc the current database context
3833     * @param projectId the current project id
3834     * @param resourcename the resource name to read the parent id for
3835     *
3836     * @return  the parent id of the given resource
3837     *
3838     * @throws CmsDataAccessException if something goes wrong
3839     */
3840    protected String internalReadParentId(CmsDbContext dbc, CmsUUID projectId, String resourcename)
3841    throws CmsDataAccessException {
3842
3843        if ("/".equalsIgnoreCase(resourcename)) {
3844            return CmsUUID.getNullUUID().toString();
3845        }
3846
3847        String parent = CmsResource.getParentFolder(resourcename);
3848        parent = CmsFileUtil.removeTrailingSeparator(parent);
3849
3850        ResultSet res = null;
3851        PreparedStatement stmt = null;
3852        Connection conn = null;
3853        String parentId = null;
3854
3855        try {
3856            conn = m_sqlManager.getConnection(dbc);
3857            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RESOURCES_READ_PARENT_STRUCTURE_ID");
3858            stmt.setString(1, parent);
3859            res = stmt.executeQuery();
3860
3861            if (res.next()) {
3862                parentId = res.getString(1);
3863                while (res.next()) {
3864                    // do nothing only move through all rows because of mssql odbc driver
3865                }
3866            } else {
3867                throw new CmsVfsResourceNotFoundException(
3868                    Messages.get().container(Messages.ERR_READ_PARENT_ID_1, dbc.removeSiteRoot(resourcename)));
3869            }
3870        } catch (SQLException e) {
3871            throw new CmsDbSqlException(
3872                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
3873                e);
3874        } finally {
3875            m_sqlManager.closeAll(dbc, conn, stmt, res);
3876        }
3877
3878        return parentId;
3879    }
3880
3881    /**
3882     * Creates a new {@link CmsRelation} object from the given result set entry.<p>
3883     *
3884     * @param res the result set
3885     *
3886     * @return the new {@link CmsRelation} object
3887     *
3888     * @throws SQLException if something goes wrong
3889     */
3890    protected CmsRelation internalReadRelation(ResultSet res) throws SQLException {
3891
3892        CmsUUID sourceId = new CmsUUID(res.getString(m_sqlManager.readQuery("C_RELATION_SOURCE_ID")));
3893        String sourcePath = res.getString(m_sqlManager.readQuery("C_RELATION_SOURCE_PATH"));
3894        CmsUUID targetId = new CmsUUID(res.getString(m_sqlManager.readQuery("C_RELATION_TARGET_ID")));
3895        String targetPath = res.getString(m_sqlManager.readQuery("C_RELATION_TARGET_PATH"));
3896        int type = res.getInt(m_sqlManager.readQuery("C_RELATION_TYPE"));
3897        return new CmsRelation(sourceId, sourcePath, targetId, targetPath, CmsRelationType.valueOf(type));
3898    }
3899
3900    /**
3901     * Returns the resource state of the given resource.<p>
3902     *
3903     * @param dbc the database context
3904     * @param projectId the id of the project
3905     * @param resource the resource to read the resource state for
3906     *
3907     * @return the resource state of the given resource
3908     *
3909     * @throws CmsDbSqlException if something goes wrong
3910     */
3911    protected CmsResourceState internalReadResourceState(CmsDbContext dbc, CmsUUID projectId, CmsResource resource)
3912    throws CmsDbSqlException {
3913
3914        CmsResourceState state = CmsResource.STATE_KEEP;
3915
3916        Connection conn = null;
3917        PreparedStatement stmt = null;
3918        ResultSet res = null;
3919        try {
3920            conn = m_sqlManager.getConnection(dbc);
3921            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_READ_RESOURCE_STATE");
3922            stmt.setString(1, resource.getResourceId().toString());
3923            res = stmt.executeQuery();
3924            if (res.next()) {
3925                state = CmsResourceState.valueOf(res.getInt(m_sqlManager.readQuery("C_RESOURCES_STATE")));
3926                while (res.next()) {
3927                    // do nothing only move through all rows because of mssql odbc driver
3928                }
3929            }
3930        } catch (SQLException e) {
3931            throw new CmsDbSqlException(
3932                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
3933                e);
3934        } finally {
3935            m_sqlManager.closeAll(dbc, conn, stmt, res);
3936        }
3937        return state;
3938    }
3939
3940    /**
3941     * Returns the structure state of the given resource.<p>
3942     *
3943     * @param dbc the database context
3944     * @param projectId the id of the project
3945     * @param resource the resource to read the structure state for
3946     *
3947     * @return the structure state of the given resource
3948     *
3949     * @throws CmsDbSqlException if something goes wrong
3950     */
3951    protected CmsResourceState internalReadStructureState(CmsDbContext dbc, CmsUUID projectId, CmsResource resource)
3952    throws CmsDbSqlException {
3953
3954        CmsResourceState state = CmsResource.STATE_KEEP;
3955
3956        Connection conn = null;
3957        PreparedStatement stmt = null;
3958        ResultSet res = null;
3959        try {
3960            conn = m_sqlManager.getConnection(dbc);
3961            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_READ_STRUCTURE_STATE");
3962            stmt.setString(1, resource.getStructureId().toString());
3963            res = stmt.executeQuery();
3964            if (res.next()) {
3965                state = CmsResourceState.valueOf(res.getInt(m_sqlManager.readQuery("C_RESOURCES_STRUCTURE_STATE")));
3966                while (res.next()) {
3967                    // do nothing only move through all rows because of mssql odbc driver
3968                }
3969            }
3970        } catch (SQLException e) {
3971            throw new CmsDbSqlException(
3972                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
3973                e);
3974        } finally {
3975            m_sqlManager.closeAll(dbc, conn, stmt, res);
3976        }
3977        return state;
3978    }
3979
3980    /**
3981     * Removes a resource physically in the database.<p>
3982     *
3983     * @param dbc the current database context
3984     * @param currentProject the current project
3985     * @param resource the folder to remove
3986     *
3987     * @throws CmsDataAccessException if something goes wrong
3988     */
3989    protected void internalRemoveFolder(CmsDbContext dbc, CmsProject currentProject, CmsResource resource)
3990    throws CmsDataAccessException {
3991
3992        PreparedStatement stmt = null;
3993        Connection conn = null;
3994
3995        try {
3996            conn = m_sqlManager.getConnection(dbc);
3997
3998            // delete the structure record
3999            stmt = m_sqlManager.getPreparedStatement(conn, currentProject, "C_STRUCTURE_DELETE_BY_STRUCTUREID");
4000            stmt.setString(1, resource.getStructureId().toString());
4001            stmt.executeUpdate();
4002
4003            m_sqlManager.closeAll(dbc, null, stmt, null);
4004
4005            // delete the resource record
4006            stmt = m_sqlManager.getPreparedStatement(conn, currentProject, "C_RESOURCES_DELETE_BY_RESOURCEID");
4007            stmt.setString(1, resource.getResourceId().toString());
4008            stmt.executeUpdate();
4009        } catch (SQLException e) {
4010            throw new CmsDbSqlException(
4011                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
4012                e);
4013        } finally {
4014            m_sqlManager.closeAll(dbc, conn, stmt, null);
4015        }
4016    }
4017
4018    /**
4019     * Updates the offline version numbers.<p>
4020     *
4021     * @param dbc the current database context
4022     * @param resource the resource to update the version number for
4023     *
4024     * @throws CmsDataAccessException if something goes wrong
4025     */
4026    protected void internalUpdateVersions(CmsDbContext dbc, CmsResource resource) throws CmsDataAccessException {
4027
4028        if (dbc.getRequestContext() == null) {
4029            // no needed during initialization
4030            return;
4031        }
4032        if (dbc.currentProject().isOnlineProject()) {
4033            // this method is supposed to be used only in the offline project
4034            return;
4035        }
4036
4037        // read the online version numbers
4038        Map<String, Integer> onlineVersions = readVersions(
4039            dbc,
4040            CmsProject.ONLINE_PROJECT_ID,
4041            resource.getResourceId(),
4042            resource.getStructureId());
4043        int onlineStructureVersion = onlineVersions.get("structure").intValue();
4044        int onlineResourceVersion = onlineVersions.get("resource").intValue();
4045
4046        Connection conn = null;
4047        PreparedStatement stmt = null;
4048        ResultSet res = null;
4049
4050        try {
4051            conn = m_sqlManager.getConnection(dbc);
4052
4053            // update the resource version
4054            stmt = m_sqlManager.getPreparedStatement(conn, dbc.currentProject(), "C_RESOURCES_UPDATE_RESOURCE_VERSION");
4055            stmt.setInt(1, onlineResourceVersion);
4056            stmt.setString(2, resource.getResourceId().toString());
4057            stmt.executeUpdate();
4058            m_sqlManager.closeAll(dbc, null, stmt, null);
4059
4060            // update the structure version
4061            stmt = m_sqlManager.getPreparedStatement(
4062                conn,
4063                dbc.currentProject(),
4064                "C_RESOURCES_UPDATE_STRUCTURE_VERSION");
4065            stmt.setInt(1, onlineStructureVersion);
4066            stmt.setString(2, resource.getStructureId().toString());
4067            stmt.executeUpdate();
4068            m_sqlManager.closeAll(dbc, null, stmt, null);
4069
4070        } catch (SQLException e) {
4071            throw new CmsDbSqlException(
4072                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
4073                e);
4074        } finally {
4075            m_sqlManager.closeAll(dbc, conn, stmt, res);
4076        }
4077    }
4078
4079    /**
4080     * Validates that the length setting of a resource is always correct.<p>
4081     *
4082     * Files need to have a resource length of >= 0, while folders require
4083     * a resource length of -1.<p>
4084     *
4085     * @param resource the resource to check the length for
4086     * @throws CmsDataAccessException if the resource length is not correct
4087     */
4088    protected void internalValidateResourceLength(CmsResource resource) throws CmsDataAccessException {
4089
4090        if (resource.isFolder() && (resource.getLength() == -1)) {
4091            return;
4092        }
4093
4094        if (resource.isFile() && (resource.getLength() >= 0)) {
4095            return;
4096        }
4097
4098        throw new CmsDataAccessException(
4099            Messages.get().container(
4100                Messages.ERR_INVALID_RESOURCE_LENGTH_2,
4101                new Integer(resource.getLength()),
4102                resource.getRootPath()));
4103    }
4104
4105    /**
4106     * Moves all relations of a resource to the new path.<p>
4107     *
4108     * @param dbc the current database context
4109     * @param projectId the id of the project to apply the changes
4110     * @param structureId the structure id of the resource to apply the changes to
4111     * @param rootPath the new root path
4112     *
4113     * @throws CmsDataAccessException if something goes wrong
4114     */
4115    protected void moveRelations(CmsDbContext dbc, CmsUUID projectId, CmsUUID structureId, String rootPath)
4116    throws CmsDataAccessException {
4117
4118        Connection conn = null;
4119        PreparedStatement stmt = null;
4120
4121        try {
4122            conn = m_sqlManager.getConnection(dbc);
4123            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_MOVE_RELATIONS_SOURCE");
4124            stmt.setString(1, rootPath);
4125            stmt.setString(2, structureId.toString());
4126
4127            stmt.executeUpdate();
4128            m_sqlManager.closeAll(dbc, null, stmt, null);
4129
4130            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_MOVE_RELATIONS_TARGET");
4131            stmt.setString(1, rootPath);
4132            stmt.setString(2, structureId.toString());
4133
4134            stmt.executeUpdate();
4135        } catch (SQLException e) {
4136            throw new CmsDbSqlException(
4137                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
4138                e);
4139        } finally {
4140            m_sqlManager.closeAll(dbc, conn, stmt, null);
4141        }
4142    }
4143
4144    /**
4145     * Appends the appropriate selection criteria related with the expiration date.<p>
4146     *
4147     * @param projectId the id of the project of the resources
4148     * @param startTime the start time
4149     * @param endTime the end time
4150     * @param conditions buffer to append the selection criteria
4151     * @param params list to append the selection parameters
4152     */
4153    protected void prepareExpiredTimeRangeCondition(
4154        CmsUUID projectId,
4155        long startTime,
4156        long endTime,
4157        StringBuffer conditions,
4158        List<Object> params) {
4159
4160        if (startTime > 0L) {
4161            // READ_IGNORE_TIME: if NOT set, add condition to match expired date against startTime
4162            conditions.append(BEGIN_INCLUDE_CONDITION);
4163            conditions.append(m_sqlManager.readQuery(projectId, "C_STRUCTURE_SELECT_BY_DATE_EXPIRED_AFTER"));
4164            conditions.append(END_CONDITION);
4165            params.add(new Long(startTime));
4166        }
4167
4168        if (endTime > 0L) {
4169            // READ_IGNORE_TIME: if NOT set, add condition to match expired date against endTime
4170            conditions.append(BEGIN_INCLUDE_CONDITION);
4171            conditions.append(m_sqlManager.readQuery(projectId, "C_STRUCTURE_SELECT_BY_DATE_EXPIRED_BEFORE"));
4172            conditions.append(END_CONDITION);
4173            params.add(new Long(endTime));
4174        }
4175    }
4176
4177    /**
4178     * Appends the appropriate selection criteria related with the parentPath.<p>
4179     *
4180     * @param projectId the id of the project of the resources
4181     * @param parent the parent path or UUID (if mode is C_READMODE_EXCLUDE_TREE)
4182     * @param mode the selection mode
4183     * @param conditions buffer to append the selection criteria
4184     * @param params list to append the selection parameters
4185     */
4186    protected void preparePathCondition(
4187        CmsUUID projectId,
4188        String parent,
4189        int mode,
4190        StringBuffer conditions,
4191        List<Object> params) {
4192
4193        if (parent == CmsDriverManager.READ_IGNORE_PARENT) {
4194            // parent can be ignored
4195            return;
4196        }
4197
4198        if ((mode & CmsDriverManager.READMODE_EXCLUDE_TREE) > 0) {
4199            // only return immediate children - use UUID optimization
4200            conditions.append(BEGIN_INCLUDE_CONDITION);
4201            conditions.append(m_sqlManager.readQuery(projectId, "C_RESOURCES_SELECT_BY_PARENT_UUID"));
4202            conditions.append(END_CONDITION);
4203            params.add(parent);
4204            return;
4205        }
4206
4207        if ("/".equalsIgnoreCase(parent)) {
4208            // if root folder is parent, no additional condition is needed since all resources match anyway
4209            return;
4210        }
4211
4212        // add condition to read path subtree
4213        conditions.append(BEGIN_INCLUDE_CONDITION);
4214        conditions.append(m_sqlManager.readQuery(projectId, "C_RESOURCES_SELECT_BY_PATH_PREFIX"));
4215        conditions.append(END_CONDITION);
4216        params.add(CmsFileUtil.addTrailingSeparator(escapeDbWildcard(parent)) + "%");
4217    }
4218
4219    /**
4220     * Appends the appropriate selection criteria related with the projectId.<p>
4221     *
4222     * @param projectId the id of the project of the resources
4223     * @param mode the selection mode
4224     * @param conditions buffer to append the selection criteria
4225     * @param params list to append the selection parameters
4226     */
4227    protected void prepareProjectCondition(CmsUUID projectId, int mode, StringBuffer conditions, List<Object> params) {
4228
4229        if ((mode & CmsDriverManager.READMODE_INCLUDE_PROJECT) > 0) {
4230            // C_READMODE_INCLUDE_PROJECT: add condition to match the PROJECT_ID
4231            conditions.append(BEGIN_INCLUDE_CONDITION);
4232            conditions.append(m_sqlManager.readQuery(projectId, "C_RESOURCES_SELECT_BY_PROJECT_LASTMODIFIED"));
4233            conditions.append(END_CONDITION);
4234            params.add(projectId.toString());
4235        }
4236    }
4237
4238    /**
4239     * Build the whole WHERE sql statement part for the given relation filter.<p>
4240     *
4241     * @param projectId the current project id
4242     * @param filter the filter
4243     * @param resource the resource (may be null, if you want to delete all relations for the resource in the filter)
4244     * @param params the parameter values (return parameter)
4245     * @param checkSource if the query is for the source relations
4246     *
4247     * @return the WHERE sql statement part string
4248     */
4249    protected String prepareRelationConditions(
4250        CmsUUID projectId,
4251        CmsRelationFilter filter,
4252        CmsResource resource,
4253        List<Object> params,
4254        boolean checkSource) {
4255
4256        StringBuffer conditions = new StringBuffer(128);
4257        params.clear(); // be sure the parameters list is clear
4258
4259        // source or target filter
4260        if (filter.isSource() || filter.isTarget()) {
4261            // source or target id filter from resource
4262            if (resource != null) {
4263                conditions.append(BEGIN_CONDITION);
4264                if (filter.isSource() && checkSource) {
4265                    if (!filter.isIncludeSubresources()) {
4266                        conditions.append(m_sqlManager.readQuery(projectId, "C_RELATION_FILTER_TARGET_ID"));
4267                        params.add(resource.getStructureId().toString());
4268                    } else {
4269                        conditions.append(m_sqlManager.readQuery(projectId, "C_RELATION_FILTER_TARGET_PATH"));
4270                        params.add(resource.getRootPath() + '%');
4271                    }
4272                } else if (filter.isTarget() && !checkSource) {
4273                    if (!filter.isIncludeSubresources()) {
4274                        conditions.append(m_sqlManager.readQuery(projectId, "C_RELATION_FILTER_SOURCE_ID"));
4275                        params.add(resource.getStructureId().toString());
4276                    } else {
4277                        conditions.append(m_sqlManager.readQuery(projectId, "C_RELATION_FILTER_SOURCE_PATH"));
4278                        params.add(resource.getRootPath() + '%');
4279                    }
4280                }
4281                conditions.append(END_CONDITION);
4282            }
4283
4284            // target or source id filter from filter parameter
4285            if (filter.getStructureId() != null) {
4286                if (conditions.length() == 0) {
4287                    conditions.append(BEGIN_CONDITION);
4288                } else {
4289                    conditions.append(BEGIN_INCLUDE_CONDITION);
4290                }
4291
4292                if (filter.isSource() && checkSource) {
4293                    conditions.append(m_sqlManager.readQuery(projectId, "C_RELATION_FILTER_SOURCE_ID"));
4294                    params.add(filter.getStructureId().toString());
4295                } else if (filter.isTarget() && !checkSource) {
4296                    conditions.append(m_sqlManager.readQuery(projectId, "C_RELATION_FILTER_TARGET_ID"));
4297                    params.add(filter.getStructureId().toString());
4298                }
4299                conditions.append(END_CONDITION);
4300            }
4301
4302            // target or source path filter from filter parameter
4303            if (CmsStringUtil.isNotEmptyOrWhitespaceOnly(filter.getPath())) {
4304                if (conditions.length() == 0) {
4305                    conditions.append(BEGIN_CONDITION);
4306                } else {
4307                    conditions.append(BEGIN_INCLUDE_CONDITION);
4308                }
4309
4310                String queryPath = filter.getPath();
4311                if (filter.isIncludeSubresources()) {
4312                    queryPath += '%';
4313                }
4314                if (filter.isSource() && checkSource) {
4315                    conditions.append(m_sqlManager.readQuery(projectId, "C_RELATION_FILTER_SOURCE_PATH"));
4316                    params.add(queryPath);
4317                } else if (filter.isTarget() && !checkSource) {
4318                    conditions.append(m_sqlManager.readQuery(projectId, "C_RELATION_FILTER_TARGET_PATH"));
4319                    params.add(queryPath);
4320                }
4321                conditions.append(END_CONDITION);
4322            }
4323        }
4324
4325        // relation type filter
4326        Set<CmsRelationType> types = filter.getTypes();
4327        if (!types.isEmpty()) {
4328            if (conditions.length() == 0) {
4329                conditions.append(BEGIN_CONDITION);
4330            } else {
4331                conditions.append(BEGIN_INCLUDE_CONDITION);
4332            }
4333            conditions.append(m_sqlManager.readQuery(projectId, "C_RELATION_FILTER_TYPE"));
4334            conditions.append(BEGIN_CONDITION);
4335            Iterator<CmsRelationType> it = types.iterator();
4336            while (it.hasNext()) {
4337                CmsRelationType type = it.next();
4338                conditions.append("?");
4339                params.add(new Integer(type.getId()));
4340                if (it.hasNext()) {
4341                    conditions.append(", ");
4342                }
4343            }
4344            conditions.append(END_CONDITION);
4345            conditions.append(END_CONDITION);
4346        }
4347        return conditions.toString();
4348    }
4349
4350    /**
4351     * Appends the appropriate selection criteria related with the released date.<p>
4352     *
4353     * @param projectId the id of the project
4354     * @param startTime the start time
4355     * @param endTime the stop time
4356     * @param conditions buffer to append the selection criteria
4357     * @param params list to append the selection parameters
4358     */
4359    protected void prepareReleasedTimeRangeCondition(
4360        CmsUUID projectId,
4361        long startTime,
4362        long endTime,
4363        StringBuffer conditions,
4364        List<Object> params) {
4365
4366        if (startTime > 0L) {
4367            // READ_IGNORE_TIME: if NOT set, add condition to match released date against startTime
4368            conditions.append(BEGIN_INCLUDE_CONDITION);
4369            conditions.append(m_sqlManager.readQuery(projectId, "C_STRUCTURE_SELECT_BY_DATE_RELEASED_AFTER"));
4370            conditions.append(END_CONDITION);
4371            params.add(new Long(startTime));
4372        }
4373
4374        if (endTime > 0L) {
4375            // READ_IGNORE_TIME: if NOT set, add condition to match released date against endTime
4376            conditions.append(BEGIN_INCLUDE_CONDITION);
4377            conditions.append(m_sqlManager.readQuery(projectId, "C_STRUCTURE_SELECT_BY_DATE_RELEASED_BEFORE"));
4378            conditions.append(END_CONDITION);
4379            params.add(new Long(endTime));
4380        }
4381    }
4382
4383    /**
4384     * Appends the appropriate selection criteria related with the read mode.<p>
4385     *
4386     * @param projectId the id of the project of the resources
4387     * @param mode the selection mode
4388     * @param conditions buffer to append the selection criteria
4389     */
4390    protected void prepareResourceCondition(CmsUUID projectId, int mode, StringBuffer conditions) {
4391
4392        if ((mode & CmsDriverManager.READMODE_ONLY_FOLDERS) > 0) {
4393            // C_READMODE_ONLY_FOLDERS: add condition to match only folders
4394            conditions.append(BEGIN_INCLUDE_CONDITION);
4395            conditions.append(m_sqlManager.readQuery(projectId, "C_RESOURCES_SELECT_ONLY_FOLDERS"));
4396            conditions.append(END_CONDITION);
4397        } else if ((mode & CmsDriverManager.READMODE_ONLY_FILES) > 0) {
4398            // C_READMODE_ONLY_FILES: add condition to match only files
4399            conditions.append(BEGIN_INCLUDE_CONDITION);
4400            conditions.append(m_sqlManager.readQuery(projectId, "C_RESOURCES_SELECT_ONLY_FILES"));
4401            conditions.append(END_CONDITION);
4402        }
4403    }
4404
4405    /**
4406     * Appends the appropriate selection criteria related with the resource state.<p>
4407     *
4408     * @param projectId the id of the project of the resources
4409     * @param state the resource state
4410     * @param mode the selection mode
4411     * @param conditions buffer to append the selection criteria
4412     * @param params list to append the selection parameters
4413     */
4414    protected void prepareStateCondition(
4415        CmsUUID projectId,
4416        CmsResourceState state,
4417        int mode,
4418        StringBuffer conditions,
4419        List<Object> params) {
4420
4421        if (state != null) {
4422            if ((mode & CmsDriverManager.READMODE_EXCLUDE_STATE) > 0) {
4423                // C_READ_MODIFIED_STATES: add condition to match against any state but not given state
4424                conditions.append(BEGIN_EXCLUDE_CONDITION);
4425            } else {
4426                // otherwise add condition to match against given state if necessary
4427                conditions.append(BEGIN_INCLUDE_CONDITION);
4428            }
4429            conditions.append(m_sqlManager.readQuery(projectId, "C_RESOURCES_SELECT_BY_RESOURCE_STATE"));
4430            conditions.append(END_CONDITION);
4431            params.add(new Integer(state.getState()));
4432            params.add(new Integer(state.getState()));
4433        }
4434    }
4435
4436    /**
4437     * Appends the appropriate selection criteria related with the date of the last modification.<p>
4438     *
4439     * @param projectId the id of the project of the resources
4440     * @param startTime start of the time range
4441     * @param endTime end of the time range
4442     * @param conditions buffer to append the selection criteria
4443     * @param params list to append the selection parameters
4444     */
4445    protected void prepareTimeRangeCondition(
4446        CmsUUID projectId,
4447        long startTime,
4448        long endTime,
4449        StringBuffer conditions,
4450        List<Object> params) {
4451
4452        if (startTime > 0L) {
4453            // READ_IGNORE_TIME: if NOT set, add condition to match last modified date against startTime
4454            conditions.append(BEGIN_INCLUDE_CONDITION);
4455            conditions.append(m_sqlManager.readQuery(projectId, "C_RESOURCES_SELECT_BY_DATE_LASTMODIFIED_AFTER"));
4456            conditions.append(END_CONDITION);
4457            params.add(new Long(startTime));
4458        }
4459
4460        if (endTime > 0L) {
4461            // READ_IGNORE_TIME: if NOT set, add condition to match last modified date against endTime
4462            conditions.append(BEGIN_INCLUDE_CONDITION);
4463            conditions.append(m_sqlManager.readQuery(projectId, "C_RESOURCES_SELECT_BY_DATE_LASTMODIFIED_BEFORE"));
4464            conditions.append(END_CONDITION);
4465            params.add(new Long(endTime));
4466        }
4467    }
4468
4469    /**
4470     * Appends the appropriate selection criteria related with the resource type.<p>
4471     *
4472     * @param projectId the id of the project of the resources
4473     * @param type the resource type
4474     * @param mode the selection mode
4475     * @param conditions buffer to append the selection criteria
4476     * @param params list to append the selection parameters
4477     */
4478    protected void prepareTypeCondition(
4479        CmsUUID projectId,
4480        int type,
4481        int mode,
4482        StringBuffer conditions,
4483        List<Object> params) {
4484
4485        if (type != CmsDriverManager.READ_IGNORE_TYPE) {
4486            if ((mode & CmsDriverManager.READMODE_EXCLUDE_TYPE) > 0) {
4487                // C_READ_FILE_TYPES: add condition to match against any type, but not given type
4488                conditions.append(BEGIN_EXCLUDE_CONDITION);
4489                conditions.append(m_sqlManager.readQuery(projectId, "C_RESOURCES_SELECT_BY_RESOURCE_TYPE"));
4490                conditions.append(END_CONDITION);
4491                params.add(new Integer(type));
4492            } else {
4493                //otherwise add condition to match against given type if necessary
4494                conditions.append(BEGIN_INCLUDE_CONDITION);
4495                conditions.append(m_sqlManager.readQuery(projectId, "C_RESOURCES_SELECT_BY_RESOURCE_TYPE"));
4496                conditions.append(END_CONDITION);
4497                params.add(new Integer(type));
4498            }
4499        }
4500    }
4501
4502    /**
4503     * Appends the appropriate selection criteria related with the resource type.<p>
4504     *
4505     * @param projectId the id of the project of the resources
4506     * @param types the resource type id's
4507     * @param mode the selection mode
4508     * @param conditions buffer to append the selection criteria
4509     * @param params list to append the selection parameters
4510     */
4511    protected void prepareTypesCondition(
4512        CmsUUID projectId,
4513        List<Integer> types,
4514        int mode,
4515        StringBuffer conditions,
4516        List<Object> params) {
4517
4518        if ((mode & CmsDriverManager.READMODE_EXCLUDE_TYPE) > 0) {
4519            // C_READ_FILE_TYPES: add condition to match against any type, but not given type
4520            conditions.append(BEGIN_EXCLUDE_CONDITION);
4521            conditions.append(m_sqlManager.readQuery(projectId, "C_RESOURCES_SELECT_BY_RESOURCE_TYPE"));
4522            conditions.append(END_CONDITION);
4523            params.add(new Integer(CmsDriverManager.READ_IGNORE_TYPE));
4524        } else if (!((types == null) || types.isEmpty())) {
4525            //otherwise add condition to match against given type if necessary
4526            conditions.append(BEGIN_INCLUDE_CONDITION);
4527            Iterator<Integer> typeIt = types.iterator();
4528            while (typeIt.hasNext()) {
4529                conditions.append(m_sqlManager.readQuery(projectId, "C_RESOURCES_SELECT_BY_RESOURCE_TYPE"));
4530                params.add(typeIt.next());
4531                if (typeIt.hasNext()) {
4532                    conditions.append(OR_CONDITION);
4533                }
4534            }
4535            conditions.append(END_CONDITION);
4536        }
4537    }
4538
4539    /**
4540     * Reads all resources inside a given project matching the criteria specified by parameter values.<p>
4541     *
4542     * Important: If {@link CmsDriverManager#READMODE_EXCLUDE_TREE} is true (or {@link CmsDriverManager#READMODE_INCLUDE_TREE} is false),
4543     * the provided parent String must be the UUID of the parent folder, NOT the parent folder path.<p>
4544     *
4545     * @param dbc the current database context
4546     * @param projectId the project id for matching resources
4547     * @param parentPath the path to the resource used as root of the searched subtree or {@link CmsDriverManager#READ_IGNORE_PARENT},
4548     *               {@link CmsDriverManager#READMODE_EXCLUDE_TREE} means to read immediate children only
4549     * @param types the resource types of matching resources or <code>null</code> (meaning inverted by {@link CmsDriverManager#READMODE_EXCLUDE_TYPE}
4550     * @param state the state of matching resources (meaning inverted by {@link CmsDriverManager#READMODE_EXCLUDE_STATE} or <code>null</code> to ignore
4551     * @param lastModifiedAfter the start of the time range for the last modification date of matching resources or READ_IGNORE_TIME
4552     * @param lastModifiedBefore the end of the time range for the last modification date of matching resources or READ_IGNORE_TIME
4553     * @param releasedAfter the start of the time range for the release date of matching resources
4554     * @param releasedBefore the end of the time range for the release date of matching resources
4555     * @param expiredAfter the start of the time range for the expire date of matching resources
4556     * @param expiredBefore the end of the time range for the expire date of matching resources
4557     * @param mode additional mode flags:
4558     * <ul>
4559     *  <li>{@link CmsDriverManager#READMODE_INCLUDE_TREE}
4560     *  <li>{@link CmsDriverManager#READMODE_EXCLUDE_TREE}
4561     *  <li>{@link CmsDriverManager#READMODE_INCLUDE_PROJECT}
4562     *  <li>{@link CmsDriverManager#READMODE_EXCLUDE_TYPE}
4563     *  <li>{@link CmsDriverManager#READMODE_EXCLUDE_STATE}
4564     * </ul>
4565     *
4566     * @return a list of CmsResource objects matching the given criteria
4567     *
4568     * @throws CmsDataAccessException if something goes wrong
4569     */
4570    protected List<CmsResource> readTypesInResourceTree(
4571        CmsDbContext dbc,
4572        CmsUUID projectId,
4573        String parentPath,
4574        List<Integer> types,
4575        CmsResourceState state,
4576        long lastModifiedAfter,
4577        long lastModifiedBefore,
4578        long releasedAfter,
4579        long releasedBefore,
4580        long expiredAfter,
4581        long expiredBefore,
4582        int mode)
4583    throws CmsDataAccessException {
4584
4585        List<CmsResource> result = new ArrayList<CmsResource>();
4586
4587        StringBuffer conditions = new StringBuffer();
4588        List<Object> params = new ArrayList<Object>(5);
4589
4590        // prepare the selection criteria
4591        prepareProjectCondition(projectId, mode, conditions, params);
4592        prepareResourceCondition(projectId, mode, conditions);
4593        prepareTypesCondition(projectId, types, mode, conditions, params);
4594        prepareTimeRangeCondition(projectId, lastModifiedAfter, lastModifiedBefore, conditions, params);
4595        prepareReleasedTimeRangeCondition(projectId, releasedAfter, releasedBefore, conditions, params);
4596        prepareExpiredTimeRangeCondition(projectId, expiredAfter, expiredBefore, conditions, params);
4597        preparePathCondition(projectId, parentPath, mode, conditions, params);
4598        prepareStateCondition(projectId, state, mode, conditions, params);
4599
4600        // now read matching resources within the subtree
4601        ResultSet res = null;
4602        PreparedStatement stmt = null;
4603        Connection conn = null;
4604
4605        try {
4606            conn = m_sqlManager.getConnection(dbc);
4607            StringBuffer queryBuf = new StringBuffer(256);
4608            queryBuf.append(m_sqlManager.readQuery(projectId, "C_RESOURCES_READ_TREE"));
4609            queryBuf.append(conditions);
4610            queryBuf.append(" ");
4611            queryBuf.append(m_sqlManager.readQuery(projectId, "C_RESOURCES_ORDER_BY_PATH"));
4612            stmt = m_sqlManager.getPreparedStatementForSql(conn, queryBuf.toString());
4613
4614            for (int i = 0; i < params.size(); i++) {
4615                if (params.get(i) instanceof Integer) {
4616                    stmt.setInt(i + 1, ((Integer)params.get(i)).intValue());
4617                } else if (params.get(i) instanceof Long) {
4618                    stmt.setLong(i + 1, ((Long)params.get(i)).longValue());
4619                } else {
4620                    stmt.setString(i + 1, (String)params.get(i));
4621                }
4622            }
4623
4624            res = stmt.executeQuery();
4625            while (res.next()) {
4626                CmsResource resource = createResource(res, projectId);
4627                result.add(resource);
4628            }
4629
4630        } catch (SQLException e) {
4631            throw new CmsDbSqlException(
4632                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
4633                e);
4634        } finally {
4635            m_sqlManager.closeAll(dbc, conn, stmt, res);
4636        }
4637
4638        return result;
4639    }
4640
4641    /**
4642     * Repairs broken links.<p>
4643     *
4644     * When a resource is created any relation pointing to it is updated to use the right id.<p>
4645     *
4646     * @param dbc the current database context
4647     * @param projectId the project id
4648     * @param structureId the structure id of the resource that may help to repair broken links
4649     * @param rootPath the path of the resource that may help to repair broken links
4650     *
4651     * @throws CmsDataAccessException if something goes wrong
4652     */
4653    protected void repairBrokenRelations(CmsDbContext dbc, CmsUUID projectId, CmsUUID structureId, String rootPath)
4654    throws CmsDataAccessException {
4655
4656        PreparedStatement stmt = null;
4657        Connection conn = null;
4658
4659        try {
4660            conn = m_sqlManager.getConnection(dbc);
4661            stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RELATIONS_REPAIR_BROKEN");
4662            stmt.setString(1, structureId.toString());
4663            stmt.setString(2, rootPath);
4664            stmt.executeUpdate();
4665        } catch (SQLException e) {
4666            throw new CmsDbSqlException(
4667                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
4668                e);
4669        } finally {
4670            m_sqlManager.closeAll(dbc, conn, stmt, null);
4671        }
4672    }
4673
4674    /**
4675     * Updates broken links.<p>
4676     *
4677     * When a resource is deleted, then the relations pointing to
4678     * the deleted resource are set to the null uuid.<p>
4679     *
4680     * @param dbc the current database context
4681     * @param projectId the project id
4682     * @param rootPath the root path of the resource that has been deleted
4683     *
4684     * @throws CmsDataAccessException if something goes wrong
4685     */
4686    protected void updateBrokenRelations(CmsDbContext dbc, CmsUUID projectId, String rootPath)
4687    throws CmsDataAccessException {
4688
4689        PreparedStatement stmt = null;
4690        Connection conn = null;
4691        try {
4692            try {
4693                conn = m_sqlManager.getConnection(dbc);
4694                stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RELATIONS_UPDATE_BROKEN");
4695                stmt.setString(1, rootPath);
4696                stmt.executeUpdate();
4697            } finally {
4698                m_sqlManager.closeAll(dbc, conn, stmt, null);
4699            }
4700
4701            try {
4702                conn = m_sqlManager.getConnection(dbc);
4703                stmt = m_sqlManager.getPreparedStatement(conn, projectId, "C_RELATIONS_DELETE_BROKEN_LOCALE_RELATIONS");
4704                stmt.setString(1, rootPath);
4705                stmt.executeUpdate();
4706            } finally {
4707                m_sqlManager.closeAll(dbc, conn, stmt, null);
4708            }
4709        } catch (SQLException e) {
4710            throw new CmsDbSqlException(
4711                Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
4712                e);
4713        }
4714
4715    }
4716
4717    /**
4718     * Wrap a SQL exception into a CmsDbSqlException.<p>
4719     *
4720     * @param stmt the used statement
4721     * @param e the exception
4722     *
4723     * @return the CmsDbSqlException
4724     */
4725    protected CmsDbSqlException wrapException(PreparedStatement stmt, SQLException e) {
4726
4727        return new CmsDbSqlException(
4728            Messages.get().container(Messages.ERR_GENERIC_SQL_1, CmsDbSqlException.getErrorQuery(stmt)),
4729            e);
4730    }
4731
4732    /**
4733     * Creates a prepared statement by combining a base query with the generated SQL conditions for a given
4734     * URL name mapping filter.<p>
4735     *
4736     * @param conn the connection to use for creating the prepared statement
4737     * @param baseQuery the base query to which the conditions should be appended
4738     * @param filter the filter from which to generate the conditions
4739     *
4740     * @return the created prepared statement
4741     *
4742     * @throws SQLException if something goes wrong
4743     */
4744    PreparedStatement getPreparedStatementForFilter(Connection conn, String baseQuery, CmsUrlNameMappingFilter filter)
4745    throws SQLException {
4746
4747        CmsPair<String, List<I_CmsPreparedStatementParameter>> conditionData = prepareUrlNameMappingConditions(filter);
4748        String whereClause = "";
4749        if (!conditionData.getFirst().equals("")) {
4750            whereClause = " WHERE " + conditionData.getFirst();
4751        }
4752        String query = baseQuery + whereClause;
4753        PreparedStatement stmt = m_sqlManager.getPreparedStatementForSql(conn, query);
4754        int counter = 1;
4755        for (I_CmsPreparedStatementParameter param : conditionData.getSecond()) {
4756            param.insertIntoStatement(stmt, counter);
4757            counter += 1;
4758        }
4759        return stmt;
4760    }
4761
4762    /**
4763     * Helper method to convert an alias filter to SQL conditions.<p>
4764     *
4765     * @param filter the alias filter
4766     * @return a pair containing a condition string and the parameters which are necessary for the conditions
4767     */
4768    private CmsPair<String, List<String>> buildAliasConditions(CmsAliasFilter filter) {
4769
4770        List<String> conditions = new ArrayList<String>();
4771        conditions.add("1 = 1");
4772        List<String> conditionParams = new ArrayList<String>();
4773        if (filter.getSiteRoot() != null) {
4774            conditions.add("site_root = ?");
4775            conditionParams.add(filter.getSiteRoot());
4776        }
4777        if (filter.getStructureId() != null) {
4778            conditions.add("structure_id = ?");
4779            conditionParams.add(filter.getStructureId().toString());
4780        }
4781        if (filter.getPath() != null) {
4782            conditions.add("path = ?");
4783            conditionParams.add(filter.getPath());
4784        }
4785        String conditionString = CmsStringUtil.listAsString(conditions, " AND ");
4786        return CmsPair.create(conditionString, conditionParams);
4787    }
4788
4789    /**
4790     * Helper method to prepare the SQL conditions for accessing rewrite aliases using a given filter.<p>
4791     *
4792     * @param filter the filter to use for rewrite aliases
4793     *
4794     * @return a pair consisting of an SQL condition string and a list of query parameters
4795     */
4796    private CmsPair<String, List<Object>> prepareRewriteAliasConditions(CmsRewriteAliasFilter filter) {
4797
4798        List<String> conditions = new ArrayList<String>();
4799        conditions.add("1 = 1");
4800        List<Object> parameters = new ArrayList<Object>();
4801        if (filter.getSiteRoot() != null) {
4802            parameters.add(filter.getSiteRoot());
4803            conditions.add("SITE_ROOT = ?");
4804        }
4805        if (filter.getId() != null) {
4806            parameters.add(filter.getId().toString());
4807            conditions.add("ID = ?");
4808        }
4809        return CmsPair.create(CmsStringUtil.listAsString(conditions, " AND "), parameters);
4810    }
4811
4812    /**
4813     * Replaces the %(PROJECT) macro inside a query with either ONLINE or OFFLINE, depending on the value
4814     * of a flag.<p>
4815     *
4816     * We use this instead of the ${PROJECT} replacement mechanism when we need explicit control over the
4817     * project, and don't want to implicitly use the project of the DB context.<p>
4818     *
4819     * @param query the query in which the macro should be replaced
4820     * @param online if true, the macro will be replaced with "ONLINE", else "OFFLINE"
4821     *
4822     * @return the query with the replaced macro
4823     */
4824    private String replaceProject(String query, boolean online) {
4825
4826        return query.replace("%(PROJECT)", online ? ONLINE : OFFLINE);
4827    }
4828
4829}