Java tutorial
/* * 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 org.apache.roller.util.SQLScriptRunner; 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.Date; 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; import org.apache.roller.weblogger.pojos.WeblogPermission; /** * 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 if (tableExists(con, "rolleruser") && tableExists(con, "userrole")) { return false; } } catch (Throwable t) { throw new RuntimeException("Error checking for tables", t); } 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 >= 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 < 130) { upgradeTo130(con, runScripts); dbversion = 130; } if (dbversion < 200) { upgradeTo200(con, runScripts); dbversion = 200; } if (dbversion < 210) { upgradeTo210(con, runScripts); dbversion = 210; } if (dbversion < 230) { upgradeTo230(con, runScripts); dbversion = 230; } if (dbversion < 240) { upgradeTo240(con, runScripts); dbversion = 240; } if (dbversion < 300) { upgradeTo300(con, runScripts); dbversion = 300; } if (dbversion < 310) { upgradeTo310(con, runScripts); dbversion = 310; } if (dbversion < 400) { upgradeTo400(con, runScripts); dbversion = 400; } if (dbversion < 500) { upgradeTo500(con, runScripts); dbversion = 500; } if (dbversion < 510) { upgradeTo510(con, runScripts); dbversion = 510; } // 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 for Roller 1.3.0 */ private void upgradeTo130(Connection con, boolean runScripts) throws StartupException { SQLScriptRunner runner = null; try { if (runScripts) { String handle = getDatabaseHandle(con); String scriptPath = handle + "/120-to-130-migration.sql"; successMessage("Running database upgrade script: " + scriptPath); runner = new SQLScriptRunner(scripts.getDatabaseScript(scriptPath)); runner.runScript(con, true); messages.addAll(runner.getMessages()); } /* * The new theme management code is going into place and it uses * the old website.themeEditor attribute to store a users theme. * * In pre-1.3 Roller *all* websites are considered to be using a * custom theme, so we need to make sure this is properly defined * by setting the theme on all websites to custom. * * NOTE: If we don't do this then nothing would break, but some users * would be suprised that their template customizations are no longer * in effect because they are using a shared theme instead. */ successMessage("Doing upgrade to 130 ..."); successMessage("Ensuring that all website themes are set to custom"); PreparedStatement setCustomThemeStmt = con.prepareStatement("update website set editortheme = ?"); setCustomThemeStmt.setString(1, org.apache.roller.weblogger.pojos.WeblogTheme.CUSTOM); setCustomThemeStmt.executeUpdate(); if (!con.getAutoCommit()) con.commit(); successMessage("Upgrade to 130 complete."); } catch (Exception e) { log.error("ERROR running 310 database upgrade script", e); if (runner != null) messages.addAll(runner.getMessages()); errorMessage("Problem upgrading database to version 130", e); throw new StartupException("Problem upgrading database to version 130", e); } updateDatabaseVersion(con, 130); } /** * Upgrade database for Roller 2.0.0 */ private void upgradeTo200(Connection con, boolean runScripts) throws StartupException { SQLScriptRunner runner = null; try { if (runScripts) { String handle = getDatabaseHandle(con); String scriptPath = handle + "/130-to-200-migration.sql"; successMessage("Running database upgrade script: " + scriptPath); runner = new SQLScriptRunner(scripts.getDatabaseScript(scriptPath)); runner.runScript(con, true); messages.addAll(runner.getMessages()); } successMessage("Doing upgrade to 200 ..."); successMessage("Populating roller_user_permissions table"); PreparedStatement websitesQuery = con .prepareStatement("select w.id as wid, u.id as uid, u.username as uname from " + "website as w, rolleruser as u where u.id=w.userid"); PreparedStatement websiteUpdate = con.prepareStatement("update website set handle=? where id=?"); PreparedStatement entryUpdate = con.prepareStatement("update weblogentry set userid=?, status=?, " + "pubtime=pubtime, updatetime=updatetime " + "where publishentry=? and websiteid=?"); PreparedStatement permsInsert = con.prepareStatement("insert into roller_permissions " + "(id, username, actions, objectid, objecttype, pending, datecreated) " + "values (?,?,?,?,?,?,?)"); // loop through websites, each has a user java.sql.Date now = new java.sql.Date(new Date().getTime()); ResultSet websiteSet = websitesQuery.executeQuery(); while (websiteSet.next()) { String websiteid = websiteSet.getString("wid"); String userid = websiteSet.getString("uid"); String username = websiteSet.getString("uname"); successMessage("Processing website: " + username); // use website user's username as website handle websiteUpdate.clearParameters(); websiteUpdate.setString(1, username); websiteUpdate.setString(2, websiteid); websiteUpdate.executeUpdate(); // update all of pubished entries to include userid and status entryUpdate.clearParameters(); entryUpdate.setString(1, userid); entryUpdate.setString(2, "PUBLISHED"); entryUpdate.setBoolean(3, true); entryUpdate.setString(4, websiteid); entryUpdate.executeUpdate(); // update all of draft entries to include userid and status entryUpdate.clearParameters(); entryUpdate.setString(1, userid); entryUpdate.setString(2, "DRAFT"); entryUpdate.setBoolean(3, false); entryUpdate.setString(4, websiteid); entryUpdate.executeUpdate(); // add permission for user in website permsInsert.clearParameters(); permsInsert.setString(1, websiteid + "p"); permsInsert.setString(2, username); permsInsert.setString(3, WeblogPermission.ADMIN); permsInsert.setString(4, websiteid); permsInsert.setString(5, "Weblog"); permsInsert.setBoolean(6, false); permsInsert.setDate(7, now); permsInsert.setBoolean(5, false); permsInsert.executeUpdate(); } if (!con.getAutoCommit()) con.commit(); successMessage("Upgrade to 200 complete."); } catch (Exception e) { log.error("ERROR running 310 database upgrade script", e); if (runner != null) messages.addAll(runner.getMessages()); errorMessage("Problem upgrading database to version 200", e); throw new StartupException("Problem upgrading database to version 200", e); } updateDatabaseVersion(con, 200); } /** * Upgrade database for Roller 2.1.0 */ private void upgradeTo210(Connection con, boolean runScripts) throws StartupException { SQLScriptRunner runner = null; try { if (runScripts) { String handle = getDatabaseHandle(con); String scriptPath = handle + "/200-to-210-migration.sql"; successMessage("Running database upgrade script: " + scriptPath); runner = new SQLScriptRunner(scripts.getDatabaseScript(scriptPath)); runner.runScript(con, true); messages.addAll(runner.getMessages()); } /* * For Roller 2.1.0 we are going to standardize some of the * weblog templates and make them less editable. To do this * we need to do a little surgery. * * The goal for this upgrade is to ensure that ALL weblogs now have * the required "Weblog" template as their default template. */ successMessage("Doing upgrade to 210 ..."); successMessage("Ensuring that all weblogs use the 'Weblog' template as their default page"); // this query will give us all websites that have modified their // default page to link to something other than "Weblog" PreparedStatement selectUpdateWeblogs = con .prepareStatement("select website.id,template,website.handle from website,webpage " + "where webpage.id = website.defaultpageid " + "and webpage.link != 'Weblog'"); PreparedStatement selectWeblogTemplate = con .prepareStatement("select id from webpage where websiteid = ? and link = 'Weblog'"); PreparedStatement updateWeblogTemplate = con .prepareStatement("update webpage set template = ? where id = ?"); // insert a new template for a website PreparedStatement insertWeblogTemplate = con.prepareStatement("insert into webpage" + "(id, name, description, link, websiteid, template, updatetime) " + "values(?,?,?,?,?,?,?)"); // update the default page for a website PreparedStatement updateDefaultPage = con .prepareStatement("update website set defaultpageid = ? " + "where id = ?"); String description = "This template is used to render the main " + "page of your weblog."; ResultSet websiteSet = selectUpdateWeblogs.executeQuery(); Date now = new Date(); while (websiteSet.next()) { String websiteid = websiteSet.getString(1); String template = websiteSet.getString(2); String handle = websiteSet.getString(3); successMessage("Processing website: " + handle); String defaultpageid = null; // it's possible that this weblog has a "Weblog" template, but just // isn't using it as their default. if so we need to fix that. selectWeblogTemplate.clearParameters(); selectWeblogTemplate.setString(1, websiteid); ResultSet weblogPageSet = selectWeblogTemplate.executeQuery(); if (weblogPageSet.next()) { // this person already has a "Weblog" template, so update it String id = weblogPageSet.getString(1); updateWeblogTemplate.clearParameters(); updateWeblogTemplate.setString(1, template); updateWeblogTemplate.setString(2, id); updateWeblogTemplate.executeUpdate(); // make sure and adjust what default page id we want to use defaultpageid = id; } else { // no "Weblog" template, so insert a new one insertWeblogTemplate.clearParameters(); insertWeblogTemplate.setString(1, websiteid + "q"); insertWeblogTemplate.setString(2, "Weblog"); insertWeblogTemplate.setString(3, description); insertWeblogTemplate.setString(4, "Weblog"); insertWeblogTemplate.setString(5, websiteid); insertWeblogTemplate.setString(6, template); insertWeblogTemplate.setDate(7, new java.sql.Date(now.getTime())); insertWeblogTemplate.executeUpdate(); // set the new default page id defaultpageid = websiteid + "q"; } // update defaultpageid value updateDefaultPage.clearParameters(); updateDefaultPage.setString(1, defaultpageid); updateDefaultPage.setString(2, websiteid); updateDefaultPage.executeUpdate(); } if (!con.getAutoCommit()) con.commit(); successMessage("Upgrade to 210 complete."); } catch (Exception e) { log.error("ERROR running 310 database upgrade script", e); if (runner != null) messages.addAll(runner.getMessages()); log.error("Problem upgrading database to version 210", e); throw new StartupException("Problem upgrading database to version 210", e); } updateDatabaseVersion(con, 210); } /** * Upgrade database for Roller 2.3.0 */ private void upgradeTo230(Connection con, boolean runScripts) throws StartupException { SQLScriptRunner runner = null; try { if (runScripts) { String handle = getDatabaseHandle(con); String scriptPath = handle + "/210-to-230-migration.sql"; successMessage("Running database upgrade script: " + scriptPath); runner = new SQLScriptRunner(scripts.getDatabaseScript(scriptPath)); runner.runScript(con, true); messages.addAll(runner.getMessages()); } } catch (Exception e) { log.error("ERROR running 310 database upgrade script", e); if (runner != null) messages.addAll(runner.getMessages()); errorMessage("Problem upgrading database to version 230", e); throw new StartupException("Problem upgrading database to version 230", e); } updateDatabaseVersion(con, 230); } /** * Upgrade database for Roller 2.4.0 */ private void upgradeTo240(Connection con, boolean runScripts) throws StartupException { SQLScriptRunner runner = null; try { if (runScripts) { String handle = getDatabaseHandle(con); String scriptPath = handle + "/230-to-240-migration.sql"; successMessage("Running database upgrade script: " + scriptPath); runner = new SQLScriptRunner(scripts.getDatabaseScript(scriptPath)); runner.runScript(con, true); messages.addAll(runner.getMessages()); } } catch (Exception e) { log.error("ERROR running 310 database upgrade script", e); if (runner != null) messages.addAll(runner.getMessages()); errorMessage("Problem upgrading database to version 240", e); throw new StartupException("Problem upgrading database to version 240", e); } updateDatabaseVersion(con, 240); } /** * Upgrade database for Roller 3.0.0 */ private void upgradeTo300(Connection con, boolean runScripts) throws StartupException { SQLScriptRunner runner = null; try { if (runScripts) { String handle = getDatabaseHandle(con); String scriptPath = handle + "/240-to-300-migration.sql"; successMessage("Running database upgrade script: " + scriptPath); runner = new SQLScriptRunner(scripts.getDatabaseScript(scriptPath)); runner.runScript(con, true); messages.addAll(runner.getMessages()); } /* * For Roller 3.0.0 we are allowing each weblogentry to track a * locale now so that we can support multi-lingual blogs. As part * of the upgrade process we want to do 2 things .. * * 1. make sure all weblogs have a locale * 2. set the locale on all entries to the locale for the weblog */ successMessage("Doing upgrade to 300 ..."); // get system default language String locale = java.util.Locale.getDefault().getLanguage(); successMessage("Setting website locale to " + locale + " for websites with no locale"); // update all weblogs where locale is "null" PreparedStatement updateNullWeblogLocale = con .prepareStatement("update website set locale = ? where locale is NULL"); // update all weblogs where locale is empty string "" PreparedStatement updateEmptyWeblogLocale = con .prepareStatement("update website set locale = ? where locale = ''"); updateNullWeblogLocale.setString(1, locale); updateEmptyWeblogLocale.setString(1, locale); updateNullWeblogLocale.executeUpdate(); updateEmptyWeblogLocale.executeUpdate(); successMessage("Setting weblogentry locales to website locale"); // get all entries and the locale of its website PreparedStatement selectWeblogsLocale = con.prepareStatement("select weblogentry.id,website.locale " + "from weblogentry,website " + "where weblogentry.websiteid = website.id"); // set the locale for an entry PreparedStatement updateWeblogLocale = con .prepareStatement("update weblogentry set locale = ? where id = ?"); ResultSet websiteSet = selectWeblogsLocale.executeQuery(); while (websiteSet.next()) { String entryid = websiteSet.getString(1); String entrylocale = websiteSet.getString(2); // update entry locale updateWeblogLocale.clearParameters(); updateWeblogLocale.setString(1, entrylocale); updateWeblogLocale.setString(2, entryid); updateWeblogLocale.executeUpdate(); } if (!con.getAutoCommit()) con.commit(); successMessage("Upgrade to 300 complete."); } catch (Exception e) { log.error("ERROR running 310 database upgrade script", e); if (runner != null) messages.addAll(runner.getMessages()); errorMessage("Problem upgrading database to version 300", e); throw new StartupException("Problem upgrading database to version 300", e); } updateDatabaseVersion(con, 300); } /** * Upgrade database for Roller 3.1.0 */ private void upgradeTo310(Connection con, boolean runScripts) throws StartupException { SQLScriptRunner runner = null; try { if (runScripts) { String handle = getDatabaseHandle(con); String scriptPath = handle + "/300-to-310-migration.sql"; successMessage("Running database upgrade script: " + scriptPath); runner = new SQLScriptRunner(scripts.getDatabaseScript(scriptPath)); runner.runScript(con, true); messages.addAll(runner.getMessages()); } } catch (Exception e) { log.error("ERROR running 310 database upgrade script", e); if (runner != null) messages.addAll(runner.getMessages()); errorMessage("Problem upgrading database to version 310", e); throw new StartupException("Problem upgrading database to version 310", e); } updateDatabaseVersion(con, 310); } /** * Upgrade database for 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 for Roller 4.1.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); } } 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); } } /** * 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().indexOf("mysql") != -1) { handle = "mysql"; } else if (productName.toLowerCase().indexOf("derby") != -1) { handle = "derby"; } else if (productName.toLowerCase().indexOf("hsql") != -1) { handle = "hsqldb"; } else if (productName.toLowerCase().indexOf("postgres") != -1) { handle = "postgresql"; } else if (productName.toLowerCase().indexOf("oracle") != -1) { handle = "oracle"; } else if (productName.toLowerCase().indexOf("microsoft") != -1) { handle = "mssql"; } else if (productName.toLowerCase().indexOf("db2") != -1) { handle = "db2"; } return handle; } /** * Return true if named table exists in database. */ private boolean tableExists(Connection con, String tableName) throws SQLException { String[] types = { "TABLE" }; ResultSet rs = con.getMetaData().getTables(null, null, "%", null); while (rs.next()) { if (tableName.toLowerCase().equals(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()) { if (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); } } }