From 63d90b0f44016b15bed6c4108d90a71c15f05a09 Mon Sep 17 00:00:00 2001 From: Federico Caselli Date: Sat, 30 Jul 2022 12:40:37 +0200 Subject: [PATCH] SQLite reflection ignores schema internal names Added new parameter to SQLite for reflection methods called ``sqlite_include_internal=True``; when omitted, local tables that start with the prefix ``sqlite_``, which per SQLite documentation are noted as "internal schema" tables such as the ``sqlite_sequence`` table generated to support "AUTOINCREMENT" columns, will not be included in reflection methods that return lists of local objects. This prevents issues for example when using Alembic autogenerate, which previously would consider these SQLite-generated tables as being remove from the model. Fixes: #8234 Change-Id: I36ee7a053e04b6c46c912aaa0d7e035a5b88a4f9 --- doc/build/changelog/unreleased_20/8234.rst | 16 ++++ lib/sqlalchemy/dialects/sqlite/base.py | 94 ++++++++++++++++++---- test/dialect/test_sqlite.py | 72 +++++++++++++++++ 3 files changed, 165 insertions(+), 17 deletions(-) create mode 100644 doc/build/changelog/unreleased_20/8234.rst diff --git a/doc/build/changelog/unreleased_20/8234.rst b/doc/build/changelog/unreleased_20/8234.rst new file mode 100644 index 0000000000..750c1438f3 --- /dev/null +++ b/doc/build/changelog/unreleased_20/8234.rst @@ -0,0 +1,16 @@ +.. change:: + :tags: sqlite, usecase + :tickets: 8234 + + Added new parameter to SQLite for reflection methods called + ``sqlite_include_internal=True``; when omitted, local tables that start + with the prefix ``sqlite_``, which per SQLite documentation are noted as + "internal schema" tables such as the ``sqlite_sequence`` table generated to + support "AUTOINCREMENT" columns, will not be included in reflection methods + that return lists of local objects. This prevents issues for example when + using Alembic autogenerate, which previously would consider these + SQLite-generated tables as being remove from the model. + + .. seealso:: + + :ref:`sqlite_include_internal` diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py index 35f30566ab..ce688741f4 100644 --- a/lib/sqlalchemy/dialects/sqlite/base.py +++ b/lib/sqlalchemy/dialects/sqlite/base.py @@ -850,11 +850,42 @@ dialect in conjunction with the :class:`_schema.Table` construct: `SQLite CREATE TABLE options `_ + +.. _sqlite_include_internal: + +Reflecting internal schema tables +---------------------------------- + +Reflection methods that return lists of tables will omit so-called +"SQLite internal schema object" names, which are referred towards by SQLite +as any object name that is prefixed with ``sqlite_``. An example of +such an object is the ``sqlite_sequence`` table that's generated when +the ``AUTOINCREMENT`` column parameter is used. In order to return +these objects, the parameter ``sqlite_include_internal=True`` may be +passed to methods such as :meth:`_schema.MetaData.reflect` or +:meth:`.Inspector.get_table_names`. + +.. versionadded:: 2.0 Added the ``sqlite_include_internal=True`` parameter. + Previously, these tables were not ignored by SQLAlchemy reflection + methods. + +.. note:: + + The ``sqlite_include_internal`` parameter does not refer to the + "system" tables that are present in schemas such as ``sqlite_master``. + +.. seealso:: + + `SQLite Internal Schema Objects `_ - in the SQLite + documentation. + """ # noqa +from __future__ import annotations import datetime import numbers import re +from typing import Optional from .json import JSON from .json import JSONIndexType @@ -2062,27 +2093,53 @@ class SQLiteDialect(default.DefaultDialect): name = table_name return name + def _sqlite_main_query( + self, + table: str, + type_: str, + schema: Optional[str], + sqlite_include_internal: bool, + ): + main = self._format_schema(schema, table) + if not sqlite_include_internal: + filter_table = " AND name NOT LIKE 'sqlite~_%' ESCAPE '~'" + else: + filter_table = "" + query = ( + f"SELECT name FROM {main} " + f"WHERE type='{type_}'{filter_table} " + "ORDER BY name" + ) + return query + @reflection.cache - def get_table_names(self, connection, schema=None, **kw): - main = self._format_schema(schema, "sqlite_master") - s = f"SELECT name FROM {main} WHERE type='table' ORDER BY name" - names = connection.exec_driver_sql(s).scalars().all() + def get_table_names( + self, connection, schema=None, sqlite_include_internal=False, **kw + ): + query = self._sqlite_main_query( + "sqlite_master", "table", schema, sqlite_include_internal + ) + names = connection.exec_driver_sql(query).scalars().all() return names @reflection.cache - def get_temp_table_names(self, connection, **kw): - main = "sqlite_temp_master" - s = f"SELECT name FROM {main} WHERE type='table' ORDER BY name" - names = connection.exec_driver_sql(s).scalars().all() + def get_temp_table_names( + self, connection, sqlite_include_internal=False, **kw + ): + query = self._sqlite_main_query( + "sqlite_temp_master", "table", None, sqlite_include_internal + ) + names = connection.exec_driver_sql(query).scalars().all() return names @reflection.cache - def get_temp_view_names(self, connection, **kw): - s = ( - "SELECT name FROM sqlite_temp_master " - "WHERE type='view' ORDER BY name " + def get_temp_view_names( + self, connection, sqlite_include_internal=False, **kw + ): + query = self._sqlite_main_query( + "sqlite_temp_master", "view", None, sqlite_include_internal ) - names = connection.exec_driver_sql(s).scalars().all() + names = connection.exec_driver_sql(query).scalars().all() return names @reflection.cache @@ -2098,10 +2155,13 @@ class SQLiteDialect(default.DefaultDialect): return "main" @reflection.cache - def get_view_names(self, connection, schema=None, **kw): - main = self._format_schema(schema, "sqlite_master") - s = f"SELECT name FROM {main} WHERE type='view' ORDER BY name" - names = connection.exec_driver_sql(s).scalars().all() + def get_view_names( + self, connection, schema=None, sqlite_include_internal=False, **kw + ): + query = self._sqlite_main_query( + "sqlite_master", "view", schema, sqlite_include_internal + ) + names = connection.exec_driver_sql(query).scalars().all() return names @reflection.cache diff --git a/test/dialect/test_sqlite.py b/test/dialect/test_sqlite.py index aae6d41e9e..286c6bcf8c 100644 --- a/test/dialect/test_sqlite.py +++ b/test/dialect/test_sqlite.py @@ -12,6 +12,7 @@ from sqlalchemy import Column from sqlalchemy import column from sqlalchemy import Computed from sqlalchemy import create_engine +from sqlalchemy import DDL from sqlalchemy import DefaultClause from sqlalchemy import event from sqlalchemy import exc @@ -3382,3 +3383,74 @@ class OnConflictTest(AssertsCompiledSQL, fixtures.TablesTest): conn.scalar(sql.select(bind_targets.c.data)), "new updated data processed", ) + + +class ReflectInternalSchemaTables(fixtures.TablesTest): + __only_on__ = "sqlite" + __backend__ = True + + @classmethod + def define_tables(cls, metadata): + Table( + "sqliteatable", + metadata, + Column("id", Integer, primary_key=True), + Column("other", String(42)), + sqlite_autoincrement=True, + ) + view = "CREATE VIEW sqliteview AS SELECT * FROM sqliteatable" + event.listen(metadata, "after_create", DDL(view)) + event.listen(metadata, "before_drop", DDL("DROP VIEW sqliteview")) + + def test_get_table_names(self, connection): + insp = inspect(connection) + + res = insp.get_table_names(sqlite_include_internal=True) + eq_(res, ["sqlite_sequence", "sqliteatable"]) + res = insp.get_table_names() + eq_(res, ["sqliteatable"]) + + meta = MetaData() + meta.reflect(connection) + eq_(len(meta.tables), 1) + eq_(set(meta.tables), {"sqliteatable"}) + + def test_get_view_names(self, connection): + insp = inspect(connection) + + res = insp.get_view_names(sqlite_include_internal=True) + eq_(res, ["sqliteview"]) + res = insp.get_view_names() + eq_(res, ["sqliteview"]) + + def test_get_temp_table_names(self, connection, metadata): + Table( + "sqlitetemptable", + metadata, + Column("id", Integer, primary_key=True), + Column("other", String(42)), + sqlite_autoincrement=True, + prefixes=["TEMPORARY"], + ).create(connection) + insp = inspect(connection) + + res = insp.get_temp_table_names(sqlite_include_internal=True) + eq_(res, ["sqlite_sequence", "sqlitetemptable"]) + res = insp.get_temp_table_names() + eq_(res, ["sqlitetemptable"]) + + def test_get_temp_view_names(self, connection): + + view = ( + "CREATE TEMPORARY VIEW sqlitetempview AS " + "SELECT * FROM sqliteatable" + ) + connection.exec_driver_sql(view) + insp = inspect(connection) + try: + res = insp.get_temp_view_names(sqlite_include_internal=True) + eq_(res, ["sqlitetempview"]) + res = insp.get_temp_view_names() + eq_(res, ["sqlitetempview"]) + finally: + connection.exec_driver_sql("DROP VIEW sqlitetempview") -- 2.47.2