1 from django.db.backends.mysql.base import DatabaseOperations |
1 from django.db.backends import BaseDatabaseIntrospection |
2 from MySQLdb import ProgrammingError, OperationalError |
2 from MySQLdb import ProgrammingError, OperationalError |
3 from MySQLdb.constants import FIELD_TYPE |
3 from MySQLdb.constants import FIELD_TYPE |
4 import re |
4 import re |
5 |
5 |
6 quote_name = DatabaseOperations().quote_name |
|
7 foreign_key_re = re.compile(r"\sCONSTRAINT `[^`]*` FOREIGN KEY \(`([^`]*)`\) REFERENCES `([^`]*)` \(`([^`]*)`\)") |
6 foreign_key_re = re.compile(r"\sCONSTRAINT `[^`]*` FOREIGN KEY \(`([^`]*)`\) REFERENCES `([^`]*)` \(`([^`]*)`\)") |
8 |
7 |
9 def get_table_list(cursor): |
8 class DatabaseIntrospection(BaseDatabaseIntrospection): |
10 "Returns a list of table names in the current database." |
9 data_types_reverse = { |
11 cursor.execute("SHOW TABLES") |
10 FIELD_TYPE.BLOB: 'TextField', |
12 return [row[0] for row in cursor.fetchall()] |
11 FIELD_TYPE.CHAR: 'CharField', |
|
12 FIELD_TYPE.DECIMAL: 'DecimalField', |
|
13 FIELD_TYPE.NEWDECIMAL: 'DecimalField', |
|
14 FIELD_TYPE.DATE: 'DateField', |
|
15 FIELD_TYPE.DATETIME: 'DateTimeField', |
|
16 FIELD_TYPE.DOUBLE: 'FloatField', |
|
17 FIELD_TYPE.FLOAT: 'FloatField', |
|
18 FIELD_TYPE.INT24: 'IntegerField', |
|
19 FIELD_TYPE.LONG: 'IntegerField', |
|
20 FIELD_TYPE.LONGLONG: 'IntegerField', |
|
21 FIELD_TYPE.SHORT: 'IntegerField', |
|
22 FIELD_TYPE.STRING: 'CharField', |
|
23 FIELD_TYPE.TIMESTAMP: 'DateTimeField', |
|
24 FIELD_TYPE.TINY: 'IntegerField', |
|
25 FIELD_TYPE.TINY_BLOB: 'TextField', |
|
26 FIELD_TYPE.MEDIUM_BLOB: 'TextField', |
|
27 FIELD_TYPE.LONG_BLOB: 'TextField', |
|
28 FIELD_TYPE.VAR_STRING: 'CharField', |
|
29 } |
13 |
30 |
14 def get_table_description(cursor, table_name): |
31 def get_table_list(self, cursor): |
15 "Returns a description of the table, with the DB-API cursor.description interface." |
32 "Returns a list of table names in the current database." |
16 cursor.execute("SELECT * FROM %s LIMIT 1" % quote_name(table_name)) |
33 cursor.execute("SHOW TABLES") |
17 return cursor.description |
34 return [row[0] for row in cursor.fetchall()] |
18 |
35 |
19 def _name_to_index(cursor, table_name): |
36 def get_table_description(self, cursor, table_name): |
20 """ |
37 "Returns a description of the table, with the DB-API cursor.description interface." |
21 Returns a dictionary of {field_name: field_index} for the given table. |
38 cursor.execute("SELECT * FROM %s LIMIT 1" % self.connection.ops.quote_name(table_name)) |
22 Indexes are 0-based. |
39 return cursor.description |
23 """ |
|
24 return dict([(d[0], i) for i, d in enumerate(get_table_description(cursor, table_name))]) |
|
25 |
40 |
26 def get_relations(cursor, table_name): |
41 def _name_to_index(self, cursor, table_name): |
27 """ |
42 """ |
28 Returns a dictionary of {field_index: (field_index_other_table, other_table)} |
43 Returns a dictionary of {field_name: field_index} for the given table. |
29 representing all relationships to the given table. Indexes are 0-based. |
44 Indexes are 0-based. |
30 """ |
45 """ |
31 my_field_dict = _name_to_index(cursor, table_name) |
46 return dict([(d[0], i) for i, d in enumerate(self.get_table_description(cursor, table_name))]) |
32 constraints = [] |
47 |
33 relations = {} |
48 def get_relations(self, cursor, table_name): |
34 try: |
49 """ |
35 # This should work for MySQL 5.0. |
50 Returns a dictionary of {field_index: (field_index_other_table, other_table)} |
36 cursor.execute(""" |
51 representing all relationships to the given table. Indexes are 0-based. |
37 SELECT column_name, referenced_table_name, referenced_column_name |
52 """ |
38 FROM information_schema.key_column_usage |
53 my_field_dict = self._name_to_index(cursor, table_name) |
39 WHERE table_name = %s |
54 constraints = [] |
40 AND table_schema = DATABASE() |
55 relations = {} |
41 AND referenced_table_name IS NOT NULL |
56 try: |
42 AND referenced_column_name IS NOT NULL""", [table_name]) |
57 # This should work for MySQL 5.0. |
43 constraints.extend(cursor.fetchall()) |
58 cursor.execute(""" |
44 except (ProgrammingError, OperationalError): |
59 SELECT column_name, referenced_table_name, referenced_column_name |
45 # Fall back to "SHOW CREATE TABLE", for previous MySQL versions. |
60 FROM information_schema.key_column_usage |
46 # Go through all constraints and save the equal matches. |
61 WHERE table_name = %s |
47 cursor.execute("SHOW CREATE TABLE %s" % quote_name(table_name)) |
62 AND table_schema = DATABASE() |
|
63 AND referenced_table_name IS NOT NULL |
|
64 AND referenced_column_name IS NOT NULL""", [table_name]) |
|
65 constraints.extend(cursor.fetchall()) |
|
66 except (ProgrammingError, OperationalError): |
|
67 # Fall back to "SHOW CREATE TABLE", for previous MySQL versions. |
|
68 # Go through all constraints and save the equal matches. |
|
69 cursor.execute("SHOW CREATE TABLE %s" % self.connection.ops.quote_name(table_name)) |
|
70 for row in cursor.fetchall(): |
|
71 pos = 0 |
|
72 while True: |
|
73 match = foreign_key_re.search(row[1], pos) |
|
74 if match == None: |
|
75 break |
|
76 pos = match.end() |
|
77 constraints.append(match.groups()) |
|
78 |
|
79 for my_fieldname, other_table, other_field in constraints: |
|
80 other_field_index = self._name_to_index(cursor, other_table)[other_field] |
|
81 my_field_index = my_field_dict[my_fieldname] |
|
82 relations[my_field_index] = (other_field_index, other_table) |
|
83 |
|
84 return relations |
|
85 |
|
86 def get_indexes(self, cursor, table_name): |
|
87 """ |
|
88 Returns a dictionary of fieldname -> infodict for the given table, |
|
89 where each infodict is in the format: |
|
90 {'primary_key': boolean representing whether it's the primary key, |
|
91 'unique': boolean representing whether it's a unique index} |
|
92 """ |
|
93 cursor.execute("SHOW INDEX FROM %s" % self.connection.ops.quote_name(table_name)) |
|
94 indexes = {} |
48 for row in cursor.fetchall(): |
95 for row in cursor.fetchall(): |
49 pos = 0 |
96 indexes[row[4]] = {'primary_key': (row[2] == 'PRIMARY'), 'unique': not bool(row[1])} |
50 while True: |
97 return indexes |
51 match = foreign_key_re.search(row[1], pos) |
|
52 if match == None: |
|
53 break |
|
54 pos = match.end() |
|
55 constraints.append(match.groups()) |
|
56 |
98 |
57 for my_fieldname, other_table, other_field in constraints: |
|
58 other_field_index = _name_to_index(cursor, other_table)[other_field] |
|
59 my_field_index = my_field_dict[my_fieldname] |
|
60 relations[my_field_index] = (other_field_index, other_table) |
|
61 |
|
62 return relations |
|
63 |
|
64 def get_indexes(cursor, table_name): |
|
65 """ |
|
66 Returns a dictionary of fieldname -> infodict for the given table, |
|
67 where each infodict is in the format: |
|
68 {'primary_key': boolean representing whether it's the primary key, |
|
69 'unique': boolean representing whether it's a unique index} |
|
70 """ |
|
71 cursor.execute("SHOW INDEX FROM %s" % quote_name(table_name)) |
|
72 indexes = {} |
|
73 for row in cursor.fetchall(): |
|
74 indexes[row[4]] = {'primary_key': (row[2] == 'PRIMARY'), 'unique': not bool(row[1])} |
|
75 return indexes |
|
76 |
|
77 DATA_TYPES_REVERSE = { |
|
78 FIELD_TYPE.BLOB: 'TextField', |
|
79 FIELD_TYPE.CHAR: 'CharField', |
|
80 FIELD_TYPE.DECIMAL: 'DecimalField', |
|
81 FIELD_TYPE.DATE: 'DateField', |
|
82 FIELD_TYPE.DATETIME: 'DateTimeField', |
|
83 FIELD_TYPE.DOUBLE: 'FloatField', |
|
84 FIELD_TYPE.FLOAT: 'FloatField', |
|
85 FIELD_TYPE.INT24: 'IntegerField', |
|
86 FIELD_TYPE.LONG: 'IntegerField', |
|
87 FIELD_TYPE.LONGLONG: 'IntegerField', |
|
88 FIELD_TYPE.SHORT: 'IntegerField', |
|
89 FIELD_TYPE.STRING: 'CharField', |
|
90 FIELD_TYPE.TIMESTAMP: 'DateTimeField', |
|
91 FIELD_TYPE.TINY: 'IntegerField', |
|
92 FIELD_TYPE.TINY_BLOB: 'TextField', |
|
93 FIELD_TYPE.MEDIUM_BLOB: 'TextField', |
|
94 FIELD_TYPE.LONG_BLOB: 'TextField', |
|
95 FIELD_TYPE.VAR_STRING: 'CharField', |
|
96 } |
|