Debian Codegraph @

django/db/backends/mysql/base.py

raw
1 """
2 MySQL database backend for Django.
3
4 Requires mysqlclient: https://pypi.org/project/mysqlclient/
5 """
6
7 from django.core.exceptions import ImproperlyConfigured
8 from django.db import IntegrityError
9 from django.db.backends import utils as backend_utils
10 from django.db.backends.base.base import BaseDatabaseWrapper
11 from django.utils.asyncio import async_unsafe
12 from django.utils.functional import cached_property
13 from django.utils.regex_helper import _lazy_re_compile
14
15 try:
16 import MySQLdb as Database
17 except ImportError as err:
18 raise ImproperlyConfigured(
19 "Error loading MySQLdb module.\nDid you install mysqlclient?"
20 ) from err
21
22 from MySQLdb.constants import CLIENT, FIELD_TYPE
23 from MySQLdb.converters import conversions
24
25 # Some of these import MySQLdb, so import them after checking if it's installed.
26 from .client import DatabaseClient
27 from .creation import DatabaseCreation
28 from .features import DatabaseFeatures
29 from .introspection import DatabaseIntrospection
30 from .operations import DatabaseOperations
31 from .schema import DatabaseSchemaEditor
32 from .validation import DatabaseValidation
33
34 version = Database.version_info
35 if version < (1, 4, 3):
36 raise ImproperlyConfigured(
37 "mysqlclient 1.4.3 or newer is required; you have %s." % Database.__version__
38 )
39
40
41 # MySQLdb returns TIME columns as timedelta -- they are more like timedelta in
42 # terms of actual behavior as they are signed and include days -- and Django
43 # expects time.
44 django_conversions = {
45 **conversions,
46 **{FIELD_TYPE.TIME: backend_utils.typecast_time},
47 }
48
49 # This should match the numerical portion of the version numbers (we can treat
50 # versions like 5.0.24 and 5.0.24a as the same).
51 server_version_re = _lazy_re_compile(r"(\d{1,2})\.(\d{1,2})\.(\d{1,2})")
52
53
54 class CursorWrapper:
55 """
56 A thin wrapper around MySQLdb's normal cursor class that catches particular
57 exception instances and reraises them with the correct types.
58
59 Implemented as a wrapper, rather than a subclass, so that it isn't stuck
60 to the particular underlying representation returned by Connection.cursor().
61 """
62
63 codes_for_integrityerror = (
64 1048, # Column cannot be null
65 1690, # BIGINT UNSIGNED value is out of range
66 3819, # CHECK constraint is violated
67 4025, # CHECK constraint failed
68 )
69
70 def __init__(self, cursor):
71 self.cursor = cursor
72
73 def execute(self, query, args=None):
74 try:
75 # args is None means no string interpolation
76 return self.cursor.execute(query, args)
77 except Database.OperationalError as e:
78 # Map some error codes to IntegrityError, since they seem to be
79 # misclassified and Django would prefer the more logical place.
80 if e.args[0] in self.codes_for_integrityerror:
81 raise IntegrityError(*tuple(e.args))
82 raise
83
84 def executemany(self, query, args):
85 try:
86 return self.cursor.executemany(query, args)
87 except Database.OperationalError as e:
88 # Map some error codes to IntegrityError, since they seem to be
89 # misclassified and Django would prefer the more logical place.
90 if e.args[0] in self.codes_for_integrityerror:
91 raise IntegrityError(*tuple(e.args))
92 raise
93
94 def __getattr__(self, attr):
95 return getattr(self.cursor, attr)
96
97 def __iter__(self):
98 return iter(self.cursor)
99
100
101 class DatabaseWrapper(BaseDatabaseWrapper):
102 vendor = "mysql"
103 # This dictionary maps Field objects to their associated MySQL column
104 # types, as strings. Column-type strings can contain format strings; they'll
105 # be interpolated against the values of Field.__dict__ before being output.
106 # If a column type is set to None, it won't be included in the output.
107
108 _data_types = {
109 "AutoField": "integer AUTO_INCREMENT",
110 "BigAutoField": "bigint AUTO_INCREMENT",
111 "BinaryField": "longblob",
112 "BooleanField": "bool",
113 "CharField": "varchar(%(max_length)s)",
114 "DateField": "date",
115 "DateTimeField": "datetime(6)",
116 "DecimalField": "numeric(%(max_digits)s, %(decimal_places)s)",
117 "DurationField": "bigint",
118 "FileField": "varchar(%(max_length)s)",
119 "FilePathField": "varchar(%(max_length)s)",
120 "FloatField": "double precision",
121 "IntegerField": "integer",
122 "BigIntegerField": "bigint",
123 "IPAddressField": "char(15)",
124 "GenericIPAddressField": "char(39)",
125 "JSONField": "json",
126 "OneToOneField": "integer",
127 "PositiveBigIntegerField": "bigint UNSIGNED",
128 "PositiveIntegerField": "integer UNSIGNED",
129 "PositiveSmallIntegerField": "smallint UNSIGNED",
130 "SlugField": "varchar(%(max_length)s)",
131 "SmallAutoField": "smallint AUTO_INCREMENT",
132 "SmallIntegerField": "smallint",
133 "TextField": "longtext",
134 "TimeField": "time(6)",
135 "UUIDField": "char(32)",
136 }
137
138 @cached_property
139 def data_types(self):
140 _data_types = self._data_types.copy()
141 if self.features.has_native_uuid_field:
142 _data_types["UUIDField"] = "uuid"
143 return _data_types
144
145 # For these data types:
146 # - MySQL < 8.0.13 doesn't accept default values and implicitly treats them
147 # as nullable
148 # - all versions of MySQL and MariaDB don't support full width database
149 # indexes
150 _limited_data_types = (
151 "tinyblob",
152 "blob",
153 "mediumblob",
154 "longblob",
155 "tinytext",
156 "text",
157 "mediumtext",
158 "longtext",
159 "json",
160 )
161
162 operators = {
163 "exact": "= %s",
164 "iexact": "LIKE %s",
165 "contains": "LIKE BINARY %s",
166 "icontains": "LIKE %s",
167 "gt": "> %s",
168 "gte": ">= %s",
169 "lt": "< %s",
170 "lte": "<= %s",
171 "startswith": "LIKE BINARY %s",
172 "endswith": "LIKE BINARY %s",
173 "istartswith": "LIKE %s",
174 "iendswith": "LIKE %s",
175 }
176
177 # The patterns below are used to generate SQL pattern lookup clauses when
178 # the right-hand side of the lookup isn't a raw string (it might be an expression
179 # or the result of a bilateral transformation).
180 # In those cases, special characters for LIKE operators (e.g. \, *, _) should be
181 # escaped on database side.
182 #
183 # Note: we use str.format() here for readability as '%' is used as a wildcard for
184 # the LIKE operator.
185 pattern_esc = r"REPLACE(REPLACE(REPLACE({}, '\\', '\\\\'), '%%', '\%%'), '_', '\_')"
186 pattern_ops = {
187 "contains": "LIKE BINARY CONCAT('%%', {}, '%%')",
188 "icontains": "LIKE CONCAT('%%', {}, '%%')",
189 "startswith": "LIKE BINARY CONCAT({}, '%%')",
190 "istartswith": "LIKE CONCAT({}, '%%')",
191 "endswith": "LIKE BINARY CONCAT('%%', {})",
192 "iendswith": "LIKE CONCAT('%%', {})",
193 }
194
195 isolation_levels = {
196 "read uncommitted",
197 "read committed",
198 "repeatable read",
199 "serializable",
200 }
201
202 Database = Database
203 SchemaEditorClass = DatabaseSchemaEditor
204 # Classes instantiated in __init__().
205 client_class = DatabaseClient
206 creation_class = DatabaseCreation
207 features_class = DatabaseFeatures
208 introspection_class = DatabaseIntrospection
209 ops_class = DatabaseOperations
210 validation_class = DatabaseValidation
211
212 def get_database_version(self):
213 return self.mysql_version
214
215 def get_connection_params(self):
216 kwargs = {
217 "conv": django_conversions,
218 "charset": "utf8mb4",
219 }
220 settings_dict = self.settings_dict
221 if settings_dict["USER"]:
222 kwargs["user"] = settings_dict["USER"]
223 if settings_dict["NAME"]:
224 kwargs["database"] = settings_dict["NAME"]
225 if settings_dict["PASSWORD"]:
226 kwargs["password"] = settings_dict["PASSWORD"]
227 if settings_dict["HOST"].startswith("/"):
228 kwargs["unix_socket"] = settings_dict["HOST"]
229 elif settings_dict["HOST"]:
230 kwargs["host"] = settings_dict["HOST"]
231 if settings_dict["PORT"]:
232 kwargs["port"] = int(settings_dict["PORT"])
233 # We need the number of potentially affected rows after an
234 # "UPDATE", not the number of changed rows.
235 kwargs["client_flag"] = CLIENT.FOUND_ROWS
236 # Validate the transaction isolation level, if specified.
237 options = settings_dict["OPTIONS"].copy()
238 isolation_level = options.pop("isolation_level", "read committed")
239 if isolation_level:
240 isolation_level = isolation_level.lower()
241 if isolation_level not in self.isolation_levels:
242 raise ImproperlyConfigured(
243 "Invalid transaction isolation level '%s' specified.\n"
244 "Use one of %s, or None."
245 % (
246 isolation_level,
247 ", ".join("'%s'" % s for s in sorted(self.isolation_levels)),
248 )
249 )
250 self.isolation_level = isolation_level
251 kwargs.update(options)
252 return kwargs
253
254 @async_unsafe
255 def get_new_connection(self, conn_params):
256 connection = Database.connect(**conn_params)
257 # bytes encoder in mysqlclient doesn't work and was added only to
258 # prevent KeyErrors in Django < 2.0. We can remove this workaround when
259 # mysqlclient 2.1 becomes the minimal mysqlclient supported by Django.
260 # See https://github.com/PyMySQL/mysqlclient/issues/489
261 if connection.encoders.get(bytes) is bytes:
262 connection.encoders.pop(bytes)
263 return connection
264
265 def init_connection_state(self):
266 super().init_connection_state()
267 assignments = []
268 if self.features.is_sql_auto_is_null_enabled:
269 # SQL_AUTO_IS_NULL controls whether an AUTO_INCREMENT column on
270 # a recently inserted row will return when the field is tested
271 # for NULL. Disabling this brings this aspect of MySQL in line
272 # with SQL standards.
273 assignments.append("SET SQL_AUTO_IS_NULL = 0")
274
275 if self.isolation_level:
276 assignments.append(
277 "SET SESSION TRANSACTION ISOLATION LEVEL %s"
278 % self.isolation_level.upper()
279 )
280
281 if assignments:
282 with self.cursor() as cursor:
283 cursor.execute("; ".join(assignments))
284
285 @async_unsafe
286 def create_cursor(self, name=None):
287 cursor = self.connection.cursor()
288 return CursorWrapper(cursor)
289
290 def _rollback(self):
291 try:
292 BaseDatabaseWrapper._rollback(self)
293 except Database.NotSupportedError:
294 pass
295
296 def _set_autocommit(self, autocommit):
297 with self.wrap_database_errors:
298 self.connection.autocommit(autocommit)
299
300 def disable_constraint_checking(self):
301 """
302 Disable foreign key checks, primarily for use in adding rows with
303 forward references. Always return True to indicate constraint checks
304 need to be re-enabled.
305 """
306 with self.cursor() as cursor:
307 cursor.execute("SET foreign_key_checks=0")
308 return True
309
310 def enable_constraint_checking(self):
311 """
312 Re-enable foreign key checks after they have been disabled.
313 """
314 # Override needs_rollback in case constraint_checks_disabled is
315 # nested inside transaction.atomic.
316 self.needs_rollback, needs_rollback = False, self.needs_rollback
317 try:
318 with self.cursor() as cursor:
319 cursor.execute("SET foreign_key_checks=1")
320 finally:
321 self.needs_rollback = needs_rollback
322
323 def check_constraints(self, table_names=None):
324 """
325 Check each table name in `table_names` for rows with invalid foreign
326 key references. This method is intended to be used in conjunction with
327 `disable_constraint_checking()` and `enable_constraint_checking()`, to
328 determine if rows with invalid references were entered while constraint
329 checks were off.
330 """
331 with self.cursor() as cursor:
332 if table_names is None:
333 table_names = self.introspection.table_names(cursor)
334 for table_name in table_names:
335 primary_key_column_name = self.introspection.get_primary_key_column(
336 cursor, table_name
337 )
338 if not primary_key_column_name:
339 continue
340 relations = self.introspection.get_relations(cursor, table_name)
341 for column_name, (
342 referenced_column_name,
343 referenced_table_name,
344 ) in relations.items():
345 cursor.execute(
346 """
347 SELECT REFERRING.`%s`, REFERRING.`%s` FROM `%s` as REFERRING
348 LEFT JOIN `%s` as REFERRED
349 ON (REFERRING.`%s` = REFERRED.`%s`)
350 WHERE REFERRING.`%s` IS NOT NULL AND REFERRED.`%s` IS NULL
351 """
352 % (
353 primary_key_column_name,
354 column_name,
355 table_name,
356 referenced_table_name,
357 column_name,
358 referenced_column_name,
359 column_name,
360 referenced_column_name,
361 )
362 )
363 for bad_row in cursor.fetchall():
364 raise IntegrityError(
365 "The row in table '%s' with primary key '%s' has an "
366 "invalid foreign key: %s.%s contains a value '%s' that "
367 "does not have a corresponding value in %s.%s."
368 % (
369 table_name,
370 bad_row[0],
371 table_name,
372 column_name,
373 bad_row[1],
374 referenced_table_name,
375 referenced_column_name,
376 )
377 )
378
379 def is_usable(self):
380 try:
381 self.connection.ping()
382 except Database.Error:
383 return False
384 else:
385 return True
386
387 @cached_property
388 def display_name(self):
389 return "MariaDB" if self.mysql_is_mariadb else "MySQL"
390
391 @cached_property
392 def data_type_check_constraints(self):
393 if self.features.supports_column_check_constraints:
394 check_constraints = {
395 "PositiveBigIntegerField": "`%(column)s` >= 0",
396 "PositiveIntegerField": "`%(column)s` >= 0",
397 "PositiveSmallIntegerField": "`%(column)s` >= 0",
398 }
399 return check_constraints
400 return {}
401
402 @cached_property
403 def mysql_server_data(self):
404 with self.temporary_connection() as cursor:
405 # Select some server variables and test if the time zone
406 # definitions are installed. CONVERT_TZ returns NULL if 'UTC'
407 # timezone isn't loaded into the mysql.time_zone table.
408 cursor.execute("""
409 SELECT VERSION(),
410 @@sql_mode,
411 @@default_storage_engine,
412 @@sql_auto_is_null,
413 @@lower_case_table_names,
414 CONVERT_TZ('2001-01-01 01:00:00', 'UTC', 'UTC') IS NOT NULL
415 """)
416 row = cursor.fetchone()
417 return {
418 "version": row[0],
419 "sql_mode": row[1],
420 "default_storage_engine": row[2],
421 "sql_auto_is_null": bool(row[3]),
422 "lower_case_table_names": bool(row[4]),
423 "has_zoneinfo_database": bool(row[5]),
424 }
425
426 @cached_property
427 def mysql_server_info(self):
428 return self.mysql_server_data["version"]
429
430 @cached_property
431 def mysql_version(self):
432 match = server_version_re.match(self.mysql_server_info)
433 if not match:
434 raise Exception(
435 "Unable to determine MySQL version from version string %r"
436 % self.mysql_server_info
437 )
438 return tuple(int(x) for x in match.groups())
439
440 @cached_property
441 def mysql_is_mariadb(self):
442 return "mariadb" in self.mysql_server_info.lower()
443
444 @cached_property
445 def sql_mode(self):
446 sql_mode = self.mysql_server_data["sql_mode"]
447 return set(sql_mode.split(",") if sql_mode else ())