operations.py 8.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206
  1. from __future__ import unicode_literals
  2. import uuid
  3. from django.conf import settings
  4. from django.db.backends.base.operations import BaseDatabaseOperations
  5. from django.utils import six, timezone
  6. from django.utils.encoding import force_text
  7. class DatabaseOperations(BaseDatabaseOperations):
  8. compiler_module = "django.db.backends.mysql.compiler"
  9. # MySQL stores positive fields as UNSIGNED ints.
  10. integer_field_ranges = dict(BaseDatabaseOperations.integer_field_ranges,
  11. PositiveSmallIntegerField=(0, 65535),
  12. PositiveIntegerField=(0, 4294967295),
  13. )
  14. def date_extract_sql(self, lookup_type, field_name):
  15. # http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
  16. if lookup_type == 'week_day':
  17. # DAYOFWEEK() returns an integer, 1-7, Sunday=1.
  18. # Note: WEEKDAY() returns 0-6, Monday=0.
  19. return "DAYOFWEEK(%s)" % field_name
  20. else:
  21. return "EXTRACT(%s FROM %s)" % (lookup_type.upper(), field_name)
  22. def date_trunc_sql(self, lookup_type, field_name):
  23. fields = ['year', 'month', 'day', 'hour', 'minute', 'second']
  24. format = ('%%Y-', '%%m', '-%%d', ' %%H:', '%%i', ':%%s') # Use double percents to escape.
  25. format_def = ('0000-', '01', '-01', ' 00:', '00', ':00')
  26. try:
  27. i = fields.index(lookup_type) + 1
  28. except ValueError:
  29. sql = field_name
  30. else:
  31. format_str = ''.join([f for f in format[:i]] + [f for f in format_def[i:]])
  32. sql = "CAST(DATE_FORMAT(%s, '%s') AS DATETIME)" % (field_name, format_str)
  33. return sql
  34. def datetime_extract_sql(self, lookup_type, field_name, tzname):
  35. if settings.USE_TZ:
  36. field_name = "CONVERT_TZ(%s, 'UTC', %%s)" % field_name
  37. params = [tzname]
  38. else:
  39. params = []
  40. # http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
  41. if lookup_type == 'week_day':
  42. # DAYOFWEEK() returns an integer, 1-7, Sunday=1.
  43. # Note: WEEKDAY() returns 0-6, Monday=0.
  44. sql = "DAYOFWEEK(%s)" % field_name
  45. else:
  46. sql = "EXTRACT(%s FROM %s)" % (lookup_type.upper(), field_name)
  47. return sql, params
  48. def datetime_trunc_sql(self, lookup_type, field_name, tzname):
  49. if settings.USE_TZ:
  50. field_name = "CONVERT_TZ(%s, 'UTC', %%s)" % field_name
  51. params = [tzname]
  52. else:
  53. params = []
  54. fields = ['year', 'month', 'day', 'hour', 'minute', 'second']
  55. format = ('%%Y-', '%%m', '-%%d', ' %%H:', '%%i', ':%%s') # Use double percents to escape.
  56. format_def = ('0000-', '01', '-01', ' 00:', '00', ':00')
  57. try:
  58. i = fields.index(lookup_type) + 1
  59. except ValueError:
  60. sql = field_name
  61. else:
  62. format_str = ''.join([f for f in format[:i]] + [f for f in format_def[i:]])
  63. sql = "CAST(DATE_FORMAT(%s, '%s') AS DATETIME)" % (field_name, format_str)
  64. return sql, params
  65. def date_interval_sql(self, timedelta):
  66. return "INTERVAL '%d 0:0:%d:%d' DAY_MICROSECOND" % (
  67. timedelta.days, timedelta.seconds, timedelta.microseconds), []
  68. def format_for_duration_arithmetic(self, sql):
  69. if self.connection.features.supports_microsecond_precision:
  70. return 'INTERVAL %s MICROSECOND' % sql
  71. else:
  72. return 'INTERVAL FLOOR(%s / 1000000) SECOND' % sql
  73. def drop_foreignkey_sql(self):
  74. return "DROP FOREIGN KEY"
  75. def force_no_ordering(self):
  76. """
  77. "ORDER BY NULL" prevents MySQL from implicitly ordering by grouped
  78. columns. If no ordering would otherwise be applied, we don't want any
  79. implicit sorting going on.
  80. """
  81. return [(None, ("NULL", [], False))]
  82. def fulltext_search_sql(self, field_name):
  83. return 'MATCH (%s) AGAINST (%%s IN BOOLEAN MODE)' % field_name
  84. def last_executed_query(self, cursor, sql, params):
  85. # With MySQLdb, cursor objects have an (undocumented) "_last_executed"
  86. # attribute where the exact query sent to the database is saved.
  87. # See MySQLdb/cursors.py in the source distribution.
  88. return force_text(getattr(cursor, '_last_executed', None), errors='replace')
  89. def no_limit_value(self):
  90. # 2**64 - 1, as recommended by the MySQL documentation
  91. return 18446744073709551615
  92. def quote_name(self, name):
  93. if name.startswith("`") and name.endswith("`"):
  94. return name # Quoting once is enough.
  95. return "`%s`" % name
  96. def random_function_sql(self):
  97. return 'RAND()'
  98. def sql_flush(self, style, tables, sequences, allow_cascade=False):
  99. # NB: The generated SQL below is specific to MySQL
  100. # 'TRUNCATE x;', 'TRUNCATE y;', 'TRUNCATE z;'... style SQL statements
  101. # to clear all tables of all data
  102. if tables:
  103. sql = ['SET FOREIGN_KEY_CHECKS = 0;']
  104. for table in tables:
  105. sql.append('%s %s;' % (
  106. style.SQL_KEYWORD('TRUNCATE'),
  107. style.SQL_FIELD(self.quote_name(table)),
  108. ))
  109. sql.append('SET FOREIGN_KEY_CHECKS = 1;')
  110. sql.extend(self.sequence_reset_by_name_sql(style, sequences))
  111. return sql
  112. else:
  113. return []
  114. def validate_autopk_value(self, value):
  115. # MySQLism: zero in AUTO_INCREMENT field does not work. Refs #17653.
  116. if value == 0:
  117. raise ValueError('The database backend does not accept 0 as a '
  118. 'value for AutoField.')
  119. return value
  120. def value_to_db_datetime(self, value):
  121. if value is None:
  122. return None
  123. # MySQL doesn't support tz-aware datetimes
  124. if timezone.is_aware(value):
  125. if settings.USE_TZ:
  126. value = value.astimezone(timezone.utc).replace(tzinfo=None)
  127. else:
  128. raise ValueError("MySQL backend does not support timezone-aware datetimes when USE_TZ is False.")
  129. if not self.connection.features.supports_microsecond_precision:
  130. value = value.replace(microsecond=0)
  131. return six.text_type(value)
  132. def value_to_db_time(self, value):
  133. if value is None:
  134. return None
  135. # MySQL doesn't support tz-aware times
  136. if timezone.is_aware(value):
  137. raise ValueError("MySQL backend does not support timezone-aware times.")
  138. return six.text_type(value)
  139. def max_name_length(self):
  140. return 64
  141. def bulk_insert_sql(self, fields, num_values):
  142. items_sql = "(%s)" % ", ".join(["%s"] * len(fields))
  143. return "VALUES " + ", ".join([items_sql] * num_values)
  144. def combine_expression(self, connector, sub_expressions):
  145. """
  146. MySQL requires special cases for ^ operators in query expressions
  147. """
  148. if connector == '^':
  149. return 'POW(%s)' % ','.join(sub_expressions)
  150. return super(DatabaseOperations, self).combine_expression(connector, sub_expressions)
  151. def get_db_converters(self, expression):
  152. converters = super(DatabaseOperations, self).get_db_converters(expression)
  153. internal_type = expression.output_field.get_internal_type()
  154. if internal_type in ['BooleanField', 'NullBooleanField']:
  155. converters.append(self.convert_booleanfield_value)
  156. if internal_type == 'UUIDField':
  157. converters.append(self.convert_uuidfield_value)
  158. if internal_type == 'TextField':
  159. converters.append(self.convert_textfield_value)
  160. return converters
  161. def convert_booleanfield_value(self, value, expression, connection, context):
  162. if value in (0, 1):
  163. value = bool(value)
  164. return value
  165. def convert_uuidfield_value(self, value, expression, connection, context):
  166. if value is not None:
  167. value = uuid.UUID(value)
  168. return value
  169. def convert_textfield_value(self, value, expression, connection, context):
  170. if value is not None:
  171. value = force_text(value)
  172. return value