REST API does not handle SQL Views properly
-------------------------------------------
Key: GEOS-4254
URL: http://jira.codehaus.org/browse/GEOS-4254
Project: GeoServer
Issue Type: Bug
Components: REST
Affects Versions: 2.1-beta2
Reporter: Eli Miller
Assignee: Andrea Aime
Priority: Minor
Attachments: rest-sql-view.patch
I have encountered some problems when creating SQL Views through the REST API. I generally tried to follow the details from GSIP-48 (specifically, the XML syntax noted in http://geoserver.org/display/GEOS/GSIP+48+-+Parametric+SQL+views+in+GeoServer). To illustrate the issue I created some tables in PostGIS:
CREATE TABLE foo (
feature_id INTEGER NOT NULL,
static_attr INTEGER NOT NULL
);
SELECT AddGeometryColumn('foo','the_geom', 4326, 'POLYGON', 2);
INSERT INTO foo (feature_id, static_attr, the_geom) VALUES (1, 1, ST_GeomFromText('POLYGON((-10 -10, -10 10, 10 10, 10 -10, -10 -10))', 4326));
CREATE TABLE data (
feature_id INTEGER NOT NULL,
dyn_attr INTEGER NOT NULL
);
INSERT INTO data (feature_id, dyn_attr) VALUES (1, 2);
and then POSTed the following request to http://localhost:8080/geoserver/rest/workspaces/<ws>/datastores/<store>/featuretypes:
<featureType>
<name>x</name>
<nativeName>x</nativeName>
<srs>EPSG:4326</srs>
<nativeBoundingBox><minx>-10.0</minx><maxx>10.0</maxx><miny>-10.0</miny><maxy>10.0</maxy><crs>EPSG:4326</crs></nativeBoundingBox>
<latLonBoundingBox><minx>-10.0</minx><maxx>10.0</maxx><miny>-10.0</miny><maxy>10.0</maxy><crs>EPSG:4326</crs></latLonBoundingBox>
<attributes/>
<metadata>
<entry key="JDBC_VIRTUAL_TABLE">
<virtualTable><name>x</name><sql>select d.feature_id, d.dyn_attr, f.static_attr, f.the_geom
from data d, foo f
where d.feature_id = f.feature_id</sql>
<keyColumn>feature_id</keyColumn>
<geometry><name>the_geom</name><type>Geometry</type><srid>4326</srid></geometry>
</virtualTable></entry></metadata></featureType>
which results in the following Exception:
2010-12-09 14:35:36,776 ERROR [geoserver.rest] - :Error occurred creating table
2010-12-09 14:35:36,776 ERROR [geoserver.rest] -
org.geoserver.rest.RestletException: java.io.IOException: Error occurred creating table
at org.geoserver.rest.ReflectiveResource.handleException(ReflectiveResource.java:339)
at org.geoserver.rest.ReflectiveResource.handlePost(ReflectiveResource.java:124)
at org.restlet.Finder.handle(Finder.java:296)
at org.geoserver.rest.BeanDelegatingRestlet.handle(BeanDelegatingRestlet.java:38)
...
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:619)
Caused by: java.io.IOException: Error occurred creating table
at org.geotools.jdbc.JDBCDataStore.createSchema(JDBCDataStore.java:658)
at org.geotools.jdbc.JDBCDataStore.createSchema(JDBCDataStore.java:136)
at org.geoserver.catalog.rest.FeatureTypeResource.handleObjectPost(FeatureTypeResource.java:117)
at org.geoserver.rest.ReflectiveResource.handlePost(ReflectiveResource.java:121)
... 222 more
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"
Position: 57
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:345)
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at org.geotools.jdbc.JDBCDataStore.createSchema(JDBCDataStore.java:650)
... 225 more
In the code it appears that if nativeName (or name if nativeName is null) does not refer to an existing feature type then FeatureTypeResource will try to create a new schema (in this case a database table). I presume that a SQL View should never fall into this step. After modifying FeatureTypeResource to skip table creation if there is a metadata entry of JDBC_VIRTUAL_TABLE everything seems to work as expected. I have attached the patch to support this correction. Please let me know if I am overlooking any other steps that are necessary during creation of a SQL View.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.codehaus.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira