Thread: Avoiding explicit addDataType calls for PostGIS

Avoiding explicit addDataType calls for PostGIS

From
Markus Schaber
Date:
(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@postgresql.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@logi-track.com | www.logi-track.com

Attachment

Re: Avoiding explicit addDataType calls for PostGIS

From
Kris Jurka
Date:

On Wed, 6 Oct 2004, Markus Schaber wrote:

> 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 problem with the driver instantiating any classes from strings is that
classloaders for the driver may not be able to load the given classes even
though user code could.  Note that PGConnection.addDataType(String type,
String classname) is deprecated for addDataType(String type, Class klass)
to avoid exactly this problem.

That said I understand your frustration and I think your first approach is
rather clever.  Let's see what other people have to say about the other
approaches and the above limitation.

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

Yeah, it used to be part of the main server tree and used that
license, but now that it's out on its own I'll see about adding one.  A
plain BSD license in case you were wondering.

Kris Jurka

Re: Avoiding explicit addDataType calls for PostGIS

From
Oliver Jowett
Date:
Markus Schaber wrote:

> 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.

If your objective is to produce a driver that automatically supports the
extra datatypes, how about a provider-properties-file approach?

i.e. when creating a new connection, the postgresql driver looks for all
resources called postgresql.properties (or whatever) via
ClassLoader.getResources() and reads each in turn. They are interpreted
as properties files, and the resulting properties drive datatype
registration. It could also perhaps be used for other driver
configuration, things like setting the default prepareThreshold etc. in
the absence of URL instructions to the contrary.

Then we can distribute a stock JDBC driver that has
postgresql.properties looking something like:

   postgresql.datatype.box=org.postgresql.geometric.PGbox
   postgresql.datatype.circle=org.postgresql.geometric.PGcircle
   ...

Perhaps we want to interpret postgresql.<a>=<b> identically to a URL
parameter of <a>=<b> to make it more general? This is then essentially
your list-of-datatypes-in-URL approach, with a bit of extra flexibility
so you can easily specify new defaults.

PostGIS/JTS can package the basic JDBC driver together with the
appropriate extra datatype classes and a new postgresql.properties. All
users of that driver then get the new datatypes automatically registered.

You still have the problem that the driver must be able to load the
datatype classes from the driver's classloader, but if you're
repackaging the driver to include extra types, I think that's acceptable
(if you can add a postgresql.properties, you can presumably add the
extra classes too..)

How does that fit with what you need?

(for another example of this approach, see the JNDI resource file docs
at
http://java.sun.com/j2se/1.4.2/docs/api/javax/naming/Context.html#RESOURCEFILES)

-O

Re: Avoiding explicit addDataType calls for PostGIS

From
Markus Schaber
Date:
Hi, Oliver,

On Thu, 07 Oct 2004 10:11:22 +1300
Oliver Jowett <oliver@opencloud.com> wrote:

> If your objective is to produce a driver that automatically supports
> the extra datatypes, how about a provider-properties-file approach?
>
> i.e. when creating a new connection, the postgresql driver looks for
> all resources called postgresql.properties (or whatever) via
> ClassLoader.getResources() and reads each in turn. They are
> interpreted as properties files, and the resulting properties drive
> datatype registration. It could also perhaps be used for other driver
> configuration, things like setting the default prepareThreshold etc.
> in the absence of URL instructions to the contrary.

That is a nice idea that allows to control default configuration without
recompiling, and without explicit code in the app. It bites me that I
missed that idea...

The major advantage would be that, by simply adding the postgis.jar to
the class path, the driver would automagically know the appropriate data
types - no further config needed.

But I wonder how you would handle the case when you have two connections
to GIS databases, where you want the first one to map PostGIS geometry
to JTS Java Objects, while the second one should produce PostGIS JDBC
objects. This example is not as artificial as it sounds, we currently
have two independent applications running in the same app server. You
will need a connection (rsp. datasource config) specific way to decide
which additional datatypes to add.

> Perhaps we want to interpret postgresql.<a>=<b> identically to a URL
> parameter of <a>=<b> to make it more general? This is then essentially
>
> your list-of-datatypes-in-URL approach, with a bit of extra
> flexibility so you can easily specify new defaults.

Interpreting both URL parameters and properties would solve the conflict
problem I mentioned above, as long as one does not rely on the "default"
one because the property file read order may be non-deterministic.

> PostGIS/JTS can package the basic JDBC driver together with the
> appropriate extra datatype classes and a new postgresql.properties.
> All users of that driver then get the new datatypes automatically
> registered.

It is not necessary to mangle the original postgresql.properties. They
simply have to put a postgresql.properties in the appropriate path
(package) in their own jar. The driver then can use getResources() to
get an enumeration of all org/postgres/postgresql.properties that are
anywhere in the CLASSPATH, and loop over them to read all additional
datatypes. Plug&Play as it was intended.

I'm currently not shure, however, whether it's possible to create a
deterministic ordering for the ressource enumeration.

> You still have the problem that the driver must be able to load the
> datatype classes from the driver's classloader, but if you're
> repackaging the driver to include extra types, I think that's
> acceptable (if you can add a postgresql.properties, you can presumably
> add the extra classes too..)

I also think that this is a minor problem, as one always can use
addDataType() or a Driver wrapper class when needing classes from
specific class loaders.

> How does that fit with what you need?

I think it's a very nice idea, and will definitely make the life easier
for most people.

And, combined with the URL param = properties param approach, we can
even handle the above mentioned collision very fine.

Maybe we cannot provide all of the flexibility my footnote [2] in the
original posting said ("they also allow for other modifications to the
connection, e. G. to add prepared statements that are used by most
clients etc."), but it would provide the most elegant solution for my
original problem.


Thanks,
Markus Schaber
--
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@logi-track.com | www.logi-track.com

Re: Avoiding explicit addDataType calls for PostGIS

From
Markus Schaber
Date:
Hi, Kris,

On Wed, 6 Oct 2004 11:40:31 -0500 (EST)
Kris Jurka <books@ejurka.com> wrote:

> > 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 problem with the driver instantiating any classes from strings is that
> classloaders for the driver may not be able to load the given classes even
> though user code could.
>
> Note that PGConnection.addDataType(String type,
> String classname) is deprecated for addDataType(String type, Class klass)
> to avoid exactly this problem.

Is the opposite problem possible? I think of the driver class loader be
able to reach the driver extension, but not the user code class loader.

> That said I understand your frustration and I think your first approach is
> rather clever.  Let's see what other people have to say about the other
> approaches and the above limitation.

My driver wrapper approach even has the advantage that it can work with
different class loaders, as you can arbitrarily code whatever you want.

> > (BTW, is it possible that there is no license file in pg-jdbc cvs?)
>
> Yeah, it used to be part of the main server tree and used that
> license, but now that it's out on its own I'll see about adding one.  A
> plain BSD license in case you were wondering.

I thought it to be like that. However, adding the license file to the
tree will simply remove all clues.

Thanks,
Markus Schaber

--
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@logi-track.com | www.logi-track.com

Re: Avoiding explicit addDataType calls for PostGIS

From
Oliver Jowett
Date:
Markus Schaber wrote:

> Is the opposite problem possible? I think of the driver class loader be
> able to reach the driver extension, but not the user code class loader.

That seems unlikely; it'd only happen if you had user code that did not
contain any references to the extension classes. If there are no
references to the extensions, how does the user code use the returned
objects? Reflection, or a common superclass shared by both classloaders,
seem like the only options.

It's also debatable whether the driver should even allow user code to
cause loading and instantiation of classes that the user code would not
otherwise be able to access..

-O

Problem with boolean type

From
"Johann Robette"
Date:
Hi,

I'm using Postgres 8.0 beta 3 and the appropriate JDBC Driver.

I use EJB to create a record in a table containing a Boolean field
"answered".
The EJB method expects a Boolean object so I pass new Boolean(false) in
order to create it as false.

But I get the following error :
java.sql.SQLException: ERROR: column "answered" is of type boolean but
expression is of type text


Any help will be greatly appreciated.

JR



Re: Avoiding explicit addDataType calls for PostGIS

From
Markus Schaber
Date:
[Sorry for the duplicate message, Oliver, my fist mail was
unintentionally sent privately to you]

Hi, Oliver,

On Thu, 07 Oct 2004 23:31:25 +1300
Oliver Jowett <oliver@opencloud.com> wrote:

> > Is the opposite problem possible? I think of the driver class loader be
> > able to reach the driver extension, but not the user code class loader.
>
> That seems unlikely; it'd only happen if you had user code that did not
> contain any references to the extension classes. If there are no
> references to the extensions, how does the user code use the returned
> objects? Reflection, or a common superclass shared by both classloaders,
> seem like the only options.

It is also possible that the classes were known at compile time, and are
used read-only at run-time. That means that the user does not create
instances itsself, but only gets them from the db.

But I assume this are academic examples.

> It's also debatable whether the driver should even allow user code to
> cause loading and instantiation of classes that the user code would not
> otherwise be able to access..

That's a good question.

So, from the security point, we should scan the ressources for possible
classes (not eliminating collisions), and then filter all classes the
user gives by name (via addDataType, URL or whatever) wether they are
allowed (they exist in the ressources file).

Markus

--
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@logi-track.com | www.logi-track.com

Re: Problem with boolean type

From
Kris Jurka
Date:

On Thu, 7 Oct 2004, Johann Robette wrote:

> I use EJB to create a record in a table containing a Boolean field
> "answered".
> The EJB method expects a Boolean object so I pass new Boolean(false) in
> order to create it as false.
>
> But I get the following error :
> java.sql.SQLException: ERROR: column "answered" is of type boolean but
> expression is of type text

I don't see how that could be happening.  The attached test works fine for
me and exercises every way I see to set a boolean.  Perhaps the EJB is
internally converting this to a setString() call?  Could you investigate
more into what actual driver calls are being made?

Kris Jurka

Attachment

Re: Problem with boolean type

From
Oliver Jowett
Date:
Johann Robette wrote:

> I'm using Postgres 8.0 beta 3 and the appropriate JDBC Driver.

What is the "appropriate JDBC driver"?

> I use EJB to create a record in a table containing a Boolean field
> "answered".
> The EJB method expects a Boolean object so I pass new Boolean(false) in
> order to create it as false.
>
> But I get the following error :
> java.sql.SQLException: ERROR: column "answered" is of type boolean but
> expression is of type text

Do you have access to the code that is calling the JDBC driver? Can you
show us that code?

-O

Re: Avoiding explicit addDataType calls for PostGIS

From
Oliver Jowett
Date:
Markus Schaber wrote:
> Hi, Oliver,
>
> On Thu, 07 Oct 2004 10:11:22 +1300
> Oliver Jowett <oliver@opencloud.com> wrote:
>
>>If your objective is to produce a driver that automatically supports
>>the extra datatypes, how about a provider-properties-file approach?
>
> That is a nice idea that allows to control default configuration without
> recompiling, and without explicit code in the app.

Here is a first cut at implementing this.

The driver looks for resources named "postgresql.properties" in its
classloader, loads them as property files in the order the classloader
returns them, and uses the result as default properties. These defaults
are overridden in turn by user-provided properties and properties
extracted from the connection URL.

Properties of the form datatype.<typename>=<classname> cause the given
class to be loaded and registered via addDataType. The default set of
datatypes (interval, money, line, etc) are registered in this way by a
default postgresql.properties included in the driver jar.

I haven't looked at preventing user properties from causing classloading
yet.. don't know if it is worth it.

Also I have not investigated the exact order of resources that
ClassLoader.getResources() returns; it might need some tuning.

-O
Index: org/postgresql/Driver.java.in
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/Driver.java.in,v
retrieving revision 1.48
diff -u -c -r1.48 Driver.java.in
*** org/postgresql/Driver.java.in    10 Oct 2004 15:39:30 -0000    1.48
--- org/postgresql/Driver.java.in    12 Oct 2004 04:35:56 -0000
***************
*** 15,20 ****
--- 15,21 ----
  import java.io.*;
  import java.sql.*;
  import java.util.*;
+ import java.net.URL;

  import org.postgresql.util.PSQLException;
  import org.postgresql.util.PSQLState;
***************
*** 66,71 ****
--- 67,95 ----
          }
      }

+     //
+     // Helper to retrieve default properties from classloader resource
+     // properties files.
+     //
+     private Properties defaultProperties;
+     synchronized Properties getDefaultProperties() throws IOException {
+         if (defaultProperties != null)
+             return defaultProperties;
+
+         Properties merged = new Properties();
+
+         Enumeration urls = getClass().getClassLoader().getResources("postgresql.properties");
+         while (urls.hasMoreElements()) {
+             URL url = (URL)urls.nextElement();
+             InputStream is = url.openStream();
+             merged.load(is);
+             is.close();
+         }
+
+         defaultProperties = merged;
+         return defaultProperties;
+     }
+
      /*
       * Try to make a database connection to the given URL.    The driver
       * should return "null" if it realizes it is the wrong kind of
***************
*** 125,132 ****
       */
      public java.sql.Connection connect(String url, Properties info) throws SQLException
      {
!         Properties props;
!         if ((props = parseURL(url, info)) == null)
          {
              if (Driver.logDebug)
                  Driver.debug("Error in url" + url);
--- 149,172 ----
       */
      public java.sql.Connection connect(String url, Properties info) throws SQLException
      {
!         // get defaults
!         Properties defaults;
!         try {
!             defaults = getDefaultProperties();
!         } catch (IOException ioe) {
!             throw new PSQLException(GT.tr("Error loading default settings from postgresql.properties"),
!                                     PSQLState.UNEXPECTED_ERROR, ioe);
!         }
!
!         // override defaults with provided properties
!         Properties props = new Properties(defaults);
!         for (Enumeration e = info.propertyNames(); e.hasMoreElements(); ) {
!             String propName = (String)e.nextElement();
!             props.put(propName, info.getProperty(propName));
!         }
!
!         // parse URL and add more properties
!         if ((props = parseURL(url, props)) == null)
          {
              if (Driver.logDebug)
                  Driver.debug("Error in url" + url);
Index: org/postgresql/jdbc2/AbstractJdbc2Connection.java
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2Connection.java,v
retrieving revision 1.18
diff -u -c -r1.18 AbstractJdbc2Connection.java
*** org/postgresql/jdbc2/AbstractJdbc2Connection.java    10 Oct 2004 15:39:40 -0000    1.18
--- org/postgresql/jdbc2/AbstractJdbc2Connection.java    12 Oct 2004 04:35:57 -0000
***************
*** 93,103 ****
          prepareThreshold = 0;
          try {
              prepareThreshold = Integer.parseInt(info.getProperty("prepareThreshold", "0"));
          } catch (Exception e) {}
!
!         if (prepareThreshold < 0)
!             prepareThreshold = 0;
!
          //Print out the driver version number
          if (Driver.logInfo)
              Driver.info(Driver.getVersion());
--- 93,102 ----
          prepareThreshold = 0;
          try {
              prepareThreshold = Integer.parseInt(info.getProperty("prepareThreshold", "0"));
+             if (prepareThreshold < 0)
+                 prepareThreshold = 0;
          } catch (Exception e) {}
!
          //Print out the driver version number
          if (Driver.logInfo)
              Driver.info(Driver.getVersion());
***************
*** 118,124 ****
          rollbackQuery = getQueryExecutor().createSimpleQuery("ROLLBACK");

          // Initialize object handling
!         initObjectTypes();
      }

      /*
--- 117,123 ----
          rollbackQuery = getQueryExecutor().createSimpleQuery("ROLLBACK");

          // Initialize object handling
!         initObjectTypes(info);
      }

      /*
***************
*** 417,444 ****
      // This holds the available types, a String to Class mapping.
      private final HashMap objectTypes = new HashMap();

-     // This array contains the types that are supported as standard.
-     //
-     // The first entry is the types name on the database, the second
-     // the full class name of the handling class.
-     //
-     private static final Object[][] defaultObjectTypes = {
-         { "box",      org.postgresql.geometric.PGbox.class     },
-         { "circle",   org.postgresql.geometric.PGcircle.class  },
-         { "line",     org.postgresql.geometric.PGline.class    },
-         { "lseg",     org.postgresql.geometric.PGlseg.class    },
-         { "path",     org.postgresql.geometric.PGpath.class    },
-         { "point",    org.postgresql.geometric.PGpoint.class   },
-         { "polygon",  org.postgresql.geometric.PGpolygon.class },
-         { "money",    org.postgresql.util.PGmoney.class        },
-         { "interval", org.postgresql.util.PGInterval.class     }
-     };
-
      // This initialises the objectTypes hashtable
!     private void initObjectTypes() throws SQLException
      {
!         for (int i = 0; i < defaultObjectTypes.length; ++i)
!             addDataType((String) defaultObjectTypes[i][0], (Class) defaultObjectTypes[i][1]);
      }

      /**
--- 416,441 ----
      // This holds the available types, a String to Class mapping.
      private final HashMap objectTypes = new HashMap();

      // This initialises the objectTypes hashtable
!     private void initObjectTypes(Properties info) throws SQLException
      {
!         for (Enumeration e = info.propertyNames(); e.hasMoreElements(); ) {
!             String propertyName = (String)e.nextElement();
!             if (propertyName.startsWith("datatype.")) {
!                 String typeName = propertyName.substring(9);
!                 String className = info.getProperty(propertyName);
!                 Class klass;
!
!                 try {
!                     klass = Class.forName(className);
!                 } catch (ClassNotFoundException cnfe) {
!                     throw new PSQLException(GT.tr("Unable to load the class {0} responsible for the datatype {1}",
newObject[] { className, typeName }), 
!                                             PSQLState.SYSTEM_ERROR, cnfe);
!                 }
!
!                 addDataType(typeName, klass);
!             }
!         }
      }

      /**
Index: build.xml
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/build.xml,v
retrieving revision 1.52
diff -u -c -r1.52 build.xml
*** build.xml    10 Oct 2004 15:39:29 -0000    1.52
--- build.xml    12 Oct 2004 04:35:57 -0000
***************
*** 105,110 ****
--- 105,111 ----
        </fileset>

        <fileset dir="${srcdir}">
+         <include name="postgresql.properties"/>
          <include name="${package}/translation/*.class" />
        </fileset>
      </jar>
*** /dev/null    Mon Feb  2 17:15:26 2004
--- postgresql.properties    Tue Oct 12 17:08:52 2004
***************
*** 0 ****
--- 1,16 ----
+ #
+ # This property file is included in the driver jar and controls the default
+ # driver settings. These values may be overridden or added to by placing
+ # additional property files (also named 'postgresql.properties') in the
+ # driver's classpath.
+ #
+ prepareThreshold=5
+ datatype.box=org.postgresql.geometric.PGbox
+ datatype.circle=org.postgresql.geometric.PGcircle
+ datatype.line=org.postgresql.geometric.PGline
+ datatype.lseg=org.postgresql.geometric.PGlseg
+ datatype.path=org.postgresql.geometric.PGpath
+ datatype.point=org.postgresql.geometric.PGpoint
+ datatype.polygon=org.postgresql.geometric.PGpolygon
+ datatype.money=org.postgresql.util.PGmoney
+ datatype.interval=org.postgresql.util.PGInterval

Re: Avoiding explicit addDataType calls for PostGIS

From
Markus Schaber
Date:
Hi, Oliver,

On Tue, 12 Oct 2004 17:55:27 +1300
Oliver Jowett <oliver@opencloud.com> wrote:

> >>If your objective is to produce a driver that automatically supports
> >>the extra datatypes, how about a provider-properties-file approach?
> >
> > That is a nice idea that allows to control default configuration without
> > recompiling, and without explicit code in the app.
>
> Here is a first cut at implementing this.

Great - just what I always dreamed of :-)

And it was on my agenda today to code this - now I can start with testing :-)

Thanks a lot!

Markus

--
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@logi-track.com | www.logi-track.com

Re: Avoiding explicit addDataType calls for PostGIS

From
Kris Jurka
Date:

On Tue, 12 Oct 2004, Oliver Jowett wrote:

> > That is a nice idea that allows to control default configuration without
> > recompiling, and without explicit code in the app.
>
> Here is a first cut at implementing this.
>

This generally looks good, but I was hoping Markus would do some of the
serious testing to see if it meets the needs of his real world app.
Particularly the question about the order the property files are found and
processed in.  Additionally the claim that two applications running on the
same JVM want to use two different mappings, I'm not sure that's possible.

Some simpler questions I had are:

1) Is the name postgresql.properties with no package name a good idea?  It
doesn't seem ideal for an application to have to create an org/postgresql
directory just to hold a properties file, but I don't like the idea of
invading a global namespace.

2) I notice you've set the default prepareThreshold to five as we agreed
to a while ago, but I haven't gotten around to doing.  The reason I
haven't done it yet is because I was concerned about how to do this (and
keep it in sync) for the DataSource implementations.  With this patch it
is impossible to set the prepareThreshold back to zero from the
DataSource.  It would be a simple fix to change the DataSource code to
make this work, but since we're discussing the general ability to set
defaults I thought I'd bring it up.

Kris Jurka

Re: Avoiding explicit addDataType calls for PostGIS

From
Markus Schaber
Date:
Hi, Kris & Oliver,

On Tue, 19 Oct 2004 01:57:47 -0500 (EST)
Kris Jurka <books@ejurka.com> wrote:

> > > That is a nice idea that allows to control default configuration without
> > > recompiling, and without explicit code in the app.
> >
> > Here is a first cut at implementing this.
>
> This generally looks good, but I was hoping Markus would do some of the
> serious testing to see if it meets the needs of his real world app.

Sorry, I was a little busy the last days, but I tested the code, and it
worked fine. As we have the PostGIS and JTS jars in the jboss /lib
directory (just aside the pgjdbc driver), all application classloaders
are fine to load them. It allows us to use the JBoss connection pooling
without any weird hacks.

> Particularly the question about the order the property files are found and
> processed in.  Additionally the claim that two applications running on the
> same JVM want to use two different mappings, I'm not sure that's possible.

It works, although you cannot always determine the default mapping as
soon as several extensions provide mappings for the same postgres type.
However, most users will not use both extensions at the same time.

But as the Properties Map and the URL parameters override the mappings
given there, it is possible on a per connection base to determine which
type of mapping you want (and even to override the default mappings). We
currently do this in the jboss pooling config files, just using
different datasource names for different mappings.

I just created a small test app (see attachment) that demonstrates this,
it produces the following output:

--- snip ---
Loading: PostgreSQL 8.0develschabi JDBC3 with SSL (build 306)
Testing: jdbc:postgresql://127.0.0.1:5432/logigis
Got: org.postgis.PGgeometry = POINT (42.0 23.0)

Testing: jdbc:postgresql://127.0.0.1:5432/logigis?datatype.geometry=org.postgis.PGgeometry
Got: org.postgis.PGgeometry = POINT (42.0 23.0)

Testing: jdbc:postgresql://127.0.0.1:5432/logigis?datatype.geometry=com.logitrack.gis.util.JtsGeometry
Got: com.logitrack.gis.util.JtsGeometry = POINT (42 23)
--- snap ---

The first test (without any explicit type) correctly indicates that I
have a postgresql.properties file defining the PostGIS class . When I
remove the file, test 2 and 3 are identical, but the first test spits
out the following text (as expected):

--- snip ---
Testing: jdbc:postgresql://127.0.0.1:5432/logigis
Got: org.postgresql.util.PGobject = SRID=-1;POINT(42 23)
--- snap ---

> Some simpler questions I had are:
>
> 1) Is the name postgresql.properties with no package name a good idea?  It
> doesn't seem ideal for an application to have to create an org/postgresql
> directory just to hold a properties file, but I don't like the idea of
> invading a global namespace.

I agree, moving the file to org/postgresql/ would be a good idea. As my
mail from 7 Oct 2004 10:46:03 shows, this was my original interpretation
of Olivers proposal. Note that, typically, not the app will deliver the
property files, but the extension writers like PostGIS and/or JTS.

> 2) I notice you've set the default prepareThreshold to five as we agreed
> to a while ago, but I haven't gotten around to doing.  The reason I
> haven't done it yet is because I was concerned about how to do this (and
> keep it in sync) for the DataSource implementations.  With this patch it
> is impossible to set the prepareThreshold back to zero from the
> DataSource.  It would be a simple fix to change the DataSource code to
> make this work, but since we're discussing the general ability to set
> defaults I thought I'd bring it up.

I think it is possible, using URL parameters or Properties in the
Datasource definition.

Markus

--
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@logi-track.com | www.logi-track.com

Attachment

Re: Avoiding explicit addDataType calls for PostGIS

From
Oliver Jowett
Date:
Kris Jurka wrote:

> 1) Is the name postgresql.properties with no package name a good idea?  It
> doesn't seem ideal for an application to have to create an org/postgresql
> directory just to hold a properties file,

Yes, that's what I was trying to avoid.

> but I don't like the idea of  invading a global namespace.

We should be OK so long as we don't get a conflict with some other
postgresql-related java application (who else is going to use that
name?) We could call it 'postgresql.jdbc-driver.properties' or something
if you like.

> 2) I notice you've set the default prepareThreshold to five as we agreed
> to a while ago, but I haven't gotten around to doing.  The reason I
> haven't done it yet is because I was concerned about how to do this (and
> keep it in sync) for the DataSource implementations.  With this patch it
> is impossible to set the prepareThreshold back to zero from the
> DataSource.  It would be a simple fix to change the DataSource code to
> make this work, but since we're discussing the general ability to set
> defaults I thought I'd bring it up.

This is a bit of a can of worms..

The datasource implementations don't currently provide access to many of
the useful properties the driver has.

The ones it does support are handled via generating an appropriate URL
to pass to getConnection(). This is going to get unwieldly very fast.
I'd suggest using the Properties variant of getConnection() to pass
extra options in.

How should defaults interact with datasource settings? If you serialize
a configured datasource and later deserialize it in an environment where
the driver defaults are different, do you get the updated defaults for
values you didn't set on the datasource, or do you get the defaults at
the time the object was serialized?

Should we distinguish "not set" from "set to X" for all properties? This
plays more nicely with defaults ("not set" means "use default") but also
means we can't easily use primitive types in the accessors (e.g.
get/setPrepareThreshold would have to deal in something other than a
bare 'int'). This also seems necessary for things like the ssl property
in its current form.

Should it be possible to reset a datasource property to "not set" after
setting it to something else?

If you call a getter on a datasource when the underlying value is
defaulted (i.e. not explicitly set) should you get a result meaning
"this value is at whatever the default is" or should the datasource look
up the actual default that would be used if you created a connection
right now and return that?

For the datatype.* properties, how do we map them to datasource
accessors? Having many separate properties is nice from the point of
view of being able to incrementally add to the property via different
defaults files, but it's nasty to map to a JavaBean-like accessors (and
also makes Driver.getPropertyInfo() impossible to completely implement).
Perhaps an array-based accessor, one element per datatype?
Alternatively, collapse all the datatype stuff down to a single property
and teach the property-munging code how to merge (rather than replace)
multiple settings of that property together. (this second option is more
like what JNDI property files do)

phew.

-O

Re: Avoiding explicit addDataType calls for PostGIS

From
Markus Schaber
Date:
Hi, Oliver,

On Wed, 20 Oct 2004 10:36:39 +1300
Oliver Jowett <oliver@opencloud.com> wrote:

> > 1) Is the name postgresql.properties with no package name a good idea?  It
> > doesn't seem ideal for an application to have to create an org/postgresql
> > directory just to hold a properties file,
>
> Yes, that's what I was trying to avoid.

But, usually, it will be extension and library writers and not the
application writers that provide the postgresql.properties file.

I think most applications will use URL or Properties to express their
specific wishes.

So I'd prefer to put it into org/postgresql.

> For the datatype.* properties, how do we map them to datasource
> accessors? Having many separate properties is nice from the point of
> view of being able to incrementally add to the property via different
> defaults files, but it's nasty to map to a JavaBean-like accessors (and
> also makes Driver.getPropertyInfo() impossible to completely implement).

Well, we could get nearer to a perfect getPropertyInfo() when we take
all the datatype.* declarations we read from the properties files. This
way, any extension completes the list with its own datatypes, and a
developer / admin can add further ones if needed. (Maybe we should allow
empty datatypes.type declarations for this case).

> Perhaps an array-based accessor, one element per datatype?

Or a Map, so we can index via name.

> Alternatively, collapse all the datatype stuff down to a single property
> and teach the property-munging code how to merge (rather than replace)
> multiple settings of that property together. (this second option is more
> like what JNDI property files do)

This sounds cleaner, IMHO. This would produce (and allow) to specify
something like datatypes=geom:org.postgis.PGgeometry,blubb=com.foo.bar

Should I try to change the patch this way?

Thanks,
Markus
--
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@logi-track.com | www.logi-track.com

Re: Avoiding explicit addDataType calls for PostGIS

From
Oliver Jowett
Date:
Markus Schaber wrote:

>>Perhaps an array-based accessor, one element per datatype?
>
> Or a Map, so we can index via name.

This doesn't play so well with JavaBeans. It has mechanisms for handling
array-based accessors, but a Map is just a serializable object..

>>Alternatively, collapse all the datatype stuff down to a single property
>>and teach the property-munging code how to merge (rather than replace)
>>multiple settings of that property together. (this second option is more
>>like what JNDI property files do)
>
> This sounds cleaner, IMHO. This would produce (and allow) to specify
> something like datatypes=geom:org.postgis.PGgeometry,blubb=com.foo.bar
>
> Should I try to change the patch this way?

If you have time that'd be appreciated. I'm short on time and recovering
from illness right now :(

-O

Re: Avoiding explicit addDataType calls for PostGIS

From
Markus Schaber
Date:
Hi, Oliver,

On Thu, 04 Nov 2004 09:04:54 +1300
Oliver Jowett <oliver@opencloud.com> wrote:

> >>Alternatively, collapse all the datatype stuff down to a single property
> >>and teach the property-munging code how to merge (rather than replace)
> >>multiple settings of that property together. (this second option is more
> >>like what JNDI property files do)
> >
> > This sounds cleaner, IMHO. This would produce (and allow) to specify
> > something like datatypes=geom:org.postgis.PGgeometry,blubb=com.foo.bar
> >
> > Should I try to change the patch this way?
>
> If you have time that'd be appreciated.

I'll see how far I get, it's rather busy at work those days.

Maybe it even makes sense to split the patch into two parts: The general
property work, and the invention of the datatypes property.

> I'm short on time and recovering
> from illness right now :(

Get well soon!

Thanks for your efforts,
Markus Schaber
--
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@logi-track.com | www.logi-track.com

Re: Avoiding explicit addDataType calls for PostGIS

From
Kris Jurka
Date:

> [use a property file to configure datatypes]
>

Looking at this patch again, I don't believe the JDBC driver should use
this extension mechanism to configure itself.  The whole concept is error
prone with multiple .properties files, so why should we start things off
by creating a properties file when we don't need to.

Kris Jurka

Re: Avoiding explicit addDataType calls for PostGIS

From
Oliver Jowett
Date:
Kris Jurka wrote:
>
>>[use a property file to configure datatypes]
>
> Looking at this patch again, I don't believe the JDBC driver should use
> this extension mechanism to configure itself.  The whole concept is error
> prone with multiple .properties files,

I don't see multiple properties files with overlapping settings being a
common use case for exactly that reason.

> so why should we start things off
> by creating a properties file when we don't need to.

The configuration information is bulky, so cramming it into the URL
doesn't seem ideal.

It's also cleaner from a packaging perspective: you don't need to poke
application-specific internal configuration information into a
user-visible or user-configured URL.

-O

Re: Avoiding explicit addDataType calls for PostGIS

From
Kris Jurka
Date:

On Mon, 8 Nov 2004, Oliver Jowett wrote:

> Kris Jurka wrote:
> >
> >>[use a property file to configure datatypes]
> >
> > Looking at this patch again, I don't believe the JDBC driver should use
> > this extension mechanism to configure itself.  The whole concept is error
> > prone with multiple .properties files,
>
> I don't see multiple properties files with overlapping settings being a
> common use case for exactly that reason.
>
> > so why should we start things off
> > by creating a properties file when we don't need to.
>
> The configuration information is bulky, so cramming it into the URL
> doesn't seem ideal.
>
> It's also cleaner from a packaging perspective: you don't need to poke
> application-specific internal configuration information into a
> user-visible or user-configured URL.
>

Let me be more clear.  I like the properties file method for configuring
external libraries (postgis), but I don't like it for configuring the
driver itself.  Specifically consider your addition of prepareThreshold=5,
suppose I wanted to override this setting with my own properties file.
Then I'd have to be real careful about how I setup my classpath.

Kris Jurka

Re: Avoiding explicit addDataType calls for PostGIS

From
Oliver Jowett
Date:
Kris Jurka wrote:

> Let me be more clear.  I like the properties file method for configuring
> external libraries (postgis), but I don't like it for configuring the
> driver itself.  Specifically consider your addition of prepareThreshold=5,
> suppose I wanted to override this setting with my own properties file.
> Then I'd have to be real careful about how I setup my classpath.

Ah, ok.

Currently, if someone wants to change a default, they have to either
modify the driver source and recompile, or remember to override the
built-in default in every single URL ever used.

If the default is stored in a properties file packaged with the driver,
they can tweak the default by editing that properties file, which seems
much easier.

I agree that trying to manage multiple properties files that override
each other is confusing, because it's so dependent on what the
classloader decides to do. So don't do that!

-O

Re: Avoiding explicit addDataType calls for PostGIS

From
Kris Jurka
Date:

On Mon, 8 Nov 2004, Oliver Jowett wrote:

> Kris Jurka wrote:
>
> > Let me be more clear.  I like the properties file method for configuring
> > external libraries (postgis), but I don't like it for configuring the
> > driver itself.  Specifically consider your addition of prepareThreshold=5,
> > suppose I wanted to override this setting with my own properties file.
> > Then I'd have to be real careful about how I setup my classpath.
>
> Ah, ok.
>
> Currently, if someone wants to change a default, they have to either
> modify the driver source and recompile, or remember to override the
> built-in default in every single URL ever used.
>
> If the default is stored in a properties file packaged with the driver,
> they can tweak the default by editing that properties file, which seems
> much easier.

We're getting closer to understanding each other.  I've just committed
a version of this patch that reads all available
org.postgresql.driverconfig.properties files and uses them to set any
defaults.  My whole point is that I did not want to put a
driverconfig.properties file in postgresql.jar because we don't need to
and it will only create potential problems.

Kris Jurka

Re: Avoiding explicit addDataType calls for PostGIS

From
Markus Schaber
Date:
Hi, Kris,

On Mon, 8 Nov 2004 04:54:39 -0500 (EST)
Kris Jurka <books@ejurka.com> wrote:

> We're getting closer to understanding each other.  I've just committed
> a version of this patch that reads all available
> org.postgresql.driverconfig.properties files and uses them to set any
> defaults.  My whole point is that I did not want to put a
> driverconfig.properties file in postgresql.jar because we don't need to
> and it will only create potential problems.

So we continue to have the defaults compiled in, instead of delivering
them with a properties file, but parse third-party property files.

I thing that's a good decision, I think.

Thanks a lot,
Markus

--
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@logi-track.com | www.logi-track.com