[Django] #30183: Sqlite instrospection.get_constraints() for unique constraints return wrong count of constraints and doesn't have columns for all items
Django
count of constraints and doesn't have columns for all items
------------------------------------------+------------------------
Reporter: Pavel Tyslacki | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: master
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
------------------------------------------+------------------------
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}
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30183>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
count of constraints and doesn't have columns for all items
Reporter: Pavel Tyslacki | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: master
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* cc: Simon Charette (added)
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/30183#comment:1>
Django
count of constraints and doesn't have columns for all items
Reporter: Pavel Tyslacki | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: master
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by 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`
[https://github.com/django/django/blob/0104b5a41704430aaa1067da2281a86a83c8543a/django/db/backends/sqlite3/introspection.py#L253-L261
to appear before] which is not always the case as you've come to discover.
--
Ticket URL: <https://code.djangoproject.com/ticket/30183#comment:2>
Django
count of constraints and doesn't have columns for all items
Reporter: Pavel Tyslacki | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: master
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/30183#comment:3>
Django
count of constraints and doesn't have columns for all items
Reporter: Pavel Tyslacki | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* component: Migrations => Database layer (models, ORM)
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/30183#comment:4>
Django
count of constraints and doesn't have columns for all items
Reporter: Pavel Tyslacki | Owner: Pavel
| Tyslacki
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Pavel Tyslacki):
* status: new => assigned
* owner: nobody => Pavel Tyslacki
* has_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/30183#comment:5>
Django
count of constraints and doesn't have columns for all items
Reporter: Pavel Tyslacki | Owner: Pavel
| Tyslacki
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tim Graham <timograham@…>):
In [changeset:"4492be348ad6fb24957068e63448142399629d18" 4492be34]:
{{{
#!CommitTicketReference repository=""
revision="4492be348ad6fb24957068e63448142399629d18"
Refs #30183 -- Moved SQLite table constraint parsing to a method.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30183#comment:6>
Django
count of constraints and doesn't have columns for all items
Reporter: Pavel Tyslacki | Owner: Pavel
| Tyslacki
Component: Database layer | Version: master
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham <timograham@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"782d85b6dfa191e67c0f1d572641d8236c79174c" 782d85b]:
{{{
#!CommitTicketReference repository=""
revision="782d85b6dfa191e67c0f1d572641d8236c79174c"
Fixed #30183 -- Added introspection of inline SQLite constraints.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30183#comment:7>
Django
count of constraints and doesn't have columns for all items
Reporter: Pavel Tyslacki | Owner: Pavel
| Tyslacki
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tim Graham <timograham@…>):
In [changeset:"40b0a58f5ff949fba1072627e4ad11ef98aa7f36" 40b0a58]:
{{{
#!CommitTicketReference repository=""
revision="40b0a58f5ff949fba1072627e4ad11ef98aa7f36"
[2.2.x] Fixed #30183 -- Added introspection of inline SQLite constraints.
Backport of 782d85b6dfa191e67c0f1d572641d8236c79174c from master.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30183#comment:9>
Django
count of constraints and doesn't have columns for all items
Reporter: Pavel Tyslacki | Owner: Pavel
| Tyslacki
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tim Graham <timograham@…>):
In [changeset:"d8252025bc85b23e8f9d774f46dc2773750deebf" d8252025]:
{{{
#!CommitTicketReference repository=""
revision="d8252025bc85b23e8f9d774f46dc2773750deebf"
[2.2.x] Refs #30183 -- Moved SQLite table constraint parsing to a method.
Backport of 4492be348ad6fb24957068e63448142399629d18 from master.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30183#comment:8>
Django
count of constraints and doesn't have columns for all items
Reporter: Pavel Tyslacki | Owner: Pavel
| Tyslacki
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"4b6db766ba4b613d317c87f87d1d63865b7424a4" 4b6db766]:
{{{
#!CommitTicketReference repository=""
revision="4b6db766ba4b613d317c87f87d1d63865b7424a4"
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.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30183#comment:10>
Django
count of constraints and doesn't have columns for all items
Reporter: Pavel Tyslacki | Owner: Pavel
| Tyslacki
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"76d293f3e6abdb1955287a483005d26af6f93036" 76d293f3]:
{{{
#!CommitTicketReference repository=""
revision="76d293f3e6abdb1955287a483005d26af6f93036"
[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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30183#comment:11>