H2 Version
Emily
This may be more of a question for geotools than geodb as ultimately
I'm hoping to use geodb in conjunction with geotools.
However, I'm wondering if there is a reason for requiring such an
"old" version of H2 (Version 1.1.118). The latest version is
1.3.158. I am by no means an H2 expert but it seems there have been a
lot of updates to h2 since this version was released:
http://www.h2database.com/html/changelog.html
One thing I noticed was that blob storage has changed. Instead of one
file for each blob they are all stored in the same database file (for
smaller blobs at least).
If there is some reason for remaining with the older version of h2 I'd
be interested in it.
Thanks,
Emily
Justin Deoliveira
ego...@refractions.net
I did a very quick test today to see if the new version would work with the
Geotools library and had some failures with the test cases so I'm going to do a
bit of investigation work and I'll follow-up with the geotools mailing list.
Emily
Emily
I've resolved the issues with geotools related to hatbox. A new
hatbox version (1.0-b9) is now available for version 1.3.x of H2. See:
http://sourceforge.net/projects/hatbox/forums/forum/770401/topic/4663405
I'm wondering if we can get a new release of geodb that relies on the
new hatbox/h2. I'll then submit a bug report to geotools with the
related patches (and updated pom files) in the hopes of getting
geotools updated to the newer version of H2. Does this sound
reasonable? If you need something else just let me know.
Thanks,
Emily
On Aug 17, 8:23 pm, ego...@refractions.net wrote:
> Thanks Justin,
>
> I did a very quick test today to see if the new version would work with the
> Geotools library and had some failures with the test cases so I'm going to do a
> bit of investigation work and I'll follow-up with the geotools mailing list.
>
> Emily
>
Justin Deoliveira
Justin Deoliveira
Emily Gouge
Justin Deoliveira
Justin Deoliveira
Emily Gouge
version. They all pass but the performance is lousy. So hold off on a
0.7 release until I've had a chance to look into why the performance is
so lousy.
Thanks,
Emily
On 22/08/2011 11:53 AM, Justin Deoliveira wrote:
> Alrighty, 0.7-RC1 artifacts are up, as well as a new tag in the git repo.
>
> On Mon, Aug 22, 2011 at 12:50 PM, Emily Gouge<ego...@refractions.net>wrote:
>
>> Works for me - thanks!
>>
>>
>> On 22/08/2011 11:22 AM, Justin Deoliveira wrote:
>>
>>> Hey Emily,
>>>
>>> Thanks for all the work on this. We can definitely put out a new geodb
>>> with
>>> the new dependencies. I will tag a 0.7-RC1 release and put some new
>>> artifacts. Then if you could give it a try and once you verify that
>>> everything is ok i will put it out as 0.7. Sound good?
>>>
>>> On Mon, Aug 22, 2011 at 10:59 AM, Emily<ego...@refractions.net> wrote:
>>>
>>> Hi again Justin,
>>>>
>>>> I've resolved the issues with geotools related to hatbox. A new
>>>> hatbox version (1.0-b9) is now available for version 1.3.x of H2. See:
>>>> http://sourceforge.net/**projects/hatbox/forums/forum/**
>>>> 770401/topic/4663405<http://sourceforge.net/projects/hatbox/forums/forum/770401/topic/4663405>
>>>>
>>>> I'm wondering if we can get a new release of geodb that relies on the
>>>> new hatbox/h2. I'll then submit a bug report to geotools with the
>>>> related patches (and updated pom files) in the hopes of getting
>>>> geotools updated to the newer version of H2. Does this sound
>>>> reasonable? If you need something else just let me know.
>>>>
>>>> Thanks,
>>>> Emily
>>>>
>>>>
>>>> On Aug 17, 8:23 pm, ego...@refractions.net wrote:
>>>>
>>>>> Thanks Justin,
>>>>>
>>>>> I did a very quick test today to see if the new version would work with
>>>>>
>>>> the
>>>>
>>>>> Geotools library and had some failures with the test cases so I'm going
>>>>>
>>>> to do a
>>>>
>>>>> bit of investigation work and I'll follow-up with the geotools mailing
>>>>>
>>>> list.
>>>>
>>>>>
>>>>> Emily
>>>>>
>>>>> Quoting Justin Deoliveira<jdeol...@opengeo.**org<jdeol...@opengeo.org>
>>>>>> :
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> Hi Emily,
>>>>>>
>>>>>
>>>>> Yeah, there definitely is no reason for requiring that version, and it
>>>>>> should certainly work fine with newer versions. If anything it is
>>>>>>
>>>>> because
>>>>
>>>>> there are a lot of components in both geotools and geoserver that have
>>>>>>
>>>>> an h2
>>>>
>>>>> dependency and they both use an older version. But it should be easy
>>>>>>
>>>>> enough
>>>>
>>>>> to upgrade the version on the trunk of both geotools and geoserver. Can
>>>>>>
>>>>> you
>>>>
>>>>> open a jira request to do so or bring it up as a topic on the mailing
>>>>>>
>>>>> lists.
>>>>
>>>>> Thanks Emily.
>>>>>>
>>>>>
>>>>> -Justin
>>>>>>
>>>>>
>>>>> On Fri, Aug 12, 2011 at 10:05 AM, Emily<ego...@refractions.net>
>>>>>>
>>>>> wrote:
>>>>
>>>>>
>>>>> Hi Justin,
>>>>>>>
>>>>>>
>>>>> This may be more of a question for geotools than geodb as ultimately
>>>>>>> I'm hoping to use geodb in conjunction with geotools.
>>>>>>> However, I'm wondering if there is a reason for requiring such an
>>>>>>> "old" version of H2 (Version 1.1.118). The latest version is
>>>>>>> 1.3.158. I am by no means an H2 expert but it seems there have been
>>>>>>>
>>>>>> a
>>>>
>>>>> lot of updates to h2 since this version was released:
>>>>>>> http://www.h2database.com/**html/changelog.html<http://www.h2database.com/html/changelog.html>
Justin Deoliveira
Emily Gouge
I came across this error in the h2 logs:
org.h2.jdbc.JdbcSQLException: The public static Java method was not
found: "Version (geodb.GeoDB)"; SQL statement:
CREATE ALIAS Version FOR "geodb.GeoDB.Version" ...
The geodb.sql defines:
CREATE ALIAS Version FOR "geodb.GeoDB.Version"
However there is no Version method. I saw a GeoToolsVersion method,
however it seems to return an arbitrary value of 0.1. I don't believe
this error affects using the database or performance, but it might be
nice to add a Version method (or remove the ALIAS).
Emily
Justin Deoliveira
Justin Deoliveira
Emily Gouge
I've managed to track down the major performance causing issue with h2.
Geotools generates a select statement with an IN clause which in the
older version of h2 took < 1 sec and in the new version takes about 50
sec. I'm following up with the H2 users group and will let you know
when/if I find an appropriate resolution for the problem.
Emily
On 24/08/2011 7:26 AM, Justin Deoliveira wrote:
> Will do, thanks Emily.
>
> On Tue, Aug 23, 2011 at 10:00 AM, Emily Gouge<ego...@refractions.net>wrote:
>
>> I ran the set of geotools tests and a few of my own against this new
>> version. They all pass but the performance is lousy. So hold off on a 0.7
>> release until I've had a chance to look into why the performance is so
>> lousy.
>>
>> Thanks,
>> Emily
>>
>>
>>
>>
>>
>> On 22/08/2011 11:53 AM, Justin Deoliveira wrote:
>>
>>> Alrighty, 0.7-RC1 artifacts are up, as well as a new tag in the git repo.
>>>
>>> On Mon, Aug 22, 2011 at 12:50 PM, Emily Gouge<ego...@refractions.net>**
>>> wrote:
>>>
>>> Works for me - thanks!
>>>>
>>>>
>>>> On 22/08/2011 11:22 AM, Justin Deoliveira wrote:
>>>>
>>>> Hey Emily,
>>>>>
>>>>> Thanks for all the work on this. We can definitely put out a new geodb
>>>>> with
>>>>> the new dependencies. I will tag a 0.7-RC1 release and put some new
>>>>> artifacts. Then if you could give it a try and once you verify that
>>>>> everything is ok i will put it out as 0.7. Sound good?
>>>>>
>>>>> On Mon, Aug 22, 2011 at 10:59 AM, Emily<ego...@refractions.net>
>>>>> wrote:
>>>>>
>>>>> Hi again Justin,
>>>>>
>>>>>>
>>>>>> I've resolved the issues with geotools related to hatbox. A new
>>>>>> hatbox version (1.0-b9) is now available for version 1.3.x of H2. See:
>>>>>> http://sourceforge.net/****projects/hatbox/forums/forum/****<http://sourceforge.net/**projects/hatbox/forums/forum/**>
>>>>>> 770401/topic/4663405<http://**sourceforge.net/projects/**
>>>>>> hatbox/forums/forum/770401/**topic/4663405<http://sourceforge.net/projects/hatbox/forums/forum/770401/topic/4663405>
>>>>>>>
>>>>>>
>>>>>>
>>>>>> I'm wondering if we can get a new release of geodb that relies on the
>>>>>> new hatbox/h2. I'll then submit a bug report to geotools with the
>>>>>> related patches (and updated pom files) in the hopes of getting
>>>>>> geotools updated to the newer version of H2. Does this sound
>>>>>> reasonable? If you need something else just let me know.
>>>>>>
>>>>>> Thanks,
>>>>>> Emily
>>>>>>
>>>>>>
>>>>>> On Aug 17, 8:23 pm, ego...@refractions.net wrote:
>>>>>>
>>>>>> Thanks Justin,
>>>>>>>
>>>>>>> I did a very quick test today to see if the new version would work
>>>>>>> with
>>>>>>>
>>>>>>> the
>>>>>>
>>>>>> Geotools library and had some failures with the test cases so I'm
>>>>>>> going
>>>>>>>
>>>>>>> to do a
>>>>>>
>>>>>> bit of investigation work and I'll follow-up with the geotools mailing
>>>>>>>
>>>>>>> list.
>>>>>>
>>>>>>
>>>>>>> Emily
>>>>>>>
>>>>>>> Quoting Justin Deoliveira<jdeol...@opengeo.****org<
>>>>>>>> http://www.h2database.com/****html/changelog.html<http://www.h2database.com/**html/changelog.html>
>>>>>>>>> <http://**www.h2database.com/html/**changelog.html<http://www.h2database.com/html/changelog.html>
Justin Deoliveira
Justin Deoliveira
Emily Gouge
I haven't gotten very far finding a solution to the H2 performance
issue; however the issue is not related to either hatbox or geodb. The
performance issues I have arise within geotools because of the way
geotools builds sql queries for H2. Geotools generates a query similar to:
SELECT "ID","geom" as "geom" FROM "TESTDATA"
WHERE "ID" IN (
SELECT CAST (HATBOX_JOIN_ID AS INT)
FROM
HATBOX_MBR_INTERSECTS_ENV('PUBLIC', 'TESTDATA', -139.17094658129298,
-113.94051433313055, 44.17730198523503, 64.12298153276885)
)
This query (on my sample data) took about 60 seconds. However if this
query is re-written as a table join (instead of in statement) it takes <
1sec.
SELECT a."ID",a."geom" as "geom" FROM "TESTDATA" a
INNER JOIN
(SELECT CAST (HATBOX_JOIN_ID AS INT) as id
FROM
HATBOX_MBR_INTERSECTS_ENV('PUBLIC', 'TESTDATA', -139.17094658129298,
-113.94051433313055, 44.17730198523503, 64.12298153276885)
) b ON a."ID" = b.id
I think this is an H2 bug and at the moment the best thing to do would
be to work around it by re-writing the query as a join statement.
Having said that I'm not sure how much work implementing a work-around
would be, if it can be justified, or if it is even a very good idea. Do
you have any thoughts on this?
Emily
On 30/08/2011 10:29 AM, Justin Deoliveira wrote:
> Thanks for keeping us in the loop Emily. I actually think i remember reading
> about that issue on the h2 forum a while back. I look forward to hearing the
> findings.
>
> On Tue, Aug 30, 2011 at 9:49 AM, Emily Gouge<ego...@refractions.net> wrote:
>
>> An update on the performance issues I was having:
>>
>> I've managed to track down the major performance causing issue with h2.
>> Geotools generates a select statement with an IN clause which in the older
>> version of h2 took< 1 sec and in the new version takes about 50 sec. I'm
>> following up with the H2 users group and will let you know when/if I find an
>> appropriate resolution for the problem.
>>
>> Emily
>>
>>
>> On 24/08/2011 7:26 AM, Justin Deoliveira wrote:
>>
>>> Will do, thanks Emily.
>>>
>>> On Tue, Aug 23, 2011 at 10:00 AM, Emily Gouge<ego...@refractions.net>**
>>>>>>>> http://sourceforge.net/******projects/hatbox/forums/forum/******<http://sourceforge.net/****projects/hatbox/forums/forum/****>
>>>>>>>> <http://sourceforge.net/****projects/hatbox/forums/forum/****<http://sourceforge.net/**projects/hatbox/forums/forum/**>
>>>>>>>>>
>>>>>>>>
>>>>>>>> 770401/topic/4663405<http://****sourceforge.net/projects/**<http://sourceforge.net/projects/**>
>>>>>>>> hatbox/forums/forum/770401/****topic/4663405<http://**
>>>>>>>> sourceforge.net/projects/**hatbox/forums/forum/770401/**
>>>>>>>> topic/4663405<http://sourceforge.net/projects/hatbox/forums/forum/770401/topic/4663405>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>> I'm wondering if we can get a new release of geodb that relies on the
>>>>>>>> new hatbox/h2. I'll then submit a bug report to geotools with the
>>>>>>>> related patches (and updated pom files) in the hopes of getting
>>>>>>>> geotools updated to the newer version of H2. Does this sound
>>>>>>>> reasonable? If you need something else just let me know.
>>>>>>>>
>>>>>>>> Thanks,
>>>>>>>> Emily
>>>>>>>>
>>>>>>>>
>>>>>>>> On Aug 17, 8:23 pm, ego...@refractions.net wrote:
>>>>>>>>
>>>>>>>> Thanks Justin,
>>>>>>>>
>>>>>>>>>
>>>>>>>>> I did a very quick test today to see if the new version would work
>>>>>>>>> with
>>>>>>>>>
>>>>>>>>> the
>>>>>>>>>
>>>>>>>>
>>>>>>>> Geotools library and had some failures with the test cases so I'm
>>>>>>>>
>>>>>>>>> going
>>>>>>>>>
>>>>>>>>> to do a
>>>>>>>>>
>>>>>>>>
>>>>>>>> bit of investigation work and I'll follow-up with the geotools
>>>>>>>> mailing
>>>>>>>>
>>>>>>>>>
>>>>>>>>> list.
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> Emily
>>>>>>>>>
>>>>>>>>> Quoting Justin Deoliveira<jdeol...@opengeo.******org<
>>>>>>>>> http://www.h2database.com/******html/changelog.html<http://www.h2database.com/****html/changelog.html>
>>>>>>>>>> <http://**www.h2database.com/**html/**changelog.html<http://www.h2database.com/**html/changelog.html>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>> <http://**www.h2database.com/**html/**changelog.html<http://www.h2database.com/html/**changelog.html>
Justin Deoliveira
(SELECT CAST (HATBOX_JOIN_ID AS INT) as id
FROM
HATBOX_MBR_INTERSECTS_ENV('PUBLIC', 'TESTDATA', -139.17094658129298, -113.94051433313055, 44.17730198523503, 64.12298153276885)
Justin Deoliveira
Emily Gouge
> Thanks again for your continued diligence on this. Agreed this is an h2 bug,
> did you get any word back about when/if it might be fixed?
Not yet. I will let you know if something changes.
> I also agree that a good workaround not would be to rewrite the query. The
> jdbc datatstores were modified recently to handle joins, this is not a join
> at the query level. Would there be any way to rewrite the join query so that
> it did not need aliases? At least not on the main table being query. What I
> am thinking is something like:
Aliases are not required.
> SELECT "ID", "geom" as "geom" FROM "TESTDATA"
> INNER JOIN
> (SELECT CAST (HATBOX_JOIN_ID AS INT) as id
> FROM
> HATBOX_MBR_INTERSECTS_ENV('**PUBLIC', 'TESTDATA', -139.17094658129298,
> -113.94051433313055, 44.17730198523503, 64.12298153276885)
> ) b ON "TESTDATA"."ID" = b.id
>
> If that query works then it should be simpler, but still tricky because
> currently the index predicate is specified in the WHERE clause... here it
> has to be moved to the FROM clause and I don't think currently there are
> hooks for that. One could be added pretty easily though.
I think we should hold off for now. As was pointed out to me, other
projects that rely on geotools also make use of h2 in their projects.
If we fix the issue in the geotools, these other projects might have
similar issues.
I don't have a super strong use-case for upgrading so I'd hate to "open
a can of worms" if not absolutely necessary. If my requirements change,
somebody else has a strong use-case, or the bug is fixed then we can
re-visit this issue.
Thanks for your help & input,
Emily
> On Thu, Sep 8, 2011 at 10:22 AM, Emily Gouge<ego...@refractions.net> wrote:
>
>> Hi Justin,
>>
>> I haven't gotten very far finding a solution to the H2 performance issue;
>> however the issue is not related to either hatbox or geodb. The performance
>> issues I have arise within geotools because of the way geotools builds sql
>> queries for H2. Geotools generates a query similar to:
>>
>> SELECT "ID","geom" as "geom" FROM "TESTDATA"
>> WHERE "ID" IN (
>> SELECT CAST (HATBOX_JOIN_ID AS INT)
>> FROM
>> HATBOX_MBR_INTERSECTS_ENV('**PUBLIC', 'TESTDATA', -139.17094658129298,
>> -113.94051433313055, 44.17730198523503, 64.12298153276885)
>> )
>>
>> This query (on my sample data) took about 60 seconds. However if this query
>> is re-written as a table join (instead of in statement) it takes< 1sec.
>>
>> SELECT a."ID",a."geom" as "geom" FROM "TESTDATA" a
>> INNER JOIN
>> (SELECT CAST (HATBOX_JOIN_ID AS INT) as id
>> FROM
>> HATBOX_MBR_INTERSECTS_ENV('**PUBLIC', 'TESTDATA', -139.17094658129298,
>>>>>>>>>> http://sourceforge.net/********projects/hatbox/forums/forum/***
>>>>>>>>>> *****<http://sourceforge.net/******projects/hatbox/forums/forum/******>
>>>>>>>>>> <http://sourceforge.net/******projects/hatbox/forums/**forum/****<http://sourceforge.net/****projects/hatbox/forums/forum/****>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> <http://sourceforge.net/******projects/hatbox/forums/forum/******<http://sourceforge.net/****projects/hatbox/forums/forum/****>
>>>>>>>>>> <http://sourceforge.net/****projects/hatbox/forums/forum/****<http://sourceforge.net/**projects/hatbox/forums/forum/**>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>> 770401/topic/4663405<http://******sourceforge.net/projects/**<**
>>>>>>>>>> http://sourceforge.net/**projects/**<http://sourceforge.net/projects/**>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> hatbox/forums/forum/770401/******topic/4663405<http://**
>>>>>>>>>> sourceforge.net/projects/****hatbox/forums/forum/770401/**<http://sourceforge.net/projects/**hatbox/forums/forum/770401/**>
>>>>>>>>>> topic/4663405<http://**sourceforge.net/projects/**
>>>>>>>>>> hatbox/forums/forum/770401/**topic/4663405<http://sourceforge.net/projects/hatbox/forums/forum/770401/topic/4663405>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>> I'm wondering if we can get a new release of geodb that relies on
>>>>>>>>>> the
>>>>>>>>>> new hatbox/h2. I'll then submit a bug report to geotools with the
>>>>>>>>>> related patches (and updated pom files) in the hopes of getting
>>>>>>>>>> geotools updated to the newer version of H2. Does this sound
>>>>>>>>>> reasonable? If you need something else just let me know.
>>>>>>>>>>
>>>>>>>>>> Thanks,
>>>>>>>>>> Emily
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> On Aug 17, 8:23 pm, ego...@refractions.net wrote:
>>>>>>>>>>
>>>>>>>>>> Thanks Justin,
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>> I did a very quick test today to see if the new version would work
>>>>>>>>>>> with
>>>>>>>>>>>
>>>>>>>>>>> the
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>> Geotools library and had some failures with the test cases so I'm
>>>>>>>>>>
>>>>>>>>>> going
>>>>>>>>>>>
>>>>>>>>>>> to do a
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>> bit of investigation work and I'll follow-up with the geotools
>>>>>>>>>> mailing
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>> list.
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> Emily
>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> Quoting Justin Deoliveira<jdeol...@opengeo.********org<
>>>>>>>>>>> http://www.h2database.com/********html/changelog.html<http://www.h2database.com/******html/changelog.html>
>>>>>>>>>>> <http://**www.h2database.com/****html/**changelog.html<http://www.h2database.com/****html/changelog.html>
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>> <http://**www.h2database.com/****html/**changelog.html<http://www.h2database.com/**html/**changelog.html>
>>>>>>>>>>>> <http://**www.h2database.com/**html/**changelog.html<http://www.h2database.com/**html/changelog.html>
>>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>> <http://**www.h2database.com/****html/**changelog.html<http://www.h2database.com/**html/**changelog.html>
>>>>>>>>>>>>> <http://**www.h2database.com/html/****changelog.html<http://www.h2database.com/html/**changelog.html>
Justin Deoliveira
Justin Deoliveira