#29928: TestCase doesn't check for foreign key constraints when using sqlite
-------------------------------------+-------------------------------------
Reporter: Michel Samia | Owner: nobody
Type: Bug | Status: new
Component: Testing framework | Version: 1.11
Severity: Normal | Resolution:
Keywords: sqlite db foreign | Triage Stage:
key TestCase | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Michel Samia:
Old description:
> Hi,
>
> When I create some stupid insertion, foreign keys are not checked in test
> when using default sqlite engine. It looks like a regression to
> https://code.djangoproject.com/ticket/11665
> sqlite3 driver doesn't enforce foreign key constraints until commit is
> called - impossible to use them in TestCase
>
> In the first test method (low level) I ensure that sqlite is able to
> catch such foreign key violations. In the second (high level) I prove
> that django effectively disables this check in TestCase.
>
> models.py:
> {{{#!python
> class Person(models.Model):
> name = models.CharField(max_length=20)
> mom = models.ForeignKey('Person', on_delete=models.CASCADE,
> null=True)
> }}}
>
> tests.py:
> {{{#!python
> import sqlite3
>
> from django.test import TestCase
> from .models import Person
>
> # Create your tests here.
> class AppTests(TestCase):
> def test_sqlite_constraints_low_level(self):
> conn = sqlite3.connect(':memory:')
> c = conn.cursor()
>
> # Create table
> c.execute('''CREATE TABLE contacts (
> id INTEGER PRIMARY KEY,
> name TEXT NOT NULL,
> mom INTEGER,
> FOREIGN KEY(mom) REFERENCES contacts(id)
> )
> ''')
>
> c.execute('PRAGMA foreign_keys = ON')
>
> c.execute("insert into contacts(id, name, mom) values(1, 'Marge',
> null)")
> c.execute("insert into contacts(id, name, mom) values(2, 'Bart',
> 1)")
>
> with self.assertRaises(sqlite3.IntegrityError):
> c.execute("insert into contacts(id, name, mom) values(3,
> 'devil', 100)")
>
> conn.commit()
> conn.close()
>
> def test_constraints_high_level(self):
> """
> this should fail, but doesn't because of deferred constraints
> checking:
> https://github.com/django/django/blame/803840abf7dcb6ac190f021a971f1e3dc8f6792a/django/db/backends/sqlite3/schema.py#L16
>
> In the related issue Simon Charette explicitly requests the
> defered checking
> https://code.djangoproject.com/ticket/14204#comment:19
>
> actually the deferred behavior is needed by loading fixtures with
> incorrect order of inserts or with object pointing to itself
>
> However, in test is should not be check at the end of the test as
> discussed in https://code.djangoproject.com/ticket/11665
>
> Related stack overflow question
> https://stackoverflow.com/questions/42238857/how-can-i-enable-foreign-
> key-checks-in-pytest-using-sqllite/53194777#53194777
> """
> marge = Person.objects.create(name='Marge')
> Person.objects.create(name='Bart', mom=marge)
> ids = list(Person.objects.values_list('id',
> flat=True).order_by('id'))
> biggest_id = ids[-1]
>
> with self.assertRaises(sqlite3.IntegrityError):
> Person.objects.create(name='devil', mom_id=biggest_id + 1)
> }}}
New description:
Hi,
When I create some stupid insertion, foreign keys are not checked in test
when using default sqlite engine. It looks like a regression to
https://code.djangoproject.com/ticket/11665
sqlite3 driver doesn't enforce foreign key constraints until commit is
called - impossible to use them in TestCase
In the first test method (low level) I ensure that sqlite is able to catch
such foreign key violations. In the second (high level) I prove that
django effectively disables this check in TestCase.
models.py:
{{{#!python
class Person(models.Model):
name = models.CharField(max_length=20)
mom = models.ForeignKey('Person', on_delete=models.CASCADE, null=True)
}}}
tests.py:
{{{#!python
import sqlite3
from django.test import TestCase
from .models import Person
# Create your tests here.
class AppTests(TestCase):
def test_sqlite_constraints_low_level(self):
conn = sqlite3.connect(':memory:')
c = conn.cursor()
# Create table
c.execute('''CREATE TABLE contacts (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
mom INTEGER,
FOREIGN KEY(mom) REFERENCES contacts(id)
)
''')
c.execute('PRAGMA foreign_keys = ON')
c.execute("insert into contacts(id, name, mom) values(1, 'Marge',
null)")
c.execute("insert into contacts(id, name, mom) values(2, 'Bart',
1)")
with self.assertRaises(sqlite3.IntegrityError):
c.execute("insert into contacts(id, name, mom) values(3,
'devil', 100)")
conn.commit()
conn.close()
def test_constraints_high_level(self):
"""
this should fail, but doesn't because of deferred constraints
checking:
https://github.com/django/django/blame/803840abf7dcb6ac190f021a971f1e3dc8f6792a/django/db/backends/sqlite3/schema.py#L16
In the related issue Simon Charette explicitly requests the
defered checking
https://code.djangoproject.com/ticket/14204#comment:19
actually the deferred behavior is needed by loading fixtures with
incorrect order of inserts or with object pointing to itself
However, in test is should not be check at the end of the test as
discussed in https://code.djangoproject.com/ticket/11665
Related stack overflow question
https://stackoverflow.com/questions/42238857/how-can-i-enable-foreign-key-
checks-in-pytest-using-sqllite/53194777#53194777
"""
marge = Person.objects.create(name='Marge')
Person.objects.create(name='Bart', mom=marge)
ids = list(Person.objects.values_list('id',
flat=True).order_by('id'))
biggest_id = ids[-1]
with self.assertRaises(sqlite3.IntegrityError):
Person.objects.create(name='devil', mom_id=biggest_id + 100)
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/29928#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 [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/068.323ff07a1497dde556d261bdc280828c%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
