diff options
| -rw-r--r-- | doc/build/changelog/unreleased_20/8234.rst | 16 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/sqlite/base.py | 94 | ||||
| -rw-r--r-- | test/dialect/test_sqlite.py | 72 |
3 files changed, 165 insertions, 17 deletions
diff --git a/doc/build/changelog/unreleased_20/8234.rst b/doc/build/changelog/unreleased_20/8234.rst new file mode 100644 index 000000000..750c1438f --- /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 35f30566a..ce688741f 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 <https://www.sqlite.org/lang_createtable.html>`_ + +.. _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 <https://www.sqlite.org/fileformat2.html#intschema>`_ - 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 aae6d41e9..286c6bcf8 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") |