Anil Gangolli wrote:
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.
I'll give that a try, but I attempted something similar when I was
playing with this and I think the problem is that you can't subselect
from the same table you are updating to. At least that was the case for
mysql.
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.
I have always been operating under the assumption that users who run a
large enough site for this to really be an issue should be monitoring
the project close enough to know when these situations arise and can
then figure out how to make the upgrade faster.
-- Allen
--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
