GeoTools

PostgisDataStore doesn't always correctly handle mixed-case table and field names

Details

  • Type: Bug Bug
  • Status: Closed Closed
  • Priority: Major Major
  • Resolution: Fixed
  • Affects Version/s: None
  • Fix Version/s: 2.1.M4
  • Component/s: jdbc-postgis plugin
  • Labels:
    None

Description

I'm not sure that this would be considered a bug, since many people will object that it's better to use lower case names with PostgreSQL, anyway I really need to preserve exact case in tables and field names.
The problem is that in a few places of the PostgisDataStore code (I didn't managed to identify all of them) lower case is assumed, that is no double-quoting is added around names.
This is true particularly in the createSchema() code, but there're other parts affected.
I have a fixed version of PostgisDataStore (albeit it's not general enough to be used as is) that fixed almost all of the above. Where and how can I submit it???

Bye
Paolo Rizzi

Issue Links

is related to

Improvement - An improvement or enhancement to an existing feature or task. GEOT-720 OracleDataStore doesn't always correctly handle mixed-case table and field names

  • Minor - Minor loss of function, or other problem where easy workaround is present.
  • Closed - The issue is considered finished, the resolution is correct. Issues which are not closed can be reopened.
relates to

New Feature - A new feature of the product, which has yet to be developed. GEOT-379 PostgisFIDMapperFactory should be configurable

  • Minor - Minor loss of function, or other problem where easy workaround is present.
  • Closed - The issue is considered finished, the resolution is correct. Issues which are not closed can be reopened.

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Chris Holmes added a comment -
No, I consider it a bug, I agree with you (and I'm the module maintainer ;) ) In my opinion the double quoting does not hurt anyone, and is useful for a number of people. GeoServer relies on the double quoting. All the code that GeoSErver uses I am pretty positive does it correctly. I have never used the createSchema code, but if I did I would probably have fixed it. The patch is definitely welcome, attach it directly to this issue.
Show
Chris Holmes added a comment - No, I consider it a bug, I agree with you (and I'm the module maintainer ;) ) In my opinion the double quoting does not hurt anyone, and is useful for a number of people. GeoServer relies on the double quoting. All the code that GeoSErver uses I am pretty positive does it correctly. I have never used the createSchema code, but if I did I would probably have fixed it. The patch is definitely welcome, attach it directly to this issue.
Hide
Permalink
Chris Holmes added a comment -
linked to 379, which has all the submitted code.
Show
Chris Holmes added a comment - linked to 379, which has all the submitted code.
Hide
Permalink
Chris Holmes added a comment -
I think this issue got in all the way, with the fixes I did apply.
Show
Chris Holmes added a comment - I think this issue got in all the way, with the fixes I did apply.
Hide
Permalink
Paolo Rizzi added a comment -
A table like this:
CREATE TABLE "Foo"
(
  elementostradale_id varchar(32) NOT NULL DEFAULT ''::character varying,
  ord int4 NOT NULL DEFAULT 0,
  lato int4,
  CONSTRAINT pk_foo PRIMARY KEY (ord, elementostradale_id)
}
that has a case sensitive name AND a composite primary key, gives problem.
If you try to add it to GeoServer 1.3.0-beta, for example, you'll get this error:
org.geotools.data.DataSourceException: SQL Error building FeatureType for Disciplina ERROR: relation "foo" does not exist

org.geotools.data.jdbc.fidmapper.DefaultFIDMapperFactory.getPkColumnInfo(DefaultFIDMapperFactory.java:260)
org.geotools.data.jdbc.fidmapper.DefaultFIDMapperFactory.getMapper(DefaultFIDMapperFactory.java:61)
org.geotools.data.jdbc.JDBCDataStore.buildFIDMapper(JDBCDataStore.java:1146)
org.geotools.data.jdbc.FeatureTypeHandler.getFIDMapper(FeatureTypeHandler.java:233)
org.geotools.data.jdbc.FeatureTypeHandler.getFeatureTypeInfo(FeatureTypeHandler.java:199)
org.geotools.data.jdbc.FeatureTypeHandler.getSchema(FeatureTypeHandler.java:168)
org.geotools.data.jdbc.JDBCDataStore.getSchema(JDBCDataStore.java:499)
org.vfny.geoserver.action.data.DataFeatureTypesNewAction.execute(DataFeatureTypesNewAction.java:76)
org.vfny.geoserver.action.ConfigAction.execute(ConfigAction.java:98)
org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:75)

If the table has a defined PK of just one field, all goes well.

Bye
Paolo Rizzi
Show
Paolo Rizzi added a comment - A table like this: CREATE TABLE "Foo" (   elementostradale_id varchar(32) NOT NULL DEFAULT ''::character varying,   ord int4 NOT NULL DEFAULT 0,   lato int4,   CONSTRAINT pk_foo PRIMARY KEY (ord, elementostradale_id) } that has a case sensitive name AND a composite primary key, gives problem. If you try to add it to GeoServer 1.3.0-beta, for example, you'll get this error: org.geotools.data.DataSourceException: SQL Error building FeatureType for Disciplina ERROR: relation "foo" does not exist org.geotools.data.jdbc.fidmapper.DefaultFIDMapperFactory.getPkColumnInfo(DefaultFIDMapperFactory.java:260) org.geotools.data.jdbc.fidmapper.DefaultFIDMapperFactory.getMapper(DefaultFIDMapperFactory.java:61) org.geotools.data.jdbc.JDBCDataStore.buildFIDMapper(JDBCDataStore.java:1146) org.geotools.data.jdbc.FeatureTypeHandler.getFIDMapper(FeatureTypeHandler.java:233) org.geotools.data.jdbc.FeatureTypeHandler.getFeatureTypeInfo(FeatureTypeHandler.java:199) org.geotools.data.jdbc.FeatureTypeHandler.getSchema(FeatureTypeHandler.java:168) org.geotools.data.jdbc.JDBCDataStore.getSchema(JDBCDataStore.java:499) org.vfny.geoserver.action.data.DataFeatureTypesNewAction.execute(DataFeatureTypesNewAction.java:76) org.vfny.geoserver.action.ConfigAction.execute(ConfigAction.java:98) org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484) org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274) org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482) org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525) javax.servlet.http.HttpServlet.service(HttpServlet.java:717) javax.servlet.http.HttpServlet.service(HttpServlet.java:810) org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:75) If the table has a defined PK of just one field, all goes well. Bye Paolo Rizzi
Hide
Permalink
Anonymous added a comment -
The problem seems to be present also if the table has a single field PK that has a non lowercase name, like in:
CREATE TABLE "Foo"
(
  "elementoStradale_id" varchar(32) NOT NULL DEFAULT ''::character varying,
  ord int4 NOT NULL DEFAULT 0,
  lato int4,
  CONSTRAINT pk_foo PRIMARY KEY ("elementoStradale_id")
}

I'll debug a little more and then I'll post a fix.

Bye
Paolo Rizzi
Show
Anonymous added a comment - The problem seems to be present also if the table has a single field PK that has a non lowercase name, like in: CREATE TABLE "Foo" (   "elementoStradale_id" varchar(32) NOT NULL DEFAULT ''::character varying,   ord int4 NOT NULL DEFAULT 0,   lato int4,   CONSTRAINT pk_foo PRIMARY KEY ("elementoStradale_id") } I'll debug a little more and then I'll post a fix. Bye Paolo Rizzi
Hide
Permalink
dblasby added a comment -
This is a good test table:

CREATE TABLE "Foo"
(
  "ELE_ID" varchar(32) NOT NULL DEFAULT ''::character varying,
  "ORD" int4 NOT NULL DEFAULT 0,
  lato int4,
  CONSTRAINT pk_foo PRIMARY KEY ("ORD", "ELE_ID")
);

table and 2 columns requiring escapes.

I have a fix for this, but I cannot build main. Hopefully that will be fixed tomorrow.
Show
dblasby added a comment - This is a good test table: CREATE TABLE "Foo" (   "ELE_ID" varchar(32) NOT NULL DEFAULT ''::character varying,   "ORD" int4 NOT NULL DEFAULT 0,   lato int4,   CONSTRAINT pk_foo PRIMARY KEY ("ORD", "ELE_ID") ); table and 2 columns requiring escapes. I have a fix for this, but I cannot build main. Hopefully that will be fixed tomorrow.
Hide
Permalink
Andrea Aime added a comment -
Mass closing all issues that have been in "resolved" state for 2 months or more without any feedback or update
Show
Andrea Aime added a comment - Mass closing all issues that have been in "resolved" state for 2 months or more without any feedback or update

People

  • Assignee:
    Chris Holmes
    Reporter:
    Paolo Rizzi
Vote (0)
Watch (1)

Dates

  • Created:
    Updated:
    Resolved: