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;
029
030import org.opencms.setup.CmsSetupDBWrapper;
031import org.opencms.setup.CmsSetupDb;
032import org.opencms.setup.db.A_CmsUpdateDBPart;
033import org.opencms.util.CmsStringUtil;
034
035import java.io.IOException;
036import java.sql.SQLException;
037import java.util.ArrayList;
038import java.util.Arrays;
039import java.util.Collections;
040import java.util.HashMap;
041import java.util.Iterator;
042import java.util.List;
043import java.util.Map;
044
045/**
046 * This class drops all indexes of each table of the database.<p>
047 * This is done so that the indexes can be updated to the version 6.2.3 and afterwards to version 7
048 *
049 * @since 7.0.0
050 */
051public class CmsUpdateDBDropOldIndexes extends A_CmsUpdateDBPart {
052
053    /** Constant array of the base tables of the OpenCms 7.0.x installation.<p> */
054    protected static final String[] CMS_TABLES = {
055        "CMS_BACKUP_CONTENTS",
056        "CMS_BACKUP_PROJECTRESOURCES",
057        "CMS_BACKUP_PROJECTS",
058        "CMS_BACKUP_PROPERTIES",
059        "CMS_BACKUP_PROPERTYDEF",
060        "CMS_BACKUP_RESOURCES",
061        "CMS_BACKUP_STRUCTURE",
062        "CMS_GROUPS",
063        "CMS_GROUPUSERS",
064        "CMS_OFFLINE_ACCESSCONTROL",
065        "CMS_OFFLINE_CONTENTS",
066        "CMS_OFFLINE_PROPERTIES",
067        "CMS_OFFLINE_PROPERTYDEF",
068        "CMS_OFFLINE_RESOURCES",
069        "CMS_OFFLINE_STRUCTURE",
070        "CMS_ONLINE_ACCESSCONTROL",
071        "CMS_ONLINE_CONTENTS",
072        "CMS_ONLINE_PROPERTIES",
073        "CMS_ONLINE_PROPERTYDEF",
074        "CMS_ONLINE_RESOURCES",
075        "CMS_ONLINE_STRUCTURE",
076        "CMS_PROJECTRESOURCES",
077        "CMS_PROJECTS",
078        "CMS_PUBLISH_HISTORY",
079        "CMS_STATICEXPORT_LINKS",
080        "CMS_SYSTEMID",
081        "CMS_TASK",
082        "CMS_TASKLOG",
083        "CMS_TASKPAR",
084        "CMS_TASKTYPE",
085        "CMS_USERS",
086
087    };
088
089    /** Constant ArrayList of the tables of the base OpenCms 7.0.x installation.<p> */
090    protected static final List<String> CMS_TABLES_LIST = Collections.unmodifiableList(Arrays.asList(CMS_TABLES));
091
092    /** Constant for the sql query to drop an index from a table.<p> */
093    protected static final String QUERY_DROP_INDEX = "Q_DROP_INDEX";
094
095    /** Constant for the sql query to show the indexes of a table.<p> */
096    protected static final String QUERY_SHOW_INDEX = "Q_SHOW_INDEXES";
097
098    /** Constant for the sql query replacement of the tablename.<p> */
099    protected static final String REPLACEMENT_TABLENAME = "${tablename}";
100
101    /** Constant for the field of the index name.<p> */
102    private static final String FIELD_INDEX = "KEY_NAME";
103
104    /** Constant for the primary key of a the index result set.<p> */
105    private static final String PRIMARY_KEY = "PRIMARY";
106
107    /** Constant for the SQL query properties.<p> */
108    private static final String QUERY_PROPERTY_FILE = "cms_drop_all_indexes_queries.properties";
109
110    /** Constant for the sql query replacement of the index.<p> */
111    private static final String REPLACEMENT_INDEX = "${dropindexes}";
112
113    /**
114     * Constructor.<p>
115     *
116     * @throws IOException if the query properties cannot be read
117     */
118    public CmsUpdateDBDropOldIndexes()
119    throws IOException {
120
121        super();
122        loadQueryProperties(getPropertyFileLocation() + QUERY_PROPERTY_FILE);
123    }
124
125    /**
126     * @see org.opencms.setup.db.A_CmsUpdateDBPart#internalExecute(org.opencms.setup.CmsSetupDb)
127     */
128    @Override
129    protected void internalExecute(CmsSetupDb dbCon) {
130
131        List<String> tablenames = CMS_TABLES_LIST;
132
133        // Iterate over all the tables.
134        for (Iterator<String> tableIterator = tablenames.iterator(); tableIterator.hasNext();) {
135            String tablename = tableIterator.next();
136            System.out.println("dropping indexes for table " + tablename);
137            // Check if the table is existing
138            if (dbCon.hasTableOrColumn(tablename, null)) {
139                try {
140                    List<String> indexes = getIndexes(dbCon, tablename);
141
142                    // Iterate over the indexes of one table
143                    StringBuffer buffer = new StringBuffer();
144                    for (Iterator<String> indexIt = indexes.iterator(); indexIt.hasNext();) {
145                        String index = indexIt.next();
146                        // Drop the primary key
147                        if (index.equalsIgnoreCase(PRIMARY_KEY)) {
148                            buffer.append("DROP PRIMARY KEY");
149                            if (indexIt.hasNext()) {
150                                buffer.append(",");
151                            }
152                        } else {
153                            // Drop the index
154                            buffer.append(" DROP INDEX ");
155                            buffer.append(index);
156                            if (indexIt.hasNext()) {
157                                buffer.append(",");
158                            }
159                        }
160                    }
161                    String tempIndex = readQuery(tablename);
162                    if (CmsStringUtil.isNotEmptyOrWhitespaceOnly(tempIndex)) {
163                        buffer.append(", ");
164                        buffer.append(tempIndex);
165                    }
166
167                    // Only execute the query if there is something to change
168                    if (CmsStringUtil.isNotEmptyOrWhitespaceOnly(buffer.toString())) {
169                        String dropIndexQuery = readQuery(QUERY_DROP_INDEX);
170                        Map<String, String> replacer = new HashMap<String, String>();
171                        replacer.put(REPLACEMENT_TABLENAME, tablename);
172                        replacer.put(REPLACEMENT_INDEX, buffer.toString());
173                        // Drop the indexes
174                        try {
175                            dbCon.updateSqlStatement(dropIndexQuery, replacer, null);
176                        } catch (SQLException e) {
177                            e.printStackTrace();
178                        }
179                    }
180
181                } catch (SQLException e) {
182                    e.printStackTrace();
183                }
184            }
185        }
186    }
187
188    /**
189     * Gets the indexes for a table.<p>
190     *
191     * @param dbCon the db connection interface
192     * @param tablename the table to get the indexes from
193     *
194     * @return a list of indexes
195     *
196     * @throws SQLException if somehting goes wrong
197     */
198    private List<String> getIndexes(CmsSetupDb dbCon, String tablename) throws SQLException {
199
200        List<String> indexes = new ArrayList<String>();
201        String tableIndex = readQuery(QUERY_SHOW_INDEX);
202        Map<String, String> replacer = new HashMap<String, String>();
203        replacer.put(REPLACEMENT_TABLENAME, tablename);
204        CmsSetupDBWrapper db = null;
205        try {
206            db = dbCon.executeSqlStatement(tableIndex, replacer);
207            while (db.getResultSet().next()) {
208                String index = db.getResultSet().getString(FIELD_INDEX);
209
210                if (!indexes.contains(index)) {
211                    indexes.add(index);
212                }
213
214            }
215        } finally {
216            if (db != null) {
217                db.close();
218            }
219        }
220        return indexes;
221    }
222}