Avoiding explicit addDataType calls for PostGIS

(Sorry for the weird crosspost, but I think this will be of interest for
all of the receipients. As the suggested Mechanism is a general
technique possibly useful for other postgresql extensions, I suggest to
discuss this item on pgsql-jdbc(at)postgresql(dot)org, although I'll read the
postgis and jts lists as well.)

Hello,

Both PostGIS and JTS (using the JtsGeometry Wrapper Class attached to
this mail) support direct reading of their geometry classes via
ResultSet.getObject(int) method.

But it is annoying that one has to explicitly call the addDataType()
method on every connection at startup. First, this puts PostGIS/JTS
specific code in places that should be driver independend. Second, it
does not play well with connection pooling (jboss etc.) as one always
has to re-add those datatypes on any connection you get.

To circumvent those drawbacks, we introduced the attached GisWrapper
class. This allows to define a dataSource as follows (jboss syntax):

<datasources>
<no-tx-datasource>
<jndi-name>pgds_gis_data</jndi-name>

<connection-url>jdbc:postgresql_postGIS://127.0.0.1:5432/logigis</conne
ction-url>
<driver-class>com.logitrack.gis.util.GisWrapper</driver-class>
<user-name>logi</user-name>
<password>track</password>
</no-tx-datasource>
</datasources>

So by simply changing the subprotocol in the URL, you can add the
PostGIS or JTS specific datatypes to the connection. This first approach
is fine for us, but I can see two further approaches to this problem.

The second approach would add a "wrapper" parameter to the postgresql
driver url. This parameter contains comma-separated class names that all
implement a common interface. On every connect() call, the driver would
then instantiate all of those classes [1], and then pass
the created PGConnection to those classes, before return.

The third approach would add a "datatypes" parameter to the postgresql
driver url. This parameter contains a comma-separated list of type:Class
pairs. Then, Driver.connect() would simply call addDataType for all
those pairs before returning the pgConnection.

The first and second approach have the advantage to be more flexible[2],
while the third one avoids that one has to write a driver wrapper class
when he simply wants to add such additional data types. Both the second
and thid would be a rather small patch to org.postgresql.Driver which I
would develop, test and donate.

Which of the approaches is the one to go? If you (rsp. the pg_jdbc
maintainers) decide that such general code is not useful for pg_jdbc,
I'll donate polished GisWrapper variants and JtsGeometry to the PostGIS
/ JTS projects. If you think that the second or third approach [3] are
okay to be included, I am willing to develop and donate you the patches,
and the appropriate documentation and code including JtsGeometry to
JTS/PostGIS.

Remember that I would prefer the discussion to take place on pg_jdbc
list to avoid splittering over the various lists.

Thanks,
Markus Schaber

(BTW, is it possible that there is no license file in pg-jdbc cvs?)

[1] via reflection, caching would be possible, of course

[2] they also allow for other modifications to the connection, e. G. to
add prepared statements that are used by most clients etc.

[3] or both, they are not mutually exclusive.

--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios(at)logi-track(dot)com | www.logi-track.com