10

In my django project, I use mysql db for production, and sqlite for tests.

Problem is, some of my code rely on model integrity checking. It works well with mysql, but integrity errors are not thrown when the same code is executed in tests.

I know that foreign keys checking must be activated in sqlite :

PRAGMA foreign_keys = 1;

However, I don't know where is the best way to do this activation (same question here).

Moreover, the following code won't work :

def test_method(self):
    from django.db import connection
    cursor = connection.cursor()
    cursor.execute('PRAGMA foreign_keys = ON')
    c = cursor.execute('PRAGMA foreign_keys')
    print c.fetchone()
    >>> (0,)

Any ideas?

Community
  • 1
  • 1
Thibault J
  • 4,336
  • 33
  • 44
  • 4
    Why aren't you using the same DB for the tests that is used in production ? Isn't the aim of the test environnement to reproduce the production environnement ?* – Clement Herreman Jul 19 '11 at 13:33
  • Testing a single app, with `time python manage.py test products`. Mysql : 0m58.232s. Sqlite : 0m5.153s. – Thibault J Jul 19 '11 at 14:30
  • Not a single beginning of a solution here but looks like a lot of discussion (and patches) are going on on this ticket about the kind of issue you're raising here https://code.djangoproject.com/ticket/11665 – NiKo Jul 19 '11 at 15:16
  • 1
    @Thibault J: I knew that would be the reason, however in testing, isn't it more important that tests fit to real situation ? I'm currently in the same situation, different DBMS between test and prod, and I had some serious WTF, even if I'm using an ORM. – Clement Herreman Jul 19 '11 at 15:21
  • 1
    @Clement That is very important indeed. But I'm using TDD, and I cannot wait 60s to run a single test every line of code. On my continuous integration plateforme, tests are run under mysql. – Thibault J Jul 19 '11 at 15:57

2 Answers2

18

So, if finally found the correct answer. All I had to do was to add this code in the __init__.py file in one of my installed app:

from django.db.backends.signals import connection_created


def activate_foreign_keys(sender, connection, **kwargs):
    """Enable integrity constraint with sqlite."""
    if connection.vendor == 'sqlite':
        cursor = connection.cursor()
        cursor.execute('PRAGMA foreign_keys = ON;')

connection_created.connect(activate_foreign_keys)
Thibault J
  • 4,336
  • 33
  • 44
  • 1
    This doesn't seem to work on the [initial data](https://docs.djangoproject.com/en/dev/howto/initial-data/) that's added during syncdb. – user240515 Dec 02 '11 at 22:45
  • Be sure to remove old migration files which does not create foreign keys, otherwise foreign keys will not be created. I was trapped by this for a while. – Fish Monitor Nov 07 '14 at 09:05
  • 1
    Although I removed my DB file, removed all the previous migrations, and created new migrations, this made no difference for me. The tables were not created with "on delete cascade" as expected. I wonder why that's not happening. – Mr. Lance E Sloan Mar 11 '16 at 17:51
2

You could use django signals, listening to post_syncdb. 

from django.db.models.signals import post_syncdb

def set_pragma_on(sender, **kwargs):
    "your code here"

post_syncdb.connect(set_pragma_on)

This ensures that whenever syncdb is run (syncdb is run, when creating the test database), that your SQLite database has set 'pragma' to 'on'. You should check which database you are using in the above method 'set_pragma_on'.

jazz
  • 2,371
  • 19
  • 23
  • Your answer is not perfectly correct, but it guided me toward the correct solution (see my answer on same page). Muchos gracias. – Thibault J Jul 27 '11 at 10:56
  • This won't have the expected effect: the `post_syncdb` command only runs after `syncdb`, not for any other connections (and the pragma does not persist beyond the current connection). – Pi Delport Jul 29 '12 at 15:57