Django

Code

Ticket #1286 (reopened)

Opened 2 years ago

Last modified 1 year ago

[patch] Automatic detection of primary key with inspectdb for Mysql

Reported by: gandalf@owca.info Assigned to: nobody
Component: django-admin.py inspectdb Version: SVN
Keywords: inspectdb Cc:
Triage Stage: Accepted Has patch: 1
Needs documentation: 0 Needs tests: 1
Patch needs improvement: 1

Description

Manual adding of primary_key for inspectdb models is annoying. Here is my try at automatic detection of primary key. It also tries to identify unique fields and add unique=True property.

It should probably work with other databases as well but I did not test it. 

gandalf@paw:~/django_src/django/core$ svn diff
Index: db/__init__.py
===================================================================
--- db/__init__.py      (revision 2156)
+++ db/__init__.py      (working copy)
@@ -37,6 +37,7 @@
 get_table_list = dbmod.get_table_list
 get_table_description = dbmod.get_table_description
 get_relations = dbmod.get_relations
+get_indexes = dbmod.get_indexes
 OPERATOR_MAPPING = dbmod.OPERATOR_MAPPING
 DATA_TYPES = dbmod.DATA_TYPES
 DATA_TYPES_REVERSE = dbmod.DATA_TYPES_REVERSE
Index: db/backends/ado_mssql.py
===================================================================
--- db/backends/ado_mssql.py    (revision 2156)
+++ db/backends/ado_mssql.py    (working copy)
@@ -118,6 +118,9 @@
 def get_relations(cursor, table_name):
     raise NotImplementedError

+def get_indexes(cursor, table_name):
+    raise NotImplementedError
+
 OPERATOR_MAPPING = {
     'exact': '= %s',
     'iexact': 'LIKE %s',
Index: db/backends/postgresql.py
===================================================================
--- db/backends/postgresql.py   (revision 2156)
+++ db/backends/postgresql.py   (working copy)
@@ -126,6 +126,9 @@
             continue
     return relations

+def get_indexes(cursor, table_name):
+    raise NotImplementedError
+
 # Register these custom typecasts, because Django expects dates/times to be
 # in Python's native (standard-library) datetime/time format, whereas psycopg
 # use mx.DateTime by default.
Index: db/backends/sqlite3.py
===================================================================
--- db/backends/sqlite3.py      (revision 2156)
+++ db/backends/sqlite3.py      (working copy)
@@ -134,6 +134,9 @@
 def get_relations(cursor, table_name):
     raise NotImplementedError

+def get_indexes(cursor, table_name):
+    raise NotImplementedError
+
 # Operators and fields ########################################################

 # SQLite requires LIKE statements to include an ESCAPE clause if the value
Index: db/backends/mysql.py
===================================================================
--- db/backends/mysql.py        (revision 2156)
+++ db/backends/mysql.py        (working copy)
@@ -135,6 +135,15 @@
 def get_relations(cursor, table_name):
     raise NotImplementedError

+def get_indexes(cursor, table_name):
+    "Returns a dict of indexes for given table"
+    cursor.execute("SHOW INDEX FROM %s" % DatabaseWrapper().quote_name(table_name))
+    indexes = {}
+    for row in cursor.fetchall():
+        indexes[row[4]] = {'Key_name' : row[2],
+                           'Non_unique' : row[1]}
+    return indexes
+
 OPERATOR_MAPPING = {
     'exact': '= %s',
     'iexact': 'LIKE %s',
Index: management.py
===================================================================
--- management.py       (revision 2156)
+++ management.py       (working copy)
@@ -581,6 +581,12 @@
             relations = db.get_relations(cursor, table_name)
         except NotImplementedError:
             relations = {}
+
+        try:
+            indexes = db.get_indexes(cursor, table_name)
+        except NotImplementedError:
+            indexes = {}
+
         for i, row in enumerate(db.get_table_description(cursor, table_name)):
             column_name = row[0]
             if relations.has_key(i):
@@ -609,6 +615,14 @@
                 if field_type == 'CharField' and row[3]:
                     extra_params['maxlength'] = row[3]

+                if column_name in indexes:
+                    if indexes[column_name]['Key_name'] == 'PRIMARY':
+                        extra_params['primary_key'] = True
+                    elif indexes[column_name]['Non_unique'] == 0L:
+                        extra_params['unique'] = True
+                    else:
+                        print indexes[column_name]
+
                 field_desc = '%s = meta.%s(' % (column_name, field_type)
                 field_desc += ', '.join(['%s=%s' % (k, v) for k, v in extra_params.items()])
                 field_desc += ')'
gandalf@paw:~/django_src/django/core$ vim management.py
gandalf@paw:~/django_src/django/core$ svn diff
Index: db/__init__.py
===================================================================
--- db/__init__.py      (revision 2156)
+++ db/__init__.py      (working copy)
@@ -37,6 +37,7 @@
 get_table_list = dbmod.get_table_list
 get_table_description = dbmod.get_table_description
 get_relations = dbmod.get_relations
+get_indexes = dbmod.get_indexes
 OPERATOR_MAPPING = dbmod.OPERATOR_MAPPING
 DATA_TYPES = dbmod.DATA_TYPES
 DATA_TYPES_REVERSE = dbmod.DATA_TYPES_REVERSE
Index: db/backends/ado_mssql.py
===================================================================
--- db/backends/ado_mssql.py    (revision 2156)
+++ db/backends/ado_mssql.py    (working copy)
@@ -118,6 +118,9 @@
 def get_relations(cursor, table_name):
     raise NotImplementedError

+def get_indexes(cursor, table_name):
+    raise NotImplementedError
+
 OPERATOR_MAPPING = {
     'exact': '= %s',
     'iexact': 'LIKE %s',
Index: db/backends/postgresql.py
===================================================================
--- db/backends/postgresql.py   (revision 2156)
+++ db/backends/postgresql.py   (working copy)
@@ -126,6 +126,9 @@
             continue
     return relations

+def get_indexes(cursor, table_name):
+    raise NotImplementedError
+
 # Register these custom typecasts, because Django expects dates/times to be
 # in Python's native (standard-library) datetime/time format, whereas psycopg
 # use mx.DateTime by default.
Index: db/backends/sqlite3.py
===================================================================
--- db/backends/sqlite3.py      (revision 2156)
+++ db/backends/sqlite3.py      (working copy)
@@ -134,6 +134,9 @@
 def get_relations(cursor, table_name):
     raise NotImplementedError

+def get_indexes(cursor, table_name):
+    raise NotImplementedError
+
 # Operators and fields ########################################################

 # SQLite requires LIKE statements to include an ESCAPE clause if the value
Index: db/backends/mysql.py
===================================================================
--- db/backends/mysql.py        (revision 2156)
+++ db/backends/mysql.py        (working copy)
@@ -135,6 +135,15 @@
 def get_relations(cursor, table_name):
     raise NotImplementedError

+def get_indexes(cursor, table_name):
+    "Returns a dict of indexes for given table"
+    cursor.execute("SHOW INDEX FROM %s" % DatabaseWrapper().quote_name(table_name))
+    indexes = {}
+    for row in cursor.fetchall():
+        indexes[row[4]] = {'Key_name' : row[2],
+                           'Non_unique' : row[1]}
+    return indexes
+
 OPERATOR_MAPPING = {
     'exact': '= %s',
     'iexact': 'LIKE %s',
Index: management.py
===================================================================
--- management.py       (revision 2156)
+++ management.py       (working copy)
@@ -581,6 +581,12 @@
             relations = db.get_relations(cursor, table_name)
         except NotImplementedError:
             relations = {}
+
+        try:
+            indexes = db.get_indexes(cursor, table_name)
+        except NotImplementedError:
+            indexes = {}
+
         for i, row in enumerate(db.get_table_description(cursor, table_name)):
             column_name = row[0]
             if relations.has_key(i):
@@ -609,6 +615,12 @@
                 if field_type == 'CharField' and row[3]:
                     extra_params['maxlength'] = row[3]

+                if column_name in indexes:
+                    if indexes[column_name]['Key_name'] == 'PRIMARY':
+                        extra_params['primary_key'] = True
+                    elif indexes[column_name]['Non_unique'] == 0L:
+                        extra_params['unique'] = True
+
                 field_desc = '%s = meta.%s(' % (column_name, field_type)
                 field_desc += ', '.join(['%s=%s' % (k, v) for k, v in extra_params.items()])
                 field_desc += ')'

Attachments

mysql.diff (3.3 kB) - added by Jan Rademaker <j.rademaker@gmail.com> on 02/18/06 17:30:35.
Covers additional scenarios

Change History

02/18/06 15:26:30 changed by adrian

  • status changed from new to closed.
  • resolution set to fixed.

(In [2346]) Fixed #1286 -- Improved 'inspectdb' so that it introspects primary_key=True and unique=True for MySQL. Thanks, gandalf@owca.info

02/18/06 17:30:35 changed by Jan Rademaker <j.rademaker@gmail.com>

  • attachment mysql.diff added.

Covers additional scenarios

02/18/06 17:37:31 changed by Jan Rademaker <j.rademaker@gmail.com>

  • status changed from closed to reopened.
  • resolution deleted.

mysql.diff covers (some of) the following scenarios. 

-- SHOW INDEX FROM test1
test1	0	PRIMARY	1	myid	A	0	NULL	NULL	
test1	0	f1_f2_f3	1	f1	A	NULL	NULL	NULL	
test1	0	f1_f2_f3	2	f2	A	NULL	NULL	NULL	
test1	0	f1_f2_f3	3	f3	A	NULL	NULL	NULL	
test1	0	f2_unique	1	f2	A	NULL	NULL	NULL	
test1	1	f1_f2	1	f1	A	NULL	NULL	NULL	
test1	1	f1_f2	2	f2	A	NULL	NULL	NULL	
test1	1	myid_f3	1	myid	A	NULL	NULL	NULL	
test1	1	myid_f3	2	f3	A	NULL	NULL	NULL
  • If a PRIMARY key spans serveral columns it is ignored
  • A field is only considered unique when the index does not span more than 1 column
  • In the case of 1 or more unique indexes that span more than 1 column the first is used as META.unique_together

Example output: 

from django.core import meta

class Test1(meta.Model):
    myid = meta.IntegerField(primary_key=True)
    f1 = meta.IntegerField()
    f2 = meta.IntegerField(unique=True)
    f3 = meta.IntegerField()
    class META:
        db_table = 'test1'
        unique_together = (("f1", "f2", "f3"),)

02/19/06 08:37:33 changed by anonymous

  • summary changed from Automatic detection of primary key with inspectdb for Mysql to [patch] Automatic detection of primary key with inspectdb for Mysql.

02/27/06 17:47:21 changed by jacob

  • status changed from reopened to closed.
  • resolution set to fixed.

Auto-detection isn't going to be perfect; this works as well as it can at this point.

02/27/06 19:12:42 changed by adrian

  • status changed from closed to reopened.
  • resolution deleted.

Reopening because this can be improved with the patch -- I just haven't had time to implement it.

11/26/06 18:37:11 changed by adrian

  • component changed from django-admin.py to django-admin.py inspectdb.

01/19/07 04:56:48 changed by Simon G. <dev@simon.net.nz>

  • keywords set to inspectdb.
  • needs_better_patch set to 1.
  • needs_tests set to 1.
  • stage changed from Unreviewed to Accepted.

Add/Change #1286 ([patch] Automatic detection of primary key with inspectdb for Mysql)




Change Properties
Action