[Django] #30183: Sqlite instrospection.get_constraints() for unique constraints return wrong count of constraints and doesn't have columns for all items

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

[Django] #30183: Sqlite instrospection.get_constraints() for unique constraints return wrong count of constraints and doesn't have columns for all items

Django
#30183: Sqlite instrospection.get_constraints() for unique constraints return wrong
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.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/048.2681bc1befaf943f36e9e56fe705c908%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30183: Sqlite instrospection.get_constraints() for unique constraints return wrong count of constraints and doesn't have columns for all items

Django
#30183: Sqlite instrospection.get_constraints() for unique constraints return wrong
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          |               Resolution:
     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 <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/063.a1abf3536badbd35db42ec56a057638f%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30183: Sqlite instrospection.get_constraints() for unique constraints return wrong count of constraints and doesn't have columns for all items

Django
In reply to this post by Django
#30183: Sqlite instrospection.get_constraints() for unique constraints return wrong
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          |               Resolution:
     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 <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/063.8e8e04c19b8566480c5fd639b5561648%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30183: Sqlite instrospection.get_constraints() for unique constraints return wrong count of constraints and doesn't have columns for all items

Django
In reply to this post by Django
#30183: Sqlite instrospection.get_constraints() for unique constraints return wrong
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          |               Resolution:
     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 <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/063.ebda5c9428feda43c30986517645a3ad%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30183: SQLite instrospection.get_constraints() for unique constraints return wrong count of constraints and doesn't have columns for all items (was: Sqlite instrospection.get_constraints() for unique constraints return wrong count of constraints and doesn't have columns for all items)

Django
In reply to this post by Django
#30183: SQLite instrospection.get_constraints() for unique constraints return wrong
count of constraints and doesn't have columns for all items
-------------------------------------+-------------------------------------
     Reporter:  Pavel Tyslacki       |                    Owner:  nobody
         Type:  Bug                  |                   Status:  new
    Component:  Database layer       |                  Version:  master
  (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 <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/063.509fd66549428f9d503826ec09a4c4ee%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30183: SQLite instrospection.get_constraints() for unique constraints return wrong count of constraints and doesn't have columns for all items

Django
In reply to this post by Django
#30183: SQLite instrospection.get_constraints() for unique constraints return wrong
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
    Has patch:  1                    |      Needs documentation:  0
  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 <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/063.c5e66913279a0e4e8aa389f2f40e61d2%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30183: SQLite instrospection.get_constraints() for unique constraints return wrong count of constraints and doesn't have columns for all items

Django
In reply to this post by Django
#30183: SQLite instrospection.get_constraints() for unique constraints return wrong
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
    Has patch:  1                    |      Needs documentation:  0
  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 <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/063.d3c8c68cd1be3f7aad74a24e3b98449b%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30183: SQLite instrospection.get_constraints() for unique constraints return wrong count of constraints and doesn't have columns for all items

Django
In reply to this post by Django
#30183: SQLite instrospection.get_constraints() for unique constraints return wrong
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
    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 <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/063.bd989b6c4bb46b5232fb4243b5f222d3%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30183: SQLite instrospection.get_constraints() for unique constraints return wrong count of constraints and doesn't have columns for all items

Django
In reply to this post by Django
#30183: SQLite instrospection.get_constraints() for unique constraints return wrong
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
    Has patch:  1                    |      Needs documentation:  0
  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 <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/063.71c922150f455d9678f3e30a2275296d%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #30183: SQLite instrospection.get_constraints() for unique constraints return wrong count of constraints and doesn't have columns for all items

Django
In reply to this post by Django
#30183: SQLite instrospection.get_constraints() for unique constraints return wrong
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
    Has patch:  1                    |      Needs documentation:  0
  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 <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/063.5d9dbda05df4cef7e58f33cbc1a60a7f%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.