Django-1.2.1/django/db/backends/mysql/base.py

   1 """
   2 MySQL database backend for Django.
   3
   4 Requires MySQLdb: http://sourceforge.net/projects/mysql-python
   5 """
   6
   7 import re
   8 import sys
   9
  10 try:
  11     import MySQLdb as Database
  12 except ImportError, e:
  13     from django.core.exceptions import ImproperlyConfigured
  14     raise ImproperlyConfigured("Error loading MySQLdb module: %s" % e)
  15
  16 # We want version (1, 2, 1, 'final', 2) or later. We can't just use
  17 # lexicographic ordering in this check because then (1, 2, 1, 'gamma')
  18 # inadvertently passes the version test.
  19 version = Database.version_info
  20 if (version < (1,2,1) or (version[:3] == (1, 2, 1) and
  21         (len(version) < 5 or version[3] != 'final' or version[4] < 2))):
  22     from django.core.exceptions import ImproperlyConfigured
  23     raise ImproperlyConfigured("MySQLdb-1.2.1p2 or newer is required; you have %s" % Database.__version__)
  24
  25 from MySQLdb.converters import conversions
  26 from MySQLdb.constants import FIELD_TYPE, FLAG, CLIENT
  27
  28 from django.db import utils
  29 from django.db.backends import *
  30 from django.db.backends.signals import connection_created
  31 from django.db.backends.mysql.client import DatabaseClient
  32 from django.db.backends.mysql.creation import DatabaseCreation
  33 from django.db.backends.mysql.introspection import DatabaseIntrospection
  34 from django.db.backends.mysql.validation import DatabaseValidation
  35 from django.utils.safestring import SafeString, SafeUnicode
  36
  37 # Raise exceptions for database warnings if DEBUG is on
  38 from django.conf import settings
  39 if settings.DEBUG:
  40     from warnings import filterwarnings
  41     filterwarnings("error", category=Database.Warning)
  42
  43 DatabaseError = Database.DatabaseError
  44 IntegrityError = Database.IntegrityError
  45
  46 # MySQLdb-1.2.1 returns TIME columns as timedelta -- they are more like
  47 # timedelta in terms of actual behavior as they are signed and include days --
  48 # and Django expects time, so we still need to override that. We also need to
  49 # add special handling for SafeUnicode and SafeString as MySQLdb's type
  50 # checking is too tight to catch those (see Django ticket #6052).
  51 django_conversions = conversions.copy()
  52 django_conversions.update({
  53     FIELD_TYPE.TIME: util.typecast_time,
  54     FIELD_TYPE.DECIMAL: util.typecast_decimal,
  55     FIELD_TYPE.NEWDECIMAL: util.typecast_decimal,
  56 })
  57
  58 # This should match the numerical portion of the version numbers (we can treat
  59 # versions like 5.0.24 and 5.0.24a as the same). Based on the list of version
  60 # at http://dev.mysql.com/doc/refman/4.1/en/news.html and
  61 # http://dev.mysql.com/doc/refman/5.0/en/news.html .
  62 server_version_re = re.compile(r'(\d{1,2})\.(\d{1,2})\.(\d{1,2})')
  63
  64 # MySQLdb-1.2.1 and newer automatically makes use of SHOW WARNINGS on
  65 # MySQL-4.1 and newer, so the MysqlDebugWrapper is unnecessary. Since the
  66 # point is to raise Warnings as exceptions, this can be done with the Python
  67 # warning module, and this is setup when the connection is created, and the
  68 # standard util.CursorDebugWrapper can be used. Also, using sql_mode
  69 # TRADITIONAL will automatically cause most warnings to be treated as errors.
  70
  71 class CursorWrapper(object):
  72     """
  73     A thin wrapper around MySQLdb's normal cursor class so that we can catch
  74     particular exception instances and reraise them with the right types.
  75
  76     Implemented as a wrapper, rather than a subclass, so that we aren't stuck
  77     to the particular underlying representation returned by Connection.cursor().
  78     """
  79     codes_for_integrityerror = (1048,)
  80
  81     def __init__(self, cursor):
  82         self.cursor = cursor
  83
  84     def execute(self, query, args=None):
  85         try:
  86             return self.cursor.execute(query, args)
  87         except Database.IntegrityError, e:
  88             raise utils.IntegrityError, utils.IntegrityError(*tuple(e)), sys.exc_info()[2]
  89         except Database.OperationalError, e:
  90             # Map some error codes to IntegrityError, since they seem to be
  91             # misclassified and Django would prefer the more logical place.
  92             if e[0] in self.codes_for_integrityerror:
  93                 raise utils.IntegrityError, utils.IntegrityError(*tuple(e)), sys.exc_info()[2]
  94             raise
  95         except Database.DatabaseError, e:
  96             raise utils.DatabaseError, utils.DatabaseError(*tuple(e)), sys.exc_info()[2]
  97
  98     def executemany(self, query, args):
  99         try:
 100             return self.cursor.executemany(query, args)
 101         except Database.IntegrityError, e:
 102             raise utils.IntegrityError, utils.IntegrityError(*tuple(e)), sys.exc_info()[2]
 103         except Database.OperationalError, e:
 104             # Map some error codes to IntegrityError, since they seem to be
 105             # misclassified and Django would prefer the more logical place.
 106             if e[0] in self.codes_for_integrityerror:
 107                 raise utils.IntegrityError, utils.IntegrityError(*tuple(e)), sys.exc_info()[2]
 108             raise
 109         except Database.DatabaseError, e:
 110             raise utils.DatabaseError, utils.DatabaseError(*tuple(e)), sys.exc_info()[2]
 111
 112     def __getattr__(self, attr):
 113         if attr in self.__dict__:
 114             return self.__dict__[attr]
 115         else:
 116             return getattr(self.cursor, attr)
 117
 118     def __iter__(self):
 119         return iter(self.cursor)
 120
 121 class DatabaseFeatures(BaseDatabaseFeatures):
 122     empty_fetchmany_value = ()
 123     update_can_self_select = False
 124     allows_group_by_pk = True
 125     related_fields_match_type = True
 126     allow_sliced_subqueries = False
 127
 128 class DatabaseOperations(BaseDatabaseOperations):
 129     compiler_module = "django.db.backends.mysql.compiler"
 130
 131     def date_extract_sql(self, lookup_type, field_name):
 132         # http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
 133         if lookup_type == 'week_day':
 134             # DAYOFWEEK() returns an integer, 1-7, Sunday=1.
 135             # Note: WEEKDAY() returns 0-6, Monday=0.
 136             return "DAYOFWEEK(%s)" % field_name
 137         else:
 138             return "EXTRACT(%s FROM %s)" % (lookup_type.upper(), field_name)
 139
 140     def date_trunc_sql(self, lookup_type, field_name):
 141         fields = ['year', 'month', 'day', 'hour', 'minute', 'second']
 142         format = ('%%Y-', '%%m', '-%%d', ' %%H:', '%%i', ':%%s') # Use double percents to escape.
 143         format_def = ('0000-', '01', '-01', ' 00:', '00', ':00')
 144         try:
 145             i = fields.index(lookup_type) + 1
 146         except ValueError:
 147             sql = field_name
 148         else:
 149             format_str = ''.join([f for f in format[:i]] + [f for f in format_def[i:]])
 150             sql = "CAST(DATE_FORMAT(%s, '%s') AS DATETIME)" % (field_name, format_str)
 151         return sql
 152
 153     def drop_foreignkey_sql(self):
 154         return "DROP FOREIGN KEY"
 155
 156     def force_no_ordering(self):
 157         """
 158         "ORDER BY NULL" prevents MySQL from implicitly ordering by grouped
 159         columns. If no ordering would otherwise be applied, we don't want any
 160         implicit sorting going on.
 161         """
 162         return ["NULL"]
 163
 164     def fulltext_search_sql(self, field_name):
 165         return 'MATCH (%s) AGAINST (%%s IN BOOLEAN MODE)' % field_name
 166
 167     def no_limit_value(self):
 168         # 2**64 - 1, as recommended by the MySQL documentation
 169         return 18446744073709551615L
 170
 171     def quote_name(self, name):
 172         if name.startswith("`") and name.endswith("`"):
 173             return name # Quoting once is enough.
 174         return "`%s`" % name
 175
 176     def random_function_sql(self):
 177         return 'RAND()'
 178
 179     def sql_flush(self, style, tables, sequences):
 180         # NB: The generated SQL below is specific to MySQL
 181         # 'TRUNCATE x;', 'TRUNCATE y;', 'TRUNCATE z;'... style SQL statements
 182         # to clear all tables of all data
 183         if tables:
 184             sql = ['SET FOREIGN_KEY_CHECKS = 0;']
 185             for table in tables:
 186                 sql.append('%s %s;' % (style.SQL_KEYWORD('TRUNCATE'), style.SQL_FIELD(self.quote_name(table))))
 187             sql.append('SET FOREIGN_KEY_CHECKS = 1;')
 188
 189             # 'ALTER TABLE table AUTO_INCREMENT = 1;'... style SQL statements
 190             # to reset sequence indices
 191             sql.extend(["%s %s %s %s %s;" % \
 192                 (style.SQL_KEYWORD('ALTER'),
 193                  style.SQL_KEYWORD('TABLE'),
 194                  style.SQL_TABLE(self.quote_name(sequence['table'])),
 195                  style.SQL_KEYWORD('AUTO_INCREMENT'),
 196                  style.SQL_FIELD('= 1'),
 197                 ) for sequence in sequences])
 198             return sql
 199         else:
 200             return []
 201
 202     def value_to_db_datetime(self, value):
 203         if value is None:
 204             return None
 205
 206         # MySQL doesn't support tz-aware datetimes
 207         if value.tzinfo is not None:
 208             raise ValueError("MySQL backend does not support timezone-aware datetimes.")
 209
 210         # MySQL doesn't support microseconds
 211         return unicode(value.replace(microsecond=0))
 212
 213     def value_to_db_time(self, value):
 214         if value is None:
 215             return None
 216
 217         # MySQL doesn't support tz-aware datetimes
 218         if value.tzinfo is not None:
 219             raise ValueError("MySQL backend does not support timezone-aware datetimes.")
 220
 221         # MySQL doesn't support microseconds
 222         return unicode(value.replace(microsecond=0))
 223
 224     def year_lookup_bounds(self, value):
 225         # Again, no microseconds
 226         first = '%s-01-01 00:00:00'
 227         second = '%s-12-31 23:59:59.99'
 228         return [first % value, second % value]
 229
 230     def max_name_length(self):
 231         return 64
 232
 233 class DatabaseWrapper(BaseDatabaseWrapper):
 234
 235     operators = {
 236         'exact': '= %s',
 237         'iexact': 'LIKE %s',
 238         'contains': 'LIKE BINARY %s',
 239         'icontains': 'LIKE %s',
 240         'regex': 'REGEXP BINARY %s',
 241         'iregex': 'REGEXP %s',
 242         'gt': '> %s',
 243         'gte': '>= %s',
 244         'lt': '< %s',
 245         'lte': '<= %s',
 246         'startswith': 'LIKE BINARY %s',
 247         'endswith': 'LIKE BINARY %s',
 248         'istartswith': 'LIKE %s',
 249         'iendswith': 'LIKE %s',
 250     }
 251
 252     def __init__(self, *args, **kwargs):
 253         super(DatabaseWrapper, self).__init__(*args, **kwargs)
 254
 255         self.server_version = None
 256         self.features = DatabaseFeatures()
 257         self.ops = DatabaseOperations()
 258         self.client = DatabaseClient(self)
 259         self.creation = DatabaseCreation(self)
 260         self.introspection = DatabaseIntrospection(self)
 261         self.validation = DatabaseValidation(self)
 262
 263     def _valid_connection(self):
 264         if self.connection is not None:
 265             try:
 266                 self.connection.ping()
 267                 return True
 268             except DatabaseError:
 269                 self.connection.close()
 270                 self.connection = None
 271         return False
 272
 273     def _cursor(self):
 274         if not self._valid_connection():
 275             kwargs = {
 276                 'conv': django_conversions,
 277                 'charset': 'utf8',
 278                 'use_unicode': True,
 279             }
 280             settings_dict = self.settings_dict
 281             if settings_dict['USER']:
 282                 kwargs['user'] = settings_dict['USER']
 283             if settings_dict['NAME']:
 284                 kwargs['db'] = settings_dict['NAME']
 285             if settings_dict['PASSWORD']:
 286                 kwargs['passwd'] = settings_dict['PASSWORD']
 287             if settings_dict['HOST'].startswith('/'):
 288                 kwargs['unix_socket'] = settings_dict['HOST']
 289             elif settings_dict['HOST']:
 290                 kwargs['host'] = settings_dict['HOST']
 291             if settings_dict['PORT']:
 292                 kwargs['port'] = int(settings_dict['PORT'])
 293             # We need the number of potentially affected rows after an
 294             # "UPDATE", not the number of changed rows.
 295             kwargs['client_flag'] = CLIENT.FOUND_ROWS
 296             kwargs.update(settings_dict['OPTIONS'])
 297             self.connection = Database.connect(**kwargs)
 298             self.connection.encoders[SafeUnicode] = self.connection.encoders[unicode]
 299             self.connection.encoders[SafeString] = self.connection.encoders[str]
 300             connection_created.send(sender=self.__class__)
 301         cursor = CursorWrapper(self.connection.cursor())
 302         return cursor
 303
 304     def _rollback(self):
 305         try:
 306             BaseDatabaseWrapper._rollback(self)
 307         except Database.NotSupportedError:
 308             pass
 309
 310     def get_server_version(self):
 311         if not self.server_version:
 312             if not self._valid_connection():
 313                 self.cursor()
 314             m = server_version_re.match(self.connection.get_server_info())
 315             if not m:
 316                 raise Exception('Unable to determine MySQL version from version string %r' % self.connection.get_server_info())
 317             self.server_version = tuple([int(x) for x in m.groups()])
 318         return self.server_version