From cb23fa243f5138aac7acb2a134d567f1a297d42e Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Wed, 17 Sep 2014 15:15:21 -0400 Subject: [PATCH] - Added :meth:`.Inspector.get_temp_table_names` and :meth:`.Inspector.get_temp_view_names`; currently, only the SQLite dialect supports these methods. The return of temporary table and view names has been **removed** from SQLite's version of :meth:`.Inspector.get_table_names` and :meth:`.Inspector.get_view_names`; other database backends cannot support this information (such as MySQL), and the scope of operation is different in that the tables can be local to a session and typically aren't supported in remote schemas. fixes #3204 --- doc/build/changelog/changelog_10.rst | 18 +++++ doc/build/changelog/migration_10.rst | 20 +++++ lib/sqlalchemy/dialects/sqlite/base.py | 49 ++++++------ lib/sqlalchemy/engine/interfaces.py | 18 ++++- lib/sqlalchemy/engine/reflection.py | 24 ++++++ lib/sqlalchemy/testing/requirements.py | 14 ++++ .../testing/suite/test_reflection.py | 78 +++++++++++++++++++ test/dialect/test_sqlite.py | 17 ---- test/requirements.py | 11 +++ 9 files changed, 205 insertions(+), 44 deletions(-) diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst index b00dbb0a77..7bfcb574ee 100644 --- a/doc/build/changelog/changelog_10.rst +++ b/doc/build/changelog/changelog_10.rst @@ -21,6 +21,24 @@ series as well. For changes that are specific to 1.0 with an emphasis on compatibility concerns, see :doc:`/changelog/migration_10`. + .. change:: + :tags: bug, sqlite + :tickets: 3204 + + Added :meth:`.Inspector.get_temp_table_names` and + :meth:`.Inspector.get_temp_view_names`; currently, only the + SQLite dialect supports these methods. The return of temporary + table and view names has been **removed** from SQLite's version + of :meth:`.Inspector.get_table_names` and + :meth:`.Inspector.get_view_names`; other database backends cannot + support this information (such as MySQL), and the scope of operation + is different in that the tables can be local to a session and + typically aren't supported in remote schemas. + + .. seealso:: + + :ref:`change_3204` + .. change:: :tags: feature, postgresql :tickets: 2891 diff --git a/doc/build/changelog/migration_10.rst b/doc/build/changelog/migration_10.rst index d967afa35b..246eb9a14d 100644 --- a/doc/build/changelog/migration_10.rst +++ b/doc/build/changelog/migration_10.rst @@ -953,6 +953,26 @@ when using ODBC to avoid this issue entirely. :ticket:`3182` +.. _change_3204: + +SQLite has distinct methods for temporary table/view name reporting +------------------------------------------------------------------- + +The :meth:`.Inspector.get_table_names` and :meth:`.Inspector.get_view_names` +methods in the case of SQLite would also return the names of temporary +tables and views, which is not provided by any other dialect (in the case +of MySQL at least it is not even possible). This logic has been moved +out to two new methods :meth:`.Inspector.get_temp_table_names` and +:meth:`.Inspector.get_temp_view_names`. + +Note that reflection of a specific named temporary table or temporary view, +either by ``Table('name', autoload=True)`` or via methods like +:meth:`.Inspector.get_columns` continues to function for most if not all +dialects. For SQLite specifically, there is a bug fix for UNIQUE constraint +reflection from temp tables as well, which is :ticket:`3203`. + +:ticket:`3204` + .. _change_2984: Drizzle Dialect is now an External Dialect diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py index af793d2757..b0bf670a63 100644 --- a/lib/sqlalchemy/dialects/sqlite/base.py +++ b/lib/sqlalchemy/dialects/sqlite/base.py @@ -829,20 +829,26 @@ class SQLiteDialect(default.DefaultDialect): if schema is not None: qschema = self.identifier_preparer.quote_identifier(schema) master = '%s.sqlite_master' % qschema - s = ("SELECT name FROM %s " - "WHERE type='table' ORDER BY name") % (master,) - rs = connection.execute(s) else: - try: - s = ("SELECT name FROM " - " (SELECT * FROM sqlite_master UNION ALL " - " SELECT * FROM sqlite_temp_master) " - "WHERE type='table' ORDER BY name") - rs = connection.execute(s) - except exc.DBAPIError: - s = ("SELECT name FROM sqlite_master " - "WHERE type='table' ORDER BY name") - rs = connection.execute(s) + master = "sqlite_master" + s = ("SELECT name FROM %s " + "WHERE type='table' ORDER BY name") % (master,) + rs = connection.execute(s) + return [row[0] for row in rs] + + @reflection.cache + def get_temp_table_names(self, connection, **kw): + s = "SELECT name FROM sqlite_temp_master "\ + "WHERE type='table' ORDER BY name " + rs = connection.execute(s) + + return [row[0] for row in rs] + + @reflection.cache + def get_temp_view_names(self, connection, **kw): + s = "SELECT name FROM sqlite_temp_master "\ + "WHERE type='view' ORDER BY name " + rs = connection.execute(s) return [row[0] for row in rs] @@ -869,20 +875,11 @@ class SQLiteDialect(default.DefaultDialect): if schema is not None: qschema = self.identifier_preparer.quote_identifier(schema) master = '%s.sqlite_master' % qschema - s = ("SELECT name FROM %s " - "WHERE type='view' ORDER BY name") % (master,) - rs = connection.execute(s) else: - try: - s = ("SELECT name FROM " - " (SELECT * FROM sqlite_master UNION ALL " - " SELECT * FROM sqlite_temp_master) " - "WHERE type='view' ORDER BY name") - rs = connection.execute(s) - except exc.DBAPIError: - s = ("SELECT name FROM sqlite_master " - "WHERE type='view' ORDER BY name") - rs = connection.execute(s) + master = "sqlite_master" + s = ("SELECT name FROM %s " + "WHERE type='view' ORDER BY name") % (master,) + rs = connection.execute(s) return [row[0] for row in rs] diff --git a/lib/sqlalchemy/engine/interfaces.py b/lib/sqlalchemy/engine/interfaces.py index 71df29cac1..0ad2efae00 100644 --- a/lib/sqlalchemy/engine/interfaces.py +++ b/lib/sqlalchemy/engine/interfaces.py @@ -308,7 +308,15 @@ class Dialect(object): def get_table_names(self, connection, schema=None, **kw): """Return a list of table names for `schema`.""" - raise NotImplementedError + raise NotImplementedError() + + def get_temp_table_names(self, connection, schema=None, **kw): + """Return a list of temporary table names on the given connection, + if supported by the underlying backend. + + """ + + raise NotImplementedError() def get_view_names(self, connection, schema=None, **kw): """Return a list of all view names available in the database. @@ -319,6 +327,14 @@ class Dialect(object): raise NotImplementedError() + def get_temp_view_names(self, connection, schema=None, **kw): + """Return a list of temporary view names on the given connection, + if supported by the underlying backend. + + """ + + raise NotImplementedError() + def get_view_definition(self, connection, view_name, schema=None, **kw): """Return view definition. diff --git a/lib/sqlalchemy/engine/reflection.py b/lib/sqlalchemy/engine/reflection.py index cf1f2d3dd1..c0a3240a53 100644 --- a/lib/sqlalchemy/engine/reflection.py +++ b/lib/sqlalchemy/engine/reflection.py @@ -201,6 +201,30 @@ class Inspector(object): tnames = list(topological.sort(tuples, tnames)) return tnames + def get_temp_table_names(self): + """return a list of temporary table names for the current bind. + + This method is unsupported by most dialects; currently + only SQLite implements it. + + .. versionadded:: 1.0.0 + + """ + return self.dialect.get_temp_table_names( + self.bind, info_cache=self.info_cache) + + def get_temp_view_names(self): + """return a list of temporary view names for the current bind. + + This method is unsupported by most dialects; currently + only SQLite implements it. + + .. versionadded:: 1.0.0 + + """ + return self.dialect.get_temp_view_names( + self.bind, info_cache=self.info_cache) + def get_table_options(self, table_name, schema=None, **kw): """Return a dictionary of options specified when the table of the given name was created. diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py index a04bcbbdd4..da3e3128a0 100644 --- a/lib/sqlalchemy/testing/requirements.py +++ b/lib/sqlalchemy/testing/requirements.py @@ -313,6 +313,20 @@ class SuiteRequirements(Requirements): def foreign_key_constraint_reflection(self): return exclusions.open() + @property + def temp_table_reflection(self): + return exclusions.open() + + @property + def temp_table_names(self): + """target dialect supports listing of temporary table names""" + return exclusions.closed() + + @property + def temporary_views(self): + """target database supports temporary views""" + return exclusions.closed() + @property def index_reflection(self): return exclusions.open() diff --git a/lib/sqlalchemy/testing/suite/test_reflection.py b/lib/sqlalchemy/testing/suite/test_reflection.py index 575a38db93..690a880bb7 100644 --- a/lib/sqlalchemy/testing/suite/test_reflection.py +++ b/lib/sqlalchemy/testing/suite/test_reflection.py @@ -95,6 +95,27 @@ class ComponentReflectionTest(fixtures.TablesTest): cls.define_index(metadata, users) if testing.requires.view_column_reflection.enabled: cls.define_views(metadata, schema) + if not schema and testing.requires.temp_table_reflection.enabled: + cls.define_temp_tables(metadata) + + @classmethod + def define_temp_tables(cls, metadata): + temp_table = Table( + "user_tmp", metadata, + Column("id", sa.INT, primary_key=True), + Column('name', sa.VARCHAR(50)), + Column('foo', sa.INT), + sa.UniqueConstraint('name', name='user_tmp_uq'), + sa.Index("user_tmp_ix", "foo"), + prefixes=['TEMPORARY'] + ) + if testing.requires.view_reflection.enabled and \ + testing.requires.temporary_views.enabled: + event.listen( + temp_table, "after_create", + DDL("create temporary view user_tmp_v as " + "select * from user_tmp") + ) @classmethod def define_index(cls, metadata, users): @@ -147,6 +168,7 @@ class ComponentReflectionTest(fixtures.TablesTest): users, addresses, dingalings = self.tables.users, \ self.tables.email_addresses, self.tables.dingalings insp = inspect(meta.bind) + if table_type == 'view': table_names = insp.get_view_names(schema) table_names.sort() @@ -162,6 +184,20 @@ class ComponentReflectionTest(fixtures.TablesTest): answer = ['dingalings', 'email_addresses', 'users'] eq_(sorted(table_names), answer) + @testing.requires.temp_table_names + def test_get_temp_table_names(self): + insp = inspect(self.metadata.bind) + temp_table_names = insp.get_temp_table_names() + eq_(sorted(temp_table_names), ['user_tmp']) + + @testing.requires.view_reflection + @testing.requires.temp_table_names + @testing.requires.temporary_views + def test_get_temp_view_names(self): + insp = inspect(self.metadata.bind) + temp_table_names = insp.get_temp_view_names() + eq_(sorted(temp_table_names), ['user_tmp_v']) + @testing.requires.table_reflection def test_get_table_names(self): self._test_get_table_names() @@ -294,6 +330,28 @@ class ComponentReflectionTest(fixtures.TablesTest): def test_get_columns_with_schema(self): self._test_get_columns(schema=testing.config.test_schema) + @testing.requires.temp_table_reflection + def test_get_temp_table_columns(self): + meta = MetaData(testing.db) + user_tmp = self.tables.user_tmp + insp = inspect(meta.bind) + cols = insp.get_columns('user_tmp') + self.assert_(len(cols) > 0, len(cols)) + + for i, col in enumerate(user_tmp.columns): + eq_(col.name, cols[i]['name']) + + @testing.requires.temp_table_reflection + @testing.requires.view_column_reflection + @testing.requires.temporary_views + def test_get_temp_view_columns(self): + insp = inspect(self.metadata.bind) + cols = insp.get_columns('user_tmp_v') + eq_( + [col['name'] for col in cols], + ['id', 'name', 'foo'] + ) + @testing.requires.view_column_reflection def test_get_view_columns(self): self._test_get_columns(table_type='view') @@ -426,6 +484,26 @@ class ComponentReflectionTest(fixtures.TablesTest): def test_get_unique_constraints(self): self._test_get_unique_constraints() + @testing.requires.temp_table_reflection + def test_get_temp_table_unique_constraints(self): + insp = inspect(self.metadata.bind) + eq_( + insp.get_unique_constraints('user_tmp'), + [{'column_names': ['name'], 'name': 'user_tmp_uq'}] + ) + + @testing.requires.temp_table_reflection + def test_get_temp_table_indexes(self): + insp = inspect(self.metadata.bind) + indexes = insp.get_indexes('user_tmp') + eq_( + # TODO: we need to add better filtering for indexes/uq constraints + # that are doubled up + [idx for idx in indexes if idx['name'] == 'user_tmp_ix'], + [{'unique': False, 'column_names': ['foo'], 'name': 'user_tmp_ix'}] + ) + + @testing.requires.unique_constraint_reflection @testing.requires.schemas def test_get_unique_constraints_with_schema(self): diff --git a/test/dialect/test_sqlite.py b/test/dialect/test_sqlite.py index e77a039806..e1f6225f4e 100644 --- a/test/dialect/test_sqlite.py +++ b/test/dialect/test_sqlite.py @@ -515,23 +515,6 @@ class DialectTest(fixtures.TestBase, AssertsExecutionResults): finally: cx.execute('DETACH DATABASE test_schema') - @testing.exclude('sqlite', '<', (2, 6), 'no database support') - def test_temp_table_reflection(self): - cx = testing.db.connect() - try: - cx.execute('CREATE TEMPORARY TABLE tempy (id INT)') - assert 'tempy' in cx.dialect.get_table_names(cx, None) - meta = MetaData(cx) - tempy = Table('tempy', meta, autoload=True) - assert len(tempy.c) == 1 - meta.drop_all() - except: - try: - cx.execute('DROP TABLE tempy') - except exc.DBAPIError: - pass - raise - def test_file_path_is_absolute(self): d = pysqlite_dialect.dialect() eq_( diff --git a/test/requirements.py b/test/requirements.py index 14bb256912..cfdfc8054d 100644 --- a/test/requirements.py +++ b/test/requirements.py @@ -296,6 +296,17 @@ class DefaultRequirements(SuiteRequirements): "sqlite" ) + @property + def temp_table_names(self): + """target dialect supports listing of temporary table names""" + + return only_on(['sqlite']) + + @property + def temporary_views(self): + """target database supports temporary views""" + return only_on(['sqlite', 'postgresql']) + @property def update_nowait(self): """Target database must support SELECT...FOR UPDATE NOWAIT""" -- 2.47.3