OpenJPA

[Daniel Weidele <da...@uni-konstanz.de>]

Hi folks,

I'm wondering whether there is any non-native support for MySQL spatial in OpenJPA?
It would be sufficient to have a perimeter search in Criteria API, e.g. based on lon/lan stored as decimals.

Best regards & thx for any suggestions.

Re: OpenJPA

[Kevin Sutter <kw...@gmail.com>]

Hi Daniel,
I think having this discussion in "public" is fine.  We might want to shift
it to the dev mailing list since the questions are leaning towards
extending or modifying the openjpa implementation.  I'll cross post to dev
and then we can drop the users.  Interested parties can follow along...

All that I meant is that JPQL and Criteria API are defined by the spec.
The implementation is still completely up to each provider, like OpenJPA.
But, once someone would attempt to extend the JPQL and/or Criteria API, you
have to be concerned with how the spec will continue to be supported.  Now
if you are thinking of extending these definitions just for your own usage
and maintain your own version/branch/sandbox, then no problem.  But, if you
or we are considering pulling in these extensions into the base offering,
then we need to be aware of the expectations.  That's all I was trying to
point out.

Attempting to contain some of this unique MySQL processing via stored
procedures sounds like a good approach.  This would introduce less or
minimal impact to the existing query syntax or interfaces.  At a minimum, a
white paper describing your spatial support strategy would be an excellent
write-up.

Good luck with your experimentation,
Kevin

On Mon, Apr 16, 2012 at 3:46 PM, Daniel Weidele <
daniel.weidele@uni-konstanz.de> wrote:

> Good evening, Kevin,
>
> thanks for you explanations - I will investigate Object-to-XML mapping a
> bit to gain further insights.
>
> When you say JPQL and Criteria API are specified by JPA - does it mean that
> JPA API already "ships with" lexing, tokenizer, etc.- and by that hard to
> modify?
> I'd more expect this to be a task of implemenations and by that part of
> OpenJPA. If so, we should model a generic syntax in first place. Then we
> could modify the scanning process to further be able to map to MySQL. When
> I browsed through the MySQL GIS functionalities it also seems to be "ok" to
> handle.
>
> By the way - I solved the lack of a "distance"-function in MySQL by a
> stored procedure. So now let's see how to perform it nicely from Criteria
> API [I'd rather not like to rewrite my Criteria API queries to native SQL -
> instead I'd prefer to put the efforts in extending the Criteria API - so,
> tomorrow I'll have a look at Object-to-XML]
>
> Good night!
> Daniel
>
>
> PS: I'm not sure whether to go on with this conversation in public or
> switch to private mail?
>
> 2012/4/16 Kevin Sutter <kw...@gmail.com>
>
> > Hi Daniel,
> > You're on the right "mental" track.  Of course, it's not quite as
> straight
> > forward...  Performing the Object-Relational mapping for spatial is
> > probably the easiest part of the exercise.  Pulling the data values from
> > the database, and persisting the data values back to the database is
> fairly
> > straight-forward.  You can probably accomplish most of that today with
> > minimal updates to the MySQL data dictionary (for use with OpenJPA).
> >
> > The more tricky aspect would be attempting to update the JPQL and/or
> > Criteria API for being able to query across this data.  Since both of
> these
> > are defined by the JPA specification, extending either of these is going
> to
> > take some effort.  Unless you can map the spatial types to already
> existing
> > primitive types (ie. BigDecimal, long, string, etc)...
> >
> > JPQL gets tricky because you would have to change the parser to pull out
> > the next syntax.  These new constructs would then drive the generation of
> > the SQL (which would be limited to MySQL in this case).  Lots of work.
> >
> > Extending the Criteria API may be a little more straight forward.  You
> > would have to define the new methods for extending the Criteria API,
> which
> > would drive down to creating the SQL.  OpenJPA's Criteria API meets up
> with
> > the JPQL processing so that we only generate the SQL via a single
> process.
> > This also provides us with the ability to dump out an equivalent
> > "JPQL"-like string for Criteria API generated queries.
> >
> > I would probably consider extending our current implementation rather
> than
> > just updating the current implementation.  Makes it easier to
> differentiate
> > your extensions from the mainline code.  You might want to take a look at
> > what we did for supporting Object-to-XML mapping.  Although different
> from
> > what you are attempting, it should give you an idea of how we extended
> our
> > current support and took advantage of common processing without
> > re-inventing the wheel.  I know that the WebSphere product has done
> similar
> > extensions for their JPA solution (built on top of OpenJPA).
> >
> > Good luck with your investigation!
> >
> > Kevin
> >
> > On Mon, Apr 16, 2012 at 12:40 PM, Daniel Weidele <
> > daniel.weidele@uni-konstanz.de> wrote:
> >
> > > Hello Kevin,
> > >
> > > thanks for the @Embeddable annotation. However, I reviewed MySQL status
> > and
> > > it seems their spatial extension does not yet support a "distance()"
> > > function. It looks like I have to contribute there, before contributing
> > > here - because especially the "distance" function would be something I
> > need
> > > :-(
> > >
> > > Nevertheless, as you already mentioned - to provide full-feature
> support
> > I
> > > would as well say that @Embeddable is not enough. Of course modeling
> the
> > > Geo-structures itself has to be done wisely - but my feeling is that
> the
> > > more tricky part is teaching JPQL / Criteria API the geometry
> functions.
> > > Until now I would not know where to start, as I have not that much
> > insight.
> > > I'd assume to have to provide some knowledge within a MySQL dictionary
> to
> > > OpenJPA and then translate the "user" input (either from Criteria API,
> or
> > > JPQL) - such that we have a generic language which is then translated
> > into
> > > MySQL syntax.
> > >
> > > Is this a "mental model" that fits the actual implementation mechanism,
> > > somehow? Or am I totally wrong with the assumption - if not - do you
> know
> > > some of the entry points which will be of interest?
> > >
> > > Best,
> > > Daniel
> > >
> > >
> > > 2012/4/16 Kevin Sutter <kw...@gmail.com>
> > >
> > > > Hi Daniel,
> > > > There is no direct support for the MySQL spatial extensions in
> OpenJPA.
> > > > You could maybe model the support by wrappering the constructs in an
> > > > embeddable, but that may not give you the full support you are
> looking
> > > > for.  You could always help develop this support in OpenJPA and
> > > contribute
> > > > it back to the community...  :-)
> > > >
> > > > Thanks,
> > > > Kevin
> > > >
> > > > On Mon, Apr 16, 2012 at 10:15 AM, Daniel Weidele <
> > > > daniel.weidele@uni-konstanz.de> wrote:
> > > >
> > > > > Hi folks,
> > > > >
> > > > > I'm wondering whether there is any non-native support for MySQL
> > spatial
> > > > in
> > > > > OpenJPA?
> > > > > It would be sufficient to have a perimeter search in Criteria API,
> > e.g.
> > > > > based on lon/lan stored as decimals.
> > > > >
> > > > > Best regards & thx for any suggestions.
> > > > >
> > > >
> > >
> >
>

Re: OpenJPA

[Kevin Sutter <kw...@gmail.com>]

Hi Daniel,
I think having this discussion in "public" is fine.  We might want to shift
it to the dev mailing list since the questions are leaning towards
extending or modifying the openjpa implementation.  I'll cross post to dev
and then we can drop the users.  Interested parties can follow along...

All that I meant is that JPQL and Criteria API are defined by the spec.
The implementation is still completely up to each provider, like OpenJPA.
But, once someone would attempt to extend the JPQL and/or Criteria API, you
have to be concerned with how the spec will continue to be supported.  Now
if you are thinking of extending these definitions just for your own usage
and maintain your own version/branch/sandbox, then no problem.  But, if you
or we are considering pulling in these extensions into the base offering,
then we need to be aware of the expectations.  That's all I was trying to
point out.

Attempting to contain some of this unique MySQL processing via stored
procedures sounds like a good approach.  This would introduce less or
minimal impact to the existing query syntax or interfaces.  At a minimum, a
white paper describing your spatial support strategy would be an excellent
write-up.

Good luck with your experimentation,
Kevin

On Mon, Apr 16, 2012 at 3:46 PM, Daniel Weidele <
daniel.weidele@uni-konstanz.de> wrote:

> Good evening, Kevin,
>
> thanks for you explanations - I will investigate Object-to-XML mapping a
> bit to gain further insights.
>
> When you say JPQL and Criteria API are specified by JPA - does it mean that
> JPA API already "ships with" lexing, tokenizer, etc.- and by that hard to
> modify?
> I'd more expect this to be a task of implemenations and by that part of
> OpenJPA. If so, we should model a generic syntax in first place. Then we
> could modify the scanning process to further be able to map to MySQL. When
> I browsed through the MySQL GIS functionalities it also seems to be "ok" to
> handle.
>
> By the way - I solved the lack of a "distance"-function in MySQL by a
> stored procedure. So now let's see how to perform it nicely from Criteria
> API [I'd rather not like to rewrite my Criteria API queries to native SQL -
> instead I'd prefer to put the efforts in extending the Criteria API - so,
> tomorrow I'll have a look at Object-to-XML]
>
> Good night!
> Daniel
>
>
> PS: I'm not sure whether to go on with this conversation in public or
> switch to private mail?
>
> 2012/4/16 Kevin Sutter <kw...@gmail.com>
>
> > Hi Daniel,
> > You're on the right "mental" track.  Of course, it's not quite as
> straight
> > forward...  Performing the Object-Relational mapping for spatial is
> > probably the easiest part of the exercise.  Pulling the data values from
> > the database, and persisting the data values back to the database is
> fairly
> > straight-forward.  You can probably accomplish most of that today with
> > minimal updates to the MySQL data dictionary (for use with OpenJPA).
> >
> > The more tricky aspect would be attempting to update the JPQL and/or
> > Criteria API for being able to query across this data.  Since both of
> these
> > are defined by the JPA specification, extending either of these is going
> to
> > take some effort.  Unless you can map the spatial types to already
> existing
> > primitive types (ie. BigDecimal, long, string, etc)...
> >
> > JPQL gets tricky because you would have to change the parser to pull out
> > the next syntax.  These new constructs would then drive the generation of
> > the SQL (which would be limited to MySQL in this case).  Lots of work.
> >
> > Extending the Criteria API may be a little more straight forward.  You
> > would have to define the new methods for extending the Criteria API,
> which
> > would drive down to creating the SQL.  OpenJPA's Criteria API meets up
> with
> > the JPQL processing so that we only generate the SQL via a single
> process.
> > This also provides us with the ability to dump out an equivalent
> > "JPQL"-like string for Criteria API generated queries.
> >
> > I would probably consider extending our current implementation rather
> than
> > just updating the current implementation.  Makes it easier to
> differentiate
> > your extensions from the mainline code.  You might want to take a look at
> > what we did for supporting Object-to-XML mapping.  Although different
> from
> > what you are attempting, it should give you an idea of how we extended
> our
> > current support and took advantage of common processing without
> > re-inventing the wheel.  I know that the WebSphere product has done
> similar
> > extensions for their JPA solution (built on top of OpenJPA).
> >
> > Good luck with your investigation!
> >
> > Kevin
> >
> > On Mon, Apr 16, 2012 at 12:40 PM, Daniel Weidele <
> > daniel.weidele@uni-konstanz.de> wrote:
> >
> > > Hello Kevin,
> > >
> > > thanks for the @Embeddable annotation. However, I reviewed MySQL status
> > and
> > > it seems their spatial extension does not yet support a "distance()"
> > > function. It looks like I have to contribute there, before contributing
> > > here - because especially the "distance" function would be something I
> > need
> > > :-(
> > >
> > > Nevertheless, as you already mentioned - to provide full-feature
> support
> > I
> > > would as well say that @Embeddable is not enough. Of course modeling
> the
> > > Geo-structures itself has to be done wisely - but my feeling is that
> the
> > > more tricky part is teaching JPQL / Criteria API the geometry
> functions.
> > > Until now I would not know where to start, as I have not that much
> > insight.
> > > I'd assume to have to provide some knowledge within a MySQL dictionary
> to
> > > OpenJPA and then translate the "user" input (either from Criteria API,
> or
> > > JPQL) - such that we have a generic language which is then translated
> > into
> > > MySQL syntax.
> > >
> > > Is this a "mental model" that fits the actual implementation mechanism,
> > > somehow? Or am I totally wrong with the assumption - if not - do you
> know
> > > some of the entry points which will be of interest?
> > >
> > > Best,
> > > Daniel
> > >
> > >
> > > 2012/4/16 Kevin Sutter <kw...@gmail.com>
> > >
> > > > Hi Daniel,
> > > > There is no direct support for the MySQL spatial extensions in
> OpenJPA.
> > > > You could maybe model the support by wrappering the constructs in an
> > > > embeddable, but that may not give you the full support you are
> looking
> > > > for.  You could always help develop this support in OpenJPA and
> > > contribute
> > > > it back to the community...  :-)
> > > >
> > > > Thanks,
> > > > Kevin
> > > >
> > > > On Mon, Apr 16, 2012 at 10:15 AM, Daniel Weidele <
> > > > daniel.weidele@uni-konstanz.de> wrote:
> > > >
> > > > > Hi folks,
> > > > >
> > > > > I'm wondering whether there is any non-native support for MySQL
> > spatial
> > > > in
> > > > > OpenJPA?
> > > > > It would be sufficient to have a perimeter search in Criteria API,
> > e.g.
> > > > > based on lon/lan stored as decimals.
> > > > >
> > > > > Best regards & thx for any suggestions.
> > > > >
> > > >
> > >
> >
>

Re: OpenJPA

[Daniel Weidele <da...@uni-konstanz.de>]

Good evening, Kevin,

thanks for you explanations - I will investigate Object-to-XML mapping a
bit to gain further insights.

When you say JPQL and Criteria API are specified by JPA - does it mean that
JPA API already "ships with" lexing, tokenizer, etc.- and by that hard to
modify?
I'd more expect this to be a task of implemenations and by that part of
OpenJPA. If so, we should model a generic syntax in first place. Then we
could modify the scanning process to further be able to map to MySQL. When
I browsed through the MySQL GIS functionalities it also seems to be "ok" to
handle.

By the way - I solved the lack of a "distance"-function in MySQL by a
stored procedure. So now let's see how to perform it nicely from Criteria
API [I'd rather not like to rewrite my Criteria API queries to native SQL -
instead I'd prefer to put the efforts in extending the Criteria API - so,
tomorrow I'll have a look at Object-to-XML]

Good night!
Daniel


PS: I'm not sure whether to go on with this conversation in public or
switch to private mail?

2012/4/16 Kevin Sutter <kw...@gmail.com>

> Hi Daniel,
> You're on the right "mental" track.  Of course, it's not quite as straight
> forward...  Performing the Object-Relational mapping for spatial is
> probably the easiest part of the exercise.  Pulling the data values from
> the database, and persisting the data values back to the database is fairly
> straight-forward.  You can probably accomplish most of that today with
> minimal updates to the MySQL data dictionary (for use with OpenJPA).
>
> The more tricky aspect would be attempting to update the JPQL and/or
> Criteria API for being able to query across this data.  Since both of these
> are defined by the JPA specification, extending either of these is going to
> take some effort.  Unless you can map the spatial types to already existing
> primitive types (ie. BigDecimal, long, string, etc)...
>
> JPQL gets tricky because you would have to change the parser to pull out
> the next syntax.  These new constructs would then drive the generation of
> the SQL (which would be limited to MySQL in this case).  Lots of work.
>
> Extending the Criteria API may be a little more straight forward.  You
> would have to define the new methods for extending the Criteria API, which
> would drive down to creating the SQL.  OpenJPA's Criteria API meets up with
> the JPQL processing so that we only generate the SQL via a single process.
> This also provides us with the ability to dump out an equivalent
> "JPQL"-like string for Criteria API generated queries.
>
> I would probably consider extending our current implementation rather than
> just updating the current implementation.  Makes it easier to differentiate
> your extensions from the mainline code.  You might want to take a look at
> what we did for supporting Object-to-XML mapping.  Although different from
> what you are attempting, it should give you an idea of how we extended our
> current support and took advantage of common processing without
> re-inventing the wheel.  I know that the WebSphere product has done similar
> extensions for their JPA solution (built on top of OpenJPA).
>
> Good luck with your investigation!
>
> Kevin
>
> On Mon, Apr 16, 2012 at 12:40 PM, Daniel Weidele <
> daniel.weidele@uni-konstanz.de> wrote:
>
> > Hello Kevin,
> >
> > thanks for the @Embeddable annotation. However, I reviewed MySQL status
> and
> > it seems their spatial extension does not yet support a "distance()"
> > function. It looks like I have to contribute there, before contributing
> > here - because especially the "distance" function would be something I
> need
> > :-(
> >
> > Nevertheless, as you already mentioned - to provide full-feature support
> I
> > would as well say that @Embeddable is not enough. Of course modeling the
> > Geo-structures itself has to be done wisely - but my feeling is that the
> > more tricky part is teaching JPQL / Criteria API the geometry functions.
> > Until now I would not know where to start, as I have not that much
> insight.
> > I'd assume to have to provide some knowledge within a MySQL dictionary to
> > OpenJPA and then translate the "user" input (either from Criteria API, or
> > JPQL) - such that we have a generic language which is then translated
> into
> > MySQL syntax.
> >
> > Is this a "mental model" that fits the actual implementation mechanism,
> > somehow? Or am I totally wrong with the assumption - if not - do you know
> > some of the entry points which will be of interest?
> >
> > Best,
> > Daniel
> >
> >
> > 2012/4/16 Kevin Sutter <kw...@gmail.com>
> >
> > > Hi Daniel,
> > > There is no direct support for the MySQL spatial extensions in OpenJPA.
> > > You could maybe model the support by wrappering the constructs in an
> > > embeddable, but that may not give you the full support you are looking
> > > for.  You could always help develop this support in OpenJPA and
> > contribute
> > > it back to the community...  :-)
> > >
> > > Thanks,
> > > Kevin
> > >
> > > On Mon, Apr 16, 2012 at 10:15 AM, Daniel Weidele <
> > > daniel.weidele@uni-konstanz.de> wrote:
> > >
> > > > Hi folks,
> > > >
> > > > I'm wondering whether there is any non-native support for MySQL
> spatial
> > > in
> > > > OpenJPA?
> > > > It would be sufficient to have a perimeter search in Criteria API,
> e.g.
> > > > based on lon/lan stored as decimals.
> > > >
> > > > Best regards & thx for any suggestions.
> > > >
> > >
> >
>

Re: OpenJPA

[Kevin Sutter <kw...@gmail.com>]

Hi Daniel,
You're on the right "mental" track.  Of course, it's not quite as straight
forward...  Performing the Object-Relational mapping for spatial is
probably the easiest part of the exercise.  Pulling the data values from
the database, and persisting the data values back to the database is fairly
straight-forward.  You can probably accomplish most of that today with
minimal updates to the MySQL data dictionary (for use with OpenJPA).

The more tricky aspect would be attempting to update the JPQL and/or
Criteria API for being able to query across this data.  Since both of these
are defined by the JPA specification, extending either of these is going to
take some effort.  Unless you can map the spatial types to already existing
primitive types (ie. BigDecimal, long, string, etc)...

JPQL gets tricky because you would have to change the parser to pull out
the next syntax.  These new constructs would then drive the generation of
the SQL (which would be limited to MySQL in this case).  Lots of work.

Extending the Criteria API may be a little more straight forward.  You
would have to define the new methods for extending the Criteria API, which
would drive down to creating the SQL.  OpenJPA's Criteria API meets up with
the JPQL processing so that we only generate the SQL via a single process.
This also provides us with the ability to dump out an equivalent
"JPQL"-like string for Criteria API generated queries.

I would probably consider extending our current implementation rather than
just updating the current implementation.  Makes it easier to differentiate
your extensions from the mainline code.  You might want to take a look at
what we did for supporting Object-to-XML mapping.  Although different from
what you are attempting, it should give you an idea of how we extended our
current support and took advantage of common processing without
re-inventing the wheel.  I know that the WebSphere product has done similar
extensions for their JPA solution (built on top of OpenJPA).

Good luck with your investigation!

Kevin

On Mon, Apr 16, 2012 at 12:40 PM, Daniel Weidele <
daniel.weidele@uni-konstanz.de> wrote:

> Hello Kevin,
>
> thanks for the @Embeddable annotation. However, I reviewed MySQL status and
> it seems their spatial extension does not yet support a "distance()"
> function. It looks like I have to contribute there, before contributing
> here - because especially the "distance" function would be something I need
> :-(
>
> Nevertheless, as you already mentioned - to provide full-feature support I
> would as well say that @Embeddable is not enough. Of course modeling the
> Geo-structures itself has to be done wisely - but my feeling is that the
> more tricky part is teaching JPQL / Criteria API the geometry functions.
> Until now I would not know where to start, as I have not that much insight.
> I'd assume to have to provide some knowledge within a MySQL dictionary to
> OpenJPA and then translate the "user" input (either from Criteria API, or
> JPQL) - such that we have a generic language which is then translated into
> MySQL syntax.
>
> Is this a "mental model" that fits the actual implementation mechanism,
> somehow? Or am I totally wrong with the assumption - if not - do you know
> some of the entry points which will be of interest?
>
> Best,
> Daniel
>
>
> 2012/4/16 Kevin Sutter <kw...@gmail.com>
>
> > Hi Daniel,
> > There is no direct support for the MySQL spatial extensions in OpenJPA.
> > You could maybe model the support by wrappering the constructs in an
> > embeddable, but that may not give you the full support you are looking
> > for.  You could always help develop this support in OpenJPA and
> contribute
> > it back to the community...  :-)
> >
> > Thanks,
> > Kevin
> >
> > On Mon, Apr 16, 2012 at 10:15 AM, Daniel Weidele <
> > daniel.weidele@uni-konstanz.de> wrote:
> >
> > > Hi folks,
> > >
> > > I'm wondering whether there is any non-native support for MySQL spatial
> > in
> > > OpenJPA?
> > > It would be sufficient to have a perimeter search in Criteria API, e.g.
> > > based on lon/lan stored as decimals.
> > >
> > > Best regards & thx for any suggestions.
> > >
> >
>

Re: OpenJPA

[Daniel Weidele <da...@uni-konstanz.de>]

Hello Kevin,

thanks for the @Embeddable annotation. However, I reviewed MySQL status and
it seems their spatial extension does not yet support a "distance()"
function. It looks like I have to contribute there, before contributing
here - because especially the "distance" function would be something I need
:-(

Nevertheless, as you already mentioned - to provide full-feature support I
would as well say that @Embeddable is not enough. Of course modeling the
Geo-structures itself has to be done wisely - but my feeling is that the
more tricky part is teaching JPQL / Criteria API the geometry functions.
Until now I would not know where to start, as I have not that much insight.
I'd assume to have to provide some knowledge within a MySQL dictionary to
OpenJPA and then translate the "user" input (either from Criteria API, or
JPQL) - such that we have a generic language which is then translated into
MySQL syntax.

Is this a "mental model" that fits the actual implementation mechanism,
somehow? Or am I totally wrong with the assumption - if not - do you know
some of the entry points which will be of interest?

Best,
Daniel


2012/4/16 Kevin Sutter <kw...@gmail.com>

> Hi Daniel,
> There is no direct support for the MySQL spatial extensions in OpenJPA.
> You could maybe model the support by wrappering the constructs in an
> embeddable, but that may not give you the full support you are looking
> for.  You could always help develop this support in OpenJPA and contribute
> it back to the community...  :-)
>
> Thanks,
> Kevin
>
> On Mon, Apr 16, 2012 at 10:15 AM, Daniel Weidele <
> daniel.weidele@uni-konstanz.de> wrote:
>
> > Hi folks,
> >
> > I'm wondering whether there is any non-native support for MySQL spatial
> in
> > OpenJPA?
> > It would be sufficient to have a perimeter search in Criteria API, e.g.
> > based on lon/lan stored as decimals.
> >
> > Best regards & thx for any suggestions.
> >
>

Re: OpenJPA

[Kevin Sutter <kw...@gmail.com>]

Hi Daniel,
There is no direct support for the MySQL spatial extensions in OpenJPA.
You could maybe model the support by wrappering the constructs in an
embeddable, but that may not give you the full support you are looking
for.  You could always help develop this support in OpenJPA and contribute
it back to the community...  :-)

Thanks,
Kevin

On Mon, Apr 16, 2012 at 10:15 AM, Daniel Weidele <
daniel.weidele@uni-konstanz.de> wrote:

> Hi folks,
>
> I'm wondering whether there is any non-native support for MySQL spatial in
> OpenJPA?
> It would be sufficient to have a perimeter search in Criteria API, e.g.
> based on lon/lan stored as decimals.
>
> Best regards & thx for any suggestions.
>