HANA: Fix bug when writing attributes with a geometry column

qgis · May 4, 2021 · 747db18 · 747db18
1 parent e973852
commit 747db18
Show file tree

Hide file tree

Showing 6 changed files with 78 additions and 13 deletions.
diff --git a/src/providers/hana/qgshanaconnection.cpp b/src/providers/hana/qgshanaconnection.cpp
@@ -94,10 +94,12 @@ QgsField AttributeField::toQgsField() const
     case SQLDataTypes::TypeTimestamp:
       fieldType = QVariant::DateTime;
       break;
-    case 29812: // ST_GEOMETRY, ST_POINT
-      fieldType = QVariant::String;
-      break;
     default:
+      if ( isGeometry() )
+        // There are two options how to treat geometry columns that are attributes:
+        // 1. Type is QVariant::String. The value is provided as WKT and editable.
+        // 2. Type is QVariant::ByteArray. The value is provided as BLOB and uneditable.
+        fieldType = QVariant::String;
       break;
   }
 
@@ -669,8 +671,11 @@ void QgsHanaConnection::readQueryFields( const QString &sql, const QString &sche
     ResultSetMetaDataUnicodeRef rsmd = stmt->getMetaDataUnicode();
     for ( unsigned short i = 1; i <= rsmd->getColumnCount(); ++i )
     {
+      QString schema = QString::fromStdU16String( rsmd->getSchemaName( i ) );
+      if ( schema.isEmpty() )
+        schema = schemaName;
       AttributeField field;
-      field.schemaName = QString::fromStdU16String( rsmd->getSchemaName( i ) );
+      field.schemaName = schema;
       field.tableName = QString::fromStdU16String( rsmd->getTableName( i ) );
       field.name = QString::fromStdU16String( rsmd->getColumnName( i ) );
       field.typeName = QString::fromStdU16String( rsmd->getColumnTypeName( i ) );
@@ -680,8 +685,10 @@ void QgsHanaConnection::readQueryFields( const QString &sql, const QString &sche
       field.isAutoIncrement = rsmd->isAutoIncrement( i );
       field.size = static_cast<int>( rsmd->getColumnLength( i ) );
       field.precision = -1;
+      if ( field.isGeometry() )
+        field.srid = getColumnSrid( schema, field.tableName, field.name );
       // As field comments cannot be retrieved via ODBC, we get it from SYS.TABLE_COLUMNS.
-      field.comment = getColumnComments( !field.schemaName.isEmpty() ? field.schemaName : schemaName, field.tableName, field.name );
+      field.comment = getColumnComments( schema, field.tableName, field.name );
 
       callback( field );
     }

diff --git a/src/providers/hana/qgshanaconnection.h b/src/providers/hana/qgshanaconnection.h
@@ -32,6 +32,7 @@ struct AttributeField
   QString tableName;
   QString name;
   short type;
+  int srid;
   QString typeName;
   int size;
   int precision;
@@ -40,6 +41,8 @@ struct AttributeField
   bool isSigned;
   QString comment;
 
+  bool isGeometry() const { return type == 29812; /* ST_GEOMETRY, ST_POINT */ }
+
   QgsField toQgsField() const;
 };
 

diff --git a/src/providers/hana/qgshanafeatureiterator.cpp b/src/providers/hana/qgshanafeatureiterator.cpp
@@ -57,6 +57,16 @@ namespace
 
     return bbox.intersect( QgsRectangle( minx, miny, maxx, maxy ) );
   }
+
+  QString fieldExpression( const QgsField &field )
+  {
+    QString typeName = field.typeName();
+    QString fieldName = QgsHanaUtils::quotedIdentifier( field.name() );
+    if ( field.type() == QVariant::String &&
+         ( typeName == QLatin1String( "ST_GEOMETRY" ) || typeName == QLatin1String( "ST_POINT" ) ) )
+      return QStringLiteral( "%1.ST_ASWKT()" ).arg( fieldName );
+    return fieldName;
+  }
 }
 
 QgsHanaFeatureIterator::QgsHanaFeatureIterator(
@@ -339,18 +349,18 @@ QString QgsHanaFeatureIterator::buildSqlQuery( const QgsFeatureRequest &request
   // Add feature id column
   for ( int idx : qgis::as_const( mSource->mPrimaryKeyAttrs ) )
   {
-    QString fieldName = mSource->mFields.at( idx ).name();
-    sqlFields.push_back( QgsHanaUtils::quotedIdentifier( fieldName ) );
+    const QgsField &field = mSource->mFields.at( idx );
+    sqlFields.push_back( fieldExpression( field ) );
   }
 
   for ( int idx : qgis::as_const( attrIds ) )
   {
     if ( mSource->mPrimaryKeyAttrs.contains( idx ) )
       continue;
 
-    QString fieldName = mSource->mFields.at( idx ).name();
     mAttributesToFetch.append( idx );
-    sqlFields.push_back( QgsHanaUtils::quotedIdentifier( fieldName ) );
+    const QgsField &field = mSource->mFields.at( idx );
+    sqlFields.push_back( fieldExpression( field ) );
   }
 
   mHasAttributes = !mAttributesToFetch.isEmpty();

diff --git a/src/providers/hana/qgshanaprovider.cpp b/src/providers/hana/qgshanaprovider.cpp
@@ -257,8 +257,20 @@ namespace
         }
         break;
       default:
-        QgsDebugMsg( QStringLiteral( "Unknown value type ('%1') for parameter %2" )
-                     .arg( QString::number( field.type ), QString::number( paramIndex ) ) );
+        if ( field.isGeometry() )
+        {
+          if ( value.type() == QVariant::String )
+            stmt->setString( paramIndex, isNull ? String() : String( value.toString().toStdString() ) );
+          else if ( value.type() == QVariant::ByteArray )
+          {
+            QByteArray arr = value.toByteArray();
+            stmt->setBinary( paramIndex, isNull ? Binary() :  Binary( vector<char>( arr.begin(), arr.end() ) ) );
+          }
+        }
+        else
+          QgsDebugMsg( QStringLiteral( "Unknown value type ('%1') for parameter %2" )
+                       .arg( QString::number( field.type ), QString::number( paramIndex ) ) );
+        break;
     }
   }
 
@@ -631,7 +643,10 @@ bool QgsHanaProvider::addFeatures( QgsFeatureList &flist, Flags flags )
     }
 
     columnNames << QgsHanaUtils::quotedIdentifier( field.name );
-    values << QStringLiteral( "?" );
+    if ( field.isGeometry() && mFields.at( idx ).type() == QVariant::String )
+      values << QStringLiteral( "ST_GeomFromWKT(?, %1)" ).arg( QString::number( field.srid ) );
+    else
+      values << QStringLiteral( "?" );
     fieldIds << idx;
   }
 
@@ -1124,7 +1139,11 @@ bool QgsHanaProvider::changeAttributeValues( const QgsChangedAttributesMap &attr
           continue;
 
         pkChanged = pkChanged || mPrimaryKeyAttrs.contains( fieldIndex );
-        attrs << QStringLiteral( "%1=?" ).arg( QgsHanaUtils::quotedIdentifier( field.name ) );
+        if ( field.isGeometry() && mFields.at( fieldIndex ).type() == QVariant::String )
+          attrs << QStringLiteral( "%1=ST_GeomFromWKT(?, %2)" ).arg(
+                  QgsHanaUtils::quotedIdentifier( field.name ), QString::number( field.srid ) );
+        else
+          attrs << QStringLiteral( "%1=?" ).arg( QgsHanaUtils::quotedIdentifier( field.name ) );
       }
 
       if ( attrs.empty() )

diff --git a/src/providers/hana/qgshanaresultset.cpp b/src/providers/hana/qgshanaresultset.cpp
@@ -171,6 +171,8 @@ QVariant QgsHanaResultSet::getValue( unsigned short columnIndex )
     case SQLDataTypes::VarBinary:
     case SQLDataTypes::LongVarBinary:
       return QgsHanaUtils::toVariant( mResultSet->getBinary( columnIndex ) );
+    case 29812: /* ST_GEOMETRY, ST_POINT */
+      return QgsHanaUtils::toVariant( mResultSet->getBinary( columnIndex ) );
     default:
       QgsDebugMsg( QStringLiteral( "Unhandled HANA type %1" ).arg( QString::fromStdU16String( mMetadata->getColumnTypeName( columnIndex ) ) ) );
       return QVariant();

diff --git a/tests/src/python/test_provider_hana.py b/tests/src/python/test_provider_hana.py
@@ -297,6 +297,30 @@ def testBinaryTypeEdit(self):
         expected = {1: QByteArray(b'bbbvx'), 2: QByteArray(b'dddd')}
         self.assertEqual(values, expected)
 
+    def testGeometryAttributes(self):
+        create_sql = f'CREATE TABLE "{self.schemaName}"."geometry_attribute" ( ' \
+            'ID INTEGER NOT NULL PRIMARY KEY,' \
+            'GEOM1 ST_GEOMETRY(4326),' \
+            'GEOM2 ST_GEOMETRY(4326))'
+        insert_sql = f'INSERT INTO "{self.schemaName}"."geometry_attribute" (ID, GEOM1, GEOM2) ' \
+                     f'VALUES (?, ST_GeomFromText(?, 4326), ST_GeomFromText(?, 4326)) '
+        insert_args = [[1, 'POINT (1 2)', 'LINESTRING (0 0,1 1)']]
+        self.prepareTestTable('geometry_attribute', create_sql, insert_sql, insert_args)
+
+        vl = self.createVectorLayer(f'table="{self.schemaName}"."geometry_attribute" (GEOM1) sql=',
+                                    'testgeometryattribute')
+        fields = vl.dataProvider().fields()
+        self.assertEqual(fields.names(), ['ID', 'GEOM2'])
+        self.assertEqual(fields.at(fields.indexFromName('ID')).type(), QVariant.Int)
+        self.assertEqual(fields.at(fields.indexFromName('GEOM2')).type(), QVariant.String)
+        values = {feat['ID']: feat['GEOM2'] for feat in vl.getFeatures()}
+        self.assertEqual(values, {1: 'LINESTRING (0 0,1 1)'})
+
+        # change attribute value
+        self.assertTrue(vl.dataProvider().changeAttributeValues({1: {1: 'LINESTRING (0 0,2 2)'}}))
+        values = {feat['ID']: feat['GEOM2'] for feat in vl.getFeatures()}
+        self.assertEqual(values, {1: 'LINESTRING (0 0,2 2)'})
+
     def testFilterRectOutsideSrsExtent(self):
         """Test filterRect which partially lies outside of the srs extent"""
         self.source.setSubsetString(None)