1 /* 2 * Copyright (C) 2005-2008 Jive Software. All rights reserved. 3 * 4 * Licensed under the Apache License, Version 2.0 (the "License"); 5 * you may not use this file except in compliance with the License. 6 * You may obtain a copy of the License at 7 * 8 * http://www.apache.org/licenses/LICENSE-2.0 9 * 10 * Unless required by applicable law or agreed to in writing, software 11 * distributed under the License is distributed on an "AS IS" BASIS, 12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13 * See the License for the specific language governing permissions and 14 * limitations under the License. 15 */ 16 17 package org.jivesoftware.database; 18 19 import java.io.BufferedReader; 20 import java.io.File; 21 import java.io.FileInputStream; 22 import java.io.FileNotFoundException; 23 import java.io.IOException; 24 import java.io.InputStream; 25 import java.io.InputStreamReader; 26 import java.sql.Connection; 27 import java.sql.PreparedStatement; 28 import java.sql.ResultSet; 29 import java.sql.SQLException; 30 import java.sql.Statement; 31 import java.util.Arrays; 32 33 import org.jivesoftware.database.bugfix.OF1515; 34 import org.jivesoftware.database.bugfix.OF33; 35 import org.jivesoftware.openfire.XMPPServer; 36 import org.jivesoftware.openfire.container.Plugin; 37 import org.jivesoftware.openfire.container.PluginManager; 38 import org.jivesoftware.openfire.container.PluginMetadataHelper; 39 import org.jivesoftware.util.JiveGlobals; 40 import org.jivesoftware.util.LocaleUtils; 41 import org.slf4j.Logger; 42 import org.slf4j.LoggerFactory; 43 44 /** 45 * Manages database schemas for Openfire and Openfire plugins. The manager uses the 46 * ofVersion database table to figure out which database schema is currently installed 47 * and then attempts to automatically apply database schema changes as necessary.<p> 48 * 49 * Running database schemas automatically requires appropriate database permissions. 50 * Without those permissions, the automatic installation/upgrade process will fail 51 * and users will be prompted to apply database changes manually. 52 * 53 * @see DbConnectionManager#getSchemaManager() 54 * 55 * @author Matt Tucker 56 */ 57 public class SchemaManager { 58 59 private static final Logger Log = LoggerFactory.getLogger(SchemaManager.class); 60 61 private static final String CHECK_VERSION_OLD = 62 "SELECT minorVersion FROM jiveVersion"; 63 private static final String CHECK_VERSION = 64 "SELECT version FROM ofVersion WHERE name=?"; 65 private static final String CHECK_VERSION_JIVE = 66 "SELECT version FROM jiveVersion WHERE name=?"; 67 68 /** 69 * Current Openfire database schema version. 70 */ 71 private static final int DATABASE_VERSION = 33; 72 73 /** 74 * Checks the Openfire database schema to ensure that it's installed and up to date. 75 * If the schema isn't present or up to date, an automatic update will be attempted. 76 * 77 * @param con a connection to the database. 78 * @return true if database schema checked out fine, or was automatically installed 79 * or updated successfully. 80 */ checkOpenfireSchema(Connection con)81 public boolean checkOpenfireSchema(Connection con) { 82 // Change 'wildfire' to 'openfire' in ofVersion table (update to new name) 83 updateToOpenfire(con); 84 try { 85 return checkSchema(con, "openfire", DATABASE_VERSION, 86 new ResourceLoader() { 87 @Override 88 public InputStream loadResource(String resourceName) { 89 File file = new File(JiveGlobals.getHomeDirectory() + File.separator + 90 "resources" + File.separator + "database", resourceName); 91 try { 92 return new FileInputStream(file); 93 } 94 catch (FileNotFoundException e) { 95 return null; 96 } 97 } 98 }); 99 } 100 catch (Exception e) { 101 Log.error(LocaleUtils.getLocalizedString("upgrade.database.failure"), e); 102 System.out.println(LocaleUtils.getLocalizedString("upgrade.database.failure")); 103 } 104 return false; 105 } 106 107 /** 108 * Checks the plugin's database schema (if one is required) to ensure that it's 109 * installed and up to date. If the schema isn't present or up to date, an automatic 110 * update will be attempted. 111 * 112 * @param plugin the plugin. 113 * @return true if database schema checked out fine, or was automatically installed 114 * or updated successfully, or if it isn't needed. False will only be returned 115 * if there is an error. 116 */ 117 public boolean checkPluginSchema(final Plugin plugin) { 118 final PluginManager pluginManager = XMPPServer.getInstance().getPluginManager(); 119 String schemaKey = PluginMetadataHelper.getDatabaseKey(plugin); 120 int schemaVersion = PluginMetadataHelper.getDatabaseVersion(plugin); 121 // If the schema key or database version aren't defined, then the plugin doesn't 122 // need database tables. 123 if (schemaKey == null || schemaVersion == -1) { 124 return true; 125 } 126 Connection con = null; 127 try { 128 con = DbConnectionManager.getConnection(); 129 return checkSchema(con, schemaKey, schemaVersion, new ResourceLoader() { 130 @Override 131 public InputStream loadResource(String resourceName) { 132 File file = new File(pluginManager.getPluginPath(plugin) + 133 File.separator + "database", resourceName); 134 try { 135 return new FileInputStream(file); 136 } 137 catch (FileNotFoundException e) { 138 return null; 139 } 140 } 141 }); 142 } 143 catch (Exception e) { 144 Log.error(LocaleUtils.getLocalizedString("upgrade.database.failure"), e); 145 System.out.println(LocaleUtils.getLocalizedString("upgrade.database.failure")); 146 } 147 finally { 148 DbConnectionManager.closeConnection(con); 149 } 150 return false; 151 } 152 153 /** 154 * Checks to see if the database needs to be upgraded. This method should be 155 * called once every time the application starts up. 156 * 157 * @param con the database connection to use to check the schema with. 158 * @param schemaKey the database schema key (name). 159 * @param requiredVersion the version that the schema should be at. 160 * @param resourceLoader a resource loader that knows how to load schema files. 161 * @throws Exception if an error occured. 162 * @return True if the schema update was successful. 163 */ 164 private boolean checkSchema(Connection con, String schemaKey, int requiredVersion, 165 ResourceLoader resourceLoader) throws Exception 166 { 167 int currentVersion = -1; 168 PreparedStatement pstmt = null; 169 ResultSet rs = null; 170 try { 171 pstmt = con.prepareStatement(CHECK_VERSION); 172 pstmt.setString(1, schemaKey); 173 rs = pstmt.executeQuery(); 174 if (rs.next()) { 175 currentVersion = rs.getInt(1); 176 } 177 } 178 catch (SQLException sqle) { 179 // The database schema must not be installed. 180 Log.debug("SchemaManager: Error verifying "+schemaKey+" version, probably ignorable.", sqle); 181 DbConnectionManager.closeStatement(rs, pstmt); 182 if (schemaKey.equals("openfire")) { 183 try { 184 // Releases of Openfire before 3.6.0 stored the version in a jiveVersion table. 185 pstmt = con.prepareStatement(CHECK_VERSION_JIVE); 186 pstmt.setString(1, schemaKey); 187 rs = pstmt.executeQuery(); 188 if (rs.next()) { 189 currentVersion = rs.getInt(1); 190 } 191 } 192 catch (SQLException sqlea) { 193 // The database schema must not be installed. 194 Log.debug("SchemaManager: Error verifying "+schemaKey+" version, probably ignorable.", sqlea); 195 DbConnectionManager.closeStatement(rs, pstmt); 196 197 // Releases of Openfire before 2.6.0 stored a major and minor version 198 // number so the normal check for version can fail. Check for the 199 // version using the old format in that case. 200 try { 201 202 pstmt = con.prepareStatement(CHECK_VERSION_OLD); 203 rs = pstmt.executeQuery(); 204 if (rs.next()) { 205 currentVersion = rs.getInt(1); 206 } 207 } 208 catch (SQLException sqle2) { 209 // The database schema must not be installed. 210 Log.debug("SchemaManager: Error verifying "+schemaKey+" version, probably ignorable", sqle2); 211 } 212 } 213 } 214 } 215 finally { 216 DbConnectionManager.closeStatement(rs, pstmt); 217 } 218 // If already up to date, return. 219 if (currentVersion >= requiredVersion) { 220 return true; 221 } 222 // If the database schema isn't installed at all, we need to install it. 223 else if (currentVersion == -1) { 224 Log.info(LocaleUtils.getLocalizedString("upgrade.database.missing_schema", 225 Arrays.asList(schemaKey))); 226 System.out.println(LocaleUtils.getLocalizedString("upgrade.database.missing_schema", 227 Arrays.asList(schemaKey))); 228 // Resource will be like "/database/openfire_hsqldb.sql" 229 String resourceName = schemaKey + "_" + 230 DbConnectionManager.getDatabaseType() + ".sql"; 231 232 try (InputStream resource = resourceLoader.loadResource(resourceName)) { 233 if (resource == null) { 234 return false; 235 } 236 // For plugins, we will automatically convert jiveVersion to ofVersion 237 executeSQLScript(con, resource, !schemaKey.equals("openfire") && !schemaKey.equals("wildfire")); 238 } 239 catch (Exception e) { 240 Log.error(e.getMessage(), e); 241 return false; 242 } 243 Log.info(LocaleUtils.getLocalizedString("upgrade.database.success")); 244 System.out.println(LocaleUtils.getLocalizedString("upgrade.database.success")); 245 return true; 246 } 247 // Must have a version of the schema that needs to be upgraded. 248 else { 249 // The database is an old version that needs to be upgraded. 250 Log.info(LocaleUtils.getLocalizedString("upgrade.database.old_schema", 251 Arrays.asList(currentVersion, schemaKey, requiredVersion))); 252 System.out.println(LocaleUtils.getLocalizedString("upgrade.database.old_schema", 253 Arrays.asList(currentVersion, schemaKey, requiredVersion))); 254 // If the database type is unknown, we don't know how to upgrade it. 255 if (DbConnectionManager.getDatabaseType() == DbConnectionManager.DatabaseType.unknown) { 256 Log.info(LocaleUtils.getLocalizedString("upgrade.database.unknown_db")); 257 System.out.println(LocaleUtils.getLocalizedString("upgrade.database.unknown_db")); 258 return false; 259 } 260 // Upgrade scripts for interbase are not maintained. 261 else if (DbConnectionManager.getDatabaseType() == DbConnectionManager.DatabaseType.interbase) { 262 Log.info(LocaleUtils.getLocalizedString("upgrade.database.interbase_db")); 263 System.out.println(LocaleUtils.getLocalizedString("upgrade.database.interbase_db")); 264 return false; 265 } 266 267 // Run all upgrade scripts until we're up to the latest schema. 268 for (int i = currentVersion + 1; i <= requiredVersion; i++) { 269 try (InputStream resource = getUpgradeResource(resourceLoader, i, schemaKey)) { 270 // apply the 'database-patches-done-in-java' 271 try { 272 if (i == 21 && schemaKey.equals("openfire")) { 273 OF33.executeFix(con); 274 } 275 if (i == 28 && schemaKey.equals("openfire")) { 276 OF1515.executeFix(); 277 } 278 } catch (Exception e) { 279 Log.error(e.getMessage(), e); 280 return false; 281 } 282 if (resource == null) { 283 continue; 284 } 285 executeSQLScript(con, resource, !schemaKey.equals("openfire") && !schemaKey.equals("wildfire")); 286 } catch (Exception e) { 287 Log.error(e.getMessage(), e); 288 return false; 289 } 290 } 291 Log.info(LocaleUtils.getLocalizedString("upgrade.database.success")); 292 System.out.println(LocaleUtils.getLocalizedString("upgrade.database.success")); 293 return true; 294 } 295 } 296 297 private InputStream getUpgradeResource(ResourceLoader resourceLoader, int upgradeVersion, 298 String schemaKey) 299 { 300 InputStream resource = null; 301 if ("openfire".equals(schemaKey)) { 302 // Resource will be like "/database/upgrade/6/openfire_hsqldb.sql" 303 String path = JiveGlobals.getHomeDirectory() + File.separator + "resources" + 304 File.separator + "database" + File.separator + "upgrade" + File.separator + 305 upgradeVersion; 306 String filename = schemaKey + "_" + DbConnectionManager.getDatabaseType() + ".sql"; 307 File file = new File(path, filename); 308 try { 309 resource = new FileInputStream(file); 310 } 311 catch (FileNotFoundException e) { 312 // If the resource is null, the specific upgrade number is not available. 313 } 314 } 315 else { 316 String resourceName = "upgrade/" + upgradeVersion + "/" + schemaKey + "_" + 317 DbConnectionManager.getDatabaseType() + ".sql"; 318 resource = resourceLoader.loadResource(resourceName); 319 } 320 return resource; 321 } 322 323 private void updateToOpenfire(Connection con){ 324 PreparedStatement pstmt = null; 325 try { 326 pstmt = con.prepareStatement("UPDATE jiveVersion SET name='openfire' WHERE name='wildfire'"); 327 pstmt.executeUpdate(); 328 } 329 catch (Exception ex) { 330 // Log.warn("Error when trying to update to new name", ex); 331 // This is "scary" to see in the logs and causes more confusion than it's worth at this point. 332 // So silently move on. 333 } 334 finally { 335 DbConnectionManager.closeStatement(pstmt); 336 } 337 } 338 339 /** 340 * Executes a SQL script. 341 * 342 * @param con database connection. 343 * @param resource an input stream for the script to execute. 344 * @param autoreplace automatically replace jiveVersion with ofVersion 345 * @throws IOException if an IOException occurs. 346 * @throws SQLException if an SQLException occurs. 347 */ 348 private static void executeSQLScript(Connection con, InputStream resource, Boolean autoreplace) throws IOException, 349 SQLException 350 { 351 try (BufferedReader in = new BufferedReader(new InputStreamReader(resource))) { 352 boolean done = false; 353 while (!done) { 354 StringBuilder command = new StringBuilder(); 355 while (true) { 356 String line = in.readLine(); 357 if (line == null) { 358 done = true; 359 break; 360 } 361 // Ignore comments and blank lines. 362 if (isSQLCommandPart(line)) { 363 command.append(' ').append(line); 364 } 365 if (line.trim().endsWith(";")) { 366 break; 367 } 368 } 369 // Send command to database. 370 if (!done && !command.toString().equals("")) { 371 // Remove last semicolon when using Oracle or DB2 to prevent "invalid character error" 372 if (DbConnectionManager.getDatabaseType() == DbConnectionManager.DatabaseType.oracle || 373 DbConnectionManager.getDatabaseType() == DbConnectionManager.DatabaseType.db2) { 374 command.deleteCharAt(command.length() - 1); 375 } 376 /* 377 * PreparedStatements are not useful at this Point, because no parameters are set. They also prevent the 378 * creation of trigger in orcale, so use simple statements 379 * (see http://docs.oracle.com/cd/E11882_01/java.112/e16548/oraint.htm#CHDIIDBE) 380 */ 381 Statement stmt = null; 382 try { 383 String cmdString = command.toString(); 384 if (autoreplace) { 385 cmdString = cmdString.replaceAll("jiveVersion", "ofVersion"); 386 } 387 stmt = con.createStatement(); 388 stmt.execute(cmdString); 389 } 390 catch (SQLException e) { 391 // Lets show what failed 392 Log.error("SchemaManager: Failed to execute SQL:\n"+command.toString()); 393 throw e; 394 } 395 finally { 396 DbConnectionManager.closeStatement(stmt); 397 } 398 } 399 } 400 } 401 } 402 403 private static abstract class ResourceLoader { 404 405 public abstract InputStream loadResource(String resourceName); 406 407 } 408 409 /** 410 * Returns true if a line from a SQL schema is a valid command part. 411 * 412 * @param line the line of the schema. 413 * @return true if a valid command part. 414 */ 415 private static boolean isSQLCommandPart(String line) { 416 line = line.trim(); 417 if (line.equals("")) { 418 return false; 419 } 420 // Check to see if the line is a comment. Valid comment types: 421 // "//" is HSQLDB 422 // "--" is DB2 and Postgres 423 // "#" is MySQL 424 // "REM" is Oracle 425 // "/*" is SQLServer 426 return !(line.startsWith("//") || line.startsWith("--") || line.startsWith("#") || 427 line.startsWith("REM") || line.startsWith("/*") || line.startsWith("*")); 428 } 429 } 430