From 4c8469947d79766813bc9326ef16c25ff6882f53 Mon Sep 17 00:00:00 2001 From: Jeff Horemans Date: Tue, 13 Aug 2024 11:22:51 -0400 Subject: [PATCH] Reflect broader range of check constraints for SQLite. Improvements to the regex used by the SQLite dialect to reflect the name and contents of a CHECK constraint. Constraints with newline, tab, or space characters in either or both the constraint text and constraint name are now properly reflected. Pull request courtesy Jeff Horemans. Fixes: #11677 Closes: #11701 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/11701 Pull-request-sha: b2d629f18695ecb08cddeb99d7c2573e1f3d04b2 Change-Id: I2dd06c778e7c130848e418a80004032eb6144e6d --- doc/build/changelog/unreleased_20/11677.rst | 10 +++ lib/sqlalchemy/dialects/sqlite/base.py | 70 ++++++++++++++++++--- test/dialect/test_sqlite.py | 38 +++++++++++ 3 files changed, 110 insertions(+), 8 deletions(-) create mode 100644 doc/build/changelog/unreleased_20/11677.rst diff --git a/doc/build/changelog/unreleased_20/11677.rst b/doc/build/changelog/unreleased_20/11677.rst new file mode 100644 index 0000000000..b1ac39b436 --- /dev/null +++ b/doc/build/changelog/unreleased_20/11677.rst @@ -0,0 +1,10 @@ +.. change:: + :tags: bug, sqlite + :tickets: 11677 + + Improvements to the regex used by the SQLite dialect to reflect the name + and contents of a CHECK constraint. Constraints with newline, tab, or + space characters in either or both the constraint text and constraint name + are now properly reflected. Pull request courtesy Jeff Horemans. + + diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py index 04e84a68d2..a678e10940 100644 --- a/lib/sqlalchemy/dialects/sqlite/base.py +++ b/lib/sqlalchemy/dialects/sqlite/base.py @@ -2624,15 +2624,69 @@ class SQLiteDialect(default.DefaultDialect): connection, table_name, schema=schema, **kw ) - CHECK_PATTERN = r"(?:CONSTRAINT (.+) +)?" r"CHECK *\( *(.+) *\),? *" + # Notes: + # * The pattern currently matches any character for the name of the + # constraint, including newline characters (re.S flag) as long as + # none of the SQLite's table constraints keywords are encountered + # by a negative lookahead. + # This prevents the pattern from matching subsequent constraints + # as part of the name. + # This is only done for those keywords if seperated by spaces, to + # support constraint names that contains them e.g. "check_value". + # + # * Because check constraint definitions can also contain newline + # or tab characters, the pattern matches any character untill either + # the beginning of the next constraint statement using a + # non-capturing and non-consuming group, allowing the next one + # to match, or the end of the table definition + # e.g. newline and closing ')'. + CHECK_PATTERN = r""" + # Non-capturing group for the name part of named check constraints. + # This group is optional as unnamed check constraints can exist. + (?: + # Match beginning of constraint definition seperated by whitespace. + CONSTRAINT\s + + # First capturing group that matches the actual name of the constraint. + # Any characters is allowed, as long as none of the reserved table + # constraint keywords are encountered using a negative lookahead. + ((?:(?!\sPRIMARY\s|\sFOREIGN\sKEY|\sUNIQUE\s|\sCHECK\s).)+) + + # End of optional non-capturing name group seperated by whitespace. + \s)? + + # Match beginning of the check expression with starting parenthesis + # and optional whitespace. + CHECK\s?\( + + # Match actual expression, which can be any character. + (.+?) + + # End parenthesis of the check expression. + \) + + # Non-capturing group that helps denote the end of the check + # expression part. + # This can either be (1) the beginning of the next constraint, + # or (2) the end of the table definition. + (?: + + # (1) Matches end of check constraint with trailing comma, + # optional whitespace (including newline), and the beginning + # of the next constraint (either named or unnamed). + ,[\s\n]*(?=CONSTRAINT|CHECK) + # OR operator, seperating (1) & (2) + | + # (2) Matches end parenthesis of table definition, seperated by + # newline. + \n\) + # End of non-capturing group. + ) + """ cks = [] - # NOTE: we aren't using re.S here because we actually are - # taking advantage of each CHECK constraint being all on one - # line in the table definition in order to delineate. This - # necessarily makes assumptions as to how the CREATE TABLE - # was emitted. - - for match in re.finditer(CHECK_PATTERN, table_data or "", re.I): + for match in re.finditer( + CHECK_PATTERN, table_data or "", re.I | re.S | re.VERBOSE + ): name = match.group(1) if name: diff --git a/test/dialect/test_sqlite.py b/test/dialect/test_sqlite.py index 8dedadbde9..0a5acdec94 100644 --- a/test/dialect/test_sqlite.py +++ b/test/dialect/test_sqlite.py @@ -1816,6 +1816,27 @@ class ConstraintReflectionTest(fixtures.TestBase): Table("q", meta, Column("id", Integer), PrimaryKeyConstraint("id")) + # intentional new line + Table( + "r", + meta, + Column("id", Integer), + Column("value", Integer), + Column("prefix", String), + PrimaryKeyConstraint("id"), + CheckConstraint("id > 0"), + # Constraint definition with newline and tab characters + CheckConstraint( + """((value > 0) AND \n\t(value < 100) AND \n\t + (value != 50))""", + name="ck_r_value_multiline", + ), + # Constraint name with special chars and 'check' in the name + CheckConstraint("value IS NOT NULL", name="^check-r* #\n\t"), + # Constraint definition with special characters. + CheckConstraint("prefix NOT GLOB '*[^-. /#,]*'"), + ) + meta.create_all(conn) # will contain an "autoindex" @@ -1911,6 +1932,7 @@ class ConstraintReflectionTest(fixtures.TestBase): "b", "a1", "a2", + "r", ]: conn.exec_driver_sql("drop table %s" % name) @@ -2456,6 +2478,22 @@ class ConstraintReflectionTest(fixtures.TestBase): {"sqltext": "q > 1 AND q < 6", "name": None}, ], ) + print(inspector.get_check_constraints("r")) + eq_( + inspector.get_check_constraints("r"), + [ + {"sqltext": "value IS NOT NULL", "name": "^check-r* #\n\t"}, + # Triple-quote multi-line definition should have added a + # newline and whitespace: + { + "sqltext": "((value > 0) AND \n\t(value < 100) AND \n\t\n" + " (value != 50))", + "name": "ck_r_value_multiline", + }, + {"sqltext": "id > 0", "name": None}, + {"sqltext": "prefix NOT GLOB '*[^-. /#,]*'", "name": None}, + ], + ) @testing.combinations( ("plain_name", "plain_name"), -- 2.47.2