Fix: QgsHanaProvider cannot be initialized with a query

qgis · May 4, 2021 · 9c8bd52 · 9c8bd52
1 parent e93a92e
commit 9c8bd52
Show file tree

Hide file tree

Showing 6 changed files with 122 additions and 24 deletions.
diff --git a/src/providers/hana/qgshanaconnection.cpp b/src/providers/hana/qgshanaconnection.cpp
@@ -911,17 +911,16 @@ QStringList QgsHanaConnection::getPrimaryKeyCandidates( const QgsHanaLayerProper
   return ret;
 }
 
-QgsWkbTypes::Type QgsHanaConnection::getColumnGeometryType( const QString &schemaName, const QString &tableName, const QString &columnName )
+QgsWkbTypes::Type QgsHanaConnection::getColumnGeometryType( const QString &querySource, const QString &columnName )
 {
   if ( columnName.isEmpty() )
     return QgsWkbTypes::NoGeometry;
 
   QgsWkbTypes::Type ret = QgsWkbTypes::Unknown;
-  QString sql = QStringLiteral( "SELECT upper(%1.ST_GeometryType()), %1.ST_Is3D(), %1.ST_IsMeasured() FROM %2.%3 "
-                                "WHERE %1 IS NOT NULL LIMIT %4" ).arg(
+  QString sql = QStringLiteral( "SELECT upper(%1.ST_GeometryType()), %1.ST_Is3D(), %1.ST_IsMeasured() FROM %2 "
+                                "WHERE %1 IS NOT NULL LIMIT %3" ).arg(
                   QgsHanaUtils::quotedIdentifier( columnName ),
-                  QgsHanaUtils::quotedIdentifier( schemaName ),
-                  QgsHanaUtils::quotedIdentifier( tableName ),
+                  querySource,
                   QString::number( GEOMETRIES_SELECT_LIMIT ) );
 
   try
@@ -952,6 +951,13 @@ QgsWkbTypes::Type QgsHanaConnection::getColumnGeometryType( const QString &schem
   return ret;
 }
 
+QgsWkbTypes::Type QgsHanaConnection::getColumnGeometryType( const QString &schemaName, const QString &tableName, const QString &columnName )
+{
+  QString querySource = QStringLiteral( "%1.%2" ).arg( QgsHanaUtils::quotedIdentifier( schemaName ),
+                        QgsHanaUtils::quotedIdentifier( tableName ) );
+  return getColumnGeometryType( querySource, columnName );
+}
+
 QString QgsHanaConnection::getColumnDataType( const QString &schemaName, const QString &tableName, const QString &columnName )
 {
   const char *sql = "SELECT DATA_TYPE_NAME FROM SYS.TABLE_COLUMNS WHERE SCHEMA_NAME = ? AND "

diff --git a/src/providers/hana/qgshanaconnection.h b/src/providers/hana/qgshanaconnection.h
@@ -90,6 +90,7 @@ class QgsHanaConnection : public QObject
     void readTableFields( const QString &schemaName, const QString &tableName, const std::function<void( const AttributeField &field )> &callback );
     QVector<QgsHanaSchemaProperty> getSchemas( const QString &ownerName );
     QStringList getLayerPrimaryKey( const QString &schemaName, const QString &tableName );
+    QgsWkbTypes::Type getColumnGeometryType( const QString &querySource, const QString &columnName );
     QgsWkbTypes::Type getColumnGeometryType( const QString &schemaName, const QString &tableName, const QString &columnName );
     QString getColumnDataType( const QString &schemaName, const QString &tableName, const QString &columnName );
     int getColumnSrid( const QString &schemaName, const QString &tableName, const QString &columnName );

diff --git a/src/providers/hana/qgshanafeatureiterator.cpp b/src/providers/hana/qgshanafeatureiterator.cpp
@@ -445,10 +445,9 @@ QString QgsHanaFeatureIterator::buildSqlQuery( const QgsFeatureRequest &request
     }
   }
 
-  QString sql = QStringLiteral( "SELECT %1 FROM %2.%3" ).arg(
+  QString sql = QStringLiteral( "SELECT %1 FROM %2" ).arg(
                   sqlFields.isEmpty() ? QStringLiteral( "*" ) : sqlFields.join( ',' ),
-                  QgsHanaUtils::quotedIdentifier( mSource->mSchemaName ),
-                  QgsHanaUtils::quotedIdentifier( mSource->mTableName ) );
+                  mSource->mQuery );
 
   if ( !sqlFilter.isEmpty() )
     sql += QStringLiteral( " WHERE (%1)" ).arg( sqlFilter.join( QLatin1String( ") AND (" ) ) );
@@ -479,8 +478,8 @@ QVariantList QgsHanaFeatureIterator::buildSqlQueryParameters( ) const
 QgsHanaFeatureSource::QgsHanaFeatureSource( const QgsHanaProvider *p )
   : mDatabaseVersion( p->mDatabaseVersion )
   , mUri( p->mUri )
-  , mSchemaName( p->mSchemaName )
-  , mTableName( p->mTableName )
+  , mQuery( p->mQuerySource )
+  , mQueryWhereClause( p->mQueryWhereClause )
   , mPrimaryKeyType( p->mPrimaryKeyType )
   , mPrimaryKeyAttrs( p->mPrimaryKeyAttrs )
   , mPrimaryKeyCntx( p->mPrimaryKeyCntx )
@@ -490,7 +489,6 @@ QgsHanaFeatureSource::QgsHanaFeatureSource( const QgsHanaProvider *p )
   , mSrid( p->mSrid )
   , mSrsExtent( p->mSrsExtent )
   , mCrs( p->crs() )
-  , mQueryWhereClause( p->mQueryWhereClause )
 {
   if ( p->mHasSrsPlanarEquivalent && p->mDatabaseVersion.majorVersion() <= 1 )
     mSrid = QgsHanaUtils::toPlanarSRID( p->mSrid );

diff --git a/src/providers/hana/qgshanafeatureiterator.h b/src/providers/hana/qgshanafeatureiterator.h
@@ -39,8 +39,8 @@ class QgsHanaFeatureSource : public QgsAbstractFeatureSource
   private:
     QVersionNumber mDatabaseVersion;
     QgsDataSourceUri mUri;
-    QString mSchemaName;
-    QString mTableName;
+    QString mQuery;
+    QString mQueryWhereClause;
     QgsHanaPrimaryKeyType mPrimaryKeyType = QgsHanaPrimaryKeyType::PktUnknown;
     QList<int> mPrimaryKeyAttrs;
     std::shared_ptr<QgsHanaPrimaryKeyContext> mPrimaryKeyCntx;
@@ -50,7 +50,6 @@ class QgsHanaFeatureSource : public QgsAbstractFeatureSource
     int mSrid;
     QgsRectangle mSrsExtent;
     QgsCoordinateReferenceSystem mCrs;
-    QString mQueryWhereClause;
 
     friend class QgsHanaFeatureIterator;
     friend class QgsHanaExpressionCompiler;

diff --git a/src/providers/hana/qgshanaprovider.cpp b/src/providers/hana/qgshanaprovider.cpp
@@ -50,8 +50,17 @@ using namespace std;
 
 namespace
 {
+  bool isQuery( const QString &source )
+  {
+    QString trimmed = source.trimmed();
+    return trimmed.startsWith( '(' ) && trimmed.endsWith( ')' );
+  }
+
   QString buildQuery( const QString &source, const QString &columns, const QString &where, const QString &orderBy, int limit )
   {
+    if ( isQuery( source ) && columns == QLatin1String( "*" ) && where.isEmpty() && limit <= 0 )
+      return source;
+
     QString sql = QStringLiteral( "SELECT %1 FROM %2" ).arg( columns, source );
     if ( !where.isEmpty() )
       sql  += QStringLiteral( " WHERE " ) + where;
@@ -358,7 +367,7 @@ QgsHanaProvider::QgsHanaProvider(
     return;
   }
 
-  if ( mSchemaName.isEmpty() && mTableName.startsWith( '(' ) && mTableName.endsWith( ')' ) )
+  if ( isQuery( mTableName ) )
   {
     mIsQuery = true;
     mQuerySource = mTableName;
@@ -367,7 +376,9 @@ QgsHanaProvider::QgsHanaProvider(
   else
   {
     mIsQuery = false;
-    mQuerySource = QStringLiteral( "%1.%2" ).arg( QgsHanaUtils::quotedIdentifier( mSchemaName ), QgsHanaUtils::quotedIdentifier( mTableName ) );
+    mQuerySource = QStringLiteral( "%1.%2" ).arg(
+                     QgsHanaUtils::quotedIdentifier( mSchemaName ),
+                     QgsHanaUtils::quotedIdentifier( mTableName ) );
   }
 
   try
@@ -1398,17 +1409,29 @@ void QgsHanaProvider::readGeometryType( QgsHanaConnection &conn )
   if ( mGeometryColumn.isNull() || mGeometryColumn.isEmpty() )
     mDetectedGeometryType = QgsWkbTypes::NoGeometry;
 
-  mDetectedGeometryType = conn.getColumnGeometryType( mSchemaName, mTableName, mGeometryColumn );
+  if ( mIsQuery )
+  {
+    QString query = buildQuery( QStringLiteral( "*" ) );
+    if ( !isQuery( query ) )
+      query = "(" + query + ")";
+    mDetectedGeometryType = conn.getColumnGeometryType( query, mGeometryColumn );
+  }
+  else
+    mDetectedGeometryType = conn.getColumnGeometryType( mSchemaName, mTableName, mGeometryColumn );
 }
 
 void QgsHanaProvider::readMetadata( QgsHanaConnection &conn )
 {
-  QString sql = QStringLiteral( "SELECT COMMENTS FROM TABLES WHERE SCHEMA_NAME = ? AND TABLE_NAME = ?" );
-  QVariant comment = conn.executeScalar( sql, { mSchemaName, mTableName } );
-  if ( !comment.isNull() )
-    mLayerMetadata.setAbstract( comment.toString() );
-  mLayerMetadata.setType( QStringLiteral( "dataset" ) );
   mLayerMetadata.setCrs( crs() );
+  mLayerMetadata.setType( QStringLiteral( "dataset" ) );
+
+  if ( !mIsQuery )
+  {
+    QString sql = QStringLiteral( "SELECT COMMENTS FROM SYS.TABLES WHERE SCHEMA_NAME = ? AND TABLE_NAME = ?" );
+    QVariant comment = conn.executeScalar( sql, { mSchemaName, mTableName } );
+    if ( !comment.isNull() )
+      mLayerMetadata.setAbstract( comment.toString() );
+  }
 }
 
 void QgsHanaProvider::readSrsInformation( QgsHanaConnection &conn )
@@ -1417,7 +1440,15 @@ void QgsHanaProvider::readSrsInformation( QgsHanaConnection &conn )
     return;
 
   if ( mSrid < 0 )
-    mSrid = conn.getColumnSrid( mSchemaName, mTableName, mGeometryColumn );
+  {
+    if ( mIsQuery )
+      mSrid = conn.getColumnSrid( mQuerySource, mGeometryColumn );
+    else
+      mSrid = conn.getColumnSrid( mSchemaName, mTableName, mGeometryColumn );
+
+    if ( mSrid < 0 )
+      return;
+  }
 
   QgsRectangle ext;
   bool isRoundEarth = false;

diff --git a/tests/src/python/test_provider_hana.py b/tests/src/python/test_provider_hana.py
@@ -24,12 +24,15 @@
     NULL,
     QgsCoordinateReferenceSystem,
     QgsDataProvider,
+    QgsDataSourceUri,
     QgsFeatureRequest,
     QgsFeature,
     QgsFieldConstraints,
     QgsProviderRegistry,
     QgsRectangle,
-    QgsSettings)
+    QgsSettings,
+    QgsVectorDataProvider,
+    QgsWkbTypes)
 from qgis.testing import start_app, unittest
 from test_hana_utils import QgsHanaProviderUtils
 from utilities import unitTestDataPath
@@ -226,6 +229,66 @@ def testCompositeUniqueConstraints(self):
         self.assertFalse(bool(vl.fieldConstraints(val2_field_idx) & QgsFieldConstraints.ConstraintUnique))
         self.assertFalse(bool(vl.fieldConstraints(val3_field_idx) & QgsFieldConstraints.ConstraintUnique))
 
+    def testQueryLayers(self):
+        def test_query(query, key, geometry, attribute_names, wkb_type=QgsWkbTypes.NoGeometry):
+            uri = QgsDataSourceUri()
+            uri.setSchema(self.schemaName)
+            uri.setTable(query)
+            uri.setKeyColumn(key)
+            uri.setGeometryColumn(geometry)
+            vl = self.createVectorLayer(uri.uri(False), 'testquery')
+
+            for capability in [QgsVectorDataProvider.SelectAtId,
+                               QgsVectorDataProvider.TransactionSupport,
+                               QgsVectorDataProvider.CircularGeometries,
+                               QgsVectorDataProvider.ReadLayerMetadata]:
+                self.assertTrue(vl.dataProvider().capabilities() & capability)
+
+            for capability in [QgsVectorDataProvider.AddAttributes,
+                               QgsVectorDataProvider.ChangeAttributeValues,
+                               QgsVectorDataProvider.DeleteAttributes,
+                               QgsVectorDataProvider.RenameAttributes,
+                               QgsVectorDataProvider.AddFeatures,
+                               QgsVectorDataProvider.ChangeFeatures,
+                               QgsVectorDataProvider.DeleteFeatures,
+                               QgsVectorDataProvider.ChangeGeometries,
+                               QgsVectorDataProvider.FastTruncate]:
+                self.assertFalse(vl.dataProvider().capabilities() & capability)
+
+            fields = vl.dataProvider().fields()
+            self.assertCountEqual(attribute_names, fields.names())
+            for field_idx in vl.primaryKeyAttributes():
+                self.assertIn(fields[field_idx].name(), key.split(","))
+                self.assertEqual(len(vl.primaryKeyAttributes()) == 1,
+                                 bool(vl.fieldConstraints(field_idx) & QgsFieldConstraints.ConstraintUnique))
+            if fields.count() > 0:
+                if vl.featureCount() == 0:
+                    self.assertEqual(QVariant(), vl.maximumValue(0))
+                    self.assertEqual(QVariant(), vl.minimumValue(0))
+                else:
+                    vl.maximumValue(0)
+                    vl.minimumValue(0)
+            self.assertEqual(vl.featureCount(), len([f for f in vl.getFeatures()]))
+            self.assertFalse(vl.addFeatures([QgsFeature()]))
+            self.assertFalse(vl.deleteFeatures([0]))
+            self.assertEqual(wkb_type, vl.wkbType())
+            self.assertEqual(wkb_type == QgsWkbTypes.NoGeometry or wkb_type == QgsWkbTypes.Unknown,
+                             vl.extent().isNull())
+
+        test_query('(SELECT * FROM DUMMY)', None, None, ['DUMMY'], QgsWkbTypes.NoGeometry)
+        test_query('(SELECT CAST(NULL AS INT) ID1, CAST(NULL AS INT) ID2, CAST(NULL AS ST_GEOMETRY) SHAPE FROM DUMMY)',
+                   'ID1,ID2', None, ['ID1', 'ID2', 'SHAPE'], QgsWkbTypes.NoGeometry)
+        test_query('(SELECT CAST(1 AS INT) ID1, CAST(NULL AS BIGINT) ID2 FROM DUMMY)',
+                   'ID1', None, ['ID1', 'ID2'], QgsWkbTypes.NoGeometry)
+        test_query('(SELECT CAST(NULL AS INT) ID1, CAST(NULL AS INT) ID2, CAST(NULL AS ST_GEOMETRY) SHAPE FROM DUMMY)',
+                   None, 'SHAPE', ['ID1', 'ID2'], QgsWkbTypes.Unknown)
+        test_query('(SELECT CAST(NULL AS INT) ID1, CAST(NULL AS BIGINT) ID2, CAST(NULL AS ST_GEOMETRY) SHAPE FROM '
+                   'DUMMY)', 'ID2', 'SHAPE', ['ID1', 'ID2'], QgsWkbTypes.Unknown)
+        test_query('(SELECT CAST(NULL AS INT) ID1, CAST(NULL AS ST_GEOMETRY) SHAPE1, CAST(NULL AS ST_GEOMETRY) SHAPE2 '
+                   'FROM DUMMY)', 'ID1', 'SHAPE1', ['ID1', 'SHAPE2'], QgsWkbTypes.Unknown)
+        test_query(f'(SELECT "pk" AS "key", "cnt", "geom" AS "g" FROM "{self.schemaName}"."some_data")',
+                   'key', 'g', ['key', 'cnt'], QgsWkbTypes.Point)
+
     def testBooleanType(self):
         create_sql = f'CREATE TABLE "{self.schemaName}"."boolean_type" ( ' \
             '"id" INTEGER NOT NULL PRIMARY KEY,' \