Fixed #3615: Added support for loading fixtures with forward references on database backends (such as MySQL/InnoDB) that do not support deferred constraint checking. Many thanks to jsdalton for coming up with a clever solution to this long-standing issue, and to jacob, ramiro, graham_king, and russellm for review/testing. (Apologies if I missed anyone else who helped here.)

git-svn-id: http://code.djangoproject.com/svn/django/trunk@16590 bcc190cf-cafb-0310-a4f2-bffc1f526a37

2 files changed, 78 insertions, 0 deletions

diff --git a/django/db/backends/sqlite3/base.py b/django/db/backends/sqlite3/base.py
index 79c5eded34..f922d38638 100644
--- a/django/db/backends/sqlite3/base.py
+++ b/django/db/backends/sqlite3/base.py
@@ -206,6 +206,40 @@ class DatabaseWrapper(BaseDatabaseWrapper):
             connection_created.send(sender=self.__class__, connection=self)
         return self.connection.cursor(factory=SQLiteCursorWrapper)
 
+    def check_constraints(self, table_names=None):
+        """
+        Checks each table name in table-names for rows with invalid foreign key references. This method is
+        intended to be used in conjunction with `disable_constraint_checking()` and `enable_constraint_checking()`, to
+        determine if rows with invalid references were entered while constraint checks were off.
+
+        Raises an IntegrityError on the first invalid foreign key reference encountered (if any) and provides
+        detailed information about the invalid reference in the error message.
+
+        Backends can override this method if they can more directly apply constraint checking (e.g. via "SET CONSTRAINTS
+        ALL IMMEDIATE")
+        """
+        cursor = self.cursor()
+        if table_names is None:
+            table_names = self.introspection.get_table_list(cursor)
+        for table_name in table_names:
+            primary_key_column_name = self.introspection.get_primary_key_column(cursor, table_name)
+            if not primary_key_column_name:
+                continue
+            key_columns = self.introspection.get_key_columns(cursor, table_name)
+            for column_name, referenced_table_name, referenced_column_name in key_columns:
+                cursor.execute("""
+                    SELECT REFERRING.`%s`, REFERRING.`%s` FROM `%s` as REFERRING
+                    LEFT JOIN `%s` as REFERRED
+                    ON (REFERRING.`%s` = REFERRED.`%s`)
+                    WHERE REFERRING.`%s` IS NOT NULL AND REFERRED.`%s` IS NULL"""
+                    % (primary_key_column_name, column_name, table_name, referenced_table_name,
+                    column_name, referenced_column_name, column_name, referenced_column_name))
+                for bad_row in cursor.fetchall():
+                    raise utils.IntegrityError("The row in table '%s' with primary key '%s' has an invalid "
+                        "foreign key: %s.%s contains a value '%s' that does not have a corresponding value in %s.%s."
+                        % (table_name, bad_row[0], table_name, column_name, bad_row[1],
+                        referenced_table_name, referenced_column_name))
+
     def close(self):
         # If database is in memory, closing the connection destroys the
         # database. To prevent accidental data loss, ignore close requests on
diff --git a/django/db/backends/sqlite3/introspection.py b/django/db/backends/sqlite3/introspection.py
index 5ee7b64bcd..9652a4da6a 100644
--- a/django/db/backends/sqlite3/introspection.py
+++ b/django/db/backends/sqlite3/introspection.py
@@ -103,6 +103,35 @@ class DatabaseIntrospection(BaseDatabaseIntrospection):
 
         return relations
 
+    def get_key_columns(self, cursor, table_name):
+        """
+        Returns a list of (column_name, referenced_table_name, referenced_column_name) for all
+        key columns in given table.
+        """
+        key_columns = []
+
+        # Schema for this table
+        cursor.execute("SELECT sql FROM sqlite_master WHERE tbl_name = %s AND type = %s", [table_name, "table"])
+        results = cursor.fetchone()[0].strip()
+        results = results[results.index('(')+1:results.rindex(')')]
+
+        # Walk through and look for references to other tables. SQLite doesn't
+        # really have enforced references, but since it echoes out the SQL used
+        # to create the table we can look for REFERENCES statements used there.
+        for field_index, field_desc in enumerate(results.split(',')):
+            field_desc = field_desc.strip()
+            if field_desc.startswith("UNIQUE"):
+                continue
+
+            m = re.search('"(.*)".*references (.*) \(["|](.*)["|]\)', field_desc, re.I)
+            if not m:
+                continue
+
+            # This will append (column_name, referenced_table_name, referenced_column_name) to key_columns
+            key_columns.append(tuple([s.strip('"') for s in m.groups()]))
+
+        return key_columns
+
     def get_indexes(self, cursor, table_name):
         """
         Returns a dictionary of fieldname -> infodict for the given table,
@@ -128,6 +157,21 @@ class DatabaseIntrospection(BaseDatabaseIntrospection):
             indexes[name]['unique'] = True
         return indexes
 
+    def get_primary_key_column(self, cursor, table_name):
+        """
+        Get the column name of the primary key for the given table.
+        """
+        # Don't use PRAGMA because that causes issues with some transactions
+        cursor.execute("SELECT sql FROM sqlite_master WHERE tbl_name = %s AND type = %s", [table_name, "table"])
+        results = cursor.fetchone()[0].strip()
+        results = results[results.index('(')+1:results.rindex(')')]
+        for field_desc in results.split(','):
+            field_desc = field_desc.strip()
+            m = re.search('"(.*)".*PRIMARY KEY$', field_desc)
+            if m:
+                return m.groups()[0]
+        return None
+
     def _table_info(self, cursor, name):
         cursor.execute('PRAGMA table_info(%s)' % self.connection.ops.quote_name(name))
         # cid, name, type, notnull, dflt_value, pk