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