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

1 files changed, 77 insertions, 17 deletions

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