Opened 15 years ago

Closed 15 years ago

Last modified 12 years ago

#9779 closed (fixed)

Adding Foreign Key detection to SQLite inspectdb

Reported by: gabbott Owned by: nobody
Component: django-admin.py inspectdb Version: dev
Severity: Keywords:
Cc: Ramiro Morales Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

I was using the SQLite introspection and realized that it didn't support foreign key detection. I realize that SQLite does not enforce the "reference" sqlite commands the data is there and can easily be parsed out. Here is my patch:

Index: django/db/backends/sqlite3/introspection.py
===================================================================
--- django/db/backends/sqlite3/introspection.py	(revision 9627)
+++ django/db/backends/sqlite3/introspection.py	(working copy)
@@ -55,8 +55,59 @@
                  info['null_ok']) for info in self._table_info(cursor, table_name)]
 
     def get_relations(self, cursor, table_name):
-        raise NotImplementedError
+        """
+        Returns a dictionary of {field_index: (field_index_other_table, other_table)}
+        representing all relationships to the given table. Indexes are 0-based.
+        """
+        
+        import re
+        
+        # Dictionary of relations to return
+        relations = {}
+        
+        # Schema for this table
+        cursor.execute("select sql from sqlite_master where tbl_name='%s'" % table_name)
+        results = cursor.fetchone()[0].strip()
+        results = results.split('(', 1)[1]
+        results = results.rsplit(')', 1)[0]
 
+        # walk through and look for references to other tables.
+        for index, i in enumerate(results.split(',')):
+            i = i.strip()
+            if i.startswith("UNIQUE"):
+                continue
+
+            m = re.search('references (.*) \(["|](.*)["|]\)', i, re.I)
+            
+            if not m:
+                continue
+        
+            table, column = m.groups()
+            
+            table = table.strip('"')
+            column = column.strip('"')
+            
+            cursor.execute("select sql from sqlite_master where tbl_name='%s'" % table)
+            
+            other_table_results = cursor.fetchone()[0].strip()
+            other_table_results = other_table_results.split('(', 1)[1]
+            other_table_results = other_table_results.rsplit(')', 1)[0]
+            
+            second_index = None
+            for _index, j in enumerate(other_table_results.split(',')):
+                j = j.strip()
+                if j.startswith('UNIQUE'):
+                    continue
+
+                name = j.split(' ', 1)[0].strip('"')
+                if name == column:
+                    second_index = _index
+            
+            if second_index != None:
+                relations[index] = (second_index, table)
+                            
+        return relations
+
     def get_indexes(self, cursor, table_name):
         """
         Returns a dictionary of fieldname -> infodict for the given table,

Attachments (2)

9779-fk-introspection-for-sqlite.diff (2.3 KB ) - added by Ramiro Morales 15 years ago.
patch by gabbott in .diff form
tutorial-db-introspection.diff (1.6 KB ) - added by Ramiro Morales 15 years ago.
Efefcto of that patch intrspecting a sqlite3 DB with the project from the Django tutorial

Download all attachments as: .zip

Change History (10)

comment:1 by Jacob, 15 years ago

milestone: 1.1
Triage Stage: UnreviewedAccepted

comment:2 by Jacob, 15 years ago

Can you please turn this into a real patch?

comment:3 by Ramiro Morales, 15 years ago

Cc: Ramiro Morales added

by Ramiro Morales, 15 years ago

patch by gabbott in .diff form

by Ramiro Morales, 15 years ago

Efefcto of that patch intrspecting a sqlite3 DB with the project from the Django tutorial

comment:4 by Ramiro Morales, 15 years ago

The tutorial-db-introspection.diff file shows differences between the models.py file obtained with current unmodified trunk and the one obtained after gabbott's patch has been applied when introspecting a sqlite3 database in which the polls application from the Django tutorial has previously been created with syncdb (INSTALLED_APPS contained django.contrib.auth', 'django.contrib.contenttypes', 'django.contrib.sessions', 'django.contrib.sites' and the origina 'polls' app itself)

comment:5 by Jacob, 15 years ago

(In [10362]) Added tests for database introspection. Refs #9779.

comment:6 by Jacob, 15 years ago

(In [10363]) [1.0.X] Added tests for database introspection. Refs #9779. Backport r10362 from trunk.

comment:7 by Jacob, 15 years ago

Resolution: fixed
Status: newclosed

(In [10364]) Fixed #9779: added support for relation detection to inspectdb under SQLite. Thanks, gabbott and ramiro.

comment:8 by Jacob, 12 years ago

milestone: 1.1

Milestone 1.1 deleted

Note: See TracTickets for help on using tickets.
Back to Top