Merge pull request #40890 from troopa81/fix_oracle_srid_createemptylayer

[Oracle] Fix metadata table srid when creating an empty layer
qgis · Jan 7, 2021 · 1754ecc · 1754ecc
2 parents 17500a7 + f5f7389
commit 1754ecc
Show file tree

Hide file tree

Showing 3 changed files with 40 additions and 23 deletions.
diff --git a/src/providers/oracle/qgsoracleprovider.cpp b/src/providers/oracle/qgsoracleprovider.cpp
@@ -3055,17 +3055,10 @@ QgsVectorLayerExporter::ExportError QgsOracleProvider::createEmptyLayer(
     QStringList parts = srs.authid().split( ":" );
     if ( parts.size() == 2 )
     {
-      // apparently some EPSG codes don't have the auth_name setup in cs_srs
-      if ( !exec( qry, QString( "SELECT srid FROM mdsys.cs_srs WHERE coalesce(auth_name,'EPSG')=? AND auth_srid=?" ),
-                  QVariantList() << parts[0] << parts[1] ) )
-      {
-        throw OracleException( tr( "Could not lookup authid %1:%2" ).arg( parts[0] ).arg( parts[1] ), qry );
-      }
-
-      if ( qry.next() )
-      {
-        srid = qry.value( 0 ).toInt();
-      }
+      const int id = parts[1].toInt();
+      QgsCoordinateReferenceSystem crs = lookupCrs( conn, id );
+      if ( crs == srs )
+        srid = id;
     }
 
     if ( srid == 0 )
@@ -3260,17 +3253,13 @@ QgsVectorLayerExporter::ExportError QgsOracleProvider::createEmptyLayer(
   return QgsVectorLayerExporter::NoError;
 }
 
-QgsCoordinateReferenceSystem QgsOracleProvider::crs() const
+QgsCoordinateReferenceSystem QgsOracleProvider::lookupCrs( QgsOracleConn *conn, int srsid )
 {
   QgsCoordinateReferenceSystem srs;
-  QgsOracleConn *conn = connectionRO();
-  if ( !conn )
-    return srs;
-
   QSqlQuery qry( *conn );
 
   // apparently some EPSG codes don't have the auth_name setup in cs_srs
-  if ( exec( qry, QString( "SELECT coalesce(auth_name,'EPSG'),auth_srid,wktext FROM mdsys.cs_srs WHERE srid=?" ), QVariantList() << mSrid ) )
+  if ( exec( qry, QString( "SELECT coalesce(auth_name,'EPSG'),auth_srid,wktext FROM mdsys.cs_srs WHERE srid=?" ), QVariantList() << srsid ) )
   {
     if ( qry.next() )
     {
@@ -3285,13 +3274,13 @@ QgsCoordinateReferenceSystem QgsOracleProvider::crs() const
     }
     else
     {
-      QgsMessageLog::logMessage( tr( "Oracle SRID %1 not found." ).arg( mSrid ), tr( "Oracle" ) );
+      QgsMessageLog::logMessage( tr( "Oracle SRID %1 not found." ).arg( srsid ), tr( "Oracle" ) );
     }
   }
   else
   {
     QgsMessageLog::logMessage( tr( "Lookup of Oracle SRID %1 failed.\nSQL: %2\nError: %3" )
-                               .arg( mSrid )
+                               .arg( srsid )
                                .arg( qry.lastQuery() )
                                .arg( qry.lastError().text() ),
                                tr( "Oracle" ) );
@@ -3300,6 +3289,15 @@ QgsCoordinateReferenceSystem QgsOracleProvider::crs() const
   return srs;
 }
 
+QgsCoordinateReferenceSystem QgsOracleProvider::crs() const
+{
+  QgsOracleConn *conn = connectionRO();
+  if ( !conn )
+    return QgsCoordinateReferenceSystem();
+
+  return lookupCrs( conn, mSrid );
+}
+
 QString QgsOracleProvider::subsetString() const
 {
   return mSqlWhereClause;

diff --git a/src/providers/oracle/qgsoracleprovider.h b/src/providers/oracle/qgsoracleprovider.h
@@ -194,6 +194,13 @@ class QgsOracleProvider final: public QgsVectorDataProvider
     QString whereClause( QgsFeatureId featureId, QVariantList &args ) const;
     QString pkParamWhereClause() const;
 
+    /**
+     * Look up \a srsid coordinate reference system from database using \a conn connection
+     * Returns the coordinate system for the data source. If the provider isn't capable of finding
+     * a matching one, then an invalid QgsCoordinateReferenceSystem will be returned.
+     */
+    static QgsCoordinateReferenceSystem lookupCrs( QgsOracleConn *conn, int srsid );
+
     /**
      * Evaluates the given expression string server-side and convert the result to the given type
      */

diff --git a/tests/src/python/test_provider_oracle.py b/tests/src/python/test_provider_oracle.py
@@ -823,19 +823,31 @@ def testEvaluateDefaultValues(self):
         self.assertEqual(attributes, [[1, 'qgis'], [2, 'test'], [3, 'qgis'], [4, 'test']])
 
     def testCreateEmptyLayer(self):
+
+        # cleanup (it seems overwrite option doesn't clean the sdo_geom_metadata table)
+        self.execSQLCommand('DROP TABLE "QGIS"."EMPTY_LAYER"', ignore_errors=True)
+        self.execSQLCommand("DELETE FROM user_sdo_geom_metadata  where TABLE_NAME='EMPTY_LAYER'", ignore_errors=True)
+
         uri = self.dbconn + "srid=4326 type=POINT table=\"EMPTY_LAYER\" (GEOM)"
         exporter = QgsVectorLayerExporter(uri=uri, provider='oracle', fields=QgsFields(), geometryType=QgsWkbTypes.Point, crs=QgsCoordinateReferenceSystem(4326), overwrite=True)
         self.assertEqual(exporter.errorCount(), 0)
         self.assertEqual(exporter.errorCode(), 0)
-        # check IF there is an empty table (will throw error if the EMPTY_LAYER table does not excist)
+
+        # check IF there is an empty table (will throw error if the EMPTY_LAYER table does not exist)
         self.execSQLCommand('SELECT count(*) FROM "QGIS"."EMPTY_LAYER"')
+
+        # check that metadata table has been correctly populated
+        query = QSqlQuery(self.conn)
+        self.assertTrue(query.exec_("SELECT column_name, srid FROM user_sdo_geom_metadata WHERE table_name = 'EMPTY_LAYER'"))
+        self.assertTrue(query.next())
+        self.assertEqual(query.value(0), "GEOM")
+        self.assertEqual(query.value(1), 4326)
+        query.finish()
+
         vl = QgsVectorLayer(
             self.dbconn + ' sslmode=disable table="QGIS"."EMPTY_LAYER" sql=',
             'test', 'oracle')
         self.assertTrue(vl.isValid())
-        # cleanup
-        self.execSQLCommand('DROP TABLE "QGIS"."EMPTY_LAYER"')
-        self.execSQLCommand("DELETE FROM user_sdo_geom_metadata  where TABLE_NAME='EMPTY_LAYER'")
 
 
 if __name__ == '__main__':