A statement like this
update weblogcategory,weblogcategoryassoc
set weblogcategory.parentid = weblogcategoryassoc.ancestorid
where weblogcategory.id = weblogcategoryassoc.categoryid
and weblogcategoryassoc.relation = 'PARENT';
is not a multi-table update. It is actually updating only weblogcategory
based on a join. It's the join syntax in the update that is non-standard. I
think it can be rephrased using a nested select in a way that will work for
our "supported" dbs.
Here's a shot with what I think is standard SQL.
I only tried on mySQL, but I think it should work for Derby and
Oracle. You will want to test this of course.
update weblogcategory as wc set wc.parentid =
(select wca.ancestorid from weblogcategoryassoc as wca
where wc.id = wca.categoryid and wca.relation='PARENT')
Note that wc.id is "bound" at the scope of the row being updated, and I
think this is what you want.
Dave's approach should work too, but it may take a while longer on larger
sites. If it's tolerable, we can just go with that; it may not be worth the
effort of testing this approach.
--a.
----- Original Message -----
From: "Allen Gilliland" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Friday, November 17, 2006 9:14 AM
Subject: Re: svn commit: r475368 [1/2] - in /incubator/roller/trunk:
metadata/database/ metadata/database/hibernate/
src/org/apache/roller/business/ src/org/apache/roller/business/hibernate/
src/org/apache/roller/pojos/ src/org/apache/roller/ui/authoring/stru
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
