Determine pkey in Oracle view

qgis · May 13, 2020 · 497233d · 497233d
1 parent dee5882
commit 497233d
Show file tree

Hide file tree

Showing 2 changed files with 33 additions and 2 deletions.
diff --git a/src/providers/oracle/qgsoracleprovider.cpp b/src/providers/oracle/qgsoracleprovider.cpp
@@ -895,8 +895,8 @@ bool QgsOracleProvider::determinePrimaryKey()
   if ( !mIsQuery )
   {
     if ( !exec( qry, QString( "SELECT column_name"
-                              " FROM all_ind_columns a"
-                              " JOIN all_constraints b ON a.index_name=constraint_name AND a.index_owner=b.owner"
+                              " 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 ) )
     {

diff --git a/tests/src/python/test_provider_oracle.py b/tests/src/python/test_provider_oracle.py
@@ -726,6 +726,37 @@ def testGetFeatureFidInvalid(self):
         feature = vl.getFeature(2)
         self.assertTrue(feature.isValid())
 
+    def testDeterminePKOnView(self):
+        """
+        Determine primary key on a view if primary key constraint (disabled) exists on view
+        """
+
+        self.execSQLCommand('DROP TABLE "QGIS"."TABLE_TESTPKS"', ignore_errors=True)
+        self.execSQLCommand("""CREATE TABLE "QGIS"."TABLE_TESTPKS" (pk1 INTEGER, DESCRIPTION VARCHAR2(25), pk2 NUMBER, CONSTRAINT cons_pk PRIMARY KEY(pk1, pk2))""")
+        self.execSQLCommand("""INSERT INTO "QGIS"."TABLE_TESTPKS" VALUES(1000,'Desc for 1st record', 1)""")
+        self.execSQLCommand("""INSERT INTO "QGIS"."TABLE_TESTPKS" VALUES(2000,'Desc for 2nd record', 2)""")
+        self.execSQLCommand("""CREATE OR REPLACE VIEW "QGIS"."VIEW_TESTPKS" AS SELECT * FROM "QGIS"."TABLE_TESTPKS" """)
+
+        vl = QgsVectorLayer(
+            self.dbconn + ' sslmode=disable table="QGIS"."TABLE_TESTPKS" sql=',
+            'test', 'oracle')
+
+        self.assertEqual(vl.dataProvider().pkAttributeIndexes(), [0, 2])
+
+        vl = QgsVectorLayer(
+            self.dbconn + ' sslmode=disable table="QGIS"."VIEW_TESTPKS" sql=',
+            'test', 'oracle')
+
+        self.assertEqual(vl.dataProvider().pkAttributeIndexes(), [])
+
+        self.execSQLCommand("""ALTER VIEW VIEW_TESTPKS ADD CONSTRAINT const_view_pks PRIMARY KEY (pk1,pk2) DISABLE""")
+
+        vl = QgsVectorLayer(
+            self.dbconn + ' sslmode=disable table="QGIS"."VIEW_TESTPKS" sql=',
+            'test', 'oracle')
+
+        self.assertEqual(vl.dataProvider().pkAttributeIndexes(), [0, 2])
+
 
 if __name__ == '__main__':
     unittest.main()