Allow GeoServer to use a raw, parametric SQL statement as a data source for WFS/WMS

Andrea Aime

The release that this proposal will be implemented for release 2.1.0

Choose one of: Under Discussion, In Progress, Completed, Rejected, Deferred

GeoServer already supports publishing database views. That is however limiting in a couple of ways:

• in some production enviroments the GIS web services administrator cannot create new views in the database, which has its own administrator (which means going through some bureocracy in order to add/change the view definitions)
• it is not possible to make the source dynamic in terms of what its filtering, it’s not possible to leverage native filtering abilities of the data source

Building upon the GeoTools VirtualTable work done at the New York 2010 code sprint this proposal adds a user interface to configure a sql view as a new layer in the GeoServer catalog.

The workflow without the query parameters looks like the following:

• the user goes into the “new layer”, upon choosing a JDBC data store a new link appears at the bottom of the publishing table allowing to create a new SQL view
• the SQL view page allows to define the query and see what attributes it defines, and allows to specify which attributes are to be used for the identifier generation, as well as to provide detailed information about the geometry and its native database srid
• the layer creation then proceeds as usual, but instead of having the feature type refresh link there is a “edit query” one that leads to a sql view editor
• the editor has the same functionality as the creation page, but does not allow to change the srid (due to limitations in the current catalog editing it’s quite hard to change the native srid after the fact)

In order to handle the views a few GUI classes and internal classes need to be made view aware:

• a VirtualTable definition will be stores inside the FeatureTypeInfo metadata map, and a public key will be declared to access it in a safe manner
• the resource pool will check for the VirtualTable definition when creating new feature types and will add it dynamically to the JDBC data store configuration
• the “new layer” page will avoid listing the sql views as tables that can be republished to make sure we don’t end up having two layers sharing the same view definition but with just one aware of that (a possible alternative solution is to recognize the layer is actually based ona view and clone it instead)
• the storage subsystem needs a new custom serializer for VirtualTable. The stored view xml will end up looking something like:

    <featureType>
     <id>FeatureTypeInfoImpl~~7678d26d:129411513b8:~~7ffe</id>
     <name>mstates</name>
     <nativeName>mstates</nativeName>
     <namespace>
     <id>NamespaceInfoImpl~~~~570ae188:124761b8d78:~~7ff4</id>
     </namespace>
     <title\>mstates</title\>
     <srs>EPSG:4326</srs>
     <nativeBoundingBox>
     <minx>~~116.062</minx>
     <maxx>~~66.97</maxx>
     <miny>30.195</miny>
     <maxy>49.372</maxy>
     </nativeBoundingBox>
     <latLonBoundingBox>
     <minx>~~116.062</minx>
     <maxx>~~66.97</maxx>
     <miny>30.195</miny>
     <maxy>49.372</maxy>
     <crs>EPSG:4326</crs>
     </latLonBoundingBox>
     <projectionPolicy>FORCE*DECLARED</projectionPolicy>
     <enabled>true</enabled>
     <metadata>
     <entry key="cachingEnabled">false</entry>
     <entry key="JDBC_VIRTUAL_TABLE">
     <virtualTable>
     <name>mstates</name>
     <sql>select gid, state*name, persons, the*geom from pgstates where
    STATE*NAME LIKE ‘M%’</sql>
     <keyColumn>gid</keyColumn>
     <geometry>
     <name>the\_geom</name>
     <type>MultiPolygon</type>
     <srid>4326</srid>
     </geometry>
     </virtualTable>
     </entry>
     </metadata>
     <store class="dataStore">
     <id>DataStoreInfoImpl~~62ffd145:12807a326ca:–7ffa</id>
     </store>
     <maxFeatures>0</maxFeatures>
     <numDecimals>0</numDecimals>
    </featureType>

• the rest configuration should be able to handle sql views out of the box using the above xml syntax

h4. Parametric workflow

A parametric sql query will have %param% markers inside of it that need to be replaced with actual values. It will look something like:

select a, b, c
from t1 inner join t2 on t1.id = t2.fk
where t1.class = '%class%'
%extra_condition%

Each parameter needs to be explicitly declared in the sql view definition along with:

• parameter name
• default value if no actual value is provided
• a regular expression to protect against sql injection attacks

The GUI to define views will be modified adding a new table to specify parameter definitions and associated regular expression.

At the lower level the JDBC store will look for the parameters using either the “env” filter function, sharing the same parameter substitution mechanism as SLD, or using a new query hint (the just is still out on geotools-devel about that).

Passing parameters to the views from a GetMap request will use a new "viewparams" parameter which the same structure as formatoptions and env. For the above query passing down the parameters will look like:

http://host:port/geoserver/wms?...&request=GetMap&view_params"=class:vertebrate;extra_condition='value'%20and%20attr2%20in%20(1,2,3) 

The documentation will make it very clear that allowing to pass full sql constructs like in the example opens up opportunities for sql injection attacks.

This section should contain feedback provided by PSC members who may have a problem with the proposal.

State here any backwards compatibility issues.

• Alessio Fabiani +1
• Andrea Aime +1
• Chris Holmes (Chair)
• Jody Garnett +1
• Rob Atkinson +1
• Simone Giannecchini
• Ben Caradoc-Davies +1
• Mark Leslie

JIRA Task Email Discussion Wiki Page