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.setup.db.update6to7;
029
030import org.opencms.setup.CmsSetupDBWrapper;
031import org.opencms.setup.CmsSetupDb;
032import org.opencms.setup.db.A_CmsUpdateDBPart;
033
034import java.io.IOException;
035import java.sql.SQLException;
036import java.util.ArrayList;
037import java.util.List;
038
039/**
040 * This class inserts formerly deleted users/groups in the CMS_HISTORY_PRINCIPALS table.<p>
041 *
042 * These users/groups are read out of the following tables:
043 * <ul>
044 * <li>CMS_BACKUP_RESOURCES</li>
045 * <li>CMS_BACKUP_PROJECTS</li>
046 * </ul>
047 *
048 * @since 7.0.0
049 */
050public class CmsUpdateDBHistoryPrincipals extends A_CmsUpdateDBPart {
051
052    /** Constant for sql query to create the history principals table.<p> */
053    protected static final String QUERY_HISTORY_PRINCIPALS_CREATE_TABLE = "Q_HISTORY_PRINCIPALS_CREATE_TABLE";
054
055    /** Constant for the CMS_HISTORY_PRINICIPALS table.<p> */
056    protected static final String TABLE_CMS_HISTORY_PRINCIPALS = "CMS_HISTORY_PRINCIPALS";
057
058    /** Constant for sql query.<p> */
059    private static final String QUERY_HISTORY_PRINCIPALS_PROJECTS_GROUPS = "Q_HISTORY_PRINCIPALS_PROJECTS_GROUPS";
060
061    /** Constant for sql query.<p> */
062    private static final String QUERY_HISTORY_PRINCIPALS_PROJECTS_MANAGERGROUPS = "Q_HISTORY_PRINCIPALS_PROJECTS_MANAGERGROUPS";
063
064    /** Constant for sql query.<p> */
065    private static final String QUERY_HISTORY_PRINCIPALS_PROJECTS_PUBLISHED = "Q_HISTORY_PRINCIPALS_PROJECTS_PUBLISHED";
066
067    /** Constant for sql query.<p> */
068    private static final String QUERY_HISTORY_PRINCIPALS_PROJECTS_USERS = "Q_HISTORY_PRINCIPALS_PROJECTS_USERS";
069
070    /** Constant for sql query.<p> */
071    private static final String QUERY_HISTORY_PRINCIPALS_RESOURCES = "Q_HISTORY_PRINCIPALS_RESOURCES";
072
073    /** Constant for the SQL query properties.<p> */
074    private static final String QUERY_PROPERTY_FILE = "cms_history_principals_queries.properties";
075
076    /** Constant for the sql query to select the count of history principals.<p> */
077    private static final String QUERY_SELECT_COUNT_HISTORY_PRINCIPALS = "Q_SELECT_COUNT_HISTORY_PRINICPALS";
078
079    /** Constant for sql query.<p> */
080    private static final String QUERY_UPDATE_DATEDELETED = "Q_UPDATE_DATEDELETED";
081
082    /**
083     * Constructor.<p>
084     *
085     * @throws IOException if the sql queries properties file could not be read
086     */
087    public CmsUpdateDBHistoryPrincipals()
088    throws IOException {
089
090        super();
091        loadQueryProperties(getPropertyFileLocation() + QUERY_PROPERTY_FILE);
092    }
093
094    /**
095     * Creates the CMS_HISTORY_PRINCIPALS table if it does not exist yet.<p>
096     *
097     * @param dbCon the db connection interface
098     *
099     * @throws SQLException if soemthing goes wrong
100     */
101    protected void createHistPrincipalsTable(CmsSetupDb dbCon) throws SQLException {
102
103        System.out.println(new Exception().getStackTrace()[0].toString());
104        if (!dbCon.hasTableOrColumn(TABLE_CMS_HISTORY_PRINCIPALS, null)) {
105            String createStatement = readQuery(QUERY_HISTORY_PRINCIPALS_CREATE_TABLE);
106            dbCon.updateSqlStatement(createStatement, null, null);
107        } else {
108            System.out.println("table " + TABLE_CMS_HISTORY_PRINCIPALS + " already exists");
109        }
110    }
111
112    /**
113     * @see org.opencms.setup.db.A_CmsUpdateDBPart#internalExecute(org.opencms.setup.CmsSetupDb)
114     */
115    @Override
116    protected void internalExecute(CmsSetupDb dbCon) throws SQLException {
117
118        System.out.println(new Exception().getStackTrace()[0].toString());
119        if (insertHistoryPrincipals(dbCon)) {
120            List<Object> params = new ArrayList<Object>();
121            params.add(Long.valueOf(System.currentTimeMillis()));
122
123            dbCon.updateSqlStatement(readQuery(QUERY_UPDATE_DATEDELETED), null, params);
124        }
125    }
126
127    /**
128     * Checks if the CMS_HISTORY_PRINCIPALS already has data in it.<p>
129     *
130     * @param dbCon the db connection interface
131     * @return true if there is already data in the table, false if it is empty
132     *
133     * @throws SQLException if something goes wrong
134     */
135    private boolean hasData(CmsSetupDb dbCon) throws SQLException {
136
137        System.out.println(new Exception().getStackTrace()[0].toString());
138        boolean result = false;
139        String query = readQuery(QUERY_SELECT_COUNT_HISTORY_PRINCIPALS);
140        CmsSetupDBWrapper db = null;
141        try {
142            db = dbCon.executeSqlStatement(query, null);
143            if (db.getResultSet().next()) {
144                if (db.getResultSet().getInt("COUNT") > 0) {
145                    result = true;
146                }
147            }
148        } finally {
149            if (db != null) {
150                db.close();
151            }
152        }
153
154        return result;
155    }
156
157    /**
158     * Inserts deleted users/groups in the history principals table.<p>
159     *
160     * @param dbCon the db connection interface
161     * @return true if the USER_DATEDELETED needs updating, false if not
162     *
163     * @throws SQLException if something goes wrong
164     */
165    private boolean insertHistoryPrincipals(CmsSetupDb dbCon) throws SQLException {
166
167        System.out.println(new Exception().getStackTrace()[0].toString());
168
169        createHistPrincipalsTable(dbCon);
170
171        boolean updateUserDateDeleted = false;
172        if (isKeepHistory() && !hasData(dbCon)) {
173            dbCon.updateSqlStatement(readQuery(QUERY_HISTORY_PRINCIPALS_RESOURCES), null, null);
174            dbCon.updateSqlStatement(readQuery(QUERY_HISTORY_PRINCIPALS_PROJECTS_GROUPS), null, null);
175            dbCon.updateSqlStatement(readQuery(QUERY_HISTORY_PRINCIPALS_PROJECTS_MANAGERGROUPS), null, null);
176            dbCon.updateSqlStatement(readQuery(QUERY_HISTORY_PRINCIPALS_PROJECTS_PUBLISHED), null, null);
177            dbCon.updateSqlStatement(readQuery(QUERY_HISTORY_PRINCIPALS_PROJECTS_USERS), null, null);
178            updateUserDateDeleted = true; // update the colum USER_DATETELETED
179        }
180
181        return updateUserDateDeleted;
182    }
183}