SQLite instrospection.get_constraints() for unique constraints return wrong count of constraints and doesn't have columns for all items

[Pavel Tyslacki]

Examples below has next issues:

1. None name of constraint
2. 'columns': [] for some constraints (get by SELECT sql FROM sqlite_master parsing)
3. duplicates of constraints from different places (get by SELECT sql FROM sqlite_master parsing and from idexes)
4. ignoring of some constraints when it created for same columns

This issue blocking fixing #30172 for sqlite, because migrations can use introspection for constraint detection and changing.

cursor.execute("""
    CREATE TABLE "test1" (
        "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
        "name" varchar(255) NOT NULL, 
        "email" varchar(255) NOT NULL
    )
""")
print(connection.introspection.get_constraints(cursor, 'test1'))
# '__primary__': {'columns': ['id'], 'primary_key': True, 'unique': False, 'foreign_key': False, 'check': False, 'index': False}

cursor.execute("""
    CREATE TABLE "test2" (
        "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
        "name" varchar(255) NOT NULL UNIQUE, 
        "email" varchar(255) NOT NULL
    )
""")
print(connection.introspection.get_constraints(cursor, 'test2'))
# None: {'unique': True, 'columns': [], 'primary_key': False, 'foreign_key': False, 'check': False, 'index': False}
# 'sqlite_autoindex_test2_1': {'columns': ['name'], 'primary_key': False, 'unique': True, 'foreign_key': False, 'check': False, 'index': True}
# '__primary__': {'columns': ['id'], 'primary_key': True, 'unique': False, 'foreign_key': False, 'check': False, 'index': False}

cursor.execute("""
    CREATE TABLE "test3" (
        "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
        "name" varchar(255) NOT NULL, 
        "email" varchar(255) NOT NULL, 
        CONSTRAINT "name_uniq" UNIQUE ("name")
    )
""")
print(connection.introspection.get_constraints(cursor, 'test3'))
# 'name_uniq': {'unique': True, 'columns': [], 'primary_key': False, 'foreign_key': False, 'check': False, 'index': False},
# 'sqlite_autoindex_test3_1': {'columns': ['name'], 'primary_key': False, 'unique': True, 'foreign_key': False, 'check': False, 'index': True},
# '__primary__': {'columns': ['id'], 'primary_key': True, 'unique': False, 'foreign_key': False, 'check': False, 'index': False}

cursor.execute("""
    CREATE TABLE "test4" (
        "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
        "name" varchar(255) NOT NULL UNIQUE, 
        "email" varchar(255) NOT NULL, 
        CONSTRAINT "name_uniq" UNIQUE ("name")
    )
""")
print(connection.introspection.get_constraints(cursor, 'test4'))
# None: {'unique': True, 'columns': [], 'primary_key': False, 'foreign_key': False, 'check': False, 'index': False},
# 'name_uniq': {'unique': True, 'columns': [], 'primary_key': False, 'foreign_key': False, 'check': False, 'index': False},
# 'sqlite_autoindex_test4_1': {'columns': ['name'], 'primary_key': False, 'unique': True, 'foreign_key': False, 'check': False, 'index': True},
# '__primary__': {'columns': ['id'], 'primary_key': True, 'unique': False, 'foreign_key': False, 'check': False, 'index': False}

cursor.execute("""
    CREATE TABLE "test5" (
        "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
        "name" varchar(255) NOT NULL UNIQUE, 
        "email" varchar(255) NOT NULL, 
        CONSTRAINT "name_uniq" UNIQUE ("name")
    )
""")
cursor.execute("""
    CREATE UNIQUE INDEX "test5_uniq" ON "test5" ("name")
""")
print(connection.introspection.get_constraints(cursor, 'test5'))
# None: {'unique': True, 'columns': [], 'primary_key': False, 'foreign_key': False, 'check': False, 'index': False},
# 'name_uniq': {'unique': True, 'columns': [], 'primary_key': False, 'foreign_key': False, 'check': False, 'index': False},
# 'test5_uniq': {'columns': ['name'], 'primary_key': False, 'unique': True, 'foreign_key': False, 'check': False, 'index': True},
# 'sqlite_autoindex_test5_1': {'columns': ['name'], 'primary_key': False, 'unique': True, 'foreign_key': False, 'check': False, 'index': True},
# '__primary__': {'columns': ['id'], 'primary_key': True, 'unique': False, 'foreign_key': False, 'check': False, 'index': False}

cursor.execute("""
    CREATE TABLE "test6" (
        "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
        "name" varchar(255) NOT NULL UNIQUE, 
        "email" varchar(255) NOT NULL, 
        CONSTRAINT "name_uniq" UNIQUE ("email")
    )
""")
cursor.execute("""
    CREATE UNIQUE INDEX "test6_uniq" ON "test6" ("name", "email")
""")
print(connection.introspection.get_constraints(cursor, 'test6'))
# None: {'unique': True, 'columns': [], 'primary_key': False, 'foreign_key': False, 'check': False, 'index': False},
# 'name_uniq': {'unique': True, 'columns': [], 'primary_key': False, 'foreign_key': False, 'check': False, 'index': False},
# 'test6_uniq': {'columns': ['name', 'email'], 'primary_key': False, 'unique': True, 'foreign_key': False, 'check': False, 'index': True},
# 'sqlite_autoindex_test6_2': {'columns': ['email'], 'primary_key': False, 'unique': True, 'foreign_key': False, 'check': False, 'index': True},
# 'sqlite_autoindex_test6_1': {'columns': ['name'], 'primary_key': False, 'unique': True, 'foreign_key': False, 'check': False, 'index': True},
# '__primary__': {'columns': ['id'], 'primary_key': True, 'unique': False, 'foreign_key': False, 'check': False, 'index': False}

[Tim Graham]

SQLite introspection was improved in dba4a634ba999bf376caee193b3378bc0b730bd4 but there are still limitations. Simon might be able to advise if any of these issues are fixable or if another approach (similar to how it's often required to rebuild the entire table for schema changes) is required to solve #30172 for SQLite.

[Simon Charette]

Hello Pavel, thanks for tackling #30172.

I'm pretty sure it's possible to adjust the SQLite constraint introspection logic to appropriately detect inline UNIQUE constraint which seems to be the issue here. The current logic assumes CONSTRAINT ​to appear before which is not always the case as you've come to discover.

[Pavel Tyslacki]

Yep, I'm a bit investigating it. For now look like for inline constraints (CHECK and UNIQUE) detection you should use table definition parsing, indexes created via CREATE INDEX can use current logic.

Just describe why only table definition parsing should be used for inline UNIQUE constraint:

• both named and unnamed (UNIQUE in field definition) has different name within index_list and you can match this indexes only (as I see) with fields comparison
• two inline UNIQUE constraints with same fields will be represented as one index in index_list
• there are lack for ASC/DESC ordering detecting
• you cannot delete indexes created as inline constraints via DROP INDEX

I have prototype of table definition parsing so hope I'll can finish fix soon.

[Tim Graham <timograham@…>]

In 4492be34:

Refs #30183 -- Moved SQLite table constraint parsing to a method.

[Tim Graham <timograham@…>]

In 782d85b:

Fixed #30183 -- Added introspection of inline SQLite constraints.

[Tim Graham <timograham@…>]

In d8252025:

[2.2.x] Refs #30183 -- Moved SQLite table constraint parsing to a method.

Backport of 4492be348ad6fb24957068e63448142399629d18 from master.

[Tim Graham <timograham@…>]

In 40b0a58:

[2.2.x] Fixed #30183 -- Added introspection of inline SQLite constraints.

Backport of 782d85b6dfa191e67c0f1d572641d8236c79174c from master.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 4b6db766:

Refs #30183 -- Doc'd dropping support for sqlparse < 0.2.2.

Support for sqlparse < 0.2.2 was broken in
782d85b6dfa191e67c0f1d572641d8236c79174c because is_whitespace property
was added in sqlparse 0.2.2.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 76d293f3:

[3.0.x] Refs #30183 -- Doc'd dropping support for sqlparse < 0.2.2.

Support for sqlparse < 0.2.2 was broken in
782d85b6dfa191e67c0f1d572641d8236c79174c because is_whitespace property
was added in sqlparse 0.2.2.

Backport of 4b6db766ba4b613d317c87f87d1d63865b7424a4 from master

[Mariusz Felisiak <felisiak.mariusz@…>]

In cdad78e6:

[2.2.x] Refs #30183 -- Doc'd dropping support for sqlparse < 0.2.2.

Support for sqlparse < 0.2.2 was broken in
782d85b6dfa191e67c0f1d572641d8236c79174c because is_whitespace property
was added in sqlparse 0.2.2.

Backport of 4b6db766ba4b613d317c87f87d1d63865b7424a4 from master.