Fix primary key issues in HANA

qgis · Jan 28, 2021 · 2632ca6 · 2632ca6
1 parent c4a69f6
commit 2632ca6
Show file tree

Hide file tree

Showing 11 changed files with 178 additions and 60 deletions.
diff --git a/src/providers/hana/qgshanaconnection.cpp b/src/providers/hana/qgshanaconnection.cpp
@@ -553,7 +553,7 @@ void QgsHanaConnection::readLayerInfo( QgsHanaLayerProperty &layerProperty )
 {
   layerProperty.srid = getColumnSrid( layerProperty.schemaName, layerProperty.tableName, layerProperty.geometryColName );
   layerProperty.type = getColumnGeometryType( layerProperty.schemaName, layerProperty.tableName, layerProperty.geometryColName );
-  layerProperty.pkCols = getPrimaryeKeyCandidates( layerProperty );
+  layerProperty.pkCols = getPrimaryKeyCandidates( layerProperty );
 }
 
 QVector<QgsHanaSchemaProperty> QgsHanaConnection::getSchemas( const QString &ownerName )
@@ -585,7 +585,7 @@ QVector<QgsHanaSchemaProperty> QgsHanaConnection::getSchemas( const QString &own
   return list;
 }
 
-QStringList QgsHanaConnection::getLayerPrimaryeKey( const QString &schemaName, const QString &tableName )
+QStringList QgsHanaConnection::getLayerPrimaryKey( const QString &schemaName, const QString &tableName )
 {
   try
   {
@@ -609,7 +609,7 @@ QStringList QgsHanaConnection::getLayerPrimaryeKey( const QString &schemaName, c
   }
 }
 
-QStringList QgsHanaConnection::getPrimaryeKeyCandidates( const QgsHanaLayerProperty &layerProperty )
+QStringList QgsHanaConnection::getPrimaryKeyCandidates( const QgsHanaLayerProperty &layerProperty )
 {
   if ( !layerProperty.isView )
     return QStringList();
@@ -618,6 +618,11 @@ QStringList QgsHanaConnection::getPrimaryeKeyCandidates( const QgsHanaLayerPrope
   QgsHanaResultSetRef rsColumns = getColumns( layerProperty.schemaName, layerProperty.tableName, QStringLiteral( "%" ) );
   while ( rsColumns->next() )
   {
+    int dataType = rsColumns->getValue( 5/*DATA_TYPE */ ).toInt();
+    // We exclude GEOMETRY and LOB columns
+    if ( dataType == 29812 /* GEOMETRY TYPE */ || dataType == SQLDataTypes::LongVarBinary ||
+         dataType == SQLDataTypes::LongVarChar || dataType == SQLDataTypes::WLongVarChar )
+      continue;
     ret << rsColumns->getValue( 4/*COLUMN_NAME */ ).toString();
   }
   rsColumns->close();

diff --git a/src/providers/hana/qgshanaconnection.h b/src/providers/hana/qgshanaconnection.h
@@ -66,7 +66,7 @@ class QgsHanaConnection : public QObject
       bool userTablesOnly = true );
     void readLayerInfo( QgsHanaLayerProperty &layerProperty );
     QVector<QgsHanaSchemaProperty> getSchemas( const QString &ownerName );
-    QStringList getLayerPrimaryeKey( const QString &schemaName, const QString &tableName );
+    QStringList getLayerPrimaryKey( const QString &schemaName, const QString &tableName );
     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 );
@@ -82,7 +82,7 @@ class QgsHanaConnection : public QObject
   private:
     QgsHanaConnection( odbc::ConnectionRef connection, const QgsDataSourceUri &uri );
 
-    QStringList getPrimaryeKeyCandidates( const QgsHanaLayerProperty &layerProperty );
+    QStringList getPrimaryKeyCandidates( const QgsHanaLayerProperty &layerProperty );
 
     odbc::PreparedStatementRef createPreparedStatement( const QString &sql, const QVariantList &args );
 

diff --git a/src/providers/hana/qgshanadataitems.cpp b/src/providers/hana/qgshanadataitems.cpp
@@ -242,7 +242,6 @@ QString QgsHanaLayerItem::comments() const
 
 QString QgsHanaLayerItem::createUri() const
 {
-  QString pkColName = !mLayerProperty.pkCols.isEmpty() ? mLayerProperty.pkCols.at( 0 ) : QString();
   QgsHanaConnectionItem *connItem = qobject_cast<QgsHanaConnectionItem *>( parent() ?
                                     parent()->parent() : nullptr );
 
@@ -253,9 +252,26 @@ QString QgsHanaLayerItem::createUri() const
   }
 
   QgsHanaSettings settings( connItem->name(), true );
+
+  QStringList pkColumns;
+  if ( !mLayerProperty.pkCols.isEmpty() )
+  {
+    const QStringList pkColumnsStored = settings.keyColumns( mLayerProperty.schemaName, mLayerProperty.tableName );
+    if ( !pkColumnsStored.empty() )
+    {
+      // We check whether the primary key columns still exist.
+      auto intersection = pkColumnsStored.toSet().intersect( mLayerProperty.pkCols.toSet() );
+      if ( intersection.size() == pkColumnsStored.size() )
+      {
+        for ( const auto &column : pkColumnsStored )
+          pkColumns << QgsHanaUtils::quotedIdentifier( column );
+      }
+    }
+  }
+
   QgsDataSourceUri uri = settings.toDataSourceUri();
   uri.setDataSource( mLayerProperty.schemaName, mLayerProperty.tableName,
-                     mLayerProperty.geometryColName, mLayerProperty.sql, pkColName );
+                     mLayerProperty.geometryColName, mLayerProperty.sql, pkColumns.join( ',' ) );
   uri.setWkbType( mLayerProperty.type );
   if ( uri.wkbType() != QgsWkbTypes::NoGeometry )
     uri.setSrid( QString::number( mLayerProperty.srid ) );

diff --git a/src/providers/hana/qgshanaprovider.cpp b/src/providers/hana/qgshanaprovider.cpp
@@ -1406,7 +1406,7 @@ void QgsHanaProvider::determinePrimaryKey()
     QgsHanaConnectionRef conn( mUri );
     if ( conn->isTable( mSchemaName, mTableName ) )
     {
-      QStringList layerPrimaryKey = conn->getLayerPrimaryeKey( mSchemaName, mTableName );
+      QStringList layerPrimaryKey = conn->getLayerPrimaryKey( mSchemaName, mTableName );
       primaryKey = QgsHanaPrimaryKeyUtils::determinePrimaryKeyFromColumns( layerPrimaryKey, mAttributeFields );
     }
     else

diff --git a/src/providers/hana/qgshanaproviderconnection.cpp b/src/providers/hana/qgshanaproviderconnection.cpp
@@ -338,7 +338,7 @@ QList<QgsHanaProviderConnection::TableProperty> QgsHanaProviderConnection::table
         }
         else  // Fetch and set the real pks
         {
-          QStringList pks = conn->getLayerPrimaryeKey( layerInfo.schemaName, layerInfo.tableName );
+          QStringList pks = conn->getLayerPrimaryKey( layerInfo.schemaName, layerInfo.tableName );
           property.setPrimaryKeyColumns( pks );
         }
         tables.push_back( property );

diff --git a/src/providers/hana/qgshanasettings.cpp b/src/providers/hana/qgshanasettings.cpp
@@ -14,6 +14,7 @@
  * (at your option) any later version.
  *
  ***************************************************************************/
+#include "qgis.h"
 #include "qgshanasettings.h"
 #include "qgssettings.h"
 
@@ -48,6 +49,19 @@ QString QgsHanaSettings::port() const
     return mIdentifier;
 }
 
+QStringList QgsHanaSettings::keyColumns( const QString &schemaName, const QString &objectName ) const
+{
+  return mKeyColumns.value( schemaName ).value( objectName );
+}
+
+void QgsHanaSettings::setKeyColumns( const QString &schemaName, const QString &objectName, const QStringList &columnNames )
+{
+  if ( columnNames.empty() )
+    mKeyColumns[schemaName].remove( objectName );
+  else
+    mKeyColumns[schemaName][objectName] = columnNames;
+}
+
 void QgsHanaSettings::setFromDataSourceUri( const QgsDataSourceUri &uri )
 {
   mDriver = uri.driver();
@@ -102,6 +116,7 @@ QgsDataSourceUri QgsHanaSettings::toDataSourceUri() const
   uri.setConnection( mHost, port(), mDatabase, mUserName, mPassword );
   uri.setDriver( mDriver );
   uri.setSchema( mSchema );
+
   if ( mSslEnabled )
   {
     uri.setParam( QStringLiteral( "sslEnabled" ), QStringLiteral( "true" ) );
@@ -122,7 +137,7 @@ QgsDataSourceUri QgsHanaSettings::toDataSourceUri() const
 void QgsHanaSettings::load()
 {
   QgsSettings settings;
-  QString key = path();
+  const QString key = path();
   mDriver = settings.value( key + "/driver" ).toString();
   mHost = settings.value( key + "/host" ).toString();
   mIdentifierType = settings.value( key + "/identifierType" ).toUInt();
@@ -145,11 +160,33 @@ void QgsHanaSettings::load()
   mSslTrustStore = settings.value( key + "/sslTrustStore" ).toString();
   mSslValidateCertificate = settings.value( key + "/sslValidateCertificate", true ).toBool();
   mSslHostNameInCertificate = settings.value( key + "/sslHostNameInCertificate" ).toString();
+
+  const QString keysPath = key + "/keys";
+  settings.beginGroup( keysPath );
+  const QStringList schemaNames = settings.childGroups();
+  if ( !schemaNames.empty() )
+  {
+    for ( const QString &schemaName : schemaNames )
+    {
+      const QString schemaKey = keysPath + "/" + schemaName;
+      QgsSettings subSettings;
+      subSettings.beginGroup( schemaKey );
+      const QStringList objectNames = subSettings.childKeys();
+      if ( objectNames.empty() )
+        continue;
+      for ( const QString &objectName : objectNames )
+      {
+        QVariant value = subSettings.value( objectName );
+        if ( !value.isNull() )
+          mKeyColumns[schemaName][objectName] = value.toStringList();
+      }
+    }
+  }
 }
 
 void QgsHanaSettings::save()
 {
-  QString key( path() );
+  const QString key( path() );
   QgsSettings settings;
   settings.setValue( key + "/driver", mDriver );
   settings.setValue( key + "/host", mHost );
@@ -171,12 +208,32 @@ void QgsHanaSettings::save()
   settings.setValue( key + "/sslTrustStore", mSslTrustStore );
   settings.setValue( key + "/sslValidateCertificate", mSslValidateCertificate );
   settings.setValue( key + "/sslHostNameInCertificate", mSslHostNameInCertificate );
+
+  if ( !mKeyColumns.empty() )
+  {
+    const QString keysPath = key + "/keys/";
+    settings.beginGroup( keysPath );
+    const QStringList schemaNames = mKeyColumns.keys();
+    for ( const QString &schemaName : schemaNames )
+    {
+      const auto &schemaKeys = mKeyColumns[schemaName];
+      if ( schemaKeys.empty() )
+        continue;
+      const QStringList objectNames = schemaKeys.keys();
+      settings.beginGroup( schemaName );
+      for ( const QString &objectName : objectNames )
+        settings.setValue( objectName, schemaKeys[objectName] );
+      settings.endGroup();
+    }
+    settings.endGroup();
+  }
+
   settings.sync();
 }
 
 void QgsHanaSettings::removeConnection( const QString &name )
 {
-  QString key( getBaseKey() + name );
+  const QString key( getBaseKey() + name );
   QgsSettings settings;
   settings.remove( key + "/driver" );
   settings.remove( key + "/host" );
@@ -198,6 +255,7 @@ void QgsHanaSettings::removeConnection( const QString &name )
   settings.remove( key + "/sslTrustStore" );
   settings.remove( key + "/sslValidateCertificate" );
   settings.remove( key + "/sslHostNameInCertificate" );
+  settings.remove( key + "/keys" );
   settings.remove( key );
   settings.sync();
 }

diff --git a/src/providers/hana/qgshanasettings.h b/src/providers/hana/qgshanasettings.h
@@ -182,6 +182,16 @@ class QgsHanaSettings
      */
     QString port() const;
 
+    /**
+     * Gets the key columns for the given database object.
+     */
+    QStringList keyColumns( const QString &schemaName, const QString &objectName ) const;
+
+    /**
+     * Sets the key columns for the given database object.
+     */
+    void setKeyColumns( const QString &schemaName, const QString &objectName, const QStringList &columnNames );
+
     /**
      * Sets values from a RDBMS data source URI.
      */
@@ -234,6 +244,7 @@ class QgsHanaSettings
     QString mSslTrustStore;
     bool mSslValidateCertificate = false;
     QString mSslHostNameInCertificate;
+    QMap<QString, QMap<QString, QStringList>> mKeyColumns;
 };
 
 #endif // QGSHANAPSETTINGS_H
diff --git a/src/providers/hana/qgshanasourceselect.cpp b/src/providers/hana/qgshanasourceselect.cpp
@@ -457,7 +457,7 @@ void QgsHanaSourceSelect::mSearchModeComboBox_currentIndexChanged( const QString
 
 void QgsHanaSourceSelect::setLayerType( const QgsHanaLayerProperty &layerProperty )
 {
-  mTableModel.addTableEntry( layerProperty );
+  mTableModel.addTableEntry( mConnectionName, layerProperty );
 }
 
 QgsHanaSourceSelect::~QgsHanaSourceSelect()
@@ -495,6 +495,16 @@ void QgsHanaSourceSelect::populateConnectionList()
   cmbConnections->setDisabled( cmbConnections->count() == 0 );
 }
 
+QStringList QgsHanaSourceSelect::selectedTables()
+{
+  return mSelectedTables;
+}
+
+QString QgsHanaSourceSelect::connectionInfo()
+{
+  return mConnectionInfo;
+}
+
 // Slot for performing action when the Add button is clicked
 void QgsHanaSourceSelect::addButtonClicked()
 {
@@ -506,7 +516,7 @@ void QgsHanaSourceSelect::addButtonClicked()
     if ( idx.column() != QgsHanaTableModel::DbtmTable )
       continue;
 
-    QString uri = mTableModel.layerURI( mProxyModel.mapToSource( idx ), QgsHanaUtils::connectionInfo( mDataSrcUri ) );
+    QString uri = mTableModel.layerURI( mProxyModel.mapToSource( idx ), mConnectionName, mConnectionInfo );
     if ( uri.isNull() )
       continue;
 
@@ -536,10 +546,11 @@ void QgsHanaSourceSelect::btnConnect_clicked()
     return;
   }
 
+  const QString connName = cmbConnections->currentText();
+
   QModelIndex rootItemIndex = mTableModel.indexFromItem( mTableModel.invisibleRootItem() );
   mTableModel.removeRows( 0, mTableModel.rowCount( rootItemIndex ), rootItemIndex );
 
-  const QString connName = cmbConnections->currentText();
   QgsHanaSettings settings( connName, true );
   settings.setAllowGeometrylessTables( cbxAllowGeometrylessTables->isChecked() );
 
@@ -554,12 +565,13 @@ void QgsHanaSourceSelect::btnConnect_clicked()
     return;
   }
 
-  mDataSrcUri = uri;
+  mConnectionName = connName;
+  mConnectionInfo = QgsHanaUtils::connectionInfo( uri );
 
   QApplication::setOverrideCursor( Qt::BusyCursor );
 
-  mColumnTypeThread = qgis::make_unique<QgsHanaColumnTypeThread>( settings.name(), uri, settings.allowGeometrylessTables(), settings.userTablesOnly() );
-  mColumnTypeTask = qgis::make_unique<QgsProxyProgressTask>( tr( "Scanning tables for %1" ).arg( cmbConnections->currentText() ) );
+  mColumnTypeThread = qgis::make_unique<QgsHanaColumnTypeThread>( mConnectionName, uri, settings.allowGeometrylessTables(), settings.userTablesOnly() );
+  mColumnTypeTask = qgis::make_unique<QgsProxyProgressTask>( tr( "Scanning tables for %1" ).arg( mConnectionName ) );
   QgsApplication::taskManager()->addTask( mColumnTypeTask.get() );
 
   connect( mColumnTypeThread.get(), &QgsHanaColumnTypeThread::setLayerType,
@@ -597,16 +609,6 @@ void QgsHanaSourceSelect::columnThreadFinished()
   finishList();
 }
 
-QStringList QgsHanaSourceSelect::selectedTables()
-{
-  return mSelectedTables;
-}
-
-QString QgsHanaSourceSelect::connectionInfo()
-{
-  return QgsHanaUtils::connectionInfo( mDataSrcUri );
-}
-
 void QgsHanaSourceSelect::refresh()
 {
   populateConnectionList();
@@ -621,7 +623,7 @@ void QgsHanaSourceSelect::setSql( const QModelIndex &index )
   }
 
   QModelIndex idx = mProxyModel.mapToSource( index );
-  QString uri = mTableModel.layerURI( idx, QgsHanaUtils::connectionInfo( mDataSrcUri ) );
+  QString uri = mTableModel.layerURI( idx, mConnectionName, mConnectionInfo );
   if ( uri.isNull() )
   {
     QgsDebugMsg( "no uri" );

diff --git a/src/providers/hana/qgshanasourceselect.h b/src/providers/hana/qgshanasourceselect.h
@@ -144,7 +144,8 @@ class QgsHanaSourceSelect : public QgsAbstractDataSourceWidget, private Ui::QgsD
     void finishList();
     void showHelp();
 
-    QgsDataSourceUri mDataSrcUri;
+    QString mConnectionName;
+    QString mConnectionInfo;
     // A thread for detecting geometry types
     std::unique_ptr<QgsHanaColumnTypeThread> mColumnTypeThread;
     std::unique_ptr<QgsProxyProgressTask> mColumnTypeTask;