| 1 |
import uuid |
| 2 |
|
| 3 |
from django.conf import settings |
| 4 |
from django.db.backends.base.operations import BaseDatabaseOperations |
| 5 |
from django.db.backends.utils import split_tzname_delta |
| 6 |
from django.db.models import Exists, ExpressionWrapper, Lookup |
| 7 |
from django.db.models.constants import OnConflict |
| 8 |
from django.utils import timezone |
| 9 |
from django.utils.encoding import force_str |
| 10 |
from django.utils.regex_helper import _lazy_re_compile |
| 11 |
|
| 12 |
|
| 13 |
class DatabaseOperations(BaseDatabaseOperations): |
| 14 |
compiler_module = "django.db.backends.mysql.compiler" |
| 15 |
|
| 16 |
# MySQL stores positive fields as UNSIGNED ints. |
| 17 |
integer_field_ranges = { |
| 18 |
**BaseDatabaseOperations.integer_field_ranges, |
| 19 |
"PositiveSmallIntegerField": (0, 65535), |
| 20 |
"PositiveIntegerField": (0, 4294967295), |
| 21 |
"PositiveBigIntegerField": (0, 18446744073709551615), |
| 22 |
} |
| 23 |
cast_data_types = { |
| 24 |
"AutoField": "signed integer", |
| 25 |
"BigAutoField": "signed integer", |
| 26 |
"SmallAutoField": "signed integer", |
| 27 |
"CharField": "char(%(max_length)s)", |
| 28 |
"DecimalField": "decimal(%(max_digits)s, %(decimal_places)s)", |
| 29 |
"TextField": "char", |
| 30 |
"IntegerField": "signed integer", |
| 31 |
"BigIntegerField": "signed integer", |
| 32 |
"SmallIntegerField": "signed integer", |
| 33 |
"PositiveBigIntegerField": "unsigned integer", |
| 34 |
"PositiveIntegerField": "unsigned integer", |
| 35 |
"PositiveSmallIntegerField": "unsigned integer", |
| 36 |
"DurationField": "signed integer", |
| 37 |
} |
| 38 |
cast_char_field_without_max_length = "char" |
| 39 |
explain_prefix = "EXPLAIN" |
| 40 |
|
| 41 |
# EXTRACT format cannot be passed in parameters. |
| 42 |
_extract_format_re = _lazy_re_compile(r"[A-Z_]+") |
| 43 |
|
| 44 |
def date_extract_sql(self, lookup_type, sql, params): |
| 45 |
# https://dev.mysql.com/doc/mysql/en/date-and-time-functions.html |
| 46 |
if lookup_type == "week_day": |
| 47 |
# DAYOFWEEK() returns an integer, 1-7, Sunday=1. |
| 48 |
return f"DAYOFWEEK({sql})", params |
| 49 |
elif lookup_type == "iso_week_day": |
| 50 |
# WEEKDAY() returns an integer, 0-6, Monday=0. |
| 51 |
return f"WEEKDAY({sql}) + 1", params |
| 52 |
elif lookup_type == "week": |
| 53 |
# Override the value of default_week_format for consistency with |
| 54 |
# other database backends. |
| 55 |
# Mode 3: Monday, 1-53, with 4 or more days this year. |
| 56 |
return f"WEEK({sql}, 3)", params |
| 57 |
elif lookup_type == "iso_year": |
| 58 |
# Get the year part from the YEARWEEK function, which returns a |
| 59 |
# number as year * 100 + week. |
| 60 |
return f"TRUNCATE(YEARWEEK({sql}, 3), -2) / 100", params |
| 61 |
else: |
| 62 |
# EXTRACT returns 1-53 based on ISO-8601 for the week number. |
| 63 |
lookup_type = lookup_type.upper() |
| 64 |
if not self._extract_format_re.fullmatch(lookup_type): |
| 65 |
raise ValueError(f"Invalid loookup type: {lookup_type!r}") |
| 66 |
return f"EXTRACT({lookup_type} FROM {sql})", params |
| 67 |
|
| 68 |
def date_trunc_sql(self, lookup_type, sql, params, tzname=None): |
| 69 |
sql, params = self._convert_sql_to_tz(sql, params, tzname) |
| 70 |
fields = { |
| 71 |
"year": "%Y-01-01", |
| 72 |
"month": "%Y-%m-01", |
| 73 |
} |
| 74 |
if lookup_type in fields: |
| 75 |
format_str = fields[lookup_type] |
| 76 |
return f"CAST(DATE_FORMAT({sql}, %s) AS DATE)", (*params, format_str) |
| 77 |
elif lookup_type == "quarter": |
| 78 |
return ( |
| 79 |
f"MAKEDATE(YEAR({sql}), 1) + " |
| 80 |
f"INTERVAL QUARTER({sql}) QUARTER - INTERVAL 1 QUARTER", |
| 81 |
(*params, *params), |
| 82 |
) |
| 83 |
elif lookup_type == "week": |
| 84 |
return f"DATE_SUB({sql}, INTERVAL WEEKDAY({sql}) DAY)", (*params, *params) |
| 85 |
else: |
| 86 |
return f"DATE({sql})", params |
| 87 |
|
| 88 |
def _prepare_tzname_delta(self, tzname): |
| 89 |
tzname, sign, offset = split_tzname_delta(tzname) |
| 90 |
return f"{sign}{offset}" if offset else tzname |
| 91 |
|
| 92 |
def _convert_sql_to_tz(self, sql, params, tzname): |
| 93 |
if tzname and settings.USE_TZ and self.connection.timezone_name != tzname: |
| 94 |
return f"CONVERT_TZ({sql}, %s, %s)", ( |
| 95 |
*params, |
| 96 |
self.connection.timezone_name, |
| 97 |
self._prepare_tzname_delta(tzname), |
| 98 |
) |
| 99 |
return sql, params |
| 100 |
|
| 101 |
def datetime_cast_date_sql(self, sql, params, tzname): |
| 102 |
sql, params = self._convert_sql_to_tz(sql, params, tzname) |
| 103 |
return f"DATE({sql})", params |
| 104 |
|
| 105 |
def datetime_cast_time_sql(self, sql, params, tzname): |
| 106 |
sql, params = self._convert_sql_to_tz(sql, params, tzname) |
| 107 |
return f"TIME({sql})", params |
| 108 |
|
| 109 |
def datetime_extract_sql(self, lookup_type, sql, params, tzname): |
| 110 |
sql, params = self._convert_sql_to_tz(sql, params, tzname) |
| 111 |
return self.date_extract_sql(lookup_type, sql, params) |
| 112 |
|
| 113 |
def datetime_trunc_sql(self, lookup_type, sql, params, tzname): |
| 114 |
sql, params = self._convert_sql_to_tz(sql, params, tzname) |
| 115 |
fields = ["year", "month", "day", "hour", "minute", "second"] |
| 116 |
format = ("%Y-", "%m", "-%d", " %H:", "%i", ":%s") |
| 117 |
format_def = ("0000-", "01", "-01", " 00:", "00", ":00") |
| 118 |
if lookup_type == "quarter": |
| 119 |
return ( |
| 120 |
f"CAST(DATE_FORMAT(MAKEDATE(YEAR({sql}), 1) + " |
| 121 |
f"INTERVAL QUARTER({sql}) QUARTER - " |
| 122 |
f"INTERVAL 1 QUARTER, %s) AS DATETIME)" |
| 123 |
), (*params, *params, "%Y-%m-01 00:00:00") |
| 124 |
if lookup_type == "week": |
| 125 |
return ( |
| 126 |
f"CAST(DATE_FORMAT(" |
| 127 |
f"DATE_SUB({sql}, INTERVAL WEEKDAY({sql}) DAY), %s) AS DATETIME)" |
| 128 |
), (*params, *params, "%Y-%m-%d 00:00:00") |
| 129 |
try: |
| 130 |
i = fields.index(lookup_type) + 1 |
| 131 |
except ValueError: |
| 132 |
pass |
| 133 |
else: |
| 134 |
format_str = "".join(format[:i] + format_def[i:]) |
| 135 |
return f"CAST(DATE_FORMAT({sql}, %s) AS DATETIME)", (*params, format_str) |
| 136 |
return sql, params |
| 137 |
|
| 138 |
def time_trunc_sql(self, lookup_type, sql, params, tzname=None): |
| 139 |
sql, params = self._convert_sql_to_tz(sql, params, tzname) |
| 140 |
fields = { |
| 141 |
"hour": "%H:00:00", |
| 142 |
"minute": "%H:%i:00", |
| 143 |
"second": "%H:%i:%s", |
| 144 |
} |
| 145 |
if lookup_type in fields: |
| 146 |
format_str = fields[lookup_type] |
| 147 |
return f"CAST(DATE_FORMAT({sql}, %s) AS TIME)", (*params, format_str) |
| 148 |
else: |
| 149 |
return f"TIME({sql})", params |
| 150 |
|
| 151 |
def fetch_returned_insert_rows(self, cursor): |
| 152 |
""" |
| 153 |
Given a cursor object that has just performed an INSERT...RETURNING |
| 154 |
statement into a table, return the tuple of returned data. |
| 155 |
""" |
| 156 |
return cursor.fetchall() |
| 157 |
|
| 158 |
def format_for_duration_arithmetic(self, sql): |
| 159 |
return "INTERVAL %s MICROSECOND" % sql |
| 160 |
|
| 161 |
def force_no_ordering(self): |
| 162 |
""" |
| 163 |
"ORDER BY NULL" prevents MySQL from implicitly ordering by grouped |
| 164 |
columns. If no ordering would otherwise be applied, we don't want any |
| 165 |
implicit sorting going on. |
| 166 |
""" |
| 167 |
return [(None, ("NULL", [], False))] |
| 168 |
|
| 169 |
def last_executed_query(self, cursor, sql, params): |
| 170 |
# With MySQLdb, cursor objects have an (undocumented) "_executed" |
| 171 |
# attribute where the exact query sent to the database is saved. |
| 172 |
# See MySQLdb/cursors.py in the source distribution. |
| 173 |
# MySQLdb returns string, PyMySQL bytes. |
| 174 |
return force_str(getattr(cursor, "_executed", None), errors="replace") |
| 175 |
|
| 176 |
def no_limit_value(self): |
| 177 |
# 2**64 - 1, as recommended by the MySQL documentation |
| 178 |
return 18446744073709551615 |
| 179 |
|
| 180 |
def quote_name(self, name): |
| 181 |
if name.startswith("`") and name.endswith("`"): |
| 182 |
return name # Quoting once is enough. |
| 183 |
return "`%s`" % name |
| 184 |
|
| 185 |
def return_insert_columns(self, fields): |
| 186 |
# MySQL doesn't support an INSERT...RETURNING statement. |
| 187 |
if not fields: |
| 188 |
return "", () |
| 189 |
columns = [ |
| 190 |
"%s.%s" |
| 191 |
% ( |
| 192 |
self.quote_name(field.model._meta.db_table), |
| 193 |
self.quote_name(field.column), |
| 194 |
) |
| 195 |
for field in fields |
| 196 |
] |
| 197 |
return "RETURNING %s" % ", ".join(columns), () |
| 198 |
|
| 199 |
def sql_flush(self, style, tables, *, reset_sequences=False, allow_cascade=False): |
| 200 |
if not tables: |
| 201 |
return [] |
| 202 |
|
| 203 |
sql = ["SET FOREIGN_KEY_CHECKS = 0;"] |
| 204 |
if reset_sequences: |
| 205 |
# It's faster to TRUNCATE tables that require a sequence reset |
| 206 |
# since ALTER TABLE AUTO_INCREMENT is slower than TRUNCATE. |
| 207 |
sql.extend( |
| 208 |
"%s %s;" |
| 209 |
% ( |
| 210 |
style.SQL_KEYWORD("TRUNCATE"), |
| 211 |
style.SQL_FIELD(self.quote_name(table_name)), |
| 212 |
) |
| 213 |
for table_name in tables |
| 214 |
) |
| 215 |
else: |
| 216 |
# Otherwise issue a simple DELETE since it's faster than TRUNCATE |
| 217 |
# and preserves sequences. |
| 218 |
sql.extend( |
| 219 |
"%s %s %s;" |
| 220 |
% ( |
| 221 |
style.SQL_KEYWORD("DELETE"), |
| 222 |
style.SQL_KEYWORD("FROM"), |
| 223 |
style.SQL_FIELD(self.quote_name(table_name)), |
| 224 |
) |
| 225 |
for table_name in tables |
| 226 |
) |
| 227 |
sql.append("SET FOREIGN_KEY_CHECKS = 1;") |
| 228 |
return sql |
| 229 |
|
| 230 |
def sequence_reset_by_name_sql(self, style, sequences): |
| 231 |
return [ |
| 232 |
"%s %s %s %s = 1;" |
| 233 |
% ( |
| 234 |
style.SQL_KEYWORD("ALTER"), |
| 235 |
style.SQL_KEYWORD("TABLE"), |
| 236 |
style.SQL_FIELD(self.quote_name(sequence_info["table"])), |
| 237 |
style.SQL_FIELD("AUTO_INCREMENT"), |
| 238 |
) |
| 239 |
for sequence_info in sequences |
| 240 |
] |
| 241 |
|
| 242 |
def validate_autopk_value(self, value): |
| 243 |
# Zero in AUTO_INCREMENT field does not work without the |
| 244 |
# NO_AUTO_VALUE_ON_ZERO SQL mode. |
| 245 |
if value == 0 and not self.connection.features.allows_auto_pk_0: |
| 246 |
raise ValueError( |
| 247 |
"The database backend does not accept 0 as a value for AutoField." |
| 248 |
) |
| 249 |
return value |
| 250 |
|
| 251 |
def adapt_datetimefield_value(self, value): |
| 252 |
if value is None: |
| 253 |
return None |
| 254 |
|
| 255 |
# Expression values are adapted by the database. |
| 256 |
if hasattr(value, "resolve_expression"): |
| 257 |
return value |
| 258 |
|
| 259 |
# MySQL doesn't support tz-aware datetimes |
| 260 |
if timezone.is_aware(value): |
| 261 |
if settings.USE_TZ: |
| 262 |
value = timezone.make_naive(value, self.connection.timezone) |
| 263 |
else: |
| 264 |
raise ValueError( |
| 265 |
"MySQL backend does not support timezone-aware datetimes when " |
| 266 |
"USE_TZ is False." |
| 267 |
) |
| 268 |
return str(value) |
| 269 |
|
| 270 |
def adapt_timefield_value(self, value): |
| 271 |
if value is None: |
| 272 |
return None |
| 273 |
|
| 274 |
# Expression values are adapted by the database. |
| 275 |
if hasattr(value, "resolve_expression"): |
| 276 |
return value |
| 277 |
|
| 278 |
# MySQL doesn't support tz-aware times |
| 279 |
if timezone.is_aware(value): |
| 280 |
raise ValueError("MySQL backend does not support timezone-aware times.") |
| 281 |
|
| 282 |
return value.isoformat(timespec="microseconds") |
| 283 |
|
| 284 |
def max_name_length(self): |
| 285 |
return 64 |
| 286 |
|
| 287 |
def pk_default_value(self): |
| 288 |
return "NULL" |
| 289 |
|
| 290 |
def combine_expression(self, connector, sub_expressions): |
| 291 |
if connector == "^": |
| 292 |
return "POW(%s)" % ",".join(sub_expressions) |
| 293 |
# Convert the result to a signed integer since MySQL's binary operators |
| 294 |
# return an unsigned integer. |
| 295 |
elif connector in ("&", "|", "<<", "#"): |
| 296 |
connector = "^" if connector == "#" else connector |
| 297 |
return "CONVERT(%s, SIGNED)" % connector.join(sub_expressions) |
| 298 |
elif connector == ">>": |
| 299 |
lhs, rhs = sub_expressions |
| 300 |
return "FLOOR(%(lhs)s / POW(2, %(rhs)s))" % {"lhs": lhs, "rhs": rhs} |
| 301 |
return super().combine_expression(connector, sub_expressions) |
| 302 |
|
| 303 |
def get_db_converters(self, expression): |
| 304 |
converters = super().get_db_converters(expression) |
| 305 |
internal_type = expression.output_field.get_internal_type() |
| 306 |
if internal_type == "BooleanField": |
| 307 |
converters.append(self.convert_booleanfield_value) |
| 308 |
elif internal_type == "DateTimeField": |
| 309 |
if settings.USE_TZ: |
| 310 |
converters.append(self.convert_datetimefield_value) |
| 311 |
elif internal_type == "UUIDField": |
| 312 |
converters.append(self.convert_uuidfield_value) |
| 313 |
return converters |
| 314 |
|
| 315 |
def convert_booleanfield_value(self, value, expression, connection): |
| 316 |
if value in (0, 1): |
| 317 |
value = bool(value) |
| 318 |
return value |
| 319 |
|
| 320 |
def convert_datetimefield_value(self, value, expression, connection): |
| 321 |
if value is not None: |
| 322 |
value = timezone.make_aware(value, self.connection.timezone) |
| 323 |
return value |
| 324 |
|
| 325 |
def convert_uuidfield_value(self, value, expression, connection): |
| 326 |
if value is not None: |
| 327 |
value = uuid.UUID(value) |
| 328 |
return value |
| 329 |
|
| 330 |
def binary_placeholder_sql(self, value): |
| 331 |
return ( |
| 332 |
"_binary %s" if value is not None and not hasattr(value, "as_sql") else "%s" |
| 333 |
) |
| 334 |
|
| 335 |
def subtract_temporals(self, internal_type, lhs, rhs): |
| 336 |
lhs_sql, lhs_params = lhs |
| 337 |
rhs_sql, rhs_params = rhs |
| 338 |
if internal_type == "TimeField": |
| 339 |
if self.connection.mysql_is_mariadb: |
| 340 |
# MariaDB includes the microsecond component in TIME_TO_SEC as |
| 341 |
# a decimal. MySQL returns an integer without microseconds. |
| 342 |
return ( |
| 343 |
"CAST((TIME_TO_SEC(%(lhs)s) - TIME_TO_SEC(%(rhs)s)) " |
| 344 |
"* 1000000 AS SIGNED)" |
| 345 |
) % { |
| 346 |
"lhs": lhs_sql, |
| 347 |
"rhs": rhs_sql, |
| 348 |
}, ( |
| 349 |
*lhs_params, |
| 350 |
*rhs_params, |
| 351 |
) |
| 352 |
return ( |
| 353 |
"((TIME_TO_SEC(%(lhs)s) * 1000000 + MICROSECOND(%(lhs)s)) -" |
| 354 |
" (TIME_TO_SEC(%(rhs)s) * 1000000 + MICROSECOND(%(rhs)s)))" |
| 355 |
) % {"lhs": lhs_sql, "rhs": rhs_sql}, tuple(lhs_params) * 2 + tuple( |
| 356 |
rhs_params |
| 357 |
) * 2 |
| 358 |
params = (*rhs_params, *lhs_params) |
| 359 |
return "TIMESTAMPDIFF(MICROSECOND, %s, %s)" % (rhs_sql, lhs_sql), params |
| 360 |
|
| 361 |
def explain_query_prefix(self, format=None, **options): |
| 362 |
# Alias MySQL's TRADITIONAL to TEXT for consistency with other backends. |
| 363 |
if format and format.upper() == "TEXT": |
| 364 |
format = "TRADITIONAL" |
| 365 |
elif ( |
| 366 |
not format and "TREE" in self.connection.features.supported_explain_formats |
| 367 |
): |
| 368 |
# Use TREE by default (if supported) as it's more informative. |
| 369 |
format = "TREE" |
| 370 |
analyze = options.pop("analyze", False) |
| 371 |
prefix = super().explain_query_prefix(format, **options) |
| 372 |
if analyze and self.connection.features.supports_explain_analyze: |
| 373 |
# MariaDB uses ANALYZE instead of EXPLAIN ANALYZE. |
| 374 |
prefix = ( |
| 375 |
"ANALYZE" if self.connection.mysql_is_mariadb else prefix + " ANALYZE" |
| 376 |
) |
| 377 |
if format and not (analyze and not self.connection.mysql_is_mariadb): |
| 378 |
# Only MariaDB supports the analyze option with formats. |
| 379 |
prefix += " FORMAT=%s" % format |
| 380 |
return prefix |
| 381 |
|
| 382 |
def regex_lookup(self, lookup_type): |
| 383 |
# REGEXP_LIKE doesn't exist in MariaDB. |
| 384 |
if self.connection.mysql_is_mariadb: |
| 385 |
if lookup_type == "regex": |
| 386 |
return "%s REGEXP BINARY %s" |
| 387 |
return "%s REGEXP %s" |
| 388 |
|
| 389 |
match_option = "c" if lookup_type == "regex" else "i" |
| 390 |
return "REGEXP_LIKE(%%s, %%s, '%s')" % match_option |
| 391 |
|
| 392 |
def insert_statement(self, on_conflict=None): |
| 393 |
if on_conflict == OnConflict.IGNORE: |
| 394 |
return "INSERT IGNORE INTO" |
| 395 |
return super().insert_statement(on_conflict=on_conflict) |
| 396 |
|
| 397 |
def lookup_cast(self, lookup_type, internal_type=None): |
| 398 |
lookup = "%s" |
| 399 |
if internal_type == "JSONField": |
| 400 |
if self.connection.mysql_is_mariadb or lookup_type in ( |
| 401 |
"iexact", |
| 402 |
"contains", |
| 403 |
"icontains", |
| 404 |
"startswith", |
| 405 |
"istartswith", |
| 406 |
"endswith", |
| 407 |
"iendswith", |
| 408 |
"regex", |
| 409 |
"iregex", |
| 410 |
): |
| 411 |
lookup = "JSON_UNQUOTE(%s)" |
| 412 |
return lookup |
| 413 |
|
| 414 |
def conditional_expression_supported_in_where_clause(self, expression): |
| 415 |
# MySQL ignores indexes with boolean fields unless they're compared |
| 416 |
# directly to a boolean value. |
| 417 |
if isinstance(expression, (Exists, Lookup)): |
| 418 |
return True |
| 419 |
if isinstance(expression, ExpressionWrapper) and expression.conditional: |
| 420 |
return self.conditional_expression_supported_in_where_clause( |
| 421 |
expression.expression |
| 422 |
) |
| 423 |
if getattr(expression, "conditional", False): |
| 424 |
return False |
| 425 |
return super().conditional_expression_supported_in_where_clause(expression) |
| 426 |
|
| 427 |
def on_conflict_suffix_sql(self, fields, on_conflict, update_fields, unique_fields): |
| 428 |
if on_conflict == OnConflict.UPDATE: |
| 429 |
conflict_suffix_sql = "ON DUPLICATE KEY UPDATE %(fields)s" |
| 430 |
# The use of VALUES() is deprecated in MySQL 8.0.20+. Instead, use |
| 431 |
# aliases for the new row and its columns available in MySQL |
| 432 |
# 8.0.19+. |
| 433 |
if not self.connection.mysql_is_mariadb: |
| 434 |
if self.connection.mysql_version >= (8, 0, 19): |
| 435 |
conflict_suffix_sql = f"AS new {conflict_suffix_sql}" |
| 436 |
field_sql = "%(field)s = new.%(field)s" |
| 437 |
else: |
| 438 |
field_sql = "%(field)s = VALUES(%(field)s)" |
| 439 |
# Use VALUE() on MariaDB. |
| 440 |
else: |
| 441 |
field_sql = "%(field)s = VALUE(%(field)s)" |
| 442 |
|
| 443 |
fields = ", ".join( |
| 444 |
[ |
| 445 |
field_sql % {"field": field} |
| 446 |
for field in map(self.quote_name, update_fields) |
| 447 |
] |
| 448 |
) |
| 449 |
return conflict_suffix_sql % {"fields": fields} |
| 450 |
return super().on_conflict_suffix_sql( |
| 451 |
fields, |
| 452 |
on_conflict, |
| 453 |
update_fields, |
| 454 |
unique_fields, |
| 455 |
) |