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; 029 030import org.opencms.main.CmsException; 031import org.opencms.main.CmsLog; 032import org.opencms.util.CmsDataTypeUtil; 033import org.opencms.util.CmsStringUtil; 034 035import java.io.File; 036import java.io.FileNotFoundException; 037import java.io.FileReader; 038import java.io.IOException; 039import java.io.LineNumberReader; 040import java.io.Reader; 041import java.io.StringReader; 042import java.sql.Connection; 043import java.sql.DriverManager; 044import java.sql.PreparedStatement; 045import java.sql.ResultSet; 046import java.sql.SQLException; 047import java.sql.Statement; 048import java.util.ArrayList; 049import java.util.Iterator; 050import java.util.List; 051import java.util.Map; 052import java.util.StringTokenizer; 053 054import org.apache.commons.logging.Log; 055 056/** 057 * Helper class to call database setup scripts.<p> 058 * 059 * @since 6.0.0 060 */ 061public class CmsSetupDb extends Object { 062 063 /** The folder where to read the setup data from. */ 064 public static final String SETUP_DATA_FOLDER = "WEB-INF/setupdata/"; 065 066 /** The folder where the setup wizard is located. */ 067 public static final String SETUP_FOLDER = CmsSetupBean.FOLDER_SETUP; 068 069 /** The log object for this class. */ 070 private static final Log LOG = CmsLog.getLog(CmsSetupDb.class); 071 072 /** The setup base path. */ 073 private String m_basePath; 074 075 /** A SQL connection. */ 076 private Connection m_con; 077 078 /** A flag signaling if error logging is enabled. */ 079 private boolean m_errorLogging; 080 081 /** A list to store error messages. */ 082 private List<String> m_errors; 083 084 /** 085 * Creates a new CmsSetupDb object.<p> 086 * 087 * @param basePath the location of the setup scripts 088 */ 089 public CmsSetupDb(String basePath) { 090 091 m_errors = new ArrayList<String>(); 092 m_basePath = basePath; 093 m_errorLogging = true; 094 } 095 096 /** 097 * Returns an optional warning message if needed, <code>null</code> if not.<p> 098 * 099 * @param db the selected database key 100 * 101 * @return html warning, or <code>null</code> if no warning 102 */ 103 public String checkVariables(String db) { 104 105 StringBuffer html = new StringBuffer(512); 106 if (m_con == null) { 107 return null; // prior error, trying to get a connection 108 } 109 Exception exception = null; 110 if (db.equals("mysql")) { 111 String statement = "SELECT @@max_allowed_packet;"; 112 Statement stmt = null; 113 ResultSet rs = null; 114 long maxAllowedPacket = 0; 115 try { 116 stmt = m_con.createStatement(); 117 rs = stmt.executeQuery(statement); 118 if (rs.next()) { 119 maxAllowedPacket = rs.getLong(1); 120 } 121 } catch (Exception e) { 122 exception = e; 123 } finally { 124 if (stmt != null) { 125 try { 126 stmt.close(); 127 } catch (SQLException e) { 128 // ignore 129 } 130 } 131 } 132 if (exception == null) { 133 int megabyte = 1024 * 1024; 134 if (maxAllowedPacket > 0) { 135 html.append("<p>MySQL system variable <code>'max_allowed_packet'</code> is set to "); 136 html.append(maxAllowedPacket); 137 html.append(" Byte ("); 138 html.append((maxAllowedPacket / megabyte) + "MB).</p>\n"); 139 } 140 html.append( 141 "<p>Please note that it will not be possible for OpenCms to handle files bigger than this value in the VFS.</p>\n"); 142 int requiredMaxAllowdPacket = 16; 143 if (maxAllowedPacket < (requiredMaxAllowdPacket * megabyte)) { 144 m_errors.add( 145 "<p><b>Your <code>'max_allowed_packet'</code> variable is set to less than " 146 + (requiredMaxAllowdPacket * megabyte) 147 + " Byte (" 148 + requiredMaxAllowdPacket 149 + "MB).</b></p>\n" 150 + "<p>The required value for running OpenCms is at least " 151 + requiredMaxAllowdPacket 152 + "MB." 153 + "Please change your MySQL configuration (in the <code>my.ini</code> or <code>my.cnf</code> file).</p>\n"); 154 } 155 } else { 156 html.append( 157 "<p><i>OpenCms was not able to detect the value of your <code>'max_allowed_packet'</code> variable.</i></p>\n"); 158 html.append( 159 "<p>Please note that it will not be possible for OpenCms to handle files bigger than this value.</p>\n"); 160 html.append( 161 "<p><b>The recommended value for running OpenCms is 16MB, please set it in your MySQL configuration (in your <code>my.ini</code> or <code>my.cnf</code> file).</b></p>\n"); 162 html.append(CmsException.getStackTraceAsString(exception)); 163 } 164 } 165 if (html.length() == 0) { 166 return null; 167 } 168 return html.toString(); 169 } 170 171 /** 172 * Clears the error messages stored internally.<p> 173 */ 174 public void clearErrors() { 175 176 m_errors.clear(); 177 } 178 179 /** 180 * Closes the internal connection to the database.<p> 181 */ 182 public void closeConnection() { 183 184 try { 185 if (m_con != null) { 186 m_con.close(); 187 } 188 } catch (Exception e) { 189 // ignore 190 } 191 m_con = null; 192 } 193 194 /** 195 * Calls the create database script for the given database.<p> 196 * 197 * @param database the name of the database 198 * @param replacer the replacements to perform in the drop script 199 */ 200 public void createDatabase(String database, Map<String, String> replacer) { 201 202 m_errorLogging = true; 203 executeSql(database, "create_db.sql", replacer, true); 204 } 205 206 /** 207 * Calls the create database script for the given database.<p> 208 * 209 * @param database the name of the database 210 * @param replacer the replacements to perform in the drop script 211 * @param abortOnError indicates if the script is aborted if an error occurs 212 */ 213 public void createDatabase(String database, Map<String, String> replacer, boolean abortOnError) { 214 215 m_errorLogging = true; 216 executeSql(database, "create_db.sql", replacer, abortOnError); 217 } 218 219 /** 220 * Calls the create tables script for the given database.<p> 221 * 222 * @param database the name of the database 223 * @param replacer the replacements to perform in the drop script 224 */ 225 public void createTables(String database, Map<String, String> replacer) { 226 227 m_errorLogging = true; 228 executeSql(database, "create_tables.sql", replacer, true); 229 } 230 231 /** 232 * Calls the create tables script for the given database.<p> 233 * 234 * @param database the name of the database 235 * @param replacer the replacements to perform in the drop script 236 * @param abortOnError indicates if the script is aborted if an error occurs 237 */ 238 public void createTables(String database, Map<String, String> replacer, boolean abortOnError) { 239 240 m_errorLogging = true; 241 executeSql(database, "create_tables.sql", replacer, abortOnError); 242 } 243 244 /** 245 * Calls the drop script for the given database. 246 * 247 * @param database the name of the database 248 * @param replacer the replacements to perform in the drop script 249 */ 250 public void dropDatabase(String database, Map<String, String> replacer) { 251 252 m_errorLogging = true; 253 executeSql(database, "drop_db.sql", replacer, false); 254 } 255 256 /** 257 * Calls the drop script for the given database. 258 * 259 * @param database the name of the database 260 * @param replacer the replacements to perform in the drop script 261 * @param abortOnError indicates if the script is aborted if an error occurs 262 */ 263 public void dropDatabase(String database, Map<String, String> replacer, boolean abortOnError) { 264 265 m_errorLogging = true; 266 executeSql(database, "drop_db.sql", replacer, abortOnError); 267 } 268 269 /** 270 * Calls the drop tables script for the given database.<p> 271 * 272 * @param database the name of the database 273 */ 274 public void dropTables(String database) { 275 276 m_errorLogging = true; 277 executeSql(database, "drop_tables.sql", null, false); 278 } 279 280 /** 281 * Calls the drop tables script for the given database.<p> 282 * 283 * @param database the name of the database 284 * @param replacer the replacements to perform in the drop script 285 */ 286 public void dropTables(String database, Map<String, String> replacer) { 287 288 m_errorLogging = true; 289 executeSql(database, "drop_tables.sql", replacer, false); 290 } 291 292 /** 293 * Calls the drop tables script for the given database.<p> 294 * 295 * @param database the name of the database 296 * @param replacer the replacements to perform in the drop script 297 * @param abortOnError indicates if the script is aborted if an error occurs 298 */ 299 public void dropTables(String database, Map<String, String> replacer, boolean abortOnError) { 300 301 m_errorLogging = true; 302 executeSql(database, "drop_tables.sql", replacer, abortOnError); 303 } 304 305 /** 306 * Creates and executes a database statement from a String returning the result set.<p> 307 * 308 * @param query the query to execute 309 * @param replacer the replacements to perform in the script 310 * 311 * @return the result set of the query 312 * 313 * @throws SQLException if something goes wrong 314 */ 315 public CmsSetupDBWrapper executeSqlStatement(String query, Map<String, String> replacer) throws SQLException { 316 317 CmsSetupDBWrapper dbwrapper = new CmsSetupDBWrapper(m_con); 318 dbwrapper.createStatement(); 319 320 String queryToExecute = query; 321 322 // Check if a map of replacements is given 323 if (replacer != null) { 324 queryToExecute = replaceTokens(query, replacer); 325 } 326 // do the query 327 dbwrapper.excecuteQuery(queryToExecute); 328 329 // return the result 330 return dbwrapper; 331 332 } 333 334 /** Creates and executes a database statement from a String returning the result set.<p> 335 * 336 * @param query the query to execute 337 * @param replacer the replacements to perform in the script 338 * @param params the list of parameters for the statement 339 * 340 * @return the result set of the query 341 * 342 * @throws SQLException if something goes wrong 343 */ 344 public CmsSetupDBWrapper executeSqlStatement(String query, Map<String, String> replacer, List<Object> params) 345 throws SQLException { 346 347 CmsSetupDBWrapper dbwrapper = new CmsSetupDBWrapper(m_con); 348 349 String queryToExecute = query; 350 351 // Check if a map of replacements is given 352 if (replacer != null) { 353 queryToExecute = replaceTokens(query, replacer); 354 } 355 356 dbwrapper.createPreparedStatement(queryToExecute, params); 357 358 dbwrapper.excecutePreparedQuery(); 359 360 return dbwrapper; 361 } 362 363 /** 364 * Returns the connection.<p> 365 * 366 * @return the connection 367 */ 368 public Connection getConnection() { 369 370 return m_con; 371 } 372 373 /** 374 * Returns a Vector of Error messages.<p> 375 * 376 * @return all error messages collected internally 377 */ 378 public List<String> getErrors() { 379 380 return m_errors; 381 } 382 383 /** 384 * Checks if the given table, column or combination of both is available in the database in case insensitive way.<P> 385 * 386 * @param table the sought table 387 * @param column the sought column 388 * 389 * @return true if the requested table/column is available, false if not 390 */ 391 public boolean hasTableOrColumn(String table, String column) { 392 393 String tableName, columnName; 394 boolean result; 395 396 tableName = table == null ? null : table.toUpperCase(); 397 columnName = column == null ? null : column.toUpperCase(); 398 result = hasTableOrColumnCaseSensitive(tableName, columnName); 399 400 if (!result) { 401 tableName = table == null ? null : table.toLowerCase(); 402 columnName = column == null ? null : column.toLowerCase(); 403 result = result || hasTableOrColumnCaseSensitive(tableName, columnName); 404 } 405 406 return result; 407 } 408 409 /** 410 * Checks if the given table, column or combination of both is available in the database in a case sensitive way.<P> 411 * 412 * @param table the sought table 413 * @param column the sought column 414 * 415 * @return true if the requested table/column is available, false if not 416 */ 417 public boolean hasTableOrColumnCaseSensitive(String table, String column) { 418 419 boolean result = false; 420 ResultSet set = null; 421 422 try { 423 if (CmsStringUtil.isNotEmptyOrWhitespaceOnly(column)) { 424 // Check if the column is given 425 set = m_con.getMetaData().getColumns(null, null, table, column); 426 if (set.next()) { 427 String colname = set.getString("COLUMN_NAME"); 428 if (colname.equalsIgnoreCase(column)) { 429 result = true; // The column is available 430 } 431 } 432 } else if (CmsStringUtil.isNotEmptyOrWhitespaceOnly(table)) { 433 // Check the table 434 set = m_con.getMetaData().getTables(null, null, table, null); 435 if (set.next()) { 436 String tablename = set.getString("TABLE_NAME"); 437 if (tablename.equalsIgnoreCase(table)) { 438 result = true; 439 } 440 } 441 } 442 } catch (SQLException e) { 443 e.printStackTrace(); 444 result = false; 445 } finally { 446 try { 447 if (set != null) { 448 set.close(); 449 } 450 } catch (SQLException e) { 451 e.printStackTrace(); 452 } 453 } 454 return result; 455 } 456 457 /** 458 * Checks if internal errors occurred.<p> 459 * 460 * @return true if internal errors occurred 461 */ 462 public boolean noErrors() { 463 464 return m_errors.isEmpty(); 465 } 466 467 /** 468 * Sets a new internal connection to the database.<p> 469 * 470 * @param conn the connection to use 471 */ 472 public void setConnection(Connection conn) { 473 474 m_con = conn; 475 } 476 477 /** 478 * Creates a new internal connection to the database.<p> 479 * 480 * @param DbDriver JDBC driver class name 481 * @param DbConStr JDBC connect URL 482 * @param DbConStrParams JDBC connect URL params, or null 483 * @param DbUser JDBC database user 484 * @param DbPwd JDBC database password 485 */ 486 public void setConnection(String DbDriver, String DbConStr, String DbConStrParams, String DbUser, String DbPwd) { 487 488 setConnection(DbDriver, DbConStr, DbConStrParams, DbUser, DbPwd, true); 489 } 490 491 /** 492 * Creates a new internal connection to the database.<p> 493 * 494 * @param DbDriver JDBC driver class name 495 * @param DbConStr JDBC connect URL 496 * @param DbConStrParams JDBC connect URL params, or null 497 * @param DbUser JDBC database user 498 * @param DbPwd JDBC database password 499 * @param logErrors if set to 'true', errors are written to the log file 500 */ 501 public void setConnection( 502 String DbDriver, 503 String DbConStr, 504 String DbConStrParams, 505 String DbUser, 506 String DbPwd, 507 boolean logErrors) { 508 509 String jdbcUrl = DbConStr; 510 try { 511 if (DbConStrParams != null) { 512 jdbcUrl += DbConStrParams; 513 } 514 if (CmsStringUtil.isEmptyOrWhitespaceOnly(DbPwd)) { 515 DbPwd = null; 516 } 517 Class.forName(DbDriver).newInstance(); 518 m_con = DriverManager.getConnection(jdbcUrl, DbUser, DbPwd); 519 LOG.info("OpenCms setup connection established: " + m_con); 520 LOG.info(" [autocommit: " + m_con.getAutoCommit() + "]"); 521 } catch (ClassNotFoundException e) { 522 System.out.println("Class not found exception: " + e); 523 m_errors.add(Messages.get().getBundle().key(Messages.ERR_LOAD_JDBC_DRIVER_1, DbDriver)); 524 m_errors.add(CmsException.getStackTraceAsString(e)); 525 } catch (Exception e) { 526 if (logErrors) { 527 System.out.println("Exception: " + CmsException.getStackTraceAsString(e)); 528 } 529 m_errors.add(Messages.get().getBundle().key(Messages.ERR_DB_CONNECT_1, DbConStr)); 530 m_errors.add(CmsException.getStackTraceAsString(e)); 531 } 532 } 533 534 /** 535 * Calls an update script.<p> 536 * 537 * @param updateScript the update script code 538 * @param replacers the replacers to use in the script code 539 */ 540 public void updateDatabase(String updateScript, Map<String, String> replacers) { 541 542 StringReader reader = new StringReader(updateScript); 543 executeSql(reader, replacers, true); 544 } 545 546 /** 547 * Calls an update script.<p> 548 * 549 * @param updateScript the update script code 550 * @param replacers the replacers to use in the script code 551 * @param abortOnError indicates if the script is aborted if an error occurs 552 */ 553 public void updateDatabase(String updateScript, Map<String, String> replacers, boolean abortOnError) { 554 555 StringReader reader = new StringReader(updateScript); 556 executeSql(reader, replacers, abortOnError); 557 } 558 559 /** 560 * Creates and executes a database statment from a String.<p> 561 * 562 * @param query the query to execute 563 * @param replacer the replacements to perform in the script 564 * @param params the list of parameters for the statement 565 * 566 * @return the result set of the query 567 * 568 * @throws SQLException if something goes wrong 569 */ 570 public int updateSqlStatement(String query, Map<String, String> replacer, List<Object> params) throws SQLException { 571 572 String queryToExecute = query; 573 // Check if a map of replacements is given 574 if (replacer != null) { 575 queryToExecute = replaceTokens(query, replacer); 576 } 577 578 int result; 579 PreparedStatement stmt = null; 580 stmt = m_con.prepareStatement(queryToExecute); 581 try { 582 // Check the params 583 if (params != null) { 584 for (int i = 0; i < params.size(); i++) { 585 Object item = params.get(i); 586 587 // Check if the parameter is a string 588 if (item instanceof String) { 589 stmt.setString(i + 1, (String)item); 590 } 591 if (item instanceof Integer) { 592 Integer number = (Integer)item; 593 stmt.setInt(i + 1, number.intValue()); 594 } 595 if (item instanceof Long) { 596 Long longNumber = (Long)item; 597 stmt.setLong(i + 1, longNumber.longValue()); 598 } 599 600 // If item is none of types above set the statement to use the bytes 601 if (!(item instanceof Integer) && !(item instanceof String) && !(item instanceof Long)) { 602 try { 603 stmt.setBytes(i + 1, CmsDataTypeUtil.dataSerialize(item)); 604 } catch (IOException e) { 605 e.printStackTrace(); 606 } 607 } 608 } 609 } 610 611 if (!queryToExecute.startsWith("UPDATE CMS_ONLINE_STRUCTURE SET STRUCTURE_VERSION") 612 && !queryToExecute.startsWith("UPDATE CMS_OFFLINE_STRUCTURE SET STRUCTURE_VERSION")) { 613 System.out.println("executing query: " + queryToExecute); 614 if ((params != null) && !params.isEmpty()) { 615 System.out.println("params: " + params); 616 } 617 } 618 result = stmt.executeUpdate(); 619 } finally { 620 stmt.close(); 621 } 622 623 return result; 624 } 625 626 /** 627 * Internal method to parse and execute a setup script.<p> 628 * 629 * @param inputReader an input stream reader on the setup script 630 * @param replacers the replacements to perform in the script 631 * @param abortOnError if a error occurs this flag indicates if to continue or to abort 632 */ 633 private void executeSql(Reader inputReader, Map<String, String> replacers, boolean abortOnError) { 634 635 String statement = ""; 636 LineNumberReader reader = null; 637 String line = null; 638 639 // parse the setup script 640 try { 641 reader = new LineNumberReader(inputReader); 642 643 while (true) { 644 line = reader.readLine(); 645 if (line == null) { 646 break; 647 } 648 StringTokenizer st = new StringTokenizer(line); 649 650 while (st.hasMoreTokens()) { 651 String currentToken = st.nextToken(); 652 653 // comment! Skip rest of the line 654 if (currentToken.startsWith("#")) { 655 break; 656 } 657 658 // not to be executed 659 if (currentToken.startsWith("prompt")) { 660 break; 661 } 662 663 // add token to query 664 statement += " " + currentToken; 665 666 // query complete (terminated by ';') 667 if (currentToken.endsWith(";")) { 668 // cut of ';' at the end 669 statement = statement.substring(0, (statement.length() - 1)); 670 671 // normal statement, execute it 672 try { 673 if (replacers != null) { 674 statement = replaceTokens(statement, replacers); 675 executeStatement(statement); 676 } else { 677 executeStatement(statement); 678 } 679 } catch (SQLException e) { 680 if (!abortOnError) { 681 if (m_errorLogging) { 682 m_errors.add("Error executing SQL statement: " + statement); 683 m_errors.add(CmsException.getStackTraceAsString(e)); 684 } 685 } else { 686 throw e; 687 } 688 } 689 690 // reset 691 statement = ""; 692 } 693 } 694 695 statement += " \n"; 696 } 697 } catch (SQLException e) { 698 if (m_errorLogging) { 699 m_errors.add("Error executing SQL statement: " + statement); 700 m_errors.add(CmsException.getStackTraceAsString(e)); 701 } 702 } catch (Exception e) { 703 if (m_errorLogging) { 704 m_errors.add("Error parsing database setup SQL script in line: " + line); 705 m_errors.add(CmsException.getStackTraceAsString(e)); 706 } 707 } finally { 708 try { 709 if (reader != null) { 710 reader.close(); 711 } 712 } catch (Exception e) { 713 // noop 714 } 715 } 716 } 717 718 /** 719 * Internal method to parse and execute a setup script.<p> 720 * 721 * @param databaseKey the database variant of the script 722 * @param sqlScript the name of the script 723 * @param replacers the replacements to perform in the script 724 * @param abortOnError if a error occurs this flag indicates if to continue or to abort 725 */ 726 private void executeSql(String databaseKey, String sqlScript, Map<String, String> replacers, boolean abortOnError) { 727 728 String filename = null; 729 try { 730 filename = m_basePath 731 + CmsSetupBean.FOLDER_SETUP 732 + "database" 733 + File.separator 734 + databaseKey 735 + File.separator 736 + sqlScript; 737 executeSql(new FileReader(filename), replacers, abortOnError); 738 } catch (FileNotFoundException e) { 739 if (m_errorLogging) { 740 m_errors.add("Database setup SQL script not found: " + filename); 741 m_errors.add(CmsException.getStackTraceAsString(e)); 742 } 743 } 744 } 745 746 /** 747 * Creates and executes a database statement from a String.<p> 748 * 749 * @param statement the database statement 750 * 751 * @throws SQLException if something goes wrong 752 */ 753 private void executeStatement(String statement) throws SQLException { 754 755 Statement stmt = null; 756 757 try { 758 stmt = m_con.createStatement(); 759 stmt.execute(statement); 760 } finally { 761 if (stmt != null) { 762 stmt.close(); 763 } 764 } 765 } 766 767 /** 768 * Replaces tokens "${xxx}" in a specified SQL query.<p> 769 * 770 * @param sql a SQL query 771 * @param replacers a Map with values keyed by "${xxx}" tokens 772 * @return the SQl query with all "${xxx}" tokens replaced 773 */ 774 private String replaceTokens(String sql, Map<String, String> replacers) { 775 776 Iterator<Map.Entry<String, String>> keys = replacers.entrySet().iterator(); 777 while (keys.hasNext()) { 778 Map.Entry<String, String> entry = keys.next(); 779 780 String key = entry.getKey(); 781 String value = entry.getValue(); 782 783 sql = CmsStringUtil.substitute(sql, key, value); 784 } 785 786 return sql; 787 } 788}