From acb2faedcd1502248e6db46228112133094dd3cf Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Mon, 7 Dec 2009 23:08:48 +0000
Subject: [PATCH] - sqlite dialect properly generates CREATE INDEX for a table
 that is in an alternate schema.  [ticket:1439]

---
 CHANGES                            |  4 ++++
 lib/sqlalchemy/databases/sqlite.py | 23 +++++++++++++++++++++++
 test/dialect/test_sqlite.py        | 12 ++++++++++++
 3 files changed, 39 insertions(+)

diff --git a/CHANGES b/CHANGES
index 3745e1156c..e9ddf26ae2 100644
--- a/CHANGES
+++ b/CHANGES
@@ -64,6 +64,10 @@ CHANGES
     - The cursor associated with connection pool connections
       (i.e. _CursorFairy) now proxies `__iter__()` to the 
       underlying cursor correctly. [ticket:1632]
+
+- sqlite
+    - sqlite dialect properly generates CREATE INDEX for a table
+      that is in an alternate schema.  [ticket:1439]
       
 - postgresql
     - Added support for reflecting the DOUBLE PRECISION type,
diff --git a/lib/sqlalchemy/databases/sqlite.py b/lib/sqlalchemy/databases/sqlite.py
index 8952b2b1da..6b5e0d5d53 100644
--- a/lib/sqlalchemy/databases/sqlite.py
+++ b/lib/sqlalchemy/databases/sqlite.py
@@ -614,6 +614,19 @@ class SQLiteSchemaGenerator(compiler.SchemaGenerator):
             colspec += " NOT NULL"
         return colspec
 
+    def visit_index(self, index):
+        preparer = self.preparer
+        self.append("CREATE ")
+        if index.unique:
+            self.append("UNIQUE ")
+        self.append("INDEX %s ON %s (%s)" \
+                    % (preparer.format_index(index,
+                       name=self._validate_identifier(index.name, True)),
+                       preparer.format_table(index.table, use_schema=False),
+                       ', '.join(preparer.quote(c.name, c.quote)
+                                 for c in index.columns)))
+        self.execute()
+
 class SQLiteIdentifierPreparer(compiler.IdentifierPreparer):
     reserved_words = set([
         'add', 'after', 'all', 'alter', 'analyze', 'and', 'as', 'asc',
@@ -638,6 +651,16 @@ class SQLiteIdentifierPreparer(compiler.IdentifierPreparer):
     def __init__(self, dialect):
         super(SQLiteIdentifierPreparer, self).__init__(dialect)
 
+    def format_index(self, index, use_schema=True, name=None):
+        """Prepare a quoted index and schema name."""
+
+        if name is None:
+            name = index.name
+        result = self.quote(name, index.quote)
+        if not self.omit_schema and use_schema and getattr(index.table, "schema", None):
+            result = self.quote_schema(index.table.schema, index.table.quote_schema) + "." + result
+        return result
+
 dialect = SQLiteDialect
 dialect.poolclass = pool.SingletonThreadPool
 dialect.statement_compiler = SQLiteCompiler
diff --git a/test/dialect/test_sqlite.py b/test/dialect/test_sqlite.py
index eb4581e20f..f90f1c8b18 100644
--- a/test/dialect/test_sqlite.py
+++ b/test/dialect/test_sqlite.py
@@ -306,6 +306,18 @@ class DialectTest(TestBase, AssertsExecutionResults):
             raise
 
 
+    def test_create_index_with_schema(self):
+        """Test creation of index with explicit schema"""
+
+        meta = MetaData(testing.db)
+        t = Table('foo', meta, Column('bar', String, index=True), schema='main')
+
+        try:
+            meta.create_all()
+        finally:
+            meta.drop_all()
+
+
 class SQLTest(TestBase, AssertsCompiledSQL):
     """Tests SQLite-dialect specific compilation."""
 
-- 
2.47.3