From 228c582cdc10b9537bedbe37e1cd5d7c56a5faa4 Mon Sep 17 00:00:00 2001 From: Federico Caselli Date: Fri, 1 May 2020 22:22:51 +0200 Subject: [PATCH] SQLite 3.31 added support for computed column. This change enables their support in SQLAlchemy when targeting SQLite. Fixes: #5297 Change-Id: Ia9f21a49e58fc977e3c669b8176036c95d93b9c8 (cherry picked from commit 40fdcec6dfd6444a85a15e42065ce3a20e82e99f) --- doc/build/changelog/unreleased_13/5297.rst | 6 ++ lib/sqlalchemy/dialects/sqlite/base.py | 81 ++++++++++++++++++---- test/dialect/test_sqlite.py | 29 ++++---- test/requirements.py | 2 +- 4 files changed, 90 insertions(+), 28 deletions(-) create mode 100644 doc/build/changelog/unreleased_13/5297.rst diff --git a/doc/build/changelog/unreleased_13/5297.rst b/doc/build/changelog/unreleased_13/5297.rst new file mode 100644 index 0000000000..1fb1508c5f --- /dev/null +++ b/doc/build/changelog/unreleased_13/5297.rst @@ -0,0 +1,6 @@ +.. change:: + :tags: usecase, sqlite + :tickets: 5297 + + SQLite 3.31 added support for computed column. This change + enables their support in SQLAlchemy when targeting SQLite. diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py index 3e48e85a6f..d19474bff3 100644 --- a/lib/sqlalchemy/dialects/sqlite/base.py +++ b/lib/sqlalchemy/dialects/sqlite/base.py @@ -1077,8 +1077,6 @@ class SQLiteCompiler(compiler.SQLCompiler): class SQLiteDDLCompiler(compiler.DDLCompiler): def get_column_specification(self, column, **kwargs): - if column.computed is not None: - raise exc.CompileError("SQLite does not support computed columns") coltype = self.dialect.type_compiler.process( column.type, type_expression=column @@ -1125,6 +1123,9 @@ class SQLiteDDLCompiler(compiler.DDLCompiler): colspec += " AUTOINCREMENT" + if column.computed is not None: + colspec += " " + self.process(column.computed) + return colspec def visit_primary_key_constraint(self, constraint): @@ -1682,34 +1683,75 @@ class SQLiteDialect(default.DefaultDialect): @reflection.cache def get_columns(self, connection, table_name, schema=None, **kw): + pragma = "table_info" + # computed columns are threaded as hidden, they require table_xinfo + if self.server_version_info >= (3, 31): + pragma = "table_xinfo" info = self._get_table_pragma( - connection, "table_info", table_name, schema=schema + connection, pragma, table_name, schema=schema ) - columns = [] + tablesql = None for row in info: - (name, type_, nullable, default, primary_key) = ( - row[1], - row[2].upper(), - not row[3], - row[4], - row[5], - ) + name = row[1] + type_ = row[2].upper() + nullable = not row[3] + default = row[4] + primary_key = row[5] + hidden = row[6] if pragma == "table_xinfo" else 0 + + # hidden has value 0 for normal columns, 1 for hidden columns, + # 2 for computed virtual columns and 3 for computed stored columns + # https://www.sqlite.org/src/info/069351b85f9a706f60d3e98fbc8aaf40c374356b967c0464aede30ead3d9d18b + if hidden == 1: + continue + + generated = bool(hidden) + persisted = hidden == 3 + + if tablesql is None and generated: + tablesql = self._get_table_sql( + connection, table_name, schema, **kw + ) columns.append( self._get_column_info( - name, type_, nullable, default, primary_key + name, + type_, + nullable, + default, + primary_key, + generated, + persisted, + tablesql, ) ) return columns - def _get_column_info(self, name, type_, nullable, default, primary_key): + def _get_column_info( + self, + name, + type_, + nullable, + default, + primary_key, + generated, + persisted, + tablesql, + ): + + if generated: + # the type of a column "cc INTEGER GENERATED ALWAYS AS (1 + 42)" + # somehow is "INTEGER GENERATED ALWAYS" + type_ = re.sub("generated", "", type_, flags=re.IGNORECASE) + type_ = re.sub("always", "", type_, flags=re.IGNORECASE).strip() + coltype = self._resolve_type_affinity(type_) if default is not None: default = util.text_type(default) - return { + colspec = { "name": name, "type": coltype, "nullable": nullable, @@ -1717,6 +1759,17 @@ class SQLiteDialect(default.DefaultDialect): "autoincrement": "auto", "primary_key": primary_key, } + if generated: + sqltext = "" + if tablesql: + pattern = r"[^,]*\s+AS\s+\(([^,]*)\)\s*(?:virtual|stored)?" + match = re.search( + re.escape(name) + pattern, tablesql, re.IGNORECASE + ) + if match: + sqltext = match.group(1) + colspec["computed"] = {"sqltext": sqltext, "persisted": persisted} + return colspec def _resolve_type_affinity(self, type_): """Return a data type from a reflected column, using affinity tules. diff --git a/test/dialect/test_sqlite.py b/test/dialect/test_sqlite.py index 01a0a54371..a03ad1fdaa 100644 --- a/test/dialect/test_sqlite.py +++ b/test/dialect/test_sqlite.py @@ -586,11 +586,15 @@ class DefaultsTest(fixtures.TestBase, AssertsCompiledSQL): """test non-quoted integer value on older sqlite pragma""" dialect = sqlite.dialect() - info = dialect._get_column_info("foo", "INTEGER", False, 3, False) + info = dialect._get_column_info( + "foo", "INTEGER", False, 3, False, False, False, None + ) eq_(info["default"], "3") -class DialectTest(fixtures.TestBase, AssertsExecutionResults): +class DialectTest( + fixtures.TestBase, AssertsExecutionResults, AssertsCompiledSQL +): __only_on__ = "sqlite" @@ -751,13 +755,13 @@ class DialectTest(fixtures.TestBase, AssertsExecutionResults): eq_(d.create_connect_args(url), expected) @testing.combinations( - ("no_persisted", "ignore"), - ("persisted_none", None), - ("persisted_true", True), - ("persisted_false", False), - id_="ia", + ("no_persisted", "", "ignore"), + ("persisted_none", "", None), + ("persisted_true", " STORED", True), + ("persisted_false", " VIRTUAL", False), + id_="iaa", ) - def test_column_computed(self, persisted): + def test_column_computed(self, text, persisted): m = MetaData() kwargs = {"persisted": persisted} if persisted != "ignore" else {} t = Table( @@ -766,11 +770,10 @@ class DialectTest(fixtures.TestBase, AssertsExecutionResults): Column("x", Integer), Column("y", Integer, Computed("x + 2", **kwargs)), ) - assert_raises_message( - exc.CompileError, - "SQLite does not support computed columns", - schema.CreateTable(t).compile, - dialect=sqlite.dialect(), + self.assert_compile( + schema.CreateTable(t), + "CREATE TABLE t (x INTEGER," + " y INTEGER GENERATED ALWAYS AS (x + 2)%s)" % text, ) diff --git a/test/requirements.py b/test/requirements.py index 4f17853a5a..73d8d0e544 100644 --- a/test/requirements.py +++ b/test/requirements.py @@ -1528,7 +1528,7 @@ class DefaultRequirements(SuiteRequirements): @property def computed_columns(self): - return skip_if(["postgresql < 12", "sqlite", "mysql < 5.7"]) + return skip_if(["postgresql < 12", "sqlite < 3.31", "mysql < 5.7"]) @property def python_profiling_backend(self): -- 2.39.5