From 5fb63bc1423e75812a24e809d16731a3282c2a12 Mon Sep 17 00:00:00 2001
From: cheremnov <32135863+cheremnov@users.noreply.github.com>
Date: Thu, 24 Feb 2022 02:22:33 -0500
Subject: Comments on (named) constraints
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Adds support for comments on named constraints, including `ForeignKeyConstraint`, `PrimaryKeyConstraint`, `CheckConstraint`, `UniqueConstraint`, solving the [Issue 5667](https://github.com/sqlalchemy/sqlalchemy/issues/5667).

Supports only PostgreSQL backend.

### Description

Following the example of [Issue 1546](https://github.com/sqlalchemy/sqlalchemy/issues/1546), supports comments on constraints. Specifically, enables comments on _named_ ones — as I get it, PostgreSQL prohibits comments on unnamed constraints.

Enables setting the comments for named constraints like this:
```
Table(
   'example', metadata,
   Column('id', Integer),
   Column('data', sa.String(30)),
   PrimaryKeyConstraint(
       "id", name="id_pk", comment="id_pk comment"
    ),
   CheckConstraint('id < 100', name="cc1", comment="Id value can't exceed 100"),
   UniqueConstraint(['data'], name="uc1", comment="Must have unique data field"),
)
```

Provides the DDL representation for constraint comments and routines to create and drop them. Class `.Inspector` reflects constraint comments via methods like `get_check_constraints` .
### Checklist
<!-- go over following points. check them with an `x` if they do apply, (they turn into clickable checkboxes once the PR is submitted, so no need to do everything at once)

-->

This pull request is:

- [ ] A documentation / typographical error fix
- [ ] A short code fix
- [x] A new feature implementation
	- Solves the issue 5667.
	- The commit message includes `Fixes: 5667`.
	- Includes tests based on comment reflection.

**Have a nice day!**

Fixes: #5667
Closes: #7742
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/7742
Pull-request-sha: 42a5d3c3e9ccf9a9d5397fd007aeab0854f66130

Change-Id: Ia60f578595afdbd6089541c9a00e37997ef78ad3
---
 lib/sqlalchemy/dialects/sqlite/base.py | 10 +++++-----
 1 file changed, 5 insertions(+), 5 deletions(-)

(limited to 'lib/sqlalchemy/dialects/sqlite')

diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index 22f003e38..35f30566a 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -2535,7 +2535,7 @@ class SQLiteDialect(default.DefaultDialect):
         )
 
         CHECK_PATTERN = r"(?:CONSTRAINT (.+) +)?" r"CHECK *\( *(.+) *\),? *"
-        check_constraints = []
+        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
@@ -2548,10 +2548,10 @@ class SQLiteDialect(default.DefaultDialect):
             if name:
                 name = re.sub(r'^"|"$', "", name)
 
-            check_constraints.append({"sqltext": match.group(2), "name": name})
-
-        if check_constraints:
-            return check_constraints
+            cks.append({"sqltext": match.group(2), "name": name})
+        cks.sort(key=lambda d: d["name"] or "~")  # sort None as last
+        if cks:
+            return cks
         else:
             return ReflectionDefaults.check_constraints()
 
-- 
cgit v1.2.1