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