| 1 |
from collections import namedtuple |
| 2 |
|
| 3 |
import sqlparse |
| 4 |
from MySQLdb.constants import FIELD_TYPE |
| 5 |
|
| 6 |
from django.db.backends.base.introspection import BaseDatabaseIntrospection |
| 7 |
from django.db.backends.base.introspection import FieldInfo as BaseFieldInfo |
| 8 |
from django.db.backends.base.introspection import TableInfo as BaseTableInfo |
| 9 |
from django.db.models import Index |
| 10 |
from django.utils.datastructures import OrderedSet |
| 11 |
|
| 12 |
FieldInfo = namedtuple( |
| 13 |
"FieldInfo", |
| 14 |
BaseFieldInfo._fields |
| 15 |
+ ("extra", "is_unsigned", "has_json_constraint", "comment", "data_type"), |
| 16 |
) |
| 17 |
InfoLine = namedtuple( |
| 18 |
"InfoLine", |
| 19 |
"col_name data_type max_len num_prec num_scale extra column_default " |
| 20 |
"collation is_unsigned comment", |
| 21 |
) |
| 22 |
TableInfo = namedtuple("TableInfo", BaseTableInfo._fields + ("comment",)) |
| 23 |
|
| 24 |
|
| 25 |
class DatabaseIntrospection(BaseDatabaseIntrospection): |
| 26 |
data_types_reverse = { |
| 27 |
FIELD_TYPE.BLOB: "TextField", |
| 28 |
FIELD_TYPE.CHAR: "CharField", |
| 29 |
FIELD_TYPE.DECIMAL: "DecimalField", |
| 30 |
FIELD_TYPE.NEWDECIMAL: "DecimalField", |
| 31 |
FIELD_TYPE.DATE: "DateField", |
| 32 |
FIELD_TYPE.DATETIME: "DateTimeField", |
| 33 |
FIELD_TYPE.DOUBLE: "FloatField", |
| 34 |
FIELD_TYPE.FLOAT: "FloatField", |
| 35 |
FIELD_TYPE.INT24: "IntegerField", |
| 36 |
FIELD_TYPE.JSON: "JSONField", |
| 37 |
FIELD_TYPE.LONG: "IntegerField", |
| 38 |
FIELD_TYPE.LONGLONG: "BigIntegerField", |
| 39 |
FIELD_TYPE.SHORT: "SmallIntegerField", |
| 40 |
FIELD_TYPE.STRING: "CharField", |
| 41 |
FIELD_TYPE.TIME: "TimeField", |
| 42 |
FIELD_TYPE.TIMESTAMP: "DateTimeField", |
| 43 |
FIELD_TYPE.TINY: "IntegerField", |
| 44 |
FIELD_TYPE.TINY_BLOB: "TextField", |
| 45 |
FIELD_TYPE.MEDIUM_BLOB: "TextField", |
| 46 |
FIELD_TYPE.LONG_BLOB: "TextField", |
| 47 |
FIELD_TYPE.VAR_STRING: "CharField", |
| 48 |
} |
| 49 |
|
| 50 |
def get_field_type(self, data_type, description): |
| 51 |
field_type = super().get_field_type(data_type, description) |
| 52 |
if "auto_increment" in description.extra: |
| 53 |
if field_type == "IntegerField": |
| 54 |
return "AutoField" |
| 55 |
elif field_type == "BigIntegerField": |
| 56 |
return "BigAutoField" |
| 57 |
elif field_type == "SmallIntegerField": |
| 58 |
return "SmallAutoField" |
| 59 |
if description.is_unsigned: |
| 60 |
if field_type == "BigIntegerField": |
| 61 |
return "PositiveBigIntegerField" |
| 62 |
elif field_type == "IntegerField": |
| 63 |
return "PositiveIntegerField" |
| 64 |
elif field_type == "SmallIntegerField": |
| 65 |
return "PositiveSmallIntegerField" |
| 66 |
if description.data_type.upper() == "UUID": |
| 67 |
return "UUIDField" |
| 68 |
# JSON data type is an alias for LONGTEXT in MariaDB, use check |
| 69 |
# constraints clauses to introspect JSONField. |
| 70 |
if description.has_json_constraint: |
| 71 |
return "JSONField" |
| 72 |
return field_type |
| 73 |
|
| 74 |
def get_table_list(self, cursor): |
| 75 |
"""Return a list of table and view names in the current database.""" |
| 76 |
cursor.execute(""" |
| 77 |
SELECT |
| 78 |
table_name, |
| 79 |
table_type, |
| 80 |
table_comment |
| 81 |
FROM information_schema.tables |
| 82 |
WHERE table_schema = DATABASE() |
| 83 |
""") |
| 84 |
return [ |
| 85 |
TableInfo(row[0], {"BASE TABLE": "t", "VIEW": "v"}.get(row[1]), row[2]) |
| 86 |
for row in cursor.fetchall() |
| 87 |
] |
| 88 |
|
| 89 |
def get_table_description(self, cursor, table_name): |
| 90 |
""" |
| 91 |
Return a description of the table with the DB-API cursor.description |
| 92 |
interface." |
| 93 |
""" |
| 94 |
json_constraints = {} |
| 95 |
if ( |
| 96 |
self.connection.mysql_is_mariadb |
| 97 |
and self.connection.features.can_introspect_json_field |
| 98 |
): |
| 99 |
# JSON data type is an alias for LONGTEXT in MariaDB, select |
| 100 |
# JSON_VALID() constraints to introspect JSONField. |
| 101 |
cursor.execute( |
| 102 |
""" |
| 103 |
SELECT c.constraint_name AS column_name |
| 104 |
FROM information_schema.check_constraints AS c |
| 105 |
WHERE |
| 106 |
c.table_name = %s AND |
| 107 |
LOWER(c.check_clause) = |
| 108 |
'json_valid(`' + LOWER(c.constraint_name) + '`)' AND |
| 109 |
c.constraint_schema = DATABASE() |
| 110 |
""", |
| 111 |
[table_name], |
| 112 |
) |
| 113 |
json_constraints = {row[0] for row in cursor.fetchall()} |
| 114 |
# A default collation for the given table. |
| 115 |
cursor.execute( |
| 116 |
""" |
| 117 |
SELECT table_collation |
| 118 |
FROM information_schema.tables |
| 119 |
WHERE table_schema = DATABASE() |
| 120 |
AND table_name = %s |
| 121 |
""", |
| 122 |
[table_name], |
| 123 |
) |
| 124 |
row = cursor.fetchone() |
| 125 |
default_column_collation = row[0] if row else "" |
| 126 |
# information_schema database gives more accurate results for some figures: |
| 127 |
# - varchar length returned by cursor.description is an internal length, |
| 128 |
# not visible length (#5725) |
| 129 |
# - precision and scale (for decimal fields) (#5014) |
| 130 |
# - auto_increment is not available in cursor.description |
| 131 |
cursor.execute( |
| 132 |
""" |
| 133 |
SELECT |
| 134 |
column_name, data_type, character_maximum_length, |
| 135 |
numeric_precision, numeric_scale, extra, column_default, |
| 136 |
CASE |
| 137 |
WHEN collation_name = %s THEN NULL |
| 138 |
ELSE collation_name |
| 139 |
END AS collation_name, |
| 140 |
CASE |
| 141 |
WHEN column_type LIKE '%% unsigned' THEN 1 |
| 142 |
ELSE 0 |
| 143 |
END AS is_unsigned, |
| 144 |
column_comment |
| 145 |
FROM information_schema.columns |
| 146 |
WHERE table_name = %s AND table_schema = DATABASE() |
| 147 |
""", |
| 148 |
[default_column_collation, table_name], |
| 149 |
) |
| 150 |
field_info = {line[0]: InfoLine(*line) for line in cursor.fetchall()} |
| 151 |
|
| 152 |
cursor.execute( |
| 153 |
"SELECT * FROM %s LIMIT 1" % self.connection.ops.quote_name(table_name) |
| 154 |
) |
| 155 |
|
| 156 |
def to_int(i): |
| 157 |
return int(i) if i is not None else i |
| 158 |
|
| 159 |
fields = [] |
| 160 |
for line in cursor.description: |
| 161 |
info = field_info[line[0]] |
| 162 |
fields.append( |
| 163 |
FieldInfo( |
| 164 |
*line[:2], |
| 165 |
to_int(info.max_len) or line[2], |
| 166 |
to_int(info.max_len) or line[3], |
| 167 |
to_int(info.num_prec) or line[4], |
| 168 |
to_int(info.num_scale) or line[5], |
| 169 |
line[6], |
| 170 |
info.column_default, |
| 171 |
info.collation, |
| 172 |
info.extra, |
| 173 |
info.is_unsigned, |
| 174 |
line[0] in json_constraints, |
| 175 |
info.comment, |
| 176 |
info.data_type, |
| 177 |
) |
| 178 |
) |
| 179 |
return fields |
| 180 |
|
| 181 |
def get_sequences(self, cursor, table_name, table_fields=()): |
| 182 |
for field_info in self.get_table_description(cursor, table_name): |
| 183 |
if "auto_increment" in field_info.extra: |
| 184 |
# MySQL allows only one auto-increment column per table. |
| 185 |
return [{"table": table_name, "column": field_info.name}] |
| 186 |
return [] |
| 187 |
|
| 188 |
def get_relations(self, cursor, table_name): |
| 189 |
""" |
| 190 |
Return a dictionary of {field_name: (field_name_other_table, other_table)} |
| 191 |
representing all foreign keys in the given table. |
| 192 |
""" |
| 193 |
cursor.execute( |
| 194 |
""" |
| 195 |
SELECT column_name, referenced_column_name, referenced_table_name |
| 196 |
FROM information_schema.key_column_usage |
| 197 |
WHERE table_name = %s |
| 198 |
AND table_schema = DATABASE() |
| 199 |
AND referenced_table_schema = DATABASE() |
| 200 |
AND referenced_table_name IS NOT NULL |
| 201 |
AND referenced_column_name IS NOT NULL |
| 202 |
""", |
| 203 |
[table_name], |
| 204 |
) |
| 205 |
return { |
| 206 |
field_name: (other_field, other_table) |
| 207 |
for field_name, other_field, other_table in cursor.fetchall() |
| 208 |
} |
| 209 |
|
| 210 |
def get_storage_engine(self, cursor, table_name): |
| 211 |
""" |
| 212 |
Retrieve the storage engine for a given table. Return the default |
| 213 |
storage engine if the table doesn't exist. |
| 214 |
""" |
| 215 |
cursor.execute( |
| 216 |
""" |
| 217 |
SELECT engine |
| 218 |
FROM information_schema.tables |
| 219 |
WHERE |
| 220 |
table_name = %s AND |
| 221 |
table_schema = DATABASE() |
| 222 |
""", |
| 223 |
[table_name], |
| 224 |
) |
| 225 |
result = cursor.fetchone() |
| 226 |
if not result: |
| 227 |
return self.connection.features._mysql_storage_engine |
| 228 |
return result[0] |
| 229 |
|
| 230 |
def _parse_constraint_columns(self, check_clause, columns): |
| 231 |
check_columns = OrderedSet() |
| 232 |
statement = sqlparse.parse(check_clause)[0] |
| 233 |
tokens = (token for token in statement.flatten() if not token.is_whitespace) |
| 234 |
for token in tokens: |
| 235 |
if ( |
| 236 |
token.ttype == sqlparse.tokens.Name |
| 237 |
and self.connection.ops.quote_name(token.value) == token.value |
| 238 |
and token.value[1:-1] in columns |
| 239 |
): |
| 240 |
check_columns.add(token.value[1:-1]) |
| 241 |
return check_columns |
| 242 |
|
| 243 |
def get_constraints(self, cursor, table_name): |
| 244 |
""" |
| 245 |
Retrieve any constraints or keys (unique, pk, fk, check, index) across |
| 246 |
one or more columns. |
| 247 |
""" |
| 248 |
constraints = {} |
| 249 |
# Get the actual constraint names and columns |
| 250 |
name_query = """ |
| 251 |
SELECT kc.`constraint_name`, kc.`column_name`, |
| 252 |
kc.`referenced_table_name`, kc.`referenced_column_name`, |
| 253 |
c.`constraint_type` |
| 254 |
FROM |
| 255 |
information_schema.key_column_usage AS kc, |
| 256 |
information_schema.table_constraints AS c |
| 257 |
WHERE |
| 258 |
kc.table_schema = DATABASE() AND |
| 259 |
( |
| 260 |
kc.referenced_table_schema = DATABASE() OR |
| 261 |
kc.referenced_table_schema IS NULL |
| 262 |
) AND |
| 263 |
c.table_schema = kc.table_schema AND |
| 264 |
c.constraint_name = kc.constraint_name AND |
| 265 |
c.constraint_type != 'CHECK' AND |
| 266 |
kc.table_name = %s |
| 267 |
ORDER BY kc.`ordinal_position` |
| 268 |
""" |
| 269 |
cursor.execute(name_query, [table_name]) |
| 270 |
for constraint, column, ref_table, ref_column, kind in cursor.fetchall(): |
| 271 |
if constraint not in constraints: |
| 272 |
constraints[constraint] = { |
| 273 |
"columns": OrderedSet(), |
| 274 |
"primary_key": kind == "PRIMARY KEY", |
| 275 |
"unique": kind in {"PRIMARY KEY", "UNIQUE"}, |
| 276 |
"index": False, |
| 277 |
"check": False, |
| 278 |
"foreign_key": (ref_table, ref_column) if ref_column else None, |
| 279 |
} |
| 280 |
if self.connection.features.supports_index_column_ordering: |
| 281 |
constraints[constraint]["orders"] = [] |
| 282 |
constraints[constraint]["columns"].add(column) |
| 283 |
# Add check constraints. |
| 284 |
if self.connection.features.can_introspect_check_constraints: |
| 285 |
unnamed_constraints_index = 0 |
| 286 |
columns = { |
| 287 |
info.name for info in self.get_table_description(cursor, table_name) |
| 288 |
} |
| 289 |
if self.connection.mysql_is_mariadb: |
| 290 |
type_query = """ |
| 291 |
SELECT c.constraint_name, c.check_clause |
| 292 |
FROM information_schema.check_constraints AS c |
| 293 |
WHERE |
| 294 |
c.constraint_schema = DATABASE() AND |
| 295 |
c.table_name = %s |
| 296 |
""" |
| 297 |
else: |
| 298 |
type_query = """ |
| 299 |
SELECT cc.constraint_name, cc.check_clause |
| 300 |
FROM |
| 301 |
information_schema.check_constraints AS cc, |
| 302 |
information_schema.table_constraints AS tc |
| 303 |
WHERE |
| 304 |
cc.constraint_schema = DATABASE() AND |
| 305 |
tc.table_schema = cc.constraint_schema AND |
| 306 |
cc.constraint_name = tc.constraint_name AND |
| 307 |
tc.constraint_type = 'CHECK' AND |
| 308 |
tc.table_name = %s |
| 309 |
""" |
| 310 |
cursor.execute(type_query, [table_name]) |
| 311 |
for constraint, check_clause in cursor.fetchall(): |
| 312 |
constraint_columns = self._parse_constraint_columns( |
| 313 |
check_clause, columns |
| 314 |
) |
| 315 |
# Ensure uniqueness of unnamed constraints. Unnamed unique |
| 316 |
# and check columns constraints have the same name as |
| 317 |
# a column. |
| 318 |
if set(constraint_columns) == {constraint}: |
| 319 |
unnamed_constraints_index += 1 |
| 320 |
constraint = "__unnamed_constraint_%s__" % unnamed_constraints_index |
| 321 |
constraints[constraint] = { |
| 322 |
"columns": constraint_columns, |
| 323 |
"primary_key": False, |
| 324 |
"unique": False, |
| 325 |
"index": False, |
| 326 |
"check": True, |
| 327 |
"foreign_key": None, |
| 328 |
} |
| 329 |
# Now add in the indexes |
| 330 |
cursor.execute( |
| 331 |
"SHOW INDEX FROM %s" % self.connection.ops.quote_name(table_name) |
| 332 |
) |
| 333 |
for table, non_unique, index, colseq, column, order, type_ in [ |
| 334 |
x[:6] + (x[10],) for x in cursor.fetchall() |
| 335 |
]: |
| 336 |
if index not in constraints: |
| 337 |
constraints[index] = { |
| 338 |
"columns": OrderedSet(), |
| 339 |
"primary_key": False, |
| 340 |
"unique": not non_unique, |
| 341 |
"check": False, |
| 342 |
"foreign_key": None, |
| 343 |
} |
| 344 |
if self.connection.features.supports_index_column_ordering: |
| 345 |
constraints[index]["orders"] = [] |
| 346 |
constraints[index]["index"] = True |
| 347 |
constraints[index]["type"] = ( |
| 348 |
Index.suffix if type_ == "BTREE" else type_.lower() |
| 349 |
) |
| 350 |
constraints[index]["columns"].add(column) |
| 351 |
if self.connection.features.supports_index_column_ordering: |
| 352 |
constraints[index]["orders"].append("DESC" if order == "D" else "ASC") |
| 353 |
# Convert the sorted sets to lists |
| 354 |
for constraint in constraints.values(): |
| 355 |
constraint["columns"] = list(constraint["columns"]) |
| 356 |
return constraints |