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; 029 030import org.opencms.configuration.CmsParameterConfiguration; 031import org.opencms.setup.CmsSetupDBWrapper; 032import org.opencms.setup.CmsSetupDb; 033import org.opencms.setup.CmsUpdateBean; 034import org.opencms.util.CmsStringUtil; 035import org.opencms.util.CmsUUID; 036 037import java.sql.SQLException; 038import java.util.ArrayList; 039import java.util.HashMap; 040import java.util.Iterator; 041import java.util.List; 042import java.util.Map; 043 044/** 045 * This manager controls the update of the database from OpenCms 6 to OpenCms 7.<p> 046 * 047 * @since 7.0.0 048 */ 049public class CmsUpdateDBManager { 050 051 /** The database name. */ 052 private String m_dbName; 053 054 /** The pools connection data. */ 055 private Map<String, Map<String, String>> m_dbPools = new HashMap<String, Map<String, String>>(); 056 057 /** The detected mayor version, based on DB structure. */ 058 private double m_detectedVersion; 059 060 /** List of xml update plugins. */ 061 private List<I_CmsUpdateDBPart> m_plugins; 062 063 /** 064 * Default constructor.<p> 065 */ 066 public CmsUpdateDBManager() { 067 068 // no-op 069 } 070 071 /** 072 * Returns the configured jdbc driver for the given pool.<p> 073 * 074 * @param pool the db pool to get the driver for 075 * 076 * @return the driver class name 077 */ 078 public String getDbDriver(String pool) { 079 080 return m_dbPools.get(pool).get("driver"); 081 } 082 083 /** 084 * Returns the database name.<p> 085 * 086 * @return the database name 087 */ 088 public String getDbName() { 089 090 return m_dbName; 091 } 092 093 /** 094 * Returns the configured jdbc url parameters for the given pool.<p> 095 * 096 * @param pool the db pool to get the params for 097 * 098 * @return the jdbc url parameters 099 */ 100 public String getDbParams(String pool) { 101 102 return m_dbPools.get(pool).get("params"); 103 } 104 105 /** 106 * Returns the configured jdbc connection url for the given pool.<p> 107 * 108 * @param pool the db pool to get the url for 109 * 110 * @return the jdbc connection url 111 */ 112 public String getDbUrl(String pool) { 113 114 return m_dbPools.get(pool).get("url"); 115 } 116 117 /** 118 * Returns the configured database user for the given pool.<p> 119 * 120 * @param pool the db pool to get the user for 121 * 122 * @return the database user 123 */ 124 public String getDbUser(String pool) { 125 126 return m_dbPools.get(pool).get("user"); 127 } 128 129 /** 130 * Returns the detected mayor version, based on DB structure.<p> 131 * 132 * @return the detected mayor version 133 */ 134 public double getDetectedVersion() { 135 136 if (m_detectedVersion == 0) { 137 needUpdate(); 138 } 139 return m_detectedVersion; 140 } 141 142 /** 143 * Returns all configured database pools.<p> 144 * 145 * @return a list of {@link String} objects 146 */ 147 public List<String> getPools() { 148 149 return new ArrayList<String>(m_dbPools.keySet()); 150 } 151 152 /** 153 * Generates html code for the given db pool.<p> 154 * 155 * @param pool the db pool to generate html for 156 * 157 * @return html code 158 * 159 * @throws Exception if something goes wrong 160 */ 161 public String htmlPool(String pool) throws Exception { 162 163 StringBuffer html = new StringBuffer(256); 164 165 html.append("<a href=\"javascript:switchview('").append(pool).append("');\">"); 166 html.append(pool).append("</a><br>\n"); 167 html.append("\t<div id='").append(pool).append("' style='display: none;'>\n"); 168 html.append("\t\t<table border='0'>\n"); 169 html.append("\t\t\t<tr><td>JDBC Driver:</td><td>" + getDbDriver(pool) + "</td></tr>\n"); 170 html.append("\t\t\t<tr><td>JDBC Connection Url:</td><td>" + getDbUrl(pool) + "</td></tr>\n"); 171 html.append("\t\t\t<tr><td>JDBC Connection Url Params:</td><td>" + getDbParams(pool) + "</td></tr>\n"); 172 html.append("\t\t\t<tr><td>Database User:</td><td>" + getDbUser(pool) + "</td></tr>\n"); 173 html.append("\t\t</table>\n"); 174 html.append("\t</div>\n"); 175 176 return html.toString(); 177 } 178 179 public String htmlPool(String pool, boolean hiddenInfo) { 180 181 if (hiddenInfo) { 182 try { 183 return htmlPool(pool); 184 } catch (Exception e) { 185 // 186 } 187 } 188 StringBuffer html = new StringBuffer(256); 189 190 html.append("<p>"); 191 html.append(pool).append("</p><br>\n"); 192 html.append("\t<div id='").append(pool); 193 html.append("\t\t<table border='0'>\n"); 194 html.append("\t\t\t<tr><td>JDBC Driver:</td><td>" + getDbDriver(pool) + "</td></tr>\n"); 195 html.append("\t\t\t<tr><td>JDBC Connection Url:</td><td>" + getDbUrl(pool) + "</td></tr>\n"); 196 html.append("\t\t\t<tr><td>JDBC Connection Url Params:</td><td>" + getDbParams(pool) + "</td></tr>\n"); 197 html.append("\t\t\t<tr><td>Database User:</td><td>" + getDbUser(pool) + "</td></tr>\n"); 198 html.append("\t\t</table>\n"); 199 html.append("\t</div>\n"); 200 201 return html.toString(); 202 } 203 204 /** 205 * Initializes the Update Manager object with the updateBean to get the database connection.<p> 206 * 207 * @param updateBean the update bean with the database connection 208 * 209 * @throws Exception if the setup bean is not initialized 210 */ 211 public void initialize(CmsUpdateBean updateBean) throws Exception { 212 213 if (updateBean.isInitialized()) { 214 CmsParameterConfiguration props = updateBean.getProperties(); 215 216 // Initialize the CmsUUID generator. 217 CmsUUID.init(props.get("server.ethernet.address")); 218 219 m_dbName = props.get("db.name"); 220 221 List<String> pools = CmsStringUtil.splitAsList(props.get("db.pools"), ','); 222 for (String pool : pools) { 223 Map<String, String> data = new HashMap<String, String>(); 224 data.put("driver", props.get("db.pool." + pool + ".jdbcDriver")); 225 data.put("url", props.get("db.pool." + pool + ".jdbcUrl")); 226 data.put("params", props.get("db.pool." + pool + ".jdbcUrl.params")); 227 data.put("user", props.get("db.pool." + pool + ".user")); 228 data.put("pwd", props.get("db.pool." + pool + ".password")); 229 data.put("keepHistory", String.valueOf(updateBean.isKeepHistory())); 230 m_dbPools.put(pool, data); 231 } 232 } else { 233 throw new Exception("setup bean not initialized"); 234 } 235 } 236 237 /** 238 * Checks if an update is needed.<p> 239 * 240 * @return if an update is needed 241 */ 242 public boolean needUpdate() { 243 244 String pool = "default"; 245 246 double currentVersion = 8.5; 247 m_detectedVersion = 8.5; 248 249 CmsSetupDb setupDb = new CmsSetupDb(null); 250 251 try { 252 setupDb.setConnection( 253 getDbDriver(pool), 254 getDbUrl(pool), 255 getDbParams(pool), 256 getDbUser(pool), 257 m_dbPools.get(pool).get("pwd")); 258 259 if (!setupDb.hasTableOrColumn("CMS_USERS", "USER_OU")) { 260 m_detectedVersion = 6; 261 } else if (!setupDb.hasTableOrColumn("CMS_ONLINE_URLNAME_MAPPINGS", null)) { 262 m_detectedVersion = 7; 263 } else if (!setupDb.hasTableOrColumn("CMS_USER_PUBLISH_LIST", null)) { 264 m_detectedVersion = 8; 265 } 266 } finally { 267 setupDb.closeConnection(); 268 } 269 270 return currentVersion != m_detectedVersion; 271 } 272 273 /** 274 * Updates all database pools.<p> 275 */ 276 public void run() { 277 278 try { 279 // add a list of plugins to execute 280 // be sure to use the right order 281 m_plugins = new ArrayList<I_CmsUpdateDBPart>(); 282 283 if (getDetectedVersion() < 7) { 284 m_plugins.add(new org.opencms.setup.db.update6to7.CmsUpdateDBDropOldIndexes()); 285 m_plugins.add(new org.opencms.setup.db.update6to7.CmsUpdateDBUpdateOU()); 286 m_plugins.add(new org.opencms.setup.db.update6to7.CmsUpdateDBCmsUsers()); 287 m_plugins.add(new org.opencms.setup.db.update6to7.CmsUpdateDBProjectId()); 288 m_plugins.add(new org.opencms.setup.db.update6to7.CmsUpdateDBNewTables()); 289 m_plugins.add(new org.opencms.setup.db.update6to7.CmsUpdateDBHistoryTables()); 290 m_plugins.add(new org.opencms.setup.db.update6to7.CmsUpdateDBHistoryPrincipals()); 291 m_plugins.add(new org.opencms.setup.db.update6to7.CmsUpdateDBDropUnusedTables()); 292 m_plugins.add(new org.opencms.setup.db.update6to7.CmsUpdateDBContentTables()); 293 m_plugins.add(new org.opencms.setup.db.update6to7.CmsUpdateDBAlterTables()); 294 m_plugins.add(new org.opencms.setup.db.update6to7.CmsUpdateDBDropBackupTables()); 295 m_plugins.add(new org.opencms.setup.db.update6to7.CmsUpdateDBCreateIndexes7()); 296 } else { 297 m_plugins.add(new org.opencms.setup.db.update7to8.CmsUpdateDBNewTables()); 298 m_plugins.add(new org.opencms.setup.db.update7to8.CmsUpdatePasswordColumn()); 299 } 300 } catch (Throwable t) { 301 t.printStackTrace(); 302 throw new RuntimeException(t); 303 } 304 305 Iterator<String> it = getPools().iterator(); 306 while (it.hasNext()) { 307 String dbPool = it.next(); 308 System.out.println("Starting DB Update for pool " + dbPool + "... "); 309 310 try { 311 updateDatabase(dbPool); 312 } catch (Throwable t) { 313 t.printStackTrace(); 314 } 315 316 System.out.println("... DB Update finished for " + dbPool + "."); 317 } 318 } 319 320 /** 321 * Updates the database.<p> 322 * 323 * @param pool the database pool to update 324 */ 325 public void updateDatabase(String pool) { 326 327 Map<String, String> dbPoolData = new HashMap<String, String>(m_dbPools.get(pool)); 328 329 // display info 330 System.out.println("JDBC Driver: " + getDbDriver(pool)); 331 System.out.println("JDBC Connection Url: " + getDbUrl(pool)); 332 System.out.println("JDBC Connection Url Params: " + getDbParams(pool)); 333 System.out.println("Database User: " + getDbUser(pool)); 334 335 // get the db implementation name 336 String dbName = getDbName(); 337 String name = null; 338 if (dbName.indexOf("mysql") > -1) { 339 getMySqlEngine(dbPoolData); 340 name = "mysql"; 341 } else if (dbName.indexOf("oracle") > -1) { 342 getOracleTablespaces(dbPoolData); 343 name = "oracle"; 344 } else if (dbName.indexOf("postgresql") > -1) { 345 getPostgreSqlTablespaces(dbPoolData); 346 name = "postgresql"; 347 } else { 348 System.out.println("db " + dbName + " not supported"); 349 return; 350 } 351 352 // execute update 353 Iterator<I_CmsUpdateDBPart> it = m_plugins.iterator(); 354 while (it.hasNext()) { 355 I_CmsUpdateDBPart updatePart = it.next(); 356 I_CmsUpdateDBPart dbUpdater = getInstanceForDb(updatePart, name); 357 if (dbUpdater != null) { 358 dbUpdater.execute(dbPoolData); 359 } 360 } 361 } 362 363 /** 364 * Creates a new instance for the given database and setting the db pool data.<p> 365 * 366 * @param dbUpdater the generic updater part 367 * @param dbName the database to get a new instance for 368 * 369 * @return right instance instance for the given database 370 */ 371 protected I_CmsUpdateDBPart getInstanceForDb(I_CmsUpdateDBPart dbUpdater, String dbName) { 372 373 String clazz = dbUpdater.getClass().getName(); 374 int pos = clazz.lastIndexOf('.'); 375 clazz = clazz.substring(0, pos) + "." + dbName + clazz.substring(pos); 376 try { 377 return (I_CmsUpdateDBPart)Class.forName(clazz).newInstance(); 378 } catch (Exception e) { 379 e.printStackTrace(); 380 return null; 381 } 382 } 383 384 /** 385 * Retrieves the mysql engine name.<p> 386 * 387 * @param dbPoolData the database pool data 388 */ 389 protected void getMySqlEngine(Map<String, String> dbPoolData) { 390 391 String engine = "MYISAM"; 392 CmsSetupDb setupDb = new CmsSetupDb(null); 393 CmsSetupDBWrapper db = null; 394 try { 395 setupDb.setConnection( 396 dbPoolData.get("driver"), 397 dbPoolData.get("url"), 398 dbPoolData.get("params"), 399 dbPoolData.get("user"), 400 dbPoolData.get("pwd")); 401 402 db = setupDb.executeSqlStatement("SHOW TABLE STATUS LIKE 'CMS_GROUPS';", null); 403 if (db.getResultSet().next()) { 404 engine = db.getResultSet().getString("Engine").toUpperCase(); 405 } 406 407 } catch (SQLException e) { 408 e.printStackTrace(); 409 } finally { 410 if (db != null) { 411 db.close(); 412 } 413 setupDb.closeConnection(); 414 } 415 dbPoolData.put("engine", engine); 416 System.out.println("Table engine: " + engine); 417 } 418 419 /** 420 * Retrieves the oracle tablespace names.<p> 421 * 422 * @param dbPoolData the database pool data 423 */ 424 protected void getOracleTablespaces(Map<String, String> dbPoolData) { 425 426 String dataTablespace = "users"; 427 String indexTablespace = "users"; 428 CmsSetupDb setupDb = new CmsSetupDb(null); 429 430 try { 431 setupDb.setConnection( 432 dbPoolData.get("driver"), 433 dbPoolData.get("url"), 434 dbPoolData.get("params"), 435 dbPoolData.get("user"), 436 dbPoolData.get("pwd")); 437 438 // read tablespace for data 439 CmsSetupDBWrapper db = null; 440 try { 441 db = setupDb.executeSqlStatement("SELECT DISTINCT tablespace_name FROM user_tables", null); 442 if (db.getResultSet().next()) { 443 dataTablespace = db.getResultSet().getString(1).toLowerCase(); 444 } 445 } finally { 446 if (db != null) { 447 db.close(); 448 } 449 } 450 // read tablespace for indexes 451 try { 452 db = setupDb.executeSqlStatement("SELECT DISTINCT tablespace_name FROM user_indexes", null); 453 if (db.getResultSet().next()) { 454 indexTablespace = db.getResultSet().getString(1).toLowerCase(); 455 } 456 } finally { 457 if (db != null) { 458 db.close(); 459 } 460 } 461 } catch (SQLException e) { 462 e.printStackTrace(); 463 } finally { 464 setupDb.closeConnection(); 465 } 466 467 dbPoolData.put("indexTablespace", indexTablespace); 468 System.out.println("Index Tablespace: " + indexTablespace); 469 470 dbPoolData.put("dataTablespace", dataTablespace); 471 System.out.println("Data Tablespace: " + dataTablespace); 472 } 473 474 /** 475 * Retrieves the postgresql tablespace names.<p> 476 * 477 * @param dbPoolData the database pool data 478 */ 479 protected void getPostgreSqlTablespaces(Map<String, String> dbPoolData) { 480 481 String dataTablespace = "pg_default"; 482 String indexTablespace = "pg_default"; 483 CmsSetupDb setupDb = new CmsSetupDb(null); 484 485 try { 486 setupDb.setConnection( 487 dbPoolData.get("driver"), 488 dbPoolData.get("url"), 489 dbPoolData.get("params"), 490 dbPoolData.get("user"), 491 dbPoolData.get("pwd")); 492 493 // read tablespace for data 494 CmsSetupDBWrapper db = null; 495 try { 496 db = setupDb.executeSqlStatement( 497 "SELECT DISTINCT pg_tablespace.spcname FROM pg_class, pg_tablespace WHERE pg_class.relname='cms_user' AND pg_class.reltablespace = pg_tablespace.oid", 498 null); 499 if (db.getResultSet().next()) { 500 dataTablespace = db.getResultSet().getString(1).toLowerCase(); 501 } 502 } finally { 503 if (db != null) { 504 db.close(); 505 } 506 } 507 // read tablespace for indexes 508 try { 509 db = setupDb.executeSqlStatement( 510 "SELECT DISTINCT pg_tablespace.spcname FROM pg_class, pg_tablespace WHERE pg_class.relname='cms_users_pkey' AND pg_class.reltablespace = pg_tablespace.oid", 511 null); 512 if (db.getResultSet().next()) { 513 indexTablespace = db.getResultSet().getString(1).toLowerCase(); 514 } 515 } finally { 516 if (db != null) { 517 db.close(); 518 } 519 } 520 } catch (SQLException e) { 521 e.printStackTrace(); 522 } finally { 523 setupDb.closeConnection(); 524 } 525 526 dbPoolData.put("indexTablespace", indexTablespace); 527 System.out.println("Index Tablespace: " + indexTablespace); 528 529 dbPoolData.put("dataTablespace", dataTablespace); 530 System.out.println("Data Tablespace: " + dataTablespace); 531 } 532}