From a4a81f7b962102ba1ed3a60d9ac52c62a57c4458 Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Sat, 7 Jan 2012 15:37:51 -0500
Subject: [PATCH] - [bug] the "name" of an FK constraint in SQLite is reflected
 as "None", not "0" [ticket:2364]. SQLite does not appear to support
 constraint naming in any case (the names are ignored).

---
 CHANGES                                |  6 +++++
 lib/sqlalchemy/dialects/sqlite/base.py |  2 ++
 test/dialect/test_sqlite.py            | 37 ++++++++++++++++++++++++++
 test/engine/test_reflection.py         | 12 +++++++--
 4 files changed, 55 insertions(+), 2 deletions(-)

diff --git a/CHANGES b/CHANGES
index e2d6e827cb..a1cb54d3d1 100644
--- a/CHANGES
+++ b/CHANGES
@@ -23,6 +23,12 @@ CHANGES
     criteria which will join via AND, i.e.
     query.filter(x==y, z>q, ...)
 
+- sqlite
+  - [bug] the "name" of an FK constraint in SQLite
+    is reflected as "None", not "0" [ticket:2364].
+    SQLite does not appear to support constraint
+    naming in any case (the names are ignored).
+
 - Py3K
   - [bug] Fixed inappropriate usage of util.py3k
     flag and renamed it to util.py3k_warning, since 
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index 63f66f9d28..004798956a 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -697,6 +697,8 @@ class SQLiteDialect(default.DefaultDialect):
             if row is None:
                 break
             (constraint_name, rtbl, lcol, rcol) = (row[0], row[2], row[3], row[4])
+            if not constraint_name:
+                constraint_name = None
             # sqlite won't return rcol if the table
             # was created with REFERENCES <tablename>, no col
             if rcol is None:
diff --git a/test/dialect/test_sqlite.py b/test/dialect/test_sqlite.py
index c4748500ae..ed9d7e4936 100644
--- a/test/dialect/test_sqlite.py
+++ b/test/dialect/test_sqlite.py
@@ -728,4 +728,41 @@ class ReflectHeadlessFKsTest(fixtures.TestBase):
 
         assert b.c.id.references(a.c.id)
 
+class ReflectFKConstraintTest(fixtures.TestBase):
+    __only_on__ = 'sqlite'
 
+    def setup(self):
+        testing.db.execute("CREATE TABLE a (id INTEGER PRIMARY KEY)")
+        testing.db.execute("CREATE TABLE b (id INTEGER PRIMARY KEY, "
+                            "FOREIGN KEY(id) REFERENCES a(id))")
+        testing.db.execute("CREATE TABLE c (id INTEGER, "
+                            "CONSTRAINT bar PRIMARY KEY(id),"
+                            "CONSTRAINT foo FOREIGN KEY(id) REFERENCES a(id))")
+
+    def teardown(self):
+        testing.db.execute("drop table c")
+        testing.db.execute("drop table b")
+        testing.db.execute("drop table a")
+
+    def test_name_is_none(self):
+        # and not "0"
+        meta = MetaData()
+        b = Table('b', meta, autoload=True, autoload_with=testing.db)
+        eq_(
+            [con.name for con in b.constraints],
+            [None, None]
+        )
+
+    def test_name_not_none(self):
+        # we don't have names for PK constraints,
+        # it appears we get back None in the pragma for 
+        # FKs also (also it doesn't even appear to be documented on sqlite's docs
+        # at http://www.sqlite.org/pragma.html#pragma_foreign_key_list
+        # how did we ever know that's the "name" field ??)
+
+        meta = MetaData()
+        c = Table('c', meta, autoload=True, autoload_with=testing.db)
+        eq_(
+            set([con.name for con in c.constraints]),
+            set([None, None])
+        )
diff --git a/test/engine/test_reflection.py b/test/engine/test_reflection.py
index 45d72bfabe..e32c868d63 100644
--- a/test/engine/test_reflection.py
+++ b/test/engine/test_reflection.py
@@ -1469,7 +1469,12 @@ class ComponentReflectionTest(fixtures.TestBase):
         users_fkeys = insp.get_foreign_keys(users.name,
                                             schema=schema)
         fkey1 = users_fkeys[0]
-        self.assert_(fkey1['name'] is not None)
+
+        @testing.fails_on('sqlite', 'no support for constraint names')
+        def go():
+            self.assert_(fkey1['name'] is not None)
+        go()
+
         eq_(fkey1['referred_schema'], expected_schema)
         eq_(fkey1['referred_table'], users.name)
         eq_(fkey1['referred_columns'], ['user_id', ])
@@ -1478,7 +1483,10 @@ class ComponentReflectionTest(fixtures.TestBase):
         addr_fkeys = insp.get_foreign_keys(addresses.name,
                                            schema=schema)
         fkey1 = addr_fkeys[0]
-        self.assert_(fkey1['name'] is not None)
+        @testing.fails_on('sqlite', 'no support for constraint names')
+        def go():
+            self.assert_(fkey1['name'] is not None)
+        go()
         eq_(fkey1['referred_schema'], expected_schema)
         eq_(fkey1['referred_table'], users.name)
         eq_(fkey1['referred_columns'], ['user_id', ])
-- 
2.47.2