SQL to auto discover PK fields on views when JDBC getPrimaryKeys fails
----------------------------------------------------------------------
Key: GEOS-3617
URL: http://jira.codehaus.org/browse/GEOS-3617
Project: GeoServer
Issue Type: Improvement
Components: Oracle
Affects Versions: 2.0.0
Environment: OracleNG
Reporter: Bryan Hall
Assignee: Andrea Aime
Priority: Minor
When using views to access data in a table, the JDBC getPrimaryKeys call fails to indicate the correct PK column - sometimes. I have not been able to determine why it works sometimes, and not others - and assume it is an Oracle bug.
Here is SQL that could be used to probe the db to return the PK (if any) when the getPrimaryKeys call fails. It requires no special privilages, and should just be called by the view schema owner. As long as the view and base table use the same name for the column, it will work. Substitute the desired values for 'GEOSERVER_RO' and 'AIRFIELD_SURFACE_AREA':
WITH source_table
AS (SELECT b.owner, b.object_name
FROM sys.ALL_OBJECTS a,
sys.ALL_OBJECTS b,
( SELECT object_id, referenced_object_id
FROM public_dependency
START WITH object_id =
(SELECT object_id
FROM sys.ALL_OBJECTS
WHERE owner = 'GEOSERVER_RO'
AND object_name =
'AIRFIELD_SURFACE_AREA'
AND object_type = 'VIEW')
CONNECT BY PRIOR referenced_object_id = object_id) c
WHERE a.object_id = c.object_id
AND b.object_id = c.referenced_object_id
AND a.owner NOT IN ('SYS', 'SYSTEM')
AND b.owner NOT IN ('SYS', 'SYSTEM')
AND a.object_name <> 'DUAL'
AND b.object_name <> 'DUAL'
AND b.object_type = 'TABLE')
SELECT l.column_name, l.position
FROM source_table s,
all_constraints n
JOIN
all_cons_columns l
ON l.owner = n.owner
AND l.table_name = n.table_name
AND l.constraint_name = n.constraint_name
WHERE n.constraint_type = 'P'
AND n.table_name = s.object_name
AND n.owner = s.owner;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.codehaus.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira