From 649f06759d933f4aacdfbb302e845e2bcb5e7641 Mon Sep 17 00:00:00 2001
From: Rodrigo Menezes <rodrigo.menezes@moat.com>
Date: Thu, 14 Aug 2014 14:47:23 -0400
Subject: Added support for postgres_relkind.

---
 lib/sqlalchemy/dialects/postgresql/base.py |  44 +++++++++----
 lib/sqlalchemy/engine/reflection.py        |   4 +-
 setup.cfg                                  |   7 ++
 test/dialect/postgresql/test_reflection.py | 102 ++++++++++++++++++++++++++++-
 test/requirements.py                       |   8 +++
 5 files changed, 149 insertions(+), 16 deletions(-)

diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 5ff2f7c61..b3506f5d2 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -1669,11 +1669,12 @@ class PGDialect(default.DefaultDialect):
             "ops": {}
         }),
         (schema.Table, {
-            "ignore_search_path": False
+            "ignore_search_path": False,
+            "relkind": None
         })
     ]
 
-    reflection_options = ('postgresql_ignore_search_path', )
+    reflection_options = ('postgresql_ignore_search_path', 'postgresql_relkind')
 
     _backslash_escapes = True
 
@@ -1898,7 +1899,7 @@ class PGDialect(default.DefaultDialect):
         return tuple([int(x) for x in m.group(1, 2, 3) if x is not None])
 
     @reflection.cache
-    def get_table_oid(self, connection, table_name, schema=None, **kw):
+    def get_table_oid(self, connection, table_name, schema=None, postgresql_relkind=None, **kw):
         """Fetch the oid for schema.table_name.
 
         Several reflection methods require the table oid.  The idea for using
@@ -1911,13 +1912,28 @@ class PGDialect(default.DefaultDialect):
             schema_where_clause = "n.nspname = :schema"
         else:
             schema_where_clause = "pg_catalog.pg_table_is_visible(c.oid)"
+
+        RELKIND_SYNONYMS = {
+            'materialized': 'm',
+            'foreign': 'f'
+        }
+        ACCEPTED_RELKINDS = ('r','v','m','f')
+        if postgresql_relkind is None:
+            postgresql_relkind = 'r'
+        else:
+            postgresql_relkind = postgresql_relkind.lower()
+            if postgresql_relkind in RELKIND_SYNONYMS:
+                postgresql_relkind = RELKIND_SYNONYMS[postgresql_relkind.lower()]
+            if postgresql_relkind not in ACCEPTED_RELKINDS:
+                raise exc.SQLAlchemyError('Invalid postgresql_relkind: %s' % postgresql_relkind)
+
         query = """
             SELECT c.oid
             FROM pg_catalog.pg_class c
             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
             WHERE (%s)
-            AND c.relname = :table_name AND c.relkind in ('r','v')
-        """ % schema_where_clause
+            AND c.relname = :table_name AND c.relkind in ('%s', 'v')
+        """ % (schema_where_clause, postgresql_relkind)
         # Since we're binding to unicode, table_name and schema_name must be
         # unicode.
         table_name = util.text_type(table_name)
@@ -2014,7 +2030,8 @@ class PGDialect(default.DefaultDialect):
     def get_columns(self, connection, table_name, schema=None, **kw):
 
         table_oid = self.get_table_oid(connection, table_name, schema,
-                                       info_cache=kw.get('info_cache'))
+                                       info_cache=kw.get('info_cache'),
+                                       postgresql_relkind=kw.get('postgresql_relkind'))
         SQL_COLS = """
             SELECT a.attname,
               pg_catalog.format_type(a.atttypid, a.atttypmod),
@@ -2164,7 +2181,8 @@ class PGDialect(default.DefaultDialect):
     @reflection.cache
     def get_pk_constraint(self, connection, table_name, schema=None, **kw):
         table_oid = self.get_table_oid(connection, table_name, schema,
-                                       info_cache=kw.get('info_cache'))
+                                       info_cache=kw.get('info_cache'),
+                                       postgresql_relkind=kw.get('postgresql_relkind'))
 
         if self.server_version_info < (8, 4):
             PK_SQL = """
@@ -2214,7 +2232,8 @@ class PGDialect(default.DefaultDialect):
                          postgresql_ignore_search_path=False, **kw):
         preparer = self.identifier_preparer
         table_oid = self.get_table_oid(connection, table_name, schema,
-                                       info_cache=kw.get('info_cache'))
+                                       info_cache=kw.get('info_cache'),
+                                       postgresql_relkind=kw.get('postgresql_relkind'))
 
         FK_SQL = """
           SELECT r.conname,
@@ -2318,11 +2337,11 @@ class PGDialect(default.DefaultDialect):
     @reflection.cache
     def get_indexes(self, connection, table_name, schema, **kw):
         table_oid = self.get_table_oid(connection, table_name, schema,
-                                       info_cache=kw.get('info_cache'))
+                                       info_cache=kw.get('info_cache'),
+                                       postgresql_relkind=kw.get('postgresql_relkind'))
 
         # cast indkey as varchar since it's an int2vector,
         # returned as a list by some drivers such as pypostgresql
-
         IDX_SQL = """
           SELECT
               i.relname as relname,
@@ -2336,7 +2355,7 @@ class PGDialect(default.DefaultDialect):
                         pg_attribute a
                         on t.oid=a.attrelid and %s
           WHERE
-              t.relkind = 'r'
+              t.relkind IN ('r', 'v', 'f', 'm')
               and t.oid = :table_oid
               and ix.indisprimary = 'f'
           ORDER BY
@@ -2391,7 +2410,8 @@ class PGDialect(default.DefaultDialect):
     def get_unique_constraints(self, connection, table_name,
                                schema=None, **kw):
         table_oid = self.get_table_oid(connection, table_name, schema,
-                                       info_cache=kw.get('info_cache'))
+                                       info_cache=kw.get('info_cache'),
+                                       postgresql_relkind=kw.get('postgresql_relkind'))
 
         UNIQUE_SQL = """
             SELECT
diff --git a/lib/sqlalchemy/engine/reflection.py b/lib/sqlalchemy/engine/reflection.py
index 012d1d35d..afe9a8b3e 100644
--- a/lib/sqlalchemy/engine/reflection.py
+++ b/lib/sqlalchemy/engine/reflection.py
@@ -378,7 +378,6 @@ class Inspector(object):
          use :class:`.quoted_name`.
 
         """
-
         return self.dialect.get_indexes(self.bind, table_name,
                                         schema,
                                         info_cache=self.info_cache, **kw)
@@ -405,7 +404,6 @@ class Inspector(object):
         .. versionadded:: 0.8.4
 
         """
-
         return self.dialect.get_unique_constraints(
             self.bind, table_name, schema, info_cache=self.info_cache, **kw)
 
@@ -573,7 +571,7 @@ class Inspector(object):
                                                conname, link_to_name=True,
                                                **options))
         # Indexes
-        indexes = self.get_indexes(table_name, schema)
+        indexes = self.get_indexes(table_name, schema, **table.dialect_kwargs)
         for index_d in indexes:
             name = index_d['name']
             columns = index_d['column_names']
diff --git a/setup.cfg b/setup.cfg
index 7517220a6..4ec4b0837 100644
--- a/setup.cfg
+++ b/setup.cfg
@@ -26,6 +26,13 @@ profile_file=test/profiles.txt
 # create database link test_link connect to scott identified by tiger using 'xe';
 oracle_db_link = test_link
 
+# host name of a postgres database that has the postgres_fdw extension.
+# to create this run:
+# CREATE EXTENSION postgres_fdw;
+# GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO public;
+# this can be localhost to create a loopback foreign table
+postgres_test_db_link = localhost
+
 
 [db]
 default=sqlite:///:memory:
diff --git a/test/dialect/postgresql/test_reflection.py b/test/dialect/postgresql/test_reflection.py
index 1d6a41765..313be0b37 100644
--- a/test/dialect/postgresql/test_reflection.py
+++ b/test/dialect/postgresql/test_reflection.py
@@ -12,8 +12,108 @@ import sqlalchemy as sa
 from sqlalchemy.dialects.postgresql import base as postgresql
 
 
-class DomainReflectionTest(fixtures.TestBase, AssertsExecutionResults):
+class RelKindReflectionTest(fixtures.TestBase, AssertsExecutionResults):
+    """Test postgresql_relkind reflection option"""
+
+    __requires__ = 'postgresql_test_dblink',
+    __only_on__ = 'postgresql >= 9.3'
+    __backend__ = True
+
+    @classmethod
+    def setup_class(cls):
+        from sqlalchemy.testing import config
+        cls.dblink = config.file_config.get('sqla_testing', 'postgres_test_db_link')
+
+        metadata = MetaData(testing.db)
+        testtable = Table(
+            'testtable', metadata,
+            Column(
+                'id', Integer, primary_key=True),
+            Column(
+                'data', String(30)))
+        metadata.create_all()
+        testtable.insert().execute({'id': 89, 'data': 'd1'})
+
+        con = testing.db.connect()
 
+        for ddl in \
+                "CREATE MATERIALIZED VIEW test_mview AS SELECT * FROM testtable;", \
+                "CREATE SERVER test_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'test', host '%s');" % cls.dblink, \
+                "CREATE USER MAPPING FOR public SERVER test_server options (user 'scott', password 'tiger');", \
+                "CREATE FOREIGN TABLE test_foreigntable ( \
+                    id          INT, \
+                    data        VARCHAR(30) \
+                ) SERVER test_server OPTIONS (table_name 'testtable');":
+            try:
+                con.execute(ddl)
+            except exc.DBAPIError as e:
+                if 'already exists' not in str(e):
+                    raise e
+
+    @classmethod
+    def teardown_class(cls):
+        con = testing.db.connect()
+        con.execute('DROP FOREIGN TABLE test_foreigntable;')
+        con.execute('DROP USER MAPPING FOR public SERVER test_server;')
+        con.execute('DROP SERVER test_server;')
+        con.execute('DROP MATERIALIZED VIEW test_mview;')
+        con.execute('DROP TABLE testtable;')
+
+    def test_mview_is_reflected(self):
+        mview_relkind_names = ('m', 'materialized')
+        for mview_relkind_name in mview_relkind_names:
+            metadata = MetaData(testing.db)
+            table = Table('test_mview', metadata, autoload=True, postgresql_relkind=mview_relkind_name)
+            eq_(set(table.columns.keys()), set(['id', 'data']), "Columns of reflected mview didn't equal expected columns")
+
+    def test_mview_select(self):
+        metadata = MetaData(testing.db)
+        table = Table('test_mview', metadata, autoload=True, postgresql_relkind='m')
+        assert table.select().execute().fetchall() == [
+            (89, 'd1',)
+        ]
+
+    def test_foreign_table_is_reflected(self):
+        foreign_table_relkind_names = ('f', 'foreign')
+        for foreign_table_relkind_name in foreign_table_relkind_names:
+            metadata = MetaData(testing.db)
+            table = Table('test_foreigntable', metadata, autoload=True, postgresql_relkind=foreign_table_relkind_name)
+            eq_(set(table.columns.keys()), set(['id', 'data']), "Columns of reflected foreign table didn't equal expected columns")
+
+    def test_foreign_table_select(self):
+        metadata = MetaData(testing.db)
+        table = Table('test_foreigntable', metadata, autoload=True, postgresql_relkind='f')
+        assert table.select().execute().fetchall() == [
+            (89, 'd1',)
+        ]
+
+    def test_foreign_table_roundtrip(self):
+        metadata = MetaData(testing.db)
+        table = Table('test_foreigntable', metadata, autoload=True, postgresql_relkind='f')
+
+        connection = testing.db.connect()
+        trans = connection.begin()
+        try:
+            table.delete().execute()
+            table.insert().execute({'id': 89, 'data': 'd1'})
+            trans.commit()
+        except:
+            trans.rollback()
+            raise
+
+        assert table.select().execute().fetchall() == [
+            (89, 'd1',)
+        ]
+
+    def test_invalid_relkind(self):
+        metadata = MetaData(testing.db)
+        def create_bad_table():
+            return Table('test_foreigntable', metadata, autoload=True, postgresql_relkind='nope')
+
+        assert_raises(exc.SQLAlchemyError, create_bad_table)
+
+
+class DomainReflectionTest(fixtures.TestBase, AssertsExecutionResults):
     """Test PostgreSQL domains"""
 
     __only_on__ = 'postgresql > 8.3'
diff --git a/test/requirements.py b/test/requirements.py
index e8705d145..927c94bfb 100644
--- a/test/requirements.py
+++ b/test/requirements.py
@@ -716,6 +716,14 @@ class DefaultRequirements(SuiteRequirements):
                     "oracle_db_link option not specified in config"
                 )
 
+    @property
+    def postgresql_test_dblink(self):
+        return skip_if(
+                    lambda config: not config.file_config.has_option(
+                        'sqla_testing', 'postgres_test_db_link'),
+                    "postgres_test_db_link option not specified in config"
+                )
+    
     @property
     def percent_schema_names(self):
         return skip_if(
-- 
cgit v1.2.1


From 4b26bf2e6f254d5cfc0998a9399a310c0f59944b Mon Sep 17 00:00:00 2001
From: Rodrigo Menezes <rodrigo.menezes@moat.com>
Date: Thu, 14 Aug 2014 17:19:10 -0400
Subject: Add a view synonym too for consistency.

---
 lib/sqlalchemy/dialects/postgresql/base.py | 3 ++-
 1 file changed, 2 insertions(+), 1 deletion(-)

diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 19d2c7ca4..893b4b3c1 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -1953,7 +1953,8 @@ class PGDialect(default.DefaultDialect):
 
         RELKIND_SYNONYMS = {
             'materialized': 'm',
-            'foreign': 'f'
+            'foreign': 'f',
+            'view': 'v'
         }
         ACCEPTED_RELKINDS = ('r','v','m','f')
         if postgresql_relkind is None:
-- 
cgit v1.2.1


From 2f7dce1d6fa43e88f64c81b6e612fbc42235fddd Mon Sep 17 00:00:00 2001
From: Rodrigo Menezes <rodrigo.menezes@moat.com>
Date: Tue, 26 Aug 2014 12:53:34 -0400
Subject: Removed all mentions to postgresql_relkind

---
 lib/sqlalchemy/dialects/postgresql/base.py | 38 +++++++-----------------------
 test/dialect/postgresql/test_reflection.py | 33 +++++++++-----------------
 2 files changed, 20 insertions(+), 51 deletions(-)

diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 893b4b3c1..155136c1d 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -1712,7 +1712,7 @@ class PGDialect(default.DefaultDialect):
         })
     ]
 
-    reflection_options = ('postgresql_ignore_search_path', 'postgresql_relkind')
+    reflection_options = ('postgresql_ignore_search_path',)
 
     _backslash_escapes = True
 
@@ -1937,7 +1937,7 @@ class PGDialect(default.DefaultDialect):
         return tuple([int(x) for x in m.group(1, 2, 3) if x is not None])
 
     @reflection.cache
-    def get_table_oid(self, connection, table_name, schema=None, postgresql_relkind=None, **kw):
+    def get_table_oid(self, connection, table_name, schema=None, **kw):
         """Fetch the oid for schema.table_name.
 
         Several reflection methods require the table oid.  The idea for using
@@ -1951,28 +1951,13 @@ class PGDialect(default.DefaultDialect):
         else:
             schema_where_clause = "pg_catalog.pg_table_is_visible(c.oid)"
 
-        RELKIND_SYNONYMS = {
-            'materialized': 'm',
-            'foreign': 'f',
-            'view': 'v'
-        }
-        ACCEPTED_RELKINDS = ('r','v','m','f')
-        if postgresql_relkind is None:
-            postgresql_relkind = 'r'
-        else:
-            postgresql_relkind = postgresql_relkind.lower()
-            if postgresql_relkind in RELKIND_SYNONYMS:
-                postgresql_relkind = RELKIND_SYNONYMS[postgresql_relkind.lower()]
-            if postgresql_relkind not in ACCEPTED_RELKINDS:
-                raise exc.SQLAlchemyError('Invalid postgresql_relkind: %s' % postgresql_relkind)
-
         query = """
             SELECT c.oid
             FROM pg_catalog.pg_class c
             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
             WHERE (%s)
-            AND c.relname = :table_name AND c.relkind in ('%s', 'v')
-        """ % (schema_where_clause, postgresql_relkind)
+            AND c.relname = :table_name AND c.relkind in ('r', 'v', 'm', 'f')
+        """ % schema_where_clause
         # Since we're binding to unicode, table_name and schema_name must be
         # unicode.
         table_name = util.text_type(table_name)
@@ -2069,8 +2054,7 @@ class PGDialect(default.DefaultDialect):
     def get_columns(self, connection, table_name, schema=None, **kw):
 
         table_oid = self.get_table_oid(connection, table_name, schema,
-                                       info_cache=kw.get('info_cache'),
-                                       postgresql_relkind=kw.get('postgresql_relkind'))
+                                       info_cache=kw.get('info_cache'))
         SQL_COLS = """
             SELECT a.attname,
               pg_catalog.format_type(a.atttypid, a.atttypmod),
@@ -2224,8 +2208,7 @@ class PGDialect(default.DefaultDialect):
     @reflection.cache
     def get_pk_constraint(self, connection, table_name, schema=None, **kw):
         table_oid = self.get_table_oid(connection, table_name, schema,
-                                       info_cache=kw.get('info_cache'),
-                                       postgresql_relkind=kw.get('postgresql_relkind'))
+                                       info_cache=kw.get('info_cache'))
 
         if self.server_version_info < (8, 4):
             PK_SQL = """
@@ -2275,8 +2258,7 @@ class PGDialect(default.DefaultDialect):
                          postgresql_ignore_search_path=False, **kw):
         preparer = self.identifier_preparer
         table_oid = self.get_table_oid(connection, table_name, schema,
-                                       info_cache=kw.get('info_cache'),
-                                       postgresql_relkind=kw.get('postgresql_relkind'))
+                                       info_cache=kw.get('info_cache'))
 
         FK_SQL = """
           SELECT r.conname,
@@ -2380,8 +2362,7 @@ class PGDialect(default.DefaultDialect):
     @reflection.cache
     def get_indexes(self, connection, table_name, schema, **kw):
         table_oid = self.get_table_oid(connection, table_name, schema,
-                                       info_cache=kw.get('info_cache'),
-                                       postgresql_relkind=kw.get('postgresql_relkind'))
+                                       info_cache=kw.get('info_cache'))
 
         # cast indkey as varchar since it's an int2vector,
         # returned as a list by some drivers such as pypostgresql
@@ -2453,8 +2434,7 @@ class PGDialect(default.DefaultDialect):
     def get_unique_constraints(self, connection, table_name,
                                schema=None, **kw):
         table_oid = self.get_table_oid(connection, table_name, schema,
-                                       info_cache=kw.get('info_cache'),
-                                       postgresql_relkind=kw.get('postgresql_relkind'))
+                                       info_cache=kw.get('info_cache'))
 
         UNIQUE_SQL = """
             SELECT
diff --git a/test/dialect/postgresql/test_reflection.py b/test/dialect/postgresql/test_reflection.py
index 678c35881..405ac5921 100644
--- a/test/dialect/postgresql/test_reflection.py
+++ b/test/dialect/postgresql/test_reflection.py
@@ -13,8 +13,8 @@ import sqlalchemy as sa
 from sqlalchemy.dialects.postgresql import base as postgresql
 
 
-class RelKindReflectionTest(fixtures.TestBase, AssertsExecutionResults):
-    """Test postgresql_relkind reflection option"""
+class AlternateRelkindReflectionTest(fixtures.TestBase, AssertsExecutionResults):
+    """Test reflection on materialized views and foreign tables"""
 
     __requires__ = 'postgresql_test_dblink',
     __only_on__ = 'postgresql >= 9.3'
@@ -61,36 +61,32 @@ class RelKindReflectionTest(fixtures.TestBase, AssertsExecutionResults):
         con.execute('DROP TABLE testtable;')
 
     def test_mview_is_reflected(self):
-        mview_relkind_names = ('m', 'materialized')
-        for mview_relkind_name in mview_relkind_names:
-            metadata = MetaData(testing.db)
-            table = Table('test_mview', metadata, autoload=True, postgresql_relkind=mview_relkind_name)
-            eq_(set(table.columns.keys()), set(['id', 'data']), "Columns of reflected mview didn't equal expected columns")
+        metadata = MetaData(testing.db)
+        table = Table('test_mview', metadata, autoload=True)
+        eq_(set(table.columns.keys()), set(['id', 'data']), "Columns of reflected mview didn't equal expected columns")
 
     def test_mview_select(self):
         metadata = MetaData(testing.db)
-        table = Table('test_mview', metadata, autoload=True, postgresql_relkind='m')
+        table = Table('test_mview', metadata, autoload=True)
         assert table.select().execute().fetchall() == [
             (89, 'd1',)
         ]
 
     def test_foreign_table_is_reflected(self):
-        foreign_table_relkind_names = ('f', 'foreign')
-        for foreign_table_relkind_name in foreign_table_relkind_names:
-            metadata = MetaData(testing.db)
-            table = Table('test_foreigntable', metadata, autoload=True, postgresql_relkind=foreign_table_relkind_name)
-            eq_(set(table.columns.keys()), set(['id', 'data']), "Columns of reflected foreign table didn't equal expected columns")
+        metadata = MetaData(testing.db)
+        table = Table('test_foreigntable', metadata, autoload=True)
+        eq_(set(table.columns.keys()), set(['id', 'data']), "Columns of reflected foreign table didn't equal expected columns")
 
     def test_foreign_table_select(self):
         metadata = MetaData(testing.db)
-        table = Table('test_foreigntable', metadata, autoload=True, postgresql_relkind='f')
+        table = Table('test_foreigntable', metadata, autoload=True)
         assert table.select().execute().fetchall() == [
             (89, 'd1',)
         ]
 
     def test_foreign_table_roundtrip(self):
         metadata = MetaData(testing.db)
-        table = Table('test_foreigntable', metadata, autoload=True, postgresql_relkind='f')
+        table = Table('test_foreigntable', metadata, autoload=True)
 
         connection = testing.db.connect()
         trans = connection.begin()
@@ -106,13 +102,6 @@ class RelKindReflectionTest(fixtures.TestBase, AssertsExecutionResults):
             (89, 'd1',)
         ]
 
-    def test_invalid_relkind(self):
-        metadata = MetaData(testing.db)
-        def create_bad_table():
-            return Table('test_foreigntable', metadata, autoload=True, postgresql_relkind='nope')
-
-        assert_raises(exc.SQLAlchemyError, create_bad_table)
-
 
 class DomainReflectionTest(fixtures.TestBase, AssertsExecutionResults):
     """Test PostgreSQL domains"""
-- 
cgit v1.2.1


From bcf7a55da01633c4890502463a08cb96af9fe5e9 Mon Sep 17 00:00:00 2001
From: Rodrigo Menezes <rodrigo.menezes@moat.com>
Date: Tue, 26 Aug 2014 12:56:54 -0400
Subject: Remove relkind from construct arguments.

---
 lib/sqlalchemy/dialects/postgresql/base.py | 3 +--
 1 file changed, 1 insertion(+), 2 deletions(-)

diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 155136c1d..75d0696ad 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -1707,8 +1707,7 @@ class PGDialect(default.DefaultDialect):
             "ops": {}
         }),
         (schema.Table, {
-            "ignore_search_path": False,
-            "relkind": None
+            "ignore_search_path": False
         })
     ]
 
-- 
cgit v1.2.1


From d39be884321d0afbae7ef3da556382b53fef8060 Mon Sep 17 00:00:00 2001
From: Rodrigo Menezes <rodrigo.menezes@moat.com>
Date: Tue, 26 Aug 2014 13:02:19 -0400
Subject: Removed changes that are no longer necessary for postgresql_relkind.
 Also, removed newline changes.

---
 lib/sqlalchemy/dialects/postgresql/base.py | 4 ++--
 lib/sqlalchemy/engine/reflection.py        | 4 +++-
 2 files changed, 5 insertions(+), 3 deletions(-)

diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 206a25d28..40b2f60ae 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -1781,7 +1781,7 @@ class PGDialect(default.DefaultDialect):
         })
     ]
 
-    reflection_options = ('postgresql_ignore_search_path',)
+    reflection_options = ('postgresql_ignore_search_path', )
 
     _backslash_escapes = True
 
@@ -2019,7 +2019,6 @@ class PGDialect(default.DefaultDialect):
             schema_where_clause = "n.nspname = :schema"
         else:
             schema_where_clause = "pg_catalog.pg_table_is_visible(c.oid)"
-
         query = """
             SELECT c.oid
             FROM pg_catalog.pg_class c
@@ -2435,6 +2434,7 @@ class PGDialect(default.DefaultDialect):
 
         # cast indkey as varchar since it's an int2vector,
         # returned as a list by some drivers such as pypostgresql
+        
         IDX_SQL = """
           SELECT
               i.relname as relname,
diff --git a/lib/sqlalchemy/engine/reflection.py b/lib/sqlalchemy/engine/reflection.py
index afe9a8b3e..012d1d35d 100644
--- a/lib/sqlalchemy/engine/reflection.py
+++ b/lib/sqlalchemy/engine/reflection.py
@@ -378,6 +378,7 @@ class Inspector(object):
          use :class:`.quoted_name`.
 
         """
+
         return self.dialect.get_indexes(self.bind, table_name,
                                         schema,
                                         info_cache=self.info_cache, **kw)
@@ -404,6 +405,7 @@ class Inspector(object):
         .. versionadded:: 0.8.4
 
         """
+
         return self.dialect.get_unique_constraints(
             self.bind, table_name, schema, info_cache=self.info_cache, **kw)
 
@@ -571,7 +573,7 @@ class Inspector(object):
                                                conname, link_to_name=True,
                                                **options))
         # Indexes
-        indexes = self.get_indexes(table_name, schema, **table.dialect_kwargs)
+        indexes = self.get_indexes(table_name, schema)
         for index_d in indexes:
             name = index_d['name']
             columns = index_d['column_names']
-- 
cgit v1.2.1


From fbd2d70a5cfd7b5c219c51cb5b7866c4ab89cece Mon Sep 17 00:00:00 2001
From: Rodrigo Menezes <rodrigo.menezes@moat.com>
Date: Wed, 3 Sep 2014 16:38:43 -0400
Subject: Fixing some pep8s and adding get_foreign_tables.

---
 lib/sqlalchemy/dialects/postgresql/base.py | 22 ++++++++++++++++++++--
 lib/sqlalchemy/engine/interfaces.py        |  6 ++++++
 test/dialect/postgresql/test_reflection.py | 17 +++++++++++------
 test/requirements.py                       |  2 +-
 4 files changed, 38 insertions(+), 9 deletions(-)

diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 40b2f60ae..69ae6cfed 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -2086,7 +2086,7 @@ class PGDialect(default.DefaultDialect):
         s = """
         SELECT relname
         FROM pg_class c
-        WHERE relkind = 'v'
+        WHERE relkind IN ('m', v')
           AND '%(schema)s' = (select nspname from pg_namespace n
           where n.oid = c.relnamespace)
         """ % dict(schema=current_schema)
@@ -2098,6 +2098,24 @@ class PGDialect(default.DefaultDialect):
             view_names = [row[0] for row in connection.execute(s)]
         return view_names
 
+    @reflection.cache
+    def get_foreign_table_names(self, connection, schema=None, **kw):
+        if schema is not None:
+            current_schema = schema
+        else:
+            current_schema = self.default_schema_name
+
+        result = connection.execute(
+            sql.text("SELECT relname FROM pg_class c "
+                     "WHERE relkind = 'f' "
+                     "AND '%s' = (select nspname from pg_namespace n "
+                     "where n.oid = c.relnamespace) " %
+                     current_schema,
+                     typemap={'relname': sqltypes.Unicode}
+                     )
+        )
+        return [row[0] for row in result]
+
     @reflection.cache
     def get_view_definition(self, connection, view_name, schema=None, **kw):
         if schema is not None:
@@ -2434,7 +2452,7 @@ class PGDialect(default.DefaultDialect):
 
         # cast indkey as varchar since it's an int2vector,
         # returned as a list by some drivers such as pypostgresql
-        
+
         IDX_SQL = """
           SELECT
               i.relname as relname,
diff --git a/lib/sqlalchemy/engine/interfaces.py b/lib/sqlalchemy/engine/interfaces.py
index 71df29cac..e1e346850 100644
--- a/lib/sqlalchemy/engine/interfaces.py
+++ b/lib/sqlalchemy/engine/interfaces.py
@@ -319,6 +319,12 @@ class Dialect(object):
 
         raise NotImplementedError()
 
+    def get_foreign_tables(self, connection, view_name, schema=None, **kw):
+        """Return a list of foreign table names for `schema`."""
+        """
+
+        raise NotImplementedError()
+
     def get_view_definition(self, connection, view_name, schema=None, **kw):
         """Return view definition.
 
diff --git a/test/dialect/postgresql/test_reflection.py b/test/dialect/postgresql/test_reflection.py
index 405ac5921..567aec927 100644
--- a/test/dialect/postgresql/test_reflection.py
+++ b/test/dialect/postgresql/test_reflection.py
@@ -13,7 +13,7 @@ import sqlalchemy as sa
 from sqlalchemy.dialects.postgresql import base as postgresql
 
 
-class AlternateRelkindReflectionTest(fixtures.TestBase, AssertsExecutionResults):
+class AltRelkindReflectionTest(fixtures.TestBase, AssertsExecutionResults):
     """Test reflection on materialized views and foreign tables"""
 
     __requires__ = 'postgresql_test_dblink',
@@ -23,7 +23,8 @@ class AlternateRelkindReflectionTest(fixtures.TestBase, AssertsExecutionResults)
     @classmethod
     def setup_class(cls):
         from sqlalchemy.testing import config
-        cls.dblink = config.file_config.get('sqla_testing', 'postgres_test_db_link')
+        cls.dblink = config.file_config.get('sqla_testing',
+                                            'postgres_test_db_link')
 
         metadata = MetaData(testing.db)
         testtable = Table(
@@ -39,8 +40,10 @@ class AlternateRelkindReflectionTest(fixtures.TestBase, AssertsExecutionResults)
 
         for ddl in \
                 "CREATE MATERIALIZED VIEW test_mview AS SELECT * FROM testtable;", \
-                "CREATE SERVER test_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'test', host '%s');" % cls.dblink, \
-                "CREATE USER MAPPING FOR public SERVER test_server options (user 'scott', password 'tiger');", \
+                "CREATE SERVER test_server FOREIGN DATA WRAPPER postgres_fdw \
+                    OPTIONS (dbname 'test', host '%s');" % cls.dblink, \
+                "CREATE USER MAPPING FOR public \
+                    SERVER test_server options (user 'scott', password 'tiger');", \
                 "CREATE FOREIGN TABLE test_foreigntable ( \
                     id          INT, \
                     data        VARCHAR(30) \
@@ -63,7 +66,8 @@ class AlternateRelkindReflectionTest(fixtures.TestBase, AssertsExecutionResults)
     def test_mview_is_reflected(self):
         metadata = MetaData(testing.db)
         table = Table('test_mview', metadata, autoload=True)
-        eq_(set(table.columns.keys()), set(['id', 'data']), "Columns of reflected mview didn't equal expected columns")
+        eq_(set(table.columns.keys()), set(['id', 'data']),
+            "Columns of reflected mview didn't equal expected columns")
 
     def test_mview_select(self):
         metadata = MetaData(testing.db)
@@ -75,7 +79,8 @@ class AlternateRelkindReflectionTest(fixtures.TestBase, AssertsExecutionResults)
     def test_foreign_table_is_reflected(self):
         metadata = MetaData(testing.db)
         table = Table('test_foreigntable', metadata, autoload=True)
-        eq_(set(table.columns.keys()), set(['id', 'data']), "Columns of reflected foreign table didn't equal expected columns")
+        eq_(set(table.columns.keys()), set(['id', 'data']),
+            "Columns of reflected foreign table didn't equal expected columns")
 
     def test_foreign_table_select(self):
         metadata = MetaData(testing.db)
diff --git a/test/requirements.py b/test/requirements.py
index 46c19389a..14bb25691 100644
--- a/test/requirements.py
+++ b/test/requirements.py
@@ -712,7 +712,7 @@ class DefaultRequirements(SuiteRequirements):
                         'sqla_testing', 'postgres_test_db_link'),
                     "postgres_test_db_link option not specified in config"
                 )
-    
+
     @property
     def percent_schema_names(self):
         return skip_if(
-- 
cgit v1.2.1


From 619b0be0ce05c394613d8565c08c09cac10cdd88 Mon Sep 17 00:00:00 2001
From: Rodrigo Menezes <rodrigo.menezes@moat.com>
Date: Fri, 5 Sep 2014 13:37:32 -0400
Subject: Added get_foreign_table_names to interface and put it in the test
 requirements.

---
 lib/sqlalchemy/dialects/postgresql/base.py |  1 +
 lib/sqlalchemy/engine/default.py           |  1 +
 lib/sqlalchemy/engine/interfaces.py        |  4 ++--
 lib/sqlalchemy/testing/requirements.py     | 15 +++++++++++++++
 4 files changed, 19 insertions(+), 2 deletions(-)

diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 69ae6cfed..f65bc2473 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -1753,6 +1753,7 @@ class PGDialect(default.DefaultDialect):
 
     supports_default_values = True
     supports_empty_insert = False
+    supports_foreign_tables = True
     supports_multivalues_insert = True
     default_paramstyle = 'pyformat'
     ischema_names = ischema_names
diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py
index 2fece76b9..a59efd301 100644
--- a/lib/sqlalchemy/engine/default.py
+++ b/lib/sqlalchemy/engine/default.py
@@ -107,6 +107,7 @@ class DefaultDialect(interfaces.Dialect):
     default_paramstyle = 'named'
     supports_default_values = False
     supports_empty_insert = True
+    supports_foreign_tables = False
     supports_multivalues_insert = False
 
     server_version_info = None
diff --git a/lib/sqlalchemy/engine/interfaces.py b/lib/sqlalchemy/engine/interfaces.py
index e1e346850..795be8273 100644
--- a/lib/sqlalchemy/engine/interfaces.py
+++ b/lib/sqlalchemy/engine/interfaces.py
@@ -319,8 +319,8 @@ class Dialect(object):
 
         raise NotImplementedError()
 
-    def get_foreign_tables(self, connection, view_name, schema=None, **kw):
-        """Return a list of foreign table names for `schema`."""
+    def get_foreign_table_names(self, connection, schema=None, **kw):
+        """Return a list of foreign table names for `schema`.
         """
 
         raise NotImplementedError()
diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py
index a04bcbbdd..f0f0e9e47 100644
--- a/lib/sqlalchemy/testing/requirements.py
+++ b/lib/sqlalchemy/testing/requirements.py
@@ -252,6 +252,15 @@ class SuiteRequirements(Requirements):
 
         return exclusions.closed()
 
+    @property
+    def foreign_tables(self):
+        """target platform supports FOREIGN TABLEs."""
+
+        return exclusions.only_if(
+            lambda config: config.db.dialect.supports_foreign_tables,
+            "%(database)s %(does_support)s 'FOREIGN TABLEs'"
+        )
+
     @property
     def schemas(self):
         """Target database must support external schemas, and have one
@@ -301,6 +310,12 @@ class SuiteRequirements(Requirements):
         """
         return self.views
 
+    @property
+    def foreign_table_reflection(self):
+        """target database must support inspection of the full CREATE FOREIGN TABLE definition.
+        """
+        return self.foreign_tables
+
     @property
     def schema_reflection(self):
         return self.schemas
-- 
cgit v1.2.1


From fd2faa9bc2c6d2d1b0b8e1738f0bce21e2527bb0 Mon Sep 17 00:00:00 2001
From: Rodrigo Menezes <rodrigo.menezes@moat.com>
Date: Fri, 5 Sep 2014 13:54:48 -0400
Subject: Added documentation. Changed my mind - added
 get_foreign_table_names() only to PGInspect and not in the Dialect. Added
 tests for PGInspect and removed a bunch of the old test scaffolding.

---
 lib/sqlalchemy/dialects/postgresql/base.py | 36 ++++++++++++++----------------
 lib/sqlalchemy/engine/default.py           |  1 -
 lib/sqlalchemy/engine/interfaces.py        |  6 -----
 lib/sqlalchemy/engine/reflection.py        |  3 +++
 lib/sqlalchemy/testing/requirements.py     | 15 -------------
 test/dialect/postgresql/test_reflection.py |  6 +++++
 6 files changed, 26 insertions(+), 41 deletions(-)

diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index f65bc2473..63f6eb891 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -1679,6 +1679,23 @@ class PGInspector(reflection.Inspector):
         schema = schema or self.default_schema_name
         return self.dialect._load_enums(self.bind, schema)
 
+    def get_foreign_table_names(self, connection, schema=None, **kw):
+        if schema is not None:
+            current_schema = schema
+        else:
+            current_schema = self.default_schema_name
+
+        result = connection.execute(
+            sql.text("SELECT relname FROM pg_class c "
+                     "WHERE relkind = 'f' "
+                     "AND '%s' = (select nspname from pg_namespace n "
+                     "where n.oid = c.relnamespace) " %
+                     current_schema,
+                     typemap={'relname': sqltypes.Unicode}
+                     )
+        )
+        return [row[0] for row in result]
+
 
 class CreateEnumType(schema._CreateDropBase):
     __visit_name__ = "create_enum_type"
@@ -1753,7 +1770,6 @@ class PGDialect(default.DefaultDialect):
 
     supports_default_values = True
     supports_empty_insert = False
-    supports_foreign_tables = True
     supports_multivalues_insert = True
     default_paramstyle = 'pyformat'
     ischema_names = ischema_names
@@ -2099,24 +2115,6 @@ class PGDialect(default.DefaultDialect):
             view_names = [row[0] for row in connection.execute(s)]
         return view_names
 
-    @reflection.cache
-    def get_foreign_table_names(self, connection, schema=None, **kw):
-        if schema is not None:
-            current_schema = schema
-        else:
-            current_schema = self.default_schema_name
-
-        result = connection.execute(
-            sql.text("SELECT relname FROM pg_class c "
-                     "WHERE relkind = 'f' "
-                     "AND '%s' = (select nspname from pg_namespace n "
-                     "where n.oid = c.relnamespace) " %
-                     current_schema,
-                     typemap={'relname': sqltypes.Unicode}
-                     )
-        )
-        return [row[0] for row in result]
-
     @reflection.cache
     def get_view_definition(self, connection, view_name, schema=None, **kw):
         if schema is not None:
diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py
index a59efd301..2fece76b9 100644
--- a/lib/sqlalchemy/engine/default.py
+++ b/lib/sqlalchemy/engine/default.py
@@ -107,7 +107,6 @@ class DefaultDialect(interfaces.Dialect):
     default_paramstyle = 'named'
     supports_default_values = False
     supports_empty_insert = True
-    supports_foreign_tables = False
     supports_multivalues_insert = False
 
     server_version_info = None
diff --git a/lib/sqlalchemy/engine/interfaces.py b/lib/sqlalchemy/engine/interfaces.py
index 795be8273..71df29cac 100644
--- a/lib/sqlalchemy/engine/interfaces.py
+++ b/lib/sqlalchemy/engine/interfaces.py
@@ -319,12 +319,6 @@ class Dialect(object):
 
         raise NotImplementedError()
 
-    def get_foreign_table_names(self, connection, schema=None, **kw):
-        """Return a list of foreign table names for `schema`.
-        """
-
-        raise NotImplementedError()
-
     def get_view_definition(self, connection, view_name, schema=None, **kw):
         """Return view definition.
 
diff --git a/lib/sqlalchemy/engine/reflection.py b/lib/sqlalchemy/engine/reflection.py
index 012d1d35d..f41ea1fa2 100644
--- a/lib/sqlalchemy/engine/reflection.py
+++ b/lib/sqlalchemy/engine/reflection.py
@@ -227,6 +227,9 @@ class Inspector(object):
         :param schema: Optional, retrieve names from a non-default schema.
          For special quoting, use :class:`.quoted_name`.
 
+        .. versionchanged:: 1.0.0 now returns materialized views as well
+        as normal views.
+
         """
 
         return self.dialect.get_view_names(self.bind, schema,
diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py
index f0f0e9e47..a04bcbbdd 100644
--- a/lib/sqlalchemy/testing/requirements.py
+++ b/lib/sqlalchemy/testing/requirements.py
@@ -252,15 +252,6 @@ class SuiteRequirements(Requirements):
 
         return exclusions.closed()
 
-    @property
-    def foreign_tables(self):
-        """target platform supports FOREIGN TABLEs."""
-
-        return exclusions.only_if(
-            lambda config: config.db.dialect.supports_foreign_tables,
-            "%(database)s %(does_support)s 'FOREIGN TABLEs'"
-        )
-
     @property
     def schemas(self):
         """Target database must support external schemas, and have one
@@ -310,12 +301,6 @@ class SuiteRequirements(Requirements):
         """
         return self.views
 
-    @property
-    def foreign_table_reflection(self):
-        """target database must support inspection of the full CREATE FOREIGN TABLE definition.
-        """
-        return self.foreign_tables
-
     @property
     def schema_reflection(self):
         return self.schemas
diff --git a/test/dialect/postgresql/test_reflection.py b/test/dialect/postgresql/test_reflection.py
index 567aec927..3bc4cd715 100644
--- a/test/dialect/postgresql/test_reflection.py
+++ b/test/dialect/postgresql/test_reflection.py
@@ -107,6 +107,12 @@ class AltRelkindReflectionTest(fixtures.TestBase, AssertsExecutionResults):
             (89, 'd1',)
         ]
 
+    def test_get_foreign_table_names(self):
+        inspector = inspect(testing.db)
+        connection = testing.db.connect()
+        ft_names = inspector.get_foreign_table_names(connection)
+        assert u'test_foreigntable' in ft_names
+
 
 class DomainReflectionTest(fixtures.TestBase, AssertsExecutionResults):
     """Test PostgreSQL domains"""
-- 
cgit v1.2.1