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}