class PopLeftListF(BaseExpression): sql_expression = collapse_spaces(""" IF( (@tmp_c:=LOCATE(',', @tmp_f:=%s)) > 0, SUBSTRING(@tmp_f, @tmp_c + 1), '' ) """) def __init__(self, lhs): super(BaseExpression, self).__init__() self.lhs = lhs def get_source_expressions(self): return [self.lhs] def set_source_expressions(self, exprs): self.lhs = exprs[0] def as_sql(self, compiler, connection): field, field_params = compiler.compile(self.lhs) sql = self.sql_expression % (field) return sql, field_params
class PopListF(BaseExpression): sql_expression = collapse_spaces(""" SUBSTRING( @tmp_f:=%s, 1, IF( LOCATE(',', @tmp_f), ( CHAR_LENGTH(@tmp_f) - CHAR_LENGTH(SUBSTRING_INDEX(@tmp_f, ',', -1)) - 1 ), 0 ) ) """) def __init__(self, lhs): super(BaseExpression, self).__init__() self.lhs = lhs def get_source_expressions(self): return [self.lhs] def set_source_expressions(self, exprs): self.lhs = exprs[0] def as_sql(self, compiler, connection): field, field_params = compiler.compile(self.lhs) sql = self.sql_expression % (field) return sql, field_params
class PopLeftListF(BaseExpression): sql_expression = collapse_spaces(""" IF( (@tmp_c:=LOCATE(',', @tmp_f:=%s)) > 0, SUBSTRING(@tmp_f, @tmp_c + 1), '' ) """) def __init__(self, lhs: BaseExpression) -> None: super().__init__() self.lhs = lhs def get_source_expressions(self) -> list[BaseExpression]: return [self.lhs] def set_source_expressions(self, exprs: Iterable[BaseExpression]) -> None: (self.lhs, ) = exprs def as_sql( self, compiler: SQLCompiler, connection: BaseDatabaseWrapper, ) -> tuple[str, tuple[Any, ...]]: field, field_params = compiler.compile(self.lhs) sql = self.sql_expression % (field) return sql, tuple(field_params)
class Command(BaseCommand): args = "<optional cache aliases>" help = collapse_spaces(""" Runs cache.cull() on all your MySQLCache caches, or only those specified aliases. """) def handle(self, *aliases, **options): verbosity = options.get('verbosity') if aliases: names = set(aliases) else: names = settings.CACHES for alias in names: try: cache = caches[alias] except InvalidCacheBackendError: raise CommandError("Cache '{}' does not exist".format(alias)) if not isinstance(cache, MySQLCache): # pragma: no cover continue if verbosity >= 1: self.stdout.write("Deleting from cache '{}'... ".format(alias), ending='') num_deleted = cache.cull() if verbosity >= 1: self.stdout.write("{} entries deleted.".format(num_deleted))
class AddSetF(TwoSidedExpression): # A slightly complicated expression. # basically if 'value' is not in the set, concat the current set with a # comma and 'value' # N.B. using MySQL side variables to avoid repeat calculation of # expression[s] sql_expression = collapse_spaces(""" IF( FIND_IN_SET(@tmp_val:=%s, @tmp_f:=%s), @tmp_f, CONCAT_WS( ',', IF(CHAR_LENGTH(@tmp_f), @tmp_f, NULL), @tmp_val ) ) """) def as_sql(self, compiler, connection): field, field_params = compiler.compile(self.lhs) value, value_params = compiler.compile(self.rhs) sql = self.sql_expression % (value, field) params = tuple(value_params) + tuple(field_params) return sql, params
class AppendLeftListF(TwoSidedExpression): # A slightly complicated expression. # basically if 'value' is not in the set, concat the current set with a # comma and 'value' # N.B. using MySQL side variables to avoid repeat calculation of # expression[s] sql_expression = collapse_spaces(""" CONCAT_WS( ',', %s, IF( (@tmp_f:=%s) > '', @tmp_f, NULL ) ) """) def as_sql(self, compiler, connection): field, field_params = compiler.compile(self.lhs) value, value_params = compiler.compile(self.rhs) sql = self.sql_expression % (value, field) params = [] params.extend(field_params) params.extend(value_params) return sql, params
class AppendListF(TwoSidedExpression): # A slightly complicated expression. # basically if 'value' is not in the set, concat the current set with a # comma and 'value' # N.B. using MySQL side variables to avoid repeat calculation of # expression[s] sql_expression = collapse_spaces(""" CONCAT_WS( ',', IF( (@tmp_f:=%s) > '', @tmp_f, NULL ), %s ) """) def as_sql( self, compiler: SQLCompiler, connection: BaseDatabaseWrapper, ) -> tuple[str, tuple[Any, ...]]: field, field_params = compiler.compile(self.lhs) value, value_params = compiler.compile(self.rhs) sql = self.sql_expression % (field, value) params = tuple(value_params) + tuple(field_params) return sql, params
class Command(BaseCommand): args = "<app_name>" help = collapse_spaces(""" Outputs a migration that will create a table. """) if django.VERSION[:2] >= (1, 10): def add_arguments(self, parser): parser.add_argument( 'aliases', metavar='aliases', nargs='*', help='Specify the cache alias(es) to create migrations for.', ) def handle(self, *args, **options): if django.VERSION[:2] >= (1, 10): aliases = set(options['aliases']) else: aliases = set(args) if not aliases: aliases = settings.CACHES tables = set() for alias in aliases: try: cache = caches[alias] except InvalidCacheBackendError: raise CommandError("Cache '{}' does not exist".format(alias)) if not isinstance(cache, MySQLCache): # pragma: no cover continue tables.add(cache._table) if not tables: self.stderr.write("No MySQLCache instances in CACHES") return migration = self.render_migration(tables) self.stdout.write(migration) def render_migration(self, tables): # This used to use a Django template, but we can't instantiate them # direct now, as the user may not have the django template engine # defined in TEMPLATES out = [header] for table in tables: out.append(table_operation.replace('{{ table }}', table), ) out.append(footer) return ''.join(out)
def strict_mode_warning(alias): message = "MySQL Strict Mode is not set for database connection '{}'" hint = collapse_spaces(""" MySQL's Strict Mode fixes many data integrity problems in MySQL, such as data truncation upon insertion, by escalating warnings into errors. It is strongly recommended you activate it. See: https://django-mysql.readthedocs.io/en/latest/checks.html#django-mysql-w001-strict-mode """) return Warning( message.format(alias), hint=hint, id='django_mysql.W001', )
def strict_mode_warning(alias): message = "MySQL Strict Mode is not set for database connection '{}'" hint = collapse_spaces(""" MySQL's Strict Mode fixes many data integrity problems in MySQL, such as data truncation upon insertion, by escalating warnings into errors. It is strongly recommended you activate it. See: http://django-mysql.readthedocs.org/en/latest/checks.html#django-mysql-w001-strict-mode """) return Warning( message.format(alias), hint=hint, id='django_mysql.W001', )
def utf8mb4_warning(alias): message = "The character set is not utf8mb4 for database connection '{}'" hint = collapse_spaces(""" The default 'utf8' character set does not include support for all Unicode characters. It's strongly recommended you move to use 'utf8mb4'. See: https://django-mysql.readthedocs.io/en/latest/checks.html#django-mysql-w003-utf8mb4 """) return Warning( message.format(alias), hint=hint, id='django_mysql.W003', )
def innodb_strict_mode_warning(alias): message = "InnoDB Strict Mode is not set for database connection '{}'" hint = collapse_spaces(""" InnoDB Strict Mode escalates several warnings around InnoDB-specific statements into errors. It's recommended you activate this, but it's not very likely to affect you if you don't. See: https://django-mysql.readthedocs.io/en/latest/checks.html#django-mysql-w002-innodb-strict-mode """) return Warning( message.format(alias), hint=hint, id='django_mysql.W002', )
def utf8mb4_warning(alias): message = "The character set is not utf8mb4 for database connection '{}'" hint = collapse_spaces(""" The default 'utf8' character set does not include support for all Unicode characters. It's strongly recommended you move to use 'utf8mb4'. See: http://django-mysql.readthedocs.org/en/latest/checks.html#django-mysql-w003-utf8mb4 """) return Warning( message.format(alias), hint=hint, id='django_mysql.W003', )
def innodb_strict_mode_warning(alias): message = "InnoDB Strict Mode is not set for database connection '{}'" hint = collapse_spaces(""" InnoDB Strict Mode escalates several warnings around InnoDB-specific statements into errors. It's recommended you activate this, but it's not very likely to affect you if you don't. See: http://django-mysql.readthedocs.org/en/latest/checks.html#django-mysql-w002-innodb-strict-mode """) return Warning( message.format(alias), hint=hint, id='django_mysql.W002', )
class RemoveSetF(TwoSidedExpression): # Wow, this is a real doozy of an expression. # Basically, if it IS in the set, cut the string up to be everything except # that element. # There are some tricks going on - e.g. LEAST to evaluate a sub expression # but not use it in the output of CONCAT_WS sql_expression = collapse_spaces(""" IF( @tmp_pos:=FIND_IN_SET(%s, @tmp_f:=%s), CONCAT_WS( ',', LEAST( @tmp_len:=( CHAR_LENGTH(@tmp_f) - CHAR_LENGTH(REPLACE(@tmp_f, ',', '')) + IF(CHAR_LENGTH(@tmp_f), 1, 0) ), NULL ), CASE WHEN (@tmp_before:=SUBSTRING_INDEX(@tmp_f, ',', @tmp_pos - 1)) = '' THEN NULL ELSE @tmp_before END, CASE WHEN (@tmp_after:= SUBSTRING_INDEX(@tmp_f, ',', - (@tmp_len - @tmp_pos))) = '' THEN NULL ELSE @tmp_after END ), @tmp_f ) """) def as_sql( self, compiler: SQLCompiler, connection: BaseDatabaseWrapper, ) -> tuple[str, tuple[Any, ...]]: field, field_params = compiler.compile(self.lhs) value, value_params = compiler.compile(self.rhs) sql = self.sql_expression % (value, field) params = tuple(value_params) + tuple(field_params) return sql, params
class SetLength(Transform): lookup_name = 'len' output_field = IntegerField() # No str.count equivalent in MySQL :( expr = collapse_spaces(""" ( CHAR_LENGTH(%s) - CHAR_LENGTH(REPLACE(%s, ',', '')) + IF(CHAR_LENGTH(%s), 1, 0) ) """) def as_sql(self, compiler, connection): lhs, params = compiler.compile(self.lhs) return self.expr % (lhs, lhs, lhs), params
class Command(BaseCommand): args = "<app_name>" help = collapse_spaces(""" Outputs a migration that will create a table. """) def add_arguments(self, parser: argparse.ArgumentParser) -> None: parser.add_argument( "aliases", metavar="aliases", nargs="*", help="Specify the cache alias(es) to create migrations for.", ) def handle(self, *args: Any, aliases: list[str], **options: Any) -> None: if not aliases: aliases = list(settings.CACHES) tables = set() for alias in aliases: try: cache = caches[alias] except InvalidCacheBackendError: raise CommandError(f"Cache '{alias}' does not exist") if not isinstance(cache, MySQLCache): # pragma: no cover continue tables.add(cache._table) if not tables: self.stderr.write("No MySQLCache instances in CACHES") return migration = self.render_migration(tables) self.stdout.write(migration) def render_migration(self, tables: set[str]) -> str: # This used to use a Django template, but we can't instantiate them # direct now, as the user may not have the django template engine # defined in TEMPLATES out = [header] for table in tables: out.append(table_operation.replace("{{ table }}", table)) out.append(footer) return "".join(out)
class SetLength(Transform): lookup_name = "len" output_field = IntegerField() # No str.count equivalent in MySQL :( expr = collapse_spaces(""" ( CHAR_LENGTH(%s) - CHAR_LENGTH(REPLACE(%s, ',', '')) + IF(CHAR_LENGTH(%s), 1, 0) ) """) def as_sql(self, compiler: SQLCompiler, connection: BaseDatabaseWrapper) -> tuple[str, Iterable[Any]]: lhs, params = compiler.compile(self.lhs) return self.expr % (lhs, lhs, lhs), params
def _check_default(self): errors = [] if isinstance(self.default, (list, dict)): errors.append( checks.Error( "Do not use mutable defaults for JSONField", hint=collapse_spaces(""" Mutable defaults get shared between all instances of the field, which probably isn't what you want. You should replace your default with a callable, e.g. replace default={{}} with default=dict. The default you passed was '{}'. """.format(self.default)), obj=self, id="django_mysql.E017", )) return errors
class Command(BaseCommand): args = "<optional cache aliases>" help = collapse_spaces(""" Runs cache.cull() on all your MySQLCache caches, or only those specified aliases. """) if django.VERSION[:2] >= (1, 10): def add_arguments(self, parser): parser.add_argument( 'aliases', metavar='aliases', nargs='*', help='Specify the cache alias(es) to cull.', ) def handle(self, *args, **options): verbosity = options.get('verbosity') if django.VERSION[:2] >= (1, 10): aliases = set(options['aliases']) else: aliases = set(args) if not aliases: aliases = settings.CACHES for alias in aliases: try: cache = caches[alias] except InvalidCacheBackendError: raise CommandError("Cache '{}' does not exist".format(alias)) if not isinstance(cache, MySQLCache): # pragma: no cover continue if verbosity >= 1: self.stdout.write( "Deleting from cache '{}'... ".format(alias), ending='', ) num_deleted = cache.cull() if verbosity >= 1: self.stdout.write("{} entries deleted.".format(num_deleted))
def _check_default(self): errors = [] if isinstance(self.default, (list, dict)): errors.append( checks.Error( 'Do not use mutable defaults for JSONField', hint=collapse_spaces(''' Mutable defaults get shared between all instances of the field, which probably isn't what you want. You should replace your default with a callable, e.g. replace default={{}} with default=dict. The default you passed was '{}'. '''.format(self.default)), obj=self, id='django_mysql.E017', ) ) return errors
class Command(BaseCommand): args = "<app_name>" help = collapse_spaces(""" Outputs a migration that will create a table. """) def handle(self, *aliases, **options): if aliases: names = set(aliases) else: names = settings.CACHES tables = set() for alias in names: try: cache = caches[alias] except InvalidCacheBackendError: raise CommandError("Cache '{}' does not exist".format(alias)) if not isinstance(cache, MySQLCache): # pragma: no cover continue tables.add(cache._table) if not tables: self.stderr.write("No MySQLCache instances in CACHES") return migration = self.render_migration(tables) self.stdout.write(migration) def render_migration(self, tables): # This used to use a Django template, but we can't instantiate them # direct now, as the user may not have the django template engine # defined in TEMPLATES out = [header] for table in tables: out.append(table_operation.replace('{{ table }}', table)) out.append(footer) return ''.join(out)
class Command(BaseCommand): args = "<optional cache aliases>" help = collapse_spaces( """ Runs cache.cull() on all your MySQLCache caches, or only those specified aliases. """ ) def add_arguments(self, parser): parser.add_argument( "aliases", metavar="aliases", nargs="*", help="Specify the cache alias(es) to cull.", ) def handle(self, *args, **options): verbosity = options.get("verbosity") aliases = set(options["aliases"]) if not aliases: aliases = settings.CACHES for alias in aliases: try: cache = caches[alias] except InvalidCacheBackendError: raise CommandError(f"Cache '{alias}' does not exist") if not isinstance(cache, MySQLCache): # pragma: no cover continue if verbosity >= 1: self.stdout.write(f"Deleting from cache '{alias}'... ", ending="") num_deleted = cache.cull() if verbosity >= 1: self.stdout.write(f"{num_deleted} entries deleted.")
class MySQLCache(BaseDatabaseCache): # Got an error with the add() query using BIGINT_UNSIGNED_MAX, so use a # value slightly 1 bit less (still an incalculable time into the future of # 1970) FOREVER_TIMEOUT = BIGINT_UNSIGNED_MAX >> 1 create_table_sql = dedent('''\ CREATE TABLE `{table_name}` ( cache_key varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL PRIMARY KEY, value longblob NOT NULL, value_type char(1) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT 'p', expires BIGINT UNSIGNED NOT NULL ); ''') @classmethod def _now(cls): # Values in the expires column are milliseconds since unix epoch (UTC) return int(time() * 1000) def __init__(self, table, params): super(MySQLCache, self).__init__(table, params) options = params.get('OPTIONS', {}) self._compress_min_length = options.get('COMPRESS_MIN_LENGTH', 5000) self._compress_level = options.get('COMPRESS_LEVEL', 6) self._cull_probability = options.get('CULL_PROBABILITY', 0.01) # Figure out our *reverse* key function if self.key_func is default_key_func: self.reverse_key_func = default_reverse_key_func if ':' in self.key_prefix: raise ValueError( "Cannot use the default KEY_FUNCTION and " "REVERSE_KEY_FUNCTION if you have a colon in your " "KEY_PREFIX.") else: reverse_key_func = params.get('REVERSE_KEY_FUNCTION', None) self.reverse_key_func = get_reverse_key_func(reverse_key_func) # Django API + helpers def get(self, key, default=None, version=None): key = self.make_key(key, version=version) self.validate_key(key) db = router.db_for_read(self.cache_model_class) table = connections[db].ops.quote_name(self._table) with connections[db].cursor() as cursor: cursor.execute(self._get_query.format(table=table), (key, self._now())) row = cursor.fetchone() if row is None: return default else: value, value_type = row return self.decode(value, value_type) _get_query = collapse_spaces(""" SELECT value, value_type FROM {table} WHERE cache_key = %s AND expires >= %s """) def get_many(self, keys, version=None): made_key_to_key = { self.make_key(key, version=version): key for key in keys } made_keys = list(made_key_to_key.keys()) for key in made_keys: self.validate_key(key) db = router.db_for_read(self.cache_model_class) table = connections[db].ops.quote_name(self._table) with connections[db].cursor() as cursor: cursor.execute(self._get_many_query.format(table=table), (made_keys, self._now())) rows = cursor.fetchall() data = {} for made_key, value, value_type in rows: key = made_key_to_key[made_key] data[key] = self.decode(value, value_type) return data _get_many_query = collapse_spaces(""" SELECT cache_key, value, value_type FROM {table} WHERE cache_key IN %s AND expires >= %s """) def set(self, key, value, timeout=DEFAULT_TIMEOUT, version=None): key = self.make_key(key, version=version) self.validate_key(key) self._base_set('set', key, value, timeout) def add(self, key, value, timeout=DEFAULT_TIMEOUT, version=None): key = self.make_key(key, version=version) self.validate_key(key) return self._base_set('add', key, value, timeout) def _base_set(self, mode, key, value, timeout=DEFAULT_TIMEOUT): exp = self.get_backend_timeout(timeout) db = router.db_for_write(self.cache_model_class) table = connections[db].ops.quote_name(self._table) self._maybe_cull() with connections[db].cursor() as cursor: value, value_type = self.encode(value) if mode == 'set': query = self._set_query params = (key, value, value_type, exp) elif mode == 'add': query = self._add_query params = (key, value, value_type, exp, self._now()) cursor.execute(query.format(table=table), params) if mode == 'set': return True elif mode == 'add': # Use a special code in the add query for "did insert" insert_id = cursor.lastrowid return (insert_id != 444) _set_many_query = collapse_spaces(""" INSERT INTO {table} (cache_key, value, value_type, expires) VALUES {{VALUES_CLAUSE}} ON DUPLICATE KEY UPDATE value=VALUES(value), value_type=VALUES(value_type), expires=VALUES(expires) """) _set_query = _set_many_query.replace('{{VALUES_CLAUSE}}', '(%s, %s, %s, %s)') # Uses the IFNULL / LEAST / LAST_INSERT_ID trick to communicate the special # value of 444 back to the client (LAST_INSERT_ID is otherwise 0, since # there is no AUTO_INCREMENT column) _add_query = collapse_spaces(""" INSERT INTO {table} (cache_key, value, value_type, expires) VALUES (%s, %s, %s, %s) ON DUPLICATE KEY UPDATE value=IF(expires > @tmp_now:=%s, value, VALUES(value)), value_type=IF(expires > @tmp_now, value_type, VALUES(value_type)), expires=IF( expires > @tmp_now, IFNULL( LEAST(LAST_INSERT_ID(444), NULL), expires ), VALUES(expires) ) """) def set_many(self, data, timeout=DEFAULT_TIMEOUT, version=None): exp = self.get_backend_timeout(timeout) db = router.db_for_write(self.cache_model_class) table = connections[db].ops.quote_name(self._table) self._maybe_cull() params = [] for key, value in six.iteritems(data): made_key = self.make_key(key, version=version) self.validate_key(made_key) value, value_type = self.encode(value) params.extend((made_key, value, value_type, exp)) query = self._set_many_query.replace( '{{VALUES_CLAUSE}}', ','.join('(%s, %s, %s, %s)' for key in data)).format(table=table) with connections[db].cursor() as cursor: cursor.execute(query, params) def delete(self, key, version=None): key = self.make_key(key, version=version) self.validate_key(key) db = router.db_for_write(self.cache_model_class) table = connections[db].ops.quote_name(self._table) with connections[db].cursor() as cursor: cursor.execute(self._delete_query.format(table=table), (key, )) _delete_query = collapse_spaces(""" DELETE FROM {table} WHERE cache_key = %s """) def delete_many(self, keys, version=None): made_keys = [self.make_key(key, version=version) for key in keys] for key in made_keys: self.validate_key(key) db = router.db_for_write(self.cache_model_class) table = connections[db].ops.quote_name(self._table) with connections[db].cursor() as cursor: cursor.execute(self._delete_many_query.format(table=table), (made_keys, )) _delete_many_query = collapse_spaces(""" DELETE FROM {table} WHERE cache_key IN %s """) def has_key(self, key, version=None): key = self.make_key(key, version=version) self.validate_key(key) db = router.db_for_read(self.cache_model_class) table = connections[db].ops.quote_name(self._table) with connections[db].cursor() as cursor: cursor.execute(self._has_key_query.format(table=table), (key, self._now())) return cursor.fetchone() is not None _has_key_query = collapse_spaces(""" SELECT 1 FROM {table} WHERE cache_key = %s and expires > %s """) def incr(self, key, delta=1, version=None): return self._base_delta(key, delta, version, '+') def decr(self, key, delta=1, version=None): return self._base_delta(key, delta, version, '-') def _base_delta(self, key, delta, version, operation): key = self.make_key(key, version=version) self.validate_key(key) db = router.db_for_write(self.cache_model_class) table = connections[db].ops.quote_name(self._table) with connections[db].cursor() as cursor: updated = cursor.execute( self._delta_query.format(table=table, operation=operation), (delta, key)) if not updated: raise ValueError("Key '%s' not found, or not an integer" % key) # New value stored in insert_id return cursor.lastrowid # Looks a bit tangled to turn the blob back into an int for updating, but # it works. Stores the new value for insert_id() with LAST_INSERT_ID _delta_query = collapse_spaces(""" UPDATE {table} SET value = LAST_INSERT_ID( CAST(value AS SIGNED INTEGER) {operation} %s ) WHERE cache_key = %s AND value_type = 'i' """) def clear(self): db = router.db_for_write(self.cache_model_class) table = connections[db].ops.quote_name(self._table) with connections[db].cursor() as cursor: cursor.execute("DELETE FROM {table}".format(table=table)) def validate_key(self, key): """ Django normally warns about maximum key length, but we error on it. """ if len(key) > 250: raise ValueError( "Cache key is longer than the maxmimum 250 characters: {}". format(key)) return super(MySQLCache, self).validate_key(key) def encode(self, obj): """ Take a Python object and return it as a tuple (value, value_type), a blob and a one-char code for what type it is """ if self._is_valid_mysql_bigint(obj): return obj, 'i' value = pickle.dumps(obj, pickle.HIGHEST_PROTOCOL) value_type = 'p' if (self._compress_min_length and len(value) >= self._compress_min_length): value = zlib.compress(value, self._compress_level) value_type = 'z' return value, value_type def _is_valid_mysql_bigint(self, value): return ( # Can't support int/long subclasses since they should are expected # to decode back to the same object (type(value) in six.integer_types) and # Can't go beyond these ranges BIGINT_SIGNED_MIN <= value <= BIGINT_SIGNED_MAX) def decode(self, value, value_type): """ Take a value blob and its value_type one-char code and convert it back to a python object """ if value_type == 'i': return int(value) if value_type == 'z': value = zlib.decompress(value) value_type = 'p' if value_type == 'p': return pickle.loads(force_bytes(value)) raise ValueError( "Unknown value_type '{}' read from the cache table.".format( value_type)) def _maybe_cull(self): # Roll the dice, if it says yes then cull if self._cull_probability and random() <= self._cull_probability: self.cull() def get_backend_timeout(self, timeout=DEFAULT_TIMEOUT): if timeout is None: return self.FOREVER_TIMEOUT timeout = super(MySQLCache, self).get_backend_timeout(timeout) return int(timeout * 1000) # Our API extensions def keys_with_prefix(self, prefix, version=None): if self.reverse_key_func is None: raise ValueError( "To use the _with_prefix commands with a custom KEY_FUNCTION, " "you need to specify a custom REVERSE_KEY_FUNCTION too.") if version is None: version = self.version db = router.db_for_read(self.cache_model_class) table = connections[db].ops.quote_name(self._table) prefix = self.make_key(prefix + '%', version=version) with connections[db].cursor() as cursor: cursor.execute( """SELECT cache_key FROM {table} WHERE cache_key LIKE %s AND expires >= %s""".format(table=table), (prefix, self._now())) rows = cursor.fetchall() full_keys = {row[0] for row in rows} keys = {} for full_key in full_keys: key, key_prefix, key_version = self.reverse_key_func(full_key) if key_version == version: keys[key] = key_version return set(six.iterkeys(keys)) def get_with_prefix(self, prefix, version=None): if self.reverse_key_func is None: raise ValueError( "To use the _with_prefix commands with a custom KEY_FUNCTION, " "you need to specify a custom REVERSE_KEY_FUNCTION too.") if version is None: version = self.version db = router.db_for_read(self.cache_model_class) table = connections[db].ops.quote_name(self._table) prefix = self.make_key(prefix + '%', version=version) version = six.text_type(version) with connections[db].cursor() as cursor: cursor.execute( """SELECT cache_key, value, value_type FROM {table} WHERE cache_key LIKE %s AND expires >= %s""".format(table=table), (prefix, self._now())) rows = cursor.fetchall() data = {} for made_key, value, value_type in rows: key, key_prefix, key_version = self.reverse_key_func(made_key) data[key] = self.decode(value, value_type) return data def delete_with_prefix(self, prefix, version=None): if version is None: version = self.version db = router.db_for_write(self.cache_model_class) table = connections[db].ops.quote_name(self._table) prefix = self.make_key(prefix + '%', version=version) with connections[db].cursor() as cursor: return cursor.execute( """DELETE FROM {table} WHERE cache_key LIKE %s""".format(table=table), (prefix, )) def cull(self): db = router.db_for_write(self.cache_model_class) table = connections[db].ops.quote_name(self._table) with connections[db].cursor() as cursor: # First, try just deleting expired keys num_deleted = cursor.execute( "DELETE FROM {table} WHERE expires < %s".format(table=table), (self._now(), )) # -1 means "Don't limit size" if self._max_entries == -1: return cursor.execute("SELECT COUNT(*) FROM {table}".format(table=table)) num = cursor.fetchone()[0] if num < self._max_entries: return num_deleted # Now do a key-based cull if self._cull_frequency == 0: num_deleted += cursor.execute( "DELETE FROM {table}".format(table=table)) else: cull_num = num // self._cull_frequency cursor.execute( """SELECT cache_key FROM {table} ORDER BY cache_key LIMIT 1 OFFSET %s""".format(table=table), (cull_num, )) max_key = cursor.fetchone()[0] num_deleted += cursor.execute( """DELETE FROM {table} WHERE cache_key < %s""".format(table=table), (max_key, )) return num_deleted
class Command(BaseCommand): args = "<optional connection alias>" help = collapse_spaces(""" Detects DateTimeFields with column type 'datetime' instead of 'datetime(6)' and outputs the SQL to fix them. """) def add_arguments(self, parser): parser.add_argument( 'alias', metavar='alias', nargs='?', default=DEFAULT_DB_ALIAS, help='Specify the database connection alias to output ' 'parameters for.', ) def handle(self, *args, **options): alias = options['alias'] try: connection = connections[alias] except ConnectionDoesNotExist: raise CommandError("Connection '{}' does not exist".format(alias)) if connection.vendor != 'mysql': raise CommandError( "{} is not a MySQL database connection".format(alias)) sqls = [] with connection.cursor() as cursor: for table_name in self.all_table_names(): sql = self.datetime_fix_sql(connection, cursor, table_name) if sql: sqls.append(sql) for sql in sqls: self.stdout.write(sql) def all_table_names(self): table_names = set() for app_config in apps.get_app_configs(): for model in app_config.get_models(): table_names.add(model._meta.db_table) return list(sorted(table_names)) def datetime_fix_sql(self, connection, cursor, table_name): cursor.execute( """ SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND DATA_TYPE = 'datetime' AND DATETIME_PRECISION = 0 ORDER BY COLUMN_NAME """, (table_name, ), ) bad_column_names = [r[0] for r in cursor.fetchall()] if not bad_column_names: return qn = connection.ops.quote_name cursor.execute("SHOW CREATE TABLE {}".format(qn(table_name))) create_table = cursor.fetchone()[1] column_specs = parse_create_table(create_table) modify_columns = [] for column_name in bad_column_names: column_spec = column_specs[column_name] new_column_spec = column_spec.replace('datetime', 'datetime(6)', 1) modify_columns.append( 'MODIFY COLUMN {} {}'.format(qn(column_name), new_column_spec), ) return 'ALTER TABLE {table_name}\n {columns};'.format( table_name=qn(table_name), columns=',\n '.join(modify_columns), )
class MySQLCache(BaseDatabaseCache): # Got an error with the add() query using BIGINT_UNSIGNED_MAX, so use a # value slightly 1 bit less (still an incalculable time into the future of # 1970) FOREVER_TIMEOUT = BIGINT_UNSIGNED_MAX >> 1 create_table_sql = dedent( """\ CREATE TABLE `{table_name}` ( cache_key varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL PRIMARY KEY, value longblob NOT NULL, value_type char(1) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT 'p', expires BIGINT UNSIGNED NOT NULL ); """ ) @classmethod def _now(cls) -> int: # Values in the expires column are milliseconds since unix epoch (UTC) return int(time() * 1000) reverse_key_func: Callable[[str], tuple[str, str, int]] | None def __init__(self, table: str, params: dict[str, Any]) -> None: super().__init__(table, params) options = params.get("OPTIONS", {}) self._compress_min_length = options.get("COMPRESS_MIN_LENGTH", 5000) self._compress_level = options.get("COMPRESS_LEVEL", 6) self._cull_probability = options.get("CULL_PROBABILITY", 0.01) # Figure out our *reverse* key function if self.key_func is default_key_func: self.reverse_key_func = default_reverse_key_func if ":" in self.key_prefix: raise ValueError( "Cannot use the default KEY_FUNCTION and " "REVERSE_KEY_FUNCTION if you have a colon in your " "KEY_PREFIX." ) else: reverse_key_func = params.get("REVERSE_KEY_FUNCTION", None) self.reverse_key_func = get_reverse_key_func(reverse_key_func) # Django API + helpers def get( self, key: str, default: Any | None = None, version: int | None = None ) -> Any: key = self.make_key(key, version=version) self.validate_key(key) db = router.db_for_read(self.cache_model_class) table = connections[db].ops.quote_name(self._table) with connections[db].cursor() as cursor: cursor.execute(self._get_query.format(table=table), (key, self._now())) row = cursor.fetchone() if row is None: return default else: value, value_type = row return self.decode(value, value_type) _get_query = collapse_spaces( """ SELECT value, value_type FROM {table} WHERE cache_key = %s AND expires >= %s """ ) def get_many( self, keys: Iterable[str], version: int | None = None ) -> dict[str, Any]: made_key_to_key = {self.make_key(key, version=version): key for key in keys} made_keys = list(made_key_to_key.keys()) for key in made_keys: self.validate_key(key) db = router.db_for_read(self.cache_model_class) table = connections[db].ops.quote_name(self._table) with connections[db].cursor() as cursor: cursor.execute( self._get_many_query.format( table=table, list_sql=get_list_sql(made_keys) ), made_keys + [self._now()], ) rows = cursor.fetchall() data = {} for made_key, value, value_type in rows: key = made_key_to_key[made_key] data[key] = self.decode(value, value_type) return data _get_many_query = collapse_spaces( """ SELECT cache_key, value, value_type FROM {table} WHERE cache_key IN {list_sql} AND expires >= %s """ ) def set( self, key: str, value: Any, timeout: Any = DEFAULT_TIMEOUT, version: int | None = None, ) -> None: key = self.make_key(key, version=version) self.validate_key(key) self._base_set("set", key, value, timeout) def add( self, key: str, value: Any, timeout: Any = DEFAULT_TIMEOUT, version: int | None = None, ) -> bool: key = self.make_key(key, version=version) self.validate_key(key) return self._base_set("add", key, value, timeout) def _base_set( self, mode: str, key: str, value: Any, timeout: Any = DEFAULT_TIMEOUT ) -> bool: if mode not in ("set", "add"): raise ValueError("'mode' should be 'set' or 'add'") exp = self.get_backend_timeout(timeout) db = router.db_for_write(self.cache_model_class) table = connections[db].ops.quote_name(self._table) self._maybe_cull() with connections[db].cursor() as cursor: value, value_type = self.encode(value) params: tuple[Any, ...] if mode == "set": query = self._set_query params = (key, value, value_type, exp) else: # mode = 'add' query = self._add_query params = (key, value, value_type, exp, self._now()) cursor.execute(query.format(table=table), params) if mode == "set": return True else: # mode = 'add' # Use a special code in the add query for "did insert" insert_id = cursor.lastrowid return insert_id != 444 _set_many_query = collapse_spaces( """ INSERT INTO {table} (cache_key, value, value_type, expires) VALUES {{VALUES_CLAUSE}} ON DUPLICATE KEY UPDATE value=VALUES(value), value_type=VALUES(value_type), expires=VALUES(expires) """ ) _set_query = _set_many_query.replace("{{VALUES_CLAUSE}}", "(%s, %s, %s, %s)") # Uses the IFNULL / LEAST / LAST_INSERT_ID trick to communicate the special # value of 444 back to the client (LAST_INSERT_ID is otherwise 0, since # there is no AUTO_INCREMENT column) _add_query = collapse_spaces( """ INSERT INTO {table} (cache_key, value, value_type, expires) VALUES (%s, %s, %s, %s) ON DUPLICATE KEY UPDATE value=IF(expires > @tmp_now:=%s, value, VALUES(value)), value_type=IF(expires > @tmp_now, value_type, VALUES(value_type)), expires=IF( expires > @tmp_now, IFNULL( LEAST(LAST_INSERT_ID(444), NULL), expires ), VALUES(expires) ) """ ) def set_many( self, data: dict[str, Any], timeout: Any = DEFAULT_TIMEOUT, version: int | None = None, ) -> list[str]: exp = self.get_backend_timeout(timeout) db = router.db_for_write(self.cache_model_class) table = connections[db].ops.quote_name(self._table) self._maybe_cull() params: list[Any] = [] for key, value in data.items(): made_key = self.make_key(key, version=version) self.validate_key(made_key) value, value_type = self.encode(value) params.extend((made_key, value, value_type, exp)) query = self._set_many_query.replace( "{{VALUES_CLAUSE}}", ",".join("(%s, %s, %s, %s)" for key in data) ).format(table=table) with connections[db].cursor() as cursor: cursor.execute(query, params) return [] def delete(self, key: str, version: int | None = None) -> None: key = self.make_key(key, version=version) self.validate_key(key) db = router.db_for_write(self.cache_model_class) table = connections[db].ops.quote_name(self._table) with connections[db].cursor() as cursor: cursor.execute(self._delete_query.format(table=table), (key,)) _delete_query = collapse_spaces( """ DELETE FROM {table} WHERE cache_key = %s """ ) def delete_many(self, keys: Iterable[str], version: int | None = None) -> None: made_keys = [self.make_key(key, version=version) for key in keys] for key in made_keys: self.validate_key(key) db = router.db_for_write(self.cache_model_class) table = connections[db].ops.quote_name(self._table) with connections[db].cursor() as cursor: cursor.execute( self._delete_many_query.format( table=table, list_sql=get_list_sql(made_keys) ), made_keys, ) _delete_many_query = collapse_spaces( """ DELETE FROM {table} WHERE cache_key IN {list_sql} """ ) def has_key(self, key: str, version: int | None = None) -> bool: key = self.make_key(key, version=version) self.validate_key(key) db = router.db_for_read(self.cache_model_class) table = connections[db].ops.quote_name(self._table) with connections[db].cursor() as cursor: cursor.execute(self._has_key_query.format(table=table), (key, self._now())) return cursor.fetchone() is not None _has_key_query = collapse_spaces( """ SELECT 1 FROM {table} WHERE cache_key = %s and expires > %s """ ) def incr(self, key: str, delta: int = 1, version: int | None = None) -> int: return self._base_delta(key, delta, version, "+") def decr(self, key: str, delta: int = 1, version: int | None = None) -> int: return self._base_delta(key, delta, version, "-") def _base_delta( self, key: str, delta: int, version: int | None, operation: _BaseDeltaType, ) -> int: key = self.make_key(key, version=version) self.validate_key(key) db = router.db_for_write(self.cache_model_class) table = connections[db].ops.quote_name(self._table) with connections[db].cursor() as cursor: updated = cursor.execute( self._delta_query.format(table=table, operation=operation), (delta, key) ) if not updated: raise ValueError("Key '%s' not found, or not an integer" % key) # New value stored in insert_id return cursor.lastrowid # Looks a bit tangled to turn the blob back into an int for updating, but # it works. Stores the new value for insert_id() with LAST_INSERT_ID _delta_query = collapse_spaces( """ UPDATE {table} SET value = LAST_INSERT_ID( CAST(value AS SIGNED INTEGER) {operation} %s ) WHERE cache_key = %s AND value_type = 'i' """ ) def clear(self) -> None: db = router.db_for_write(self.cache_model_class) table = connections[db].ops.quote_name(self._table) with connections[db].cursor() as cursor: cursor.execute(f"DELETE FROM {table}") def touch( self, key: str, timeout: Any = DEFAULT_TIMEOUT, version: int | None = None ) -> None: key = self.make_key(key, version=version) self.validate_key(key) exp = self.get_backend_timeout(timeout) db = router.db_for_write(self.cache_model_class) table = connections[db].ops.quote_name(self._table) with connections[db].cursor() as cursor: cursor.execute( self._touch_query.format(table=table), [exp, key, self._now()] ) _touch_query = collapse_spaces( """ UPDATE {table} SET expires = %s WHERE cache_key = %s AND expires >= %s """ ) def validate_key(self, key: str) -> None: """ Django normally warns about maximum key length, but we error on it. """ if len(key) > 250: raise ValueError( f"Cache key is longer than the maxmimum 250 characters: {key}" ) return super().validate_key(key) def encode(self, obj: Any) -> tuple[int | bytes, _EncodedKeyType]: """ Take a Python object and return it as a tuple (value, value_type), a blob and a one-char code for what type it is """ if self._is_valid_mysql_bigint(obj): return obj, "i" value = pickle.dumps(obj, pickle.HIGHEST_PROTOCOL) value_type: _EncodedKeyType = "p" if self._compress_min_length and len(value) >= self._compress_min_length: value = zlib.compress(value, self._compress_level) value_type = "z" return value, value_type def _is_valid_mysql_bigint(self, value: int | bytes) -> bool: return ( # Can't support int subclasses since they should are expected to # decode back to the same object type(value) is int # Can't go beyond these ranges and BIGINT_SIGNED_MIN <= value <= BIGINT_SIGNED_MAX ) def decode(self, value: bytes, value_type: _EncodedKeyType) -> Any: """ Take a value blob and its value_type one-char code and convert it back to a python object """ if value_type == "i": return int(value) raw_value: bytes if value_type == "z": raw_value = zlib.decompress(value) value_type = "p" else: raw_value = force_bytes(value) if value_type == "p": return pickle.loads(raw_value) raise ValueError( f"Unknown value_type '{value_type}' read from the cache table." ) def _maybe_cull(self) -> None: # Roll the dice, if it says yes then cull if self._cull_probability and random() <= self._cull_probability: self.cull() def get_backend_timeout(self, timeout: Any = DEFAULT_TIMEOUT) -> int: if timeout is None: return self.FOREVER_TIMEOUT timeout = super().get_backend_timeout(timeout) return int(timeout * 1000) # Our API extensions def keys_with_prefix( self, prefix: str, version: int | None = None ) -> builtins.set[str]: if self.reverse_key_func is None: raise ValueError( "To use the _with_prefix commands with a custom KEY_FUNCTION, " "you need to specify a custom REVERSE_KEY_FUNCTION too." ) if version is None: version = self.version db = router.db_for_read(self.cache_model_class) table = connections[db].ops.quote_name(self._table) prefix = self.make_key(prefix + "%", version=version) with connections[db].cursor() as cursor: cursor.execute( """SELECT cache_key FROM {table} WHERE cache_key LIKE %s AND expires >= %s""".format( table=table ), (prefix, self._now()), ) rows = cursor.fetchall() full_keys = {row[0] for row in rows} keys = {} for full_key in full_keys: key, key_prefix, key_version = self.reverse_key_func(full_key) keys[key] = key_version return set(keys) def get_with_prefix( self, prefix: str, version: int | None = None ) -> dict[str, Any]: if self.reverse_key_func is None: raise ValueError( "To use the _with_prefix commands with a custom KEY_FUNCTION, " "you need to specify a custom REVERSE_KEY_FUNCTION too." ) if version is None: version = self.version db = router.db_for_read(self.cache_model_class) table = connections[db].ops.quote_name(self._table) prefix = self.make_key(prefix + "%", version=version) with connections[db].cursor() as cursor: cursor.execute( """SELECT cache_key, value, value_type FROM {table} WHERE cache_key LIKE %s AND expires >= %s""".format( table=table ), (prefix, self._now()), ) rows = cursor.fetchall() data = {} for made_key, value, value_type in rows: key, key_prefix, key_version = self.reverse_key_func(made_key) data[key] = self.decode(value, value_type) return data def delete_with_prefix(self, prefix: str, version: int | None = None) -> int: if version is None: version = self.version db = router.db_for_write(self.cache_model_class) table = connections[db].ops.quote_name(self._table) prefix = self.make_key(prefix + "%", version=version) with connections[db].cursor() as cursor: return cursor.execute( """DELETE FROM {table} WHERE cache_key LIKE %s""".format( table=table ), (prefix,), ) def cull(self) -> int: db = router.db_for_write(self.cache_model_class) table = connections[db].ops.quote_name(self._table) with connections[db].cursor() as cursor: # First, try just deleting expired keys num_deleted = cursor.execute( f"DELETE FROM {table} WHERE expires < %s", (self._now(),), ) # -1 means "Don't limit size" if self._max_entries == -1: return 0 cursor.execute(f"SELECT COUNT(*) FROM {table}") num = cursor.fetchone()[0] if num < self._max_entries: return num_deleted # Now do a key-based cull if self._cull_frequency == 0: num_deleted += cursor.execute(f"DELETE FROM {table}") else: cull_num = num // self._cull_frequency cursor.execute( """SELECT cache_key FROM {table} ORDER BY cache_key LIMIT 1 OFFSET %s""".format( table=table ), (cull_num,), ) max_key = cursor.fetchone()[0] num_deleted += cursor.execute( """DELETE FROM {table} WHERE cache_key < %s""".format( table=table ), (max_key,), ) return num_deleted