/*
 * Licensed to the Apache Software Foundation (ASF) under one or more
 *  contributor license agreements.  The ASF licenses this file to You
 * 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.  For additional information regarding
 * copyright in this work, please see the NOTICE file in the top level
 * directory of this distribution.
 */

package org.apache.roller.weblogger.business.startup;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.roller.weblogger.business.DatabaseProvider;


/**
 * Handles the install/upgrade of the Roller Weblogger database when the user
 * has configured their installation type to 'auto'.
 */
public class DatabaseInstaller {
    
    private static Log log = LogFactory.getLog(DatabaseInstaller.class);
    
    private final DatabaseProvider db;
    private final DatabaseScriptProvider scripts;
    private final String version;
    private List<String> messages = new ArrayList<String>();
    
    // the name of the property which holds the dbversion value
    private static final String DBVERSION_PROP = "roller.database.version";
    
    
    public DatabaseInstaller(DatabaseProvider dbProvider, DatabaseScriptProvider scriptProvider) {
        db = dbProvider;
        scripts = scriptProvider;
        
        Properties props = new Properties();
        try {
            props.load(getClass().getResourceAsStream("/roller-version.properties"));
        } catch (IOException e) {
            log.error("roller-version.properties not found", e);
        }
        
        version = props.getProperty("ro.version", "UNKNOWN");
    }
    
    
    /** 
     * Determine if database schema needs to be upgraded.
     */
    public boolean isCreationRequired() {
        Connection con = null;
        try {            
            con = db.getConnection();
            
            // just check for a couple key Roller tables
            // roller_user table called rolleruser before Roller 5.1
            if (tableExists(con, "userrole") && (tableExists(con, "roller_user") || tableExists(con, "rolleruser"))) {
                return false;
            }
            
        } catch (Exception e) {
            throw new RuntimeException("Error checking for tables", e);
        } finally {
            try {
                if (con != null) {
                    con.close();
                }
            } catch (Exception ignored) {}
        }
        
        return true;
    }
    
    
    /** 
     * Determine if database schema needs to be upgraded.
     */
    public boolean isUpgradeRequired() {
        int desiredVersion = parseVersionString(version);
        int databaseVersion;
        try {
            databaseVersion = getDatabaseVersion();
        } catch (StartupException ex) {
            throw new RuntimeException(ex);
        }
        
        // if dbversion is unset then assume a new install, otherwise compare
        if (databaseVersion < 0) {
            // if this is a fresh db then we need to set the database version
            Connection con = null;
            try {
                con = db.getConnection();
                setDatabaseVersion(con, version);
            } catch (Exception ioe) {
                errorMessage("ERROR setting database version");
            } finally {
                try {
                    if (con != null) {
                        con.close();
                    }
                } catch (Exception ignored) {
                }
            }

            return false;
        } else {
            return databaseVersion < desiredVersion;
        }
    }
    
    
    public List<String> getMessages() {
        return messages;
    }
    
    
    private void errorMessage(String msg) {
        messages.add(msg);
        log.error(msg);
    }    
    
    
    private void errorMessage(String msg, Throwable t) {
        messages.add(msg);
        log.error(msg, t);
    }
    
    
    private void successMessage(String msg) {
        messages.add(msg);
        log.trace(msg);
    }
    
    
    /**
     * Create datatabase tables.
     */
    public void createDatabase() throws StartupException {
        
        log.info("Creating Roller Weblogger database tables.");
        
        Connection con = null;
        SQLScriptRunner create = null;
        try {
            con = db.getConnection();
            String handle = getDatabaseHandle(con);
            create = new SQLScriptRunner(scripts.getDatabaseScript(handle + "/createdb.sql"));
            create.runScript(con, true);
            messages.addAll(create.getMessages());
            
            setDatabaseVersion(con, version);
            
        } catch (SQLException sqle) {
            log.error("ERROR running SQL in database creation script", sqle);
            if (create != null) {
                messages.addAll(create.getMessages());
            }
            errorMessage("ERROR running SQL in database creation script");
            throw new StartupException("Error running sql script", sqle); 
            
        } catch (Exception ioe) {
            log.error("ERROR running database creation script", ioe);
            if (create != null) {
                messages.addAll(create.getMessages());
            }
            errorMessage("ERROR reading/parsing database creation script");
            throw new StartupException("Error running SQL script", ioe);

        } finally {
            try {
                if (con != null) {
                    con.close();
                }
            } catch (Exception ignored) {}
        }
    }
    
    
    /**
     * Upgrade database if dbVersion is older than desiredVersion.
     */
    public void upgradeDatabase(boolean runScripts) throws StartupException {
        
        int myVersion = parseVersionString(version);
        int dbversion = getDatabaseVersion();
        
        log.debug("Database version = "+dbversion);
        log.debug("Desired version = "+myVersion);
       
        Connection con = null;
        try {
            con = db.getConnection();
            if(dbversion < 0) {
                String msg = "Cannot upgrade database tables, Roller database version cannot be determined";
                errorMessage(msg);
                throw new StartupException(msg);
            } else if (dbversion < 310) {
                String msg = "Roller " + myVersion + " cannot upgrade from versions older than 3.10; " +
                        "try first upgrading to an earlier version of Roller.";
                errorMessage(msg);
                throw new StartupException(msg);
            } else if(dbversion >= myVersion) {
                log.info("Database is current, no upgrade needed");
                return;
            }

            log.info("Database is old, beginning upgrade to version "+myVersion);

            // iterate through each upgrade as needed
            // to add to the upgrade sequence simply add a new "if" statement
            // for whatever version needed and then define a new method upgradeXXX()

            if(dbversion < 400) {
                upgradeTo400(con, runScripts);
                dbversion = 400;
            }
            if(dbversion < 500) {
                upgradeTo500(con, runScripts);
                dbversion = 500;
            }
            if(dbversion < 510) {
                upgradeTo510(con, runScripts);
                dbversion = 510;
            }
            if(dbversion < 520) {
                upgradeTo520(con, runScripts);
                dbversion = 520;
            }
            
            // make sure the database version is the exact version
            // we are upgrading too.
            updateDatabaseVersion(con, myVersion);
        
        } catch (SQLException e) {
            throw new StartupException("ERROR obtaining connection");
        } finally {
            try {
                if (con != null) {
                    con.close();
                }
            } catch (Exception ignored) {}
        }
    }

    /**
     * Upgrade database to Roller 4.0.0
     */
    private void upgradeTo400(Connection con, boolean runScripts) throws StartupException {
        
        successMessage("Doing upgrade to 400 ...");
        
        // first we need to run upgrade scripts 
        SQLScriptRunner runner = null;
        try {    
            if (runScripts) {
                String handle = getDatabaseHandle(con);
                String scriptPath = handle + "/310-to-400-migration.sql";
                successMessage("Running database upgrade script: "+scriptPath);                
                runner = new SQLScriptRunner(scripts.getDatabaseScript(scriptPath));
                runner.runScript(con, true);
                messages.addAll(runner.getMessages());
            }
        } catch(Exception ex) {
            log.error("ERROR running 400 database upgrade script", ex);
            if (runner != null) {
                messages.addAll(runner.getMessages());
            }
            
            errorMessage("Problem upgrading database to version 400", ex);
            throw new StartupException("Problem upgrading database to version 400", ex);
        }
        
        
        // now upgrade hierarchical objects data model
        try {
            successMessage("Populating parentid columns for weblogcategory and folder tables");
            
            // Populate parentid in weblogcategory and folder tables.
            //
            // We'd like to do something like the below, but few databases 
            // support multiple table udpates, which are part of SQL-99
            //
            // update weblogcategory, weblogcategoryassoc 
            //   set weblogcategory.parentid = weblogcategoryassoc.ancestorid 
            //   where 
            //      weblogcategory.id = weblogcategoryassoc.categoryid 
            //      and weblogcategoryassoc.relation = 'PARENT';
            //
            // update folder,folderassoc 
            //   set folder.parentid = folderassoc.ancestorid 
            //   where 
            //      folder.id = folderassoc.folderid 
            //      and folderassoc.relation = 'PARENT';
            
            PreparedStatement selectParents = con.prepareStatement(
                "select categoryid, ancestorid from weblogcategoryassoc where relation='PARENT'");
            PreparedStatement updateParent = con.prepareStatement(
                "update weblogcategory set parentid=? where id=?");            
            ResultSet parentSet = selectParents.executeQuery();
            while (parentSet.next()) {
                String categoryid = parentSet.getString(1);
                String parentid = parentSet.getString(2);                
                updateParent.clearParameters();
                updateParent.setString( 1, parentid);
                updateParent.setString( 2, categoryid);
                updateParent.executeUpdate();
            }
            
            selectParents = con.prepareStatement(
                "select folderid, ancestorid from folderassoc where relation='PARENT'");
            updateParent = con.prepareStatement(
                "update folder set parentid=? where id=?");            
            parentSet = selectParents.executeQuery();
            while (parentSet.next()) {
                String folderid = parentSet.getString(1);
                String parentid = parentSet.getString(2);                
                updateParent.clearParameters();
                updateParent.setString( 1, parentid);
                updateParent.setString( 2, folderid);
                updateParent.executeUpdate();
            }
            
            if (!con.getAutoCommit()) {
                con.commit();
            }
           
            successMessage("Done populating parentid columns.");
            
        } catch (Exception e) {
            errorMessage("Problem upgrading database to version 320", e);
            throw new StartupException("Problem upgrading database to version 320", e);
        }
        
        
        try {
            successMessage("Populating path columns for weblogcategory and folder tables.");
                        
            // Populate path in weblogcategory and folder tables.
            //
            // It would be nice if there was a simple sql solution for doing
            // this, but sadly the only real way to do it is through brute
            // force walking the hierarchical trees.  Luckily, it seems that
            // most people don't create multi-level hierarchies, so hopefully
            // this won't be too bad
            
            // set path to '/' for nodes with no parents (aka root nodes)
            PreparedStatement setRootPaths = con.prepareStatement(
                "update weblogcategory set path = '/' where parentid is NULL");
            setRootPaths.clearParameters();
            setRootPaths.executeUpdate();
            
            // select all nodes whose parent has no parent (aka 1st level nodes)
            PreparedStatement selectL1Children = con.prepareStatement(
                "select f.id, f.name from weblogcategory f, weblogcategory p "+
                    "where f.parentid = p.id and p.parentid is NULL");
            // update L1 nodes with their path (/<name>)
            PreparedStatement updateL1Children = con.prepareStatement(
                "update weblogcategory set path=? where id=?");
            ResultSet L1Set = selectL1Children.executeQuery();
            while (L1Set.next()) {
                String id = L1Set.getString(1);
                String name = L1Set.getString(2);                
                updateL1Children.clearParameters();
                updateL1Children.setString( 1, "/"+name);
                updateL1Children.setString( 2, id);
                updateL1Children.executeUpdate();
            }
            
            // now for the complicated part =(
            // we need to keep iterating over L2, L3, etc nodes and setting
            // their path until all nodes have been updated.
            
            // select all nodes whose parent path has been set, excluding L1 nodes
            PreparedStatement selectLxChildren = con.prepareStatement(
                "select f.id, f.name, p.path from weblogcategory f, weblogcategory p "+
                    "where f.parentid = p.id and p.path <> '/' "+
                    "and p.path is not NULL and f.path is NULL");
            // update Lx nodes with their path (<parentPath>/<name>)
            PreparedStatement updateLxChildren = con.prepareStatement(
                "update weblogcategory set path=? where id=?");
            
            // this loop allows us to run this part of the upgrade process as
            // long as is necessary based on the depth of the hierarchy, and
            // we use the do/while construct to ensure it's run at least once
            int catNumCounted = 0;
            do {
                log.debug("Doing pass over Lx children for categories");
                
                // reset count for each iteration of outer loop
                catNumCounted = 0;
                
                ResultSet LxSet = selectLxChildren.executeQuery();
                while (LxSet.next()) {
                    String id = LxSet.getString(1);
                    String name = LxSet.getString(2);
                    String parentPath = LxSet.getString(3);
                    updateLxChildren.clearParameters();
                    updateLxChildren.setString( 1, parentPath+"/"+name);
                    updateLxChildren.setString( 2, id);
                    updateLxChildren.executeUpdate();
                    
                    // count the updated rows
                    catNumCounted++;
                }
                
                log.debug("Updated "+catNumCounted+" Lx category paths");
            } while(catNumCounted > 0);
            
            
            // set path to '/' for nodes with no parents (aka root nodes)
            setRootPaths = con.prepareStatement(
                "update folder set path = '/' where parentid is NULL");
            setRootPaths.clearParameters();
            setRootPaths.executeUpdate();
            
            // select all nodes whose parent has no parent (aka 1st level nodes)
            selectL1Children = con.prepareStatement(
                "select f.id, f.name from folder f, folder p "+
                    "where f.parentid = p.id and p.parentid is NULL");
            // update L1 nodes with their path (/<name>)
            updateL1Children = con.prepareStatement(
                "update folder set path=? where id=?");
            L1Set = selectL1Children.executeQuery();
            while (L1Set.next()) {
                String id = L1Set.getString(1);
                String name = L1Set.getString(2);                
                updateL1Children.clearParameters();
                updateL1Children.setString( 1, "/"+name);
                updateL1Children.setString( 2, id);
                updateL1Children.executeUpdate();
            }
            
            // now for the complicated part =(
            // we need to keep iterating over L2, L3, etc nodes and setting
            // their path until all nodes have been updated.
            
            // select all nodes whose parent path has been set, excluding L1 nodes
            selectLxChildren = con.prepareStatement(
                "select f.id, f.name, p.path from folder f, folder p "+
                    "where f.parentid = p.id and p.path <> '/' "+
                    "and p.path is not NULL and f.path is NULL");
            // update Lx nodes with their path (/<name>)
            updateLxChildren = con.prepareStatement(
                "update folder set path=? where id=?");
            
            // this loop allows us to run this part of the upgrade process as
            // long as is necessary based on the depth of the hierarchy, and
            // we use the do/while construct to ensure it's run at least once
            int folderNumUpdated = 0;
            do {
                log.debug("Doing pass over Lx children for folders");
                
                // reset count for each iteration of outer loop
                folderNumUpdated = 0;
                
                ResultSet LxSet = selectLxChildren.executeQuery();
                while (LxSet.next()) {
                    String id = LxSet.getString(1);
                    String name = LxSet.getString(2);
                    String parentPath = LxSet.getString(3);
                    updateLxChildren.clearParameters();
                    updateLxChildren.setString( 1, parentPath+"/"+name);
                    updateLxChildren.setString( 2, id);
                    updateLxChildren.executeUpdate();
                    
                    // count the updated rows
                    folderNumUpdated++;
                }
                
                log.debug("Updated "+folderNumUpdated+" Lx folder paths");
            } while(folderNumUpdated > 0);
            
            if (!con.getAutoCommit()) {
                con.commit();
            }
           
            successMessage("Done populating path columns.");
            
        } catch (SQLException e) {
            log.error("Problem upgrading database to version 320", e);
            throw new StartupException("Problem upgrading database to version 320", e);
        }
        
        
        // 4.0 changes the planet data model a bit, so we need to clean that up
        try {
            successMessage("Merging planet groups 'all' and 'external'");
            
            // Move all subscriptions in the planet group 'external' to group 'all'
            
            String allGroupId = null;
            PreparedStatement selectAllGroupId = con.prepareStatement(
                "select id from rag_group where handle = 'all'");
            ResultSet rs = selectAllGroupId.executeQuery();
            if (rs.next()) {
                allGroupId = rs.getString(1);
            }
            
            String externalGroupId = null;
            PreparedStatement selectExternalGroupId = con.prepareStatement(
                "select id from rag_group where handle = 'external'");            
            rs = selectExternalGroupId.executeQuery();
            if (rs.next()) {
                externalGroupId = rs.getString(1);
            }
            
            // we only need to merge if both of those groups already existed
            if(allGroupId != null && externalGroupId != null) {
                PreparedStatement updateGroupSubs = con.prepareStatement(
                        "update rag_group_subscription set group_id = ? where group_id = ?");
                updateGroupSubs.clearParameters();
                updateGroupSubs.setString( 1, allGroupId);
                updateGroupSubs.setString( 2, externalGroupId);
                updateGroupSubs.executeUpdate();
                
                // we no longer need the group 'external'
                PreparedStatement deleteExternalGroup = con.prepareStatement(
                        "delete from rag_group where handle = 'external'");
                deleteExternalGroup.executeUpdate();
                
            // if we only have group 'external' then just rename it to 'all'
            } else if(allGroupId == null && externalGroupId != null) {
                
                // rename 'external' to 'all'
                PreparedStatement renameExternalGroup = con.prepareStatement(
                        "update rag_group set handle = 'all' where handle = 'external'");
                renameExternalGroup.executeUpdate();
            }
            
            if (!con.getAutoCommit()) {
                con.commit();
            }
           
            successMessage("Planet group 'external' merged into group 'all'.");
            
        } catch (Exception e) {
            errorMessage("Problem upgrading database to version 400", e);
            throw new StartupException("Problem upgrading database to version 400", e);
        }
        
        
        // update local planet subscriptions to use new local feed format
        try {
            successMessage("Upgrading local planet subscription feeds to new feed url format");
            
            // need to start by looking up absolute site url
            PreparedStatement selectAbsUrl = 
                    con.prepareStatement("select value from roller_properties where name = 'site.absoluteurl'");
            String absUrl = null;
            ResultSet rs = selectAbsUrl.executeQuery();
            if(rs.next()) {
                absUrl = rs.getString(1);
            }
            
            if(absUrl != null && absUrl.length() > 0) {
                PreparedStatement selectSubs = 
                        con.prepareStatement("select id,feed_url,author from rag_subscription");
            
            PreparedStatement updateSubUrl = 
                    con.prepareStatement("update rag_subscription set last_updated=last_updated, feed_url = ? where id = ?");
            
            ResultSet rset = selectSubs.executeQuery();
            while (rset.next()) {
                String id = rset.getString(1);
                String feed_url = rset.getString(2);
                String handle = rset.getString(3);
                
                // only work on local feed urls
                if (feed_url.startsWith(absUrl)) {
                    // update feed_url to 'weblogger:<handle>'
                    updateSubUrl.clearParameters();
                    updateSubUrl.setString( 1, "weblogger:"+handle);
                    updateSubUrl.setString( 2, id);
                    updateSubUrl.executeUpdate();
                }
            }
            }
            
            if (!con.getAutoCommit()) {
                con.commit();
            }
           
            successMessage("Comments successfully updated to use new comment plugins.");
            
        } catch (Exception e) {
            errorMessage("Problem upgrading database to version 400", e);
            throw new StartupException("Problem upgrading database to version 400", e);
        }
        
        
        // upgrade comments to use new plugin mechanism
        try {
            successMessage("Upgrading existing comments with content-type & plugins");
            
            // look in db and see if comment autoformatting is enabled
            boolean autoformatEnabled = false;
            String autoformat = null;
            PreparedStatement selectIsAutoformtEnabled = con.prepareStatement(
                "select value from roller_properties where name = 'users.comments.autoformat'");
            ResultSet rs = selectIsAutoformtEnabled.executeQuery();
            if (rs.next()) {
                autoformat = rs.getString(1);
                if(autoformat != null && "true".equals(autoformat)) {
                    autoformatEnabled = true;
                }
            }
            
            // look in db and see if comment html escaping is enabled
            boolean htmlEnabled = false;
            String escapehtml = null;
            PreparedStatement selectIsEscapehtmlEnabled = con.prepareStatement(
                "select value from roller_properties where name = 'users.comments.escapehtml'");
            ResultSet rs1 = selectIsEscapehtmlEnabled.executeQuery();
            if (rs1.next()) {
                escapehtml = rs1.getString(1);
                // NOTE: we allow html only when html escaping is OFF
                if(escapehtml != null && !"true".equals(escapehtml)) {
                    htmlEnabled = true;
                }
            }
            
            // first lets set the new 'users.comments.htmlenabled' property
            PreparedStatement addCommentHtmlProp = con.prepareStatement("insert into roller_properties(name,value) values(?,?)");
            addCommentHtmlProp.clearParameters();
            addCommentHtmlProp.setString(1, "users.comments.htmlenabled");
            if(htmlEnabled) {
                addCommentHtmlProp.setString(2, "true");
            } else {
                addCommentHtmlProp.setString(2, "false");
            }
            addCommentHtmlProp.executeUpdate();
            
            // determine content-type for existing comments
            String contentType = "text/plain";
            if(htmlEnabled) {
                contentType = "text/html";
            }
            
            // determine plugins for existing comments
            String plugins = "";
            if(htmlEnabled && autoformatEnabled) {
                plugins = "HTMLSubset,AutoFormat";
            } else if(htmlEnabled) {
                plugins = "HTMLSubset";
            } else if(autoformatEnabled) {
                plugins = "AutoFormat";
            }
            
            // set new comment plugins configuration property 'users.comments.plugins'
            PreparedStatement addCommentPluginsProp = 
                    con.prepareStatement("insert into roller_properties(name,value) values(?,?)");
            addCommentPluginsProp.clearParameters();
            addCommentPluginsProp.setString(1, "users.comments.plugins");
            addCommentPluginsProp.setString(2, plugins);
            addCommentPluginsProp.executeUpdate();
            
            // set content-type for all existing comments
            PreparedStatement updateCommentsContentType = 
                    con.prepareStatement("update roller_comment set posttime=posttime, contenttype = ?");
            updateCommentsContentType.clearParameters();
            updateCommentsContentType.setString(1, contentType);
            updateCommentsContentType.executeUpdate();

            // set plugins for all existing comments
            PreparedStatement updateCommentsPlugins = 
                    con.prepareStatement("update roller_comment set posttime=posttime, plugins = ?");
            updateCommentsPlugins.clearParameters();
            updateCommentsPlugins.setString(1, plugins);
            updateCommentsPlugins.executeUpdate();
            
            if (!con.getAutoCommit()) {
                con.commit();
            }
           
            successMessage("Comments successfully updated to use new comment plugins.");
            
        } catch (Exception e) {
            errorMessage("Problem upgrading database to version 400", e);
            throw new StartupException("Problem upgrading database to version 400", e);
        }
        
        // finally, upgrade db version string to 400
        updateDatabaseVersion(con, 400);
    }
    
    
    /**
     * Upgrade database to Roller 5.0
     */
    private void upgradeTo500(Connection con, boolean runScripts) throws StartupException {
        
        // first we need to run upgrade scripts 
        SQLScriptRunner runner = null;
        try {    
            if (runScripts) {
                String handle = getDatabaseHandle(con);
                String scriptPath = handle + "/400-to-500-migration.sql";
                successMessage("Running database upgrade script: "+scriptPath);                
                runner = new SQLScriptRunner(scripts.getDatabaseScript(scriptPath));
                runner.runScript(con, true);
                messages.addAll(runner.getMessages());
            }
        } catch(Exception ex) {
            log.error("ERROR running 500 database upgrade script", ex);
            if (runner != null) {
                messages.addAll(runner.getMessages());
            }
            
            errorMessage("Problem upgrading database to version 500", ex);
            throw new StartupException("Problem upgrading database to version 500", ex);
        }        
    }

    /**
     * Upgrade database to Roller 5.1
     */
	private void upgradeTo510(Connection con, boolean runScripts) throws StartupException {
        
        // first we need to run upgrade scripts 
        SQLScriptRunner runner = null;
        try {    
            if (runScripts) {
                String handle = getDatabaseHandle(con);
                String scriptPath = handle + "/500-to-510-migration.sql";
                successMessage("Running database upgrade script: "+scriptPath);                
                runner = new SQLScriptRunner(scripts.getDatabaseScript(scriptPath));
                runner.runScript(con, true);
                messages.addAll(runner.getMessages());
            }
        } catch(Exception ex) {
            log.error("ERROR running 510 database upgrade script", ex);
            if (runner != null) {
                messages.addAll(runner.getMessages());
            }
            
            errorMessage("Problem upgrading database to version 510", ex);
            throw new StartupException("Problem upgrading database to version 510", ex);
        }        
	}

    /**
     * Upgrade database to Roller 5.2
     */
    private void upgradeTo520(Connection con, boolean runScripts) throws StartupException {

        // first we need to run upgrade scripts
        SQLScriptRunner runner = null;
        try {
            if (runScripts) {
                String handle = getDatabaseHandle(con);
                String scriptPath = handle + "/510-to-520-migration.sql";
                successMessage("Running database upgrade script: "+scriptPath);
                runner = new SQLScriptRunner(scripts.getDatabaseScript(scriptPath));
                runner.runScript(con, true);
                messages.addAll(runner.getMessages());
            }
        } catch(Exception ex) {
            log.error("ERROR running 520 database upgrade script", ex);
            if (runner != null) {
                messages.addAll(runner.getMessages());
            }

            errorMessage("Problem upgrading database to version 520", ex);
            throw new StartupException("Problem upgrading database to version 520", ex);
        }
    }

    /**
     * Use database product name to get the database script directory name.
     */
    public String getDatabaseHandle(Connection con) throws SQLException {
        
        String productName = con.getMetaData().getDatabaseProductName();
        String handle = "mysql";
        if (       productName.toLowerCase().contains("mysql")) {
            handle =  "mysql";
        } else if (productName.toLowerCase().contains("derby")) {
            handle =  "derby";
        } else if (productName.toLowerCase().contains("hsql")) {
            handle =  "hsqldb";
        } else if (productName.toLowerCase().contains("postgres")) {
            handle =  "postgresql";
        } else if (productName.toLowerCase().contains("oracle")) {
            handle =  "oracle";
        } else if (productName.toLowerCase().contains("microsoft")) {
            handle =  "mssql";
        } else if (productName.toLowerCase().contains("db2")) {
            handle =  "db2";
        }
        
        return handle;
    }

    
    /**
     * Return true if named table exists in database.
     */
    private boolean tableExists(Connection con, String tableName) throws SQLException {
        ResultSet rs = con.getMetaData().getTables(null, null, "%", null);
        while (rs.next()) {
            if (tableName.equalsIgnoreCase(rs.getString("TABLE_NAME").toLowerCase())) {
                return true;
            }
        }
        return false;
    }
    
    
    private int getDatabaseVersion() throws StartupException {
        int dbversion = -1;
        
        // get the current db version
        Connection con = null;
        try {
            con = db.getConnection();
            Statement stmt = con.createStatement();
            
            // just check in the roller_properties table
            ResultSet rs = stmt.executeQuery(
                    "select value from roller_properties where name = '"+DBVERSION_PROP+"'");
            
            if(rs.next()) {
                dbversion = Integer.parseInt(rs.getString(1));
                
            } else {
                // tough to know if this is an upgrade with no db version :/
                // however, if roller_properties is not empty then we at least
                // we have someone upgrading from 1.2.x
                rs = stmt.executeQuery("select count(*) from roller_properties");
                if (rs.next() && rs.getInt(1) > 0) {
                    dbversion = 120;
                }
            }
            
        } catch(Exception e) {
            // that's strange ... hopefully we didn't need to upgrade
            log.error("Couldn't lookup current database version", e);           
        } finally {
            try {
                if (con != null) {
                    con.close();
                }
            } catch (Exception ignored) {}
        }       
        return dbversion;
    }
    
    
    private int parseVersionString(String vstring) {        
        int myversion = 0;
        
        // NOTE: this assumes a maximum of 3 digits for the version number
        // so if we get to 10.0 then we'll need to upgrade this
        
        // strip out non-digits
        vstring = vstring.replaceAll("\\Q.\\E", "");
        vstring = vstring.replaceAll("\\D", "");
        if(vstring.length() > 3) {
            vstring = vstring.substring(0, 3);
        }
        
        // parse to an int
        try {
            int parsed = Integer.parseInt(vstring);            
            if(parsed < 100) {
                myversion = parsed * 10;
            } else {
                myversion = parsed;
            }
        } catch(Exception e) {}
        
        return myversion;
    }
    

    /**
     * Insert a new database.version property.
     * This should only be called once for new installations
     */
    private void setDatabaseVersion(Connection con, String version) 
            throws StartupException {
        setDatabaseVersion(con, parseVersionString(version));
    }

    /**
     * Insert a new database.version property.
     * This should only be called once for new installations
     */
    private void setDatabaseVersion(Connection con, int version)
            throws StartupException {
        
        try {
            Statement stmt = con.createStatement();
            stmt.executeUpdate("insert into roller_properties "+
                    "values('"+DBVERSION_PROP+"', '"+version+"')");
            
            log.debug("Set database verstion to "+version);
        } catch(SQLException se) {
            throw new StartupException("Error setting database version.", se);
        }
    }
    
    
    /**
     * Update the existing database.version property
     */
    private void updateDatabaseVersion(Connection con, int version)
            throws StartupException {
        
        try {
            Statement stmt = con.createStatement();
            stmt.executeUpdate("update roller_properties "+
                    "set value = '"+version+"'"+
                    "where name = '"+DBVERSION_PROP+"'");
            
            log.debug("Updated database verstion to "+version);
        } catch(SQLException se) {
            throw new StartupException("Error setting database version.", se);
        } 
    }

}