Hi, during the NY OpenGeo code sprint last month we've designed and coded support for what we call "virtual tables", that is, feature types that are defined via a straight, native sql query.
If you cannot wait to see the code there is a patch attached to http://jira.codehaus.org/browse/GEOT-2123. Interaction wise, with the current state of the patch you can define virtual tables either via a factory parameter or with code. The factory parameter is a string in property file format, the definition of a virtual table can look like: myVirtTable=select a, b, c, the_geom from realtable where a > 3 myVirtTable.pk=a,b myVirtTable.geom.the_geom=POINT,4326 The definition states that the "myVirtTable" is defined by the query "select a, b, c, the_geom from realtable where a > 3", that the fids are generated using the columns a and b (it does not mean it's writable, just allows for stable fids), and that the "the_geom" column is geometric, POINT type, and the native srid is 4326. You can add as many definitions as you want in the property file. Programmatically it looks like: VirtualTable vt = new VirtualTable("myVirtTable", "select a, b, c, the_geom from realtable where a > 3"); vt.addGeometryMetadatata("the_geom", Point.class, 4326); vt.setPrimaryKeyColumns(Arrays.asList("a", "b")); dataStore.addVirtualTable(vt); Internally the code takes the SQL provided and builds queries that use the SQL as a subquery in the FROM clause, something we verified all major databases support today. Something like: select <the fields in the Query> from (<your sql>) where <filters in the Query> GeoServer wise I'm not too happy with the usage of the factory parameter and I was considering dropping it. Since we are already planning to have a UI to add new feature types, this would fit squarely as a variant to it, instead of creating a new table you give me a query and some metadata. So it would end up in the "create new layer" workflow. The query and the metadata would be stored in the FeatureTypeInfo metadata map, and we'd need a callback from ResourcePool that allows for configuration of the data store: grab all the feature type info connected to that datastore, scan for view definitions, register them as the datastore is being created. Add and drop dynamically as these are created, removed or updated by the user. Soo... how does it sound? And also, where will it ring? Trunk only? GS wise it would be a nice way to push users to upgrade once GS 2.1 is out. Cheers Andrea -- Andrea Aime OpenGeo - http://opengeo.org Expert service straight from the developers. ------------------------------------------------------------------------------ Download Intel® Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev _______________________________________________ Geoserver-devel mailing list Geoserver-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geoserver-devel
