From bbe3f0a27c5b4cb6506d2f23d8a2654c80d6b481 Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Fri, 12 Nov 2010 10:36:03 -0500
Subject: [PATCH] - The REFERENCES clause in a CREATE TABLE that includes a
 remote schema name now renders the remote name without the schema clause, as
 required by SQLite.  [ticket:1851]

---
 CHANGES                                |  6 ++++++
 lib/sqlalchemy/dialects/sqlite/base.py |  4 ++++
 lib/sqlalchemy/sql/compiler.py         |  7 ++++++-
 test/dialect/test_sqlite.py            | 21 +++++++++++++++++++++
 4 files changed, 37 insertions(+), 1 deletion(-)

diff --git a/CHANGES b/CHANGES
index 7a07b7c766..ee7fad4368 100644
--- a/CHANGES
+++ b/CHANGES
@@ -35,6 +35,12 @@ CHANGES
     has_table() property works again.  Regression from
     0.6.3 (we don't have a Jython buildbot, sorry)
     [ticket:1960]
+
+- sqlite
+  - The REFERENCES clause in a CREATE TABLE that includes
+    a remote schema name now renders the remote name without
+    the schema clause, as required by SQLite.  [ticket:1851]
+    
     
 0.6.5
 =====
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index b84b18e68e..8ff93580b7 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -271,6 +271,10 @@ class SQLiteDDLCompiler(compiler.DDLCompiler):
         return super(SQLiteDDLCompiler, self).\
                     visit_primary_key_constraint(constraint)
  
+    def define_constraint_remote_table(self, constraint, table, preparer):
+        """Format the remote table clause of a CREATE CONSTRAINT clause."""
+        
+        return preparer.format_table(table, use_schema=False)
 
     def visit_create_index(self, create):
         index = create.element
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index d3b8bf023a..4b41c6ed33 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -1306,7 +1306,7 @@ class DDLCompiler(engine.Compiled):
         text += "FOREIGN KEY(%s) REFERENCES %s (%s)" % (
             ', '.join(preparer.quote(f.parent.name, f.parent.quote)
                       for f in constraint._elements.values()),
-            preparer.format_table(remote_table),
+            self.define_constraint_remote_table(constraint, remote_table, preparer),
             ', '.join(preparer.quote(f.column.name, f.column.quote)
                       for f in constraint._elements.values())
         )
@@ -1314,6 +1314,11 @@ class DDLCompiler(engine.Compiled):
         text += self.define_constraint_deferrability(constraint)
         return text
 
+    def define_constraint_remote_table(self, constraint, table, preparer):
+        """Format the remote table clause of a CREATE CONSTRAINT clause."""
+        
+        return preparer.format_table(table)
+
     def visit_unique_constraint(self, constraint):
         text = ""
         if constraint.name is not None:
diff --git a/test/dialect/test_sqlite.py b/test/dialect/test_sqlite.py
index 0cdd3848e2..9099ec5d70 100644
--- a/test/dialect/test_sqlite.py
+++ b/test/dialect/test_sqlite.py
@@ -384,6 +384,27 @@ class SQLTest(TestBase, AssertsCompiledSQL):
                                 "SELECT CAST(STRFTIME('%s', t.col1) AS "
                                 "INTEGER) AS anon_1 FROM t" % subst)
 
+    def test_constraints_with_schemas(self):
+        metadata = MetaData()
+        t1 = Table('t1', metadata, 
+                        Column('id', Integer, primary_key=True),
+                        schema='master')
+        t2 = Table('t2', metadata, 
+                        Column('id', Integer, primary_key=True),
+                        Column('t2_id', Integer, ForeignKey('master.t1.id')),
+                        schema='master'
+                    )
+        
+        self.assert_compile(
+            schema.CreateTable(t2),
+                "CREATE TABLE master.t2 ("
+                "id INTEGER NOT NULL, "
+                "t2_id INTEGER, "
+                "PRIMARY KEY (id), "
+                "FOREIGN KEY(t2_id) REFERENCES t1 (id)"
+                ")"            
+        )
+
 
 class InsertTest(TestBase, AssertsExecutionResults):
 
-- 
2.47.3