001: /**
002: * $Revision$
003: * $Date$
004: *
005: * Copyright (C) 2006 Jive Software. All rights reserved.
006: *
007: * This software is published under the terms of the GNU Public License (GPL),
008: * a copy of which is included in this distribution.
009: */package org.jivesoftware.database;
010:
011: import org.jivesoftware.util.JiveGlobals;
012: import org.jivesoftware.util.LocaleUtils;
013: import org.jivesoftware.util.Log;
014: import org.jivesoftware.openfire.XMPPServer;
015: import org.jivesoftware.openfire.container.Plugin;
016: import org.jivesoftware.openfire.container.PluginManager;
017:
018: import java.io.*;
019: import java.sql.*;
020: import java.util.Arrays;
021:
022: /**
023: * Manages database schemas for Openfire and Openfire plugins. The manager uses the
024: * jiveVersion database table to figure out which database schema is currently installed
025: * and then attempts to automatically apply database schema changes as necessary.<p>
026: *
027: * Running database schemas automatically requires appropriate database permissions.
028: * Without those permissions, the automatic installation/upgrade process will fail
029: * and users will be prompted to apply database changes manually.
030: *
031: * @see DbConnectionManager#getSchemaManager()
032: *
033: * @author Matt Tucker
034: */
035: public class SchemaManager {
036:
037: private static final String CHECK_VERSION_OLD = "SELECT minorVersion FROM jiveVersion";
038: private static final String CHECK_VERSION = "SELECT version FROM jiveVersion WHERE name=?";
039:
040: /**
041: * Current Openfire database schema version.
042: */
043: private static final int DATABASE_VERSION = 13;
044:
045: /**
046: * Creates a new Schema manager.
047: */
048: SchemaManager() {
049:
050: }
051:
052: /**
053: * Checks the Openfire database schema to ensure that it's installed and up to date.
054: * If the schema isn't present or up to date, an automatic update will be attempted.
055: *
056: * @param con a connection to the database.
057: * @return true if database schema checked out fine, or was automatically installed
058: * or updated successfully.
059: */
060: public boolean checkOpenfireSchema(Connection con) {
061: // Change 'wildfire' to 'openfire' in jiveVersion table (update to new name)
062: updateToOpenfire(con);
063: try {
064: return checkSchema(con, "openfire", DATABASE_VERSION,
065: new ResourceLoader() {
066: public InputStream loadResource(
067: String resourceName) {
068: File file = new File(JiveGlobals
069: .getHomeDirectory()
070: + File.separator
071: + "resources"
072: + File.separator + "database",
073: resourceName);
074: try {
075: return new FileInputStream(file);
076: } catch (FileNotFoundException e) {
077: return null;
078: }
079: }
080: });
081: } catch (Exception e) {
082: Log.error(LocaleUtils
083: .getLocalizedString("upgrade.database.failure"), e);
084: System.out.println(LocaleUtils
085: .getLocalizedString("upgrade.database.failure"));
086: }
087: return false;
088: }
089:
090: /**
091: * Checks the plugin's database schema (if one is required) to ensure that it's
092: * installed and up to date. If the schema isn't present or up to date, an automatic
093: * update will be attempted.
094: *
095: * @param plugin the plugin.
096: * @return true if database schema checked out fine, or was automatically installed
097: * or updated successfully, or if it isn't needed. False will only be returned
098: * if there is an error.
099: */
100: public boolean checkPluginSchema(final Plugin plugin) {
101: final PluginManager pluginManager = XMPPServer.getInstance()
102: .getPluginManager();
103: String schemaKey = pluginManager.getDatabaseKey(plugin);
104: int schemaVersion = pluginManager.getDatabaseVersion(plugin);
105: // If the schema key or database version aren't defined, then the plugin doesn't
106: // need database tables.
107: if (schemaKey == null || schemaVersion == -1) {
108: return true;
109: }
110: Connection con = null;
111: try {
112: con = DbConnectionManager.getConnection();
113: return checkSchema(con, schemaKey, schemaVersion,
114: new ResourceLoader() {
115: public InputStream loadResource(
116: String resourceName) {
117: File file = new File(pluginManager
118: .getPluginDirectory(plugin)
119: + File.separator + "database",
120: resourceName);
121: try {
122: return new FileInputStream(file);
123: } catch (FileNotFoundException e) {
124: return null;
125: }
126: }
127: });
128: } catch (Exception e) {
129: Log.error(LocaleUtils
130: .getLocalizedString("upgrade.database.failure"), e);
131: System.out.println(LocaleUtils
132: .getLocalizedString("upgrade.database.failure"));
133: } finally {
134: DbConnectionManager.closeConnection(con);
135: }
136: return false;
137: }
138:
139: /**
140: * Checks to see if the database needs to be upgraded. This method should be
141: * called once every time the application starts up.
142: *
143: * @param con the database connection to use to check the schema with.
144: * @param schemaKey the database schema key (name).
145: * @param requiredVersion the version that the schema should be at.
146: * @param resourceLoader a resource loader that knows how to load schema files.
147: * @throws Exception if an error occured.
148: */
149: private boolean checkSchema(Connection con, String schemaKey,
150: int requiredVersion, ResourceLoader resourceLoader)
151: throws Exception {
152: int currentVersion = -1;
153: PreparedStatement pstmt = null;
154: ResultSet rs = null;
155: try {
156: pstmt = con.prepareStatement(CHECK_VERSION);
157: pstmt.setString(1, schemaKey);
158: rs = pstmt.executeQuery();
159: if (rs.next()) {
160: currentVersion = rs.getInt(1);
161: }
162: } catch (SQLException sqle) {
163: DbConnectionManager.closeResultSet(rs);
164: DbConnectionManager.closeStatement(pstmt);
165: // Releases of Openfire before 2.6.0 stored a major and minor version
166: // number so the normal check for version can fail. Check for the
167: // version using the old format in that case.
168: if (schemaKey.equals("openfire")) {
169: try {
170: if (pstmt != null) {
171: pstmt.close();
172: }
173: pstmt = con.prepareStatement(CHECK_VERSION_OLD);
174: rs = pstmt.executeQuery();
175: if (rs.next()) {
176: currentVersion = rs.getInt(1);
177: }
178: } catch (SQLException sqle2) {
179: // The database schema must not be installed.
180: Log
181: .debug(
182: "SchemaManager: Error verifying server version",
183: sqle2);
184: }
185: }
186: } finally {
187: DbConnectionManager.closeResultSet(rs);
188: DbConnectionManager.closeStatement(pstmt);
189: }
190: // If already up to date, return.
191: if (currentVersion >= requiredVersion) {
192: return true;
193: }
194: // If the database schema isn't installed at all, we need to install it.
195: else if (currentVersion == -1) {
196: Log.info(LocaleUtils.getLocalizedString(
197: "upgrade.database.missing_schema", Arrays
198: .asList(schemaKey)));
199: System.out.println(LocaleUtils.getLocalizedString(
200: "upgrade.database.missing_schema", Arrays
201: .asList(schemaKey)));
202: // Resource will be like "/database/openfire_hsqldb.sql"
203: String resourceName = schemaKey + "_"
204: + DbConnectionManager.getDatabaseType() + ".sql";
205: InputStream resource = resourceLoader
206: .loadResource(resourceName);
207: if (resource == null) {
208: return false;
209: }
210: try {
211: executeSQLScript(con, resource);
212: } catch (Exception e) {
213: Log.error(e);
214: return false;
215: } finally {
216: try {
217: resource.close();
218: } catch (Exception e) {
219: // Ignore.
220: }
221: }
222: Log.info(LocaleUtils
223: .getLocalizedString("upgrade.database.success"));
224: System.out.println(LocaleUtils
225: .getLocalizedString("upgrade.database.success"));
226: return true;
227: }
228: // Must have a version of the schema that needs to be upgraded.
229: else {
230: // The database is an old version that needs to be upgraded.
231: Log.info(LocaleUtils
232: .getLocalizedString("upgrade.database.old_schema",
233: Arrays.asList(currentVersion, schemaKey,
234: requiredVersion)));
235: System.out.println(LocaleUtils
236: .getLocalizedString("upgrade.database.old_schema",
237: Arrays.asList(currentVersion, schemaKey,
238: requiredVersion)));
239: // If the database type is unknown, we don't know how to upgrade it.
240: if (DbConnectionManager.getDatabaseType() == DbConnectionManager.DatabaseType.unknown) {
241: Log
242: .info(LocaleUtils
243: .getLocalizedString("upgrade.database.unknown_db"));
244: System.out
245: .println(LocaleUtils
246: .getLocalizedString("upgrade.database.unknown_db"));
247: return false;
248: }
249: // Upgrade scripts for interbase are not maintained.
250: else if (DbConnectionManager.getDatabaseType() == DbConnectionManager.DatabaseType.interbase) {
251: Log
252: .info(LocaleUtils
253: .getLocalizedString("upgrade.database.interbase_db"));
254: System.out
255: .println(LocaleUtils
256: .getLocalizedString("upgrade.database.interbase_db"));
257: return false;
258: }
259:
260: // Run all upgrade scripts until we're up to the latest schema.
261: for (int i = currentVersion + 1; i <= requiredVersion; i++) {
262: InputStream resource = getUpgradeResource(
263: resourceLoader, i, schemaKey);
264: if (resource == null) {
265: continue;
266: }
267: try {
268: executeSQLScript(con, resource);
269: } catch (Exception e) {
270: Log.error(e);
271: return false;
272: } finally {
273: try {
274: resource.close();
275: } catch (Exception e) {
276: // Ignore.
277: }
278: }
279: }
280: Log.info(LocaleUtils
281: .getLocalizedString("upgrade.database.success"));
282: System.out.println(LocaleUtils
283: .getLocalizedString("upgrade.database.success"));
284: return true;
285: }
286: }
287:
288: private InputStream getUpgradeResource(
289: ResourceLoader resourceLoader, int upgradeVersion,
290: String schemaKey) {
291: InputStream resource = null;
292: if ("openfire".equals(schemaKey)) {
293: // Resource will be like "/database/upgrade/6/openfire_hsqldb.sql"
294: String path = JiveGlobals.getHomeDirectory()
295: + File.separator + "resources" + File.separator
296: + "database" + File.separator + "upgrade"
297: + File.separator + upgradeVersion;
298: String filename = schemaKey + "_"
299: + DbConnectionManager.getDatabaseType() + ".sql";
300: File file = new File(path, filename);
301: try {
302: resource = new FileInputStream(file);
303: } catch (FileNotFoundException e) {
304: // If the resource is null, the specific upgrade number is not available.
305: }
306: } else {
307: String resourceName = "upgrade/" + upgradeVersion + "/"
308: + schemaKey + "_"
309: + DbConnectionManager.getDatabaseType() + ".sql";
310: resource = resourceLoader.loadResource(resourceName);
311: }
312: return resource;
313: }
314:
315: private void updateToOpenfire(Connection con) {
316: PreparedStatement pstmt = null;
317: try {
318: pstmt = con
319: .prepareStatement("UPDATE jiveVersion SET name='openfire' WHERE name='wildfire'");
320: pstmt.executeUpdate();
321: } catch (Exception ex) {
322: Log.warn("Error when trying to update to new name", ex);
323: } finally {
324: DbConnectionManager.closeStatement(pstmt);
325: }
326: }
327:
328: /**
329: * Executes a SQL script.
330: *
331: * @param con database connection.
332: * @param resource an input stream for the script to execute.
333: * @throws IOException if an IOException occurs.
334: * @throws SQLException if an SQLException occurs.
335: */
336: private static void executeSQLScript(Connection con,
337: InputStream resource) throws IOException, SQLException {
338: BufferedReader in = null;
339: try {
340: in = new BufferedReader(new InputStreamReader(resource));
341: boolean done = false;
342: while (!done) {
343: StringBuilder command = new StringBuilder();
344: while (true) {
345: String line = in.readLine();
346: if (line == null) {
347: done = true;
348: break;
349: }
350: // Ignore comments and blank lines.
351: if (isSQLCommandPart(line)) {
352: command.append(" ").append(line);
353: }
354: if (line.trim().endsWith(";")) {
355: break;
356: }
357: }
358: // Send command to database.
359: if (!done && !command.toString().equals("")) {
360: // Remove last semicolon when using Oracle or DB2 to prevent "invalid character error"
361: if (DbConnectionManager.getDatabaseType() == DbConnectionManager.DatabaseType.oracle
362: || DbConnectionManager.getDatabaseType() == DbConnectionManager.DatabaseType.db2) {
363: command.deleteCharAt(command.length() - 1);
364: }
365: Statement stmt = con.createStatement();
366: stmt.execute(command.toString());
367: stmt.close();
368: }
369: }
370: } finally {
371: if (in != null) {
372: try {
373: in.close();
374: } catch (Exception e) {
375: Log.error(e);
376: }
377: }
378: }
379: }
380:
381: private static abstract class ResourceLoader {
382:
383: public abstract InputStream loadResource(String resourceName);
384:
385: }
386:
387: /**
388: * Returns true if a line from a SQL schema is a valid command part.
389: *
390: * @param line the line of the schema.
391: * @return true if a valid command part.
392: */
393: private static boolean isSQLCommandPart(String line) {
394: line = line.trim();
395: if (line.equals("")) {
396: return false;
397: }
398: // Check to see if the line is a comment. Valid comment types:
399: // "//" is HSQLDB
400: // "--" is DB2 and Postgres
401: // "#" is MySQL
402: // "REM" is Oracle
403: // "/*" is SQLServer
404: return !(line.startsWith("//") || line.startsWith("--")
405: || line.startsWith("#") || line.startsWith("REM")
406: || line.startsWith("/*") || line.startsWith("*"));
407: }
408: }
|