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

Mon Oct 11 14:40:15 PDT 2021

Hello,

After a few days thinking about it, I came to the conclusion that it could be nice to have an extra option in the qgis oracle connection settings that let the user control if he want to see synonym as layer.

In my case qgis use another user than the schema owner (atlas). That user was granted to have right on a set of tables on this  schema to improve the relevance of the displayed table/view and the qgis performance. To avoid to precise the schema each time we do a query, a related synonym was also created. But then you have to deal with this duplicate layer issue.

If this is not the way to go when another user has to be able to use table/view from another schema. What are the alternative (if any)?

Jonathan
-----Message d'origine-----
De : Julien Cabieces <julien.cabieces at oslandia.com>
Envoyé : jeudi 7 octobre 2021 19:01
À : Piraux Jonathan <jonathan.piraux at sibelga.be>
Cc : Jürgen E. Fischer <jef at norbit.de>; Richard Duivenvoorde <rdmailings at duif.net>; qgis-developer at lists.osgeo.org
Objet : Re: ARE: [QGIS-Developer] duplicate oracle views displayed in the layer list

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

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>