Retrieve real primary keys columns

qgis · Jan 29, 2021 · d79ade0 · d79ade0
1 parent 2fc9048
commit d79ade0
Show file tree

Hide file tree

Showing 5 changed files with 50 additions and 25 deletions.
diff --git a/src/providers/oracle/qgsoracleconn.cpp b/src/providers/oracle/qgsoracleconn.cpp
@@ -1058,6 +1058,33 @@ QString QgsOracleConn::createSpatialIndex( const QString &ownerName, const QStri
   return QString( "QGIS_IDX_%1" ).arg( n, 10, 10, QChar( '0' ) );
 }
 
+QStringList QgsOracleConn::getPrimaryKeys( const QString &ownerName, const QString &tableName )
+{
+  QSqlQuery qry( mDatabase );
+
+  QStringList result;
+
+  if ( !exec( qry, QString( "SELECT column_name"
+                            " FROM all_cons_columns a"
+                            " JOIN all_constraints b ON a.constraint_name=b.constraint_name AND a.owner=b.owner"
+                            " WHERE b.constraint_type='P' AND b.owner=? AND b.table_name=?" ),
+              QVariantList() << ownerName << tableName ) )
+  {
+    QgsMessageLog::logMessage( tr( "Unable to execute the query.\nThe error message from the database was:\n%1.\nSQL: %2" )
+                               .arg( qry.lastError().text() )
+                               .arg( qry.lastQuery() ), tr( "Oracle" ) );
+    return result;
+  }
+
+  while ( qry.next() )
+  {
+    QString name = qry.value( 0 ).toString();
+    result << name;
+  }
+
+  return result;
+}
+
 
 QgsPoolOracleConn::QgsPoolOracleConn( const QString &connInfo )
   : mConn( QgsOracleConnPool::instance()->acquireConnection( connInfo ) )

diff --git a/src/providers/oracle/qgsoracleconn.h b/src/providers/oracle/qgsoracleconn.h
@@ -224,6 +224,11 @@ class QgsOracleConn : public QObject
      */
     QString createSpatialIndex( const QString &ownerName, const QString &tableName, const QString &geometryColumn );
 
+    /**
+     * Returns list of defined primary keys for \a tableName table in \a ownerName schema/user
+     */
+    QStringList getPrimaryKeys( const QString &ownerName, const QString &tableName );
+
     static const int sGeomTypeSelectLimit;
 
     static QgsWkbTypes::Type wkbTypeFromDatabase( int gtype );

diff --git a/src/providers/oracle/qgsoracleprovider.cpp b/src/providers/oracle/qgsoracleprovider.cpp
@@ -851,24 +851,12 @@ bool QgsOracleProvider::determinePrimaryKey()
   QSqlQuery qry( *conn );
   if ( !mIsQuery )
   {
-    if ( !exec( qry, QString( "SELECT column_name"
-                              " FROM all_cons_columns a"
-                              " JOIN all_constraints b ON a.constraint_name=b.constraint_name AND a.owner=b.owner"
-                              " WHERE b.constraint_type='P' AND b.owner=? AND b.table_name=?" ),
-                QVariantList() << mOwnerName << mTableName ) )
-    {
-      QgsMessageLog::logMessage( tr( "Unable to execute the query.\nThe error message from the database was:\n%1.\nSQL: %2" )
-                                 .arg( qry.lastError().text() )
-                                 .arg( qry.lastQuery() ), tr( "Oracle" ) );
-      return false;
-    }
+    const QStringList pkeys = conn->getPrimaryKeys( mOwnerName, mTableName );
 
     bool isInt = true;
 
-    while ( qry.next() )
+    for ( QString name : pkeys )
     {
-      QString name = qry.value( 0 ).toString();
-
       int idx = mAttributeFields.indexFromName( name );
       if ( idx < 0 )
       {

diff --git a/src/providers/oracle/qgsoracleproviderconnection.cpp b/src/providers/oracle/qgsoracleproviderconnection.cpp
@@ -356,12 +356,9 @@ QList<QgsAbstractDatabaseProviderConnection::TableProperty> QgsOracleProviderCon
     property.setSchema( pr.ownerName );
     property.setGeometryColumn( pr.geometryColName );
     property.setGeometryColumnCount( prFlags & QgsAbstractDatabaseProviderConnection::TableFlag::Aspatial ? 0 : 1 );
-
-    // TODO These are candidates, not actual PKs
-    property.setPrimaryKeyColumns( pr.pkCols );
+    property.setPrimaryKeyColumns( pr.isView ? pr.pkCols : conn->getPrimaryKeys( pr.ownerName, pr.tableName ) );
 
     tables.push_back( property );
-
   }
 
   return tables;

diff --git a/tests/src/python/test_qgsproviderconnection_oracle.py b/tests/src/python/test_qgsproviderconnection_oracle.py
@@ -65,13 +65,6 @@ def setUpClass(cls):
         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 execSQLCommand(self, sql, ignore_errors=False):
         self.assertTrue(self.conn)
         query = QSqlQuery(self.conn)
@@ -158,6 +151,21 @@ def test_configuration(self):
         self.assertEqual(ds_uri.param('dbworkspace'), 'workspace')
         conn.remove('myconf')
 
+    def test_pkcols(self):
+        """Test retrieval of primary columns"""
+
+        self.execSQLCommand("""CREATE OR REPLACE VIEW "QGIS"."SOME_DATA_VIEW" AS SELECT * FROM "QGIS"."SOME_DATA" """)
+
+        md = QgsProviderRegistry.instance().providerMetadata('oracle')
+        conn = md.createConnection(self.uri, {})
+        tables = conn.tables('QGIS')
+
+        tables_dict = dict([(table.tableName(), table.primaryKeyColumns()) for table in tables])
+
+        self.assertEqual(sorted(tables_dict['SOME_DATA_VIEW']), ['GEOM', 'cnt', 'date', 'dt', 'name', 'name2', 'num_char', 'pk', 'time'])
+        self.assertEqual(sorted(tables_dict['SOME_DATA']), ['pk'])
+        self.assertEqual(sorted(tables_dict['POINT_DATA_IDENTITY']), ['pk'])
+
 
 if __name__ == '__main__':
     unittest.main()