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.postgresql; 029 030import org.opencms.setup.CmsSetupDBWrapper; 031import org.opencms.setup.CmsSetupDb; 032import org.opencms.util.CmsCollectionsGenericWrapper; 033import org.opencms.util.CmsDataTypeUtil; 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.Connection; 041import java.sql.PreparedStatement; 042import java.sql.SQLException; 043import java.util.ArrayList; 044import java.util.HashMap; 045import java.util.Iterator; 046import java.util.List; 047import java.util.Map; 048 049/** 050 * PostgreSQL implementation of the generic update class for the Users.<p> 051 * 052 * @since 7.0.2 053 */ 054public class CmsUpdateDBCmsUsers extends org.opencms.setup.db.update6to7.CmsUpdateDBCmsUsers { 055 056 /** Constant for the SQL query properties.<p> */ 057 private static final String QUERY_PROPERTY_FILE = "cms_users_queries.properties"; 058 059 /** Constant for the replacement in the sql query. */ 060 private static final String REPLACEMENT_TABLEINDEX_SPACE = "${indexTablespace}"; 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 columnname USER_ID of the resultset.<p> */ 069 private static final String RESULTSET_USER_ID = "USER_ID"; 070 071 /** Constant for the columnname USER_INFO of the resultset.<p> */ 072 private static final String RESULTSET_USER_INFO = "USER_INFO"; 073 074 /** Constant for the columnname USER_ADDRESS of the resultset.<p> */ 075 private static final String USER_ADDRESS = "USER_ADDRESS"; 076 077 /** Constant for the columnname USER_DESCRIPTION of the resultset.<p> */ 078 private static final String USER_DESCRIPTION = "USER_DESCRIPTION"; 079 080 /** Constant for the columnname USER_TYPE.<p> */ 081 private static final String USER_TYPE = "USER_TYPE"; 082 083 /** Constant for the query to the select the user infos for a user.<p> */ 084 private static final String QUERY_SELECT_USER_DATA = "Q_SELECT_USER_DATA"; 085 086 /** 087 * Constructor.<p> 088 * 089 * @throws IOException if the sql queries properties file could not be read 090 */ 091 public CmsUpdateDBCmsUsers() 092 throws IOException { 093 094 super(); 095 loadQueryProperties(getPropertyFileLocation() + QUERY_PROPERTY_FILE); 096 } 097 098 /** 099 * @see org.opencms.setup.db.A_CmsUpdateDBPart#internalExecute(org.opencms.setup.CmsSetupDb) 100 */ 101 @Override 102 protected void internalExecute(CmsSetupDb dbCon) { 103 104 System.out.println(new Exception().getStackTrace()[0].toString()); 105 try { 106 if (dbCon.hasTableOrColumn(CMS_USERS_TABLE, USER_TYPE)) { 107 CmsUUID id = createWebusersGroup(dbCon); 108 addWebusersToGroup(dbCon, id); 109 } else { 110 System.out.println("table " + CHECK_CMS_USERDATA + " already exists"); 111 } 112 } catch (SQLException e) { 113 e.printStackTrace(); 114 } 115 try { 116 // Check if the CMS_USERDATA table exists 117 if (!checkUserDataTable(dbCon)) { 118 createUserDataTable(dbCon); // Could throw Exception during table creation 119 120 String query = readQuery(QUERY_SELECT_USER_DATA); 121 CmsSetupDBWrapper db = null; 122 try { 123 db = dbCon.executeSqlStatement(query, null); 124 while (db.getResultSet().next()) { 125 String userID = (String)db.getResultSet().getObject(RESULTSET_USER_ID); 126 127 try { 128 byte[] blob = db.getResultSet().getBytes(RESULTSET_USER_INFO); 129 130 ByteArrayInputStream bin = new ByteArrayInputStream(blob); 131 ObjectInputStream oin = new ObjectInputStream(bin); 132 133 Map<String, Object> infos = CmsCollectionsGenericWrapper.map(oin.readObject()); 134 135 if (infos == null) { 136 infos = new HashMap<String, Object>(); 137 } 138 139 // Add user address and user description of the current user 140 String userAddress = (String)db.getResultSet().getObject(USER_ADDRESS); 141 if (CmsStringUtil.isNotEmptyOrWhitespaceOnly(userAddress)) { 142 infos.put(USER_ADDRESS, userAddress); 143 } 144 String userDescription = (String)db.getResultSet().getObject(USER_DESCRIPTION); 145 if (CmsStringUtil.isNotEmptyOrWhitespaceOnly(userDescription)) { 146 infos.put(USER_DESCRIPTION, userDescription); 147 } 148 149 // Write the user data to the table 150 writeAdditionalUserInfo(dbCon, userID, infos); 151 } catch (Throwable e) { 152 e.printStackTrace(); 153 } 154 } 155 } finally { 156 if (db != null) { 157 db.close(); 158 } 159 } 160 161 // add the column USER_DATECREATED 162 addUserDateCreated(dbCon); 163 164 // remove the unnecessary columns from CMS_USERS 165 removeUnnecessaryColumns(dbCon); 166 167 } else { 168 System.out.println("table " + CHECK_CMS_USERDATA + " already exists"); 169 } 170 } catch (SQLException e) { 171 e.printStackTrace(); 172 } 173 } 174 175 /** 176 * @see org.opencms.setup.db.update6to7.CmsUpdateDBCmsUsers#createUserDataTable(org.opencms.setup.CmsSetupDb) 177 */ 178 @Override 179 protected void createUserDataTable(CmsSetupDb dbCon) throws SQLException { 180 181 String indexTablespace = m_poolData.get("indexTablespace"); 182 183 Map<String, String> replacer = new HashMap<String, String>(); 184 replacer.put(REPLACEMENT_TABLEINDEX_SPACE, indexTablespace); 185 186 String createStatement = readQuery(QUERY_CREATE_TABLE_USERDATA); 187 dbCon.updateSqlStatement(createStatement, replacer, null); 188 189 // create indices 190 List<String> indexElements = new ArrayList<String>(); 191 indexElements.add("CMS_USERDATA_01_IDX_INDEX"); 192 indexElements.add("CMS_USERDATA_02_IDX_INDEX"); 193 194 Iterator<String> iter = indexElements.iterator(); 195 while (iter.hasNext()) { 196 String stmt = readQuery(iter.next()); 197 try { 198 // Create the index 199 dbCon.updateSqlStatement(stmt, replacer, null); 200 } catch (SQLException e) { 201 e.printStackTrace(); 202 } 203 } 204 } 205 206 /** 207 * Writes one set of additional user info (key and its value) to the CMS_USERDATA table.<p> 208 * 209 * @param dbCon the db connection interface 210 * @param id the user id 211 * @param key the data key 212 * @param value the data value 213 */ 214 @Override 215 protected void writeUserInfo(CmsSetupDb dbCon, String id, String key, Object value) { 216 217 Connection conn = dbCon.getConnection(); 218 219 try { 220 PreparedStatement p = conn.prepareStatement(readQuery(QUERY_INSERT_CMS_USERDATA)); 221 p.setString(1, id); 222 p.setString(2, key); 223 p.setBytes(3, CmsDataTypeUtil.dataSerialize(value)); 224 p.setString(4, value.getClass().getName()); 225 p.executeUpdate(); 226 conn.commit(); 227 228 } catch (SQLException e) { 229 e.printStackTrace(); 230 } catch (IOException e) { 231 e.printStackTrace(); 232 } 233 } 234}