From be87f0b0ec992e6f3e72735d8e95c654da969f6d Mon Sep 17 00:00:00 2001 From: Karen Tracey Date: Sun, 7 Aug 2011 00:43:26 +0000 Subject: 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 --- django/db/backends/sqlite3/base.py | 34 ++++++++++++++++++++++ django/db/backends/sqlite3/introspection.py | 44 +++++++++++++++++++++++++++++ 2 files changed, 78 insertions(+) (limited to 'django/db/backends/sqlite3') 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 -- cgit v1.3