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;
032
033import java.io.IOException;
034import java.sql.SQLException;
035import java.util.HashMap;
036import java.util.Map;
037
038/**
039 * PostgreSQL implementation to update the project ids to uuids.<p>
040 *
041 * @since 7.0.2
042 */
043public class CmsUpdateDBProjectId extends org.opencms.setup.db.update6to7.CmsUpdateDBProjectId {
044
045    /** Constant for the sql primary key of the CMS_PROJECTRESOURCES table.<p> */
046    private static final String COLUMN_PROJECT_ID_RESOURCE_PATH = "PROJECT_ID,RESOURCE_PATH";
047
048    /** Constant for the SQL query properties.<p> */
049    private static final String QUERY_PROPERTY_FILE = "cms_projectid_queries.properties";
050
051    /** Constant for the replacement in the sql query. */
052    private static final String REPLACEMENT_TABLEINDEX_SPACE = "${indexTablespace}";
053
054    /**
055     * Constructor.<p>
056     *
057     * @throws IOException if the sql queries properties file could not be read
058     */
059    public CmsUpdateDBProjectId()
060    throws IOException {
061
062        super();
063        loadQueryProperties(getPropertyFileLocation() + QUERY_PROPERTY_FILE);
064    }
065
066    /**
067     * @see org.opencms.setup.db.update6to7.CmsUpdateDBProjectId#addPrimaryKey(org.opencms.setup.CmsSetupDb, java.lang.String, java.lang.String)
068     */
069    @Override
070    protected void addPrimaryKey(CmsSetupDb dbCon, String tablename, String primaryKey) throws SQLException {
071
072        String indexTablespace = m_poolData.get("indexTablespace");
073
074        System.out.println(new Exception().getStackTrace()[0].toString());
075        if (dbCon.hasTableOrColumn(tablename, null)) {
076            String query = readQuery(QUERY_ADD_PRIMARY_KEY);
077            Map<String, String> replacer = new HashMap<String, String>();
078            replacer.put(REPLACEMENT_TABLENAME, tablename);
079            replacer.put(REPLACEMENT_PRIMARY_KEY, primaryKey);
080            replacer.put(REPLACEMENT_TABLEINDEX_SPACE, indexTablespace);
081            dbCon.updateSqlStatement(query, replacer, null);
082        } else {
083            System.out.println("table " + tablename + " does not exists");
084        }
085    }
086
087    /**
088     * @see org.opencms.setup.db.update6to7.CmsUpdateDBProjectId#addUUIDColumnToTable(org.opencms.setup.CmsSetupDb, java.lang.String, java.lang.String)
089     */
090    @Override
091    protected void addUUIDColumnToTable(CmsSetupDb dbCon, String tablename, String column) throws SQLException {
092
093        System.out.println(new Exception().getStackTrace()[0].toString());
094        if (!dbCon.hasTableOrColumn(tablename, column)) {
095            String query = readQuery(QUERY_ADD_TEMP_UUID_COLUMN); // Get the query
096            // if the table is not one of the ONLINE or OFFLINE resources add the new column in the first position
097            if (!RESOURCES_TABLES_LIST.contains(tablename)) {
098                //query += " FIRST";
099            }
100            Map<String, String> replacer = new HashMap<String, String>(); // Build the replacements
101            replacer.put(REPLACEMENT_TABLENAME, tablename);
102            replacer.put(REPLACEMENT_COLUMN, column);
103            dbCon.updateSqlStatement(query, replacer, null); // execute the query
104        } else {
105            System.out.println("column " + column + " in table " + tablename + " already exists");
106        }
107    }
108
109    /**
110     * @see org.opencms.setup.db.update6to7.CmsUpdateDBProjectId#checkColumnTypeProjectId(int)
111     */
112    @Override
113    protected boolean checkColumnTypeProjectId(int type) {
114
115        return type == java.sql.Types.INTEGER;
116    }
117
118    /**
119     * @see org.opencms.setup.db.update6to7.CmsUpdateDBProjectId#createHistProjectsTable(org.opencms.setup.CmsSetupDb)
120     */
121    @Override
122    protected void createHistProjectsTable(CmsSetupDb dbCon) throws SQLException {
123
124        System.out.println(new Exception().getStackTrace()[0].toString());
125        if (!dbCon.hasTableOrColumn(HISTORY_PROJECTS_TABLE, null)) {
126            String createStatement = readQuery(QUERY_CREATE_HISTORY_PROJECTS_TABLE);
127
128            String indexTablespace = m_poolData.get("indexTablespace");
129            Map<String, String> replacer = new HashMap<String, String>();
130            replacer.put(REPLACEMENT_TABLEINDEX_SPACE, indexTablespace);
131
132            dbCon.updateSqlStatement(createStatement, replacer, null);
133            transferDataToHistoryTable(dbCon);
134        } else {
135            System.out.println("table " + HISTORY_PROJECTS_TABLE + " already exists");
136        }
137    }
138
139    /**
140     * @see org.opencms.setup.db.update6to7.CmsUpdateDBProjectId#createTempTable(org.opencms.setup.CmsSetupDb)
141     */
142    @Override
143    protected void createTempTable(CmsSetupDb dbCon) throws SQLException {
144
145        System.out.println(new Exception().getStackTrace()[0].toString());
146        if (!dbCon.hasTableOrColumn(TEMPORARY_TABLE_NAME, null)) {
147            String createStatement = readQuery(QUERY_CREATE_TEMP_TABLE_UUIDS);
148
149            String indexTablespace = m_poolData.get("indexTablespace");
150            Map<String, String> replacer = new HashMap<String, String>();
151            replacer.put(REPLACEMENT_TABLEINDEX_SPACE, indexTablespace);
152
153            dbCon.updateSqlStatement(createStatement, replacer, null);
154        } else {
155            System.out.println("table " + TEMPORARY_TABLE_NAME + " already exists");
156        }
157    }
158
159    /**
160     * @see org.opencms.setup.db.update6to7.CmsUpdateDBProjectId#getColumnProjectIdResourcePath()
161     */
162    @Override
163    protected String getColumnProjectIdResourcePath() {
164
165        return COLUMN_PROJECT_ID_RESOURCE_PATH;
166    }
167
168    /**
169     * @see org.opencms.setup.db.update6to7.CmsUpdateDBProjectId#needsUpdating(org.opencms.setup.CmsSetupDb, java.lang.String)
170     */
171    @Override
172    protected boolean needsUpdating(CmsSetupDb dbCon, String tablename) throws SQLException {
173
174        System.out.println(new Exception().getStackTrace()[0].toString());
175        boolean result = true;
176
177        String query = readQuery(QUERY_DESCRIBE_TABLE);
178        Map<String, String> replacer = new HashMap<String, String>();
179        replacer.put(REPLACEMENT_TABLENAME, tablename);
180        CmsSetupDBWrapper db = null;
181        try {
182            db = dbCon.executeSqlStatement(query, replacer);
183
184            while (db.getResultSet().next()) {
185                String fieldname = db.getResultSet().getString("COLUMN_NAME");
186                if (fieldname.equals(COLUMN_PROJECT_ID) || fieldname.equals(COLUMN_PROJECT_LASTMODIFIED)) {
187                    try {
188                        String fieldtype = db.getResultSet().getString("DATA_TYPE");
189                        // If the type is varchar then no update needs to be done.
190                        if (fieldtype.indexOf("VARCHAR") > -1) {
191                            return false;
192                        }
193                    } catch (SQLException e) {
194                        result = true;
195                    }
196                }
197            }
198        } finally {
199            if (db != null) {
200                db.close();
201            }
202        }
203
204        return result;
205    }
206}