Thread: 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@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
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
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
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
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
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
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
[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
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
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
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
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
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
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
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
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
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
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
> [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
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
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
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
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
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