1 """

     2 MySQL database backend for Django.

     3 

     4 Requires MySQLdb: http://sourceforge.net/projects/mysql-python

     5 """

     6 

     7 import re

     8 

     9 try:

    10     import MySQLdb as Database

    11 except ImportError, e:

    12     from django.core.exceptions import ImproperlyConfigured

    13     raise ImproperlyConfigured("Error loading MySQLdb module: %s" % e)

    14 

    15 # We want version (1, 2, 1, 'final', 2) or later. We can't just use

    16 # lexicographic ordering in this check because then (1, 2, 1, 'gamma')

    17 # inadvertently passes the version test.

    18 version = Database.version_info

    19 if (version < (1,2,1) or (version[:3] == (1, 2, 1) and

    20         (len(version) < 5 or version[3] != 'final' or version[4] < 2))):

    21     from django.core.exceptions import ImproperlyConfigured

    22     raise ImproperlyConfigured("MySQLdb-1.2.1p2 or newer is required; you have %s" % Database.__version__)

    23 

    24 from MySQLdb.converters import conversions

    25 from MySQLdb.constants import FIELD_TYPE, FLAG, CLIENT

    26 

    27 from django.db.backends import *

    28 from django.db.backends.signals import connection_created

    29 from django.db.backends.mysql.client import DatabaseClient

    30 from django.db.backends.mysql.creation import DatabaseCreation

    31 from django.db.backends.mysql.introspection import DatabaseIntrospection

    32 from django.db.backends.mysql.validation import DatabaseValidation

    33 from django.utils.safestring import SafeString, SafeUnicode

    34 

    35 # Raise exceptions for database warnings if DEBUG is on

    36 from django.conf import settings

    37 if settings.DEBUG:

    38     from warnings import filterwarnings

    39     filterwarnings("error", category=Database.Warning)

    40 

    41 DatabaseError = Database.DatabaseError

    42 IntegrityError = Database.IntegrityError

    43 

    44 # MySQLdb-1.2.1 returns TIME columns as timedelta -- they are more like

    45 # timedelta in terms of actual behavior as they are signed and include days --

    46 # and Django expects time, so we still need to override that. We also need to

    47 # add special handling for SafeUnicode and SafeString as MySQLdb's type

    48 # checking is too tight to catch those (see Django ticket #6052).

    49 django_conversions = conversions.copy()

    50 django_conversions.update({

    51     FIELD_TYPE.TIME: util.typecast_time,

    52     FIELD_TYPE.DECIMAL: util.typecast_decimal,

    53     FIELD_TYPE.NEWDECIMAL: util.typecast_decimal,

    54 })

    55 

    56 # This should match the numerical portion of the version numbers (we can treat

    57 # versions like 5.0.24 and 5.0.24a as the same). Based on the list of version

    58 # at http://dev.mysql.com/doc/refman/4.1/en/news.html and

    59 # http://dev.mysql.com/doc/refman/5.0/en/news.html .

    60 server_version_re = re.compile(r'(\d{1,2})\.(\d{1,2})\.(\d{1,2})')

    61 

    62 # MySQLdb-1.2.1 and newer automatically makes use of SHOW WARNINGS on

    63 # MySQL-4.1 and newer, so the MysqlDebugWrapper is unnecessary. Since the

    64 # point is to raise Warnings as exceptions, this can be done with the Python

    65 # warning module, and this is setup when the connection is created, and the

    66 # standard util.CursorDebugWrapper can be used. Also, using sql_mode

    67 # TRADITIONAL will automatically cause most warnings to be treated as errors.

    68 

    69 class CursorWrapper(object):

    70     """

    71     A thin wrapper around MySQLdb's normal cursor class so that we can catch

    72     particular exception instances and reraise them with the right types.

    73 

    74     Implemented as a wrapper, rather than a subclass, so that we aren't stuck

    75     to the particular underlying representation returned by Connection.cursor().

    76     """

    77     codes_for_integrityerror = (1048,)

    78 

    79     def __init__(self, cursor):

    80         self.cursor = cursor

    81 

    82     def execute(self, query, args=None):

    83         try:

    84             return self.cursor.execute(query, args)

    85         except Database.OperationalError, e:

    86             # Map some error codes to IntegrityError, since they seem to be

    87             # misclassified and Django would prefer the more logical place.

    88             if e[0] in self.codes_for_integrityerror:

    89                 raise Database.IntegrityError(tuple(e))

    90             raise

    91 

    92     def executemany(self, query, args):

    93         try:

    94             return self.cursor.executemany(query, args)

    95         except Database.OperationalError, e:

    96             # Map some error codes to IntegrityError, since they seem to be

    97             # misclassified and Django would prefer the more logical place.

    98             if e[0] in self.codes_for_integrityerror:

    99                 raise Database.IntegrityError(tuple(e))

   100             raise

   101 

   102     def __getattr__(self, attr):

   103         if attr in self.__dict__:

   104             return self.__dict__[attr]

   105         else:

   106             return getattr(self.cursor, attr)

   107 

   108     def __iter__(self):

   109         return iter(self.cursor)

   110 

   111 class DatabaseFeatures(BaseDatabaseFeatures):

   112     empty_fetchmany_value = ()

   113     update_can_self_select = False

   114     allows_group_by_pk = True

   115     related_fields_match_type = True

   116 

   117 class DatabaseOperations(BaseDatabaseOperations):

   118     def date_extract_sql(self, lookup_type, field_name):

   119         # http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

   120         if lookup_type == 'week_day':

   121             # DAYOFWEEK() returns an integer, 1-7, Sunday=1.

   122             # Note: WEEKDAY() returns 0-6, Monday=0.

   123             return "DAYOFWEEK(%s)" % field_name

   124         else:

   125             return "EXTRACT(%s FROM %s)" % (lookup_type.upper(), field_name)

   126 

   127     def date_trunc_sql(self, lookup_type, field_name):

   128         fields = ['year', 'month', 'day', 'hour', 'minute', 'second']

   129         format = ('%%Y-', '%%m', '-%%d', ' %%H:', '%%i', ':%%s') # Use double percents to escape.

   130         format_def = ('0000-', '01', '-01', ' 00:', '00', ':00')

   131         try:

   132             i = fields.index(lookup_type) + 1

   133         except ValueError:

   134             sql = field_name

   135         else:

   136             format_str = ''.join([f for f in format[:i]] + [f for f in format_def[i:]])

   137             sql = "CAST(DATE_FORMAT(%s, '%s') AS DATETIME)" % (field_name, format_str)

   138         return sql

   139 

   140     def drop_foreignkey_sql(self):

   141         return "DROP FOREIGN KEY"

   142 

   143     def force_no_ordering(self):

   144         """

   145         "ORDER BY NULL" prevents MySQL from implicitly ordering by grouped

   146         columns. If no ordering would otherwise be applied, we don't want any

   147         implicit sorting going on.

   148         """

   149         return ["NULL"]

   150 

   151     def fulltext_search_sql(self, field_name):

   152         return 'MATCH (%s) AGAINST (%%s IN BOOLEAN MODE)' % field_name

   153 

   154     def no_limit_value(self):

   155         # 2**64 - 1, as recommended by the MySQL documentation

   156         return 18446744073709551615L

   157 

   158     def quote_name(self, name):

   159         if name.startswith("`") and name.endswith("`"):

   160             return name # Quoting once is enough.

   161         return "`%s`" % name

   162 

   163     def random_function_sql(self):

   164         return 'RAND()'

   165 

   166     def sql_flush(self, style, tables, sequences):

   167         # NB: The generated SQL below is specific to MySQL

   168         # 'TRUNCATE x;', 'TRUNCATE y;', 'TRUNCATE z;'... style SQL statements

   169         # to clear all tables of all data

   170         if tables:

   171             sql = ['SET FOREIGN_KEY_CHECKS = 0;']

   172             for table in tables:

   173                 sql.append('%s %s;' % (style.SQL_KEYWORD('TRUNCATE'), style.SQL_FIELD(self.quote_name(table))))

   174             sql.append('SET FOREIGN_KEY_CHECKS = 1;')

   175 

   176             # 'ALTER TABLE table AUTO_INCREMENT = 1;'... style SQL statements

   177             # to reset sequence indices

   178             sql.extend(["%s %s %s %s %s;" % \

   179                 (style.SQL_KEYWORD('ALTER'),

   180                  style.SQL_KEYWORD('TABLE'),

   181                  style.SQL_TABLE(self.quote_name(sequence['table'])),

   182                  style.SQL_KEYWORD('AUTO_INCREMENT'),

   183                  style.SQL_FIELD('= 1'),

   184                 ) for sequence in sequences])

   185             return sql

   186         else:

   187             return []

   188 

   189     def value_to_db_datetime(self, value):

   190         if value is None:

   191             return None

   192 

   193         # MySQL doesn't support tz-aware datetimes

   194         if value.tzinfo is not None:

   195             raise ValueError("MySQL backend does not support timezone-aware datetimes.")

   196 

   197         # MySQL doesn't support microseconds

   198         return unicode(value.replace(microsecond=0))

   199 

   200     def value_to_db_time(self, value):

   201         if value is None:

   202             return None

   203 

   204         # MySQL doesn't support tz-aware datetimes

   205         if value.tzinfo is not None:

   206             raise ValueError("MySQL backend does not support timezone-aware datetimes.")

   207 

   208         # MySQL doesn't support microseconds

   209         return unicode(value.replace(microsecond=0))

   210 

   211     def year_lookup_bounds(self, value):

   212         # Again, no microseconds

   213         first = '%s-01-01 00:00:00'

   214         second = '%s-12-31 23:59:59.99'

   215         return [first % value, second % value]

   216 

   217 class DatabaseWrapper(BaseDatabaseWrapper):

   218 

   219     operators = {

   220         'exact': '= %s',

   221         'iexact': 'LIKE %s',

   222         'contains': 'LIKE BINARY %s',

   223         'icontains': 'LIKE %s',

   224         'regex': 'REGEXP BINARY %s',

   225         'iregex': 'REGEXP %s',

   226         'gt': '> %s',

   227         'gte': '>= %s',

   228         'lt': '< %s',

   229         'lte': '<= %s',

   230         'startswith': 'LIKE BINARY %s',

   231         'endswith': 'LIKE BINARY %s',

   232         'istartswith': 'LIKE %s',

   233         'iendswith': 'LIKE %s',

   234     }

   235 

   236     def __init__(self, *args, **kwargs):

   237         super(DatabaseWrapper, self).__init__(*args, **kwargs)

   238 

   239         self.server_version = None

   240         self.features = DatabaseFeatures()

   241         self.ops = DatabaseOperations()

   242         self.client = DatabaseClient(self)

   243         self.creation = DatabaseCreation(self)

   244         self.introspection = DatabaseIntrospection(self)

   245         self.validation = DatabaseValidation()

   246 

   247     def _valid_connection(self):

   248         if self.connection is not None:

   249             try:

   250                 self.connection.ping()

   251                 return True

   252             except DatabaseError:

   253                 self.connection.close()

   254                 self.connection = None

   255         return False

   256 

   257     def _cursor(self):

   258         if not self._valid_connection():

   259             kwargs = {

   260                 'conv': django_conversions,

   261                 'charset': 'utf8',

   262                 'use_unicode': True,

   263             }

   264             settings_dict = self.settings_dict

   265             if settings_dict['DATABASE_USER']:

   266                 kwargs['user'] = settings_dict['DATABASE_USER']

   267             if settings_dict['DATABASE_NAME']:

   268                 kwargs['db'] = settings_dict['DATABASE_NAME']

   269             if settings_dict['DATABASE_PASSWORD']:

   270                 kwargs['passwd'] = settings_dict['DATABASE_PASSWORD']

   271             if settings_dict['DATABASE_HOST'].startswith('/'):

   272                 kwargs['unix_socket'] = settings_dict['DATABASE_HOST']

   273             elif settings_dict['DATABASE_HOST']:

   274                 kwargs['host'] = settings_dict['DATABASE_HOST']

   275             if settings_dict['DATABASE_PORT']:

   276                 kwargs['port'] = int(settings_dict['DATABASE_PORT'])

   277             # We need the number of potentially affected rows after an

   278             # "UPDATE", not the number of changed rows.

   279             kwargs['client_flag'] = CLIENT.FOUND_ROWS

   280             kwargs.update(settings_dict['DATABASE_OPTIONS'])

   281             self.connection = Database.connect(**kwargs)

   282             self.connection.encoders[SafeUnicode] = self.connection.encoders[unicode]

   283             self.connection.encoders[SafeString] = self.connection.encoders[str]

   284             connection_created.send(sender=self.__class__)

   285         cursor = CursorWrapper(self.connection.cursor())

   286         return cursor

   287 

   288     def _rollback(self):

   289         try:

   290             BaseDatabaseWrapper._rollback(self)

   291         except Database.NotSupportedError:

   292             pass

   293 

   294     def get_server_version(self):

   295         if not self.server_version:

   296             if not self._valid_connection():

   297                 self.cursor()

   298             m = server_version_re.match(self.connection.get_server_info())

   299             if not m:

   300                 raise Exception('Unable to determine MySQL version from version string %r' % self.connection.get_server_info())

   301             self.server_version = tuple([int(x) for x in m.groups()])

   302         return self.server_version