From 8071c21b5cb5009b45e8449dcb37b7c3786445e0 Mon Sep 17 00:00:00 2001 From: John A Stevenson Date: Mon, 26 Aug 2024 11:05:38 -0400 Subject: [PATCH] Update SQLite UNIQUE inline constraint parsing to handle tabs Improvements to the regex used by the SQLite dialect to reflect the name and contents of a UNIQUE constraint that is defined inline within a column definition inside of a SQLite CREATE TABLE statement, accommodating for tab characters present within the column / constraint line. Pull request courtesy John A Stevenson. Fixes: #11746 Closes: #11759 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/11759 Pull-request-sha: 5752491154ad19e29abec8d69fa4076d996d964e Change-Id: I048a90c992bfaf6857c1150f50bf3c6cc5697095 --- doc/build/changelog/unreleased_20/11746.rst | 12 +++++++++ lib/sqlalchemy/dialects/sqlite/base.py | 4 +-- test/dialect/test_sqlite.py | 27 ++++++++++++++++----- 3 files changed, 35 insertions(+), 8 deletions(-) create mode 100644 doc/build/changelog/unreleased_20/11746.rst diff --git a/doc/build/changelog/unreleased_20/11746.rst b/doc/build/changelog/unreleased_20/11746.rst new file mode 100644 index 0000000000..36dc1a7393 --- /dev/null +++ b/doc/build/changelog/unreleased_20/11746.rst @@ -0,0 +1,12 @@ +.. change:: + :tags: bug, sqlite + :tickets: 11746 + + Improvements to the regex used by the SQLite dialect to reflect the name + and contents of a UNIQUE constraint that is defined inline within a column + definition inside of a SQLite CREATE TABLE statement, accommodating for tab + characters present within the column / constraint line. Pull request + courtesy John A Stevenson. + + + diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py index a678e10940..cf8f16966b 100644 --- a/lib/sqlalchemy/dialects/sqlite/base.py +++ b/lib/sqlalchemy/dialects/sqlite/base.py @@ -2588,8 +2588,8 @@ class SQLiteDialect(default.DefaultDialect): return UNIQUE_PATTERN = r'(?:CONSTRAINT "?(.+?)"? +)?UNIQUE *\((.+?)\)' INLINE_UNIQUE_PATTERN = ( - r'(?:(".+?")|(?:[\[`])?([a-z0-9_]+)(?:[\]`])?) ' - r"+[a-z0-9_ ]+? +UNIQUE" + r'(?:(".+?")|(?:[\[`])?([a-z0-9_]+)(?:[\]`])?)[\t ]' + r"+[a-z0-9_ ]+?[\t ]+UNIQUE" ) for match in re.finditer(UNIQUE_PATTERN, table_data, re.I): diff --git a/test/dialect/test_sqlite.py b/test/dialect/test_sqlite.py index b137c0579f..8afa800530 100644 --- a/test/dialect/test_sqlite.py +++ b/test/dialect/test_sqlite.py @@ -2503,17 +2503,27 @@ class ConstraintReflectionTest(fixtures.TestBase): argnames="colname,expected", ) @testing.combinations( - "uq", "uq_inline", "pk", "ix", argnames="constraint_type" + "uq", + "uq_inline", + "uq_inline_tab_before", # tab before column params + "uq_inline_tab_within", # tab within column params + "pk", + "ix", + argnames="constraint_type", ) def test_constraint_cols( self, colname, expected, constraint_type, connection, metadata ): - if constraint_type == "uq_inline": + if constraint_type.startswith("uq_inline"): + inline_create_sql = { + "uq_inline": "CREATE TABLE t (%s INTEGER UNIQUE)", + "uq_inline_tab_before": "CREATE TABLE t (%s\tINTEGER UNIQUE)", + "uq_inline_tab_within": "CREATE TABLE t (%s INTEGER\tUNIQUE)", + } + t = Table("t", metadata, Column(colname, Integer)) connection.exec_driver_sql( - """ - CREATE TABLE t (%s INTEGER UNIQUE) - """ + inline_create_sql[constraint_type] % connection.dialect.identifier_preparer.quote(colname) ) else: @@ -2531,7 +2541,12 @@ class ConstraintReflectionTest(fixtures.TestBase): t.create(connection) - if constraint_type in ("uq", "uq_inline"): + if constraint_type in ( + "uq", + "uq_inline", + "uq_inline_tab_before", + "uq_inline_tab_within", + ): const = inspect(connection).get_unique_constraints("t")[0] eq_(const["column_names"], [expected]) elif constraint_type == "pk": -- 2.47.2