GeoTools

Oracle NG datastore can not deal with different schema owners

Details

  • Type: Improvement Improvement
  • Status: Open Open
  • Priority: Major Major
  • Resolution: Unresolved
  • Affects Version/s: None
  • Fix Version/s: None
  • Component/s: jdbc-oracle plugin
  • Labels:
    None

Description

Currently the Oracle NG datastore can not deal correctly with the following situation:

X schema owners, which are all accessible by a read-only Oracle user. Using this read-only user I want to access tables from all schema owners in one store.

So the store needs to keep track of the schema owner associated with the tables when doing queries.

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Paul Joyce added a comment -
Other GIS software solves this problem by presenting every Oracle table/view as "schema.name". Perhaps GeoTools could do the same if no schema was specified in the connection. If a schema was specified, it could either continue to present just names, or show "specified_schema.name" for all the tables/views (for consistency with the no-schema situation).

It is not possible to "keep track" of the schema internally, as you may have the same name in multiple schemas. The only was to keep a unique reference is to prefix it with the schema.

From my testing in GeoServer, if there are two Oracle tables with the same name in different schemas, GeoServer only shows one of them.
Show
Paul Joyce added a comment - Other GIS software solves this problem by presenting every Oracle table/view as "schema.name". Perhaps GeoTools could do the same if no schema was specified in the connection. If a schema was specified, it could either continue to present just names, or show "specified_schema.name" for all the tables/views (for consistency with the no-schema situation). It is not possible to "keep track" of the schema internally, as you may have the same name in multiple schemas. The only was to keep a unique reference is to prefix it with the schema. From my testing in GeoServer, if there are two Oracle tables with the same name in different schemas, GeoServer only shows one of them.
Hide
Permalink
Andrea Aime added a comment -
Using the . is problematic, that would not make for a valid WFS layer name. Using _ would be valid though (we need some way to map that to schema and table name, though, however I guess any separator we choose would present that problem as people can include pretty much anything in table names when escaping the names with double quotes)
Show
Andrea Aime added a comment - Using the . is problematic, that would not make for a valid WFS layer name. Using _ would be valid though (we need some way to map that to schema and table name, though, however I guess any separator we choose would present that problem as people can include pretty much anything in table names when escaping the names with double quotes)
Hide
Permalink
Bernhard Kiselka added a comment -
Can't reproduce this issue: creating a layer with a non-schema user works for me in geoserver 2.2.3!
It works both with a write-role (granted rights for select, insert, update and delete on certain objects) as well as a read-role (granted rights for select on certain objects)

But I know there was a problem getting the mdsys-information correctly a long time ago.
Show
Bernhard Kiselka added a comment - Can't reproduce this issue: creating a layer with a non-schema user works for me in geoserver 2.2.3! It works both with a write-role (granted rights for select, insert, update and delete on certain objects) as well as a read-role (granted rights for select on certain objects) But I know there was a problem getting the mdsys-information correctly a long time ago.
Hide
Permalink
Andrea Aime added a comment -
Bernard, the issue happens if you have multiple tables with the same name in different schemas
Show
Andrea Aime added a comment - Bernard, the issue happens if you have multiple tables with the same name in different schemas
Hide
Permalink
Bernhard Kiselka added a comment -
I have a store for each schema. I.e. the schema name is defined in the store. That is why it worked.
Show
Bernhard Kiselka added a comment - I have a store for each schema. I.e. the schema name is defined in the store. That is why it worked.
Hide
Permalink
Andrea Aime added a comment -
Yep, this report is about having a single store without the schema defined, so able to access all schemas (and failing to do so when multiple tables in different schemas have the same name)
Show
Andrea Aime added a comment - Yep, this report is about having a single store without the schema defined, so able to access all schemas (and failing to do so when multiple tables in different schemas have the same name)

People

  • Assignee:
    Andrea Aime
    Reporter:
    Bart van den Eijnden
Vote (0)
Watch (2)

Dates

  • Created:
    Updated: