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