Merge branch 'master' of github.com:ijl/sqlalchemy into merge_2183

6 files changed, 164 insertions, 8 deletions

diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 445355e9f..07aa76f65 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -2236,7 +2236,7 @@ class MySQLDialect(default.DefaultDialect):
             ref_names = spec['foreign']
 
             con_kw = {}
-            for opt in ('name', 'onupdate', 'ondelete'):
+            for opt in ('onupdate', 'ondelete'):
                 if spec.get(opt, False):
                     con_kw[opt] = spec[opt]
 
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 55c6b315a..5c88ee023 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -1901,6 +1901,15 @@ class PGDialect(default.DefaultDialect):
                 n.oid = c.relnamespace
           ORDER BY 1
         """
+        # http://www.postgresql.org/docs/9.0/static/sql-createtable.html
+        FK_REGEX = re.compile(
+            r'FOREIGN KEY \((.*?)\) REFERENCES (?:(.*?)\.)?(.*?)\((.*?)\)'
+            r'[\s]?(MATCH (FULL|PARTIAL|SIMPLE)+)?'
+            r'[\s]?(ON UPDATE (CASCADE|RESTRICT|NO ACTION|SET NULL|SET DEFAULT)+)?'
+            r'[\s]?(ON DELETE (CASCADE|RESTRICT|NO ACTION|SET NULL|SET DEFAULT)+)?'
+            r'[\s]?(DEFERRABLE|NOT DEFERRABLE)?'
+            r'[\s]?(INITIALLY (DEFERRED|IMMEDIATE)+)?'
+        )
 
         t = sql.text(FK_SQL, typemap={
                                 'conname': sqltypes.Unicode,
@@ -1908,15 +1917,18 @@ class PGDialect(default.DefaultDialect):
         c = connection.execute(t, table=table_oid)
         fkeys = []
         for conname, condef, conschema in c.fetchall():
-            m = re.search('FOREIGN KEY \((.*?)\) REFERENCES '
-                            '(?:(.*?)\.)?(.*?)\((.*?)\)', condef).groups()
+            m = re.search(FK_REGEX, condef).groups()
             constrained_columns, referred_schema, \
-                    referred_table, referred_columns = m
+                    referred_table, referred_columns, \
+                    _, match, _, onupdate, _, ondelete, \
+                    deferrable, _, initially = m
+            if deferrable is not None:
+                deferrable = True if deferrable == 'DEFERRABLE' else False
             constrained_columns = [preparer._unquote_identifier(x)
                         for x in re.split(r'\s*,\s*', constrained_columns)]
 
             if referred_schema:
-                referred_schema =\
+                referred_schema = \
                                 preparer._unquote_identifier(referred_schema)
             elif schema is not None and schema == conschema:
                 # no schema was returned by pg_get_constraintdef().  This
@@ -1934,7 +1946,14 @@ class PGDialect(default.DefaultDialect):
                 'constrained_columns': constrained_columns,
                 'referred_schema': referred_schema,
                 'referred_table': referred_table,
-                'referred_columns': referred_columns
+                'referred_columns': referred_columns,
+                'options': {
+                    'onupdate': onupdate,
+                    'ondelete': ondelete,
+                    'deferrable': deferrable,
+                    'initially': initially,
+                    'match': match
+                }
             }
             fkeys.append(fkey_d)
         return fkeys
diff --git a/lib/sqlalchemy/engine/reflection.py b/lib/sqlalchemy/engine/reflection.py
index a9ccf5539..461f5eb23 100644
--- a/lib/sqlalchemy/engine/reflection.py
+++ b/lib/sqlalchemy/engine/reflection.py
@@ -564,9 +564,14 @@ class Inspector(object):
                                 )
                 for column in referred_columns:
                     refspec.append(".".join([referred_table, column]))
+            if 'options' in fkey_d:
+                options = fkey_d['options']
+            else:
+                options = {}
             table.append_constraint(
                 sa_schema.ForeignKeyConstraint(constrained_columns, refspec,
-                                               conname, link_to_name=True))
+                                               conname, link_to_name=True,
+                                               **options))
         # Indexes
         indexes = self.get_indexes(table_name, schema)
         for index_d in indexes:
diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py
index 40bd5b90a..6d5ffd4d6 100644
--- a/lib/sqlalchemy/sql/schema.py
+++ b/lib/sqlalchemy/sql/schema.py
@@ -2400,7 +2400,9 @@ class ForeignKeyConstraint(Constraint):
                     ondelete=self.ondelete,
                     use_alter=self.use_alter,
                     link_to_name=self.link_to_name,
-                    match=self.match
+                    match=self.match,
+                    deferrable=self.deferrable,
+                    initially=self.initially
                 )
 
         if table is not None:
diff --git a/test/dialect/postgresql/test_dialect.py b/test/dialect/postgresql/test_dialect.py
index aa11662a0..5b845cbd1 100644
--- a/test/dialect/postgresql/test_dialect.py
+++ b/test/dialect/postgresql/test_dialect.py
@@ -17,6 +17,7 @@ from sqlalchemy.dialects.postgresql import base as postgresql
 import logging
 import logging.handlers
 from sqlalchemy.testing.mock import Mock
+from sqlalchemy.engine.reflection import Inspector
 
 class MiscTest(fixtures.TestBase, AssertsExecutionResults, AssertsCompiledSQL):
 
@@ -231,3 +232,83 @@ class MiscTest(fixtures.TestBase, AssertsExecutionResults, AssertsCompiledSQL):
                 ddl_compiler.get_column_specification(t.c.c),
                 "c %s NOT NULL" % expected
             )
+
+class InspectionTest(fixtures.TestBase, AssertsExecutionResults, AssertsCompiledSQL):
+
+    __only_on__ = 'postgresql'
+
+    def test_get_foreign_keys(self):
+        """
+        PGDialect.get_foreign_keys()
+        """
+        metadata = MetaData(testing.db)
+        person = Table('person', metadata,
+            Column('id', String(length=32), nullable=False, primary_key=True),
+            Column('company_id', ForeignKey('company.id',
+                name='person_company_id_fkey',
+                match='FULL', onupdate='RESTRICT', ondelete='RESTRICT',
+                deferrable=True, initially='DEFERRED'
+                )
+            )
+        )
+        company = Table('company', metadata,
+            Column('id', String(length=32), nullable=False, primary_key=True),
+            Column('name', String(length=255)),
+            Column('industry_id', ForeignKey('industry.id',
+                name='company_industry_id_fkey',
+                onupdate='CASCADE', ondelete='CASCADE',
+                deferrable=False, # PG default
+                initially='IMMEDIATE' # PG default
+                )
+            )
+        )
+        industry = Table('industry', metadata,
+            Column('id', Integer(), nullable=False, primary_key=True),
+            Column('name', String(length=255))
+        )
+        fk_ref = {
+            'person_company_id_fkey': {
+                'name': 'person_company_id_fkey',
+                'constrained_columns': ['company_id'],
+                'referred_columns': ['id'],
+                'referred_table': 'company',
+                'referred_schema': None,
+                'options': {
+                    'onupdate': 'RESTRICT',
+                    'deferrable': True,
+                    'ondelete': 'RESTRICT',
+                    'initially': 'DEFERRED',
+                    'match': 'FULL'
+                    }
+                },
+            'company_industry_id_fkey': {
+                'name': 'company_industry_id_fkey',
+                'constrained_columns': ['industry_id'],
+                'referred_columns': ['id'],
+                'referred_table': 'industry',
+                'referred_schema': None,
+                'options': {
+                    'onupdate': 'CASCADE',
+                    'deferrable': None,
+                    'ondelete': 'CASCADE',
+                    'initially': None,
+                    'match': None
+                }
+            }
+        }
+        try:
+            connection = testing.db.connect()
+            industry.create()
+            company.create()
+            person.create()
+            inspector = Inspector.from_engine(connection)
+            fks = inspector.get_foreign_keys('person') + \
+                inspector.get_foreign_keys('company')
+            for fk in fks:
+                ref = fk_ref[fk['name']]
+                for key, val in fk.items():
+                    eq_(val, ref[key])
+        finally:
+            person.drop()
+            company.drop()
+            industry.drop()
diff --git a/test/engine/test_reflection.py b/test/engine/test_reflection.py
index 21c050915..c36052781 100644
--- a/test/engine/test_reflection.py
+++ b/test/engine/test_reflection.py
@@ -602,6 +602,55 @@ class ReflectionTest(fixtures.TestBase, ComparesTables):
             is a2.c.user_id
         assert u2.join(a2).onclause.compare(u2.c.id == a2.c.user_id)
 
+    @testing.only_on(['postgresql', 'mysql'])
+    @testing.provide_metadata
+    def test_fk_options(self):
+        """test that foreign key reflection includes options (on
+        backends with {dialect}.get_foreign_keys() support)"""
+
+        if testing.against('postgresql'):
+            test_attrs = ('match', 'onupdate', 'ondelete', 'deferrable', 'initially')
+            addresses_user_id_fkey = sa.ForeignKey(
+                # Each option is specifically not a Postgres default, or
+                # it won't be returned by PG's inspection
+                'users.id',
+                name = 'addresses_user_id_fkey',
+                match='FULL',
+                onupdate='RESTRICT',
+                ondelete='RESTRICT',
+                deferrable=True,
+                initially='DEFERRED'
+            )
+        elif testing.against('mysql'):
+            # MATCH, DEFERRABLE, and INITIALLY cannot be defined for MySQL
+            # ON UPDATE and ON DELETE have defaults of RESTRICT, which are
+            # elided by MySQL's inspection
+            addresses_user_id_fkey = sa.ForeignKey(
+                'users.id',
+                name = 'addresses_user_id_fkey',
+                onupdate='CASCADE',
+                ondelete='CASCADE'
+            )
+            test_attrs = ('onupdate', 'ondelete')
+
+        meta = self.metadata
+        Table('users', meta,
+            Column('id', sa.Integer, primary_key=True),
+            Column('name', sa.String(30)),
+            test_needs_fk=True)
+        Table('addresses', meta,
+            Column('id', sa.Integer, primary_key=True),
+            Column('user_id', sa.Integer, addresses_user_id_fkey),
+            test_needs_fk=True)
+        meta.create_all()
+
+        meta2 = MetaData()
+        meta2.reflect(testing.db)
+        for fk in meta2.tables['addresses'].foreign_keys:
+            ref = locals()[fk.name]
+            for attr in test_attrs:
+                assert getattr(fk, attr) == getattr(ref, attr)
+
     def test_pks_not_uniques(self):
         """test that primary key reflection not tripped up by unique
         indexes"""