Bug report #11963

Oracle Provider do not list non geographic views

Added by Médéric RIBREUX almost 7 years ago. Updated almost 7 years ago.

Status:Closed
Priority:Normal
Assignee:Jürgen Fischer
Category:Data Provider/Oracle
Affected QGIS version:2.6.0 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:20171

Description

Hello,

I have created a non geographic view from a geographic table. QGis Oracle Data provider does not list the view when I try to refresh the table list on the provider dialog box.

The request made by QGis returns my view. I've captured the SQL query made by QGis to grab table/view list:

 SELECT user AS owner,c.table_name,c.column_name,NULL AS srid,o.object_type AS type
 FROM user_tab_columns c JOIN user_objects o ON c.table_name=o.object_name AND o.object_type IN ('TABLE','VIEW','SYNONYM') WHERE c.data_type='SDO_GEOMETRY'
 UNION
 SELECT user AS owner,object_name,NULL AS column_name,NULL AS srid,object_type AS type
 FROM user_objects c WHERE c.object_type IN ('TABLE','VIEW','SYNONYM')

It seems that the provider is not able to list non geographic views on the dialog box...

Associated revisions

Revision bdd4388c
Added by Jürgen Fischer almost 7 years ago

oracle provider: also retrieve pk candidates for geometryless views (fixes #11963)

History

#1 Updated by Jukka Rahkonen almost 7 years ago

A side note: It looks like QGIS user must be the owner of the tables and views. In enterprise environment that is not so common. Normally db admins create the tables and views and grant rights to users based on their roles.

#2 Updated by Médéric RIBREUX almost 7 years ago

Hello,

in my case, I am the owner of the view, so this is not a DB rights problem.

Furthermore, as far as I can see, you do not need to be the owner to work with a table under QGis. In my environment, I can work (read or write) with layers or non-geographic tables which are stored under another schema than my user's mine.

There is an option in the Oracle Data Provider dialog box dedicated to connexions where you can enable listing of tables than are not in the schema of the user.

Regards,

#3 Updated by Jukka Rahkonen almost 7 years ago

I was just looking at the SQL that you captured: ...FROM user_tab_columns.

I have actually never used QGIS with Oracle but I have a few databases available and I can try to do some short test. But there are quite a many check boxes in the dialog. Could you add a complete list of all the selections that you have used in your trials?

Did you capture the SQL from the Oracle side or do you know some clever way for capturing them from QGIS? I have so limited Oracle user rights that I can't capture anything from Oracle. Anyway, the key is in "c.data_type='SDO_GEOMETRY'" which selects only objects with SDO_GEOMETRY column. Check box "List tables without geometries" should obviously remove that rule.

#4 Updated by Médéric RIBREUX almost 7 years ago

Hello,

I have "search only in user tables" checked on Oracle Provider connexions. As a result, QGis looks only in USER* tables.

Anyway, the key is in "c.data_type='SDO_GEOMETRY'" which selects only objects with SDO_GEOMETRY column. Check box "List tables without geometries" should obviously remove that rule.
But there is a UNION query in the SQL query above and you can see that it grabs anything that is a table or a view (WHERE c.object_type IN ('TABLE','VIEW','SYNONYM')) even if there is no geometric column.

To capture SQL queries, I just use Wireshark and make it capture all the traffic between my workstation and the Oracle server. The network stream is not encrypted so you can easily capture it (Analyse TCP flow)...

#5 Updated by Jürgen Fischer almost 7 years ago

How is that view defined? Does it have numeric columns? e.g. describe in sqlplus.

You could also check the debug output.

#6 Updated by Jukka Rahkonen almost 7 years ago

I made a simple test by making a new view as
create view qgis_test_view as (select string_attribute from table);

This new view does not show in the Add Oracle Table(s) dialog even if the "Also list tables with no geometry" is checked so I can confirm this issue. I believe also that not all real non-spatial tables were listed but I had severe troubles with testing. QGIS 2.6 did not seem to finish the reading of layer list ever even I had checked the "Use estimated table metadata" option.

#7 Updated by Médéric RIBREUX almost 7 years ago

Hello,

the view is defined like this:

CREATE OR REPLACE VIEW "DDEV1"."PAE_LST" AS SELECT "ID_PAE", "TICO" FROM "PAE";

It is created with user DDEV1. Here is the SQL creation of table "PAE":

CREATE TABLE "PAE" (
    ID_PAE number(5),
    REF_PAE varchar2(9),
    REF_EXT varchar2(9),
    CODE_EI varchar2(10),
    COPYRIGHT varchar2(400),
    DENQ number(4) CONSTRAINT "PAE_DENQ_NN" NOT NULL,
    DBOR varchar2(50) CONSTRAINT "PAE_DBOR_NN" NOT NULL,
    NOMS varchar2(400) CONSTRAINT "PAE_NOMS_NN" NOT NULL,
    ETUD number(4) CONSTRAINT "PAE_ETUD_NN" NOT NULL,
    PETUD number(4),
    PDEN varchar2(300),
    ACTU varchar2(300),
    TICO varchar2(150),
    REG number(3),
    DPT number(3),
    COM number(5),
    INSEE number(5),
    PLOC varchar2(150),
    AIRE varchar2(150),
    LIEU varchar2(150),
    ADRS varchar2(150),
    CADA varchar2(100),
    REF_ENS number(4) ,
    IMPL number(4),
    SCI varchar2(50),
    DATAT varchar2(6),
    AUTR varchar2(255),
    PERS varchar2(255),
    LIEUH varchar2(150),
    HIST varchar2(4000) ,
    HISTURL varchar2(255),
    EPAN number(8,5),
    TYPO varchar2(300),
    PETAT varchar2(500),
    CONT varchar2(2000),
    DESCRI varchar2(4000),
    DESCURL varchar2(255),
    ZPLU number(9),
    PSTA number(4),
    VISI CHAR CHECK (VISI IN (0,1)),
    PSITE varchar2(300),
    DPRO varchar2(150),
    PPRO varchar2(500),
    PINDPAT varchar2(500),
    INTE number(4),
    AREMA varchar2(300),
    OBS varchar2(4000),
    ENTR varchar2(500),
    PRECO varchar2(4000),
    PRECOURL varchar2(255),
    OPROT number(4),
    AVIS_DPARC varchar2(2000),
    PEOB varchar2(1000),
    VALID CHAR CHECK (VALID IN (0,1)),
    ACCES CHAR CHECK (ACCES IN (0,1)),
    GEOM SDO_GEOMETRY,
    CONSTRAINT "PAE_PK" PRIMARY KEY ("ID_PAE")
);

When I activate debug, I can see this:

[4136] ..\\..\\..\\..\\..\\..\\src\\providers\\oracle\\qgsoracletablemodel.cpp(44) : (QgsOracleTableModel::addTableEntry) [0ms] DDEV1.PAE_LST. type=100 srid=0 view=yes pk= sql=
[4136] ..\\..\\..\\..\\..\\..\\src\\providers\\oracle\\qgsoracletablemodel.cpp(48) : (QgsOracleTableModel::addTableEntry) [0ms] View without pk skipped.

I understand that QGis doesn't find primary key but how can you declare a primary key in a VIEW ?

The first field: "ID_PAE" is the primary key of table "PAE" and I thought it would be seen as the primary key in the view....

Digging into Oracle documentation, I've found that you can add primary key constraint on a view using DISABLE NOVALIDATE options on the constraint.

I've rewritten my view definition like this:

CREATE OR REPLACE VIEW "DDEV1"."PAE_LST" (
  "ID_PAE",
  "TICO",
  CONSTRAINT "TD_PK" PRIMARY KEY ("ID_PAE") DISABLE NOVALIDATE
) AS
SELECT "ID_PAE", "TICO" FROM "PAE";

But I've got the same problem... I've also tried with other non-geographic views from non-geographic tables and got the same symptoms.

When I just add the geometry column, QGis is able to list the view, even if there is no primary key on the constraint:

CREATE OR REPLACE VIEW "DDEV1"."PAE_LST" (
  "ID_PAE",
  "TICO",
  "GEOM" 
) AS
SELECT "ID_PAE", "TICO", "GEOM" FROM "PAE";

The above view is visible (and can be opened) on the layer list of the Oracle Provider.

Here is what the debugger is able to see with the geographic view:

[4136] ..\\..\\..\\..\\..\\..\\src\\providers\\oracle\\qgsoracletablemodel.cpp(44) : (QgsOracleTableModel::addTableEntry) [0ms] DDEV1.PAE_LST.GEOM type=3|6|0 srid=0|0|0 view=yes pk=ID_PAE|TICO|GEOM sql=
[4136] ..\\..\\..\\..\\..\\..\\src\\providers\\oracle\\qgsoraclesourceselect.cpp(433) : (QgsOracleSourceSelect::setLayerType) [3ms] entering.
[4136] ..\\..\\..\\..\\..\\..\\src\\providers\\oracle\\qgsoracletablemodel.cpp(44) : (QgsOracleTableModel::addTableEntry) [0ms] DDEV1.PAE_LST. type=100 srid=0 view=yes pk= sql=
[4136] ..\\..\\..\\..\\..\\..\\src\\providers\\oracle\\qgsoracletablemodel.cpp(48) : (QgsOracleTableModel::addTableEntry) [0ms] View without pk skipped.

I think this is a bug... But perhaps there is a better way to declare non-geographic views ?

#8 Updated by Jukka Rahkonen almost 7 years ago

Oracle is a bit special in this detail (too) and you really can create a primary key for a view. Do it this way for an existing view:

alter view your_view add constraint your_view_pk
primary key(attr_name) disable novalidate;

#9 Updated by Jürgen Fischer almost 7 years ago

  • Status changed from Open to Closed

#10 Updated by Médéric RIBREUX almost 7 years ago

Hello,

thank you very much Jürgen !

Also available in: Atom PDF