Debian Codegraph @

django/db/backends/mysql/operations.py

raw
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 )