[Geoserver-devel] Oracle-NG fails on Oracle View

GeoServer GeoServer Developer
1

Hi,

There is an issue with OracleNG which is retrograde. I did not have this problem in 1.7.0.

This error …

11 Dec 22:54:04 ERROR [geotools.rendering] - Could not determine fid from primary key

occurs when selecting an Oracle view as a feature type, even when the view is on a single table with a single column primary key.

This is written by JDBCDataStore.getPrimaryKey as a warning.

11 Dec 23:24:01 WARN [geotools.jdbc] - No primary key found for LANDPARCELS.

And the following line states that the feature type was loaded.

11 Dec 23:24:37 INFO [geoserver.catalog] - Loaded feature type ‘topp:LANDPARCELS’

But when accessing the feature via wms JDBCFeatureReader.next() calls

fid = dataStore.encodeFID(pkey,rs);

with a null pkey resulting in the following exception

11 Dec 22:54:04 ERROR [geotools.rendering] - Could not determine fid from primary key

java.lang.RuntimeException: Could not determine fid from primary key

at org.geotools.jdbc.JDBCFeatureReader.next(JDBCFeatureReader.java:265)

.

The obvious issue with this is that “no primary key” is only a warning but later proves fatal.

Five possible solutions:

  1. Easiest – don’t allow views – check the object type and reject submission.

  2. Easy – check for primary key when creating feature and make lack of primary key prevent submission.

  3. Moderate – check object type at creation and allow admin to specify primary key (Competent admins would understand the issues of the specified column not being uniquely indexed).

  4. Moderate – allow no primary key – but also no transactions. An fid should only be needed for WFS.

  5. Hardest – check the view definition and if it is an updateable view, then it has a primary key.

In any case, just relying on the ojdbc call and assuming only tables are submitted by admins will not solve the issue.

Options 1 and 2 are not acceptable real world solutions.

Option 5 may be easier than I think – but at the moment I think it goes in the too-hard basket – there could be many complex scenarios.

Option 4 requires a code change but does not properly solve the problem where there are valid reasons for using a view and the view is and should be updateable.

Option 3, IMHO, offers the only viable solution in the short term.

Regards

Stuart

2

Stuart A. Mitchell ha scritto:

Hi,

There is an issue with OracleNG which is retrograde. I did not have this problem in 1.7.0.

This error ...

11 Dec 22:54:04 ERROR [geotools.rendering] - Could not determine fid from primary key

occurs when selecting an Oracle view as a feature type, even when the view is on a single table with a single column primary key.

Yeah, known issue:
http://jira.codehaus.org/browse/GEOT-2101
I had a test in the works for it, but I first need to work on
this one:
http://jira.codehaus.org/browse/GEOT-2103
And that one requires more time, since we have to split an existing
class in two.

Five possible solutions:

1. Easiest – don’t allow views – check the object type and reject submission.

2. Easy – check for primary key when creating feature and make lack of primary key prevent submission.

3. Moderate – check object type at creation and allow admin to specify primary key (Competent admins would understand the issues of the specified column not being uniquely indexed).

4. Moderate – allow no primary key – but also no transactions. An fid should only be needed for WFS.

5. Hardest – check the view definition and if it is an updateable view, then it has a primary key.

In any case, just relying on the ojdbc call and assuming only tables are submitted by admins will not solve the issue.

Options 1 and 2 are not acceptable real world solutions.

Indeed, they are regressions.

Option 5 may be easier than I think – but at the moment I think it goes in the too-hard basket – there could be many complex scenarios.

Interesting one, no idea how to do that in Oracle... in most other
databases views are not updatable, period.

Option 4 requires a code change but does not properly solve the problem where there are valid reasons for using a view and the view is and should be updateable.

That's what we do in the old datastore, no pk, we generate a random
FID. Read only works fine as long as you don't try to use FID filters.
And that's how I intend to fix it in the short term.

Option 3, IMHO, offers the only viable solution in the short term.

Indeed it's best from the user point of view, but I refuse to work on
the 1.7.x UI :wink:
(12 classes to change only to add a flag in the UI and get it
go down to the configuration, and then you have to make special
changes in the code to deal with jdbc datastores in a special way,
yuk).

Cheers
Andrea

Regards

Stuart

------------------------------------------------------------------------

------------------------------------------------------------------------------
SF.Net email is Sponsored by MIX09, March 18-20, 2009 in Las Vegas, Nevada.
The future of the web can't happen without you. Join us at MIX09 to help
pave the way to the Next Web now. Learn more and register at
http://ad.doubleclick.net/clk;208669438;13503038;i?http://2009.visitmix.com/

------------------------------------------------------------------------

_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

3

Maybe in 2.0 this can be fixed. Oracle and SQL Server both have
updateable views

-----Original Message-----
From: Andrea Aime [mailto:aaime@anonymised.com]
Sent: Friday, 12 December 2008 01:11
To: Stuart A. Mitchell
Cc: geoserver-devel@lists.sourceforge.net
Subject: Re: [Geoserver-devel] Oracle-NG fails on Oracle View

Stuart A. Mitchell ha scritto:

Hi,

There is an issue with OracleNG which is retrograde. I did not have

this

problem in 1.7.0.

This error ...

11 Dec 22:54:04 ERROR [geotools.rendering] - Could not determine fid
from primary key

occurs when selecting an Oracle view as a feature type, even when the
view is on a single table with a single column primary key.

Yeah, known issue:
http://jira.codehaus.org/browse/GEOT-2101
I had a test in the works for it, but I first need to work on
this one:
http://jira.codehaus.org/browse/GEOT-2103
And that one requires more time, since we have to split an existing
class in two.

Five possible solutions:

1. Easiest - don't allow views - check the object type and reject
submission.

2. Easy - check for primary key when creating feature and make lack

of primary key prevent submission.

3. Moderate - check object type at creation and allow admin to
specify primary key (Competent admins would understand the issues of

the

specified column not being uniquely indexed).

4. Moderate - allow no primary key - but also no transactions. An

fid

should only be needed for WFS.

5. Hardest - check the view definition and if it is an updateable
view, then it has a primary key.

In any case, just relying on the ojdbc call and assuming only tables

are

submitted by admins will not solve the issue.

Options 1 and 2 are not acceptable real world solutions.

Indeed, they are regressions.

Option 5 may be easier than I think - but at the moment I think it

goes

in the too-hard basket - there could be many complex scenarios.

Interesting one, no idea how to do that in Oracle... in most other
databases views are not updatable, period.

Option 4 requires a code change but does not properly solve the

problem

where there are valid reasons for using a view and the view is and
should be updateable.

That's what we do in the old datastore, no pk, we generate a random
FID. Read only works fine as long as you don't try to use FID filters.
And that's how I intend to fix it in the short term.

Option 3, IMHO, offers the only viable solution in the short term.

Indeed it's best from the user point of view, but I refuse to work on
the 1.7.x UI :wink:
(12 classes to change only to add a flag in the UI and get it
go down to the configuration, and then you have to make special
changes in the code to deal with jdbc datastores in a special way,
yuk).

Cheers
Andrea

Regards

Stuart

------------------------------------------------------------------------

------------------------------------------------------------------------
------

SF.Net email is Sponsored by MIX09, March 18-20, 2009 in Las Vegas,

Nevada.

The future of the web can't happen without you. Join us at MIX09 to

help

pave the way to the Next Web now. Learn more and register at

http://ad.doubleclick.net/clk;208669438;13503038;i?http://2009.visitmix.
com/

------------------------------------------------------------------------

_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

4

Just came across this message having discovered the same bug myself when attempting to update from 1.6.3 to 1.7.1 Personally I’m not too bothered about being able to edit views but if the feature was to be added I would think it would be sensible to allow the user to specify the ID field. Having a look through the Oracle data dictionary views I can’t immediately find a way to work it out automatically anyway. Finding the primary key for tables is easy using something like:

SELECT *
FROM all_ind_columns
WHERE owner = ‘MY_SCHEMA’ and table_name = ‘MY_TABLE’;

but this doesn’t include views.

You can check if the columns in the view are in fact updateable by using:

SELECT *
FROM all_updatable_columns
WHERE owner = ‘MY_SCHEMA’ and table_name = ‘MY_TABLE’;

but there doesn’t seem to be any way to find the primary key except to read the view definition and check the base tables which would be a massive pain. Better to allow the admin to specify it IMHO.

On another note, when updating to 1.7.1 I couldn’t get any Oracle connections working. After getting the error referred to in the original email for NG I tried OCI and the regular Oracle connection and both times got the message “Test Connection failed” or something similar. Reverting back to 1.6.3 fixed the problem. Is this also a known issue or should I go back and try again?

cheers,

Tom

On Thu, Dec 11, 2008 at 3:41 PM, Stuart A. Mitchell <stuartm@anonymised.com> wrote:

Maybe in 2.0 this can be fixed. Oracle and SQL Server both have
updateable views

-----Original Message-----
From: Andrea Aime [mailto:aaime@anonymised.com]
Sent: Friday, 12 December 2008 01:11
To: Stuart A. Mitchell
Cc: geoserver-devel@lists.sourceforge.net
Subject: Re: [Geoserver-devel] Oracle-NG fails on Oracle View

Stuart A. Mitchell ha scritto:

Hi,

There is an issue with OracleNG which is retrograde. I did not have
this
problem in 1.7.0.

This error …

11 Dec 22:54:04 ERROR [geotools.rendering] - Could not determine fid
from primary key

occurs when selecting an Oracle view as a feature type, even when the
view is on a single table with a single column primary key.

Yeah, known issue:
http://jira.codehaus.org/browse/GEOT-2101
I had a test in the works for it, but I first need to work on
this one:
http://jira.codehaus.org/browse/GEOT-2103
And that one requires more time, since we have to split an existing
class in two.

Five possible solutions:

  1. Easiest - don’t allow views - check the object type and reject
    submission.

  2. Easy - check for primary key when creating feature and make lack

of primary key prevent submission.

  1. Moderate - check object type at creation and allow admin to
    specify primary key (Competent admins would understand the issues of
    the
    specified column not being uniquely indexed).

  2. Moderate - allow no primary key - but also no transactions. An
    fid
    should only be needed for WFS.

  3. Hardest - check the view definition and if it is an updateable
    view, then it has a primary key.

In any case, just relying on the ojdbc call and assuming only tables
are
submitted by admins will not solve the issue.

Options 1 and 2 are not acceptable real world solutions.

Indeed, they are regressions.

Option 5 may be easier than I think - but at the moment I think it
goes
in the too-hard basket - there could be many complex scenarios.

Interesting one, no idea how to do that in Oracle… in most other
databases views are not updatable, period.

Option 4 requires a code change but does not properly solve the
problem
where there are valid reasons for using a view and the view is and
should be updateable.

That’s what we do in the old datastore, no pk, we generate a random
FID. Read only works fine as long as you don’t try to use FID filters.
And that’s how I intend to fix it in the short term.

Option 3, IMHO, offers the only viable solution in the short term.

Indeed it’s best from the user point of view, but I refuse to work on
the 1.7.x UI :wink:
(12 classes to change only to add a flag in the UI and get it
go down to the configuration, and then you have to make special
changes in the code to deal with jdbc datastores in a special way,
yuk).

Cheers
Andrea

Regards

Stuart

SF.Net email is Sponsored by MIX09, March 18-20, 2009 in Las Vegas,
Nevada.
The future of the web can’t happen without you. Join us at MIX09 to
help
pave the way to the Next Web now. Learn more and register at

http://ad.doubleclick.net/clk;208669438;13503038;i?http://2009.visitmix.
com/

Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

–
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

SF.Net email is Sponsored by MIX09, March 18-20, 2009 in Las Vegas, Nevada.
The future of the web can’t happen without you. Join us at MIX09 to help
pave the way to the Next Web now. Learn more and register at
http://ad.doubleclick.net/clk;208669438;13503038;i?http://2009.visitmix.com/

Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

5

Tom (JDi Solutions) ha scritto:

Just came across this message having discovered the same bug myself when attempting to update from 1.6.3 to 1.7.1 Personally I'm not too bothered about being able to edit views but if the feature was to be added I would think it would be sensible to allow the user to specify the ID field.

Yep, agreed, it's definitely something we want to add. It requires
an UI allowing the admin to specify the ID field for views only,
so one that is dynamic enough to recognize the feature type
comes from a jdbc datastore... so something to be worked
on for the GS 2.0 UI.

On another note, when updating to 1.7.1 I couldn't get any Oracle connections working. After getting the error referred to in the original email for NG I tried OCI and the regular Oracle connection and both times got the message "Test Connection failed" or something similar. Reverting back to 1.6.3 fixed the problem. Is this also a known issue or should I go back and try again?

I don't know of known errors related to connections, I can connect
fine from my box? What is the specific error you get in the
geoserver log when that happens?
Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.