Always include a schema name in SQLite PRAGMA

Fixed bug where usage of "PRAGMA table_info" in SQLite dialect meant that
reflection features to detect for table existence, list of table columns,
and list of foreign keys, would default to any table in any attached
database, when no schema name was given and the table did not exist in the
base schema.  The fix explicitly runs PRAGMA for the 'main' schema and then
the 'temp' schema if the 'main' returned no rows, to maintain the behavior
of tables + temp tables in the "no schema" namespace, attached tables only
in the "schema" namespace.

Fixes: #4793
Change-Id: I75bc03ef42581c46b98987510d2d2e701df07412

1 files changed, 20 insertions, 11 deletions

diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index c9309cbad..ef8507d05 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -2003,17 +2003,26 @@ class SQLiteDialect(default.DefaultDialect):
     def _get_table_pragma(self, connection, pragma, table_name, schema=None):
         quote = self.identifier_preparer.quote_identifier
         if schema is not None:
-            statement = "PRAGMA %s." % quote(schema)
+            statements = ["PRAGMA %s." % quote(schema)]
         else:
-            statement = "PRAGMA "
+            # because PRAGMA looks in all attached databases if no schema
+            # given, need to specify "main" schema, however since we want
+            # 'temp' tables in the same namespace as 'main', need to run
+            # the PRAGMA twice
+            statements = ["PRAGMA main.", "PRAGMA temp."]
+
         qtable = quote(table_name)
-        statement = "%s%s(%s)" % (statement, pragma, qtable)
-        cursor = connection.execute(statement)
-        if not cursor._soft_closed:
-            # work around SQLite issue whereby cursor.description
-            # is blank when PRAGMA returns no rows:
-            # http://www.sqlite.org/cvstrac/tktview?tn=1884
-            result = cursor.fetchall()
+        for statement in statements:
+            statement = "%s%s(%s)" % (statement, pragma, qtable)
+            cursor = connection.execute(statement)
+            if not cursor._soft_closed:
+                # work around SQLite issue whereby cursor.description
+                # is blank when PRAGMA returns no rows:
+                # http://www.sqlite.org/cvstrac/tktview?tn=1884
+                result = cursor.fetchall()
+            else:
+                result = []
+            if result:
+                return result
         else:
-            result = []
-        return result
+            return []