You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@roller.apache.org by Dave <sn...@gmail.com> on 2006/11/17 14:22:20 UTC

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

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

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

Posted by Anil Gangolli <an...@busybuddha.org>.
I had tried this on MySQL before posting; I just haven't tried it on Derby 
or Oracle.  The subselect does not actually select from the table being 
updated, only the assoc table.  It would if you did it as a join, but that's 
not required here.



----- Original Message ----- 
From: "Allen Gilliland" <Al...@Sun.COM>
To: <ro...@incubator.apache.org>
Sent: Saturday, November 18, 2006 8:20 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


>
>
> 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" 
>> <al...@sun.com>
>> To: <ro...@incubator.apache.org>
>> 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
>>>
>>
> 


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

Posted by Allen Gilliland <Al...@Sun.COM>.

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" 
> <al...@sun.com>
> To: <ro...@incubator.apache.org>
> 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
>>
> 

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

Posted by Anil Gangolli <an...@busybuddha.org>.
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" <al...@sun.com>
To: <ro...@incubator.apache.org>
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
>


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

Posted by Allen Gilliland <al...@sun.com>.
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