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.
* 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>
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>
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>
* component: Migrations => Database layer (models, ORM)
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/30183#comment:4>
* status: new => assigned
* owner: nobody => Pavel Tyslacki
* has_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/30183#comment:5>
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>
* 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>
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>
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>
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>
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>