Source code

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;
033import org.opencms.util.CmsCollectionsGenericWrapper;
034import org.opencms.util.CmsStringUtil;
035import org.opencms.util.CmsUUID;
036
037import java.io.ByteArrayInputStream;
038import java.io.IOException;
039import java.io.ObjectInputStream;
040import java.sql.Blob;
041import java.sql.SQLException;
042import java.util.ArrayList;
043import java.util.HashMap;
044import java.util.Iterator;
045import java.util.List;
046import java.util.Map;
047
048/**
049 * This class makes an update of the CMS_USERS table splitting it up into CMS_USERS and CMS_USERDATA.<p>
050 * Unnecessary colums from CMS_USERS will be deleted and the new column USER_DATECREATED is added.
051 *
052 * @since 7.0.0
053 */
054public class CmsUpdateDBCmsUsers extends A_CmsUpdateDBPart {
055
056    /** Constant for the query to create the user data table.<p> */
057    protected static final String QUERY_CREATE_TABLE_USERDATA = "Q_CREATE_TABLE_USERDATA";
058
059    /** Constant for the query to insert the new user data into the new table CMS_USERDATA.<p> */
060    protected static final String QUERY_INSERT_CMS_USERDATA = "Q_INSERT_CMS_USERDATA";
061
062    /** Constant for the table CMS_USERDATA.<p> */
063    private static final String CHECK_CMS_USERDATA = "CMS_USERDATA";
064
065    /** Constant for the table name of CMS_USERS.<p> */
066    private static final String CMS_USERS_TABLE = "CMS_USERS";
067
068    /** Constant for the sql query to add the USER_DATECREATED column to CMS_USERS.<p> */
069    private static final String QUERY_ADD_USER_DATECREATED_COLUMN = "Q_ADD_USER_DATECREATED";
070
071    /** Constant for the sql query to add all webusers to the group with the given id.<p> */
072    private static final String QUERY_ADD_WEBUSERS_TO_GROUP = "Q_ADD_WEBUSERS_TO_GROUP";
073
074    /** Constant for the sql query to create a new group in the CMS_GROUPS table for the webusers.<p> */
075    private static final String QUERY_CREATE_WEBUSERS_GROUP = "Q_CREATE_WEBUSERS_GROUP";
076
077    /** Constant for the sql query to drop the USER_ADDRESS column from CMS_USERS.<p> */
078    private static final String QUERY_DROP_USER_ADDRESS_COLUMN = "Q_DROP_USER_ADDRESS_COLUMN";
079
080    /** Constant for the sql query to drop the USER_DESCRIPTION column from CMS_USERS.<p> */
081    private static final String QUERY_DROP_USER_DESCRIPTION_COLUMN = "Q_DROP_USER_DESCRIPTION_COLUMN";
082
083    /** Constant for the sql query to drop the USER_INFO column from CMS_USERS.<p> */
084    private static final String QUERY_DROP_USER_INFO_COLUMN = "Q_DROP_USER_INFO_COLUMN";
085
086    /** Constant for the sql query to drop the USER_TYPE column from CMS_USERS.<p> */
087    private static final String QUERY_DROP_USER_TYPE_COLUMN = "Q_DROP_USER_TYPE_COLUMN";
088
089    /** Constant for the SQL query properties.<p> */
090    private static final String QUERY_PROPERTY_FILE = "cms_users_queries.properties";
091
092    /** Constant for the query to the select the user infos for a user.<p> */
093    private static final String QUERY_SELECT_USER_DATA = "Q_SELECT_USER_DATA";
094
095    /** Constant for the sql query to set the USER_DATECREATED value.<p> */
096    private static final String QUERY_SET_USER_DATECREATED = "Q_SET_USER_DATECREATED";
097
098    /** Constant for the columnname USER_ID of the resultset.<p> */
099    private static final String RESULTSET_USER_ID = "USER_ID";
100
101    /** Constant for the columnname USER_INFO of the resultset.<p> */
102    private static final String RESULTSET_USER_INFO = "USER_INFO";
103
104    /** Constant for the columnname USER_ADDRESS of the resultset.<p> */
105    private static final String USER_ADDRESS = "USER_ADDRESS";
106
107    /** Constant for the columnname USER_DATECREATED.<p> */
108    private static final String USER_DATECREATED = "USER_DATECREATED";
109
110    /** Constant for the columnname USER_DESCRIPTION of the resultset.<p> */
111    private static final String USER_DESCRIPTION = "USER_DESCRIPTION";
112
113    /** Constant for the columnname USER_INFO.<p> */
114    private static final String USER_INFO = "USER_INFO";
115
116    /** Constant for the columnname USER_TYPE.<p> */
117    private static final String USER_TYPE = "USER_TYPE";
118
119    /**
120     * Default constructor.<p>
121     *
122     * @throws IOException if the default sql queries property file could not be read
123     */
124    public CmsUpdateDBCmsUsers()
125    throws IOException {
126
127        super();
128        loadQueryProperties(getPropertyFileLocation() + QUERY_PROPERTY_FILE);
129    }
130
131    /**
132     * @see org.opencms.setup.db.A_CmsUpdateDBPart#internalExecute(org.opencms.setup.CmsSetupDb)
133     */
134    @Override
135    protected void internalExecute(CmsSetupDb dbCon) {
136
137        System.out.println(new Exception().getStackTrace()[0].toString());
138        try {
139            if (dbCon.hasTableOrColumn(CMS_USERS_TABLE, USER_TYPE)) {
140                CmsUUID id = createWebusersGroup(dbCon);
141                addWebusersToGroup(dbCon, id);
142            } else {
143                System.out.println("table " + CHECK_CMS_USERDATA + " already exists");
144            }
145        } catch (SQLException e) {
146            e.printStackTrace();
147        }
148        try {
149            // Check if the CMS_USERDATA table exists
150            if (!checkUserDataTable(dbCon)) {
151                createUserDataTable(dbCon); // Could throw Exception during table creation
152
153                String query = readQuery(QUERY_SELECT_USER_DATA);
154                CmsSetupDBWrapper db = null;
155                try {
156                    db = dbCon.executeSqlStatement(query, null);
157                    while (db.getResultSet().next()) {
158                        String userID = (String)db.getResultSet().getObject(RESULTSET_USER_ID);
159                        System.out.println("UserId: " + userID);
160
161                        try {
162                            Blob blob = db.getResultSet().getBlob(RESULTSET_USER_INFO);
163
164                            ByteArrayInputStream bin = new ByteArrayInputStream(blob.getBytes(1, (int)blob.length()));
165                            ObjectInputStream oin = new ObjectInputStream(bin);
166
167                            Map<String, Object> infos = CmsCollectionsGenericWrapper.map(oin.readObject());
168
169                            if (infos == null) {
170                                infos = new HashMap<String, Object>();
171                            }
172
173                            // Add user address and user description of the current user
174                            String userAddress = (String)db.getResultSet().getObject(USER_ADDRESS);
175                            if (CmsStringUtil.isNotEmptyOrWhitespaceOnly(userAddress)) {
176                                infos.put(USER_ADDRESS, userAddress);
177                            }
178                            String userDescription = (String)db.getResultSet().getObject(USER_DESCRIPTION);
179                            if (CmsStringUtil.isNotEmptyOrWhitespaceOnly(userDescription)) {
180                                infos.put(USER_DESCRIPTION, userDescription);
181                            }
182
183                            // Write the user data to the table
184                            writeAdditionalUserInfo(dbCon, userID, infos);
185                        } catch (Throwable e) {
186                            e.printStackTrace();
187                        }
188                    }
189                } finally {
190                    if (db != null) {
191                        db.close();
192                    }
193                }
194
195                // add the column USER_DATECREATED
196                addUserDateCreated(dbCon);
197
198                // remove the unnecessary columns from CMS_USERS
199                removeUnnecessaryColumns(dbCon);
200
201            } else {
202                System.out.println("table " + CHECK_CMS_USERDATA + " already exists");
203            }
204        } catch (SQLException e) {
205            e.printStackTrace();
206        }
207    }
208
209    /**
210     * Adds the new column USER_DATECREATED to the CMS_USERS table.<p>
211     *
212     * @param dbCon the db connection interface
213     *
214     * @throws SQLException if something goes wrong
215     */
216    protected void addUserDateCreated(CmsSetupDb dbCon) throws SQLException {
217
218        System.out.println(new Exception().getStackTrace()[0].toString());
219        // Add the column to the table if necessary
220        if (!dbCon.hasTableOrColumn(CMS_USERS_TABLE, USER_DATECREATED)) {
221            String addUserDateCreated = readQuery(QUERY_ADD_USER_DATECREATED_COLUMN);
222            dbCon.updateSqlStatement(addUserDateCreated, null, null);
223
224            String setUserDateCreated = readQuery(QUERY_SET_USER_DATECREATED);
225            List<Object> param = new ArrayList<Object>();
226            // Set the creation date to the current time
227            param.add(new Long(System.currentTimeMillis()));
228
229            dbCon.updateSqlStatement(setUserDateCreated, null, param);
230        } else {
231            System.out.println("column " + USER_DATECREATED + " in table " + CMS_USERS_TABLE + " already exists");
232        }
233    }
234
235    /**
236     * Adds all webusers to the new previously created webusers group.<p>
237     *
238     * @param dbCon the db connection interface
239     * @param id the id of the new webusers group
240     *
241     * @throws SQLException if something goes wrong
242     */
243    protected void addWebusersToGroup(CmsSetupDb dbCon, CmsUUID id) throws SQLException {
244
245        String sql = readQuery(QUERY_ADD_WEBUSERS_TO_GROUP);
246        Map<String, String> replacements = new HashMap<String, String>();
247        replacements.put("${GROUP_ID}", id.toString());
248        dbCon.updateSqlStatement(sql, replacements, null);
249    }
250
251    /**
252     * Checks if the CMS_USERDATA table exists.<p>
253     *
254     * @param dbCon the db connection interface
255     *
256     * @return true if it exists, false if not.
257     */
258    protected boolean checkUserDataTable(CmsSetupDb dbCon) {
259
260        System.out.println(new Exception().getStackTrace()[0].toString());
261        return dbCon.hasTableOrColumn(CHECK_CMS_USERDATA, null);
262    }
263
264    /**
265     * Creates the CMS_USERDATA table if it does not exist yet.<p>
266     *
267     * @param dbCon the db connection interface
268     *
269     * @throws SQLException if soemthing goes wrong
270     */
271    protected void createUserDataTable(CmsSetupDb dbCon) throws SQLException {
272
273        System.out.println(new Exception().getStackTrace()[0].toString());
274        String createStatement = readQuery(QUERY_CREATE_TABLE_USERDATA);
275        dbCon.updateSqlStatement(createStatement, null, null);
276    }
277
278    /**
279     * creates a new group for the webusers.<p>
280     *
281     * @param dbCon the db connection interface
282     *
283     * @return the id of the new generated group
284     *
285     * @throws SQLException if something goes wrong
286     */
287    protected CmsUUID createWebusersGroup(CmsSetupDb dbCon) throws SQLException {
288
289        String sql = readQuery(QUERY_CREATE_WEBUSERS_GROUP);
290        List<Object> params = new ArrayList<Object>();
291        CmsUUID id = new CmsUUID();
292        params.add(id.toString());
293        params.add(CmsUUID.getNullUUID().toString());
294        params.add("allWebusersFromUpgrade6to7");
295        params.add(
296            "This group was created by the OpenCms Upgrade Wizard to facilitate the handling of former called WebUsers, can be deleted if needed.");
297        params.add(new Integer(0));
298        params.add("/");
299        dbCon.updateSqlStatement(sql, null, params);
300        return id;
301    }
302
303    /**
304     * Removes the columns USER_INFO, USER_ADDRESS, USER_DESCRIPTION and USER_TYPE from the CMS_USERS table.<p>
305     *
306     * @param dbCon the db connection interface
307     *
308     * @throws SQLException if something goes wrong
309     */
310    protected void removeUnnecessaryColumns(CmsSetupDb dbCon) throws SQLException {
311
312        System.out.println(new Exception().getStackTrace()[0].toString());
313        // Get the sql queries to drop the columns
314        String dropUserInfo = readQuery(QUERY_DROP_USER_INFO_COLUMN);
315        String dropUserAddress = readQuery(QUERY_DROP_USER_ADDRESS_COLUMN);
316        String dropUserDescription = readQuery(QUERY_DROP_USER_DESCRIPTION_COLUMN);
317        String dropUserType = readQuery(QUERY_DROP_USER_TYPE_COLUMN);
318
319        // execute the queries to drop the columns, if they exist
320        if (dbCon.hasTableOrColumn(CMS_USERS_TABLE, USER_INFO)) {
321            dbCon.updateSqlStatement(dropUserInfo, null, null);
322        } else {
323            System.out.println("no column " + USER_INFO + " in table " + CMS_USERS_TABLE + " found");
324        }
325        if (dbCon.hasTableOrColumn(CMS_USERS_TABLE, USER_ADDRESS)) {
326            dbCon.updateSqlStatement(dropUserAddress, null, null);
327        } else {
328            System.out.println("no column " + USER_ADDRESS + " in table " + CMS_USERS_TABLE + " found");
329        }
330        if (dbCon.hasTableOrColumn(CMS_USERS_TABLE, USER_DESCRIPTION)) {
331            dbCon.updateSqlStatement(dropUserDescription, null, null);
332        } else {
333            System.out.println("no column " + USER_DESCRIPTION + " in table " + CMS_USERS_TABLE + " found");
334        }
335        if (dbCon.hasTableOrColumn(CMS_USERS_TABLE, USER_TYPE)) {
336            dbCon.updateSqlStatement(dropUserType, null, null);
337        } else {
338            System.out.println("no column " + USER_TYPE + " in table " + CMS_USERS_TABLE + " found");
339        }
340    }
341
342    /**
343     * Writes the additional user infos to the database.<p>
344     *
345     * @param dbCon the db connection interface
346     * @param id the user id
347     * @param additionalInfo the additional info of the user
348     */
349    protected void writeAdditionalUserInfo(CmsSetupDb dbCon, String id, Map<String, Object> additionalInfo) {
350
351        Iterator<Map.Entry<String, Object>> entries = additionalInfo.entrySet().iterator();
352        while (entries.hasNext()) {
353            Map.Entry<String, Object> entry = entries.next();
354            if ((entry.getKey() != null) && (entry.getValue() != null)) {
355                // Write the additional user information to the database
356                writeUserInfo(dbCon, id, entry.getKey(), entry.getValue());
357            }
358        }
359    }
360
361    /**
362     * Writes one set of additional user info (key and its value) to the CMS_USERDATA table.<p>
363     *
364     * @param dbCon the db connection interface
365     * @param id the user id
366     * @param key the data key
367     * @param value the data value
368     */
369    protected void writeUserInfo(CmsSetupDb dbCon, String id, String key, Object value) {
370
371        String query = readQuery(QUERY_INSERT_CMS_USERDATA);
372
373        try {
374            // Generate the list of parameters to add into the user info table
375            List<Object> params = new ArrayList<Object>();
376            params.add(id);
377            params.add(key);
378            params.add(value);
379            params.add(value.getClass().getName());
380
381            dbCon.updateSqlStatement(query, null, params);
382        } catch (SQLException e) {
383            e.printStackTrace();
384        }
385    }
386}