Heads up...
I'm using Derby so I needed to fix the below SQL:
+# this needs to be done in a database independent manner before releasing
+update weblogcategory,weblogcategoryassoc
+set weblogcategory.parentid = weblogcategoryassoc.ancestorid
+where weblogcategory.id = weblogcategoryassoc.categoryid
+and weblogcategoryassoc.relation = 'PARENT';
+
+# also need to set proper parentid for folders
+update folder,folderassoc
+set folder.parentid = folderassoc.ancestorid
+where folder.id = folderassoc.folderid
+and folderassoc.relation = 'PARENT';
Looks like the only databases that support multi-table updates are
MySQL, MS SQL Server and Oracle -- and they all do it differently. So
I did the work in Java in UpdateDatabase.java and committed what
worked for me. Please review. Here's the important part of the code:
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();
}
Obviously, it's going to be slower than the MySQL version you created,
so perhaps we need to document the SQL version in the install guide
too.
- Dave
