PG: expose foreign tables

qgis · Mar 7, 2020 · 6c86055 · 6c86055
1 parent abdef05
commit 6c86055
Show file tree

Hide file tree

Showing 7 changed files with 77 additions and 3 deletions.
diff --git a/python/core/auto_generated/qgsabstractdatabaseproviderconnection.sip.in b/python/core/auto_generated/qgsabstractdatabaseproviderconnection.sip.in
@@ -38,6 +38,7 @@ is not supported or cannot be performed without errors.
       Raster,
       View,
       MaterializedView,
+      Foreign,
     };
 
     typedef QFlags<QgsAbstractDatabaseProviderConnection::TableFlag> TableFlags;

diff --git a/src/core/qgsabstractdatabaseproviderconnection.h b/src/core/qgsabstractdatabaseproviderconnection.h
@@ -57,6 +57,7 @@ class CORE_EXPORT QgsAbstractDatabaseProviderConnection : public QgsAbstractProv
       Raster = 1 << 3,            //!< Raster table
       View = 1 << 4,              //!< View table
       MaterializedView = 1 << 5,  //!< Materialized view table
+      Foreign = 1 << 6,           //!< Foreign data wrapper
     };
 
     Q_ENUMS( TableFlag )

diff --git a/src/providers/postgres/qgspostgresconn.cpp b/src/providers/postgres/qgspostgresconn.cpp
@@ -667,6 +667,7 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
     layerProperty.sql.clear();
     layerProperty.relKind = relkind;
     layerProperty.isView = isView;
+    layerProperty.isForeignTable = isForeignTable;
     layerProperty.isRaster = isRaster;
     layerProperty.isMaterializedView = isMaterializedView;
     layerProperty.tableComment = comment;
@@ -706,7 +707,7 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
                                   " JOIN pg_namespace n ON n.oid=c.relnamespace"
                                   " JOIN pg_type t ON t.oid=a.atttypid"
                                   " LEFT JOIN pg_type b ON b.oid=t.typbasetype"
-                                  " WHERE c.relkind IN ('v','r','m','p')"
+                                  " WHERE c.relkind IN ('v','r','m','p','f')"
                                   " AND has_schema_privilege( n.nspname, 'usage' )"
                                   " AND has_table_privilege( c.oid, 'select' )"
                                   " AND (t.typname IN (%1) OR b.typname IN (%1))" )
@@ -781,6 +782,7 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
       layerProperty.geometryColName = column;
       layerProperty.relKind = relkind;
       layerProperty.isView = isView;
+      layerProperty.isForeignTable = isForeignTable;
       layerProperty.isRaster = coltype == QLatin1String( "raster" );
       layerProperty.isMaterializedView = isMaterializedView;
       layerProperty.tableComment = comment;
@@ -840,7 +842,7 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
                                   " WHERE pg_namespace.oid=pg_class.relnamespace"
                                   " AND has_schema_privilege(pg_namespace.nspname,'usage')"
                                   " AND has_table_privilege(pg_class.oid,'select')"
-                                  " AND pg_class.relkind IN ('v','r','m','p')"
+                                  " AND pg_class.relkind IN ('v','r','m','p','f')"
                                   " AND pg_class.oid = a.attrelid"
                                   " AND NOT a.attisdropped"
                                   " AND a.attnum > 0" )
@@ -890,6 +892,7 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
       layerProperty.nSpCols = 0;
       layerProperty.relKind = relkind;
       layerProperty.isView = isView;
+      layerProperty.isForeignTable = isForeignTable;
       layerProperty.isRaster = false;
       layerProperty.isMaterializedView = isMaterializedView;
       layerProperty.tableComment = comment;

diff --git a/src/providers/postgres/qgspostgresconn.h b/src/providers/postgres/qgspostgresconn.h
@@ -82,6 +82,7 @@ struct QgsPostgresLayerProperty
   QString                       relKind;
   bool                          isView = false;
   bool                          isMaterializedView = false;
+  bool                          isForeignTable = false;
   bool                          isRaster = false;
   QString                       tableComment;
 

diff --git a/src/providers/postgres/qgspostgresdataitems.cpp b/src/providers/postgres/qgspostgresdataitems.cpp
@@ -468,6 +468,10 @@ QgsPGLayerItem *QgsPGSchemaItem::createLayer( QgsPostgresLayerProperty layerProp
   {
     tip = tr( "Raster" );
   }
+  else if ( layerProperty.isForeignTable )
+  {
+    tip = tr( "Foreign table" );
+  }
   else
   {
     tip = tr( "Table" );

diff --git a/src/providers/postgres/qgspostgresproviderconnection.cpp b/src/providers/postgres/qgspostgresproviderconnection.cpp
@@ -362,7 +362,10 @@ QList<QgsPostgresProviderConnection::TableProperty> QgsPostgresProviderConnectio
         {
           prFlags.setFlag( QgsPostgresProviderConnection::TableFlag::MaterializedView );
         }
-        // Table type
+        if ( pr.isForeignTable )
+        {
+          prFlags.setFlag( QgsPostgresProviderConnection::TableFlag::Foreign );
+        }
         if ( pr.isRaster )
         {
           prFlags.setFlag( QgsPostgresProviderConnection::TableFlag::Raster );

diff --git a/tests/src/python/test_qgsproviderconnection_postgres.py b/tests/src/python/test_qgsproviderconnection_postgres.py
@@ -23,9 +23,11 @@
     QgsProviderRegistry,
     QgsCoordinateReferenceSystem,
     QgsRasterLayer,
+    QgsDataSourceUri,
 )
 from qgis.testing import unittest
 from osgeo import gdal
+from qgis.PyQt.QtCore import QTemporaryDir
 
 
 class TestPyQgsProviderConnectionPostgres(unittest.TestCase, TestPyQgsProviderConnectionBase):
@@ -237,6 +239,65 @@ def test_char_type_conversion(self):
         conn = md.createConnection(self.uri, {})
         self.assertEqual(conn.executeSql("SELECT relname, relkind FROM pg_class c, pg_namespace n WHERE n.oid = c.relnamespace AND relname = 'bikes_view' AND c.relkind IN ('t', 'v', 'm')"), [['bikes_view', 'v']])
 
+    def test_foreign_table_csv(self):
+        """Test foreign table"""
+
+        md = QgsProviderRegistry.instance().providerMetadata(self.providerKey)
+        conn = md.createConnection(self.uri, {})
+        temp_dir = QTemporaryDir()
+        csv_path = os.path.join(temp_dir.path(), 'test.csv')
+        csv = """id,description,geom_x,geom_y
+1,Basic point,10.5,20.82
+2,Integer point,11,22
+3,Final point,13.0,23.0
+"""
+        with open(csv_path, 'w') as f:
+            f.write(csv)
+
+        os.chmod(temp_dir.path(), 0o777)
+        os.chmod(csv_path, 0o777)
+
+        foreign_table_definition = """
+CREATE EXTENSION IF NOT EXISTS file_fdw;
+CREATE SERVER IF NOT EXISTS file_fdw_test_server FOREIGN DATA WRAPPER file_fdw;
+CREATE FOREIGN TABLE IF NOT EXISTS points_csv (
+    id integer not null,
+    name text,
+    x numeric,
+    y numeric ) SERVER file_fdw_test_server OPTIONS ( filename '%s', format 'csv', header 'true' );
+""" % csv_path
+
+        conn.executeSql(foreign_table_definition)
+
+        self.assertNotEquals(conn.tables('public', QgsAbstractDatabaseProviderConnection.Foreign | QgsAbstractDatabaseProviderConnection.Aspatial), [])
+
+    def test_foreign_table_server(self):
+        """Test foreign table with server"""
+
+        md = QgsProviderRegistry.instance().providerMetadata(self.providerKey)
+        conn = md.createConnection(self.uri, {})
+
+        uri = QgsDataSourceUri(conn.uri())
+        host = uri.host()
+        port = uri.port()
+        user = uri.username()
+        dbname = uri.database()
+        password = uri.password()
+        service = uri.service()
+
+        foreign_table_definition = """
+CREATE EXTENSION IF NOT EXISTS postgres_fdw;
+CREATE SERVER IF NOT EXISTS postgres_fdw_test_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (service '{service}', dbname '{dbname}', host '{host}', port '{port}');
+DROP SCHEMA  IF EXISTS foreign_schema CASCADE;
+CREATE SCHEMA IF NOT EXISTS foreign_schema;
+CREATE USER MAPPING IF NOT EXISTS FOR CURRENT_USER SERVER postgres_fdw_test_server OPTIONS (user '{user}', password '{password}');
+IMPORT FOREIGN SCHEMA qgis_test LIMIT TO ( "someData" )
+  FROM SERVER postgres_fdw_test_server
+  INTO foreign_schema;
+""".format(host=host, user=user, port=port, dbname=dbname, password=password, service=service)
+        conn.executeSql(foreign_table_definition)
+        self.assertEquals(conn.tables('foreign_schema', QgsAbstractDatabaseProviderConnection.Foreign)[0].tableName(), 'someData')
+
 
 if __name__ == '__main__':
     unittest.main()