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}