From d3ee4f6155acbc1b2df85fef3f4d2cdae9e1306c Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Fri, 12 Nov 2010 10:49:17 -0500
Subject: [PATCH] - On the same theme, the REFERENCES clause in a CREATE TABLE
 that includes a remote schema to a *different* schema than that of the parent
 table doesn't render at all, as cross-schema references do not appear to be
 supported.

---
 CHANGES                                |  9 +++++--
 lib/sqlalchemy/dialects/sqlite/base.py | 12 ++++++++-
 test/dialect/test_sqlite.py            | 36 +++++++++++++++++++++++---
 3 files changed, 51 insertions(+), 6 deletions(-)

diff --git a/CHANGES b/CHANGES
index ee7fad4368..7b8749a3f5 100644
--- a/CHANGES
+++ b/CHANGES
@@ -38,9 +38,14 @@ CHANGES
 
 - sqlite
   - The REFERENCES clause in a CREATE TABLE that includes
-    a remote schema name now renders the remote name without
+    a remote schema to another table with the same schema 
+    name now renders the remote name without
     the schema clause, as required by SQLite.  [ticket:1851]
-    
+
+  - On the same theme, the REFERENCES clause in a CREATE TABLE
+    that includes a remote schema to a *different* schema
+    than that of the parent table doesn't render at all,
+    as cross-schema references do not appear to be supported.
     
 0.6.5
 =====
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index 8ff93580b7..994904b6ac 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -270,7 +270,17 @@ class SQLiteDDLCompiler(compiler.DDLCompiler):
  
         return super(SQLiteDDLCompiler, self).\
                     visit_primary_key_constraint(constraint)
- 
+                    
+    def visit_foreign_key_constraint(self, constraint):
+        
+        local_table = constraint._elements.values()[0].parent.table
+        remote_table = list(constraint._elements.values())[0].column.table
+        
+        if local_table.schema != remote_table.schema:
+            return None
+        else:
+            return super(SQLiteDDLCompiler, self).visit_foreign_key_constraint(constraint)
+
     def define_constraint_remote_table(self, constraint, table, preparer):
         """Format the remote table clause of a CREATE CONSTRAINT clause."""
         
diff --git a/test/dialect/test_sqlite.py b/test/dialect/test_sqlite.py
index 9099ec5d70..19ec260d37 100644
--- a/test/dialect/test_sqlite.py
+++ b/test/dialect/test_sqlite.py
@@ -391,17 +391,47 @@ class SQLTest(TestBase, AssertsCompiledSQL):
                         schema='master')
         t2 = Table('t2', metadata, 
                         Column('id', Integer, primary_key=True),
-                        Column('t2_id', Integer, ForeignKey('master.t1.id')),
+                        Column('t1_id', Integer, ForeignKey('master.t1.id')),
                         schema='master'
                     )
+        t3 = Table('t3', metadata, 
+                        Column('id', Integer, primary_key=True),
+                        Column('t1_id', Integer, ForeignKey('master.t1.id')),
+                        schema='alternate'
+                    )
+        t4 = Table('t4', metadata, 
+                        Column('id', Integer, primary_key=True),
+                        Column('t1_id', Integer, ForeignKey('master.t1.id')),
+                    )
         
+        # schema->schema, generate REFERENCES with no schema name
         self.assert_compile(
             schema.CreateTable(t2),
                 "CREATE TABLE master.t2 ("
                 "id INTEGER NOT NULL, "
-                "t2_id INTEGER, "
+                "t1_id INTEGER, "
                 "PRIMARY KEY (id), "
-                "FOREIGN KEY(t2_id) REFERENCES t1 (id)"
+                "FOREIGN KEY(t1_id) REFERENCES t1 (id)"
+                ")"            
+        )
+
+        # schema->different schema, don't generate REFERENCES
+        self.assert_compile(
+            schema.CreateTable(t3),
+                "CREATE TABLE alternate.t3 ("
+                "id INTEGER NOT NULL, "
+                "t1_id INTEGER, "
+                "PRIMARY KEY (id)"
+                ")"            
+        )
+
+        # same for local schema
+        self.assert_compile(
+            schema.CreateTable(t4),
+                "CREATE TABLE t4 ("
+                "id INTEGER NOT NULL, "
+                "t1_id INTEGER, "
+                "PRIMARY KEY (id)"
                 ")"            
         )
 
-- 
2.47.2