Skip to content

Commit 6287e1d

Browse files
zzzeekGerrit Code Review
zzzeek
authored and
Gerrit Code Review
committed
Merge "SQLite 3.31 added support for computed column."
2 parents fa70758 + 87949de commit 6287e1d

4 files changed

Lines changed: 90 additions & 28 deletions

File tree

‎doc/build/changelog/unreleased_13/5297.rst‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,6 @@
1+
.. change::
2+
:tags: usecase, sqlite
3+
:tickets: 5297
4+
5+
SQLite 3.31 added support for computed column. This change
6+
enables their support in SQLAlchemy when targeting SQLite.

‎lib/sqlalchemy/dialects/sqlite/base.py‎

Lines changed: 67 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -1076,8 +1076,6 @@ def visit_empty_set_expr(self, element_types):
10761076

10771077
class SQLiteDDLCompiler(compiler.DDLCompiler):
10781078
def get_column_specification(self, column, **kwargs):
1079-
if column.computed is not None:
1080-
raise exc.CompileError("SQLite does not support computed columns")
10811079

10821080
coltype = self.dialect.type_compiler.process(
10831081
column.type, type_expression=column
@@ -1124,6 +1122,9 @@ def get_column_specification(self, column, **kwargs):
11241122

11251123
colspec += " AUTOINCREMENT"
11261124

1125+
if column.computed is not None:
1126+
colspec += " " + self.process(column.computed)
1127+
11271128
return colspec
11281129

11291130
def visit_primary_key_constraint(self, constraint):
@@ -1690,41 +1691,93 @@ def get_view_definition(self, connection, view_name, schema=None, **kw):
16901691

16911692
@reflection.cache
16921693
def get_columns(self, connection, table_name, schema=None, **kw):
1694+
pragma = "table_info"
1695+
# computed columns are threaded as hidden, they require table_xinfo
1696+
if self.server_version_info >= (3, 31):
1697+
pragma = "table_xinfo"
16931698
info = self._get_table_pragma(
1694-
connection, "table_info", table_name, schema=schema
1699+
connection, pragma, table_name, schema=schema
16951700
)
1696-
16971701
columns = []
1702+
tablesql = None
16981703
for row in info:
1699-
(name, type_, nullable, default, primary_key) = (
1700-
row[1],
1701-
row[2].upper(),
1702-
not row[3],
1703-
row[4],
1704-
row[5],
1705-
)
1704+
name = row[1]
1705+
type_ = row[2].upper()
1706+
nullable = not row[3]
1707+
default = row[4]
1708+
primary_key = row[5]
1709+
hidden = row[6] if pragma == "table_xinfo" else 0
1710+
1711+
# hidden has value 0 for normal columns, 1 for hidden columns,
1712+
# 2 for computed virtual columns and 3 for computed stored columns
1713+
# https://www.sqlite.org/src/info/069351b85f9a706f60d3e98fbc8aaf40c374356b967c0464aede30ead3d9d18b
1714+
if hidden == 1:
1715+
continue
1716+
1717+
generated = bool(hidden)
1718+
persisted = hidden == 3
1719+
1720+
if tablesql is None and generated:
1721+
tablesql = self._get_table_sql(
1722+
connection, table_name, schema, **kw
1723+
)
17061724

17071725
columns.append(
17081726
self._get_column_info(
1709-
name, type_, nullable, default, primary_key
1727+
name,
1728+
type_,
1729+
nullable,
1730+
default,
1731+
primary_key,
1732+
generated,
1733+
persisted,
1734+
tablesql,
17101735
)
17111736
)
17121737
return columns
17131738

1714-
def _get_column_info(self, name, type_, nullable, default, primary_key):
1739+
def _get_column_info(
1740+
self,
1741+
name,
1742+
type_,
1743+
nullable,
1744+
default,
1745+
primary_key,
1746+
generated,
1747+
persisted,
1748+
tablesql,
1749+
):
1750+
1751+
if generated:
1752+
# the type of a column "cc INTEGER GENERATED ALWAYS AS (1 + 42)"
1753+
# somehow is "INTEGER GENERATED ALWAYS"
1754+
type_ = re.sub("generated", "", type_, flags=re.IGNORECASE)
1755+
type_ = re.sub("always", "", type_, flags=re.IGNORECASE).strip()
1756+
17151757
coltype = self._resolve_type_affinity(type_)
17161758

17171759
if default is not None:
17181760
default = util.text_type(default)
17191761

1720-
return {
1762+
colspec = {
17211763
"name": name,
17221764
"type": coltype,
17231765
"nullable": nullable,
17241766
"default": default,
17251767
"autoincrement": "auto",
17261768
"primary_key": primary_key,
17271769
}
1770+
if generated:
1771+
sqltext = ""
1772+
if tablesql:
1773+
pattern = r"[^,]*\s+AS\s+\(([^,]*)\)\s*(?:virtual|stored)?"
1774+
match = re.search(
1775+
re.escape(name) + pattern, tablesql, re.IGNORECASE
1776+
)
1777+
if match:
1778+
sqltext = match.group(1)
1779+
colspec["computed"] = {"sqltext": sqltext, "persisted": persisted}
1780+
return colspec
17281781

17291782
def _resolve_type_affinity(self, type_):
17301783
"""Return a data type from a reflected column, using affinity tules.

‎test/dialect/test_sqlite.py‎

Lines changed: 16 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -600,11 +600,15 @@ def test_old_style_default(self):
600600
"""test non-quoted integer value on older sqlite pragma"""
601601

602602
dialect = sqlite.dialect()
603-
info = dialect._get_column_info("foo", "INTEGER", False, 3, False)
603+
info = dialect._get_column_info(
604+
"foo", "INTEGER", False, 3, False, False, False, None
605+
)
604606
eq_(info["default"], "3")
605607

606608

607-
class DialectTest(fixtures.TestBase, AssertsExecutionResults):
609+
class DialectTest(
610+
fixtures.TestBase, AssertsExecutionResults, AssertsCompiledSQL
611+
):
608612

609613
__only_on__ = "sqlite"
610614

@@ -779,13 +783,13 @@ def test_connect_args(self, url, expected):
779783
eq_(d.create_connect_args(url), expected)
780784

781785
@testing.combinations(
782-
("no_persisted", "ignore"),
783-
("persisted_none", None),
784-
("persisted_true", True),
785-
("persisted_false", False),
786-
id_="ia",
786+
("no_persisted", "", "ignore"),
787+
("persisted_none", "", None),
788+
("persisted_true", " STORED", True),
789+
("persisted_false", " VIRTUAL", False),
790+
id_="iaa",
787791
)
788-
def test_column_computed(self, persisted):
792+
def test_column_computed(self, text, persisted):
789793
m = MetaData()
790794
kwargs = {"persisted": persisted} if persisted != "ignore" else {}
791795
t = Table(
@@ -794,11 +798,10 @@ def test_column_computed(self, persisted):
794798
Column("x", Integer),
795799
Column("y", Integer, Computed("x + 2", **kwargs)),
796800
)
797-
assert_raises_message(
798-
exc.CompileError,
799-
"SQLite does not support computed columns",
800-
schema.CreateTable(t).compile,
801-
dialect=sqlite.dialect(),
801+
self.assert_compile(
802+
schema.CreateTable(t),
803+
"CREATE TABLE t (x INTEGER,"
804+
" y INTEGER GENERATED ALWAYS AS (x + 2)%s)" % text,
802805
)
803806

804807

‎test/requirements.py‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1600,7 +1600,7 @@ def oracle5x(self):
16001600

16011601
@property
16021602
def computed_columns(self):
1603-
return skip_if(["postgresql < 12", "sqlite", "mysql < 5.7"])
1603+
return skip_if(["postgresql < 12", "sqlite < 3.31", "mysql < 5.7"])
16041604

16051605
@property
16061606
def python_profiling_backend(self):

0 commit comments

Comments
 (0)