Re: [Geotools-devel] Passthru query for PostGIS?

[Chris H. <ch...@op...>]

> > I was thinking that it would be nice to have a SquirrelSQL look alike tool
> > to manage a Postgis database, but with the ability to show results as a map,
> > not as a table... now, it seems there is no support for passthru queries, and
> > there is no infrastructure even... What I mean is that a query can return
> > any feature type, maybe resulting from a join, so you pass the query, and
> > have
> > the FeatureType as a collateral result, whilst current structure pretends
> > that you
> > first pass the feature type, then perform a query on it...
> 
> I think that chris had me turn this one around, You can ask for a FeatureReader
> with just a Query.  This would allow a query to be something "magic" like a
> complex SQL Query.

Wow, I didn't know that you actually did that, I just wanted it, but 
didn't remember that you had implemented.  Cool.

> So there are two options:
> - Teach the SQLEncoder more sql
> - Make a SQLQuery that the SQL Encoder echo's straight out.
> 
> I belive the existing SQL Encoder is fairly complete, I have not seen a full
> listing of its capabilies.

The one thing that is missing is the ability to do joins.  To say I want
three columns from one db, two from another, joined on an attribute.  
This would be a nice ability to have.  I think for joins it makes more
sense to do the second option, as they can get pretty complicated.  At the
last ogc meeting there was discussion of a query language, but it looks
like it'll be awhile coming.  So instead of inventing our own I think
allowing users to specify their sql query makes sense.

Adding this then begs the question of how we treat 'virtual' tables.  
Perhaps in addition to a getFeatureSource(typename) we have a 
getFeatureSource(SQLQuery)?  Or perhaps makeFeatureSource(typename, 
SQLQuery), where it creates the virtual table, which can then be requested 
by typename?  I think this is actually slightly seperate from the 
SQLEncoder, as the query will replace the table name.  So instead of 
'SELECT length FROM roads WHERE length = 5;' You'd have 'SELECT length 
FROM (SELECT * FROM roads, test where roads.gid=test.gid) WHERE length = 
5).  So perhaps we define a way to establish such a virtual table?  The 
SQLEncoder would then encode the 'typename' as the join.  This is not 
thought through, so may just be backward.  But the nice thing about doing 
things this way is it allows us to continue to use all the functionality 
of the Query, encoded into sql, so a FeatureSource can still return 
everything it needs to.  

Thinking further, it sounds like a SQLQuery should also be a part of the 
SQLEncoder, replacing the typename where appropriate.  If this step is not 
taken then there's no way to make a FeatureSource - if a client just 
specified what their sql query was then they wouldn't be able to do 
additional propertyname and where filtering, that stuff needs to be 
encoded seperately.

Hmmm... I'm narrowing the scope of this problem to just the joins, not the
general db specific stuff you were asking for.  I'm not sure if there's a
good generic way to handle all the possibilities, except perhaps at a low
level where you just ask for a FeatureReader.  Our sql statements are
divided up in the Query, property names for the select, filters for the
where.  So I don't know what to do about the general problem, but
specifically for joins I think it makes sense to somehow allow users to
specify what to call their table.  And to just make it available for
advanced users who know sql at least to start; it'd be too much work to
otherwise (though in fact might not be too bad, if we had users define 
their tables by queries - that is property names and filters.  The one 
additional thing we'd need is the sql AS, that is to somehow call a 
propertyname something else - if that was done then the filter perhaps 
define the join).  I think this gets into Gabriel's work with a 'view' 
that we should look at again and make more explicit.

So basically the answer to your broader query Andrea is that no, this has 
not been seriously been thought about.  It's been desired for awhile, but 
no one _needed_ it, and there was a lot else to do.  So it sounds like 
it's time to start thinking about it.

Chris