Registered function support for JDBC databases

[Ben Caradoc-Davies-2]

I have written a prototype for "registered function" support in WFS
filter queries, in which functions not implemented in GeoServer are
passed to the database for execution. This allows complex or expensive
domain-specific queries to be performed on the database server side.

Please comment if you think that this could be expanded or modified to
help you.

See:
http://jira.codehaus.org/browse/GEOT-1929

Notes:
- Only PostGIS is supported at the moment.
- Only the community-schemas build has it
- There is no register of functions, so all are permitted (SECURITY!)
(See GEOT-1930.)

More and better support when this is merged onto trunk and into core.

Credits go to Rob Atkinson for the concept, name, and advocating this
approach to users for about a year.  :-)

--
Ben Caradoc-Davies <Ben.Caradoc-Davies@...>
Software Engineer, CSIRO Exploration and Mining
Australian Resources Research Centre
26 Dick Perry Ave, Kensington WA 6151, Australia

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@...
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

[Jody Garnett]

A couple things; executing any sql function is not a good thing from a
security standpoint (but you know this). You should be able to advertise
additional functions on a data store by datastore basis using the filter
capabilities data structure. You are the first person to want to do this
so please let me know if you have any questions.

I like your idea of storing the additional functions as a table; the
best move would be to have the PostGISDataStoreFactory check for this
table and make the correct implementations as needed.

Jody

Ben Caradoc-Davies wrote:

> I have written a prototype for "registered function" support in WFS
> filter queries, in which functions not implemented in GeoServer are
> passed to the database for execution. This allows complex or expensive
> domain-specific queries to be performed on the database server side.
>
> Please comment if you think that this could be expanded or modified to
> help you.
>
> See:
> http://jira.codehaus.org/browse/GEOT-1929
>
> Notes:
> - Only PostGIS is supported at the moment.
> - Only the community-schemas build has it
> - There is no register of functions, so all are permitted (SECURITY!)
> (See GEOT-1930.)
>
> More and better support when this is merged onto trunk and into core.
>
> Credits go to Rob Atkinson for the concept, name, and advocating this
> approach to users for about a year.  :-)
>
>  

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@...
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

[Rob Atkinson-2]

There is nothing intrinsically postgis about the solution, we envisage it a JDBC capability.

A parameter with a sensible default could advertise the table or function that would be interrogated to find functions intended to be advertised. 

My could attempt to call a function
getRegisteredFunctions(key)  where key is a configuration parameter that may be null. Such a function could just hardcode function names if you only want a couple.

The alternative would be to allow the JDBC connection to access a table, so different user logins could access different sets of functions.  This may be more work at the db administration end.

Open to ideas as to which is best strategy.

Rob

On Tue, Jul 22, 2008 at 12:29 AM, Jody Garnett <jgarnett@...> wrote:

A couple things; executing any sql function is not a good thing from a
security standpoint (but you know this). You should be able to advertise
additional functions on a data store by datastore basis using the filter
capabilities data structure. You are the first person to want to do this
so please let me know if you have any questions.

I like your idea of storing the additional functions as a table; the
best move would be to have the PostGISDataStoreFactory check for this
table and make the correct implementations as needed.

Jody

Ben Caradoc-Davies wrote:
> I have written a prototype for "registered function" support in WFS
> filter queries, in which functions not implemented in GeoServer are
> passed to the database for execution. This allows complex or expensive
> domain-specific queries to be performed on the database server side.
>
> Please comment if you think that this could be expanded or modified to
> help you.
>
> See:
> http://jira.codehaus.org/browse/GEOT-1929
>
> Notes:
> - Only PostGIS is supported at the moment.
> - Only the community-schemas build has it
> - There is no register of functions, so all are permitted (SECURITY!)
> (See GEOT-1930.)
>
> More and better support when this is merged onto trunk and into core.
>
> Credits go to Rob Atkinson for the concept, name, and advocating this
> approach to users for about a year.  :-)
>
>


-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Geotools-devel mailing list
Geotools-devel@...
https://lists.sourceforge.net/lists/listinfo/geotools-devel

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@...
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

[Jody Garnett]

Rob Atkinson wrote:
> There is nothing intrinsically postgis about the solution, we envisage
> it a JDBC capability.
I agree :-)

> A parameter with a sensible default could advertise the table or
> function that would be interrogated to find functions intended to be
> advertised.
>
> My could attempt to call a function getRegisteredFunctions(key)  where
> key is a configuration parameter that may be null. Such a function
> could just hardcode function names if you only want a couple.
>
> The alternative would be to allow the JDBC connection to access a
> table, so different user logins could access different sets of
> functions.  This may be more work at the db administration end.
>
> Open to ideas as to which is best strategy.

I would go with a single table for now; not sure what to call it. My
initial feedback was more about how to advertise what functions are
available - so geoserver can report them as available etc.

There is one thing to check in the WFS specification; I am under the
impression that server needs to report back a filter capabilities
(including a function list). These capabilities may be for all feature
types? Or hopefully on a feature type by feature type basis ...
depending on what the specification says we may need to set up a change
request for the OGC.

Jody

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@...
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

[Rob.Atkinson]

> My initial feedback was more about how to advertise what functions are available -

allowing only these functions is possibly the higher priority.

> so geoserver can report them as available etc.

>There is one thing to check in the WFS specification; I am under the
impression that server needs to report back a filter capabilities
(including a function list). These capabilities may be for all feature
types? Or hopefully on a feature type by feature type basis ...
depending on what the specification says we may need to set up a change
request for the OGC.

Ahh! Herein lies a quandry. WFS doesnt allow per feature functions to be advertised, and most interesting features have implicit or explicit operations that would be nice to advertise per feature as functions.  Ideally, those functions are only allowed on those features too.

IMHO WFS does not fully implement the ISO General Feature model, in its current form.

That said, just knowing the function name isnt very useful... you really need to know the range of the values of the arguments. This brings us back to the more general issue of advertising a query model for a service, and I think the functions are part of this. Simon Cox and I submitted a change request to WFS based on my prompting - that allowed a per-feature type query model to be advertised by reference, so we didnt have to prematurely define the ultimate query model definition language just yet.  Unfortunately there just isn't enough experience with WFS out there for people to fully grasp the implications - they still think in terms of eyeballing all the data on the disk to see how to formulate a sensible filter.

IMHO any SDI will need to catalog query models ("what questions can you ask") and link services to these, rather than cataloging services and then hoping to magically find a way of understanding what they do. This takes the metadata out-of-band, and we can implement an SDI with current WFS quite happily.

Rob






-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@...
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

[Jody Garnett]

Rob.Atkinson@... wrote:
> Ahh! Herein lies a quandry. WFS doesnt allow per feature functions to be advertised, and most interesting features have implicit or explicit operations that would be nice to advertise per feature as functions.  Ideally, those functions are only allowed on those features too.
>  
Start small start small :-) Listing the functions with parameter names
will go a long ways :-) Expressions are "untyped" so you don't even have
to mess around with types for the first cut ...

Jody

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@...
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

[Rob Atkinson-2]

hmm - IMHO not having to get a WFS change request is starting small. Just accepting that we can get the functionality working and work out how to advertise it later (i.e. there isnt really much point advertising it if its feature specific) seems to me a much lower bar.  Getting the big picture right usually means the pieces are simpler (cleaner separation of concerns!)

Rob

On Tue, Jul 22, 2008 at 11:15 AM, Jody Garnett <jgarnett@...> wrote:

Rob.Atkinson@... wrote:

Ahh! Herein lies a quandry. WFS doesnt allow per feature functions to be advertised, and most interesting features have implicit or explicit operations that would be nice to advertise per feature as functions.  Ideally, those functions are only allowed on those features too.
 

Start small start small :-) Listing the functions with parameter names will go a long ways :-) Expressions are "untyped" so you don't even have to mess around with types for the first cut ...

Jody

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@...
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

[Jody Garnett]

Rob Atkinson wrote:
> hmm - IMHO not having to get a WFS change request is starting small.
> Just accepting that we can get the functionality working and work out
> how to advertise it later (i.e. there isnt really much point
> advertising it if its feature specific) seems to me a much lower bar.
Difficulty is that when we make requests from udig against a wfs we tend
to check the request against the published capabilities; so we can catch
the use of functions that won't be recognized.

Jody

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@...
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

[Rob.Atkinson]

OK - I suppose thats a valid sanity check - not enough info to help formulate the request, since you dont even know what attributes of what feature types it applies to, but you can reject some dumb ones. Anyway, its such a trivial thing to advertise them we can do it now. I was just sceptical about its value, but if there are clients that care its valuable enough for now.

Rob

-----Original Message-----
From: Jody Garnett [mailto:jgarnett@...]
Sent: Tue 7/22/2008 11:51 AM
To: Rob Atkinson
Cc: Atkinson, Rob (CLW, Lucas Heights); Caradoc-Davies, Ben (E&M, Kensington); geoserver-devel@...; geotools-devel@...
Subject: Re: [Geotools-devel] Registered function support for JDBC databases

Rob Atkinson wrote:
> hmm - IMHO not having to get a WFS change request is starting small.
> Just accepting that we can get the functionality working and work out
> how to advertise it later (i.e. there isnt really much point
> advertising it if its feature specific) seems to me a much lower bar.
Difficulty is that when we make requests from udig against a wfs we tend
to check the request against the published capabilities; so we can catch
the use of functions that won't be recognized.

Jody




-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@...
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

[Ben Caradoc-Davies-2]

Jody Garnett wrote:
> A couple things; executing any sql function is not a good thing from a
> security standpoint (but you know this).

Indeed.

> You should be able to advertise
> additional functions on a data store by datastore basis using the filter
> capabilities data structure. You are the first person to want to do this
> so please let me know if you have any questions.

I had originally intended to use one dynamic proxy class for each
function, because FilterCapabilities has a one-class to one-function
mapping, but FunctionFinder wants a default constructor, and dynamic
proxy classes constructors take an invocation handler. What I have done
is to use a single RegisteredFunction class to handle all unrecognised
functions.

The design of FilterCapabilities does not support one class representing
multiple functions. It needs to be refactored to have an instance (not a
class) representing a function, and another instance representing a
function with bound parameters. This would be much like the GeoAPI
Attribute/Descriptor split.

> I like your idea of storing the additional functions as a table; the
> best move would be to have the PostGISDataStoreFactory check for this
> table and make the correct implementations as needed.

This functionality should be pulled right up into JDBCDataStore and friends.

--
Ben Caradoc-Davies <Ben.Caradoc-Davies@...>
Software Engineer, CSIRO Exploration and Mining
Australian Resources Research Centre
26 Dick Perry Ave, Kensington WA 6151, Australia

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@...
https://lists.sourceforge.net/lists/listinfo/geoserver-devel