in app/src/main/java/org/apache/roller/weblogger/business/startup/DatabaseInstaller.java [272:709]
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);
}