[QGIS-Developer] ARE: duplicate oracle views displayed in the layer list

[Julien Cabieces]

Hi,

I think this is legit to load the table and its synonym, it's 2
different object. Imagine if your synonym refers to a table in a
different schema/owner, we would have to display the synonym.

You could argue that we could display only one instance of the same
table, but one could expect to see both. So IMHO, there is nothing
to be fixed in QGIS.

I can't see any easy alternative, except developing a python plugin to
have your own specific display.

Kind regards,
Julien


> Hello,
>
> I think I finally found why QGIS display 2 times the same layer in the browser and the layer manager. The first query done by QGIS when opening an oracle connexion is
>
> SELECT
>        c.table_name,
>        c.column_name,
>        c.srid,
>        o.object_type     AS TYPE
>   FROM all_sdo_geom_metadata  c
>        JOIN all_objects o
>            ON     c.table_name = o.object_name
>               AND o.object_type IN ('TABLE', 'VIEW', 'SYNONYM')
>               AND c.owner = 'ATLAS'
>
> This query return ,in my case, two records about the same table/view. But one time with the type table/view and one time the type synonym.
>
> Here is an example
>
> |TABLE_NAME   |COLUMN_NAME   |SRID |TYPE   |
> |-------------|--------------|-----|-------|
> |V_GIS_CABLEBT|PLAN_DE_POSE  |31370|SYNONYM|
> |V_GIS_CABLEBT|PLAN_DE_POSE  |31370|VIEW   |
>
> Why does qgis include the synonym object type into consideration?
>
> Remove the synonym is not an option for me.
> Do you have an alternative?
> At QGIS level could we change this ?
>
> Thanks
>
> Jonathan
>
>
>
> -----Message d'origine-----
> De : Piraux Jonathan
> Envoyé : mercredi 6 octobre 2021 13:56
> À : 'Jürgen E. Fischer' <jef at norbit.de>; Richard Duivenvoorde <rdmailings at duif.net>
> Cc : qgis-developer at lists.osgeo.org
> Objet : RE: [QGIS-Developer] duplicate oracle views displayed in the layer list
>
> Hello,
>
> Thanks for your support.
>
> I am happy that the original author of the oracle provider joined the conversation.
>
> Regarding the hypothesis of Richard. My views can contain multiple geometry column (it is not always the case, most of the time it is one geom. column) BUT each of them only contains one sort of geometry.
>
> If there is 2 geometry column. I get the following result in the qgis browser:
>
> One layer for the first geometry column
> A second layer for the second geometry column
>
> ---> This is expected
>
> But also :
>
> A third layer also representing the first geometry column a Fourth layer also representing the second geometry column An attribute layer without any geometry.
>
> Here is an example:
>
> https://ibb.co/FJ1HGGG
>
> So if I take back Richard's hypothesis. One option could be that the way how I create my record in user_SDO_GEOM_METADATATABLE is not correct and therefore QGIS detect a difference between what is imputed in the diminfo column of the  user_SDO_GEOM_METADATATABLE view and the analysis done on the 100 first record of one geometry column (I am using the option estimate metadata ) of  the view when qgis analyse each view to estimate the geometry type.
>
> Here how I do my insert in user_sdo_geom_metadata:
>
> INSERT INTO MDSYS.USER_SDO_GEOM_METADATA (table_name, column_name,diminfo,srid) VALUES (''' || VIEW_NAME || ''',''' || VIEW_FIELD_NAME || ''', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT(''X'',133000,168000,0.00005),
> MDSYS.SDO_DIM_ELEMENT(''Y'',155000,184000,0.00005),
> MDSYS.SDO_DIM_ELEMENT(''Z'',-1000,1000,0.00005)),31370)
>
> (this is executed in the framework of a psql script).
>
> Do I have to change something in my insert statement? Is my diminfo correct?
>
>
> Jonathan
>
>
>
>
> -----Message d'origine-----
> De : QGIS-Developer <qgis-developer-bounces at lists.osgeo.org> De la part de Jürgen E. Fischer Envoyé : mercredi 6 octobre 2021 09:52 À : Richard Duivenvoorde <rdmailings at duif.net> Cc : qgis-developer at lists.osgeo.org Objet : Re: [QGIS-Developer] duplicate oracle views displayed in the layer list
>
> Hi Richard,
>
> On Wed, 06. Oct 2021 at 07:54:57 +0200, Richard Duivenvoorde wrote:
>> /me wildly guessing here: if QGIS 'sees' different geom types, it is
>> shown multiple in the browser isn't it, or am I mixing up things
>> here..? Like if a layer has both points and lines?
>
> There is no geometry type constraint in Oracle, so you can have any geometry type - even ones that don't yet exists.  So you get one line for each existing geometry type (point, line, area) and one where you have to select the geometry type in case you want to add a not yet existing geometry type.
>
> Same happens for GEOMETRY columns (instead limited to POINT, LINESTRING or
> POLYGON) in postgres.
>
>
> Jürgen