unit tests using sqlite do not check foreign keys

[dan wendlandt]

for sqllite the foreign keys are not enabled by default.

this means that our db unit tests are not really testing what someone would see in real use.

more info here: http://code.google.com/p/sqlitefktg4sa/

[Juliano Martinez (ncode)]

Dan,

This is a half true bug, it only affects systems running with python2.4/2.5 and have python sqlite module built with sqlite version >= 3.6.19.

http://www.hwaci.com/sw/sqlite/foreignkeys.html

Python on debian, ubuntu and systems using python2.6 have sqlite3 with foreign keys.

squeeze ncode@atomsk:~$ ldd /usr/lib/python2.6/lib-dynload/_sqlite3.so
 linux-vdso.so.1 => (0x00007fff6f7f4000)
 libsqlite3.so.0 => /usr/lib/libsqlite3.so.0 (0x00007fd33fc33000)
 libpthread.so.0 => /lib/libpthread.so.0 (0x00007fd33fa17000)
 libc.so.6 => /lib/libc.so.6 (0x00007fd33f6b4000)
 libdl.so.2 => /lib/libdl.so.2 (0x00007fd33f4b0000)
 /lib64/ld-linux-x86-64.so.2 (0x00007fd3400e3000)

squeeze ncode@atomsk:~$ dpkg -S /usr/lib/libsqlite3.so.0
libsqlite3-0: /usr/lib/libsqlite3.so.0

squeeze ncode@atomsk:~$ dpkg -l libsqlite3-0
Desired=Unknown/Install/Remove/Purge/Hold
| Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
|/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
||/ Name Version Description
+++-=========================-=========================-==================================================================
ii libsqlite3-0 3.7.3-1 SQLite 3 shared library

[Dave Haynes (dave-haynes)]

SQLite3 does not enforce FK constraints by default, even if they have been successfully compiled into the library. An explicit PRAGMA is required at run time.

One solution is to implement the pattern described here: http://stackoverflow.com/a/7831210
A good location for this code is nova/tests/__init__.py

[David Ripton (dripton)]

I'm taking this one.

[Jason (zzs)]

Hi David,

I am fixing the bug #1080887,
https://bugs.launchpad.net/quantum/+bug/1080887
which need foreign key checking enforcement for unit
testing, so can I use you fixing for it?

Thanks in advance!

Best regards,

Jason

[Aaron Rosen (arosen)]

Hi Jason,

Here is a patch that fixes this though it causes most of the unit tests to break because they don't respect foreign key reference using sqlite.

diff --git a/quantum/db/api.py b/quantum/db/api.py
index e10c7d4..0246a95 100644
--- a/quantum/db/api.py
+++ b/quantum/db/api.py
@@ -23,6 +23,7 @@ import time
 import sqlalchemy as sql
 from sqlalchemy import create_engine
 from sqlalchemy.exc import DisconnectionError
+from sqlalchemy.interfaces import PoolListener
 from sqlalchemy.orm import sessionmaker, exc

 from quantum.db import model_base
@@ -56,6 +57,12 @@ class MySQLPingListener(object):
                 raise

+class SqliteForeignKeysListener(PoolListener):
+ """Foreign keys are not enforced in sqlite unless specified."""
+ def connect(self, dbapi_con, con_record):
+ dbapi_con.execute('pragma foreign_keys=ON')
+
+
 def configure_db(options):
     """
     Establish the database, create an engine if needed, and
@@ -75,6 +82,9 @@ def configure_db(options):
         if 'mysql' in connection_dict.drivername:
             engine_args['listeners'] = [MySQLPingListener()]

+ if 'sqlite' in connection_dict.drivername:
+ engine_args['listeners'] = [SqliteForeignKeysListener()]
+
         _ENGINE = create_engine(options['sql_connection'], **engine_args)
         base = options.get('base', BASE)
         if not register_models(base):

[Jason (zzs)]

Hi Aaron,

Thank you very much, it works. :-)

As you mentioned some unit tests cases broke after applied the patch above.

[David Ripton (dripton)]

On 12/03/2012 06:31 PM, Jason Zhang wrote:
> Hi David,
>
> I am fixing the bug #1080887,
> https://bugs.launchpad.net/quantum/+bug/1080887
> which need foreign key checking enforcement for unit
> testing, so can I use you fixing for it?

Of course, but getting all the tests to work is a challenge.

--
David Ripton Red Hat <email address hidden>

[Jason (zzs)]

Hi David,

I think I can take this task if you are OK for this.

Thanks,

Best regards,

Jason

[David Ripton (dripton)]

On 12/05/2012 09:28 PM, Jason Zhang wrote:
> Hi David,
>
> I think I can take this task if you are OK for this.
>
> Thanks,
>
> Best regards,
>
> Jason
>

Sure, go ahead.

--
David Ripton Red Hat <email address hidden>

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/17820

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/17820
Committed: http://github.com/openstack/quantum/commit/babe698caf195be6c0f8d4a61dc967007aaedac7
Submitter: Jenkins
Branch: master

commit babe698caf195be6c0f8d4a61dc967007aaedac7
Author: Jason Zhang <email address hidden>
Date: Mon Dec 10 18:04:18 2012 -0800

    Fixed the unit tests using SQLite do not check foreign keys.

    The foreign key constraints will be enabled for each SQLite
    database connection.

    By default the foreign key constraints are disabled in SQLite,
    so some test cases failed after enabling the foreign key
    constraints for unit tests. This fixings also fixed the failed
    test cases because of the foreign key enforcement.

    Fixes: bug #1021023
    Change-Id: I89f0cbbd75bb685b50dfe6628116fa971c5e78cb