Fixes duplication in table list when allowing geometryLessTables

qgis · Jan 29, 2021 · ac21374 · ac21374
1 parent ac6011d
commit ac21374
Show file tree

Hide file tree

Showing 2 changed files with 69 additions and 6 deletions.
diff --git a/src/providers/oracle/qgsoracleconn.cpp b/src/providers/oracle/qgsoracleconn.cpp
@@ -296,18 +296,16 @@ bool QgsOracleConn::tableInfo( const QString &schema, bool geometryColumnsOnly,
 
   if ( allowGeometrylessTables )
   {
-
     // also here!
     sql += QStringLiteral( " UNION SELECT %1,object_name,NULL AS column_name,NULL AS srid,object_type AS type"
-                           " FROM %2_objects c WHERE c.object_type IN ('TABLE','VIEW','SYNONYM') %3" )
+                           " FROM %2_objects c WHERE c.object_type IN ('TABLE','VIEW','SYNONYM') "
+                           // get only geometry table without geometry column
+                           " AND NOT EXISTS( SELECT 1 FROM %2_tab_columns cols WHERE cols.table_name=c.object_name AND cols.data_type='SDO_GEOMETRY') %3" )
            .arg( owner,
                  prefix,
                  userTablesOnly || schema.isEmpty() ? QString() : QStringLiteral( " AND c.owner=%1" ).arg( quotedValue( schema ) ) );
   }
 
-  // sql = "SELECT * FROM (" + sql + ")";
-  // sql += " ORDER BY owner,isview,table_name,column_name";
-
   QSqlQuery qry( mDatabase );
   if ( !exec( qry, sql, QVariantList() ) )
   {

diff --git a/tests/src/python/test_qgsproviderconnection_oracle.py b/tests/src/python/test_qgsproviderconnection_oracle.py
@@ -19,8 +19,10 @@
     QgsVectorLayer,
     QgsProviderRegistry,
     QgsDataSourceUri,
+    QgsAbstractDatabaseProviderConnection
 )
 from qgis.testing import unittest
+from qgis.PyQt.QtSql import QSqlDatabase, QSqlQuery
 
 
 class TestPyQgsProviderConnectionOracle(unittest.TestCase, TestPyQgsProviderConnectionBase):
@@ -47,14 +49,77 @@ def setUpClass(cls):
 
         cls.uri = cls.dbconn
 
+        cls.conn = QSqlDatabase.addDatabase('QOCISPATIAL', "oracletest")
+        cls.conn.setDatabaseName('localhost/XEPDB1')
+        if 'QGIS_ORACLETEST_DBNAME' in os.environ:
+            cls.conn.setDatabaseName(os.environ['QGIS_ORACLETEST_DBNAME'])
+        cls.conn.setUserName('QGIS')
+        cls.conn.setPassword('qgis')
+        assert cls.conn.open()
+
         # try:
         #     md = QgsProviderRegistry.instance().providerMetadata(cls.providerKey)
         #     conn = md.createConnection(cls.uri, {})
         #     conn.executeSql('drop schema [myNewSchema]')
         # except:
         #     pass
 
-    # def test_configuration(self):
+    def execSQLCommand(self, sql, ignore_errors=False):
+        self.assertTrue(self.conn)
+        query = QSqlQuery(self.conn)
+        res = query.exec_(sql)
+        if not ignore_errors:
+            self.assertTrue(res, sql + ': ' + query.lastError().text())
+        query.finish()
+
+    def test_tables_with_options(self):
+
+        md = QgsProviderRegistry.instance().providerMetadata('oracle')
+
+        def get_tables(schema, configuration, flags=QgsAbstractDatabaseProviderConnection.TableFlags()):
+            conn = md.createConnection(self.uri, configuration)
+            tables = conn.tables(schema, flags)
+            return sorted([table.tableName() for table in tables if table.tableName() in [
+                'DATE_TIMES', 'GENERATED_COLUMNS', 'LINE_DATA', 'OTHER_TABLE', 'POINT_DATA', 'POINT_DATA_IDENTITY', 'POLY_DATA', 'SOME_DATA', 'SOME_POLY_DATA']])
+
+        # all tables
+        self.assertEqual(get_tables('QGIS', {}),
+                         ['DATE_TIMES', 'GENERATED_COLUMNS', 'LINE_DATA', 'POINT_DATA', 'POINT_DATA_IDENTITY', 'POLY_DATA', 'SOME_DATA', 'SOME_POLY_DATA'])
+
+        # only non-spatial tables
+        self.assertEqual(get_tables('QGIS', {}, QgsAbstractDatabaseProviderConnection.Aspatial),
+                         ['DATE_TIMES', 'GENERATED_COLUMNS'])
+
+        # only vector tables
+        self.assertEqual(get_tables('QGIS', {}, QgsAbstractDatabaseProviderConnection.Vector),
+                         ['LINE_DATA', 'POINT_DATA', 'POINT_DATA_IDENTITY', 'POLY_DATA', 'SOME_DATA', 'SOME_POLY_DATA'])
+
+        # only table existing in sdo_geom_metadata table
+        self.assertEqual(get_tables('QGIS', {"geometryColumnsOnly": True}, QgsAbstractDatabaseProviderConnection.Vector),
+                         ['SOME_DATA', 'SOME_POLY_DATA'])
+
+        self.execSQLCommand('DROP TABLE OTHER_USER.OTHER_TABLE', ignore_errors=True)
+        self.execSQLCommand('DROP USER OTHER_USER CASCADE', ignore_errors=True)
+        self.execSQLCommand('CREATE USER OTHER_USER')
+        self.execSQLCommand('GRANT ALL PRIVILEGES TO OTHER_USER')
+        self.execSQLCommand('CREATE TABLE OTHER_USER.OTHER_TABLE ( "pk" INTEGER PRIMARY KEY, GEOM SDO_GEOMETRY)')
+
+        # if a schema is specified, schema (i.e. user) tables are returned, whatever userTablesOnly value
+        self.assertEqual(get_tables('OTHER_USER', {"userTablesOnly": True}),
+                         ['OTHER_TABLE'])
+
+        self.assertEqual(get_tables('OTHER_USER', {"userTablesOnly": False}),
+                         ['OTHER_TABLE'])
+
+        # no schema is specified, all user tables (vector ones in this case) are returned
+        self.assertEqual(get_tables('', {"userTablesOnly": True}, QgsAbstractDatabaseProviderConnection.Vector),
+                         ['LINE_DATA', 'POINT_DATA', 'POINT_DATA_IDENTITY', 'POLY_DATA', 'SOME_DATA', 'SOME_POLY_DATA'])
+
+        # no schema is specified, all tables (vector ones in this case) tables are returned
+        self.assertEqual(get_tables('', {"userTablesOnly": False}, QgsAbstractDatabaseProviderConnection.Vector),
+                         ['LINE_DATA', 'OTHER_TABLE', 'POINT_DATA', 'POINT_DATA_IDENTITY', 'POLY_DATA', 'SOME_DATA', 'SOME_POLY_DATA'])
+
+        # def test_configuration(self):
     #     """Test storage and retrieval for configuration parameters"""
 
     #     uri = 'dbname=\'qgis_test\' service=\'driver={SQL Server};server=localhost;port=1433;database=qgis_test\' user=\'sa\' password=\'<YourStrong!Passw0rd>\' srid=4326 type=Point estimatedMetadata=\'true\' disableInvalidGeometryHandling=\'1\' table="qgis_test"."someData" (geom)'