Hi, I'm not sure adding a new extra option for this is a good thing, it looks like a little bit too specific to a use case. Another user could want to display only synonym, or display table if there is not already a synonym...
Why do you bother seeing both table name and synonym? Is it really a problem? Kind regards, Julien > 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 <[email protected]> > Envoyé : jeudi 7 octobre 2021 19:01 > À : Piraux Jonathan <[email protected]> > Cc : Jürgen E. Fischer <[email protected]>; Richard Duivenvoorde > <[email protected]>; [email protected] > 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' >> <[email protected]>; Richard Duivenvoorde <[email protected]> Cc : >> [email protected] 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 <[email protected]> De la >> part de Jürgen E. Fischer Envoyé : mercredi 6 octobre 2021 09:52 À : >> Richard Duivenvoorde <[email protected]> Cc : >> [email protected] 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> _______________________________________________ QGIS-Developer mailing list [email protected] List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
