Add base tests for create SQL layer

qgis · Jul 6, 2021 · 9384c78 · 9384c78
1 parent 96f594e
commit 9384c78
Show file tree

Hide file tree

Showing 13 changed files with 192 additions and 17 deletions.
diff --git a/python/core/auto_generated/providers/qgsabstractdatabaseproviderconnection.sip.in b/python/core/auto_generated/providers/qgsabstractdatabaseproviderconnection.sip.in
@@ -348,7 +348,7 @@ This information is calculated from the geometry columns types.
 
     enum SqlLayerDefinitionCapability
     {
-      Filters,
+      Filter,
       GeometryColumn,
       PrimaryKeys,
       SelectAtId

diff --git a/src/core/providers/ogr/qgsgeopackageproviderconnection.cpp b/src/core/providers/ogr/qgsgeopackageproviderconnection.cpp
@@ -379,7 +379,7 @@ void QgsGeoPackageProviderConnection::setDefaultCapabilities()
   };
   mSqlLayerDefinitionCapabilities =
   {
-    SqlLayerDefinitionCapability::Filters,
+    SqlLayerDefinitionCapability::Filter,
     SqlLayerDefinitionCapability::PrimaryKeys,
     SqlLayerDefinitionCapability::GeometryColumn,
   };

diff --git a/src/core/providers/qgsabstractdatabaseproviderconnection.h b/src/core/providers/qgsabstractdatabaseproviderconnection.h
@@ -407,7 +407,7 @@ class CORE_EXPORT QgsAbstractDatabaseProviderConnection : public QgsAbstractProv
       private:
 
         //! Holds the list of geometry wkb types and srids supported by the table
-        QList<GeometryColumnType>    mGeometryColumnTypes;
+        QList<GeometryColumnType>     mGeometryColumnTypes;
         //! Table schema
         QString                       mSchema;
         //! Table name
@@ -485,10 +485,11 @@ class CORE_EXPORT QgsAbstractDatabaseProviderConnection : public QgsAbstractProv
 
     /**
      * \brief The SqlLayerDefinitionCapability enum lists the arguments supported by the provider when creating SQL query layers.
+     * \since QGIS 3.22
      */
     enum SqlLayerDefinitionCapability
     {
-      Filters = 1 << 1,           //! SQL layer definition supports filters
+      Filter = 1 << 1,            //! SQL layer definition supports filter
       GeometryColumn = 1 << 2,    //! SQL layer definition supports geometry colum
       PrimaryKeys = 1 << 3,       //! SQL layer definition supports primary keys
       SelectAtId = 1 << 4         //! SQL layer definition supports disabling select at id
@@ -500,6 +501,8 @@ class CORE_EXPORT QgsAbstractDatabaseProviderConnection : public QgsAbstractProv
 
     /**
      * \brief The SqlKeywordCategory enum represents the categories of the SQL keywords used by the SQL query editor.
+     * \note The category has currently no usage, but it was planned for future uses.
+     * \since QGIS 3.22
      */
     enum SqlKeywordCategory
     {
@@ -508,9 +511,9 @@ class CORE_EXPORT QgsAbstractDatabaseProviderConnection : public QgsAbstractProv
       Function,     //! SQL generic function
       Geospatial,   //! SQL spatial function
       Operator,     //! SQL operator
-      Math,         //! SQL math functions
-      Aggregate,    //! SQL aggregate functions
-      String,       //! SQL string functions
+      Math,         //! SQL math function
+      Aggregate,    //! SQL aggregate function
+      String,       //! SQL string function
       Identifier    //! SQL identifier
     };
 

diff --git a/src/gui/qgsqueryresultwidget.cpp b/src/gui/qgsqueryresultwidget.cpp
@@ -82,7 +82,7 @@ QgsQueryResultWidget::QgsQueryResultWidget( QWidget *parent, QgsAbstractDatabase
       mGeometryColumnCheckBox->setVisible( showGeometryColumnConfig );
       mGeometryColumnComboBox->setVisible( showGeometryColumnConfig );
 
-      const bool showFilterConfig { connection &&connection->sqlLayerDefinitionCapabilities().testFlag( QgsAbstractDatabaseProviderConnection::SqlLayerDefinitionCapability::Filters ) };
+      const bool showFilterConfig { connection &&connection->sqlLayerDefinitionCapabilities().testFlag( QgsAbstractDatabaseProviderConnection::SqlLayerDefinitionCapability::Filter ) };
       mFilterLabel->setVisible( showFilterConfig );
       mFilterToolButton->setVisible( showFilterConfig );
       mFilterLineEdit->setVisible( showFilterConfig );

diff --git a/src/providers/mssql/qgsmssqlproviderconnection.cpp b/src/providers/mssql/qgsmssqlproviderconnection.cpp
@@ -101,7 +101,7 @@ void QgsMssqlProviderConnection::setDefaultCapabilities()
   };
   mSqlLayerDefinitionCapabilities =
   {
-    SqlLayerDefinitionCapability::Filters,
+    SqlLayerDefinitionCapability::Filter,
     SqlLayerDefinitionCapability::PrimaryKeys,
     SqlLayerDefinitionCapability::GeometryColumn,
     SqlLayerDefinitionCapability::SelectAtId,

diff --git a/src/providers/oracle/qgsoracleprovider.cpp b/src/providers/oracle/qgsoracleprovider.cpp
@@ -2624,7 +2624,7 @@ bool QgsOracleProvider::getGeometryDetails()
       // no data - so take what's requested
       if ( mRequestedGeomType == QgsWkbTypes::Unknown )
       {
-        QgsMessageLog::logMessage( tr( "Geometry type and srid for empty column %1 of %2 undefined." ).arg( mGeometryColumn ).arg( mQuery ) );
+        QgsMessageLog::logMessage( tr( "Geometry type and srid for empty column %1 of %2 undefined." ).arg( mGeometryColumn, mQuery ) );
       }
 
       detectedType = QgsWkbTypes::Unknown;

diff --git a/src/providers/oracle/qgsoracleproviderconnection.cpp b/src/providers/oracle/qgsoracleproviderconnection.cpp
@@ -123,7 +123,7 @@ void QgsOracleProviderConnection::setDefaultCapabilities()
   };
   mSqlLayerDefinitionCapabilities =
   {
-    SqlLayerDefinitionCapability::Filters,
+    SqlLayerDefinitionCapability::Filter,
     SqlLayerDefinitionCapability::GeometryColumn,
     SqlLayerDefinitionCapability::PrimaryKeys,
   };
@@ -164,16 +164,101 @@ QgsVectorLayer *QgsOracleProviderConnection::createSqlVectorLayer( const QgsAbst
     {
       sqlId ++;
     }
-    tUri.setTable( QStringLiteral( "(SELECT row_number() over () AS qgis_generated_uid_%1_, qgis_generated_subq_%3_.* FROM (%2\n) qgis_generated_subq_%3_\n)" ).arg( QString::number( pkId ), options.sql, QString::number( sqlId ) ) );
+    tUri.setTable( QStringLiteral( "(SELECT row_number() over (ORDER BY NULL) AS qgis_generated_uid_%1_, qgis_generated_subq_%3_.* FROM (%2\n) qgis_generated_subq_%3_\n)" ).arg( QString::number( pkId ), options.sql, QString::number( sqlId ) ) );
   }
 
   if ( ! options.geometryColumn.isEmpty() )
   {
     tUri.setGeometryColumn( options.geometryColumn );
   }
 
-  return new QgsVectorLayer{ tUri.uri(), options.layerName.isEmpty() ? QStringLiteral( "QueryLayer" ) : options.layerName, providerKey() };
+  std::unique_ptr<QgsVectorLayer> vl = std::make_unique<QgsVectorLayer>( tUri.uri(), options.layerName.isEmpty() ? QStringLiteral( "QueryLayer" ) : options.layerName, providerKey() );
 
+  // Try to guess the geometry and srid
+  if ( ! vl->isValid() )
+  {
+    const auto limit { QgsDataSourceUri( uri() ).useEstimatedMetadata() ? QStringLiteral( "AND ROWNUM < 100" ) : QString() };
+    const auto sql { QStringLiteral( R"(
+      SELECT DISTINCT a.%1.SDO_GTYPE As gtype,
+                            a.%1.SDO_SRID
+            FROM (%2) a
+            WHERE a.%1 IS NOT NULL %3
+            ORDER BY a.%1.SDO_GTYPE
+    )" ).arg( options.geometryColumn, options.sql, limit ) };
+    const auto candidates { executeSql( sql ) };
+    for ( const auto &row : std::as_const( candidates ) )
+    {
+      bool ok;
+      const auto type { row[ 0 ].toInt( &ok ) };
+      if ( ok )
+      {
+        const auto srid { row[ 0 ].toInt( &ok ) };
+
+        if ( ok )
+        {
+
+          QgsWkbTypes::Type geomType { QgsWkbTypes::Type::Unknown };
+
+          switch ( type )
+          {
+            case 2001:
+              geomType = QgsWkbTypes::Point;
+              break;
+            case 2002:
+              geomType = QgsWkbTypes::LineString;
+              break;
+            case 2003:
+              geomType = QgsWkbTypes::Polygon;
+              break;
+            // Note: 2004 is missing
+            case 2005:
+              geomType = QgsWkbTypes::MultiPoint;
+              break;
+            case 2006:
+              geomType = QgsWkbTypes::MultiLineString;
+              break;
+            case 2007:
+              geomType = QgsWkbTypes::MultiPolygon;
+              break;
+            // 3K...
+            case 3001:
+              geomType = QgsWkbTypes::Point25D;
+              break;
+            case 3002:
+              geomType = QgsWkbTypes::LineString25D;
+              break;
+            case 3003:
+              geomType = QgsWkbTypes::Polygon25D;
+              break;
+            // Note: 3004 is missing
+            case 3005:
+              geomType = QgsWkbTypes::MultiPoint25D;
+              break;
+            case 3006:
+              geomType = QgsWkbTypes::MultiLineString25D;
+              break;
+            case 3007:
+              geomType = QgsWkbTypes::MultiPolygon25D;
+              break;
+            default:
+              geomType = QgsWkbTypes::Type::Unknown;
+          }
+          if ( geomType != QgsWkbTypes::Type::Unknown )
+          {
+            tUri.setSrid( QString::number( srid ) );
+            tUri.setWkbType( geomType );
+            vl = std::make_unique<QgsVectorLayer>( tUri.uri(), options.layerName.isEmpty() ? QStringLiteral( "QueryLayer" ) : options.layerName, providerKey() );
+            if ( vl->isValid() )
+            {
+              break;
+            }
+          }
+        }
+      }
+    }
+  }
+
+  return vl.release();
 }
 
 void QgsOracleProviderConnection::store( const QString &name ) const

diff --git a/src/providers/postgres/qgspostgresproviderconnection.cpp b/src/providers/postgres/qgspostgresproviderconnection.cpp
@@ -85,7 +85,7 @@ void QgsPostgresProviderConnection::setDefaultCapabilities()
   };
   mSqlLayerDefinitionCapabilities =
   {
-    SqlLayerDefinitionCapability::Filters,
+    SqlLayerDefinitionCapability::Filter,
     SqlLayerDefinitionCapability::PrimaryKeys,
     SqlLayerDefinitionCapability::GeometryColumn,
     SqlLayerDefinitionCapability::SelectAtId,

diff --git a/src/providers/spatialite/qgsspatialiteproviderconnection.cpp b/src/providers/spatialite/qgsspatialiteproviderconnection.cpp
@@ -439,8 +439,8 @@ void QgsSpatiaLiteProviderConnection::setDefaultCapabilities()
   };
   mSqlLayerDefinitionCapabilities =
   {
-    Filters,
-    GeometryColumn
+    SqlLayerDefinitionCapability::Filter,
+    SqlLayerDefinitionCapability::GeometryColumn
   };
 
 }

diff --git a/tests/src/python/test_qgsproviderconnection_base.py b/tests/src/python/test_qgsproviderconnection_base.py
@@ -56,6 +56,10 @@ class TestPyQgsProviderConnectionBase():
     myUtf8Table = 'myUtf8\U0001f604Table'
     geometryColumnName = 'geom'
 
+    # Provider test cases can define a schema and table name for SQL query layers test
+    sqlVectorLayerSchema = None  # string, empty string for schema-less DBs (SQLite)
+    sqlVectorLayerTable = None   # string
+
     @classmethod
     def setUpClass(cls):
         """Run before all tests"""
@@ -505,3 +509,74 @@ def _cancel():
                 QgsApplication.processEvents()
             end = time.time()
             self.assertTrue(end - start < 1)
+
+    def testCreateSqlVectorLayer(self):
+        """Test vector layer creation from SQL query"""
+
+        md = QgsProviderRegistry.instance().providerMetadata(self.providerKey)
+        conn = md.createConnection(self.uri, {})
+
+        if not conn.capabilities() & QgsAbstractDatabaseProviderConnection.SqlLayers:
+            print(f"FIXME: {self.providerKey} data provider does not support query layers!")
+            return
+
+        try:
+            schema = getattr(self, 'sqlVectorLayerSchema')
+        except:
+            print(f"FIXME: {self.providerKey} data provider test case does not define self.sqlVectorLayerSchema for query layers test!")
+            return
+
+        try:
+            table = getattr(self, 'sqlVectorLayerTable')
+        except:
+            print(f"FIXME: {self.providerKey} data provider test case does not define self.sqlVectorLayerTable for query layers test!")
+            return
+
+        sql_layer_capabilities = conn.sqlLayerDefinitionCapabilities()
+
+        # Try a simple select first
+
+        table_info = conn.table(schema, table)
+
+        options = QgsAbstractDatabaseProviderConnection.SqlVectorLayerOptions()
+        options.layerName = 'My SQL Layer'
+
+        # Some providers do not support schema
+        if schema != '':
+            options.sql = f'SELECT * FROM "{table_info.schema()}"."{table_info.tableName()}"'
+        else:
+            options.sql = f'SELECT * FROM "{table_info.tableName()}"'
+
+        options.geometryColumn = table_info.geometryColumn()
+        options.primaryKeyColumns = table_info.primaryKeyColumns()
+
+        vl = conn.createSqlVectorLayer(options)
+        self.assertTrue(vl.isValid())
+        self.assertTrue(vl.isSpatial())
+        self.assertEqual(vl.name(), options.layerName)
+
+        # Some providers can also create SQL layer without an explicit PK
+        if sql_layer_capabilities & QgsAbstractDatabaseProviderConnection.PrimaryKeys:
+            options.primaryKeyColumns = []
+            vl = conn.createSqlVectorLayer(options)
+            self.assertTrue(vl.isValid())
+            self.assertTrue(vl.isSpatial())
+
+        # Some providers can also create SQL layer without an explicit geometry column
+        if sql_layer_capabilities & QgsAbstractDatabaseProviderConnection.GeometryColumn:
+            options.primaryKeyColumns = table_info.primaryKeyColumns()
+            options.geometryColumn = ''
+            vl = conn.createSqlVectorLayer(options)
+            self.assertTrue(vl.isValid())
+            # This may fail for OGR where the provider is smart enough to guess the geometry column
+            if self.providerKey != 'ogr':
+                self.assertFalse(vl.isSpatial())
+
+        # Some providers can also create SQL layer with filters
+        if sql_layer_capabilities & QgsAbstractDatabaseProviderConnection.Filter:
+            options.primaryKeyColumns = table_info.primaryKeyColumns()
+            options.geometryColumn = table_info.geometryColumn()
+            options.filter = f'"{options.primaryKeyColumns[0]}" > 0'
+            vl = conn.createSqlVectorLayer(options)
+            self.assertTrue(vl.isValid())
+            self.assertTrue(vl.isSpatial())
diff --git a/tests/src/python/test_qgsproviderconnection_ogr_gpkg.py b/tests/src/python/test_qgsproviderconnection_ogr_gpkg.py
@@ -50,7 +50,11 @@ class TestPyQgsProviderConnectionGpkg(unittest.TestCase, TestPyQgsProviderConnec
         SELECT i FROM r
         LIMIT 10000000
         )
-    SELECT i FROM r WHERE i = 1;"""
+    SELECT i FROM r WHERE i = 1; """
+
+    # Provider test cases can define a schema and table name for SQL query layers test
+    sqlVectorLayerSchema = ''
+    sqlVectorLayerTable = 'cdb_lines'
 
     @classmethod
     def setUpClass(cls):

diff --git a/tests/src/python/test_qgsproviderconnection_oracle.py b/tests/src/python/test_qgsproviderconnection_oracle.py
@@ -46,6 +46,10 @@ class TestPyQgsProviderConnectionOracle(unittest.TestCase, TestPyQgsProviderConn
     myUtf8Table = 'MYUTF8\U0001F604TABLE'
     geometryColumnName = 'GEOM'
 
+    # Provider test cases can define a schema and table name for SQL query layers test
+    sqlVectorLayerSchema = 'QGIS'
+    sqlVectorLayerTable = 'SOME_DATA'
+
     def execSQLCommand(self, sql, ignore_errors=False):
         self.assertTrue(self.conn)
         query = QSqlQuery(self.conn)

diff --git a/tests/src/python/test_qgsproviderconnection_postgres.py b/tests/src/python/test_qgsproviderconnection_postgres.py
@@ -42,6 +42,10 @@ class TestPyQgsProviderConnectionPostgres(unittest.TestCase, TestPyQgsProviderCo
     # Provider test cases can define a slowQuery for executeSql cancellation test
     slowQuery = "select pg_sleep(30)"
 
+    # Provider test cases can define a schema and table name for SQL query layers test
+    sqlVectorLayerSchema = 'qgis_test'
+    sqlVectorLayerTable = 'someData'
+
     @classmethod
     def setUpClass(cls):
         """Run before all tests"""