Opened 6 years ago

Closed 6 years ago

Last modified 4 years ago

#30183 closed Bug (fixed)

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

Reported by: Pavel Tyslacki Owned by: Pavel Tyslacki
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords:
Cc: Simon Charette Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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}

Change History (12)

comment:1 by Tim Graham, 6 years ago

Cc: Simon Charette added

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.

comment:2 by Simon Charette, 6 years ago

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.

comment:3 by Pavel Tyslacki, 6 years ago

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.

comment:4 by Tim Graham, 6 years ago

Component: MigrationsDatabase layer (models, ORM)
Summary: Sqlite instrospection.get_constraints() for unique constraints return wrong count of constraints and doesn't have columns for all itemsSQLite instrospection.get_constraints() for unique constraints return wrong count of constraints and doesn't have columns for all items
Triage Stage: UnreviewedAccepted

comment:5 by Pavel Tyslacki, 6 years ago

Has patch: set
Owner: changed from nobody to Pavel Tyslacki
Status: newassigned

comment:6 by Tim Graham <timograham@…>, 6 years ago

In 4492be34:

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

comment:7 by Tim Graham <timograham@…>, 6 years ago

Resolution: fixed
Status: assignedclosed

In 782d85b:

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

comment:8 by Tim Graham <timograham@…>, 6 years ago

In d8252025:

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

Backport of 4492be348ad6fb24957068e63448142399629d18 from master.

comment:9 by Tim Graham <timograham@…>, 6 years ago

In 40b0a58:

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

Backport of 782d85b6dfa191e67c0f1d572641d8236c79174c from master.

comment:10 by Mariusz Felisiak <felisiak.mariusz@…>, 5 years ago

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.

comment:11 by Mariusz Felisiak <felisiak.mariusz@…>, 5 years ago

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

comment:12 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

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.

Note: See TracTickets for help on using tickets.
Back to Top