1 """

     2 MySQL database backend for Django.

     3 

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

     5 """

     6 

     7 from django.db.backends import BaseDatabaseWrapper, BaseDatabaseFeatures, BaseDatabaseOperations, util

     8 from django.utils.encoding import force_unicode

     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 from MySQLdb.converters import conversions

    15 from MySQLdb.constants import FIELD_TYPE

    16 import types

    17 import re

    18 

    19 DatabaseError = Database.DatabaseError

    20 IntegrityError = Database.IntegrityError

    21 

    22 django_conversions = conversions.copy()

    23 django_conversions.update({

    24     types.BooleanType: util.rev_typecast_boolean,

    25     FIELD_TYPE.DATETIME: util.typecast_timestamp,

    26     FIELD_TYPE.DATE: util.typecast_date,

    27     FIELD_TYPE.TIME: util.typecast_time,

    28     FIELD_TYPE.DECIMAL: util.typecast_decimal,

    29     FIELD_TYPE.STRING: force_unicode,

    30     FIELD_TYPE.VAR_STRING: force_unicode,

    31     # Note: We don't add a convertor for BLOB here. Doesn't seem to be required.

    32 })

    33 

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

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

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

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

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

    39 

    40 # This is an extra debug layer over MySQL queries, to display warnings.

    41 # It's only used when DEBUG=True.

    42 class MysqlDebugWrapper:

    43     def __init__(self, cursor):

    44         self.cursor = cursor

    45 

    46     def execute(self, sql, params=()):

    47         try:

    48             return self.cursor.execute(sql, params)

    49         except Database.Warning, w:

    50             self.cursor.execute("SHOW WARNINGS")

    51             raise Database.Warning("%s: %s" % (w, self.cursor.fetchall()))

    52 

    53     def executemany(self, sql, param_list):

    54         try:

    55             return self.cursor.executemany(sql, param_list)

    56         except Database.Warning, w:

    57             self.cursor.execute("SHOW WARNINGS")

    58             raise Database.Warning("%s: %s" % (w, self.cursor.fetchall()))

    59 

    60     def __getattr__(self, attr):

    61         if attr in self.__dict__:

    62             return self.__dict__[attr]

    63         else:

    64             return getattr(self.cursor, attr)

    65 

    66 class DatabaseFeatures(BaseDatabaseFeatures):

    67     autoindexes_primary_keys = False

    68     inline_fk_references = False

    69     empty_fetchmany_value = ()

    70     update_can_self_select = False

    71 

    72 class DatabaseOperations(BaseDatabaseOperations):

    73     def date_extract_sql(self, lookup_type, field_name):

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

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

    76 

    77     def date_trunc_sql(self, lookup_type, field_name):

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

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

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

    81         try:

    82             i = fields.index(lookup_type) + 1

    83         except ValueError:

    84             sql = field_name

    85         else:

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

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

    88         return sql

    89 

    90     def drop_foreignkey_sql(self):

    91         return "DROP FOREIGN KEY"

    92 

    93     def fulltext_search_sql(self, field_name):

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

    95 

    96     def limit_offset_sql(self, limit, offset=None):

    97         # 'LIMIT 20,40'

    98         sql = "LIMIT "

    99         if offset and offset != 0:

   100             sql += "%s," % offset

   101         return sql + str(limit)

   102 

   103     def no_limit_value(self):

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

   105         return 18446744073709551615L

   106 

   107     def quote_name(self, name):

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

   109             return name # Quoting once is enough.

   110         return "`%s`" % name

   111 

   112     def random_function_sql(self):

   113         return 'RAND()'

   114 

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

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

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

   118         # to clear all tables of all data

   119         if tables:

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

   121             for table in tables:

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

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

   124 

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

   126             # to reset sequence indices

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

   128                 (style.SQL_KEYWORD('ALTER'),

   129                  style.SQL_KEYWORD('TABLE'),

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

   131                  style.SQL_KEYWORD('AUTO_INCREMENT'),

   132                  style.SQL_FIELD('= 1'),

   133                 ) for sequence in sequences])

   134             return sql

   135         else:

   136             return []

   137 

   138 class DatabaseWrapper(BaseDatabaseWrapper):

   139     features = DatabaseFeatures()

   140     ops = DatabaseOperations()

   141     operators = {

   142         'exact': '= %s',

   143         'iexact': 'LIKE %s',

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

   145         'icontains': 'LIKE %s',

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

   147         'iregex': 'REGEXP %s',

   148         'gt': '> %s',

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

   150         'lt': '< %s',

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

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

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

   154         'istartswith': 'LIKE %s',

   155         'iendswith': 'LIKE %s',

   156     }

   157 

   158     def __init__(self, **kwargs):

   159         super(DatabaseWrapper, self).__init__(**kwargs)

   160         self.server_version = None

   161 

   162     def _valid_connection(self):

   163         if self.connection is not None:

   164             try:

   165                 self.connection.ping()

   166                 return True

   167             except DatabaseError:

   168                 self.connection.close()

   169                 self.connection = None

   170         return False

   171 

   172     def _cursor(self, settings):

   173         if not self._valid_connection():

   174             kwargs = {

   175                 # Note: use_unicode intentonally not set to work around some

   176                 # backwards-compat issues. We do it manually.

   177                 'user': settings.DATABASE_USER,

   178                 'db': settings.DATABASE_NAME,

   179                 'passwd': settings.DATABASE_PASSWORD,

   180                 'conv': django_conversions,

   181             }

   182             if settings.DATABASE_HOST.startswith('/'):

   183                 kwargs['unix_socket'] = settings.DATABASE_HOST

   184             else:

   185                 kwargs['host'] = settings.DATABASE_HOST

   186             if settings.DATABASE_PORT:

   187                 kwargs['port'] = int(settings.DATABASE_PORT)

   188             kwargs.update(self.options)

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

   190             cursor = self.connection.cursor()

   191             if self.connection.get_server_info() >= '4.1' and not self.connection.character_set_name().startswith('utf8'):

   192                 if hasattr(self.connection, 'charset'):

   193                     # MySQLdb < 1.2.1 backwards-compat hacks.

   194                     conn = self.connection

   195                     cursor.execute("SET NAMES 'utf8'")

   196                     cursor.execute("SET CHARACTER SET 'utf8'")

   197                     to_str = lambda u, dummy=None, c=conn: c.literal(u.encode('utf-8'))

   198                     conn.converter[unicode] = to_str

   199                 else:

   200                     self.connection.set_character_set('utf8')

   201         else:

   202             cursor = self.connection.cursor()

   203         return cursor

   204 

   205     def make_debug_cursor(self, cursor):

   206         return BaseDatabaseWrapper.make_debug_cursor(self, MysqlDebugWrapper(cursor))

   207 

   208     def _rollback(self):

   209         try:

   210             BaseDatabaseWrapper._rollback(self)

   211         except Database.NotSupportedError:

   212             pass

   213 

   214     def get_server_version(self):

   215         if not self.server_version:

   216             if not self._valid_connection():

   217                 self.cursor()

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

   219             if not m:

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

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

   222         return self.server_version