GeoTools

table schema handling does not work

Details

  • Type: Bug Bug
  • Status: Closed Closed
  • Priority: Major Major
  • Resolution: Fixed
  • Affects Version/s: None
  • Fix Version/s: 2.3.1, 2.4.0
  • Component/s: jdbc-db2 plugin
  • Labels:
    None

Description

Omitting a table schema name does not work because of

DB2SpatialCatalog.loadCatalog

builds an SQL Statement

String queryGeom = "SELECT table_schema, table_name, column_name, "
+ " type_name, srs_id" + " FROM db2gse.st_geometry_columns"
+ " WHERE table_schema = '" + schemaName + "' ";

If no Name is given the where Condition resolves to "where table_schema='null'". I think using the userid would fix this.
The next problem is using lower case letters in the schema name because DB2 stores the names as uppercase. Automatically converting
would resolve this problem.

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
David Adler added a comment -
Can you tell me a little more about how you are producing this error? I thought that it was required to specify the schema name when you created a DB2DataStore so the schemaName will not be null.

It is not really appropriate to automatically convert the schema name to uppercase all the time because DB2 supports mixed-case schema names if you specify them in double-quotes for example:
SELECT COUNT(*) FROM "MyMixedSchema"."MyMixedTable"
If you didn't provide the quotes, this would default to MYMIXEDSCHEMA.MYMIXEDTABLE.

This is critical to support non-Western languages in particular.
Show
David Adler added a comment - Can you tell me a little more about how you are producing this error? I thought that it was required to specify the schema name when you created a DB2DataStore so the schemaName will not be null. It is not really appropriate to automatically convert the schema name to uppercase all the time because DB2 supports mixed-case schema names if you specify them in double-quotes for example: SELECT COUNT(*) FROM "MyMixedSchema"."MyMixedTable" If you didn't provide the quotes, this would default to MYMIXEDSCHEMA.MYMIXEDTABLE. This is critical to support non-Western languages in particular.
Hide
Permalink
Christian Mueller added a comment -
The original issue was based on DB2 8.2.4 on AIX 5.3. Now I did some tests on DB2 9.1 FP2 on Linux.

If you connect to a db and issue "db2 select current schema from sysibm.sysdummy1" then your implicit schema name is your username in uppercase.

db2 'select schemaname from syscat.schemata' will not list your name,

but

db2 select current schema from sysibm.sysdummy1

will.

Creating a table with

db2 "create table t (a integer )"

and

db2 "select table_schema from sysibm.tables where table_name = 'T'"

will show your username uppercase.

I think we can agree on the fact that creating a schema without double quotes results in uppercase conversation. Try

drop table t
db2 create schema 'abc'
db2 set current schema abc
db2 db2 "create table t (a integer )"
db2 "select table_schema from sysibm.tables where table_name = 'T'"

shows you ABC

Now try

drop table t
db2 'create schema "xyz"'
db2 'set current schema "xyz"'
db2 db2 "create table t (a integer )"
db2 "select table_schema from sysibm.tables where table_name = 'T'"

shows you xyz (without double quotes)

So I think the following rule would be a solution

1) If no schema name is given set the schema name to the username in uppercase
2) if a schema name is given and it is double quoted, remove the quotes
3) If a not quoted schema name is given convert it to uppercase

I hope this will help you. By the way, does your Datastore suppert db2gse.instersects and all similar functions. ?. I really need them but I did not found something in the source code.















Show
Christian Mueller added a comment - The original issue was based on DB2 8.2.4 on AIX 5.3. Now I did some tests on DB2 9.1 FP2 on Linux. If you connect to a db and issue "db2 select current schema from sysibm.sysdummy1" then your implicit schema name is your username in uppercase. db2 'select schemaname from syscat.schemata' will not list your name, but db2 select current schema from sysibm.sysdummy1 will. Creating a table with db2 "create table t (a integer )" and db2 "select table_schema from sysibm.tables where table_name = 'T'" will show your username uppercase. I think we can agree on the fact that creating a schema without double quotes results in uppercase conversation. Try drop table t db2 create schema 'abc' db2 set current schema abc db2 db2 "create table t (a integer )" db2 "select table_schema from sysibm.tables where table_name = 'T'" shows you ABC Now try drop table t db2 'create schema "xyz"' db2 'set current schema "xyz"' db2 db2 "create table t (a integer )" db2 "select table_schema from sysibm.tables where table_name = 'T'" shows you xyz (without double quotes) So I think the following rule would be a solution 1) If no schema name is given set the schema name to the username in uppercase 2) if a schema name is given and it is double quoted, remove the quotes 3) If a not quoted schema name is given convert it to uppercase I hope this will help you. By the way, does your Datastore suppert db2gse.instersects and all similar functions. ?. I really need them but I did not found something in the source code.
Hide
Permalink
David Adler added a comment -
Hi Christian, all of your observations about how DB2 defaults to upper-case are certainly correct.

The question is how does one handle the case where the user wants the schema name to be lowercase? Is this the intent of your rule #2? This would probably work. I will look at the code to see what is involved in implementing this. I assume you would like to see this in GT2.3.1?

The handling of the various spatial predicates is in org.geotools.data.db2.filter.SQLEncoderDB2.java. These are all part of the JUnit testing. The challenge right now is figuring out how to make the filters work in Trunk/GT2.4.
Show
David Adler added a comment - Hi Christian, all of your observations about how DB2 defaults to upper-case are certainly correct. The question is how does one handle the case where the user wants the schema name to be lowercase? Is this the intent of your rule #2? This would probably work. I will look at the code to see what is involved in implementing this. I assume you would like to see this in GT2.3.1? The handling of the various spatial predicates is in org.geotools.data.db2.filter.SQLEncoderDB2.java. These are all part of the JUnit testing. The challenge right now is figuring out how to make the filters work in Trunk/GT2.4.
Hide
Permalink
Christian Mueller added a comment -
Yes, rule #2 is the interesting thing. The user has to use double quotes. If you create an sql statement you have to use double quotes too if you use schemas in lower case. For example, if you specify th connect params in GeoServer and use a schema in lowercase you must enter the quotes otherwise your input is not deterministic.

I am verifying GeoSever for my company. Geoserver is based on a 3.2.1 snapshot of Geotools (I did not find the source, so I have an workspace with 2.3.0 ). For my part it is ok because I know I have to enter the db2 user name as schema name in uppercase letters. But it was a long way (debugging the source) and I do not think that other guys will be so patient. But it is your decision.

For my part I need to develop a coverage mosaic/pyramid plugin which stores the Tiles in a JDBC DB. (DB2 on AIX in my case)Since the file based plugin has limitiations with very big coverages. First the whole tile indices (stored as shape file) are in the Java Heap causing out of memory Errors, second if have about half a million files on my volumes which is simply to much :-)



Show
Christian Mueller added a comment - Yes, rule #2 is the interesting thing. The user has to use double quotes. If you create an sql statement you have to use double quotes too if you use schemas in lower case. For example, if you specify th connect params in GeoServer and use a schema in lowercase you must enter the quotes otherwise your input is not deterministic. I am verifying GeoSever for my company. Geoserver is based on a 3.2.1 snapshot of Geotools (I did not find the source, so I have an workspace with 2.3.0 ). For my part it is ok because I know I have to enter the db2 user name as schema name in uppercase letters. But it was a long way (debugging the source) and I do not think that other guys will be so patient. But it is your decision. For my part I need to develop a coverage mosaic/pyramid plugin which stores the Tiles in a JDBC DB. (DB2 on AIX in my case)Since the file based plugin has limitiations with very big coverages. First the whole tile indices (stored as shape file) are in the Java Heap causing out of memory Errors, second if have about half a million files on my volumes which is simply to much :-)
Hide
Permalink
David Adler added a comment -
I implemented the change as you suggested and added a JUnit test for all the cases. This has been committed to the GeoTools 2.3 branch. I also made this change to trunk. I haven't backported it to 2.2. Please let me know if you have any problems.

If it works, please close the issue.

I am interested to have coverage support for DB2 but have had no time to think about this.
Show
David Adler added a comment - I implemented the change as you suggested and added a JUnit test for all the cases. This has been committed to the GeoTools 2.3 branch. I also made this change to trunk. I haven't backported it to 2.2. Please let me know if you have any problems. If it works, please close the issue. I am interested to have coverage support for DB2 but have had no time to think about this.
Hide
Permalink
Christian Mueller added a comment -
DB2 Schema Handling works as expected
Show
Christian Mueller added a comment - DB2 Schema Handling works as expected

People

  • Assignee:
    David Adler
    Reporter:
    Christian Mueller
Vote (0)
Watch (0)

Dates

  • Created:
    Updated:
    Resolved: