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.oracle;
029
030import org.opencms.setup.CmsSetupDBWrapper;
031import org.opencms.setup.CmsSetupDb;
032
033import java.io.IOException;
034import java.sql.SQLException;
035import java.util.ArrayList;
036import java.util.Arrays;
037import java.util.Collections;
038import java.util.HashMap;
039import java.util.Iterator;
040import java.util.List;
041import java.util.Map;
042
043/**
044 * Oracle implementation to drop the old indexes from the database.<p>
045 *
046 * @since 7.0.0
047 */
048public class CmsUpdateDBDropOldIndexes extends org.opencms.setup.db.update6to7.CmsUpdateDBDropOldIndexes {
049
050    /** Constant for the field of the constraint name.<p> */
051    private static final String FIELD_CONSTRAINT_ORACLE = "CONSTRAINT_NAME";
052
053    /** Constant for the field of the index name.<p> */
054    private static final String FIELD_INDEX_ORACLE = "INDEX_NAME";
055
056    /** Constant for the sql query to drop a unique index key. */
057    private static final String QUERY_DROP_CONSTRAINT = "Q_DROP_CONSTRAINT";
058
059    /** Constant for the SQL query properties.<p> */
060    private static final String QUERY_PROPERTY_FILE_ORACLE = "cms_drop_all_indexes_queries.properties";
061
062    /** Constant for the sql query to list contraints for a table. */
063    private static final String QUERY_SHOW_CONSTRAINTS = "Q_SHOW_CONSTRAINTS";
064
065    /** Constant for the replacement of the index name. */
066    private static final String REPLACEMENT_INDEX_ORACLE = "${indexname}";
067
068    /** Constant array of the temporary indexes. */
069    private static final String[] TEMP_INDEXES = {
070        "CMS_BACKUP_CONTENTS_INDEX_1",
071        "CMS_BACKUP_CONTENTS_INDEX_2",
072        "CMS_BACKUP_PROJECTRESOURCES_INDEX_1",
073        "CMS_BACKUP_PROJECTS_INDEX_1",
074        "CMS_BACKUP_PROJECTS_INDEX_2",
075        "CMS_BACKUP_PROJECTS_INDEX_3",
076        "CMS_BACKUP_PROJECTS_INDEX_4",
077        "CMS_BACKUP_PROJECTS_INDEX_5",
078        "CMS_BACKUP_PROJECTS_INDEX_6",
079        "CMS_BACKUP_PROPERTIES_INDEX_1",
080        "CMS_BACKUP_PROPERTIES_INDEX_2",
081        "CMS_BACKUP_PROPERTIES_INDEX_3",
082        "CMS_BACKUP_PROPERTYDEF_INDEX_1",
083        "CMS_BACKUP_RESOURCES_INDEX_1",
084        "CMS_BACKUP_RESOURCES_INDEX_2",
085        "CMS_BACKUP_RESOURCES_INDEX_3",
086        "CMS_BACKUP_RESOURCES_INDEX_4",
087        "CMS_BACKUP_RESOURCES_INDEX_5",
088        "CMS_BACKUP_STRUCTURE_INDEX_1",
089        "CMS_BACKUP_STRUCTURE_INDEX_2",
090        "CMS_BACKUP_STRUCTURE_INDEX_3",
091        "CMS_BACKUP_STRUCTURE_INDEX_4",
092        "CMS_GROUPS_INDEX_1",
093        "CMS_OFFLINE_CONTENTS_INDEX_1",
094        "CMS_OFFLINE_RESOURCES_INDEX_1",
095        "CMS_OFFLINE_RESOURCES_INDEX_2",
096        "CMS_OFFLINE_RESOURCES_INDEX_3",
097        "CMS_OFFLINE_RESOURCES_INDEX_4",
098        "CMS_OFFLINE_STRUCTURE_INDEX_1",
099        "CMS_OFFLINE_STRUCTURE_INDEX_2",
100        "CMS_OFFLINE_STRUCTURE_INDEX_3",
101        "CMS_ONLINE_CONTENTS_INDEX_1",
102        "CMS_ONLINE_RESOURCES_INDEX_1",
103        "CMS_ONLINE_RESOURCES_INDEX_2",
104        "CMS_ONLINE_RESOURCES_INDEX_3",
105        "CMS_ONLINE_RESOURCES_INDEX_4",
106        "CMS_ONLINE_STRUCTURE_INDEX_1",
107        "CMS_ONLINE_STRUCTURE_INDEX_2",
108        "CMS_ONLINE_STRUCTURE_INDEX_3",
109        "CMS_PROJECTRESOURCES_INDEX_1",
110        "CMS_PROJECTS_INDEX_1",
111        "CMS_PROJECTS_INDEX_2",
112        "CMS_PROJECTS_INDEX_3",
113        "CMS_PROJECTS_INDEX_4",
114        "CMS_PUBLISH_HISTORY_INDEX_1",
115        "CMS_PUBLISH_HISTORY_INDEX_2"};
116
117    /** Constant Array List of the temporary indexes. */
118    private static final List<String> TEMP_INDEXES_LIST = Collections.unmodifiableList(Arrays.asList(TEMP_INDEXES));
119
120    /**
121     * Constructor.<p>
122     *
123     * @throws IOException if the sql queries properties file could not be read
124     */
125    public CmsUpdateDBDropOldIndexes()
126    throws IOException {
127
128        super();
129        loadQueryProperties(getPropertyFileLocation() + QUERY_PROPERTY_FILE_ORACLE);
130    }
131
132    // No implementation yet
133
134    /**
135     * @see org.opencms.setup.db.A_CmsUpdateDBPart#internalExecute(org.opencms.setup.CmsSetupDb)
136     */
137    @Override
138    protected void internalExecute(CmsSetupDb dbCon) {
139
140        List<String> tablenames = CMS_TABLES_LIST;
141
142        // Iterate over all the tables.
143        for (Iterator<String> tableIterator = tablenames.iterator(); tableIterator.hasNext();) {
144            String tablename = tableIterator.next();
145            System.out.println("dropping indexes for table " + tablename);
146            // Check if the table is existing
147            if (dbCon.hasTableOrColumn(tablename, null)) {
148                try {
149
150                    // First drop constraints
151                    List<String> constraints = getConstraints(dbCon, tablename);
152                    Iterator<String> iter = constraints.iterator();
153                    while (iter.hasNext()) {
154                        String constraint = iter.next();
155
156                        String dropConstraint = readQuery(QUERY_DROP_CONSTRAINT);
157                        Map<String, String> replacer = new HashMap<String, String>();
158                        replacer.put(REPLACEMENT_TABLENAME, tablename);
159                        replacer.put(REPLACEMENT_INDEX_ORACLE, constraint);
160                        dbCon.updateSqlStatement(dropConstraint, replacer, null);
161                    }
162
163                    // Drop the indexes from the table.
164                    List<String> indexes = getIndexes(dbCon, tablename);
165                    iter = indexes.iterator();
166                    while (iter.hasNext()) {
167                        String index = iter.next();
168
169                        // Drop the index
170                        String dropIndex = readQuery(QUERY_DROP_INDEX);
171                        Map<String, String> replacerIndex = new HashMap<String, String>();
172                        replacerIndex.put(REPLACEMENT_INDEX_ORACLE, index);
173                        dbCon.updateSqlStatement(dropIndex, replacerIndex, null);
174                    }
175                } catch (SQLException e) {
176                    e.printStackTrace();
177                }
178            }
179        }
180
181        // Create the temporary indexes
182        // Each index must be created in its own query
183        for (Iterator<String> tempIndexes = TEMP_INDEXES_LIST.iterator(); tempIndexes.hasNext();) {
184            try {
185                String createIndex = tempIndexes.next();
186                String creationQuery = readQuery(createIndex);
187                dbCon.updateSqlStatement(creationQuery, null, null);
188            } catch (SQLException e) {
189                e.printStackTrace();
190            }
191        }
192    }
193
194    /**
195     * Gets the constraints for a table.<p>
196     *
197     * @param dbCon the db connection interface
198     * @param tablename the table to get the indexes from
199     *
200     * @return a list of constraints
201     *
202     * @throws SQLException if something goes wrong
203     */
204    private List<String> getConstraints(CmsSetupDb dbCon, String tablename) throws SQLException {
205
206        List<String> constraints = new ArrayList<String>();
207        String tableConstraints = readQuery(QUERY_SHOW_CONSTRAINTS);
208        Map<String, String> replacer = new HashMap<String, String>();
209        replacer.put(REPLACEMENT_TABLENAME, tablename);
210        CmsSetupDBWrapper db = null;
211        try {
212            db = dbCon.executeSqlStatement(tableConstraints, replacer);
213            while (db.getResultSet().next()) {
214                String constraint = db.getResultSet().getString(FIELD_CONSTRAINT_ORACLE);
215                if (!constraints.contains(constraint)) {
216                    constraints.add(constraint);
217                }
218
219            }
220        } finally {
221            if (db != null) {
222                db.close();
223            }
224        }
225
226        return constraints;
227    }
228
229    /**
230     * Gets the indexes for a table.<p>
231     *
232     * @param dbCon the db connection interface
233     * @param tablename the table to get the indexes from
234     *
235     * @return a list of indexes
236     *
237     * @throws SQLException if something goes wrong
238     */
239    private List<String> getIndexes(CmsSetupDb dbCon, String tablename) throws SQLException {
240
241        List<String> indexes = new ArrayList<String>();
242        String tableIndex = readQuery(QUERY_SHOW_INDEX);
243        Map<String, String> replacer = new HashMap<String, String>();
244        replacer.put(REPLACEMENT_TABLENAME, tablename);
245        CmsSetupDBWrapper db = null;
246        try {
247            db = dbCon.executeSqlStatement(tableIndex, replacer);
248            while (db.getResultSet().next()) {
249                String index = db.getResultSet().getString(FIELD_INDEX_ORACLE);
250                if (!indexes.contains(index)) {
251                    indexes.add(index);
252                }
253            }
254        } finally {
255            if (db != null) {
256                db.close();
257            }
258        }
259        return indexes;
260    }
261}