SchemaManager.java 18.3 KB
Newer Older
1
/**
2
 * Copyright (C) 2005-2008 Jive Software. All rights reserved.
3
 *
4 5 6 7 8 9 10 11 12 13 14
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
15 16 17 18
 */

package org.jivesoftware.database;

19 20 21 22 23 24 25 26 27 28 29 30 31
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;

32
import org.jivesoftware.database.bugfix.OF33;
33 34 35
import org.jivesoftware.openfire.XMPPServer;
import org.jivesoftware.openfire.container.Plugin;
import org.jivesoftware.openfire.container.PluginManager;
36 37 38 39
import org.jivesoftware.util.JiveGlobals;
import org.jivesoftware.util.LocaleUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
40 41

/**
42
 * Manages database schemas for Openfire and Openfire plugins. The manager uses the
43
 * ofVersion database table to figure out which database schema is currently installed
44 45 46 47 48 49 50 51 52 53 54 55
 * 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 {

56 57
	private static final Logger Log = LoggerFactory.getLogger(SchemaManager.class);

58
    private static final String CHECK_VERSION_OLD =
59
            "SELECT minorVersion FROM jiveVersion";
60
    private static final String CHECK_VERSION =
61
            "SELECT version FROM ofVersion WHERE name=?";
62 63
    private static final String CHECK_VERSION_JIVE =
            "SELECT version FROM jiveVersion WHERE name=?";
64 65

    /**
66
     * Current Openfire database schema version.
67
     */
68
    private static final int DATABASE_VERSION = 25;
69 70

    /**
71
     * Checks the Openfire database schema to ensure that it's installed and up to date.
72 73 74 75 76 77
     * 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.
     */
78
    public boolean checkOpenfireSchema(Connection con) {
79
        // Change 'wildfire' to 'openfire' in ofVersion table (update to new name)
80
        updateToOpenfire(con);
81
        try {
82
            return checkSchema(con, "openfire", DATABASE_VERSION,
83
                    new ResourceLoader() {
84 85
                        @Override
						public InputStream loadResource(String resourceName) {
86 87 88 89
                            File file = new File(JiveGlobals.getHomeDirectory() + File.separator +
                                    "resources" + File.separator + "database", resourceName);
                            try {
                                return new FileInputStream(file);
90 91
                            }
                            catch (FileNotFoundException e) {
92 93
                                return null;
                            }
94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110
                        }
                    });
        }
        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
111 112
     *      or updated successfully, or if it isn't needed. False will only be returned
     *      if there is an error.
113 114 115 116 117 118 119 120
     */
    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
121
            return true;
122 123 124 125 126
        }
        Connection con = null;
        try {
            con = DbConnectionManager.getConnection();
            return checkSchema(con, schemaKey, schemaVersion, new ResourceLoader() {
127 128
                @Override
				public InputStream loadResource(String resourceName) {
129 130 131 132 133 134 135 136 137 138
                    File file = new File(pluginManager.getPluginDirectory(plugin) +
                            File.separator + "database", resourceName);
                    try {
                        return new FileInputStream(file);
                    }
                    catch (FileNotFoundException e) {
                        return null;
                    }
                }
            });
139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158
        }
        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.
159
     * @return True if the schema update was successful.
160 161 162 163 164 165
     */
    private boolean checkSchema(Connection con, String schemaKey, int requiredVersion,
            ResourceLoader resourceLoader) throws Exception
    {
        int currentVersion = -1;
        PreparedStatement pstmt = null;
166
        ResultSet rs = null;
167 168 169
        try {
            pstmt = con.prepareStatement(CHECK_VERSION);
            pstmt.setString(1, schemaKey);
170
            rs = pstmt.executeQuery();
171 172 173
            if (rs.next()) {
                currentVersion = rs.getInt(1);
            }
174 175
        }
        catch (SQLException sqle) {
176 177
            // The database schema must not be installed.
            Log.debug("SchemaManager: Error verifying "+schemaKey+" version, probably ignorable.", sqle);
178
            DbConnectionManager.closeStatement(rs, pstmt);
179
            if (schemaKey.equals("openfire")) {
180
                try {
181 182 183
                    // Releases of Openfire before 3.6.0 stored the version in a jiveVersion table.
                    pstmt = con.prepareStatement(CHECK_VERSION_JIVE);
                    pstmt.setString(1, schemaKey);
184
                    rs = pstmt.executeQuery();
185 186 187 188
                    if (rs.next()) {
                        currentVersion = rs.getInt(1);
                    }
                }
189
                catch (SQLException sqlea) {
190 191
                    // The database schema must not be installed.
                    Log.debug("SchemaManager: Error verifying "+schemaKey+" version, probably ignorable.", sqlea);
192 193
                    DbConnectionManager.closeStatement(rs, pstmt);

194 195 196 197
                    // Releases of Openfire 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.
                    try {
198

199 200 201 202 203 204 205 206
                        pstmt = con.prepareStatement(CHECK_VERSION_OLD);
                        rs = pstmt.executeQuery();
                        if (rs.next()) {
                            currentVersion = rs.getInt(1);
                        }
                    }
                    catch (SQLException sqle2) {
                        // The database schema must not be installed.
207
                        Log.debug("SchemaManager: Error verifying "+schemaKey+" version, probably ignorable", sqle2);
208
                    }
209 210 211 212
                }
            }
        }
        finally {
213
            DbConnectionManager.closeStatement(rs, pstmt);
214 215
        }
        // If already up to date, return.
216
        if (currentVersion >= requiredVersion) {
Matt Tucker's avatar
Matt Tucker committed
217
            return true;
218 219 220
        }
        // If the database schema isn't installed at all, we need to install it.
        else if (currentVersion == -1) {
221
            Log.info(LocaleUtils.getLocalizedString("upgrade.database.missing_schema",
222 223 224
                    Arrays.asList(schemaKey)));
            System.out.println(LocaleUtils.getLocalizedString("upgrade.database.missing_schema",
                    Arrays.asList(schemaKey)));
225
            // Resource will be like "/database/openfire_hsqldb.sql"
226 227
            String resourceName = schemaKey + "_" +
                    DbConnectionManager.getDatabaseType() + ".sql";
228 229 230 231 232

            try (InputStream resource = resourceLoader.loadResource(resourceName)) {
                if (resource == null) {
                    return false;
                }
233 234
                // For plugins, we will automatically convert jiveVersion to ofVersion
                executeSQLScript(con, resource, !schemaKey.equals("openfire") && !schemaKey.equals("wildfire"));
235 236
            }
            catch (Exception e) {
237
                Log.error(e.getMessage(), e);
238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264
                return false;
            }
            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.
265
            for (int i = currentVersion + 1; i <= requiredVersion; i++) {
266 267
                try (InputStream resource = getUpgradeResource(resourceLoader, i, schemaKey)) {
                    // apply the 'database-patches-done-in-java'
268
                    try {
269 270 271 272 273 274
                        if (i == 21 && schemaKey.equals("openfire")) {
                            OF33.executeFix(con);
                        }
                    } catch (Exception e) {
                        Log.error(e.getMessage(), e);
                        return false;
275
                    }
276 277
                    if (resource == null) {
                        continue;
278
                    }
279 280 281 282
                    executeSQLScript(con, resource, !schemaKey.equals("openfire") && !schemaKey.equals("wildfire"));
                } catch (Exception e) {
                    Log.error(e.getMessage(), e);
                    return false;
283 284 285 286 287 288 289 290
                }
            }
            Log.info(LocaleUtils.getLocalizedString("upgrade.database.success"));
            System.out.println(LocaleUtils.getLocalizedString("upgrade.database.success"));
            return true;
        }
    }

291
    private InputStream getUpgradeResource(ResourceLoader resourceLoader, int upgradeVersion,
292
            String schemaKey)
293 294
    {
        InputStream resource = null;
295 296
        if ("openfire".equals(schemaKey)) {
            // Resource will be like "/database/upgrade/6/openfire_hsqldb.sql"
297 298 299 300 301 302 303 304 305 306 307 308 309
            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 {
310
            String resourceName = "upgrade/" + upgradeVersion + "/" + schemaKey + "_" +
311 312 313 314 315 316
                    DbConnectionManager.getDatabaseType() + ".sql";
            resource = resourceLoader.loadResource(resourceName);
        }
        return resource;
    }

317 318 319
    private void updateToOpenfire(Connection con){
        PreparedStatement pstmt = null;
        try {
320
            pstmt = con.prepareStatement("UPDATE jiveVersion SET name='openfire' WHERE name='wildfire'");
321 322 323
            pstmt.executeUpdate();
        }
        catch (Exception ex) {
324 325 326
//            Log.warn("Error when trying to update to new name", ex);
            // This is "scary" to see in the logs and causes more confusion than it's worth at this point.
            // So silently move on.
327 328 329 330 331 332
        }
        finally {
            DbConnectionManager.closeStatement(pstmt);
        }
    }

333 334 335 336 337
    /**
     * Executes a SQL script.
     *
     * @param con database connection.
     * @param resource an input stream for the script to execute.
338
     * @param autoreplace automatically replace jiveVersion with ofVersion
339 340 341
     * @throws IOException if an IOException occurs.
     * @throws SQLException if an SQLException occurs.
     */
342
    private static void executeSQLScript(Connection con, InputStream resource, Boolean autoreplace) throws IOException,
343 344
            SQLException
    {
345
        try (BufferedReader in = new BufferedReader(new InputStreamReader(resource))) {
346 347 348 349 350 351 352 353 354 355 356
            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)) {
357
                        command.append(' ').append(line);
358
                    }
359
                    if (line.trim().endsWith(";")) {
360 361 362 363 364
                        break;
                    }
                }
                // Send command to database.
                if (!done && !command.toString().equals("")) {
365 366 367
                    // Remove last semicolon when using Oracle or DB2 to prevent "invalid character error"
                    if (DbConnectionManager.getDatabaseType() == DbConnectionManager.DatabaseType.oracle ||
                            DbConnectionManager.getDatabaseType() == DbConnectionManager.DatabaseType.db2) {
368 369
                        command.deleteCharAt(command.length() - 1);
                    }
370
                    PreparedStatement pstmt = null;
371
                    try {
372 373 374 375
                        String cmdString = command.toString();
                        if (autoreplace)  {
                            cmdString = cmdString.replaceAll("jiveVersion", "ofVersion");
                        }
376 377
                        pstmt = con.prepareStatement(cmdString);
                        pstmt.execute();
378 379 380 381 382 383
                    }
                    catch (SQLException e) {
                        // Lets show what failed
                        Log.error("SchemaManager: Failed to execute SQL:\n"+command.toString());
                        throw e;
                    }
384 385 386
                    finally {
                        DbConnectionManager.closeStatement(pstmt);
                    }
387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417
                }
            }
        }
    }

    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("*"));
    }
akrherz's avatar
akrherz committed
418
}