sqlite inspection with get_unique_constraints doesn't pick up (all) UNIQUE constraints

[Jon Nelson]

In sqlite you may optionally define a unique constraint without a name (and without CONSTRAINT):

CREATE TABLE foo (a int, b int, UNIQUE (a,b))

As of 0.9.8, the as-supplied regex which does the constraint parsing will miss these. I'm using this:

'(?!CONSTRAINT \w+)? UNIQUE \(([^\)]+)\)'

and some local code modifications, and it seems to work fine.

Michael Bayer responded via email with:

sqlite doesn’t supply any system of getting the unique constraints for a table, nor does it give an implicit name to any of them, and thus doesn’t match what all other relational databases do in this regard. We should probably return these with name=None, feel free to open a ticket, but this would be 1.0 only. In the meantime, if inspection of SQLite constraints are needed you should give them names.

[Mike Bayer]

cool thanks, I thought I already created this but I was mistaken!

[Mike Bayer]

see the PR at https://bitbucket.org/zzzeek/sqlalchemy/issues?status=new&status=open&milestone=1.0

[Mike Bayer]

with the new alembic features coming out, this is now critical.

[Mike Bayer]

see also #3261.

[Mike Bayer]

• rework sqlite FK and unique constraint system to combine both PRAGMA and regexp parsing of SQL in order to form a complete picture of constraints + their names. fixes #3244 fixes #3261
• factor various PRAGMA work to be centralized into one call

→ <<cset 468db416dbf2>>