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.HashMap; 037import java.util.Iterator; 038import java.util.List; 039import java.util.Map; 040 041/** 042 * Oracle implementation for the creation of the indexes of the tables in version 7.<p> 043 * 044 * @since 7.0.0 045 */ 046public class CmsUpdateDBCreateIndexes7 extends org.opencms.setup.db.update6to7.CmsUpdateDBCreateIndexes7 { 047 048 /** Constant for the field of the constraint name.<p> */ 049 private static final String FIELD_CONSTRAINT = "CONSTRAINT_NAME"; 050 051 /** Constant for the field of the index name.<p> */ 052 private static final String FIELD_INDEX = "INDEX_NAME"; 053 054 /** Constant for the sql query to drop the primary key of a table. */ 055 private static final String QUERY_DROP_CONSTRAINT = "Q_DROP_CONSTRAINT"; 056 057 /** Constant for the sql query to drop an index. */ 058 private static final String QUERY_DROP_INDEX = "Q_DROP_INDEX"; 059 060 /** Constant for the SQL query properties.<p> */ 061 private static final String QUERY_PROPERTY_FILE = "cms_add_new_indexes_queries.properties"; 062 063 /** Constant for the sql query to list contraints for a table. */ 064 private static final String QUERY_SHOW_CONSTRAINTS = "Q_SHOW_CONSTRAINTS"; 065 066 /** Constant for the replacement of the indexname. */ 067 private static final String REPLACEMENT_INDEXNAME = "${indexname}"; 068 069 /** Constant for the replacement in the sql query. */ 070 private static final String REPLACEMENT_TABLEINDEX_SPACE = "${indexTablespace}"; 071 072 /** 073 * Constructor.<p> 074 * 075 * @throws IOException if the sql queries properties file could not be read 076 */ 077 public CmsUpdateDBCreateIndexes7() 078 throws IOException { 079 080 super(); 081 loadQueryProperties(getPropertyFileLocation() + QUERY_PROPERTY_FILE); 082 } 083 084 /** 085 * @see org.opencms.setup.db.A_CmsUpdateDBPart#internalExecute(org.opencms.setup.CmsSetupDb) 086 */ 087 @Override 088 protected void internalExecute(CmsSetupDb dbCon) { 089 090 System.out.println(new Exception().getStackTrace()[0].toString()); 091 List<String> elements = new ArrayList<String>(); 092 elements.add("CMS_CONTENTS"); 093 elements.add("CMS_GROUPS"); 094 elements.add("CMS_GROUPUSERS"); 095 elements.add("CMS_OFFLINE_ACCESSCONTROL"); 096 elements.add("CMS_OFFLINE_CONTENTS"); 097 elements.add("CMS_OFFLINE_PROPERTIES"); 098 elements.add("CMS_OFFLINE_PROPERTYDEF"); 099 elements.add("CMS_OFFLINE_RESOURCES"); 100 elements.add("CMS_OFFLINE_STRUCTURE"); 101 elements.add("CMS_ONLINE_ACCESSCONTROL"); 102 elements.add("CMS_ONLINE_PROPERTIES"); 103 elements.add("CMS_ONLINE_PROPERTYDEF"); 104 elements.add("CMS_ONLINE_RESOURCES"); 105 elements.add("CMS_ONLINE_STRUCTURE"); 106 elements.add("CMS_PROJECTRESOURCES"); 107 elements.add("CMS_PROJECTS"); 108 elements.add("CMS_PUBLISH_HISTORY"); 109 elements.add("CMS_STATICEXPORT_LINKS"); 110 elements.add("CMS_USERS"); 111 112 // iterate the queries 113 for (Iterator<String> it = elements.iterator(); it.hasNext();) { 114 String tablename = it.next(); 115 116 // Check if the table exists 117 if (dbCon.hasTableOrColumn(tablename, null)) { 118 119 // Drop the constraints 120 try { 121 List<String> constraints = getConstraintsTopDrop(dbCon, tablename); 122 Iterator<String> iter = constraints.iterator(); 123 while (iter.hasNext()) { 124 String constraint = iter.next(); 125 126 String dropConstraint = readQuery(QUERY_DROP_CONSTRAINT); 127 Map<String, String> replacer = new HashMap<String, String>(); 128 replacer.put(REPLACEMENT_TABLENAME, tablename); 129 replacer.put(REPLACEMENT_INDEXNAME, constraint); 130 dbCon.updateSqlStatement(dropConstraint, replacer, null); 131 } 132 } catch (SQLException e) { 133 e.printStackTrace(); 134 } 135 136 // Drop the indexes 137 try { 138 List<String> indexes = getIndexesToDrop(dbCon, tablename); 139 Iterator<String> iter = indexes.iterator(); 140 while (iter.hasNext()) { 141 String index = iter.next(); 142 143 // Drop the index 144 String dropIndex = readQuery(QUERY_DROP_INDEX); 145 Map<String, String> replacerIndex = new HashMap<String, String>(); 146 replacerIndex.put(REPLACEMENT_INDEXNAME, index); 147 dbCon.updateSqlStatement(dropIndex, replacerIndex, null); 148 } 149 } catch (SQLException e) { 150 e.printStackTrace(); 151 } 152 153 } else { 154 System.out.println("Table " + tablename + "does not exist."); 155 } 156 } 157 // Create the new indexes for the table 158 createNewIndexes(dbCon); 159 } 160 161 /** 162 * Creates the new indexes for the given table.<p> 163 * 164 * @param dbCon the connection to the database 165 */ 166 private void createNewIndexes(CmsSetupDb dbCon) { 167 168 List<String> indexElements = new ArrayList<String>(); 169 indexElements.add("CMS_CONTENTS_PRIMARY_KEY"); 170 indexElements.add("CMS_CONTENTS_01_IDX_INDEX"); 171 indexElements.add("CMS_CONTENTS_02_IDX_INDEX"); 172 indexElements.add("CMS_CONTENTS_03_IDX_INDEX"); 173 indexElements.add("CMS_CONTENTS_04_IDX_INDEX"); 174 indexElements.add("CMS_CONTENTS_05_IDX_INDEX"); 175 indexElements.add("CMS_GROUPS_PRIMARY_KEY"); 176 indexElements.add("CMS_GROUPS_UNIQUE_KEY_GROUPS"); 177 indexElements.add("CMS_GROUPS_01_IDX_INDEX"); 178 indexElements.add("CMS_GROUPS_02_IDX_INDEX"); 179 indexElements.add("CMS_GROUPS_03_IDX_INDEX"); 180 indexElements.add("CMS_GROUPUSERS_PRIMARY_KEY"); 181 indexElements.add("CMS_GROUPUSERS_01_IDX_INDEX"); 182 indexElements.add("CMS_GROUPUSERS_02_IDX_INDEX"); 183 indexElements.add("CMS_OFFLINE_ACCESSCONTROL_PRIMARY_KEY"); 184 indexElements.add("CMS_OFFLINE_ACCESSCONTROL_01_IDX_INDEX"); 185 indexElements.add("CMS_OFFLINE_ACCESSCONTROL_02_IDX_INDEX"); 186 indexElements.add("CMS_OFFLINE_CONTENTS_PRIMARY_KEY"); 187 indexElements.add("CMS_OFFLINE_PROPERTIES_PRIMARY_KEY"); 188 indexElements.add("CMS_OFFLINE_PROPERTIES_UNIQUE_KEY_PROPERTIES"); 189 indexElements.add("CMS_OFFLINE_PROPERTIES_01_IDX_INDEX"); 190 indexElements.add("CMS_OFFLINE_PROPERTIES_02_IDX_INDEX"); 191 indexElements.add("CMS_OFFLINE_PROPERTYDEF_PRIMARY_KEY"); 192 indexElements.add("CMS_OFFLINE_PROPERTYDEF_UNIQUE_KEY_PROPERTYDEF"); 193 indexElements.add("CMS_OFFLINE_RESOURCES_PRIMARY_KEY"); 194 indexElements.add("CMS_OFFLINE_RESOURCES_01_IDX_INDEX"); 195 indexElements.add("CMS_OFFLINE_RESOURCES_02_IDX_INDEX"); 196 indexElements.add("CMS_OFFLINE_RESOURCES_03_IDX_INDEX"); 197 indexElements.add("CMS_OFFLINE_RESOURCES_04_IDX_INDEX"); 198 indexElements.add("CMS_OFFLINE_RESOURCES_05_IDX_INDEX"); 199 indexElements.add("CMS_OFFLINE_STRUCTURE_PRIMARY_KEY"); 200 indexElements.add("CMS_OFFLINE_STRUCTURE_01_IDX_INDEX"); 201 indexElements.add("CMS_OFFLINE_STRUCTURE_02_IDX_INDEX"); 202 indexElements.add("CMS_OFFLINE_STRUCTURE_03_IDX_INDEX"); 203 indexElements.add("CMS_OFFLINE_STRUCTURE_04_IDX_INDEX"); 204 indexElements.add("CMS_OFFLINE_STRUCTURE_05_IDX_INDEX"); 205 indexElements.add("CMS_OFFLINE_STRUCTURE_06_IDX_INDEX"); 206 indexElements.add("CMS_OFFLINE_STRUCTURE_07_IDX_INDEX"); 207 indexElements.add("CMS_ONLINE_ACCESSCONTROL_PRIMARY_KEY"); 208 indexElements.add("CMS_ONLINE_ACCESSCONTROL_01_IDX_INDEX"); 209 indexElements.add("CMS_ONLINE_ACCESSCONTROL_02_IDX_INDEX"); 210 indexElements.add("CMS_ONLINE_PROPERTIES_PRIMARY_KEY"); 211 indexElements.add("CMS_ONLINE_PROPERTIES_UNIQUE_KEY_PROPERTIES"); 212 indexElements.add("CMS_ONLINE_PROPERTIES_01_IDX_INDEX"); 213 indexElements.add("CMS_ONLINE_PROPERTIES_02_IDX_INDEX"); 214 indexElements.add("CMS_ONLINE_PROPERTYDEF_PRIMARY_KEY"); 215 indexElements.add("CMS_ONLINE_PROPERTYDEF_UNIQUE_KEY_PROPERTYDEF"); 216 indexElements.add("CMS_ONLINE_RESOURCES_PRIMARY_KEY"); 217 indexElements.add("CMS_ONLINE_RESOURCES_01_IDX_INDEX"); 218 indexElements.add("CMS_ONLINE_RESOURCES_02_IDX_INDEX"); 219 indexElements.add("CMS_ONLINE_RESOURCES_03_IDX_INDEX"); 220 indexElements.add("CMS_ONLINE_RESOURCES_04_IDX_INDEX"); 221 indexElements.add("CMS_ONLINE_RESOURCES_05_IDX_INDEX"); 222 indexElements.add("CMS_ONLINE_STRUCTURE_PRIMARY_KEY"); 223 indexElements.add("CMS_ONLINE_STRUCTURE_01_IDX_INDEX"); 224 indexElements.add("CMS_ONLINE_STRUCTURE_02_IDX_INDEX"); 225 indexElements.add("CMS_ONLINE_STRUCTURE_03_IDX_INDEX"); 226 indexElements.add("CMS_ONLINE_STRUCTURE_04_IDX_INDEX"); 227 indexElements.add("CMS_ONLINE_STRUCTURE_05_IDX_INDEX"); 228 indexElements.add("CMS_ONLINE_STRUCTURE_06_IDX_INDEX"); 229 indexElements.add("CMS_ONLINE_STRUCTURE_07_IDX_INDEX"); 230 indexElements.add("CMS_PROJECTRESOURCES_PRIMARY_KEY"); 231 indexElements.add("CMS_PROJECTRESOURCES_01_IDX_INDEX"); 232 indexElements.add("CMS_PROJECTS_PRIMARY_KEY"); 233 indexElements.add("CMS_PROJECTS_UNIQUE_KEY_PROJECTS"); 234 indexElements.add("CMS_PROJECTS_01_IDX_INDEX"); 235 indexElements.add("CMS_PROJECTS_02_IDX_INDEX"); 236 indexElements.add("CMS_PROJECTS_03_IDX_INDEX"); 237 indexElements.add("CMS_PROJECTS_04_IDX_INDEX"); 238 indexElements.add("CMS_PROJECTS_05_IDX_INDEX"); 239 indexElements.add("CMS_PROJECTS_06_IDX_INDEX"); 240 indexElements.add("CMS_PROJECTS_07_IDX_INDEX"); 241 indexElements.add("CMS_PUBLISH_HISTORY_PRIMARY_KEY"); 242 indexElements.add("CMS_PUBLISH_HISTORY_01_IDX_INDEX"); 243 indexElements.add("CMS_STATICEXPORT_LINKS_PRIMARY_KEY"); 244 indexElements.add("CMS_STATICEXPORT_LINKS_01_IDX_INDEX"); 245 indexElements.add("CMS_USERS_PRIMARY_KEY"); 246 indexElements.add("CMS_USERS_UNIQUE_KEY_USERS"); 247 indexElements.add("CMS_USERS_01_IDX_INDEX"); 248 indexElements.add("CMS_USERS_02_IDX_INDEX"); 249 250 String indexTablespace = m_poolData.get("indexTablespace"); 251 252 // Create the indexes 253 for (Iterator<String> createIndexes = indexElements.iterator(); createIndexes.hasNext();) { 254 String queryToRead = createIndexes.next(); 255 String query = readQuery(queryToRead); 256 try { 257 Map<String, String> replacer = new HashMap<String, String>(); 258 replacer.put(REPLACEMENT_TABLEINDEX_SPACE, indexTablespace); 259 // Create the index 260 dbCon.updateSqlStatement(query, replacer, null); 261 } catch (SQLException e) { 262 e.printStackTrace(); 263 } 264 265 } 266 267 } 268 269 /** 270 * Gets the constraints for a table.<p> 271 * 272 * @param dbCon the db connection interface 273 * @param tablename the table to get the indexes from 274 * 275 * @return a list of constraints 276 * 277 * @throws SQLException if something goes wrong 278 */ 279 private List<String> getConstraintsTopDrop(CmsSetupDb dbCon, String tablename) throws SQLException { 280 281 List<String> constraints = new ArrayList<String>(); 282 String tableConstraints = readQuery(QUERY_SHOW_CONSTRAINTS); 283 Map<String, String> replacer = new HashMap<String, String>(); 284 replacer.put(REPLACEMENT_TABLENAME, tablename); 285 CmsSetupDBWrapper db = null; 286 try { 287 db = dbCon.executeSqlStatement(tableConstraints, replacer); 288 while (db.getResultSet().next()) { 289 String constraint = db.getResultSet().getString(FIELD_CONSTRAINT); 290 if (!constraints.contains(constraint)) { 291 constraints.add(constraint); 292 } 293 } 294 } finally { 295 if (db != null) { 296 db.close(); 297 } 298 } 299 return constraints; 300 } 301 302 /** 303 * Returns the list of the indexes that shall be dropped before adding the final new indexes.<p> 304 * 305 * @param dbCon the connection to the database 306 * @param tablename the table to drop the indexes from 307 * 308 * @return the list of indexes to drop 309 */ 310 private List<String> getIndexesToDrop(CmsSetupDb dbCon, String tablename) { 311 312 List<String> indexes = new ArrayList<String>(); 313 String tableIndex = readQuery(QUERY_SHOW_INDEX); 314 Map<String, String> replacer = new HashMap<String, String>(); 315 replacer.put(REPLACEMENT_TABLENAME, tablename); 316 CmsSetupDBWrapper db = null; 317 try { 318 db = dbCon.executeSqlStatement(tableIndex, replacer); 319 while (db.getResultSet().next()) { 320 String index = db.getResultSet().getString(FIELD_INDEX); 321 if (!indexes.contains(index)) { 322 indexes.add(index); 323 } 324 } 325 } catch (SQLException e) { 326 e.printStackTrace(); 327 } finally { 328 if (db != null) { 329 db.close(); 330 } 331 } 332 333 return indexes; 334 } 335 336}