I introduced a late bug where it doesn't work if a pass through SQL is given, but no schema.xml is supplied.
I'm also going to check out how it runs with postgis tonight and maybe Rob's geometryless JDBCDataStore (I've developed against Oracle Spatial) - that will undoubtably flush out a few more bugs, so might be best to wait till then before we try to merge it into a reference branch.
Stuart Girvan at Geoscience Austrlia has had some success with it running against Oracle Spatial - but he was using my development version, so we haven't tried applying the changes to the head just yet.
I have a bit more to add to Chris' comments - but want to address the bugs / other JDBC Datastores first.
Ok, I've had a good review of the code, and am passing it onto the list
for others to check out. Overall it looks like some great work,
definitely a nice solution to a very hard problem. It makes use of
most all the right classes I believe, except for the fact that the work
is not in GeoTools, but in GeoServer. But that is the recommended
strategy, as passing this stuff off to GeoTools will and should involve
some more minds looking it over.
It does imply some core changes in how a few things are done, and I
think this should be looked at in combination with the Expr work that
Jody and Andrea are undertaking. I can get down and dirty with that
and look into how we should incorporate this. In an ideal world we
make the DataStore access so good that a user would not have to
actually specify the bypassSQL at all, they would just provide a
mapping of the datastore values that they wanted, and some pre
processing would turn it into the appropriate sql. But I'm not sure
that we have the resources to undertake that effort, as I think it will
involve coming up with a whole new xml syntax to specify such things
(though I may be wrong). I think deegree has done some work in this
direction, specifying their table mappings in XML (though they just
limit it to single datastores, not cross datastores as we want to do). I asked them about it awhile ago, but never got a response. If we
don't get there I think the bypass sql stuff is a very reasonable half
step to allow.
This does imply some changes to AttributeTypes and FeatureTypes, in
order to carry nested information. Peter just did this in GeoServer
*TypeInfo classes, which is now becoming a popular way of working - I
remember Jody just stuffing all the extra stuff he wanted out of
AttributeTypes in there as well. Peter's code provides a good jumping
off point, that we should take further to actually become a part of
GeoTools.
I think one area for improvement is the FeatureTransformer stuff. The
approach taken is definitely the right one to get the work done without
messing with a bunch of geotools stuff, but I think both Jody and I
were hoping that it would be able to work more within the geotools
framework. I definitely understand why you chose not to, since it is a
nasty beast, and it takes a good bit of work to get acquinted with how
it all works, let alone how to modify it. Indeed I'm not yet sure that
it is possible to combine bypass sql with the geotools structure,
without coming up with the full syntax I mentioned of earlier. I need
to dig into the code and think about it a lot more, but it is
definitely quite nice to have an example of how it needs to work.
Our thought was that a special ASFeatureTransformer and XMLElement
structure would not be needed - that that information could be
contained in the AttributeType and FeatureType, which would then be
appropriately transformed. You could have nested AttributeTypes -
we've actually started work on this, but it's hidden away as static
classes in the DefaultAttributeType class. For example there is a
DefaultAttributeType.Feature, that you can store a full nested feature
in. I think perhaps it is time to move that hidden structure into its
own hiearachy, and to expand it to deal with the nested Attributes that
SCO wants to deal with. Once the nested structure can be completely
represented in GeoTools then the FeatureTransformer should be able to
just take an Attribute and realize that it is a nested attribute and
pass each nested one to be expanded. Hmmm.... Thinking on it though
this is definitely more work than I had imagined. But I think it is
worthwhile to think through all this stuff long and hard and get it
into GeoTools for real.
So I'm going to spend the next couple of weeks doing that, and see what
I come up with. As for strategies of what to do with this code, I
think the best thing would be to start a branch off of GeoServer 1.2. I would like to get it up and running on my machine as well, I think a
specialized cite/confBypass would be in order, perhaps even with a
database script to set things up, so that developers can all get a
similar set up. Then we should attempt to refactor the work into
GeoTools, comparing against the working example on the branch. I think
this will be a great way to keep us honest, to make sure the code comes
out the same.
But yeah, great job on getting this started, it's definitely a huge leap
forward in the right direction, on a problem we've been wanting to
solve for awhile. I should be able to devote some decent resources to
it, and hopefully we can get GeoTools up to snuff.
best regards,
Chris
----- Forwarded message from Peter Barrs <pbarrs@anonymised.com>
-----
Date: Fri, 17 Sep 2004 22:27:00 +1000
From: Peter Barrs <pbarrs@anonymised.com>
Reply-To: pbarrs@anonymised.com
Subject: Pass through SQL and Nested XML source
To: Chris Holmes <cholmes@anonymised.com>
Hi Chris,
Please finally find attached source tarballs of changed/new
geoserver/geootools source files
They are still quite rough - but there is enough working to get the
idea.
Also I've attached some brief outline notes to help.
If you want to have a chat about it that is fine - you'll just need to
email me in advance, I'm not using a permanently on connection for the
next few weeks, unless I go into town.
I know Jody would like this stuck on the list first up - but you might
want to look at it first and then decide. It hasn't been run through
jalopy or anuthing like that.
---
Some final notes: on nested XML element output
1) You must have a fid attribute for each recurrence group (see
schema.xml example)
2) You must sort the result set by these fids (the query in info.xml)
(There is a hook in the code for me to write some break checking code
that doesn't require fids, but I haven't done it yet and fids are the
most efficient way of doing break checking; the result set still has to
be sorted)
3) To invoke the nested XML outputter, you have to set the outputFormat
attribute to GML2-AS in the WFS request:
<wfs:GetFeature service="WFS" version="1.0.0"
outputFormat="GML2-AS"
xmlns:wfs="http://www.opengis.net/wfs"
xmlns:ogc="http://www.opengis.net/ogc"
xmlns:gml="http://www.opengis.net/gml"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.opengis.net/wfs
http://schemas.opengis.net/wfs/1.0.0/WFS-basic.xsd">
<wfs:Query typeName="sco:geochem">
<ogc:Filter>
<ogc:PropertyIsEqualTo>
<ogc:PropertyName>_arg_analyte</ogc:PropertyName>
<ogc:Literal>Ag</ogc:Literal>
</ogc:PropertyIsEqualTo>
</ogc:Filter> </wfs:Query>
</wfs:GetFeature>
(GML2-AS-ZIP should produce compressed output, although I have never
tested this)
Cheers
Peter Barrs
----- End forwarded message -----
----------------------------------------------------------
This mail sent through IMP: https://webmail.limegroup.com/
------------------------------------------------------------------------
Pass through SQL and nested XML element output
An SQL select may be supplied in a feature type's info.xml. This facility should work with Datastores derived from JDBCDataStore. At the time of writing it has only had limited testing on Oracle Spatial. The select statement is provided in a new attribute, bypassSQL, see below:
<featureType datastore="kyanite.oracle.geochem">
<name>geochem</name>
<!-- native EPGS code for the FeatureTypeInfoDTO : for Oracle, all geometries must use an equivalent SRID -->
<SRS>4283</SRS>
<title>GA GeoChem sample</title>
<abstract>GA GeoChem sample for studying output of GML application schemas</abstract>
<numDecimals value="0"/>
<keywords>geochemistry</keywords>
<latLonBoundingBox dynamic="false" miny="-90" maxy="90" maxx="180" minx="-180"/>
<bypassSQL><![CDATA[
Select * from (
select
a.siteNo GeochemFID, a.siteno SiteNo, a.siteid SiteID, a.locdesc LocationName, l.locmethod LocationMethod,
a.dlat Latitude, a.dlong Longitude, a.datum Datum, a.latlong_geom LatLong_geom,
r.rockno RockNo, r.sampleid SampleID, r.qualifier || ' ' || r.lithname QualifiedLithName,
aa.analno AnalysisNo, aa.analtype AnalyteType,
ar.resultno ResultNo,
ar.analyte Analyte, ar.value Value, ar.unit Units, ar.error Error,
ap.detection_limit_min DetectionLimit, ap.unit Unit, ap.precision Precision, m.description Method, al.name Laboratory
from
allsites a, rocks r, locmethods l, allresults ar , allanalyses aa, analyte_parameter ap, batches b, methods m, laboratory al
where
aa.analno=ar.analno
and a.method=l.locmethno
and a.siteid=r.siteid
and r.rockno=aa.rockno
and ar.analysis_id=ap.analysis_id
and ap.methodno=m.methodno
and ar.batchno=b.batchno
and ap.labno=al.labno
and ar.analyte = ?
order by a.siteno, r.rockno, aa.analno, ar.analyte )
]]></bypassSQL>x`
<!--
the default style this FeatureTypeInfoDTO can be represented by.
at least must contain the "default" attribute
-->
<styles default="normal"/>
</featureType>
Note the "ar.analyte = ?" clause. The ? is a placeholder, whose value is supplied by a property in a WFS Filter, eg
<wfs:GetFeature service="WFS" version="1.0.0"
outputFormat="GML2-AS"
xmlns:wfs="http://www.opengis.net/wfs"
xmlns:ogc="http://www.opengis.net/ogc"
xmlns:gml="http://www.opengis.net/gml"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.opengis.net/wfs
http://schemas.opengis.net/wfs/1.0.0/WFS-basic.xsd">
<wfs:Query typeName="sco:geochem">
<ogc:Filter>
<ogc:PropertyIsEqualTo>
<ogc:PropertyName>_arg_analyte</ogc:PropertyName>
<ogc:Literal>Ag</ogc:Literal>
</ogc:PropertyIsEqualTo>
</ogc:Filter> </wfs:Query>
</wfs:GetFeature>
The literal associated with any property name prefixed with _arg in a PropertyIsEqualTo relationship is passed to a ? placeholder. They are matched by order of appearance. The SQL select is executed as a JDBC prepared statement.
The default GML2 outputter can be used (set outputFormat="GML2" in the request). Typically output will be sent to the nested XML element outputter. Specify outputFormat="GML2-AS" as above. This is discussed in detail, further below.
------------------------------------------------------------------------
Implementation
Loading Bypass SQL
The bypassSQL attribute is loaded by the loadFeaturePt2 method in org.vfny.geoserver.global.xml.XMLConfigReader into a geoserver FeatureTypeInfoDTO
org.vfny.geoserver.global.dto.FeatureTypeInfoDTO provides getBypassSQL and getBypassSQL methods
ToDo: Update FeatureTypeInfoDTO .toString() method for bypassSQL field ??
FeatureTypeInfoDTO .equals() has been updated
org.vfny.geoserver.global.dto.FeatureTypeInfo loads bypassSQL from FeatureTypeInfoDTO in the constructor.
The FeatureTypeInfo constructor is discussed further below, in the treatment of a feature type schema.xml
Processing BypassSQL
Geotools AbstractService pass the WFS request to org.vfny.geoserver.responses.wfs.FeatureResponse
FeatureReponse has been modified; the execute(FeatureRequest) method has been modified to send the request to passThroughSQLExecute(FeatureRequest) if a query within the request contains a bypassSQL, otherwise it's flicked to standardExecute(FeatureRequest), which is just the original execite(featureRequest) method, before it was commandeered.
This really needs to be tidied up; it won't work if there are mixed queries within a single request - ie some with bypassSQL, some without.
At this point two new classes are introduced:
org.vfny.geoserver.responses.wfs.BypassSQLFeatureResults
and
org.vfny.geoserver.responses.wfs.SQLPlaceholders
These classes should be OK to read through - note that there is no checking that the no. of placeholders in the SQL matches the no. of literals.
The main issue is that if no schema.xml has been supplied with the feature type info.xml, a Geotools FeatureType has to be constructed from result metadata. (Geotools feature type for JDBCDataStores are built from table metadata, which
doesn't work for pass through SQL). This is somewhat inefficient in that the FeatureType has to be known a priori, so the query is executed in the constructor, and then again subsequently...
When built this way, the FeatureType is not properly cached (see FeatureTypeHandler) and this should be looked at, or we should decide if we want to support this mode of operation. It is handy for quick adhoc SQL - and the schema.xml file is a pain to write, so I would like to keep it if we can address the issues below.
The main problem with building a FeatureType from result set metadata, is that the treatment of geometry type info is not consistent or accessible in subclassed datastores, so for now I assume that if a column isn't a primitive - it is spatial (!). (Actually will be OK most of the time). This approach works for OracleSpatial, but I'm not sure about other data stores. Note that table and result set metadata are obtained by quite different means.
If a schema.xml is supplied, the geotools FeatureType is built in the org.vfny.geoserver.global.dto.FeatureTypeInfo constructor and there are no efficiency or unknown type issues.
The other issue is that BypassSQLFeatureResults is built in Geoserver and therefore not in the same package as the Geotools JDBCDataStore. To provide quick access to connections and type info I made some private members public. I haven't tried to address this till the Geoserver team decide where this code really belongs - or if it needs to be split up.
Pass through SQL ignores FIDS and locking - I've assumed that it will only be used in read only situations; not part of an update transaction.
If producing nested XML output, FIDS are explicitly mapped in schema.xml
So if we are going to support pass through SQL using the default GML2 outputter, FID support has to be revisited.
------------------------------------------------------------------------
SQLPlaceholders processes the filter to extract the placeholder values. Any "bits" left over are tacked onto the bypassSQL.
This is handy for BBOX processing, but if producing nested XML output (GML2-AS), it is important that the result set rows be ordered in the order of element value enumeration in the XML output.
------------------------------------------------------------------------
Processing schema.xml
A schema.xml file associates database attributes with an internal java type and an external xml type and an xpath path, describing where in the XML output the database attribute maps to. In practice it is probably sufficient to use Strings for all types except the geometry field.
A geotools FeatureType is derived from the internal java type attribute dbJavaType in schema.xml
This is done by org.vfny.geoserver.global.XMLelementStructure, which is created as a member of org.vfny.geoserver.global.FeatureTypeInfo in the FeatureTypeInfo constructor.
Modfications were made to org.vfny.geoserver.global.Data, org.geotools.data.jdbc.JDBCDataStore, FeatureTypeHandler and org.geotools.data.jdbc.FeatureTypeInfo to stop auto FetaureType (re)generation, where it went looking for a table to obtain metadata from.
As a consequence it is no longer necessary to name a fetaure type after a database table / view.
------------------------------------------------------------------------
Ouput
If the GML2-AS formatter is requested (AS =Application Schema), FeatureResponseDelegateFactory creates a GML2AppSchemaFeatureResponseDelegate which creates an ASFeatureTransformer.
Unfortunately I couldn't see how to subclass the geotools FeatureTranslator in FeatureTransformer, so I had to copy the whole class (giving ASFeatureTransformer), which cascaded back to GML2AppSchemaFeatureResponseDelegate being copied from GML2SchemaFeatureResponseDelegate
I think there definitely needs to be some refactoring here. Also (again) I've put classes which probably belong in GeoTools into GeoServer - cause it's easier to develop code that way.
That aside, ASFeatureTransformer eventually spawns a FeatureTranslator, the handleFeatureReader() methods of which consults with org.vfny.geoserver.global.XMLelementStructure to determine which elements get written out; the actual writing out still happens (but by callback) in FeatureTranslator.
XMLElementStructure can map XML element structures such as
<A1>
<B1><C1>..</C1>.</B1>
<D1>..</D1>
</A1>
But the outputter will only process
<A1>
<B1>
<C1>..</C1>
</B1>
</A1>
at this time.
Currently XMLElementStructure lives in the Geoserver FeatureTypeInfo - it may be better placed in Geotools FeatureType.
------------------------------------------------------------------------
I haven't paid much attention to Exception processing (looking for some guidance) and paid no attention to max feature limiting.
------------------------------------------------------------------------
Example schema.xml
<xs:complexType name="geochem_Type">
<xs:complexContent>
<xs:extension base="gml:AbstractFeatureType">
<xs:sequence>
<!-- It is expected that a ResultSet arising from the associated bypassSQL element in
info.xml will contain attributes corresponding to the element names below -->
<!-- the dbJavaType attribute maps from JDBC SQL types onto Java types; for geometries,
use class names in com.vividsolutions.jts.geom.Geometry, eg for an SDO_GEOMETRY
column in Oracle, use Geometry. In Postgis, a variety of geometry classes may be
used - see org.geotools.data.postgis.PostgisDataStore GEOM_TYPE_MAP entries -->
<!-- Site, mapped to Feature Type = Geochem -->
<xs:element name="GeochemFID" type="xs:integer" dbJavaType="java.lang.Integer" nillable="false"
minOccurs="1" maxOccurs="1" xpath="/Geochem@anonymised.com"/>
<xs:element name="SiteNo" type="xs:integer" dbJavaType="java.lang.Integer" nillable="false"
minOccurs="1" maxOccurs="1" xpath="/Geochem/SiteNo"/>
<xs:element name="SiteID" type="xs:integer" dbJavaType="java.lang.Integer" nillable="true"
minOccurs="0" maxOccurs="1" xpath="/Geochem/SiteId"/>
<xs:element name="LocationName" type="xs:string" dbJavaType="java.lang.String" nillable="true"
minOccurs="0" maxOccurs="1" xpath="/Geochem/LocationName"/>
<xs:element name="LocationMethod" type="xs:string" dbJavaType="java.lang.String" nillable="true"
minOccurs="0" maxOccurs="1" xpath="/Geochem/LocationMethod"/>
<xs:element name="Latitude" type="xs:double" dbJavaType="java.lang.Double" nillable="true"
minOccurs="0" maxOccurs="1" xpath="/Geochem/Latitude"/>
<xs:element name="Longitude" type="xs:double" dbJavaType="java.lang.Double" nillable="true"
minOccurs="0" maxOccurs="1" xpath="/Geochem/Longitude"/>
<xs:element name="Datum" type="xs:string" dbJavaType="java.lang.String" nillable="true" minOccurs="0"
maxOccurs="1" xpath="/Geochem/Datum"/>
<xs:element name="LatLong_Geom" dbJavaType="com.vividsolutions.jts.geom.Geometry" type="gml:PointPropertyType"
nillable="true" minOccurs="1" maxOccurs="1" xpath="/Geochem/gml:location"/>
<!-- Rock -->
<xs:element name="RockNo" type="xs:integer" dbJavaType="java.lang.Integer" nillable="false"
minOccurs="1" maxOccurs="1" xpath="/Geochem/Rock@anonymised.com"/>
<xs:element name="SampleId" type="xs:string" dbJavaType="java.lang.String" nillable="true"
minOccurs="0" maxOccurs="1" xpath="/Geochem/Rock/SampleId"/>
<xs:element name="QualifiedLithName" type="xs:string" dbJavaType="java.lang.String" nillable="true"
minOccurs="0" maxOccurs="1" xpath="/Geochem/Rock/QualifiedLithName"/>
<xs:element name="AnalysisNo" type="xs:integer" dbJavaType="java.lang.Integer" nillable="false"
minOccurs="1" maxOccurs="1" xpath="/Geochem/Rock/Analysis@anonymised.com"/>
<xs:element name="AnalyteType" type="xs:string" dbJavaType="java.lang.String" nillable="true"
minOccurs="0" maxOccurs="1" xpath="/Geochem/Rock/Analysis/AnaylyteType"/>
<!-- Result -->
<xs:element name="ResultNo" type="xs:integer" dbJavaType="java.lang.Integer" nillable="false"
minOccurs="1" maxOccurs="1" xpath="/Geochem/Rock/Analysis/Result@anonymised.com"/>
<xs:element name="Analyte" type="xs:string" dbJavaType="java.lang.String" nillable="true"
minOccurs="0" maxOccurs="1" xpath="/Geochem/Rock/Analysis/Result/Analyte"/>
<xs:element name="Value" type="xs:double" dbJavaType="java.lang.Double" nillable="true" minOccurs="0"
maxOccurs="1" xpath="/Geochem/Rock/Analysis/Result/Value"/>
<xs:element name="Units" type="xs:string" dbJavaType="java.lang.String" nillable="true" minOccurs="0"
maxOccurs="1" xpath="/Geochem/Rock/Analysis/Result/Units"/>
<xs:element name="Error" type="xs:double" dbJavaType="java.lang.Double" nillable="true" minOccurs="0"
maxOccurs="1" xpath="/Geochem/Rock/Analysis/Result/Error"/>
<!-- these should be xlinked - multivalued lookup -->
<xs:element name="DetectionLimit" type="xs:double" dbJavaType="java.lang.Double" nillable="true"
minOccurs="0" maxOccurs="1" xpath="/Geochem/Rock/Analysis/Result/DetectionLimit"/>
<xs:element name="Unit" type="xs:string" dbJavaType="java.lang.String" nillable="true" minOccurs="0"
maxOccurs="1" xpath="/Geochem/Rock/Analysis/Result/Unit"/>
<xs:element name="Precision" type="xs:double" dbJavaType="java.lang.Double" nillable="true"
minOccurs="0" maxOccurs="1" xpath="/Geochem/Rock/Analysis/Result/Precision"/>
<xs:element name="Method" type="xs:string" dbJavaType="java.lang.String" nillable="true" minOccurs="0"
maxOccurs="1" xpath="/Geochem/Rock/Analysis/Result/Method"/>
<xs:element name="Laboratory" type="xs:string" dbJavaType="java.lang.String" nillable="true"
minOccurs="0" maxOccurs="1" xpath="/Geochem/Rock/Analysis/Result/Laboratory"/>
</xs:sequence>
</xs:extension>
</xs:complexContent>
</xs:complexType>