sweet, thanks. i was planning on doing the same thing since
unfortunately there is no sql standard for doing multi-table updates
like you said :/
what you have looks fine to me. we can put the sql in the upgrade guide
if you want, but i think the is more confusing than it's worth. i would
expect that running the code below is fast enough for most users. only
people running very large installations would really need to run the
direct sql and i figure that those people probably aren't reading the
upgrade guide anyways.
i am also looking at adding that 'path' column so that we can query for
entries/bookmarks against whole sections of the category/folder trees
much more easily. there are actually a number of places where i have
seen this would probably make a very nice performance improvement, such
in the BookmarkPlugin which currently walks the entire folder tree to
get all bookmarks on every rendering :/
-- Allen
Dave wrote:
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
