001: /*
002: * File : $Source: /usr/local/cvs/opencms/src-setup/org/opencms/setup/update6to7/generic/CmsUpdateDBCmsUsers.java,v $
003: * Date : $Date: 2008-02-27 12:05:35 $
004: * Version: $Revision: 1.2 $
005: *
006: * This library is part of OpenCms -
007: * the Open Source Content Management System
008: *
009: * Copyright (c) 2002 - 2008 Alkacon Software GmbH (http://www.alkacon.com)
010: *
011: * This library is free software; you can redistribute it and/or
012: * modify it under the terms of the GNU Lesser General Public
013: * License as published by the Free Software Foundation; either
014: * version 2.1 of the License, or (at your option) any later version.
015: *
016: * This library is distributed in the hope that it will be useful,
017: * but WITHOUT ANY WARRANTY; without even the implied warranty of
018: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
019: * Lesser General Public License for more details.
020: *
021: * For further information about Alkacon Software GmbH, please see the
022: * company website: http://www.alkacon.com
023: *
024: * For further information about OpenCms, please see the
025: * project website: http://www.opencms.org
026: *
027: * You should have received a copy of the GNU Lesser General Public
028: * License along with this library; if not, write to the Free Software
029: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
030: */
031:
032: package org.opencms.setup.update6to7.generic;
033:
034: import org.opencms.setup.CmsSetupDBWrapper;
035: import org.opencms.setup.CmsSetupDb;
036: import org.opencms.setup.update6to7.A_CmsUpdateDBPart;
037: import org.opencms.util.CmsStringUtil;
038: import org.opencms.util.CmsUUID;
039:
040: import java.io.ByteArrayInputStream;
041: import java.io.IOException;
042: import java.io.ObjectInputStream;
043: import java.sql.Blob;
044: import java.sql.SQLException;
045: import java.util.ArrayList;
046: import java.util.HashMap;
047: import java.util.Iterator;
048: import java.util.List;
049: import java.util.Map;
050:
051: /**
052: * This class makes an update of the CMS_USERS table splitting it up into CMS_USERS and CMS_USERDATA.<p>
053: * Unnecessary colums from CMS_USERS will be deleted and the new column USER_DATECREATED is added.
054: *
055: * @author Roland Metzler
056: *
057: * @version $Revision: 1.2 $
058: *
059: * @since 7.0.0
060: */
061: public class CmsUpdateDBCmsUsers extends A_CmsUpdateDBPart {
062:
063: /** Constant for the query to create the user data table.<p> */
064: protected static final String QUERY_CREATE_TABLE_USERDATA = "Q_CREATE_TABLE_USERDATA";
065:
066: /** Constant for the query to insert the new user data into the new table CMS_USERDATA.<p> */
067: protected static final String QUERY_INSERT_CMS_USERDATA = "Q_INSERT_CMS_USERDATA";
068:
069: /** Constant for the table CMS_USERDATA.<p> */
070: private static final String CHECK_CMS_USERDATA = "CMS_USERDATA";
071:
072: /** Constant for the table name of CMS_USERS.<p> */
073: private static final String CMS_USERS_TABLE = "CMS_USERS";
074:
075: /** Constant for the sql query to add the USER_DATECREATED column to CMS_USERS.<p> */
076: private static final String QUERY_ADD_USER_DATECREATED_COLUMN = "Q_ADD_USER_DATECREATED";
077:
078: /** Constant for the sql query to add all webusers to the group with the given id.<p> */
079: private static final String QUERY_ADD_WEBUSERS_TO_GROUP = "Q_ADD_WEBUSERS_TO_GROUP";
080:
081: /** Constant for the sql query to create a new group in the CMS_GROUPS table for the webusers.<p> */
082: private static final String QUERY_CREATE_WEBUSERS_GROUP = "Q_CREATE_WEBUSERS_GROUP";
083:
084: /** Constant for the sql query to drop the USER_ADDRESS column from CMS_USERS.<p> */
085: private static final String QUERY_DROP_USER_ADDRESS_COLUMN = "Q_DROP_USER_ADDRESS_COLUMN";
086:
087: /** Constant for the sql query to drop the USER_DESCRIPTION column from CMS_USERS.<p> */
088: private static final String QUERY_DROP_USER_DESCRIPTION_COLUMN = "Q_DROP_USER_DESCRIPTION_COLUMN";
089:
090: /** Constant for the sql query to drop the USER_INFO column from CMS_USERS.<p> */
091: private static final String QUERY_DROP_USER_INFO_COLUMN = "Q_DROP_USER_INFO_COLUMN";
092:
093: /** Constant for the sql query to drop the USER_TYPE column from CMS_USERS.<p> */
094: private static final String QUERY_DROP_USER_TYPE_COLUMN = "Q_DROP_USER_TYPE_COLUMN";
095:
096: /** Constant for the SQL query properties.<p> */
097: private static final String QUERY_PROPERTY_FILE = "generic/cms_users_queries.properties";
098:
099: /** Constant for the query to the select the user infos for a user.<p> */
100: private static final String QUERY_SELECT_USER_DATA = "Q_SELECT_USER_DATA";
101:
102: /** Constant for the sql query to set the USER_DATECREATED value.<p> */
103: private static final String QUERY_SET_USER_DATECREATED = "Q_SET_USER_DATECREATED";
104:
105: /** Constant for the columnname USER_ID of the resultset.<p> */
106: private static final String RESULTSET_USER_ID = "USER_ID";
107:
108: /** Constant for the columnname USER_INFO of the resultset.<p> */
109: private static final String RESULTSET_USER_INFO = "USER_INFO";
110:
111: /** Constant for the columnname USER_ADDRESS of the resultset.<p> */
112: private static final String USER_ADDRESS = "USER_ADDRESS";
113:
114: /** Constant for the columnname USER_DATECREATED.<p> */
115: private static final String USER_DATECREATED = "USER_DATECREATED";
116:
117: /** Constant for the columnname USER_DESCRIPTION of the resultset.<p> */
118: private static final String USER_DESCRIPTION = "USER_DESCRIPTION";
119:
120: /** Constant for the columnname USER_INFO.<p> */
121: private static final String USER_INFO = "USER_INFO";
122:
123: /** Constant for the columnname USER_TYPE.<p> */
124: private static final String USER_TYPE = "USER_TYPE";
125:
126: /**
127: * Default constructor.<p>
128: *
129: * @throws IOException if the default sql queries property file could not be read
130: */
131: public CmsUpdateDBCmsUsers() throws IOException {
132:
133: super ();
134: loadQueryProperties(QUERY_PROPERTIES_PREFIX
135: + QUERY_PROPERTY_FILE);
136: }
137:
138: /**
139: * @see org.opencms.setup.update6to7.A_CmsUpdateDBPart#internalExecute(org.opencms.setup.CmsSetupDb)
140: */
141: public void internalExecute(CmsSetupDb dbCon) {
142:
143: System.out.println(new Exception().getStackTrace()[0]
144: .toString());
145: try {
146: if (dbCon.hasTableOrColumn(CMS_USERS_TABLE, USER_TYPE)) {
147: CmsUUID id = createWebusersGroup(dbCon);
148: addWebusersToGroup(dbCon, id);
149: } else {
150: System.out.println("table " + CHECK_CMS_USERDATA
151: + " already exists");
152: }
153: } catch (SQLException e) {
154: e.printStackTrace();
155: }
156: try {
157: // Check if the CMS_USERDATA table exists
158: if (!checkUserDataTable(dbCon)) {
159: createUserDataTable(dbCon); // Could throw Exception during table creation
160:
161: String query = readQuery(QUERY_SELECT_USER_DATA);
162: CmsSetupDBWrapper db = null;
163: try {
164: db = dbCon.executeSqlStatement(query, null);
165: while (db.getResultSet().next()) {
166: String userID = (String) db.getResultSet()
167: .getObject(RESULTSET_USER_ID);
168: System.out.println("UserId: " + userID);
169:
170: try {
171: Blob blob = db.getResultSet().getBlob(
172: RESULTSET_USER_INFO);
173:
174: ByteArrayInputStream bin = new ByteArrayInputStream(
175: blob.getBytes(1, (int) blob
176: .length()));
177: ObjectInputStream oin = new ObjectInputStream(
178: bin);
179:
180: Map infos = (Map) oin.readObject();
181:
182: if (infos == null) {
183: infos = new HashMap();
184: }
185:
186: // Add user address and user description of the current user
187: String userAddress = (String) db
188: .getResultSet().getObject(
189: USER_ADDRESS);
190: if (CmsStringUtil
191: .isNotEmptyOrWhitespaceOnly(userAddress)) {
192: infos.put(USER_ADDRESS, userAddress);
193: }
194: String userDescription = (String) db
195: .getResultSet().getObject(
196: USER_DESCRIPTION);
197: if (CmsStringUtil
198: .isNotEmptyOrWhitespaceOnly(userDescription)) {
199: infos.put(USER_DESCRIPTION,
200: userDescription);
201: }
202:
203: // Write the user data to the table
204: writeAdditionalUserInfo(dbCon, userID,
205: infos);
206: } catch (Throwable e) {
207: e.printStackTrace();
208: }
209: }
210: } finally {
211: if (db != null) {
212: db.close();
213: }
214: }
215:
216: // add the column USER_DATECREATED
217: addUserDateCreated(dbCon);
218:
219: // remove the unnecessary columns from CMS_USERS
220: removeUnnecessaryColumns(dbCon);
221:
222: } else {
223: System.out.println("table " + CHECK_CMS_USERDATA
224: + " already exists");
225: }
226: } catch (SQLException e) {
227: e.printStackTrace();
228: }
229: }
230:
231: /**
232: * Adds the new column USER_DATECREATED to the CMS_USERS table.<p>
233: *
234: * @param dbCon the db connection interface
235: *
236: * @throws SQLException if something goes wrong
237: */
238: protected void addUserDateCreated(CmsSetupDb dbCon)
239: throws SQLException {
240:
241: System.out.println(new Exception().getStackTrace()[0]
242: .toString());
243: // Add the column to the table if necessary
244: if (!dbCon.hasTableOrColumn(CMS_USERS_TABLE, USER_DATECREATED)) {
245: String addUserDateCreated = readQuery(QUERY_ADD_USER_DATECREATED_COLUMN);
246: dbCon.updateSqlStatement(addUserDateCreated, null, null);
247:
248: String setUserDateCreated = readQuery(QUERY_SET_USER_DATECREATED);
249: List param = new ArrayList();
250: // Set the creation date to the current time
251: param.add(new Long(System.currentTimeMillis()));
252:
253: dbCon.updateSqlStatement(setUserDateCreated, null, param);
254: } else {
255: System.out.println("column " + USER_DATECREATED
256: + " in table " + CMS_USERS_TABLE
257: + " already exists");
258: }
259: }
260:
261: /**
262: * Adds all webusers to the new previously created webusers group.<p>
263: *
264: * @param dbCon the db connection interface
265: * @param id the id of the new webusers group
266: *
267: * @throws SQLException if something goes wrong
268: */
269: protected void addWebusersToGroup(CmsSetupDb dbCon, CmsUUID id)
270: throws SQLException {
271:
272: String sql = readQuery(QUERY_ADD_WEBUSERS_TO_GROUP);
273: Map replacements = new HashMap();
274: replacements.put("${GROUP_ID}", id.toString());
275: dbCon.updateSqlStatement(sql, replacements, null);
276: }
277:
278: /**
279: * Checks if the CMS_USERDATA table exists.<p>
280: *
281: * @param dbCon the db connection interface
282: *
283: * @return true if it exists, false if not.
284: */
285: protected boolean checkUserDataTable(CmsSetupDb dbCon) {
286:
287: System.out.println(new Exception().getStackTrace()[0]
288: .toString());
289: return dbCon.hasTableOrColumn(CHECK_CMS_USERDATA, null);
290: }
291:
292: /**
293: * Creates the CMS_USERDATA table if it does not exist yet.<p>
294: *
295: * @param dbCon the db connection interface
296: *
297: * @throws SQLException if soemthing goes wrong
298: */
299: protected void createUserDataTable(CmsSetupDb dbCon)
300: throws SQLException {
301:
302: System.out.println(new Exception().getStackTrace()[0]
303: .toString());
304: String createStatement = readQuery(QUERY_CREATE_TABLE_USERDATA);
305: dbCon.updateSqlStatement(createStatement, null, null);
306: }
307:
308: /**
309: * creates a new group for the webusers.<p>
310: *
311: * @param dbCon the db connection interface
312: *
313: * @return the id of the new generated group
314: *
315: * @throws SQLException if something goes wrong
316: */
317: protected CmsUUID createWebusersGroup(CmsSetupDb dbCon)
318: throws SQLException {
319:
320: String sql = readQuery(QUERY_CREATE_WEBUSERS_GROUP);
321: List params = new ArrayList();
322: CmsUUID id = new CmsUUID();
323: params.add(id.toString());
324: params.add(CmsUUID.getNullUUID().toString());
325: params.add("allWebusersFromUpgrade6to7");
326: params
327: .add("This group was created by the OpenCms Upgrade Wizard to facilitate the handling of former called WebUsers, can be deleted if needed.");
328: params.add(new Integer(0));
329: params.add("/");
330: dbCon.updateSqlStatement(sql, null, params);
331: return id;
332: }
333:
334: /**
335: * Removes the columns USER_INFO, USER_ADDRESS, USER_DESCRIPTION and USER_TYPE from the CMS_USERS table.<p>
336: *
337: * @param dbCon the db connection interface
338: *
339: * @throws SQLException if something goes wrong
340: */
341: protected void removeUnnecessaryColumns(CmsSetupDb dbCon)
342: throws SQLException {
343:
344: System.out.println(new Exception().getStackTrace()[0]
345: .toString());
346: // Get the sql queries to drop the columns
347: String dropUserInfo = readQuery(QUERY_DROP_USER_INFO_COLUMN);
348: String dropUserAddress = readQuery(QUERY_DROP_USER_ADDRESS_COLUMN);
349: String dropUserDescription = readQuery(QUERY_DROP_USER_DESCRIPTION_COLUMN);
350: String dropUserType = readQuery(QUERY_DROP_USER_TYPE_COLUMN);
351:
352: // execute the queries to drop the columns, if they exist
353: if (dbCon.hasTableOrColumn(CMS_USERS_TABLE, USER_INFO)) {
354: dbCon.updateSqlStatement(dropUserInfo, null, null);
355: } else {
356: System.out.println("no column " + USER_INFO + " in table "
357: + CMS_USERS_TABLE + " found");
358: }
359: if (dbCon.hasTableOrColumn(CMS_USERS_TABLE, USER_ADDRESS)) {
360: dbCon.updateSqlStatement(dropUserAddress, null, null);
361: } else {
362: System.out.println("no column " + USER_ADDRESS
363: + " in table " + CMS_USERS_TABLE + " found");
364: }
365: if (dbCon.hasTableOrColumn(CMS_USERS_TABLE, USER_DESCRIPTION)) {
366: dbCon.updateSqlStatement(dropUserDescription, null, null);
367: } else {
368: System.out.println("no column " + USER_DESCRIPTION
369: + " in table " + CMS_USERS_TABLE + " found");
370: }
371: if (dbCon.hasTableOrColumn(CMS_USERS_TABLE, USER_TYPE)) {
372: dbCon.updateSqlStatement(dropUserType, null, null);
373: } else {
374: System.out.println("no column " + USER_TYPE + " in table "
375: + CMS_USERS_TABLE + " found");
376: }
377: }
378:
379: /**
380: * Writes the additional user infos to the database.<p>
381: *
382: * @param dbCon the db connection interface
383: * @param id the user id
384: * @param additionalInfo the additional info of the user
385: */
386: protected void writeAdditionalUserInfo(CmsSetupDb dbCon, String id,
387: Map additionalInfo) {
388:
389: Iterator entries = additionalInfo.entrySet().iterator();
390: while (entries.hasNext()) {
391: Map.Entry entry = (Map.Entry) entries.next();
392: if (entry.getKey() != null && entry.getValue() != null) {
393: // Write the additional user information to the database
394: writeUserInfo(dbCon, id, (String) entry.getKey(), entry
395: .getValue());
396: }
397: }
398: }
399:
400: /**
401: * Writes one set of additional user info (key and its value) to the CMS_USERDATA table.<p>
402: *
403: * @param dbCon the db connection interface
404: * @param id the user id
405: * @param key the data key
406: * @param value the data value
407: */
408: protected void writeUserInfo(CmsSetupDb dbCon, String id,
409: String key, Object value) {
410:
411: String query = readQuery(QUERY_INSERT_CMS_USERDATA);
412:
413: try {
414: // Generate the list of parameters to add into the user info table
415: List params = new ArrayList();
416: params.add(id);
417: params.add(key);
418: params.add(value);
419: params.add(value.getClass().getName());
420:
421: dbCon.updateSqlStatement(query, null, params);
422: } catch (SQLException e) {
423: e.printStackTrace();
424: }
425: }
426: }
|