Code

Opened 5 years ago

Closed 5 years ago

Last modified 3 years ago

#9779 closed (fixed)

Adding Foreign Key detection to SQLite inspectdb

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

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 5 years ago.
patch by gabbott in .diff form
tutorial-db-introspection.diff (1.6 KB) - added by ramiro 5 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 Changed 5 years ago by jacob

  • milestone set to 1.1
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

comment:2 Changed 5 years ago by jacob

Can you please turn this into a real patch?

comment:3 Changed 5 years ago by ramiro

  • Cc ramiro added

Changed 5 years ago by ramiro

patch by gabbott in .diff form

Changed 5 years ago by ramiro

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

comment:4 Changed 5 years ago by ramiro

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 Changed 5 years ago by jacob

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

comment:6 Changed 5 years ago by jacob

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

comment:7 Changed 5 years ago by jacob

  • Resolution set to fixed
  • Status changed from new to closed

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

comment:8 Changed 3 years ago by jacob

  • milestone 1.1 deleted

Milestone 1.1 deleted

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.