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

Wed Oct 6 14:00:25 PDT 2021

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

--
Jürgen E. Fischer           norBIT GmbH             Tel. +49-4931-918175-31
Dipl.-Inf. (FH)             Rheinstraße 13          Fax. +49-4931-918175-50
Software Engineer           D-26506 Norden            https://www.norbit.de
QGIS release manager (PSC)  Germany                 IRC: jef on Libera|OFTC

On 10/6/21 12:37 AM, jonathan.piraux at sibelga.be wrote:
> Hello,
>
> I created the issue here:
>
> https://github.com/qgis/QGIS/issues/45419
>
>
> You a right the primary key is recognized as primary key if I add it from the browser.
>
> Nevertheless, I still have the issue that views are visible 2 times. Therefore I suspect that there is an another issue that make view appearing 3 time in the browser as well ass in the source manager. But I don't know if it is a an oracle issue or if it is a qgis issue.
>
> Here is what I have in my browser:
>
> https://ibb.co/ncfB5ZT
>
> How does the browser construct the layer tree?
> I saw in the code that if the table/view has multiple geometry column then it create 2 layer. One by geometry column.
> But apart from that. Is there any other qgis logic that might create multiple layer from out the same oracle table/view?
>
> I saw in the log that qgis execute 2 times the same query like:
>
> 2021-10-06T00:12:41     WARNING    SQL: SELECT DISTINCT t."GEOSCHEMATIQUE".SDO_GTYPE FROM (SELECT "GEOSCHEMATIQUE" FROM "ATLAS"."V_GIS_REGLETTEBT" WHERE "GEOSCHEMATIQUE" IS NOT NULL AND rownum             Erreur: ORA-00904: "GEOSCHEMATIQUE": invalid identifier
>
> 2021-10-06T00:12:41     WARNING    SQL: SELECT DISTINCT t."GEOSCHEMATIQUE".SDO_GTYPE FROM (SELECT "GEOSCHEMATIQUE" FROM "ATLAS"."V_GIS_REGLETTEBT" WHERE "GEOSCHEMATIQUE" IS NOT NULL AND rownum             Erreur: ORA-00904: "GEOSCHEMATIQUE": invalid identifier
> (please ignore the invalid identifier error, this come from the fact
> that the geometry column name in my view is not the same the one
> recorder in the all_sdo_geom_metadate view, I have to fix this)
>
>
> Could it be that qgis execute 2 times the same code?
>
> Thanks
>
> Jonathan
>

Disclaimer

Le présent courriel ainsi que ses annexes éventuelles peuvent contenir des informations confidentielles et/ou protégées par des droits de propriété intellectuelle et sont destinés á l'usage exclusif du (des) destinataire(s) susmentionné(s). Toute utilisation - notamment, mais non exclusivement, pour la reproduction, la communication ou la distribution totale ou partielle sous quelque forme que ce soit - de leur contenu par des personnes autres que le(s) destinataire(s) désigné(s) est interdite. Si vous avez reçu ce courriel par erreur, veuillez en informer l'expéditeur soit par téléphone soit par courriel, et supprimer de tout ordinateur les données y afférentes. Merci de votre collaboration. En dépit des précautions prises, des courriels peuvent être interceptés, modifiés, perdus, détruits ou contenir des virus. Nous déclinons toute responsabilité quant aux éventuels dommages éventuellement causés dans cette éventualité. Toute personne qui communique avec SIBELGA par courriel est supposée accepter ce risque.

Deze mail en zijn eventuele bijlagen kunnen informatie bevatten die vertrouwelijk is en/of beschermd door intellectuele-eigendomsrechten en zijn bestemd voor exclusief gebruik door voormelde geadresseerde(n). Elk gebruik - onder meer, maar niet exclusief, voor volledige of gedeeltelijke reproductie, communicatie of distributie, onder welke vorm ook - van de inhoud ervan door andere personen dan de aangeduide geadresseerde(n), is verboden. Hebt u deze mail per vergissing ontvangen, gelieve de verzender daarvan per telefoon of mail te verwittigen, en de eraan gekoppelde gegevens van elke computer te verwijderen. Met dank voor uw medewerking. Ondanks de genomen voorzorgen, kunnen er mails worden onderschept, gewijzigd, verloren, vernietigd of kunnen zij virussen bevatten. Wij wijzen elke verantwoordelijkheid af met betrekking tot eventuele schade die in die hypothese zou zijn veroorzaakt. Elke persoon die via mail met SIBELGA communiceert, wordt geacht dat risico te aanvaarden.

Sibelga<https://www.sibelga.be>