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 <
[email protected]> wrote:
>
>
> Maybe in 2.0 this can be fixed. Oracle and SQL Server both have
> updateable views
>
> -----Original Message-----
> From: Andrea Aime [mailto:[email protected]]
> Sent: Friday, 12 December 2008 01:11
> To: Stuart A. Mitchell
> Cc: [email protected]
> 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 ;)
> (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/<http://ad.doubleclick.net/clk;208669438;13503038;i?http://2009.visitmix.com/>
> >
> >
> >
> ------------------------------------------------------------------------
> >
> > _______________________________________________
> > Geoserver-devel mailing list
> > [email protected]
> > 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
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/geoserver-devel
>
------------------------------------------------------------------------------
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
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-devel
