SchemaManager.java 15 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12
/**
 * $Revision$
 * $Date$
 *
 * Copyright (C) 2006 Jive Software. All rights reserved.
 *
 * This software is published under the terms of the GNU Public License (GPL),
 * a copy of which is included in this distribution.
 */

package org.jivesoftware.database;

13
import org.jivesoftware.util.JiveGlobals;
14
import org.jivesoftware.util.LocaleUtils;
15 16
import org.jivesoftware.util.Log;
import org.jivesoftware.wildfire.XMPPServer;
17
import org.jivesoftware.wildfire.container.Plugin;
18
import org.jivesoftware.wildfire.container.PluginManager;
19

20
import java.io.*;
21
import java.sql.*;
22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
import java.util.Arrays;

/**
 * Manages database schemas for Wildfire and Wildfire plugins. The manager uses the
 * jiveVersion database table to figure out which database schema is currently installed
 * and then attempts to automatically apply database schema changes as necessary.<p>
 *
 * Running database schemas automatically requires appropriate database permissions.
 * Without those permissions, the automatic installation/upgrade process will fail
 * and users will be prompted to apply database changes manually.
 *
 * @see DbConnectionManager#getSchemaManager()
 *
 * @author Matt Tucker
 */
public class SchemaManager {

    private static final String CHECK_VERSION_OLD =
            "SELECT minorVersion FROM jiveVersion";
    private static final String CHECK_VERSION =
            "SELECT version FROM jiveVersion WHERE name=?";

    /**
     * Current Wildfire database schema version.
     */
47
    private static final int DATABASE_VERSION = 11;
48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68

    /**
     * Creates a new Schema manager.
     */
    SchemaManager() {

    }

    /**
     * Checks the Wildfire database schema to ensure that it's installed and up to date.
     * If the schema isn't present or up to date, an automatic update will be attempted.
     *
     * @param con a connection to the database.
     * @return true if database schema checked out fine, or was automatically installed
     *      or updated successfully.
     */
    public boolean checkWildfireSchema(Connection con) {
        try {
            return checkSchema(con, "wildfire", DATABASE_VERSION,
                    new ResourceLoader() {
                        public InputStream loadResource(String resourceName) {
69 70 71 72
                            File file = new File(JiveGlobals.getHomeDirectory() + File.separator +
                                    "resources" + File.separator + "database", resourceName);
                            try {
                                return new FileInputStream(file);
73 74
                            }
                            catch (FileNotFoundException e) {
75 76
                                return null;
                            }
77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93
                        }
                    });
        }
        catch (Exception e) {
            Log.error(LocaleUtils.getLocalizedString("upgrade.database.failure"), e);
            System.out.println(LocaleUtils.getLocalizedString("upgrade.database.failure"));
        }
        return false;
    }

    /**
     * Checks the plugin's database schema (if one is required) to ensure that it's
     * installed and up to date. If the schema isn't present or up to date, an automatic
     * update will be attempted.
     *
     * @param plugin the plugin.
     * @return true if database schema checked out fine, or was automatically installed
Matt Tucker's avatar
Matt Tucker committed
94 95
     *      or updated successfully, or if it isn't needed. False will only be returned
     *      if there is an error.
96 97 98 99 100 101 102 103
     */
    public boolean checkPluginSchema(final Plugin plugin) {
        final PluginManager pluginManager = XMPPServer.getInstance().getPluginManager();
        String schemaKey = pluginManager.getDatabaseKey(plugin);
        int schemaVersion = pluginManager.getDatabaseVersion(plugin);
        // If the schema key or database version aren't defined, then the plugin doesn't
        // need database tables.
        if (schemaKey == null || schemaVersion == -1) {
Matt Tucker's avatar
Matt Tucker committed
104
            return true;
105 106 107 108 109
        }
        Connection con = null;
        try {
            con = DbConnectionManager.getConnection();
            return checkSchema(con, schemaKey, schemaVersion, new ResourceLoader() {
110 111 112 113 114 115 116 117 118 119 120
                public InputStream loadResource(String resourceName) {
                    File file = new File(pluginManager.getPluginDirectory(plugin) +
                            File.separator + "database", resourceName);
                    try {
                        return new FileInputStream(file);
                    }
                    catch (FileNotFoundException e) {
                        return null;
                    }
                }
            });
121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146
        }
        catch (Exception e) {
            Log.error(LocaleUtils.getLocalizedString("upgrade.database.failure"), e);
            System.out.println(LocaleUtils.getLocalizedString("upgrade.database.failure"));
        }
        finally {
            DbConnectionManager.closeConnection(con);
        }
        return false;
    }

    /**
     * Checks to see if the database needs to be upgraded. This method should be
     * called once every time the application starts up.
     *
     * @param con the database connection to use to check the schema with.
     * @param schemaKey the database schema key (name).
     * @param requiredVersion the version that the schema should be at.
     * @param resourceLoader a resource loader that knows how to load schema files.
     * @throws Exception if an error occured.
     */
    private boolean checkSchema(Connection con, String schemaKey, int requiredVersion,
            ResourceLoader resourceLoader) throws Exception
    {
        int currentVersion = -1;
        PreparedStatement pstmt = null;
147
        ResultSet rs = null;
148 149 150
        try {
            pstmt = con.prepareStatement(CHECK_VERSION);
            pstmt.setString(1, schemaKey);
151
            rs = pstmt.executeQuery();
152 153 154
            if (rs.next()) {
                currentVersion = rs.getInt(1);
            }
155 156
        }
        catch (SQLException sqle) {
157 158
            DbConnectionManager.closeResultSet(rs);
            DbConnectionManager.closeStatement(pstmt);
159 160 161
            // Releases of Wildfire before 2.6.0 stored a major and minor version
            // number so the normal check for version can fail. Check for the
            // version using the old format in that case.
162 163 164 165 166 167
            if (schemaKey.equals("wildfire")) {
                try {
                    if (pstmt != null) {
                        pstmt.close();
                    }
                    pstmt = con.prepareStatement(CHECK_VERSION_OLD);
168
                    rs = pstmt.executeQuery();
169 170 171 172 173 174
                    if (rs.next()) {
                        currentVersion = rs.getInt(1);
                    }
                }
                catch (SQLException sqle2) {
                    // The database schema must not be installed.
175
                    Log.debug("Error verifying server version", sqle2);
176 177 178 179
                }
            }
        }
        finally {
180 181
            DbConnectionManager.closeResultSet(rs);
            DbConnectionManager.closeStatement(pstmt);
182 183
        }
        // If already up to date, return.
184
        if (currentVersion >= requiredVersion) {
Matt Tucker's avatar
Matt Tucker committed
185
            return true;
186 187 188
        }
        // If the database schema isn't installed at all, we need to install it.
        else if (currentVersion == -1) {
189
            Log.info(LocaleUtils.getLocalizedString("upgrade.database.missing_schema",
190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239
                    Arrays.asList(schemaKey)));
            System.out.println(LocaleUtils.getLocalizedString("upgrade.database.missing_schema",
                    Arrays.asList(schemaKey)));
            // Resource will be like "/database/wildfire_hsqldb.sql"
            String resourceName = schemaKey + "_" +
                    DbConnectionManager.getDatabaseType() + ".sql";
            InputStream resource = resourceLoader.loadResource(resourceName);
            if (resource == null) {
                return false;
            }
            try {
                executeSQLScript(con, resource);
            }
            catch (Exception e) {
                Log.error(e);
                return false;
            }
            finally {
                try {
                    resource.close();
                }
                catch (Exception e) {
                    // Ignore.
                }
            }
            Log.info(LocaleUtils.getLocalizedString("upgrade.database.success"));
            System.out.println(LocaleUtils.getLocalizedString("upgrade.database.success"));
            return true;
        }
        // Must have a version of the schema that needs to be upgraded.
        else {
            // The database is an old version that needs to be upgraded.
            Log.info(LocaleUtils.getLocalizedString("upgrade.database.old_schema",
                    Arrays.asList(currentVersion, schemaKey, requiredVersion)));
            System.out.println(LocaleUtils.getLocalizedString("upgrade.database.old_schema",
                    Arrays.asList(currentVersion, schemaKey, requiredVersion)));
            // If the database type is unknown, we don't know how to upgrade it.
            if (DbConnectionManager.getDatabaseType() == DbConnectionManager.DatabaseType.unknown) {
                Log.info(LocaleUtils.getLocalizedString("upgrade.database.unknown_db"));
                System.out.println(LocaleUtils.getLocalizedString("upgrade.database.unknown_db"));
                return false;
            }
            // Upgrade scripts for interbase are not maintained.
            else if (DbConnectionManager.getDatabaseType() == DbConnectionManager.DatabaseType.interbase) {
                Log.info(LocaleUtils.getLocalizedString("upgrade.database.interbase_db"));
                System.out.println(LocaleUtils.getLocalizedString("upgrade.database.interbase_db"));
                return false;
            }

            // Run all upgrade scripts until we're up to the latest schema.
240
            for (int i = currentVersion + 1; i <= requiredVersion; i++) {
241
                InputStream resource = getUpgradeResource(resourceLoader, i, schemaKey);
242
                if (resource == null) {
243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266
                    continue;
                }
                try {
                    executeSQLScript(con, resource);
                }
                catch (Exception e) {
                    Log.error(e);
                    return false;
                }
                finally {
                    try {
                        resource.close();
                    }
                    catch (Exception e) {
                        // Ignore.
                    }
                }
            }
            Log.info(LocaleUtils.getLocalizedString("upgrade.database.success"));
            System.out.println(LocaleUtils.getLocalizedString("upgrade.database.success"));
            return true;
        }
    }

267
    private InputStream getUpgradeResource(ResourceLoader resourceLoader, int upgradeVersion,
268
            String schemaKey)
269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285
    {
        InputStream resource = null;
        if ("wildfire".equals(schemaKey)) {
            // Resource will be like "/database/upgrade/6/wildfire_hsqldb.sql"
            String path = JiveGlobals.getHomeDirectory() + File.separator + "resources" +
                    File.separator + "database" + File.separator + "upgrade" + File.separator +
                    upgradeVersion;
            String filename = schemaKey + "_" + DbConnectionManager.getDatabaseType() + ".sql";
            File file = new File(path, filename);
            try {
                resource = new FileInputStream(file);
            }
            catch (FileNotFoundException e) {
                // If the resource is null, the specific upgrade number is not available.
            }
        }
        else {
286
            String resourceName = "upgrade/" + upgradeVersion + "/" + schemaKey + "_" +
287 288 289 290 291 292
                    DbConnectionManager.getDatabaseType() + ".sql";
            resource = resourceLoader.loadResource(resourceName);
        }
        return resource;
    }

293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317
    /**
     * Executes a SQL script.
     *
     * @param con database connection.
     * @param resource an input stream for the script to execute.
     * @throws IOException if an IOException occurs.
     * @throws SQLException if an SQLException occurs.
     */
    private static void executeSQLScript(Connection con, InputStream resource) throws IOException,
            SQLException
    {
        BufferedReader in = null;
        try {
            in = new BufferedReader(new InputStreamReader(resource));
            boolean done = false;
            while (!done) {
                StringBuilder command = new StringBuilder();
                while (true) {
                    String line = in.readLine();
                    if (line == null) {
                        done = true;
                        break;
                    }
                    // Ignore comments and blank lines.
                    if (isSQLCommandPart(line)) {
318
                        command.append(" ").append(line);
319
                    }
320
                    if (line.trim().endsWith(";")) {
321 322 323 324 325
                        break;
                    }
                }
                // Send command to database.
                if (!done && !command.toString().equals("")) {
326 327 328 329 330
                    // Remove last semicolon when using Oracle to prevent "invalid character error"
                    if (DbConnectionManager.getDatabaseType() == DbConnectionManager.DatabaseType
                            .oracle) {
                        command.deleteCharAt(command.length() - 1);
                    }
331 332 333 334 335 336 337 338 339 340 341 342
                    Statement stmt = con.createStatement();
                    stmt.execute(command.toString());
                    stmt.close();
                }
            }
        }
        finally {
            if (in != null) {
                try {
                    in.close();
                }
                catch (Exception e) {
343
                    Log.error(e);
344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375
                }
            }
        }
    }

    private static abstract class ResourceLoader {

        public abstract InputStream loadResource(String resourceName);

    }

    /**
     * Returns true if a line from a SQL schema is a valid command part.
     *
     * @param line the line of the schema.
     * @return true if a valid command part.
     */
    private static boolean isSQLCommandPart(String line) {
        line = line.trim();
        if (line.equals("")) {
            return false;
        }
        // Check to see if the line is a comment. Valid comment types:
        //   "//" is HSQLDB
        //   "--" is DB2 and Postgres
        //   "#" is MySQL
        //   "REM" is Oracle
        //   "/*" is SQLServer
        return !(line.startsWith("//") || line.startsWith("--") || line.startsWith("#") ||
                line.startsWith("REM") || line.startsWith("/*") || line.startsWith("*"));
    }
}